MySQL Learning

MySQL外键约束

父表

1
2
3
4
5
mysql> CREATE TABLE privices(
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> pname VARCHAR(20) NOT NULL
-> );
Query OK, 0 rows affected (0.05 sec)

子表

1
2
3
4
5
6
7
mysql> CREATE TABLE users(
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> username VARCHAR(10) NOT NULL,
-> pid SMALLINT UNSIGNED,
-> FOREIGN KEY(pid) REFERENCES privices(id)
-> );
Query OK, 0 rows affected (0.03 sec)

==注:父表和子表必须主外键数据类型一样才能创建,当主表在创建时就存在索引==


查看是否创建索引

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
mysql> SHOW INDEXES FROM  privices\G;
*************************** 1. row ***************************
Table: privices
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)


mysql> SHOW INDEXES FROM users\G;
*************************** 1. row ***************************
Table: users
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: users
Non_unique: 1
Key_name: pid
Seq_in_index: 1
Column_name: pid
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)

MySQL外键约束操作

建立CASCADE操作(从父表删除或更新且自动删除或更新子表中匹配的行)

1
2
3
4
5
6
7
mysql> CREATE TABLE users1(
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> username VARCHAR(10) NOT NULL,
-> pid SMALLINT UNSIGNED,
-> FOREIGN KEY(pid) REFERENCES privices(id) ON DELETE CASCADE
-> );
Query OK, 0 rows affected (0.05 sec)

插入数据

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
mysql> INSERT INTO privices(pname) VALUES('A');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO privices(pname) VALUES('B');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO privices(pname) VALUES('V');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM privices;
+----+-------+
| id | pname |
+----+-------+
| 1 | A |
| 2 | B |
| 3 | V |
+----+-------+
3 rows in set (0.00 sec)

mysql> INSERT INTO users1(username,pid) VALUES('TOM',3);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO users1(username,pid) VALUES('JACK',3);
Query OK, 1 row affected (0.20 sec)

mysql> INSERT INTO users1(username,pid) VALUES('WANG',3);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO users1(username,pid) VALUES('LISI',1);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM users1;
+----+----------+------+
| id | username | pid |
+----+----------+------+
| 1 | TOM | 3 |
| 2 | JACK | 3 |
| 3 | WANG | 3 |
| 4 | LISI | 1 |
+----+----------+------+
4 rows in set (0.00 sec)

删除数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> DELETE FROM privices WHERE id =3;
Query OK, 1 row affected (0.02 sec

mysql> SELECT * FROM privices;
+----+-------+
| id | pname |
+----+-------+
| 1 | A |
| 2 | B |
+----+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM users1;
+----+----------+------+
| id | username | pid |
+----+----------+------+
| 4 | LISI | 1 |
+----+----------+------+
1 row in set (0.00 sec)

==注:一般数据操作删除时,一般只用逻辑删除,很少会用到物理删除,因为一旦遇到要复原数据是一件很麻烦的事情,逻辑外键也是一样,一般使用列级约束较多==


数据表修改

添加最后位置

1
2
3
mysql> ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0

添加指定位置

1
2
3
mysql> ALTER TABLE users1 ADD password VARCHAR(30) NOT NULL AFTER username;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0

添加在最前列

1
2
3
mysql> ALTER TABLE users1 ADD tname VARCHAR(20) NOT NULL FIRST;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0

删除单列

1
2
3
mysql> ALTER TABLE users1 DROP tname;
Query OK, 0 rows affected (0.27 sec)
Records: 0 Duplicates: 0 Warnings: 0

删除多列

1
2
3
mysql> ALTER TABLE users1 DROP password,DROP age;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0

添加主键约束

1
2
3
mysql> ALTER TABLE users2 ADD CONSTRAINT PK_users2_id PRIMARY KEY(id);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

添加唯一约束

1
2
3
mysql> ALTER TABLE users2 ADD UNIQUE (username);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

添加外键约束

1
2
3
mysql> ALTER TABLE users2 ADD FOREIGN KEY(pid) REFERENCES privices(id);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0

添加默认约束

1
2
3
mysql> ALTER TABLE users2 ALTER age SET DEFAULT 15;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

删除默认约束

1
2
3
mysql> ALTER TABLE users2 ALTER age DROP DEFAULT;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

==注:这些都是在创建表时没有任何约束时所进行的操作==


删除主键约束

1
2
3
mysql> ALTER TABLE users2 DROP PRIMARY KEY;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0

删除唯一约束

1
2
3
mysql> ALTER TABLE users2 DROP INDEX username;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0

删除外键约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
第一步:查看外键是什么
mysql> SHOW CREATE TaBLE users2;
--------------------------------+
| Table | Create Table
| users2 | CREATE TABLE `users2` (
`username` varchar(20) NOT NULL,
`pid` smallint(5) unsigned DEFAULT NULL,
`id` smallint(5) unsigned NOT NULL,
`age` tinyint(3) unsigned NOT NULL,
KEY `pid` (`pid`),
CONSTRAINT `users2_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `privices` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------------------------------+
1 row in set (0.00 sec)

第二步:删除查找出来的外键
mysql> ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

修改列最前面

1
2
3
mysql> ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0

修改列里的数据类型

1
2
3
mysql> ALTER TABLE users2 MODIFY id TINYINT UNSIGNED NOT NULL;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0

修改列名称

1
2
3
mysql> ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0

修改数据表名称

1
2
3
4
5
6
7
第一种:单表更名
mysql> ALTER TABLE users2 RENAME users3;
Query OK, 0 rows affected (0.02 sec)

第二种:多表更名
mysql> RENAME TABLE users3 TO users2;
Query OK, 0 rows affected (0.02 sec)

==注:尽量少使用列名称和表名称修改==


插入数据INSERT

插入一行数据

1
2
mysql> INSERT INTO users VALUES(DEFAULT,'TOM','1234',DEFAULT,1);
Query OK, 1 row affected (0.04 sec)

插入多行数据

1
2
3
mysql> INSERT INTO users VALUES(NULL,'JACK','123456',25,1),(DEFAULT,'UNCLE',1234,DEFAULT,0);
Query OK, 2 rows affected (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 0

只能插入一行数据

1
2
mysql> INSERT INTO users SET username='ROSE',password='123456',age=10,sex=1;
Query OK, 1 row affected (0.04 sec)

==当主键为自动时我们可以将主键的纪录设置为DEFAULT/NULL==


更新数据UPDATE

对某一记录进行操作

1
mysql> UPDATE users SET age=age+5;

对某一记录进行操作

1
2
3
mysql> UPDATE users SET age=age-id,sex=0;
Query OK, 8 rows affected (0.01 sec)
Rows matched: 8 Changed: 8 Warnings: 0

加条件进行记录操作

1
2
3
mysql> UPDATE users SET age=age+10 WHERE id%2=0;
Query OK, 4 rows affected (0.02 sec)
Rows matched: 4 Changed: 4 Warnings: 0

删除记录DELETE

1
2
mysql> DELETE FROM users WHERE id=5;
Query OK, 1 row affected (0.02 sec)

查询记录SELECT

顺序查找

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SELECT id,username FROM users;
+----+----------+
| id | username |
+----+----------+
| 1 | TOM |
| 2 | JACK |
| 3 | TOM |
| 4 | JACK |
| 10 | JACK |
| 11 | UNCLE |
| 12 | ROSE |
+----+----------+
7 rows in set (0.00 sec)

别名查找

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SELECT id AS userId,username AS uname FROM users;
+--------+-------+
| userId | uname |
+--------+-------+
| 1 | TOM |
| 2 | JACK |
| 3 | TOM |
| 4 | JACK |
| 10 | JACK |
| 11 | UNCLE |
| 12 | ROSE |
+--------+-------+
7 rows in set (0.01 sec)

==注:当查询的字段顺序不一样时出现的结果也是不一样,用别名查找时最好加上AS关键字==


分组查询GROUP BY和HAVING

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> SELECT age FROM users GROUP BY age;
+-----+
| age |
+-----+
| 4 |
| 13 |
| 22 |
| 24 |
| 30 |
| 35 |
| 38 |
+-----+
7 rows in set (0.01 s

mysql> SELECT age FROM users GROUP BY age HAVING count(age)>=20;
Empty set (0.00 sec)

==注:一般HAVING分组后都接的是聚会函数如AVG()/COUNT()/MAX()/MIN()等==


分组查询ORDER BY

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SELECT * FROM users ORDER BY id DESC;
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 12 | ROSE | 123456 | 13 | 0 |
| 11 | UNCLE | 1234 | 4 | 0 |
| 10 | JACK | 123456 | 30 | 0 |
| 4 | JACK | 123456 | 35 | 0 |
| 3 | TOM | 1234 | 22 | 0 |
| 2 | JACK | 123456 | 38 | 0 |
| 1 | TOM | 1234 | 24 | 0 |
+----+----------+----------+-----+------+
7 rows in set (0.00 sec)

==注:DESC降序排列/ASC升序排列==


限制查询LIMIT

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT * FROM users LIMIT 5;
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 1 | TOM | 1234 | 24 | 0 |
| 2 | JACK | 123456 | 38 | 0 |
| 3 | TOM | 1234 | 22 | 0 |
| 4 | JACK | 123456 | 35 | 0 |
| 10 | JACK | 123456 | 30 | 0 |
+----+----------+----------+-----+------+
5 rows in set (0.00 sec)

表示第五条数据开始的2条数据

1
2
3
4
5
6
7
8
mysql> SELECT * FROM users LIMIT 5,2;
+----+----------+----------+-----+------+
| id | username | password | age | sex |
+----+----------+----------+-----+------+
| 11 | UNCLE | 1234 | 4 | 0 |
| 12 | ROSE | 123456 | 13 | 0 |
+----+----------+----------+-----+------+
2 rows in set (0.00 sec)

==注:LIMIT分页会需要这个关键字==

------------------------------------本文结束感谢您的阅读--------------------------------
wangyekun wechat
扫一扫关注一波
坚持原创,您的支持将鼓励我继续创作!