数据库-Mysql学习笔记

数据库（DataBase）

用于存储和管理数据的仓库

特点：1.持久化存储数据，本质是一个文件系统。

2.方便存储和管理数据。

3.使用了统一的方式操作数据库（SQL）。

MySQL

一个数据库服务器软件。

计算机安装了MySQL之后，可称作MySQL服务器。

MySQL——>数据库（文件夹）——>表（文件）——>数据记录

SQL是structured query language的缩写，意为结构化查询语言。

其实就是定义了操作所有关系型数据库的原则。

数据库操作

DDL（操作数据库，表）

操作数据库：

1.查询：

查看数据库目录：show databases；

查看其中一个数据库的字符集（查询创建语句）：show create database 数据库名称；

创建一个数据库：create database 数据库名称；

先判断是否存在再创建：create database if not exists 数据库名称；

指定字符集创建数据库：create database 数据库名称 character set gbk/utf8；

先判断是否存在再指定字符集创建数据库：create database if not exists 数据库名称 character set gbk/utf8；

2.修改：

alter database 数据库名称 character set utf8/gbk；

3.删除：

直接删除：drop database 数据库名称；

判断是否存在再删除：drop database if exists 数据库名称；

4.使用（进入）指定数据库：

查询当前正在使用的数据库：select database（）；

指定使用某个数据库：use 数据库名称；

操作表：

1.查询表格

查询某个数据库中所有表的名称：show tables；

查询表格结构：sesc 表格名称；

2.创建表格

create table 表格名称（

列名1 数据类型1，

...

列名n 数据类型n）；

常见的数据类型：整数int，小数double（最多位数，小数点后保留的位数），日期date（yyyy-MM-dd），datetime（yyyy-MM-dd HH：mm：ss），时间戳类型timestamp（格式与datetime相同，若不赋值，默认为当前时间）。

3.删除表格：

drop table if exists 表格名称；

或drop table 表格名称；

复制表格：create table 新表格名称 like 表格名称；

4.修改表格：

修改表格名称：alter table 表格名称 rename to 新名称；

查看表格字符集：show create table 表格名称；

修改字符集：alter table 表格名称 character set utf8/gbk；

添加一列：alter table 表格名称 add 列名数据类型；

修改列名称，类型：alter table 表格名称 change 原列名新列名数据类型；

只改数据类型：alter table 表格名称 modify 列名新数据类型；

删除列：alter table 表格名称 drop 列名；

DML（增删改表中数据）

1.添加数据

insert into 表格名称（列名1，...,列名n）

values（值1，...,值n）；

可用 select * from 表格名称 进行查询。

注意：1.列名与值要一一对应。

2.若不写列名，则默认给所有列添加值：

insert into 表格名称 values（值1，...,值n）；

3.除了数字，其他数据类型需要用引号（单双均可），日期应写为"1996-03-22"（例）

2.删除数据

delete from 表格名称 where 条件；

如果不写条件，就默认删除表格中所有数据（不推荐）

推荐：truncate table 表格名称（本质上是删除旧表，再创建一个一模一样的空表）

3.修改数据

update 表格名称 set 列名1=值1，...where...;

例如：update sudent set age=18 where id=3;

如不加条件，所有数据记录都会被修改。

DQL（查询表中的记录）：

完整语法：

select 字段列表

from 表名列表

where 条件列表

group by 分组字段

having 分组之后的条件

order by 排序

limit 分页限定

1.查询某些列：

select 列名1，列名2... from 表格名称;

如想查询所有列，可用*代替列名。

2.去除重复的结果集

select distinct 列名1，列名2... from 表格名称;（只有两条数据指定的所有列全部一样才会去除）

3.简单运算

select 列名1+列名2 from 表格名称；

注意：有null参与的运算，结果都为null

改良版：select 列名1+ifnull（列名2，0） from 表格名称；（意为如果列名2的内容为null，则替换为0）

给运算后的结果另起名字：select 列名1+ifnull（列名2，0）as 自定义新列名 from 表格名称；（as可用一个或多个空格代替）

也可以给其他列另起名字：select 列名1 新名字 from 表格名称；

4.条件查询

select * from 表格名称 where 条件语句;

常用的逻辑连接符：小于<,大于>,小于等于<=,大于等于>=，等于=，不等于！=/<>

与and/between a and b：select * from student where age>=20 and age <=30; select * from student where age between 20 and 30;

或or/in(a,b,c)：select * from student where age=20 or age =25 or age =30; select * from student where age in (20,25,30);

筛选条件为null时不可用=，应该用is，如：select * from student where score is null;

筛选条件不为null时用is not，如：select * from student where score is not null;

5.模糊查询

select * from 表格名称 where 列名 like "含占位符的表达式";

占位符：_表示一个字符，%表示0到多个字符。如：姓马的人——>"马%"，姓名第二个字是马的人——>"_马%"，姓名是三个字的人——>"___"，姓名中包含马的人——>"%马%"

6.排序查询

select * from 表格名称 order by 列名 asc升序（默认）/desc降序;

若须多个字段排序，则写为select * from 表格名称 order by 列名1 asc/desc，列名2 asc/desc，列名3 asc/desc...;（排名有先后，前面条件一样时，才考虑后面的条件）

7.聚合函数

将一列数据作为一个整体，进行纵向的计算。

聚合函数在计算时会排除null，若想包含null，count选择主键计数，或者用ifnull（列名，替换值）。

count计算个数，max计算最大值，min计算最小值，sum计算和，avg计算平均值。

select 函数名（列名）from 表格名称;

8.分组查询

select 列名1，聚合函数（列名2）from 表格名称 group by 列名1；

select后只能写列名或聚合函数。

例如：SELECT sex,AVG(math) FROM student GROUP BY sex;表示根据不同性别计算数学的平均分

与条件查询结合：select 列名1，聚合函数（列名2）from 表格名称 where 条件语句 group by 列名1；

例如：SELECT sex,AVG(math) FROM student where math>90 GROUP BY sex;表示在数学成绩90以上的条件下，根据不同性别计算数学的平均分

加上分组之后条件：SELECT sex,AVG(math),count(id) FROM student where math>90 GROUP BY sex having count（id）>2;在上述语句的基础上，去除人数小于等于2的分组

一般聚合函数可以起一个别名，这样判断的时候更加清晰，如：SELECT sex,AVG(math),count(id) 人数 FROM student where math>90 GROUP BY sex having 人数>2;

having与where的区别：1.where在分组之前限定，如果不满足条件则不参与分组。having在分组之后进行限定，如果不满足则不会被查询出来

2.where后不能跟聚合函数，having可以进行聚合函数的判断

9.分页查询

limit 开始的索引每页查询条数；（mySQL专属）

公式：开始索引 = （当前页码-1）*每页显示条数

以每页查询3条为例：

select * from 表格名 limit 0，3；

select * from 表格名 limit 3，3；

select * from 表格名 limit 6，3；

DCL（管理用户，授权）：

管理用户

1.查询用户：

use mysql;

select * from user;

2.添加用户：create user "用户名"@"主机名" identified by "密码";

3.删除用户：drop user "用户名"@"主机名";

4.修改用户密码：

update user set password =password("新密码") where user = "用户名";

或 set password for "用户名"@"主机名"=password("新密码") ;

如忘记了root用户的密码：以管理员身份在cmd中输入net stop mysql停止mysql服务。使用无验证方式启动mysql服务mysqld --skip-grant-tables。打开新的cmd窗口，输入mysql按回车即登录成功。输入use msql，修改密码。关闭两个窗口，再在任务管理器中手动结束mysql.exe的进程。重新启动mysql服务，即可使用新密码登录。

权限管理：

1.查询权限：show grants for "用户名"@"主机名";

2.授予权限：grant 权限列表 on 数据库名.表名 to "用户名"@"主机名";

授予所有权限：grant all on *.* to "用户名"@"主机名";

3.撤销权限：revoke 权限列表 on 数据库名.表名 from "用户名"@"主机名";

约束

对表中的数据进行限定，保证数据的正确性，有效性和完整性。

1.非空约束

添加此约束，则该列不可添加为null的数据。

创建表时添加约束：列名数据类型 NOT NULL;

表创建完成后，再对其添加约束：alter table 表格名称 modify 列名数据类型 NOT NULL;

删除非空约束：alter table 表格名称 modify 列名数据类型；

2.唯一约束

添加此约束，则该列不可添加重复数据（null可以重复）

创建表时添加约束：列名数据类型 unique;

表创建完成后，再对其添加约束：alter table 表格名称 modify 列名数据类型 unique;

删除唯一约束：alter table 表格名称 drop index 列名；

3.主键约束

一张表只能有一个字段为主键，主键就是表中记录的唯一标识。

创建表时添加约束：列名数据类型 primary key;

表创建完成后，再对其添加约束：alter table 表格名称 modify 列名数据类型 primary key;

删除唯一约束：alter table 表格名称 drop primary key；（无须指定字段名，因为一张表只有一个主键）

主键自动增长：列名数据类型 primary key AUTO_INCREMENT，（创建表格时）

在往表格中另外添加数据时，主键可以为空，会自动赋予上一次添加值的+1，如：INSERT INTO boy VALUES(NULL,"小李");

删除自动增长：alter table 表格名称 modify 列名数据类型；

添加自动增长：alter table 表格名称 modify 列名数据类型 AUTO_INCREMENT；

4.外键约束

foreign key，让表与表产生关系，从而保证数据的正确性。

先创建主表（略），然后创建从表，在从表中添加语句来关联主表，从表写法如下：

create table 表格名称（

...

外键列字段名数据类型，

constraint 外键名称 foreign key （外键列字段名）references 主表名称（主表列名）

）；

 1 CREATE TABLE campany (
 2 id INT PRIMARY KEY AUTO_INCREMENT,
 3 NAME VARCHAR(15) );
 4 
 5 CREATE TABLE employee (
 6 id INT PRIMARY KEY AUTO_INCREMENT,
 7 NAME VARCHAR(15),
 8 age INT,
 9 dep_id INT,
10 CONSTRAINT idfk FOREIGN KEY (dep_id) REFERENCES campany(id)
11 );

View Code

删除外键：alter table 表格名称 drop foreign key 外键名称；

另外添加外键：alter table 表格名称 add constraint 外键名称 foreign key （外键列字段名）references 主表名称（主表列名）；

级联更新：on update cascade

级联删除：on delete cascade

添加级联相关写法（更新和删除可以单独写）：alter table 表格名称 add constraint 外键名称 foreign key （外键列字段名）references 主表名称（主表列名）

on update cascade on delete cascade；

数据库的设计

多表之间的关系

1.一对多（多对一）

在多的一方添加一列外键，指向一的一方的主键。

例如：在员工表中添加一列，指向部门表的id，1表示销售部，2表示财务部等。

外键列字段名数据类型，

constraint 外键名称 foreign key （外键列字段名）references 主表名称（主表主键字段名）

2.多对多

需要添加第三张中间表来实现。该中间表至少包含两个字段，作为中间表的外键，指向两张主表的主键，里面存放的是两张主表的主键之间的所有关系。

3.一对一

在任意一方添加唯一外键（unique约束）指向另一方的主键

练习：目录与旅游线路（一对多），旅游线路与用户（多对多）

 1 REATE TABLE tour_category(
 2     cid INT PRIMARY KEY AUTO_INCREMENT,
 3     cname VARCHAR(100) NOT NULL UNIQUE);
 4     
 5 CREATE TABLE tour_route(
 6     rid INT PRIMARY KEY AUTO_INCREMENT,
 7     rname VARCHAR(100) NOT NULL UNIQUE,
 8     price DOUBLE,
 9     rdate DATE,
10     cid INT,
11     FOREIGN KEY(cid) REFERENCES tour_category(cid));
12 
13 CREATE TABLE tour_user(
14     uid INT PRIMARY KEY AUTO_INCREMENT,
15     username VARCHAR(100) UNIQUE NOT NULL,
16     PASSWORD VARCHAR(30) NOT NULL,
17     NAME VARCHAR(100),
18     birthday DATE,
19     sex CHAR(1) DEFAULT '男',
20     telephone VARCHAR(11),
21     email VARCHAR(100));
22     
23 CREATE TABLE favorite(
24     rid INT,
25     DATE DATETIME,
26     uid INT,
27     PRIMARY KEY(rid,uid),
28     FOREIGN KEY(rid) REFERENCES tour_route(rid),
29     FOREIGN KEY(uid) REFERENCES tour_user(uid));

View Code

范式：

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）

函数依赖：通过A（属性/属性组）的值可以唯一确定B属性的值，则称B依赖于A。通过A能定位唯一的B。

如：A—>学号，B—>姓名或 A—>（学号，课程名称），B—>分数

完全函数依赖：A是一个属性组，B的确定依赖A中所有的属性值。需要所有A的属性才能确定B，则称B完全依赖于A

如：A—>（学号，课程名称），B—>分数

部分函数依赖：A是一个属性组，B的确定只依赖A中某一些属性值。只需要A的一部分属性值就能确定B。

如：A—>（学号，课程名称），B—>姓名

传递函数依赖：A（属性/属性组）可唯一确定B，B（属性/属性组）可唯一确定C，则称C传递函数依赖于A。

如：A—>学号，B—>系名，C—>系主任

码：一张表中，一个属性或属性组被其他所有属性完全依赖，则称其为该表的码。

如：（学号，课程名称）—>可以确定系名，系主任，分数

主属性：码中的所有属性

非主属性：除了码的其他属性

原表格：

第一范式（1NF）：每一列都是不可分割的原子数据项，我们之前创建的表格都满足第一范式。

满足第一范式的表格变化为：

第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于码（消除非主属性对主码的部分函数依赖）

满足第二范式的表格变化为：

第三范式（3NF）：在3NF基础上，任何非主属性不依赖于其他非主属性。（消除传递依赖）。

满足第三范式的表格变化为：

数据库的备份与还原：

1.命令语句：mysqldump-u用户名-p密码>备份存放路径

丢失时还原：创建并使用需要还原的数据库后，输入命令：source 备份存放路径

2.mysqlyog：选中需要备份的数据库，右键，选择备份/导出，备份到数据库，转储到SQL，选择地址后点击导出即可。

丢失时还原：选中root@localhost，右键选择执行sql脚本，选择备份的文件，点击执行即可。

多表查询：

语法：select 列名列表 from 表名列表 where ...

笛卡尔积：有两个集合A,B，取这两个集合的所有组成情况。

要完成多表查询，需要在表格之间外键关联的基础上，消除无用的数据。

内连接查询：

隐式内连接：使用where语句来消除无用数据

以员工表与部门表为例：

select

t1.name,

t1.gender,

t2.name

from

employee t1,

department t2

where

t1.id = t2.id;

显式内连接：

语法：select 列名列表 from 表名1（inner）join 表名2 on 条件

外连接查询：

左外连接：select 字段列表 from 表1 left （outer）join 表2 on 条件

查询的是表1的所有数据与表1与表2的交集数据。

比如，表1为员工表，表2为部门表，若有员工未加入部门，则查询时也会显示。

右外连接：select 字段列表 from 表1 right （outer）join 表2 on 条件

查询的是表2的所有数据与表1与表2的交集数据。

比如，表1为员工表，表2为部门表，若有员工未加入部门，则查询时不会显示。

子查询：

查询中嵌套查询，称嵌套查询为子查询

单行单列：

例：查询工资最高的员工的信息：select * from employee where salary = （select max（salary）from employee）；

查询员工工资小于平均工资的人：select * from employee where salary < （select avg（salary）from employee）；

多行单列：

查询财务部和市场部所有学生信息：select * from employee where employee.dep_id in (select id from department where name="财务部" or name="市场部")；

多行多列:

子查询可以作为一张虚拟表参与查询。

如：查询员工入职日期是2011-11-11之后的员工信息和部门信息。

select * from department t1 , (select * from employee where employee.joindate>2011-11-11) t2 where t1.id = t2.department_id; t2相当于是员工表的一部分

练习：

表格信息代码：

 1 CREATE TABLE dept(
 2  id INT PRIMARY KEY ,
 3  dname VARCHAR(50),
 4  loc VARCHAR(50));   
 5  
 6  INSERT INTO dept(id,dname,loc) VALUES
 7  (10,"教研部","北京"),
 8  (20,"学工部","上海"),
 9  (30,"销售部","广州"),
10  (40,"财务部","深圳");
11  
12  CREATE TABLE job(
13   id INT PRIMARY KEY,
14   jname VARCHAR(20),
15   description VARCHAR(50));
16   
17   INSERT INTO job(id,jname,description) VALUES
18  (1,"董事长","管理公司"),
19  (2,"经理","管理部门员工"),
20  (3,"销售员","推销产品"),
21  (4,"文员","使用办公软件");
22  
23  CREATE TABLE emp(
24   id INT PRIMARY KEY,
25   ename VARCHAR(50),
26   job_id INT,
27   mgr INT,
28   joindate DATE,
29   salary DECIMAL(7,2),
30   bonus DECIMAL(7,2),
31   dept_id INT,
32   CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job(id),
33   CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept(id));
34   
35   INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
36   (1001,"孙悟空",4,1004,"2000-12-17","8000.00",NULL,20),
37   (1002,"卢俊义",3,1006,"2001-02-20","16000.00","3000.00",30),
38   (1003,"林冲",3,1006,"2001-02-22","12500.00","5000.00",30),`dept`
39   (1004,"唐僧",2,1009,"2001-04-02","29750.00",NULL,20),
40   (1005,"李逵",4,1006,"2001-09-28","12500.00","14000.00",30),
41   (1006,"宋江",2,1009,"2001-05-01","28500.00",NULL,30),
42   (1007,"刘备",2,1009,"2001-09-01","24500.00",NULL,10),
43   (1008,"猪八戒",4,1004,"2007-04-19","30000.00",NULL,20),
44   (1009,"罗贯中",1,NULL,"2001-11-17","50000.00",NULL,10),
45   (1010,"吴用",3,1004,"2001-09-08","15000.00","0.00",30),
46   (1011,"沙僧",4,1004,"2007-05-23","11000.00",NULL,20),
47   (1012,"李逵",4,1004,"2001-12-03","9500.00",NULL,30),
48   (1013,"小白龙",4,1004,"2001-12-03","30000.00",NULL,20),
49   (1014,"关羽",4,1004,"2002-01-23","13000.00",NULL,10);
50   
51   CREATE TABLE salarygrade(
52   grade INT PRIMARY KEY,
53   losalary INT,
54   hisalary INT);
55   
56   INSERT INTO salarygrade(grade,losalary,hisalary) VALUES
57   (1,7000,12000),
58   (2,12010,14000),
59   (3,14010,20000),
60   (4,20010,30000),
61   (5,30010,99990);

View Code

表格信息结构：

1.查询所有员工信息，查询员工编号，员工姓名，工资，职务名称，职务描述

分析：员工编号，员工姓名，工资属于emp表格，职务名称，职务描述属于job表格，从两个表格中分别查询各个字段，用外键连接即可

SELECT
t1.id,t1.ename,t1.salary,t2.jname,t2.description
FROM
emp t1,job t2
WHERE
t1.job_id = t2.id;

2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

分析：与上一题类似，用到了三张表，需要写两个条件将三张表联系起来

SELECT
t1.id,t1.ename,t1.salary,t2.jname,t2.description,t3.dname,t3.loc
FROM
emp t1,job t2,dept t3
WHERE
t1.job_id = t2.id AND t1.dept_id=t3.id

3.查询员工姓名，工资，工资等级

分析：通过between and筛选每个人工资的相应工资等级

SELECT
t1.ename,t1.salary,t2.grade
FROM
emp t1, salarygrade t2
WHERE
t1.salary BETWEEN t2.losalary AND t1.salary<t2.hisalary

4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

分析：三个连接条件用and分开即可

SELECT
t1.ename,t1.salary,t2.jname,t2.description,t3.dname,t3.loc,t4.grade
FROM
emp t1, job t2,dept t3, salarygrade t4
WHERE
t1.job_id = t2.id
AND t1.dept_id = t3.id
AND t1.salary BETWEEN t4.losalary AND t4.hisalary

5.查询部门编号，部门名称，部门位置，部门人数

分析：将每个部门的人数用分组查询查询出来作为一个虚拟表，再与部门表联系起来一起查询

SELECT
t1.id,t1.dname,t1.loc,t2.total
FROM
dept t1,
(SELECT
dept_id,COUNT(id) total -- 给人数重新起名
FROM
emp
GROUP BY
dept_id) t2-- 虚拟表作为t2
WHERE
t1.id = t2.dept_id;

6.查询所有员工的姓名，及其直接上级的姓名，没有领导的员工也需要查询

分析：将emp表进行自关联，为了查询没有领导的员工，需要使用左外连接，注意格式为left join on。

SELECT
t1.id, t1.ename, t1.mgr 领导id, t2.ename 领导姓名
FROM
emp t1 -- 代表全体员工
LEFT JOIN emp t2 -- 代表员工的上级
ON
t1.mgr = t2.id

事务：

概念：如果包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

操作：1.在需要执行的一连串操作前加 start transaction；

2.查看临时结果，若出现错误，进行回滚 rollback；

3.更正操作后再次查看临时结果，无误后提交 commit；

事务提交的两种方式：

1.自动提交一条DML语句会自动提交一次事务，Mysql默认自动提交。

2.手动提交需要手动开启事务start transaction，再手动提交commit，否则，看到改动的结果是临时结果，不会保存。oracle数据库默认手动提交。

修改默认提交方式：set@@autocommit=1/0； 1表示自动提交，0表示手动提交

事务的四大特征：

1.原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败

2.持久性：当事务提交或回滚后，数据库会持久化地保存数据

3.隔离性：多个事务之间相互独立。

4.一致性：事务操作前后，数据总量不变。

事务的隔离级别

概念：多个事务之间是隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。

存在问题：

1.脏读：一个事务，读取到另一个事务中没有提交的数据。

2.不可重复读（虚读）：在同一个事务中，两次读到的数据不一样。

3.幻读：一个事务操作（DML）数据表中所有记录，另一条事务添加了一条数据，则第一个事务查询不到这些修改。（mysql不存在幻读）

隔离级别：从小到大安全级别越来越高，但是效率越来越低。

1.read uncommitted 读未提交

产生的问题：脏读，不可重复读，幻读

2.read committed 读已提交（oracle默认）

产生的问题：不可重复读，幻读

3.repeatble read 可重复读（mysql默认）

产生的问题：幻读

4.serializable 串行化

可以解决所有的问题

查询隔离级别：select@@tx_isolation;

设置隔离级别：set globlal transaction isolation level 级别字符串；

隔离级别案例：

read uncommitted 读未提交：A给B转账（未提交），B即可查询到，但A回滚后，钱又回到A账户里，此时B就取不出钱了（脏读，不可重复读）

read committed 读已提交：A给B转账（未提交），B查询不到，A提交后，才能查询到（不可重复读）

repeatble read 可重复读：A给B转账（未提交），B查询不到，A提交后，B也查询不到，B提交当前查询，再重新查询，才能查询到

serializable 串行化：A在进行转账操作时，该账户锁死，B无法进行操作，A提交后，B才可以进行查询操作

posted @ 2020-04-11 23:03 菅兮徽音阅读(402) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

菅兮徽音

数据库-Mysql学习笔记

公告