数据库之表的使用
sql_mode 的设置
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
#设置为安全模式
增语法
1.所有数据按顺序插入
insert into 表名 values(值1,....值n)
2.指定字段匹配插入,可以任意顺序
insert into 表名(name,age)values("zb",18)
3.插入查询结果
insert into 表1(name,age,sex)select 字段1,字段2,from 表2
删语法
1.会记录自增消息,操作会被日志记录,效率低
delete from 表名[条件]
2.清空表,会重置自增信息
truncate [table] 表名
改语法
update 表名 set 字段1 =值1,字段2 =值2 [条件]
不选择条件,默认全修改
查语法
select [distinct ] 字段1 [as 别名] ,字段2,.....字段n from [库名].表名
where 约束条件
group by 分组依据
having 过滤条件
order by 排序的字段
limit 限制显示的条数
#条件的书写规则严格按照语法顺序书写可以缺省,但不可以错序
#执行顺序from >>where>>group by>> having >> distinct>>order by>>limit
#having 只能用于分组后,where只能用于分组前
#分组后的条件均可以使用聚合函数
聚合函数:max(),min(),avg(),sum(),count(),
group_concat():组内字段拼接,用来查看组内其他字段
单表数据
CREATE TABLE emp (
id int(0) NOT NULL AUTO_INCREMENT,
name varchar(10) NOT NULL,
gender enum('男','女','未知') NULL DEFAULT '未知',
age int(0) NULL DEFAULT 0,
salary float NULL DEFAULT 0,
area varchar(20) NULL DEFAULT '中国',
port varchar(20) DEFAULT '未知',
dep varchar(20),
PRIMARY KEY (`id`)
);
INSERT INTO emp VALUES
(1, 'yangsir', '男', 42, 10.5, '上海', '浦东', '教职部'),
(2, 'engo', '男', 38, 9.4, '山东', '济南', '教学部'),
(3, 'jerry', '女', 30, 3.0, '江苏', '张家港', '教学部'),
(4, 'tank', '女', 28, 2.4, '广州', '广东', '教学部'),
(5, 'jiboy', '男', 28, 2.4, '江苏', '苏州', '教学部'),
(6, 'zero', '男', 28, 8.8, '中国', '黄浦', '咨询部'),
(7, 'owen', '男', 28, 8.8, '江苏', '盐城', '教学部'),
(8, 'zero', '女', 18, 4.8, '安徽', '宣城', '教学部'),
(9, 'ying', '女', 36, 1.2, '安徽', '芜湖', '咨询部'),
(10, 'kevin', '男', 36, 5.8, '山东', '济南', '教学部'),
(11, 'monkey', '女', 28, 1.2, '山东', '青岛', '教职部'),
(12, 'san', '男', 30, 9.0, '上海', '浦东', '咨询部'),
(13, 'san1', '男', 30, 6.0, '上海', '浦东', '咨询部'),
(14, 'san2', '男', 30, 6.0, '上海', '浦西', '教学部');
where 条件
1.比较运算符
select id, name,age from emp where id != 8 ;
2.区间运算
between 10 and 30
in (10,20,30)
select id ,name from emp where id between 10 and 20;
select id ,name from emp where name in ("engo","owen",jerry");
3.逻辑运算符
and or not
select id ,name ,age from emp where id >10 and age>20;
4.相似运算符
like "_owen%" 模糊匹配, _表示一个字符,%任意字符
select id ,name ,salary from emp where name like "%g%";
5.正则匹配
语法 字段 regexp "正则表达式" 注:只支持部分正则语法
查找姓名有数字的员工信息
select name from emp where name regexp ".*[0-9]+.*";
常用函数:
concat (字段1,....字段n)完成字段的拼接
select concat(name,",",age) "信息" from emp where age > 30;
+------------+
| 信息 |
+------------+
| yangsir,42 |
| engo,38 |
| ying,36 |
| kevin,36 |
+------------+
concat_ws(x,字段1,.....,字段n):完成字段的拼接,x为连接符
select concat_ws("-",name,age,salary) "高薪人士" from emp where salary > 6;
+-----------------+
| 高薪人士 |
+-----------------+
| yangsir-42-10.5 |
| engo-38-9.4 |
| zero-28-8.8 |
| owen-28-8.8 |
| san-30-9 |
upper()
select concat(upper(name),"-",age) "试试" from emp where salary >6;
lower()
ceil()向上取整
floor()向下取整
round()四舍五入
mysql> select ceil(salary),floor(salary),round(salary) from emp where id=2;
+--------------+---------------+---------------+
| ceil(salary) | floor(salary) | round(salary) |
+--------------+---------------+---------------+
| 10 | 9 | 9 |
+--------------+---------------+---------------+
1 row in set (0.00 sec)
需求:各性别中附属于教学部的最高薪资
select gender,max(salary) from emp where dep = "教学部" group by gender ;
mysql> select gender,max(salary) from emp where dep = "教学部" group by gender ;
+--------+-------------+
| gender | max(salary) |
+--------+-------------+
| 男 | 9.4 |
| 女 | 3 |
+--------+-------------+
以上性别的其他信息
select * from emp where salary in (select max(salary) from emp where dep = "教学部" group by gender );
----+-------+--------+------+--------+--------+-----------+-----------+
| id | name | gender | age | salary | area | port | dep |
+----+-------+--------+------+--------+--------+-----------+-----------+
| 2 | engo | 男 | 38 | 9.4 | 山东 | 济南 | 教学部 |
| 3 | jerry | 女 | 30 | 3 | 江苏 | 张家港 | 教学部 |
+----+-------+--------+------+--------+--------+-----------+-----------+
子查询
```python
将一条查询结果作为另外一条查询的条件
语法:一条select语句用()包裹得到的结果作为另一条select语句的条件
单行子查询:
子查询语句的结果为一行数据,可以结合 运算符来完成父查询
多行子查询:
子查询语句的结果为多行数据,可以结合 in|all|any
in 是否在结果范围内,在就取出来
子查询:
select salary from emp where id between 2 and 8;
+--------+
| salary |
+--------+
| 9.4 |
| 3 |
| 2.4 |
| 2.4 |
| 8.8 |
| 8.8 |
最大9.4 ,最小 2.4
all 将结果视为一个整体,此时用 = 号来判断是没有数据符合
select * from emp where salary = all(select max(salary) from emp where dep = "教学部" group by gender );
Empty set (0.00 sec)
用> 号判断,是需要大于结果中最大的(9.4)
select * from emp where salary > all(select salary from emp where id between 2 and 8);
+----+---------+--------+------+--------+--------+--------+-----------+
| id | name | gender | age | salary | area | port | dep |
+----+---------+--------+------+--------+--------+--------+-----------+
| 1 | yangsir | 男 | 42 | 10.5 | 上海 | 浦东 | 教职部 |
+----+---------+--------+------+--------+--------+--------+-----------+
用< 号判断,是需要小于结果中最小(2.4)的
select * from emp where salary <= all(select salary from emp where id between 2 and 8);
select * from emp where salary < all(select salary from emp where id between 2 and 8);
+----+--------+--------+------+--------+--------+--------+-----------+
| id | name | gender | age | salary | area | port | dep |
+----+--------+--------+------+--------+--------+--------+-----------+
| 9 | ying | 女 | 36 | 1.2 | 安徽 | 芜湖 | 咨询部 |
| 11 | monkey | 女 | 28 | 1.2 | 山东 | 青岛 | 教职部 |
+----+--------+--------+------+--------+--------+--------+-----------+
any:
= 子查询结果中的任意一个都可以
select * from emp where salary = any(select salary from emp where id between 2 and 8);
+----+-------+--------+------+--------+--------+-----------+-----------+
| id | name | gender | age | salary | area | port | dep |
+----+-------+--------+------+--------+--------+-----------+-----------+
| 2 | engo | 男 | 38 | 9.4 | 山东 | 济南 | 教学部 |
| 3 | jerry | 女 | 30 | 3 | 江苏 | 张家港 | 教学部 |
| 4 | tank | 女 | 28 | 2.4 | 广州 | 广东 | 教学部 |
| 5 | jiboy | 男 | 28 | 2.4 | 江苏 | 苏州 | 教学部 |
| 6 | zero | 男 | 28 | 8.8 | 中国 | 黄浦 | 咨询部 |
| 7 | owen | 男 | 28 | 8.8 | 安徽 | 宣城 | 教学部 |
+----+-------+--------+------+--------+--------+-----------+-----------+
< 小于任意一个都可以也就是小于最大的都可以
select * from emp where salary < any(select salary from emp where id between 2 and 8);
+----+--------+--------+------+--------+--------+-----------+-----------+
| id | name | gender | age | salary | area | port | dep |
+----+--------+--------+------+--------+--------+-----------+-----------+
| 3 | jerry | 女 | 30 | 3 | 江苏 | 张家港 | 教学部 |
| 4 | tank | 女 | 28 | 2.4 | 广州 | 广东 | 教学部 |
| 5 | jiboy | 男 | 28 | 2.4 | 江苏 | 苏州 | 教学部 |
| 6 | zero | 男 | 28 | 8.8 | 中国 | 黄浦 | 咨询部 |
| 7 | owen | 男 | 28 | 8.8 | 安徽 | 宣城 | 教学部 |
| 9 | ying | 女 | 36 | 1.2 | 安徽 | 芜湖 | 咨询部 |
| 10 | kevin | 男 | 36 | 5.8 | 山东 | 济南 | 教学部 |
| 11 | monkey | 女 | 28 | 1.2 | 山东 | 青岛 | 教职部 |
| 12 | san | 男 | 30 | 9 | 上海 | 浦东 | 咨询部 |
| 13 | san1 | 男 | 30 | 6 | 上海 | 浦东 | 咨询部 |
| 14 | san2 | 男 | 30 | 6 | 上海 | 浦西 | 教学部 |
+----+--------+--------+------+--------+--------+-----------+-----------+
>大于任意一个都可以,也就是大于最小的就可以
select * from emp where salary > any(select salary from emp where id between 2 and 8);
+----+---------+--------+------+--------+--------+-----------+-----------+
| id | name | gender | age | salary | area | port | dep |
+----+---------+--------+------+--------+--------+-----------+-----------+
| 1 | yangsir | 男 | 42 | 10.5 | 上海 | 浦东 | 教职部 |
| 2 | engo | 男 | 38 | 9.4 | 山东 | 济南 | 教学部 |
| 3 | jerry | 女 | 30 | 3 | 江苏 | 张家港 | 教学部 |
| 6 | zero | 男 | 28 | 8.8 | 中国 | 黄浦 | 咨询部 |
| 7 | owen | 男 | 28 | 8.8 | 安徽 | 宣城 | 教学部 |
| 10 | kevin | 男 | 36 | 5.8 | 山东 | 济南 | 教学部 |
| 12 | san | 男 | 30 | 9 | 上海 | 浦东 | 咨询部 |
| 13 | san1 | 男 | 30 | 6 | 上海 | 浦东 | 咨询部 |
| 14 | san2 | 男 | 30 | 6 | 上海 | 浦西 | 教学部 |
+----+---------+--------+------+--------+--------+-----------+-----------+
having筛选
完成分组之后的筛选
注意:having 条件是实现聚合结果层面上的筛选>=拿聚合结果来判断
平均薪资大于6万的部门
select dep,avg(salary) from emp group by dep having avg(salary) > 6;
+-----------+-------------------+
| dep | avg(salary) |
+-----------+-------------------+
| 咨询部 | 6.250000059604645 |
+-----------+-------------------+
order by 排序
完成排序
注意:可以使用聚合函数,哪怕没有明确group by
没有明确分组,就是以整张表作为大组
升序|降序 : asc | desc
#不指定顺序,默认为升序
select * from emp order by salary;
+----+---------+--------+------+--------+--------+-----------+-----------+
| id | name | gender | age | salary | area | port | dep |
+----+---------+--------+------+--------+--------+-----------+-----------+
| 11 | monkey | 女 | 28 | 1.2 | 山东 | 青岛 | 教职部 |
| 9 | ying | 女 | 36 | 1.2 | 安徽 | 芜湖 | 咨询部 |
| 4 | tank | 女 | 28 | 2.4 | 广州 | 广东 | 教学部 |
| 5 | jiboy | 男 | 28 | 2.4 | 江苏 | 苏州 | 教学部 |
| 3 | jerry | 女 | 30 | 3 | 江苏 | 张家港 | 教学部 |
| 10 | kevin | 男 | 36 | 5.8 | 山东 | 济南 | 教学部 |
| 14 | san2 | 男 | 30 | 6 | 上海 | 浦西 | 教学部 |
| 13 | san1 | 男 | 30 | 6 | 上海 | 浦东 | 咨询部 |
| 7 | owen | 男 | 28 | 8.8 | 安徽 | 宣城 | 教学部 |
| 6 | zero | 男 | 28 | 8.8 | 中国 | 黄浦 | 咨询部 |
| 12 | san | 男 | 30 | 9 | 上海 | 浦东 | 咨询部 |
| 2 | engo | 男 | 38 | 9.4 | 山东 | 济南 | 教学部 |
| 1 | yangsir | 男 | 42 | 10.5 | 上海 | 浦东 | 教职部 |
+----+---------+--------+------+--------+--------+-----------+-----------+
eg: order by age desc => 按照年龄降序
select * from emp order by age desc;
+----+---------+--------+------+--------+--------+-----------+-----------+
| id | name | gender | age | salary | area | port | dep |
+----+---------+--------+------+--------+--------+-----------+-----------+
| 1 | yangsir | 男 | 42 | 10.5 | 上海 | 浦东 | 教职部 |
| 2 | engo | 男 | 38 | 9.4 | 山东 | 济南 | 教学部 |
| 10 | kevin | 男 | 36 | 5.8 | 山东 | 济南 | 教学部 |
| 9 | ying | 女 | 36 | 1.2 | 安徽 | 芜湖 | 咨询部 |
| 13 | san1 | 男 | 30 | 6 | 上海 | 浦东 | 咨询部 |
| 12 | san | 男 | 30 | 9 | 上海 | 浦东 | 咨询部 |
| 14 | san2 | 男 | 30 | 6 | 上海 | 浦西 | 教学部 |
| 3 | jerry | 女 | 30 | 3 | 江苏 | 张家港 | 教学部 |
| 6 | zero | 男 | 28 | 8.8 | 中国 | 黄浦 | 咨询部 |
| 5 | jiboy | 男 | 28 | 2.4 | 江苏 | 苏州 | 教学部 |
| 11 | monkey | 女 | 28 | 1.2 | 山东 | 青岛 | 教职部 |
| 4 | tank | 女 | 28 | 2.4 | 广州 | 广东 | 教学部 |
| 7 | owen | 男 | 28 | 8.8 | 安徽 | 宣城 | 教学部 |
+----+---------+--------+------+--------+--------+-----------+-----------+
需求:将部门按照部门平均工资降序方式排序
select dep,avg(salary) from emp group by dep order by avg(salary) desc;
+-----------+-------------------+
| dep | avg(salary) |
+-----------+-------------------+
| 咨询部 | 6.250000059604645 |
| 教职部 | 5.850000023841858 |
| 教学部 | 5.400000027247837 |
+-----------+-------------------+
limit 限制
限制最终结果的数据行数
注意:limit 只与数字结合使用
使用方法:
limit 1:只显示一行数据
limit 6, 5: 从第6+1行开始显示5条数据(索引从0开始)
需求:
获得薪资最高的人的一条信息
select * from emp order by salary desc limit 1;
+----+---------+--------+------+--------+--------+--------+-----------+
| id | name | gender | age | salary | area | port | dep |
+----+---------+--------+------+--------+--------+--------+-----------+
| 1 | yangsir | 男 | 42 | 10.5 | 上海 | 浦东 | 教职部 |
+----+---------+--------+------+--------+--------+--------+-----------+
1 查出所有员工的名字，薪资,格式为
<名字:egon> <薪资:3000>
select concat("<名字:",name,">") "name" , concat("<薪资:",salary,">") "salary" from emp limit 2;
+------------------+---------------+
| name | salary |
+------------------+---------------+
| <名字:yangsir> | <薪资:10.5> |
| <名字:engo> | <薪资:9.4> |
+------------------+---------------+
2 查出所有的岗位（去掉重复）
select distinct dep from emp;
+-----------+
| dep |
+-----------+
| 教职部 |
| 教学部 |
| 咨询部 |
+-----------+
3 查出所有员工名字，以及他们的年薪,年薪的字段名为annual_year
select name,salary*12 "annual_year" from emp;
+---------+--------------------+
| name | annual_year |
+---------+--------------------+
| yangsir | 126 |
| engo | 112.79999542236328 |
| jerry | 36 |
| tank | 28.80000114440918 |
| jiboy | 28.80000114440918 |
| zero | 105.60000228881836 |
| owen | 105.60000228881836 |
| ying | 14.40000057220459 |
| kevin | 69.60000228881836 |
| monkey | 14.40000057220459 |
| san | 108 |
| san1 | 72 |
| san2 | 72 |
+---------+--------------------+
-
查看岗位是教学部的员工姓名、年龄
select name,age from emp where dep ="教学部"; -
查看岗位是教学部且年龄大于30岁的员工姓名、年龄
select name,age,dep from emp where dep ="教学部" and age>30; -
查看岗位是教学部且薪资在2-6范围内的员工姓名、年龄、薪资
select name,age,salary from emp where salary between 2 and 6; -
查看岗位描述不为NULL的员工信息
select * from emp where dep != null; -
查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
select name,age,salary from emp where salary in (10000,9000,30000); -
查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
select name,age,salary from emp where dep = "teacher" and salary not in(10000,9000,30000); -
查看岗位是teacher且名字是jin开头的员工姓名、年薪
select name,salary from emp where dep = "teacher" and regexp "^jin.*" -
查询岗位名以及岗位包含的所有员工名字
select dep,group_concat(name) from emp group by dep; -
查询岗位名以及各岗位内包含的员工个数
select dep,count(name) from emp group by dep; -
查询公司内男员工和女员工的个数
select gender,count(gender) from emp group by gender; -
查询岗位名以及各岗位的平均薪资
select dep,avg(salary) "平均薪资" from emp group by dep; -
查询岗位名以及各岗位的最高薪资
select dep,max(salary) "最高薪资" from emp group by dep; -
查询岗位名以及各岗位的最低薪资
select dep,min(salary) "最低薪资" from emp group by dep; -
查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
select gender "性别",avg(salary) "平均薪资" from emp group by gender; -
查询各岗位内包含的员工个数大于2的岗位名、岗位内包含员工名字、个数
select dep,group_concat(name)"name",count(id)"count" from emp group by dep having count(id) > 2;
+-----------+---------------------------------------+-------+
| dep | name | count |
+-----------+---------------------------------------+-------+
| 咨询部 | san1,san,ying,zero | 4 |
| 教学部 | san2,kevin,owen,jiboy,tank,jerry,engo | 7 |
+-----------+---------------------------------------+-------+ -
查询各岗位平均薪资大于 4 的岗位名、平均工资
select dep,avg(salary) from emp group by dep having avg(salary) > 4; -
查询各岗位平均薪资大于4且小于8的岗位名、平均工资
select dep,avg(salary) from emp group by dep having avg(salary) >4 and avg(salary)<8; -
查询所有员工信息，先按照age升序排序，如果age相同则按照薪资降序排序
select * from emp order by age, salary desc; -
查询各岗位平均薪资大于4的岗位名、平均工资,结果按平均薪资升序排列
select dep ,avg(salary) from emp group by dep order by avg(salary); -
查询各岗位平均薪资大于14的岗位名、平均工资,结果按平均薪资降序排列
select dep ,avg(salary) from emp group by dep order by avg(salary) desc;
