sql分类
DDL 数据定义语言
定义数据库对象(数据库,表,字段)
查询所有数据库 show database;
查询当前数据库 select database();
使用数据库 use 数据库名;
创建数据库 create database [ if not exists ] 数据库名;
删除数据库 drop database [ if exists ] 数据库名;
创建表 create table 表名(
字段1 字段类型 [ 约束 ] [ comment 字段注解 ],
)[ comment 表注解 ];
查询当前数据库所有表:show tables;
查询表结构:desc 表名;
查询建表语句:show create table 表名;
添加字段:alter table 表名 add 字段名 类型(长度) [comment 注解] [约束];
修改字段类型:alter table 表名 modify 字段名 新数据类型(长度);
修改字段名和字段类型:alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注解] [约束];
删除字段: alter table 表名 drop column 字段名;
修改表名:rename table 表名 to 新表名;
通过图像画界面操作更简单
约束:
约束 | 描述 | 关键字 |
非空约束 | 字段不能为空 | not null |
唯一约束 | 保证字段的所有数据是唯一,不重复的 | unique |
主键约束 | 是一行数据的唯一标识,要求非空且唯一 | primary key(auto_increment) |
默认约束 | 保存数据时,未指定字段值,则采用默认值 | default |
外键约束 | 让两张表建立连接,保证数据的一致性和整性 | foreign key |
数据类型:
分类 | 类型 | 大小(byte) | 范围 | 格式 | 描述 |
日期类型 | 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 | YYYY | 年份值 | |
datetime | 8 | 1000-01-01 00:00:00 至 9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 | |
timestamp | 4 | 1970-01-01 00:00:01 至 2038-01-19 03:14:07 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值,时间戳 |
分类 | 类型 | 大小 | 描述 |
字符串类型 | char | 0-255 bytes | 定长字符串 |
varchar | 0-65535 bytes | 变长字符串 | |
tinyblob | 0-255 bytes | 不超过255个字符的二进制数据 | |
tinytext | 0-255 bytes | 短文本字符串 | |
blob | 0-65 535 bytes | 二进制形式的长文本数据 | |
text | 0-65 535 bytes | 长文本数据 | |
mediumblob | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 | |
mediumtext | 0-16 777 215 bytes | 中等长度文本数据 | |
longblob | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 | |
longtext | 0-4 294 967 295 bytes | 极大文本数据 |
分类 | 类型 | 大小(byte) | 有符号(SIGNED)范围 | 无符号(UNSIGNED)范围 | 描述 | 备注 |
数值类型 | tinyint | 1 | (-128,127) | (0,255) | 小整数值 | |
smallint | 2 | (-32768,32767) | (0,65535) | 大整数值 | ||
mediumint | 3 | (-8388608,8388607) | (0,16777215) | 大整数值 | ||
int | 4 | (-2147483648,2147483647) | (0,4294967295) | 大整数值 | ||
bigint | 8 | (-2^63,2^63-1) | (0,2^64-1) | 极大整数值 | ||
float | 4 | (-3.402823466 E+38,3.402823466351 E+38) | 0 和 (1.175494351 E-38,3.402823466 E+38) | 单精度浮点数值 | float(5,2):5表示整个数字长度,2 表示小数位个数 | |
double | 8 | (-1.7976931348623157 E+308,1.7976931348623157 E+308) | 0 和 (2.2250738585072014 E-308,1.7976931348623157 E+308) | 双精度浮点数值 | double(5,2):5表示整个数字长度,2 表示小数位个数 | |
decimal | 小数值(精度更高) | decimal(5,2):5表示整个数字长度,2 表示小数位个数 |
DML 数据操作语言
对数据库表中的数据进行增删改
指定字段添加数据: insert into 表名(字段1,) values(值1,);
全部字段添加数据: insert into 表名 values(值1,值2,);
批量添加数据(指定字段): insert into 表名(字段名1,) values(值1,值2)(值1,值2);
批量添加数据: insert into 表名() values(值1,值2,...),(值1,值2,...);
修改数据: update 表名 set 字段名1= 值1,字段2=值2,....[ where 条件 ];
删除数据: delete from 表名 [where 条件];
DQL 数据查询语言
查询数据库中表的记录
查询多个字段:select 字段1,字段2,字段3 from 表名;
查询所有字段(通配符):select* from 表名;
设置别名:select 字段 [as 别名1], [as 别名2] ,from 表名;as可省略
去除重复内容记录: select distinct 字段列表 from 表名;
聚合函数: count avg max min sum
分组查询: select 字段列表 from 表名 [where 条件] group by 分组字段名 [ having 分组后过滤条件]
where与having区别:
1. 执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤
2.判断条件不同:where不能对聚合函数进行判断,而having可以
排序查询:
select 字段列表 from 表名 [ where 条件列表 ] [ group by 分组字段] order by 字段1 排序方式1,字段2,排序方式2...;
ASC 升序(默认) DESC 降序
多字段排序,当第一个字段相同时,才会根据第二个字段进行排序
分页查询:
select 字段列表 from 表名 limit 起始索引,查询记录数;
函数:
if(表达式,tvalue,fvalue):当表达式为true取tvalue ,为false取fvalue
case 表达式 when value1 then result1[when value2 then value2 ...] [else result] end
例:对性别,职业等进行判断转换成需要的数据
DCL 数据控制语言
创建数据库用户,控制数据库的访问权限
外键:
物理外键:
概念:使用foreign key 定义外键关联另一张表
缺点:影响增删改的效率(需要检擦外键关系)
仅用于单节点数据库,不适用于分布式,集群场景
容易引发数据库死锁
create table 表名(
字段名 数据类型,
....
[constraint] [外键名称] foreign key (外键字段名)references 主表 (字段名)
);
alter table 表名 add constraint 外键名称 foreign key (外键字段名)references 主表 (字段名);
逻辑外键:在业务逻辑中解决外键关联
多表设计
一对多关系:在多的一方添加外键,关联另外一方的主键
一对一关系:在任意一放加入外键,关联另外一方的主键,并且设置外键为唯一的unique
多对多关系:建立第三张中间表,至少包含两个外键,分别关联两方主键
查询
多表查询: 笛卡尔积
连接查询
内连接:
相当于查询A,B交集部分数据
隐式内连接:select 字段列表 from 表1,表2 where 条件...;
显式内连接:select 字段列表 from 表1 [ inner ] join 表2 on 连接条件;
外连接:
左外连接:查询左表所有数据(包括两张表交集部分数据)
select 字段列表 from 表1 left[ inner ] join 表2 on 连接条件;
右外连接:select 字段列表 from 表1 right[ inner ] join 表2 on 连接条件;
子查询:
SQL语句中嵌套select语句
select * from 1 where column1 = (select column1 from 2 ...);
标量子查询:子查询返回结果为单个值 常用操作符 = > >= ...
列子查询:子查询返回结果为一列 常用操作符 in not in
行子查询:子查询返回结果为一行 常用操作符 = in <>
表子查询:子查询返回结果为多行多列 常用操作符
事务
事务是一组操作的集合,它是一个不可分割的工作单位,会把所有的操作作为一个整体一起向系统提交或者撤销请求
开启事务:start transaction ; / begin;
提交事务:commit;
回滚事务:rollback;
四大特性:
原子性:事务是最小单位,要么一起成功,要么全部失败
一致性:事务完成时,必须使所有数据都保持一致
隔离性:事务在不受外部并发操作影响的独立环境下运行
持久性:事务回滚或提交,对数据的改变是永久的
索引
索引是帮助数据库高效获取数据的数据结构
结构:
一般为B+Tree
语法:
create [ unique ] Index 索引名 on 表名 (字段名,...);
show index from 表名;
drop index 索引名 on 表名;