MYSQL学习笔记（一）

查看全部库 --> show databases

创建库 --> create database {库名}

删除库 --> drop database {库名}

进入库 --> use {库名}

查看全部表 --> show tables

查看表结构 --> desc {表名}

创建表结构 --> create table {表名} ({字段名} {类型}, {字段名} {类型})

删除表 --> drop table {表名}

查询关键字

dual 伪表

示例;select 1/1 as a from dual

distinct 去重

示例;select distinct a.name from a

is not null 或 <> ;; 不为空

示例;select a.age from a where a.age is not null

或 select a.age from a where <> ;;

not between ... and 不在...范围

示例;select a.id,a.name,a.age from a where a.age not between 18 and 20

not in 不在指定取值内

示例;select a.id,a.name,a.age from a where a.age not in (18,19)

like 模糊查询 前like;%张;、后like;;张%、全like;%张%

示例;select a.id,a.name,a.age from a where a.name like ;张%

order by...asc / order by...desc 升序/降序

示例;select a.id,a.name,a.age from a order by a.age desc

group by 分组

示例;select a.id,a.name,a.age from a group by a.age

having 分组后的数据进行过滤

示例;SELECT gender, MAX(score) FROM student GROUP BY gender HAVING MAX(score) >= 60

limit {第n;1个元素开始};{条数} 或 limit {条数} offset {第n;1个元素开始} 分页

示例;select a.id,a.name,a.age from a limit 0,10

或 select a.id,a.name,a.age from a limit 10 offset 0

inner join on 内连接

示例;select a.id,b.name,b.age from a inner join b on a.id = b.a_id

left join on 左外连接

示例;select a.id,b.name,b.age from a left join b on a.id = b.a_id

right join on 右外连接

示例;select a.id,b.name,b.age from a right join b on a.id = b.a_id

union 联合查询;去重;但影响效率;

示例;select a.id,b.name,b.age from a left join b on a.id = b.a_id

union

select a.id,b.name,b.age from a right join b on a.id = b.a_id

union all 联合查询;不去重;比union效率快;

示例;select a.id,b.name,b.age from a left join b on a.id = b.a_id

union all

select a.id,b.name,b.age from a right join b on a.id = b.a_id where b.age is null

数值、字符串、时间函数

round 四舍五入

示例;select round(10/3,2) from dual

truncate 保留数值位数

示例;select truncate(10/3,2) from dual

least / greatest 最小/最大

示例;select least(;b;,;c;,;a;),greatest(;b;,;c;,;a;) from dual

length 字段值长度

示例;select length(;zhangsan;) from dual

concat 拼接字符串

示例;select concat(;a;,;b;) from dual

concat_ws 下划线拼接字符串

示例;select concat_ws(;_;,;a;,;b;) from dual

replace 替换字符

示例;select REPLACE(;zhangsan;,;san;,;ca;) from dual

upper 或 ucase 字符串转大写

示例;select UPPER(;zhangsan;),UCASE(;zhangsan;) from dual

lower 或 lcase 字符串转小写

示例;select LOWER(;ZHANGSAN;),LCASE(;ZHANGSAN;) from dual

curdate / curtime / now 当前年月日/当前时分秒/当前年月日时分秒

示例;select CURDATE(), CURTIME(), NOW() from dual

UNIX_TIMESTAMP / FROM_UNIXTIME 日期时间戳相互转换

示例;select UNIX_TIMESTAMP(;2022-09-04 18:00:00;), FROM_UNIXTIME(1662285600) from dual

流程控制函数

IF 表达式为true返回value1;false返回value2

示例;SELECT name, IF(age is NOT NULL, age, ;未知;) age FROM student

IFNULL 字段值不为null返回本身value;为null替换字段内容

示例;SELECT name, IFNULL(age, ;未知;) age FROM student

CASE WHEN...THEN WHEN...THEN 相当于if elseif

示例;

SELECT name, score, CASE WHEN score >= 80 THEN ;优秀; WHEN score >= 60 THEN ;及格; ELSE ;不及格; END reviews FROM student

CASE {字段} WHEN...THEN WHEN...THEN 相当于switch case

示例;

SELECT name, score, CASE score WHEN 81 THEN ;优秀; WHEN 65 THEN ;及格; ELSE ;未知; END reviews FROM student

加密函数

MD5 / SHA 密码加密;SHA比MD5更安全

示例;SELECT MD5(;admin;), SHA(;admin;) FROM DUAL

SQL的执行流程

from --> on --> (left/right/inner join) --> where --> group by --> having

--> select --> distinct --> order by --> limit

多行比较操作符

ANY 与子查询任意一个的返回值比较

示例;SELECT id,time_out FROM fapi_api_basic

WHERE time_out >= ANY (SELECT time_out FROM fapi_api_basic WHERE time_out > 10000)

ALL 与子查询所有的返回值比较

示例;SELECT id,time_out FROM fapi_api_basic

WHERE time_out <= ALL (SELECT time_out FROM fapi_api_basic WHERE time_out IS NOT NULL GROUP BY time_out)

EXISTS 根据c表的id;在a表中查得到数据返回c表数据;查不到不返回c表数据

示例;SELECT * FROM fapi_api_catalog c

WHERE EXISTS (SELECT * FROM fapi_api_basic a WHERE c.id = a.api_catalog_id)

NOT EXISTS 根据c表的id;在a表中查不到数据返回c表数据;查得到不返回c表数据

示例;SELECT * FROM fapi_api_catalog c

WHERE NOT EXISTS (SELECT * FROM fapi_api_basic a WHERE c.id = a.api_catalog_id)

DROP,TRUNCATE,DELETE区别

DROP表结构、表数据全部删除;TRUNCATE表数据全部删除;DELETE可以对表数据过滤删除

TRUNCATE一旦执行;数据不可rollback;TRUNCATE内置autocommit = true;不可修改

DELETE可以实现rollback;执行delete前将autocommit = false就可以实现rollback;默认情况下;DELETE也是autocommit = true

数据类型

类型字节规范说明tinyint(m)1-128 ~ 127m整数位数;布尔值smallint(m)2-32768 ~ 32767mediumint(m)3-8388608 ~ 8388607int、integer(m)4-2147483648 ~ 2147483647bigint(m)8-9223372036854775808~9223372036854775807float(m,d)4单精度浮点型; 8位精度d小数位数double(m,d)8双精度浮点型;16位精度小数四舍五入;精度丢失decimal(m,d)9m<65;d<30且 d<m 是小数位小数定长取值;不会出现精度问题bit(M)11 <= M <= 64二进制值year11901~2155年time3-838:59:59 ~ 838:59:59时间date31000-01-01 ~ 9999-12-03日期datetime81000-01-01 00:00:00 ~ 9999-12-31 23:59:59日期时间;查看较于直观timestamp41970-01-01 00:00:00 ~ 2038-01-19 03:14:07 UTC时间戳;便于计算char(M)M0 <= M <= 255固定长度varchar(M)M;10 <= M <= 65535需要计算长度;效率不如chartinytextL;20 <= L <= 255textL;20 <= L <= 65535mediumtextL;30 <= L <= 16777215longtextL;40 <= L <= 4294967295enum1;21 <= L <= 65535set1;2;3;4;80 <= L <= 64

ALTER 增加、删除约束

增加字段并添加约束;ALTER TABLE {表名} ADD {字段} {类型} {约束}

修改字段类型并添加约束;ALTER TABLE {表名} MODIFY {字段} {类型} {约束}

删除字段;ALTER TABLE {表名} DROP {字段}

删除索引;ALTER TABLE {表名} DROP INDEX {索引名}

NOT NULL 非空约束

示例;create table t (id int NOT NULL, name vachar(20))

UNIQUE 唯一约束 ;null值可以是多个;

示例;create table t (id int UNIQUE, name vachar(20))

CONSTRAINT(约束命名) 表级约束 ;可以实现复合唯一约束;

示例;create table t (id int, name vachar(20), CONSTRAINT t_id UNIQUE(id))

复合唯一约束示例;create table t (id int, name vachar(20), phone varchar(20)

CONSTRAINT t_id_phone UNIQUE(id, phone))

PRIMARY KEY 主键约束 ;相当于非空和唯一约束;一个表只能有一个主键;

示例;create table t (id int PRIMARY KEY, name vachar(20))

PRIMARY KEY AUTO_INCREMENT 主键自增

示例;create table t (id int PRIMARY KEY AUTO_INCREMENT, name vachar(20))

FOREIGN KEY ... REFERENCES 外键约束 ;从表的字段值必须在主表中对应的上;

主表;create table a (id int PRIMARY KEY AUTO_INCREMENT, name varchar(20))

从表;create table b (id int PRIMARY KEY AUTO_INCREMENT, class varchar(20), a_id int

CONSTRAINT b_a_id FOREIGN KEY (a_id) REFERENCES a(id))

CHECK 校验约束

create table t (id int, name varchar(20), age int check(age > 18))

DEFAULT 默认值约束

create table t (id int, name varchar(20) DEFAULT ;未知姓名;)