MySQL

参考:

命令

连接与配置

mysql [-h localhost] [-P 3306] -u root -p//登录mysql

set names utf8//设置编码

exit//退出mysql

库操作

SHOW - 查询

SHOW DATABASES//显示所有数据库
SHOW DATABASE()//显示当前数据库
USE RUNOOB//切换数据库

CREATE - 创建

1
2
3

CREATE DATABASE [IF NOT EXISTS] 数据库名 
[DEFAULT CHARACTER SET 字符集名(如utf8mb4)]  -- 指定数据库默认字符集
[DEFAULT COLLATE 排序规则(如utf8mb4_general_ci)]  -- 指定数据库默认排序规则

CREATE DATABASE IF NOT EXISTS mydatabase//创建新数据库
CREATE DATABASE mydatabase//创建新数据库
CREATE DATABASE mydatabase DEFAULT CHARACTER SET utf8mb4//创建新数据库,指定默认字符集为utf8mb4
CREATE DATABASE mydatabase DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci//创建新数据库,指定默认字符集为utf8mb4,默认排序规则为utf8mb4_general_ci

DROP - 删除

1

DROP DATABASE [IF EXISTS] 数据库名 //删除数据库

DROP DATABASE mydatabase//删除数据库
DROP DATABASE IF EXISTS mydatabase//删除数据库

ALTER - 修改

1

ALTER DATABASE 数据库名 [DEFAULT CHARACTER SET 字符集名] [DEFAULT COLLATE 排序规则]`//修改数据库编码

ALTER DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci//修改数据库编码(其实加不加DEFAULT都可以)
ALTER DATABASE mydatabase DEFAULT CHARACTER SET utf8mb4//修改数据库默认编码
ALTER DATABASE mydatabase DEFAULT COLLATE utf8mb4_general_ci//修改数据库默认排序规则

表操作

SHOW - 查询

SHOW TABLES//显示所有表
SHOW TABLE STATUS//显示所有表的状态

DESC 表名//显示表结构

SHOW CREATE TABLE 表名//显示创建表的语句

CREATE - 创建

1
2
3
4
5
6
7

CREATE TABLE IF NOT EXISTS 表名 (
    列名 数据类型 [COMMENT '列注释'],
    列名 数据类型 [COMMENT '列注释'],
    列名 数据类型 [COMMENT '列注释'],
    ...
    列名 数据类型 [COMMENT '列注释']
)[COMMENT '表注释']

数据类型：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

CREATE TABLE IF NOT EXISTS users (
    id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    -- 用户ID字段，无符号整数，自动递增，主键，注释为"用户ID"
    
    username VARCHAR(50) NOT NULL COMMENT '用户名',
    -- 用户名字段，可变长度字符串(最大50字符)，不能为空，注释为"用户名"
    
    password VARCHAR(255) NOT NULL COMMENT '密码',
    -- 密码字段，可变长度字符串(最大255字符)，不能为空，注释为"密码"
    
    email VARCHAR(100) UNIQUE COMMENT '邮箱',
    -- 邮箱字段，可变长度字符串(最大100字符)，唯一约束，注释为"邮箱"
    
    age TINYINT UNSIGNED COMMENT '年龄',
    -- 年龄字段，无符号小整数，注释为"年龄"
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    -- 创建时间字段，时间戳类型，默认值为当前时间，注释为"创建时间"
    
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
    -- 更新时间字段，时间戳类型，默认值为当前时间，当记录更新时自动更新为当前时间，注释为"更新时间"
    
) COMMENT '用户信息表';
-- 整个表的注释为"用户信息表"

ALTER - 修改

1
2
3
4
5

ALTER TABLE 表名 ADD 列名 数据类型 [COMMENT '列注释'] [约束]
ALTER TABLE 表名 MODIFY 列名 数据类型 [COMMENT '列注释'] [约束]
ALTER TABLE 表名 CHANGE 列名 新列名 数据类型 [COMMENT '列注释'] [约束]
ALTER TABLE 表名 DROP 列名
ALTER TABLE 表名 RENAME TO 新表名

ALTER TABLE Websites CHANGE name site_name VARCHAR(255)//修改表名
ALTER TABLE Websites CHANGE url site_url VARCHAR(255)//修改表字段名
ALTER TABLE Websites CHANGE age site_age TINYINT UNSIGNED COMMENT '年龄'//修改表字段名,数据类型,注释
ALTER TABLE student MODIFY id INT UNSIGNED AUTO_INCREMENT//修改表字段名,数据类型,自增
ALTER TABLE student COMMENT '学生信息表'//修改表注释

DROP - 删除

1
2

DROP TABLE [IF EXISTS] 表名
TRUNCATE TABLE 表名 --清空表但保留表结构

DROP TABLE IF EXISTS Websites//删除表

数据操作

INSERT - 添加

1
2
3
4

INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...) --指定字段添加数据
INSERT INTO 表名 VALUES (值1, 值2, ...) --全部字段添加数据
INSERT INTO 表名 (列1, 列2, ...) VALUES (值11, 值12, ...), (值21, 值22, ...), (值31, 值32, ...) --指定字段批量添加数据
INSERT INTO 表名 VALUES (值11, 值12, ...), (值21, 值22, ...), (值31, 值32, ...) --全部字段批量添加数据

INSERT INTO Websites (name, url, alexa, country) VALUES ('Google', 'https://www.google.com', 1, 'USA')//向Websites表中插入数据
INSERT INTO Websites (name, url, alexa, country) VALUES ('RUNOOB', 'https://www.runoob.com', 13, 'CN')//向Websites表中插入数据

UPDATE - 修改

1

UPDATE 表名 SET 列1 = 值1, 列2 = 值2, ... [WHERE 条件]

UPDATE Websites SET name='Google' WHERE id=1//将id为1的数据的name更新为Google
UPDATE Websites SET name='Google', url='https://www.google.com' WHERE id=1//将id为1的数据的name和url更新为Google和https://www.google.com

DELETE - 删除

1

DELETE FROM 表名 WHERE 条件

DELETE FROM Websites WHERE id=1//删除id为1的数据
DELETE FROM Websites WHERE id=2//删除id为2的数据

查询操作

1
2
3
4

SELECT 列名1, 列名2, ... FROM 表名 WHERE 条件 GROUP BY 列名1, 列名2 HAVING 分组后过滤条件 ORDER BY 列名1 [ASC|DESC], 列名2 [ASC|DESC] LIMIT 数量
SELECT 列名1[as 别名1], 列名2[as 别名2], ... FROM 表名 --取别名，as可以省略
SELECT DISTINCT 列名1, 列名2, ... FROM 表名 --去重
SELECT 列名 FROM 表名 LIMIT 起始索引, 每页记录数

起始索引从0开始，且等于(页码-1)*每页记录数，查询记录数为-1时，表示查询所有数据。

• 默认排序方式为升序，即ASC。如果需要降序排列，则需要使用DESC关键字。

WHERE 和 HAVING的区别:

• WHERE 是在查询数据之前对数据进行过滤，而 HAVING 是在查询数据之后对数据进行过滤。
• WHERE 不能用于聚合函数，而 HAVING 可以用于聚合函数。

聚合函数:

• 聚合函数可以对一列进行计算，并返回计算结果。
• 聚合函数通常与 SELECT 语句一起使用，用于对查询结果进行统计分析。

SELECT COUNT(*) FROM Websites//查询Websites表的行数
SELECT SUM(age) FROM Websites//查询Websites表的age列的总和
SELECT AVG(age) FROM Websites//查询Websites表的age列的平均值
SELECT MAX(age) FROM Websites//查询Websites表的age列的最大值
SELECT MIN(age) FROM Websites//查询Websites表的age列的最小值

SELECT * FROM Websites//查询所有数据
SELECT * FROM Websites WHERE id=1//查询id为1的数据

SELECT * FROM Websites WHERE id=1 AND name='Google'//查询id为1且name为Google的数据
SELECT * FROM Websites WHERE id=1 OR name='Google'//查询id为1或name为Google的数据

SELECT * FROM Websites WHERE id=1,2,3//查询id为1,2,3的数据
SELECT * FROM Websites WHERE id IN (1,2,3)//查询id为1,2,3的数据
SELECT * FROM Websites WHERE id NOT IN (1,2,3)//查询id不为1,2,3的数据
SELECT * FROM Websites WHERE id BETWEEN 1 AND 10//查询id在1到10之间的数据

SELECT * FROM Websites WHERE id LIKE '%Google%'//查询name中包含Google的数据
SELECT * FROM Websites WHERE id LIKE 'Google%'//查询name以Google开头的数据
SELECT * FROM Websites WHERE id LIKE '%Google'//查询name以Google结尾的数据

SELECT * FROM Websites WHERE id=1 ORDER BY name//查询id为1的数据并按name排序
SELECT * FROM Websites WHERE id=1 ORDER BY name DESC//查询id为1的数据并按name倒序排序
SELECT row1,row2 FROM Websites WHERE id=1 ORDER BY name DESC//查询id为1的数据并按name倒序排序,只返回row1,row2列

SELECT * FROM Websites WHERE id=1 GROUP BY name//查询id为1的数据并按name分组
SELECT * FROM Websites WHERE id=1 HAVING COUNT(*) > 1//查询id为1的数据并按name分组后筛选出数量大于1的数据

SELECT * FROM Websites WHERE id=1 LIMIT 10//查询id为1的数据并限制返回10条数据
SELECT * FROM Websites WHERE id=1 LIMIT 10 OFFSET 20//查询id为1的数据并限制返回10条数据,从第20条开始

SELECT * FROM Websites WHERE id=1 UNION SELECT * FROM Websites WHERE id=2//查询id为1或2的数据并去重
SELECT * FROM Websites WHERE id=1 UNION ALL SELECT * FROM Websites WHERE id=2//查询id为1或2的数据并保留重复数据

用户操作

USE mysql//切换到mysql数据库
SELECT * FROM user
SELECT Host, User FROM mysql.user//查询用户

CREATE USER '用户名newuser'@'主机名localhost' IDENTIFIED BY '密码password'//创建用户
CREATE USER '用户名newuser'@ '%' IDENTIFIED BY '密码password'//创建用户,主机名%表示任意主机
ALTER USER '用户名newuser'@'主机名localhost' IDENTIFIED [WITH mysql_native_password] BY '新密码newpassword'//修改用户密码
DROP USER '用户名newuser'@'主机名localhost'//删除用户

SHOW GRANTS FOR '用户名newuser'@'主机名localhost'//显示用户权限
GRANT 权限 ON 数据库名.表名 TO '用户名newuser'@'主机名localhost'//授予用户权限
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost' WITH GRANT OPTION//授予用户所有权限
REVOKE 权限 ON 数据库名.表名 FROM '用户名newuser'@'主机名localhost'//撤销用户权限
FLUSH PRIVILEGES;//刷新权限

函数

字符串函数

SELECT CONCAT('Hello','World')//连接字符串
UPDATE emp SET workno = LPAD(workno,4,'0')//更新emp表workno字段,不足4位的前面补0

数值函数

SELECT ROUND(1.5)//四舍五入
SELECT ROUND(1.5,1)//保留1位小数
SELECT TRUNCATE(1.5,1)//保留1位小数,不进行四舍五入
SELECT RAND()//随机数(0-1)
SELECT MOD(10,3)//求余(10%3)
SELECT FLOOR(1.5)//向下取整
SELECT CEIL(1.5)//向上取整

日期函数

SELECT NOW()//当前日期时间
SELECT CURDATE()//当前日期
SELECT CURTIME()//当前时间
SELECT YEAR(NOW())//当前年份
SELECT DATE_ADD(NOW(),INTERVAL 70 DAY)//当前日期加1年
SELECT DATE_SUB(NOW(),INTERVAL 1 YEAR)//当前日期减1年
SELECT DATEDIFF('2020-1-1','2021-1-1')//日期相减(天数)
SELECT TIMEDIFF('10:00:00','9:00:00')//时间相减(天数)
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')//格式化日期时间
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d')//格式化日期
SELECT name,DATEDIFF(NOW(),birth) FROM people WHERE datediff(NOW(),birth)>60//查询年龄大于60岁的人

流程函数

SELECT IF(FALSE,'OK','ERROR')//返回ERROR
SELECT IFNULL('OK','ERROR')//返回OK
SELECT IFNULL(NULL,'ERROR')//返回ERROR
SELECT CASE 1 WHEN 1 THEN 'OK' ELSE 'ERROR' END//返回OK
SELECT CASE 2 WHEN 1 THEN 'OK' ELSE 'ERROR' END//返回ERROR

1
2
3
4

SELECT 
name,
(CASE workaddress when "北京" THEN "1" WHEN "上海" THEN "2" ELSE "3" END) AS "工作地址"
FROM people

1
2
3
4
5

SELECT 
id,
name,
(CASE WHEN math > 90 THEN "优秀" WHEN math > 60 THEN "及格" ELSE "不及格" END) AS "数学等级"
FROM score

约束

其他

• CREATE INDEX - 创建索引（搜索键）

1
2

CREATE INDEX index_name
ON table_name (column_name)

CREATE INDEX idx_name ON Websites (name)//在Websites表的name字段创建索引
CREATE INDEX idx_url ON Websites (url)//在Websites表的url字段创建索引

• DROP INDEX - 删除索引

1
2

DROP INDEX index_name
ON table_name

DROP INDEX idx_name ON Websites//删除Websites表的name字段索引
DROP INDEX idx_url ON Websites//删除Websites表的url字段索引

• HAVING - 用于对分组后的结果集进行筛选

• JOIN - 用于将两个或多个表的记录结合起来

1
2
3
4

SELECT column_name(s)
FROM table_name1
JOIN table_name2
ON table_name1.column_name = table_name2.column_name

• LEFT JOIN - 左连接

1
2
3
4

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name = table_name2.column_name

• RIGHT JOIN - 右连接

1
2
3
4

SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name = table_name2.column_name

• FULL JOIN - 全连接

1
2
3
4

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name = table_name2.column_name

• UNION - 用于合并两个或多个 SELECT 语句的结果集

1
2
3

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2  

参考

https://www.runoob.com/sql/sql-tutorial.html

https://www.runoob.com/mysql/mysql-tutorial.html

未完待续…