mysql笔记

字段的约束：
主键(primary key)：值不能重复，auto_incremen代表值自动增长
唯一(unique)：此字段的值不允许重复
非空(not null)
默认值(default)

字段名 数据类型 unique
字段名 数据类型 default 值

整数：int，如int unsigned表示一个无符号的整数
小数：decimal，如decimal(5,2)表示共存5位数，小数占2位，不能超过2位，整数占3位，不能超过三位
字符串：varchar，如varchar(4)表示最多存3个字符，一个中文/字母都占一个字符
日期时间：datetime

create table 表名(字段名 数据类型,…);
insert into 表名 values(…),(…)…;
insert into 表名(字段1,…) values (值1,…),(值2,…)…;
update 表名 set 字段1=值1,字段2=值2… where 条件；
select * from 表名;
select 字段1,字段2,… from 表名
delete from 表名 where 条件;
truncate table 表名;//删除表的所有数据保留表结构
drop table 表名;
drop table if exists 表名;

use mydb;
#1.1	建立一个张表，表名为stu, 一共四个字段(id编号, name姓名, age年龄, height身高), 字段要求如下:
#id 类型: 整数，主键，自增长;
#name 类型: 字符串 长度10 , 值唯一, 不能重复,
#age 类型: 无符号小整数, 默认值20,
#height 类型: 小数, 3位整数,2位小数, 非空;

create table stu(
id int PRIMARY key AUTO_INCREMENT,
name varchar(10) unique,
age tinyint DEFAULT 20,
height decimal(5,2) not null
);

INSERT into stu (name,age,height) values('张三',20,1.72);
insert into stu (name,age,height) values('李四',30,1.78);

insert into stu(name,age,height) values('王五',25,1.81),('赵六',27,1.76);

select * from stu;
#1.5	stu表中，'王五'年龄修改为21岁
update stu set age=21 where name='王五';
#1.6	stu表中，'李四'年龄增加10岁
update stu set age=age+10 where name='李四';
#1.7	删除stu表中年龄等于20岁的记录
delete from stu where age=20;
#1.8	删除stu表中姓名等于'赵六'的记录
delete from stu where name='赵六';
#1.9	删除stu表所有记录
delete from stu;
#1.10	删除表stu
drop table if exists stu;

模糊查询

like
%表示多个任意字符
_表示一个任意字符

空判断

is null —是否为null
is not null —是否不为null
不能用 字段名 = null 字段名 != null这些都是错误的

排序

select *from 表名 where 条件 order by 字段1 asc/desc,字段2 asc/desc；
一定要把where写在order by前面
默认按照字段值从小到大排序
升序：asc（默认值）从小到大排序，可以省略
降序：desc从大到小排序

#查询name姓名为两个字的学生记录
select * from students where name like '__';
#查询name姓'白'且年龄大于30的学生记录
select * from students where name like '白%' and age>30;
#查询studentNo学号以1结尾的学生记录
SELECT * from students where studentNo like '%1';

-- 例 1：查询所有学生记录，按 age 年龄从小到大排序
select * from students order by age asc;
select * from students order by age;
-- 例 2：查询所有学生记录，按 age 年龄从大到小排序
select * from students order by age desc;
-- 例 2：查询所有学生记录，按 age 年龄从大到小排序，
--  年龄相同时，再按 studentNo 学号从小到大排序
SELECT * from students ORDER BY age desc, studentNo;
#查询所有男学生记录，按class班级从小到大排序，班级相同时，再按studentNo学号再按学号从大到小排序
select *from students where sex='男' ORDER BY class asc,studentNo desc;
-- 练习 1：查询所有学生的最大年龄、最小年龄、平均年龄；
select max(age),min(age),avg(age) from students;
-- 练习 2：查询’1 班’共有多少个学生； 
select count(*) from students where class='1班';
-- 练习 3：查询’3 班’中年龄小于 30 岁的同学有几个；
select count(*) from students where class='3班' and age<30;

分组查询

select 分组函数，分组后的字段 from 表 where 条件
 group by 分组的字段 
  having 分组后的筛选 
   order by 排序列表

对比where与having：

where是对原始数据进行筛选
having是对group by之后已经分过组的数据进行筛选
having可以使用聚合函数, where不能用聚合函数

where和group by和order by的顺序：

select * from 表名 where 条件 group by 字段 order by 字段

#查询各种性别的人数
select sex,count(*) from students group by sex;
#练习：用数据分组方法，统计各个班级学生总数、平均年龄、最大年龄、最小年龄。
select class,count(*),avg(age),max(age),min(age) from students group by class;

#用having子句查询除了1班以外，其他各个班级学生的平均年龄、最大年龄和最小年龄
select class,avg(age),max(age),min(age) from students group by class having class !='1班';
#查询班级总人数大于2人的班级名称以及班级对应的总人数
select class,count(*) from students group by class having count(*)>2;

limit显示指定的记录数

select * from 表名 where 条件 group by 字段 order by 字段 limit start, count
limit总是出现在select语句的最后,
start代表开始行号,行号从0开始编号
count代表要显示多少行
省略start,默认从0开始,从第一行开始

-- 查询前三行记录
SELECT * from students limit 0,3;
SELECT * from students limit 3;
-- 查询从第4行开始的3条学生记录 start是从0开始
SELECT * from students limit 3,3;

-- 使用limit语句，查询年龄最大的学生记录
select *from students order by age desc limit 1;
-- 查询年龄最大同学的name
select name from students order by age desc limit 1;
-- 查询年龄最小的女同学信息
SELECT * from students where sex = '女' ORDER BY age limit 1;

select from students limit (n-1)m,m
m：每页显示多少条记录
n：第n页
(n-1)*m

-- 每页显示4条记录,第3页的结果 (3-1)*4,4
select * from students limit 8, 4;
-- 每页显示4条记录,第2页的结果 (2-1)*4,4
select * from students limit 4, 4;

-- 每页显示5条记录,分别多条select显示每页的记录
-- 第一页
SELECT * from students limit 5;
-- 第二页:
SELECT * from students limit 5, 5;
-- 第三页:
SELECT * from students limit 10, 5;

insert into teacher values('861','刘羽','女','1978-09-14','助教','电子工程系');
-- 1.2	查询Student表中的所有记录的Sname、Ssex和Class列。
select sname,ssex,class from student;
--1.3	查询teacher表中的部门depart,要求不能显示重复值。
select distinct depart from teacher;

select * from score where degree between 60 and 80;

update student set sname='王美丽' where sname='王丽';

select cno from score where degree>85;
--1.7	查询Score表中成绩degree为85，86或88的记录。
select *from score where degree in (85,86,88);

select * from student where class='95031' or ssex='女';

delete * from teacher where tno='861';

select * from student order by class desc;

select * from score order by cno,degree desc;

select count(*) from student where class='95031';

-- 查询student表中95033班和95031班全体学生的记录
select * from student where class in('95033','95031');
--1.14	查询Student表中不姓“王”的同学记录。
select *from student where sname not like '王%';
--1.15	以年龄从小到大的顺序查询Student表中的全部记录
--注意sbirthday是'1978-09-14'，所以用降序
select * from student order by sbirthday desc;

--1.16	查询score表中每门课的平均成绩
select cno,avg(degree) from score group by cno;

select sno from score where degree>70 and degree<90;

drop table if exists a;

内连接：

语法1：
select * from 表1
inner join 表2 on 表1.字段=表2.字段

语法2：隐式内连接
select* from 表1,表2
where 表1.字段=表2.字段

带有where的内连接：
select * from 表1
inner join 表2 on 表1.字段=表2.字段 where 条件

select name,courseNo,score from students stu,scores sc
where stu.studentNo=sc.studentNo;
--练习1：查询成绩表信息，同时显示成绩对应的课程名称
SELECT * from scores,courses where scores.courseNo=courses.courseNo;

select name,courseNo,score from students s1 inner join scores s2 on s1.studentNo=s2.studentNo
where s1.name='王昭君';

--查询姓名为’王昭君’,并且成绩小于 90 的信息，要求只显示姓名、成绩
select name, score from students s1 
INNER JOIN scores s2 on s1.studentNo = s2.studentNo
where s1.name = '王昭君' and s2.score < 90;

查询所有学生的'linux'课程成绩，要求只显示姓名、成绩、课程名 
select name,score,courseName from students 
inner join scores on students.studentNo=scores.studentNo
inner join courses on scores.courseNo=courses.courseNo
where courseName='linux';

--查询成绩最高的男生信息，要求显示姓名、课程名、成绩
select name,courseName,score from students
inner join scores on students.studentNo=scores.studentNo
inner join courses on scores.courseNo=courses.courseNo
where students.sex='男'
order by score desc limit 1;

左连接：

select * from 表1
left join 表2 on 表1.字段=表2.字段

--查询所有学生的信息以及成绩，包括没有成绩的学生
SELECT * from students left JOIN scores ON
students.studentNo = scores.studentNo;

右连接：

select * from 表1
right join 表2 on 表1.字段=表2.字段

--查询所有课程的信息，包括没有成绩的课程
SELECT * from scores RIGHT JOIN courses ON
scores.courseNo = courses.courseNo;

子查询：

子查询是嵌套到主查询里面的，做为主查询的数据源或者条件
子查询是独立可以单独运行的查询语句，不能独立独立运行

-- 例 1：查询大于平均年龄的学生记录
-- SELECT avg(age) from students;
-- 
-- select * from students where age > 30.1667;

-- 用子查询实现
select * from students where age > (SELECT avg(age) from students);

-- 例 2：查询 30 岁的学生的成绩
-- 1,查询30岁学生的studentNO
-- select studentNo from students where age = 30;
-- 
-- SELECT * from scores where studentNo in ('001', '003', '011');

-- 用子查询实现
SELECT * from scores where studentNo in 
(select studentNo from students where age = 30);

-- 例 3：用子查询，查询所有女生的信息和成绩
-- 用内连接实现
SELECT * from students INNER JOIN scores ON
students.studentNo = scores.studentNo
where sex = '女';
-- 用子查询实现
select * from (SELECT * from students where sex = '女') stu
INNER JOIN scores sc on stu.studentNo = sc.studentNo;

--1.1	查询成绩表score中学号sno为“109”、课程号cno为“3-105”的所有记录
select degree from score where sno='109' and cno='3=105';
--1.2	查询部门depart为“计算机系”与“电子工程系“教师的Tname和Prof
select tname,prof from teacher where depart in('计算机系','电子工程系');

--查询男教师的姓名tname及其所上的课程名称cname
select tname,cname from teacher,course where teacher.tno=course.tno and teacher.tsex='男';

select tname,cname from course inner join teacher on course.tno=teacher.tno
where teacher.tsex='男';

--1.4	查询最高分同学的Sno、Cno和Degree列。
select sno,cno,degree from score where degree in (select max(degree) from score); 
--1.5	查询和“李军”同性别的所有同学的Sname
select sname from student where ssex=(select ssex from student where sname='李军');

--！ 1.6	查询至少有2名男生的class
select class from student where ssex='男' group by class having count(*)>=2;

--1.7	查询所有学生的姓名Sname、课程编号Cno和成绩Degree
select sname,cno,degree from student 
inner join score on student.sno=score.sno;

select sname,cno,degree from student,score
where student.sno=score.sno;

--1.8	查询所有学生的学生编号Sno、学生姓名sname, 课程名称Cname和成绩Degree
--Column 'sno' in field list is ambiguous，要指定数据库，避免指代不清
select student.sno,sname,cname,degree from student
inner join score on student.sno=score.sno
inner join course on score.cno=course.cno;

--1.9	查询“95033”班学生的平均分
--用的是in，不是=
select avg(degree) from score where sno in(select sno from student where class='95033');

--1.10	查询课程编号cno 为'3-105'中成绩degree最高学生的姓名sname和性别ssex以及成绩degree
--排序order by，分组group by
select sname,ssex,degree from student 
inner join score on student.sno=score.sno
where score.cno='3-105'
order by score.degree desc limit 1;