mysql
net start mysql # 启动mysql服务
net stop mysql # 停止mysql服务
mysql -u root -p # 连接服务端
mysql -h 127.0.0.1 -P 3306 -uroot -p
show databases; # 查看所有库名
\c # 取消错误命令
quit,exit #退出
tasklist #查看当前进程
tasklist |findstr mysqld # 查看指定进程
tasklist /F /PID PID号 #杀死具体进程
services.msc #查看当前运行进程数
mysql --install #将mysql制作成系统服务
mysql --remove #移除mysql系统服务
mysqladmin -uroot -p 原密码 password 新密码
# 设置密码
set password = password("123456")
# 去除密码
set password = password("")
# 查询当前登录的用户是谁
select user()
"""8.0 之前版本的语法"""
# 给具体某个ip设置账号密码,用来访问数据库(公网ip)
create user 'ceshi100'@'192.168.65.1' identified by '333';
# 给具体某个网段下的所有ip设置共同的账号密码,用来访问数据库(公网ip) %代表任意的ip 0~255
create user 'ceshi101'@'192.168.65.%' identified by '444';
# 所有的ip都可以登录到服务器数据库中
create user 'ceshi102'@'%' identified by '555';
"""8.0 版本的语法"""
-- 创建用户 （如果执行这条语句报错了：请参考问题二）
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypassword';
-- 给用户分配权限
grant all privileges on *.* to 'myuser'@'%' ;
-- 单独授予某种权限的写法;具体再详细的操作请参考其他文章
GRANT SELECT ON oilsystem.input TO 'myuser'@'%';
-- 刷新权限，使操作生效
flush privileges;
# 刷新权限,立刻生效
flush privileges
# 查询当前登录的用户是谁
select user()
# 查看哪个ip哪个用户的权限
show grants for 'ceshi102'@'%';
# 主要权限
select 查看数据表的权限
insert 插入数据表的权限
update 更新数据表的权限
delete 删除数据表的权限
drop 删除数据库的权限
* 代表所有
"""
# all 代表所有权限
grant select,drop on *.* to 'ceshi102'@'%' identified by "333";
# 移除权限
grant all on *.* to 'ceshi102'@'%' identified by "333";
# 移除权限单个
revoke drop on *.* from 'ceshi102'@'%';
# 移除权限单个所有
revoke all on *.* from 'ceshi102'@'%'
# 删除用户
drop user 'ceshi102'@'%'
# 刷新权限,立刻生效
flush privileges
# 创建一个数据库
create database db0824 charset utf8;
# 查看所有数据库
show databases;
# 查看建立数据库的语句
show create database db0824 # CREATE DATABASE `db0824` /*!40100 DEFAULT CHARACTER SET utf8 */
#改编码格式
alter database db0824_1 charset gbk
#删除数据库
drop database db0824_1;
# (2) 操作[数据表] (文件)
# 先选择数据库
use 数据库名;
# 添加表 (字段名1 类型1 ,字段名2 类型2 , ... ... )
create table t1(id int , name char);
# 查看所有表
show tables;
# 查看建表语句 配合\G 可以垂直显示
show create table t1;
# 查看表结构
desc t1;
# modify 只能改变数据类型
alter table t1 modify name char(5);
# change 连字段名 + 类型一起改变
alter table t1 change name newname char(4);
# add 添加字段
alter table t1 add age int;
# drop 删除字段 column列
alter table t1 drop column age;
# rename 更改表名
alter table t1 rename t1_2
#删除表
drop table t1_2;
#删除bai表数据
delete from t1_2; id 会继承
#清空表
truncate table t1_2; id会清空 删除后不记录mysql日志，不可以恢复数据。
# (3) 操作[记录] (文件中的数据)
# 一次插入一条数据
'''insert into 表名(字段1,字段2,...) values(值1,值2, .... )'''
insert into t1_1(id,name) values(1,"xboy1");
# 一次插入多条数据
insert into t1_1(id,name) values(2,"xboy2"),(3,"xboy3"),(4,"xboy4");
# 不指定具体字段插入(默认把所有字段对应的值插一遍)
insert into t1_1 values(5,'xboy5');
# 指定单个字段插入数据
insert into t1_1(name) values('xboy6');
# * 代表所有
select * from t1_1;
# 指定字段进行查询
select id,name from t1_1;
# update 表名 set 字段=值 where 条件
update t1_1 set name="王闻你真帅" where id = 1
#切记更改时,加上条件,否则全都改掉了
update t1_1 set name="王闻你真帅"
# 指定id=2的这条数据删除
delete from t1_1 where id = 1
# 如果不加条件,删除所有数据
delete from t1_1
# 重置数据表 (删除数据 + 重置id)
truncate table t1_1;
# 整型
tinyint 1个字节 有符号(-128 ~ 127) 无符号(unsigned) (0 ~ 255) 小整型值
int 4个字节 有符号(-21亿 ~ 21亿 左右) 无符号(unsigned) (0 ~ 42亿) 大整型值
# 浮点型
float(255,30) 单精度
double(255,30) 双精度
decimal(65,30) 金钱类型,使用字符串的形式保存小数
"""默认存在四舍五入"""
"""float 默认小数保留5位,double小数位截取16位,decimal(10,0) 默认保留整数,存在四舍五入"""
# 字符串
char 字符长度 255个
varchar 字符长度 21845个 (注意:总字节数不超过65535)
char(11) 定长 : 固定开辟11个字符长度的空间(手机号,身份证号,银行卡), 开辟空间速度上char速度更快
varchar(11) 变长 : 最多开辟11个字符长度的空间(评论,消息留言,地址), 开辟速度相较于char慢一点.
text 文本类型,存论文,小说,文章..
# mysql 中函数
select user()
select concat()
select concat(参数1,参数2,参数3 , ... ) 把所有的参数拼接在一起
# 枚举 和 集合
enum 枚举 : 从列出来的数据当中选一个(性别)
set 集合 : 从列出来的数据当中选多个(自动去重) 爱好
date YYYY-MM-DD 年月日 (结婚纪念日,节假日)
time HH:MM:SS 时分秒 (体育竞赛)
year YYYY 年份值 (历史,酒的年份)
datetime YYYY-MM-DD HH:MM:SS 年月日 时分秒 (用户登录时间,下单时间)
create table t1(d date , t time , y year , dt datetime);
insert into t1 values("2020-08-25","08:25:30","2020","2020-08-25 08:25:30")
insert into t1 values(now(),now(),now(),now())
select now() #获取当前时间
timestamp YYYYMMDDHHMMSS(时间戳) 自动更新时间(不需要手动,系统自动更新时间) 数据上一次的修改时间
create table t2(dt datetime , ts timestamp);
insert into t2 values(null,null);
insert into t2 values(20200825082530,20200825082530);
# ### part2 约束 : 对要进行编辑的数据进行类型上的限制,不满足限制条件的数据不允许操作.
unsigned 无符号
not null 不为空
default 设置默认值
unique 唯一约束,数据唯一不重复
primary key 主键,标记数据的唯一特征(唯一且不为空)
auto_increment 自增加1(一般配合主键使用, 或 unique进行自增)
zerofill 零填充(配合整型int使用) int(11) , 位数不够11位,拿0补充
foreign key 外键,把多张表通过一个关联字段联合在一起,(这个字段可以加外键)
# unsigned 无符号
create table t3(id int unsigned);
insert into t3 values(100);
insert into t3 values(-100); error
# not null 不为空
create table t4(id int not null , name varchar(255));
insert into t4 values(1,"宋云杰");
insert into t4 values(null,"宋云杰"); error
insert into t4(name) values('abc'); error
# default 设置默认值
create table t5(id int not null,name varchar(255) default '高雪峰');
insert into t5 values(1,null);
insert into t5(id) values(2);
# unique 唯一约束,数据唯一不重复
"""
索引:相当于字典的目录,通过索引可以加快查询的速度
UNI 唯一索引,允许插入NULL空值
"""
create table t6(id int unique , name varchar(255) default '戈隆');
insert into t6(id) values(1);
insert into t6(id) values(1); error
insert into t6(id) values(null); ok
insert into t6(id) values(null); ok
# primary key 主键,标记数据的唯一特征(唯一且不为空)
"""PRI 主键 非空且唯一 在一个表里只能有一个主键"""
create table t7(id int not null unique , name varchar(255) default '戈隆');
insert into t7 values(1,"1122")
insert into t7 values(null,"1122")
# primary key 创建主键
create table t8(id int primary key , name varchar(255) default '戈隆' );
insert into t8 values(1,"ppp")
# 两者同时存在 (优先显示primary key 作为主键,另一个设置成UNI 唯一索引)
create table t9(id int primary key , name char(3) not null unique);
# 一个表里只能有一个主键
create table t10(id int primary key , name char(3) primary key); error
# auto_increment 自增加1(一般配合主键使用, 或 unique进行自增)
create table t11(id int primary key auto_increment , name varchar(255) default '孙致和')
insert into t11 values(1,"李博伦")
insert into t11 values(null,"李亚")
insert into t11(id) values(null)
# 使用默认值自动插入
insert into t11 values()
# delete 只删除数据,id号保留
delete from t11 ;
# truncate 删除所有数据 + 重置id
truncate table t11;
# zerofill 零填充(配合整型int使用) int(11) , 位数不够11位,拿0补充
create table t12(id int(8) zerofill);
insert into t12 values(2)
insert into t12 values(123456789)
"""
主键索引 PRI 唯一索引 UNI 普通索引 MUL
"""
# 1.联合唯一约束(字段都设置成not null + unique 显示PRI , 联合在一起表达一种唯一性)
"""unique(字段1,字段2,字段3 ... ) 把多个字段拼在一起表达唯一的数据"""
create table t1_server(id int , server_name varchar(255) not null,ip char(15) not null,port int not null , unique(ip,port));
insert into t1_server values(1,"aaa","192.168.65.135",3306);
insert into t1_server values(1,"aaa","192.168.65.135",3306); error
insert into t1_server values(1,"aaa","192.168.65.135",443);
insert into t1_server values(1,"aaa","192.168.65.130",443);
# 2.联合唯一约束(字段不设置成not null)
create table t2_server(id int , server_name varchar(255) not null,ip char(15) ,port int , unique(ip,port));
insert into t2_server values(1,"aaa","192.168.65.135",3306);
insert into t2_server values(1,"aaa",null,null); # 注意点,允许插入多个空值;
# 3.联合唯一索引 和 主键 之间是否可以同时存在?
create table t3_server(id int , server_name varchar(255) not null,ip char(15) not null,port int not null , unique(ip,port));
alter table t3_server add primary key(id);
"""
unique(ip,port) 联合唯一索引
primary key(ip,port) 联合主键
这两个用法一模一样,区别:前者可以继续添加一个主键,后者不能再额外添加主键
主键可以是单个字段,也可以是联合主键,设置多个单字段做主键不行的.
"""
# foreign key 外键,把多张表通过一个关联字段联合在一起,(这个字段可以加外键) [可设置成联级更新和删除]
"""外键所关联的其他字段必须具有唯一属性 unique 或者 primary key"""
例子：
student1:
id name age address classid
1 yuanweizhuo 88 天堂 2
2 lihuling 99 地府 2
3 wangwen 18 包头 3
class1:
id classname datetime
1 python30 2020-4-28 10:10:10
2 python31 2020-7-9 10:10:10
3 python32 2020-9-5 10:10:10
# 创建class1
create table class1(id int , classname varchar(255))
# 删除索引
alter table class1 drop index id
# 添加索引
alter table class1 add unique(id);
# 创建student1
create table student1(
id int primary key auto_increment,
name varchar(255),
age int ,
classid int,
foreign key(classid) references class1(id)
);
# 添加数据
insert into class1 values(1,"python30");
insert into class1 values(2,"python31");
insert into class1 values(3,"python32");
insert into student1 values(null,"yuanweizhuo",88,2);
insert into student1 values(null,"lihuling",99,2);
insert into student1 values(null,"wangwen",18,3);
# 删除class1里面的python31这个班级 (报错删不掉,因为有其他数据关联该班级)
delete from class1 where id = 2;
# 需要先把关联的其他数据都删掉之后再删,才能成功
delete from student1 where id = 1;
delete from student1 where id = 2;
# 联级删除 联级更新 (谨慎使用)
"""
联级删除 on delete cascade
联级更新 on update cascade
"""
# 创建class2
create table class2(id int unique , classname varchar(255)) ;
# 创建student2
create table student2(
id int primary key auto_increment,
name varchar(255),
age int ,
classid int,
foreign key(classid) references class2(id) on delete cascade on update cascade
);
# 添加数据
insert into class2 values(1,"python30");
insert into class2 values(2,"python31");
insert into class2 values(3,"python32");
insert into student2 values(null,"yuanweizhuo",88,2);
insert into student2 values(null,"lihuling",99,2);
insert into student2 values(null,"wangwen",18,3);
# 联级删除
delete from class2 where id = 2
# 联级更新
update class2 set id = 100 where classname = "python32";
++++++++++++++++++++++++++++++++++++++++++++++++++
# ### part4 表与表之间的关系
(1) 一对一 : 表1 id z1 z2 z3 .. 表2 id z4 z5 z6 (可以设置z3为关联字段且唯一 , 关联表2中的唯一一个id)
(2) 一对多 或者 多对一 : 一个班级里面可以由多个学生,在学生表中创建一个关联字段,关联班级,把关联字段设置成外键,去存储班级的id
(3) 多对多 : 一个学生可以学习多个学科,一个学科也可以被多个学生学习
一本书可以被多个作者共同撰写,一个作者也可以写多本书.
xueke (表1)
id name
1 math
2 english
3 wuli
student (表2)
id name
1 wangwen
2 songyunjie
3 gaoxuefeng
# 表达多对多关系时,需要第三张关系表
relation (表3) 把xid 和 sid 这两个关联字段设置成外键,xid 关联xueke的id , sid关联student 的id
xid sid
1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3
++++++++++++++++++++++++++++++++++++++++++++++++++
# ### part5 存储引擎: 存储数据的一种结构方式
show engines; 查看所有的存储引擎
# 概念:
表级锁: 如果有人修改当前这个表,会直接上锁,其他用户无法进行修改,不能进行高并发.
行级锁: 如果有人修改当前这个表中的一条记录,当前这条数据会被锁定,其他数据仍然可以被修改,速度快,允许高并发
事务处理: 执行sql语句是哦,必须所有的操作全部成功,最终提交数据,否则数据回滚,回到刚开始没操作的那个状态.
begin : 开启事务
commit: 提交数据
rollback: 回滚数据
MyISAM : 支持表级锁(5.6版本前默认存储引擎)
InnoDB : 事务处理,行级锁,外键(5.6版本后默认存储引擎)
MEMORY : 把数据放在内存中,做一个临时的缓存
BLACKHOLE : 黑洞,产生binlog日志,不产生真实数据
用来同步主从数据库中的数据,场景发生在多服务器集群中 (一主一从,一主多从,主数据库:增删改,从数据库:查)
create table myisam1(id int , name varchar(255)) engine = MyISAM;
myisam1.frm 表结构
myisam1.MYD 表数据
myisam1.MYI 表索引
create table innodb1(id int , name varchar(255)) engine = InnoDB;
innodb1.frm 表结构
innodb1.ibd 表数据 + 表索引
create table memory1(id int , name varchar(255)) engine = MEMORY;
memory1.frm 表结构
没有数据文件的,因为所有的数据都临时存储在内存之中
create table blackhole1(id int , name varchar(255)) engine = BLACKHOLE;
blackhole1.frm 表结构
内存中不存储任何值
++++++++++++++++++++++++++++++++++++++++++++++++++
# ### 额外补充
# 关于约束的添加和删除
# 1 添加/删除 约束 not null
#alter table 表名 modify 字段名 类型
alter table t1 modify id int not null
alter table t1 modify id int
# 2 添加/删除 unique 唯一索引
# alter table 表名 add unique(id)
alter table t1 add unique(id)
alter table t1 drop index id
# 3 添加/删除 primary key
# alter table 表名 add primary key(id);
alter table t1 add primary key(id);
alter table t1 drop primary key;
# 4 添加/删除 foreign key 外键 (show create table student1 找到外键名字,然后再删)
alter table student1 drop foreign key student1_ibfk_1; #删除
alter table student1 add foreign key(classid) references class1(id) #添加
# ### part1 单表查询
#sql查询语句的完整语法
select .. from .. where .. group by .. having .. order by .. limit ..
# 一.where 条件的使用
"""功能: 对表中的数据进行过滤筛选"""
"""
1.判断的符号
= > >= < <= != <> 不等于
2.拼接天骄的关键字
and or not
3.查询范围区间 between
between 小值 and 大值 [小值,大值] 查询两者之间的这个范围所有数据
4.查询某个值在具体某个范围里 in
in(1,2,3,4)
5.模糊查询 like "%" "_" 通配符
like "%a" 匹配以a结尾的任意长度的字符串
like "a%" 匹配以a开头的任意长度的字符串
like "%a%" 匹配含有a字母的任意长度的字符串
like "_a" 个数一共是2个字符,必须以a结尾,前面字符随意
like "a__" 个数一共是3个字符,必须以a开头,后面字符随意
"""
# (1) 单条件的查询
# 查询部门是sale 的所有员工姓名
select emp_name from employee where post = "sale";
# (2) 多条件的查询
# 部门是teacher , 收入大于10000的所有数据
select * from employee where post="teacher" and salary > 10000;
# (3) between .. and ..
# 收入在1万~2万之间的所有员工姓名和收入
select emp_name,salary from employee where salary between 10000 and 20000;
# 收入不在1万~2万之间的所有员工姓名和收入
select emp_name,salary from employee where salary not between 10000 and 20000;
# (4) null 关键字 在查询时候,需要使用is进行判断 ,不能用=
select * from employee where post_comment = null;
select * from employee where post_comment = '';
select * from employee where post_comment is null;
select * from employee where post_comment is not null;
# (5) in 在 ... 之中
# 查询收入是 3000 ,4000,5000 ,8300所有原型的姓名和收入
select emp_name,salary from employee where salary in (3000,4000,5000,8300) # (推荐)
select emp_name,salary from employee where salary = 3000 or salary=4000 or salary=5000 or salary=8300;
# not .. in ..
select emp_name,salary from employee where salary not in (3000,4000,5000,8300);
# (6) 模糊查询 like "%" "_"
# (1) 匹配员工姓名 以on结尾的. "%" 通配符
select emp_name from employee where emp_name like "%on";
# (2) "_"通配符 限定字符长度使用_
select emp_name from employee where emp_name like "a_e_";
# (7) concat (as 起别名) #返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。
select concat("姓名:",emp_name,"工资:",salary) as ceshi from employee;
# concat_ws(拼接的符号, 参数1,参数2,参数3 .. .. )
select concat_ws(" : " , emp_name , salary) as ceshi from employee;
# 计算每个人的年薪 可以在mysql使用四则运算(+ - * / )
select concat_ws(" : " , emp_name, salary * 12) as ceshi200 from employee;
# 二.group by 子句 分组分类
"""group by 字段 对当前字段进行分类 , by后面接什么字段,select 就搜什么字段"""
select sex from employee group by sex;
select post from employee group by post;
# group_concat 按照分类的形式进行字段的拼接
select group_concat(emp_name) from employee group by post;
# 聚合函数
# count 统计总数 *所有 #这表有多少行
select count(*) from employee;
# max 统计最大值
select max(salary) from employee;
# min 统计最小值
select min(salary) from employee;
# avg 统计平均值
select avg(salary) from employee;
# sum 统计总和
select sum(salary) from employee;
# 1. 查询部门名以及各部门的平均薪资 聚合函数 + 分组 配合使用
select post,avg(salary) from employee group by post
# 2. 查询部门名以及各部门的最高薪资
select post,max(salary) from employee group by post
# 3. 查询部门名以及各部门的最低薪资
select post,min(salary) from employee group by post
# 4. 查询公司内男员工和女员工的个数
select sex, count(*) from employee group by sex
# 5. 查询部门名以及部门包含的所有员工名字
select post,group_concat(emp_name) from employee group by post
# 可以group by 两个字段,搜索2个字段
select post , emp_name from employee group by post , emp_name
# 三.having 数据在分类分组之后,进行二次数据过滤,一般是配合group by 使用,分组之后过滤
# 找平均薪资大于10000以上的所有部门
select post,avg(salary) from employee group by post having avg(salary) > 10000
# 1.查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
select post , group_concat(emp_name) , count(*) from employee group by post having count(*) < 2 ;
# 2.查询各岗位平均薪资小于10000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) < 10000;
# 3.查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) < 20000;
# 四.order by 排序, 按照什么字段进行排序
# asc 升序: 从小到大 (默认)
# desc 降序: 从大到小
select * from employee order by age #(默认是asc升序)
select * from employee order by age desc #(默认是desc 倒序)
# 1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
select * from employee order by age , hire_date desc;
# 2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
select post, avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary)
# 3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
select post, avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc
# 五.limit 限制查询条数 (数据分页)
"""limit m,n m代表从第几条数据进行查询,0代表第一条,n代表的查询几条"""
select * from employee limit 0,5 # 从第一条数据开始搜, 搜5条数据
select * from employee limit 5,5 # 从第六条数据开始搜, 搜5条数据
# 只搜索一条数据
select * from employee limit 1
# 只搜索3条数据
select * from employee limit 3
# 搜索这个表中最后一条数据
select * from employee order by id desc limit 1
# 六.(了解)可以使用正则表达式查询数据 (不推荐,效率不高)
select * from employee where emp_name regexp ".*on$"; # .*? 这个?号 mysql 不识别
select * from employee where emp_name regexp "程";
select * from employee where emp_name regexp "程.*金";
# ### part2 多表查询
# 内连接 : (inner join ) -> 两表或者多表满足条件的所有数据查询出来(两表之间的共有数据)
"""
# 两表查询
select 字段 from 表1 inner join 表2 on 必要的关联条件
# 多表查询
select 字段 from 表1 inner join 表2 on 必要的关联条件1 inner join 表3 on 必要的关联条件2 ...
"""
# 基本语法 inner join on + 条件
select * from employee inner join department on employee.dep_id = department.id;
# 用as 起别名(推荐)
select * from employee as e inner join department as d on e.dep_id = d.id;\
# as 可以省略
select * from employee e inner join department d on e.dep_id = d.id;
# where 默认实现的就是内联查询的效果
select * from employee , department where employee.dep_id = department.id;
select * from employee as e , department as d where e.dep_id = d.id;
# 外连接:
# 1.左连接(左联查询 left join ) 以左表为主,右表为辅,完整查询左表所有数据,右表没有的补null
select * from employee left join department on employee.dep_id = department.id;
# 2.右连接(右联查询 right join ) 以右表为主,左表为辅,完整查询右表所有数据,左表没有的补null
select * from employee right join department on employee.dep_id = department.id;
# 3.全连接
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id
""""""
# ### part3 子查询
"""
子查询: 嵌套查询
(1) sql语句当中又嵌套了另外一条sql语句,用()括号抱起来,表达一个整体
(2) 一般应用在from 字符后面(表达一张表),where 子句后面(表达一个条件)
(3) 查询速度从快到慢 : 单表查询 -> 联表速度 -> 子查询
"""
# 一.找出平均年龄大于25岁以上的部门
# (1) 普通where
select
d.id,d.name
from
employee as e,department as d
where
e.dep_id = d.id
group by
d.id,d.name
having
avg(e.age) > 25;
# (2) inner join
select
d.id,d.name
from
employee as e inner join department as d on e.dep_id = d.id
group by
d.id,d.name
having
avg(e.age) > 25;
# (3)子查询
# 1.先选出平均年龄大于25岁的部门id
select dep_id from employee group by dep_id having avg(age) > 25; # 201 202
# 2.通过部门id,找部门的名字
select name from department where id in (201,202);
# 3.综合拼接
select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25);
# 二.查看技术部门员工姓名
# (1) 普通where
select
e.name,d.name
from
employee as e , department as d
where
e.dep_id = d.id
and
d.name = "技术"
# (2) inner join 写法
select
e.name,d.name
from
employee as e inner join department as d on e.dep_id = d.id
where
# 非必要条件写在where字句中
d.name = "技术"
# (3) 子查询
# 1.找计数部门对应的id
select id from department where name ="技术"
# 2.通过id找员工姓名
select name from employee where dep_id = 200;
# 3.综合拼接
select name,dep_id from employee where dep_id = (select id from department where name ="技术");
# 三.查看哪个部门没员工
# 联表差生null值,谁是null谁就没员工
select
d.id,d.name
from
department as d left join employee as e on e.dep_id = d.id
where
e.id is null
# 子查询
department
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+
employee
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 | 18
| 2 | alex | female | 48 | 201 | 43
| 3 | wupeiqi | male | 38 | 201 | 43
| 4 | yuanhao | female | 28 | 202 | 28
| 5 | liwenzhou | male | 18 | 200 | 18
| 6 | jingliyang | female | 18 | 204 | 18
+----+------------+--------+------+--------+
# 1.查询员工都在哪些部门 (200 , 201 202 204)
select dep_id from employee group by dep_id
# 2.把不在这些部门的数据找出来
select id from department where id not in (200,201,202,204)
# 3.综合拼接
select id,name from department where id not in (select dep_id from employee group by dep_id );
# 四.查询大于平均年龄的员工名与年龄
# 如果平均年龄是25;
select name,age from employee where age > 25
# 计算平均年龄
select avg(age) from employee
# 综合拼接
select name,age from employee where age > (select avg(age) from employee);
# 五.把大于其本部门平均年龄的员工名和姓名查出来
# 1.先计算各部门平均年龄是多少
select dep_id,avg(age) as avg_age from employee group by dep_id
# 2.把查询各部门的平均年龄和过去employee做联表,变成更大的表方便后期做单表查询;
select
*
from
employee as t1 inner join (1号数据) as t2 on t1.dep_id = t2.dep_id
# 3.综合拼接
select
*
from
employee as t1 inner join (select dep_id,avg(age) as avg_age from employee group by dep_id) as t2 on t1.dep_id = t2.dep_id
# 4.做最后的条件帅选
select
*
from
employee as t1 inner join (select dep_id,avg(age) as avg_age from employee group by dep_id) as t2 on t1.dep_id = t2.dep_id
where
t1.age > t2.avg_age
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id | max_date
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 | 2038-1-1
| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
| 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
# 六.查询每个部门最新入职的那位员工 # 利用上一套数据表进行查询;
# 1.找每个部门最大的入职时间
select post , max(hire_date) as max_date from employee group by post;
# 2.把子查询搜索出来的数据和 employee 联合成一张更大的表,做一次单表查询
select
*
from
employee as t1 inner join (1号查询出来的数据) as t2 on t1.post = t2.post
where
t1.hire_date = t2.max_date
# 综合拼接
select
t1.emp_name,t1.hire_date
from
employee as t1 inner join (select post , max(hire_date) as max_date from employee group by post) as t2 on t1.post = t2.post
where
t1.hire_date = t2.max_date
# 七.带EXISTS关键字的子查询
"""
EXISTS 关键字,表达存在
如果内层sql 能够查询到数据,返回True ,外层sql执行查询语句
如果内层sql 不能够查询到数据,返回False ,外层sql不执行查询语句
"""
select * from employee where exists ( select * from employee where id = 100);
"""
子查询总结:
子查询可以单独作为一个临时数据表,临时数据,临时字段
一般用在 from where select 子句后面
可以通过查询出来的临时数据和另外的表做一次联表,变成更大的表,然后做单表查询,以得到想要的结果.
"""
# 额外 distinct 去重[尝试操作]
select distinct depart_id from employee;
扫一扫
5万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
