二、数据库强化
验证数据库
配置环境变量
MySQL数据库概述
数据库:存储数据的仓库,数据是有组织的进行存储(DB)
数据库管理系统:操纵和管理数据库的大型软件(DBMS)
SQL:操作关系型数据的编程语言,定义了一套关系型数据库统一的标准
SQL
SQL通用语法
select *
from table
where
(1)SQL语句通常可以单行或者多行书写,以封号结尾
(2)SQL语句可以使用空格/缩进来增强语句的可读性
SQL分类
DDL、DML、DQL、DCL
DDL
数据定义语言,用来定义数据库对象(数据库,表,字段)
数据库的操作
1.查询所有的数据库
show database;
2.切换数据库
use neuedusoft;
3.查询当前数据
select database();
4.创建数据库
create database [if not exists] 数据库名 [default charset 字符集];
5.删除数据库
drop database [if exists] 数据库名;
表的操作
1.查询当前数据库所有的表
show tables;
2.查看指定表结构
desc animes
3.查看指定表的建表语句
show create table 表名;
![image-20230720102624335](大实训.assets/image-20230720102624335.png)
4.创建表结构
create table 表名(
字段名1 字段1类型 NOT NULL/DEFAULT NULL DEFAULT ‘默认值’ AUTO_INCREMENT comment '对字段的中文解释',
字段名2 字段2类型 NOT NULL/NULL DEFAULT ‘默认值’ comment '对字段的中文解释',
字段名1 字段1类型 NOT NULL/NULL DEFAULT ‘默认值’ comment '对字段的中文解释',
PRIMARY KEY('字段1')
)DEFAULT CHARSET=utf8 comment '对表中文解释';
id(primary_key,NOT NULL,AUTO_INCREMENT,序号) | name(NULL,姓名) | age(NULL,年龄) | gender(NULL,性别) | status(NOT NULL,”是否删除“,1:正常使用;0:被删除) |
---|---|---|---|---|
1 | 张三 | 25 | 男 | 1 |
2 | 李四 | 26 | 女 | 1 |
3 | 王五 | 25 | 男 | 1 |
create table tb_user(
id int NOT NULL --AUTO_INCREMENT comment'序号',
name varchar(50) DEFAULT NULL comment'姓名',
age int DEFAULT NULL comment'年龄',
gender varchar(1) DEFAULT NULL comment'性别',
status int NOT NULL comment'1:正常使用,0:被删除',
--PRIMARY KEY('id')
)comment'用户信息表';
表的字段数据类型
数值类型
int/integer、float(单精度)、double(双精度)、decimal(小数值)
字符串类型:
char、varchar、text、blob
日期类型:
date(日期值:yyyy-mm-dd)、time(时间:hh:mm:ss)、year(年)、timestamp(时间戳)/datetime
表操作-修改
1.添加字段
alter table 表名 ADD 字段名 字段的数据类型 [comment];
2.修改字段名和数据类型
alter table 表名 change 旧字段名 新字段名 数据类型;
3.修改数据类型
alter table 表名 modify 字段名 新的字段类型;
4.删除字段
alter table 表名 drop 字段名;
5.修改表名
alter table 表名 rename to 新表名;
6.删除表
drop table if exists 表名;
7.清空表
delete from 表名;
8.删除指定表,重新新建表
truncate table 表名;
DML
数据的操作语言,用来操作数据库表中的数据(增删改)
添加数据
1.给指定字段添加数据
insert into userinfo(name,age,sex) VALUES('张三',26,'男');
2.给全部字段添加数据
insert into userinfo VALUES(3,'李四',25,'女',1);
3.批量添加数据
insert into userinfo VALUES(4,'王五',24,'女',1),(5,'李四',25,'女',1);
修改数据
UPDATE userinfo SET name='赵六' where id = 3;
删除数据
DELETE from userinfo WHERE id =4;
DQL
数据的查询语言,用来查询数据表中的数据(记录)
select
字段列表
from
表名
where
条件列表
group by
分组字段列表
order by
排序字段列表
limit
数量
select name,COUNT(name) from userinfo GROUP BY name;
1. 去重
SELECT DISTINCT name FROM userinfo;
2.条件查询
-- 统计年龄小于20的人数
SELECT count(name) as count FROM userinfo WHERE age <= 20;
-- 显示在20到30之间的名字
SELECT name FROM userinfo WHERE age BETWEEN 20 AND 30;
SELECT name FROM userinfo WHERE age >= 20 AND age <= 30;
-- in
-- SELECT name in(SELECT)
-- AND
SELECT * FROM userinfo WHERE age = 20 AND id = 6;
-- 查询姓名是两个字的
SELECT * FROM userinfo WHERE name LIKE "___";
SELECT * FROM userinfo WHERE name = "张一三";
-- 查询姓名中以三结尾的信息
SELECT * FROM userinfo WHERE name LIKE "%三";
-- 查询姓名中以张开头的信息
SELECT * FROM userinfo WHERE name LIKE "张%";
-- 查询姓名中包含“三”字的信息
SELECT * FROM userinfo WHERE name LIKE "%三%";
3.聚合函数
-- max
SELECT
*
FROM
userinfo
WHERE age in (SELECT MAX(age) as max_age FROM userinfo);
-- min
SELECT
*
FROM
userinfo
WHERE age in (SELECT MIN(age) as min_age FROM userinfo);
-- sum
SELECT SUM(age) FROM userinfo WHERE name LIKE "%三%";
4.排序查询
select 字段列表 from 表名 order by 字段1 升序/降序;
-- 对userinfo表中的年龄进行升序排序
SELECT * FROM userinfo ORDER BY age asc;
-- 对userinfo表中的年龄进行降序排序
SELECT * FROM userinfo ORDER BY age desc;
5.分页查询
select * from 表名 limit 起始索引,查询记录总数;
-- 查询前三条信息
SELECT * FROM userinfo LIMIT 0,3;
SELECT * FROM userinfo LIMIT 3;
-- 查询紧接着三条信息
SELECT * FROM userinfo LIMIT 3,3;
6.执行顺序
select
字段列表
from
表名
where
条件列表
group by
分组字段列表
order by
排序字段列表
limit
数量
--顺序
from
表名
where
条件列表
group by
分组字段列表
select
字段列表
order by
排序字段列表
limit
分页参数
DCL
数据控制语言,用来创建数据库用户、控制数据库的访问权限
管理用户
1.查询用户
-- 查询用户
SELECT * FROM user;
2.创建用户
-- 创建用户
CREATE user 'soft'@'%' IDENTIFIED by '123456';
权限管理
1.授予权限
GRANT SELECT on animes.* to 'soft'@'localhost';
GRANT INSERT on animes.sys_role to 'soft'@'localhost';
GRANT ALL on animes.* to 'soft'@'localhost';
2.撤销权限
REVOKE SELECT on animes.* from 'soft'@'localhost';
函数
字符串函数
-- Hello mysql
/*
字符串的拼接函数
*/
SELECT CONCAT('hello','mysql');
/*
全部将大写字母转换成小写字母
*/
SELECT LOWER('AbCD');
/*
全部将小写字母转换成大写字母
*/
SELECT UPPER('AbCD');
/*
左填充
*/
SELECT LPAD('121',5,'0');
/*
右填充
*/
SELECT RPAD('121',5,'0');
/*
去除空格
*/
SELECT TRIM('Hello MySQL');
/*
截取字符串
*/
SELECT SUBSTRING('Hello MySQL',1,5);
数值函数
/*
向上取整
*/
SELECT CEIL(1.1);
/*
向下取整
*/
SELECT floor(1.1);
/*
求参数x的四舍五入的值
如果是两位数,后面的一个参数是需要保留的小数
*/
SELECT ROUND(2.56,1);
/*
取模
*/
SELECT MOD(10,3);
/*
随机数
*/
SELECT RAND();
日期函数
/*
返回当前日期
*/
SELECT CURDATE();
/*
返回当前时间
*/
SELECT CURTIME();
/*
返回日期和时间
*/
SELECT NOW();
/*
单独获取年月日
*/
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
/*
给定日期计算天数
*/
SELECT DATEDIFF(CURDATE(),'2023-7-18');
/*
增加时间间隔
*/
SELECT date_add(now(),INTERVAL 70 YEAR);
流程函数
/*
if
*/
SELECT IF(1>2,'ok','Error');
/*
IFNULL(expr1,expr2)
*/
SELECT IFNULL(null,'456');
约束
概念:约束是用在表字段中的规则,用来限制存储在表中的数据
非空约束
唯一约束
默认约束
检查约束(8.0)
外键约束
NO ACTION :在删除父表中的记录的时候,会首先检查是否存在外键,如果有,则不允许被删除
RESTRICT :在父表删除记录的时候,首先检查是否存在外键,如果存在不允许删除(默认值)
cascade :当父表中的数据被删除的是偶,检查是否有外键,如果有外键,不允许删除,当父表数据更新的时候,会更改 对应外键的记录
set null :当父表中的数据删除或者更新的时候,都把对应的外键的值设置成null
多表查询
多表关系
一对多
案例:部门和员工的关系
多对多
案例:学生、成绩、课程之间的关系
一对一
案例:学生和学生信息之间关系
多表查询概述
数据准备
员工表(emp)
id : 序号
name : 姓名
age :年龄
job : 职位
salary :薪资
entrydate : 入职时间
managerid : 直属领导
dept_id : 部门id
部门表(dept)
id : 序号
name:部门名称
概述
笛卡尔积
两个集合中的数据一一批匹配
消除无效的笛卡尔积,保留两张表中有关联的数据:
SELECT * FROM dept,emp WHERE dept.id = emp.dept_id;
分类
内连接
1.隐式内连接
-- 查询每一个员工的姓名以及关联的部门的名称(隐式内连接实现)
SELECT emp.name,dept.name FROM emp,dept WHERE dept.id = emp.dept_id;
2.显式内连接
-- 显式内连接(inner JOIN)
SELECT e.name,d.name FROM emp e JOIN dept d on e.dept_id = d.id;
外连接
1.左外连接
-- 查询emp表中所有数据,和对应的部门信息
SELECT e.*,d.name FROM emp e LEFT OUTER JOIN dept d on e.dept_id = d.id;
2.右外连接
-- 查询dept表中的所有数据,和对应 的员工的信息
SELECT d.*,e.* FROM emp e RIGHT OUTER JOIN dept d on e.dept_id = d.id;
3.自链接
-- 查询员工及其所属的领导的名字
SELECT a.name,b.name FROM emp a,emp b WHERE a.managerid = b.id;
注意:自连接查询,必须为表起别名。
-- 联合查询
/*
要去查询薪资低于5000的员工,和年领大于50的员工的全部信息。
*/
SELECT * FROM emp WHERE salary < 5000 or age > 50;
SELECT * FROM emp WHERE salary < 5000
UNION ALL
SELECT * FROM emp WHERE age > 50;
子查询
根据查询结果不同:
标量子查询(子查询结果为单个值)
列子查询(子查询结果为一列)
行子查询(子查询结果为一行)
表子查询(子查询结果为多行多列)
根据子查询结果位置不同:
where之后
from之后
select之后
标量子查询
/*
查询销售部的所有的员工信息
1.查询销售部的id
2.根据销售部的id,查询员工信息
*/
SELECT id FROM dept WHERE name='销售部';
SELECT * FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name='销售部');
/*
查询在‘方东白’入职后的员工的信息
1.查询‘方东白’的入职日期
2.根据‘方东白’的入职日期查询之后入职的员工信息
*/
SELECT entrydate FROM emp WHERE name='方东白';
SELECT * FROM emp WHERE entrydate > (SELECT entrydate FROM emp WHERE name='方东白');
列子查询
IN、NOT IN
/*
查询“销售部”和“市场部”的所有的员工信息
1.查询“销售部”和“市场部”的id
2.根据两个部门的id,查询员工信息
*/
SELECT id FROM dept WHERE name='销售部' or name='市场部';
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE name='销售部' or name='市场部');
/*
查询比财务部所有人工资都高的员工信息
1.查询财务的id,根据id查询所有人的薪资
2.查询比财务部所有人工资高的人员信息
*/
SELECT id FROM dept WHERE name='财务部';
SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name='财务部');
SELECT * FROM emp WHERE salary > ALL(SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name='财务部'));
/*
查询比研发部其中任意一人工资高的员工信息
ANY
1.查询研发部的id,根据id查询所有的员工薪资
2.比研发部其中一人工资高的员工信息
*/
SELECT id FROM dept WHERE name = '研发部';
SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '研发部');
SELECT * FROM emp WHERE salary > any(SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '研发部'));
行子查询
/*
查询与‘张无忌’的薪资及直属领导相同的员工信息
1.查询‘张无忌’的薪资和指数领导
2.查询相同的员工信息
*/
SELECT salary,managerid FROM emp WHERE name = '张无忌';
SELECT * FROM emp WHERE (salary,managerid) = (SELECT salary,managerid FROM emp WHERE name = '张无忌');
表子查询
/*
查询与‘鹿杖客’,‘宋远桥’的职位和薪资相同的员工信息
1.查询‘鹿杖客’,‘宋远桥’的职位和薪资
2.查询相同的员工信息
*/
SELECT job,salary FROM emp WHERE name = '鹿杖客' or name = '宋远桥' ;
SELECT * FROM emp WHERE (job,salary) in (SELECT job,salary FROM emp WHERE name = '鹿杖客' or name = '宋远桥' );
/*
查询入职日期是‘2006-01-01’之后入职的员工信息,及部门信息
1.查询入职日期是‘2006-01-01’之后入职的员工信息
2.根据员工信息中的部门id,查询对应的部门信息
*/
SELECT * FROM emp WHERE entrydate > '2006-01-01';
SELECT e.*,d.* FROM (SELECT * FROM emp WHERE entrydate > '2006-01-01') e LEFT JOIN dept d on e.dept_id = d.id;
视图
概念:视图就是把一个表或者多个表做一个数据集合,可以通过select进行查询。
特点:
1.创建视图所依据的表叫做“基表”
2.基表可以是一个,也可以是多个
3.视图中的数据可以随意修改
4.如果修改违反了约束,也不能修改
5.视图的优点:安全性、隐私性更强,简化一些sql语句。
创建视图
create view 视图名 as 查询语句;
/*
创建一个视图:
4号部门的员工编号,员工姓名,职位
*/
CREATE VIEW v_emp4
as SELECT id,name,job FROM emp WHERE dept_id = 4;
在视图上实现DML操作
1.查询
/*
从视图中查询职位是销售的人员名称
*/
SELECT name FROM v_emp4 WHERE job='销售';
2.插入
/*
向视图中插入一条数据
*/
INSERT INTO v_emp4 VALUES(18,"张三","销售");
3.更新
4.删除
DROP VIEW v_emp4;
5.查看当前创建的所有试图
SELECT * FROM information_schema.VIEWS;
/*
创建一个视图:
公司的员工信息:总人数,平均工资,总工资,最高工资,最低工资
每个部门的员工信息:部门的编号、部门名称、部门的总人数、部门的平均工资,部门的最高工资和最低工资
*/
CREATE VIEW v_report
as
SELECT
(SELECT COUNT(*) FROM emp) 总人数,
(SELECT AVG(salary) FROM emp) 平均工资,
(SELECT SUM(salary) FROM emp) 总工资,
(SELECT max(salary) FROM emp) 最高工资,
(SELECT MIN(salary) FROM emp) 最低工资,
e.dept_id 部门编号,
d.name 部门名称,
COUNT(e.id) 部门总人数,
AVG(e.salary) 部门平均工资,
MAX(e.salary) 部门最高工资,
MIN(e.salary) 部门最低工资
FROM
emp e LEFT JOIN dept d
ON
e.dept_id = d.id
GROUP BY
e.dept_id;
索引
1.创建索引
/*
INDEX:普通索引
UNIQUE: 唯一索引
PRIMARY KEY: 主键索引
FULLTEXT: 全文索引
SPATIAL: 空间索引
*/
CREATE INDEX index_disname on disease(DiseaseName);
2.删除索引
DROP INDEX index_disname on disease;
3.查看索引
SHOW INDEX FROM disease;