目录
16.1、mysql 简单操作
16.2、增删改查
16.2.1、create 用法
16.2.2、alter用法
16.2.3、insert 用法
16.2.4、update用法
16.2.5、delete用法
16.2.6、select 用法
16.3、外键约束
16.4、多表查询练习
// 暂无理论，待更新状态
16.1、mysql 简单操作
// 创建 MariaDB [(none)]> create database xiong; // 创建库的时候指定字符编码 MariaDB [(none)]> create database if not exists xiong1 charset 'gbk'; // 查看所有表 MariaDB [(none)]> show databases; // 查看数据库的创建信息 MariaDB [(none)]> show create database xiong; +----------+----------------------------------------------------------------+ | Database | Create Database | +----------+----------------------------------------------------------------+ | xiong | CREATE DATABASE `xiong` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+----------------------------------------------------------------+ // 查看表的创建信息 MariaDB [xiong1]> show create table fristtab; // 查看表详细信息 MariaDB [xiong1]> desc fristtab; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | smallint(6) | NO | PRI | NULL | | | name | varchar(25) | YES | | NULL | | | phone | char(13) | YES | | NULL | | | addr | varchar(300) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ // 删除库 MariaDB [(none)]> drop database xiong;
// 约束条件
非空： not null 唯一键：unique 主键： primary key 自动增长： auto_increment
16.2 增删改表
创建第一张表
MariaDB [(none)]> use xiong1 Database changed MariaDB [xiong1]> CREATE TABLE fristtab( -> id SMALLINT primary KEY, -> name VARCHAR(25), /* 定长13位*/ -> phone CHAR(13), -> addr VARCHAR(300) -> ); Query OK, 0 rows affected (0.19 sec)
16.2.1、create 用法
// create 语法：
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
// 创建表并复制fristtab的表结构，只复制表的结构
MariaDB [xiong1]> create TABLE frist2 like fristtab;
// 复制表结构并且复制表内信息
MariaDB [xiong1]> create TABLE frist3 SELECT * from frist;16.2.2、alter用法
// 语法： ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name [alter_specification [, alter_specification] ...] [partition_options] // 修改数据库的字符编码 MariaDB [(none)]> alter database xiong1 charset 'utf8'; // 添加一个字段 MariaDB [xiong1]> ALTER TABLE fristtab ADD mail SMALLINT(6); // 添加多个字段 MariaDB [xiong1]> ALTER TABLE fristtab ADD A INT, ADD B INT , ADD C INT; // 修改字段类型 // 语法 alter table table_name modifry 字段名 类型 [约束条件] [first after 字段] // 修改Mail字段为varchar非空 MariaDB [xiong1]> ALTER TABLE fristtab modify mail VARCHAR(6) not NULL ; // 修改Mail字段为varchar非空, 在id字段后插入 MariaDB [xiong1]> ALTER TABLE fristtab modify mail VARCHAR(6) not NULL after id; // 同时修改多个字段 MariaDB [xiong1]> ALTER TABLE fristtab modify B SMALLINT, modify C SMALLINT; // 修改字段名称 // 语法 alter table table_name change old_字段 new_字段 类型 [约束条件] [first after 字段] // 修改字段名称由Mail改为mails ,不能同时修改两个 MariaDB [xiong1]> alter table fristtab change mail mails varchar(5) not null after name; // 删除一个字段 MariaDB [xiong1]> ALTER TABLE fristtab DROP mails;
// 修改表名
//语法 rename table old_tablename to new_tablename // 修改表的名称 MariaDB [xiong1]> rename table fristtab to first;
16.2.3、insert 用法
// insert 语法
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
// 增加一行数据 按已有的列一个一个对比添加
MariaDB [xiong1]> insert INTO frist (id,name,mails,phone,addr) VALUES (1,"xiong","88com","1111111111111","bjaa");
// 添加如果id自动增就可以忽略它, 但如果想忽略中间的比如mails那它就会报错了
MariaDB [xiong1]> insert INTO frist (name,phone,addr) VALUES ("uu","2222222222222","cxs");
ERROR 1364 (HY000): Field 'mails' doesn't have a default value
// 自动增长id, 让addr为空就没问题，前一个字段跟最后一个字段可以留空,中间一旦留空就会报上一个例子的错误
MariaDB [xiong1]> insert INTO frist (name,mails,phone) VALUES ("uu",'cc',"2222222222222");
Query OK, 1 row affected (0.03 sec)
// 查询结果为 | 3 | uu | cc | 2222222222222 | NULL |
// 插入多行 以逗号做为分隔,继续写下一行
insert INTO frist (name,mails,phone) VALUES ("gg",'8.com',"8888"),
("hh",'9.com',"9999");
// 没有列表的时候插入数据, 需要一个字段对应一个值
MariaDB [xiong1]> INSERT INTO frist VALUES ('uu','cc','11111','shahai');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
// 成功的写法应为：
MariaDB [xiong1]> INSERT INTO frist VALUES ('4','uu','cc','11111','shahai');
// 添加多行
MariaDB [xiong1]> INSERT INTO frist VALUES ('7','nn','10.com','101010','mobu'),
('8','sd','11.com','111111','debu');16.2.4、update用法
// update 语法： 更新时一定要加一个条件where 否则表中所有的数据都会被修改
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
// 将name为uu的名称更换为repa
MariaDB [xiong1]> UPDATE frist set name="repa" WHERE name="uu";
// 将id为3的 name 改为repa3 地址修改为hebei
MariaDB [xiong1]> update frist set name="repa3",addr="hebei" where id=3;16.2.5、delete用法
//delete 语法： DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name[.*] [, tbl_name[.*]] ... USING table_references [WHERE where_condition] // 删除id为2的 MariaDB [xiong1]> delete from frist where id=2; // 删除id为3的或者名称为repa3 MariaDB [xiong1]> delete from frist where id=3 and name="repa3"; // 删除Id为7的 并且名称为hh的两行或一行数据 MariaDB [xiong1]> delete from frist where id=7 or name="hh"; // 删除表 MariaDB [xiong1]> delete from frist // 清空表 慎用之 MariaDB [xiong1]> truncate FROM frist; // 例 创建一个与frist一样的表用于测试, 清空 MariaDB [xiong1]> create TABLE frist3 SELECT * from frist; Query OK, 3 rows affected (0.26 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [xiong1]> select * from frist3; +----+-------+-------+---------------+--------+ | id | name | mails | phone | addr | +----+-------+-------+---------------+--------+ | 1 | xiong | 88com | 1111111111111 | bjaa | | 4 | repa | cc | 11111 | shahai | | 5 | gg | 8.com | 8888 | NULL | +----+-------+-------+---------------+--------+ 3 rows in set (0.00 sec) MariaDB [xiong1]> truncate frist3; Query OK, 0 rows affected (0.16 sec) MariaDB [xiong1]> select * from frist3; Empty set (0.00 sec)
16.2.6、select 用法
// 新建一个初始表
-- 名称, 重量, 单价, 总价
CREATE TABLE menu(
id int AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20),
nums FLOAT(5,2),
unit_price FLOAT(5,2)
);
// 数据内容
INSERT INTO menu (name,nums,unit_price) VALUES ('apple',5.5,10),
('peach',10,4),
('pear',8,3),
('pear',7,3.1),
('plum',33,2),
('orange',7,4),
('orange',3,4),
('strawberry',8,5);
// 查询表达式
SELECT *|field1,filed2 ... FROM tab_name
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数
// 语法： select *|fild1,fild2 ... from table_name
// 查询表内所有内容
MariaDB [xiong1]> select * from menu;
// 只查询指定字段内容
MariaDB [xiong1]> select name,nums from menu;
// 过滤表中重复内容
MariaDB [xiong1]> SELECT DISTINCT name,nums FROM menu;
// select 中也可以使用表达式，比如字段as 别名 或者 字段 别名 (不建议第二种 字段 别名)
MariaDB [xiong1]> SELECT DISTINCT name as 姓名,nums as 个数 FROM menu;
+------------+-------+
| 姓名 | 个数 |
+------------+-------+
| apple | 5.50 |
+------------+-------+// where，进行过滤查询操作
// 查询name为apple的内容 MariaDB [xiong1]> select * from menu where name="apple"; +----+-------+------+------------+ | id | name | nums | unit_price | +----+-------+------+------------+ | 1 | apple | 5.50 | 10.00 | +----+-------+------+------------+ // 也可以使用 比较运算符 < > = !=的方式来进行过滤 MariaDB [xiong1]> select * from menu where nums<5; +----+--------+------+------------+ | id | name | nums | unit_price | +----+--------+------+------------+ | 7 | orange | 3.00 | 4.00 | +----+--------+------+------------+ MariaDB [xiong1]> select * from menu where nums=7.00; +----+--------+------+------------+ | id | name | nums | unit_price | +----+--------+------+------------+ | 4 | pear | 7.00 | 3.10 | | 6 | orange | 7.00 | 4.00 | +----+--------+------+------------+ MariaDB [xiong1]> select * from menu where nums>3; +----+------------+-------+------------+ | id | name | nums | unit_price | +----+------------+-------+------------+ | 1 | apple | 5.50 | 10.00 | | 2 | peach | 10.00 | 4.00 | | 3 | pear | 8.00 | 3.00 | | 4 | pear | 7.00 | 3.10 | ..... MariaDB [xiong1]> select * from menu where nums!=7.00; +----+------------+-------+------------+ | id | name | nums | unit_price | +----+------------+-------+------------+ | 1 | apple | 5.50 | 10.00 | | 2 | peach | 10.00 | 4.00 | | 3 | pear | 8.00 | 3.00 | | 5 | plum | 33.00 | 2.00 | .... // between 8 到 10内的数据，包含等于8，10 MariaDB [xiong1]> select * from menu where nums between 8 and 10; +----+------------+-------+------------+ | id | name | nums | unit_price | +----+------------+-------+------------+ | 2 | peach | 10.00 | 4.00 | | 3 | pear | 8.00 | 3.00 | | 8 | strawberry | 8.00 | 5.00 | +----+------------+-------+------------+ // in 等于8 或者20的数值才会显示 MariaDB [xiong1]> select * from menu where nums in(8,20); +----+------------+------+------------+ | id | name | nums | unit_price | +----+------------+------+------------+ | 3 | pear | 8.00 | 3.00 | | 8 | strawberry | 8.00 | 5.00 | +----+------------+------+------------+ // like 模糊查询 语法：WHERE column LIKE 'XXXX%' MariaDB [xiong1]> select * from menu where name like 'app%'; +----+-------+------+------------+ | id | name | nums | unit_price | +----+-------+------+------------+ | 1 | apple | 5.50 | 10.00 | +----+-------+------+------------+ // 查询总价大于20的物品 受查询优先级的影响 sql先执行where然后再查看其它, where 总价 >20查询会报错 MariaDB [xiong1]> select name,nums*unit_price as 总价 from menu where nums*unit_price>20; +------------+-------+ | name | 总价 | +------------+-------+ | apple | 55.00 | | peach | 40.00 | | pear | 24.00 | | pear | 21.70 | | plum | 66.00 | | orange | 28.00 | | strawberry | 40.00 | +------------+-------+ // 查询总价区间为10到25的有哪些 MariaDB [xiong1]> select name,nums*unit_price as 总价 from menu where nums*unit_price between 10 and 25; +--------+-------+ | name | 总价 | +--------+-------+ | pear | 24.00 | | pear | 21.70 | | orange | 12.00 | +--------+-------+
// order by 指定排序的列，排序的列即可是表中的列名，也可以是select 语句后指定的别名
语法： -- select *|field1,field2... from tab_name order by field [Asc|Desc] asc: 默认排序升序 desc: 降序,值大的在上 ORDER BY 子句应位于SELECT语句的结尾 // 查询nums大于等10并且让它 asc 排序 MariaDB [xiong1]> select * from menu where nums>=10 order by nums; +----+-------+-------+------------+ | id | name | nums | unit_price | +----+-------+-------+------------+ | 2 | peach | 10.00 | 4.00 | | 5 | plum | 33.00 | 2.00 | +----+-------+-------+------------+ // desc 降序 MariaDB [xiong1]> select * from menu where nums>=10 order by nums desc; +----+-------+-------+------------+ | id | name | nums | unit_price | +----+-------+-------+------------+ | 5 | plum | 33.00 | 2.00 | | 2 | peach | 10.00 | 4.00 | +----+-------+-------+------------+ // 查询所有价格的总价并让它降序排序 MariaDB [xiong1]> select name,nums*unit_price as total from menu order by totaldesc; +------------+-------+ | name | total | +------------+-------+ | plum | 66.00 | | apple | 55.00 | | peach | 40.00 | | strawberry | 40.00 | | orange | 28.00 | | pear | 24.00 | | pear | 21.70 | | orange | 12.00 | +------------+-------+
// 函数
// count 统计个数 MariaDB [xiong1]> select count(id) from menu; +-----------+ | count(id) | +-----------+ | 8 | +-----------+ // sum 统计和 MariaDB [xiong1]> select sum(nums) from menu; +-----------+ | sum(nums) | +-----------+ | 81.50 | +-----------+ 1 row in set (0.00 sec) //统计总价的平均值 MariaDB [xiong1]> select sum(nums*unit_price)/count(name) as 平均值 from menu; +-----------+ | 平均值 | +-----------+ | 35.837500 | +-----------+ 1 row in set (0.00 sec) // avg 平均值 // 统计总价的平均值 MariaDB [xiong1]> select avg(nums*unit_price) as 平均值 from menu; +-----------+ | 平均值 | +-----------+ | 35.837500 | +-----------+ // max,min 最大值,最小值 //取出最小值, 如果直接name,nums查询结果是不对的，只能使用联合查询 MariaDB [xiong1]> select name,nums from menu where nums = (select min(nums) from menu); +--------+------+ | name | nums | +--------+------+ | orange | 3.00 | +--------+------+ // 取出最大值 MariaDB [xiong1]> select name,nums from menu where nums = (select max(nums) from menu); +------+-------+ | name | nums | +------+-------+ | plum | 33.00 | +------+-------+
// group by 分组查询
// 分组查询 按名称筛选 MariaDB [xiong1]> select * from menu group by name; +----+------------+-------+------------+ | id | name | nums | unit_price | +----+------------+-------+------------+ | 1 | apple | 5.50 | 10.00 | | 6 | orange | 7.00 | 4.00 | | 2 | peach | 10.00 | 4.00 | | 3 | pear | 8.00 | 3.00 | | 5 | plum | 33.00 | 2.00 | | 8 | strawberry | 8.00 | 5.00 | +----+------------+-------+------------+ // 按字段筛选 效果与名称筛选一样 MariaDB [xiong1]> select * from menu group by 2; // 按名称排序，显示每件商品的和 MariaDB [xiong1]> select name,nums*unit_price as total from menu group by name; +------------+-------+ | name | total | +------------+-------+ | apple | 55.00 | | orange | 28.00 | | peach | 40.00 | | pear | 24.00 | | plum | 66.00 | | strawberry | 40.00 | +------------+-------+ // 按名称排序,并且显示总价大于30的商品 MariaDB [xiong1]> select name,nums*unit_price as total from menu group by name having total>30; +------------+-------+ | name | total | +------------+-------+ | apple | 55.00 | | peach | 40.00 | | plum | 66.00 | | strawberry | 40.00 | +------------+-------+ 4 rows in set (0.00 sec) /* having 和 where两者都可以对查询结果进行进一步的过滤，差别有： <1>where语句只能用在分组之前的筛选，having可以用在分组之后的筛选； <2>使用where语句的地方都可以用having进行替换 <3>having中可以用聚合函数，where中就不行。 */
// limit
// 查看第一行 MariaDB [xiong1]> select * from menu limit 1; +----+-------+------+------------+ | id | name | nums | unit_price | +----+-------+------+------------+ | 1 | apple | 5.50 | 10.00 | +----+-------+------+------------+ 1 row in set (0.00 sec) // 跳过前二行 查看往后的第三条 MariaDB [xiong1]> select * from menu limit 2,3; +----+------+-------+------------+ | id | name | nums | unit_price | +----+------+-------+------------+ | 3 | pear | 8.00 | 3.00 | | 4 | pear | 7.00 | 3.10 | | 5 | plum | 33.00 | 2.00 | +----+------+-------+------------+ 3 rows in set (0.00 sec)
16.3、外键约束
# 主表
CREATE TABLE `teachers` (
`TID` smallint(5) AUTO_INCREMENT,
`Name` varchar(100) NOT NULL,
`Age` tinyint(3) unsigned NOT NULL,
`Gender` enum('F','M') DEFAULT NULL,
PRIMARY KEY (`TID`)
) ENGINE=INNODB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
INSERT INTO `teachers` VALUES (1,'Song Jiang',45,'M'),
(2,'Zhang Sanfeng',94,'M'),
(3,'Miejue Shitai',77,'F'),
(4,'Lin Chaoying',93,'F');
CREATE TABLE `students` (
`StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar(50) NOT NULL,
`Age` tinyint(3) unsigned NOT NULL,
`Gender` enum('F','M') NOT NULL,
`TeacherID` smallint(5), -- 外键与主键的类型一定要保持一致
PRIMARY KEY (`StuID`),
FOREIGN KEY (TeacherID) REFERENCES teachers(TID)
) ENGINE=INNODB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;
INSERT INTO `students` VALUES (1,'Shi Zhongyu',22,'M',2,3),
(2,'Shi Potian',22,'M',1,2),
(3,'Xie Yanke',53,'M',2,3),
(4,'Ding Dian',32,'M',4,4),
(5,'Yu Yutong',26,'M',3,1);
// 增加外键
MariaDB [s1]> ALTER TABLE students ADD CONSTRAINT students_ibfk_1 FOREIGN KEY (TeacherID) REFERENCES teachers(TID)
// 删除外键
MariaDB [s1]> alter table students drop FOREIGN KEY students_ibfk_1;
// 增加不存在的ID值里就会直接报错
MariaDB [s1]> INSERT INTO students VALUES ('six',19,'M',5);
MariaDB [s1]> INSERT INTO students VALUES (7,'sx',29,'F',5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f ails (`s1`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`TeacherID`) REFERENCES `teachers` (`TID`))
// 增加存在的就没毛病
MariaDB [s1]> INSERT INTO students VALUES (6,'six',19,'F',2);
Query OK, 1 row affected (0.03 sec)
// 删除外键,无法删除，mysql就能直接删除,可能是有所优化.
MariaDB [s1]> delete from teachers where TID=4;
// 删除外键，不能直接drop,需要先检查类型然后再进行外键删除.
MariaDB [s1]> alter table students drop students_ibfk_1;
ERROR 1091 (42000): Can't DROP 'students_ibfk_1'; check that column/key exists
// 成功案例
MariaDB [s1]> alter table students drop foreign key students_ibfk_1;
Query OK, 0 rows affected (0.05 sec)
-----------------innodb支持的四种方式---------------------------------------
-----cascade方式 在父表上update/delete记录时，同步update/delete掉子表的匹配记录
-----外键的级联删除：如果父表中的记录被删除，则子表中对应的记录自动被删除--------
// 增加级联外键
ALTER TABLE students ADD CONSTRAINT students_ibfk_1 FOREIGN KEY (TeacherID) REFERENCES teachers(TID) ON DELETE CASCADE
// 删除父级表的TID=1时，子表的teacherID=1也都会被级联删除
MariaDB [s1]> delete from teachers where TID=1;
// 删除子表不会影响父表中的记录
-----------------------------------------------------
------set null方式 在父表上update/delete记录时，将子表上匹配记录的列设为null
-- 要注意子表的外键列不能为not null
// 增加外键
MariaDB [s1]> ALTER TABLE students ADD CONSTRAINT abcd FOREIGN KEY (TeacherID) REFERENCES teachers(TID) on DELETE SET NULL ;
// 增加父表中的tid
MariaDB [s1]> delect from teachers where tid=2;
// 子表中的tid就会直接显示为NULL
+-------+-------------+-----+--------+-----------+
| StuID | Name | Age | Gender | TeacherID |
+-------+-------------+-----+--------+-----------+
| 2 | Shi Potian | 22 | M | NULL |
| 3 | Xie Yanke | 53 | M | 3 |
| 6 | six | 19 | F | NULL |
+-------+-------------+-----+--------+-----------+
// 再次重新新加一个TID=2的值，子表中显示的也是为null
MariaDB [s1]> insert into teachers values (2,'find','30','M');
//update 修改为null的值,显示成功，并没有什么用，查询还是为空,
MariaDB [s1]> update students set TeacherID=2 where TeacherID='NULL';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
// 检查为default null 修改, 并不行，删除外键之后就没有defalut null的值了.
MariaDB [s1]> alter table students modify TeacherID smallint(5) NOT NULL;
ERROR 1830 (HY000): Column 'TeacherID' cannot be NOT NULL: needed in a foreign key constraint 's1/abcd' SET NULL
------Restrict方式 :拒绝对父表进行删除更新操作(了解)
------No action方式 在mysql中同Restrict,如果子表中有匹配的记录,则不允许对父表对应候选键
-- 进行update/delete操作（了解）16.4、多表查询练习
sql表 进入控制台直接source hellodb.sql 导入就行 下载地址： 链接：https://pan.baidu.com/s/1FiYwRJLeqI0IrRRgqbwTrQ 密码：yknn
// 这表有点长,给它整成二图，方便查看并操作
// 查看老师对应的学生 内连接inner join MariaDB [hellodb]> select * from students inner join teachers on students.TeacherID=teachers.TID; +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+ | StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender | +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+ | 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M | | 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F | | 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F | +-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+ 3 rows in set (0.00 sec) // 只显示老师学生姓名 MariaDB [hellodb]> select students.Name as 学生姓名,teachers.Name as 老师姓名 from students inner join teachers on students.TeacherID=teachers.TID; +-------------+---------------+ | 学生姓名 | 老师姓名 | +-------------+---------------+ | Yu Yutong | Song Jiang | | Shi Zhongyu | Miejue Shitai | | Ding Dian | Lin Chaoying | +-------------+---------------+ 3 rows in set (0.00 sec) // 显示班级对应的学生人数 group_concat() -- 将重复的显示在一行, group by classid将班级id进行分组查询 MariaDB [hellodb]> select classes.ClassID as 班级id, classes.Class as 班级,group_concat(students.Name) as 学生姓名 from students inner join classes on students.ClassID = classes.ClassID group by classes.ClassID; +--------+----------------+------------------------------------------------------+ | 班级id | 班级 | 学生姓名 | +--------+----------------+------------------------------------------------------+ | 1 | Shaolin Pai | Xiao Qiao,Wen Qingqing,Xu Zhu,Shi Potian | | 2 | Emei Pai | Shi Zhongyu,Tian Boguang,Xie Yanke | | 3 | QingCheng Pai | Yue Lingshan,Yu Yutong,Lu Wushuang,Xi Ren | | 4 | Wudang Pai | Duan Yu,Ma Chao,Ding Dian,Lin Chong | | 5 | Riyue Shenjiao | Shi Qing | | 6 | Lianshan Pai | Xue Baochai,Ren Yingying,Huang Yueying,Yuan Chengzhi | | 7 | Ming Jiao | Diao Chan,Hua Rong,Lin Daiyu | +--------+----------------+------------------------------------------------------+ // 查看每个课程对应的分数 MariaDB [hellodb]> select courses.courseid as 课程编号, courses.course as 课程名称,group_concat(scores.score) as 课程分数 from courses inner join scores on courses.courseid = scores.courseid group by courses.courseid; +----------+----------------+-------------+ | 课程编号 | 课程名称 | 课程分数 | +----------+----------------+-------------+ | 1 | Hamo Gong | 39,96,86 | | 2 | Kuihua Baodian | 89,88,77,47 | | 3 | Jinshe Jianfa | 93 | | 4 | Taiji Quan | 57 | | 5 | Daiyu Zanghua | 97,71 | | 6 | Weituo Zhang | 75,93 | | 7 | Dagou Bangfa | 83,63 | +----------+----------------+-------------+ 7 rows in set (0.00 sec) // 查看每个课程的总分数 MariaDB [hellodb]> select courses.courseid as 课程编号, courses.course as 课程名称,sum(scores.score) as 课程分数 from courses inner join scores on courses.courseid = scores.courseid group by courses.courseid; +----------+----------------+----------+ | 课程编号 | 课程名称 | 课程分数 | +----------+----------------+----------+ | 1 | Hamo Gong | 221 | | 2 | Kuihua Baodian | 301 | | 3 | Jinshe Jianfa | 93 | | 4 | Taiji Quan | 57 | | 5 | Daiyu Zanghua | 168 | | 6 | Weituo Zhang | 168 | | 7 | Dagou Bangfa | 146 | +----------+----------------+----------+ 7 rows in set (0.00 sec) // 接上面,找出分数大于100的班级 having 条件 可以直接使用as name MariaDB [hellodb]> select courses.courseid as 课程编号, courses.course as 课程名称,sum(scores.score) as 课程分数 from courses inner join scores on courses.courseid = scores.courseid group by courses.courseid having 课程分数>100; +----------+----------------+----------+ | 课程编号 | 课程名称 | 课程分数 | +----------+----------------+----------+ | 1 | Hamo Gong | 221 | | 2 | Kuihua Baodian | 301 | | 5 | Daiyu Zanghua | 168 | | 6 | Weituo Zhang | 168 | | 7 | Dagou Bangfa | 146 | +----------+----------------+----------+ 5 rows in set (0.00 sec) // 查找出学生对应的班级以及对应班级的分数 多表left join 连接查询 MariaDB [hellodb]> select courses.courseid,courses.course,students.Name,scores.score from scores left join students on scores.StuID = students.stuid left join courses on scores.courseid = courses.courseid; +----------+----------------+-------------+-------+ | courseid | course | Name | score | +----------+----------------+-------------+-------+ | 2 | Kuihua Baodian | Shi Zhongyu | 77 | | 6 | Weituo Zhang | Shi Zhongyu | 93 | | 2 | Kuihua Baodian | Shi Potian | 47 | | 5 | Daiyu Zanghua | Shi Potian | 97 | | 2 | Kuihua Baodian | Xie Yanke | 88 | | 6 | Weituo Zhang | Xie Yanke | 75 | | 5 | Daiyu Zanghua | Ding Dian | 71 | | 2 | Kuihua Baodian | Ding Dian | 89 | | 1 | Hamo Gong | Yu Yutong | 39 | | 7 | Dagou Bangfa | Yu Yutong | 63 | | 1 | Hamo Gong | Shi Qing | 96 | | 1 | Hamo Gong | Xi Ren | 86 | | 7 | Dagou Bangfa | Xi Ren | 83 | | 4 | Taiji Quan | Lin Daiyu | 57 | | 3 | Jinshe Jianfa | Lin Daiyu | 93 | +----------+----------------+-------------+-------+ 15 rows in set (0.00 sec) // 查看学生对应班级,以及分数 select group_concat(courses.course) as 班级名称,students.Name as 学生姓名,group_concat(scores.score) as 分数 from scores left join students on scores.StuID = students.stuid left join courses on scores.courseid= courses.courseid group by Name; +------------------------------+-------------+-------+ | 班级名称 | 学生姓名 | 分数 | +------------------------------+-------------+-------+ | Kuihua Baodian,Daiyu Zanghua | Ding Dian | 89,71 | | Jinshe Jianfa,Taiji Quan | Lin Daiyu | 93,57 | | Daiyu Zanghua,Kuihua Baodian | Shi Potian | 97,47 | | Hamo Gong | Shi Qing | 96 | | Weituo Zhang,Kuihua Baodian | Shi Zhongyu | 93,77 | | Dagou Bangfa,Hamo Gong | Xi Ren | 83,86 | | Weituo Zhang,Kuihua Baodian | Xie Yanke | 75,88 | | Dagou Bangfa,Hamo Gong | Yu Yutong | 63,39 | +------------------------------+-------------+-------+ 8 rows in set (0.00 sec) // 接上,查看总分 select group_concat(courses.course) as 班级名称,students.Name as 学生姓名,sum(scores.score) as 分数 from scores left join students on scores.StuID = students.stuid left join courses on scores.courseid= courses.courseid group by Name; +------------------------------+-------------+------+ | 班级名称 | 学生姓名 | 分数 | +------------------------------+-------------+------+ | Kuihua Baodian,Daiyu Zanghua | Ding Dian | 160 | | Jinshe Jianfa,Taiji Quan | Lin Daiyu | 150 | | Daiyu Zanghua,Kuihua Baodian | Shi Potian | 144 | | Hamo Gong | Shi Qing | 96 | | Weituo Zhang,Kuihua Baodian | Shi Zhongyu | 170 | | Dagou Bangfa,Hamo Gong | Xi Ren | 169 | | Weituo Zhang,Kuihua Baodian | Xie Yanke | 163 | | Dagou Bangfa,Hamo Gong | Yu Yutong | 102 | +------------------------------+-------------+------+ // 接上,查看总分大于120分以上的 select group_concat(courses.course) as 班级名称,students.Name as 学生姓名,sum(scores.score) as 分数 from scores left join students on scores.StuID = students.stuid left join courses on scores.courseid= courses.courseid group by Name HAVING 分数>120; +------------------------------+-------------+------+ | 班级名称 | 学生姓名 | 分数 | +------------------------------+-------------+------+ | Kuihua Baodian,Daiyu Zanghua | Ding Dian | 160 | | Jinshe Jianfa,Taiji Quan | Lin Daiyu | 150 | | Daiyu Zanghua,Kuihua Baodian | Shi Potian | 144 | | Weituo Zhang,Kuihua Baodian | Shi Zhongyu | 170 | | Dagou Bangfa,Hamo Gong | Xi Ren | 169 | | Weituo Zhang,Kuihua Baodian | Xie Yanke | 163 | +------------------------------+-------------+------+ 6 rows in set (0.00 sec) // 右连接,以右边为主 select students.name as 学生姓名,teachers.name as 老师姓名 from teachers RIGHT join students on teachers.tid = students.teacherid; +---------------+---------------+ | 学生姓名 | 老师姓名 | +---------------+---------------+ | Shi Zhongyu | Miejue Shitai | | Shi Potian | NULL | | Xie Yanke | NULL | | Ding Dian | Lin Chaoying | | Yu Yutong | Song Jiang | | Shi Qing | NULL | | Xi Ren | NULL | | Lin Daiyu | NULL | | Ren Yingying | NULL | | Yue Lingshan | NULL | | Yuan Chengzhi | NULL | | Wen Qingqing | NULL | | Tian Boguang | NULL | ...... // 如有更多问题,请留言
