VIPJAVA/02_JavaWeb/day02_mysql/day02.sql

#插入数据
-- 删除stu表
drop table if exists stu;

-- 创建stu表
CREATE TABLE stu (
     id int, -- 编号
     name varchar(20), -- 姓名
     age int, -- 年龄
     sex varchar(5), -- 性别
     address varchar(100), -- 地址
     math double(5,2), -- 数学成绩
     english double(5,2), -- 英语成绩
     hire_date date -- 入学时间
);

-- 添加数据
INSERT INTO stu(id,name,age,sex,address,math,english,hire_date)
VALUES
    (1,'马运',55,'男','杭州',66,78,'1995-09-01'),
    (2,'马花疼',45,'女','深圳',98,87,'1998-09-01'),
    (3,'马斯克',55,'男','香港',56,77,'1999-09-02'),
    (4,'柳白',20,'女','湖南',76,65,'1997-09-05'),
    (5,'柳青',20,'男','湖南',86,NULL,'1998-09-01'),
    (6,'刘德花',57,'男','香港',99,99,'1998-09-01'),
    (7,'张学右',22,'女','香港',99,99,'1998-09-01'),
    (8,'德玛西亚',18,'男','南京',56,65,'1994-09-02');

# 查询name、age两列
select name ,age from stu;

# 查询所有列的数据，列名的列表可以使用*替代
select * from stu;

# 加注释
select
    name , -- 姓名
    age
from stu;


# 别名
# 查询姓名、数学成绩、英语成绩。并通过as给math和english起别名（as关键字可以省略）
select
    math as 数学 , english as 英语
from stu;

# as 省略 表名 别名
select
    math  数学 , english  英语
from stu s;

# s. 表名.字段名
select s.math ,s.english  from stu s;

# 去重复
select distinct address from stu;

select * from stu;



-- 条件查询练习

# 查询年龄大于20岁的学员信息
select * from stu where age > 20;

#查询年龄大于等于20岁的学员信息
select * from stu where age >= 20;

# 查询年龄大于等于20岁 并且 年龄 小于等于 30岁 的学员信息
# and 链接
select * from stu where age >= 20 and age <= 30 ;
# between and
select * from stu where age between 20 and 30; # 小  大

# 查询入学日期在'1998-09-01' 到 '1999-09-01'  之间的学员信息

select * from stu where hire_date >= '1998-09-01' and hire_date <= '1999-09-01' ;
select * from stu where hire_date between '1998-09-01' and '1999-09-01' ;

# 查询年龄等于18岁的学员信息
select * from stu where age = 18;

# 查询年龄不等于18岁的学员信息
select * from stu where age != 18;
select * from stu where age <> 18;

# 查询年龄等于18岁 或者 年龄等于20岁 或者 年龄等于22岁的学员信息

select * from stu where age = 18 or age = 20 or age = 22;
select * from stu where age in ( 18,20,22 );

# 查询英语成绩为 null的学员信息，null值的比较不能使用 =  或者 != 。需要使用 is  或者 is not
select * from stu where english is null  ;
select * from stu where english is not null  ;

# <=>
select * from stu where english <=> null  ;

# 查询姓'马'的学员信息
select * from stu where name like '马%';
# 查询第二个字是'花'的学员信息
select * from stu where name like '_花%';

# 查询名字中包含 '德' 的学员信息
select * from stu where name like '%德%';

#all
select * from stu where age > all();


-- 排序
# 查询学生信息，按照年龄升序排列
select * from stu  order by age;
select * from stu  order by age ASC ;

# 查询学生信息，按照数学成绩降序排列
select * from stu  order by math DESC ;

# 查询学生信息，按照数学成绩降序排列，如果数学成绩一样，再按照英语成绩升序排列
select * from stu  order by math DESC , english ASC ;



-- 聚合函数
# 统计班级一共有多少个学生
select count(id) from stu;

select count(1) from stu;

# 不统计 null
select count(english) from stu;

# 查询数学成绩的最高分
SELECT MAX(math) FROM stu;

# 查询数学成绩的最低分
SELECT MIN(math) from stu;

# 查询数学成绩的总分
SELECT SUM(math) from stu;

# 查询数学成绩的平均分
SELECT AVG(math) from  stu;

select * from stu;

-- 分组查询
# 查询男同学和女同学各自的数学平均分
# 字段  必须是  分组的条件  其他字段 没有意义
select avg(math) , sex from stu group by sex;

# 查询男同学和女同学各自的数学平均分，以及各自人数
select avg(math),count(sex),sex from stu group by sex;

# 查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组
select avg(math),count(sex),sex from stu where math > 70 group by sex;

# 查询男同学和女同学各自的数学平均分，以及各自人数，
# 要求：分数低于70分的不参与分组，分组之后人数大于2个的
select
    avg(math),count(sex),sex
from stu
where
    math > 70
group by
    sex
having
    count(sex) > 2;



-- 分页

# 从0开始查询，查询3条数据
select * from  stu limit 0 , 3;

# 每页显示3条数据，查询第1页数据
# 每页显示3条
select * from  stu limit 0 , 3;

# 每页显示3条数据，查询第2页数据
select * from  stu limit 3 , 3;

# 每页显示3条数据，查询第3页数据
select * from  stu limit 6 , 3;

# 根据 页数计算 起始位置
# 当前页(第一页) (1 - 1) * 条数
# 当前页(第二页) (2 - 1) * 条数
# 当前页(第三页) (3 - 1) * 条数

# (当前页-1) 条数



Select * from stu;



-- 约束
# 非空约束
create table person1 (
    name varchar(20) not null
);
#验证
insert into person1(name) values (null);
insert into person1(name) values ('kunkun');



# 唯一约束
create table person2 (
    id int auto_increment unique ,
    name varchar(20) not null
);

insert into person2(id,name) values (1,'kunkun');
insert into person2(id,name) values (2,'kunkun');
insert into person2(name) values ('kunkun');


create table person3 (
     id int auto_increment ,
     name varchar(20) not null,

     unique (id)
);

insert into person3(name) values ('kunkun');
insert into person3(id,name) values (1,'kunkun');


# 主键约束

create table person4 (
     id int primary key auto_increment ,
     name varchar(20) not null
);


insert into person4(name) values ('kunkun');
insert into person4(id,name) values (1,'kunkun');

# 默认约束

create table person5 (
     id int primary key auto_increment ,
     name varchar(20) not null,
     age int(3) default 0 -- 默认值

);

insert into person5(name) values ('kunkun' );
# 给出null 就是 null 不是默认值
insert into person5(name,age) values ('kunkun',null );


-- 外键约束
# 部门表
create table dept(
    id int auto_increment primary key ,
    name varchar(50) not null unique
);

# 员工表
create table emp(
    id int auto_increment primary key ,
    name varchar(50) not null unique,
    dept_id int ,
    foreign key fk_id (dept_id) references dept(id)
);

# 先添加主表内容
# 部门表内容
insert into dept( name ) values ('开发部门');

insert into emp( name , dept_id ) values ('kunk',1);


# 删除  主表中从表有数据的 不可以
delete from dept where  id = 2;