mysql常见的快捷键
快捷键:ctrl + l 清屏
快捷键:ctrl + c 终止
exit : 退出数据库
\q : 退出
\G : 垂直显示
初见Mysql
# (1) 登录
mysql -u用户 -p密码 -hip地址
mysql -uroot -p -h默认本地ip
localhost => 127.0.0.1
# (2) 退出
exit 或者 \q
# 查询当前登录用户
select user()
# 设置密码
set password = password("123456")
# 去除密码
set password = password("");
# 创建一个账户在192.168.126.1能连接mysql server
create user "ceshi100"@"192.168.1.1" identified by "111";
# 创建一个账户在192.168.126.% 这个网段下能连接mysql server
create user "ceshi101"@"192.168.1.%" identified by "222";
# 创建一个账户所有远程端都能连接mysql server
create user "ceshi102"@"%" identified by "333";
### USAGE 没有任何权限
# 查看具体某个ip下的用户权限
show grants for "ceshi102"@"%";
+--------------------------------------+
| Grants for ceshi102@% |
+--------------------------------------+
| GRANT USAGE ON *.* TO 'ceshi102'@'%' |
+--------------------------------------+
# 授权语法
grant 权限 on 数据库.表 to "用户名"@"ip地址" identified by "密码";
"""
select 查询数据的权限
insert 添加数据的权限
update 更改数据的权限
delete 删除数据的权限
* 所有
....
"""
# 授予查询权限
grant select,insert on *.* to "ceshi102"@"%";
# 授予所有权限
grant all on *.* to "ceshi102"@"%";
# 移除删除权限(删除数据库/表)
revoke drop on *.* from "ceshi102"@"%"
# 移除所有权限
revoke all on *.* from "ceshi102"@"%"
# 刷新权限,立刻生效
flush privileges
数据库的 增查改删
增:
# 创建数据库
create database db001 charset utf8;
查:
# 查看数据库
show databases;
# 查看建库语句;
show create database db001;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------- -------------------------------------+
| db001 | CREATE DATABASE `db001` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
CREATE DATABASE `db002` /*!40100 DEFAULT CHARACTER SET utf8 */
改:
alter database db002 charset gbk;
删:
# 删除数据库
drop database db001
数据库的表 增查改删
增:
# 选择数据库
use db001
# 创建表
create table t1(id int , name char);
查:
# 查看所有表
show tables;
# 查看建表语句
show create table t1;
"""
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
"""
# 查看表结构
desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(1) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
改:
# modify 只能改变类型
alter table t1 modify name char(5);
# change 改变类型+字段名
alter table t1 change name name123 char(4);
# add 添加字段
alter table t1 add age int;
# drop 删除字段
alter table t1 drop age;
# rename 更改表明
alter table t1 rename t1111111;
删:
drop table t1;
数据库的表的内容 增查改删
增:
# 一次插入一条数据
insert into t1(id,name) values(1,'abcd');
# 一次插入多条数据
insert into t1(id,name) values(2,"王文"),(3,"刘文波"),(4,"康裕康"),(5,"张保障");
# 不指定具体字段,默认把字段全部插一遍
insert into t1 values(6,"沈思雨");
# 可以具体指定某个字段进行插入
insert into t1(name) values("张宇");
查:
# * 所有
select * from t1;
# 查询单个字段
select id from t1;
# 查询多个字段
select id,name from t1;
改:
# update 表名 set 字段=值 where 条件
update t1 set name="王伟" where id = 2;
# 不加条件有风险,一改全改,一定加where
update t1 set name="王伟" ;
删:
# 删除的时候,必须加上where
delete from t1 where id = 1;
# 删除所有数据,一删全删,一定加where
delete from t1;
# 删除所有 (数据+重置id)
truncate table t1;
常用数据类型
# 整型
tinyint 1个字节 有符号范围(-128~127) 无符号(0~255) unsigned 小整型值
int 4个字节 有符号范围(-21亿 ~ 21亿左右) 无符号(0~42亿) 大整型值
create table t3(id int , sex tinyint);
insert into t3(id,sex) values(4000000000,127) error out of range
insert into t3(id,sex) values(13,128) error Out of range
insert into t3(id,sex) values(13,127);
# 浮点型
float(255,30) 单精度
double(255,30) 双精度
decimal(65,30) 金钱类型 (用字符串的形式来存储小数)
create table t4(f1 float(5,3) , f2 double(5,3) , f3 decimal(5,3) );
insert into t4 values(1.7777777777777777777777777,1.7777777777777777777777777,1.7777777777777777777777777);
insert into t4 values(11.7777777777777777777777777,11.7777777777777777777777777,11.7777777777777777777777777);
insert into t4 values(111.7777777777777777777777777,111.7777777777777777777777777,111.7777777777777777777777777); error out of range
insert into t4 values(1.7,1.7,1.7); error 整数位最多保留2位 , 小数位最多保留3位;存在四舍五入
# float 小数位默认保留5位,double 小数位默认保留16位,decimal 默认保留整数,四舍五入
create table t5(f1 float , f2 double , f3 decimal);
insert into t5 values(1.7777777777777777777777777,1.7777777777777777777777777,1.7777777777777777777777777);
create table t6(f1 float(7,3));
insert into t6 values(1234.5678);
+----------+
| f1 |
+----------+
| 1234.568 |
+----------+
# 整数位最多保留4位,小数位最多保留3位
# 默认double保留的小数位更多,float保留的小数位少;decimal保留整数位
insert into t6 values(12345.67);
# 字符串 char(字符长度) varchar(字符长度)
char 字符长度 255个
varchar 字符长度 21845个
char(11) 定长:固定开辟11个字符长度的空间(手机号,身份证号),开辟空间的速度上来说比较快,从数据结构上来说,需谨慎,可能存在空间浪费. max = 255
varchar(11) 变长:动态最多开辟11个字符长度的空间(评论,广告),开辟空间的速度上来说相对慢,从数据结构上来说,推荐使用,不存在空间浪费 max > 255
text 文本类型:针对于文章,论文,小说. max > varchar
create table t7(c char(11), v varchar(11) , t text);
insert into t7 values("11111","11111","11111");
insert into t7 values("你好啊你好啊你好啊你好","你好啊你好啊你好啊你好","你好啊你好啊你好啊你好");
# concat 可以把各个字段拼接在一起
select concat(c,"<=>",v,"<=>",t) from t7;
数据库内部方法
select user()
select concat()
select database()
select now()
# 枚举和集合
enum 枚举 : 从列出来的数据当中选一个 (性别)
set 集合 : 从列出来的数据当中选多个 (爱好)
create table t8(
id int ,
name varchar(10) ,
sex enum("男性","兽性","人妖") ,
money float(5,3) ,
hobby set("吃肉","抽烟","喝酒","打麻将","嫖赌")
);
# 正常写法
insert into t8(id,name,sex , money , hobby) values(1,"张保障","兽性",2.6,"打麻将,吃肉,嫖赌");
# 自动去重
insert into t8(id,name,sex , money , hobby) values(1,"张保障","兽性",2.6,"打麻将,吃肉,嫖赌,嫖赌,嫖赌,嫖赌,嫖赌,嫖赌");
# 异常写法 : 不能选择除了列出来的数据之外的其他值 error 报错
insert into t8(id,name,sex , money , hobby) values(1,"张保障","人妖12",2.6,"打麻将,吃肉,嫖赌12");
时间类型
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-11-3","9:19:30","2020","2020-11-3 9:19:30");
insert into t1 values(now(),now(),now(),now());
timestamp YYYYMMDDHHMMSS(时间戳) 自动更新时间 (不需要手动写入,自动实现更新记录,[用作记录修改的时间])
create table t2(dt datetime , ts timestamp);
insert into t2 values(20201103092530 , 20201103092530);
insert into t2 values(null,null); # 区别 timestamp 自动更新时间(以当前时间戳) datetime没有
insert into t2 values(20390102101010 , 20390102101010); error # 超越2038
约束 : 对编辑的数据进行类型的限制,不满足约束条件的报错
unsigned : 无符号
not null : 不为空
default : 默认值
unique : 唯一值,加入唯一索引
(索引相当于字典目录,索引的提出是为了加快速度,一味地乱加索引不会提高查询效率)
primary key: 主键
auto_increment: 自增加一
zerofill : 零填充
foreign key: 外键
# unsigned 无符号
create table t3(id int unsigned);
insert into t3 values(-1); error
insert into t3 values(4000000000); success
# not null : 不为空
create table t4(id int not null , name varchar(11));
insert into t4 values(1,"张宇");
insert into t4 values(null,"张宇"); error
insert into t4(name) values("李四"); error
# default : 默认值
create table t5(id int not null , name varchar(11) default "沈思雨" );
insert into t5 values(1,null);
insert into t5(id) values(2);
create table t5_2(id int not null default "1111" , name varchar(11) default "沈思雨" );
insert into t5_2 values(); # 在values里面不写值,默认使用默认值;
# unique : 唯一值,加入唯一索引(索引的提出是为了加快速度,一味地乱加索引不会提高查询效率)
# 唯一 可为null 标记成: UNI
create table t6(id int unique , name char(10) default "赵万里" );
insert into t6(id) values(1);
insert into t6(id) values(1); error
insert into t6(id) values(null);
insert into t6(id) values(null); # id变成了多个null
# primary key: 主键 [ 唯一 + 不为null ] PRI 标记数据的唯一特征
"""一个表中,只能设置一个字段为一个主键,unique唯一约束可以设置多个"""
# 创建主键
create table t7(id int primary key , name varchar(10) default "赵沈阳");
insert into t7(id) values(1);
insert into t7(id) values(1); error
insert into t7(id) values(null); error
# unique + not null => PRI
create table t8(id int unique not null , name varchar(10) default "赵沈阳" );
# primary key / unique + not null => 优先把primary key 作为主键;
create table t9(id1 int unique not null , id2 int primary key );
# 一个表只能设置单个字段为一个主键;
create table t10(id1 int primary key , id2 int primary key ); error
# auto_increment: 自增加一 (一般配合 主键或者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 from t11;
# 删除数据 + 重置id
truncate table t11;
# zerofill : 零填充 (配合int使用,不够5位拿0来填充)
create table t12(id int(5) zerofill);
insert into t12 values(1234567);
insert into t12 values(12);
索引
主键索引 : PRI [primary key]
唯一索引 : UNI [unique]
普通索引 : MUL [index]
# 1.联合唯一索引
"""unique(字段1,字段2,字段3 ..... ) 合在一起,该数据不能重复"""
# unique + not null
create table t1_server(id int , server_name varchar(10) not null , ip varchar(15) not null , port int not null , unique(ip,port) );
insert into t1_server values(1,"阿里","192.168.11.251",3306);
insert into t1_server values(1,"阿里","192.168.11.251",80);
insert into t1_server values(1,"阿里","192.168.11.252",80);
insert into t1_server values(1,"阿里","192.168.11.252",80); error
# unique : 有可能出现多个空值的情况要注意;
create table t2_server(id int , server_name varchar(10) not null , ip varchar(15) , port int , unique(ip,port) );
insert into t2_server values(1,"腾讯","192.168.11.251",3306);
insert into t2_server values(1,"腾讯","192.168.11.251",3306); error
insert into t2_server values(1,"腾讯",null,null); # 注意点: 允许插入多个空值;
+------+-------------+----------------+------+
| id | server_name | ip | port |
+------+-------------+----------------+------+
| 1 | 腾讯 | 192.168.11.251 | 3306 |
| 1 | 腾讯 | NULL | NULL |
| 1 | 腾讯 | NULL | NULL |
| 1 | 腾讯 | NULL | NULL |
| 1 | 腾讯 | NULL | NULL |
+------+-------------+----------------+------+
# 2.联合唯一主键
create table t3_server(id int ,server_name varchar(10) not null , ip varchar(15) , port int , primary key(ip,port) );
insert into t3_server values(1,"华为","192.168.11.251",3306);
insert into t3_server values(1,"华为","192.168.11.251",3307);
总结:
primary key(字段1,字段2 ... ) 联合唯一主键 , 单个字段情况,可以设置一个主键,如果是多个字段只能设置成联合主键,合在一起表达一个主键概念;
unique(字段1,字段2 ... ) 联合唯一索引
index(字段1,字段2 ... ) 联合普通索引
# 3.foreign key: 外键,把多张表通过一个关联字段联合在一起 (该字段可以设置成外键,作用是可以联级更新或者联级删除)
语法: foreign key(classid) references class1(id)
条件: 被关联的字段,必须具备唯一属性;
student1:
id name age classid
1 wangtongpei 58 1
2 liuyifeng 85 1
3 wangwen 18 2
class1:
id classname
1 python32
2 python33
# 创建class1
create table class1(id int , classname varchar(255));
# 添加唯一索引
alter table class1 add unique(id);
# 创建class222
create table class222(id int unique, classname varchar(255));
# 删除索引
alter table class1 drop index 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,"python32");
insert into class1 values(2,"python33");
insert into class1 values(3,"python34");
insert into student1 values(null,"wangtongpei",58,1);
insert into student1 values(null,"liuyifeng",85,1);
insert into student1 values(null,"wangwen",18,2);
# 没有关联的数据可以直接删除
delete from class1 where id = 1;
# 有关联的数据不能直接删除,要先把关联的数据删掉之后再删除
delete from student1 where id = 3;
delete from class1 where id = 2;
# 联级更新 , 联级删除 ( 谨慎使用 )
"""
联级删除 on delete cascade
联级更新 on update cascade
"""
# 创建class2
create table class2(id int primary key auto_increment, 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,"python32");
insert into class2 values(2,"python33");
insert into class2 values(3,"python34");
insert into student2 values(null,"wangtongpei",58,1);
insert into student2 values(null,"liuyifeng",85,1);
insert into student2 values(null,"wangwen",18,2);
# 联级删除 (把所有关联数据全部删除,谨慎;)
delete from class2 where id = 1;
# 联级更新 (把所有关联数据全部更新,谨慎;)
update class2 set id = 100 where classname="python33";
表与表之间的关系
(1) 一对一 : 在一个家庭中每个人名所对应的身份证号码
(2) 一对多(多对一) : 在中国一个姓名对应着多张身份证，反之多张身份证对应一个姓名
(3) 多对多 : 一本书可以被多个作者共同编写,一个作者可以写多本书
xueke (表1)
id name
1 math
2 english
3 wuli
student (表2)
id name
1 wangwen
2 wangwei
3 wangtongpei
relation (关系表3)
"""
把 xid 和 sid 这两个关联字段设置成外键,
关联xueke表里的id(对应的xid) ,
关联student表里的id(对应的sid)
"""
xid sid
1 1
1 2
1 3
2 1
2 2
2 3
存储引擎 : 存储数据的一种结构方式
# 概念:
表级锁 : 只要有一个线程执行修改表中的相关操作,就会上锁,其他线程默认等待;
行级锁 : 针对于当前表中的这条记录,这一行进行上锁,其他数据仍然可以被其他线程修改,实现高并发,高可用;
事务处理: 执行sql语句时,必须所有的操作全部成功,才最终提交数据,有一条失败,直接回滚,恢复到先前状态
begin : 开启事务
commit : 提交数据
rollback : 回滚数据
MyISAM: 表级锁
InnoDB: 事务处理,行级锁,外键
MEMORY: 把数据放在内存中,临时缓存;
BLACKHOLE: anything you write to it disappears
一般用于同步主从数据库;(放在主数据库和从数据库之间的一台服务器;)
"""
主数据库: 增删改
从数据库: 查询
配置: 一主一从 , 一主多从 , 多主多从
"""
create table myisam1( id int ) engine=MyISAM;
.frm 表结构
.MYD 表数据
.MYI 表索引
create table innodb1( id int ) engine=InnoDB;
.frm 表结构
.ibd 表数据+表索引
create table memory1( id int ) engine=MEMORY;
.frm 只有表结构 , 数据存放在内存中
create table blackhole( id int ) engine=BLACKHOLE;
.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 外键 (先通过desc 表 找到外键名字,然后再删)
alter table student1 drop foreign key student1_ibfk_1; #删除
alter table student1 add foreign key(classid) references class1(id) #添加
单表查询
""" select ... from ... where ... group by ... having ... order by ... limit ... """
# 一.where 条件的使用
"""
功能: 对表中的数据进行帅选和过滤
语法:
1.判断的符号
= (!= <>不等于) > >= < <=
2.拼接不同的条件的关键字
and or not
3.查询对应的区间值
between 小值 and 大值 [小值,大值] 查询两者之间的范围值
4.查询具体在哪个范围中
in(1,21,333,444) 指定范围
5.模糊查询 like % 通配符 _ 通配符
like "%b" 匹配以b结尾的任意长度的字符串
like "b%" 匹配以b开头的任意长度的字符串
like "%b%" 匹配字符串中含有b的任意长度的内容
like "__b" 匹配总长度为3个字符,任意内容的字符串,并且以b结尾
like "b_" 匹配总长度为2个字符,任意内容的字符串,并且以b开头
"""
# 1. 查询部门是sale的所有员工姓名:
select emp_name from employee where post="sale";
# 2. 部门是teacher , 收入大于10000的所有数据
select * from employee where post = "teacher" and salary > 10000;
# 3. 收入在1万到2万之间的所有员工姓名和收入
select emp_name,salary from employee where salary between 10000 and 20000;
# 4. 收入不在1万到2万之间的所有员工姓名和收入
select emp_name,salary from employee where salary not between 10000 and 20000;
# 5. 查看岗位描述为NULL的员工信息
select emp_name from employee where post_comment = null;
select emp_name from employee where post_comment = '';
select emp_name from employee where post_comment is null;
# 6. 查看岗位描述不为NULL的员工信息
select emp_name from employee where post_comment is not null;
# 7. 查询收入是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;
# 8. 查询收入不是3000 ,4000 ,5000,8300 所有员工的姓名和收入
select emp_name,salary from employee where salary not in(3000,4000,5000,8300);
# 9. 以on结尾的员工名搜一下
select emp_name from employee where emp_name like "%on";
select emp_name from employee where emp_name like "ji%";
select emp_name from employee where emp_name like "_le_";
# 10. 统计员工一年的年薪
select concat(" 姓名: ",emp_name," 收入: ",salary) from employee;
# 计算年薪,可以在mysql中使用四则运算符 + - * /
select concat(" 姓名: ",emp_name," 收入: ",salary * 12) from employee;
select concat_ws(" : ",emp_name,salary*12 ) from employee;
# 11. 查询部门的种类
# distinct 返回唯一不同的值
select distinct(post) from employee;
# 二.group by 子句 分组分类
"""group by 字段,对数据进行分类, by后面接什么字段,select后面就搜什么字段"""
select sex from employee group by sex;
# group_concat 按照分组把对应字段拼在一起;
select group_concat(emp_name),post 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 avg(salary),post from employee group by post;
# 2. 查询部门名以及各部门的最高薪资
select max(salary),post from employee group by post;
# 3. 查询部门名以及各部门的最低薪资
select min(salary),post from employee group by post;
# 4. 查询公司内男员工和女员工的个数
select count(*),sex from employee group by sex;
# 5. 查询部门名以及部门包含的所有员工名字
select group_concat(emp_name),post from employee group by post;
# 6 可以group by 两个字段,就可以同时搜索两个字段
select emp_name,post 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) between 10000 and 20000
select post, avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) < 20000;
# 四.order by 排序 , 按照某字段排序
order by age asc (升序) order by age desc (降序)
# 按照年龄从小到大排序
select * from employee order by age;
# 按照年龄从大到小排序
select * from employee order by age desc;
# 1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
select * from employee order by age asc , hire_date desc;
# 2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc
# 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代表从第几条数据开始查, n 代表查几条 m=0 代表的是第一条数据"""
select * from employee limit 0,10 # 0代表的是第一条数据
select * from employee limit 10,10 # 10代表的是第十一条数据
select * from employee limit 20,10 # 20代表的是第二十一条数据
# limit + num num => 搜索的条数据
select * from employee limit 1
# 搜索这个表里面最后一条数据
select * from employee order by id desc limit 1
# 搜索这个表里面最后五条数据
select * from employee order by id desc limit 5
# 六.mysql 当中可以使用正则表达式 (不推荐,效率低)
select * from employee where emp_name regexp ".*on$"; # mysql中无法识别?
select * from employee where emp_name regexp "^程.*";
select * from employee where emp_name regexp "^程.*金";
part2 多表查询
# 1.内联接 : inner join : 两表或者多表之间,把满足条件的所有数据查询出来 (多表之间共同拥有的数据会被查询出来)
# 两表联查
select 字段 from 表1 inner join 表2 on 必要的关联条件
# 多表联查
select 字段 from 表1 inner join 表2 on 必要的关联条件1 inner join 表3 on 必要的关联条件2
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 写法默写是内联接( 等同于inner join )
select * from employee,department where employee.dep_id = department.id;
select * from employee as e ,department as d where e.dep_id = d.id;
# 2.外联接 : left join左联接 / right join 右联接
# (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.全联接 : union
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 employee.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 employee.dep_id having avg(age)>25);
# 二.查看技术部门员工姓名
# (1) 普通的where 查询
select
e.id,e.name
from
employee as e,department as d
where
e.dep_id = d.id
and
d.name = "技术"
# (2) inner join
select
e.id,e.name
from
employee as e inner join department as d on e.dep_id = d.id
where
d.name = "技术"
# (3)子查询
# (1) 找技术部门对应的id
select id from department where name = "技术";
# (2) 通过id找员工姓名
select name from employee where dep_id = 200;
# (3) 综合拼接
select id,name from employee where dep_id = (select id from department where name = "技术");
# 三.查看哪个部门没员工
# 联表写法
select
d.id,d.name
from
department as d left join employee as e on d.id = e.dep_id
where
e.dep_id is null
# 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);
department;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+
employee;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |avg(age)
+----+------------+--------+------+--------+
| 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
+----+------------+--------+------+--------+
# 四.查询大于平均年龄的员工名与年龄
# 假设已经知道了平均年龄;
select name,age from employee where age > 30;
# 计算平均年龄
select avg(age) from employee;
# 综合拼接
select name,age from employee where age > (select avg(age) from employee);
# 五.把大于其本部门平均年龄的员工名和姓名查出来
# 1.先计算本部门的平均年龄是多少
select dep_id , avg(age) from employee group by dep_id;
+--------+----------+
| dep_id | avg(age) |
+--------+----------+
| 200 | 18.0000 |
| 201 | 43.0000 |
| 202 | 28.0000 |
| 204 | 18.0000 |
+--------+----------+
# 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.最后做一次单表查询,让age > 平均值
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
age >avg_age
# 六.查询每个部门最新入职的那位员工 # 利用上一套数据表进行查询;
employee
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id | max_date
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | | 7300.33 | 401 | 1 | 2017-03-01
| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | 2015-03-02
| 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | 2015-03-02
| 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | 2015-03-02
| 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | 2015-03-02
| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | 2015-03-02
| 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | 2015-03-02
| 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | 2015-03-02
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | 2017-01-27
| 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | 2017-01-27
| 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | 2017-01-27
| 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | 2017-01-27
| 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | 2017-01-27
| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | 2016-03-11
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | 2016-03-11
| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | 2016-03-11
| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | 2016-03-11
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | 2016-03-11
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
# 1.找各部门的最新入职的时间
select post,max(hire_date) as max_date from employee group by post
+-----------------------------------------+------------+
| post | max_date |
+-----------------------------------------+------------+
| operation | 2016-03-11 |
| sale | 2017-01-27 |
| teacher | 2015-03-02 |
| 老男孩驻沙河办事处外交大使 | 2017-03-01 |
+-----------------------------------------+------------+
# 2.把子查询搜索出来的结果作为一张表和employee这个表做联表,把max_date拼接在employee这个表中,变成一张大表,最后做一次单表查询
select
*
from
employee as t1 inner join (1号数据) as t2 on t1.post = t2.post
where
t1.hire_date = t2.max_date
# 3.综合拼装
select
emp_name , max_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语句
如果内层sql , 不能查到数据, 返回False , 外层sql不执行sql语句
"""
select * from employee where exists (select * from employee where id = 1);
select * from employee where exists (select * from employee where id = 100000);
"""
总结:
子查询可以单独作为临时数据,作为一张表或者一个字段,通过()进行包裹,表达一个整体;
一般用在from,where,select.子句的后面
可以通过查询出来的数据和另外的表做联表变成更大一张表,
最后做单表查询,达到目的;
"""
