操作表的SQL语句补充
-
alter
1.修改表名
alter table 表名 rename 新表名；
2.新增字段
alter table add 字段名 字段类型(数字) 约束条件
3.新增指定字段排在第一位
alter table add 字段名 字段类型(数字) 约束条件 first;
4.新增字段指定字段的位置
alter table add 字段名 字段类型(数字) 约束条件 after 已经存在字段；
5.修改字段
alter table 表名 change 旧字段 新字段 字段类型（数字） 约束条件；
6.删除字段
alter table 表名 drop 字段名；
-
alter实战例子
create table T1_alter(id int primary key auto_increment,name varchar(32));
#修改表名
alter table T1_alter rename test_alter;
#新增字段
alter table test_alter add age int;
#新增字段排在第一位
alter table test_alter add name first;
#新增字段指定位置
alter table test_alter add sex varchar(32) default 'male' after name;
#修改字段
alter table test_alter modify name varchar(32);
#删除字段
alter table drop 字段名；
mysql关键字查询书写顺序
select => from => where => Group by => having =>order by => limit
select 与from
-
SQL语句的关键字编写顺序与执行顺序是不一致的!!!
eg: select name from emp;
肯定是先支持from确定表 之后执行select确定字段
编写SQL语句针对select和from可以先写个固定模板
select * from 表名 其他操作
select后的字段可能是实际的 也可能是通过SQL动态产生的 所以可以先用*占位最后再修改
-
数据练习
-
查询练习以以下数据为主
#创建测试库 create table emp( 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 );#插入数据 insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values ('jason','male',18,'20170301','浦东第一帅形象代言',7300.33,401,1), ('tom','male',78,'20150302','teacher',1000000.31,401,1), ('kevin','male',81,'20130305','teacher',8300,401,1), ('tony','male',73,'20140701','teacher',3500,401,1), ('owen','male',28,'20121101','teacher',2100,401,1), ('jack','female',18,'20110211','teacher',9000,401,1), ('jenny','male',18,'19000301','teacher',30000,401,1), ('sank','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);
where
查询关键字之where筛选
查询条件越多，查询出来的记录就会越少。因为，设置的条件越多，查询语句的限制就更多，能够满足所有条件的记录就更少。为了使查询出来的记录正是自己想要的，可以在 WHERE 语句中将查询条件设置的更加具体。
| 查询条件 | 关键字 |
|---|---|
| 比较 | 比较运算符和逻辑运算符 |
| 确定范围 | between and关键字查询 |
| 确定集合 | in not in |
| 空值 | is null |
| 多条件查询（多重运算） | and ,or, XOR |
| 字符匹配 | like,not like 通配符%，_ |
-
1.查询id 大于3小于等于6的数据
select * from emp where id > 3 and id <=6; -
2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary = 2000 or salary =18000 or salary = 17000; #简写 select * from emp where salary in (2000,18000,17000); -
3.查询员工姓名中包含o字母的员工姓名和薪资
select name,salary from emp where like '%o%'; -
4.查询员工姓名是由四个字符组的员工姓名与其薪资
select name,salary from emp where name like '____'; #一个下划线是代表一个字符 select name,salary from emp where name like char_lenth(name) = 4; -
5.查询id小于3或者大于6的数据
select * from emp where id <3 or id>6; select * from emp where id not between 3 and 6; -
6.查询薪资不在20000.18000,17000的数据
select * from emp where salary not in (20000,18000,17000); -
7.查询岗位描述为空的员工名与岗位名 针对null 不能用等号只能用is
select name,post from emp where post_comment =null; #不能用等号
select name,post from emp where post_comment is NULL;
select name,post from emp where post_comment is not null; #查询岗位描述不为空的
group by
分组：按照一些指定的条件将单个单个的数据分为一个个整体
分组之后我们研究的对象应该是以组为单位，不应该再直接获取单个数据项，如果获取了应该直接报错，
select 后面直接填写的字段只能是分组的一句（其它字段需要借助一些方法才可以获取）
set global sql_mode = 'strict_trans_tables,only_full_group_by';
| group by配合分组常见使用的聚合函数 |
|---|
| max/最大值 |
| min/最小值 |
| sum/总和 |
| count/计数 |
| avg/平均 |
-
获取每个部门的最高工资 #表中post字段是组
以组为单位统计组内数据>>>聚合查询(聚集到一起合成为一个结果)
每个部门的最高工资 # 也可以取别名 as，as可以省略
select post,max(salary) from emp group by post; select post as '部门'，max(salary) as '最高工资' from emp group by post; -
每个部门的最低工资
select post,min(salary) from emp group by post; -
每个部门的平均工资
select post,avg(salary) from emp group by post; -
每个部门的工资总和
select post,sum(salary) from emp group by post; -
每个部门的人数
select post,count(id) from emp group by post; -
查询分组之后的部门名称和每个部门下所有的学生姓名
-
group_concat（分组之后用）不仅可以用来显示除分组外字段还有拼接字符串的作用
select post,group_concat(name) from emp group by post;
select post,group_concat(name,"_SB") from emp group by post;
select post,group_concat(name,": ",salary) from emp group by post;
select post,group_concat(salary) from emp group by post;
having
where 与having 的功能其实是一样的，都是用来筛选数据
只不过where 用于分组之前的筛选，而having 用于分组之后的筛选
为了认为的区分，所以叫where是筛选，having是过滤
1.统计各部门年龄再30岁以上的员工平均工资，并且保留平均工资大于10000的部门
select post,avg(salary) from emp
where age >= 30
group by post
having avg(salary) >1000;
distinct
去重，去重前提数据必须是一模一样
select distinct age from emp;
order by
排序，升序或者降序
| order by关键字 |
|---|
| asc 默认升序 |
| desc默认降序 |
- 工资升序排列
select * from emp order by salary asc;
- 工资降序排列
select * from emp order by salary desc;
- 年龄降序
select * from emp order by age desc;
- 先按照age降排序，在年轻相同的情况下再按照薪资升序排
select * from emp oreder by age desc,salary asc;
- 统计各部门年龄在10以上的员工平均工资，并且保留平均工资大于1000的部门，然后对工资进行排序
mysql> select post,avg(salary) from emp where age>10 group by post having avg(salary) > 1000 order by salary;
limit
| limit关键字 |
|---|
| limit A ；A 这里代指数字 |
| limit A，B； （A,B必须为整数）A表示起始位，B表示展示几条 |
- 展示3条数据
select * from emp limit 3;
- 从第4条展示，并且只展示5条数据
select * from emp limit 3,5;
regexp正则
select * from emp where name regexp '^j.*(n|y)$';
多表查询
子查询
将一张表的查询结果括号括起来当做另外一条SQL语句的条件
eg:类似以日常生活中解决问题的方式
第一步干什么
第二步基于第一步的结果在做操作 ...
连表操作
先将所有涉及到结果的表全部拼接到一起形成一张大表 然后从大表中查询数据
#创建表
create table dep1(
id int primary key auto_increment,
name varchar(20)
);
create table emp1(
id int primary key auto_increment,
name varchar(20),
gender enum('male','female') not null default 'male',
age int,
dep_id int
);
##插入测试数据
insert into dep1 values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'安保')
;
insert into emp1(name,gender,age,dep_id) values
('jason','male',18,200),
('dragon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);
子查询
查询jason的部门名称
1.先获取jason的部门编号
select dep_id from emp1 where name = 'jason'; # 200
2.根据部门编号获取部门名称
select name from dep1 where id = 200;
#子查询
mysql> select name from dep1 where id = (select dep_id from emp1 where name = 'jason');
连表操作
select * from emp1,dep1; #笛卡尔积
我们不会用笛卡尔积来求数据，效率太低，连表有专门的语法
| 名称 | 方法 |
|---|---|
| inner join | 内连接 |
| left join | 左连接 |
| right join | 右连接 |
| union | 全连接 |
select * from emp1 left join dep1 on emp1.dep_id = dep1.id;
select * from emp1 right join dep1 on emp1.dep_id = dep1.id;
练习题
2.完成下列练习题
2.完成下列练习题
1. 查询岗位名以及岗位包含的所有员工名字
select post,group_concat(name) from emp group by post;
2. 查询岗位名以及各岗位内包含的员工个数
select post,count(post) from emp group by post;
3. 查询公司内男员工和女员工的个数
select sex,count(sex) from emp group by sex;
4. 查询岗位名以及各岗位的平均薪资
select post,avg(salary) from emp group by post;
5. 查询岗位名以及各岗位的最高薪资
select post,max(salary) from emp group by post;
6. 查询岗位名以及各岗位的最低薪资
select post,min(salary) from emp group by post;
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
select sex,avg(salary) from emp group by sex;
8. 统计各部门年龄在30岁以上的员工平均工资
select post,avg(salary) from emp where age>30 group by post;
9. 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
select post,avg(salary) from emp where age > 10 group by post having avg(salary)>10000 order by avg(salary);