文章目录
数据库(DataBase,DB)
什么是数据库?
用于存储和管理的仓库。
数据库的特点
1.持久化存储数据(其实数据库就是一个文件系统)
2.方便存储和管理数据
3.使用了统一的方式操作数据库—SQL
一、SQL
1.什么是SQL?
Structured Query Language :结构化查询语言
其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方,称为“方言”。
2.SQL通用语法
1.SQL语句可以单行或多行书写,以分号结尾;
2.可使用空格和缩进来增强语句的可读性;
3.MySQL数据库的SQL语句不区分大小写,关键字建议使用大写;
4.3种注释:
A.单行注释:-- 注释内容 或 # 注释内容(mysql特有)【–后面的空格必须有,#后面的空格可有可无】
B.多行注释:/* 注释 */
二、SQL语句分类
1)DDL(Data Definition Language) 数据定义语言
用来定义数据库对象:数据库,表,列等。关键字:create,drop,alter等
2)DML(Data Manipulation Language)数据操作语言
用来对数据库中表的数据进行增删改。关键字:insert,delete,update等
3)DQL(Data Query Language)数据查询语言
用来查询数据库表的记录(数据)。关键字:select,where等
4)DCL(Data Control Language)数据控制语言(了解)
(一)DDL:操作数据库、表
1.操作数据库:CRUD
-
C(Create):创建
* 创建数据库:create database 数据库名称;
* 创建数据库,判断不存在,再创建:create database if not exits 数据库名称;
* 创建数据库,并指定字符集:create database 数据库名称 character set 字符集名;
* 综合小练习:创建db4数据库,判断是否存在,并指定字符集为gbk:create database if not exits db4 character set gbk; -
R(Retrieve):查询
* 查询所有数据库的名称: show databases;
* 查询某个数据库的字符集-查询某个数据库的创建语句:show create database 数据库名称; -
U(Update):修改
* 修改数据库的字符集:alter database 数据库名称 character set 字符集名称; -
D(Delete):删除
* 删除数据库:drop database 数据库名称;
* 判断数据库存在,存在再删除:drop database if exits 数据库名称; -
使用数据库
* 查询当前正在使用的数据库名称:select database();
* 使用数据库:use 数据库名称;
2.操作数据库表
- C(Create):创建
* ①语法:
create table 表名(
列名1 数据类型1,
列名2 数据类型2,
…
列名n 数据类型n
);
**注意:**最后一列,不需要加逗号(,);
数据库类型:
1.int:整数类型【例:age int】
2.double:小数类型【例:score double(5,2)[表示最多有5位有效数字,小数点后保留两位]】
3.data:日期,只包含年月日,yyyy-MM-dd
4.datatime:日期,包含年月日时分秒 yyyy-MM-dd HH:mm:ss
5.timestamp:时间错类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss【如果将来不给这个字段赋值,或赋值为null,则默认使用当前的系统时间来自动复制】
6.varchar:字符串【例:name varchar(20)[表示姓名最大20个字符]】
②复制表:create table 表名 like 被复制的表名; - R(Retrieve):查询
* 查询某个数据库中所有的表名称:show tables;
* 查询表结构:desc 表名; - U(Update):修改
* 修改表名:alter table 表名 rename to 新的表名;
* 修改表的字符集:alter table 表名 character set 字符集名称;
* 添加一列:alter table 表名 add 列名 数据类型;
* 修改列名称、类型:
①alter table 表名 change 列名 新列名 新数据类型;
②alter table 表名 modify 列名 新数据类型;
* 删除列:alter table 表名 drop 列名; - D(Delete):删除
* drop table 表名;
* drop table if exists 表名;
(二)DML:增删改表中数据
- 添加数据:insert into 表名(列名1,列名2,…,列名n) values(值1,值2,…,值n);
【注意事项:】
* 列名和值要一一对应。
* 如果表名后,不定义列名,则默认给所有列添加值
insert into 表名 values(值1,值2,…,值n);
* 除了数字类型,其他类型需要使用引号(单双都可以)引起来 - 删除数据:delete from 表名[where 条件];
【注意事项:】
* 如果不加条件,则删除表中所有的记录。
* 如果要删除所有的记录,有如下两种实现方式:
①delete from 表名;【不推荐使用,因为有多少条记录就会执行多少次删除操作】
②truncate table 表名;【表示先删除表,然后再创建一张一模一样的表。推荐使用,效率更高】 - 修改数据:update 表名 set 列名1 = 值1,列名2 = 值2,…[where 条件];
【注意事项:】如果不加任何条件,则会将表中所有记录全部修改。
(三)DQL:查询表中记录
select * from 表名;
- 1.语法:
* select 字段列表
* from 表名列表
* where 条件列表
* group by 分组字段
* having 分子之后的条件
* order by 排序
* limit 分页限定 - 2.查询基础:
- ①多个字段的查询:select 字段名1,字段名2,…from 表名;
- 注意:如果查询所有字段,则可以使用*来替代字段列表。
- ②去除重复:distinct
- ③计算列:
- 一般可以使用四则运算计算一些列的值(一般只会进行数值型的计算)
- ifnull(表达式1,表达式2):null参与的运算,计算结果都为null
- 表达式1:哪个字段判断是否为null
- 表达式2:如果该字段为null后的替换值
- ④起别名:as:as也可以省略
- ①多个字段的查询:select 字段名1,字段名2,…from 表名;
- 3.条件查询
- ①where子句后跟的条件
- ②运算符
- ‘>’ ‘<’ ‘<=’ ‘>=’ ‘=’ ‘<>’
- between…and
- in(集合)
- like【模糊查询】
- 占位符
- _:单个任意字符
- %:多个任意字符
- 占位符
- is null(is not null)
- and 或 &&
- or 或 ||
- not 或 !
举例1【模糊查询】:学生表中,姓马的同学有哪些?
select * from student where name like ‘马%’;
举例1【模糊查询】:学生表中,查询姓名是3个字得人?
select * from student where name like ‘___’;
举例1【模糊查询】:学生表中,查询姓名中包含马的人?
select * from student where name like ‘%马%’;
(四)DQL:查询语句
1.排序查询
- 语法:order by 子句
- order by 排序字段1 排序方式1, 排序字段2 排序方式2…
- 排序方式:
- ASC:升序,默认的
- DESC:降序
- 注意:
- 如果有多个排序条件,则当前边的条件值一样时,才回判断第二条件
【例如:学生表中,按照数学成绩排名,如果数学成绩一样,则按照英语成绩排名?】
select * from student order by math ASC,english DESC;
- 如果有多个排序条件,则当前边的条件值一样时,才回判断第二条件
2.聚合函数
聚合函数:将一列数据作为一个整体,进行纵向的计算。
-
count:计算个数
- 一般选择非空的列------主键
- count(*)【不推荐】
-
max:计算最大值
-
min:计算最小值
-
sum:计算和
-
avg:计算平均值
-
注意:聚合函数的计算,会排除null值。解决方案有两种:
- ①选择不包含非空的列进行计算
- ②IFNULL函数
3.分组查询
- 语法:group by 分组字段;
- 注意:
- 分组之后查询的字段:分组字段、聚合函数
- where 和 having 的区别?
- where在分组之前进行限定,如果不满足条件,则不参与分组。 having在分组之后进行限定,如果不满足结果,则不会被查询出来。
- where后不可以跟聚合函数,having可以进行聚合函数的判断。
【实例】
按照性别分组,分别查询男、女同学的平均分
SELECT sex,AVG(math) FROM student GROUP BY sex;
按照性别分组,分别查询男、女同学的平均分和人数
SELECT sex,AVG(math),COUNT(id) FROM student GROUP BY sex;
按照性别分组,分别查询男、女同学的平均分和人数。要求:分数低于70分的人,不参与分组
SELECT sex,AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex;
按照性别分组,分别查询男、女同学的平均分和人数。要求:分数低于70分的人,不参与分组,并且分组之后,人数要大于2个人
SELECT sex,AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;
4.分页查询
- 语法:limit 开始的索引,每页查询的条数
- 公式:开始的索引 = (当前的页码 - 1)* 每页显示的条数
【实例:每页显示3条记录?】
SELECT * FROM student LIMIT 0,3; -----第一页
SELECT * FROM student LIMIT 3,3; -----第二页
SELECT * FROM student LIMIT 6,3; -----第三页 - limit 是一个 MySQL“方言”
三、约束
- 概念:对表中的数据进行限定,保证数据的正确性、有效性和完整性。
- 分类:
- ①主键约束:primary key
- ②非空约束:not null
- ③唯一约束:unique
- ④外键约束:foreign key
- 非空约束 not null
- 创建表时添加约束
【
例:
CREATE TABLE stu(
id INT,
NAME VARCHAR(20) NOT NULL -----非空
);
例:–删除name的非空约束
ALTER TABLE stu MODIFY name VARCHAR(20);
】 - 创建表完后,添加非空约束
ALTER TABLE stu MODIFY name VARCHAR(20) NOT NULL;
- 创建表时添加约束
- 唯一约束:unique,值不能重复
- 创建表时,添加唯一约束
- CREATE TABLE stu(
id INT,
NAME VARCHAR(20) UNIQUE
);
- CREATE TABLE stu(
- 删除唯一约束:
- ALTER TABLE stu MODIFY name VARCHAR(20);【这是错误的】
- ALTER TABLE stu DROP INDEX name;【这是正确的】
- 在创建表后,添加唯一约束
- ALTER TABLE stu MODIFY name VARCHAR(20) UNIQUE;
- 注意:mysql中,唯一约束限定的列的值可以有多个null
- 创建表时,添加唯一约束
- 主键约束:primary key
- 注意:
- ①含义:非空且唯一
- ②一张表只能有一个字段为主键
- ③主键就是表中记录的唯一标识
- 在创建表时,添加主键约束
- CREATE TABLE stu(
id INT PRIMARY KEY,–给id添加主键约束
name VARCHAR(20)
);
- CREATE TABLE stu(
- 删除主键:
- ALTER TABLE stu MODIFY id int;【这是错误的!!】
- ALTER TABLE stu DROP PRIMARY KEY;
- 创建完表后,添加主键
- ALTER TABLE stu MODIFY name VARCHAR(20) NOT NULL;
- 自动增长
- ①概念:如果某一列是数值类型的,使用AUTO_INCREMENT可以来完成值的自动增长
- ②在创建表时,
- CREATE TABLE stu(
id INT PRIMARY KEY AUTO_INCREMENT,–给id添加主键约束
name VARCHAR(20)
);
- CREATE TABLE stu(
- ③删除自动增长
ALTER TABLE stu MODIFY id INT; - ④创建完表后,添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
- 注意:
- 外键约束:foreign key,让表与表产生关系,从而保证数据的正确性
- 在创建表时,添加外键
- 语法:
CREATE TABLE 表名(
…
外键列
CONSTRAINT 外键名称 FOREIGN KEY 外键列名称 REFERENCES 主表名称(主表列名称)
);
- 语法:
- 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称; - 创建完表后,添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键列名称)REFERENCES 主表名称(主表列名称); - 级联操作
- 添加级联操作:级联更新【需要在添加外键的时候,设置级联更新】
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键列名称)REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE; - 设置级联删除
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键列名称)REFERENCES 主表名称(主表列名称) ON DELETE CASCADE;
- 添加级联操作:级联更新【需要在添加外键的时候,设置级联更新】
- 在创建表时,添加外键
四、多表之间的关系
1.分类
- 一对一(了解):
- 如:人和身份证
- 分析:一个人只有一个身份证,一个身份证只能对应一个人
- 一对多(多对一):
- 如:部门和员工
- 分析:一个部门有多个员工,一个员工只能对应一个部门
- 多对多:
- 如:学生和课程
- 分析:一个学生可以选择很多门课程,一个课程也可以被很多学生选择
2.实现关系
- 一对多
- 例如:部门和员工
- 实现方式:在多的一方建立外键,指向一的一方的主键
- 例如:部门和员工
- 多对多
- 例如:学生和课程
- 实现方式:多对多关系实现需要借助第三张中间表。中间表至少包含两个字段,这两个字段作为第三张表的外键,分别指向两张表的主键。
- 例如:学生和课程
- 一对一(了解):
- 如:人和身份证
- 实现方式:一对一关系实现,可以在任意一方添加唯一的外键指向另一方的主键。
- 一般情况下,如果出现一对一的关系,我们会将他们合并成一张表
- 如:人和身份证
五、范式
- 概念:设计数据库时,需要遵循的一些规范。要遵循后边的范式要求,必须先遵循前边的所有范式要求。
- 分类:
- 第一范式(1NF):每一列都是不可分割的原子数据项。
- 存在的问题:
① 存在非常严重的数据冗余(重复);
② 数据添加存在问题,数据不合法;
③ 删除数据存在问题。
- 存在的问题:
- 第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码的部分函数依赖)。
- 几个概念:
- ① 函数依赖:A–>B,如果通过A属性(属性组)的值,可以确定唯一的B属性的值,则称B依赖于A。【例如:学号–>姓名,(学号,课程名称)–>分数】
- ② 完全函数依赖:A–>B,如果A是一个属性组,则B属性值的确定需要依赖于A属性组中所有的属性值。【例如:(学号,课程名称)–>分数】
- ③ 部分函数依赖:A–>B,如果A是一个属性组,则B属性值的确定只需要依赖于A属性组中的某一些属性值即可。【例如:(学号,课程名称)–>姓名】
- ④ 传递函数依赖:A–>B,B–>C,如果通过A属性(属性组)的值,可以确定唯一B属性的值,再通过B属性(属性组)的值可以确定唯一C属性的值,则称C传递函数依赖于A。
- ⑤ 码:如果在一张表中,一个属性或属性组,被其他所有属性所完全依赖,则称这个属性(属性组)为该表码。
- 主属性:码属性组中的所有属性
- 非主属性:除码属性组的属性
- 存在的问题:
- 数据添加存在问题,数据不合法;
- 删除数据存在问题。
- 几个概念:
- 第三范式(3NF):在2NF基础上,任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)。
- 第一范式(1NF):每一列都是不可分割的原子数据项。
六、数据库的备份与还原
- ① 命令行:
- 语法:
- 备份:mysqldump -u用户名 -p密码名 > 保存的路径
- 还原:
- 1.登录数据库
- 2.创建数据库
- 3.使用数据库
- 4.执行文件,source 文件路径
- 语法:
- ②图形化工具