MySQL基础（三）

于 2020-03-06 21:13:13 发布
阅读量155 收藏
点赞数
文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/qq_43635468/article/details/104696464
版权

目录

更改数据的操作

插入数据

首先创建一个数据表

mysql> CREATE TABLE IF NOT EXISTS user(
    -> id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE,
    -> password CHAR(32) NOT NULL,
    -> email VARCHAR(50) NOT NULL DEFAULT '382771946@qq.com',
    -> age TINYINT UNSIGNED DEFAULT 18
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> DESC user;
+----------+---------------------+------+-----+------------------+----------------+
| Field    | Type                | Null | Key | Default          | Extra          |
+----------+---------------------+------+-----+------------------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL             | auto_increment |
| username | varchar(20)         | NO   | UNI | NULL             |                |
| password | char(32)            | NO   |     | NULL             |                |
| email    | varchar(50)         | NO   |     | 382771946@qq.com |                |
| age      | tinyint(3) unsigned | YES  |     | 18               |                |
+----------+---------------------+------+-----+------------------+----------------+
5 rows in set (0.00 sec)

1、不指定具体的字段名
INSERT [INTO] tbl_name VALUES I VALUE(值···)

mysql> INSERT INTO user VALUES(1,'KING','KING','KING@QQ.COM',20);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT user VALUE(2,'QUEEN','QUEEN','QUEEN@QQ.COM',30);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM user;
+----+----------+----------+--------------+------+
| id | username | password | email        | age  |
+----+----------+----------+--------------+------+
|  1 | KING     | KING     | KING@QQ.COM  |   20 |
|  2 | QUEEN    | QUEEN    | QUEEN@QQ.COM |   30 |
+----+----------+----------+--------------+------+
2 rows in set (0.00 sec)

2、列出指定字段
INSERT [INTO] tbl_name(字段名称1，···) VALUES I VALUE(值1，…)

mysql> INSERT user(username,password) VALUES('A','AAA');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT user(username,id,password,email,age) VALUES('B',55,'BBB','BBB@QQ.COM',40);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM user;
+----+----------+----------+------------------+------+
| id | username | password | email            | age  |
+----+----------+----------+------------------+------+
|  1 | KING     | KING     | KING@QQ.COM      |   20 |
|  2 | QUEEN    | QUEEN    | QUEEN@QQ.COM     |   30 |
|  3 | A        | AAA      | 382771946@qq.com |   18 |
| 55 | B        | BBB      | BBB@QQ.COM       |   40 |
+----+----------+----------+------------------+------+
4 rows in set (0.00 sec)

3、同时插入多条记录
INSERT [INTO] tbl_name[(字段名称···] VALUES(值···),(值···)···

mysql> INSERT user VALUES(6,'C','CCC','C@QQ.COM',35),
    -> (8,'D','DDD','D@QQ.COM',9),
    -> (18,'E','EEE','E@QQ.COM',32);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM user;
+----+----------+----------+------------------+------+
| id | username | password | email            | age  |
+----+----------+----------+------------------+------+
|  1 | KING     | KING     | KING@QQ.COM      |   20 |
|  2 | QUEEN    | QUEEN    | QUEEN@QQ.COM     |   30 |
|  3 | A        | AAA      | 382771946@qq.com |   18 |
|  6 | C        | CCC      | C@QQ.COM         |   35 |
|  8 | D        | DDD      | D@QQ.COM         |    9 |
| 18 | E        | EEE      | E@QQ.COM         |   32 |
| 55 | B        | BBB      | BBB@QQ.COM       |   40 |
+----+----------+----------+------------------+------+
7 rows in set (0.00 sec)

4、通过SET形式插入记录
INSERT [INTO] tbl_name SET 字段名称=值,···

mysql> INSERT INTO user SET id=98,username='test',password='this is a test',email='123@qq.com',
    -> age=48;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT user SET username='F',password='FFF' ;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM user;
+----+----------+----------------+------------------+------+
| id | username | password       | email            | age  |
+----+----------+----------------+------------------+------+
|  1 | KING     | KING           | KING@QQ.COM      |   20 |
|  2 | QUEEN    | QUEEN          | QUEEN@QQ.COM     |   30 |
|  3 | A        | AAA            | 382771946@qq.com |   18 |
|  6 | C        | CCC            | C@QQ.COM         |   35 |
|  8 | D        | DDD            | D@QQ.COM         |    9 |
| 18 | E        | EEE            | E@QQ.COM         |   32 |
| 55 | B        | BBB            | BBB@QQ.COM       |   40 |
| 98 | test     | this is a test | 123@qq.com       |   48 |
| 99 | F        | FFF            | 382771946@qq.com |   18 |
+----+----------+----------------+------------------+------+
9 rows in set (0.00 sec)

5、将查询结果插入到表中
将一个数据表中的查询结果插入到另一张表中
INSERT [INTO] tbl_name[(字段名称，···)] SELECT 字段名称 FROM tbl_name [WHERE 条件]

mysql> CREATE TABLE IF NOT EXISTS testUser(
    -> id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT testUser SELECT id,username FROM user;
Query OK, 9 rows affected (0.29 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM testUser;
+----+----------+
| id | username |
+----+----------+
|  3 | A        |
| 55 | B        |
|  6 | C        |
|  8 | D        |
| 18 | E        |
| 99 | F        |
|  1 | KING     |
|  2 | QUEEN    |
| 98 | test     |
+----+----------+
9 rows in set (0.00 sec)

更新数据

UPDATE tbl_name SET 字段名称=值，··· [WHERE 条件] [ORDER BY 字段名称] [LIMIT 限制条数]

mysql> UPDATE user SET password='king123',email='123@qq.com',age=99
    -> WHERE id=1;
Query OK, 1 row affected (0.31 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE user SET age=age-5 WHERE id>=3;
Query OK, 7 rows affected (0.29 sec)
Rows matched: 7  Changed: 7  Warnings: 0

mysql> SELECT * FROM user;
+----+----------+----------------+------------------+------+
| id | username | password       | email            | age  |
+----+----------+----------------+------------------+------+
|  1 | KING     | king123        | 123@qq.com       |   99 |
|  2 | QUEEN    | QUEEN          | QUEEN@QQ.COM     |   30 |
|  3 | A        | AAA            | 382771946@qq.com |   13 |
|  6 | C        | CCC            | C@QQ.COM         |   30 |
|  8 | D        | DDD            | D@QQ.COM         |    4 |
| 18 | E        | EEE            | E@QQ.COM         |   27 |
| 55 | B        | BBB            | BBB@QQ.COM       |   35 |
| 98 | test     | this is a test | 123@qq.com       |   43 |
| 99 | F        | FFF            | 382771946@qq.com |   13 |
+----+----------+----------------+------------------+------+
9 rows in set (0.00 sec)

删除数据

1、DELETE FROM tbl_name [WHERE 条件] [ORDER BY 字段名称] [LIMIT 限制条数]

-- 删除指定内容
mysql> DELETE FROM user WHERE id=1;
Query OK, 1 row affected (0.29 sec)

mysql> SELECT * FROM user;
+----+----------+----------------+------------------+------+
| id | username | password       | email            | age  |
+----+----------+----------------+------------------+------+
|  2 | QUEEN    | QUEEN          | QUEEN@QQ.COM     |   30 |
|  3 | A        | AAA            | 382771946@qq.com |   13 |
|  6 | C        | CCC            | C@QQ.COM         |   30 |
|  8 | D        | DDD            | D@QQ.COM         |    4 |
| 18 | E        | EEE            | E@QQ.COM         |   27 |
| 55 | B        | BBB            | BBB@QQ.COM       |   35 |
| 98 | test     | this is a test | 123@qq.com       |   43 |
| 99 | F        | FFF            | 382771946@qq.com |   13 |
+----+----------+----------------+------------------+------+
8 rows in set (0.00 sec)

-- 若无指定条件则删除全部内容
mysql> DELETE FROM user;
Query OK, 8 rows affected (0.01 sec)

mysql> SELECT * FROM user;
Empty set (0.00 sec)

这种方式删除全部内容后，自增长值不会重置

mysql> INSERT user SET username='F',password='FFF';
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM user;
+-----+----------+----------+------------------+------+
| id  | username | password | email            | age  |
+-----+----------+----------+------------------+------+
| 100 | F        | FFF      | 382771946@qq.com |   18 |
+-----+----------+----------+------------------+------+
1 row in set (0.00 sec)

2、彻底清空数据表
这种方式删除全部内容后，自增长值将重置
TRUNCATE [TABLE] tbl_name

mysql> TRUNCATE user;
Query OK, 0 rows affected (0.29 sec)

mysql> SELECT * FROM user;
Empty set (0.00 sec)

mysql> INSERT user SET username='F',password='FFF';
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM user;
+----+----------+----------+------------------+------+
| id | username | password | email            | age  |
+----+----------+----------+------------------+------+
|  1 | F        | FFF      | 382771946@qq.com |   18 |
+----+----------+----------+------------------+------+
1 row in set (0.00 sec)

查询数据基本操作

数据的管理在很大一部分是在进行查找工作，而SELECT占据了很大的一部分。
首先创建一个名为cms的数据库，在其中创建名为cms_admin的数据表。

+----+----------+----------+--------------+------------+
| id | username | password | email        | role       |
+----+----------+----------+--------------+------------+
|  1 | admin    | admin    | admin@qq.com | 超级管理员  |
|  2 | king     | king     | admin@qq.com | 普通管理员  |
|  3 | Tom      | Tom      | admin@qq.com | 普通管理员  |
|  4 | queen    | queen    | admin@qq.com | 普通管理员  |
|  5 | test     | test     | admin@qq.com | 普通管理员  |
+----+----------+----------+--------------+------------+

查询表达式

SELECT select_expr [,select_expr...]
[
    FROM table_reference
        WHERE [条件] 
        [GROUP BY {col_name | position} [ASC| DESC],...分组] 
        [HAVING 条件 对分组结果二次筛选] 
        [ORDER BY {col_name | expr |position} [ASC| DESC],...排序] 
        [LIMIT 限制显示条数]
]

1、每一个表达式表示想要的一列,必须至少有一列,多个列之间以逗号分隔；
2、*表示所有列，tbL_name.*可以表示命名表的所有列；
3、查询表达式可以使用[AS] alias_name为其赋予别名。

查询所有列

mysql> SELECT * FROM cms_admin;
+----+----------+----------+--------------+------------+
| id | username | password | email        | role       |
+----+----------+----------+--------------+------------+
|  1 | admin    | admin    | admin@qq.com | 超级管理员          |
|  2 | king     | king     | admin@qq.com | 普通管理员         |
|  3 | Tom      | Tom      | admin@qq.com | 普通管理员         |
|  4 | queen    | queen    | admin@qq.com | 普通管理员         |
|  5 | test     | test     | admin@qq.com | 普通管理员         |
+----+----------+----------+--------------+------------+
5 rows in set (0.00 sec)

查询指定列

mysql> SELECT id,username FROM cms_admin;
+----+----------+
| id | username |
+----+----------+
|  1 | admin    |
|  2 | king     |
|  4 | queen    |
|  5 | test     |
|  3 | Tom      |
+----+----------+
5 rows in set (0.00 sec)

对数据表起别名（AS可以省略）

mysql> SELECT a.id,a.username,a.email,a.role FROM cms_admin AS a;
+----+----------+--------------+------------+
| id | username | email        | role       |
+----+----------+--------------+------------+
|  1 | admin    | admin@qq.com | 超级管理员  |
|  2 | king     | admin@qq.com | 普通管理员  |
|  3 | Tom      | admin@qq.com | 普通管理员  |
|  4 | queen    | admin@qq.com | 普通管理员  |
|  5 | test     | admin@qq.com | 普通管理员  |
+----+----------+--------------+------------+
5 rows in set (0.28 sec)

对字段起别名（AS可以省略）

mysql> SELECT id AS '编号',username AS '用户名',email AS '邮箱',role '角色' FROM cms_admin;
+------+--------+--------------+------------+
| 编号  | 用户名 | 邮箱         | 角色        |
+------+--------+--------------+------------+
|    1 | admin  | admin@qq.com | 超级管理员  |
|    2 | king   | admin@qq.com | 普通管理员  |
|    3 | Tom    | admin@qq.com | 普通管理员  |
|    4 | queen  | admin@qq.com | 普通管理员  |
|    5 | test   | admin@qq.com | 普通管理员  |
+------+--------+--------------+------------+
5 rows in set (0.00 sec)

WHERE条件

在这里插入图片描述
1、比较

--查询id为1的记录
mysql> SELECT id,username,email FROM cms_admin WHERE id=1;
+----+----------+--------------+
| id | username | email        |
+----+----------+--------------+
|  1 | admin    | admin@qq.com |
+----+----------+--------------+
1 row in set (0.00 sec)

2、范围

--查询id在3到4范围内的记录
mysql> SELECT * FROM cms_admin WHERE id BETWEEN 3 AND 4;
+----+----------+----------+--------------+------------+
| id | username | password | email        | role       |
+----+----------+----------+--------------+------------+
|  3 | Tom      | Tom      | admin@qq.com | 普通管理员  |
|  4 | queen    | queen    | admin@qq.com | 普通管理员  |
+----+----------+----------+--------------+------------+
2 rows in set (2.66 sec)

--查询id不在3到4范围内的记录
mysql> SELECT * FROM cms_admin WHERE id NOT BETWEEN 3 AND 4;
+----+----------+----------+--------------+------------+
| id | username | password | email        | role       |
+----+----------+----------+--------------+------------+
|  1 | admin    | admin    | admin@qq.com | 超级管理员  |
|  2 | king     | king     | admin@qq.com | 普通管理员  |
|  5 | test     | test     | admin@qq.com | 普通管理员  |
+----+----------+----------+--------------+------------+
3 rows in set (0.00 sec)

--查询id在指定集合的记录
mysql> SELECT * FROM cms_admin WHERE id IN(1,3,5);
+----+----------+----------+--------------+------------+
| id | username | password | email        | role       |
+----+----------+----------+--------------+------------+
|  1 | admin    | admin    | admin@qq.com | 超级管理员  |
|  3 | Tom      | Tom      | admin@qq.com | 普通管理员  |
|  5 | test     | test     | admin@qq.com | 普通管理员  |
+----+----------+----------+--------------+------------+
3 rows in set (0.00 sec)

3、模糊查询

-- 查询username中有t字符的记录
mysql> SELECT * FROM cms_admin WHERE username LIKE '%t%';
+----+----------+----------+--------------+------------+
| id | username | password | email        | role       |
+----+----------+----------+--------------+------------+
|  3 | Tom      | Tom      | admin@qq.com | 普通管理员  |
|  5 | test     | test     | admin@qq.com | 普通管理员  |
+----+----------+----------+--------------+------------+
2 rows in set (0.00 sec)

-- 查询username为3位的记录
mysql> SELECT * FROM cms_admin WHERE username LIKE '___';
+----+----------+----------+--------------+------------+
| id | username | password | email        | role       |
+----+----------+----------+--------------+------------+
|  3 | Tom      | Tom      | admin@qq.com | 普通管理员  |
+----+----------+----------+--------------+------------+
1 row in set (0.00 sec)

分组查询

GROUP BY 实现分组查询
2、配合聚合函数COUNT()、MAX()、MIN()、AVG()、SUM()；
3、配合WITH ROLLUP记录上面所有记录的总和。

-- 按照role分组，分组后每组只显示一条信息
mysql> SELECT * FROM cms_admin GROUP BY role;
+----+----------+----------+--------------+------------+
| id | username | password | email        | role       |
+----+----------+----------+--------------+------------+
|  2 | king     | king     | admin@qq.com | 普通管理员  |
|  1 | admin    | admin    | admin@qq.com | 超级管理员  |
+----+----------+----------+--------------+------------+
2 rows in set (2.65 sec)

-- 查询id大于2的记录，再按照role分组
mysql> SELECT * FROM cms_admin WHERE id>2 GROUP BY role;
+----+----------+----------+--------------+------------+
| id | username | password | email        | role       |
+----+----------+----------+--------------+------------+
|  3 | Tom      | Tom      | admin@qq.com | 普通管理员  |
+----+----------+----------+--------------+------------+
1 row in set (0.00 sec)

1、配合GROUP CONCAT()得到分组详情

-- 查询id、role、用户名详情，按role分组
mysql> SELECT id,role,GROUP_CONCAT(username) FROM cms_admin GROUP BY role;
+----+------------+------------------------+
| id | role       | GROUP_CONCAT(username) |
+----+------------+------------------------+
|  2 | 普通管理员  | king,Tom,queen,test    |
|  1 | 超级管理员  | admin                  |
+----+------------+------------------------+
2 rows in set (0.28 sec)

2、配合聚合函数
COUNT()：统计记录条数，COUNT()；
MAX()：查找最大记录；
MIN()：查找最小记录；
AVG()：得出平均值；
SUM()：得出总和。
首先将数据表中加入年龄和性别字段

+----+----------+----------+--------------+------------+------+------+
| id | username | password | email        | role       | age  | sex  |
+----+----------+----------+--------------+------------+------+------+
|  1 | admin    | admin    | admin@qq.com | 超级管理员  |   11 | 男   |
|  2 | king     | king     | admin@qq.com | 普通管理员  |   21 | 女   |
|  3 | Tom      | Tom      | admin@qq.com | 普通管理员  |   33 | 男   |
|  4 | queen    | queen    | admin@qq.com | 普通管理员  |   44 | 保密 |
|  5 | test     | test     | admin@qq.com | 普通管理员  |   25 | 女   |
+----+----------+----------+--------------+------------+------+------+

COUNT()：计算组内记录的条数，不统计NULL。

--查询编号,性别,用户名详情,组中总人数,组中最大年龄,最小年龄,平均年龄,以及年龄总和，以性别分组
mysql> SELECT id,sex,GROUP_CONCAT(username),
    -> COUNT(*) AS totalUsers,
    -> MAX(age) AS max_age,
    -> MIN(age) AS min_age,
    -> AVG(age) AS avg_age,
    -> SUM(age) AS sum_age
    -> FROM cms_admin
    -> GROUP BY sex;
+----+------+------------------------+------------+---------+---------+---------+---------+
| id | sex  | GROUP_CONCAT(username) | totalUsers | max_age | min_age | avg_age | sum_age |
+----+------+------------------------+------------+---------+---------+---------+---------+
|  1 | 男   | admin,Tom              |          2 |      33 |      11 | 22.0000 |      44 |
|  2 | 女   | king,test              |          2 |      25 |      21 | 23.0000 |      46 |
|  4 | 保密 | queen                  |          1 |      44 |      44 | 44.0000 |      44 |
+----+------+------------------------+------------+---------+---------+---------+---------+
3 rows in set (0.05 sec)

3、配合WITH ROLLUP记录上面所有记录的总和

mysql> SELECT id,sex,
    -> COUNT(*) AS totalUsers,
    -> MAX(age) AS max_age,
    -> MIN(age) AS min_age
    -> FROM cms_admin
    -> GROUP BY sex WITH ROLLUP;
+----+------+------------+---------+---------+
| id | sex  | totalUsers | max_age | min_age |
+----+------+------------+---------+---------+
|  1 | 男   |          2 |      33 |      11 |
|  2 | 女   |          2 |      25 |      21 |
|  4 | 保密 |          1 |      44 |      44 |
|  4 | NULL |          5 |      44 |      11 |
+----+------+------------+---------+---------+
4 rows in set (0.00 sec)

4、HAVING子句
通过HAVING子句对分组结果进行二次筛选。

-- 查询性别sex,用户名详情,组中总人数,最大年龄,年龄总和,根据性别分组
mysql> SELECT sex,GROUP_CONCAT(username) AS users,
    -> COUNT(*) AS totalUsers,
    -> MAX(age) AS max_age,
    -> SUM(age) AS sum_age
    -> FROM cms_admin
    -> GROUP BY sex;
+------+-----------+------------+---------+---------+
| sex  | users     | totalUsers | max_age | sum_age |
+------+-----------+------------+---------+---------+
| 男   | admin,Tom |          2 |      33 |      44 |
| 女   | king,test |          2 |      25 |      46 |
| 保密 | queen     |          1 |      44 |      44 |
+------+-----------+------------+---------+---------+
3 rows in set (0.00 sec)

-- 查询组中人数大于1的
mysql> SELECT sex,GROUP_CONCAT(username) AS users,
    -> COUNT(*) AS totalUsers,
    -> MAX(age) AS max_age,
    -> SUM(age) AS sum_age
    -> FROM cms_admin
    -> GROUP BY sex
    -> HAVING COUNT(*)>1;
+------+-----------+------------+---------+---------+
| sex  | users     | totalUsers | max_age | sum_age |
+------+-----------+------------+---------+---------+
| 男   | admin,Tom |          2 |      33 |      44 |
| 女   | king,test |          2 |      25 |      46 |
+------+-----------+------------+---------+---------+
2 rows in set (0.00 sec)

排序

通过ORDER BY对查询结果排序。
默认排序为主键升序ASC

mysql> SELECT * FROM cms_admin;
+----+----------+----------+--------------+------------+------+------+
| id | username | password | email        | role       | age  | sex  |
+----+----------+----------+--------------+------------+------+------+
|  1 | admin    | admin    | admin@qq.com | 超级管理员  |   11 | 男   |
|  2 | king     | king     | admin@qq.com | 普通管理员  |   21 | 女   |
|  3 | Tom      | Tom      | admin@qq.com | 普通管理员  |   33 | 男   |
|  4 | queen    | queen    | admin@qq.com | 普通管理员  |   44 | 保密 |
|  5 | test     | test     | admin@qq.com | 普通管理员  |   25 | 女   |
+----+----------+----------+--------------+------------+------+------+
5 rows in set (0.29 sec)

指定排序为主键降序DESC

mysql> SELECT * FROM cms_admin ORDER BY id DESC;
+----+----------+----------+--------------+------------+------+------+
| id | username | password | email        | role       | age  | sex  |
+----+----------+----------+--------------+------------+------+------+
|  5 | test     | test     | admin@qq.com | 普通管理员  |   25 | 女   |
|  4 | queen    | queen    | admin@qq.com | 普通管理员  |   44 | 保密 |
|  3 | Tom      | Tom      | admin@qq.com | 普通管理员  |   33 | 男   |
|  2 | king     | king     | admin@qq.com | 普通管理员  |   21 | 女   |
|  1 | admin    | admin    | admin@qq.com | 超级管理员  |   11 | 男   |
+----+----------+----------+--------------+------------+------+------+
5 rows in set (0.31 sec)

指定排序为年龄升序ASC

mysql> SELECT * FROM cms_admin ORDER BY age ASC;
+----+----------+----------+--------------+------------+------+------+
| id | username | password | email        | role       | age  | sex  |
+----+----------+----------+--------------+------------+------+------+
|  1 | admin    | admin    | admin@qq.com | 超级管理员  |   11 | 男   |
|  2 | king     | king     | admin@qq.com | 普通管理员  |   21 | 女   |
|  5 | test     | test     | admin@qq.com | 普通管理员  |   25 | 女   |
|  3 | Tom      | Tom      | admin@qq.com | 普通管理员  |   33 | 男   |
|  4 | queen    | queen    | admin@qq.com | 普通管理员  |   44 | 保密 |
+----+----------+----------+--------------+------------+------+------+
5 rows in set (0.00 sec)

随机显示记录

mysql> SELECT * FROM cms_admin ORDER BY RAND();
+----+----------+----------+--------------+------------+------+------+
| id | username | password | email        | role       | age  | sex  |
+----+----------+----------+--------------+------------+------+------+
|  3 | Tom      | Tom      | admin@qq.com | 普通管理员  |   33 | 男   |
|  5 | test     | test     | admin@qq.com | 普通管理员  |   25 | 女   |
|  4 | queen    | queen    | admin@qq.com | 普通管理员  |   44 | 保密 |
|  1 | admin    | admin    | admin@qq.com | 超级管理员  |   11 | 男   |
|  2 | king     | king     | admin@qq.com | 普通管理员  |   21 | 女   |
+----+----------+----------+--------------+------------+------+------+
5 rows in set (0.29 sec)

也可以同时对多条字段进行排序，以逗号隔开，当第一个字段值相同时比较下一个字段值。

限制显示条数

1、LIMIT 显示条数

-- 显示前三条
mysql> SELECT * FROM cms_admin LIMIT 3;
+----+----------+----------+--------------+------------+------+------+
| id | username | password | email        | role       | age  | sex  |
+----+----------+----------+--------------+------------+------+------+
|  1 | admin    | admin    | admin@qq.com | 超级管理员  |   11 | 男   |
|  2 | king     | king     | admin@qq.com | 普通管理员  |   21 | 女   |
|  3 | Tom      | Tom      | admin@qq.com | 普通管理员  |   33 | 男   |
+----+----------+----------+--------------+------------+------+------+
3 rows in set (0.00 sec)

2、LIMIT 偏移量，显示条数

-- 显示第二条后的两条
mysql> SELECT * FROM cms_admin LIMIT 2,2;
+----+----------+----------+--------------+------------+------+------+
| id | username | password | email        | role       | age  | sex  |
+----+----------+----------+--------------+------------+------+------+
|  3 | Tom      | Tom      | admin@qq.com | 普通管理员  |   33 | 男   |
|  4 | queen    | queen    | admin@qq.com | 普通管理员  |   44 | 保密 |
+----+----------+----------+--------------+------------+------+------+
2 rows in set (0.00 sec)

连接查询

连接查询是将两个或两个以上的表按某个条件连接起来，从中选取需要的数据。连接查询是同时查询两个或两个以上的表时使用的，当不同的表中存在相同意义的字段时，可以通过该字段连接这几个表。
首先创建两个数据表，第一张表省分字段下的值为第二张表的索引

-- cms_user
+----+--------------+--------------+-------------+-------+------+------+
| id | username     | password     | email       | proId | age  | sex  |
+----+--------------+--------------+-------------+-------+------+------+
|  1 | zhangsan     | zhangsan     | user@qq.com |     1 |   11 | 男   |
|  2 | zhangsanfeng | zhangsanfeng | user@qq.com |     2 |   21 | 女   |
|  3 | zhangziyi    | zhangziyi    | user@qq.com |     3 |   33 | 男   |
|  4 | long         | long         | user@qq.com |     4 |   44 | 女   |
|  5 | ring         | ring         | user@qq.com |     2 |   25 | 男   |
|  6 | queen        | queen        | user@qq.com |     3 |   77 | 女   |
|  7 | king         | king         | user@qq.com |     5 |   56 | 男   |
|  8 | blek         | blek         | user@qq.com |     1 |   88 | 女   |
|  9 | rose         | rose         | user@qq.com |     2 |   12 | 男   |
| 10 | lily         | lily         | user@qq.com |     2 |   32 | 女   |
| 11 | john         | john         | user@qq.com |     2 |   65 | 保密 |
+----+--------------+--------------+-------------+-------+------+------+

-- provinces
+----+-----------+
| id | proName   |
+----+-----------+
|  1 | Beijing   |
|  5 | Chongqing |
|  4 | Guangzhou |
|  2 | Shanghai  |
|  3 | Shenzhen  |
+----+-----------+

内连接查询

JOIN | CROSS JOIN | INNER JOIN
通过ON连接条件
显示两个表中符合连接条件的记录

mysql> SELECT u.id,u.username,u.email,u.sex,p.proName
    ->
    -> FROM cms_user AS u
    ->
    -> INNER JOIN provinces AS p
    ->
    -> ON u.proId=p.id;
+----+--------------+-------------+------+-----------+
| id | username     | email       | sex  | proName   |
+----+--------------+-------------+------+-----------+
|  1 | zhangsan     | user@qq.com | 男   | Beijing   |
|  2 | zhangsanfeng | user@qq.com | 女   | Shanghai  |
|  3 | zhangziyi    | user@qq.com | 男   | Shenzhen  |
|  4 | long         | user@qq.com | 女   | Guangzhou |
|  5 | ring         | user@qq.com | 男   | Shanghai  |
|  6 | queen        | user@qq.com | 女   | Shenzhen  |
|  7 | king         | user@qq.com | 男   | Chongqing |
|  8 | blek         | user@qq.com | 女   | Beijing   |
|  9 | rose         | user@qq.com | 男   | Shanghai  |
| 10 | lily         | user@qq.com | 女   | Shanghai  |
| 11 | john         | user@qq.com | 保密 | Shanghai  |
+----+--------------+-------------+------+-----------+
11 rows in set (0.00 sec)

外连接查询

在cms_user插入一条记录，其中省份数值超出第二张表的范畴

+----+----------+----------+-------------+-------+------+------+
| id | username | password | email       | proId | age  | sex  |
+----+----------+----------+-------------+-------+------+------+
| 12 | TEST     | TEST     | user@qq.com |    20 |   18 | NULL |
+----+----------+----------+-------------+-------+------+------+

1、左外连接：LEFT [OUTER] JOIN，显示左表的全部记录及右表符合连接条件的记录；

mysql> SELECT u.id,u.username,u.email,u.sex,p.proName
    ->
    -> FROM cms_user AS u
    ->
    -> LEFT JOIN provinces AS p
    ->
    -> ON u.proId=p.id;
+----+--------------+-------------+------+-----------+
| id | username     | email       | sex  | proName   |
+----+--------------+-------------+------+-----------+
|  1 | zhangsan     | user@qq.com | 男   | Beijing   |
|  2 | zhangsanfeng | user@qq.com | 女   | Shanghai  |
|  3 | zhangziyi    | user@qq.com | 男   | Shenzhen  |
|  4 | long         | user@qq.com | 女   | Guangzhou |
|  5 | ring         | user@qq.com | 男   | Shanghai  |
|  6 | queen        | user@qq.com | 女   | Shenzhen  |
|  7 | king         | user@qq.com | 男   | Chongqing |
|  8 | blek         | user@qq.com | 女   | Beijing   |
|  9 | rose         | user@qq.com | 男   | Shanghai  |
| 10 | lily         | user@qq.com | 女   | Shanghai  |
| 11 | john         | user@qq.com | 保密 | Shanghai  |
| 12 | TEST         | user@qq.com | NULL | NULL      |
+----+--------------+-------------+------+-----------+
12 rows in set (0.00 sec)

2、右外连接：RIGHT [OUTER] JOIN，显示右表的全部记录以及左表符合条件的记录。

mysql> SELECT u.id,u.username,u.email,u.sex,p.proName
    ->
    -> FROM cms_user AS u
    ->
    -> RIGHT JOIN provinces AS p
    ->
    -> ON u.proId=p.id;
+------+--------------+-------------+------+-----------+
| id   | username     | email       | sex  | proName   |
+------+--------------+-------------+------+-----------+
|    1 | zhangsan     | user@qq.com | 男   | Beijing   |
|    8 | blek         | user@qq.com | 女   | Beijing   |
|    7 | king         | user@qq.com | 男   | Chongqing |
|    4 | long         | user@qq.com | 女   | Guangzhou |
|    2 | zhangsanfeng | user@qq.com | 女   | Shanghai  |
|    5 | ring         | user@qq.com | 男   | Shanghai  |
|    9 | rose         | user@qq.com | 男   | Shanghai  |
|   10 | lily         | user@qq.com | 女   | Shanghai  |
|   11 | john         | user@qq.com | 保密 | Shanghai  |
|    3 | zhangziyi    | user@qq.com | 男   | Shenzhen  |
|    6 | queen        | user@qq.com | 女   | Shenzhen  |
+------+--------------+-------------+------+-----------+
11 rows in set (0.00 sec)

联合查询

1、UNION
2、 UNION ALL
UNION和UNION ALL区别是UNION去掉相同记录，UNION ALL是简单的合并到一起。

mysql> SELECT username FROM cms_admin;
+----------+
| username |
+----------+
| admin    |
| king     |
| queen    |
| test     |
| Tom      |
+----------+
5 rows in set (0.00 sec)

mysql> SELECT username FROM cms_user;
+--------------+
| username     |
+--------------+
| blek         |
| john         |
| king         |
| lily         |
| long         |
| queen        |
| ring         |
| rose         |
| TEST         |
| zhangsan     |
| zhangsanfeng |
| zhangziyi    |
+--------------+
12 rows in set (0.00 sec)

UNION

mysql> SELECT username FROM cms_admin UNION SELECT username FROM cms_user;
+--------------+
| username     |
+--------------+
| admin        |
| king         |
| queen        |
| test         |
| Tom          |
| blek         |
| john         |
| lily         |
| long         |
| ring         |
| rose         |
| zhangsan     |
| zhangsanfeng |
| zhangziyi    |
+--------------+
14 rows in set (0.00 sec)

UNION ALL

mysql> SELECT username FROM cms_admin UNION ALL SELECT username FROM cms_user;
+--------------+
| username     |
+--------------+
| admin        |
| king         |
| queen        |
| test         |
| Tom          |
| blek         |
| john         |
| king         |
| lily         |
| long         |
| queen        |
| ring         |
| rose         |
| TEST         |
| zhangsan     |
| zhangsanfeng |
| zhangziyi    |
+--------------+
17 rows in set (0.00 sec)

子查询

子查询是将一个查询语句嵌套在另一个查询语句中，内层查询语句的查询结果可以作为外层查询语句提供条件。
1、使用[NOT] IN的子查询；
2、使用比较运算符的子查询=、>、<、>=、<=、<>、!=、<=>；
3、使用[NOT] EXISTS的子查询;
4、使用ANY I SOME或者ALL的子查询。
在这里插入图片描述
将查询结果写入到数据表
INSERT [INTO] tbl_name [(col_name, ···) SELECT···
创建数据表同时将查询结果写入到数据表
CREATE TABLE [IF NOT EXISTS] tbl_name [(create definition,…)]select statement

外键

外键是表的一个特殊字段，被参照的表是主表，外键所在字段的表为子表。外键的作用保持数据的一致性和完整性，可以实现一对一或一对多的关系。设置外键的原则需要记住，就是依赖于数据库中已存在的表的主键。外键的作用是建立该表与其父表的关联关系，父表中对记录做操作时，子表中与之对应的信息也应有相应的改变。

注意：
1、父表和子表必须使用相同的存储引擎,而且禁止使用临时表；
2、数据表的存储引擎只能为InnoDB；
3、外键列和参照列必须具有相似的数据类型，其中数字的长度或是否有符号位必须相同，而字符的长度则可以不同；
4、外键列和参照列必须创建索引，如果外键列不存在索引的话，MysQL将自动创建索引。

创建主表department

mysql> CREATE TABLE IF NOT EXISTS department(
    -> id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    -> depName VARCHAR(20) NOT NULL UNIQUE
    -> )ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT department(depName) VALUES('Teaching'),
    -> ('marketing'),
    -> ('operation'),
    -> ('supervisor');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM department;
+----+------------+
| id | depName    |
+----+------------+
|  2 | marketing  |
|  3 | operation  |
|  4 | supervisor |
|  1 | Teaching   |
+----+------------+
4 rows in set (0.00 sec)

创建子表employee

mysql> CREATE TABLE IF NOT EXISTS employee(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE,
    -> depId TINYINT UNSIGNED,
    -> FOREIGN KEY(depId) REFERENCES department(id)
    -> )ENGINE=INNODB;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT employee(username,depId) VALUES('king',1),
    -> ('queen',2),
    -> ('zhangsan',3),
    -> ('lisi',4),
    -> ('wangwu',1)
    -> ;

mysql> SELECT * FROM employee;
+----+----------+-------+
| id | username | depId |
+----+----------+-------+
|  1 | king     |     1 |
|  2 | queen    |     2 |
|  3 | zhangsan |     3 |
|  4 | lisi     |     4 |
|  5 | wangwu   |     1 |
+----+----------+-------+
5 rows in set (0.00 sec)

mysql> DESC employee;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL    |                |
| depId    | tinyint(3) unsigned  | YES  | MUL | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.30 sec)

直接删除主表中的记录，出现错误

mysql> DELETE FROM department WHERE id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`cms`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`depId`) REFERENCES `department` (`id`))

需要先删除子表的内容，才能删除主表的记录

mysql> DELETE FROM employee WHERE depId=1;
Query OK, 2 rows affected (0.01 sec)

mysql> DELETE FROM department WHERE id=1;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM department;
+----+------------+
| id | depName    |
+----+------------+
|  2 | marketing  |
|  3 | operation  |
|  4 | supervisor |
+----+------------+
3 rows in set (0.00 sec)

往子表中插入超出主表范畴的记录，出现错误

mysql> INSERT employee(username,depId) VALUES('test',11);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`cms`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`depId`) REFERENCES `department` (`id`))

删除外键

删除外键后，约束便都没有了。

mysql> ALTER TABLE employee DROP FOREIGN KEY employee_ibfk_1;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

添加外键

mysql> ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

外键约束

CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行；
创建子表

mysql> CREATE TABLE IF NOT EXISTS employee(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE,
    -> depId TINYINT UNSIGNED,
    -> FOREIGN KEY(depId) REFERENCES department(id) ON DELETE CASCADE ON UPDATE CASCADE
    -> )ENGINE=INNODB;

SET NULL:从父表删除或更新行，并设置子表中的外键列为NULL，如果使用该选项，必须保证子表列没有指定NOT NULL；
创建子表

mysql> CREATE TABLE IF NOT EXISTS employee(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE,
    -> depId TINYINT UNSIGNED,
    -> FOREIGN KEY(depId) REFERENCES department(id) ON DELETE SET NULL ON UPDATE SET NULL
    -> )ENGINE=INNODB;

RESTRICT:拒绝对父表的删除或更新操作；
NO ACTION:标准SQL的关键字，在MySQL中与RESTRICT相同。

正则表达式查询

REGEXP 匹配方式
常用匹配方式
在这里插入图片描述
查找以t开头的记录

mysql> SELECT * FROM cms_user WHERE username REGEXP '^t';
+----+----------+----------+-------------+-------+------+------+
| id | username | password | email       | proId | age  | sex  |
+----+----------+----------+-------------+-------+------+------+
| 12 | TEST     | TEST     | user@qq.com |    20 |   18 | NULL |
+----+----------+----------+-------------+-------+------+------+
1 row in set (0.00 sec)

查找以g结尾的记录

mysql> SELECT * FROM cms_user WHERE username REGEXP 'g$';
+----+--------------+--------------+-------------+-------+------+------+
| id | username     | password     | email       | proId | age  | sex  |
+----+--------------+--------------+-------------+-------+------+------+
|  2 | zhangsanfeng | zhangsanfeng | user@qq.com |     2 |   21 | 女    |
|  4 | long         | long         | user@qq.com |     4 |   44 | 女    |
|  5 | ring         | ring         | user@qq.com |     2 |   25 | 男     |
|  7 | king         | king         | user@qq.com |     5 |   56 | 男     |
+----+--------------+--------------+-------------+-------+------+------+
4 rows in set (0.00 sec)
yfqh9588
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值