1.聚合函数
count(id) 不为null的id数
2.分组 + 聚合
group by
eg.select country,countid) from sanguo group by country;
3.分组筛选
having
eg.select country,count(id) as c_n from sanguo group by country having c_n > 2;
4.distinct()去重
5.索引
主键索引
唯一索引
普通索引
查看索引 show index from 表名
删除索引 drop index 索引名 on 表名
=========================================================================
需求:
1.老师信息:老师姓名,老师年龄,性别,爱好
2.课程信息:课程名称,课时
3.学院信息:学员信息,学员年龄,性别,毕业院校,班级,专业
4.考试信息:某位学员的某一门课考了多少分
1.表关系
1.外键 - Foreign Key
作用:约束当前表的某列值必须取自于另一张表的主键列值
外键所在的列称为“外键列”
外键所在的表称为“外键表”或“子表”
被外键列所引用的表称为“主表”或“主键表”
2.语法
1.在创建表的同时指定外键
create table Course(
id int primary Key auto_increment,
cname varchar(20)
)
create table Teacher(
id int primary key auto_increment,
tname varchar(20),
course int ,
constraint fk_course_teacher foreign key(course) references Course(id) ------->增加外键的实现
)
语法:
create table xxx(
字段 类型,
...,
constraint 外键名 foreign key(字段) references 主键表(主键列)
)
2.对已有表增加外键
alter table 表名 add constraint 外键名 foreign key(字段) references 主键表(主键)
3.删除外键
alter table 表名 drop foreign key 外键名
4.查看外键:show create table tablename
-- 单行注释 -- 创建一张course表:id,cname,cduration create table course( id int PRIMARY KEY auto_increment, cname VARCHAR(30) not null, cduration int not null ); -- 插入测试书据 insert into course (cname,cduration) values ('python基础',20), ('python高级',15), ('weg基础',9), ('python web',15), ('爬虫',10), ('书据分析&人工智能',20); -- 创建teacher表:id,age,genger,hobby,coures -- course_id 是外键,引用course表的主键id CREATE TABLE teacher( id int PRIMARY KEY auto_increment, name VARCHAR (30) not null, age int not null, genger VARCHAR (2) not null, hobby VARCHAR (50) not null, course_id int, -- 外键约束 constraint fk_course_teacher foreign key(course_id) references course(id) ); -- 向teacher表中插入主键 INSERT INTO teacher VALUES (null,'齐天大圣',28,'M','看书',1), (null,'吕泽',30,'M','自拍',2), (null,'赵萌萌',18,'F','帅哥',3); -- 创建major表:id,m_name CREATE TABLE major( id int PRIMARY KEY auto_increment, m_name VARCHAR (30) not null ); -- 向major中插入书据 INSERT INTO major(m_name) VALUES('AID'),('UID'),('JSD'),('web'); -- 创建student表:id,name,age,gender,school,class_id,major_id CREATE TABLE student( id INT PRIMARY KEY auto_increment, name VARCHAR (30) NOT NULL, age INT NOT NULL, gender CHAR (2) NOT NULL, school VARCHAR (100) NOT NULL, class_id INT NOTc NULL, major_id INT NOT NULL ); -- 更新Student表,增加外键关系在major_id上,引用自major表的主键id ALTER TABLE student add CONSTRAINT fk_major_student FOREIGN key(major_id) REFERENCES major(id); -- 插入student表内容 INSERT INTO student VALUES(null,'张三',18,'M','哈佛大学',5,1), (null,'李斯',19,'M','麻省理工',4,1), (null,'王五',26,'F','蓝翔',4,3), (null,'左左',19,'F','五道口技校',4,1); -- 创建Classinfo表:id,classname,status CREATE TABLE classinfo( id INT PRIMARY KEY auto_increment, classname VARCHAR (10) NOT NULL, status INT NOT NULL ); -- 在classinfo中插入书据 INSERT INTO classinfo VALUES (null,1901,0),(null,1902,1),(null,1903,1),(null,1904,1),(null,1905,1);*/ -- 修改student表结构,增加外键class_id,引用自classinfo表的主键id alter TABLE student add CONSTRAINT fk_classinfo_student FOREIGN KEY (class_id) REFERENCES classinfo(id); -- 创建score表:id,stu_id,course_id,score CREATE TABLE score( id INT PRIMARY KEY auto_increment, stu_id INT NOT NULL , course_id INT NOT NULL , score INT NOT NULL ); ALTER TABLE score add CONSTRAINT fk_student_score FOREIGN KEY(stu_id) REFERENCES student(id); ALTER TABLE score add CONSTRAINT fk_course_score FOREIGN KEY(course_id) REFERENCES course(id); INSERT INTO score(stu_id,course_id,score) VALUES (1,1,98),(1,2,85),(1,3,96), (2,1,69),(2,2,80),(2,4,65), (3,2,76),(3,3,88),(3,4,75); -- 删除score表中的fk_student_score外键 ALTER TABLE score drop FOREIGN KEY fk_student_score;
3.级联操作
1.语法:
alter table 表名
add constraint 外键名
foreign key(字段)
references 主键表(主键)
on delete 级联删除
on update 级联操作
-- 增加score表中的stu_id增加外键并设置级联操作 ALTER TABLE score add CONSTRAINT fk_student_score FOREIGN KEY (stu_id) REFERENCES student(id) on DELETE CASCADE on UPDATE CASCADE
2.级联操作取值
1.cascade
数据级联删除、更新
2.restrict (默认值)
子表中有关联数据那么主表中就不允许做删除、更新
3.set null
主表删除数据时,子表中的相关数据会设置为null
4.表连接查询
1.交叉连接 -笛卡尔积
eg.查询teacher和course表中的数据
select * from teacher,course;
2.内连接
在关联的两张表中把满足某些条件的数据筛选出来
语法:
select 字段,... ...
form 表1
inner join表2 ---->inner内部 join连接
on 条件
inner join 表3
on 条件
-- 使用内连接查询teacher和course表中的书据(姓名,年龄,课程,名称,课时) SELECT t.name,t.age,c.cname,c.cduration FROM teacher AS t INNER JOIN course AS c ON t.course_id = c.id
练习:
1.查询学员的姓名,年龄,所在班级名称及专业名称
2.查询学员的姓名,毕业院校,所在班级,考试科目,考试成绩
-- 1.查询学员的姓名,年龄,所在班级名称及专业名称 SELECT s_t.name,s_t.age,class.classname,m.m_name from student as s_t INNER JOIN classinfo as class ON s_t.class_id = class.id INNER JOIN major as m ON s_t.major_id = m.id where class.classname = '1902'; -- 2.查询学员的姓名,毕业院校,所在班级,考试科目,考试成绩 SELECT s_t.name,s_t.school,c.classname,cou.cname,sc.score FROM student AS s_t INNER JOIN classinfo as c on s_t.class_id = c.id INNER JOIN score as sc ON s_t.id = sc.stu_id INNER JOIN course as cou ON sc.course_id = cou.id;