MySQL学习笔记

MySQL

MySQL常用数据类型

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

详情见菜鸟教程https://www.runoob.com/mysql/mysql-data-types.html

MySQL建表约束

主键约束

它能够唯一确定一张表中的一条记录，也就是我们通过给某个字段添加主键约束，就可以使得该字段不重复且不为空
如果两个字段设置主键约束则成为联合主键，联合主键的任意字段都不能为空，部分字段可以相同，不能全部相同
```
create table user(
  id int primary key,  --primary key表示主键约束
  name varchar(20));
  
--有两个主键约束
create table user2(id int,
  name varchar(20),
    password varchar(20),
    primary key(id,name));--联合主键，只要两个主键都不相等就可以
```

自增约束

create table user3(
  id int primary key auto_increment,//auto_increment表示自增
  name varchar(20));

如果我们创建表的时候忘记创建主键约束了？该怎么办？

mysql> create table user4 (id int,name varchar(20));
--修改表结构 添加主键
mysql> alter table user4 add primary key(id);
--删除主键
mysql> alter table user4 drop primary key;

唯一约束

约束修饰的字段的值不可以重复

mysql> create table user5(id int ,name varchar(20),unique(name));

mysql> create table user6(id int ,name varchar(20) unique);

非空约束

修饰的字段不能为空

mysql> create table user7(id int ,name varchar(20) not null);

默认约束

就是当我们插入字段值的时候，如果没有传值，就会使用默认值
```
mysql> create table user8(id int , name varchar(20),age int default 10);
```
外键约束

涉及到两个表，一个父表一个子表，或一个主表一个副表。
```
mysql> create table classes(
  id int primary key,
  name varchar(20)
);

mysql> create table students(
  id int primary key,
    name varchar(20),
    class_id int,
    foreign key(class_id) references classes(id) 
);
```
classes为主表，students为副表。

主表中没有的数据值，在副表中不可以使用。主表中的记录如果被副表引用，是不可以删除的。

总结：

建表的时候就添加约束
可以使用alter 。。。add。。。
可以使用alter。。。modify。。。
删除约束alter。。。drop。。。。

数据库的三大设计范式

第一范式1NF

数据表中的所有字段都是不可分割的原子值

字段值还可以拆分的就不满足第一范式

--不满足第一范式 address可以继续拆分
--address >> privence  |  city  |  street  |  detail 
create table student(
    id int primary key;
    name varchar(20);
    address varchar(30);
);
--满足第二范式，字段不可以继续拆分
create table student(
    id int primary key;
    name varchar(20);
    privence varchar(30);
    city(30);
    street varchar(30);
    detail varchar(30)
);

范式设计的越详细，对于某些实际操作可能更好，但也不一定都是好处。

第二范式

必须是满足第一范式的前提下，第二范式要求除主键外的每一列都必须完全依赖于主键
如果必须要出现不完全依赖，只可能发生在联合主键的情况下

create table myorder(
    product_id int,
    customer_id,
    product_name varchar(20),
    customer_name varchar(20),
    primary key(product_id, cunstomer_id)
);

该表不满足第二范式，拆表！

create table myorder(
    order_id int primary key,
    product_id int,
    customer_id int
);
create table product(
    id int primary key,
    name varchar(20)
);
create table customer(
    id int primary key,
    name varchar(20)
);

分成三个表之后就满足了第二范式的设计

第三范式3NF

必须先满足第二范式，除主键列的其他列之间不能有传递依赖关系

create table myorder(
    order_id int primary key,
    product_id int,
    customer_id int,
    customer_phone varchar(15)
);

该表不满足第三范式，customer_id 可以通过 order_id 找到，customer_phone可以由customer_id找到，产生了冗余。

--满足第三范式
create table myorder(
    order_id int primary key,
    product_id int,
    customer_id int
);
create table customer(
    id int primary key,
    name varchar(20),
    customer_phone varchar(15)
);

单表查询

distinct关键词可以去除重复内容

mysql> select t_depart from teacher;
+------------+
| t_depart   |
+------------+
| 计算机系   |
| 计算机系   |
| 电子工程系 |
| 电子工程系 |
+------------+
4 rows in set (0.00 sec)

mysql> select distinct t_depart from teacher;
+------------+
| t_depart   |
+------------+
| 计算机系   |
| 电子工程系 |
+------------+
2 rows in set (0.00 sec)`

between 可用>和< 代替

select * from score where sc_degree between 60 and 80;
select * from score where sc_degree > 60 and sc_degree < 80;
+------+-------+-----------+
| s_no | c_no  | sc_degree |
+------+-------+-----------+
| 105  | 3-245 |        75 |
| 105  | 6-166 |        79 |
| 109  | 3-105 |        76 |
| 109  | 3-245 |        68 |
+------+-------+-----------+

同一字段表示或者关系的查询可用 in

mysql> select * from score where sc_degree in (85, 86, 88);
+------+-------+-----------+
| s_no | c_no  | sc_degree |
+------+-------+-----------+
| 103  | 3-245 |        86 |
| 103  | 6-166 |        85 |
| 105  | 3-105 |        88 |
+------+-------+-----------+

不同字段表示或者关系的查询可用or

mysql> select * from student where s_class = '95031' or s_sex = '女';
+------+--------+-------+---------------------+---------+
| s_no | s_name | s_sex | s_birthday          | s_class |
+------+--------+-------+---------------------+---------+
| 102  | 匡明   | 男    | 1975-10-02 00:00:00 | 95031   |
| 103  | 王丽   | 女    | 1976-01-23 00:00:00 | 95033   |
| 105  | 王芳   | 女    | 1975-02-10 00:00:00 | 95031   |
| 106  | 陆军   | 男    | 1974-06-03 00:00:00 | 95031   |
| 108  | 张全蛋 | 男    | 1975-02-10 00:00:00 | 95031   |
| 109  | 赵铁柱 | 男    | 1974-06-03 00:00:00 | 95031   |
+------+--------+-------+---------------------+---------+

asc 升序 desc 降序 order by默认是升序

mysql> select * from student order by s_class asc;
+------+--------+-------+---------------------+---------+
| s_no | s_name | s_sex | s_birthday          | s_class |
+------+--------+-------+---------------------+---------+
| 102  | 匡明   | 男    | 1975-10-02 00:00:00 | 95031   |
| 105  | 王芳   | 女    | 1975-02-10 00:00:00 | 95031   |
| 106  | 陆军   | 男    | 1974-06-03 00:00:00 | 95031   |
| 108  | 张全蛋 | 男    | 1975-02-10 00:00:00 | 95031   |
| 109  | 赵铁柱 | 男    | 1974-06-03 00:00:00 | 95031   |
| 101  | 曾华   | 男    | 1977-09-01 00:00:00 | 95033   |
| 103  | 王丽   | 女    | 1976-01-23 00:00:00 | 95033   |
| 104  | 李军   | 男    | 1976-02-20 00:00:00 | 95033   |
| 107  | 王尼玛 | 男    | 1976-02-20 00:00:00 | 95033   |
+------+--------+-------+---------------------+---------+
9 rows in set (0.00 sec)

mysql> select * from student order by s_class desc;
+------+--------+-------+---------------------+---------+
| s_no | s_name | s_sex | s_birthday          | s_class |
+------+--------+-------+---------------------+---------+
| 101  | 曾华   | 男    | 1977-09-01 00:00:00 | 95033   |
| 103  | 王丽   | 女    | 1976-01-23 00:00:00 | 95033   |
| 104  | 李军   | 男    | 1976-02-20 00:00:00 | 95033   |
| 107  | 王尼玛 | 男    | 1976-02-20 00:00:00 | 95033   |
| 102  | 匡明   | 男    | 1975-10-02 00:00:00 | 95031   |
| 105  | 王芳   | 女    | 1975-02-10 00:00:00 | 95031   |
| 106  | 陆军   | 男    | 1974-06-03 00:00:00 | 95031   |
| 108  | 张全蛋 | 男    | 1975-02-10 00:00:00 | 95031   |
| 109  | 赵铁柱 | 男    | 1974-06-03 00:00:00 | 95031   |
+------+--------+-------+---------------------+---------+
--多个升序降序要求
mysql> select * from score order by c_no asc,sc_degree desc;
+------+-------+-----------+
| s_no | c_no  | sc_degree |
+------+-------+-----------+
| 103  | 3-105 |        92 |
| 105  | 3-105 |        88 |
| 109  | 3-105 |        76 |
| 103  | 3-245 |        86 |
| 105  | 3-245 |        75 |
| 109  | 3-245 |        68 |
| 103  | 6-166 |        85 |
| 109  | 6-166 |        81 |
| 105  | 6-166 |        79 |
+------+-------+-----------+

统计 count

mysql> select count(*) from student where s_class = '95031';
+----------+
| count(*) |
+----------+
|        5 |
+----------+

用子查询或排序查询最高分

mysql> select s_no,c_no from score where sc_degree=(select max(sc_degree) from score);
+------+-------+
| s_no | c_no  |
+------+-------+
| 103  | 3-105 |
+------+-------+

avg()求平均值

select avg(sc_degree) from score where c_no = '3-105';
+----------------+
| avg(sc_degree) |
+----------------+
|        85.3333 |
+----------------+

group by分组查询

select avg(sc_degree) from score group by c_no;
+----------------+
| avg(sc_degree) |
+----------------+
|        85.3333 |
|        76.3333 |
|        81.6667 |
+----------------+

having

having字句可以让我们筛选分组之后的各种数据，where字句在聚合前先筛选记录，也就是说作用在group by和having字句前。

执行顺序

select –>where –> group by–> having–>order by

 select c_no,avg(sc_degree),count(*) from score group by c_no having count(c_no) >= 2 and c_no like '3%';
+-------+----------------+----------+
| c_no  | avg(sc_degree) | count(*) |
+-------+----------------+----------+
| 3-105 |        85.3333 |        3 |
| 3-245 |        76.3333 |        3 |

any()至少一个
all()表示所有

union求并集

 select t_name as name,t_sex as sex,t_birthday as birthday from teacher union select s_name,s_sex,s_birthday from student;
+--------+-----+---------------------+
| name   | sex | birthday            |
+--------+-----+---------------------+
| 李诚   | 男  | 1958-12-02 00:00:00 |
| 王萍   | 女  | 1972-05-05 00:00:00 |
| 刘冰   | 女  | 1977-08-14 00:00:00 |
| 张旭   | 男  | 1969-03-12 00:00:00 |
| 曾华   | 男  | 1977-09-01 00:00:00 |
| 匡明   | 男  | 1975-10-02 00:00:00 |
| 王丽   | 女  | 1976-01-23 00:00:00 |
| 李军   | 男  | 1976-02-20 00:00:00 |
| 王芳   | 女  | 1975-02-10 00:00:00 |
| 陆军   | 男  | 1974-06-03 00:00:00 |
| 王尼玛 | 男  | 1976-02-20 00:00:00 |
| 张全蛋 | 男  | 1975-02-10 00:00:00 |
| 赵铁柱 | 男  | 1974-06-03 00:00:00 |
+--------+-----+---------------------+

多表查询

mysql> select s_name ,c_no, sc_degree from student,score where student.s_no = score.s_no;
+--------+-------+-----------+
| s_name | c_no  | sc_degree |
+--------+-------+-----------+
| 王丽   | 3-105 |        92 |
| 王丽   | 3-245 |        86 |
| 王丽   | 6-166 |        85 |
| 王芳   | 3-105 |        88 |
| 王芳   | 3-245 |        75 |
| 王芳   | 6-166 |        79 |
| 赵铁柱 | 3-105 |        76 |
| 赵铁柱 | 3-245 |        68 |
| 赵铁柱 | 6-166 |        81 |
+--------+-------+-----------+

复制表中数据做条件查询

mysql> select * from score a where sc_degree < (select avg(sc_degree) from score b where a.c_no = b.c_no);
+------+-------+-----------+
| s_no | c_no  | sc_degree |
+------+-------+-----------+
| 105  | 3-245 |        75 |
| 105  | 6-166 |        79 |
| 109  | 3-105 |        76 |
| 109  | 3-245 |        68 |
| 109  | 6-166 |        81 |
+------+-------+-----------+

连接查询

数据表

+------+------+--------+
| id   | name | cardId |
+------+------+--------+
|    1 | 张三 |      1 |
|    1 | 李四 |      3 |
|    1 | 王五 |      6 |
+------+------+--------+
+------+--------+
| id   | name   |
+------+--------+
|    1 | 饭卡   |
|    2 | 建行卡 |
|    3 | 农行卡 |
|    4 | 工商卡 |
|    5 | 邮政卡 |
+------+--------+

内连接

inner join 或者 join

mysql> select * from person inner join card on person.cardId = card.id;
+------+------+--------+------+--------+
| id   | name | cardId | id   | name   |
+------+------+--------+------+--------+
|    1 | 张三 |      1 |    1 | 饭卡   |
|    1 | 李四 |      3 |    3 | 农行卡 |
+------+------+--------+------+--------+

内联查询，其实就是两张表中的数据，通过某个字段相等，查询相关记录数据

外连接

左连接

left join或者 left outer join

mysql> select * from person left join card on person.cardId = card.id;
+------+------+--------+------+--------+
| id   | name | cardId | id   | name   |
+------+------+--------+------+--------+
|    1 | 张三 |      1 |    1 | 饭卡   |
|    1 | 李四 |      3 |    3 | 农行卡 |
|    1 | 王五 |      6 | NULL | NULL   |
+------+------+--------+------+--------+

左外连接会把左边表里的所有数据取出来，而右边表中的数据，如果有相等的就显示出来，如果没有就显示 NULL

右连接

right join 或者 right left join

mysql> select * from person right join card on person.cardId = card.id;
+------+------+--------+------+--------+
| id   | name | cardId | id   | name   |
+------+------+--------+------+--------+
|    1 | 张三 |      1 |    1 | 饭卡   |
|    1 | 李四 |      3 |    3 | 农行卡 |
| NULL | NULL |   NULL |    2 | 建行卡 |
| NULL | NULL |   NULL |    4 | 工商卡 |
| NULL | NULL |   NULL |    5 | 邮政卡 |
+------+------+--------+------+--------+

右连接会把右边表里的数据全部显示出来，而左边表中的数据，如果有相等的就显示出来，如果没有就显示NULL

完全外连接

full join 或者 full outer join

mysql> select * from person full join card on person.cardId = card.id;
ERROR 1054 (42S22): Unknown column 'person.cardId' in 'on clause'

mysql不支持完全外连接，可以通过左右连接求完全外连接

mysql> select * from person left join card on person.cardId = card.id
    -> union
    -> select * from person right join card on person.cardId = card.id;
+------+------+--------+------+--------+
| id   | name | cardId | id   | name   |
+------+------+--------+------+--------+
|    1 | 张三 |      1 |    1 | 饭卡   |
|    1 | 李四 |      3 |    3 | 农行卡 |
|    1 | 王五 |      6 | NULL | NULL   |
| NULL | NULL |   NULL |    2 | 建行卡 |
| NULL | NULL |   NULL |    4 | 工商卡 |
| NULL | NULL |   NULL |    5 | 邮政卡 |
+------+------+--------+------+--------+

连接查询其实就是集合运算

内连接就是求两个集合的交集

左（右）外连接就是求左（右）边表的全部数据

完全外连接就是求交集

事务

事务的四大特性：

A 原子性：事务是最小的单位，不可以分割
C 一致性：事务要求同一事务中的sql语句，必须保证同时成功或者同时失败
D 隔离性：事务1和事务2之间是具隔离性的
I 持久性：事务一旦结束，就不可以返回

事务的隔离性

read uncommitted：读未提交的
- 其他事务读未提交数据，出现脏读；
- 如果一个事务开始写数据，另一个事务不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现
- 避免了更新丢失，可能出现脏读。也就是说事务a读到了事务b未提交的数据
read committed：读已提交的
- 允许写事务，所以会出现不可重复读
- 读取数据得事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行
- 该隔离级别避免了脏读，但是可能出现不可重复读。事务a事先读取了数据，事务b紧接着更新了数据，并提交了事务，而事务a再次读取该数据时，数据已经发生了改变。
repeatable read：可以重复读
- 禁止写事务
- 读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止其他任何事务
- 避免了不可重复读取和脏读，但是有时可能出现幻读，即第二次查询数据时会包含第一次查询中未出现的数据。这可以通过“共享读锁”和“排他写锁”实现
serializable：串行化
- 禁止任何事务，一个一个进行；
- 提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。
- 序列化是最高的事务隔离级别，同时代价也花费最高，性能很低，一般很少使用，在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻读。

最后编辑于：2020.03.03 23:10:05

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 214,504评论 6赞 496
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 91,434评论 3赞 389
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 160,089评论 0赞 349
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 57,378评论 1赞 288
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 66,472评论 6赞 386
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 50,506评论 1赞 292
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 39,519评论 3赞 413
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 38,292评论 0赞 270
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,738评论 1赞 307
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 37,022评论 2赞 329
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,194评论 1赞 342
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 34,873评论 5赞 338
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 40,536评论 3赞 322
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,162评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,413评论 1赞 268
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 47,075评论 2赞 365
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,080评论 2赞 352