一、插入数据
1. 插入完整数据(顺序插入)
语法一:INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);
语法二:INSERT INTO 表名 VALUES(值1,值2,值3…值n);2. 指定字段插入数据
语法:INSERT INTO 表名(字段1,字段2,字段3…) VALUES(值1,值2,值3…);3. 插入多条记录
语法:INSERT INTO 表名 VALUES(值1,值2,值3…值n),
(值1,值2,值3…值n),
(值1,值2,值3…值n);4. 插入查询结果
语法:INSERT INTO表名(字段1,字段2,字段3…字段n)SELECT (字段1,字段2,字段3…字段n) FROM表2WHERE …;
补充:
value与values区别
mysql 官方文档说明,values与value都是正确的。
在大量数据批量插入时,效率 value > values
二、更新数据
语法:UPDATE 表名 SET字段1=值1,
字段2=值2,WHERECONDITION;
示例:UPDATE mysql.user SET password=password(‘123’)where user=’root’ and host=’localhost’;
三、删除数据
语法:DELETE FROM表名WHERECONITION;
示例:DELETE FROM mysql.user
WHERE password=’’;
四、数据查询
1. 单表查询完成语法
select distinct 查询字段1,查询字段2,。。。 from表名where分组之前的过滤条件group by分组依据having分组之后的过滤条件order by排序字段
limit 显示的条数;
2. mysql 查询语句的执行顺序
开始->FROM子句->WHERE子句->GROUP BY子句->HAVING子句->ORDER BY子句->SELECT子句->LIMIT子句->最终结果
每个子句执行后都会产生一个中间结果,供接下来的子句使用,如果不存在某个子句,就跳过 。
lect 考生姓名, max(总成绩) asmax总成绩fromtb_Gradewhere 考生姓名 is not null
group by考生姓名having max(总成绩) > 600
order by max总成绩
示例SQL
(1). 首先执行 FROM子句, 从 tb_Grade 表组装数据源的数据
(2). 执行 WHERE 子句, 筛选 tb_Grade 表中所有数据不为 NULL的数据
(3). 执行 GROUP BY子句, 把 tb_Grade 表按 "学生姓名" 列进行分组(注:这一步开始才可以使用select中的别名,他返回的是一个游标,而不是一个表,所以在where中不可以使用select中的别名,而having却可以使用。)
(4). 计算 max() 聚集函数, 按 "总成绩" 求出总成绩中最大的一些数值
(5). 执行 HAVING 子句, 筛选课程的总成绩大于 600分的.
(7). 执行 ORDER BY 子句, 把最后的结果按 "Max 成绩" 进行排序.
过程分析
五、WHERE约束
where字句中可以使用:1. 比较运算符:> < >= <= <> !=
2. between 80 and 100值在10到20之间3. in(80,90,100) 值是10或20或304. like 'egon%'pattern可以是%或_,%表示任意多字符
_表示一个字符5. 逻辑运算符:在多个条件直接可以使用逻辑运算符 and or not
六、分组查询:GROUP BY
1. 什么是分组
#1、首先明确一点:分组发生在where之后,即分组是基于where之后得到的记录而进行的
#2、分组指的是:将所有记录按照某个相同字段进行归类,比如针对员工信息表的职位分组,或者按照性别进行分组等
#3、为何要分组呢?
取每个部门的最高工资
取每个部门的员工数
取男人数和女人数
小窍门:‘每’这个字后面的字段,就是我们分组的依据#4、大前提:
可以按照任意字段分组,但是分组完毕后,比如group by post,只能查看post字段,
如果想查看组内信息,需要借助于聚合函数
2. 设置 sql_mode ONLY_FULL_GROUP_BY
#查看MySQL 5.7默认的sql_mode如下:
mysql> select @@global.sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION#!!!注意
ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义,简单的说来,在ONLY_FULL_GROUP_BY模式下,target list中的值要么是来自于聚集函数的结果,要么是来自于group by list中的表达式的值。#设置sql_mole如下操作(我们可以去掉ONLY_FULL_GROUP_BY模式):
mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
!!!SQL_MODE设置!!!
具体用法
3. GROUP BY
单独使用GROUP BY关键字分组
SELECT post FROM employee GROUP BY post;
注意:我们按照post字段分组,那么select查询的字段只能是post,想要获取组内的其他相关信息,需要借助函数
GROUP BY关键字和GROUP_CONCAT()函数一起使用
SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照岗位分组,并查看组内成员名
SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;
GROUP BY与聚合函数一起使用
select post,count(id) as countfrom employee group by post;#按照岗位分组,并查看每个组有多少人
注意:
如果我们用unique的字段作为分组的依据,则每一条记录自成一组,这种分组没有意义
多条记录之间的某个字段值相同,该字段通常用来作为分组的依据
七、聚合函数
#强调:聚合函数聚合的是组的内容,若是没有分组,则默认一组
示例:
SELECT COUNT(*) FROM employee;
SELECT COUNT(*) FROM employee WHERE depart_id=1;
SELECT MAX(salary) FROM employee;
SELECT MIN(salary) FROM employee;
SELECT AVG(salary) FROM employee;
SELECT SUM(salary) FROM employee;
SELECT SUM(salary) FROM employee WHERE depart_id=3;
八、HAVING过滤
HAVING与WHERE 不同点.......
#!!!执行优先级从高到低:where > group by > having#1. Where 发生在分组group by之前,因而Where中可以有任意字段,但是绝对不能使用聚合函数。
#2. Having发生在分组group by之后,因而Having中可以使用分组的字段,无法直接取到其他字段,可以使用聚合函数
九、查询排序:ORDER BY
将查询出来的数据排序
按单列排序
SELECT*FROM employee ORDER BY salary;
SELECT*FROM employee ORDER BY salary ASC; #1. 升序
SELECT*FROM employee ORDER BY salary DESC; #2. 降序
按多列排序:先按照age排序,如果年纪相同,则按照薪资排序
SELECT* fromemployee
ORDER BY age,
salary DESC;
十、限制查询的记录数:LIMIT
SELECT *FROM employee ORDER BY salary DESC
LIMIT3; #默认初始位置为0
SELECT*FROM employee ORDER BY salary DESC
LIMIT 0,5; #从第0开始,即先查询出第一条,然后包含这一条在内往后查5条
SELECT*FROM employee ORDER BY salary DESC
LIMIT5,5; #从第5开始,即先查询出第6条,然后包含这一条在内往后查5条
示例代码
十一、正则
SELECT * FROM employee WHERE name REGEXP '^ale';
SELECT* FROM employee WHERE name REGEXP 'on$';
SELECT* FROM employee WHERE name REGEXP 'm{2}';
小结:对字符串匹配的方式
WHERE name= 'egon';
WHERE name LIKE'yua%';
WHERE name REGEXP'on$';
示例代码
十二、补充 concat ,group_concat,as起别名,查询四则运算
#group_concat(分组之后用)
select post,group_concat(name) fromemp group by post;
select post,group_concat(name,"_SB") fromemp group by post;
select post,group_concat(name,":",salary) fromemp group by post;
select post,group_concat(salary)fromemp group by post;#补充concat(不分组时用)
select name as 姓名,salary as 薪资 fromemp;
select concat("NAME:",name) as 姓名,concat("SAL:",salary) as 薪资 fromemp;#补充as语法
mysql> select emp.id,emp.name from emp as t1; #报错
mysql> select t1.id,t1.name fromemp as t1;#查询四则运算
select name,salary*12 as annual_salary from emp;
View Code