SQL练习2

-- 第二天

-- 按数学升序
SELECT * FROM student ORDER BY math asc;
-- 按数学降序（默认）
SELECT * FROM student ORDER BY math desc;

-- 第一条件值一样的时候，可以添加第二条件
SELECT * from student ORDER BY math desc ,english desc;

-- 聚合函数
-- 查询列数
select COUNT(IFNULL(english,0)) as 总数 FROM student;

-- 查询最大值和最小值
SELECT MAX(english) as 最大值 from student;
SELECT MIN(IFNULL(english,0)) as 最小值 from student;

-- 查询English的和
select SUM(IFNULL(english,0)) as 和 from student;

-- 查询数学成绩math平均分
select AVG(math) as 平均分 FROM student;

-- 分组查询
-- 按照性别分组，分别查询男、女同学的数学(math)平均分
SELECT sex,AVG(math) as 平均分 from student GROUP BY sex;

-- 按照性别分组，分别查询男、女同学的数学(math)平均分和人数
SELECT sex,AVG(math) as 平均分,COUNT(id) as 人数 from student GROUP BY sex;

-- 按照性别分组，分别查询男、女同学的数学(math)平均分和人数,d低于70分不参与分组
SELECT sex,AVG(math) as 平均分,COUNT(id) as 人数 from student where math >70 GROUP BY sex;

-- 按照性别分组，分别查询男、女同学的数学(math)平均分和人数,d低于70分不参与分组,分组以后人数要大于2人
SELECT sex,AVG(math) as 平均分,COUNT(id) as 人数 from student where math >70 GROUP BY sex HAVING COUNT(id)>2;


-- 分页查询
-- 每页显示4条记录
-- 语法：limit 开始索引，每页显示的条数
SELECT * FROM student LIMIT 0,4; --第2页

SELECT * FROM student LIMIT 4,4; --第2页

-- 约束
-- 创建时添加
CREATE table db2(
    id int primary key auto_increment, -- 主键约束(非空且唯一)
    name varchar(20) not null, -- 非空约束
    phone VARCHAR(20) UNIQUE -- 唯一约束
    
);
-- 非空约束  创建表完后，添加非空约束
ALTER TABLE db2 MODIFY id int(15) not null;

-- 唯一约束  创建表完后,添加唯一约束
alter table db2 modify phone varchar(25) UNIQUE;
-- 删除唯一约束
alter table db2 drop index phone;

-- 创建完表后，添加主键
alter table db2 modify id int(15) primary key;
-- 删除主键
alter table db2 drop primary key;

-- auto_increment自动增长，一般用于主键id，自动会增加
-- 创建表后，添加自动增长
alter table db2 modify id int auto_increment;
-- 删除自动增长
alter table db2 modify id int;


SELECT *from db2;

drop table db2;


-- 
CREATE TABLE emp (
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(30),
    age INT,
    dep_name VARCHAR(30),
    dep_location VARCHAR(30)
);
-- 添加数据
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳');


-- 解决方案：设置外键
-- 创建部门表(id,dep_name,dep_location)
-- 一方，主表
create table department(
    id int primary key auto_increment,
    dep_name varchar(20),
    dep_location varchar(20)
);
-- 创建员工表(id,name,age,dep_id)
-- 多方，从表
create table employee(
    id int primary key auto_increment,
    name varchar(20),
    age int,
    dep_id int, -- 外键对应主表的主键
    -- 设置外键
    constraint emp_dep_fk foreign key (dep_id) references department(id)
);

-- 删除外键
alter table employee drop foreign key emp_dep_fk;
-- 级联操作
-- 创建完表之后，添加外键，设置级联更新和级联删除
alter table employee add constraint emp_dep_fk foreign key (dep_id) references department(id) on update cascade on delete cascade;


-- 添加 2 个部门
insert into department values(null, '研发部','广州'),(null, '销售部', '深圳');

-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);

-- 查询员工
SELECT * from employee;
-- 查询部门
SELECT * FROM department;


-- 多表操作案例练习

-- 3. 案例
-- 创建旅游线路分类表 tab_category
-- cid 旅游线路分类主键，自动增长
-- cname 旅游线路分类名称非空，唯一，字符串 100
CREATE TABLE tab_category (
  cid INT PRIMARY KEY AUTO_INCREMENT,
  cname VARCHAR(100) NOT NULL UNIQUE
);
        
-- 创建旅游线路表 tab_route
/*
rid 旅游线路主键，自动增长
rname 旅游线路名称非空，唯一，字符串 100
price 价格
rdate 上架时间，日期类型
cid 外键，所属分类
*/
CREATE TABLE tab_route(
        rid INT PRIMARY KEY AUTO_INCREMENT,
        rname VARCHAR(100) NOT NULL UNIQUE,
        price DOUBLE,
        rdate DATE,
        cid INT,
        FOREIGN KEY (cid) REFERENCES tab_category(cid)
);

/*创建用户表 tab_user
uid 用户主键，自增长
username 用户名长度 100，唯一，非空
password 密码长度 30，非空
name 真实姓名长度 100
birthday 生日
sex 性别，定长字符串 1
telephone 手机号，字符串 11
email 邮箱，字符串长度 100
*/
CREATE TABLE tab_user (
        uid INT PRIMARY KEY AUTO_INCREMENT,
        username VARCHAR(100) UNIQUE NOT NULL,
        PASSWORD VARCHAR(30) NOT NULL,
        NAME VARCHAR(100),
        birthday DATE,
        sex CHAR(1) DEFAULT '男',
        telephone VARCHAR(11),
        email VARCHAR(100)
);

/*
创建收藏表 tab_favorite
rid 旅游线路 id，外键
date 收藏时间
uid 用户 id，外键
rid 和 uid 不能重复，设置复合主键，同一个用户不能收藏同一个线路两次
*/
CREATE TABLE tab_favorite (
        rid INT, -- 线路id
        DATE DATETIME,
        uid INT, -- 用户id
        -- 创建复合主键
        PRIMARY KEY(rid,uid), -- 联合主键
        FOREIGN KEY (rid) REFERENCES tab_route(rid),
        FOREIGN KEY(uid) REFERENCES tab_user(uid)
);