Java-mysql：MYSQL数据操作

添加记录：1.一次添加一条记录：INSERT [INTO] 数据表名[(字段名称1，2……)] {VALUE|VALUES}(值1，2……);2.一次添加多条记录：INSERT [INTO] 数据表名[(字段名称1，2……)] VALUES(值1，2……),(值1，2……),(值1，2……)……;3.INSERT……SET的形式：INSERT 数据表名 SET 字段名称1=值1，字段名称2=值2……；

• 首先创建数据库，创建数据表；

CREATE DATABASE IF NOT EXISTS imooc1 DEFAULT CHARACTER SET 'UTF8';
USE imooc1;
CREATE TABLE IF NOT EXISTS user1(
	id INT UNSIGNED AUTO_INCREMENT KEY COMMENT "编号",
	username VARCHAR(20) NOT NULL UNIQUE COMMENT "用户名",
	age TINYINT UNSIGNED DEFAULT 18 COMMENT "年龄",
	email VARCHAR(50) NOT NULL DEFAULT "imooc@qq.com" COMMENT "邮箱"
)ENGINE=INNODB CHARSET=UTF8;

• 以不指定字段名称的方式向表中添加记录；

-- 不指定字段名称
INSERT user1 VALUE(1, "coco", 22, "coco@qq.com");
INSERT user1 VALUES(DEFAULT, "lili", 18, "lili@qq.com");

• 以列出指定字段的形式向表中添加记录；

-- 列出指定字段的形式
INSERT user1(username, email) VALUES("rose", "rose@qq.com");

• 一次向表中添加多条记录；

-- 一次添加多条记录
INSERT user1 VALUES(NULL, "mini", 23, "mini@qq.com"), 
(DEFAULT, "jack", 25, "jack@qq.com");

• 以INSERT……SET的形式添加记录；

-- INSERT……SET的形式
INSERT user1 SET username = "a", email = "a@qq.com";

修改记录：UPDATE 表名 SET 字段名称=值，字段名称=值 [WHERE 条件];如果不添加条件，整个表中的记录都会被更新。

• 修改某条记录的指定字段名称的值；

UPDATE user1 SET age = 24 WHERE id = 1;
UPDATE user1 SET username = "lyly", age = 22, email = "lyly@qq.com" WHERE id = 3;

• 向表中所有用户的年龄加10；

-- 向表中所有用户年龄加10
UPDATE user1 SET age = age + 10;

• 向表中id<=3的用户年龄减20，将邮箱改为默认值

-- 向表中id<=3的用户年龄减20，将邮箱改为默认值
UPDATE user1 SET age = age - 20, email = DEFAULT WHERE id <= 3;

删除记录：

• DELETE FROM 表名 [WHERE 条件];如果不添加条件，整个表中的记录都会被更新。

DELETE FROM user1 WHERE username = "coco";

• DELETE清空数据表的时候不会重置AUTO_INCREMENT的值，可以通过ALTER语句将其重置为1。

ALTER TABLE user1 AUTO_INCREMENT = 1;

• 彻底清空数据表：TRUNCATE [TABLE] 表名;作用1：清除表中所有记录。作用2：会重置AUTO_INCREMENT的值。

TRUNCATE user1;

查询记录：

• SELECT 要查询内容1…… FROM 表名 [WHERE 条件] [GROUP BY {字段名称|位置} HAVING 二次筛选] [ORDER BY {字段名称|位置|表达式}[ASC|DESC]] [限制结果集的显示条数];
• 查询表中所有记录（所有字段）：SELECT * FROM 表名;

SELECT * FROM user1;

• 指定字段的信息：SELECT 字段名称,…… FROM 表名;

SELECT username, age FROM user1;

• 库名.表名：SELECT 字段名称，…… FROM 数据库名.表名;

-- 查询imooc数据库下user1表中的所有记录
SELECT * FROM imooc.user1;

• 给字段起别名：SELECT 字段名称 [AS] 别名名称，…… FROM 数据库名.表名;

-- 给字段起别名 id 编号  username 用户名 email 邮箱
SELECT id AS "编号", username AS "用户名", email AS "邮箱" FROM user1;

• 给数据表起别名：SELECT 字段名称,…… FROM 表名 [AS] 别名;

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

• 表名.字段名称：SELECT 表名.字段名称，…… FROM 表名;

-- 测试表名.字段名称
SELECT user1.id, user1.username FROM user1;

• WHERE条件：会筛选出符合条件的记录。

1. 比较运算符：> >= < <= != <> <=>
   <=> 和 = 的区别：<=>可以检测NULL值。
2. IS [NOT] NULL ：检测值是否为NULL或者NOT NULL。
3. 指定范围：[NOT] BETWEEN …… AND

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

4.指定集合：[NOT] IN(值， ……)

-- 测试指定集合 IN
-- 查询id为 1，3，5
SELECT id, username, age FROM user1
WHERE id IN(1, 3, 5);
-- 查询username不为coco, rose
SELECT id, username, age FROM user1
WHERE username NOT IN("coco", "rose");

5.逻辑运算符：AND 逻辑与 OR 逻辑或

-- 测试逻辑运算符
-- 查询id<3且age>20的用户
SELECT id, username, age FROM user1
WHERE id < 3 AND age > 20;

6.匹配字符：①[NOT] LIKE ②% 任意长度的字符串 ③_ 任意一个字符

-- 测试模糊查询 [NOT] LIKE
SELECT id, username, email FROM user1 
WHERE username LIKE "lili";

-- 要求用户名中包含l，查询字母不区分大小写
-- l出现在字符任意位置
SELECT id, username, email FROM user1 
WHERE username LIKE "%l%";   
-- l出现在字符起始位置
SELECT id, username, email FROM user1 
WHERE username LIKE "l%";   
-- l出现在字符末尾位置
SELECT id, username, email FROM user1 
WHERE username LIKE "%l"; 

-- 用户名长度为四位的用户
SELECT id, username, email FROM user1 
WHERE username LIKE "____"; 
SELECT id, username, email FROM user1 
WHERE username LIKE "__s_"; 

• GROUP BY 分组：

1. 把值相同的放到一个组中，最终查询出的结果只会显示组中一条记录

-- 按照性别分组
SELECT username, age, sex FROM user1
GROUP BY sex;

2. 分组配合GROUP_CONCAT() 查看组中某个字段的详细信息

-- 按照用户名分组，查询组中的用户名有哪些
SELECT CONCAT(username), age, email FROM user1
GROUP BY username;

3. 配合聚合函数使用

（1）COUNT() 统计记录总数，如果写的是COUNT(字段名称)，字段中的值为NULL，不统计进来，若写COUNT(*)会统计NULL值

-- 测试COUNT()
SELECT COUNT(*) FROM user1;
SELECT COUNT(id) FROM user1;
SELECT COUNT(*) AS total_username FROM user1;

-- 结合GROUP BY使用
-- 按照sex分组，得到用户名详情，并且分别组中的总人数
SELECT sex, GROUP_CONCAT(username) AS usersDetail, COUNT(*) AS totalUsers FROM user1
GROUP BY sex;

（2）SUM() 求和；MAX() 求最大值；MIN() 求最小值；AVG() 求平均值

-- 按照addr分组，得到用户名详情，总人数，得到组中年龄的总和，年龄的最大值、最小值、平均值和
SELECT addr, 
GROUP_CONCAT(username) AS usersDetail,
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
GROUP BY addr;

4. 配合WITH ROLLUP关键使用：会在记录末尾添加一条记录，是上面所有记录的总和
5. 结合WHERE条件，查询age>=25的用户，查询用户名详情用usersDetail命名，并且按照sex分组

SELECT sex, 
GROUP_CONCAT(username) AS userDetail,
COUNT(*) AS totalUsers
FROM user1
WHERE age >= 25
GROUP BY sex;

6. HAVING子句对分组结果进行二次筛选
   不进行二次筛选：

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

结果：

进行二次筛选：

-- 对于分组结果进行二次筛选，条件是组中总人数>=2
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers
FROM user1
GROUP BY addr
HAVING COUNT(*) >= 2;

结果：

• ORDER BY 排序 ORDER BY 字段名称 ASC|DESC
  ASC 升序 ， DESC 降序

-- 按照id降序排列
SELECT id, username, age
FROM user1
ORDER BY id DESC;

结果：

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

结果：

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

结果：

• LIMIT限制结果集显示条数
  LIMIT值：显示结果集的前几条记录

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

LIMIT offset, row_count ：从offset开始，显示row_count条记录，

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

练习：

-- 更新user1表中的前3条记录，将age＋5
UPDATE user1 SET age = age + 5
LIMIT 3;

-- 将user1表中id字段降序排列，更新前三条记录，将age-10
UPDATE user1 SET age = age - 10
ORDER BY id DESC
LIMIT 3;

-- 删除user1表中前三条记录
DELETE FROM user1 LIMIT 3;

-- 删除user1表中id字段降序排列的前三条记录
DELETE FROM user1 
ORDER BY id DESC
LIMIT 3;