1 SQL分类
-
DQL(Data Query Language)
数据查询语言：对数据库中数据执行查询操作的SQL。
-
DCL(Data Control Language)
数据控制语言：对数据库用户设定以及更改权限的SQL。
-
DDL(Data Definition Language)
数据定义语言：执行表、序列、视图、索引的创建和销毁工作的SQL。
-
DML(Data Manipulation Language)
数据操作语言：执行数据的增、删、改工作的SQL。
-
TCL(Transaction Control Lanager)
事务控制语言：控制数据库事务的SQL。
DQL在之前的学习中已经学习过了，DCL主要是有关权限操作的语句（实际生产中通常由DBA使用），接下来我们要学习的是DDL、DML和TCL。
2 DDL
3.1 数据库的创建和销毁
MySQL中通过库管理不同项目的表。
-
创建数据库
语法： create database [if not exists] 数据库名 [default charset 字符集] 示例： create database test;--创建数据库 create database if not exists test;--如果不存在test库，则创建数据库；否则什么都不做 --创建数据库，默认编解码集为utf8mb4 create database if not exists test default charset utf8mb4; -
销毁数据库
语法：drop database [if exists] 数据库名; 示例： drop database test;--删除数据库 drop database if exists test;--如果数据库存在，则删除数据库
3.2 表和创建和销毁
表是数据库组织和存储数据的基本单位。表主要就是由一个个字段（列）组成。
3.1.1 表的创建
语法：
create table [if not exists] 表名(
字段名 数据类型 [约束],
字段名 数据类型 [约束],
...
);-
表名和字段名
合法的标识符即可。 表名一般以t_开头，字段名多个单词间以下划线分隔。 表名和字段名不区分大小写。
-
数据类型
明确一列可以存储什么样的数据。
数据类型 解释 注意事项 tinyint 1字节整数 有符号数：-128~127 无符号数(unsigned)：0~255 smallint 2字节整数 有符号数：-32768~32767 无符号数：0~65535 mediumint 3字节整数 有符号数：-8833608~8833607 无符号数：0~1677215 int、integer 4字节整数 有符号数：-2147483648~2147483647 无符号数：0~4294967295 bigint 8字节整数 有符号数：-9223372036854775808~9223372036854775807 无符号数：0~18446744073709551615 float 4字节浮点数 -3.402823466E+38~3.402823466E+38 double 8字节浮点数 -1.7976931348623157E+308~1.7976931348623157E+308 decimal(m，d) p+2个字节，定点数 m表示精度（数字有效长度），d表示标度（小数点的位数） m最大值65，默认值10；d最大值30，默认值0 char(n) 定长字符串 无论保存的数据多少，一定占n的字符空间，n最大255 varchar(n) 变长字符串 根据保存的数据多少，占据对应的字符空间，n最大65535 text 大文本类型 能保存4G大小文字 enum 枚举类型 在给定的范围内选择一个值，比如性别 enum('F','M') date 4字节 包含年月日，1000-01-01~9999-12-31 time 3字节 包含时分秒，-838:59:59~838:59:59 datetime 8字节 包含年月日 时分秒,1000-01-01 00:00:00~9999-12-31 23:59:59 timestamp 4字节 包含年月日时分秒，1970-01-01 08:00:01~2038-01-19 11:14:07 实战建议：
-
如果数字一定大于0，且不会进行减运算，可以使用unsigned无符号数。如非必要，还是建议使用有符号数。
-
float和double存在精度问题，实战中很少使用，MySQL后续版本将不再支持上述两个类型。对精度要求较高时使用decimal。
-
char和varchar在保存多字节字符（比如中文）底层实现是一样的，所以实战中建议使用varchar。
-
实战中通常需要精确到秒，而timestamp的上限很快到达，建议使用datetime
总结需要掌握的类型：
整数：int bigint 小数：double、decimal 字符：varchar 日期：datetime
create table t_person( person_id int, person_name varchar(50), age tinyint unsigned, sex enum('F','M'), birthday datetime, salary decimal(10,2), mobile varchar(11), address varchar(500) ); -
-
约束
约束限制字段值格式和范围。
约束 解释 说明 primary key 主键 用来明确一列为主键列 not null 非空 约束一列的值不能为null unique 唯一 约束一列的值不能重复 default 默认 当列没有值时，设置默认值 references 外键约束 明确该列为外键列，并指明引用哪一张表的哪一列值 普通约束示例：
--列级约束：直接将约束定义在列中 create table t_person( person_id int primary key, person_name varchar(50) unique not null, age tinyint unsigned not null, sex enum('F','M') not null default 'M', birthday datetime not null, salary decimal(10,2) not null, mobile varchar(11) not null, address varchar(500) not null ); --表级约束：将约束定义在列定义语句后，not null、default 没有表级约束 create table t_person( person_id int , person_name varchar(50) , age tinyint unsigned , sex enum('F','M') not null default 'M', birthday datetime not null, salary decimal(10,2) not null, mobile varchar(11) not null, address varchar(500) not null, -- [constraint 约束名] 约束类型(字段名) constraint pk_person_id primary key(person_id), constraint uq_person_name unique(person_name) );外键约束示例：
--外键约束，必须使用表级约束方式定义 create table t_class( class_id int primary key, class_name varchar(20) not null ); create table t_student( student_id int primary key, student_name varchar(20) not null, class_id int not null, constraint fk_student_class foreign key(class_id) references t_class(class_id) )
3.1.2 表的销毁
语法：
drop table [if exists] 表名;
示例：
drop table t_person;
注意：如果两张表有外键引用关系，那么必须先删子表，后删父表。
3.3 视图
视图：一个视图代表一个复杂的查询SQL，基于视图操作就是基于它指代的查询SQL进行操作。
3.3.1 创建视图
语法：
create view 视图名 as 查询SQL;
示例：
create view v_employee_departments as
select e.*,d.department_name,d.location_id
from employees e left join departments d
on e.department_id = d.department_id;3.3.2 使用视图
语法：
select 列，列,...
from 视图名
[where 条件]
...
示例：
create view v_employee_departments as
select e.*,d.department_name,d.location_id
from employees e left join departments d
on e.department_id = d.department_id;3.3.3 销毁视图
语法：
drop view 视图名;
示例：
drop view v_employee_departments;3.3.4 视图的特点
-
视图本质上就是一个复杂的查询SQL
-
视图本身并不能提升查询效率，只能提升开发效率
-
视图本身不是表，不存储任何数据
-
视图本身还可以用于屏蔽底层表的一些机密列
3.4 索引
-
普通索引常用于过滤数据。例如，以商品种类作为索引，检索种类为“手机”的商品。
-
唯一索引主要用于标识一列数据不允许重复的特性，相比主键索引不常用于检索的场景。
-
主键索引是行的唯一标识，因而其主要用途是检索特定数据。
-
全文索引效率低，常用于文本中内容的检索。
创建索引
普通索引（INDEX）
# 在创建表时指定
mysql> create table student1(
id int not null,
name varchar(100) not null,
birthdy date, sex char(1) not null,
index nameindex (name(50))
);
Query OK, 0 rows affected (0.02 sec)
# 基于表结构创建
mysql> create table student2(
id int not null,
name varchar(100) not null,
birthday date,
sex char(1) not null
);
Query OK, 0 rows affected (0.01 sec)
mysql> create index nameindex on student2(name(50));
# 修改表结构创建
mysql> create table student3(
id int not null,
name varchar(100) not null,
birthday date,
sex char(1) not null
);
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER TABLE student3 ADD INDEX nameIndex(name(50));
mysql> show index from student3; //查看某个表格中的索引唯一索引（UNIQUE）
# 在创建表时指定
mysql> create table student4(id int not null, name varchar(100) not null, birthday date, sex char(1) not null, unique index id_idex (id));
Query OK, 0 rows affected (0.00 sec)
# 基于表结构创建
mysql> create table student5(id int not null, name varchar(100) not null, birthday date, sex char(1) not null);
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE unique INDEX idIndex ON student5(id);
# 基于表结构创建
mysql> create table student18(id int not null, name varchar(100) not null, birthday date, sex char(1) not null);
mysql> alter table student18 add unique index idIndex(id);
Query OK, 0 rows affected (0.02 sec)主键索引（PRIMARY KEY）
# 创建表时时指定
mysql> create table student6(id int not null, name varchar(100) not null, birthday date, sex char(1) not null, primary key (id));
Query OK, 0 rows affected (0.01 sec)
# 修改表结构创建
mysql> create table student7(id int not null, name varchar(100) not null, birthday date, sex char(1) not null);
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER TABLE student7 ADD PRIMARY KEY (id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0主键索引不能使用基于表结构创建的方式创建。
删除索引
普通索引（INDEX）
# 直接删除
mysql> DROP INDEX nameIndex ON student1;
# 修改表结构删除
mysql> ALTER TABLE student2 DROP INDEX nameIndex;唯一索引（UNIQUE）
# 直接删除
mysql> drop index id_idex on student4;
# 修改表结构删除
mysql> ALTER TABLE student5 DROP INDEX idIndex;主键索引（PRIMARY KEY）
mysql> ALTER TABLE student7 DROP PRIMARY KEY;主键不能采用直接删除的方式删除。
查看索引
mysql> SHOW INDEX FROM tab_name;慢查询
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过设定时间的语句
慢查询开启方法
命令开启
特点：只对当此数据库运行生效，一旦数据库重启便失效
-
查询当前数据库是否开启慢查询
mysql> show variables like "%slow%";
mysql> show variables like "%long%";
-
修改慢查询阈值时间
mysql> set global long_query_time=2; #将阈值时间改为2s,修改完成后由于刷新时间不确定需要重新登录查看 -
开启慢查询
mysql> set global slow_query_log='ON'; -
重新查看慢查询信息，验证是否开启
-
查看sql日志记录是否开启
show variables like "%general_log%";
-
开启sql日志记录，会将所有sql记录在指定位置
set global general_log='ON';
至此开启慢查询，当某个sql执行时间超过了指定阈值时间，将会记录到慢查询日志中
注意：general_log的sql记录会记录所有的sql，日志量极大，日常一般只开启慢查询不开启sql日志记录，只有当发现数据库异常（比如已经有慢查询产生）才会临时开启，以便完整掌握数据库信息
修改配置文件开启
特点：通过配置/etc/my.cnf配置文件开启，已经开启永久生效
# 打开mysql的配置文件 /etc/my.cnf 添加如下信息
slow_query_log=1 #设置开启慢查询 值可以为1也可以为ON
slow_query_log_file=/opt/liuyh/log/mysql/mysql-slow.log #设置慢查询日志位置,保证数据库所在用户拥有该位置权限
long_query_time=3 #设置慢查询阈值时间，单位s4.1 添加
语法：
insert into 表名(列名,列名，列名,...)
values(值，值，值，...);
示例：
insert into t_person(person_id,person_name,age,sex,birthday,salary,mobile,address)
values(1,'xiaohei',18,'M','2000-12-23 12:30:30',10000.00,'187xxxxxxx','郑州硅谷');
注意：values后小括号中的值要和表名后小括号对应！！！
--给所有列添加数据，表名后的小括号可以省略。注意，此时值就要和表定义时列的顺序保持一致！！！
insert into 表名
values(值，值，值，...);
示例：
insert into t_person
values(2,'xiaobai',18,'F','2000-12-23 12:30:30',12000.00,'185xxxxxxx','郑州硅谷');
--主键自增，将主键设置为auto_increment,主键的值无需显式赋值可自动增长
create table t_person(
person_id int primary key auto_increment,
person_name varchar(50) unique not null,
age tinyint unsigned not null,
sex enum('F','M') not null default 'M',
birthday datetime not null,
salary decimal(10,2) not null,
mobile varchar(11) not null,
address varchar(500) not null
);
insert into t_person
values(null,'xiaolv',20,'F','2000-12-23 12:30:30',12000.00,'186xxxxxxx','郑州硅谷');4.2 删除
语法：
delete from 表名
[where 条件];
示例：
delete from t_person
where person_id = 1;
注意：不加条件，则删除表中所有数据。
表截断：truncate table 表名;//删除表中所有数据
表截断：直接在物理空间中，将保存数据的空间截断，效率更高
delete from 表：一行一行的删除，效率比较低。4.3 更改
语法：
update 表名
set 列名 = 新值,列名=新值,...
[where 条件];
示例：
update t_person
set age = 20,sex = 'F'
where person_id = 1;
注意：不加where条件，则更新所有行。5 TCL 事务
事务：事务是数据库为保证SQL操作完整性而提供的一种机制。保证一个业务对应的多条SQL要么同时成功，要么同时失败。
5.1 使用事务
开启事务
begin 或者 start transaction;
第1条sql
第2条sql
...
结束事务，如果结果正确：提交事务 commit
如果结果错误：回滚事务 rollback
示例：
begin;
update t_account set balance = balance - 100 where account_name='xushy';
update t_account set balance = balance + 100 where account_name='liy';
成功：commit；
失败：rollback;5.2 事务的特点
-
Atomicity 原子性 保证多条SQL要么同时成功，要么同时失败。
-
Consistency 一致性 事务执行前后，数据的状态是一致的。
-
Isolation 隔离性 并发访问相同数据时，不同用户是否可以看到另外一个用户未提交的数据。oracle默认只能看到提交后的。
-
Durability 持久性 一个事务一旦提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响 。
数据库的重点：查询
6 权限管理
用户基础管理
登录
#本地登录
mysql -uroot -p123456
#远程登录
#客户端语法：mysql -u 用户名 -p 密码 -h ip地址 -P端口号:如果没有改端口号就不用-P指定端口
mysql -h192.168.246.253 -P 3306 -uroot -p123456
#上述命令含义为将要登录到远程一台ip为192.168.246.253，端口为3306，用户名为root，密码为123456的msyql修改用户登录权限
如果用户无法远程登录证明用户没有远程登录权限，需要修改权限
-
使用root用户本地登录
-
查看用户登录权限
mysql> use mysql; #进入msyql库 mysql> select user,host from user; #查询user表的user,host字段 -
修改用户的登录权限
mysql> update user set host = '%' where user = '用户名'; #设置root用户可在任意地点登录 #此外登录权限可以有如下设置 % 所有主机远程登录 192.168.246.% 192.168.246.0网段的所有主机 192.168.246.252 指定主机 localhost 只允许本地用户登录 -
修改完成后，从新验证登录
[root@localhost log]mysql -h192.168.246.253 -P 3306 -uroot -p123456 mysql 命令参数拓展 -h 指定主机名 【默认为localhost】 -大P MySQL服务器端口 【默认3306】 -u 指定用户名 【默认root】 -p 指定登录密码 【默认为空密码】 -e 接SQL语句，可以写多条拿;隔开 [root@localhost log]mysql -h192.168.246.253 -P 3306 -uroot -p123456 -D mysql -e 'select * from user;' #上述命令为远程登录一台ip为192.168.246.253 端口为3306 用户名为root 密码为123456的mysql数据库，并进入到mysql库中执行'select * from user;'语句
创建用户
mysql> create user gsc@'localhost' identified by 'gsc123'; #创建用户为tom，并设置密码。
#上述命令为创建用户名为gsc,密码为gsc123的用户且只能在本地登录
mysql> FLUSH PRIVILEGES; #更新授权表
# 注意：此时创建用户没有任何权限，不可操作数据删除用户
msyql>drop user 用户名；权限简介
为了更好管理数据库，数据库为用户设置了各种权限，超级管理员可以通过授予以及取消用户权限来管理用户
| 权限 | 权限级别 | 权限说明 |
|---|---|---|
| CREATE | 数据库、表或索引 | 创建数据库、表或索引权限 |
| DROP | 数据库或表 | 删除数据库或表权限 |
| GRANT OPTION | 数据库、表或保存的程序 | 赋予权限选项 #小心给予 |
| ALTER | 表 | 更改表，比如添加字段、索引等 |
| DELETE | 表 | 删除数据权限 |
| INDEX | 表 | 索引权限 |
| INSERT | 表 | 插入权限 |
| SELECT | 表 | 查询权限 |
| UPDATE | 表 | 更新权限 |
| LOCK TABLES | 服务器管理 | 锁表权限 |
| CREATE USER | 服务器管理 | 创建用户权限 |
| REPLICATION SLAVE | 服务器管理 | 复制权限 |
| SHOW DATABASES | 服务器管理 | 查看数据库权限 |
| ALL | 服务器管理 | 所有权限 |
赋予权限
grant 权限名 on 库名.表名 to 用户名@'登录地址限制' identified by '新的密码'；
#实例
grant select on test.user to gsc@'%' identfied by '111111';
将对test库中的user表的查询权限授予gsc,并设置gsc可在任意主机登录，并且密码改为111111回收权限
revoke 权限名 on 库名.表明 from 用户名;
- 被回收的权限必须存在，否则会出错
- 整个数据库，使用 ON datebase.*；
- 特定的表：使用 ON datebase.table；
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
