目录
3.1、DDL(Data Definition Language,数据定义语言)
3.2、DML(Data Manipulation Language,数据操作语言)
3.3、DQL(Data Query Language,数据查询语言)
3.4、DCL(Data Control Language,数据控制语言)
1、数据库相关概念
数据库(DataBase,DB):存储数据的仓库,数据是有组织地进行存储。
数据库管理系统(DataBase Management System,DBMS):管理数据库的大型软件。
常见的关系型数据库管理系统:
常见的关系型数据库管理系统 | 描述 |
---|---|
Oracle | 收费的大型数据库,Oracle公司的产品 |
MySQL | 开源免费的中小型数据库,后来Sun公司收购了MySQL,而Sun公司又被Oracle收购 |
SQL Server | MicroSoft公司收费的中型的数据库,C#、.net等语言常使用 |
PostgreSQL | 开源免费的中小型数据库 |
DB2 | IBM公司的大型收费数据库产品 |
SQLite | 嵌入式的微型数据库,如:作为Android内置数据库 |
MariaDB | 开源免费的中小型数据库 |
SQL(Structured Query Language,结构化查询语言):操作关系型数据库的编程语言,定义操作所有关系型数据库的统一标准。
- 数据库:文件夹;
- 表:文件;
- 数据:文件中存储的数据。
2、MySQL数据库
2.1、MySQL的下载和安装
下载:点击链接 https://downloads.mysql.com/archives/community/
2.2、MySQL的配置
1、添加环境变量
环境变量里面有很多选项,这里我们只用到Path这个参数。为什么在初始化的开始要添加环境变量呢?在CMD中输入一个可执行程序的名字,Windows会现在环境变量中的Path所指的路径中寻找以便,如果找到了就直接执行,没找到就在当前工作目录找,如果还找不到,就报错。我们添加环境变量的目的就是能够在任意情况下直接调用MySQL中的相关程序而不用总是修改工作目录,大大简化了操作。
右键此电脑 -> 属性 -> 高级系统设置 -> 高级 -> 环境变量 -> 在系统变量下面选择新建 -> 变量名为 MYSQL_HOME,变量值为 D:\mysql-5.7.24-winx64(即MySQL安装路径)-> 在系统变量中找到Path,选中后点击编辑 -> 新建 ->输入 %MYSQL_HOME%\bin -> 依次点击确定完成环境变量的配置。
2、新建配置文件
新建一个文本文件,内容如下:
[mysql]
default-character-set=utf8
[mysqld]
character-set-server=utf8
default-storage-engine=INNODB
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
上面的代码意思是配置数据库的默认编码集为utf-8和默认存储引擎为INNODB。
把上面的文本文件另存为,在保存类型里选所有文件(*.*),文件名叫my.ini,存放的路径为MySQL的根目录的bin文件夹下。
3、初始化MySQL
在CMD中输入 mysqld --initialize-insecure ,回车,稍微等待一会,如果没有出现报错信息,则证明data目录初始化没问题,此时再查看MySQL目录下已经有data目录生成。
如果出现错误:(Errcode: 13 - Permission denied) ,是由于权限不足导致的,去C:\Windows\System32 下以管理员方式运行cmd.exe 。
4、注册MySQL服务
在CMD中输入mysqld -install ,回车。
安装完MySQL服务后,你的计算机可以称为MySQL服务器了,可以提供数据存储管理的服务。
5、启动MySQL服务
在CMD中输入 net start mysql ,启动MySQL服务。
停止mysql服务:net stop mysql
或者Win+R,输入services.msc,手动开启MySQL服务。
6、修改默认账户密码
在CMD中输入 mysqladmin -u root password 1234 ,这里的1234就是指默认管理员(即root账户)的密码,可自行修改成你喜欢的。
至此,MySQL 5.7 解压版安装完毕!
2.3、登录MySQL
在CMD中输入 mysql -uroot -p1234 ,回车,出现下图且左下角为mysql>,则说明登录成功。
退出mysql:输入 exit 或 quit
登录参数:mysql -u用户名 -p密码 -h要连接的mysql服务器的ip地址(默认127.0.0.1) -P端口号(默认3306)。
2.4、卸载MySQL
如果你想卸载MySQL,也很简单。打开CMD:
1、输入net stop mysql ,回车;
2、再输入 mysqld -remove mysql ,回车;
3、最后删除MySQL目录及相关的环境变量。
2.5、MySQL数据模型
关系型数据库:关系型数据库是建立在关系模型基础上的数据库,简单说,关系型数据库是由多张能互相连接的二维表组成的数据库。
优点:
- 都是使用表结构,格式一致,易于维护;
- 使用通用的SQL语言操作,使用方便,可用于复杂查询;
- 数据存储在磁盘中,安全。
3、SQL
什么是SQL?其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方,称为“方言”。
SQL通用语法:
- SQL语句可以单行或多行书写,以分号结尾。
- 可通过空格和缩进来增强语句的可读性。
- MySQL数据库的SQL语句的SQL语句不区分大小写,关键字建议使用大写。
- 注释:① 单行注释: -- 注释内容 或 # 注释内容;② 多行注释: /* 注释*/
SQL的分类:
- DDL(Data Definition Language)数据定义语言:用来定义数据库对象:数据库,表,列等。关键字:create,drop,alter等;
- DML(Data Manipulation Language)数据操作语言:用来对数据库中表的数据进行增删改。关键字:insert,delete,update等;
- DQL(Data Query Language)数据查询语言:用来查询数据库中表的记录(数据)。关键字:select,where等;
- DCL(Data Control Language)数据控制语言(了解):用来定义数据库的访问权限和安全级别,及创建用户。关键字:GRANT,REVOKE等。
客户端图形化工具:SQLYog
姓名(Name):cr173
序列号(Code):8d8120df-a5c3-4989-8f47-5afc79c56e7c
姓名(Name):cr173
序列号(Code):59adfdfe-bcb0-4762-8267-d7fccf16beda
姓名(Name):cr173
序列号(Code):ec38d297-0543-4679-b098-4baadf91f983
备注:最好关闭软件的自动更新功能
3.1、DDL(Data Definition Language,数据定义语言)
3.1.1、操作数据库:
1、C(Create):创建数据库;
- 创建数据库:create database 数据库名称;
- 创建数据库,判断是否存在,如果不存在就创建:create database if not exists 数据库名称;
- 创建数据库,并指定字符集:create database 数据库名称 character set 字符集名;
2、R(Retrieve):查询数据库;
- 查询所有数据库的名称:show databases;
- 查看某个数据库的字符集:查询某个数据库的创建语句:show create database 数据库名称;
3、U(Update):修改数据库;
- 修改数据库的字符集:alter database 数据库名称 character 字符集名;
4、D(Delete):删除数据库;
- 删除数据库:drop database 数据库名称;
- 判断数据库存在,存在再删除:drop database if exists 数据库名;
5、使用数据库。
- 查询当前正在使用的数据库名称:select database();
- 使用数据库:use 数据库名称;
3.1.2、操作表
1、C(Create):创建表;
- 创建表:
创建表的语法: create table 表名( 列名1 数据类型1, 列名2 数据类型2, ... 列名n 数据类型n ); 范例: create table student( id int, name varchar(32), score double(4,1), birthday date, insert_time timestamp ); 注意:最后一列不需要加逗号 数据类型: int:整数类型 如 age int, double:小数类型 如 score double(5,2) #小数最多有5位,小数点后有2位 date:日期 只包含年月日,yyyy-MM-dd datetime:日期 包含年月日时分秒,yyyy-MM-dd HH:mm:ss timestamp:时间戳 包含年月日时分秒,yyyy-MM-dd HH:mm:ss。如果将来不给这个字段赋值,或赋值为null,则默认使用当前的系统时间,来自动赋值 varchar:字符串 如:name varchar(20) #姓名最大20个字符,zhangsan有8个字符,张三有2个字符
- 复制表:create table 表名 like 被复制的表名;
2、R(Retrieve):查询表;
- 查询某个数据库中所有的表名称:show tables;
- 查询表结构:desc 表名;
3、U(Update):修改表;
- 修改表名:alter table 表名 rename 新的表名;
- 修改表的字符集:alter table 表名 character set 字符集名;
- 添加一列:alter table 表名 add 列名 数据类型;
- 修改列名称、类型:alter table 表名 change 列名 新列名 新数据类型;
- 修改列类型:alter table 表名 modify 列名 新数据类型;
- 删除列:alter table 表名 drop 列名;
4、D(Delete):删除表;
- 删除某个表:drop table 表名;
- 如果删除的表存在,则删除:drop table if exists 表名;
- 删除表,然后再创建一个一模一样的空表:truncate table 表名;
3.2、DML(Data Manipulation Language,数据操作语言)
DML用来增删改表中数据。
1、添加数据
语法:insert into 表名(列名1,列名2,...,列名n) values(值1,值2,...,值n);
注意:① 列名和值要一一对应;② 如果表名后不定义列名,则默认给所有列添加值(insert into 表名 values(值1,值2,...,值n););③ 除了数字类型,其它类型需要使用引号引起来(单引号、双引号都可以)。
2、删除数据
语法:delete from 表名 [where 条件];
注意:① 如果不加条件,则删除表中所有记录;② 如果要删除所有记录:delete from 表名;(不推荐使用,有多少条记录就会执行多少次删除操作) truncate table 表名;(推荐使用,先删除表,再创建一张一样的表,效率更高)
3、修改数据
语法:update 表名 set 列名1=值1,列名2=值2,...,列名n=值n [where 条件];
注意:如果不加任何条件,则会将表中所有记录全部修改。
3.3、DQL(Data Query Language,数据查询语言)
3.3.1、查询的语法:
select
字段列表
from
表名列表
where
条件列表
group by
分组字段
having
分组之后的条件
order by
排序
limit
分页限定;
3.3.2、基础查询
1、多个字段的查询:语法:select 字段名1,字段名2,...,字段名n from 表名;
注意:如果查询所有字段,则可以使用 * 来替代字段列表。
2、去除重复:语法:select distict 字段名1,字段名2,...,字段名n from 表名;
3、计算列:语法:select 字段名1 as 别名1,字段名2 as 别名2,字段名3,字段名1+字段名3 as 结果别名 from 表名;
注意:一般可以使用四则运算计算一些列的值(一般只会进行数值型的计算);② ifnull(表达式1,表达式2):表达式1:哪个字段需要判断是否为null;表达式2:如果该字段为null后的替换值。
4、起别名:语法:select 字段名1 as 别名 from 表名; (as也可以省略)
代码演示:
SELECT * FROM student; #查询所有列表
SELECT NAME,age FROM student; #查询姓名和年龄
SELECT address FROM student; #查询地址
SELECT DISTINCT address FROM student; #去除重复的结果集
SELECT DISTINCT NAME,address FROM student;
SELECT NAME,math,english,math+english FROM student; #计算math和english的分数之和
#如果有null参与的运算,计算结果都为null
SELECT NAME,math,english,math+IFNULL(english,0) FROM student;
#起别名
SELECT NAME,math 数学,english 英语,math+IFNULL(english,0) AS 总分 FROM student;
3.3.3、条件查询
1、语法:select * from 表名 where 条件;
2、运算符
> 、 < 、 <= 、 >=、 =、 <>
BETWEEN ... AND ... IN(集合)
LIKE模糊查询:占位符可以是:
_:单个任意字符
%:多个任意字符
IS NULL and 或 && or 或 || not 或 !
代码演示:
#查询年龄大于20岁的人
SELECT * FROM student WHERE age > 20;
#查询年龄等于20岁的人
SELECT * FROM student WHERE age = 20;
#查询年龄不等于20岁的人
SELECT * FROM student WHERE age <> 20;
#查询年龄大于等于20,小于等于30的人
SELECT * FROM student WHERE age >= 20 AND age <= 30;
SELECT * FROM student WHERE age BETWEEN 20 AND 30; #包含20和30
#查询年龄等于22,18,25岁的人
SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25;
SELECT * FROM student WHERE age IN (22,18,25);
#查询英语成绩为null的人
SELECT * FROM student WHERE english = NULL; #不对,因为null值不能使用= 和 !=判断
SELECT * FROM student WHERE english IS NULL;
#查询英语成绩不为null的人
SELECT * FROM student WHERE english IS NOT NULL;
-- 模糊查询 ----------------------------------
#查询姓马的有哪些 like
SELECT * FROM student WHERE NAME LIKE '马%';
#查询姓名中第二个字是化的人
SELECT * FROM student WHERE NAME LIKE '_化%';
#查询姓名是三个字的人
SELECT * FROM student WHERE NAME LIKE '___';
#查询姓名中包含德的人
SELECT * FROM student WHERE NAME LIKE '%德%';
3.3.4、排序查询
1、语法:order by 排序字段1 排序方式1, 排序字段2 排序方式2,...;
2、排序方式:① ASC:升序(默认);② DESC:降序。
3、注意:如果有多个排序条件,则当前边的条件值一样时,才会判断第二条件。
代码演示:
SELECT * FROM student; #查询所有列表
-- 排序 ----------------------------------------
#按照数学成绩降序排列
SELECT * FROM student ORDER BY math DESC;
#按照数学成绩升序排列
SELECT * FROM student ORDER BY math ASC;
#按照数学成绩排名,如果数学成绩一样,则按照英语成绩排名
SELECT * FROM student ORDER BY math ASC, english ASC;
3.3.5、聚合函数
聚合函数:将一列数据作为一个整体,进行整体的计算。
- count:计算个数。一般选择非空的列(主键)。使用count(*)表示只要这行数据所有列都不为NULL就不会排除。
- max:计算最大值
- min:计算最小值
- sum:计算总和
- avg:计算平均值
注意:聚合函数的计算,会排除null值。解决方案:① 选择不包含非空列进行计算;② 利用IFNULL()函数。
代码演示:
SELECT * FROM student; #查询所有列表
-- 聚合函数 ----------------------------------------
# 查询学生个数
SELECT COUNT(NAME) FROM student;
SELECT COUNT(english) FROM student; #排除了none的值
SELECT COUNT(IFNULL(IFNULL(english,0))) FROM student; #解决方案
# 查询数学最高分
SELECT MAX(math) FROM student;
# 查询数学最低分
SELECT MIN(math) FROM student;
# 查询数学平均分
SELECT AVG(math) FROM student;
# 查询英语成绩总和
SELECT SUM(english) FROM student;
3.3.6、分组查询
1、语法:group by 分组字段;
2、注意:① 分组之后查询的字段:分组字段、聚合函数;② where和having 的区别:where在分组之前进行限定,如果不满足条件,则不参与分组;having在分组之后进行限定,如果不满足结果则不会被查询出来。where后不可以跟聚合函数,having可以进行聚合函数的判断。
代码演示:
SELECT * FROM student; #查询所有列表
-- 分组查询 ----------------------------------------
# 按照性别分组,分别查询男、女同学平均分
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;
SELECT sex,AVG(math),COUNT(id) AS 人数 FROM student WHERE math>70 GROUP BY sex HAVING 人数 > 2;
3.3.7、分页查询
1、语法:limit 开始的索引,每页查询的条数;
2、公式:开始的索引=(当前的页码 -1)*每页的条数。
3、分页操作是一个“方言”,不同的数据库分页操作不一样。上面的分页操作limit是一个MySQL“方言”。
代码演示:
SELECT * FROM student; #查询所有列表
-- 分页查询 ----------------------------------------
# 每页显示三条记录
SELECT * FROM student LIMIT 0, 3; #第一页
SELECT * FROM student LIMIT 3, 3; #第二页
# 公式:开始的索引=(当前的页码-1)*每页显示的条数
SELECT * FROM student LIMIT (i-1)*3, 3; # 第i页
3.3.8、约束
1、概念:约束是对表中的数据进行限定,保证数据的正确性、有效性和完整性。
2、分类:
- 主键约束:primary key,表示该列非空且唯一。注意:主键就是表中记录的唯一标识,一张表只能有一个字段为主键。
-- 创建表时添加主键约束 CREATE TABLE stu( id INT PRIMARY KEY, #给id添加主键约束 NAME VARCHAR(20) #姓名 ); -- 删除主键约束 ------------------------ ALTER TABLE stu DROP PRIMARY KEY #正确方式 # alter table stu modify id int; #错误方式 -- 创建表后添加主键约束 ---------------- ALTER TABLE stu MODIFY id INT PRIMARY KEY; SELECT * FROM stu;
自动增长:如果某一列是数值类型的,使用 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; INSERT INTO stu VALUES(NULL,'张三'); #自动得到id(增加1) INSERT INTO stu VALUES(10,'李四'); #手动设置id值 INSERT INTO stu VALUES(NULL,'王五'); #再次使用自动增长则id值为11 SELECT * FROM stu;
- 非空约束:nut null,某一列的值不能为null。
-- 创建表时添加非空约束 CREATE TABLE stu( id INT, NAME VARCHAR(20) NOT NULL #name为非空 ); -- 删除name的非空约束 -------------- ALTER TABLE stu MODIFY NAME VARCHAR(20); -- 创建表完成后,添加非空约束 ------ ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL; SELECT * FROM stu;
- 唯一约束:unique,某一列的值不能重复。注意: 唯一约束可以有null值,但是只能有一条记录为null。
-- 创建表时添加唯一约束 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); #错误方式 -- 创建表后添加唯一约束 ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE; SELECT * FROM stu;
- 外键约束:foreign key,让表与表之间产生关系,从而保证数据的正确性
① 在创建表时,可以添加外键。 语法: create table 表名( ... 外键列 constraint 外键名称 foreign key 外键列名称 references 主表名称(主表列名称) ); ② 删除外键 alter table 表名 drop foreign key 外键名称; ③ 创建表之后,添加外键 alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称) ④ 级联操作 1、添加级联操作 语法:alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称)on update cascade on delete cascade; 2、分类: 级联更新:on update cascade 级联删除:on delete cascade 代码演示: USE DATABASE mydatabase; CREATE TABLE emp ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(30), age INT, dep_name VARCHAR(30), dep_location VARCHAR(30) ); INSERT INTO emp (id,NAME,age,dep_name,dep_location) VALUES (1,'张三',20,'研发部','广州'), (2,'李四',21,'研发部','广州'), (3,'王五',20,'研发部','广州'), (4,'老王',20,'销售部','深圳'), (5,'大王',22,'销售部','深圳'), (6,'小王',18,'销售部','深圳'); DELETE FROM emp; SELECT * FROM emp; -- 数据有冗余,解决方案:表的拆分 -- 创建部门表(id,dep_name,dep_location) -- 一方,主表 CREATE TABLE department( id INT PRIMARY KEY AUTO_INCREMENT, dep_name VARCHAR(20), dep_location VARCHAR(20) ); -- 创建员工表(id,name,age,dep_id) -- 多方,从表 CREATE TABLE employee( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), age INT, dep_id INT, #外键对应主表的主键 CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id) ); -- 添加两个部门 INSERT INTO department VALUES (NULL,'研发部','广州'), (NULL,'销售部','深圳'); -- 添加员工 INSERT INTO employee (NAME, age, dep_id) VALUES ('张三',20,1), ('李四',21,1), ('王五',20,1), ('老王',20,2), ('大王',22,2), ('小王',18,2); -- 查询部门信息 SELECT * FROM department; -- 查询员工信息 SELECT * FROM employee; -- 删除外键 ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk; -- 添加外键,设置级联更新 ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE;
3.3.9、数据库的设计
1、多表之间的关系
- 一对一:如人和身份证,一个人只有一个身份证,一个身份证只能对应一个人。
实现方式: 一对一关系的实现,可以在任意一方添加外键(外键唯一)指向另一方的主键。
- 一对多(多对一):如部门和员工,一个部门有多个员工,一个员工只能对应一个部门。
实现方式: 在多的一方建立外键,指向一的一方的主键
- 多对多:如学生和课程,一个学生可以选很多门课程,一个课程也可以被很多学生选择。
实现方式: 多对多关系的实现需要借助第三张中间表。中间表至少包含两个字段,这两个字段作为中间表的外键,分别指向两张表的主键。 联合主键: create table table1 ( 字段名1 类型1, 字段名2 类型2, primary key (字段名1,字段名2) 字段1和字段2不能同时相同。 );
2、数据库设计的范式
范式:设计数据库时,需要遵循的一些规范。设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式成递次规范,越高的范式数据库冗余越小。
目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(SNF,又称完美范式)。要遵循后边的范式要求,必须先遵循前边的所有范式要求。
第一范式(1NF):每一列都是不可分割的原子数据项。
第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于码(在1NF基础上消除非主属性对主码的部分函数依赖)。
几个概念:
1、函数依赖:
A --> B,如果通过A属性(属性组)的值,可以确定唯一B属性的值,则称B依赖于A。
例如:学号 --> 姓名;(学号,课程名称) --> 分数
2、完全函数依赖
A --> B,如果A是一个属性组,则B属性值的确定需要依赖于A属性组中所有的属性值。
例如:(学号,课程名称)--> 分数
3、部分函数依赖
A --> B,如果A是一个属性组,则B属性值的确定只需要依赖于A属性组中的某一些值即可。
例如:(学号,课程名称)--> 姓名
4、传递函数依赖
A --> B,B --> C,如果通过A属性(属性组)的值,可以确定唯一B属性的值,再通过B属性(属性组)的值可以确定唯一的C属性的值,则称C传递函数依赖于A。
例如:学号 --> 系名,系名 --> 系主任。
5、码
如果在一张表中,一个属性或属性组,被其它所有属性所完全依赖,则称这个属性(属性组)为该表的码。
例如:该表中码为:(学号,课程名称)
主属性:码属性组中的所有属性。
非主属性:除去码属性组的其它属性。
第三范式(3NF):在2NF 的基础上,任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)。
3.3.10、数据库的备份和还原
1、方式1:命令行方式
备份的语法: mysqldump -u用户名 -p密码 数据库名 > 保存的路径\xxx.sql;
还原:① 登录数据库;② 创建数据库;③ 使用数据库;④ 执行文件。source 文件路径;
2、方式2:图形化工具
备份:右键数据库名 -> 备份/导出 -> 备份数据库,转储到SQL... -> 选择路径 -> 导出。
还原:右键root@LocalHost -> 执行SQL脚本 -> 选择文件 -> 执行。
3.3.11、多表查询
1、查询语法
语法:
select
列名列表
from
表名列表
where
...
笛卡尔积:有两个集合A,B,取这两个集合的所有组成情况。
2、多表查询的分类
1)内连接查询:
① 隐式内连接:使用where条件来消除无用数据。
SELECT * FROM employee,department; # 笛卡尔积
-- 隐式内连接
-- 查询所有员工信息和对应的部门信息
SELECT * FROM employee,department
WHERE employee.`dep_id`=department.`id`;
-- 查询员工表的名称,年龄,部门表的名称
SELECT t1.name,t1.age,t2.dep_name
FROM employee t1,department t2
WHERE t1.`dep_id`=t2.`id`;
② 显式内连接:语法:select 字段列表 from 表名 [inner] join 表名2 on 条件;
-- 显式内连接
SELECT * FROM employee
INNER JOIN department #inner可省略
ON employee.`dep_id`=department.`id`;
内连接查询注意事项:① 从那些表中查询数据;② 条件是什么;③ 查询哪些字段。
2)外连接查询:
① 左外连接:语法:select 字段列表 from 表1 left [outer] join 表2 on 条件;
左外连接查询的是左表所有数据以及其交集部分。
-- 左外连接
#查询所有员工信息,如果员工有部门,则查询部门的名称,没有部门,则不显示部门名称
SELECT t1.name,t1.age,t2.dep_name
FROM employee t1
LEFT JOIN department t2
ON t1.`dep_id`=t2.`id`;
② 右外连接:语法:select 字段列表 from 表1 right [outer] join 表2 on 条件;
右外连接查询的是右表所有数据以及其交集部分。
-- 右外连接
#查询所有员工信息,如果员工有部门,则查询部门的名称,没有部门,则不显示部门名称
SELECT t1.name,t1.age,t2.dep_name
FROM employee t1
RIGHT JOIN department t2
ON t1.`dep_id`=t2.`id`;
3)子查询:
子查询概念:查询中嵌套查询,称嵌套查询为子查询。
子查询的不同情况:
子查询的结果是单行单列的:子查询可以作为条件,使用运算符去判断。
-- 子查询
#查询年龄最大的员工信息
SELECT * FROM employee
WHERE age=(
SELECT MAX(age)
FROM employee
);
子查询的结果是多行单列的:子查询可以作为条件,使用运算符in来判断。
-- 子查询
#查询研发部和销售部所有员工信息
SELECT * FROM employee e
WHERE e.`dep_id` IN (
SELECT d.id FROM department d
WHERE dep_name='研发部' OR dep_name='销售部'
);
子查询的结果是多行多列的:子查询可以作为一张虚拟表参与查询。
-- 子查询
#查询员工的年龄为20岁的员工的姓名和部门信息
SELECT t1.`dep_name`,t2.name
FROM department t1, (
SELECT * FROM employee
WHERE employee.`age`=20
) t2
WHERE t1.`id`= t2.dep_id;
#普通方式
SELECT d.`dep_name`,e.`name`
FROM department d, employee e
WHERE d.`id`=e.`dep_id` AND e.`age`=20;
3.3.12、事务
1、事务的基本介绍
1)事务的概念:如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败。
2)操作:
① 开启事务:start transaction;
② 回滚:rollback;
③ 提交:commit;
SELECT * FROM account;
-- 开启事务
START TRANSACTION; #接下来的数据只会临时发生变化
-- 张三给李四转账500元
#1、张三账户-500
UPDATE account SET balance = balance - 500 WHERE NAME = '张三';
#2、李四账户+500
UPDATE account SET balance = balance + 500 WHERE NAME = '李四';
-- 发现执行没有问题,提交事务
COMMIT;
-- 发现出问题了,回滚事务
ROLLBACK;
事务提交有两种方式:① 自动提交(MySQL的默认提交方式):MySQL就是自动提交的,一条DML(增删改)语句会自动提交一次事务;② 手动提交(Oracle的默认提交方式):需要先开启事务,再提交。
-- 查看MySQL中修改事务的默认提交方式:
SELECT @@autocommit; #1代表自动提交,0代表手动提交
-- 修改默认提交方式
SET @@autocommit = 0;
2、事务的四大特征
1)原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败。
2)持久性:当事务提交或回滚后,数据库会持久化地保存数据。
3)隔离性:多个事务之间相互独立。
4)一致性:事务操作前后,数据总量不变。
3、事务的隔离级别(了解)
1)概念:多个事务之间是隔离地,相互独立地,但是如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决这些问题。
2)存在的问题:
① 脏读:一个事务读取到另一个事务中没有提交的数据。
② 不可重复读(虚读):在同一个事务中,两次读取到的数据不一样。
③ 幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改。
3)隔离级别:
① read uncommitted:读未提交。产生的问题:脏读、不可重复读、幻读
② read committed:读已提交(Oracle默认)。产生的问题:不可重复读、幻读
③ repeatable read:可重复读(MySQL默认)。产生的问题:幻读
④ serializable:串行化。可以解决所有的问题。
注意:隔离级别从小到大安全性越来越高,但是效率越来越低。
数据库查询隔离级别:select @@tx_isolation;
数据库设置隔离级别:set global transaction isolation level 级别字符串;
3.4、DCL(Data Control Language,数据控制语言)
DCL:管理用户、授权。
3.4.1、管理用户
-- 1、切换到mysql数据库
USE mysql;
-- 2、查询user表
SELECT * FROM USER;
#通配符: % 标识可以在任意主机使用用户登录数据库
-- 创建用户
CREATE USER '用户名(如 zhangsan)'@'主机名(如 localhost,或者填一个 %)'
IDENTIFIED BY '密码';
-- 修改用户密码
#修改 zhangsan 用户的密码为 abc
UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
#另一种方式
SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
-- 删除用户
DROP USER '用户名'@'主机名';
MySQL中忘记了root用户和密码怎么办?解决方式如下:
-- MySQL中忘记了root用户密码怎么办?
#1、打开CMD,并在CMD中执行:
net STOP mysql #停止mysql服务
mysqld --skip-grand-TABLES #使用无验证方式启动mysql服务 :
#2、打开新的CMD,并在新的CMD中执行:
mysql #无需账号密码,登录mysql
mysql> USE mysql; #进入mysql数据库
mysql> UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名'; #修改密码
#3、关闭原CMD和新的CMD
#4、打开任务管理器,找到mysqld.exe服务,结束该进程
#5、以管理员方式打开CMD,执行:
net START mysql
mysql -uroot -p新密码
#6、登陆成功
3.4.2、权限管理
-- 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
-- 授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
GRANT ALL ON * TO '用户名'@'主机名'; #给该用户授予使用所有数据库的所有表权限
-- 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';