SQL介绍
SQL一种操作语言，本文按具体的操作对象，分别梳理了操作数据库、表、字段、数据的代码基本语法。
一、操作数据库database
1. 五大操作代码语法：CRUD+ use
- create-retrieve-update-delete
- 主要操作数据库的编码字符集
#1.创建数据库
create database 库名;
#2.查看数据库的默认字符集
show create database 库名; -- 默认 character set == utf8
#3.修改数据库的默认字符集
alter database 库名 character set utf8mb4; -- 兼容emoji
#4.删除数据库
drop database 库名;
drop databases; -- 删除所有
#避免重复报错
create database if not exists 库名 character set utf8mb4;
drop database if exists 库名;
#5. 查询数据库
show databases; -- 显示所有数据库
select database(); -- 显示当前正使用的数据库
#6. 调用数据库
use 库名; -- 不用再加database
二、操作表table和字段column
1. 代码语法总结
use 库名; -- 指定数据库
#1. 创建表三要素： 表名称 + 字段名 + 数据类型
create table 表名( 字段1 数据类型1 [约束], 字段2 数据类型2);
show tables; -- 显示所有表格
desc 表名; -- 查询表结构，字段名称和定义 ！不加table！
#2. 修改表的表名、字符集
alter table 原表名 rename to 新表名;
show create table 表名; -- 查看字符集
alter table 表名 character set utf8;
#3. 修改字段
#增加字段
alter table 表名 add column 新字段 数据类型;
#删除字段
alter table 表名 drop column 字段;
#修改字段名+数据类型
alter table 表名 change 原字段 新字段名 新数据类型;
#只修改字段名
alter table 表名 change 字段名 字段名 新数据类型;
#只改数据类型、参数设置 modify
alter table 表名 modify 字段 新数据类型;
#4. 删除表格
drop table 表名;
#5. 复制表格/备份
#只复制表头
create table 新表名 like 被复制的表名;
#复制整个数据
create table 新表名 select * from 被复制的表名;
#复制部分表格，部分字段列
create table 新表名 select 字段列表 from 被复制的表名;
#6.查询表的其他信息
#查询表的储存路径/目录
show variables like '%datadir%';
show global variables like "%datadir%";
#查询表结构
SELECT column_name FROM information_schema.columns WHERE table_name='表名';
#查询表的主键
SELECT column_name FROM INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` WHERE table_name='表名' AND constraint_name='PRIMARY';
2. 常见数据类型总结
| 常见数据类型 | 代码 | 举例 | 备注 |
| 整数 | int | ||
| 小数 | double | double(5,2) | 最多5位数999.99,保留2位 |
| 字符串 | varchar() | varchar(20) | 最大字符长度20 |
| 日期：年月日 | date | '1990-10-10' | yyyy-MM-dd |
| 日期时间 | datetime | yyyy-mm-dd HH:mm:ss | |
| 时间戳 | timestamp | 可自动插入系统时间 |
> 赋值注意事项
1. 除了数值类型，其他数据类型全部需用''赋值
2. timestamp数据类型参数设置
#设置成功后，不给此字段赋值时/赋default，系统自动插入当前时间
desc 表名; -- 先查看时间戳字段名 defualt 值
alter table 表名 modify 时间戳字段名 timestamp
not null -- 不可为空约束，
default current_timestamp -- defualt插入系统当前时间
on update current_timestamp; -- 当修改记录时，也自动更新时间3. 代码示例
create table 表名(
姓名 varchar(20),
年龄 int,
身高 double(5,1),
性别 varchar(2) default '男'
生日 date,
注册日期 timestamp not null default current_timestamp); 三、操作表中的数据data
1. 数据增删改，查询代码语法
#1.创建/添加数据（一行一行添加）
insert into 表名(字段列表) values(赋值列表);
insert into 表名 values(赋值列表);
#2.查询数据
select * from 表名;
select 字段列表 from 表名; -- 可加新字段（数值运算、聚合函数）
#基本语法总结
select distinct 字段列表 -- 加在字段名前去除重复
ifnull(字段名,0) -- 替换null值，便于计算
from 表名列表
where 分组前的条件 -- 不满足则不会参与分组
group by 分组字段
having 分组后的条件 -- 必须是分组字段或者是聚合函数
order by 排序字段 排序方式 --默认asc，降序用desc
limit 开始的索引，每页查询的条数 -- 分页限定
;
#公式：开始的索引 =（当前的页码 - 1 ）* 每页显示的条数
-- e.g. limit 0，3；每页3条记录，从0条开始显示3条
#3. 修改数据:
update 表名 set 字段名 = 值; -- 修改字段下所有数据的值
update 表名 set 字段名1 = 值1，字段名2 = 值2,...
[where 条件]; -- 指定条件
#4.删除数据 用delete
#删除全部数据记录，只剩表头/字段：
delete from 表名； -- 效率低，一条一条地删，执行很多次。
truncate table 表名; -- 先删除,再创建一个空表。（更高效）
#条件性删除：where
delete from 表名 [where 字段 = ‘’];2. where条件语法总结
- 不等式： >, >=, <, <=, =(一个等号), != 或 <>表示不等于
- 多条件并列： and, between 20 and 30 (包含20和30), or, in (20,25,28)
- null：不能用等式判断，is null, is not null
- 模糊查询：结合like 和占位符
- 单个任意字符：_
- 多个任意字符：%
#e.g.姓名模糊查询
select * from 表 where 字段名 like '苗%'; -- 姓苗
select * from 表 where 字段名 like '_若%'; -- 第二个字
select * from 表 where 字段名 like '___'; -- 三个字；
select * from 表 where 字段名 like '%兰%'; -- 包含兰> Safe-updates模式：where后跟的条件必须是主键id，否则报错。
#方法1:更改mysql数据库模式
set sql_safe_updates = 0;
#方法2: 在where判断条件中跟上主键id
delete from 库名.表名 where [条件+主键条件];> where 和 having 的区别：
- where 分组前的筛选条件，不满足则不会参与分组； having 是分组之后的筛选条件
- where 不可以使用聚合函数查询，having和group by组合使用，分组后查询必须按分组字段或聚合函数
> 聚合函数： 将一列作为整体，列纵向计算操作
- 常用count(),max(),min(),sum(),avg()
- 默认排除null值，可用 ifnull(a,0)先替换后计算结果
- 选择不包含非空的列进行计算,e.g.count（主键) vs count（*）
3. 四种数据约束
- 非空约束 not null：非空
- 唯一约束 unique： 值唯一，但允许多个null值
- 主键约束 primary key：非空且唯一，一张表只能一个，可自动增长赋值
- 外键约束 foreign key：关联两个表，防止一些误操作，保证数据的正确性
- 级联更新： 更新一个表，另一个表也跟着更新
- 级联删除： 删除一个表，另一个表也跟着删除
- 外键值可以为null，但是不可以为不存在的外键值
#1.创建表时添加约束
create table 表名(
主键字段 数据类型 primary key auto_increment,
非空字段 数据类型 not null,
唯一字段 数据类型 unique,
外键字段 数据类型, -- 在该字段后加外键约束
constraint 外键名称, -- 可省略名称,系统会自动分配
foreign key (外键字段),
references 主表名(主表字段));
#2.修改数据约束
alter table 表名 modify 字段 数据类型 primary key auto_increment;
alter table 表名 modify 字段 数据类型 not null;
alter table 表名 modify 字段 数据类型 unique;
alter table 表名 add constraint 外键名称, -- 添加外键
foreign key (外键字段), references 主表名(主表字段)
on update cascade -- 级联更新
on delete cascade; -- 级联删除
#3.删除数据约束 用modify
alter table 表名 modify 非空字段 数据类型; -- 删除非空
alter table 表名 drop index 唯一字段; -- 删除unique
alter table 表名 drop primary key; -- 删除主键
alter table 表名 drop foreign key 外键名称； -- 删除外键
#自动增长：auto_increment 如id， 自动1，2，3，。。。，赋值时可用null代替 （读取上一条记录id的值，然后下一行+1）
alter table 表名 modify 主键 数据类型; -- 仅删除自动增长> 复合主键vs联合主键
- 复合主键：表的主键由两个及以上的字段复合组成，e.g. 姓名+电话
- 联合主键： 多个表的主键联合
#创建复合主键
Create Table 表名(
字段1 varchar(10) not null,
字段2 int not null primary key (字段1, 字段2),
字段3.. );
#创建联合主键
create table 新表名(
主键1 int, -- 表1中的主键
主键2 int, -- 表2中的主键
primary key(主键1,主键2), -- 创建复合主键
foreign key(主键1) references 表1(主键1),
foreign key(主键2) references 表2(主键2)
);4. 多表查询
- 直接查询结果集：笛卡尔积。集合A,B 取这两个集合的所有组成情况 （nA*nB 条数据），需消除无用的数据
- 内连接查询：（思路）从哪些表查询数据？条件是什么？查询哪些字段？
- 隐式内连接：where条件
- 显式内连接：inner join
- 外连接查询
- 左外连接： left join 查询左表所有数据及其交集部分
- 右外连接： right join 查询右表所有数据及其交集部分
# 笛卡尔积
select * from 表1 别名1，表2 别名2; -- 别名方便后续变量标注
# where + 条件
select * from 表1 t1，表2 t2
where t1.'name' = t2.'id'; -- 字段名用 ''标注
# inner join + on + 条件
select 字段列表 from 表1 inner join 表2
on 表1.‘id1’ = 表2.‘id2’;
# left join + on + 条件
select t1.'*',t2.'name' from 表1 t1 left join 表2 t2
on t1.'id'=t2.'dept_id';
# right join + on + 条件
select t1.'*',t2.'name' from 表1 t1 right join 表2 t2
on t1.'id'=t2.'dept_id'; 四、数据库相关知识
1. 多表之间的关系 -- 架构设计器可查看
| 关系 | 举例 | 实现方法 |
| 一对一 | 一一对应，人和身份证 | 在任意一方添加唯一unqiue外键指向另一方的主键 |
| 一对多 | 一个部门有多个员工，一个员工只能对应一个部门 | 在‘多’的一方去添加/建立外键，指向‘一’的主键 |
| 多对多 | 学生可以选很多课，一个课程也可以被很多学生选择 | 需要借助中间表（最少两个字段：两个表的各自主键） 这两个字段作为中间表的外键，分别指向两张表的主键 |
2. 数据库设计范式
- 第一范式(1NF): 每一列都是不肯分割的原子项
- 第二范式(2NF)：在1NF的基础上，非码属性必须完全依赖于候选码，（消除非主属性对主码的部分函数依赖）
- 第三范式(3NF):在2NF的基础上，任何非主属性不依赖于其他非主属性（在2NF的基础上，消除传递函数依赖）
- 巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）
> 可能存在的问题
- 数据冗余多（重复多）
- 数据添加存在问题，添加不完整的数据时，添加不合法
- 删除也存在问题，删除某些数据时把别的数据也删除了
相关概念补充：
- 函数依赖：函数A-->B 如果A属性（属性组）的值可以确定唯一B属性的值，则称B依赖于A, e.g. 学号-->姓名
- 完全函数依赖：函数A-->B, 如果A是一个属性（属性组），要确定B属性的值，需要依赖于A属性（属性组）中**所有**的属性值。e.g.(学号,课程名字)-->该课程分数
- 部分函数依赖：函数A-->B,如果A是一个属性（属性组），要确定B属性的值，只需要依赖于A属性（属性组）中某一些属性值。e.g.（学号，课程名字）-->学生姓名（只要根据学号就可以确定学生姓名）
- 传递函数依赖：函数A-->B,B-->C,如果A是一个属性（属性组），通过A要确定B属性的值，通过B可以唯一确定C的值。e.g.学号--> 所在系名 --> 系主任
- 码：如果在一张表中，一个属性（属性组）可以确定其他所有属性的值，这个属性（属性组）称为码。e.g.码（学号，课程名称）-->名字/系名/系主任/分数
- 主属性：码中的所有属性， 非主属性：除码属性组以外的属性
3. 事务的概念与特征
1. 事务的概念：如果一个操作（包含很多步骤），被事务管理，那么这些操作步骤要么同时成功，要么同时失败。
e.g. 转账操作 （步骤1查询a余额--> 2.a余额 -200 --> 3.b余额+200）
2. 事务提交的两种方式
- 自动提交：mysql数据库中事务默认会自动提交；一条dml（增删改）语句会自动提交一次事务。
- 手动提交：先开启事务，再提交(commit)；oracle默认手动提交
3. 事务四大特征：
- 原子性：是不可分割的最小操作，同时成功或失败（概念）
- 持久性：当事务提交或回滚后，数据库会持久化地保存数据
- 隔离性：多个事务之间，相互独立
- 一致性：事务操作前后，数据总量不变
4. 事务的隔离级别（了解）
- 概念：多个事务之间是隔离的/独立的。但是如果多个事务操作同一批数据，会引发一些问题，设置不同隔离级别就可以解决这些问题。
- 存在问题：
- 脏读：一个事务，读取到另一个事务没有提交的数据
- 虚读/不可重复读：同一个事务中，两次读取到的数据不一样
- 幻读：一个事务操作数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改
| 隔离级别 | 存在的问题 | |
| 1 | read uncommited | 脏读、虚读、幻读 |
| 2 | read commited | 虚读、幻读 |
| 3 | repeatable (MySQL默认） | 幻读 |
| 4 | serializable | 无 |
> 小结：随着安全性越来越高，数据库效率越来越低
#查询事务隔离级别
select @@tx_isolation;
#设置事务隔离级别
set global transaction isolation level 隔离级别;
#查看事务默认提交方式
select @@autocommit; -- 1自动，0手动
#修改事务默认提交方式：
set @@autocommit = 0;
#开启事务并提交
start transaction;
[...操作代码...]
commit; -- 手动提交事务必须
rollback; -- 如出现问题，回滚事务4. 系统自带数据库
- information_schema：基本信息
- mysql：包含user表等
- performance_schema 性能
5. 数据库用户和权限管理
1. 用户管理（操作系统数据库mysql里的user表）
#1.查询用户
use mysql;
show databases;
show tables;
select * from user;
#2.添加新用户和密码
create user '用户名'@'主机名' identified by '密码';
-- 常用主机名'localhost'表示本机，通配符'%'表示任意主机
-- 常用用户名'root'管理员
#3.修改指定用户的密码（两种实现）
update user set password = password('新密码')
where user = '用户名';
set password for '用户名'@'主机名' = password('新密码')
#4.删除用户：
drop user '用户名'@'主机名'; 假如root用户忘记了密码？（mysql）
1. 以管理员身份运行cmd，停止mysql服务 net stop mysql
2. 启动mysql服务，使用无验证方式启动 mysqld --skip-grant-tables 敲回车后即可启动
3. 输入命令修改密码 update user set password = password('new') where user = 'root';
4. 关闭两个窗口，打开任务管理器，手动结束进程
5. 启动mysql服务 net start mysql
6. 使用新密码登录
2. 权限管理
#1.查询权限
show grants for '用户名'@'主机名'; -- grant usage 允许登录
#2.修改权限：
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
e.g. grant select,update,delete
on 数据库名.表名 to '用户名'@'主机名';
#3.先授予所有权限，在任意数据库的所有表
grant all on *.* to '用户名'@'主机名';
#4.撤销指定数据库和表的权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';总结
今天梳理了SQL中非常基础的语法，以后将收集在实战操作中遇到的问题以及解决方法。
938
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
