15-python开发之Mysql（断电课堂笔记）
今日概要:
1、初识mysql
2、MySQL的增删改查
3、主键、外键
4、组合和分组
一、数据库的由来
1、什么是数据库？
数据的仓库,在ATM,购物车中存储数据为目录，称为数据库
1.数据以表格的形式出现
2.每行为各种记录名称
3.每列为记录名称所对应的数据域
4.许多的行和列组成一张表单
5.若干的表单组成database
2、数据库主要有哪些功能？
a、将数据保存到文件或者内存
b、接收特定的命令,然后对文件进行相应的操作
PS:如果有了以上软件，无须自己再去创建文件和文件夹，而是直接传递命令给上述软件，让其来进行文件操作，他们统称为数据库管理系统（DBMS，Database Management System）
3、什么是sql?
上述提到MySQL等软件可以接受命令，并做出相应的操作，由于命令中可以包含删除文件、获取文件内容等众多操作，对于编写的命令就是是SQL语句
下图就是一个数据库:
二、RDBMS术语
在我们开始学习MySQL 数据库前，让我们先了解下RDBMS的一些术语：
-
- 数据库: 数据库是一些关联表的集合。.
- 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
- 列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
- 行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
- 冗余：存储两倍数据，冗余可以使系统速度更快。(表的规范化程度越高，表与表之间的关系就越多；查询时可能经常需要在多个表之间进行连接查询；而进行连接操作会降低查询速度。例如，学生的信息存储在student表中，院系信息存储在department表中。通过student表中的dept_id字段与department表建立关联关系。如果要查询一个学生所在院系的名称，必须从student表中查找学生所在院系的编号（dept_id），然后根据这个编号去department查找系的名称。如果经常需要进行这个操作时，连接查询会浪费很多的时间。因此可以在student表中增加一个冗余字段dept_name，该字段用来存储学生所在院系的名称。这样就不用每次都进行连接操作了。)
- 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
- 外键：外键用于关联两个表。
- 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
- 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
- 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。
Mysql是最流行的关系型数据库管理系统，在WEB应用方面MySQL是最好的RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。由瑞典MySQL AB公司开发，目前属于Oracle公司。MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
-
- Mysql是开源的，所以你不需要支付额外的费用。
- Mysql支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
- MySQL使用标准的SQL数据语言形式。
- Mysql可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。
- Mysql对PHP有很好的支持，PHP是目前最流行的Web开发语言。
- MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。
- Mysql是可以定制的，采用了GPL协议，你可以修改源码来开发自己的Mysql系统。
三、数据库的基本操作
1、显示数据库
show databases;
默认数据库:
mysql - 用户权限相关的数据
test - 用于用户测试数据库
information_schema - MYSQL本身架构相关数据
2、创建数据库
#utf-8 CREATE DATABASE 数据库名称 DEFAULT CHARSET utf8 COLLATE utf8_general_ci; #gbk CREATE DATABASE 数据库名称 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
3、使用数据库
use database;
显示当前数据库所有的表SHOW TABLES;
4、用户管理
创建用户
create user '用户名'@'IP地址' identified by '密码';
删除用户
drop user '用户名'@'IP地址';
修改用户
rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';;
修改密码
set password for '用户名'@'IP地址' = Password('新密码')
PS：用户权限相关数据保存在mysql数据库的user表中，所以也可以直接对其进行操作（不建议）
5、授权管理
show grants for '用户'@'IP地址' -- 查看权限 grant 权限 on 数据库.表 to '用户'@'IP地址' -- 授权 revoke 权限 on 数据库.表 from '用户'@'IP地址' -- 取消权限 flush privileges; -- 让授权立即生效
权限授权的参数
all privileges 除grant外的所有权限
select 仅查权限
select,insert 查和插入权限
...
usage 无访问权限
alter 使用alter table
alter routine 使用alter procedure和drop procedure
create 使用create table
create routine 使用create procedure
create temporary tables 使用create temporary tables
create user 使用create user、drop user、rename user和revoke all privileges
create view 使用create view
delete 使用delete
drop 使用drop table
execute 使用call和存储过程
file 使用select into outfile 和 load data infile
grant option 使用grant 和 revoke
index 使用index
insert 使用insert
lock tables 使用lock table
process 使用show full processlist
select 使用select
show databases 使用show databases
show view 使用show view
update 使用update
reload 使用flush
shutdown 使用mysqladmin shutdown(关闭MySQL)
super 使用change master、kill、logs、purge、master和set global。还允许mysqladmin调试登陆
replication client 服务器位置的访问
replication slave 由复制从属使用
对于数据库的参数
对于目标数据库以及内部其他：
数据库名.* 数据库中的所有
数据库名.表 指定数据库中的某张表
数据库名.存储过程 指定数据库中的存储过程
*.* 所有数据库
用户的参数
用户名@IP地址 用户只能在改IP下才能访问
用户名@192.168.1.% 用户只能在改IP段下才能访问(通配符%表示任意)
用户名@% 用户可以再任意IP下访问(默认IP地址为%)
例子
grant all privileges on db1.tb1 TO '用户名'@'IP' grant select on db1.* TO '用户名'@'IP' grant select,insert on *.* TO '用户名'@'IP' revoke select on db1.tb1 from '用户名'@'IP'
mysql忘记密码操作
# 启动免授权服务端
mysqld --skip-grant-tables
# 客户端
mysql -u root -p
# 修改用户名密码
update mysql.user set authentication_string=password('666') where user='root';
flush privileges;
四、数据表的基本操作
1、创建表
create table 表名(
列名 类型 是否可以为空，
列名 类型 是否可以为空
)ENGINE=InnoDB DEFAULT CHARSET=utf8
是否为空
是否可空，null表示空，非字符串
not null - 不可空
null - 可空
默认值
默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
create table tb1(
nid int not null defalut 2,
num int not null
)
设置自增列
自增，如果为某列设置自增列，插入数据时无需设置此列，默认将自增（表中只能有一个自增列） create table tb1( nid int not null auto_increment primary key, num int null ) 或 create table tb1( nid int not null auto_increment, num int null, index(nid) ) 注意：1、对于自增列，必须是索引（含主键）。 2、对于自增可以设置步长和起始值 show session variables like 'auto_inc%'; set session auto_increment_increment=2; set session auto_increment_offset=10; shwo global variables like 'auto_inc%'; set global auto_increment_increment=2; set global auto_increment_offset=10;
主键
主键，一种特殊的唯一索引，不允许有空值，如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一。
create table tb1(
nid int not null auto_increment primary key,
num int null
)
或
create table tb1(
nid int not null,
num int not null,
primary key(nid,num)
)
外键
外键，一个特殊的索引，只能是指定内容
creat table color(
nid int not null primary key,
name char(16) not null
)
create table fruit(
nid int not null primary key,
smt char(32) null ,
color_id int not null,constraint fk_cc foreign key (color_id) references color(nid)
)
2、删除表
drop table 表名
3、清空表
delete from 表名 truncate table 表名
delete和truncate的区别：
delete 删除后，再次插入，自增id会从删除前的自增id继续增加
truncate清空后，自增id会从初始值增加
4、修改表
添加列：alter table 表名 add 列名 类型
删除列：alter table 表名 drop column 列名
修改列：
alter table 表名 modify column 列名 类型; -- 修改类型
alter table 表名 change 原列名 新列名 类型; -- 修改列名和类型
添加主键：
alter table 表名 add primary key(列名);
删除主键：
alter table 表名 drop primary key;
alter table 表名 modify 列名 int, drop primary key;
添加外键：alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);
删除外键：alter table 表名 drop foreign key 外键名称
修改默认值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
删除默认值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
5、mysql的数据类型
mysql的数据类型大致分为：数值、时间、字符串
数据类型概览
bit[(M)]
二进制位（101001），m表示二进制位的长度（1-64），默认m＝1
tinyint[(m)] [unsigned] [zerofill]
小整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-128 ～ 127.
无符号：
～ 255
特别的： MySQL中无布尔值，使用tinyint(1)构造。
int[(m)][unsigned][zerofill]
整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-2147483648 ～ 2147483647
无符号：
～ 4294967295
特别的：整数类型中的m仅用于显示，对存储范围无限制。例如： int(5),当插入数据2时，select 时数据显示为： 00002
bigint[(m)][unsigned][zerofill]
大整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-9223372036854775808 ～ 9223372036854775807
无符号：
～ 18446744073709551615
decimal[(m[,d])] [unsigned] [zerofill]
准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。
特别的：对于精确数值计算时需要用此类型
decaimal能够存储精确值的原因在于其内部按照字符串存储。
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
无符号：
-3.402823466E+38 to -1.175494351E-38,
1.175494351E-38 to 3.402823466E+38
有符号：
1.175494351E-38 to 3.402823466E+38
**** 数值越大，越不准确 ****
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
无符号：
-1.7976931348623157E+308 to -2.2250738585072014E-308
2.2250738585072014E-308 to 1.7976931348623157E+308
有符号：
2.2250738585072014E-308 to 1.7976931348623157E+308
**** 数值越大，越不准确 ****
char (m)
char数据类型用于表示固定长度的字符串，可以包含最多达255个字符。其中m代表字符串的长度。
PS: 即使数据小于m长度，也会占用m长度
varchar(m)
varchars数据类型用于变长的字符串，可以包含最多达255个字符。其中m代表该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中。
注：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡
text
text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。
mediumtext
A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.
longtext
A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters.
enum
枚举类型，
An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)
示例：
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');
set
集合类型
A SET column can have a maximum of 64 distinct members.
示例：
CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
DATE
YYYY-MM-DD（1000-01-01/9999-12-31）
TIME
HH:MM:SS（'-838:59:59'/'838:59:59'）
YEAR
YYYY（1901/2155）
DATETIME
YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y）
TIMESTAMP
YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）
6、用html展示单表，一对多，多对多
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title></title>
</head>
<body>
<h1>单表</h1>
<p>
主机名: <input type="text" />
</p>
<p>
端口: <input type="text" />
</p>
<h1>一对多</h1>
<p>
主机名: <input type="text" />
</p>
<p>
端口: <input type="text" />
</p>
<p>
<select>
<option>刘一</option>
<option>刘二</option>
<option>刘上</option>
</select>
</p>
<h1>多对多</h1>
<p>
主机名: <input type="text" />
</p>
<p>
端口: <input type="text" />
</p>
<p>
<select multiple>
<option>刘一</option>
<option>刘二</option>
<option>刘上</option>
</select>
</p>
</body>
</html>
单表:
一对多:
多对多：
五、表内容操作
增(insert)
insert into 表 (列名,列名...) values (值,值,值...) insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...) insert into 表 (列名,列名...) select (列名,列名...) from 表
删(delete)
delete from 表 delete from 表 where id＝1 and name＝'alex'
改(update)
update 表 set name ＝ 'alex' where id>1
查(select)
select * from 表 select * from 表 where id > 1 select nid,name,gender as gg from 表 where id > 1
other
a、条件 select * from 表 where id > 1 and name != 'alex' and num = 12; select * from 表 where id between 5 and 16; select * from 表 where id in (11,22,33) select * from 表 where id not in (11,22,33) select * from 表 where id in (select nid from 表) b、通配符 select * from 表 where name like 'ale%' - ale开头的所有（多个字符串） select * from 表 where name like 'ale_' - ale开头的所有（一个字符） c、限制 select * from 表 limit 5; - 前5行 select * from 表 limit 4,5; - 从第4行开始的5行 select * from 表 limit 5 offset 4 - 从第4行开始的5行 d、排序 select * from 表 order by 列 asc - 根据 “列” 从小到大排列 select * from 表 order by 列 desc - 根据 “列” 从大到小排列 select * from 表 order by 列1 desc,列2 asc - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序 e、分组 select num from 表 group by num select num,nid from 表 group by num,nid select num,nid from 表 where nid > 10 group by num,nid order nid desc select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid select num from 表 group by num having max(id) > 10 特别的：group by 必须在where之后，order by之前 f、连表 无对应关系则不显示 select A.num, A.name, B.name from A,B Where A.nid = B.nid 无对应关系则不显示 select A.num, A.name, B.name from A inner join B on A.nid = B.nid A表所有显示，如果B中无对应关系，则值为null select A.num, A.name, B.name from A left join B on A.nid = B.nid B表所有显示，如果A中无对应关系，则值为null select A.num, A.name, B.name from A right join B on A.nid = B.nid g、组合 组合，自动处理重合 select nickname from A union select name from B 组合，不处理重合 select nickname from A union all select name from B
六、事务
MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
-
- 在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
- 事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行
- 事务用来管理insert,update,delete语句
一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）
-
- 1、事务的原子性：一组事务，要么成功；要么撤回。
- 2、稳定性 ： 有非法数据（外键约束之类），事务撤回。
- 3、隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
- 4、可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。
Mysql控制台操作事务：
mysql> begin； ＃开始一个事务 mysql> insert into a (a) values(555); mysql>rollback; 回滚 ， 这样数据是不会写入的
如果上述操作正常，就可以提交commit关闭事务
七、pymysql使用
1、安装，py3不支持MysqlDB,pymysql和mysqldb的用法一样
pip3 install pymysql
2、用法(增删改都需要commit提交事务)
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
# 创建连接
conn = pymysql.connect(host='10.18.210.139', port=3306, user='root', passwd='12345', db='oldboy')
# 创建游标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) #输出变成字典格式
#cursor = conn.cursor()
# 执行SQL，并返回收影响行数
effect_row = cursor.execute("select * from t1",)
# 不能用先拼接字符串在传入,那样容易sql注入, 比如 root or 1=1 -- 这样传入就会产生sql注入
# 执行SQL，并返回受影响行数
# effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))
# 执行SQL，并返回受影响行数
# effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
# 提交，不然无法保存新建或者修改的数据、删除数据--提交事务
conn.commit()
#拿出是元组/字典类型结果
res = cursor.fetchone() #得到一个结果，在次fetchone会在上次的基础上继续取数据
# res = cursor.fetchall()
# res = cursor.fetchmany(3) #一次取3个
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
print (res)
#最新的最后一条自增id,拿的是自己的
new_id = cursor.lastrowid
cursor.scroll(1,mode='relative') #相对当前位置移动
cursor.scroll(1,mode='absolute') #相对绝对位置移动，第一个参数为1就会从1开始开始移动
3、练习：用户登录重写
#!/usr/bin/python
# -*- coding:utf-8 -*-
import pymysql
user = input('请输入用户名:')
pwd = input('请输入密码:')
# 获取数据
conn = pymysql.Connect(host='192.168.12.89',port=3306,user='root',password="123",database="s17day11db",charset='utf8')
cursor = conn.cursor()
sql = 'select * from userinfo where username="%s" and password="%s" ' %(user,pwd,)
# user = alex" --
# pwd= asdf
'select * from userinfo where username="alex" -- " and password="sdfsdf"'
# user = asdfasdf" or 1=1 --
# pwd= asdf
'select * from userinfo where username="asdfasdf" or 1=1 -- " and password="asdfasdf"'
v = cursor.execute(sql)
result = cursor.fetchone()
cursor.close()
conn.close()
print(result)
4、练习
#!/usr/bin/python
# -*- coding:utf-8 -*-
import pymysql
user = input('请输入用户名:')
pwd = input('请输入密码:')
# 获取数据
conn = pymysql.Connect(host='192.168.12.89',port=3306,user='root',password="123",database="s17day11db",charset='utf8')
cursor = conn.cursor()
v = cursor.execute('select * from userinfo where username=%s and password=%s',[user,pwd])
result = cursor.fetchone()
cursor.close()
conn.close()
print(result)
5、新建数据
#!/usr/bin/python
# -*- coding:utf-8 -*-
import pymysql
# 获取数据
conn = pymysql.Connect(host='192.168.12.89',port=3306,user='root',password="123",database="s17day11db",charset='utf8')
cursor = conn.cursor()
cursor.execute('insert into class(caption) values(%s)',['新班级'])
conn.commit()
new_class_id = cursor.lastrowid # 获取新增数据自增ID
cursor.execute('insert into student(sname,gender,class_id) values(%s,%s,%s)',['李杰','男',new_class_id])
conn.commit()
cursor.close()
conn.close()
mysql练习题
初始化表结构和数据
/* Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 50624 Source Host : localhost Source Database : sqlexam Target Server Type : MySQL Target Server Version : 50624 File Encoding : utf-8 Date: 10/21/2016 06:46:46 AM */ SET NAMES utf8; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for `class` -- ---------------------------- DROP TABLE IF EXISTS `class`; CREATE TABLE `class` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `caption` varchar(32) NOT NULL, PRIMARY KEY (`cid`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of `class` -- ---------------------------- BEGIN; INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班'); COMMIT; -- ---------------------------- -- Table structure for `course` -- ---------------------------- DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `cname` varchar(32) NOT NULL, `teacher_id` int(11) NOT NULL, PRIMARY KEY (`cid`), KEY `fk_course_teacher` (`teacher_id`), CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of `course` -- ---------------------------- BEGIN; INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2'); COMMIT; -- ---------------------------- -- Table structure for `score` -- ---------------------------- DROP TABLE IF EXISTS `score`; CREATE TABLE `score` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `student_id` int(11) NOT NULL, `course_id` int(11) NOT NULL, `num` int(11) NOT NULL, PRIMARY KEY (`sid`), KEY `fk_score_student` (`student_id`), KEY `fk_score_course` (`course_id`), CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`), CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`) ) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of `score` -- ---------------------------- BEGIN; INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87'); COMMIT; -- ---------------------------- -- Table structure for `student` -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `gender` char(1) NOT NULL, `class_id` int(11) NOT NULL, `sname` varchar(32) NOT NULL, PRIMARY KEY (`sid`), KEY `fk_class` (`class_id`), CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`) ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of `student` -- ---------------------------- BEGIN; INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四'); COMMIT; -- ---------------------------- -- Table structure for `teacher` -- ---------------------------- DROP TABLE IF EXISTS `teacher`; CREATE TABLE `teacher` ( `tid` int(11) NOT NULL AUTO_INCREMENT, `tname` varchar(32) NOT NULL, PRIMARY KEY (`tid`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of `teacher` -- ---------------------------- BEGIN; INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师'); COMMIT; SET FOREIGN_KEY_CHECKS = 1; 表结构和数据 初始化表结构和数据
练习题与参考答案
2、查询“生物”课程比“物理”课程成绩高的所有学生的学号；
思路:1、查询生物的所有学生的成绩学号
2、查询物理的所有学生的成绩学号
3、将两个结果聚合到一起，在做一次判断
select A.student_id from
(select score.student_id,score.num,course.cname from score LEFT JOIN course on score.course_id = course.cid where cname = '生物') as A
LEFT JOIN
(select score.student_id,score.num,course.cname from score LEFT JOIN course on score.course_id = course.cid where cname = '物理') as B
on
A.student_id = B.student_id where A.num > B.num
3、查询平均成绩大于60分的同学的学号和平均成绩；
1、根据学生id进行分组,使用avg获取平均值,通过having进行平均值判断
select student.sname,A.num from (select student_id,avg(num) as num from score group by student_id having avg(num) > 60 ) as A
LEFT JOIN
student on A.student_id = student.sid
4、查询所有同学的学号、姓名、选课数、总成绩；
1、通过score表查看所有人的选课数和总成绩
2、在和student表做一次join聚合
select student.sid,student.sname,A.times,A.num
from
(select student_id,count(student_id) as times,sum(num)as num from score group by student_id ) as A
LEFT JOIN
student on A.student_id = student.sid
1、先聚合在查询
另外一种方法
select score.student_id,sum(score.num),count(score.student_id),student.sname
from
score left join student on score.student_id = student.sid
group by score.student_id
5、查询姓“李”的老师的个数；
select count(tid) from teacher where tname like '李%'
select count(1) from (select tid from teacher where tname like '李%') as B
6、查询没学过“叶平”老师课的同学的学号、姓名；
1、先查老师的id 在查非李平老师id的学生,在做一次去重
select sid,sname from student where sid not in (
(select distinct student_id from score LEFT JOIN course on score.course_id = course.cid
WHERE teacher_id in (select tid from teacher where tname = '李平老师')))
另外一种思路：
先查到“李平老师”老师教的所有课ID
获取选过课的所有学生ID
学生表中筛选
select * from student where sid not in (
select DISTINCT student_id from score where score.course_id in (
select cid from course left join teacher on course.teacher_id = teacher.tid where tname = '李平老师'
)
)
7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
1、先查到学生学001又学002课程的人,根据学生分组课程数量等于2
2、在和学生表结合取出学生id和学生名字
select student_id,student.sname from
(select student_id,count(student_id) as total from score
WHERE course_id in (1,2) GROUP BY student_id HAVING total = 2) as A
LEFT JOIN student on student.sid = A.student_id
另外一种写法:
先查到既选择001又选择002课程的所有同学
根据学生进行分组，如果学生数量等于2表示，两门均已选择
select student_id,sname from
(select student_id,course_id from score where course_id = 1 or course_id = 2) as B
left join student on B.student_id = student.sid group by student_id HAVING count(student_id) > 1
8、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
select sid,sname from student where sid in (
(select distinct student_id from score LEFT JOIN course on score.course_id = course.cid
WHERE teacher_id in (select tid from teacher where tname = '李平老师')))
9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
select A.student_id from
(select score.student_id,score.num,student.sname from score LEFT JOIN student on score.student_id = student.sid where course_id = 1) as A
LEFT JOIN
(select score.student_id,score.num,student.sname from score LEFT JOIN student on score.student_id = student.sid where course_id = 2) as B
on
A.student_id = B.student_id where A.num > B.num
10、查询有课程成绩小于60分的同学的学号、姓名；
1、先查询学生小于60分的人员 在和学生表进行关联
select student.sid,student.sname from (select student_id from score where num < 60 group by student_id)
as A left join student on A.student_id = student.sid
2、第二种方法
select sid,sname from student where sid in (
select distinct student_id from score where num < 60
)
11、查询没有学全所有课的同学的学号、姓名；
1、先在score里查询没有学全的同学，在student表里进行一次查询即可
select sid,sname from student where sid in (select student_id from score GROUP BY student_id having count(student_id) < (select count(1) from course) )
另外一种方法
select student_id,sname
from score left join student on score.student_id = student.sid
group by student_id HAVING count(course_id) = (select count(1) from course)
12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；
获取 001 同学选择的所有课程
获取课程在其中的所有人以及所有课程
根据学生筛选，获取所有学生信息
再与学生表连接，获取姓名
select sid,sname from student where sid in
(select student_id from score where student_id != 1 and course_id in (select course_id from score where student_id = 1) group by student_id)
select student_id,sname, count(course_id)
from score left join student on score.student_id = student.sid
where student_id != 1 and course_id in (select course_id from score where student_id = 1) group by student_id
13、查询至少学过学号为“001”同学所选课程中任意一门课的其他同学学号和姓名；
先找到和001的学过的所有人
然后个数 ＝ 001所有学科 ＝＝》 其他人可能选择的更多
select sid,sname from student where sid in
(select student_id from score where student_id != 1
and course_id in (select course_id from score where student_id = 1) group by student_id having count(course_id) = (select count(course_id) from score WHERE student_id = 1))
14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；
个数相同
002学过的也学过
select student_id,sname from score left join student on score.student_id = student.sid where student_id in (
select student_id from score where student_id != 3 group by student_id HAVING count(course_id) = (select count(1) from score where student_id = 3)
) and course_id in (select course_id from score where student_id = 3) group by student_id HAVING count(course_id) = (select count(1) from score where student_id = 3)
15、删除学习“叶平”老师课的SC表记录；
delete from score where course_id in (select cid from course where teacher_id in (select tid from teacher where teacher.tname = '李平老师') )
另外一种方法
delete from score where course_id in (
select cid from course left join teacher on course.teacher_id = teacher.tid where teacher.tname = '李平老师'
)
16、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；
思路:
由于insert 支持
inset into tb1(xx,xx) select x1,x2 from tb2;
所有，获取所有没上过002课的所有人，获取002的平均成绩
insert into score(student_id, course_id, num) select sid,2,(select avg(num) from score where course_id = 2)
from student where sid not in (
select student_id from score where course_id = 2
)
53 2 2 65
54 13 2 65
55 14 2 65
56 15 2 65
57 16 2 65
17、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；
select sc.student_id,
(select num from score left join course on score.course_id = course.cid where course.cname = "生物" and score.student_id=sc.student_id) as sy,
(select num from score left join course on score.course_id = course.cid where course.cname = "物理" and score.student_id=sc.student_id) as wl,
(select num from score left join course on score.course_id = course.cid where course.cname = "体育" and score.student_id=sc.student_id) as ty,
count(sc.course_id),
avg(sc.num)
from score as sc
group by student_id asc
18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
select course.cname,A.max_num,A.min_num from (select course_id, max(num) as max_num, min(num) as min_num from score group by course_id) as A left join course on A.course_id = course.cid
select course_id, max(num) as max_num, min(num) as min_num from score group by course_id;
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
思路 case when .. then
select course_id, avg(num) as avgnum,sum(case when score.num > 60 then 1 else 0 END)/count(1)*100 as percent from score group by course_id order by avgnum asc,percent desc;
20、课程平均分从高到低显示（显示任课老师）
1、先查询平均分,在进行连表,一次排序
select teacher.tname,A.avgnum from (select course_id,avg(num) as avgnum from score group by course_id) as A LEFT JOIN course on A.course_id= course.cid
LEFT JOIN teacher on course.teacher_id = teacher.tid ORDER BY A.avgnum desc
另外一种方法
select avg(if(isnull(score.num),0,score.num)),teacher.tname from course
left join score on course.cid = score.course_id
left join teacher on course.teacher_id = teacher.tid
group by score.course_id
21、查询各科成绩前三名的记录:(不考虑成绩并列情况) --不清楚怎么实现的
select score.sid,score.course_id,score.num,T.first_num,T.second_num from score left join
(
select
sid,
(select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 0,1) as first_num,
(select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 3,1) as second_num
from
score as s1
) as T
on score.sid =T.sid
where score.num <= T.first_num and score.num >= T.second_num
22、查询每门课程被选修的学生数；
select course_id,count(student_id) from score GROUP BY course_id
select course_id, count(1) from score group by course_id;
23、查询出只选修了一门课程的全部学生的学号和姓名；
select student.sname,student.sid from (select student_id from score group by student_id HAVING count(course_id) = 1 ) as A
LEFT JOIN student on A.student_id = student.sid
24、查询男生、女生的人数；
select * from
(select count(1) as man from student where gender='男') as A ,
(select count(1) as feman from student where gender='女') as B
25、查询姓“张”的学生名单；
select * from student where sname like '张%'
26、查询同名同姓学生名单，并统计同名人数；
select sname,count(1) as count from student group by sname;
27、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
select * from (select course_id,avg(num) as num from score group by course_id ) as A ORDER BY A.num asc
另外一种写法
select course_id,avg(if(isnull(num), 0 ,num)) as avg from score group by course_id order by avg asc,course_id desc;
28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩；
select student_id,sname,avg(num) from score left JOIN student on score.student_id = student.sid group by student_id HAVING avg(num) > 85
29、查询课程名称为“数学”，且分数低于60的学生姓名和分数；
select student_id,sname,num from score LEFT JOIN student on score.student_id = student.sid
where course_id in (select cid from course where cname = '生物') and num < 60
select student.sname,score.num from score
left join course on score.course_id = course.cid
left join student on score.student_id = student.sid
where score.num < 60 and course.cname = '生物'
30、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；
select student_id,sname from score LEFT JOIN student on student.sid = score.student_id where course_id = 3 and num > 80
31、求选了课程的学生人数
select count(student_id) from (select student_id from score where course_id in (1,2,3,4) GROUP BY student_id) as A
32、查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；
select sname,num from score
left join student on score.student_id = student.sid
where score.course_id in (select course.cid from course left join teacher on course.teacher_id = teacher.tid where tname='张磊老师') order by num desc limit 1;
33、查询各个课程及相应的选修人数；
select course.cname,count(1) from score
left join course on score.course_id = course.cid
group by course_id;
34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
select DISTINCT s1.course_id,s2.course_id,s1.num,s2.num from score as s1, score as s2 where s1.num = s2.num and s1.course_id != s2.course_id;
35、查询每门课程成绩最好的前两名；
select score.sid,score.course_id,score.num,T.first_num,T.second_num from score left join
(
select
sid,
(select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 0,1) as first_num,
(select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 1,1) as second_num
from
score as s1
) as T
on score.sid =T.sid
where score.num <= T.first_num and score.num >= T.second_num
36、检索至少选修两门课程的学生学号；
select student_id,sname from score left join student on student.sid = score.student_id GROUP BY student_id HAVING count(student_id) > 1
37、查询全部学生都选修的课程的课程号和课程名；
select course_id,count(1) from score group by course_id having count(1) = (select count(1) from student);
38、查询没学过“叶平”老师讲授的任一门课程的学生姓名；
select student_id,student.sname from score
left join student on score.student_id = student.sid
where score.course_id not in (
select cid from course left join teacher on course.teacher_id = teacher.tid where tname = '李平老师'
)
group by student_id
39、查询两门以上不及格课程的同学的学号及其平均成绩；
select student_id,count(1) from score where num < 60 group by student_id having count(1) >=2
40、检索“004”课程分数小于60，按分数降序排列的同学学号；
select * from score where course_id = 2 and num < 60 ORDER BY num desc
41、删除“002”同学的“001”课程的成绩；
DELETE from score where course_id =1 and student_id = 001
参考文章
做有积累的事~~
浙公网安备 33010602011771号