当前位置：首页 > backend >正文

创建表结构

backend 2025/5/16 15:08:25

01.创建表

MySQL> create database tomdb charset utf8;
MySQL> use tomdb;

1.1 表MUL

1.2 创建表

# --学生表
CREATE TABLE `Student`(`s_id` VARCHAR(20),`s_name` VARCHAR(20) NOT NULL DEFAULT '',`s_birth` VARCHAR(20) NOT NULL DEFAULT '',`s_sex` VARCHAR(10) NOT NULL DEFAULT '',PRIMARY KEY(`s_id`)
);
# --课程表
CREATE TABLE `Course`(`c_id`  VARCHAR(20),`c_name` VARCHAR(20) NOT NULL DEFAULT '',`t_id` VARCHAR(20) NOT NULL,PRIMARY KEY(`c_id`)
);
# --教师表
CREATE TABLE `Teacher`(`t_id` VARCHAR(20),`t_name` VARCHAR(20) NOT NULL DEFAULT '',PRIMARY KEY(`t_id`)
);
# --成绩表
CREATE TABLE `Score`(`s_id` VARCHAR(20),`c_id`  VARCHAR(20),`s_score` INT(3),PRIMARY KEY(`s_id`,`c_id`)
);

02.创建表

2.0 创建数据展示

2.1 `学生表`

--插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');

2.2 `课程表`

--课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

2.3 `教师表`

--教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

2.4 `成绩表`

--成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

03.查询

3.1 链表查询

SELECT * from Student as a  LEFT JOIN Score as b on a.s_id=b.s_id;
SELECT * from Student as a  RIGHT JOIN Score as b on a.s_id=b.s_id;
SELECT * from Student as a  INNER JOIN Score as b on a.s_id=b.s_id;

3.2 按姓名分组

MySQL> # SELECT name, COUNT(*) FROM   user GROUP BY name;
+------+----------+
| name | COUNT(*) |
+------+----------+
| 小丽 |        1 |
| 小明 |        3 |
| 小王 |        2 |
+------+----------+
3 rows in set (0.01 sec)

3.3 with rollup统计登录次数

按姓名进行分组，再统计每个人登录的次数： with rollup

MySQL># SELECT name, SUM(singin) as singin_count FROM  user GROUP BY name WITH ROLLUP;
+------+--------------+
| name | singin_count |
+------+--------------+
| 小丽 |        2 |
| 小明 |        7 |
| 小王 |        7 |
| NULL         16 |
+------+--------------+
4 rows in set (0.00 sec)# WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。
# 按姓名进行分组，再统计每个人登录的次数，其中记录 NULL 表示所有人的登录次数。

3.4 coalesce统计登录次数

按姓名进行分组，再统计每个人登录的次数：coalesce 来设置一个可以取代 NUll 的名称

MySQL># SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM  user GROUP BY name WITH ROLLUP;
+------------------------+--------------+
| coalesce(name, '总数') | singin_count |
+------------------------+--------------+
| 小丽                   |            2 |
| 小明                   |            7 |
| 小王                   |            7 |
| 总数                   |           16 |
+------------------------+--------------+
4 rows in set (0.00 sec)#1、我们可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：
#2、select coalesce(a,b,c);
#3、参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

3.5 过滤分组HAVING

过滤分组HAVING： 查找登录次数大于2的所有用户

MySQL> SELECT name, SUM(singin) as singin_count FROM  user GROUP BY name HAVING COUNT(*) >= 2;
+------+--------------+
| name | singin_count |
+------+--------------+
| 小明 |         7 |
| 小王 |         7 |
+------+--------------+
2 rows in set (0.00 sec)

3.6 按学生分组求各科平均成绩

MySQL> select *,avg(score) from student group by name;
+----+----------+-------+---------+------------+
| id | name    | score | course  | avg(score) |
+----+----------+-------+---------+------------+
| 4 | lisi    |  88 |  math   |   88.0000 |
| 1 | zhangsan |  88 |  english |   93.0000 |
+----+----------+-------+----- ----+------------+
2 rows in set (0.00 sec)

3.7 时间过滤

10天前注册的用户中同名用户大于或等于2的数据

MySQL># select *,count(name) from user where datediff(NOW(),date)>10 group by name having count(name)>=2;
+----+------+---------------------+--------+-------------+
| id | name | date                | singin | count(name) |
+----+------+---------------------+--------+-------------+
|  1 | 小明 | 2016-04-22 15:25:33 |      1 |           3 |
|  2 | 小王 | 2016-04-20 15:25:47 |      3 |           2 |
+----+------+---------------------+--------+-------------+
2 rows in set (0.00 sec)# 原题：请查找商品表中最近30天至少有20天都有销售记录的商品

查看全文

http://www.xdnf.cn/news/1313.html