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中非常基础的语法,以后将收集在实战操作中遇到的问题以及解决方法。