数据库04

目录

表相关SQL语句

1.修改表名
  alter table 原表名 rename 新表名;
2.添加字段
  2.1 末尾添加字段
      alter table 表名 add 添加的字段名 数据类型(数字) 约束条件;
  2.2 在首行添加字段
      alter table 表名 add 添加的字段名 数据类型(数字) 约束条件 first;
  2.3 某个字段后面添加字段
      alter table 表名 add 添加的字段名 数据类型(数字) 约束条件 after 字段名;
3.修改字段
  3.1 修改字段或数据类型(不改字段也必须写上该字段)
      alter table 表名 change 旧字段 新字段 数据类型(数字) 约束条件;
  3.2 修改字段数据类型
  	  alter table 表名 modify 字段名 新的字段类型(数字) 约束条件;
4.删除字段
  alter table 表名 drop 字段名;

数据查询关键字

表查询关键字

1.数据准备(直接拷贝)
	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);

查询-select与from

1.查询
  select */查询字段 from 表名
2.关键字含义
  select 自定义查询表中字段对应的数据
  from 指定操作的对象(具体的表)
注：SQL语句的关键字编写顺序与执行顺序可能是不一致的 
    先执行from或的具体的表数据然后执行select确定查询字段
3.练习
  select * from emp         # emp表中所有数据
  select name from emp      # emp表中name字段数据

筛选-where

1.筛选:确定查询的表 然后根据所需条件筛选 最后获得想要的数据内容
  select */筛选字段 from 表名 where 筛选条件
2.关键字含义
  where 筛选条件
3.模糊筛选查询
  3.1 通配符：% _
  3.2 使用:搭配筛选条件查询
      % ：表示任何字符出现的任意次数
      _ ：表示匹配单个字符
  select * from emp where name like '%o%';
  select * from emp where name like '___';
注：like操作：没有通配符时相当于=
  select * from emp where name like 'tom'  # like='tom'
 4.练习
 4.1 查询年龄在20-60岁之间的员工薪资信息
    select name,age,salary from emp where age>=20 and age<=60;
 4.2 查询薪资为20000或30000的员工信息
    select name,salary from emp where salary=20000 or salary=30000;
 4.3 查询薪资在10000-20000之间的员工
    select name,salary from emp where salary between 10000 and 20000;
 4.4 查询薪资不在10000-20000之间的员工
    select name,salary from emp where salary not between 10000 and 20000;
 4.5 查询姓名由三个字符组成的员工
    select name,salary from emp where name like '___';
 	select name,salary from emp where char_length(name)=3;
 4.6 查询薪资不是10000 20000 30000的员工
     select name,salary from emp where salary not in (10000,20000,30000);
 4.7 查询字段为NULL的不能用等号 只能用is(如查询岗位描述为空的员工)
     select name,post_comment from emp where post_comment is NULL;
     select name,post_comment from emp where post_comment is not NULL;  # 不为空
 注：针对筛选关键字-where与and、or、not、not in、is、is not的实际应用

分组-group by

1.分组:确定分组的表 再确定按照什么字段分组
  select 分组字段 from 表名 group by 分组字段
 注：分组之后我们研究的对象应该是以组为单位 
     select后面可以直接填写的字段名只能是分组的依据(其他字段需要借助于一些方法才可以获取)
    命令：set global sql_mode='strict_trans_tables,only_full_group_by';
    select 分组字段，展示字段1,展示字段2 from 表名 group by 分组字段
eg：select post,name,age from emp group by post;
2.配合分组使用的有聚合函数
  max    最大值 
  min    最小值
  sum    求和
  count  计数
  avg    平均值
  注：在显示的时候可以给字段取别名
      select post as '部门',max(salary) '最高工资' from emp group by post;
  2.1.练习
     1 每个部门的最高工资
         select post as '部门',max(salary) '最高工资' from emp group by post;
     2 每个部门的最低工资
         select post as '部门',min(salary) '最高工资' from emp group by post;
     3 每个部门的薪资合计
         select post as '部门',sum(salary) '最高工资' from emp group by post;
     4 每个部门的人数
	     select post as '部门',count(name)  '人数' from emp group by post;
 3.group_concat(分组之后用)不仅可以用来显示除分组外字段还有拼接字符串的作用
     select 分组字段 group_concat(显示分组中所有字段/拼接/连接字段) from 表名 group by 分组字段
   3.1 练习
      1.查询部门中的所有员工姓名
        select post,group_concat(name) from emp group by post;
      2.给部门中所有的员工添加统一后缀
        select post,group_concat(name,'_x') from emp group by post;
      3.给部门中数据之间添加分割符方便查看
        select post as '部门',group_concat(name,':',salary) as '姓名：薪资' from emp group by post;
      4.查询部门中所有的薪资
        select post as '部门',group_concat(salary) as '薪资' from emp group by post;

过滤-having

1.过滤：having与where的功能其实是一样的 都是用来删选数据的
   select 显示字段 from 表名 where 筛选条件 group by 分组字段(分组)
2.区别：where用于分组之前删选
	   having用于分组之后筛选
3.练习
  3.1 统计各部门年龄在30岁以上是我员工的平均工资，并且保留平均工资大于10000的部门
    select post,avg(salary) from emp where age > 30 group by post;  # 获得部门大于30岁的平均工资
    select post,avg(salary) from emp where age > 30 group by post 
    having avg(salary)>10000; # 删选出平均值大于10000

去重-distinct

1.去重：数据必须一模一样才可以去重
  select distinct 有重复数据的字段 from 表名
2.练习
  2.1 获得没有重复年龄信息
    select distinct age from emp;

排序-order by

1.排序：获得数据对所需数据进行升序或降序排序(order by默认升序)
  1.1 升序：select * from 表名 order by 排序字段 (asc);
  1.2 降序：select * from 表名 order by 排序字段 desc;
2.练习
  2.1 将薪资按照升序排序
    select * from emp order by salary;
    select * from emp order by salary asc;
  2.2 将薪资按照降序排序
    select * from emp order by salary desc;
  2.3 将年龄升序排序之后将薪资降序排序
    select * from emp age order by age asc,salary desc;
    select * from emp age order by age,salary desc;
  2.4 统计各部门年龄在10岁以上的员工的平均工资 并且保留平均工资大于1000的部门 然后对平均工资排序
     select post,avg(salary) from emp where age>10 group by post; # 各部门年龄大于10岁的平均工资
    select post,avg(salary) from emp where age>10 group by post 
    having avg(salary)>1000;    # 保留平均值大于1000的部门
    select post,avg(salary) from emp where age>10 group by post 
    having avg(salary)>1000 order by avg(salary) asc;  # 进行升序排序

分页-limit

1.分页：显示展示的条数 方便查看
  select * from 表名 limit 条数
2.练习
  2.1 查询薪资最高的人的详细信息
    select * from emp order by salary desc;   # 先将薪资降序排序
    select * from emp order by salary desc limit 1;  # 获得第一条数据
  2.2 按照不同条数分页展示数据
    select * from emp limit 0,5;
    select * from emp limit 5,5;

正则-regexp

1.正则：通过正则表达式所需数据(正则表达式：通过一些符号数字及字母的组合从字符串中匹配出所需数据)
  select * from 表名 where 筛选字段 regexp 正则表达式
2.练习
  2.1 获得员工姓名中j开头的员工信息
  select * from emp where name regexp '^j.*(n|y)$';

多表查询

1.子查询：将一个表查询的结果括号括起来当做另一条SQL语句的条件
2.连表查询：将关系表做匹配连接到一起 然后筛选出所需数据 
#建表
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);

子查询

1.子查询：将一个表查询的结果括号括起来当做另一条SQL语句的条件
2.练习
 2.1 查询jason的部门名称
    select dep_id from emp1 where name='jason';  # 拿到jason的部门编号
    select name from dep1 where id=(select dep_id from emp1 where name='jason');  # 获得部门名称

连表查询

1.连表查询：将关系表做匹配连接到一起 然后筛选出所需数据
2.方式一：直接拼接(会产生笛卡尔积)-不会使用这种方法 效率太低
select * from 表名1，表名2;
 2.1 练习-查询员工对应的部门名称
    select * from emp1,dep1; # 将两个表拼接到一起 =select * from emp1 inner join dep1;
    select * from emp1,dep1 where emp1.dep_id=dep1.id;  # 消除笛卡尔积
    select emp1.name,dep1.name from emp1,dep1 where emp1.dep_id=dep1.id;
3.方式二：连表专门语法
   inner join   内连接-只拼接两边都有的字段数据
   left join    左连接-以左表为基准 展示所有的数据 没有对应则NULL填充
   right join   右连接-以右表为基准 展示所有的数据 没有对应则NULL填充
   union        全连接-展示所有的数据(将左右表连接到一起)
 select * from 表名1 left join 表名2 on 表1关联数据=表2关联数据
 select * from 表名1 right join 表名2 on 表1关联数据=表2关联数据 
 select * from 表名1 left join 表名2 on 表1关联数据=表2关联数据  union 
 select * from 表名1 right join 表名2 on 表1关联数据=表2关联数据 
3.1 练习-查询员工对应的部门名称
   select * from emp1 left join dep1 on emp1.dep_id=dep1.id;

作业

1. 查询岗位名以及岗位包含的所有员工名字
  select post from emp group by post;   # 查询岗位名 
  select post,group_concat(name) from emp group by post;   # 查询岗位中所有的员工姓名
2. 查询岗位名以及各岗位内包含的员工个数
  select post from emp group by post;   # 查询岗位名 
  select post,count(name) from emp group by post;   # 查询岗位中的员工个数 
3. 查询公司内男员工和女员工的个数
  select gender from emp group by gender;
  select gender,count(name) from emp group by gender;
4. 查询岗位名以及各岗位的平均薪资
   select post from emp group by post;   # 查询岗位名
   select post,avg(salary) from emp group by post having avg(salary);
5. 查询岗位名以及各岗位的最高薪资
    select post from emp group by post;   # 查询岗位名
    select post,max(salary) from emp group by post;
6. 查询岗位名以及各岗位的最低薪资
   select post from emp group by post;   # 查询岗位名
   select post,min(salary) from emp group by post;
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
   select gender from emp group by gender;
   select sex,avg(salary) from emp group by sex having avg(salary);
8. 统计各部门年龄在30岁以上的员工平均工资
    select post from emp group by post;  
    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; # 各部门年龄大于10岁的平均工资
    select post,avg(salary) from emp where age>10 group by post 
    having avg(salary)>1000;    # 保留平均值大于1000的部门
    select post,avg(salary) from emp where age>10 group by post 
    having avg(salary)>1000 order by avg(salary) asc;  # 进行升序排序
 posted on 2022-08-17 23:06  拾荒菇凉  阅读(71)  评论(0)    收藏  举报