数据库作业1

数据库作业1

作业1

-- 创建 mytest 数据库
CREATE DATABASE mytest charset=utf8; 
-- 显示当前所有数据库
SHOW DATABASES;
-- 显示当前使用数据库
SELECT DATABASE();
-- 使用 mytest
USE mytest;
-- 展示该数据库所有表
SHOW TABLES;
-- 创建 Teacher 表 且拥有字段：教师编号tid(主键) 姓名name 年龄age(默认30) 性别gender(男,女)
CREATE TABLE Teacher(tid INT PRIMARY KEY AUTO_INCREMENT NOT NULL,name VARCHAR(30),age INT DEFAULT(30),gender ENUM("男","女"))ENGINE=INNODB DEFAULF CHARSET=utf8;
-- 查看	Teacher 表结构
DESC Teacher;
-- 创建 Course 表 且拥有字段：课程编号cid(主键) 课程名称cname 教师编号tid
CREATE TABLE Course(cid INT PRIMARY KEY AUTO_INCREMENT NOT NULL,cname VARCHAR(30),tid INT UNIQUE)ENGINE=INNODB DEFAULF CHARSET=utf8;
-- 查看 course 表结构
DESC Course;
-- 修改 teacher 表的 姓名字段 name 为 tname
ALTER TABLE Teacher CHANGE name tname VARCHAR(30);
-- 查看 teacher 表数据
SELECT * FROM Teacher;
-- 为 teacher 表插入数据
INSERT INTO Teacher(tid,tname,age,gender) VALUES(1,"AMY","22","女");
INSERT INTO Teacher(tid,tname,age,gender) VALUES(2,"jm","25","男"),(3,"ss","24","女");
-- 为 course 表插入数据
INSERT INTO Course(cid,cname,tid) VALUES(1,"python",1);
INSERT INTO Course(cid,cname,tid) VALUES(2,"liaoli",2),(3,"tiyu",3);
-- 查看 course 表数据
SELECT * FROM Course;
-- 求 teacher 表的 女性总数
SELECT COUNT(*) FROM Teacher WHERE gender="女";
-- 查询 course 表中 tid 为 3 的个数
SELECT COUNT(*) FROM Course WHERE tis=3;
-- 查询 teacher 表中 tid 为 3 的老师姓名
SELECT tname FROM Teacher WHERE tis=3;
-- 查询 老师年龄在 26-30 的 姓名
SELECT tname FROM Teacher WHERE age BETWEEN 26 AND 30;
-- 查询 名字为 j 开头的 老师信息
SELECT * FROM Teacher WHERE tname LIKE "j%";

作业2

-- 查询老师男女总数
SELECT gender,COUNT(*) FROM Teacher GROUP BY gender;
-- 查询老师男女总数大于2的具体姓名，年龄
SELECT gender,COUNT(*),GROUP_CONCAT(tname,age) FROM Teacher GROUP BY gender HAVING COUNT(*)>2;
-- 查询老师的 年龄 在18-30岁的男性 年龄进行升序，当年龄相同时，按照id降序。
SELECT * FROM Teacher WHERE gender="男" AND (age BETWEEN 18 AND 30) ORDER BY age ASC,tid desc;
-- 取出老师的id为2跟3的数据
SELECT * FROM Teacher LIMIT 1,2;
-- 查询 各老师 所带的课程（即studen的tid=course的tid）
select * from Teacher AS t inner  join Course AS c on t.'tidi' = c.'it'-- 查询最大年龄男老师 的 姓名
SELECT name from Teacher WHERE age=(SELECT MAX(age) FROM Teacher WHERE gender="男") and gender="男";