数据库之表的操作与查询关键字

操作表的SQL语句

1.修改表名：

alter table 表名 rename 新表名;

2.新增字段

alter table 表名 add 字段名 字段类型(数字) 约束条件;
alter table 表名 add 字段名 字段类型(数字) 约束条件 after 已存在的字段;
alter table 表名 add 字段名 字段类型(数字) 约束条件 first;
○ after 作用是将新字段添加到某个已有字段后面
○ first 作用在表的开头添加新的字段

3.修改字段

alter table 表名 change 旧字段 新字段 字段类型(数字) 约束条件;
ater table 表名 modify 字段名 新的字段类型(数字) 约束条件;
○ modify 只能修改字段的类型

4.删除字段

alter table 表名 drop 字段名;

查询关键字之select与from

select:

自定义查询表中字段对应的数据

from:

指定要操作的对象(哪张表)
select * from 表名 其他操作
ps：SQL语句执行顺序先from确定表再select确定字段

查询关键字之where筛选

1.where使用方法

select * from 表名 where 筛选条件;

2.具体使用

# 查询表中年龄大于等于18小于30的详细数据
select * from user where age >= 18 and age <= 30;
# 查询表中名字中有'明'字的人员名字与年龄
select name,age from user where name like '%明%';
'''
like为模糊查询 提供了四种匹配模式：
% 表示任意0个或多个字符。可匹配任意类型和长度的字符
_ 表示任意单个字符。匹配单个任意字符
[] 表示括号内所列字符中的一个（类似正则表达式）
[^] 表示不在括号所列之内的单个字符
'''
# 查询表中年龄为空的员工姓名
select name from user where age is NULL;
ps:查询空不能用=，只能用is。
1
2
3
4
5
6
7
8
9
10
11
12
13
14

查询关键字之group by分组

1.group by 使用方法

将数据按指定的条件进行分组成一个个的整体
select 字段 from 表名 where 筛选条件 group by 分组条件

2.具体代码使用

# 有一张员工信息表staff
# 查询每个部门的最高工资
select post,max(salary) from staff group by post;
ps:还可以取别名
select post as '部门',max(salary) as '最高工资' from staff group by post;
# 查询部门名称和每个部门下所有的人员名称
select post,group_concat(name) from staff group by post;
# 查询部门名称和每个部门下所有的人员名称并加一个称号
select post.group_concat(name,"部门精英") from staff group by post;
1
2
3
4
5
6
7
8
9

3.知识小结

聚合函数配合分组使用：
max：最大值
min： 最小值
sum：求和
count：计数
avg： 求平均
group_concat使用:
用在分组之后不仅可以显示除分组外字段还有拼接字符串的作用
小Tips：当题目中出现 每个、平均、最大、最小等字的时候需要使用分组。

查询关键字之having过滤

1.having作用与用法

having与where的功能一样都是筛选数据，having用于分组之后的数据筛选where用于分组之后的数据筛选。

2.具体使用方式

# 统计每个部门年龄在30以上的员工的平均工资，
# 并保留平均工资大于10000的部门
select post,avg(salary) from staff 
		where age >= 30 
		group by post 
		having avg(salary) > 10000;
1
2
3
4
5
6

查询关键字之order by排序

1.order by作用与用法

order by用来对数据进行排序默认为升序
order by salary asc; 升序
order by salary desc; 降序

2.使用方法

# 统计每个部门年龄在30岁以上的员工平均工资，
# 并且保留平均工资大于10000的部门再对平均工资进行排序
select past,avg(salary) from staff
		where age > 10
		group by post
		having avg(salary) > 1000
		order by avg(salary);
1
2
3
4
5
6
7

查询关键字之limit分页

1.limit作用与用法

当数据库查询记录有几万、几十万时全表查询会导致查询数据库崩溃，这时mysql 已经给我们提供了一个功能，limit可以限制展示条数查询效率高。
select * from emp limit 条数;

2.具体使用

# 先排序出年龄大降序再用limit展示第一条就可查询到年龄最大人的详细信息
select * from staff
	order by salary desc
	limit 1;
# 从第2条数据开始查询五条
select * from staff limit 2,5;	
1
2
3
4
5
6

查询关键字之regexp正则

1.regexp作用与用法

对mysql用正则表达式的匹配，MySQL中使用 REGEXP 操作符来进行

# 查询名字中以a开头或以e结尾的数据信息
select * from staff where name '^a|e$'
1
2

2.知识小结

^ : 匹配字符串的开始位置
$: 匹配字符串的结束位置
. : 匹配出\n外的所有单个字符
详细参考：正则表达式

多表查询

1.子查询思路

如使用子查询先获取一张用户信息表中的编号，然后再通过编号在部门表中查询对应的部门
select name from staff where id = (select dep_id from user where name = 'aa';

2.连表查询

  笛卡尔积：`select * from user,staff;`
1

ps:用这种方法将两张表连起来会造成笛卡尔积当两张表数据都很大时就会奔溃，效率低

1.inner join：内连接 只拼接两表都有的字段数据
	select * from user inner join staff on user.staff_id = staff.id;
2.left join：左连接 以左边表为基准展示所有数据没有对应的用NULL填充
	select * from user left join staff on user.staff_id = staff.id;
3.right join：右链接 以右边表为基准展示所有数据没有对应的用NULL填充
	select * from user right join staff on user.staff_id = staff_id;
4.union：全链接 将两张表链接没有对应的全部用NULL填充
	select * from user left join staff on user.staff_id = staff.id
	union
	select * from user right join staff on user.staff._id = staff.id;
1
2
3
4
5
6
7
8
9
10

练习测试

1. 查询岗位名以及岗位包含的所有员工名字
   2. 查询岗位名以及各岗位内包含的员工个数
   3. 查询公司内男员工和女员工的个数
   4. 查询岗位名以及各岗位的平均薪资
   5. 查询岗位名以及各岗位的最高薪资
   6. 查询岗位名以及各岗位的最低薪资
   7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
   8. 统计各部门年龄在30岁以上的员工平均工资
   9. 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序