SQL

结构化查询语言，Structure Query Language，用于访问和操作数据库中的数据，(最初由IBM开发)

SQL是关系型数据库系统的标准语言。常见的关系型数据库管理系统(RDMS)，MySQL,SQL Server,Oracle

MySQL5.6官方文档

MySQL5.7官方文档

一、SQL分类

DDL

数据定义语言（Data Defintion Languages ）

用于改变数据库结构，包括创建、更改和删除数据库对象，例如：create、drop、alter、truncate

DML

数据操纵语句（Data Manipulation Languages）

用于添加、修改和删除数据，例如：insert、delete、update

DCL

数据控制语句（Data Control Languages）

用于实现权限控制，例如：grant、revoke

DQL

数据查询语句（Data Query Languages）

用于对数据进行检索，例如：select、where、 group by

二、存储引擎

数据库存储(数据)引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。

同一个库中可以对不同表设置不同存储引擎

查看数据库引擎

show engines;

引擎描述
InnoDB支持事务安全表(ACID)(事务处理)，支持行锁定和外键，可以实现并发控制。
每一条SQL都是一个事务
MySQL 5.5.5 之后， InnoDB 作为默认存储引擎。
MyISAM有较高的插入、查询速度，全文检索，但是不支持事务。
MySQL 5.5 之前，MyISAM作为默认存储引擎。
MEMORY将表中的数据存储到内存中，更加快速

使用建议：

如果要实现事务安全(ACID)，并要求实现并发控制，则InnoDB是一个很好的选择。

如果数据表主要用来插入和查询记录，则 MyISAM 引擎能够提供较高的处理效率。

如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存的 MEMORY 引擎中，MySQL 中使用该引擎作为临时表，存放查询的中间结果。

如果只有 INSERT 和 SELECT 操作，可以选择Archive 引擎，Archive 存储引擎支持高并发的插入操作，但是本身并不是事务安全的。Archive 存储引擎非常适合存储归档数据，如记录日志信息可以使用 Archive 引擎。

使用哪一种引擎要根据需要灵活选择，一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求。使用合适的存储引擎将会提高整个数据库的性能。

MyISAM引擎与InnoDB引擎数据的存储方式：

MyISAM

# create database db_test;
创建完成后会在 data/  目录下生成一个 db_test 文件夹
# create table test( .... ) engine=myisam;
创建完成后会在 data/db_test/ 生成三个文件 
test.frm ：数据表的框架
test.MYD ：索引文件
test.MYI ：数据文件

InnoDB

# create database db_test;
创建完成后会在 data/  目录下生成一个 db_test 文件夹
# create table test( .... ) engine=innodb;
创建完成后会自动 data/db_test/ 生成2个文件
test.frm ： 数据表框架
test.ibd ： 数据文件+索引文件
# 注意数据表的一些共享数据放在文件 data/ibdata1 中 
data/ibdata1  : InnoDB 引擎的数据表的共享数据

三、数据库管理

1.创建数据库

create database 数据库名称;

2.创建数据库指定编码格式

create database 数据库名称 default charset=utf8;
-- 判断是否有同名数据库，如果有则不创建
create database if not exists 数据库名称 default charset=utf8;

3.更改数据库编码格式

alter database 数据库名称 default character set utf8;
alter database 数据库名称 default charset set utf8;

4.删除数据库

drop database 数据库名称;

5.查看数据库

show databases;
-- DDL
show create database 数据库名称;

四、数据表管理

1.查看数据表

-- 1.查看所有数据表
show tables;
-- 2.查看表结构
desc 表名;
-- 3.查看 DDL
show create table 表名;

2.创建数据表

create table 数据表名称(
    字段1 数据类型,
    字段2 数据类型（字符长度）,
    ...
) [表选项];

例如

create table student(
    id int,
    name varchar(40),
    age tinyint unsigned,
    gender enum('male','female'),
    addtime date
) engine=innodb default charset=utf8;

create table t1(
    id int not null auto_increment primary key,
    name varchar(40) not null,
    age tinyint unsigned default 0,
    mobile char(11),
    remark varchar(255)
);

3.删除表

drop table 表名;

4.修改表名

alter table 原名 rename 新名;
rename table 原名 to 新名;

5.修改表字符集

alter table 表名 character set utf8;
alter table 表名 default charset=字符集;

6.修改表存储引擎

-- 查看
show engines;
-- 修改
alter table 表名 engine 引擎名;

7.修改表结构

添加字段

alter table 表名 add 字段名称 字段类型 [first/after 某个字段名称];

修改字段名称和数据类型

alter table 表名 change 旧名称 新名称 新/旧数据类型;

修改字段数据类型

alter table 表名 modify 字段名称 新的数据类型;

删除字段

alter table 表名 drop 字段名称;

8.移动表

alter table 原数据库名.原表名 rename 新数据库名.新表名;
rename table 原数据库名.原表名 to 新数据库名.新表名;

9.复制表结构

create table 新表名 (like 原表名);

五、数据操作

注意：在插入数据时，如果是字符类型或者日期时间类型时，必须使用单或双引号引起来。

1.插入数据

-- 1.向所有列插入数据
insert into 表名 values(字段值1，字段值2，字段值3...)
-- 2.向特定列插入数据
insert into 表名(字段1，字段3) values(字段1值，字段3值)
-- 3.带自增ID（auto_increment）的插入
insert into 表名 values(null，字段值2，字段值3...)
-- 4.添加多条记录
insert into 表名 values(字段值1，字段值2...),(字段值1，字段值2...),...

2.查询信息

-- 1.查询所有列
select * from 表名
-- 2.查询特定类
select 字段1,字段2 from 表名

3.蠕虫复制

把一张表中查询出的数据插入到另一张表，目标字段与来源字段需一致

insert into 目标表 select * from 来源表;

4.修改数据

update 表名 set 字段名=字段值,字段名=字段值...  [where 条件];

5.删除数据

-- 1.删除特定记录
delete from 数据表 [where 条件];
-- 2.清空数据 DML（删除数据）
delete from 数据表;
-- 3.清空数据 DDL（重建表），清理大量数据时速度快,重置自增值（回收高水位线）
truncate 表名;

六、数据查询

-A 查询速度更快

基本语法

select */字段列表 from 表名 where ... group by ... having  ... order by .... limit ...

1.基本查询

-- 1. =  >  <   >=  <= !=  <> 
select * from student where age [=|>|<|>=|<=|!=] 18;
-- 2. and 
select * from student where age<22 and gender='male';
select * from student where age<22 && gender='male';
-- 3. or 
select * from student where id=1 or id=3;
select * from student where id=1 || id=3;
-- 4. in 
select * from student where id in(1,3);
-- 5. not in
select * from student where id not in(1,3);
-- 6. between  and
select * from student where id between 1 and 3;
-- 7. like (%:任意零个一个或多个字符)  (_:任意一个字符)
select * from student where name like 't%';
select * from student where name like 'tes_';
-- 8. regexp 
select * from student where age regexp '2[0-9]';
-- 9.  is null
select * from student where gender is null;
-- 10. is not null
select * from student where gender is not  null;
-- 11. distinct
select distinct age from student;

2.常用统计函数

函数作用
count总行数
max最大值
min最小值
sum求和
avg平均值

3.子句

-- group by 
select subject,count(*) from student3 group by subject ;
-- having
select subject,count(*) 'count' from student3 group by subject  having count>2;
-- order by 
select * from student3 order by score asc;
select * from student3 order by score desc;
-- limit  (从零开始，然后查询几个)
select * from student3 order by id  limit 0,2;

4.常用函数

函数作用
concat()合并
select concat(id, '-',name) from student3;
now()获取当前时间
current_date()获取当前日期
ifnull()如果第一个参数不为空，则返回第一个参数，否则返回第二个参数
select ifnull(null,123);
if()如果第一个表达式为TRUE，则返回第二个参数的值，否则返回第三个参数的值
select if(true,1,0);

七、高级查询

1.union

组合两个或更多SELECT语句的结果集 ,不返回重复的结果

select * from student1
union
select * from student2

注意：union 与 order by 联合使用时会出现问题，使用limit可以解决

(select * from student3 order by score limit 999) 
union
(select * from student4 order by score desc limit 999);

union all 不忽略重复数据

2.交叉连接

查询结果为笛卡尔积，记录总数为表1记录数乘表2记录数，结果没什么意义

select * from 表1 cross join 表2;
-- 或
select * from 表1,表2;

3.内连接

在交叉连接的基础上加了一个连接条件，只返回满足连接条件的结果

select * from 表1 inner join 表2 on 连接条件;
-- 或
select * from 表1,表2 where 连接条件;

4.外连接

左外连接

返回左表所有数据，与右表相匹配的数据，右表没有匹配则显示NULL

select * from goods left join category on goods.cid=category.id;

右外连接

返回右表所有数据，与左表相匹配的数据，左表没有匹配则显示NULL

select * from goods right  join category on goods.cid=category.id;

5.子查询

Example1.查询学员信息和所学专业名称

select 
t1.name ,
(select title from subject where id=t1.sid) as subject_name
from student t1;

Example2.查询每个学科中成绩最高的学员信息

-- 方法1
select * from student t1, subject t2
where t1.sid=t2.id
and t1.score=(select max(score) from student where sid=t1.sid);

-- 方法2
select * from student 
where 
(score,sid) 
in
(select max(score) ,sid from student  group by sid);

八、用户管理

相关信息保存位置：

表描述
mysql.user所有mysql用户的账号和密码，以及用户对全库全表权限
mysql.db非mysql库的授权
mysql.tables_priv特定表的授权
mysql.columns_priv特定列的授权
mysql.procs_priv特定存储过程的授权

1.创建用户

添加用户时必须指定主机

主机可选值：%、localhost、IP、10.1.1.%

create user '用户名'@'主机' identified by '密码';

2.分配权限

权限可选值： 无权限usage 、所有权限 all 、select、update、delete、super、alter.....

数据库或者表可以使用%表示所有

-- 分配权限
grant 权限 on 数据库.表 to '用户名'@'主机';
-- 分配多个权限
grant 权限1，权限2.. on 数据库.表 to '用户名'@'主机';
-- 表特定列
grant 权限1(列1，列2..) on 数据库.表 to '用户名'@'主机';

注意：分配权限后需刷新权限表# flush privileges;

3.查看权限

-- 当前用户
show grants;
-- 指定用户
show grants for '用户名'@'主机';

3.创建账号并分配权限

grant 权限 on 数据库.表 to '用户名'@'主机' identified by '密码';

4.with grant option

新创建的用户可以创建账号并分配权限，但是所分配的权限不能高于自身权限。也可以收回权限

grant all on *.* to '用户名'@'主机' identified by '密码' with grant option;

5.回收权限

revoke select,create,delete,grant option on 数据库.表  from '用户名'@'主机';

回收用户所有权限

revoke all privileges,grant option from '用户名'@'主机';

注意：回收权限后需刷新权限表# flush privileges;

6.删除账号

drop user '用户名'@'主机';

7.修改密码

update mysql.user set password=password('新密码') where user='用户名' and host='主机';
-- 或
set password for '用户名'@'%'=password('新密码');

8.修改用户信息

rename user '原名'@'原主机' to '新名'@'新主机'

九、事务

数据库中用户一系列操作，要么全部执行，要么全部取消。

事务的使用需要有InnoDB存储引擎的支持

ACID

原子性（Atomicity）

原子性是指事务是作为一个整体被执行，事务中的操作要么全部执行，要么都全都不执行

一致性（Consistency）

事务前后数据的完整性必须保持一致。

隔离性（Isolation）

事务的隔离性是指多个事务并发执行时，一个事务的执行不应影响其他事务的执行。

持续性（Durability）

持续性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的。

开启事务

start transaction;

提交

commit;

回滚

rollback;
Last modification：September 20th, 2019 at 10:56 pm
© 允许规范转载