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条件：会筛选出符合条件的记录。

比较运算符：> >= < <= != <> <=>
<=> 和 = 的区别：<=>可以检测NULL值。IS [NOT] NULL ：检测值是否为NULL或者NOT NULL。指定范围：[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 分组：

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

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

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

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

配合聚合函数使用

（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;

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

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

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;