Table1--mysql常用操作

主题

用例

说明

书写规范

 

  • 数据库和表的名称不一定要大写。
  • 输入文本类型的数据时都要加上单引号；
  • NULL 表示未定义，它不会等于另一个NULL；
  • 不要使用双引号。

数据库授权

grant all privileges on hive_metadata.* to 'root'@'%' identified by '123456';

grant select on 数据库.* to 用户名@登录主机 identified by '密码'

创建和使用数据库

  • CREATE DATABASE greg_lists;
  • USE greg_lists;

 

创建表

CREATE TABLE doughnut_list

(

    contact_id INT NOT NULL AUTO_INCREMENT,

    doughnut_name VARCHAR(10) NOT NULL,

    doughnut_type VARCHAT(6) NOT NULL,

    doughnut_cost DEC(3,2) NOT NULL DEFALUT 1.00 ,

    PRIMARY KEY(contact_id)

)

每个表中只有一列可以加上 AUTO_INCREMENT ，该列必须为整数类型而且不能包含NULL

检查刚刚创建的表；

查看表的构成；

查看创建表的代码

  • DESC doughnut_list;
  • DESCRIBE doughnut_list;
  • SHOW CREATE TABLE doughnut_list; 

 

删除表

DROP TABLE doughnut_list;

 

插入数据

INSERT INTO doughnut_list(doughnut_name, dougnut_type) VALUES ('xx','XXX');

顺序很重要；列名可以省略，但数据值要和列名对应。

01-查找

SELECT * FROM doughnut_list WHERE doughnut_name ='XX' AND doughnut_coat =2.00;

可以替换成：WHERE OR

 

等号可以替换成：>,<,>=,<=,<>(不等) 

02-查找

SELECT * FROM doughnut_list WHERE doughnut_name IS NULL 

 

03-查找

SELECT * FROM doughunt_list WHERE doughnut_name LIKE '%CA'  

关键字LIKE 配合通配符；

 LIKE 喜欢的两种通配符 % 和_

04-查找

SELECT * FROM doughnut_list WHERE doughnut_cost BETWEEN 3.00 AND 5.00

BETWEEN 等于<= 加>=

05-查找

SELECT * FROM doughnut_list WHERE doughnut_name IN ('xx','XX','XX')

多个or 语句的一种替换； IN 前面可以加NOT，NOT 还可以和BETWEEN 和LIKE 一起使用，此时，NOT 一定要跟在WHERE 之后，NOT IN 是个例外

06-查找

SELECT title, category FROM movie_table

    WHERE title LIKE 'A% AND category ='family' ORDER BY title DESC;

用DESC 来反正顺序；ASC 是默认的排序方式

07-查找

SELECT first_name,SUM(sales) FROM cookies_sales GROUP BY first_name,ORDER BY  SUM(sales) DESC LIMIT 2;

 

08-查找

SELECT DISTINCT sale_data FROM coockies_sales ORDER BY sale_data;

 类似的处理函数 ：AVG(); MAX(); MIN()

 

 在使用MIN()函数时，如果查询的列中有NULL，对查询结果没有影响

09-查找

SELECT COUNT(DISTINCT dale_data ) FROM cookie_sales;

10-查找

SELECT emp_no,COUNT(emp_no) AS t FROM salaries GROUP BY emp_no HAVING t>15 

WHERE后不可跟COUNT()函数，故用HAVING语句来限定t>15的条件

11-查找

SELETE RIGHT(doughnut_name,2) FROM d_l;

RIGHT() 和LEFT() 可以从列中选出指定数量的字符

 

 SUBSTRING_INDEX() 摘取部分列值

 

 UPPER() 和 LOWER() 把数组字符串改为大写或小写

 

 REVERSE() 反转字符串里的字符排序

 

 LTRIM() 和 RTRIM() 清除左侧和右侧多余的空格

 

 LENGTH() 返回字符串中字符的数量

 

 字符串函数不会改变存储在表中的内容，它们只是把字符串修改后的某样当成查询结果返回。

12-查找 & 创建

CREATE TABLE profession

    (

        id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,

        profession VARCHAR(20)

    ) AS 

        SELECT profession FROM my_contacts GROUP BY profession ORDER BY profession;

 

删除

DELETE FROM doughnut_list WHERE doughnut_cost BETWEEN 3.00 AND 5.00

 

01-更改

UPDATE doughnut_list SET doughnut_coast =2.00 WHRER doughnut_coast=3.00

 

02-更改

ALTER TABLE doughnut_list ADD COLUMN contact_id INT NOT NULL AUTO_INCREMENT FIRST ,

    ADD PRIMARY KEY (contact_id);  

可选关键字：FIRST,AFTER,BEFORE,LAST,SECOND,THIRD,以此类推

03-更改

ALTER TABLE doughnut_list ADD COLUMN XX VARCHAR(10) AFTER doughnut_cost;

04-更改

ALTER TABLE doughnut_list RENAME TO d_l;

表更改

05-更改

ALTER TABLE d_l CHANGE COLUMN XX

07：删除doughnut_cost列

几个关键字：CHANGE、MODIFY、ADD、DROP

 CHANGE 改变现有列的名称和数据类型 

 MODIFY 修改现有列的数据类型或位置

06-更改

ALTER TABLE d_l MODIFY COLUMN doughnut_type VARCHAR(100)

07-更改

ALTER TABLE d_l DROP COLUMN doughnut_cost;

08-更改

UPDATE TABLE SET new_column =

        CASE 

            WHEN column1 =somevalue1

                THEN newvalue1

            WHEN colunm2 =somevalue2

                THEN newvalue2

             ELSE newvalue3

        END;

 

添加索引

ALTER TABLE my_contacts ADD INDEX(last_name);

 

设置密码

 

SET PASSWORD FOR 'root@localhost' =PASSWORD('b4dc10wnz')

 

CREATE USER xx IDENTIFIED BY 'XXXXXX' 

X 内容：账户和密码

限定用户操作

GRANT SELECT ON XX TO XX; -- X 内容：表名和账号

使用GRANT 语句可以控制用户对表和列可执行的操作

权限再分配

WITH GRANT OPTION  让用户把获得的授权授予其它用户

 

撤销授权

REMOVE SELECT ON xx FROM xx; 

具精确度的撤销授权操作：可以使用关键字CASCADE 和RESTRICT来更精确的锁定目标用户。

  •  CASCADE 表示授权的操作具有连锁反应。
  •  RESTRICT 可接收到错误信息。

全局授权

GRANT SELECT ON *.* TOxx;

 

角色相关

 

 

 

 

CREATE ROLE data_entry;

角色是把特定权限汇集成组，再把组权限授予一群人的方式。

GRANT SELECT,INSERT ON some_table TO data_entry; 

 

GRANT data_entry TO dic WITH ADMIN OPTION; --  WITH DAMIN OPTION 允许用户doc 把角色 data_entry 授予其它人

 

DROP ROLE data_entry;

 

REVOKE data_entry FROM doc CASCADE; -- 撤销角色（REVOKE）

 

Table2--mysql 常见概念辨析

存放的数据量取决于数据的使用方式，使用数据的方式将影响表的方式。

概念

说明

创建表时可以遵循的步骤

  • 挑出事物，挑出你希望表描述的某样事物；
  • 列出一份关于那样事物的信息列表，这些信息都是使用表的必要信息；
  • 使用信息列表，把关于那项事物的综合信息拆分成小信息块，以便于组织。

原子性

当数据具有原子性，就表示它已经被分割至最小块，已经不能或不应该再被分割。规范化表的第一步，让数据具有原子性。

原子性数据的正式规则

  • 具有原子性数据的列中不会有多个类型相同的值。
  • 具有原子性数据的表中不会有多个存储同类数据的列。

规范化表的优点

  • 没有重复数据，可以减小数据库的大小；
  • 查询的数据较少，你的查询会更为快速。

1NF（第一范式）

  • 每个数据行必须包含具有原子性的值；
  • 每个数据行必须有独一无二的识别项，称主键。

主键

  • 主键用于独一无二地识别出每一条记录。
  • 主键不可以为NULL；插入新数据时必须要指定主键值；主键必须简洁；主键值不可以    被修改。

1NF

  • 规则一：数据列只包含具有原子性的值；
  • 规则二：没有重复的数据组；

组合主键

  • 由两列以上组成的主键叫组合主键。组合主键就是由多个数据列构成的主键，组合各列后形成具有唯一性的键。
  • 单独的某一列并不能作为主键，当多列组合时，确保了唯一性，可以作为组合主键。

速记符号：T.x -> T.y，

关系表T中，y列函数依赖于x列

部分函数依赖

非主键的列依赖组合主键的某一部分。

传递依赖

IF 改变任何非键列可能造成其它列的改变，即为传递依赖。

传递函数依赖

任何非键列与另一个非键列有关联。

2NF

  • 规则一：先符合1NF
  • 规则二：没有部分函数依赖性

任何具有人工主键且没有组合主键的表都符合2NF 

3NF

  • 规则一：符合2NF；
  • 规则二：没有传递函数依赖性；

索引

主键和外键属于索引；考虑创建索引的对象必须在常用的列上。

模式（schema）

用于表达数据库内的结构，包括表和列，还有各种它们之间相互连接的方式。

外键

外键是表中的某一列，它引用到另一个表的主键。

  • 外键可能与它引用的主键不同。
  •  外键使用的主键成为父键；主键所在的表成为父表。
  •  外键可以为NULL，表示父表中没有相符的主键；外键的值不需要唯一
  •  外键不一定必须是父表的主键，但必须唯一

自引用外键

引用同一张表内另一列的键

约束（constraint）

创建在结构内的键被称为约束（constraint）(约束可以确保只能在子表中插入已经存在父表中的值)

  • 除了主键约束，其它常见的几种约束有：UNIQUE，以及一种MySQL 不支持的约束：检查约束

引用完整性

插入外键列中的值必须已经存在父表的来源列中。例如：

CREATE TABLE interests

    (

        int _id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

        interset VARCHAR(50) NOT NULL,

        contact_id INT NOT NULL,

        CONSTRAINT my_contacts_contact_id   -- 告诉我们键的来源表（my_contacts），键的名称（contact_id），还能说明它是一个外键

        FOREIGN KEY(contact_id) 

        REFERENCES my_contacts(contact_id)

    )

 

常见的数据模式

一对一

A表的一行最多只能与B表的某行关联，把一对一的数据留在主表更合适；

 

mysql每个类型只取一条 mysql只查一条_mysql

一对多

A表中的某一条数据，对应B表中的多行；B表中的某一条记录只能对应A表中的某一条记录

多对多

多对多的示例采用的woman和shoe的关系表述的，在处理两个表的时候，引出了junction table（连接表）。

列的别名（correlation name，相关名称）

别名的设定方式，在查询中首次出现表名的后面接AS（AS 可以省略） 并设定别名

交叉连接（笛卡尔积）

 

SELECT t.toy, b.boy FROM toys AS t CROSS JOIN boys AS b; 

 SELECT toys.toy,boys.boy FROM toys,boys; 上述语句的同意用法

 INNER JOIN 

SELECT somecolumns FROM table1 INNER JOIN table2 ON somecondition;

 INNER JOIN 利用条件式里的比较运算符结合两张表；其中ON 可以替换成WHEN 

 

 相等连接、不等连接是INNER JOIN 后的比较运算为=和<>

 自然连接（不使用“ON”子句的内连接）

 

SELECT boys.boy,toys.toy FROM boys NATURAL JOIN toys;

只有在连接的列在两张表中的名称都会相同时才会用到；NATURAL JOIN 利用相同列名的内联接

LEFT OUTER JOIN

会匹配左表中的每一行的及右表中符合条件行。

RIGHT OUTER JOIN 

 

外连接和内连接的区别

外联接一定会提供数据行，无论该行能否在另一张表中找出匹配的行。在外联接的结果中NULL 表示右表中没有找到与左表相符的记录。

自联接

自联接能把单一的表当成两张具有完全相同的信息的表来进行查询

UNION，联合

可以把两张或多张表的查询结果合并至一个表中。UNION 只能接受一个ORDER BY 且必须位于语句的末端

UNION ALL

返回列的所有内容，而不是每个值的复制实例。

INTERSECT（交集） 和 EXCEPT（差集，返回出现在第一个查询，而不再第二个查询中的列）

 

子查询

 

当单一查询不够用的时候，请使用子查询。外部的查询称为包含查询或外层查询；内部的查询就是子查询。

作为欲选取列的子查询。

  • 如果子查询可以独立运行且不会引起外层查询的任何结果，即称为非关联子查询。对应的一个概念：关联子查询
  • 子查询可以搭配 IN，NOT IN，EXISTS，NOT EXISTS

检查约束（CHECK）

CREATE TABLE piggy_bank

    (

        id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,

        coin CHAR(1) CHECK (coin in ('P','N','D','Q'))

    )

创建视图（虚拟表）

 

 

视图会像表一样出现在数据库中

CREATE VIEW web_designers AS SELECT xx

SELECT * FROMeb_designers; -- 查看创建的视图

视图对数据库的好处

  • 把复杂查询简化成一个命令
  • 即使一直改变数据库，也不会破坏依赖表的应用程序；
  • 可以隐藏读者无需看到的信息

带有 CHECK OPTION 的视图

 

CREATE VIE wW pb_dimes AS SELECT * FROM piggy_bank WHERE coin ='D' WITH CHECK OPTION;

CHECK OPTION 检查每个进行 INSERT 或 DELETE 的查询，它根据视图中的WHERE 子句来判断这些查询是否可执行，IF 不符合WHERE 条件，插入操作被拒绝 

可更新视图

 

可更新视图包括引用表里所有为NOT NULL 的列

DROP VIEW pb_dimes;

CHECK CONSTRAINT

当数据库的使用者不只一人时，CHECK CONSTRAINT 与视图均有助于维护控制权。

事务

 

事务是一群可以完成的一组工作的SQL语句。

在事务过程中，如果所有的步骤无法不受干扰的完成，则不该完成任何单一步骤。

构成一个事务的原则（ACID）

  • 原子性。事务里的每一个步骤都必须完成，否则只能都玩不成。
  • 一致性。事务完成后应该维护数据库的一致性。
  • 隔离性。每次事务都会看到具有一致性的数据库，无论其它事务有什么行动；
  • 持久性。事务完成后，数据库需要正确地存储数据并保护数据免受断电或其他威胁的伤害。

START TRANSACTION;

 

持续追踪后续所有SQL语句，直到你输入COMMIT 或 ROLLBACK

 ROLLBACK 回滚，回到事务开始前的状态

引擎

 

存储引擎必须是BDB或InnoDB两种支持事务的引擎之一

ALTER TABLE your_table TYPE =InnoDB;

附录：

SQL 的排序规则： 

  • 非字母字符出现在数字前面或后面
  • 数字出现在字母的前面
  • NULL 出现在数字和字母的前面
  • 大写字母出现在小写字母的前面
  • “A 1”出现在“A1”的前面

数据类型说明

  • CHAR 或 CHARACTER 定义的数据必须是事先设定好长度的；查询时通常使用单引号。
  • DEC, DECIMAL 缩写，提供数值空间，直到满为止。DEC(6,2)，浮点格式，前者代表总位数，后者是小数点后的位数；在查询时候不能使用引号；查询时千万不能用引号。
  • 在不同的RDBMS 中，她的名字可能是 DATATIME ，也可能是 TIMESTAMP ，负责记录日期和时间，她有个双胞胎姐妹 TIME，但TIME 不喜欢插手管DATA 的事情；查询时必用引号。
  •  VARCHAR 存储文本数据，最大长度可达255个字符，可以配合我们的数据长度进行调整；查询时必须用引号。
  • JNT 或 JNTEGER 认为数字应该是整数，也可以处理负数；查询时不能用引号。
  •  BLOB ，大量文本数据。区别 CHAR 或 VARCHAR ，有些重要的字符串运算无法操作 BLOB 类型的数据；查询时必须用引号。
  • DATA 记录日期，必用引号。
  • 单引号作为文本的一部分需要加入转义字符'\'，或是在单引号前面加上另一个单引号。

mysql每个类型只取一条 mysql只查一条_主键_02