wanpiqiu123's Blog HomeAboutArchivesTagsCategories
MySQL学习
Word count: 2.1k|Reading time: 8 min

学习MySQL

注：本文中的数据库部分来源于菜鸟教程

显示所有数据库

1
2
3
4
5
6
7
8
9
10
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runoob             |
| sys                |
+--------------------+

显示数据库中的表格

1
2
3
4
5
6
mysql> show tables;
+------------------+
| Tables_in_runoob |
+------------------+
| websites         |
+------------------+

查看表格的结构

1
2
3
4
5
6
7
8
9
10
mysql> describe websites;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int          | NO   | PRI | NULL    | auto_increment |
| name    | char(20)     | NO   |     |         |                |
| url     | varchar(255) | NO   |     |         |                |
| alexa   | int          | NO   |     | 0       |                |
| country | char(10)     | NO   |     |         |                |
+---------+--------------+------+-----+---------+----------------+

新建表格

1
2
3
4
5
6
7
8
mysql> CREATE TABLE Persons
    -> (
    -> PersonID int,
    -> LastName varchar(255),
    -> FirstName varchar(255),
    -> Address varchar(255),
    -> City varchar(255)
    -> );

查看建表语句

1
show create table TALBE_NAME;

查询语句

全部查询

1
2
3
4
5
6
7
8
9
10
mysql> select * from websites;
+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
|  1 | Google       | https://www.google.cm/    |     1 | USA     |
|  2 | 淘宝         | https://www.taobao.com/   |    13 | CN      |
|  3 | 菜鸟教程     | http://www.runoob.com/    |  4689 | CN      |
|  4 | 微博         | http://weibo.com/         |    20 | CN      |
|  5 | Facebook     | https://www.facebook.com/ |     3 | USA     |
+----+--------------+---------------------------+-------+---------+

查询特定行

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select * from websites where id = 2;
+----+--------+-------------------------+-------+---------+
| id | name   | url                     | alexa | country |
+----+--------+-------------------------+-------+---------+
|  2 | 淘宝   | https://www.taobao.com/ |    13 | CN      |
+----+--------+-------------------------+-------+---------+

mysql> select * from websites where name="google";
+----+--------+------------------------+-------+---------+
| id | name   | url                    | alexa | country |
+----+--------+------------------------+-------+---------+
|  1 | Google | https://www.google.cm/ |     1 | USA     |
+----+--------+------------------------+-------+---------+

插入行

1
2
mysql> INSERT INTO Websites (name, url, alexa, country)
    -> VALUES ('百度','https://www.baidu.com/','4','CN');

删除行

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> delete from websites where name = "Google";
Query OK, 1 row affected (0.00 sec)

mysql> select * from websites;
+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
|  2 | 淘宝         | https://www.taobao.com/   |    13 | CN      |
|  3 | 菜鸟教程     | http://www.runoob.com/    |  4689 | CN      |
|  4 | 微博         | http://weibo.com/         |    20 | CN      |
|  5 | Facebook     | https://www.facebook.com/ |     3 | USA     |
|  6 | 百度         | https://www.baidu.com/    |     4 | CN      |
+----+--------------+---------------------------+-------+---------+
1
2
3
4
5
-- 删除重复的电子邮箱
DELETE p1 FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id

修改内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> UPDATE Websites
    -> SET alexa='5000', country='USA'
    -> WHERE name='菜鸟教程';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from websites;
+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
|  2 | 淘宝         | https://www.taobao.com/   |    13 | CN      |
|  3 | 菜鸟教程     | http://www.runoob.com/    |  5000 | USA     |
|  4 | 微博         | http://weibo.com/         |    20 | CN      |
|  5 | Facebook     | https://www.facebook.com/ |     3 | USA     |
|  6 | 百度         | https://www.baidu.com/    |     4 | CN      |
+----+--------------+---------------------------+-------+---------+

约束类型

  1. NOT NULL - 指示某列不能存储 NULL 值。
  2. UNIQUE - 保证某列的每行必须有唯一的值。
  3. PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更快速地找到表中的一个特定的记录。
  4. FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
  5. CHECK - 保证列中的值符合指定的条件。
  6. DEFAULT - 规定没有给列赋值时的默认值。
  7. AUTO INCREMENT - 在新记录插入表中时生成一个唯一的数字。

主键

1
2
3
4
5
6
7
8
CREATE TABLE Persons
(
    Id_P int NOT NULL PRIMARY KEY,   //PRIMARY KEY约束
    LastName varchar(255) NOT NULL,  //非空约束
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
)

联合主键

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> CREATE TABLE Persons2
    -> (
    ->     Id_P int NOT NULL,
    ->     LastName varchar(255) NOT NULL,
    ->     FirstName varchar(255),
    ->     Address varchar(255),
    ->     City varchar(255),
    ->     PRIMARY KEY (Id_P,Adress)  //联合主键
    -> );

mysql> describe persons2;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| Id_P      | int          | NO   | PRI | NULL    |       |
| LastName  | varchar(255) | NO   |     | NULL    |       |
| FirstName | varchar(255) | YES  |     | NULL    |       |
| Address   | varchar(255) | NO   | PRI | NULL    |       |
| City      | varchar(255) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

修改主键约束

  1. 删除约束
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> ALTER table persons2
    -> Drop primary key;

mysql> describe persons2;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| Id_P      | int          | NO   |     | NULL    |       |
| LastName  | varchar(255) | NO   |     | NULL    |       |
| FirstName | varchar(255) | YES  |     | NULL    |       |
| Address   | varchar(255) | NO   |     | NULL    |       |
| City      | varchar(255) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
  1. 添加约束
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> alter table persons2
    -> add primary key(id_p);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc persons2;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| Id_P      | int          | NO   | PRI | NULL    |       |
| LastName  | varchar(255) | NO   |     | NULL    |       |
| FirstName | varchar(255) | YES  |     | NULL    |       |
| Address   | varchar(255) | NO   |     | NULL    |       |
| City      | varchar(255) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
  1. 修改类型
1
2
3
4
5
6
7
8
9
10
11
12
mysql> alter table persons2
    -> modify column city varchar(233);

+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| Id_P      | int          | NO   | PRI | NULL    |       |
| LastName  | varchar(255) | NO   |     | NULL    |       |
| FirstName | varchar(255) | YES  |     | NULL    |       |
| Address   | varchar(255) | NO   |     | NULL    |       |
| city      | varchar(233) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
1
2
3
4
5
6
7
8
9
10
11
12
mysql> alter table persons2
    -> modify column city varchar(233) primary key;
mysql> desc persons2;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| Id_P      | int          | NO   |     | NULL    |       |
| LastName  | varchar(255) | NO   |     | NULL    |       |
| FirstName | varchar(255) | YES  |     | NULL    |       |
| Address   | varchar(255) | NO   |     | NULL    |       |
| city      | varchar(233) | NO   | PRI | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

唯一约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
mysql> alter table persons2
    -> add unique(ID_p);
mysql> desc persons2;

+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| Id_P      | int          | NO   | UNI | NULL    |       |
| LastName  | varchar(255) | NO   |     | NULL    |       |
| FirstName | varchar(255) | YES  |     | NULL    |       |
| Address   | varchar(255) | NO   |     | NULL    |       |
| city      | varchar(233) | NO   | PRI | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
mysql> alter table persons2
    ->drop index id_p;

mysql> desc persons2;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| Id_P      | int          | NO   |     | NULL    |       |
| LastName  | varchar(255) | NO   |     | NULL    |       |
| FirstName | varchar(255) | YES  |     | NULL    |       |
| Address   | varchar(255) | NO   |     | NULL    |       |
| city      | varchar(233) | NO   | PRI | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

也可以使用 modify添加unique约束。

外键约束

被指向为主表（父表），创建约束的为副表（子表）。要求reference的键为unique，否则无法成功。主表的数据被副表引用时无法被删除。

  1. 初始创建约束
1
2
3
4
5
6
7
8
CREATE TABLE 表名
    (
        column1 datatype null/not null,
        column2 datatype null/not null,
        ...
        (CONSTRAINT 外键约束名) FOREIGN KEY  (column1,column2,... column_n) 
        REFERENCES 外键依赖的表 (column1,column2,...column_n)
    );
  1. 添加约束
1
2
3
4
ALTER TABLE 表名
    ->ADD (CONSTRAINT 外键约束名)
    ->FOREIGN KEY (column1, column2,...column_n) 
    ->REFERENCES 外键所依赖的表 (column1,column2,...column_n);
  1. 删除约束
1
2
ALTER TABLE Orders
DROP FOREIGN KEY KEY_NAME;

数据库的三大范式

  1. 第一范式（1NF）：列的原子性，一个字段不能被拆分。
  2. 第二范式（2NF）：在第一范式的基础上，表必须有一个主键，其他每一列必须完全依赖主键。通常的做法是将表分成几个部分，每部分都完全依赖对应的主键。最后用一个父表主键管理对应的子表的主键。
  3. 第三范式（3NF）：在第二范式的基础上，不能依赖主键之外的其他列，也就是都是直接依赖主键。

连接

内连接

1
mysql> select * from person inner join card on person.cardid = card.id;

左连接

1
mysql> select * from person left join card on person.cardid = card.id;

右连接

1
mysql> select * from person right join card on person.cardid = card.id;

MySQL不支持全连接，但是可以左连接和右连接并集得到。

事务

事务的定义

事务是一个最小的不可分割的工作单元，能够保证一个业务的完整性。

MySQL中，在执行多条有关联语句时，事务可能会要求这些语句要么同时执行成功，要么就都执行失败。

事务的自动提交状态是默认开启的，执行一条语句的时候，其产生的效果就会立即体现出来，且不能回滚。回滚指恢复到最后一次提交数据时的状态。

1
2
3
4
5
6
7
8
9
10
-- 查询事务的自动提交状态
select @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            1 |
+--------------+

-- 关闭自动提交
SET AUTOCOMMIT = 0;

关闭自动提交后，使用 commit手动提交数据，未提交的可以使用rollback回滚。

手动开启事务

1
2
begin;
-- 之后的语句

或

1
2
start transaction;
-- 之后的语句

或

1
SET AUTOCOMMIT = 0;

事务的四大特征

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

事务的隔离性

  1. READ UNCOMMITTED(读取未提交)：
    如果有多个事务，那么任意事务都可以看见其他事务的未提交数据。

  2. READ COMMITTED(读取已提交)：
    只能读取到其他事务已经提交的数据。

  3. REPEATABLE READ(可被重复读)：
    如果有多个连接都开启了事务，那么事务之间不能共享数据记录，否则只能共享已提交的记录。

  4. SERIALIZABLE(串行化):
    所有的事务都会按照固定顺序执行，执行完一个事务后再继续执行下一个事务的写入操作。

查看/修改隔离性

1
2
3
4
-- 查看隔离性
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
-- 修改隔离性
SET GLOBAL TRANSACTION ISOLATION LEVEL xxx;

脏读

一个事务读到了另一个未提交的事务。隔离性为READ UNCOMMITTED时可能出现。

不可重复读

在读取同一个表的数据时，可能会发生前后不一致的情况，比如其他连接提交了修改。隔离性为READ COMMITTED时可能出现。

幻读

一个事务提交的数据，不能被其他事务读取到。二者可能同时插入数据然后出现报错（比如使用相同主键）。隔离性为REPEATABLE READ时可能出现。

串行化

当表被一个事务操作的时候，其他事务里面的写操作是不能进行的。这意味着队列中同时只能执行一个事务的写入操作。可能出现等待超时。

Author: Jiaming Luo
Link: http://wanpiqiu123.github.io/2020/03/24/MySQL%E5%AD%A6%E4%B9%A0/
Copyright Notice: All articles in this blog are licensed under CC BY-NC-SA 4.0 unless stating additionally.
MySQL