-- 作業(yè)

USE school

-- 創(chuàng)建教師表 
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;

-- 添加教師數(shù)據(jù)
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, '高級');







-- 創(chuàng)建學(xué)院表
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;

-- 添加學(xué)院數(shù)據(jù)
INSERT INTO tb_college VALUES(1, '理學(xué)院', 'www.baidu.com');
INSERT INTO tb_college(colname, colURL) VALUES
('信息與自動化學(xué)院', 'www.anjhon1994.github.io'),
('環(huán)境工程學(xué)院', 'www.Google.com'),
('農(nóng)學(xué)院', '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, '大學(xué)物理', '2020-4-20', 4, 32);
INSERT INTO tb_course(couname, coutime, coucredit, couclass) VALUES
('高等數(shù)學(xué)', '2020-4-20', 4, 40),
('毛澤東思想', '2020-4-20', 2, 20),
('線性代數(shù)', '2020-4-20', 2, 28),
('詩歌鑒賞', '2020-4-20', 1, 12);




--  1.查詢所有學(xué)生信息 
SELECT * FROM tb_student;

--  2.查詢所有課程名稱及學(xué)分(投影和別名)
SELECT couname, coucredit FROM tb_course;

-- 3.查詢所有學(xué)生的姓名和性別
SELECT stuname, stugender FROM tb_student;

-- 4.查詢所有女學(xué)生的姓名和出生日期
SELECT stuname, stubirth FROM tb_student WHERE stugender=0;

-- 5.查詢所有80后學(xué)生的姓名、性別和出生日期
SELECT stuname, stugender, stubirth FROM tb_student WHERE stubirth>='1980-1-1';

-- 6.查詢姓"楊"的學(xué)生姓名和性別
SELECT stuname, stugender FROM tb_student WHERE stuname LIKE '楊%';

-- 7.查詢姓"楊"名字兩個字的學(xué)生姓名和性別
SELECT stuname, stugender FROM tb_student WHERE stuname LIKE '楊_';

-- 8.查詢姓"楊"名字三個字的學(xué)生姓名和性別
SELECT stuname, stugender FROM tb_student WHERE stuname LIKE '楊__';

-- 9.查詢名字中有"不"字或"嫣"字的學(xué)生的姓名
SELECT stuname FROM tb_student WHERE stuname LIKE '%不%' OR '%嫣%';

-- 10.查詢沒有錄入家庭住址的學(xué)生姓名
SELECT stuname FROM tb_student WHERE stuaddr IS NULL;

-- 11.查詢錄入了家庭住址的學(xué)生姓名
SELECT stuname FROM tb_student WHERE stuaddr IS NOT NULL;

-- 12.查詢男學(xué)生的姓名和生日按年齡從大到小排列
SELECT stuname, stubirth FROM tb_student WHERE stugender=1 ORDER BY stubirth DESC;