Table1--mysql常用操作
| 主题 | 用例 | 说明 |
| 书写规范 |
|
|
| 数据库授权 | grant all privileges on hive_metadata.* to 'root'@'%' identified by '123456'; | grant select on 数据库.* to 用户名@登录主机 identified by '密码' |
| 创建和使用数据库 |
|
|
| 创建表 | 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 |
| 检查刚刚创建的表； 查看表的构成； 查看创建表的代码 |
|
|
| 删除表 | 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来更精确的锁定目标用户。
|
| 全局授权 | 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（第一范式） |
|
| 主键 |
|
| 1NF |
|
| 组合主键 |
|
| 速记符号：T.x -> T.y， | 关系表T中，y列函数依赖于x列 |
| 部分函数依赖 | 非主键的列依赖组合主键的某一部分。 |
| 传递依赖 | IF 改变任何非键列可能造成其它列的改变，即为传递依赖。 |
| 传递函数依赖 | 任何非键列与另一个非键列有关联。 |
| 2NF |
任何具有人工主键且没有组合主键的表都符合2NF |
| 3NF |
|
| 索引 | 主键和外键属于索引；考虑创建索引的对象必须在常用的列上。 |
| 模式（schema） | 用于表达数据库内的结构，包括表和列，还有各种它们之间相互连接的方式。 |
| 外键 | 外键是表中的某一列，它引用到另一个表的主键。
|
| 自引用外键 | 引用同一张表内另一列的键 |
| 约束（constraint） | 创建在结构内的键被称为约束（constraint）(约束可以确保只能在子表中插入已经存在父表中的值)
|
| 引用完整性 | 插入外键列中的值必须已经存在父表的来源列中。例如： 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表的某行关联，把一对一的数据留在主表更合适；
| |
| 一对多 | |
| 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（差集，返回出现在第一个查询，而不再第二个查询中的列） |
|
| 子查询
| 当单一查询不够用的时候，请使用子查询。外部的查询称为包含查询或外层查询；内部的查询就是子查询。 |
| 作为欲选取列的子查询。
| |
| 检查约束（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 记录日期，必用引号。
- 单引号作为文本的一部分需要加入转义字符'\'，或是在单引号前面加上另一个单引号。
355
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
