MySQL记录相关操作基础
目录
MySQL记录相关操作基础
一 插入数据insert
1) 插入完整数据(顺序插入)
语法一：
insert 表名(字段1,字段2,字段3…字段n) values(值1,值2,值3…值n);
语法二：
insert 表名 values(值1,值2,值3…值n);
2) 指定字段插入数据
语法：
insert 表名字段1,字段2,字段3…) values (值1,值2,值3…);
3) 插入多条记录
语法：
insert 表名 values
(值1,值2,值3…值n),
(值1,值2,值3…值n),
(值1,值2,值3…值n);
4) 插入查询结果
语法：
insert 表名(字段1,字段2,字段3…字段n)
select (字段1,字段2,字段3…字段n) from 表2
where …;
二 更新数据update
# 语法：
update 表 set 字段1=值1,字段2=值2 where 条件；
三 删除数据delete
# 语法：
delete from 表 where 条件;
四 查询数据select之单表查询
1 单表查询的语法
select distinct 字段1, 字段2, 字段3, ... from 表名
where 过滤条件
group by 分组的条件
having 筛选条件
order by 排序字段
limit n; 限制条数
2 关键字的执行优先级(重点)
# 重点：关键字的执行优先级
1) from # 找到表:from
2) where # 拿着where指定的约束条件，去文件/表中取出一条条记录
3) group by # 将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4) having # 将分组的结果进行having过滤
5) select # 执行select
6) distinct # 去重
7) order by # 将结果按条件排序：order by
8) limit # 限制结果的显示条数
3 简单查询
## 示例表和记录
company.employee
员工id id int
姓名 emp_name varchar
性别 sex enum
年龄 age int
入职日期 hire_date date
岗位 post varchar
职位描述 post_comment varchar
薪水 salary double
办公室 office int
部门编号 depart_id int
## 创建表
mysql> create table employee(
-> id int not null unique auto_increment,
-> name varchar(20) not null,
-> sex enum('male','female') not null default 'male',
-> age int(3) unsigned not null default 28,
-> hire_date date not null,
-> post varchar(50),
-> post_comment varchar(100),
-> salary double(15,2),
-> office int,
-> depart_id int
-> );
## 查看表结构
mysql> desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(3) unsigned | NO | | 28 | |
| hire_date | date | NO | | NULL | |
| post | varchar(50) | YES | | NULL | |
| post_comment | varchar(100) | YES | | NULL | |
| salary | double(15,2) | YES | | NULL | |
| office | int(11) | YES | | NULL | |
| depart_id | int(11) | YES | | NULL | |
+--------------+-----------------------+------+-----+---------+----------------+
## 插入记录
#三个部门：教学，销售，运营
mysql> insert employee(name, sex, age, hire_date, post, salary, office, depart_id) values
-> ('egon', 'male', 18, '20170301', '老男孩驻沙河办事处外交大使', 7300.33, 401,1), #以下是教学部
-> ('alex', 'male', 78, '20150302', 'teacher', 1000000.31, 401, 1),
-> ('wupeiqi','male',81,'20130305','teacher',8300,401,1),
-> ('yuanhao','male',73,'20140701','teacher',3500,401,1),
-> ('liwenzhou','male',28,'20121101','teacher',2100,401,1),
-> ('jingliyang','female',18,'20110211','teacher',9000,401,1),
-> ('jinxin','male',18,'19000301','teacher',30000,401,1),
-> ('成龙','male',48,'20101111','teacher',10000,401,1),
->
-> ('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
-> ('丫丫','female',38,'20101101','sale',2000.35,402,2),
-> ('丁丁','female',18,'20110312','sale',1000.37,402,2),
-> ('星星','female',18,'20160513','sale',3000.29,402,2),
-> ('格格','female',28,'20170127','sale',4000.33,402,2),
->
-> ('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
-> ('程咬金','male',18,'19970312','operation',20000,403,3),
-> ('程咬银','female',18,'20130311','operation',19000,403,3),
-> ('程咬铜','male',18,'20150411','operation',18000,403,3),
-> ('程咬铁','female',18,'20140512','operation',17000,403,3)
-> ;
## 简单查询 ##
# 1) 根据全部字段进行查询
mysql> select id, name, sex, age, hire_date, post, post_comment, salary, office, depart_id from employee;
# 2) 查询全部
mysql> select * from employee;
# 3) 根据部分字段进行查询
mysql> select name,salary from employee;
+------------+------------+
| name | salary |
+------------+------------+
| egon | 7300.33 |
| alex | 1000000.31 |
| wupeiqi | 8300.00 |
| yuanhao | 3500.00 |
| liwenzhou | 2100.00 |
| jingliyang | 9000.00 |
| jinxin | 30000.00 |
| 成龙 | 10000.00 |
| 歪歪 | 3000.13 |
| 丫丫 | 2000.35 |
| 丁丁 | 1000.37 |
| 星星 | 3000.29 |
| 格格 | 4000.33 |
| 张野 | 10000.13 |
| 程咬金 | 20000.00 |
| 程咬银 | 19000.00 |
| 程咬铜 | 18000.00 |
| 程咬铁 | 17000.00 |
+------------+------------+
# 避免重复distinct(针对的是记录)
mysql> select distinct post from employee;
+-----------------------------------------+
| post |
+-----------------------------------------+
| 老男孩驻沙河办事处外交大使 |
| teacher |
| sale |
| operation |
+-----------------------------------------+
# 通过四则运算查询
mysql> select name, salary*12 from employee;
+------------+-------------+
| name | salary*12 |
+------------+-------------+
| egon | 87603.96 |
| alex | 12000003.72 |
| wupeiqi | 99600.00 |
| yuanhao | 42000.00 |
| liwenzhou | 25200.00 |
| jingliyang | 108000.00 |
| jinxin | 360000.00 |
| 成龙 | 120000.00 |
| 歪歪 | 36001.56 |
| 丫丫 | 24004.20 |
| 丁丁 | 12004.44 |
| 星星 | 36003.48 |
| 格格 | 48003.96 |
| 张野 | 120001.56 |
| 程咬金 | 240000.00 |
| 程咬银 | 228000.00 |
| 程咬铜 | 216000.00 |
| 程咬铁 | 204000.00 |
+------------+-------------+
mysql> select name, salary*12 as annual_salary from employee; # 可以给四则运算的字段使用as起别名
+------------+---------------+
| name | annual_salary |
+------------+---------------+
| egon | 87603.96 |
| alex | 12000003.72 |
| wupeiqi | 99600.00 |
| yuanhao | 42000.00 |
| liwenzhou | 25200.00 |
| jingliyang | 108000.00 |
| jinxin | 360000.00 |
| 成龙 | 120000.00 |
| 歪歪 | 36001.56 |
| 丫丫 | 24004.20 |
| 丁丁 | 12004.44 |
| 星星 | 36003.48 |
| 格格 | 48003.96 |
| 张野 | 120001.56 |
| 程咬金 | 240000.00 |
| 程咬银 | 228000.00 |
| 程咬铜 | 216000.00 |
| 程咬铁 | 204000.00 |
+------------+---------------+
mysql> select name, salary*12 annual_salary from employee; # 可以给四则运算的字段省略as起别名
# 定义显示格式
1) concat() 函数用于连接字符串
mysql> select concat('姓名：', name," 年薪：",salary*12) as annual_salary from employee; # 还可以添加其他字符串，如货币符号等等
+-----------------------------------------+
| annual_salary |
+-----------------------------------------+
| 姓名：egon 年薪：87603.96 |
| 姓名：alex 年薪：12000003.72 |
| 姓名：wupeiqi 年薪：99600.00 |
| 姓名：yuanhao 年薪：42000.00 |
| 姓名：liwenzhou 年薪：25200.00 |
| 姓名：jingliyang 年薪：108000.00 |
| 姓名：jinxin 年薪：360000.00 |
| 姓名：成龙 年薪：120000.00 |
| 姓名：歪歪 年薪：36001.56 |
| 姓名：丫丫 年薪：24004.20 |
| 姓名：丁丁 年薪：12004.44 |
| 姓名：星星 年薪：36003.48 |
| 姓名：格格 年薪：48003.96 |
| 姓名：张野 年薪：120001.56 |
| 姓名：程咬金 年薪：240000.00 |
| 姓名：程咬银 年薪：228000.00 |
| 姓名：程咬铜 年薪：216000.00 |
| 姓名：程咬铁 年薪：204000.00 |
+-----------------------------------------+
# 2) concat_ws() 第一个参数为分隔符
# 3) 结合case语句：
select (
case
when name="egon" then
name
when name='alex' then
concat(name, "_dsb")
else
concat(name, "_sb")
end
) as 名字 from employee;
+---------------+
| 名字 |
+---------------+
| egon |
| alex_dsb |
| wupeiqi_sb |
| yuanhao_sb |
| liwenzhou_sb |
| jingliyang_sb |
| jinxin_sb |
| 成龙_sb |
| 歪歪_sb |
| 丫丫_sb |
| 丁丁_sb |
| 星星_sb |
| 格格_sb |
| 张野_sb |
| 程咬金_sb |
| 程咬银_sb |
| 程咬铜_sb |
| 程咬铁_sb |
+---------------+
4 where约束
4.1 where字句中可以使用：
1) 比较运算符： > < >= <= !=
2) between 80 and 100 : 值在80到100之间
3) in(80,90,100) : 值是80或90或100
4) like "r%" 或 like "r_"
pattern(模式)可以是 % 或 _
%表示任意多字符
_表示一个字符
5) 逻辑运算符：在多个条件中可以使用逻辑运算符 and or not
4.2 代码示例
# 1) 单条件查询
select name from employee where post="sale";
# 2) 多条件查询
select name,salary from employee where post="teacher" and salary>10000;
# 3) 关键字between and
select name,salary from employee where salary between 10000 and 20000;
select name,salary from employee where salary not between 10000 and 20000;
# 4) 关键字is null(判断某个字段是否为null不能用等号，需要使用is)
select name, post_comment from employee where post_comment is null;
select name, post_comment from employee where post_comment is not null; # Empty set (0.00 sec)
select name, post_comment from employee where post_comment=""; # 注意！！：""是空字符串，不是null，null是写入的时候什么都没写，空字符串是写入了空字符串。
PS：执行 update employee set post_comment="" where id=2; 在使用查询空字符串就会有结果。
# 5) 关键字in集合查询
select name, salary from employee where salary=3000 or salary=3500 or salary=4000 or salary=9000;
select name, salary from employee where salary in (3000,3500,4000,9000);
select name, salary from employee where salary not in (3000,3500,4000,9000);
# 6) 关键字like模糊查询
通配符"%"
select * from employee where name like "eg%";
通配符"_"
select * from employee where name like "ale_";
5 分组查询：group by
5.1 什么是分组？为什么要分组
1) 首先，分组发生在 where之后，即分组是基于 where之后的记录进行的；
2) 分组指的是：将所有的记录按照某个相同的字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等等；
3) 为何要进行分组？
取每个部门的最高工资；取每个部门的员工数；取男性和女性的数量；
4) 大前提：
可以按照任意字段分组，但是分组完毕后，比如 group by post，只能查看 post字段，如果想查看组内信息，需要借助于聚合函数。
5.2 only_full_group_by
"！！！SQL_MODE设置！！！":
# ！！！SQL_MODE设置！！！
# 查看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';
# 不设置ONLY_FULL_GROUP_BY时查看分组的数据
mysql> select @@global.sql_mode;
+--------------------------------------------+
| @@global.sql_mode |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
mysql> select * from employee group by post;
+----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 |
+----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
# 由于没有设置ONLY_FULL_GROUP_BY,于是也可以有结果，默认都是组内的第一条记录，但其实这是没有意义的
mysql> set global sql_mode='ONLY_FULL_GROUP_BY';
mysql> quit # 设置成功后，一定要退出，然后重新登录方可生效
Bye
mysql> use db2
Database changed
mysql> select * from employee group by post;
-- ERROR 1055 (42000): 'db2.employee.id' isn't in GROUP BY # 报错
mysql> select post, count(id) from employee group by post; # 只能查看分组依据和使用聚合函数
+-----------------------------------------+-----------+
| post | count(id) |
+-----------------------------------------+-----------+
| operation | 5 |
| sale | 5 |
| teacher | 7 |
| 老男孩驻沙河办事处外交大使 | 1 |
+-----------------------------------------+-----------+
5.3 group by
# 1) 单独使用group by关键字分组
select post from employee group by post;
注意：我们按照post字段分组，那么select查询字段只能是post，想要获取组内的其他相关信息，需要借助函数。
+-----------------------------------------+
| post |
+-----------------------------------------+
| operation |
| sale |
| teacher |
| 老男孩驻沙河办事处外交大使 |
+-----------------------------------------+
# 2) group by 关键字和group_concat()函数一起使用
select post, group_concat(name) from employee group by post; # 按照岗位分组，并查看组内成员名
select post, group_concat(name) as emp_member from employee group by post;
# 3) group by与聚合函数一起使用
select post, count(id) as count from employee group by post; # 按照岗位分组，并查看每个组有多少人
+-----------------------------------------+-------+
| post | count |
+-----------------------------------------+-------+
| operation | 5 |
| sale | 5 |
| teacher | 7 |
| 老男孩驻沙河办事处外交大使 | 1 |
+-----------------------------------------+-------+
"强调："
1) 如果我们用unique的字段作为分组依据，则每一条记录自称一组，这种分组没有任何意义；
2) 多条记录之间的某个字段值相等，该字段通常用来作为分组的依据。
5.4 聚合函数
"强调：聚合函数聚合的是组的内容，若是没有分组，则默认整体是一个组。"
# 示例：
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;
6 having过滤
"having与where不一样的地方："
# !!!执行优先级从高到低：where > group by > having
# 1) where 发生在分组 group by 之前，因而where中可以有任意字段，但是绝对不能使用聚合函数；
# 2) having 发生在分组 group by 之后，因而having中可以使用分组的字段，无法直接取到其他字段，可以使用聚合函数。
# 代码验证
mysql> select @@sql_mode;
+--------------------+
| @@sql_mode |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+
mysql> select * from employee where salary > 100000;
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
mysql> select * from employee having salary > 100000;
-- ERROR 1463 (42000): Non-grouping field 'salary' is used in HAVING clause
mysql> select post, group_concat(name) from employee group by having salary > 10000; # 错误，分组后无法直接取到salary字段
-- ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'having salary > 10000' at line 1
mysql> select post, group_concat(name) from employee group by post having avg(salary) > 10000;
+-----------+---------------------------------------------------------+
| post | group_concat(name) |
+-----------+---------------------------------------------------------+
| operation | 程咬铁,程咬铜,程咬银,程咬金,张野 |
| teacher | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex |
+-----------+---------------------------------------------------------+
7 查询排序：order by
5.1 按单列排序
# 示例：
select * from employee order by salary; # 默认升序排列
select * from employee order by salary asc; # 升序排列
select * from employee order by salary desc; # 降序排列
5.2 按多列排序：
"主要条件先排序，次要条件在主要条件无法分出先后时参与排序"
# 示例：
select * from employee order by age,salary desc; # 年龄升序，薪资降序
select post, avg(salary) as v from employee where sex="male" group by post having avg(salary>3000) order by v; # 1) from 表 employee；2) where指定的约束条件sex="male"； 3) 分组group by post； 4) 将分组的结果进行having avg(salary>3000)过滤； 4) 执行select； 5) 去重(本语句无)； 6) 将结果按条件排序：order by v(即avg(salary) ) ； 7) 限制结果的显示条数(本语句无)。
+-----------------------------------------+---------------+
| post | v |
+-----------------------------------------+---------------+
| 老男孩驻沙河办事处外交大使 | 7300.330000 |
| operation | 16000.043333 |
| teacher | 175650.051667 |
+-----------------------------------------+---------------+
8 限制查询的记录数：limit
# 示例：
select * from employee order by salary desc limit 3; # 默认初始位置为0
select * from employee order by salary desc limit 0,5; # 从第0开始，即先查询出第一条，然后包含这一条在内往后查5条
select * from employee order by salary desc limit 5,5; # 从第5开始，即先查询出第6条，然后包含这一条在内往后查5条
9 使用正则表达式查询
# 示例：
select * from employee where name regexp "^ale";
select * from employee where name regexp "on$";
select name, id, age, salary from employee where name regexp "程{1}";
+-----------+----+-----+----------+
| name | id | age | salary |
+-----------+----+-----+----------+
| 程咬金 | 15 | 18 | 20000.00 |
| 程咬银 | 16 | 18 | 19000.00 |
| 程咬铜 | 17 | 18 | 18000.00 |
| 程咬铁 | 18 | 18 | 17000.00 |
+-----------+----+-----+----------+
# 小结：对字符串匹配的当时
where name="alex";
where name like "yua%";
where name regexp "on$"; # 以on为结尾的name字段的记录
浙公网安备 33010602011771号