MySql学习笔记
1. 安装
可以直接去官网下载安装包(地址:https://www.mysql.com/downloads/)
也可以直接安装Maria DB,会同步安装HeidiSQL数据库管理的图形化界面
不管是用哪种方式,如果想在命令行通过命令操作,都要把mysql的bin目录配置到环境变量,要不然控制台不识别mysql
2. 创建数据库
工具的话就是直接邮件新建即可
命令行
- mysql -uroot -p 密码就是安装的时候设置的
- create database 数据库名;
- use 数据库名
3. 创建表
接下来以练习的形式整理笔记
# 1. 创建学校表 id name
create table school (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL
)
# 2. 创建班级表 id name sid
CREATE TABLE class (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
sid INT NOT null
)
# 3. 创建学生表 id name sex birthday cid
CREATE TABLE stu (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
sex CHAR(2) DEFAULT '男',
birthday DATE NOT NULL,
cid INT NOT null
)
# DESC school 查看表结构
# DROP TABLE if EXISTS stu 如果存在就删除表
4. 添加数据
# 4. 添加学校数据
INSERT INTO school (id,NAME) VALUES (NULL,'清华大学')
INSERT INTO school (NAME) VALUES ('复旦大学')
INSERT INTO school VALUES (NULL,'浙江大学')
# 5. 添加班级数据
INSERT INTO class VALUES(NULL,'浙大英语班',3),(NULL,'浙大数学班',3),(NULL,'浙大艺术班',3)
# 6. 添加学生
INSERT INTO stu VALUES
(NULL,'清华name1','男','1990-09-04',1),
(NULL,'清华name2','女','1990-04-04',2),
(NULL,'清华name3','男','1990-03-04',2),
(NULL,'清华name4','女','1990-06-04',1),
(NULL,'复旦name1','男','1990-02-04',1),
(NULL,'复旦name2','男','1990-03-04',1),
(NULL,'复旦name3','女','1990-04-04',2),
(NULL,'浙大name1','男','1990-05-04',1),
(NULL,'浙大name3','男','1990-06-04',2),
(NULL,'浙大name3','女','1990-07-04',3)
5. 删除数据
DELETE FROM class WHERE id = 2 # 删除班级表中id为2的数据
/*
删除数据 delete from 表名 (只删除数据 ,自增不清空)
清空数据 truncate table 表名 (可删除数据,自增清空,从1开始)
删除具体数据(条件)
将学校表中的数据都删除
!= 不等于 与 <> 一个意思
删除编号为2和8的数据 用 OR 或者用 where id in (2,8)
删除编号不是2和8的数据 where id not in(2,8)
条件从小到大 从后往前
*/
6. 修改数据
# 修改清华大学的名称为‘清华大学-名校’
UPDATE school SET NAME='清华大学-名校' WHERE id = 1
# 将1999年以后出生的学生 性别改为女 班级改为 2 多列修改 逗号隔开即可
UPDATE stu SET sex='女' , cid=2 WHERE birthday >= '1999-01-01'
7. 查询数据
# 查询所有的学生信息
SELECT * FROM stu
SELECT id, name, sex, birthday, cid FROM stu
# 重命名
# 查询学生的编号、姓名、生日 列名为 stuId sruName stuBirthday --- 列名 重命名
SELECT id AS stuId ,name AS stuName,birthday AS stuBirthday FROM stu
# 查询学生的编号、姓名、生日 --- 表名 重命名
SELECT s.id, s.name, s.birthday FROM stu s
# 模糊查询 like -- 模糊匹配相等的 not like -- 模糊匹配不相等的
/*
% -- 代表0、1个或多个任意字符
_ -- 代表任意单个字符 可以写多个 代表多个
*/
# 查询name1的学生信息、没有清的学生 、清开头的7个字符名字的学生
SELECT * FROM stu WHERE NAME = '清华name1'
SELECT * FROM stu WHERE NAME NOT LIKE '清%'
SELECT * FROM stu WHERE name like '清______'
# 查询名字中含有name1的学生
SELECT * FROM stu WHERE NAME LIKE '%name1%'
8. 排序 order by
# 查询学生信息 按照id降序排序
SELECT * FROM stu ORDER BY id desc
# 查询学生信息 按照id升序排序
SELECT * FROM stu ORDER BY id asc
# 查询学生信息 按照生日升序排序
SELECT * FROM stu ORDER BY birthday asc
# 查询学生信息 按照年龄升序排序
SELECT * FROM stu ORDER BY birthday desc
# 查询学生信息 按照年纪排序 如果年纪相等 按照 学校排序 ---- 多列排序
SELECT * FROM stu ORDER BY birthday ASC, NAME asc
9. 分页 limit
# 查询学生表的信息 编号最大的两名学生
SELECT * FROM stu ORDER BY id DESC LIMIT 2
# 查询学生表的信息 1-5名 (左边从哪里开始取 右侧取几个)
SELECT * FROM stu LIMIT 0,5
SELECT * FROM stu LIMIT 5,1
# 查询最大年纪的学生信息 (下面的写法有问题)
SELECT * FROM stu ORDER BY birthday ASC LIMIT 0,1
10. 表连接
/*
笛卡尔积(交叉连接 2表各有2条数据 返回4组结果 无意义)
内连接(两种方式)要有条件
外连接(左外连接 右外连接)
*/
# 笛卡尔积
SELECT * FROM class CROSS JOIN school
# 笛卡尔积 - 简化版
SELECT * FROM class,school
# 内连接 --- inner join (小表的外键 去 连大表的主键 )
# 方式1
SELECT * FROM class INNER JOIN school ON class.sid = school.id
SELECT * FROM class JOIN school ON class.sid = school.id
SELECT * FROM class c INNER JOIN school s ON c.sid = s.id # 起别名也是可以的
# 方式2
SELECT * FROM class,school WHERE class.sid = school.id
SELECT * FROM class,school WHERE sid = school.id # 如果条件只出现一次 可以不写表
# 外连接 --- 左外连接 left join ( 左表全部展示 右表 展示符合的 不符合的 展示NULL )
# 查询每个班级的名称和学校名称 若学校名称不存在 正常查询
SELECT class.name AS className ,school.name AS schoolName FROM class left JOIN school ON class.sid = school.id
# 外连接 --- 右外连接 right join ( 右表全部展示 左表 展示符合的 不符合的 展示NULL )
# 查询每个学校的名称和班级名称 若学校名称不存在 则正常查询
SELECT school.name AS schoolName , class.name AS className FROM class RIGHT JOIN school ON sid = school.id
# 查询学生的姓名、班级的名称、学校名称
SELECT stu.name AS stuName, class.name AS className, school.name AS schoolName FROM stu JOIN class ON cid=class.id JOIN school ON sid = school.id
# 查询学生的姓名、年龄、班级的名称、学校名称
SELECT stu.name AS stuName, YEAR(NOW())-YEAR(stu.birthday) AS age, class.name AS className, school.name AS schoolName FROM stu JOIN class ON cid=class.id JOIN school ON sid = school.id
# SELECT YEAR(NOW()) 时间
11. 子查询
# 先执行括号内 再执行括号外 括号内的结果提供给括号内使用
# 查询班级编号为1的学生信息
SELECT * FROM stu WHERE cid = 1
# 查询英语班的学生信息
# 1. SELECT * FROM stu WHERE cid = '英语班编号'
# 2. SELECT id FROM class WHERE name ='复旦英语班'
# 3. SELECT * FROM stu WHERE cid = ( SELECT id FROM class WHERE name ='复旦英语班' )
SELECT * FROM stu WHERE cid in ( SELECT id FROM class WHERE name ='复旦英语班' ) # 如果不知道结果是几个 用in
# 查询清华大学的男学生 姓名 性别 年龄 班级 学校
# 表连接
SELECT stu.name,stu.sex,stu.birthday,class.name,school.name FROM stu JOIN class ON stu.cid=class.id JOIN school on class.sid = school.id WHERE stu.sex ='男' AND school.name = '清华大学-名校'
# 将英语班的男学生删除 -- 子查询
# 1. DELETE FROM stu WHERE cid = '英语班的编号'
# 2. SELECT id FROM class WHERE NAME = '英语班'
# 3. DELETE FROM stu WHERE sex= '男' AND cid IN (SELECT id FROM class WHERE NAME = '英语班')
# 将清华大学英语班的男学生删除 -- 子查询
# 1. DELETE FROM stu WHERE sex= '男' AND cid = (清华大学英语班的编号)
# 2. SELECT id FROM class WHERE NAME = '英语班' WHERE sid=(清华大学的id)
# 3. SELECT id FROM school WHERE NAME = '清华大学-名校'
# 4. DELETE FROM stu WHERE sex= '男' AND cid = (SELECT id FROM class WHERE NAME = '英语班' WHERE sid=(SELECT id FROM school WHERE NAME = '清华大学-名校'))
# 5. SELECT * FROM stu WHERE sex= '男' AND cid = (SELECT id FROM class WHERE NAME LIKE '%英语班%' AND sid=(SELECT id FROM school WHERE NAME = '清华大学-名校'))
# 查询北京大学和清华大学的英语班男生
SELECT * FROM stu WHERE sex= '男' AND cid IN (SELECT id FROM class WHERE NAME LIKE '%英语班%' AND sid IN (SELECT id FROM school WHERE NAME ='清华大学-名校' OR NAME = '复旦大学'))
SELECT * FROM stu WHERE sex = '男' AND cid IN (SELECT class.id FROM class JOIN school ON class.sid = school.id WHERE school.name='复旦大学' OR school.name = '清华大学-名校' AND class.name like '%英语班%')
12. 聚合函数
/*
count() 个数
max() 最大
min() 最小
avg() 平均值
sum() 求和
*/
CREATE TABLE score (
id int not null auto_increment primary key,
name varchar(20) not null,
yuwen int,
shuxue int,
yingyu int
)
INSERT INTO score VALUES(NULL,'小明',89,88,90),
(NULL,'小王',55,80,93),
(NULL,'小李',100,85,97),
(NULL,'小赵',82,76,94)
# 查询多少人参加了考试 SELECT COUNT(*或者列名 - 建议写主键) FROM score
SELECT COUNT(id) FROM score
# 查询语文成绩最高的学生及姓名
SELECT name,MAX(yuwen) FROM score
select name,yuwen from score where yuwen = (select max(yuwen) from score)
# 查询语文成绩最低的学生及姓名
SELECT name,MIN(yuwen) FROM score
select name,yuwen from score where yuwen = (select min(yuwen) from score)
# 查询语文的平均分
SELECT AVG(yuwen) from score
SELECT avg(yuwen+shuxue+yingyu) FROM score
SELECT sum(yuwen+shuxue+yingyu) FROM score
# 查询语文成绩高于语文平均成绩的姓名及成绩
SELECT AVG(yuwen) from score
SELECT name, yuwen from score where yuwen > (SELECT AVG(yuwen) from score)
# 查询每个学生的总分 --- 下面两个写法有问题 需要用分组
SELECT name AS '姓名', AVG(yuwen+shuxue+yingyu) AS '总分' FROM score
SELECT name AS '姓名',yuwen,shuxue,yingyu,AVG(yuwen+shuxue+yingyu) AS '总分' FROM score
13. 分组 group by having
# 查询每个学生的总分
SELECT name AS '姓名', AVG(yuwen+shuxue+yingyu) AS '总分' from score GROUP BY id
# 查询每个学生名字含有三的学生的总分 条件要用 HAVING 不可以用 WHERE
SELECT name AS '姓名', AVG(yuwen+shuxue+yingyu) AS '总分' from score GROUP BY id HAVING name like '%三%'
# 查询每个班级的名称和人数
SELECT class.name,COUNT(stu.id) from stu RIGHT JOIN class ON stu.cid = class.id GROUP BY class.id
# 查询每个班级的学校名称 班级名称和人数
SELECT school.name AS '学校名称', class.name AS '班级名称' ,COUNT(stu.id) AS '总人数' FROM stu RIGHT JOIN class on stu.cid = class.id left JOIN school ON class.sid = school.id GROUP BY class.id
# 查询每个班级的学校名称 班级名称和人数 并且大于2的
SELECT school.name AS '学校名称', class.name AS '班级名称' ,COUNT(stu.id) AS '总人数' FROM stu RIGHT JOIN class on stu.cid = class.id left JOIN school ON class.sid = school.id GROUP BY class.id HAVING COUNT(stu.id) >2