2021-03-03MySQL数据库

MySQL数据库

介绍

MySQL是一个关系型数据库管理系统，是由瑞典的mysql AB公司开发，目前属于oracle。

特点

• 开源
• 支持大型数据库，可以处理千万级条的记录。
• 使用标准的sql语言
• 可以运行在多个系统上。

MySQL安装

官网下载

https://dev.mysql.com/downloads/mysql

phpstudy集成环境

下载地址：https://www.xp.cn

mysql中几个概念

仓库：

​ database 类似于文件柜。

每一个仓库由多个表（table）组成。 类似于一个文件。

每一个表由 表头（字段） 和 表体（记录）。

数据导入

表上面右键，在菜单中选择运行sql文件，选择要运行的sql文件。执行即可。

数据库操作

创建数据库

create database 数据库名;
# 创建一个名为yunmeng的数据库
create database yunmeng;
# 指定数据库的字符集以及排序方式
create database yunmeng DEFAULT CHARACTER set utf8 COLLATE utf8_general_ci; 

查看所有的数据库

show databases;

修改数据库的编码格式

alter DATABASE 数据库名 DEFAULT CHARACTER set 编码格式;
alter DATABASE yunmeng DEFAULT CHARACTER set utf8;

删除数据库

DROP DATABASE 数据库名；
DROP DATABASE yunmeng；
DROP DATABASE IF EXISTS 数据库名;

表操作

创建表

create table 表名(
	字段1 类型1，
	......
	字段n 类型n
);

【注】常见类型： int 整型，varchar 字符串

create table teacher(
    id int(11) COMMENT '用户id',
    name varchar(255)
)

查看所有的表

show TABLES

修改表名

alter table 原表名 rename 新表名;
alter table teacher rename teacher2;

查看表的字段信息

desc 表名;
desc student;

修改字段名

alter TABLE 表名 change 原字段名 新字段名 新字段名类型
alter TABLE teacher2 change age name VARCHAR(20)

修改字段名的类型

alter TABLE teacher2 MODIFY name varchar(50)

添加字段

alter TABLE 表名 add 字段名 字段类型; first添加到第一行。after 字段名：插入到该字段后。
alter TABLE teacher2 add age varchar(10) first;

删除字段

#英文波浪号键打出来``
ALTER TABLE `teacher2` DROP COLUMN `age`;

删除表

drop table 表名;
drop table student;

数据的操作

添加数据

INSERT INTO hero(id,name,age) VALUES(1,'张三',20);
INSERT INTO hero VALUES(2,'liqing',30);
INSERT INTO hero set id = 3,name = 'nuoke',age = '40'
INSERT INTO hero VALUES(4,'v',26),(5,'ironman',80)

修改数据

# 批量更新
UPDATE hero SET age = 20
#根据 where后的条件，修改符合条件的数据。
UPDATE hero SET age = 20 WHERE id = 1;

删除数据

# 直接将表中所有的数据删除
DELETE FROM 表名;
# 条件删除 根据where语句后的条件，将满足条件的数据删除。
DELETE FROM hero WHERE id < 3;

单表查询

#查询全部记录，全部字段。
select * from student
#查询指定字段
select name,score from student
# between and 范围查询
select name,score from student WHERE score BETWEEN 80 AND 90
或者：select name,score from student WHERE score >80 and score<90
# 查询指定字段为null值的记录
SELECT * FROM student WHERE score is null
# 查询指定字段不为null值的记录
SELECT * FROM student WHERE score  is not null
# DISTINCT 关键字返回不重复的值。重复值会过滤掉。
SELECT DISTINCT name FROM hero 
# 模糊瓶匹配
SELECT * FROM student WHERE name like '杨%'

条件查询

where 后的条件判断。

 = < <= > >=

SELECT * FROM student WHERE score >=80

AND OR

# and两边的条件必须都成立，才返回该记录。
SELECT * FROM student WHERE score >=80 AND gender = '女'
# OR 两边的条件只要有一个成立，则返回该记录
SELECT * FROM student WHERE score >=80 OR gender = '女'

IN

# 在where子句中可以规定多个值。
SELECT * FROM student WHERE score in(79,89,99)

高级查询

聚合函数

count()

# 返回查询结果的记录数。
SELECT count(*) FROM student WHERE score in(79,89,99)

sum()

# 对返回结果中的指定字段进行求和
SELECT sum(score) FROM student WHERE score  = 79

avg()

# 对指定的字段进行求平均数
SELECT avg(score) FROM student 

max()

# 求指定值的最大值
SELECT max(score) FROM student 

min()

# 求指定值的最小值
SELECT min(score) FROM student 

查询最大值

select * from student where score = (select max(score) from student)

排序

SELECT * FROM student ORDER BY score DESC

【注】asc 默认 递增 desc 递减

分组查询

# group by 按照指定字段分组，一般配合聚合函数使用。
SELECT count(*),score FROM student GROUP BY score

#having 关键字 是对分组查询后的结果进行条件查询。
SELECT count(*) ,score FROM student GROUP BY score HAVING count(*)>20

分页查询

SELECT * from student LIMIT 查询开始的位置,本次查询的条数
SELECT * from student LIMIT 10,10
#limit后的参数为本次查询的条数
SELECT * from student LIMIT 10
# limit关键字可以配合order by使用
SELECT * from student ORDER BY score DESC LIMIT 0,20

函数

# 查询当前时间
select now() from student
#字符串拼接
select concat(id,'+',name,'+',score) from  student

别名

# 为表添加别名，as关键字可以省略
select s.* FROM student as s WHERE s.name = '孙九二'
#为字段取别名
select s.name as '姓名' FROM student as s WHERE s.name = '孙九二'

多表查询

左链接

SELECT * from student stu left JOIN class c ON stu.cid = c.id

复合条件查询

#可以将查询结果作为where后的查询条件判断。
select * from student WHERE cid = (select id FROM class WHERE classname = '云梦山')

exists

# EXISTS 关键字 只返回true或者false
select * from student WHERE EXISTS (select id from class WHERE classname = '云梦')

练习

1. 用一条SQL 语句 查询出每门课都大于80 分的学生姓名

name	course	grade
张三	语文	81
张三	数学	75
李四	语文	76
李四	数学	90
王五	语文	81
王五	数学	100
王五	英语	90

解答：

SELECT name FROM biao GROUP BY name HAVING min(grade)>80

2. 现有学生表如下:
id 学号 姓名 课程编号 课程名称 分数
1 2005001 张三 0001 数学 69
2 2005002 李四 0001 数学 89
3 2005001 张三 0001 数学 69
删除除了自动编号不同, 其他都相同的学生冗余信息

DELETE
FROM
	student
WHERE
	id NOT IN (
		SELECT
			*
		FROM
			(
				SELECT
					min(id) mid
				FROM
					student
				GROUP BY
					s_no,
					s_name,
					c_no,
					c_name,
					score
			) a
	)

三.课程表 Course

CREATE table Course(
Cid varchar(255),
Cname varchar(255),
Tid varchar(255)
)

INSERT INTO course(Cid,Cname,Tid) VALUES('01','语文','01')
INSERT INTO course(Cid,Cname,Tid) VALUES('02','数学','02')
INSERT INTO course(Cid,Cname,Tid) VALUES('03','英语','03')

成绩表 SC

CREATE table SC(
Sid varchar(255),
Cid varchar(255),
score varchar(255)
)

INSERT INTO SC(Sid,Cid,score) VALUES('07','03','98.0')

教师表 Teacher

CREATE table Teacher(
Tid varchar(255),
Tname varchar(255)
)

INSERT INTO teacher(Tid,Tname) VALUES('01','张三')，('02','李四')，('03','王五')

学生表 Student

CREATE table student(
Sid varchar(255),
Sname varchar(255),
Sage varchar(255),
Ssex varchar(255)
)

INSERT INTO student(Sid,Sname,Sage,Ssex) VALUES('01','赵雷','1990-01-01 00:00:00','男'),('02','钱电','1990-12-21 00:00:00','男'),('03','孙风','1990-05-20 00:00:00','男')

INSERT INTO student(Sid,Sname,Sage,Ssex) VALUES('04','李云','1990-08-06 00:00:00','男'),('05','周梅','1991-12-01 00:00:00','女'),('06','吴兰','1992-03-01 00:00:00','女'),('07','郑竹','1989-07-01 00:00:00','女'),('08','王菊','1990-01-20 00:00:00','女')

四张表的关系如下

1.查询在 SC 表存在成绩的学生信息

SELECT * FROM student WHERE sid in(SELECT sid from sc)

**2.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩 **

select s.Sid,s.Sname,count(Cid) as 选课数量,sum(score)as 总成绩 FROM student as s LEFT JOIN sc ON s.Sid = sc.Sid GROUP BY s.sid

3.查询「李」姓老师的数量

这种更好：select count(Tid) FROM teacher WHERE Tname like '李%'
SELECT count(*) FROM teacher WHERE Tname like '李%'

4.查询 01 课程分数小于 60的学生信息，按分数降序排列

select * FROM student s LEFT JOIN sc ON s.Sid = sc.Sid WHERE Cid = '01' AND score <60 ORDER BY score DESC

5.按平均成绩从高到低显示所有学生的sid以及平均成绩

select s.Sid,avg(score) FROM student s LEFT JOIN sc ON sc.Sid = s.Sid GROUP BY s.Sid ORDER BY avg(score) DESC

6.查询语文成绩排名前三的学生

SELECT sc.* FROM sc,course WHERE sc.Cid = course.Cid AND course.Cname='语文' ORDER BY score DESC LIMIT 3

SELECT * FROM sc WHERE Cid = '01' ORDER BY score DESC LIMIT 3

SELECT * FROM sc s LEFT JOIN course c on s.cid=c.cid WHERE c.cname='语文' ORDER BY score desc LIMIT 3

7.查询名字中含有「雷」字的学生信息

SELECT * FROM student WHERE Sname like '%雷%'

8.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

SELECT s.Sid,s.Sname,avg(score) FROM student s LEFT JOIN sc ON s.Sid = sc.Sid GROUP BY s.Sid HAVING avg(score)>=60

================================================================

9. 查询学过「张三」老师授课的同学的信息

SELECT
	*
FROM
	student s
WHERE
	s.Sid IN (
		SELECT DISTINCT
			Sid
		FROM
			sc
		LEFT JOIN course c ON sc.Cid = c.Cid
		LEFT JOIN teacher t ON t.Tid = c.Tid
		WHERE
			t.Tname = '张三'
	)

10.查询没有学全所有课程的同学的信息

SELECT
	*
FROM
	student s
WHERE
	s.Sid IN (
		SELECT
			s.Sid
		FROM
			student s
		LEFT JOIN sc ON s.Sid = sc.Sid
		GROUP BY
			Sid
		HAVING
			count(Cid) < 3
	)

11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT s.Sid,s.Sname,avg(score) FROM student s RIGHT JOIN sc ON sc.Sid = s.Sid WHERE score <60 GROUP BY Sid

练习二

1、将id为20的学生删除

DELETE FROM student WHERE id=20

2、将id为12的学生名字修改为许三多

UPDATE student set name='许三多' where id=12

3、统计每个班级的学生人数

SELECT c.name,COUNT(*) as 学生人数 FROM student s LEFT JOIN class c on c.id=s.class GROUP BY class

4、查询所有 张姓 学生的信息

SELECT * FROM student WHERE name LIKE '张%'

5、查询所有年龄在20岁及以上的学生信息

SELECT * FROM student WHERE age>=20

6、将学生按年龄最大的二十名学生。

SELECT *FROM student ORDER BY age DESC LIMIT 20

7、查询所有云梦山学生的平均成绩

SELECT AVG(score) FROM student s LEFT JOIN class c on c.id =s.class WHERE c.name='云梦山'

8、查询每个班成绩最高的同学

SELECT c.name,MAX(score) FROM student s LEFT JOIN class c on c.id=s.class GROUP BY class

9、计算每个班学生的总成绩

SELECT c.name,sum(score) FROM student s LEFT JOIN class c on c.id=s.class GROUP BY s.class