文章目录
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