![MySQL
[数据库教程]](/upload/202205/01/s3nywyw3tov.jpg)
萌芽阶段
-
放在内存里——瞬时：程序结束，上次的数据就没啦
-
-
不安全
-
不方便在中间插入、删除数据
-
但也不是一无是处，可以应用于配置文件：.xml文件、.ini文件等等
-
数据库的产生
核心：CRUD（最难的是“查找”）
-
层次模型
-
优点：层次清晰
-
缺点：（假如一张“专业”表有你，“学校”表也有你）
-
查询效率低下（不知道查哪张）
-
数据不完整（这张表改了那张表没改）
-
-
致命：重复就GG了
-
-
网状模型
-
优点：解决复杂问题，数据也完整了，重复的话也只操作一个文件
-
缺点：重复文件没法解决特殊性问题（假设计算机一班的人和上mysql课的有一部分人都是）
-
计算机一班的每人收100元
-
上mysql课的每人收233元
-
-
致命：并未解决导航问题
-
-
关系型
-
解决了导航问题：比如每张表都有个学号（公共的编号）
-
分批管理，各管各的
-
数据完整，层次清晰
-
说白了，关系就是共性，为了让每张表都能找到爹
-
**和谁都有关系，但是又互不影响
-
数据库基本操作
-
CRUD：增删改查（查找是最难的）
-
创建数据库：
create database if not exists `student`;-
删除数据库：
drop database if exists student;-
查看创建的数据库：
show create database `teacher`;-
乱码->字符编码：GBK、UTF-8(在学习中用GBK，企业中UTF-8)
create database if not exists `student` charset=gbk;-
修改、更新数据库字符编码：
alter database teacher charset=gbk;表
-
使用数据库 -> 创建表
-
使用数据库(use + 数据库名)：
use frank_school;-
查看表：
show tables;-
创建表：
create table student(
id int,
name varchar(30),
age int
);-
创建表（B格）：
create table teacher(
id int auto_increment primary key comment ‘主键id‘,
#auto_increment 自动增长
#primary key 主键，最主要的，用来区分
#comment 注释
name varchar(30) not null,
#not null 不能为空
phone varchar(20) comment ‘电话号码‘,
address varchar(100) default ‘暂时未知‘ comment ‘住址‘
#default 默认值，此处为‘暂时未知‘
)engine=innodb;-
查看表的结构：
desc student;
desc teacher;-
删除表：
drop table if exists oooo, stu,jjj;-
修改表：
-
添加字段：
#默认在最后面
alter table student add phone varchar(20);
#指定插入在哪，在name前面
alter table student add phone varchar(20) after name;
#在最前面
alter table student add phone varchar(20) first;-
删除字段：
drop table s;
alter table student drop address;in-
修改字段：
#既可以修改名字，也可以修改类型
alter table student change phone tel_phone int (11);
#只改变类型
alter table student modify tel_phone int (13);数据操作
-
插入数据
insert into teacher (id, name, phone, address) values(1, ‘Frank‘, ‘1888888‘, ‘ShangHai‘);
# 这样就必须按照创建表的时候的顺序填写
insert into teacher values(1, ‘Frank‘, ‘1888888‘, ‘ShangHai‘);-
查看数据
select * from teacher;
# *:全部
# from + 表-
删除数据
# 删除id为6的数据
delete from teacher where id=6;
# 删除大于三十岁的数据
delete from student where age>30;
# 清除表的所有数据（不建议:慢）
# 坏处：再创建数据的时候，id等自增数据，会接着被清空之前的id自动增长
delete from teacher;
# 建议这种方法清除数据
# 好处：id等自增数据，不会接着被清空之前的id
truncate table student;-
更新数据
# 将id=1的name，改为frank
# 注意where后面的，注意不要有重复的id等！！！！
update teacher set name=‘frank‘ where id=1;
# 也可以改多个数据
update teacher set name=‘frank‘,phone=‘1111‘ where id=1;
# 如果不写where，那么将改变所有的数据
update teacher set name=‘frank‘;
# SQL注入攻击就是改变where
# 多个条件的情况
# 将phone为1111111或者2222的数据修改address的值
update teacher set address=‘shanghai‘ where phone=‘1111111‘ or phone=‘2222‘;-
查询表数据
# 查询teacher表中的id跟phone的数据
select id,phone from teacher;
# 查询teacher表中所有的数据
select * from teacher;-
DDL：data definition language 数据库定义语言（create、alter、drop、show...)
-
DML：data manipulation language 数据操纵语言（insert、update、select...)
-
DCL： data control language
-
查找所有字符集设置
show variables like ‘character_set_%‘;-
设置客户端字符集
set character_set_client=utf8;列属性完整性
-
auto_increment自增属性一定是主键
-
auto_increment的属性中，删除id=3的数据后，就不能再插入一个id为3的数据了
-
删除主键：
alter table t_8 drop primary key; -
unique唯一键添加：
create table t_9( id int primary key, phone varchar(20) unique );
-
两个unique唯一键：
create table t_11( id int, name varchar(20) unique, phone varchar(20) unique ); -
删除唯一键：
alter table t_11 drop index phone;
// 执行后，phone不是唯一键了-
主键与唯一键的区别：
-
主键不能重复，不能空
-
；唯一键也可以重复，可以为空；
-
sql内注释和代码注释：
create table t_12(
id int(20),
name varchar(20) comment ‘姓名‘
);
// comment注释
show create table t_12;
// 会显示怎么创建的，包括注释都可以看到-
数据库完整性：
-
要有一个主键，自动增长；
-
有些要有default，暂无，例子：有同学考试缺考，就要用default
-
外键：用于连接两个表，
create table eatery(
id int primary key,
money decimal(10,4),
stuId int(4),
foreign key (stuId) references stu(stuId)
);
// 从stu表中的stuId创建外键-
显示表怎么创建的：
show create table eatery;
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| eatery | CREATE TABLE `eatery` (
`id` int(11) NOT NULL,
`money` decimal(10,4) DEFAULT NULL,
`stuId` int(4) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `stuId` (`stuId`),
CONSTRAINT `eatery_ibfk_1` FOREIGN KEY (`stuId`) REFERENCES `stu` (`stuId`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)-
删除外键
alter table eatery drop foreign key eatery_ibfk_1;-
置空：删除数据（只更删除几个）
-
级联：更新数据（操作的是所有的数据）
// 创建主表
create table stu(
-> stuId int(4) primary key,
-> name varchar(20)
-> );
// 创建从表
create table eatery(
-> id int (4) primary key,
-> money decimal(10,4),
-> stuId int(4),
-> foreign key(stuId) references stu(stuId) on delete set null on update cascade
// on delete set null：当删除时，置空
// on update cascade：当更新时，级联
-> );
数据库设计
-
关系：关系型数据库，有两张表的共有字段去确定数据的完整性
-
行：一条数据，一条数据记录，实体
-
列：一个字段，属性
-
一对一，一对多，多对一，多对多
-
第一范式 确保每列原子性
-
第二范式 非键字段必须依赖键字段
-
第三范式 消除传递依赖
单表查询
-
select
mysql> select 3*4;
+-----+
| 3*4 |
+-----+
| 12 |
+-----+
1 row in set (0.00 sec)
// 起“别名”
mysql> select 2*6 as res;
+-----+
| res |
+-----+
| 12 |
+-----+
1 row in set (0.00 sec)-
from
mysql> select * from t1;
+------+-------+
| id | name |
+------+-------+
| 1 | frank |
| 2 | jerry |
+------+-------+
2 rows in set (0.00 sec)
mysql> select * from t2;
+--------+--------+
| score1 | score2 |
+--------+--------+
| 98 | 99 |
| 90 | 77 |
+--------+--------+
2 rows in set (0.00 sec)
mysql> select * from t1,t2;
// 返回笛卡尔积
+------+-------+--------+--------+
| id | name | score1 | score2 |
+------+-------+--------+--------+
| 1 | frank | 98 | 99 |
| 2 | jerry | 98 | 99 |
| 1 | frank | 90 | 77 |
| 2 | jerry | 90 | 77 |
+------+-------+--------+--------+
4 rows in set (0.00 sec)-
where
mysql> select * from t4;
+------+----------+
| id | address |
+------+----------+
| 1 | shanghai |
| 2 | shanghai |
| 3 | beijing |
| 4 | shenzhen |
+------+----------+
4 rows in set (0.00 sec)
// where限定查找某一个范围
mysql> select * from t4 where address = ‘shanghai‘;
+------+----------+
| id | address |
+------+----------+
| 1 | shanghai |
| 2 | shanghai |
+------+----------+
2 rows in set (0.00 sec)
mysql> select * from t4 where address = ‘shenzhen‘;
+------+----------+
| id | address |
+------+----------+
| 4 | shenzhen |
+------+----------+
1 row in set (0.00 sec)
mysql> select * from t4 where address = ‘shenzhen ‘;
+------+----------+
| id | address |
+------+----------+
| 4 | shenzhen |
+------+----------+
1 row in set (0.00 sec)-
in
// 跟or一样
mysql> select * from t4 where address in(‘beijing‘,‘shanghai‘);
+------+----------+
| id | address |
+------+----------+
| 1 | shanghai |
| 2 | shanghai |
| 3 | beijing |
+------+----------+
3 rows in set (0.00 sec)-
between...and...
mysql> select * from t3 where age between 15 and 19;
+------+------+
| id | age |
+------+------+
| 1 | 18 |
| 2 | 19 |
| 3 | 19 |
| 4 | 16 |
| 5 | 17 |
+------+------+
5 rows in set (0.00 sec)-
is null
// 查找为空的
// is not null
mysql> select * from t3 where age is null;
+------+------+
| id | age |
+------+------+
| 7 | NULL |
+------+------+
1 row in set (0.00 sec)-
聚合函数
mysql> select * from score;
+------+---------+---------+------+
| id | chinese | english | math |
+------+---------+---------+------+
| 1 | 98 | 99 | 100 |
| 2 | 90 | 95 | 85 |
+------+---------+---------+------+
2 rows in set (0.00 sec)
// 求和
mysql> select sum(chinese) from score;
+--------------+
| sum(chinese) |
+--------------+
| 188 |
+--------------+
1 row in set (0.00 sec)
// 平均值
mysql> select avg(chinese) from score;
+--------------+
| avg(chinese) |
+--------------+
| 94.0000 |
+--------------+
1 row in set (0.00 sec)
// 最大值
mysql> select max(chinese) from score;
+--------------+
| max(chinese) |
+--------------+
| 98 |
+--------------+
1 row in set (0.00 sec)
// 最小值
mysql> select min(chinese) from score;
+--------------+
| min(chinese) |
+--------------+
| 90 |
+--------------+
1 row in set (0.00 sec)
// 求数量（很坑，不建议用）
mysql> select count(*) from score;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)-
like模糊查询
mysql> select * from student;
+------+--------+--------+------+-----------+
| id | name | gender | age | tel_phone |
+------+--------+--------+------+-----------+
| 1 | 张三 | 0 | 18 | 155555 |
| 2 | 张四 | 1 | 20 | 16541654 |
| 3 | 李四 | 1 | 22 | 1231 |
| 4 | 张某某 | 0 | 15 | 15646 |
+------+--------+--------+------+-----------+
4 rows in set (0.00 sec)
// %代表多个字符
mysql> select * from student where name like ‘张%‘;
+------+--------+--------+------+-----------+
| id | name | gender | age | tel_phone |
+------+--------+--------+------+-----------+
| 1 | 张三 | 0 | 18 | 155555 |
| 2 | 张四 | 1 | 20 | 16541654 |
| 4 | 张某某 | 0 | 15 | 15646 |
+------+--------+--------+------+-----------+
3 rows in set (0.00 sec)
// _代表一个字符
mysql> select * from student where name like ‘张_‘;
+------+------+--------+------+-----------+
| id | name | gender | age | tel_phone |
+------+------+--------+------+-----------+
| 1 | 张三 | 0 | 18 | 155555 |
| 2 | 张四 | 1 | 20 | 16541654 |
+------+------+--------+------+-----------+
2 rows in set (0.00 sec)-
order by 排序查询
mysql> select * from score;
+------+---------+---------+------+
| id | chinese | english | math |
+------+---------+---------+------+
| 1 | 98 | 99 | 100 |
| 2 | 90 | 95 | 85 |
| 3 | 87 | 79 | 99 |
+------+---------+---------+------+
3 rows in set (0.00 sec)
// asc升序
mysql> select * from score order by english asc;
+------+---------+---------+------+
| id | chinese | english | math |
+------+---------+---------+------+
| 3 | 87 | 79 | 99 |
| 2 | 90 | 95 | 85 |
| 1 | 98 | 99 | 100 |
+------+---------+---------+------+
3 rows in set (0.00 sec)
// desc降序
mysql> select * from score order by english desc;
+------+---------+---------+------+
| id | chinese | english | math |
+------+---------+---------+------+
| 1 | 98 | 99 | 100 |
| 2 | 90 | 95 | 85 |
| 3 | 87 | 79 | 99 |
+------+---------+---------+------+
3 rows in set (0.00 sec)-
group by 分组查询
mysql> select * from info;
+----+------+--------+---------+
| id | age | gender | address |
+----+------+--------+---------+
| 1 | 25 | 男 | 上海 |
| 2 | 23 | 女 | 上海 |
| 3 | 24 | 女 | 北京 |
| 4 | 26 | 男 | 北京 |
| 5 | 22 | 男 | 上海 |
+----+------+--------+---------+
5 rows in set (0.00 sec)
// 按照性别分组统计平均年龄
// 1.可以分组2.聚合函数
mysql> select avg(age) as ‘年龄‘,gender as ‘性别‘ from info group by gender;
+---------+------+
| 年龄 | 性别 |
+---------+------+
| 24.3333 | 男 |
| 23.5000 | 女 |
+---------+------+
2 rows in set (0.00 sec)-
group_concat
mysql> select * from student;
+----+------+-------+--------+---------+
| id | age | name | gender | phone |
+----+------+-------+--------+---------+
| 1 | 18 | ???? | 0 | 115555 |
| 2 | 20 | ?? | 1 | 5621654 |
| 3 | 31 | ????? | 1 | 15646 |
| 4 | 32 | ?? | 1 | 4856748 |
+----+------+-------+--------+---------+
4 rows in set (0.00 sec)
// 根据gender将id分组，进行聚合显示
mysql> select group_concat(id),gender from student group by gender;
+------------------+--------+
| group_concat(id) | gender |
+------------------+--------+
| 1 | 0 |
| 2,3,4 | 1 |
+------------------+--------+
2 rows in set (0.01 sec)-
having
mysql> desc info;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| age | int(11) | YES | | NULL | |
| gender | varchar(5) | YES | | NULL | |
| address | varchar(255) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
// having是在查询之后的数据进行筛选（已经查询之后的）
mysql> SELECT avg(age) AS ‘age‘,address AS ‘address‘ FROM info GROUP BY address HAVING age > 24;
+---------+---------+
| age | address |
+---------+---------+
| 24.5000 | beijing |
+---------+---------+
1 row in set (0.00 sec)-
limit
mysql> select * from info
-> ;
+----+------+--------+----------+
| id | age | gender | address |
+----+------+--------+----------+
| 1 | 25 | 0 | shanghai |
| 2 | 23 | 1 | shanghai |
| 3 | 26 | 1 | beijing |
| 4 | 23 | 0 | beijing |
| 5 | 21 | 0 | shanghai |
| 6 | 24 | 1 | shenzhen |
+----+------+--------+----------+
6 rows in set (0.00 sec)
// 从下标为0开始，查两个
mysql> select * from info limit 0,2;
+----+------+--------+----------+
| id | age | gender | address |
+----+------+--------+----------+
| 1 | 25 | 0 | shanghai |
| 2 | 23 | 1 | shanghai |
+----+------+--------+----------+
2 rows in set (0.00 sec)
// 降序前三个
mysql> select * from info order by age desc limit 3;
+----+------+--------+----------+
| id | age | gender | address |
+----+------+--------+----------+
| 3 | 26 | 1 | beijing |
| 1 | 25 | 0 | shanghai |
| 6 | 24 | 1 | shenzhen |
+----+------+--------+----------+
3 rows in set (0.00 sec)-
distinct
mysql> select distinct address from info;
+----------+
| address |
+----------+
| shanghai |
| beijing |
| shenzhen |
+----------+
3 rows in set (0.00 sec)
// 去除重复的，计算个数
mysql> select count(distinct address) from info;
+-------------------------+
| count(distinct address) |
+-------------------------+
| 3 |
+-------------------------+
1 row in set (0.00 sec)多表查询
-
union联合查询
mysql> select * from teacher;
+------+------+--------+----------+
| id | age | gender | address |
+------+------+--------+----------+
| 1 | 25 | 0 | shanghai |
| 2 | 23 | 1 | shanghai |
| 3 | 26 | 1 | beijing |
| 4 | 23 | 0 | beijing |
| 5 | 21 | 0 | shanghai |
| 6 | 24 | 1 | shenzhen |
+------+------+--------+----------+
6 rows in set (0.00 sec)
mysql> select * from info;
+----+------+--------+----------+
| id | age | gender | address |
+----+------+--------+----------+
| 1 | 25 | 0 | shanghai |
| 2 | 23 | 1 | shanghai |
| 3 | 26 | 1 | beijing |
| 4 | 23 | 0 | beijing |
| 5 | 21 | 0 | shanghai |
| 6 | 24 | 1 | shenzhen |
+----+------+--------+----------+
6 rows in set (0.00 sec)
mysql> select age,gender from info union select `name`,phone from student;
+-------+---------+
| age | gender |
+-------+---------+
| 25 | 0 |
| 23 | 1 |
| 26 | 1 |
| 23 | 0 |
| 21 | 0 |
| 24 | 1 |
| ???? | 115555 |
| ?? | 5621654 |
| ????? | 15646 |
| ?? | 4856748 |
+-------+---------+
10 rows in set (0.00 sec)-
inner join（内连接）
// 查name和score
mysql> select
name,
score
from
student
inner join score on student.id=score.stuid;
+----------+-------+
| name | score |
+----------+-------+
| zhangsan | 89.00 |
| ren | 80.00 |
| lalala | 70.00 |
+----------+-------+
3 rows in set (0.00 sec)-
left join（左连接同理right join）
// 以左表为基准，该方式student为左表
mysql> select
-> name,
-> score
-> from
-> student left join score
-> on student.id = score.stuid;
+----------+-------+
| name | score |
+----------+-------+
| zhangsan | 89.00 |
| ren | 80.00 |
| lalala | 70.00 |
| moumou | NULL |
+----------+-------+
4 rows in set (0.00 sec)-
cross join（两个表的笛卡尔积）
-
natural join：公共的字段，条件是有同名的字段，然后相当于将两个表内、左、右连接
若没有公共字段即同名的字段，则返回笛卡尔积
-
using：当两个表的字段完全相同，若内连接，则返回空，这就要用using指定字段
-
不推荐using和natural join，一般使用inner join
子查询
-
基本语句
mysql> select * from score;
+----+-------+-------+
| id | stuid | score |
+----+-------+-------+
| 1 | 1 | 89.00 |
| 2 | 3 | 80.00 |
| 3 | 4 | 70.00 |
+----+-------+-------+
3 rows in set (0.02 sec)
// 多个结果要用in不用=，还有not in
mysql> select * from student where id in (select stuid from score where score >= 75);
+----+------+----------+--------+--------+
| id | age | name | gender | phone |
+----+------+----------+--------+--------+
| 1 | 18 | zhangsan | 0 | 115555 |
| 3 | 31 | ren | 1 | 15646 |
+----+------+----------+--------+--------+
2 rows in set (0.01 sec)-
exists和not exists
mysql> select * from score;
+----+-------+-------+
| id | stuid | score |
+----+-------+-------+
| 1 | 1 | 89.00 |
| 2 | 3 | 80.00 |
| 3 | 4 | 70.00 |
+----+-------+-------+
3 rows in set (0.00 sec)
mysql> select * from student where exists (select stuid from score where score >=85);
+----+------+----------+--------+---------+
| id | age | name | gender | phone |
+----+------+----------+--------+---------+
| 1 | 18 | zhangsan | 0 | 115555 |
| 2 | 20 | moumou | 1 | 5621654 |
| 3 | 31 | ren | 1 | 15646 |
| 4 | 32 | lalala | 1 | 4856748 |
+----+------+----------+--------+---------+
4 rows in set (0.00 sec)
mysql> select * from student where exists (select stuid from score where score >=100);
Empty set (0.00 sec)视图
-
隐藏敏感的数据
-
降低sql语句的复杂度
-
创建
// 这样就可以只看到student的name和phone，而不用看到其他敏感的数据
create view vw_stu as
select name,phone from student;
// 保存视图之后，就可以直接查视图，而不用再把查询语句写一遍
select * from vw_stu;-
显示视图
// 显示视图，所以必须要用前缀区分
mysql> show tables;
+------------------------+
| Tables_in_frank_school |
+------------------------+
| info |
| score |
| student |
| teacher |
| vw_stu |
+------------------------+
5 rows in set (0.00 sec)
mysql> desc vw_stu;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name | varchar(255) | YES | | NULL | |
| phone | varchar(20) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> show create view vw_stu;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View
| character_set_client | collation_connection |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| vw_stu | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_stu` AS select `student`.`name` AS `name`,`student`.`phone` AS `phone` from `student` | utf8mb4 | utf8mb4_general_ci
|
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)
mysql> show table status where comment = ‘view‘ G
*************************** 1. row ***************************
Name: vw_stu
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: VIEW
1 row in set (0.00 sec)-
更新和删除视图
// 修改视图要查的东西
mysql> alter view vw_stu as select age,name,phone from student;
Query OK, 0 rows affected (0.01 sec)
// 删除
drop view vw_stu;-
将子查询放到视图里的时候要特别注意，要将算法改为temptable
事务
-
只要commit就不能再rollback了
-
MySQL
原文地址：https://www.cnblogs.com/Angela-Joker/p/14602400.html
![[MySQL] MySQL中的事物](https://static.528045.com/imgs/59.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
