MySQL精简复习

1、常用关键字增删查改：INSERT、DELECT、SELECT、UPDATE 以及表创建 CREATE
在使用DELETE时，不要省略WHERE子句，否则就会错误地删除表中所有行
INSERT INTO 表名（各个字段名称用逗号隔开）VALUE （各个字段名称插入值用逗号隔开）;
INSERT INTO 表名（各个字段名称用逗号隔开）SELECT 各个字段名称 FROM 另一个表;
DELETE FROM 表名 WHERE 过滤条件;
UPDATE 表名 SET 修改列名=‘更新值’ WHERE 过滤条件;
更新表：对已存在表插入新的字段或删除已有字段

//插入完整行
INSERT INTO emp (id,name,age,dep_id) VALUES('9','周杰伦','44','4'),('10','林俊杰','34','3');
//插入检索数据：从另一个表中合并员工列表
INSERT INTO emp (id,name,age,dep_id) SELECT id,name,age,dep_id FROM empnew;
//删除特定行
DELETE FROM emp WHERE id='9';
//修改数据
UPDATE emp SET age='43',dep_id='1' WHERE name='周杰伦';
//创建表
CREATE TABLE students IF NOT EXISTS(
id int NOT NULL AUTO_INCREMENT,
stu_name char(50) NOT NULL,
stu_address char(50) NULL,
stu_email char(50) NULL,
PRIMARY KEY (id)
)ENGINE=InnoDB;
//删除表
DROP TABLE student；
//重命名表
RENAME TABLE stu TO student;

//学生表插入新字段 ALTER TABLE ... ADD ...
ALTER TABLE students ADD parents_name CHAR(50);
//学生表删除已有字段 ALTER TABLE ... DROP ...
ALTER TABLE students DROP parents_name;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

PRIMARY KEY 用于指定主键
AUTO_INCREMENT 告诉MySQL每增加一行时自动增量。每个表中只允许一个AUTO_INCREMENT 列，且它必须被索引（如：使它成为主键）
NOT NULL的作用表示在插入值时，需要对列进行赋值，阻止插入没有值的列。

2、DISTINCT关键字返回不同值

SELECT DISTINCT dep_id FROM emp;
1

3、LIMIT关键字限制输出

//输出前三条数据
SELECT name FROM emp LIMIT 3;
//输出从第三行开始的两条数据
SELECT name FROM emp LIMIT 3,2;
1
2
3
4

4、完全限定表名：表名.字段 或 库名.表名----》用于规定使用的字段和表来源

emp.name
1

5、ORDER BY关键字：用于排序检索数据；在排序字段后面添加DESC或ASC来规定降序或升序

// 根据姓名排序，在名字为中文时使用CONVERT(name USING gbk)将字段改为GBK编码用于汉字排序，降序
SELECT name FROM emp ORDER BY CONVERT(name USING gbk) DESC;
//根据多个条件排序,升序
SELECT name,age,dep_id FROM emp ORDER BY dep_id,age ASC;
1
2
3
4

6、WHERE关键字：进行条件筛选，可以与AND（同时满足）操作符合OR（满足其中之一）操作符结合使用达到多条件筛选

SELECT name,age FROM emp WHERE age<30;
SELECT name,age,dep_id FROM emp WHERE dep_id=1 AND age<=30;
SELECT name,age,dep_id FROM emp WHERE dep_id=1 OR age<=30;
1
2
3

7、IN操作符：IN操作符用来指定条件范围，对范围中的每个条件都可以进行匹配。IN的功能与OR相当，但执行一般比OR更快，IN的最大优点是可以包含其他SELECT语句，能够更动态的建立WHERE子句。NOT操作符用于否定它后面所跟的条件

//查询dep_id为1或2的数据，并将结果根据dep_id和age排序
SELECT name,age,dep_id FROM emp WHERE dep_id IN (1,2) ORDER BY dep_id,age;
SELECT name,age,dep_id FROM emp WHERE dep_id NOT IN (1,2) ORDER BY dep_id,age;
1
2
3

8、LIKE操作符：使用通配符与LIKE关键字对数据进行过滤。通配符包含两种：1、百分号 %：%表示任何字符出现任何次数，百分号也可以匹配0个字符；2、下划线 _ ：_ 表示单个的任何字符。通配符有个缺点是花费时间比其它搜索方式更长

//查找所有张姓人员
SELECT name,age,dep_id FROM emp WHERE name LIKE '张%';
1
2

9、REGEXP关键字：该关键字后面跟正则表达式
1）正则表达式中“.”表示任意一个字符
2）正则表达式中OR可以用“|”表示
3）正则表达式中[ ]是另一种形式的OR，如[23]4表示23或24等价于23|24
4）正则表达式表示匹配范围，如2[3-9]表示匹配数据为23-29的所有数
5）特殊字符匹配，如“.”的匹配表达应该为’\.’
6）字符类和重复元字符的正则匹配
7）包含定位符的正则匹配

//查找年龄为x4的相关信息
SELECT name,age,dep_id FROM emp WHERE age REGEXP '.4';
//查找符合其中之一条件（年龄为23或24）的字段信息
SELECT name,age,dep_id FROM emp WHERE age REGEXP '22|24';
SELECT name,age,dep_id FROM emp WHERE age REGEXP '[23]4';
//查找年龄为23-29的员工信息
SELECT name,age,dep_id FROM emp WHERE age REGEXP '2[3-9]' ORDER BY age;
SELECT name,age,dep_id FROM emp WHERE age REGEXP '\\.' ORDER BY age;
//正则表达式：\\([0-9] sticks?\\)---》匹配例子(3 stick)、(3 sticks)、(5 stick)等
SELECT name,age,dep_id FROM emp WHERE dep_id REGEXP '\\([0-9] sticks?\\)' ORDER BY dep_id;
//以0-9或'.'开始的所有数据信息
SELECT name,age,dep_id FROM emp WHERE dep_id REGEXP '^[0-9//.]' ORDER BY dep_id;
1
2
3
4
5
6
7
8
9
10
11
12

10、字段拼接，Concat()函数，参数使用列名或字符串，各字符串之间使用逗号隔开
AS关键字用于对字段起别名。

//拼接名字价格--->如：苹果(5元)
SELECT Concat(name,'(',price,'元',')') FROM t_fruit
ORDER BY CONVERT(fname USING gbk),price;
//起别名
SELECT fname,price,fcount,price*fcount AS totalprice FROM t_fruit 
ORDER BY CONVERT(fname USING gbk),price;
1
2
3
4
5
6

11、文本处理函数
RTrim()函数：去掉串右边的空格
LTrim()函数：去掉串左边的空格
Upper()函数：将文本转化为大写
Lower()函数：将串转化为小写
Length()函数：返回串的长度
Locate()函数：找出串的一个子串
Soundex()函数：返回发音类似的值
SubString()函数：返回子串的字符

SELECT username,Upper(username) as name_upcase from tb_user 
ORDER BY id;
1
2

12、日期处理函数：MySQL使用的日期格式，不管是插入或者更新表值还是使用WHERE进行过滤，日期的格式必须为：yyyy-mm-dd hh:mm:ss

SELECT id,num FROM orders WHERE DATA(order_data)='2005-09-01';
1

13、数值处理函数：

14、聚集函数
1）求均值AVG()函数，输入参数为字段名称
2）统计函数COUNT()：统计表中行的数目或符合特定条件行的数目，对特定列进行统计时会忽略null值
3）MAX()函数：寻找最大值，列名作为参数给出
4）MIN()函数：寻找最小值，列名作为参数给出
5）SUM()函数：返回指定列的值的和，列名作为参数给出
6）这些聚集函数可以结合DISTINCT关键字一起使用，实现对不同值的操作。

//计算产品的平均价格
SELECT AVG(price) AS avg_price FROM products;
//统计有邮箱用户的数量
SELECT COUNT(email) AS num_cust FROM customers；
//产品中最贵的价格
SELECT MAX(price) AS max_price FROM products;
//产品中最便宜的价格
SELECT MIN(price) AS min_price FROM products;
//对价格不同的产品计算平均值
SELECT AVG(DISTINCT price) AS avg_price FROM products;
1
2
3
4
5
6
7
8
9
10

15、GROUP BY子句、HAVING关键字
GROUP BY 子句根据给定数据列对每个成员查询结果进行分组统计，GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之间
HAVING实现过滤分组（WHERE只能按条件进行过滤）

//根据部门id对查询结果进行统计
SELECT dep_id,COUNT(*) AS numbers FROM emp GROUP BY dep_id;
//根据部门id对查询结果进行统计，同时筛选出部门人数大于2的信息
SELECT dep_id,COUNT(*) AS numbers FROM emp GROUP BY dep_id HAVING COUNT(*)>2;
1
2
3
4

16、子查询：嵌套在其他查询中的查询成为子查询，子查询总是由内向外进行处理。

SELECT name FROM emp WHERE dep_id IN(SELECT dep_id FROM emp WHERE age>30);

//子句用于统计每个客户的订单数，最后输出查询cust_name cust_state，orders的结果并根据用户名排序
SELECT cust_name cust_state 
(SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers ORDER BY cust_name;
1
2
3
4
5
6

17、表联结：SQL 最强大的功能之一就是能在数据检索查询的执行中联结表。关系表的设计就是要保证把信息分成多个表，一类数据一个表。
笛卡尔积：若对两个表进行联结而不设立联结条件的表关系返回结果为笛卡尔积，即检索出的行数是第一个表中的行数乘第二个表的行数。因此联结条件很重要

//对emp,dept表进行表联结查询，并根据部门和年龄排序
SELECT name,age,dep_name,addr FROM emp,dept 
WHERE emp.dep_id=dept.id ORDER BY dep_id,age;
//输出结果条数为笛卡尔积
SELECT name,age,dep_name,addr FROM emp,dept ORDER BY dep_id,age;
1
2
3
4
5

18、内部联结两种格式：
1）select 字段列表 from 表1,表2… where 联结条件;
2）select 字段列表 from 表1 [inner] join 表2 on 条件;;

//使用where进行内部联结查询
SELECT name,age,dep_name,addr FROM emp,dept 
WHERE emp.dep_id=dept.id ORDER BY dep_id,age;
//使用INNER JOIN ... ON进行内部联结查询
SELECT name,age,dep_name,addr FROM
emp INNER JOIN dept ON emp.dep_id=dept.id;
//多表联结查询
SELECT emp.name,age,dep_name,addr,money 
FROM emp,dept,account 
WHERE emp.dep_id=dept.id AND emp.name=account.name;
1
2
3
4
5
6
7
8
9
10

19、自联结：假设某物品（ID为DTNTR）存在问题，因此想知道生产该物品的供应厂商生产的其他物品是否有问题。操作步骤：先通过物品ID找到供应商；在通过供应商找到其生产的所有物品
1）select 字段列表 from 表1,表2… where 联结条件;
2）select 字段列表 from 表1 [inner] join 表2 on 条件;;

//使用子查询方式
SELECT prod_id,prod_name FROM products 
WHERE vend_id=(SELECT vend_id FROM products WHERE prod_id='DTNTR')
//使用自联结
SELECT p1.prod_id,p1.prod_name FROM products AS p1,products AS p2
WHERE p1.vend_id = p2.vend_id 
AND p2.vend_id='DTNTR';
1
2
3
4
5
6
7

20、外部联结：左外部联结和右外部联结
1）select 字段列表 from 表1 left [outer] join 表2 on 条件;
2）select 字段列表 from 表1 right [outer] join 表2 on 条件;
外部联结与内部联结的区别：
内连接查询操作列出与连接条件匹配的数据行；外连接，返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表（左外连接）、右表（右外连接）或两个边接表（全外连接）中的所有数据行。（RIGHT指出的是OUTER JOIN右边的表，选择右边表的所有行）（LEFT指出的是OUTER JOIN左边的表，选择左边表的所有行）

//左外部联结
SELECT name,age,dep_name,addr FROM emp LEFT OUTER JOIN dept ON emp.dep_id=dept.id;
//右外部联结
SELECT name,age,dep_name,addr FROM emp RIGHT OUTER JOIN dept ON emp.dep_id=dept.id;
1
2
3
4

21、UNION关键字：用于组合多条SELECT执行语句
SELECT语句输出用ORDER BY进行排序，在使用UNION组合查询时，只能使用一条ORDER BY子句，且必须在最后一条SELECT语句之后。

//输出年小于27 或部门id为1和2的员工合集
SELECT name,age FROM emp WHERE age<27 UNION 
SELECT name,age FROM emp WHERE dep_id IN (1,2);
//等价于
SELECT name,age FROM emp WHERE age<27 OR dep_id IN (1,2);
//UNION 与 ORDER BY组合
SELECT name,age FROM emp WHERE age<27 UNION SELECT name,age 
FROM emp WHERE dep_id IN (1,2) ORDER BY age;
1
2
3
4
5
6
7
8

21、主键与外键
主键的两种添加方式
方式1：建表时添加唯一约束
create table 表名(
列名 数据类型 primary key auto_increment,
…
);
方式2：建完表后添唯一约束
alter table 表名 add primary key 字段名;
删除约束：
alter table 表名 drop primary key;
主键使用单个列，则该列中的每一个值都必须是唯一的；主键使用多个列，则这些列的组合值必须是唯一的。

外键添加方式：
alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称 (主表列名称);

22、指定默认值
MySQL允许使用DEFAULT关键字指定默认值，默认在CREATE TABLE 语句的列定义中使用DEFAULT。在开发时大多使用默认值而非NULL。与大多数的DBMS不一样，MySQL不允许使用函数作为默认值。

CREATE TABLE students IF NOT EXISTS(
id int NOT NULL AUTO_INCREMENT,
stu_name char(50) NOT NULL,
stu_address char(50) NULL,
stu_email char(50) NULL,
stu_inschool int NOT NULL DEFAULT 1,
PRIMARY KEY (id)
)ENGINE=InnoDB;
1
2
3
4
5
6
7
8

23、引擎类型
和其他数据库管理系统（DBMS）一样，MySQL有一个具体管理和处理数据的内部引擎。与其他DBMS不同的是，MySQL有多种引擎，全部能执行 CREATE TABLE、SELECT等命令。在MySQL中针对不同任务选择正确的引擎能够获得良好的功能和灵活性。
MySQL通过ENGINE来选择对应的引擎，当该关键字省略时，则使用默认引擎（很可能是MyISAM）
几种重要引擎：