-- 作业

USE school

-- 创建教师表 
CREATE TABLE IF NOT EXISTS tb_teachers
(
teanum INT PRIMARY KEY auto_increment,
teaname VARCHAR(20) NOT NULL,
teatel VARCHAR(11) UNIQUE,
teaage INT NOT NULL,
teatitle VARCHAR(10)
);

DROP TABLE IF EXISTS tb_teachers;

-- 添加教师数据
INSERT INTO tb_teachers VALUES(1, '张扬', '12345678901', 32, '中级');
INSERT INTO tb_teachers(teaname, teatel, teaage, teatitle) VALUES
('李飞', '12345678902', 45, '高级'),
('牛二耿', '12345678903', 27, '中级'),
('慕容广场', '12345678904', 31, '初级'),
('诸葛建国', '12345678905', 53, '高级');







-- 创建学院表
CREATE TABLE IF NOT EXISTS tb_college
(
colnum INT PRIMARY KEY auto_increment,
colname VARCHAR(20) NOT NULL UNIQUE,
colURL VARCHAR(200) UNIQUE
);

DROP TABLE IF EXISTS tb_college;

-- 添加学院数据
INSERT INTO tb_college VALUES(1, '理学院', 'www.baidu.com');
INSERT INTO tb_college(colname, colURL) VALUES
('信息与自动化学院', 'www.anjhon1994.github.io'),
('环境工程学院', 'www.Google.com'),
('农学院', 'www.taobao.com');







-- 添加课程表
CREATE TABLE IF NOT EXISTS tb_course
(
counum INT PRIMARY KEY auto_increment,
couname VARCHAR(50),
coutime DATE,
coucredit INT,
couclass INT
);
-- 删除课程表
DROP TABLE IF EXISTS tb_course;
-- 添加课程信息
INSERT INTO tb_course VALUES(1, '大学物理', '2020-4-20', 4, 32);
INSERT INTO tb_course(couname, coutime, coucredit, couclass) VALUES
('高等数学', '2020-4-20', 4, 40),
('毛泽东思想', '2020-4-20', 2, 20),
('线性代数', '2020-4-20', 2, 28),
('诗歌鉴赏', '2020-4-20', 1, 12);




--  1.查询所有学生信息 
SELECT * FROM tb_student;

--  2.查询所有课程名称及学分(投影和别名)
SELECT couname, coucredit FROM tb_course;

-- 3.查询所有学生的姓名和性别
SELECT stuname, stugender FROM tb_student;

-- 4.查询所有女学生的姓名和出生日期
SELECT stuname, stubirth FROM tb_student WHERE stugender=0;

-- 5.查询所有80后学生的姓名、性别和出生日期
SELECT stuname, stugender, stubirth FROM tb_student WHERE stubirth>='1980-1-1';

-- 6.查询姓"杨"的学生姓名和性别
SELECT stuname, stugender FROM tb_student WHERE stuname LIKE '杨%';

-- 7.查询姓"杨"名字两个字的学生姓名和性别
SELECT stuname, stugender FROM tb_student WHERE stuname LIKE '杨_';

-- 8.查询姓"杨"名字三个字的学生姓名和性别
SELECT stuname, stugender FROM tb_student WHERE stuname LIKE '杨__';

-- 9.查询名字中有"不"字或"嫣"字的学生的姓名
SELECT stuname FROM tb_student WHERE stuname LIKE '%不%' OR '%嫣%';

-- 10.查询没有录入家庭住址的学生姓名
SELECT stuname FROM tb_student WHERE stuaddr IS NULL;

-- 11.查询录入了家庭住址的学生姓名
SELECT stuname FROM tb_student WHERE stuaddr IS NOT NULL;

-- 12.查询男学生的姓名和生日按年龄从大到小排列
SELECT stuname, stubirth FROM tb_student WHERE stugender=1 ORDER BY stubirth DESC;