MySQL学习笔记

一、数据库的操作

SHOW DATABASE;-- 显示所有数据库
CREATE DATABASE;-- 创建数据库
DROP DATABASE test1;-- 删除数据库

二、数据库常用数据类型 

整数型，浮点型，定点型，字符串类型，时间型

三、表的基本操作

use demo1-- 选中数据库demo1

-- 添加students表
CREATE TABLE students (
	id INT,
	name VARCHAR(20),
	gender CHAR,
	birthday DATE
)-- （字段的名称与数据类型）

DESC students;-- 查看表长

ALTER table students MODIFY COLUMN name VARCHAR(30);-- 修改数据库students中name的数据类型NAMES

ALTER table students RENAME COLUMN NAMES TO name;-- 修改数据库students中name的字段名称

ALTER table students ADD COLUMN last_login DATETIME;-- 在数据库students中添加新的字段last_login

ALTER table students DROP COLUMN last_login;-- 在数据库students中删除字段last_login

DROP TABLE students;-- 删除表students

四、数据的增删改查

INSERT INTO students (id,name,gender,birthday) VALUES(1,'小刘','男','2003-10-08');-- 插入指定数据
INSERT INTO students VALUES(1,'小刘','男','2003-10-08');-- 默认插入所有数据
INSERT INTO students (id,name,gender,birthday) VALUES(3,'小李','男','2004-5-18'),(4,'小陈','女','2004-6-1'),(5,'小王','女','2004-12-15');-- 插入多条数据

UPDATE students set id = 2 WHERE name = '小刘';-- 修改表的数据：UPDATE 表名 set 要修改的列名和值 WHERE 要修改的数据（去掉where语句表示修改所有数据）

DELETE FROM students WHERE id = 2;-- 删除数据：DELETE FROM 表名 WHERE 要删除的数据

SELECT * FROM students;-- 查看表：SELECT 列名 FROM 表名（*代表所有列）

五、数据查询常用语句

-- 常用语句
SELECT * FROM students;-- 查看表：SELECT 列名 FROM 表名（*代表所有列）

-- WHERE子句
SELECT * FROM students WHERE id>3 AND id<=5;-- 查看表中id>3并且id<=5的数据（NOT > AND > OR）

-- IN指定多个值
SELECT * FROM students WHERE id in (3,5);-- 查看表中id为3,5的数据

-- BETWEEN _ and _
SELECT * FROM students WHERE id NOT BETWEEN 1 and 10;-- 查看表中id不在1到10之间的数据

-- LIKE模糊查询
SELECT * FROM students WHERE name LIKE '小%';-- 查看表中name里第一个字为小的数据
SELECT * FROM students WHERE name LIKE '%小%';-- 查看表中name里包含小的数据
SELECT * FROM students WHERE name LIKE '小_';-- 查看表中name里第一个字为小的两个字的数据

-- REGEXP正则表达式
SELECT * FROM students WHERE name REGEXP '^小.$';-- 查看表中name里第一个字为小的两个字的数据与 LIKE '小_' 效果一样
SELECT * FROM students WHERE name REGEXP '[陈刘]';-- 查看表中name里含陈和刘的数据或者：REGEXP '陈|刘'

-- NULL空值
SELECT * FROM students WHERE name is null;-- 查看表中name为空的数据
SELECT * FROM students WHERE name = ' ';-- 查看表中name为空字符串的数据

-- ORDER BY排序
SELECT * FROM students ORDER BY id;-- 查看表中name的数据，并按id升序排列
SELECT * FROM students ORDER BY id DESC;-- 查看表中name的数据，并按id降序排列
SELECT * FROM students ORDER BY id DESC,birthday ASC;-- 查看表中name的数据，按id降序排列并按birthday升序排列（除了使用列名id，也可使用列的序号）
SELECT COUNT(*) FROM students;-- 查看总人数
SELECT AVG(id) FROM students;-- 查看id平均值

-- GROUP BY分组
SELECT gender,COUNT(*) FROM students GROUP BY gender;-- 按照性别分组并且计算和值
SELECT gender,COUNT(*) FROM students GROUP BY gender HAVING COUNT(gender)>2 ORDER BY COUNT(gender) DESC;-- 按性别分组，计算和值并升序排列和值大于2的数据
SELECT SUBSTR(name,1,1),COUNT(SUBSTR(name,1,1)) FROM students

-- LIMIT限制
GROUP BY SUBSTR(name,1,1)
HAVING COUNT(SUBSTR(name,1,1))>2
ORDER BY COUNT(SUBSTR(name,1,1)) DESC
LIMIT 3;-- 按姓分组，计算和值并升序排列和值大于2的前三个数据
GROUP BY SUBSTR(name,1,1)
HAVING COUNT(SUBSTR(name,1,1))>2
ORDER BY COUNT(SUBSTR(name,1,1)) DESC
LIMIT 3，3;-- 按姓分组，计算和值并升序排列和值大于2的从第四个开始往后三个数据

-- DISTINCT去重
SELECT DISTINCT gender FROM students;-- 查询所有性别

-- DISTINCT合并结果（并集）
SELECT * FROM students WHERE name is null
UNION
SELECT * FROM students WHERE name = ' ';-- 查看表中name为空或者name为空字符串的数据（UNION会去重，加上ALL会全部显示）

-- INTERSECT合并结果（交集）
SELECT * FROM students WHERE name is null
INTERSECT
SELECT * FROM students WHERE name = ' ';-- 查看表中name为空并且name为空字符串的数据

-- EXCEPT合并结果（差集）
SELECT * FROM students WHERE name is null
EXCEPT
SELECT * FROM students WHERE gender = ' ';-- 查看表中name为空但是gender不为空字符串的数据

-- 子查询（使用一个查询的结果作为另一个查询的条件）
SELECT AVG(id) FROM students;
SELECT * FROM students WHERE id>(SELECT AVG(id) FROM students);-- 查找id大于平均id的数据
SELECT id,ROUND((SELECT AVG(id) FROM students)) as average,
id - ROUND((SELECT AVG(id) FROM students)) as diff
FROM students;-- 显示id与平均id的差值
CREATE TABLE new_students SELECT * FROM students WHERE id > 2;-- 新建一个表，将id大于2的数据插入
INSERT INTO new_students SELECT * FROM students WHERE id < 2;-- 将id小于2的数据插入
SELECT EXISTS(SELECT * FROM students WHERE id > 2)-- 是否存在id大于2的

-- 表关联（用来查询多个表中的数据）
SELECT * FROM player
INNER JOIN equip
ON player.id = equip.player_id;-- 用id将两个表中信息关联，显示玩家与装备匹配的结果
SELECT * FROM player，equip
WHERE player.id = equip.player_id;-- 与上面结果一致
SELECT * FROM player
RIGHT JOIN equip
ON player.id = equip.player_id;-- 查询右表中所有数据以及左表中匹配的数据，没匹配的用NULL填充
SELECT * FROM player
LEFT JOIN equip
ON player.id = equip.player_id;-- 查询左表中所有数据以及右表中匹配的数据，没匹配的用NULL填充

REGEXP正则表达式中常用通配符：

常用的聚合函数数：