MySQL数据库

数据库

存放数据的仓库

目前我们存放数据的方式有哪些

1、使用集合 优势：操作数据的效率 数据存放在内容中 缺点：数据会随着内存的销毁而销毁

2、使用普通文件 优势：数据具备持久性 数据存放于池畔 缺点：操作数据的效率低

数据库：底层也是操作文件，但是为存放的文件以及文件中的数据设计了很多的响应的数据结构与算法来提高操作效率

所有数据库是一个保证了相当的操作数据效率又保证了数据持久性的方式

所以真实开发中，选择使用数据库来存放数据

数据库的分类

关系型数据库：Oracle DB2 MySQL SQL SERVER…

非关系型数据库：ElastecSearch Redis MongoDB

关系型数据库的特点：以二位表格形式存放数据

数据库的专业术语

数据库管理系统（DBMS）

数据库（DB）

MySQL

MySQL是中国市场上使用率最高的数据库，所以学习MySQL

SQL

Structuerd Query Language 这是一门独立的语言

SQL语言可以操作数据库管理系统

规范：

1、不区分大小写 但是建议关键字都大写 自定义的名称建议小写

2、每一句SQL 都需要使用分号结束

SQL的分类

DDL：数据定义语言

​ 比如定义数据库 定义表…

​ 用来改变数据库管理系统的结构

DML:数据操作语言

​ 用来改变某张表的结构的

​ 比如：向表中 添加、删除、修改数据

DQL：数据查询语言

​ 用来查询指定表中符合条件的记录

DCL：数据控制语言

​ 用来控制数据库管理系统相关的信息，比如设置权限问题

执行SQL

1、在控制台中写

2、使用图形化工具

建议使用图形化工具

流行的图形化工具：

navicat/sqlyog…

DDL

数据库相关

# 查看所有的数据库
SHOW DATABASES;
# 创建数据库
CREATE DATABASE 数据库名称;
# 删除数据库
DEUR DATABASE 数据库名称;
# 使用指定数据库
USE 数据库名称;

数据表相关

# 展示当前数据库中所有的表
SHOW TABLES;

# 创建数据表
CREATE TABLE 表名(
    字段名1 数据类型1,
    字段名2 数据类型2,
    ....
    字段名n 数据类型n
);

#查看表信息
DESC 表名;

# 查看创建表的详细信息
SHOW CREATE TABLE 表名;

#删除表
DROP TABLE 表名;

#修改表结构
  #添加列
  ALTER TABLE 表名 ADD 字段名 数据类型;
  #删除列
  ALTER TABLE 表名 DROP 字段名;
  #修改列 只改类型
  ALTER TABLE 表名 MODIFY 字段名 新类型;
  #修改列 将列名和类型都进行修改
  ALTER TABLE 表名 CHANGE 字段名 新字段名 新类型;

mysql的数据类型

数据类型	特点
INT	整数
DOUBLE(m,n)	小数 总共最多m位，其中n位充当小数。例如DOUBLE(5,2),最大值为 999.99
DECIMAL(m,n)	不会丢失精度的小数
DATE	表示年月日
TIMESTAMP	表示年月日时分秒，带时区效果
CHAR(L)	定长字符串，占用的空间大小就是指定的长度。当该字段的值长度是确定的且固定选择CHAR
VARCHAR(L)	可变长字符串，最多占用的 空间大小是指定的长度。如果没有填满，就根据具体的内容来分配空间。但是会单独使用一个空间来指定长度。当该字段的值不能确定时选择VARCHAR

示例

创建一个user表，有id、姓名、年龄、性别、生日、身高

CREATE TABLE user{
   ID INT,
   uname VARCHAR(50),
   age INT,
   sex CHAR(2),
   birthday DATE,
   high DOUBLE(4,1)
};

#查看表
DESC user;
SHOW CREATE TABLE user;
DROP TABLE user;
ALTER TABLE user ADD phoneNum INT;
ALTER TABLE user DROP birthday;
ALTER TABLE user MODIFY  phoneNum CHAR(11);
ALTER TABLE user CHANGE phoneNum gender CHAR(2);

DML

对数据表记录进行增删改

添加记录

#  添加一行数据  只给新的这一行的指定的列添加数据
INSERT INTO 表名(列名1,列名2...)VALUES(值1,值2...);
#  添加一行数据  默认给每一列都添加数据
INSERT INTO 表名 VALUES(值1,值2...);

#  查询指定表的所有数据(属于DQL)
SELECT * FROM 表名;

删除记录

# 没有条件：删除指定表中的所有记录    有条件：删除表中符合条件的记录
DELETE FROM 表名 [WHERE 条件];

清空表

# 使用DELETE 不跟条件  但是这种写法效率低，因为它是将所有记录一条一条的删除
DELETE FROM 表名;

# 使用TRUNCATE 建议的写法，效率高，应为它是直接将表删除，然后创建一个同名的表
TRUNCATE TABLE 表名;

修改记录

# 没有条件：将表中指定列的所有数据都改成指定值  有条件：将表中符合条件的指定列的值改为指定值
UPDATE 表名 SET 字段名1=新值1,字段名2=新值2...[WHERE 条件];

DQL

引入数据

CREATE TABLE stu(
	sid		INT 	PRIMARY KEY,
	sname		VARCHAR(50),
	age		INT,
	gander		VARCHAR(10),
	province	VARCHAR(50),
	tuition		INT
)charset = utf8;

INSERT INTO `stu` VALUES ('1', '王永', '23', '男', '北京', '1500');
INSERT INTO `stu` VALUES ('2', '张雷', '25', '男', '辽宁', '2500');
INSERT INTO `stu` VALUES ('3', '李强', '22', '男', '北京', '3500');
INSERT INTO `stu` VALUES ('4', '宋永合', '25', '男', '北京', '1500');
INSERT INTO `stu` VALUES ('5', '叙美丽', '23', '女', '北京', '1000');
INSERT INTO `stu` VALUES ('6', '陈宁', '22', '女', '山东', '2500');
INSERT INTO `stu` VALUES ('7', '王丽', '21', '女', '北京', '1600');
INSERT INTO `stu` VALUES ('8', '李永', '23', '男', '北京', '3500');
INSERT INTO `stu` VALUES ('9', '张玲', '23', '女', '广州', '2500');
INSERT INTO `stu` VALUES ('10', '啊历', '18', '男', '山西', '3500');
INSERT INTO `stu` VALUES ('11', '王刚', '23', '男', '湖北', '4500');
INSERT INTO `stu` VALUES ('12', '陈永', '24', '男', '北京', '1500');
INSERT INTO `stu` VALUES ('13', '李雷', '24', '男', '辽宁', '2500');
INSERT INTO `stu` VALUES ('14', '李沿', '22', '男', '北京', '3500');
INSERT INTO `stu` VALUES ('15', '王小明', '25', '男', '北京', '1500');
INSERT INTO `stu` VALUES ('16', '王小丽', '23', '女', '北京', '1000');
INSERT INTO `stu` VALUES ('17', '唐宁', '22', '女', '山东', '2500');
INSERT INTO `stu` VALUES ('18', '唐丽', '21', '女', '北京', '1600');
INSERT INTO `stu` VALUES ('19', '啊永', '23', '男', '北京', '3500');
INSERT INTO `stu` VALUES ('20', '唐玲', '23', '女', '广州', '2500');
INSERT INTO `stu` VALUES ('21', '叙刚', '18', '男', '山西', '3500');
INSERT INTO `stu` VALUES ('22', '王累', '23', '男', '湖北', '4500');
INSERT INTO `stu` VALUES ('23', '赵安', '23', '男', '北京', '1500');
INSERT INTO `stu` VALUES ('24', '关雷', '25', '男', '辽宁', '2500');
INSERT INTO `stu` VALUES ('25', '李字', '22', '男', '北京', '3500');
INSERT INTO `stu` VALUES ('26', '叙安国', '25', '男', '北京', '1500');
INSERT INTO `stu` VALUES ('27', '陈浩难', '23', '女', '北京', '1000');
INSERT INTO `stu` VALUES ('28', '陈明', '22', '女', '山东', '2500');
INSERT INTO `stu` VALUES ('29', '孙丽', '21', '女', '北京', '1600');
INSERT INTO `stu` VALUES ('30', '李治国', '23', '男', '北京', '3500');
INSERT INTO `stu` VALUES ('31', '张娜', '23', '女', '广州', '2500');
INSERT INTO `stu` VALUES ('32', '安强', '18', '男', '山西', '3500');
INSERT INTO `stu` VALUES ('33', '王欢', '23', '男', '湖北', '4500');
INSERT INTO `stu` VALUES ('34', '周天乐', '23', '男', '北京', '1500');
INSERT INTO `stu` VALUES ('35', '关雷', '25', '男', '辽宁', '2500');
INSERT INTO `stu` VALUES ('36', '吴强', '22', '男', '北京', '3500');
INSERT INTO `stu` VALUES ('37', '吴合国', '25', '男', '北京', '1500');
INSERT INTO `stu` VALUES ('38', '正小和', '23', '女', '北京', '1000');
INSERT INTO `stu` VALUES ('39', '吴丽', '22', '女', '山东', '2500');
INSERT INTO `stu` VALUES ('40', '冯含', '21', '女', '北京', '1600');
INSERT INTO `stu` VALUES ('41', '陈冬', '23', '男', '北京', '3500');
INSERT INTO `stu` VALUES ('42', '关玲', '23', '女', '广州', '2500');
INSERT INTO `stu` VALUES ('43', '包利', '18', '男', '山西', '3500');
INSERT INTO `stu` VALUES ('44', '威刚', '23', '男', '湖北', '4500');
INSERT INTO `stu` VALUES ('45', '李永', '23', '男', '北京', '1500');
INSERT INTO `stu` VALUES ('46', '张关雷', '25', '男', '辽宁', '2500');
INSERT INTO `stu` VALUES ('47', '送小强', '22', '男', '北京', '3500');
INSERT INTO `stu` VALUES ('48', '关动林', '25', '男', '北京', '1500');
INSERT INTO `stu` VALUES ('49', '苏小哑', '23', '女', '北京', '1000');
INSERT INTO `stu` VALUES ('50', '赵宁', '22', '女', '山东', '2500');
INSERT INTO `stu` VALUES ('51', '陈丽', '21', '女', '北京', '1600');
INSERT INTO `stu` VALUES ('52', '钱小刚', '23', '男', '北京', '3500');
INSERT INTO `stu` VALUES ('53', '艾林', '23', '女', '广州', '2500');
INSERT INTO `stu` VALUES ('54', '郭林', '18', '男', '山西', '3500');
INSERT INTO `stu` VALUES ('55', '周制强', '23', '男', '湖北', '4500');

基本使用

#查询指定表中指定列的所有数据
SELECT 列名1,列名2,... FROM 表名;

# 查询指定表中所有列的所有数据
SELECT * FROM 表名;

给列取别名

# 查询结果中会显示我们指定的别名来替换真实的列名
SELECT 列名1 [as] 别名1,列名2 [as] 别名2...
FROM 表名;

示例

示例1：查询stu表中sname与age列的所有数据，其中为age列取别名为“年龄”

SELECT sname,age "年龄" 
FROM stu;

示例2：查询stu表中每一个学生三年的学费

SELECT sname,tuition*3 total_tuitian
FROM stu;

结果去重

#表示仅选取表中唯一不同的值
SELECT DISTINCT 列名
FROM 表名;

对结果排序

根据单列排序

SELECT 列名
FROM 表名
ORDER BY 指定的列名[ASC/DESC];
# ASC 升序
# DESC:降序
# 如果不写，默认为ASC,升序排序

根据多列排序

SELECT 列名
FROM 表名
ORDER BY 指定的列名1[ASC/DESC],指定的列2[ASC/DESC];
  现根据列1排序，如果列1相等再根据列2排序

条件查询

使用数学运算符的计算结果作为条件

使用方法

SELECT 列名
FROM 表名
WHERE 条件1 数学运算符 值;

数学运算符

数学符号：> < >= <= != =

使用逻辑运算符

使用方法

WHERE 条件1 逻辑运算符 条件2;

逻辑运算符：

AND:表示并且
OR:表示或者

exists运算符

exists运算符用于判断查询子句是否有记录，如果有一条记录或多条记录存在返回true，否则返回false

语法

SELECT * FROM student
WHERE exists(SELECT sc.sid FROM sc WHERE student.sid=sc.sid);

between-and

使用方法

# 当字段的值大于等于值1并且小于等于值2时 整个条件成立
WHERE 字段 BETWEEN 值1 AND 值2;

NULL

如果字段的值为null，不能使用=判断

必须使用 IS NULL/IS NOT NULL来进行判断

WHERE 字段名 IS NULL;

WHERE 字段名 IS NOT NULL;

IN

枚举查询

WHERE 字段 IN(值1,值2,...);
# 只要字段的值与()中的任意一个值相等，条件就成立
WHERE 字段 NOT IN(值1,值2,...);
# 只要字段的值与()中的任意一个值相等，条件就不成立

LIKE

模糊查询，只适用于字符串字段

%:匹配任意个任意字符

_:匹配一个任意字符

WHERE 字段名 LIKE "灵活的使用%或者_";

示例1：查询所有姓名时三个字的学生

SELECT *
FORM 表名
WHERE sname LIKE "___";

示例2：查询所有姓周的学员信息

SELECT *
FROM stu
WHERE sname LIKE "周%";

时间查询

时间函数	描述
SYSDATE()	当前系统时间(日、月、年、时、分、秒)
CURDATE()	获取当前日期
CURTIME()	获取当前时间
WEEK(DATE)	获取指定时间为一年中的第几周
YEAR(DATE)	获取指定日期的年份
HOUR()	获取指定时间的小时值
MINUTE(TIME)	获取时间的分钟值
DATEDIFF(DATE1,DATE2)	获取DATE1和DATE2之间相隔的天数
ADDDATE(DATE,N)	计算DATE加上N天后的日期

用法

SELECT 函数;

聚合函数

将指定表中的指定列的所有数据进行统一运算

MAX(列名)：求指定列中所有值中的最大值

MIN(列名)：求指定列中所有值中的最小值

AVG(列名)：求指定列中所有值中的平均值

SUM(列名)：求指定列中所有值累加的和

COUNT(列名)：求指定所有值的数量

分组查询

SELECT 列名
FROM 表名
[WHERE 条件]
GROUP BY 字段名;

使用分组查询时，查询的列名只能是分组的字段或者聚合函数

示例：查询所有不是北京的学院的男女学生各自的数量

SELECT gander，COUNT(sid)
FROM stu
WHERE province !='北京'
GROUP BY gander;

HAVING

使用GROUP BY 后面

将分组之后的数据进行过滤

GROUP BY 字段
HAVING 条件;

示例：

查询每一个省份的学生的数量，数量小于3的除外

SELECT province,COUNT(sid)
FROM stu
GROUP BY province
HVAING COUNT(sid)>=3;

限定查询

一次只查询所有数据中的一部分

关键字	说明
LIMIT offset_start,row_count	限定查询结果的起始行和总行数

使用方法

SELECT 列名 FROM 表名 LIMIT 起始行,查询行数;

# 起始行从0开始取


limit 和 offset 的用法
mysql里分页一般用limit来实现
1、select * from article LIMIT 1,3; #表示跳过第一条数据，取后面三条数据
2、select * from article LIMIT 3;#表示取前三条数据
3、select * from article LIMIT 3 OFFSET 1;#也表示跳过第一行数据，取后面三条数据
#当limit后面跟两个参数时第一个参数表示要跳过的数量，后一位表示要去的数量
#当limit后面跟一个参数时，该参数表示要取的数据的数量
#当limit和offset组合使用时，limit后面只能跟一个参数，表示要取的数量，offset表示要跳过的数量

示例

查询工资 最高的员工信息

SELECT *
FROM emp
order BY  sal DESC
LIMIT 0,1;

子查询

每一个查询语句都有查询结果

这个结果可以作为一个查询语句的条件（源）

可以简单理解为java中的返回值

示例：

查询工资最高的员工信息

SELECT *
FROM emp
WHERE sal = 最高工资

# 查询最高工资 可以把这个sql语句当作最高工资来使用
SELECT MAX(Sal)
FROM emp;

SELECT *
FROM emp
WHERE sal (SELECT MAX(Sal)
FROM emp);

约束

就是限制，可以给指定表的指定列添加约束

主键约束

主键约束：该列的值必须是唯一且非空的

所以建议每一张表都选择一列添加主键约束

那么这一列就是这一行记录的唯一表示，相当于身份证（主键）

CREATE TABLE  表名(
   字段名 类型 PRIMARY KEY
);

#也可在修改类型时进行主键的添加

主键增长

如果主键是INT类型，可以再添加自增长约束

自增长：那么这一行如果没有被赋值或者赋值为NULL

则mysal会自动的为该列赋值（默认从1，每添加一列时主键的值都在上一列的基础上加一）

CREATE TABLE 表名(
   字段名 类型 PRIMARY KEY AUTO_INCREMENT
);

ALTER TABLE user MODIFY uid INT AUTO_INCREMENT;

非空约束

添加了此约束的字段的值不能为null

CREATE TABLE 表名(
      字段名 类型 NOT NULL
);

唯一约束

添加了此约束的字段的值不能重复

CREATE TABLE 表名(
    字段名 类型 UNIQUE
);

默认值约束

添加了此约束的字段如果没有主动赋值，则还是有值，就是指定的默认值

CREATE TABLE 表名(
   字段名 类型 DEFAULT 值
);

外键约束

是用在两张表有连接的情况

语法

CONSTRAINT 约束名 FOREIGN KEY(当前表列名) REFERENCES 被引用表名(主键名)

注意：被引用的列必须是主键  为了保证引用的一定是只有一行，并且一定有一行

示例：

为一个 表添加外键约束

ALTER TABLE 表名 ADD CONSTRAINT CONSTRAINT 约束名 FOREIGN KEY(当前表列名) REFERENCES 被引用表名(主键名);

CREATE TABLE  wife(
   wid INT PRIMARY KEY AUTO_INCREMENT
   wname VARCHAR(50)
);
CREATE TABLE  husband(
   hid INT PRIMARY KEY AUTO_INCREMENT
   hname VARCHAR(50)
   wid INT,
   CONSTRAINT fk_hus_wife FOREIGN KEY(wid) REFERENCES wife(wid)
);

表之间的关系

如果表之间有引用关系，则 根据引用方式的不同两个表之间的关系也不同

一对多

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cgwDYrXB-1632454810401)(C:\Users\LZ\AppData\Roaming\Typora\typora-user-images\image-20210826113234957.png)]

商品与分类的关系就行典型的一对多关系，一般在商品中使用外键

多对多

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ntI5zjEh-1632454810403)(C:\Users\LZ\AppData\Roaming\Typora\typora-user-images\image-20210826113434252.png)]

思路：1、在两张表中都使用外键引用对象

​ 语法上没法实习，因为永远需要先创建被引用的表

​ 因为两张表都是被引用的，所以不可能实现两张表都先创建

​ 2、单独创建一个维护多对多关系的表

​ 实质上就是在这个表创建多个外键

一对一

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XSCV85gi-1632454810404)(C:\Users\LZ\AppData\Roaming\Typora\typora-user-images\image-20210826113702211.png)]

在一对多关系的前提下，如果外键不允许重复，便形成了一对一关系

所以为外键列再添加一个唯一约束就可以了

CREATE TABLE  wife(
   wid INT PRIMARY KEY AUTO_INCREMENT,
   wname VARCHAR(50)
);
CREATE TABLE  husband(
   hid INT PRIMARY KEY AUTO_INCREMENT,
   hname VARCHAR(50),
   wid INT,
   CONSTRAINT fk_hus_wife FOREIGN KEY(wid) REFERENCES wife(wid)
);

多表查询

实际开发中经常需要查询多张表的数据

这种情况就是多表查询

基本写法

SELECT 表名.列名...
FROM 表名1,表名2
[WHERE 条件];

示例

查询所有的员工信息与部门信息

SELECT *
FROM emp,dept;

查看查询结果，有问题：

结果是左边表的每一条数据都会与右边表的每一条数据进行匹配

这就造成了冗余数据

这种情况称之为笛卡尔积

所以不建议这样写

采用下面的两种方式

内连接

内连接可以去除笛卡尔积

标准写法

SELECT 列名
FROM 表1[as]别名1 INNER JOIN 表2[as]别名2
ON 条件;

条件基本都是使用连接的字段

示例：

查询所有的员工信息与部门信息

SELECT e.*,d.*
FROM emp e INNER JOIN dept  d
ON e.deptno = d.deptno;

查询时只会查询出deptno相等的 员工信息和部门信息

方言写法

SELECT 列表
FROM 表1[as]别名1 INNER JOIN 表2[as]别名2
WHERE 条件;

示例：

查询所有的员工信息与部门信息

SELECT e.*,d.*
FROM emp e INNER JOIN dept  d
ON e.deptno = d.deptno;

自然连接

SELECT 列名
FROM 表1[as]别名1 NATURAL JOIN 表2[as]别名2

默认使用两张表中名称相同的字段作为条件

实际开发中使用自然连接：1、可读性低 2、有可能连接的字段名称不一致

示例：

查询所有的员工信息与部门信息

SELECT e.*,d.*
FROM emp e NATURAL JOIN dept  d

外连接

左外连接

SELECT 列表
FROM 表1[as]别名1 LEFT [OUTER] JOIN 表2[as]别名2
ON 条件;

作用：1、也能解决 笛卡尔积

​ 2、左边表的所有数据都会查询出来，哪怕不满足指定的条件

右外连接

SELECT 列表
FROM 表1[as]别名1 RIGHT [OUTER] JOIN 表2[as]别名2
ON 条件;

作用：1、也能解决 笛卡尔积

​ 2、右边表的所有数据都会查询出来，哪怕不满住指定的条件

全外连接

效果：左右两张表的所有数据都要查询出来，无论是否满足条件

mysql没有全外连接的语法

只能通过合并结果集达到全外连接的效果

合并结果集

UNION/UNION ALL

SELECT 语句
UNION/UNION ALL
SELCET 语句

UNION :会自动的去除两个结果集中重复的数据
UNION ALL:会保留两个结果集中所有的数据，包括重复的

注意：合并的结果集列名于类型要保持一致

L
SELECT e.,d.
FROM emp e INNER JOIN dept d
ON e.deptno = d.deptno;

#### 自然连接 

````SQL
SELECT 列名
FROM 表1[as]别名1 NATURAL JOIN 表2[as]别名2

默认使用两张表中名称相同的字段作为条件

实际开发中使用自然连接：1、可读性低 2、有可能连接的字段名称不一致

示例：

查询所有的员工信息与部门信息

SELECT e.*,d.*
FROM emp e NATURAL JOIN dept  d

外连接

左外连接

SELECT 列表
FROM 表1[as]别名1 LEFT [OUTER] JOIN 表2[as]别名2
ON 条件;

作用：1、也能解决 笛卡尔积

​ 2、左边表的所有数据都会查询出来，哪怕不满足指定的条件

右外连接

SELECT 列表
FROM 表1[as]别名1 RIGHT [OUTER] JOIN 表2[as]别名2
ON 条件;

作用：1、也能解决 笛卡尔积

​ 2、右边表的所有数据都会查询出来，哪怕不满住指定的条件

全外连接

效果：左右两张表的所有数据都要查询出来，无论是否满足条件

mysql没有全外连接的语法

只能通过合并结果集达到全外连接的效果

合并结果集

UNION/UNION ALL

SELECT 语句
UNION/UNION ALL
SELCET 语句

UNION :会自动的去除两个结果集中重复的数据
UNION ALL:会保留两个结果集中所有的数据，包括重复的

注意：合并的结果集列名于类型要保持一致