文章目录
SQL
create
# CREATE DATABASE 语句 创建数据库
CREATE DATABASE my_db;
# CREATE TABLE 语句 创建数据库表
CREATE TABLE my_table (
id int,
name varchar(255),
age int,
city varchar(255)
)
# SQL 约束(Constraints) 约束用于规定表中的数据规则
# 在 SQL 中,我们有如下约束:
# NOT NULL - 指示某列不能存储 NULL 值。
# UNIQUE - 保证某列的每行必须有唯一的值 优力克
# PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列(或两个列多个列的结合)有唯一标识,有助于更容易更快速地找到表中的一个特定的记录。
# FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
# CHECK - 保证列中的值符合指定的条件
# DEFAULT - 规定没有给列赋值时的默认值。
CREATE TABLE my_table (
id int,
name varchar(255) NOT NULL,
age int,
city varchar(255)
)
#已创建表 后加约束 not null
alter table my_table modify column age int not null
# 撤销not null 约束
alter table my_table modify column age int default null
# 加 UNIQUE 约束 是一种索引
alter table my_table add UNIQUE (city)
# 撤销unique索引
alter table my_table DROP INDEX city
# 加primary key 约束索引
alter table my_table ADD PRIMARY KEY (id)
# 撤销primary key 约束
alter table my_table DROP PRIMARY KEY
# 设置default
alter table my_table alter city SET DEFAULT '北京'
#撤销default 值
alter table my_table alter city DROP DEFAULT
# CREATE INDEX 语句 创建索引 在不读取整个表的情况下,索引使数据库应用程序可以更快地查找数据
create UNIQUE INDEX city_index on my_table (city)
# 撤销删除索引
alter table my_table DROP INDEX city_index
# DROP
# 删除表
DROP TABLE my_table
# 删除库
DROP DATABASE my_db
# 只删除表中数据,不删除表
TRUNCATE TABLE new_student
# AUTO INCREMENT 字段 自动递增
CREATE TABLE table1 (
id int not null AUTO_INCREMENT,
PRIMARY KEY(id)
)
# 或者
ALTER TABLE table1 MODIFY COLUMN `id` int NOT NULL AUTO_INCREMENT
select
# 去重 DISTINCT
select DISTINCT age from student
# and 和 or
select * from student where age = 20
select * from student where name = '张三' and age = 20
select * from student where name = '张三' or age = 22
# order by 排序 默认升序 desc 降序
select * from student ORDER BY age DESC
# order by 多列排序,先根据前面字段age排序 升序, 如果存在重复 在根据grade 降序
select * from student order by age,grade desc
# LIMIT 分页
select * from student LIMIT 0,2
select * from student LIMIT 3,5
#LIKE 模糊查询 以及使用 NOT 关键字 "%" 符号用于在模式的前后定义通配符(默认字母)
select * from student where age like '%2%'
select * from student where age not like '2%'
# 通配符 % : 替代 0 个或多个字符
# _ : 替代一个字符
# [charlist]: 字符列中的任何单一字符
select * from student where name like '赵_'
# 使用 [charlist] 通配符,MySQL 中使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式。
select * from student where name REGEXP '[赵]' # 查询名称包含'赵' 的所有student
SELECT * from student where name NOT RLIKE '[三]' # 查询名称中不包含三的所有student
select * from student where name RLIKE '^[张]' # 查询名称以张开头的所有信息
select * from student where name RLIKE '^[^张]' # 查询名称不以张开头的所有信息
# IN 操作符
select * from student where name in('张大胆','张三')
# between and 操作符
select * from student where age BETWEEN 20 AND 25 # 查询年龄在 20(包含) - 25(包含)之间的学生信息
# as 别名
select name as studentname from student
# INNER JOIN 内连接是最常见的一种连接,只连接匹配的行
select * from student s INNER JOIN school l on s.school_id = l.id
# LEFT JOIN 返回左表的全部行和右表满足ON条件的行,如果左表的行在右表中没有匹配,那么这一行右表中对应数据用NULL代替
select * from student s LEFT JOIN school l on s.school_id = l.id
# RIGHT JOIN 返回右表的全部行和左表满足ON条件的行,如果右表的行在左表中没有匹配,那么这一行左表中对应数据用NULL代替
select * from school l RIGHT JOIN student s on s.school_id = l.id
# FULL OUTER JOIN 会从左表 和右表 那里返回所有的行。如果其中一个表的数据行在另一个表中没有匹配的行,那么对面的数据用NULL代替 mysql不支持全外连接 oracle支持
# select s.id,s.name,l.school from student s FULL JOIN school l on s.school_id = l.id
# mysql可以使用 union 来实现全连接
# UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
# UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每个 SELECT 语句中的列的顺序必须相同
# 默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL
select * from student s LEFT JOIN school l on s.school_id = l.id UNION select * from student s RIGHT JOIN school l on s.school_id = l.id
# SELECT INTO 语句 从一个表复制数据,然后把数据插入到另一个新表中 mysql不支持
# select id INTO student1 from student
# 替代方式
CREATE table new_student (select * from student)
# INSERT INTO SELECT 语句 从一个表复制数据,然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响
INSERT INTO new_student SELECT * from student # 插入所有数据
INSERT INTO new_student(name,age) SELECT name,age from student # 插入指定字段数据
insert into
# 一,插入所有
INSERT INTO table_name
VALUES (value1,value2,value3,...);
#
# 二、插入指定字段
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
update
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
delete
DELETE FROM table_name
WHERE some_column=some_value;
函数
# avg() 返回平均值
select AVG(age) from student
# count() 函数返回匹配指定条件的行数
select count(*) from student # COUNT(*) 函数返回表中的记录数
select count(age) from student # COUNT(column_name) 函数返回指定列的值的数目(NULL 不计入)
# FIRST() 函数返回指定的列中第一个记录的值 只有 MS Access 支持 FIRST() 函数
select FIRST(age) FROM student
# MAX() 函数返回指定列的最大值。
select max(age) from student
# MIN() 函数返回指定列的最小值
select min(age) from student
# SUM() 函数返回数值列的总数
select sum(age) from student
# GROUP BY 语句用于结合聚合函数,根据一个或多个列对结果集进行分组。 一般配合聚合函数使用
select count(*),age from student GROUP BY age # 查询相同age 出现的次数
select max(age),school_id from student GROUP BY school_id #查询学校对应的最大学生年龄
# HAVING 子句 在 SQL 中增加 HAVING 子句原因是,WHERE 关键字无法与聚合函数一起使用
# HAVING 子句可以让我们筛选分组后的各组数据 ----配合group by使用
select count(*) num,age from student GROUP BY age HAVING num>1
# EXISTS 运算符用于判断查询子句是否有记录,如果有一条或多条记录存在返回 True,否则返回 False
select * from student where EXISTS (select * from student where age > 120)
# UCASE() 函数把字段的值转换为大写 只针对英文
SELECT UCASE(name) FROM student;
# LCASE() 函数把字段的值转换为小写
SELECT LCASE(column_name) FROM table_name;
# MID() 函数用于从文本字段中提取字符
SELECT MID(name,1,2) FROM student;
# LEN() 函数返回文本字段中值的长度 字节长度 utf-8字符集中 中文占3个字节
SELECT LENGTH(name) FROM student;
# ROUND() 函数用于把数值字段舍入为指定的小数位数
SELECT ROUND(,decimals) FROM TABLE_NAME;
select ROUND(1.5656,2) # 指定返回值保留小数位
# NOW() 函数返回当前系统的日期和时间
select now()
# FORMAT() 函数用于对字段的显示进行格式化
SELECT FORMAT(42.65534453,2); # 保留两位,四舍五入
# DATE_FORMAT() 格式化时间
SELECT id, DATE_FORMAT(create_time,'%Y-%m-%d') create_time FROM student; # 2021-10-25
mysql数据库时间与东八区相差8小时 修改mysql服务器时间 (建议自己new DATE)
show variables like '%time_zone%'; //查询当前时区
set global time_zone='+8:00'; //在标准时区上加+8小时,即东8区时间
flush privileges; # 立即生效
//改回
set global time_zone=SYSTEM; #timezone改回system
设置数据库时间字段随系统时间更新和创建
ALTER TABLE `mytest`.`sys_upgrade`
MODIFY COLUMN `create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP,
MODIFY COLUMN `update_time` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
字段类型选择
数据类型 | 描述 |
---|---|
CHARACTER(n) | 字符/字符串。固定长度 n。 |
VARCHAR(n) 或 CHARACTER VARYING(n) | 字符/字符串。可变长度。最大长度 n。 |
BINARY(n) | 二进制串。固定长度 n。 |
BOOLEAN | 存储 TRUE 或 FALSE 值 |
VARBINARY(n) 或 BINARY VARYING(n) | 二进制串。可变长度。最大长度 n。 |
INTEGER§ | 整数值(没有小数点)。精度 p。 |
SMALLINT | 整数值(没有小数点)。精度 5。 |
INTEGER | 整数值(没有小数点)。精度 10。 |
BIGINT | 整数值(没有小数点)。精度 19。 |
DECIMAL(p,s) | 精确数值,精度 p,小数点后位数 s。例如:decimal(5,2) 是一个小数点前有 3 位数,小数点后有 2 位数的数字。 |
NUMERIC(p,s) | 精确数值,精度 p,小数点后位数 s。(与 DECIMAL 相同) |
FLOAT§ | 近似数值,尾数精度 p。一个采用以 10 为基数的指数计数法的浮点数。该类型的 size 参数由一个指定最小精度的单一数字组成。 |
REAL | 近似数值,尾数精度 7。 |
FLOAT | 近似数值,尾数精度 16。 |
DOUBLE PRECISION | 近似数值,尾数精度 16。 |
DATE | 存储年、月、日的值。 |
TIME | 存储小时、分、秒的值。 |
TIMESTAMP | 存储年、月、日、小时、分、秒的值。 |
INTERVAL | 由一些整数字段组成,代表一段时间,取决于区间的类型。 |
ARRAY | 元素的固定长度的有序集合 |
MULTISET | 元素的可变长度的无序集合 |
XML | 存储 XML 数据 |
LIKE模糊查询,函数优化
优化方案(1) select INSTR('abc','a') # a在abc中出现的位置 1 如果不存在 0 SELECT * FROM user WHERE INSTR(name, '张' ) # 使用在模糊查询中 (2) select LOCATE('a','abc') # a在abc中出现的位置 1 如果不存在 0 SELECT * FROM user WHERE LOCATE('张', name) # 使用在模糊查询中 (3) select POSITION('a' IN 'abc') # a在abc中出现的位置 1 如果不存在 0 SELECT * FROM user WHERE POSITION('张' IN name) # 使用在模糊查询中 instr(title,’手册’)>0 相当于likeinstr(title,’手册’)=0 相当于not like
CAST()和CONVERT()函数 装换类型
# CONVERT()和CAST()函数 可以转换的类型是有限制: # 二进制,同带binary前缀的效果 : BINARY # 字符型,可带参数 : CHAR() # 日期 : DATE # 时间: TIME # 日期时间型 : DATETIME # 浮点数 : DECIMAL # 整数 : SIGNED # 无符号整数 : UNSIGNED SELECT CONVERT('2021-10-3',DATETIME) # 2021-10-03 00:00: SELECT CONVERT(create_time,DATE) from user SELECT CAST('12.5' AS UNSIGNED) # 12 SELECT CAST(create_time AS DATE) from user
is not null (is null) 查询慢优化
原因: Null列使用索引没有意义,任何包含null值的列都不会被包含在索引中。因此where语句中的is null或is not null的语句优化器是不允许使用索引的
优化: 使用 IFNULL()
SELECT * FROM `user` where `password` is not nullSELECT * FROM `user` where IFNULL(`password`,'kong')!='kong' SELECT * FROM `user` where `password` is nullSELECT * FROM `user` where IFNULL(`password`,'') = ''
mysql 递归查询sql
-
mysql5 版本 parent_id为 表中字段 作为条件查询
delimiter $$ drop function if exists get_child_list$$ create function get_child_list(in_id varchar(10)) returns varchar(1000) begin declare ids varchar(1000) default ''; declare tempids varchar(1000); set tempids = in_id; while tempids is not null do set ids = CONCAT_WS(',',ids,tempids); select GROUP_CONCAT(id) into tempids from organization where FIND_IN_SET(parent_id,tempids)>0; end while; return ids; end $$ delimiter ;select * from organization where FIND_IN_SET(id,get_child_list(1))
-
mysql8 版本
with recursive r as(select id,org_name as name from organization as c where id=1union allselect c.id,CONCAT(r.name, '>', c.org_name) as name from organization as c,r where r.id = c.parent_id)select id,name from r;
-
java递归查询
public List<Organization> recursiveSelect(Integer id) { List<Organization> organizations = new ArrayList<>(); QueryWrapper<Organization> queryWrapper = new QueryWrapper(); if (id == null) { return organizations; } else { queryWrapper.eq("parent_id", id); List<Organization> list = organizationMapper.selectList(queryWrapper); if (CollectionUtils.isNotEmpty(list)){ for (Organization organization : list) { organizations.add(organization); organizations.addAll(recursiveSelect(organization.getId())); } return organizations; } return recursiveSelect(null); } }
-
表结构
DROP TABLE IF EXISTS `organization`;CREATE TABLE `organization` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `org_name` varchar(255) CHARACTER SET utf8mb4 NULL DEFAULT NULL COMMENT '机构名', `parent_id` bigint(20) NULL DEFAULT NULL COMMENT '父级id', `org_logo` text CHARACTER SET utf8mb4 NULL COMMENT '公司logo', `head_color` varchar(255) CHARACTER SET utf8mb4 NULL DEFAULT NULL COMMENT '头部栏颜色', `menu_color` varchar(255) CHARACTER SET utf8mb4 NULL DEFAULT NULL COMMENT '菜单栏颜色', `license_name` varchar(255) CHARACTER SET utf8mb4 NULL DEFAULT NULL COMMENT 'license服务器中文件名', `license_old_name` varchar(255) CHARACTER SET utf8mb4 NULL DEFAULT NULL COMMENT 'license原名', `state_id` int(1) NULL DEFAULT NULL COMMENT '0:失效; 1有效', `create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP, `update_time` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 40 CHARACTER SET = utf8mb4 ROW_FORMAT = DYNAMIC;
mysql EXPLAN 详情
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|---|
唯一标识 | 查询的类型 | 输出的表 | 匹配的分区 | 连接类型 | 可能的索引选择 | 实际使用的索引 | 索引长度 | 索引的哪一列被引用 | 估计要扫描的行 | 符合查询条件的数据百分比 | 附加信息 |
id:
- 该语句的唯一标识,如果explain的结果包含多个id值,则数字越大越先执行;相同id,从上往下依次执行
select_type:查询类型
- SIMPLE:简单查询(未使用UNION或子查询)
- PRIMARY:最外层的查询(子查询中最外层查询,查询中若包含任何复杂的子部分,最外层就是PRIMARY)
- UNION:在UNION中的第二个和随后的SELECT被标记为UNION,如果UNION被FROM子句中的子查询包含,那么它的第一个SELECT会被标记为DERIVED
- DEPENDENT UNION:UNION中的第二个或后面的查询,依赖了面的查询
- SUBQUERY:子查询中的第一个SELECT
- UNION RESULT:UNION的结果
- DEPENDENT SUBQUERY:子查询中的第一个SELECT,依赖了外面的查询
- DERIVED:派生表的SELECT,FROM子句的子查询
- UNCACHEABLE SUBQUERY:子查询结果无法缓存,必须针对外部查询的每一行重新评估
**派生表:**包含在FROM子句的子查询中的SELECT,MYSQL会递归执行并将结果放在一个临时表中。
table:
- 表示当前这一行正在访问哪张表,如果SQL定义了别名,则展示表的别名
partitions:
- 当前查询匹配记录的分区。对于未分区的表,返回null
type:
连接类型常用的有以下,性能从好到坏排序 如下:
- system:const的特例,仅返回一条数据的时候
- const:查找主键索引,返回的数据至多一条(0或1).属于精确查找
- eq_ref:查找唯一性索引,返回数据至多一条,属于精确查找
- ref:查找非唯一性索引,返回匹配某一条件多条数据,属于精确查找,数据返回可能多条
- range:范围扫描,查找某个索引的部分索引,一般在where子句中使用<,>,in,between等关键词
- index:全索引扫描,比ALL快,因为索引文件比数据文件小的多
- all:不使用任何索引,进行全表扫描。性能最差
possible_keys:
- 指出MySQL能使用哪个索引在表中找到记录,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用(该查询可以利用的索引,如果没有任何索引显示 null)
key:
- key列显示MySQL实际决定使用的键(索引),必然包含在possible_keys中
key_len:
- 索引使用的字节数。由于存储格式,当字段允许为NULL时,key_len比不允许为空时大1字节。
- 不损失精确性的情况下,长度越短越好
ref:
-
列与索引的比较,表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
-
如果ref是一个函数,则使用的值是函数的结果。要想查看是哪个函数,可在EXPLAIN语句之后紧跟一个SHOW WARNING语句。
rows:
- MySQL估算会扫描的行数,数值越小越好
Extra:
- 该列包含MySQL解决查询的详细信息,有以下几种情况:
- Using where:不用读取表中所有信息,仅通过索引就可以获取所需数据,这发生在对表的全部的请求列都是同一个索引的部分的时候,表示mysql服务器将在存储引擎检索行后再进行过滤
- Using temporary:表示MySQL需要使用临时表来存储结果集,常见于排序和分组查询,常见 group by ; order by
- Using filesort:当Query中包含 order by 操作,而且无法利用索引完成的排序操作称为“文件排序”
- Using join buffer:改值强调了在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果。如果出现了这个值,那应该注意,根据查询的具体情况可能需要添加索引来改进能。
- Impossible where:这个值强调了where语句会导致没有符合条件的行(通过收集统计信息不可能存在结果)。
- Select tables optimized away:这个值意味着仅通过使用索引,优化器可能仅从聚合函数结果中返回一行
- No tables used:Query语句中使用from dual 或不含任何from子句