MySQL

结构化查询语句分类

DDL，数据块定义语言

create

--创建数据库
create database  if not exists db_name;
--创建用户：
create user admin identified by '123456';
--建表,id为主键并递增,
create table grade(id int(8) not null primary key auto_increment comment '年级ID')engine=innodb default charset=utf8
--加外键
create table student (
    student_id int(8) not null comment '学号',
	grade_id int(8) default null comment '年级',
  	primary key (student_id),
  	constraint fk_grade_id foreign key (grade_id) references grade (grade_id)
	) engine=innodb default charset=utf8
--将查询出来的结果生成新表
create table new_table (select * from student);
--在newclass表id字段添加索引
create index index_newclass_id on newclass(id);

drop

--删除用户
drop user admin;
--移除用户某个权限：
revoke delete on panda_auto.* from 'admin'@'%';
--删除数据库
drop database if exists db_name;
--删除表
drop table if exists table_name;

alter

--修改密码
alert user 'admin'@'%' identifide with mysql_native_password by '123';
--设置外键为gradeid
alter table `student` add constraint `fk_gradeid` foreign key (`gradeid`) references `grade` (`gradeid`);
--设置a表主键自增从10000开始
alter table a AUTO_INCREMENT=10000;
--设置全局递增步长为2
alter table a auto_increment_increment=2;
-- 删除外键
alter table student drop foreign key fk_grade_id;
-- 发现执行完上面的,索引还在,所以还要删除索引，这个索引是建立外键的时候默认生成的
alter table student drop index fk_grade_id;
--在table_name表添加field_name字段
alter table table_name add field_name int(4) default '0';
--修改MyClass表字段sex属性宽度为4
alter table MyClass modify age varchar(4);
--修改MyClass表字段sex为age并修改宽度为10
alter table MyClass change sex age int(10);
--将字段id位置改到age字段后面
alter table MyClass modify id int(5) after age;
--删除表age字段
alter table student drop age;
--修改newclass表字符编码为UTF-8
alter table newclass convert to character set utf8;
--修改MyClass表名为newclass
rename table MyClass to newclass;

DML，数据库操作语言

insert

--添加数据
insert into table_name (id) values (1);
--批量添加
insert into newclass(grade_id) values(1),(2),(3);

update

--修改root用户密码为123：
update user set authentication_string=password('123') where user='root';
--将a表的b字段中的字符串内容中的c替换为d
update a set b=REPLACE(b,'c','d')

delete

delete from table_name where student_id = 1

truncate

--清空年级表,速度更快,InnoDB类型会重新设置auto_increment计数器,不会对事务有影响
truncate grade

DQL，数据库查询语言

select

--查询字段值为空的语法：where <字段名> is null
--查询字段值不为空的语法：where <字段名> is not null
--返回自增主键 useGeneratedKeys="true" keyProperty="course_id"
--去重distinct
--模糊查询 between and \ like \ in \ null,like结合使用的通配符:%(代表0到任意个字符)、_(一个字符)
--查询没有写家庭住址的同学(空字符串不等于null)
select student_name from student where address='' or address is null;
--内连接 inner join 查询两个表中的结果集中的交集
--外连接 outer join 左外连接 left join (以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充),右外连接 right join (以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充)
--左查询先判断主条件where，再判断条件on，左连接on限制的就是右表，如果不为真则那一行的值为null，where限制的是左表，如果不为真则此行不显示出来,左连接查询的行数是根据左表的行数来显示的
select teacher.teacher_id,teacher.name as teacher_name,timetable.name from timetable left join teacher on teacher.teacher_id = timetable.teacher_id where timetable.grade_id = 20187 and section = '第一节'
--排序：orderby,默认asc升序,降序desc
--分页：limit 初始索引，每页条数

show

--显示用户权限：
show grants for 'root'@'%';
--显示数据库列表
show databases;
--显示库中的数据表
show tables;
--查询主键递增步长
show variables like '%auto_increment_increment%';

describe

--显示数据表结构
describe/desc tables_name;

DCL，数据库控制语言，grant、commit、rollback

grant

--刷新权限
flush privileges;
--所有主机可用root登录
grant all on *.* to 'root'@'%';
--创建用户及授权
grant select,insert,update,delete,create,drop on panda_auto.* to admin@'%' identified by '123456';

commit：提交一个事务给数据库
rollback：将事务回滚,数据回到本次事务的初始状态

数据表的类型

MyISAM :节约空间及相应速度，支持全文索引，表空间较小
InnoDB ：安全性 , 事务处理及多用户操作数据表，支持事务处理、数据行锁定、外键约束
HEAP
BOB
CSV

数据类型

时间数据

时间数据类型	含义
date	3字节，日期，格式：2020-11-17
time	3字节，时间，格式：08:42:30
datetime	8字节，日期时间，格式：2014-09-18 08:42:30
timestamp	4字节，自动存储记录修改的时间，DEFAULT CURRENT_TIMESTAMP
year	1字节，年份

整型数据

整型数据类型	含义（有符号）
tinyint	1字节，范围（-128~127）
smallint	2字节，范围（-32768~32767）
mediumint	3字节，范围（-8388608~8388607）
int	4字节，范围（-2147483648~2147483647）
bigint	8字节，范围（+-9.22*10的18次方）

浮点型数据

浮点型数据类型	含义
float(m, d)	4字节，单精度浮点型，m总个数，d小数位，有效数字7位数左右
double(m, d)	8字节，双精度浮点型，m总个数，d小数位，有效数字15位左右
decimal(m, d)	decimal是存储为字符串的浮点数

字符串数据

字符串数据类型	含义
char(n)	固定长度，最多255个字符
varchar(n)	可变长度，最多65535个字符
tinytext	可变长度，最多255个字符
text	可变长度，最多65535个字符
mediumtext	可变长度，最多2的24次方-1个字符
longtext	可变长度，最多2的32次方-1个字符

其他数据

其他数据类型	含义
enum(“a1”,“a2”,“a3”)	枚举，最多包含65535个不同的成员,声明为NOT NULL时，第一个成员是默认值
set(“a1”,“a2”,“a3”)	这组值最多包括64个成员，值的选择限制为列定义中声明的值。
unsigned	无符号，在数据类型之后使用，如tinyint（-128 ~ 127）取值变为0 ~ 255

数据类型

数据类型属性	含义
auto_increment	递增，一个表只能有一个，用于主键
binary	只用于char、varchar，将以区分大小写的方式排序
default	在没有任何可用值的情况下，赋予常量，不可用于BLOB或TEXT
index	添加索引：index id(id)
not null	不得插入NULL值
primary key	主键，不能重复、不能为空，指定后不可修改，可使用多字段主键
unique	除NULL值可以重复外，其他值皆不可重复
zerofill	可用于任何数值类型，用0填充剩余字段空间

常用函数

数据函数
- abs(x) -- 绝对值 abs(-10.9) = 10
- format(x, d) -- 格式化千分位数值 format(1234567.456, 2) = 1,234,567.46
- ceil(x) -- 向上取整 ceil(10.1) = 11
- floor(x) -- 向下取整 floor (10.1) = 10
- round(x) -- 四舍五入去整
- mod(m, n) -- m%n m mod n 求余 10%3=1
- pi() -- 获得圆周率
- pow(m, n) -- m^n
- sqrt(x) -- 算术平方根
- rand() -- 随机数
- truncate(x, d) -- 截取d位小数
字符串函数
- char_length 返回字符串长度
- insert('abcdefg',1,4,'123') 123efg，替换
- lower()，upper() 返回小写、大写之后的字符
- left('abcdef',3),right('abcdef',3) 从左边、右边开始截取
- replace('abcdef','bc',’23‘) a23def，替换
- substr('abcdef',2,4) bcd，截取
- reverse() 反转
- concat(a,b,c,...) 合并，如有任何一个参数为NULL ，则返回值为 NULL。
- concat_ws('-',a,b,c,...) 合并，第一个参数是其它参数的分隔符,如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值,但是不会忽略任何空字符串。
时间和日期函数
- current_date() yyyy-MM-dd
- curdate() yyyy-MM-dd
- now() yyyy-MM-dd HH:mm:ss
- localtime() yyyy-MM-dd HH:mm:ss
- sysdate() yyyy-MM-dd HH:mm:ss
- date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j'); 格式化时间
- unix_timestamp(); 获得unix时间戳
- from_unixtime(); 从时间戳获得时间
聚合函数
- count() 返回满足Select条件的记录总和数
  - count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录
  - count(*) 包括了所有的列，相当于行数，在统计结果的时候，包含字段为null 的记录
  - count(1) 用1代表代码行，在统计结果的时候，包含字段为null 的记录
- sum() 返回数字字段或表达式列作统计，返回一列的总和
- avg() 通常为数值字段或表达列作统计，返回一列的平均值
- max() 可以为数值字段，字符字段或表达式列作统计，返回最大的值
- min() 可以为数值字段，字符字段或表达式列作统计，返回最小的值
- group_concat(),能将相同的行组合起来
加密算法
- md5()

事务

事务的ACID原则
- 原子性(Atomic)：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
- 一致性(Consist)：一个事务可以封装状态改变（除非它是一个只读的）。事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。也就是说：如果事务是并发多个，系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant)，以转账案例为例，假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，五个账户总额也应该还是500元，这就是保护性和不变性。
- 隔离性(Isolated)：隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。
- 持久性(Durable)：在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

基本语法

使用set语句来改变自动提交模式
```
set autocommit = 0;   --关闭
```

MySQL中默认是自动提交,使用事务时应先关闭自动提交

--开始一个事务,标记事务的起始点
start transaction
-- 提交一个事务给数据库
commit
-- 将事务回滚,数据回到本次事务的初始状态
rollback
-- 还原MySQL数据库的自动提交
set autocommit = 1;
-- 保存点
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点

保存点

savepoint 保存点名称 -- 设置一个事务保存点
rollback to savepoint 保存点名称 -- 回滚到保存点
release savepoint 保存点名称 -- 删除保存点

索引

索引的作用
- 提高查询速度
- 确保数据的唯一性
- 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
- 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
- 全文检索字段进行搜索优化
分类
- 主键索引 (Primary Key)，确保数据记录的唯一性，确定特定数据记录在数据库中的位置
- 唯一索引 (Unique)，避免同一个表中某数据列中的值重复，唯一索引可能有多个
- 常规索引 (Index)，快速定位特定数据，应加在查询找条件的字段，不要对经常变动的数据加索引，不宜添加太多常规索引,影响数据的插入,删除和修改操作
- 全文索引 (FullText)，只能用于MyISAM类型的数据表，只能用于char、varchar、text数据列类型,适合大型数据集
索引的数据结构
- hash类型的索引：查询单条快，范围查询慢
- btree类型的索引：b+树，层数越多，数据量指数级增长（innodb默认支持）
- 不同的存储引擎支持的索引类型也不一样
  - InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
  - MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
  - Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
  - NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
  - Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

posted on 2021-03-29 13:55 幺幺零零阅读(66) 评论(0) 编辑收藏举报

结构化查询语句分类

数据表的类型

数据类型

常用函数

事务

索引

搜索

常用链接

我的标签

随笔分类

随笔档案

阅读排行榜