一、DML操作

数据操纵语言（DML）DML用于插入、修改、删除数据记录，包括如下SQL语句：

INSERT：添加数据到数据库中

UPDATE：修改数据库中的数据

DELETE：删除数据库中的数据

1.1插入数据

1.1.1语法

1.1.2 说明

1、值列表(值1，值2，......)的顺序、个数与字段列表(字段1，字段2，......) 中字段的顺序、个数一致

（1）如果个数少了就报Column count doesn’t match value count

（2）如果VALUES前面的()中没有列出字段，那么默认就是为表中的所有字段赋值，那么个数与顺序与表结构中字段定义的一致

2、关于自增长列、默认值列、允许为NULL列的赋值

（1）如果字段列表列出了字段名，那么值列表中就要为其赋值，哪怕它是自增长列，有默认值列，可以为NULL值的列。

• InnoDB表的自动增长列可以手动插入合适的值，但是插入的值如果是NULL或者0，则实际插入的将是自动增长后的值；

• 如果列声明了“默认约束”那么对应的位置可以赋值具体的值，也可以使用“DEFAULT”，表示使用默认值；

• 如果列允许了NULL值，那么可以为对应的字段可以赋值为具体值也可以赋值为NULL

（2）对于没有列出的字段，像自增列就自动赋值，像默认值列就自动赋默认值，像允许NULL的列就自动赋NULL值，但是非空列又没有提供默认值会自动赋值为对应数据类型的默认值，例如字符串赋值为空字符串，int赋值为0；

3、VALUES也可以写成VALUE，但是VALUES是标准写法

4、可以同时插入多行

5、如果插入从表的数据，要注意查看主表参照字段的值是否存在

6、值的位置可以是常量值、表达式、函数

1.2 修改数据

1.21语法

1.2.2 说明

1、如果不写where条件，会修改所有行

2、值可以是常量值、表达式、函数

3、可以同时更新多张表

如果两个表没有建立外键，但逻辑上有外键关系

4、如果修改从表外键字段的数据，要注意查看主表参照字段的值是否存在

5、如果修改主表的被参考的字段的值，要注意查看从表的外键是否有依赖该值，如果有

（1）如果外键是on update RESTRICT或on update NO ACTION，那么要先处理从表的数据，才能修改

（2）如果外键是on update SET NULL 或 on update CASCADE，那么直接修改，从表的外键字段会自动处理

1.3 删除数据

1.31语法

1.3.2 说明

1、如果不加where条件，表示删除整张表的数据，表结构保留。

delete from 表名;

删除整张表的数据还可以使用truncate 表名;

区别：

truncate相当于删除表再重建一张同名结构的表，操作后得到一张全新表，而delete是在原有表中删除数据。如果决定清空一张表的数据，truncate速度更快一些。

TRUNCATE语句不能回滚

2、如果删除主表的记录，要注意查看从表的外键是否有依赖该行的值，如果有

（1）如果外键是on delete RESTRICT或on delete NO ACTION，那么要先处理从表的数据，才能删除

（2）如果外键是on delete SET NULL 或 on delete CASCADE，那么删除时从表的对应记录也会被置空或跟着删除

3、可以一次删除多个表的数据

例如：两个表没有建立外键，但逻辑上有外键关系，也可以通过删除多个表的数据来实现级联删除1.

1.4查询

1.4.1语法

SELECT 查询列表
  FROM 表名或视图列表
  【WHERE 条件表达式】
  【GROUP BY 字段名 【HAVING 条件表达式】】
  【ORDER BY 字段 【ASC|DESC】】
  【LIMIT m,n】;

说明：

（1）如果SELECT后面是*，那么表示查询所有字段

（2）SELECT后面的查询列表，可以是表中的字段，常量值，表达式，函数

（3）查询的结果是一个虚拟的表

select语句，可以包含5种子句：依次是where、 group by、having、 order by、limit必须照这个顺序。

1.4.2别名AS

语法：AS 别名

说明：

（1）可以给字段取别名、可以给表名取别名

（2）AS 可以省略

（3）如果给字段取别名，如果别名中包含特殊符号，例如“空格”等，建议给别名加上双引号或单引号

（4）如果是给表名取别名，那么不能加双引号或单引号，也不能有特殊符号，例如“空格”等

（5）建议别名简短，见名知意

1.4.3 去重DISTINCT

#查询员工表的部门编号
SELECT DISTINCT dept_id FROM t_employee;

#统计员工表中员工有几个部门
SELECT COUNT(DISTINCT dept_id) FROM t_employee;  

1.4.4 着重号

例如：select name from t_stu;

可以给字段或表名加着重号

如果字段名或表名与关键字一样更要加着重号了

二、MySQL的运算符

（1）算术运算符：+ - * /（除也可以写成div，div取整) %（取模可以写成mod）

（2）比较运算符：= > >= < <= !=（不等于还可以写成<>） <=>（安全等于）

（3）逻辑运算符：&&（逻辑与也可以写成and） ||（逻辑或也可以写成or） not（逻辑非） xor（逻辑异或）

（4）范围：表达式 between ... and ... （也可以写成 表达式>=... and 表达式 <=...）

? 表达式 not between ... and ...（也可以写成 表达式<... || 表达式 >...）

（5）集合：in (值，值，值...) not in(值，值，值...)

（6）模糊查询：LIKE NOT LIKE，通配符：%表示0-n个字符，_下划线代表一个字符

（7）位运算符：&（按位与） |（按位或）^（按位异或）~（按位取反）>>（右移）<<（左移）

（8）NULL值判断，is null 或 is not null，如果使用null=null，null<>null,null=0,null<>0,null=false等都不对

不过xxx is null 可以使用xxx <=> null ，xxx is not null 可以写成 not xxx <=> null

结论：所有的运算符遇到NULL结果都是NULL，除了<=>

2.1 算术运算符

#+,-,*,/(div),%(mod)
#筛选出eid是偶数的员工
SELECT * FROM t_employee WHERE eid % 2 = 0; 
SELECT * FROM t_employee WHERE eid MOD 2 = 0; 

#查看每天的基本工资值，每个月按22天算
SELECT eid,basic_salary/12 AS "日薪"
FROM t_salary;

#div也表示除，但是只保留整数部分
SELECT eid,basic_salary DIV 12 AS "日薪"
FROM t_salary;

#关于+，在Java中，+的左右两边如果有字符串，那么表示字符串的拼接,但是在MySQL中+只表示数值相加，
#如果遇到非数值类型，先尝试转成数值，如果转失败，就按0计算
SELECT eid+ename FROM t_employee;
SELECT eid+birthday FROM t_employee;

#MySQL中字符串拼接要使用字符串函数实现
SELECT CONCAT(eid,":",ename) AS result FROM t_employee;

2.2 比较运算符

#=，>, <，>=, <=， !=(不等于<>)，<=>（安全等于）
#查询basic_salary!=10000
SELECT eid,basic_salary FROM t_salary WHERE basic_salary != 10000;
SELECT eid,basic_salary FROM t_salary WHERE basic_salary <> 10000;

#查询basic_salary=10000，注意在Java中比较是==
SELECT eid,basic_salary FROM t_salary WHERE basic_salary = 10000;

#查询commission_pct等于0.40
SELECT eid,commission_pct FROM t_salary WHERE commission_pct = 0.40;
SELECT eid,commission_pct FROM t_salary WHERE commission_pct <=> 0.40;

#查询commission_pct等于NULL
SELECT eid,commission_pct FROM t_salary WHERE commission_pct IS NULL;
SELECT eid,commission_pct FROM t_salary WHERE commission_pct <=> NULL;

#查询commission_pct不等于NULL
SELECT eid,commission_pct FROM t_salary WHERE commission_pct IS NOT NULL;
SELECT eid,commission_pct FROM t_salary WHERE NOT commission_pct <=> NULL;

2.3 逻辑运算符

#与&&，或||，非!
#与 AND，或 OR ，非 NOT，异或 XOR

#查询性别男，并且在90以前出生的员工
SELECT * FROM t_employee WHERE gender=‘男‘ AND birthday<‘1990-01-01‘; 

#查询职位编号job_id是1或2的员工
SELECT * FROM t_employee WHERE job_id =1 OR job_id = 2;

#查询基本薪资是在9000-12000之间的员工编号和基本薪资
SELECT eid,basic_salary FROM t_salary WHERE basic_salary >=9000 AND basic_salary<=12000;

#查询基本薪资不在9000-12000之间的员工编号和基本薪资
SELECT eid,basic_salary FROM t_salary WHERE NOT (basic_salary >=9000 AND basic_salary<=12000);
SELECT eid,basic_salary FROM t_salary WHERE basic_salary <9000 OR basic_salary>12000;

2.4 范围和集合

#between ... and ... 和 not between ... and ... 
#in(集合)  和 not in(...)

#查询基本薪资是在9000-12000之间的员工编号和基本薪资
SELECT eid,basic_salary FROM t_salary WHERE basic_salary BETWEEN 9000 AND 12000;

#查询eid是1,3,5的基本工资
SELECT eid,basic_salary FROM t_salary WHERE eid IN (1,3,5);

2.5 模糊查询

#like 和 通配符 一起使用
#like _ 匹配单个字符
#like % 匹配任意个字符

#查询名字中有‘冰‘字的员工信息
SELECT * FROM t_employee WHERE ename LIKE ‘%冰%‘;

#查询姓李的员工信息
SELECT * FROM t_employee WHERE ename LIKE ‘李%‘;

#查询姓李，名字就一个字的员工信息
SELECT * FROM t_employee WHERE ename LIKE ‘李_‘;

#查询李冰冰的信息
SELECT * FROM t_employee WHERE ename LIKE ‘李冰冰‘;

通常情况，可以使用FIND_IN_SET()函数或LIKE操作符搜索SET值：

? mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET(‘value‘,set_col)>0;

? mysql> SELECT * FROM tbl_name WHERE set_col LIKE ‘%value%‘;

? 第1个语句找出SET_col包含value set成员的行。第2个类似，但有所不同：它在其它地方找出set_col包含value的行，甚至是在另一个SET成员的子字符串中。

? 下面的语句也是合法的：

? mysql> SELECT * FROM tbl_name WHERE set_col & 1;

? mysql> SELECT * FROM tbl_name WHERE set_col = ‘val1,val2‘;

? 第1个语句寻找包含第1个set成员的值。第2个语句寻找一个确切匹配的值。应注意第2类的比较。将set值与‘val1,val2‘比较返回的结果与同‘val2,val1‘比较返回的结果不同。指定值时的顺序应与在列定义中所列的顺序相同。

如果想要为SET列确定所有可能的值，使用SHOW COLUMNS FROM tbl_name LIKE set_col并解析输出中第2列的SET定义。

有什么实际应用呢？

? 比如我们设定用户的权限控制，一个用户可能会有多种权限，我们使用所有权限创建一个SET类型的字段，我们不需要用一系列int来定义各种权限了，直接使用一个SET字段即可：

/*
    用户权限permission表
    */
    create table user_permission(
    id int UNSIGNED not null auto_increment,
    user_id int not null ,
    permission set(‘阅读‘,‘评论‘,‘发帖‘) not null,
    primary key(id),
    unique (user_id)
    );
    desc user_permission;
    insert into user_permission values (0,1,‘阅读‘),(0,2,‘阅读‘),(0,3,‘阅读,评论‘);
    insert into user_permission values (0,4,‘阅读,评论,发帖‘);
    select *,permission+0 from user_permission;
    select permission from user_permission where user_id=1;
    select * from user_permission where permission & 10;
    SELECT * FROM user_permission WHERE FIND_IN_SET(‘评论‘,permission)>0;

2.6 NULL值判断与计算处理

#NULL值判断与处理
#查询奖金百分比不为空的员工编号
SELECT eid,commission_pct FROM t_salary WHERE commission_pct IS NOT NULL;

#查询奖金百分比为空的员工编号
SELECT eid,commission_pct FROM t_salary WHERE commission_pct IS NULL;

#关于null值计算
#所有运算符遇到null都是null

#计算实际的薪资：  basic_salary + salary * 奖金百分比
#函数：IFNULL(表达式,用什么值代替)
SELECT eid,basic_salary + performance_salary *(1+ commission_pct) FROM t_salary;#错误的
SELECT eid,basic_salary + performance_salary *(1+ IFNULL(commission_pct,0)) FROM t_salary;

#<=>安全等于
#查询奖金百分比为空的员工编号
SELECT eid,commission_pct FROM t_salary WHERE commission_pct <=> NULL;

三、关联查询，联合查询

作用:从2张或多张表中,取出有关联的数据.

关联查询一共有几种情况：

• 内连接：INNER JOIN 、CROSS JOIN
• 外连接：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）、全外连接（FULL OUTER JOIN）
• 自连接：当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义

说明：

（1）连接 n个表,至少需要 n-1个连接条件。 例如：连接三个表，至少需要两个连接条件。

（2）当两个关联查询的表如果有字段名字相同，并且要查询中涉及该关联字段，那么需要使用表名前缀加以区分

（3）当如果表名比较长时，可以给表取别名，简化SQL语句

3.1 笛卡尔积

定义：将两（或多）个表的所有行进行组合，连接后的行数为两（或多）个表的乘积数.

在MySQL中如下情况会出现笛卡尔积，主要是因为缺少关联条件或者关联条件不准确

注：外连接必须写关联条件，否则报语法错误

#笛卡尔积
#查询员工姓名和所在部门名称
SELECT ename,dname FROM t_employee,t_department;
SELECT ename,dname FROM t_employee INNER JOIN t_department;
SELECT ename,dname FROM t_employee CROSS JOIN t_department;
SELECT ename,dname FROM t_employee JOIN t_department;

3.2关联条件

表连接的约束条件可以有三种方式：WHERE, ON, USING

• WHERE：适用于所有关联查询

• ON：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写，但分开写可读性更好。

• USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等

#关联条件
#把关联条件写在where后面
SELECT ename,dname FROM t_employee,t_department WHERE t_employee.dept_id=t_department.did;

#把关联条件写在on后面，只能和JOIN一起使用
SELECT ename,dname FROM t_employee INNER JOIN t_department ON t_employee.dept_id=t_department.did;
SELECT ename,dname FROM t_employee CROSS JOIN t_department ON t_employee.dept_id=t_department.did;
SELECT ename,dname FROM t_employee JOIN t_department ON t_employee.dept_id=t_department.did;

#把关联字段写在using()中，只能和JOIN一起使用
#而且两个表中的关联字段必须名称相同，而且只能表示=
#查询员工姓名与基本工资
SELECT ename,basic_salary FROM t_employee INNER JOIN t_salary USING(eid);

#n张表关联，需要n-1个关联条件
#查询员工姓名，基本工资，部门名称
SELECT ename,basic_salary,dname FROM t_employee,t_department,t_salary 
WHERE t_employee.dept_id=t_department.did AND t_employee.eid=t_salary.eid;

SELECT ename,basic_salary,dname FROM t_employee INNER JOIN t_department INNER JOIN t_salary 
ON t_employee.dept_id=t_department.did AND t_employee.eid=t_salary.eid;

3.3 内连接（INNER JOIN）

有两种，显式的和隐式的，返回连接表中符合连接条件和查询条件的数据行

格式：

隐式：SELECT [cols_list] from 表1,表2 where [condition]

显式：SELECT [cols_list] from 表1 INNER JOIN 表2 ON [关联条件] where [其他筛选条件]

? SELECT [cols_list] from 表1 CROSS JOIN 表2 ON [关联条件] where [其他筛选条件]

? SELECT [cols_list] from 表1 JOIN 表2 ON [关联条件] where [其他筛选条件]

#内连接
#查询员工姓名和所在部门名称
SELECT ename,dname FROM t_employee,t_department WHERE t_employee.dept_id=t_department.did;
SELECT ename,dname FROM t_employee INNER JOIN t_department ON t_employee.dept_id=t_department.did;
SELECT ename,dname FROM t_employee CROSS JOIN t_department ON t_employee.dept_id=t_department.did;
SELECT ename,dname FROM t_employee JOIN t_department ON t_employee.dept_id=t_department.did;

#查询员工姓名，基本工资，部门名称
SELECT ename,basic_salary,dname FROM t_employee,t_department,t_salary 
WHERE t_employee.dept_id=t_department.did AND t_employee.eid=t_salary.eid;

SELECT ename,basic_salary,dname FROM t_employee INNER JOIN t_department INNER JOIN t_salary 
ON t_employee.dept_id=t_department.did AND t_employee.eid=t_salary.eid;

3.4 外连接（OUTER JOIN）

外连接分为：

左外连接（LEFT OUTER JOIN），简称左连接（LEFT JOIN）

右外连接（RIGHT OUTER JOIN），简称右连接（RIGHT JOIN）

全外连接（FULL OUTER JOIN），简称全连接（FULL JOIN）。

左连接（LEFT JOIN）

右外连接（RIGHT JOIN）

外连接（FULL JOIN）

mysql不支持FULL JOIN，但是可以用 left join **union **right join代替

自连接

当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询

自连接
#查询员工姓名以及领导姓名，仅显示有领导的员工
SELECT emp.ename,mgr.ename 
FROM t_employee AS emp, t_employee AS mgr
WHERE emp.mid = mgr.eid;

#查询员工姓名以及领导姓名，仅显示有领导的员工
SELECT emp.ename,mgr.ename 
FROM t_employee AS emp INNER JOIN t_employee AS mgr
ON emp.mid = mgr.eid;

#查询所有员工姓名及其领导姓名
SELECT emp.ename,mgr.ename 
FROM t_employee AS emp LEFT JOIN t_employee AS mgr
ON emp.mid = mgr.eid;

四、select的5个子句

4.1 where条件查询

从原表中的记录中进行筛选

4.2 group by 分组查询

很多情况下，用户都需要进行一些汇总操作，比如统计整个公司的人数或者统计每一个部门的人数等。

4.2.1聚合函数

• AVG(【DISTINCT】 expr) 返回expr的平均值
• COUNT（【DISTINCT】 expr）返回expr的非NULL值的数目
• MIN(【DISTINCT】 expr)返回expr的最小值
• MAX(【DISTINCT】 expr)返回expr的最大值
• SUM（【DISTINCT】 expr）返回expr的总和

#聚合函数
#AVG(【DISTINCT】 expr) 返回expr的平均值
SELECT AVG(basic_salary) FROM t_salary;

#COUNT（【DISTINCT】 expr）返回expr的非NULL值的数目
#统计员工总人数
SELECT COUNT(*) FROM t_employee;#count(*)统计的是记录数
#统计员工表的员工所在部门数
SELECT COUNT(dept_id) FROM t_employee;#统计的是非NULL值
SELECT COUNT(DISTINCT dept_id) FROM t_employee;#统计的是非NULL值，并且去重

#MIN(【DISTINCT】 expr)返回expr的最小值
#查询最低基本工资值
SELECT MIN(basic_salary) FROM t_salary;

#MAX(【DISTINCT】 expr)返回expr的最大值
#查询最高基本工资值
SELECT MAX(basic_salary) FROM t_salary;

#查询最高基本工资与最低基本工资的差值
SELECT MAX(basic_salary)-MIN(basic_salary) FROM t_salary;

#SUM（【DISTINCT】 expr）返回expr的总和
#查询基本工资总和
SELECT SUM(basic_salary) FROM t_salary;

4.2.2 group by + 聚合函数

#group by + 聚合函数
#统计每个部门的人数
SELECT dept_id,COUNT(*) FROM t_employee
GROUP BY dept_id;

#统计每个部门的平均基本工资
SELECT emp.dept_id,AVG(s.basic_salary )
FROM t_employee AS emp,t_salary AS s
WHERE emp.eid = s.eid
GROUP BY emp.dept_id;

#统计每个部门的年龄最大者
SELECT dept_id,MIN(birthday) FROM t_employee GROUP BY dept_id;

#统计每个部门基本工资最高者
SELECT emp.dept_id,MAX(s.basic_salary )
FROM t_employee AS emp,t_salary AS s
WHERE emp.eid = s.eid
GROUP BY emp.dept_id;

#统计每个部门基本工资之和
SELECT emp.dept_id,SUM(s.basic_salary )
FROM t_employee AS emp,t_salary AS s
WHERE emp.eid = s.eid
GROUP BY emp.dept_id;

注意：****

用count(*),count(1),谁好呢?

其实,对于myisam引擎的表,没有区别的.

这种引擎内部有一计数器在维护着行数.

Innodb的表,用count(*)直接读行数,效率很低,因为innodb真的要去数一遍.

关于mysql的group by的特殊：

注意：在SELECT 列表中所有未包含在组函数中的列都应该是包含在 GROUP BY 子句中的，换句话说，SELECT列表中最好不要出现GROUP BY子句中没有的列。

对于标准语句来说，这个语句是错误的，但是mysql可以这么干，出于可移植性和规范性,不推荐这么写。

4.3 having 筛选

having与where类似,可筛选数据

having与where不同点

• where针对表中的列发挥作用,查询数据；having针对查询结果中的列发挥作用,筛选数据
• where后面不能写分组函数，而having后面可以使用分组函数
• having只用于group by分组统计语句

#按照部门统计员工人数，仅显示部门人数少于3人的
SELECT dept_id,COUNT(*) AS c 
FROM t_employee 
WHERE dept_id IS NOT NULL
GROUP BY dept_id
HAVING c <3;

#查询每个部门的平均工资，并且仅显示平均工资高于10000
SELECT emp.dept_id,AVG(s.basic_salary ) AS avg_salary
FROM t_employee AS emp,t_salary AS s
WHERE emp.eid = s.eid AND dept_id IS NOT NULL
GROUP BY emp.dept_id
HAVING avg_salary >10000;

4.4 order by 排序

• 按一个或多个字段对查询结果进行排序

用法：order by col1,col2,col3...

说明：先按col1排序如果col1相同就按照col2排序，依次类推

col1，col2，col3可以是select后面的字段也可以不是

• 默认是升序，也可以在字段后面加asc显示说明是升序，desc为降序

例如：order by click_count desc;

? 如果两个字段排序不一样，例如：

? order by 字段1 asc ,字段2 desc;

• order by 后面除了跟1个或多个字段，还可以写表达式，函数，别名等

mysql 进阶