数据库学习笔记

SQL

分类	说明
DDL	数据定义语言，用来定义数据库对象
DML	数据操作语言，用来对数据增删改
DQL	数据库查询语言，用来查询数据库数据
DCL	数据控制语言，用来创建数据库用户、控制数据库的访问权限

DDL

数据库操作

查询

查询所有数据库

1	SHOW DATABASES;

查询当前数据库

1	SELECT DATABASES();

创建

1	CREATE DATABASE[IF NOT EXISTS]数据库名[DEFAULT CHARSET 字符集][COLLAT 排序规则];

删除

1	DROP DATABASE[IF EXISTS]数据库名;

使用

USE 数据库名

表操作

查询

查询当前数据库所有表

1	SHOW TABLES;

查询表结构

DESC 表名;

查询指定表的建表语句

1	SHOW CREATE TABLE 表名;

创建

创建一个表结构

CREATE TABLE 表名(
	字段1 类型 [COMMENT 字段1注释],
	字段2 类型 [COMMENT 字段2注释]
)[COMMENT 表注释];

注意：[….] 是可选参数

修改

添加字段 —— ADD

1	ALTER TABLE 表名 ADD 字段名类型 [COMMENT 注释][约束];

修改数据类型 —— MODIFY

1	ALTER TABLE 表名 MODIFY 新数据类型;

修改字段名和字段类型 —— CHANGE

1	ALTER TABLE 表名 CHANGE 旧字段名新字段名类型 [COMMENT 注释][约束];

修改表名

1	ALTER TABLE 表名 RENAME TO 新表名;

删除

删除字段

1	ALTER TABLE 表名 DROP 字段名;

删除表

1	DROP TABLE [IF EXISTS] 表名;

删除指定表，并重新创建该表

1	TRUNCATE TABLE 表名;

数据类型

数值类型

类型	大小	有符号（SIGNED）范围	无符号（UNSIFNED）范围
TINYINT	1 byte	(-128,127)	(0,255)
SMALLINT	2 bytes	(-32768,32767)	(0,65535)
MEDIUMINT	3 bytes	(-8388608,8388607)	(0,16777215)
INT/INTEGER	4 bytes	(-2147483648,2147483648)	(0,4294967295)
BIGINT	8 bytes	(-2^63,2^63-1)	(0,2^64-1)
FLOAT	4 bytes	略	略
DOUBLE	8 bytes	略	略
DECIMAL		依赖于精度和标度	依赖于精度和标度

字符串类型

类型	大小（单位: bytes）	描述
CHAR	0-255	定长字符串
VARCHAR	0-65 535	变长字符串
TINYBLOB	0-255	短二进制数据
TINYTEXT	0-255	短文本字符串
BLOB	0-65 535	二进制形式的长文本数据
TEXT	0-65 535	长文本数据
MEDIUMBLOB	0-16 777 215	二进制形式的中等长文本数据
MEDIUMTEXT	0-16 777 215	中等长文本数据
LONGBLOB	0-4 294 967 295	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295	极大文本数据

时间类型

类型	大小	范围	格式	描述
DATE	3	1000-01-01 ~ 9999-12-31	YYYY-MM-DD	日期
TIME	3	-838:59:59 ~ 838:59:59	HH:MM:SS	时间
YEAR	1	1901 ~ 2155	YYY	年份
DATATIME	8	略	YYYY-MM-DD HH:MM:SS	混合
TIMESTAMP	4	略	YYYY-MM-DD HH:MM:SS	混合+时间戳

DML

数据操作语言

添加数据

给指定字段添加数据

1	INSERT INTO 表名 (字段名1,字段名2,...) VALUES(值1,值2...);

给全部字段添加数据

1	INSERT INTO 表名 VALUES (值1,值2,...);

批量添加数据

1	INSERT INTO 表名 (字段名1,字段名2,...) VALUES(值1,值2...),(值1,值2...),(值1,值2...),...;

1	INSERT INTO 表名 VALUES (值1,值2,...),(值1,值2,...),...;

注意：字符串和日期应该在引号中

修改数据

1	UPDATE 表名 SET 字段名1 = 值1,字段名2 = 值2,...[WHERE 条件];

删除数据

1	DELETE FROM 表名 [WHERE 条件];

DQL

查询数据

基本查询

查询字段

1	SELECT 字段1,字段2,字段3,...FROM 表名;

1	SELECT * FROM 表名;

设置别名 —— AS

1	SELECT 字段1 [AS 别名1],字段2 [AS 别名2],... FROM 表名;

去除重复记录 —— distinct

1	SELECT DISTINCT 字段列表 FROM 表名;

条件查询

语法

1	SELECT 字段列表 FROM 表名 WHERE 条件列表;

条件

比较运算符	功能
>,>=,<,<=,=,!=,<>	略
BETWEEN…AND…	在某个范围之内
IN(…)	在列表中的值，多选一
LIKE 占位符	模糊匹配(_匹配多个字符，%匹配任意个字符)
IS NULL	为空
AND \ &&	并且
OR \ \|\|	或者
NOT \ !	非、不是

聚合函数

将数据作为整体，进行纵向计算

函数	功能
count	计数
max	最大值
min	最小值
avg	平均值
sum	求和

1	SELECT 函数(字段) FROM 表名;

分组查询

语法

1	SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组过滤条件];

注意：where > 聚合函数 > having

排序查询

语法

1	SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;

排序方式

ASC 升序

DESC 降序

分页查询

语法

1	SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询数目;

顺序

代码顺序

1 2	select 字段 -> 聚合函数() -> FROM 表名 -> WHERE 条件 -> GROUP BY 分组字段 -> HAVING 分组后条件 -> ORDER BY 排序字段 -> LIMIT 分页

DCL

用来控制用户

查询用户

1 2	USE mysql; SELECT * FROM user;

创建用户

1	CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

修改用户密码

1	ALTER USER ''@'' IDENTIFIED WITH mysql_native_password BY '新密码';

删除用户

1	DROP USER '用户名'@'主机名';

权限

权限	说明
ALL、ALL PRIVILEGES	所有权限
SELECT	查询数据
INSRET	插入数据
UPDATE	修改数据
DELETE	删除数据
ALTER	修改表
DROP	删除数据库/表/视图
CREATE	创建数据库/表

查询权限

1	SHOW GRANTS FOR '用户名'@'主机名';

授予权限

1	GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'表名';

撤销权限

1	REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'表名';

函数

函数是内置的可被调用的功能

字符串函数

函数	功能
CONCAT(S1,S2,…Sn)	字符串拼接
LOWER(str)	转大写
UPPER(str)	转小写
SPAD(str,n,pad)	左填充，用pad对str进行填充，达到n的长度
RPAD(str,n,pad)	右填充，用pad对str进行填充，达到n的长度
TRIM(str)	去掉头尾空格
SUBSTRING(str,start,len)	返回一个从start开始长度为len的空格

主要是用来修改数据

数值函数

函数	功能
CEIL(x)	向上取整
FLOOR(x)	向下取整
MOD(x,y)	返回x/y的模
RAND()	返回 0~1 内的随机数
ROUND(x,y)	求参数 x四舍五入的值，保留y位小数

日期函数

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
YEAR(date)	获取date年份
MONTH(date)	获取date月份
DAY(date)	获取date日期
DATE_ADD(date,INTERVAL expr,type)	返回上一个日期加上时间间隔后的时间值
DATEDIFF(date1,date2)	返回两个日期之间的天数

database