一、SQL简述
1.SQL的概述
Structure Query Language(结构化查询语言)简称SQL,它被美国国家标准局(ANSI)确定为关系型数据库语言的美国标准,后被国际化标准组织(ISO)采纳为关系数据库语言的国际标准。数据库管理系统可以通过SQL管理数据库;定义和操作数据,维护数据的完整性和安全性。
2.SQL的分类
1、DDL(Data Definition Language) 数据定义语言,用来操作数据库、表、列等; 常用语句:CREATE、 ALTER、DROP
2、DML(Data Manipulation Language) 数据操作语言,用来操作数据库中表里的数据;常用语句:INSERT、 UPDATE、 DELETE
3、DCL(Data Control Language) 数据控制语言,用来操作访问权限和安全级别; 常用语句:GRANT、DENY
4、DQL(Data Query Language) 数据查询语言,用来查询数据 常用语句:SELECT
二、数据库的三大范式
1、第一范式(1NF)是指数据库表的每一列都是不可分割的基本数据线;也就是说:每列的值具有原子性,不可再分割。
2、第二范式(2NF)是在第一范式(1NF)的基础上建立起来得,满足第二范式(2NF)必须先满足第一范式(1NF)。如果表是单主键,那么主键以外的列必须完全依赖于主键;如果表是复合主键,那么主键以外的列必须完全依赖于主键,不能仅依赖主键的一部分。
3、第三范式(3NF)是在第二范式的基础上建立起来的,即满足第三范式必须要先满足第二范式。第三范式(3NF)要求:表中的非主键列必须和主键直接相关而不能间接相关;也就是说:非主键列之间不能相关依赖。
三、数据库的数据类型
1.整数类型
下图列举了 MySQL不同整数类型所对应的字节大小和取值范围而最常用的为INT类型的
2.浮点数类型和定点数类型
3.字符串类型
当数据为CHAR(M)类型时,不管插入值的长度是实际是多少它所占用的存储空间都是M个字节;而VARCHAR(M)所对应的数据所占用的字节数为实际长度加1
4.文本类型
5.日期与时间类型
四、数据库常见命令
1.查看当前所有数据库:
show databases;
2.打开指定数据库:
use 库名;
3.查看其他数据库所有表:
show tables from 库名;
4.创建表:
create table 表名(
列名 列类型,
列名 列类型,
列名 列类型);
5.查看表结构:
desc 表名;
6.查看服务器版本
已登录mysql服务端:
select verson();
未登录到mysql服务端:
mysql --verson 或
mysql --V
五、数据定义语言DDL
1.数据库操作
1.创建数据库,判断不存在,再创建:
create database if not exists 数据库名称;
2.创建数据库,并指定字符集
create database 数据库名称 character set 字符集名;
3.查询某个数据库的字符集:查询某个数据库的创建语句
show create database 数据库名称;
4.修改数据库的字符集
alter database 数据库名称 character set 字符集名称;
5.删除数据库
drop database 数据库名称;
6.判断数据库存在,存在再删除
drop database if exists 数据库名称;
7.查询当前正在使用的数据库名称
select database();
8.使用数据库
use 数据库名称;
2.数据表操作
1、表的创建
create table 表名(
列名1 数据类型1 【字段约束】,
列名2 数据类型2 【字段约束】,
....
列名n 数据类型n 【字段约束】
);
注意:最后一列,不需要加逗号(,)
2.查询当前数据库中所有的表
show tables;
3.查询某个数据库中所有的表
show tables from 数据库名称;
4.查询表结构
desc 表名;
5.修改表名
alter table 表名 rename to 新的表名;
6.修改表的字符集
alter table 表名 character set 字符集名称;
7.添加一列
alter table 表名 add 列名 数据类型;
8.修改列名称 类型
alter table 表名 change 列名 新列别
9.新数据类型;
alter table 表名 modify 列名 新数据类型;
10.删除列
alter table 表名 drop 列名;
11.删除表
drop table if exists 表名;
3.约束
1.非空约束:not null
创建表时添加约束,例如:
CREATE TABLE stu(
id INT,
NAME VARCHAR(20) NOT NULL -- name为非空
);
创建表完后,添加非空约束:
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
删除name的非空约束:
ALTER TABLE stu MODIFY NAME VARCHAR(20);
2.唯一约束:unique,某一列的值不能重复
在创建表时,添加唯一约束:
CREATE TABLE stu(
id INT,
phone_number VARCHAR(20) UNIQUE -- 手机号设置为唯一
);
删除唯一约束:
ALTER TABLE stu DROP INDEX phone_number;
在表创建完后,添加唯一约束:
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
注意:
唯一约束可以有NULL值,但是只能有一条记录为null
3.主键约束:primary key
在创建表时,添加主键约束:
create table stu(
id int primary key,-- 给id添加主键约束
name varchar(20)
);
创建完表后,添加主键:
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
删除主键:
ALTER TABLE stu DROP PRIMARY KEY;
自动增长:
概念:如果某一列是数值类型的,使用 auto_increment 可以来完成值得自动增长
在创建表时,添加主键约束,并且完成主键自增长
create table stu(
id int primary key auto_increment,-- 给id添加主键约束
name varchar(20)
);
删除自动增长
ALTER TABLE stu MODIFY id INT;
添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
4.外键约束:foreign key,让表于表产生关系,从而保证数据的正确性。
在创建表时,可以添加外键:
create table 表名(
....
外键列
【constraint 外键名称】 foreign key (外键列名称) references 主表名称(主表列名称)
);
创建表之后,添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
级联操作:
由于修改和删除比较麻烦,需要先操作从表,再操作主表
添加级联,当修改或者删除主表的数据时,从表也随之变化;
添加级联操作:
ALTER TABLE 表名 ADD CONSTRAINT 外键名称
FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE;
级联更新:ON UPDATE CASCADE
级联删除:ON DELETE CASCADE
六、数据操纵语言DML
1.插入数据
insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
2.删除数据
删除部分数据:
delete from 表名 [where 条件]
删除所有记录
TRUNCATE TABLE 表名;
delete与TRUNCATE区别:
1.truncate不能加where条件,而delete可以加where条件
2.truncate的效率高
3.truncate 删除带自增长的列的表后,如果再插入数据,数据从1开始
4.delete 删除带自增长列的表后,如果再插入数据,数据从上一次的断点处开始
5.truncate删除不能回滚,delete删除可以回滚
3.修改数据
修改单表: update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];
修改多表: update 表1 别名1,表2 别名2 set 字段=新值,字段=新值 where 连接条件 and 筛选条件
七、数据查询语言DQL
1、简单查询
查询所有字段 MySQL命令:
select * from student;
查询指定字段(sid、sname) MySQL命令:
select sid,sname from student;
常数的查询日期标记 MySQL命令:
select sid,sname,'2021-03-02' from student;
从查询结果中过滤重复数据 :
select distinct gender from student;
注:在SELECT查询语句中DISTINCT关键字只能用在第一个所查列名之前。
2、函数
1.聚合函数
1.1、count()
统计表中数据的行数或者统计指定列其值不为NULL的数据个数
select count(*) from student;
1.2、max()
计算指定列的最大值,如果指定列是字符串类型则使用字符串排序运算
select max(age) from student;
1.3、min()
计算指定列的最小值,如果指定列是字符串类型则使用字符串排序运算
select sname,min(age) from student;
1.4、sum()
计算指定列的数值和,如果指定列类型不是数值类型则计算结果为0
select sum(age) from student;
1.5、avg()
计算指定列的平均值,如果指定列类型不是数值类型则计算结果为
select avg(age) from student;
2.其他函数
CONCAT:字符串拼接
SELECT CONCAT(str1,str2,···);
IFNULL :如果是null返回指定值,非null则返回原本值
ISNULL:判断是否为null,是就返回1,否就返回0
3、条件查询
1.使用关系运算符查询
常用的关系运算符如下所示:
例如:
查询年龄等于或大于17的学生的信息 MySQL命令:
select * from student where age>=17;
2.使用IN关键字查询
IN关键字用于判断某个字段的值是否在指定集合中。如果字段的值恰好在指定的集合中,则将字段所在的记录将査询出来。
select * from student where sid in ('S_1002','S_1003');
3.使用BETWEEN AND关键字查询
BETWEEN AND用于判断某个字段的值是否在指定的范围之内。如果字段的值在指定范围内,则将所在的记录将查询出来
select * from student where age between 15 and 18;
4.使用空值查询
在MySQL中,使用 IS NULL关键字判断字段的值是否为空值。请注意:空值NULL不同于0,也不同于空字符串
select * from student where sname is not null;
5.使用AND关键字查询
在MySQL中可使用AND关键字可以连接两个或者多个查询条件。
select * from student where age>15 and gender='male';
6.使用OR关键字查询
在使用SELECT语句查询数据时可使用OR关键字连接多个査询条件。在使用OR关键字时,只要记录满足其中任意一个条件就会被查询出来
select * from student where age>15 or gender='male';
7.使用LIKE关键字查询
MySQL中可使用LIKE关键字可以判断两个字符串是否相匹配
select * from student where sname like 'wang';
7.1 含有%通配的字符串
%用于匹配任意长度的字符串。例如,字符串“a%”匹配以字符a开始任意长度的字符串
select * from student where sname like 'li%';
7.2 含有_通配的字符串
下划线通配符只匹配单个字符,如果要匹配多个字符,需要连续使用多个下划线通配符。例如,字符串“ab_”
select * from student where sname like 'zx__';
8.使用LIMIT限制查询结果的数量
当执行查询数据时可能会返回很多条记录,而用户需要的数据可能只是其中的一条或者几条
select * from student order by age asc limit 3;
4.分组查询
GROUP BY 子句可像切蛋糕一样将表中的数据进行分组,再进行查询等操作。换言之,可通俗地理解为:通过GROUP BY将原来的表拆分成了几张小表。
4.1 GROUP BY和聚合函数一起使用
统计各部门员工个数 MySQL命令:
select count(*), departmentnumber from employee group by departmentnumber;
4.2 GROUP BY和聚合函数以及HAVING一起使用
统计工资总和大于8000的部门 MySQL命令:
select sum(salary),departmentnumber from employee group by departmentnumber having sum(salary
5.排序查询
select
要查询的东西
from
表
where
条件
order by 排序的字段|表达式|函数|别名 【asc|desc】
ASC:升序,默认的
可以按多个字段排序,用' , '隔开
支持表达式,函数,别名
一般放在查询语句的最后,只有limit子句除外
6.连接查询
6.1 笛卡尔乘积
产生条件:
1. 省略连接条件
2. 连接条件无效
3. 所有表中所有行互相连接
解决方案:添加有效筛选条件
6.2内连接
SQL92语法:
SELECT 查询列表
FROM 表名1 别名1 ,表名2 别名2
WHERE 连接条件
AND 筛选条件
GROUP BY 分组列表
HAVING 分组后筛选条件
ORDER BY 排序列表
SQL99语法:
select 字段列表
from 表名1
[inner] join 表名2 on 条件
[inner] join 表名3 on 条件
where 筛选条件
group by 分组条件
having 分组后的筛选条件
order by 排序字段
特点:
1、添加排序、分组、筛选
2、inner可以省略
3、筛选条件放在where后面,连接条件放在on后面,提高分离性,便于阅读
注意:
1. 使用表名前缀在多个表中区分相同的列
2. 在不同表中具有相同列名的列可以用表的别名加以区分
3. 如果使用了表别名,则在select语句中需要使用表别名代替表名
4. 表别名最多支持32个字符长度,但建议越少越好
6.3 外连接
左外连接:
select 字段列表
from 表1
left [outer] join 表2 on 条件
左外连接查询的是左表所有数据以及其交集部分
右外连接:
select 字段列表
from 表1
right [outer] join 表2 on 条件
右外连接查询的是右表所有数据以及其交集部分
外连接特点:
1、外连接的查询结果为主表中的所有记录,如果从表中有和它匹配的,则显示匹配值,否则显示null。
外连接表查询结果=内连接结果+主表中有而从表中没有的记录
2、左外连接:left join 左边的是主表
右外连接:right join 右边的是主表
3、左外和右外交换两个表的顺序,可以实现一样的结果
7.子查询
特点:
1、子查询都放在小括号内
2、子查询可以放在from后面、select后面、where后面、having后面,但一般放在条件的右侧
3、子查询优先于主查询执行,主查询使用了子查询的执行结果
7.1单行子查询
结果集只有一行,一般搭配单行操作符使用:> < = <> >= <=
非法使用子查询的情况:
a、子查询的结果为一组值
b、子查询的结果为空
7.2 多行子查询
结果集有多行,一般搭配多行操作符使用:any、all、in、not in
in: 属于子查询结果中的任意一个就行
any和all往往可以用其他查询代替(MIN和MAX)
8.分页查询
select 字段|表达式,...
from 表
【where 条件
group by 分组字段
having 条件
order by 排序的字段】
limit 起始的条目索引,条目数;
特点:
1.起始条目索引从0开始
2.limit子句放在查询语句的最后
3.公式:select * from 表 limit (page-1)*sizePerPage,sizePerPage
每页显示条目数sizePerPage
要显示的页数 page
9.联合查询
select 字段|常量|表达式|函数 from 表 where 条件 union 【all】
select 字段|常量|表达式|函数 from 表 where 条件 union 【all】
select 字段|常量|表达式|函数 from 表 where 条件 union 【all】
.....
select 字段|常量|表达式|函数 from 表 where 条件
* 特点:
1、多条查询语句的查询的列数必须是一致的
2、多条查询语句的查询的列的类型几乎相同
3、union代表去重,union all代表不去重
八、事务TCL
概念:如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败
1.事务的ACID特征:
原子性
一致性
隔离性
持久性
2.分类
隐式事务
没有明显的开启和结束事务的标志
insert、update、delete语句本身就是一个事务
显式事务
具有明显的开启和结束事务的标志
1. 取消自动提交事务:
set autocommit=0;
2. 开启事务:start transaction;(可选)
3.编写事务中的sql语句(select insert update delete)
语句1
语句2
...
4. 提交或回滚事务:
- commit;
- rollback;
SAVEPOINT:设置保存点,
只能搭配rollback使用
3.隔离级别
当多个事务同时操作同一个数据库的相同数据时,就会产生事务并发问题
3.1并发问题:
脏读
不可重复读
幻读
3.2事务的隔离级别
READ UNCOMMITTED
READ COMMITTED:可以避免脏读
REPEATABLE READ:可以避免脏读、不可重复读和一部分幻读
SERIALIZABLE:可以避免脏读、不可重复读和幻读
注意:隔离级别从小到大安全性越来越高,但是效率越来越低
语法:
set session|global transaction isolation level 隔离级别名;
查看隔离级别:
select @@tx_isolation;或
select @@transaction_isolation;