深入浅出MySQL(四)

MySQL数据操作之添加记录，修改记录，删除记录

添加记录

向表中添加记录有如下几种方法：

INSERT[INTO] tbl_name[(col_name,...)] {VALUE | VALUES}(VALUES...)
INSERT tbl_name VALUE(value...) 需要按照建表的顺序给每一个字段赋值
INSERT tbl_name(字段名称,...) VALUES(值,...)
INSERT tbl_name[(字段名称,...)] VALUES(值,...),(值,...),(值,...);一次可添加多条记录
INSERT tbl_name SET 字段名称=值,...
INSERT tbl_name[(字段名称,...)] SELECT 字段名称,... FROM tbl_name [WHERE 条件]

对MySQL数据操作添加记录的测试用例如下：

-- 测试添加记录
CREATE DATABASE IF NOT EXISTS kim2 DEFAULT CHARACTER SET 'UTF8';
USE kim2;
CREATE TABLE IF NOT EXISTS user(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20) NOT NULL UNIQUE,
    age TINYINT UNSIGNED DEFAULT 18,
    email VARCHAR(50) NOT NULL DEFAULT '1@qq.com'
)ENGINE = INNODB CHARSET = UTF8;
-- 不指定字段名称
INSERT user VALUE(1,'kim',26,DEFAULT);
INSERT user VALUES(NULL,'smallkim',26,DEFAULT);
INSERT user VALUES(DEFAULT,'lily',26,'lily@qq.com');
-- 列出指定字段的形式插入数据
INSERT user(username,email) VALUES('frank','frank@qq.com');
-- 一次插入多条记录
INSERT user(username,age,email) VALUES('a',12,DEFAULT),('b',DEFAULT,'b@qq.com'),('c',45,DEFAULT);
-- INSERT...SET形式插入记录
INSERT user SET username = 'd',age = '45',email = 'd@qq.com';
-- INSERT ...SELECT形式插入记录
CREATE TABLE test(
    a VARCHAR(20)
);
INSERT test(a) VALUES('AA'),('BB'),('CC');
INSERT user(username) SELECT a FROM test;

结果如下：

+----+----------+------+--------------+
| id | username | age  | email        |
+----+----------+------+--------------+
|  1 | kim      |   26 | 1@qq.com     |
|  2 | smallkim |   26 | 1@qq.com     |
|  3 | lily     |   26 | lily@qq.com  |
|  4 | frank    |   18 | frank@qq.com |
|  5 | a        |   12 | 1@qq.com     |
|  6 | b        |   18 | b@qq.com     |
|  7 | c        |   45 | 1@qq.com     |
|  8 | d        |   45 | d@qq.com     |
|  9 | AA       |   18 | 1@qq.com     |
| 10 | BB       |   18 | 1@qq.com     |
| 11 | CC       |   18 | 1@qq.com     |
+----+----------+------+--------------+

修改记录

修改记录的语句为：UPDATE tbl_name SET字段名称 = 值,字段名称 = 值,... [WHERE 条件]
注意：如果不添加条件，整个表中的记录都会被更新
对于之前的user表中的记录进行测试

+----+----------+------+--------------+
| id | username | age  | email        |
+----+----------+------+--------------+
|  1 | kim      |   26 | 1@qq.com     |
|  2 | smallkim |   26 | 1@qq.com     |
|  3 | lily     |   26 | lily@qq.com  |
|  4 | frank    |   18 | frank@qq.com |
|  5 | a        |   12 | 1@qq.com     |
|  6 | b        |   18 | b@qq.com     |
|  7 | c        |   45 | 1@qq.com     |
|  8 | d        |   45 | d@qq.com     |
|  9 | AA       |   18 | 1@qq.com     |
| 10 | BB       |   18 | 1@qq.com     |
| 11 | CC       |   18 | 1@qq.com     |
+----+----------+------+--------------+

测试用例如下：

-- 测试更新记录
-- 条件：修改第一个用户的信息(id = 1)
UPDATE user SET age = 29
WHERE id = 1;
-- 修改user表中id = 3的用户,username age email 字段
UPDATE user SET username = 'horse',age = 25,email = 'horse@qq.com'
WHERE id = 3;
-- user表中所有用户年龄加10
UPDATE user SET age = age + 10;
-- 将user表中的id <= 5的用户年龄减20,将邮箱改为默认值
UPDATE user SET age = age - 20 
WHERE id <= 5;

查询表更新的结果如下：

+----+----------+------+--------------+
| id | username | age  | email        |
+----+----------+------+--------------+
|  1 | kim      |   19 | 1@qq.com     |
|  2 | smallkim |   16 | 1@qq.com     |
|  3 | horse    |   15 | horse@qq.com |
|  4 | frank    |    8 | frank@qq.com |
|  5 | a        |    2 | 1@qq.com     |
|  6 | b        |   28 | b@qq.com     |
|  7 | c        |   55 | 1@qq.com     |
|  8 | d        |   55 | d@qq.com     |
|  9 | AA       |   28 | 1@qq.com     |
| 10 | BB       |   28 | 1@qq.com     |
| 11 | CC       |   28 | 1@qq.com     |
+----+----------+------+--------------+

删除记录

删除记录的语句为:DELETE FROM tbl_name [WHERE 条件]
如果不添加条件，那么表中所有的记录都会被删除；另外DELETE清空数据表的时候，不会重置AUTO_INCREMENT的值，可以通过ALTER语句重置AUTO_INCREMENT
继续使用user表进行测试，原表如下：

+----+----------+------+--------------+
| id | username | age  | email        |
+----+----------+------+--------------+
|  1 | kim      |   19 | 1@qq.com     |
|  2 | smallkim |   16 | 1@qq.com     |
|  3 | horse    |   15 | horse@qq.com |
|  4 | frank    |    8 | frank@qq.com |
|  5 | a        |    2 | 1@qq.com     |
|  6 | b        |   28 | b@qq.com     |
|  7 | c        |   55 | 1@qq.com     |
|  8 | d        |   55 | d@qq.com     |
|  9 | AA       |   28 | 1@qq.com     |
| 10 | BB       |   28 | 1@qq.com     |
| 11 | CC       |   28 | 1@qq.com     |
+----+----------+------+--------------+

测试用例如下：

-- 测试删除语句
-- 要求删除用户名为kim的记录
DELETE FROM user WHERE username = 'kim';
-- 删除年龄为55岁的用户
DELETE FROM user WHERE age = 55;
-- 删除表中所有记录
DELETE FROM user;
-- 重置AUTO_INCREMENT
ALTER TABLE user AUTO_INCREMENT = 1;
-- 再次插入记录的时候 AUTO_INCREMENT就从1开始了
INSERT user(username,age,email) VALUES('kim',26,'kim@qq.com'),
('smallkim',26,'small@qq.com'),
('baga',25,'baga@qq.com');

再次查询表中所有的记录：

+----+----------+------+--------------+
| id | username | age  | email        |
+----+----------+------+--------------+
|  1 | kim      |   26 | kim@qq.com   |
|  2 | smallkim |   26 | small@qq.com |
|  3 | baga     |   25 | baga@qq.com  |
+----+----------+------+--------------+

除了DELETE语句外，还有彻底清空数据表的操作TRUNCATE[TABLE] tbl_name
TRUNCATE语句可以清除表中的所有记录，并且会重置AUTO_INCREMENT的值
测试用例如下：

-- TRUNCATE 
TRUNCATE user;
-- TRUNCATE会重置AUTO_INCREMENT
INSERT user(username,age,email) VALUES('kim',26,'kim@qq.com'),
('smallkim',26,'small@qq.com'),
('baga',25,'baga@qq.com');

再次查询表中所有的记录:

+----+----------+------+--------------+
| id | username | age  | email        |
+----+----------+------+--------------+
|  1 | kim      |   26 | kim@qq.com   |
|  2 | smallkim |   26 | small@qq.com |
|  3 | baga     |   25 | baga@qq.com  |
+----+----------+------+--------------+

MySQL数据操作之查询记录

对于查询一条记录的语句的完整格式为：

SELECT select_expr,... FROM tbl_name    
[WHERE 条件]                                           
[GROUP BY {col_name | position} HAVING 二次筛选]  
[ORDER BY {col_name | position | expr} [ASC | DESC]] 
[LIMIT 限制结果集的显示条数]

SELECT 语句的基本形式

查询表中所有记录使用语句

SELECT * FROM  tbl_name

指定字段的信息进行查询:

SELECT 字段名称,... FROM tbl_name

使用库名.表名进行查询可以不用打开指定的数据库

SELECT 字段名称,... FROM db_name.tbl_name;

给字段起别名在多表联查中有着重要的作用

SELECT 字段名称 [AS] 别名名称,... FROM db_name.tbl_name

还可以给数据表起别名

SELECT 字段名称,... FROM tbl_name [AS] 别名

可以使用表名.字段名的形式来查询

SELECT tbl_name.col_name,... FROM tbl_name

测试用例如下：

-- 测试查询操作
CREATE TABLE user1(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20) NOT NULL UNIQUE,
    age TINYINT UNSIGNED NOT NULL DEFAULT 18,
    sex ENUM('男','女','保密') NOT NULL DEFAULT '保密',
    addr VARCHAR(20) NOT NULL DEFAULT '北京',
    married TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0代表未婚,1代表已婚',
    salary FLOAT(8,2) NOT NULL DEFAULT 0
)ENGINE = INNODB CHARSET = UTF8;
INSERT user1 VALUES(1,'kim',26,'男','深圳',0,30000);
INSERT user1(username,age,sex,addr,married,salary) VALUES('small',26,'女','上海',0,25000);
INSERT user1 SET username = 'frank',age = 31,sex = '女',addr = '北京',salary = 40000;
INSERT user1 VALUES(NULL,'张三',38,'男','上海',0,15000),
(NULL,'张三丰',38,'男','上海',0,15000),
(NULL,'章子怡',39,'女','北京',1,85000),
(NULL,'汪峰',42,'男','深圳',1,95000),
(NULL,'刘德华',48,'男','香港',1,115000),
(NULL,'蔡徐坤',28,'男','北京',0,75000),
(NULL,'刘嘉玲',36,'女','香港',1,65000),
(NULL,'刘诗诗',32,'女','上海',1,35000);

-- SELECT 语句的基本形式
-- 查询表中的所有记录
SELECT * FROM user1;
-- 查询username,addr,age
SELECT username,addr,age FROM user1;
-- 查询mysql数据库下的user表中所有记录 可以使用库名.表名的形式
SELECT * FROM mysql.user;


-- 给查询的字段起别名
SELECT id AS '编号',username AS '用户名',sex AS '性别' FROM user1;
-- 显示为
+------+--------+------+
| 编号 | 用户名 | 性别 |
+------+--------+------+
|    1 | kim    | 男   |
|    2 | small  | 女   |
|    3 | 张三   | 男   |
|    4 | 张三丰 | 男   |
|    5 | 章子怡 | 女   |
|    6 | 汪峰   | 男   |
|    7 | 刘德华 | 男   |
|    8 | 蔡徐坤 | 男   |
|    9 | 刘嘉玲 | 女   |
|   10 | 刘诗诗 | 女   |
|   11 | frank  | 女   |
+------+--------+------+

-- 给表起别名
SELECT id,username FROM user1 AS u;

-- 可以使用表明.字段名的形式
SELECT user1.id,user1.username FROM user1;

WHERE 条件筛选记录

WHERE条件可以选出符合条件的记录
继续使用user1表，进行测试：

使用比较运算符
比较运算符号包括：>,>=,<,<=,!=,<>,<=>
测试用例：

-- 查询id,username,age id = 5 的用户
SELECT id,username,age FROM user1
WHERE id = 5;

检测NULL值
检测NULL值需要使用<=> 或者IS [NOT] NULL。其中 =和<=>的区别为后者可以检测出NULL值。
测试用例如下：

-- 添加desc字段 VARCHAR(100)
ALTER TABLE user1
ADD userDesc VARCHAR(100);

-- 更新 id <= 9 的用户 userDesc = 'this is a test'
UPDATE user1 SET userDesc = 'this is a test'
WHERE id <= 9;

+----+----------+-----+-----+------+---------+-----------+----------------+
| id | username | age | sex | addr | married | salary    | userDesc       |
+----+----------+-----+-----+------+---------+-----------+----------------+
|  1 | kim      |  26 | 男  | 深圳 |       0 |  30000.00 | this is a test |
|  2 | small    |  26 | 女  | 上海 |       0 |  25000.00 | this is a test |
|  3 | 张三     |  38 | 男  | 上海 |       0 |  15000.00 | this is a test |
|  4 | 张三丰   |  38 | 男  | 上海 |       0 |  15000.00 | this is a test |
|  5 | 章子怡   |  39 | 女  | 北京 |       1 |  85000.00 | this is a test |
|  6 | 汪峰     |  42 | 男  | 深圳 |       1 |  95000.00 | this is a test |
|  7 | 刘德华   |  48 | 男  | 香港 |       1 | 115000.00 | this is a test |
|  8 | 蔡徐坤   |  28 | 男  | 北京 |       0 |  75000.00 | this is a test |
|  9 | 刘嘉玲   |  36 | 女  | 香港 |       1 |  65000.00 | this is a test |
| 10 | 刘诗诗   |  32 | 女  | 上海 |       1 |  35000.00 | NULL           |
| 11 | frank    |  31 | 女  | 北京 |       0 |  40000.00 | NULL           |
+----+----------+-----+-----+------+---------+-----------+----------------+

检测NULL需要使用<=>或者使用 IS[NOT] NULL 来进行筛选。如检测userDesc为NULL的用户，可以使用查询语句：

SELECT id,username,age,userDesc FROM user1
WHERE userDesc IS NULL;

或

SELECT id,username,age,userDesc FROM user1
WHERE userDesc <=> NULL;

指定范围进行查询
使用语句[NOT] BETWEEN ... AND
测试用例如下：

-- 测试范围 BETWEEN AND
-- 测试年龄在18~30之间的用户
SELECT id,username,age,sex FROM user1
WHERE age BETWEEN 18 AND 30;

+----+----------+-----+-----+
| id | username | age | sex |
+----+----------+-----+-----+
|  1 | kim      |  26 | 男  |
|  2 | small    |  26 | 女  |
|  8 | 蔡徐坤   |  28 | 男  |
+----+----------+-----+-----+

指定集合查询
使用语句[NOT] IN (值,...)
测试用例如下：

-- 测试指定集合 IN
-- 查询编号为1,3,5,7,9的用户
SELECT id,username,age FROM user1
WHERE id IN(1,3,5,7,9);

+----+----------+-----+
| id | username | age |
+----+----------+-----+
|  1 | kim      |  26 |
|  3 | 张三     |  38 |
|  5 | 章子怡   |  39 |
|  7 | 刘德华   |  48 |
|  9 | 刘嘉玲   |  36 |
+----+----------+-----+

使用逻辑运算符AND和OR
AND代表逻辑与，OR代表逻辑或
测试用例如下：

-- 查询薪水范围在60000~100000 并且性别为男 addr = '北京' 的用户
SELECT id,username,age,sex,salary FROM user1
WHERE salary BETWEEN 60000 AND 100000 AND sex = '男' AND addr = '北京';

+----+----------+-----+-----+----------+
| id | username | age | sex | salary   |
+----+----------+-----+-----+----------+
|  8 | 蔡徐坤   |  28 | 男  | 75000.00 |
+----+----------+-----+-----+----------+

匹配字符进行模糊查询
模糊查询使用LIKE语句[NOT] LIKE
LIKE通常会结合通配符进行使用,其中%代表可以匹配任意长度的字符串_代表任意一个字符。
测试用例如下：

-- 要求用户名中包含三的用户
SELECT id,username,age,sex FROM user1
WHERE username LIKE '%三%';

+----+----------+-----+
| id | username | age |
+----+----------+-----+
|  3 | 张三     |  38 |
|  4 | 张三丰   |  38 |
+----+----------+-----+


-- 查询用户名长度为3位的用户
SELECT id,username,age FROM user1
WHERE username LIKE '___';

+----+----------+-----+
| id | username | age |
+----+----------+-----+
|  1 | kim      |  26 |
|  4 | 张三丰   |  38 |
|  5 | 章子怡   |  39 |
|  7 | 刘德华   |  48 |
|  8 | 蔡徐坤   |  28 |
|  9 | 刘嘉玲   |  36 |
| 10 | 刘诗诗   |  32 |
+----+----------+-----+

GROUP BY

GROUP BY即：分组。分组会把值相同的放到一个组中，最终查询出的结果只会显示组中的一条记录。测试用例如下：

SELECT id,username,age,sex FROM user1
GROUP BY sex;

GROUP_CONCAT()
分组配合GROUP_CONCAT()查看族中某个字段的详细信息
测试用例如下：

-- GROUP_CONCAT()
-- 按照性别分组，查询组中的用户名有哪些
SELECT GROUP_CONCAT(username),age,sex,addr FROM user1
GROUP BY sex;

分组可以配合聚合函数使用
聚合函数分别有：

COUNT()
COUNT()函数为统计记录的总数;如果是COUNT(字段名称),字段的值如果为NULL,则不统计进来;COUNT(*)则会统计NULL值
SUM()
求和
MAX()
求最大值
MIN()
求最小值
AVG()
求平均值

对于聚合函数的测试用例如下：

-- 按照addr分组，得到用户名详情，总人数，得到组中年龄的总和，年龄的最大值，最小值，平均值
SELECT addr,
GROUP_CONCAT(username) AS userDetail,
COUNT(*) AS total_users,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age FROM user1
GROUP BY addr;

+------+--------------------------+-------------+---------+---------+---------+---------+
| addr | userDetail               | total_users | sum_age | max_age | min_age | avg_age |
+------+--------------------------+-------------+---------+---------+---------+---------+
| 上海 | small,张三,张三丰,刘诗诗 |           4 |     134 |      38 |      26 | 33.5000 |
| 北京 | 章子怡,蔡徐坤,frank      |           3 |      98 |      39 |      28 | 32.6667 |
| 深圳 | kim,汪峰                 |           2 |      68 |      42 |      26 | 34.0000 |
| 香港 | 刘德华,刘嘉玲            |           2 |      84 |      48 |      36 | 42.0000 |
+------+--------------------------+-------------+---------+---------+---------+---------+

配合WITH ROLLUP关键字使用
使用WITH ROLLUP关键字会在记录的末尾添加一条记录，是上面所有记录的总和
测试用例如下：

-- 测试WITH ROLLUP
SELECT GROUP_CONCAT(username) AS userDetail,
COUNT(*) AS totalUsers
FROM user1
GROUP BY sex
WITH ROLLUP;

+---------------------------------------------------------------------+------------+
| userDetail                                                          | totalUsers |
+---------------------------------------------------------------------+------------+
| kim,张三,张三丰,汪峰,刘德华,蔡徐坤                                  |          6 |
| small,章子怡,刘嘉玲,刘诗诗,frank                                    |          5 |
| kim,张三,张三丰,汪峰,刘德华,蔡徐坤,small,章子怡,刘嘉玲,刘诗诗,frank |         11 |
+---------------------------------------------------------------------+------------+

HAVING子句对分组结果进行二次筛选
HAVING子句会对分组后的结果进行二次的条件筛选
测试用例如下：

-- 测试HAVING
-- 按照addr分组，统计总人数
SELECT addr,
GROUP_CONCAT(username) AS userDetail,
COUNT(*) AS totalUsers
FROM user1
GROUP BY addr;

+------+--------------------------+------------+
| addr | userDetail               | totalUsers |
+------+--------------------------+------------+
| 上海 | small,张三,张三丰,刘诗诗 |          4 |
| 北京 | 章子怡,蔡徐坤,frank      |          3 |
| 深圳 | kim,汪峰                 |          2 |
| 香港 | 刘德华,刘嘉玲            |          2 |
+------+--------------------------+------------+

-- 对于分组结果进行二次筛选,条件为组中总人数大于等于3

SELECT addr,
GROUP_CONCAT(username) AS userDetail,
COUNT(*) AS totalUsers
FROM user1
GROUP BY addr
HAVING COUNT(*) >= 3;

-- 或

SELECT addr,
GROUP_CONCAT(username) AS userDetail,
COUNT(*) AS totalUsers
FROM user1
GROUP BY addr
HAVING totalUsers >= 3;

+------+--------------------------+------------+
| addr | userDetail               | totalUsers |
+------+--------------------------+------------+
| 上海 | small,张三,张三丰,刘诗诗 |          4 |
| 北京 | 章子怡,蔡徐坤,frank      |          3 |
+------+--------------------------+------------+

ORDER BY

ORDER BY 排序，语法为:ORDER BY 字段名称 ASC | DESC,可以实现按照某个字段升序或者降序进行查询排序显示的效果。
测试用例如下：

-- 测试ORDER BY
-- 查询的结果按照id降序排列
SELECT id,username,age
FROM user1
ORDER BY id DESC;

+----+----------+-----+
| id | username | age |
+----+----------+-----+
| 11 | frank    |  31 |
| 10 | 刘诗诗   |  32 |
|  9 | 刘嘉玲   |  36 |
|  8 | 蔡徐坤   |  28 |
|  7 | 刘德华   |  48 |
|  6 | 汪峰     |  42 |
|  5 | 章子怡   |  39 |
|  4 | 张三丰   |  38 |
|  3 | 张三     |  38 |
|  2 | small    |  26 |
|  1 | kim      |  26 |
+----+----------+-----+

-- 查询的结果按照age 升序排序
SELECT id,username,age
FROM user1
ORDER BY age ASC;

-- 或
SELECT id,username,age
FROM user1
ORDER BY age;

-- 按照多个字段排序，
SELECT id,username,age
FROM user1
ORDER By age ASC,id ASC;

-- 先按照age的升序进行排序，在排好序的基础上按照id的升序进行排序

+----+----------+-----+
| id | username | age |
+----+----------+-----+
|  1 | kim      |  26 |
|  2 | small    |  26 |
|  8 | 蔡徐坤   |  28 |
| 11 | frank    |  31 |
| 10 | 刘诗诗   |  32 |
|  9 | 刘嘉玲   |  36 |
|  3 | 张三     |  38 |
|  4 | 张三丰   |  38 |
|  5 | 章子怡   |  39 |
|  6 | 汪峰     |  42 |
|  7 | 刘德华   |  48 |
+----+----------+-----+

-- 实现随机记录
SELECT id,username,age
FROM user1
ORDER BY RAND();

LIMIT

LIMIT 的语句有两种格式：

LIMIT 值
LIMIT直接加一个值为显示结果集的前几条记录
LIMIT offset,row_count
offset为偏移量,代表从offset开始，显示几条记录

同时，UPDATE或者DELETE时，LIMIT只支持一个参数形式，也就是说支持第一种语法格式，不支持第二种语法格式
测试用例如下：

-- LIMIT 限制结果集的显示条数
-- 显示结果集中的前五条记录
SELECT id,username,age,sex
FROM user1
LIMIT 5;

-- LIMIT offset,row_count
-- offset 偏移量

SELECT id,username,age,sex
FROM user1
LIMIT 0,5;


-- 分页操作
-- 显示前3条记录
SELECT id,username,age,sex
FROM user1
LIMIT 0,3;
-- 显示下三条记录
SELECT id,username,age,sex
FROM user1
LIMIT 3,3;
-- ...
SELECT id,username,age,sex
FROM user1
LIMIT 6,3;
-- ...
SELECT id,username,age,sex
FROM user1
LIMIT 9,3;

-- 1. 更新user1表中的前三条记录，将age加5
UPDATE user1 SET age = age+ 5
LIMIT 3;
-- 2. 将user1表中id字段降序排列，更新前三条记录，将age减10
UPDATE user1 SET age = age-10 
ORDER BY id DESC
LIMIT 3;
-- 3. 删除user1表中前三条记录
DELETE FROM user1
LIMIT 3;
-- 4. 删除use1表中id字段降序排列的前三条记录
DELETE FROM user1
ORDER BY id DESC
LIMIT 3;

到这里为止，我们已经会查询语句的完整格式了，完整格式如下：

-- 完整的SELECT语句形式
SELECT addr,
GROUP_CONCAT(username) AS userDetail,
COUNT(*) AS totalUsers,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age
FROM user1
WHERE id >= 2
GROUP BY addr
HAVING totalUsers >= 2
ORDER BY totalUsers ASC
LIMIT 0,2;