MySQL学习笔记

作者: zfz_amzing | 来源:发表于2020-03-03 17:30 被阅读0次

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：串行化
- 禁止任何事务，一个一个进行；
- 提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。
- 序列化是最高的事务隔离级别，同时代价也花费最高，性能很低，一般很少使用，在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻读。

MySQL学习笔记

MySQL

MySQL常用数据类型

MySQL建表约束

数据库的三大设计范式

单表查询

多表查询

连接查询

内连接

外连接

事务

事务的四大特性：

事务的隔离性

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读