MySQL
是一个开4.innodb 2个文件 ==事务==
5.myisam 3个文件
6.常用存储引擎及使用场景
7.流程关系
1.alter table 表名 rename 新名字; 改表名
2.alter table t1 modify name char(3); 修改类型
3.alter table t1 change name name1 char(2); 修改名字和类型
6.删:drop table 表名;
7.清空表操作:
1.存储引擎决定了表的类型,而表内存放的数据也要有不同的类型,每种数据类型都有自己的宽度,但宽度是可选的详细参考
2.mysql常用数据类型概览
#1. 数字:
整型:tinyint int bigint
小数:
float :在位数比较短的情况下不精准
double :在位数比较长的情况下不精准
0.000001230123123123
存成:0.000001230000
decimal:(如果用小数,则用推荐使用decimal)
精准
内部原理是以字符串形式去存
#2. 字符串:
char(10):简单粗暴,浪费空间,存取速度快
root存成root000000
varchar:精准,节省空间,存取速度慢
sql优化:创建表时,定长的类型往前放,变长的往后放
比如性别 比如地址或描述信息
>255个字符,超了就把文件路径存放到数据库中。
比如图片,视频等找一个文件服务器,数据库中只存路径或url。
#3. 时间类型:
最常用:datetime
#4. 枚举类型与集合类型
2.数值类型
tinyint int
create table int_t (
ti tinyint, # ****
i int, # *****
f float, # 精度问题 小数点后5位 # *****
d double, # 精度更高但也不准确
e decimal(30,20)
tiun tinyint unsigned,
iun int unsigned
);
3.日期类型
内置函数 now()
datetime 打卡时间/日志/论坛博客类的评论\文章 *****
date 员工生日/入职日期/离职日期/开班时间 *****
time 上课时间/下课时间/规定上班时间 竞赛数据
year 年
timestamp 由于表示范围的问题,导致用的少了
create table time_t2(
dt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, # 表示的范围更大,还能拥有timestamp的特点
d date,
t time,
y year,
ts timestamp # 不能为空,默认值是当前时间,在修改的时候同时更新时间
)
4.字符串类型
char 0-255 定长存储 存储速度更快 占用更多的空间 *****
char(12)
alex --> 'alex ' --> 12的长度
varchar 0-65535 变长存储 存储速度慢 占用的空间小 ****
varchar(12)
'alex' --> 'alex4' --> 5的长度
手机号码/身份证号码 : char
用户名/密码 : 有一定范围弹性 char
评论 : varchar
时间换空间,空间换时间.没有可以有也没有可以无
5.枚举类型与集合类型
set 多选 集合 去重 从有限的条件中多选一个引号里逗号分隔
enum 单选 枚举 单选只能从有限的条件里选择 没有就是空
单选题,只能从有限的条件中选择
create table enum_t(
id int,
name char(12),
gender enum('男','女','不详')
)
gender性别(男 女 不详)
多选题,从有限的条件中选
create table set_t(
id int,
name char(12),
hobby set('抽烟','喝酒','烫头','搓脚')
)
PRIMARY KEY (PK) 标识该字段为该表的主键,可以唯一的标识记录
FOREIGN KEY (FK) 标识该字段为该表的外键
NOT NULL 标识该字段不能为空
UNIQUE KEY (UK) 标识该字段的值是唯一的
AUTO_INCREMENT 标识该字段的值自动增长(整数类型,而且为主键)
DEFAULT 为该字段设置默认值
UNSIGNED 无符号
ZEROFILL 使用0填充
1.not null 非空 null 可空
2.default 默认值
默认值,创建列时可以指定默认值,当插入数据时如果未主动设置,则自动添加默认值
create table tb1(
nid int not null defalut 2,
num int not null
);
3.unique 唯一
1.独一无二,唯一属性:id,身份证号等
2.是一种key,唯一键,是在数据类型之外的附加属性,有加速查询的作用
create table department2(
id int,
name varchar(20),
comment varchar(100),
constraint uk_name unique(name)
);
2.联合唯一
create table service(
id int primary key auto_increment,
name varchar(),
host varchar() not null,
port int not null,
unique(host,port) #联合唯一
);
4.primary key
unique key和primary key都是MySQL的特殊类型,不仅仅是个字段约束条件,还称为索引,可以加快查询速度,这个索引功能我们后面再讲,现在只讲一下这些key作为约束条件的效果。
关于主键的强调内容:
1.一张表中必须有,并且只能由一个主键字段:innodb引擎下存储表数据的时候,会通过你的主键字段的数据来组织管理所有的数据,将数据做成一种树形结构的数据结构,帮你较少IO次数,提高获取定位数据、获取数据的速度,优化查询。
解释:如果我们在一张表中没有设置primary key,那么mysql在创建表的时候,会按照顺序从上到下遍历你设置的字段,直到找到一个not null unique的字段,自动识别成主键pri,通过desc可以看到,这样是不是不好啊,所以我们在创建表的时候,要给他一个主键,让他优化的时候用,如果没有pri也没有not null unique字段,那么innodb引擎下的mysql被逼无奈,你没有设置主键字段,主键又有不为空且唯一的约束,又不能擅自给你的字段加上这些约束,那么没办法,它只能给你添加一个隐藏字段来帮你组织数据,如果是这样,你想想,主键是不是帮我们做优化查询用的啊,这个优化是我们可以通过主键来查询数据:例如:如果我们将id设置为主键,当我们查一个id为30的数据的时候,也就是select * from tb1 where id=30;这个查询语句的速度非常快,不需要遍历前面三十条数据,就好像我们使用的字典似的,找一个字,不需要一页一页的翻书,可以首先看目录,然后看在哪一节,然后看在哪一页,一步步的范围,然后很快就找到了,这就像我们说的mysql的索引(主键、唯一键)的工作方式,一步一步的缩小范围来查找,几步就搞定了,所以通过主键你能够快速的查询到你所需要的数据,所以,如果你的主键是mysql帮你加的隐藏的字段,你查询数据的时候,就不能将这个隐藏字段作为条件来查询数据了,就不能享受到优化后的查询速度了,对么
2.一张表里面,通常都应该有一个id字段,而且通常把这个id字段作为主键,当然你非要让其他的字段作为主键也是可以的,看你自己的设计,创建表的时候,一般都会写create table t1(id int primary key);id int primary key这个东西在建表的时候直接就写上
4.在没有设置主键的时候,not null+unique会被默认当成主键
mysql> create table t1(id int not null unique);
Query OK, rows affected (0.02 sec)
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int() | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
row in set (0.00 sec)
5.单列主键测试
============单列做主键===============
#方法一:not null+unique
create table department1(
id int not null unique, #主键
name varchar() not null unique,
comment varchar()
);
mysql> desc department1;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar() | NO | UNI | NULL | |
| comment | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
rows in set (. sec)
#方法二:在某一个字段后用primary key
create table department2(
id int primary key, #主键
name varchar(),
comment varchar()
);
mysql> desc department2;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar() | YES | | NULL | |
| comment | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
rows in set (. sec)
#方法三:在所有字段后单独定义primary key
create table department3(
id int,
name varchar(),
comment varchar(),
constraint pk_name primary key(id); #创建主键并为其命名pk_name
mysql> desc department3;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar() | YES | | NULL | |
| comment | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
rows in set (. sec)
6.联合主键解释
联合主键
和联合唯一是类似的,
mysql> create table t1(
->id int,
->port int,
->primary key(id,port)
-> );
Query OK, rows affected (. sec)
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | | |
| port | int() | NO | PRI | 0 | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.10 sec)
看key,两个都写的是pri,两个联合起来作为主键,他们两个作为一个主键,不能再有其他的主键了,也就是在创建表的时候,只能出现一次primary key方法。
有同学说,老师,我不写primary key行不,只写一个not null unique字段,当然行,但是我们应该这样做吗,是不是不应该啊,所以以后设置主键的时候,就使用primary key来指定
7.多列(联合)主键测试
==================多列做主键================
create table service(
ip varchar(),
port char(),
service_name varchar() not null,
primary key(ip,port)
);
mysql> desc service;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ip | varchar(15) | NO | PRI | NULL | |
| port | char() | NO | PRI | NULL | |
| service_name | varchar(10) | NO | | NULL | |
+--------------+-------------+------+-----+---------+-------+
rows in set (. sec)
mysql> insert into service values
-> ('172.16.45.10','3306','mysqld'),
-> ('172.16.45.11','3306','mariadb')
-> ;
Query OK, rows affected (. sec)
Records: Duplicates: Warnings:
mysql> insert into service values ('172.16.45.10','3306','nginx');
ERROR (): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'
5.auto_increment 自动增加
不指定id,则自动增长
create table student(
id int primary key auto_increment, 设置自动增加
name varchar(20),
sex enum('male','female') default 'male'
);
3.sex enum('male','female') default 'male' 创建表时指定auto_increment的初始值,注意初始值的设置为表选项,应该放到括号外
4.insert into student values(4,'asb','female'); 设置ID 对于自增的字段,在用delete删除后,再插入值,该字段仍按照删除前的位置继续增长
5.insert into student(name) values('egon'); truncate student; 应该用truncate清空表,比起delete一条一条地删除记录,truncate是直接清空表,在删除大表时用它(从0开始)
6.alter table student auto_increment=3; 在创建完表后,修改自增字段的起始值
7.show create table student; ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8(显示默认值)
8.设置自增: set session auth_increment_increment=2 #修改会话级别的步长 set global auth_increment_increment=2 #修改全局级别的步长(所有会话都生效)
6.foreign key 外键约束
on delete cascade 级联删除
on update cascade 级联更新
7.分析步骤:
8.总结
1.多对一: 如果只有步骤1成立,则是左表多对一右表 如果只有步骤2成立,则是右表多对一左表
=====================多对一=====================
create table press(
id int primary key auto_increment,
name varchar(20)
);
create table book(
id int primary key auto_increment,
name varchar(20),
press_id int not null,
foreign key(press_id) references press(id)
on delete cascade
on update cascade
);
insert into press(name) values
('北京工业地雷出版社'),
('人民音乐不好听出版社'),
('知识产权没有用出版社')
;
insert into book(name,press_id) values
('九阳神功',1),
('九阴真经',2),
('九阴白骨爪',2),
('独孤九剑',3),
('降龙十巴掌',2),
('葵花宝典',3)
=====================多对多=====================
create table author(
id int primary key auto_increment,
name varchar(20)
);
#这张表就存放作者表与书表的关系,即查询二者的关系查这表就可以了
create table author2book(
id int not null unique auto_increment,
author_id int not null,
book_id int not null,
constraint fk_author foreign key(author_id) references author(id)
on delete cascade
on update cascade,
constraint fk_book foreign key(book_id) references book(id)
on delete cascade
on update cascade,
primary key(author_id,book_id)
);
#插入四个作者,id依次排开
insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');
#每个作者与自己的代表作如下
1 egon:
1 九阳神功
2 九阴真经
3 九阴白骨爪
4 独孤九剑
5 降龙十巴掌
6 葵花宝典
2 alex:
1 九阳神功
6 葵花宝典
3 yuanhao:
4 独孤九剑
5 降龙十巴掌
6 葵花宝典
4 wpq:
1 九阳神功
insert into author2book(author_id,book_id) values
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(2,1),
(2,6),
(3,4),
(3,5),
(3,6),
(4,1)
;
=====================一对一=====================
一定是student来foreign key表customer,这样就保证了:
1 学生一定是一个客户,
2 客户不一定是学生,但有可能成为一个学生
create table customer(
id int primary key auto_increment,
name varchar(20) not null,
qq varchar(10) not null,
phone char(16) not null
);
create table student(
id int primary key auto_increment,
class_name varchar(20) not null,
customer_id int unique, #该字段一定要是唯一的
foreign key(customer_id) references customer(id) #外键的字段一定要保证unique
on delete cascade
on update cascade
);
#增加客户
insert into customer(name,qq,phone) values
('李飞机','31811231',13811341220),
('王大炮','123123123',15213146809),
('守榴弹','283818181',1867141331),
('吴坦克','283818181',1851143312),
('赢火箭','888818181',1861243314),
('战地雷','112312312',18811431230)
;
#增加学生
insert into student(class_name,customer_id) values
('脱产3班',3),
('周末19期',4),
('周末19期',5)
;
9.外键约束有三种约束模式(都是针对父表的约束):
场景:book表和publish表为多对一关系,book表的pid字段外键关联到了publish表的id字段
1 查看外键关系名称:
show create table book;
| book | CREATE TABLE `book` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(10) DEFAULT NULL,
`pid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `pid` (`pid`),
CONSTRAINT `book_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `publish` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
2 删除外键关系
alter table book drop foreign key book_ibfk_1(外键名称);
3 删除字段
alter table publish drop id(字段名称);
4 添加字段
alter table publish add id(字段名称) int(数据类型) primary key auto_increment(约束条件);
5 创建表完成之后,后添加外键关系
alter table book add foreign key(pid) references publish(id);
创建表时:
create table t1(
id int,
pid int,
constraint fk_t1_publish foreign key(pid) references publish(id);
)
创建表完成之后,后添加外键关系
alter table book add constraint fk_t1_publish foreign key(pid) references publish(id);
级联有几个模式
严格模式(默认的),外键有强制约束效果,被关联字段不能随意删除和修改
模式(cascade):外键有强制约束效果,被关联字段删除或者修改,关联他的那么字段数据会随之删除或者修改
constraint fk_t1_publish foreign key(pid) references publish(id) on delete cascade on update cascade;
set null模式: 被关联字段删除时,关联他的字段数据会置成null
select 某一个东西
可以查一个,多个,*所有
调用函数 : now() user() database() concat() concat_ws()
进行四则运算
可以去重 distinct
可以进行条件判断 case when语句
查询数据的本质:mysql会到你本地的硬盘上找到对应的文件,然后打开文件,按照你的查询条件来找出你需要的数据。下面是完整的一个单表查询的语法
select * from,这个select * 指的是要查询所有字段的数据。
SELECT distinct 字段1,字段2... FROM 库名.表名
from后面是说从库的某个表中去找数据,mysql会去找到这个库对应的文件夹下去找到你表名对应的那个数据文件,找不到就直接报错了,找到了就继续后面的操作
WHERE 条件 从表中找符合条件的数据记录,where后面跟的是你的查询条件
GROUP BY field(字段) 分组
HAVING 筛选 过滤,过滤之后执行select后面的字段筛选,就是说我要确定一下需要哪个字段的数据,你查询的字段数据进行去重,然后在进行下面的操作
ORDER BY field(字段) 将结果按照后面的字段进行排序
LIMIT 限制条数 将最后的结果加一个限制条数,就是说我要过滤或者说限制查询出来的数据记录的条数
from > where > group by > having > select > distinct > order by > limit
避免重复DISTINCT:
desc employee; 查看表结构
SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id FROM employee; select后面写什么查询什么
SELECT * FROM employee; 查询全部 不推荐用* ,查询的时候*的效率低,
SELECT name,salary FROM employee; 查询所有的name和salary
SELECT post FROM employee; 查询所有的post 但是有重复
SELECT DISTINCT post FROM employee; 查询post distinct去重功能
SELECT DISTINCT post,salary FROM employee; 因为post和salary没有完全一样的
select distinct post,sex from employee; post和sex两组数据一样才会去重
通过四则运算查询:
SELECT name, salary*12 FROM employee; 查薪资每个都*12
SELECT name, salary*12 AS Annual_salary FROM employee; as + 新字段名,就是起一个别名
SELECT name, salary*12 Annual_salary FROM employee; 通过新的别名查询 除了乘法以外,加减乘除都是可以的
自定义显示格式,自己规定查询结果的显示格式:
CONCAT() 函数用于连接字符串
SELECT CONCAT('姓名: ',name,' 年薪: ', salary*12) AS Annual_salary from employee; concat帮我们做字符串拼接的,并且拼接之后的结果,都在一个叫做Annual_salary的字段中
SELECT CONCAT('姓名: ',name,' 年薪: ', salary*12) AS Annual_salary,CONCAT('性别:',sex) from employee; 分成两列
SELECT CONCAT(name,':',salary*12) AS Annual_salary from employee; 通过冒号来将name和salary连接起来
select concat('<名字:',name,'> ','<薪资:',salary,'>') from employee; 查出所有员工的名字,薪资,格式为 <名字:egon> <薪资:3000>
select distinct depart_id from employee; 查出所有的岗位(去掉重复)
SELECT name,salary*12 AS annual_year from employee; 查出所有员工名字,以及他们的年薪,年薪的字段名为annual_year()
之前我们用where 后面跟的语句是不是id=1这种类型的啊,用=号连接的,除了=号外,还能使用其他的,看下面:
1:单条件查询
SELECT name FROM employee
WHERE post='sale'; 单表查询 优先级,where的优先级比select高,所以顺序是先找到这个employee表,然后按照post='sale'的条件,然后去表里面select数据
:多条件查询
SELECT name,salary FROM employee
WHERE post='teacher' AND salary>; 多条件查询
3:关键字BETWEEN AND 写的是一个区间
SELECT name,salary FROM employee
WHERE salary BETWEEN AND ; 关键字BETWEEN AND 是一个区间
SELECT name,salary FROM employee
WHERE salary NOT BETWEEN AND ; 加个not,就是不在这个区间内
4:关键字IS NULL(判断某个字段是否为NULL不能用等号,需要用IS) 判断null只能用is
SELECT name,post_comment FROM employee
WHERE post_comment IS NULL; 关键字IS NULL(判断某个字段是否为NULL不能用等号,需要用IS) 判断null只能用is
SELECT name,post_comment FROM employee
WHERE post_comment IS NOT NULL; 加not,
SELECT name,post_comment FROM employee
WHERE post_comment=''; ''是空字符串,不是null,两个是不同的东西,null是啥也没有,''是空的字符串的意思,是一种数据类型,null是另外一种数据类型
5:关键字IN集合查询
SELECT name,salary FROM employee
WHERE salary IN (,,,) ; salary里面是in条件的打印
SELECT name,salary FROM employee
WHERE salary NOT IN (,,,) ; salary里面不是in条件的打印
6:关键字LIKE模糊查询,模糊匹配,可以结合通配符来使用
SELECT * FROM employee
WHERE name LIKE 'eg%'; 通配符’%’ 匹配任意所有字符
SELECT * FROM employee
WHERE name LIKE 'al__'; 通配符’_’ 匹配任意一个字符 注意我这里写的两个_,用1个的话,匹配不到alex,因为al后面还有两个字符ex。
1. 查看岗位是teacher的员工姓名、年龄
select name,age from employee where post = 'teacher';
2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
select name,age from employee where post='teacher' and age > ;
3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资
select name,age,salary from employee where post='teacher' and salary between and ;
4. 查看岗位描述不为NULL的员工信息
select * from employee where post_comment is not null;
5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
select name,age,salary from employee where post='teacher' and salary in (,,);
6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
select name,age,salary from employee where post='teacher' and salary not in (,,);
7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
select name,salary* from employee where post='teacher' and name like 'jin%';
where条件咱们就说完了,这个where条件到底怎么运作的,我们来说一下:我们以select id,name,age from employee where id>;这个语句来说一下
首先先找到employee表,找到这个表之后,mysql会拿着where后面的约束条件去表里面找符合条件的数据,然后遍历你表中所有的数据,查看一下id是否大于7,逐条的对比,然后只要发现id比7大的,它就会把这一整条记录给select,但是select说我只拿id、name、age这个三个字段里面的数据,然后就打印了这三个字段的数据,然后where继续往下过滤,看看id是不是还有大于的,然后发现一个符合条件的就给select一个,然后重复这样的事情,直到把数据全部过滤一遍才会结束。这就是where条件的一个工作方式。
示例:
# 统计每个岗位的名称以及最高工资
select post,max(salary) from employee group by post;
分组时可以跟多个条件,那么这个多个条件同时重复才算是一组,group by 后面多条件用逗号分隔
select post,max(salary) from employee group by post,id;
ONLY_FULL_GROUP_BY模式
set global sql_mode='ONLY_FULL_GROUP_BY';
如果设置了这个模式,那么select后面只能写group by后面的分组依据字段和聚合函数统计结果
什么是分组?为什么要分组?
select post,max(salary) from employee group by post having max(salary)>20000;
having过滤后面的条件可以使用聚合函数,where不行
select post,avg(salary) as new_sa from employee where age>=30 group by post having avg(salary) > 10000;统计各部门年龄在30岁及以上的员工的平均薪资,并且保留平均工资大于10000的部门
select post,group_concat(name),count(id) from employee group by post having count(id) < 2;查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
select post,avg(salary) from employee group by post having avg(salary) > 10000;查询各岗位平均薪资大于10000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) <20000;查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
select count(distinct post) from employee;
去重distinct 统计个数count
示例:
select distinct post from employee;
注意问题:select的字段必须写在distinct的后面,并且如果写了多个字段,比如:
select distinct post,id from employee;这句话,意思就是post和id两个组合在一起同时重复的才算是重复数据
按单列排序 salary排序的
SELECT * FROM employee ORDER BY salary; #默认是升序排列
SELECT * FROM employee ORDER BY salary ASC; #升序
SELECT * FROM employee ORDER BY salary DESC; #降序
多条件排序
按照age字段升序,age相同的数据,按照salary降序排列
select * from employee order by age asc ,salary esc;
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;
取出工资最高的前三位
SELECT * FROM employee ORDER BY salary DESC
LIMIT 3; 默认初始位置为0,从第一条开始顺序取出三条
SELECT * FROM employee ORDER BY salary DESC
LIMIT 0,5; 从第0开始,即先查询出第一条,然后包含这一条在内往后查5条
SELECT * FROM employee ORDER BY salary DESC
LIMIT 5,5; 从第5开始,即先查询出第6条,然后包含这一条在内往后查5条
==到目前为止,单表查询所有的语法都讲完了,语法就是按照我们博客最上面说的语法顺序来写,但是执行的时候,要按照对应的各个方法的优先级去执行。==
mysql> create table tt2(id int primary key auto_increment,name char(10));
mysql> create table tt3(id int,pid int,foreign key(pid) references tt2(id) on delete set null);
Query OK, 0 rows affected (1.06 sec)
mysql> desc tt3;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| pid | int(11) | YES | MUL | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> insert into tt2(name) values('xx1'),('xx2');
Query OK, 2 rows affected (0.14 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into tt3 values(1,1),(2,1);
Query OK, 2 rows affected (0.12 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from tt3;
+------+------+
| id | pid |
+------+------+
| 1 | 1 |
| 2 | 1 |
+------+------+
2 rows in set (0.00 sec)
mysql> delete from tt2 where id = 1;
Query OK, 1 row affected (0.10 sec)
mysql> select * from tt3;
+------+------+
| id | pid |
+------+------+
| 1 | NULL |
| 2 | NULL |
+------+------+
2 rows in set (0.00 sec)
之前我们用like做模糊匹配,只有%和_,局限性比较强,所以我们说一个正则,之前我们是不是学过正则匹配,你之前学的正则表达式都可以用,正则是通用的
SELECT * FROM employee WHERE name REGEXP '^ale';
SELECT * FROM employee WHERE name REGEXP 'on$';
SELECT * FROM employee WHERE name REGEXP 'm{2}';
小结:对字符串匹配的方式
WHERE name = 'egon';
WHERE name LIKE 'yua%';
WHERE name REGEXP 'on$';
查看所有员工中名字是jin开头,n或者g结果的员工信息
select * from employee where name regexp '^jin.*[g|n]$';
强调:聚合函数聚合的是组的内容,若是没有分组,则默认一组
SELECT COUNT(*) FROM employee; count是统计个数
SELECT COUNT(*) FROM employee WHERE depart_id=1; 后面跟where条件的意思是统计一下满足depart_id=1这个的所有记录的个数
SELECT MAX(salary) FROM employee; max()统计分组后每组的最大值,这里没有写group by,那么就是统计整个表中所有记录中薪资最大的,薪资的值
SELECT MIN(salary) FROM employee;
SELECT AVG(salary) FROM employee;
SELECT SUM(salary) FROM employee;
SELECT SUM(salary) FROM employee WHERE depart_id=3;
#查询员工平均年龄在25岁以上的部门名,可以用连表,也可以用子查询,我们用子查询来搞一下
select id,name from department
where id in
(select dep_id from employee group by dep_id having avg(age) > 25);
#连表来搞一下上面这个需求
select department.name from department inner join employee on department.id=employee.dep_id
group by department.name
having avg(age)>25;
总结:子查询的思路和解决问题一样,先解决一个然后拿着这个的结果再去解决另外一个问题,连表的思路是先将两个表关联在一起,然后在进行group by啊过滤啊等等操作,两者的思路是不一样的
#查看技术部员工姓名
select name from employee
where dep_id in
(select id from department where name='技术');
#查看不足1人的部门名(子查询得到的是有人的部门id)
select name from department where id not in (select distinct dep_id from employee);
待定代码有问题,没有表,无法验证
#比较运算符:=、!=、>、>=、<、<=、<>
#查询大于所有人平均年龄的员工名与年龄
mysql> select name,age from emp where age > (select avg(age) from emp);
#查询大于部门内平均年龄的员工名、年龄
select t1.name,t1.age from emp t1
inner join
(select dep_id,avg(age) avg_age from emp group by dep_id) t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age;
EXISTS关字键字表示存在。在使用EXISTS关键字时,内层查询语句不返回查询的记录。而是返回一个真假值。True或False
当返回True时,外层查询语句将进行查询;当返回值为False时,外层查询语句不进行查询。还可以写not exists,和exists的效果就是反的
select * from employee
where exists
(select id from department where id=200);
department表中存在dept_id=203,Ture
不存在的时候是错 返回 Empty set (0.00 sec)
单独使用GROUP BY关键字分组
SELECT post FROM employee GROUP BY post;
注意:我们按照post字段分组,那么select查询的字段只能是post,想要获取组内的其他相关信息,需要借助函数
GROUP BY关键字和GROUP_CONCAT()函数一起使用,比如说我想按部门分组,每个组有哪些员工,都显示出来
SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post; 按照岗位分组,并查看组内所有成员名,通过逗号拼接在一起
SELECT post,GROUP_CONCAT(name,':',salary) as emp_members FROM employee GROUP BY post;按照岗位分组,并查看组内所有成员名,通过逗号拼接在一起
GROUP BY一般都会与聚合函数一起使用,聚合是什么意思:聚合就是将分组的数据聚集到一起,合并起来搞事情,拿到一个最后的结果
select post,count(id) as count from employee group by post; 按照岗位分组,并查看每个组有多少人,每个人都有唯一的id号,我count是计算一下分组之后每组有多少的id记录,通过这个id记录我就知道每个组有多少人了
关于集合函数,mysql提供了以下几种聚合函数:count、max、min、avg、sum等,上面的group_concat也算是一个聚合函数了,做字符串拼接的操作
1. 查询岗位名以及岗位包含的所有员工名字
'''
a、先看一下和哪个表有关系:所有的信息都在employee这个表里面,所以先写from employee,找到表了
b、看有没有什么过滤条件,大于小于啊什么的,没有吧,所以是不是不需要写where条件啊
c、看看有没有分组的内容,也就是看看上面的需求里面有没有分类的概念,发现是不是有啊,按照岗位来分组,对不对,所以该写什么了:from employee group by post;
d、然后再看需要查什么字段出来,发现是不是要看岗位名和所有员工的名字啊,所以怎么写:select post,group_concat(name) from employee group by post;这就是完整语句了,不信你试试
'''
下面的题都按照上面这个逻辑来搞一搞:
2. 查询岗位名以及各岗位内包含的员工个数
3. 查询公司内男员工和女员工的个数
4. 查询岗位名以及各岗位的平均薪资
5. 查询岗位名以及各岗位的最高薪资
6. 查询岗位名以及各岗位的最低薪资
7. 查询男员工与男员工的平均薪资,女员工与女员工的平均薪资。
8.统计各部门年龄在30岁及以上的员工平均薪资
想一下怎么写,1、from 表 2、where age>=30得到一张虚拟表 3、对虚拟表按部门group by 4、select 部门和聚合函数avg
答案:select post,avg(salary) from employee where age>=30 group by post;
看结果:
mysql> select post,avg(salary) from employee where age>=30 group by post; 因为有的部门里面的员工没有大于30岁的,所以没有显示出所有的部门
+---------+---------------+
| post | avg(salary) |
+---------+---------------+
| sale | 2500.240000 |
| teacher | 255450.077500 |
+---------+---------------+
2 rows in set (0.09 sec)
到这里我们的group by就讲完了,看一下我们完整查询语句里面还有什么
SELECT distinct 字段1,字段2... FROM 库名.表名
WHERE 条件
GROUP BY field(字段)
HAVING 筛选 #过滤,过滤之后执行select后面的字段筛选,就是说我要确定一下需要哪个字段的数据,你查询的字段数据进行去重,然后在进行下面的操作
ORDER BY field(字段) #将结果按照后面的字段进行排序
LIMIT 限制条数
注意:虽然语法里面我们先写的select,但是并不是先执行的select,按照mysql自己的规范来执行的下面关键字的优先级
from
where
group by
having
select
distinct
order by
limit
#部门表
create table department(
id int,
name varchar(20)
);
#员工表,之前我们学过foreign key,强行加上约束关联,但是我下面这个表并没有直接加foreign key,这两个表我只是让它们在逻辑意义上有关系,并没有加foreign key来强制两表建立关系,为什么要这样搞,是有些效果要给大家演示一下
#所以,这两个表是不是先建立哪个表都行啊,如果有foreign key的话,是不是就需要注意表建立的顺序了。那我们来建表。
create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
#给两个表插入一些数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'); #注意这一条数据,在下面的员工表里面没有对应这个部门的数据
insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204) #注意这条数据的dep_id字段的值,这个204,在上面的部门表里面也没有对应的部门id。所以两者都含有一条双方没有涉及到的数据,这都是为了演示一下效果设计的昂
;
#查看表结构和数据
mysql> desc department;
#查看
mysql> desc employee;
mysql> select * from department;
mysql> select * from employee1;
重点:外链接语法
SELECT 字段列表
FROM 表1 INNER|LEFT|RIGHT JOIN 表2
ON 表1.字段 = 表2.字段;
查询顺序不一样 一个在前一个在后
没有给条件所以全部显示 这就是笛卡尔积
mysql> select * from department,employee;
mysql> select * from employee,department;
mysql> select * from employee,department where employee.dep_id=department.id; #拿出id对应的数据,后面加判断
select employee.name from employee,department where employee.dep_id=department.id and department.name='技术';#有两个表的时候前面显示的name必须指定表,否则会报错
left join 左边语法
select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;#以左表为准,即找出所有员工信息,当然包括没有部门的员工 本质就是:在内连接的基础上增加左边有右边没有的结果
right join 右边语法
select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;#以右表为准,即找出所有部门信息,包括没有员工的部门 本质就是:在内连接的基础上增加右边有左边没有的结果
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
;
#注意:mysql不支持全外连接 full JOIN 强调:mysql可以使用此种方式间接实现全外连接
#注意 union与union all的区别:union会去掉相同的纪录,因为union all是left join 和right join合并,所以有重复的记录,通过union就将重复的记录去重了。
select employee.name,department.name
from employee inner join department on
employee,dep_id=department.id where age>25;
#以内连接的方式查询employee和department表,并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
select employee.id,employee.name,employee.age,department.name
from employee,department
where employee.dep_id = department.idand age > 25
order by age asc;
#以内连接的方式查询employee和department表,并且以age字段的升序方式显示
select * from employee inner join department on employee.dep_id = department.id; #查看那些部门有哪些员工,先连表 在查询
select employee.name from employee inner join department on employee.dep_id = department.id where department.name='技术';#查看某个部门的员工名单,然后根据连表的结果进行where过滤,将select*改为select employee.name
select name from employee where dep_id = (select id from department where name='技术');#那我们把上面的查询结果用括号括起来,它就表示一条id=200的数据,然后我们通过员工表来查询dep_id=这条数据作为条件来查询员工的name
这些就是子查询的一个思路,解决一个问题,再解决另外一个问题,你子查询里面可不可以是多个表的查询结果,当然可以,然后再通过这个结果作为依据来进行过滤,然后我们学一下子查询里面其他的内容,
子查询:
1:子查询是将一个查询语句嵌套在另一个查询语句中。
2:内层查询语句的查询结果,可以为外层查询语句提供查询条件。
3:子查询中可以包含:IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
4:还可以包含比较运算符:= 、 !=、> 、<等
图形化操作数据库表
掌握:
1. 测试+链接数据库
2. 新建库
3. 新建表,新增字段+类型+约束
4. 设计表:外键
5. 新建查询
6. 备份库/表
注意:
批量加注释:ctrl+?键
批量去注释:ctrl+shift+?键
先安装pymysql第三方模块,导入模块.
import pymysql
conn = pymysql.connect(
host='127.0.0.1', #主机
port=3306, #端口号
user='root',#用户名
password='666', #密码
database='day43', #需要连接的库
charset='utf8'
)
cursor = conn.cursor()
sql = "select * from dep;"
ret = cursor.execute(sql) #ret 受影响的行数
print(cursor.fetchall()) #取出所有的
print(cursor.fetchmany(3)) #取出多条
print(cursor.fetchone()) #取出单条
cursor.scroll(3,'absolute') #绝对移动,按照数据最开始位置往下移动3条
cursor.scroll(3,'relative') #相对移动,按照当前光标位置往下移动3条
conn.commit() #增删改操作时,需要进行提交
sql注入:解决方案
cursor.execute(sql,[参数1,参数2...])
不知道密码或者用户名就可以登陆的操作
防止没有密码登陆操作(了解)
聚集索引(主键) id int primary key
普通索引 Index index_name(id)
唯一索引 int unique
主键:
表创建完了之后添加: Alter table 表名 add primary key(id)
删除主键索引: Alter table 表名 drop primary key;
唯一索引:
表创建好之后添加唯一索引: alter table s1 add unique key u_name(id);
删除: alter table s1 drop unique key u_name;
普通索引:
创建:
Create table t1(
Id int,
Index index_name(id)
)
Alter table s1 add index index_name(id);
Create index index_name on s1(id);
删除:
Alter table s1 drop index u_name;
DROP INDEX 索引名 ON 表名字;
联合索引(联合主键\联合唯一\联合普通索引)
Create table t1(
Id int,
name char(10),
Index index_name(id,name)
)
b+树:提高查询效率
聚集索引
组织存储整表所有数据的依据
id primary key
叶子节点存的是真是数据,存的是整行记录
辅助索引(普通索引)
普通索引建立树形结构,提高查询效率,但是叶子节点存的是该列的数据和对应行的主键值
index name_index(name)
unique name_index(name)
select name from 表名 where name='xx';
叶子节点找到了对应数据,称为覆盖索引
找不到列数据,需要回表操作(拿着主键重新走一遍主键的树形结构找到对应行的那一列数据)
索引分类
主键索引 唯一索引 普通索引
联合主键索引 联合唯一索引 联合普通索引
联合索引的最左匹配特性
多条件查询时
where name='xx' and age='xx' and sex='xx'
index name_index(name,age,sex)
where age='xx' and sex='xx'
mysqldump
备份:mysqldump -uroot -p -B -d 库名>路径(g:\av\av.sql)
还原:mysql -uroot -p < 路径(g:\av\av.sql)
锁和事务
innodb存储引擎默认是行级锁
myISAM 表锁
select * from xx where xx=xx for update; 排它锁
事务
原子性 一致性 隔离性 持久性
begin; 或者 start transaction;
commit; 提交
rollback; 回滚
事务介绍:
简单地说,事务就是指逻辑上的一组SQL语句操作,组成这组操作的各个SQL语句,执行时要么全成功要么全失败。
例如:你给我转账5块钱,流程如下
a.从你银行卡取出5块钱,剩余计算money-5
b.把上面5块钱打入我的账户上,我收到5块,剩余计算money+5.
上述转账的过程,对应的sql语句为:
update 你_account set money=money-5 where name='你';
update 我_account set money=money+5 where name='我';
上述的两条SQL操作,在事务中的操作就是要么都执行,要么都不执行,不然钱就对不上了。
这就是事务的原子性(Atomicity)。
事务的四大特性:
1.原子性(Atomicity)
事务是一个不可分割的单位,事务中的所有SQL等操作要么都发生,要么都不发生。
2.一致性(Consistency)
事务发生前和发生后,数据的完整性必须保持一致。
3.隔离性(Isolation)
当并发访问数据库时,一个正在执行的事务在执行完毕前,对于其他的会话是不可见的,多个并发事务之间的数据是相互隔离的。也就是其他人的操作在这个事务的执行过程中是看不到这个事务的执行结果的,也就是他们拿到的是这个事务执行之前的内容,等这个事务执行完才能拿到新的数据。
4.持久性(Durability)
一个事务一旦被提交,它对数据库中的数据改变就是永久性的。如果出了错误,事务也不允撤销,只能通过'补偿性事务'。
事务的开启:
数据库默认事务是自动提交的,也就是发一条sql他就执行一条。如果想多条sql放在一个事务中执行,则需要使用事务进行处理。当我们开启一个事务,并且没有提交,mysql会自动回滚事务。或者我们使用rollback命令手动回滚事务。