本文详细介绍了SQL的分类、MySQL注释、DDL与DML操作、DQL查询、数据库备份还原、表约束、范式理论、多表查询、事务管理及DCL数据控制。涵盖了创建数据库、表结构、数据操作、查询技巧以及权限管理等内容。
SQL即Structured Query Language 结构化查询语言。
目录
一、SQL语句分类：
1.DDL( 数据定义语言Data Definition Language ) 如：建库，建表
2.DML( 数据操纵语言Data Manipulation Language)，如：对表中的记录操作增删改
3.DQL( 数据查询语言Data Query Language)，如：对表中的查询操作
4.DCL( 数据控制语言Data Control Language)，如：对用户权限的设置
二、MySQL的注释
--空格 单行注释
/* */ 多行注释
# 这是 mysql 特有的注释方式
三、数
据定义语言DDL
3.1DDL操作数据库
3.1.1创建数据库
create database db;
判断数据库是否已经存在，不存在则创建数据库
create database if not exists db;
创建数据库并指定字符集
create database db default character set gbk;
3.1.2查看数据库
show databases;
-- 查看某个数据库的定义信息
show create database db;
3.1.3修改数据库
修改数据库默认的字符集
alter database db character set utf8;
删除数据库
drop database db;
3.1.4使用数据库
查看正在使用的数据库
select database();
改变要使用的数据库
use db;
3.2DDL 操作表结构
3.2.1创建表
create table student
( 24 / 33 id int, -- 整数
name varchar(20), -- 字符串
birthday date -- 日期
);
3.2.2查看表
查看 db 数据库中的所有表以及student 表的结构，查看 student 的创建表 SQL 语句
use db;
show tables;
desc student;
show create table student;
-- 创建一个 s1 的表与 student 结构相同
create table s1 like student;
3.2.3删除表
-- 直接删除表 s1 表
drop table s1;
-- 判断表是否存在并删除 s1 表
drop table if exists `create`;
3.2.4修改表结构
修改列类型 MODIFY
alter table student modify abc varchar(20);
修改列名 CHANGE
alter table student change remark intro varchar(30);
删除列 DROP
alter table student drop intro ;
修改表名
rename table student to student2;
修改字符集 character set
alter table student character set gbk;
四、DML 操作表中的数据
4.1插入记录
insert into student (id,name,age,sex) values (1, '张三', 18, '男');
-- 插入所有列
insert into student values (2, '李四', 18, '男', '上海');
-- 如果只插入部分列，必须写列名
insert into student values (3, '王五', 18, '男');
4.2更新表记录
-- 修改所有记录
update student set sex = '2';
-- 修改个别记录
update student set sex='2' where id=5;
-- 修改个别记录多个列
update student set age=36, address='上海' where id=6;
4.3删除表记录
-- 删除个别记录
delete from student where id=1;
-- 删除表中的所有数据
delete from student;
TRUNCATE TABLE student;
truncate 和 delete 的区别：
truncate 相当于删除表的结构，再创建一张表。
五、DQL 查询表中的数据
5.1简单查询
查询表中所有数据
select * from student;
查询表中指定的列数据
select name,age from student;
指定列的别名进行查询
select name as 姓名,age as 年龄 from student;
select st.name as 姓名,age as 年龄 from student as st
清除重复值
select distinct address from student;
查询结果参与运算
select math+5 from student
select *,(math+english) 总成绩 from student
5.2条件查询
比较运算符
>、=、=、<> <>在 SQL 中表示不等于，在 mysql 中也可以使用!= 没有==
BETWEEN...AND 在一个范围之内，如：between 100 and 200 相当于条件在 100 到 200 之间，包头又包尾
IN(集合) 集合表示多个值，使用逗号分隔
LIKE '张%' 模糊查询
IS NULL 查询某一列为 NULL 的值，注：不能写=NULL
逻辑运算符
and 或 && 与，SQL 中建议使用前者，后者并不通用。
or 或 || 或
not 或 ! 非
select * from student3 where math>80
select * from student3 where age <> 20;
select * from student3 where age != 20;
select * from student3 where age>35 and sex='男';
select * from student3 where age>35 or sex='男';
select * from student3 where id=1 or id=3 or id=5
select * from student3 where id in(1,3,5);
select * from student3 where english between 75 and 90;
通配符
% 匹配任意多个字符串
_ 匹配一个字符
select * from student3 where name like '马%';
select * from student3 where name like '马_'
5.3排序
单列排序
select * from student order by age desc;
组合排序
select * from student order by age desc, math asc;
聚合函数
聚合函数查询是纵向查询， 它是对一列的值进行计算，然后返回一个结果值。聚合函数会忽略空值 NULL。
五个聚合函数
max(列名) 求这一列的最大值
min(列名) 求这一列的最小值
avg(列名) 求这一列的平均值
count(列名) 统计这一列有多少条记录
sum(列名) 对这一列求总和
IFNULL(列名，默认值) 如果列名不为空，返回这列的值。如果为 NULL，则返回默认值。
select ifnull(id,0) from student;
select count(ifnull(id,0)) from student;
select sum(math) 总分 from student;
5.4分组
一般分组会跟聚合函数一起使用。
select sex, avg(math) from student3 group by sex
查询年龄大于 25 岁的人,按性别分组,统计每组的人数
select sex, count(*) from student3 where age > 25 group by sex ;
查询年龄大于 25 岁的人，按性别分组，统计每组的人数，并只显示性别人数大于 2 的数据
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex having COUNT(*) >2;
having 与 where 的区别
where 子句
1) 对查询结果进行分组前，将不符合 where 条件的行去掉，即在分组之前过滤数据，即先过滤 再分组。
2) where 后面不可以使用聚合函数
having 子句
1) having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤。
2) having 后面可以使用聚合函数
limit 语句
-- 查询学生表中数据，从第 3 条开始显示，显示 6 条。
select * from student3 limit 2,6;
六、数据库备份和还原
6.1备份操作
-- 备份 day21 数据库中的数据到 d:\day21.sql 文件中
mysqldump -uroot -proot day21 > d:/day21.sq
6.2还原操作
use day21;
source d:/day21.sql;
七、数据库表的约束
7.1约束种类
主键 primary key
唯一 unique
非空 not null
外键 foreign key
检查约束 check 注：mysql 不支持
create table st5 ( id int primary key,
-- id 为主键
name varchar(20),
age int )
添加主键
alter table st5 add primary key(id);
删除主键
alter table st5 drop primary key;
主键自增
create table st4 ( id int primary key auto_increment, name varchar(20) ) auto_increment = 1000;
创建好以后修改起始值
alter table st4 auto_increment = 2000;
八、数据库设计
8.1数据规范化
建立科学的，规范的数据库就需 要满足一些规则来优化数据的设计和存储，这些规则就称为范式。
三大范式：
（1）1NF
数据库表的每一列都是不可分割的原子数据项，不能是集合、数组等非原子数据项。即表中的某个列有多个值 时，必须拆分为不同的列。简而言之，第一范式每一列不可再拆分，称为原子性。
（2）2NF
在满足第一范式的前提下，表中的每一个字段都完全依赖于主键。 所谓完全依赖是指不能存在仅依赖主键一部分的列。简而言之，第二范式就是在第一范式的基础上所有列完全 26 / 26 依赖于主键列。
第二范式的特点： 1) 一张表只描述一件事情。 2) 表中的每一列都完全依赖于主键
（3）3NF
在满足第二范式的前提下，表中的每一列都直接依赖于主键，而不是通过其它的列来间接依赖于主键。 简而言之，第三范式就是所有列不依赖于其它非主键列，也就是在满足 2NF 的基础上，任何非主列不得传递 依赖于主键。所谓传递依赖，指的是如果存在"A → B → C"的决定关系，则 C 传递依赖于 A。因此，满足第三范 式的数据库表应该不存在如下依赖关系：主键列 → 非主键列 x → 非主键列 y
8.2三大范式小结
1NF 原子性：表中每列不可再拆分。
2NF 不产生局部依赖，一张表只描述一件事情
3NF 不产生传递依赖，表中每一列都直接依赖于主键。而不是通过其它列间接依赖于主键
九、多表查询
9.1内连接
隐式内连接
看不到 JOIN 关键字，条件使用 WHERE 指定
select * from emp,dept where emp.`dept_id` = dept.`id`;
显式内连接
select * from emp inner join dept;
9.2外连接
select * from dept d left join emp e on d.`id` = e.`dept_id`;
9.3子查询
select * from emp where dept_id = (select id from dept where name='市场部');
select * from emp where salary < (select avg(salary) from emp);
select name from dept where id in (select dept_id from emp where salary > 5000);
十、事务
10.1事务的四大特性 ACID
原子性（Atomicity） 每个事务都是一个整体，不可再拆分，事务中所有的 SQL 语句要么都执行成功， 要么都失败。
一致性（Consistency） 事务在执行前数据库的状态与执行后数据库的状态保持一致。
隔离性（Isolation） 事务与事务之间不应该相互影响，执行时保持隔离的状态。
持久性（Durability） 一旦事务执行成功，对数据库的修改是持久的。就算关机，也是保存下来的。
10.2事务的隔离级别
脏读 一个事务读取到了另一个事务中尚未提交的数据
不可重复读 一个事务中两次读取的数据内容不一致，要求的是一个事务中多次读取时数据是一 致的，这是事务 update 时引发的问题
幻读 一个事务中两次读取的数据的数量不一致，要求在一个事务多次读取的数据的数量 是一致 的，这是 insert 或 delete 时引发的问题
10.3MySQL 数据库有四种隔离级别
读未提交 read uncommitted
读已提交 read committed
可重复读 repeatable read
串行化 serializable
MySQL的隔离级别默认是repeatable read
查询隔离级别 select @@tx_isolation;
设置隔离级别
mysql -uroot -proot
set global transaction isolation level read uncommitted;
十一、DCL数据控制语言
DDL： create / alter / drop
DML：insert /update/delete
DQL ：select /show
DCL ：grant /revoke
11.1创建用户
创建 user1 用户，只能在 localhost 这个服务器登录 mysql 服务器，密码为 123
create user 'user1'@'localhost' identified by '123';
创建 user2 用户可以在任何电脑上登录 mysql 服务器，密码为 123
create user 'user2'@'%' identified by '123';
11.2给用户授权
给 user1 用户分配对 test 这个数据库操作的权限：创建表，修改表，插入记录，更新记录，查询
grant create,alter,insert,update,select on test.* to 'user1'@'localhost';
给 user2 用户分配所有权限，对所有数据库的所有表
grant all on *.* to 'user2'@'%';
11.3撤销授权
revoke all on test.* from 'user1'@'localhost';
查看权限
SHOW GRANTS FOR 'user'@'localhost';
11.4删除用户
drop user 'user2'@'%';
修改管理员密码
mysqladmin -uroot -p password 新密码
修改普通用户密码
set password for '用户名'@'主机名' = password('新密码')
4741
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
