javaWeb-MySQL

---

一、MySQL是什么？

数据库是一个文件系统，通过SQL语句获取数据

二、MySQL的使用步骤

1.安装

见：https://blog.youkuaiyun.com/weixin_44675308/article/details/119680735

2.创建数据库

通过原生mysql创建数据库
见：https://blog.youkuaiyun.com/qq_41063760/article/details/81603809

通过Navicat软件进行可视化创建

3.MySQL的常用语句

create database web_test;
use web_test;
create table user(
	id int primary key auto_increment,
	username varchar (20) unique,
	password varchar (20) not null,
	age int,
	create_time timestamp default CURRENT_TIMESTAMP
);

对数据库操作

//创建数据库
create database 数据库名称;

//查看新建的数据库信息
show create database 数据库名称;

//修改数据库
alter database 数据库名称

//删除数据库
drop database 数据库名称

对表操作

//创建表，约束的作用是为了抱数据的完整性(主键约束、唯一约束、非空约束)
create table 表名称(
	字段1名称 字段1类型(长度) 约束1, 
	字段2名称 字段2类型(长度) 约束2
);

//查看所有表
show tables;
//查看某个表
desc 表名;

//删除表
drop table 表名;

//修改表
//修改表-1.添加列
alter table 表名 add 列名 类型(长度) 约束;
//修改表-2.修改列 (相当于新建一个一样的列来覆盖原列)
alter table 表名 modify 列名 类型(长度) 约束;
//修改表-3.删除列
alter table 表名 drop 列名;
//修改表-4.修改列名 (也相当于覆盖操作)
alter table 表名 change 旧列名 新列名 类型(长度) 约束;
//修改表-2.修改表名
rename table 表名 to 新的表名;

//清空表中的所有记录
delete from [tableName];	//逐行删除，不适合大量的删除数据 (可回滚)
truncate table [tableName];	//删除表数据(要有drop权)，但是会保留表的结构 (不可回滚)
drop table [tableName];		//整个表都删除

-- 创建表
create table ts_table(
	ts_id int(10) primary key not null auto_increment,
	ts_username varchar(20) not null,
	ts_password varchar(20) not null,
	ts_createTime timestamp default CURRENT_TIMESTAMP
);

-- 查看该数据库的所有表
show tables;

-- 修改表
-- 1.增加列
alter table ts_table add ts_gender int(1) not null;
-- 2.修改列
alter table ts_table modify ts_gender int(1);
-- 3.修改列名
alter table ts_table change ts_gender ts_sex int(1);
-- 4.修改表名
rename table ts_table to table_ts;

-- 清除表内所有数据
-- 1.delete
delete from table_ts;
-- 2.truncate
truncate table table_ts;
-- 3.drop
drop table table_ts;

对记录操作

//添加记录-1.向表中插入某些列，值为日期类型的要用''引起来
insert into 表名 (列名1, 列名2...) values(值1, 值2...);
//添加记录-2.向表中插入所有列，值为日期类型的要用''引起来
insert into 表名 values(值1, 值2...);

//删除记录，[]根据需要添加，不添加条件则直接删除表，否则删除符合添加的列
drop from 表名 [where 条件];

//修改记录，[]根据需要添加
update 表名 set 列名=值, 列名=值 [where 条件];

//查看记录
//查看记录-1.查看所有信息
select * from 表名;
//查看记录-2.查看某些列
select 列1,列2 from 表名;
//查看记录-3.条件查询
select 列1,列2 from 表名 where 条件1;
//查看记录-4.模糊查询
select 列1,列2 from 表名 where 条件1 like %关键字%;
//查看记录-5.范围查询
select 列1,列2 from 表名 where 条件1 in <范围1, 范围2, 范围3>;
//查看记录-6.排序查询：asc升序/desc降序(默认为升序，[]按需要加)
select 列1,列2 from 表名 [where 条件] order by 列名1 排序方式, 列名2 排序方式;

聚合函数的使用

//求和，[]按需要添加
select sum(列名1),sum(列名2) from 表名 [where 条件]
select sum(列名1)+sum(列名2) from 表名 [where 条件]		//2列之合

//求总数
select count(*) from 表名 [where 条件]

//获取最大/最小值
select max(列名) from 表名
select min(列名) from 表名

//获取平均值
select avg(列名) from 表名

分组查询

select 列名, 统计方式() from 表名 group by 列名 [having 统计方式() 条件 order by 统计方式 排序方式]

//如：按商品名称统计，统计每类商品花费的金额在5000以上的商品，并按照总金额升序排序
select product, sum(price) from orderitem group by product having sum(price) > 5000 order by sum(price) asc;

三、★多表设计★

1.外键约束

当多个表之间存在相连的关系时，用外键约束来保证多表之间的数据完整性

//表1
create table dept(
	d_id int primary key auto_increment,
	d_deptName varchar(20)
);
insert into dept values (null, '市场部');
insert into dept values (null, '人事部');
insert into dept values (null, '教研部');
//表2
create table employee (
	e_id int primary key auto_increment,
	e_name varchar(20),
	salary double,
	birthday date,
	sex varchar(10),
	d_department int
);
insert into employee values (null, '张三', 8000, '1988-09-01', '男', 3);
insert into employee values (null, '李四', 9000, '1988-09-01', '男', 1);
insert into employee values (null, '王五', 6000, '1988-09-01', '男', 2):
insert into employee values (null, '赵六', 10000, '1988-09-01', '男', 3);
insert into employee values (null, '孙七', 10000, '1988-09-01', '男'，1);
//添加外键约束
alter table employee add foreign key (d_department) references dept (di_d);
//设置外键非空
alter table employee modify de_department int not null;

2.一对多的表关系

建表原则：在多的一方创建外键指向一的一方的主键(多指向一)
一个部门下可以有多名员工，但是一名员工只能属于一个部门

3.多对多的表关系

多对多关系建表原则：创建第三张表(中间表)，在中间表中至少存放两个字段分别作为外键，指向双方的主键
一个学生可以选择多门课，一门课程也可以被多个学生选择

4.一对一的表关系(略)

有A、B两张表 A表中的每条的记录对应的B的一条记录(不常见，一般是建在同一个表内)
一个学生只能有一个学号，而一个学号也只能对应一个学生

四、多表查询

1.连接查询

交叉连接(查询两个表的笛卡尔积，即两者的交集)

select * from 表1 cross join 表2;
select * from 表1, 表2;

内连接(查不到值为空的列)

//显示内连接：在SQL中显示的调用inner join关键字。语法，
select * from 表1 inner join 表2 on关联条件;
//隐式内连接：在SQL中没有调用inner join关键字。语法,
select * from 表1,表2 where 关联条件;

外连接(可以查到值为空的列)

//左外连接
select * from 表1 left outer join 表2 on 关联条件;
//右外连接
select * from 表1 right outer join 表2 on 关联条件;

2.子查询

子查询：一个查询语句A需要依赖另一个查询语句B的结果 (A以B的结果为条件)
带in的子查询

//查询学生生日在91年之后的班级的信息
select * from classes where class_id in (SELECT stu_classId FROM student WHERE birthday > '1991-01-01');

带exists的子查询
只要内层子查询的信息存在，就返回一个结果作为外层查询条件

//查询学生生日大于91年之后的班级的信息是否存在，存在则会执行外面的sql语句
select * from classes where exists (SELECT stu_classId FROM student WHERE birthday > '1991-01-01');

带any的子查询
只要满足内层子查询中的任意一个比较条件，就返回一个结果作为外层查询条件

SELECT * FROM classes where class_id > ANY (SELECT stu_classId FROM student);

带all的子查询

SELECT * FROM classes where class_id > all (SELECT stu_classId FROM student);

练习

1.学校选课系统

在选课系统中包含班级，学生和课程实体
(1)班级和学生之间是有关系存在：一个班级下包含多个学生，一个学生只能属于某一个班级(一对多的关系)
(2)学生和课程之间是有关系存在： 一个学生可以选择多门课程，一门课程也可以被多个学生所选择(多对多的关系)
classes表

student表，外键约束为

course表

stu_course表，外键约束为

E-R图

2.多表查询

查询班级名称和班级总人数

//stu_classId是一对多外键
select class_name,count(*) from classes c,student s where s.stu_classId group by c.class_name;

查询学生的姓名和学生所选课程总成绩的平均分

//stu_course是一对一外键的中间表
select s.stu_name,avg(sc.score) from student s, stu_course sc where s.stu_id = sc.score_stuId group by s.stu_name;

查询学生的姓名和学生的选课总数，显示选课超过两门的学生姓名

select s.stu_name,count(*) from student s,stu_course sc where s.stu_id = sc.score_stuId group by s.stu_name having count(*) > 2;

查询平均成绩大于80分的学生总数

select count(*) from student s where s.stu_id in (select sc.score from stu_course sc group by sc.score_stuId having avg(sc.score) > 80);

查询学生平时成绩大于01班的任何一个学生的平均成绩

select s.stu_name,avg(sc.score) from student s,stu_course sc where s.stu_id = sc.score_stuId group by s.stu_name having avg(sc.score) > any(select s.stu_name,avg(sc.score) from student s,stu_course sc,classes c where s.stu_id = sc.score_stuId and s.stu_classId = c.class_id and c.class_name = '01班' group by s.stu_name);