Day11-27

CREATE TABLE `student3`  (
	`id` int(11)  PRIMARY KEY  AUTO_INCREMENT,
	`name` varchar(255)   ,
	`class` varchar(255)  ,
	`chinese` float  ,
	`english` float  ,
	`math` float  
) ;

INSERT INTO student3 (id, name, class, chinese, english, math) VALUES (1, '武松', '一班', 70, 90, 60);
INSERT INTO student3 VALUES (2, '林冲', '一班', 70, 90, 90);
INSERT INTO student3 VALUES (3, '松江', '一班', 90, 90, 20);
INSERT INTO student3 VALUES (4, '贾琏', '二班', 60, 60, 60);
INSERT INTO student3 VALUES (5, '贾宝玉', '二班', 95, 80, 5); 
INSERT INTO student3 VALUES (6, '贾环', '二班', 25, 25, 5); 
INSERT INTO student3 VALUES (7, '曹操', '三班', 90, 90, 90); 
INSERT INTO student3 VALUES (8, '曹丕', '三班', 90, 80, 80); 
INSERT INTO student3 VALUES (9, '曹植', '三班', 98, 90, 80); 
INSERT INTO student3 VALUES (10, '刘备', '三班', 95, 90, 80); 
INSERT INTO student3 VALUES (11, '诸葛亮', '三班', 98, 95, 95); 
INSERT INTO student3 VALUES (12, '孙权', '三班', 80, 90, 80); 
INSERT INTO student3 (id) VALUES (13);


select * from student3;

-- 特殊关键字

-- where


-- 算数运算符，不仅可以出现在where中，还可以出现在查询列中。

-- 语数外总分 小于180的
select id,name,(chinese + english + math),chinese,english,math from student3 where (chinese + english + math) < 180;

select id,name,(chinese + english + math),chinese,english,math from student3 ;

-- 语文-数学 分差大于30的
select id,name,(chinese - math),chinese,math from student3 where (chinese - math) > 30;

-- 加权平均，按语文0.5 英语0.1 数学0.4求加权平均分
select id,(chinese * 0.5 + english * 0.1 + math * 0.4),name from student3;

-- 加权平均分，小于等于60的
select id,(chinese * 0.5 + english * 0.1 + math * 0.4),name from student3 where (chinese * 0.5 + english * 0.1 + math * 0.4) <= 60;

-- 求每个人的平均分，语数外三科
select id,name,(chinese+english+math)/3 from student3 ;

-- 求每个人的平均分，只筛选出平均分小于60的
select id,name,(chinese+english+math)/3 from student3  where ((chinese+english+math)/3) < 60;

-- 找出id是奇数的
select * from student3 where (id % 2) = 1 ;

-- 找语文成绩是偶数的
select * from student3 where (chinese % 2) = 0 ;


-- between  and   在...中。 
-- 现在想找语文分数在 [60,80] 之间的
select * from student3 where chinese between 60 and 80;


-- like   通配符匹配。 想找姓曹的同学。  % _(单个字符)
select * from student3 where name like "曹%"; 

-- 如果是想找两个字的。姓曹的同学
select * from student3 where name like "曹_"; 

-- is null.如果要判断是否为null，需要使用它。
select * from student3 where chinese is null;


-- 想找语文考了 60,80,90 的同学。 
select * from student3 where chinese in (60,80,90);



-- 查询语数外总成绩大于 180 的同学信息；
select * from student3 where (chinese + math + english) > 180;

-- 查询数学成绩在[80，90]区间的同学姓名；
select name from student3 where math between 80 and 90;
-- 前面写小的，后面写大的。
-- select name from student3 where math between 90 and 80;
select name from student3 where math >= 80 and math <= 90;

-- 查询各科都及格的同学姓名；
select name from student3 where math>=60 and chinese >= 60 and english >= 60;

-- 查询有一科成绩小于60的同学信息
select * from student3 where chinese < 60 or english < 60 or math < 60;

-- 查询一班和二班的同学信息；(两种写法)
select * from student3 where class in ("一班", "二班");
select * from student3 where class = "一班" or class =  "二班";


-- 查询姓贾的同学（只要姓贾就行）
select * from student3 where name like "贾%";

-- 查询姓贾的同学，两个字的
select * from student3 where name like "贾_";

-- 查询语文分数在 60 或90的同学
select * from student3 where chinese = 60 or chinese = 90;

-- distinct 对某个列或者某些列去重

select distinct class from student3;


select distinct english from student3;
select distinct english,math from student3;


select * from student3;


-- limit 限制最大的返回数目
select * from student3 limit 20;

-- 从0号位置开始返回，返回5条。
-- select * from student3 limit offsetNumber, limitNumber;
select * from student3 limit 2,5;

-- select * from student3 limit limitNumber offset offsetNumber;
select * from student3 limit 5 offset 5;


-- 分页，主要是借助一个公式。 
-- 比如，现在每页6条数据，第一页 limit  offset 
select * from student3 limit 6 offset 0;
-- 分页的公式  limit ${eachPageNumber} offset (${page - 1} * eachPageNumber)
select * from student3 limit 6 offset (pageNumber - 1) * 6;



-- as 是用来给表或字段指定别名的
select id,name,(english + math + chinese) as total_score from student3;

select id,s.name,(english + math + chinese)  from student3 as s;


-- order by 根据字段排序。  
-- 如果order by后不加是升序还是降序，默认是升序 
-- asc  升序；   desc 降序
select * from student3 order by chinese ;
select * from student3 order by chinese desc;

-- 可以对多列进行排序。
-- 首先，按照语文成绩降序排；如果语文成绩相同，就按英语成绩升序排
select * from student3 order by chinese desc,english;

-- 
select * from student3 order by chinese desc,english,math desc;


-- group by  根据指定的列进行分组
select class from student3 group by class;

-- group by的select中，只能出现两个东西： 1.在group by 中出现的字段； 2.聚合函数
-- max()  min()  sum()
-- 聚合函数，代表在这个组里面的聚合结果。比如max(chinese)，代表在这个组中的语文最高分
select class,max(chinese) from student3 group by class;



-- 语文成绩相同，会进入同一个组
select chinese from student3 group by chinese;

select chinese, max(english) from student3 group by chinese;

-- 如果语文和英语都相同，会分到一个组里； 
select chinese,english from student3 group by chinese,english;


-- GROUP_CONCAT()函数会把每个分组的字段值都拼接显示出来.
select chinese,english,group_concat(name) from student3 group by chinese,english;
select class,group_concat(name) from student3 group by class;

-- HAVING 可以让我们对分组后的各组数据过滤。(一般和分组+聚合函数配合使用)
-- 现在，想找各班最高的语文分数
select class,max(chinese) from student3 group by class; 

-- 想找，班级最高语文分，大于等于95分的班级。 
-- 首先会进行分组，分组之后的结果，会进入 having进行筛选。
select class,max(chinese) from student3 group by class having max(chinese) >= 95; 


-- 如果在select中，可以看出来group by的字段，group by中的东西，可以用数字代替，从1开始
select chinese,english,count(*) from student3 group by 1,2;
select chinese,english,count(*) from student3 group by chinese,english;



-- 聚合函数

-- count()计数

select count(*) from student3;
-- 这样行不。 count(*) 其实本质也是做了分组，只是让全表都做了一个组。
select count(*),id from student3;

-- count(*) 和 count(columnName)的区别
select * from student3;
-- count(*) 就是统计有多少行，不关心里面的内容
select count(*) from student3;
-- count(columnName) 统计 columnName不为null的行数
select count(id) from student3;

select count(class) from student3;
select * from student3;


-- 查询每个同学的总成绩，平均成绩，并用别名表示；
select name, (chinese + english + math) as totao_score, ((chinese + english + math) / 3) as avg_score from student3;
-- 现在，想只显示两位。   我让你实现，怎么做？
-- 学会搜索。  student_name 转成驼峰。在Java中怎么做
SELECT 
    name,
    (chinese + english + math) AS total_score,
    ROUND((chinese + english + math) / 3, 2) AS avg_score
FROM 
    student3;

-- 查询数学最大值，并用别名表示；
select max(math) as max_score from student3;

-- 查询外语最小值，并用别名表示；
-- 查询全体学生的语数外各科平均成绩，并用别名表示；
select avg(chinese) as chinese_score, avg(english) as english_score, avg(math) as math_score  from student3;


-- 获取语文成绩大于90分的，按照班级分组
select class,group_concat(name) from student3 where chinese > 90 group by class;

-- 获取班级的平均数学成绩
select class, avg(math) from student3 group by class;

-- 获取班级人数大于三个人的班级
select class,group_concat(name) from student3 group by class having count(*) > 3;

select class,group_concat(name) from student3 group by class;

-- 获取班级平均语文成绩大于60分的
select class,group_concat(chinese), avg(chinese) from student3 group by class having avg(chinese) > 60;

-- 查询班级语文总分大于200的班级(可以显示一下语文总分)
select class,sum(chinese) from student3 group by class having sum(chinese) > 200;

-- 查询班级平均分，学生的限制：数学大于等于60，语文大于等于60的 
select class,avg(chinese + english + math) from student3 where math >= 60 and chinese >= 60 group by class ;


-- 查询班级情况，要求学生语文最大的大于等于90，语文最少分大于等于70
select class, group_concat(chinese) from student3 group by class having max(chinese) >= 90 and min(chinese) >= 70;


-- 查询班级，语文最小成绩大于等于60，数学也是

select class,group_concat(name) 
from student3
where id % 2 =1
group by class 
having count(*) >= 1
order by class
limit 3;



-- 实体完整性

-- 主键约束
create table test_primary_key(
	-- 这个就代表声明了id是主键
	id int primary key,
	name varchar(255) 
);

-- 主键的特点
-- 1.一个表，只能有一个主键
-- 2.主键是唯一的
-- 3.主键不能为null

insert into test_primary_key values (1,"张三");
insert into test_primary_key values (2,"李四");
insert into test_primary_key values (3,"王五");
-- 在一张表里，主键不能重复。3之前有，现在再插入会报错
insert into test_primary_key values (3,"赵六");
select * from test_primary_key;
insert into test_primary_key values (null,"赵六");



-- 自动增长。有一些序号，不想手动设置，  1 2 3 4 。想让mysql自动生成，
-- 可以使用 auto_increment.

create table test_auto_increment(
	id int primary key auto_increment,
	name varchar(255)
);

insert into test_auto_increment(name) values ("张三");
insert into test_auto_increment(name) values ("李四");

select * from test_auto_increment;

-- 可以通过一条命令看到自动增长的数字。   
show create table test_auto_increment;



create table test_auto_increment2(
	id int primary key auto_increment,
    name varchar(255) unique
);

insert into test_auto_increment2(name) values ("zhangsan");
insert into test_auto_increment2(name) values ("lisi");

select * from test_auto_increment2;

insert into test_auto_increment2(name) values ("lisi");
insert into test_auto_increment2(name) values ("wangwu");


-- null约束
create table test_null(
	id int primary key auto_increment,
	-- 代表name这一列，不能插入null
	name varchar(255) not null
)

insert into test_null(name) values ("zhangsan");

insert into test_null(name) values (null);

create table test_null3(
	id int primary key auto_increment,
	-- 如果是默认的，代表允许为null
	name varchar(255) null
);
-- 可以使用这个语句，查看约束
show create table test_null3;


select class,max(chinese) from student3 group by class;

Day11-28

-- null约束
-- 约束一个字段，不能为null

create table test_null4(
	id int primary key auto_increment,
	name1 varchar(255),
	-- name2这个字段不允许为null
	name2 varchar(255) not null
);

-- 指定列名； 不指定列名
insert into table_name(column1, column2, ....) values (value1, value2, ...) ;
-- value1 --> valuen  按照建表顺序写value。desc ;  show create table 
insert into table_name values (value1, value2, ...) ;
insert into test_null4 values (null, null, "null");
select * from test_null4;




-- unique 唯一值约束
create table test_unique(
	id int primary key auto_increment,
	name varchar(255) unique,
	address varchar(255)
);

-- unique特点： unique不能重复； unique可以插入null；  null可以插入多个

insert into test_unique(name) values ("zs");
insert into test_unique(name) values ("ls");
insert into test_unique(name) values ("wu");
select * from test_unique;
-- Duplicate entry 'wu' for key 'name'
-- insert into test_unique(name) values ("wu");

insert into test_unique(name, address) values (null, "湖北");
insert into test_unique(name, address) values (null, "湖南");

insert into test_unique(name, address) values ("null", "湖南");
insert into test_unique(name, address) values ("null", "湖北");




-- 外键约束

create table province(
	id int PRIMARY KEY,
	name varchar(20)
);

create table city1(
	id int ,
	name varchar(20),
	province_id int,
	-- 声明外键
    -- CONSTRAINT 外键名称 foreign key(列) references 表名(列名)
    -- 在 province_id上建立一个外键，指向   province表的id字段
	CONSTRAINT fk_pid foreign key(province_id) REFERENCES province(id)
);

insert into province values(41, "河南省");
insert into province values(43, "湖南省");
insert into province values(42, "湖北省");

insert into city1(id, name, province_id) values(1, "武汉", 42);
insert into city1(id, name, province_id) values(13, "随州", 42);
insert into city1(id, name, province_id) values(1, "长沙", 43);
insert into city1(id, name, province_id) values(6, "岳阳", 43);
-- 插入子行，会去父表查询。 
-- 删除父行，是否会去查？  会去查子行，是否有与之对应的。
insert into city1(id, name, province_id) values(1, "哈尔滨", 23);
delete from province where id = 42;
delete from province where id = 41;



-- 默认值。如果插入的时候，没有指定，就用默认值； 如果插入的时候，指定了，就用指定的值
create table test_default1(
	id int primary key auto_increment,
	name varchar(255) not null,
	country varchar(50) default "中国"
);


insert into test_default1(name) values ("张三");
insert into test_default1(name) values ("李四");
insert into test_default1(name, country) values ("Mr. zhao", "USA");

select * from test_default1;



CREATE TABLE `user_info`  (
  id int, 
  name varchar(200),
  create_time timestamp DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
);

insert into user_info(id, name) values (1, "黄杰");
insert into user_info(id, name) values (2, "殿熙");

select * from user_info;

update user_info set name = "张殿熙" where id=2;



create table test_comment(
  id int primary key auto_increment,
  name varchar(255) comment "名字",
  status int comment "0表示未付款，1表示已付款，2"
);

show create table test_comment;

初始化数据

drop table if exists student;
drop table if exists class;

create table class(
	id int primary key auto_increment,
    name varchar(200)
);

create table student(
	id int primary key auto_increment,
    name varchar(200),
    age int,
    class_id int
);


insert into class values(1,"一班");
insert into class values(2,"二班");
insert into class values(3,"三班");

insert into student values(1, "张飞", 30, 1);
insert into student values(2, "关羽", 40, 1);
insert into student values(3, "李云龙", 35, 2);
insert into student values(4, "楚云飞", 33, 2);
insert into student values(5, "王有胜", 30, 2);
insert into student values(6, "林冲", 30, 3);
insert into student values(7, "孙二娘", 35, 4);


select * from class;
select * from student;

create table tec_stu(
	id int primary key auto_increment,
    name varchar(200)
);

create table tec_cource(
	id int primary key auto_increment,
    name varchar(200)
);

-- 选课表
create table tec_sele_cource(
	id int primary key auto_increment,
    student_id int,
    cource_id int
);

insert into tec_stu values (1, "李云龙");
insert into tec_stu values (2, "楚云飞");
insert into tec_stu values (3, "赵刚");
insert into tec_stu values (4, "王有胜");
insert into tec_stu values (5, "孙悟空");

insert into tec_cource values (1, "Java");
insert into tec_cource values (2, "C++");
insert into tec_cource values (3, "Python");

insert into tec_sele_cource(student_id, cource_id) values (1,1);
insert into tec_sele_cource(student_id, cource_id) values (1,2);
insert into tec_sele_cource(student_id, cource_id) values (2,2);
insert into tec_sele_cource(student_id, cource_id) values (2,3);
insert into tec_sele_cource(student_id, cource_id) values (3,1);
insert into tec_sele_cource(student_id, cource_id) values (4,2);
insert into tec_sele_cource(student_id, cource_id) values (5,3);


select * from tec_stu;

select * from tec_cource;

select * from tec_sele_cource;

drop table if exists user;
create table user(
	id int primary key auto_increment,
	name varchar(255),
	password varchar(255)
);

drop table if exists user_detail;
create table user_detail(
	id int primary key auto_increment,
	user_id int,
	address varchar(255),
	pic varchar(255)
);

insert into user values (1, "猪八戒", "zhubajie");
insert into user values (2, "孙悟空", "sunwukong");
insert into user values (3, "白骨精", "baigujing");
insert into user values (4, "唐僧", "tangseng");
insert into user values (5, "沙僧", "shaseng");

select * from user;

insert into user_detail values(null, 1, "高老庄", "猪八戒.jpg");
insert into user_detail values(null, 2, "花果山", "孙悟空.jpg");
insert into user_detail values(null, 3, "白虎岭", "白骨精.jpg");
insert into user_detail values(null, 4, "东土大唐", "唐僧.jpg");

select * from user_detail;

select * from user cross join user_detail;


-- 内连接语法
-- select * from table1 inner join table2 on table1.column = table2.column;
-- 最终只会保留 table1中 column和table2中的column相同的数据

-- 如果不写on。其实就是一个交叉连接
select * from user inner join user_detail;


select * from user inner join user_detail on user.id = user_detail.user_id;


select * from user,user_detail 
where user.id =user_detail.user_id;


-- 左外连接
select * from user left outer join user_detail on user.id = user_detail.user_id;


-- 想看一下，同学都来自哪个班级
select s.name,c.name from class c inner join student s on c.id=s.class_id;

-- 想知道。一班有哪些同学
select s.name,c.name from class c inner join student s on c.id=s.class_id
where c.name="一班";

-- 想知道，林冲同学来自哪个班？
select s.name,c.name from class c inner join student s on c.id=s.class_id
where s.name = "林冲";


-- 内连接和外连接有什么区别
-- 内连接，只会保留匹配上的数据； 左外： 除了匹配上的数据，还会保留左表的所有数据
-- 6条
select * from class c inner join student s on c.id=s.class_id;
-- 6条
select * from class c left join student s on c.id=s.class_id;
select * from class c right join student s on c.id=s.class_id;




-- 1.想知道有哪些同学选了Java
select s.id,s.name,c.name from tec_stu s 
inner join tec_sele_cource sc 
on s.id = sc.student_id
inner join tec_cource c 
on sc.cource_id = c.id
where c.name = "Java";

-- 2.想知道李云龙同学选了哪些课程
select s.id,s.name,c.name from tec_stu s 
inner join tec_sele_cource sc 
on s.id = sc.student_id
inner join tec_cource c 
on sc.cource_id = c.id
where s.name = "李云龙";



-- 比如，现在让你设计一个系统。有学生考试，考试完了需要存储分数，课程。表怎么设计？
-- 有哪几张表，表关系是怎样的？
-- 1）学生表   id  name  gender 
-- 2）课程表   id  name 
-- 3）成绩表  id   student_id cource_id  score 
drop table if exists student;
create table student(
	id int primary key auto_increment,
	name varchar(255),
	gender varchar(30)
);

drop table if exists cource;
create table cource(
	id int primary key auto_increment,
	name varchar(255)
);
drop table if exists student_score;
create table student_score(
	id int primary key auto_increment,
	student_id int,
	cource_id int,
	score int
);

INSERT INTO student (id, name, gender) VALUES
(1, '黄杰', '男'),
(2, '邓鹏', '男'),
(3, '艾旭', '男'),
(4, '习文', '男');

INSERT INTO student_score (id, student_id, cource_id, score) VALUES
(1, 1, 1, 99),
(2, 1, 2, 90),
(3, 2, 1, 90),
(4, 2, 2, 92),
(5, 2, 3, 91),
(6, 3, 1, 60),
(7, 3, 2, 61),
(8, 3, 3, 59),
(9, 4, 1, 80),
(10, 4, 3, 85);

INSERT INTO cource (id, name) VALUES
(1, '高等数学'),
(2, '线性代数'),
(3, '大学英语');


select * from student;
select * from cource;
select * from student_score;


-- 1.想统计一下，高等数学有多少人有分数
select count(*) from student s inner join student_score ss
on s.id = ss.student_id
inner join cource c 
on ss.cource_id = c.id
where c.name = "高等数学";

-- 2.想看一下线性代数，同学都考了多少分
select * from student s inner join student_score ss
on s.id = ss.student_id
inner join cource c 
on ss.cource_id = c.id
where c.name = "线性代数";


-- 3.想看一下，是否有同学缺考线性代数。
select * from student s left join student_score ss
on s.id = ss.student_id and ss.cource_id=2;

-- 内连接和外连接。  
-- 内连接，就是只保留匹配上的数据
-- 外连接。 左外 = 内连接 + 左表的所有数据




-- 子查询：就是将一个SQL的查询结果，作为另一个SQL的输入。

-- 查询Java课程的id
select id from tec_cource where name = "Java";

select student_id from tec_sele_cource where cource_id = 1;

select * from tec_stu where id in (1,3);



-- 子查询： 就是将一条SQL的结果作为另一条SQL的输入。
select * from tec_stu where id in (select student_id from tec_sele_cource where cource_id = (select id from tec_cource where name = "C++"));
-- 子查询效率很差。 一层子查询，会生成一个临时表，所以效率很差，后续 能不用尽量不要用。


-- 比如，现在想找 一班的同学  二班的同学
select * from student3 where class in ("一班", "二班");


-- 联合查询。就是执行两条SQL，然后将SQL的结果拼接起来
select id,name,age,address,remark from student2 
union 
select id,name,class,chinese,english from student3 where class = "二班";

delete from user;


-- 如果是根据主键来分组。
select * from student group by id;