一丶数据库名称：SchoolDB
二丶数据库表信息：角色信息表
| 表名： | t_role | ||||||
| 主键： | r_id | ||||||
| 序号 | 字段名称 | 字段说明 | 类别 | 位数 | 属性 | 备注 | |
| 1 | r_id | 角色编号 | int |
|
| 主键 自动增长 | |
| 2 | r_name_EN | 角色名（英文） | varchar | 20 | 非空 | 不能重复 | |
| 3 | r_name_CH | 角色名（中文） | varchar | 20 | 非空 | 不能重复 | |
| 4 | r_remark | 说明简介 | nvarchar | 500 | 非空 | 默认为“男” | |
数据库表信息：班级信息表
| 表名： | t_class | ||||||
| 主键： | c_id | ||||||
| 序号 | 字段名称 | 字段说明 | 类别 | 位数 | 属性 | 备注 | |
| 1 | c_id | 班级编号 | int |
|
| 主键 自动增长 | |
| 2 | c_classname | 班级名 | varchar | 20 | 非空 | 不能重复 | |
数据库表信息：用户信息表
| 表名： | t_user | ||||||
| 主键： | u_id | ||||||
| 序号 | 字段名称 | 字段说明 | 类别 | 位数 | 属性 | 备注 | |
| 1 | u_id | 用户编号 | int |
|
| 主键 自动增长 | |
| 2 | u_username | 用户登陆名 | varchar | 20 | 非空 | 不能重复 | |
| 3 | u_password | 用户登陆密码 | varchar | 20 | 非空 |
| |
| 4 | u_name | 用户名 | varchar | 20 | 非空 |
| |
| 5 | u_sex | 性别 | char | 2 | 非空 | 默认为“男” 只能是男或女 | |
| 6 | u_age | 年龄 | int |
| 非空 | 年龄只能0~100区间 | |
| 7 | u_phoneNum | 电话号码 | varchar | 20 | 非空 | 长度必须是11位 | |
| 8 | u_address | 家庭住址 | varchar | 50 |
|
| |
| 9 | u_type | 状态 是否禁用 | int |
| 非空 | 只能是0或1 1 正常 0 禁用 | |
| 10 | r_id | 角色 | int |
| 非空 | 外键 | |
| 11 | c_id | 班级 | int |
| 非空 | 外键 | |
三丶实现步骤
1、向角色信息表、班级信息表、用户信息表添加对数据 分别使用单行或多行插入数据的方式完成。并使用全查询分别查询出三张表的数据。
如下图1~ 图3所示：
图1
图2
图3
要求：
角色信息表使用单行数据插入的方式完成对数据的插入
班级信息表和用户信息表使用多行插入的方式完成对数据的插入
2、查询用户信息表 如下图4所示：
图4
要求：
给字段起别名，显示用户信息表
3、按照下列需求完成所有条件查询：
-- 一，创建数据库SchoolDB-- 1，删除数据库DROP DATABASE IF EXISTS SchoolDB;-- 2，创建数据库CREATE DATABASE SchoolDB;-- 3，修改数据库编码方式和字符集排列顺序ALTER DATABASE SchoolDB CHARACTER SET utf8 COLLATE utf8_bin;-- 4，使用数据库USE SchoolDB;-- 二，在数据库SchoolDB中创建3张表-- 1，角色信息表（t_role）DROP TABLE IF EXISTS t_role;CREATE TABLE t_role(r_id INT PRIMARY KEY AUTO_INCREMENT, -- 角色编号r_name_EN VARCHAR(20) NOT NULL UNIQUE, -- 角色名（英文）r_name_CH VARCHAR(20) NOT NULL UNIQUE, -- 角色名（中文）r_remark NVARCHAR(500) NOT NULL DEFAULT '男' -- 说明简介);-- 2，班级信息表（t_class）DROP TABLE IF EXISTS t_class;CREATE TABLE t_class(c_id INT PRIMARY KEY AUTO_INCREMENT, -- 班级编号c_classname VARCHAR(20) NOT NULL UNIQUE -- 班级名);-- 3，用户信息表（t_user）DROP TABLE IF EXISTS t_user;CREATE TABLE t_user(u_id INT PRIMARY KEY AUTO_INCREMENT, -- 用户编号u_username VARCHAR(20) NOT NULL UNIQUE, -- 用户登陆名u_password VARCHAR(20) NOT NULL, -- 用户登陆密码u_name VARCHAR(20) NOT NULL, -- 用户名u_sex CHAR(2) NOT NULL DEFAULT '男', -- 性别u_age INT NOT NULL, -- 年龄u_phoneNum VARCHAR(11) NOT NULL, -- 电话号码u_address VARCHAR(50), -- 家庭住址u_type INT NOT NULL DEFAULT 1, -- 状态是否禁用r_id INT NOT NULL, -- 角色，外键c_id INT, -- 班级，外键FOREIGN KEY (r_id) REFERENCES t_role(r_id),FOREIGN KEY (c_id) REFERENCES t_class(c_id));-- 三添加数据-- 1，添加角色信息表（t_role）数据INSERT INTO t_role VALUES(DEFAULT,'boss','超级管理员','可以管理所有数据操作');INSERT INTO t_role VALUES(DEFAULT,'admin','管理员','可以管理大部分数据操作');INSERT INTO t_role VALUES(DEFAULT,'stu','学生','说明');INSERT INTO t_role VALUES(DEFAULT,'teacher','老师','说明');SELECT * FROM t_role;-- 2,添加班级信息表（t_class）数据INSERT INTO t_class VALUES(DEFAULT,'物联网201401班');INSERT INTO t_class VALUES(DEFAULT,'物联网201402班');INSERT INTO t_class VALUES(DEFAULT,'物联网201403班');INSERT INTO t_class VALUES(DEFAULT,'物联网201404班');INSERT INTO t_class VALUES(DEFAULT,'物联网201405班');SELECT * FROM t_class;-- 3，添加用户信息表（t_user）数据INSERT INTO t_user VALUES(1001,'abc','abc','abc','男',70,110,'武汉湖北科职',1,1,NULL);INSERT INTO t_user VALUES(1002,'admin','admin','admin','男',0,120,'武汉湖北科职',1,2,NULL);INSERT INTO t_user VALUES(1003,'huangba','huang66ba','黄八','男',20,15122535477,'北京东城',0,3,3);INSERT INTO t_user VALUES(1004,'jack','jklove','杰克','男',19,15011982675,'湖南长沙',1,3,4);INSERT INTO t_user VALUES(1005,'lisi',123456,'李四','男',20,15002726555,'湖北武汉',1,3,1);INSERT INTO t_user VALUES(1006,'tom','tomandjack','汤姆','女',20,13002766545,'湖南韶山',1,3,5);INSERT INTO t_user VALUES(1007,'wangwu','wangwu520','王五','男',34,13645267654,'湖北黄冈',1,4,NULL);INSERT INTO t_user VALUES(1008,'yanqi','yanyan112','燕七','女',28,13288625642,'北京海淀',1,4,NULL);INSERT INTO t_user VALUES(1009,'zhangsan',123,'张三','女',19,13017656783,'湖北武汉',1,3,2);INSERT INTO t_user VALUES(10010,'zhaoliu','01234567...','赵六','女',54,15524786722,'湖北黄冈',0,4,1);SELECT * FROM t_user;-- 查询用户信息表 如下图4所示： 要求：给字段起别名，显示用户信息表SELECT u_id AS '用户编号', u_username AS '用户名', u_name AS '姓名', u_sex AS '性别', u_age AS '年龄', u_phoneNum AS '电话', u_address AS '地址', CASE u_type WHEN 0 THEN '禁用' ELSE '正常' END AS '状态', b.r_name_CH AS '角色名称', c.c_classname AS '班级名称'FROM t_user aINNER JOIN t_role b ON a.r_id = b.r_idLEFT JOIN t_class c ON a.c_id = c.c_id;-- 四，完成如下查询题目-- 3、按照下列需求完成所有条件查询：-- -------- 1，过滤重复记录(distinct) -------------- -- 过滤用户表重复的地址SELECT DISTINCT u_address 家庭住址 FROM t_user;-- 过滤用户表重复的年龄SELECT DISTINCT u_age 年龄 FROM t_user-- --- 2，显示若干条(top)----------------- 显示班级表前3条记录SELECT * FROM t_class LIMIT 3;-- 显示用户表前5条记录SELECT * FROM t_user LIMIT 5;-- --- 3，简单条件查询(where)----------------- 显示用户表所有"男"性的信息SELECT * FROM t_user WHERE u_sex = '男';-- 显示用户表中是 学生的信息SELECT * FROM t_user WHERE u_type = 1;-- 显示用户表中年龄在24到60岁之间的人的信息(提示:between and)SELECT * FROM t_user WHERE u_age BETWEEN 24 AND 60;-- 显示用户表中年龄不在24到60岁之间的人的信息SELECT * FROM t_user WHERE u_age NOT BETWEEN 24 AND 60;-- 显示角色表中 角色名(中文)为"管理员’、"学生"、"老师"的信息SELECT * FROM t_role WHERE r_name_CH IN ('管理员', '学生', '老师');-- 显示角色表中 角色名(中文)为"超级管理员"的信息SELECT * FROM t_role WHERE r_name_CH = '超级管理员';-- 显示用户表 班级为空的信息SELECT * FROM t_user WHERE c_id IS NULL;-- 显示用户表 班级不为空的信息SELECT * FROM t_user WHERE c_id IS NOT NULL;-- 4，模糊杳询(like /not like)-- 查询用户表 电话号码是以15开头的所有人信息SELECT * FROM t_user WHERE u_phoneNum LIKE '15%';-- 查询用户表 地址是湖北或湖南的所有人信息SELECT * FROM t_user WHERE u_address LIKE '%湖北%' OR u_address LIKE '%湖南%';-- 查询用户表 地址除湖北或湖南之外的所有人信息SELECT * FROM t_user WHERE u_address NOT LIKE '%湖北%' AND u_address NOT LIKE '%湖南%';-- -----5，杳询排序(order by)-- 对用户表 用户年龄升序排序SELECT * FROM t_user ORDER BY u_age ASC;-- 对用户表 用户年龄降序排序SELECT * FROM t_user ORDER BY u_age DESC;-- -----6，对查询结果进行计算(求和sum 求平均数avg统计行数count)----------------- 求用户表用户年龄的总和SELECT SUM(u_age) 年龄的总和 FROM t_user;-- 求用户表 用户年龄的平均数SELECT AVG(u_age) 年龄的平均数 FROM t_user;-- 求用户表 有多少个用户SELECT COUNT(*) 用户人数 FROM t_user;-- 求用户表 有多少个老师SELECT COUNT(*) 老师人数 FROM t_user WHERE r_id=4;-- ---7，分组(group by/having)-------- 求各个角色的人数SELECT r_name_CH 角色名（中文）,COUNT(*) 人数 FROM t_user INNER JOIN t_role ON t_user.r_id = t_role.r_id GROUP BY t_role.r_id;-- 求各个角色中 所有人总年龄超过100的角色有哪些SELECT r_name_CH 角色名（中文） FROM t_role WHERE r_id IN (SELECT DISTINCT r_id FROM t_user GROUP BY r_id HAVING SUM(u_age) > 100);-- 4、按照下列需求完成所有链接查询：-- ---8，联接查询------ (1)内连接(inner join)-- 查询所有用户信息显示用户信息及对应的角色名(中文)SELECT t_user.*,r_name_CH 角色名（中文） FROM t_user INNER JOIN t_role ON t_user.r_id = t_role.r_id;-- 查询所有用户信息显示用户信息及对应的班级名称SELECT t_user.*,c_classname 班级名称 FROM t_user INNER JOIN t_class ON t_user.c_id = t_class.c_id;-- 查询所有用户信息显示用户信息及对应的角色名(中文)和班级名称SELECT t_user.*,r_name_CH '角色名(中文)',c_classname 班级名称FROM t_userINNER JOIN t_role ON t_user.r_id = t_role.r_idINNER JOIN t_class ON t_user.c_id = t_class.c_id;-- 查询所有"男，用户并且年龄在10到24的信息显示用户信息及对应的角色名(中文)和班级名称SELECT t_user.*,r_name_CH '角色名(中文)',c_classname 班级名称FROM t_userINNER JOIN t_role ON t_user.r_id = t_role.r_idINNER JOIN t_class ON t_user.c_id = t_class.c_idWHERE u_sex= '男' AND u_age BETWEEN 10 AND 24;-- (2)内联接的where写法-- 查询所有"男’用户并且年龄在10到24的信息显示用户信息及对应的角色名(中文)和班级名称SELECT t_user.*,r_name_CH '角色名(中文)',c_classname 班级名称FROM t_userINNER JOIN t_role ON t_user.r_id = t_role.r_idINNER JOIN t_class ON t_user.c_id = t_class.c_idWHERE u_sex= '男' AND u_age BETWEEN 10 AND 24;-- (3)外联接(left/right [outer] join)-- 查询所有用户信息显示用户信息及对应的角色名(中文)和班级名称，以主表为主SELECT t_user.*,r_name_CH '角色名(中文)',c_classname 班级名称FROM t_user LEFT JOIN t_role ON t_user.r_id = t_role.r_id LEFT JOIN t_class ON t_user.c_id = t_class.c_id;-- 查询所有用户信息显示用户信息及对应的角色名(中文)和班级名称，以副表为主SELECT t_user.*,r_name_CH,c_classnameFROM t_role RIGHT JOIN t_user ON t_user.r_id = t_role.r_id LEFT JOIN t_class ON t_user.c_id = t_class.c_id;
来源地址：https://blog.csdn.net/m0_74293254/article/details/132474472
