课后大总结
Day1
一、数据库命令总结
1.连接数据库
连接数据库进入mysql安装目录打开bin文件夹，输入cmd(此命令后无分号)
mysql.exe -u root -p
password命令后输入密码:root
设置密码
set password=password("root123");
查看所有数据库
show databases;
创建数据库
create database day1db DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
删除数据库
drop database day1db;
进入数据库
use day1db;
查看数据库中的表
show tables;
退出数据库
exit;2.创建表
(1)创建表的基本语法格式(查阅豆包得出)：
create table class(
cid int not null auto_increment primary key,
caption varchar(20) not null
)default charset=utf8;create table student(
sid int not null auto_increment primary key,
sname varchar(20) not null,
gender char(1) not null,
class_id int not null,
constraint fk_student_class foreign key (class_id) references class(cid)
)default charset=utf8;1.语法
CREATE TABLE [IF NOT EXISTS] table_name (
column1 datatype constraint,
column2 datatype constraint,
...
columnN datatype constraint
);
2.代码解读
CREATE TABLE：这是创建表的关键字命令。
[IF NOT EXISTS]：这是一个可选的部分。如果加上这个选项，当要创建的表名不存在时才会创建表。如果表已经存在，就不会执行创建操作，这样可以避免因为表名重复而导致的错误。
table_name：这是要创建的表的名称，表名在数据库中应该是唯一的，并且要符合 MySQL 的命名规则（例如，表名通常由字母、数字和下划线组成，不能以数字开头等）。
column1, column2,..., columnN：这些是表中的列名。每一列代表表中的一个数据字段，例如在一个学生表中，可能有student_id（学生编号）、student_name（学生姓名）等列。
datatype：这是列的数据类型。MySQL 支持多种数据类型，如INT（整数型）、VARCHAR（可变长字符串）、DATE（日期型）等。不同的数据类型用于存储不同种类的数据，并且有不同的存储要求和限制。
constraint：这是列的约束条件。约束用于定义列中数据的规则，常见的约束有PRIMARY KEY（主键约束，用于唯一标识表中的每一行）、NOT NULL（非空约束，规定列中的数据不能为空）、UNIQUE（唯一约束，保证列中的数据是唯一的）等。(2)使用create table 表名 as创建表
1.语法
create table 表名 as
select * from 已有表名;
2.示例
create table sc as
select * from score;(3)修改列名
在 MySQL 中，可以使用 ALTER TABLE 语句来修改表中列的名称。以下是具体的操作步骤和示例代码：
1.基本语法
ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name column_definition;
ALTER TABLE：这是修改表结构的关键字。
table_name：要修改的表的名称。
CHANGE COLUMN：表示要修改表中的列。
old_column_name：需要修改的列的当前名称。
new_column_name：要将列修改为的新名称。
column_definition：列的定义，包括数据类型、长度、约束等，需要和原列保持一致（除非你也想修改列的属性）。
2.示例
假设我们有一个表 students，其中有一个列 student_name，现在想将其更名为 name：
ALTER TABLE students
CHANGE COLUMN student_name name VARCHAR(50);
在这个示例中：
students 是表名。
student_name 是要修改的旧列名。
name 是新列名。
VARCHAR(50) 是列的数据类型和长度，这里假设原列是 VARCHAR(50)，如果原列的数据类型或长度不同，你需要使用原列的数据类型和长度。
3.注意事项
数据类型和长度：在使用 CHANGE COLUMN 时，需要指定列的完整定义，包括数据类型、长度等。如果只想修改列名而不改变其属性，可以直接复制原列的定义。如果不确定原列的定义，可以使用 SHOW COLUMNS FROM table_name 语句查看。
表的锁定：修改表结构时，MySQL 会锁定表，这可能会影响其他对该表的操作。在生产环境中，要选择合适的时间进行修改，避免对业务造成影响。
备份数据：在修改表结构之前，最好对表进行备份，以防意外情况发生。(4)创建临时表
1.使用 CREATE TEMPORARY TABLE 语句
CREATE TEMPORARY TABLE temp_table_name (
column1 datatype1,
column2 datatype2,
-- 可以添加更多列
PRIMARY KEY (column1) -- 可以添加主键或其他约束
);
2.使用 CREATE TEMPORARY TABLE... AS SELECT 语句
(1)语法
create temporary table 临时表名 as
select *
from 原表名
where 条件;
select * from temp_high_value_orders;
(2)示例
create temporary table temp_high_value_orders as
select *
from class
where class.cid>1
select * from temp_high_value_orders;
3.代码解读
可见性和作用域：
临时表仅对创建它的会话可见，其他会话无法访问。这确保了临时表不会影响其他用户或会话的数据库操作。
在存储过程中创建的临时表，在存储过程结束时，如果会话继续，临时表仍然存在，直到会话结束。
命名冲突：
临时表可以和数据库中的普通表同名，但在当前会话中，会优先使用临时表。这允许使用临时表来替代普通表进行测试或修改操作，而不影响其他会话的正常使用。
例如，如果数据库中已经有一个名为 orders 的普通表，在当前会话中创建一个名为 orders 的临时表，对 orders 的操作将只针对临时表，直到会话结束。
性能考虑：
临时表存储在内存（MEMORY 存储引擎）或磁盘（MyISAM 或 InnoDB 存储引擎）中，取决于创建时的设置或服务器的默认设置。对于大型数据集，存储在磁盘上可能更合适，以避免内存溢出。
使用索引和适当的约束可以提高临时表的性能，特别是在进行连接操作和数据检索时。
索引和约束：
可以像普通表一样为临时表添加索引和约束，以优化数据查询和操作。(5)删除临时表
1.语法
drop temporary table 临时表名称;
2.示例
drop temporary table tt_physical;
3.数据类型datatype
1.整数
int 表示有符号，取值范围:-2147483648~2147483647
int unsigned 表示无符号，取值范围:0~4294967295
int(5)zerofi11 仅用于显示，当不满足5位时，按照左边补0，例如:00002;满足时，正常显示。
tinyint((m))[unsigned][zerofi11]
有符号，取值范围:-128~127
无符号，取值范围:0~ 255
bigint[(m)][unsigned][zerofi11]
有符号，取值范围:-9223372036854775808~9223372036854775807
无符号，取值范围:0~18446744073709551615
2.小数
decimal[(m[,d])][unsigned][zerofill]
准确的小数值，m是数字总个数(负号不算)，d是小数点后个数。m最大值为65，d最大值为30。
FLOAT[(M,D)】[UNSIGNED][ZEROFILL]
单精度浮点数，非准确小数值，m是数字总个数，d是小数点后个数。
DOUBLE[ (M,D)][UNSIGNED][ZEROFILL]
双精度浮点数(非准确小数值)，m是数字总个数，d是小数点后个数。
3.文本
char(m)
定长字符串，m代表字符串的长度，最多可容纳255个字符。
定长的体现:即使内容长度小于m，也会占用m长度。例如:char(5)，数据是:yes，底层也会占用5个字符;如果超出m长度限制(默认MySQL是严格模式，所以会报错)。
varchar(m)
变长字符串，m代表字符串的长度，最多可容纳65535个字节。
变长的体现:内容小于m时，会按照真实数据长度存储;如果超出m长度限制((默认MySQL是严格模式，所以会报错)。
text
text数据类型用于保存变长的大字符串，可以组多到65535(2**16-1)个字符。一般情况下，长文本会用text类型。例如:文章、新闻等。
mediumtext
A TEXT column with a maximum length of16,777,215(2**24-1)characters.
longtext
TEXT column with amaximum length of4,294,967,295 or 4GB(2**32-1)
4.时间
datetime
YYYY-MM-DD HH:MM:SS(1000-01-01 00:00:00/9999-12-31 23:59:59)
timestamp
YYYY-MM-DD HH:MM:SS(1970-01-0100:00:00/2037年)
对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC(世界标准时间)进行存储，查询时，将其又转化为客户端当前时区进行返回。
4.约定条件constraint
null 允许为空
not null 不允许为空
default 3 设置默认值为3，也可以设置为其他值
primary key 主键(不允许为空、不能重复)
auto_increment 自增5.mysql增删改查
1.新增数据
(1)新增单行数据
insert into 表格(列名1,名2,列名3) values(数据1,数据2,数据3);
(2)新增多行数据
insert into 表格(列名1,名2,列名3) values(数据1,数据2,数据3),(数据4,数据5,数据6);
说明：values后多行数据用逗号隔开。
(3)不输入列名新增数据
insert into 表格 values(数据1,数据2,数据3);
说明：表示按默认列顺序插入数据，插入的数据数量必须和列的位置及数量一致，否则会报错。
2.删除数据
delete from 表名;
delete from 表名 where 条件;
说明：
1.条件用“=”“>”,"<",">=","<=","!="等符号表示，切记不用python中的“==”。
2.条件可以使用and和or连接符进行连接。
3.修改数据
update 表名 set 列名=值 where 条件;
说明：
1.值可以是一个具体的数值，也可以是表达式，也可以是一个函数(见mysql函数总结)。
2.条件用法同删除数据中的where中条件用法。
3.如果不添加where条件是不是修改的为整列数据，操作验证一下。
4.查询数据
select * from 表名; 查询所有数据
select 列名1,列名2,列名3 from 表名; 查询表中名称为列名1,列名2,列名3中的数据
select * from 表名 where 条件; 查询表中符合where条件的数据
select 列名1,列名2,列名3 as 别名1,别名2,别名3 from 表名; 查询表中名称为列名1,列名2,列名3中的数据，显示结果时对应的列名分别为别名1,别名2,别名3
6.mysql函数
来自豆包。
(1)数学函数
ABS()
功能：返回一个数的绝对值。 示例：ABS(-5)返回5。
CEIL()、CEILING()
功能：返回大于或等于给定数字的最小整数。 示例：CEIL(4.2)返回5，CEILING(4.8)也返回5。
FLOOR()
功能：返回小于或等于给定数字的最大整数。 示例：FLOOR(4.8)返回4。
ROUND()
功能：对给定数字进行四舍五入。可以指定小数位数。 示例：ROUND(4.4)返回4，ROUND(4.5)返回5，ROUND(4.123, 2)返回4.12（保留两位小数）。
TRUNCATE()
功能：截断数字，直接舍去指定小数位数后的数字。 示例：TRUNCATE(4.123, 2)返回4.12。
MOD ()、%（取模运算符）
功能：返回除法运算的余数。 示例：MOD(7, 3)或7 % 3都返回1。
POW()、POWER()
功能：计算一个数的幂次方。 示例：POW(2, 3)或POWER(2, 3)都返回8（计算 2 的 3 次方）。
SQRT()
功能：计算一个数的平方根。 示例：SQRT(9)返回3。
(2)字符串函数
CONCAT()
功能：将多个字符串连接成一个字符串。 示例：CONCAT('Hello', ', ', 'World')返回Hello, World。
SUBSTRING()、MID()
功能：从字符串中提取子字符串。可以指定起始位置和长度。 示例：SUBSTRING('Hello World', 7, 5)或MID('Hello World', 7, 5)都返回World（从第 7 个字符开始取 5 个字符）。
LEFT()
功能：从字符串的左边提取指定长度的子字符串。 示例：LEFT('Hello World', 5)返回Hello。
RIGHT()
功能：从字符串的右边提取指定长度的子字符串。 示例：RIGHT('Hello World', 5)返回World。
LENGTH()、CHAR_LENGTH()
功能：返回字符串的长度。对于多字节字符集，CHAR_LENGTH()更准确。 示例：LENGTH('Hello')返回5，CHAR_LENGTH('你好')返回2。
LOWER()、LCASE()
功能：将字符串转换为小写。 示例：LOWER('HELLO')或LCASE('HELLO')都返回hello。
UPPER()、UCASE()
功能：将字符串转换为大写。 示例：UPPER('hello')或UCASE('hello')都返回HELLO。
REPLACE()
功能：替换字符串中的指定子字符串。 示例：REPLACE('Hello World', 'World', 'MySQL')返回Hello MySQL。
TRIM()、LTRIM()、RTRIM() 功能：去除字符串两端（TRIM()）、左边（LTRIM()）或右边（RTRIM()）的空格或指定字符。 示例：TRIM(' Hello ')返回Hello，LTRIM(' Hello')返回Hello，RTRIM('Hello ')返回Hello。
(3)日期和时间函数
CURRENT_DATE()、CURDATE()
功能：返回当前日期。 示例：CURRENT_DATE()或CURDATE()在执行时返回当天日期，格式为YYYY - MM - DD。
CURRENT_TIME()、CURTIME()
功能：返回当前时间。 示例：CURRENT_TIME()或CURTIME()在执行时返回当前时间，格式为HH:MM:SS。
NOW()、SYSDATE()
功能：返回当前日期和时间。 示例：NOW()和SYSDATE()在执行时返回当前日期和时间，格式为YYYY - MM - DD HH:MM:SS。
DATE_ADD()、ADDDATE()
功能：向日期添加指定的时间间隔。可以是年、月、日、小时、分、秒等。 示例：DATE_ADD('2023 - 01 - 01', INTERVAL 1 DAY)或ADDDATE('2023 - 01 - 01', 1)都返回2023 - 01 - 02（向后加 1 天）。
DATE_SUB()、SUBDATE()
功能：从日期中减去指定的时间间隔。 示例：DATE_SUB('2023 - 01 - 01', INTERVAL 1 DAY)或SUBDATE('2023 - 01 - 01', 1)都返回2022 - 12 - 31（向前减 1 天）。
DATEDIFF()
功能：计算两个日期之间的天数差。 示例：DATEDIFF('2023 - 01 - 03', '2023 - 01 - 01')返回2。
TIMEDIFF()
功能：计算两个时间之间的时间差。 示例：TIMEDIFF('10:00:00', '09:00:00')返回01:00:00。
(4)聚合函数
COUNT()
功能：统计行数。可以用于统计某个列的非空值数量或者整个表的行数。 示例：COUNT(*)统计表中的所有行数，COUNT(column_name)统计column_name列的非空值数量。
SUM()
功能：计算某列数值的总和。 示例：对于一个sales表中的amount列，SUM(amount)可以计算总销售额。
AVG()
功能：计算某列数值的平均值。 示例：对于一个scores表中的score列，AVG(score)可以计算平均分数。
MAX()
功能：返回某列中的最大值。 示例：在一个prices表中的price列，MAX(price)返回最高价格。
MIN()
功能：返回某列中的最小值。 示例：在一个temperatures表中的temperature列，MIN(temperature)返回最低温度。
(5)其他函数
IF()
功能：根据条件返回不同的值。类似于编程语言中的条件判断。 示例：IF(condition, value_if_true, value_if_false)，如IF(score > 60, 'Pass', 'Fail')根据score的值判断是否及格。 CASE WHEN...THEN...ELSE...END
功能：实现更复杂的条件判断和返回值。
7.python操作MySQL步骤
def useMySQL(sql,command,database,user,password,charset='utf8',port=3306):
# 1.连接数据库，如果没有则创建
con = pymysql.connect(user=user, password=password, charset=charset, port=port,database=database)
print(con)
# 2.创建指针
cur=con.cursor()
# 3.编写sql语句
sql=sql
# 4.执行sql语句
cur.execute(sql)
# 5.提交命令
if command in ['insert','delete','update']:
con.commit()
# 6.关闭指针和连接
cur.close()
con.close()Day2
1.where
(1)关系运算符
select * from info where id>1;
select * from info where id=1;
select * from info where id>=1;
select * from info where id!=1;
(2)逻辑运算符
select * from info where name="吴佩奇" and age=19;
select * from info where name="alex" or age=49;
select * from info where (name="李杰" or email="pyyu@live.com") and age=49; (3)between
select * from info where id between 2 and 4;(4)in
select * from info where id in (1,4,6);
select * from info where id not in (1,4,6);
select * from info where id in (select id from depart);(5)exists
select * from info where exists(select * from info where id=5);
select * from info where not exists(select * from info where id=5);(6)二次查询
select * from (select * from info where id>2) as T where T.age>30;(7)通配符
select * from info where email like "%y%";
select * from info where name like "_ony";(8)null
1.语法
select * from 表名 where 列名 is null;
select * from 表名 where 列名 is not null;
2.示例
select * from tt_course_score_info where physics_num is null;--空值
select * from tt_course_score_info where physics_num is not null;-- 非空2.指定列
(1)选择所有数据
1.语法
select * from 表名;
2.示例
select * from course;
(2)选择指定列数据
1语法
select 列名1,列名2 from 表名;
2.示例
select cname,teacher_id from course;
(3)为选定列指定别名
1.语法
select 列名1 as 别名1,列名2 as 别名2 from 表名;
2.示例
select cname as u1,teacher_id as v1 from course;
说明：
as 在mysql中是为列名起别名的关键字。
(4)新增数据并指定列名
1.语法
select 列名1 as 别名1,列名2 as 别名2,"数值" as 新增列名 from 表名;
2.示例
select cname as u1,teacher_id as v1,"y" as w1 from course;
select cname as u1,teacher_id as v1,"y" as w1 from course;
代码解释：
SELECT：这是 SQL 中用于查询数据的关键字。
cname as u1：表示从 course 表中选择 cname 列，并将该列的名称在结果集中重命名为 u1。这样在最终的查询结果中，原本的 cname 列将显示为 u1 列。
teacher_id as v1：从 course 表中选择 teacher_id 列，并将其重命名为 v1 列。这在结果集中将以 v1 的名称显示该列的数据。
"y" as w1：这里是一个常量值 "y"，将其作为一个名为 w1 的列添加到结果集中。无论表 course 中有多少行数据，w1 列的值都将始终为 "y"。这是一种在结果集中添加常量列的方法，在某些情况下可以用来补充信息或进行数据标记。
FROM course：指定了要查询数据的表，即 course 表。(5)动态获取关联数据并指定新增列名
select
cname,
teacher_id,
(select tname from teacher where teacher.tid=teacher_id) as teacher
from course;
3.按指定列排序
1.顺序
select * from teacher order by 列名 desc;
2.倒序
select * from teacher order by 列名 asc;
3.示例
select * from teacher order by tname desc;
select * from teacher order by tname asc;
4.按多列排序
select * from info order by age,id desc;
优先按照age从小到大排序;如果age相同则按照id从大到小排序。4.获取部分数据
1.获取排在前面一定行数的数据
select * from teacher limit 10;
2.获取指定行号后面的一定行数的数据
select * from teacher limit 10 offset 5;
从第5行开始但不包括第5行5.分组
(1)关于对分组的理解：实际上是获取某个分组的统计数据。
1.语法
select 分组列名,分组统计函数 from 表名 group by 分组列名;
2.示例
select gender,count(gender) from student group by gender;
select gender,count(gender) as count from student group by gender;--为统计列重新命名
(2)筛选分组后的数据
关键字：having
1.示例
select age,count(id) from info where id>4 group by age having count(id)>=2;
2.代码解释：
SELECT age, COUNT(id)：
SELECT 关键字用于指定要从表中查询的列。在这里，我们要查询两个部分：
age：表示要选择 info 表中的 age 列。
COUNT(id)：COUNT() 是一个聚合函数，用于计算 id 列的数量。在这个查询中，它会统计满足条件的每组 age 中 id 的数量。
FROM info：
这部分指定了要从哪个表中查询数据，这里是 info 表。
WHERE id > 4：
WHERE 子句用于筛选满足条件的数据行。在这里，它筛选出 id 大于 4 的记录。
GROUP BY age：
GROUP BY 子句用于将数据按照 age 列的值进行分组。对于 info 表中具有相同 age 的记录，会被分到同一组中。
HAVING COUNT(id) >= 2：
HAVING 子句用于对分组后的结果进行筛选。这里使用 HAVING 而不是 WHERE，是因为 WHERE 不能用于筛选聚合结果，而 HAVING 可以。它筛选出那些 id 数量（通过 COUNT(id) 计算）大于或等于 2 的组。6.表关系(左右关系)
1.左外连接并获取所有列的数据
(1)语法
select * from 左表名 left outer join 右表名 on 左表名.对应列=右表名.对应列;
(2)示例
select * from student left outer join class on student.class_id=class.cid;
2.左外连接并获取指定列的数据
select sid,sname,gender,caption from student left outer join class on student.class_id=class.cid;
2.右外连接参左外连接
3.内连接，需要时查找笔记
4.上下连接，很少用，需要时查找笔记7.外键约束
(1)创建表时设置外键
1.创建表时设置外建
constraint 外键约束名称 foreign key (需要设置外键的列名) references 关联表名(需要关联的列名)
2.示例
constraint fk_student_class foreign key (class_id) references class(cid)
3.代码解释：
CONSTRAINT：这是 SQL 中的关键字，用于在表中创建约束。约束用于确保数据的完整性和一致性。
fk_student_class：这是给外键约束取的一个名字，这个名字可以根据需要自定义，通常遵循一定的命名规则，以便后续的管理和维护，比如 fk_表名_关联表名 这样的命名约定，在这里表示这是一个与 student 表和 class 表关联的外键约束，其名称为 fk_student_class。
FOREIGN KEY (class_id)：这部分指定了在当前表中哪个列将作为外键，这里是 class_id 列。外键列的值必须是另一个表中某列的值或者为 NULL（如果外键允许 NULL 值）。
REFERENCES class(cid)：指定了外键所引用的表和列，这里表示 class_id 列引用了 class 表中的 cid 列。这意味着 class_id 列中的值必须是 class 表中 cid 列的值，或者为 NULL（如果外键允许 NULL）。(2)为已有表添加外键约束
1.语法
alter table guardian add constraint 外键约束名称 foreign key (需要设置外键的列名) references 关联表名(需要关联的列名)
2.示例
alter table guardian add constraint fk_guardian_class foreign key guardian(class_id) references class(cid);(3)删除外键
1.语法
alter table 表名 drop foreign key 外键约束名称;
2.示例
alter table guardian drop foreign key fk_guardian_class;8.SQL语句顺序
from
join on
where
group by
having
(select执行顺序和书写顺序不一致)
order by
limit
结合老师课件写的示例，测试可以成功运行：
select *
from info
left outer join depart on info.depart_id=depart.did
where id>1
group by age
having count(id)>1
order by age
limit 1;特别舒适的MySQL语句写法
1.示例代码
SELECT t1.column1, t2.column2, COUNT(t3.column3) AS count_column3
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.table1_id
LEFT JOIN table3 t3 ON t2.id = t3.table2_id
WHERE t1.column4 > 10
GROUP BY t1.column1, t2.column2
HAVING COUNT(t3.column3) > 2
ORDER BY t1.column1 ASC
LIMIT 10;
2.执行顺序解读
该 SQL 语句的执行顺序如下：
从 table1（FROM table1 t1）开始，这是左外连接中的左表。
执行 LEFT JOIN table2 t2 ON t1.id = t2.table1_id，将 table1 中的行与 table2 中匹配的行连接起来，如果 table2 中没有匹配的行，用 NULL 填充 table2 的列。
接着执行 LEFT JOIN table3 t3 ON t2.id = t3.table2_id，将上一步的结果与 table3 中的行进行连接，如果 table3 中没有匹配的行，用 NULL 填充 table3 的列。
使用 WHERE t1.column4 > 10 对连接后的结果集进行筛选。
根据 GROUP BY t1.column1, t2.column2 对筛选后的结果进行分组。
使用 HAVING COUNT(t3.column3) > 2 对分组结果进行筛选。
从分组筛选后的结果中，选择 t1.column1、t2.column2 和 COUNT(t3.column3) 作为结果列，并将 COUNT(t3.column3) 重命名为 count_column3（SELECT t1.column1, t2.column2, COUNT(t3.column3) AS count_column3）。
对结果集进行升序排序（ORDER BY t1.column1 ASC）。
最后，仅返回前 10 行（LIMIT 10）。9.创建用户及授权
1.创建用户
create user happy_mydata@127.0.0.1 identified by "bai666";
2.查询用户
select user,authentication_string,host from mysql.user;
3.删除用户
drop user happy_mydata@127.0.0.1;
4.修改用户
rename user mydata_happy to mysql_happy@localhost;
5.修改用户密码(两种方法)
set password for 'mysql_happy'@'localhost' = '111111';
alter user 'mysql_happy'@'localhost' identified by 'new_password';
set password for mysql_happy@localhost=password("6666");(新版本已作废，仅适用于5.7.6以前的版本)
6.授权(先执行6后执行7才会完成授权)
grant all privileges on *.* to mysql_happy@localhost;
7.授权生效
flush privileges;
8.查看授权
show grants for mysql_happy@localhost;
9.取消授权(先执行9后执行7才会完成取消授权)
revoke all privileges on *.* from mysql_happy@localhost;111
Day3
Day4
Day1
1 MySQL数据库概述
2今日概要
3 windows系统-MySQL环境
老师说，加环境变量自己加。
4 mac系统-MySQL环境搭建
5 配置和密码相关
6 数据库的管理
命令汇总：
查看所有数据库
show databases;
创建数据库
create database day1db DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
删除数据库
drop database day1db;
进入数据库
use day1db;
查看数据库中的表
show tables;
7 数据表的管理
管理表命令
创建表
create table tab1(
id int,
name varchar(16)
)default charset=utf8;
查看列名
desc tab1;
create table L1(
id int,
uid int unsigned,
zid int(6) zerofill
)default charset=utf8;# 学习进度12：31
8 数据表-列类型-整型
9 数据表-列类型-小数
10 数据表-列类型-字符串
11 数据表-列类型-时间
12 数据表-小结
13 数据表-python代码操作
14 数据行-增删改查
15 数据行-python操作和应用案例
16 SQL注入
17 今日总结和作业
Day2
1 今日概要
2 必备SQL-数据准备
3 必备SQL-条件
4必备SQL-通配符
% 表示任意字符
"%佩%" 表示以任意字符开头，以任意字符结尾，中间是汉字“佩”
"%佩" 表示以任意字符开头，结尾是汉字“佩”
"吴%奇" 表示以汉字“吴”开头，以汉字“奇”结尾，中汉是任意字符
注意：这里的任意字符可以多个或一个字符
_ 表示一个字符
"_peiqi@live.com" 表示peiqi@live.com前面可以是任意一个字符，切记只能有一个字符
5必备SQL-指定列(映射)
6必备SQL–排序
7必备SQL- limit部分数据
8必备SQL-分组
个人理解：MySQL中的分组中pandas中不一样。
9必备SQL-左右连表
10必备SQL-上下连表
老师说上下连接不常用。
11必备SQL-小结
12表关系
MySQL中，表关系主要由外键约束来实现。
13表关系案例:简易版路飞学城
14用户授权管理
15今日总结
Day3
1今日概要
2 题目介绍
已解决问题：
6.
select * from (select gname,count(gname) as count from guardian group by gname) as T where T.count>1;自己写的练习题答案
9、查询成绩小于60分的同学的学号、姓名、成绩、课程名称。
select student_id,
(select sname from student where student.sid=score.student_id) as sname,
number,
(select cname from course where course.cid=score.course_id) as cname
from score
where number<60;
10.查询选修了“生物课"的所有学生ID、学生姓名、成绩。
select student_id,
(select sname from student where student.sid=score.student_id) as sname,
number
from score
where course_id=1;
11.查询选修了“生物课"且分数低于60的的所有学生ID、学生姓名、成绩。
select student_id,
(select sname from student where student.sid=score.student_id) as sname,
number,
(select cname from course where course.cid=score.course_id) as cname
from score
where number<60 and course_id=1;
12.查询所有同学的学号、姓名、选课数、总成绩。
select student_id,
(select sname from student where student.sid=score.student_id) as sname,
count(student_id) as count,
sum(number) as total_score
from score
group by student_id;
13，查询各科被选修的学生数。
select course_id,count(student_id) as count
from score
group by course_id;
14、查询各科成绩的总分、最高分、最低分，显示:课程ID、课程名称、总分、最高分、最低分。
select course_id,
(select cname from course where course.cid=score.course_id) as cname,
sum(number) as total_score,
max(number) as max_score,
min(number) as min_score
from score
group by course_id;
15.查询各科成绩的平均分，显示:课程ID、课程名称、平均分。
select course_id,
(select cname from course where course.cid=score.course_id) as cname,
round(avg(number),2) as avg_score
from score
group by course_id;
16、查询各科成绩的平均分，显示:课程ID、课程名称、平均分(按平均分从大到小排序)。
select course_id,
(select cname from course where course.cid=score.course_id) as cname,
round(avg(number),2) as avg_score
from score
group by course_id
order by number desc;
17.查询各科成绩的平均分和及格率，显示:课程ID、课程名称、平均分、及格率。
select course_id,
(select cname from course where course.cid=score.course_id) as cname,
round(avg(number),2) as avg_score,
(select fail_count from((select course_id as id,count(course_id) as fail_count from score where number<60 group by course_id)) as T where T.id=score.course_id) as fail_count,
count(course_id) as total_count,
((select fail_count from((select course_id as id,count(course_id) as fail_count from score where number<60 group by course_id)) as T where T.id=score.course_id)/count(course_id)) as pass_rate
from score
group by course_id;
18.查询平均成绩大于60的所有学生的学号、平均成绩。
select student_id,
round(sum(number)/count(number),2) as avg_score
from score
group by student_id;
19.查询平均成绩大于85的所有学生的学号、平均成绩、姓名。
发现了嵌套查询技术
select * from
(select student_id,
(select sname from student where student.sid=score.student_id) as sname,
round(sum(number)/count(number),2) as avg_score
from score
group by student_id) as T
where T.avg_score>85;
20.查询“三年二班”每个学生的 学号、姓名、总成绩、平均成绩。
select * from
(select student_id,
(select sname from student where student.sid=score.student_id) as sname,
round(sum(number)/count(number),2) as avg_score,
(select class_id from student where student.sid=score.student_id) as class_id
from score
group by student_id) as T
where T.class_id=1;
21、查询各个班级的班级名称、总成绩、平均成绩、及格率(按平均成绩从大到小排序)。
(1)创建不合格人数的临时表
create temporary table class_fail_info as
select
class_id,
(select caption from class where class.cid=U.class_id) as caption,
count(number) as fail_count
from
(select student_id,
(select sname from student where student.sid=score.student_id) as sname,
number,
(select class_id from student where student.sid=score.student_id) as class_id
from score) as U
where number<60
group by class_id;
(2)创建总人数临时表
create temporary table class_total_info as
select
class_id,
(select caption from class where class.cid=U.class_id) as caption,
count(number) as total_count,
sum(number) as total_score,
avg(number) as avg_score
from
(select student_id,
(select sname from student where student.sid=score.student_id) as sname,
number,
(select class_id from student where student.sid=score.student_id) as class_id
from score) as U
group by class_id;
(3)拼接输出本题结果
select
class_total_info.class_id as class_id,
class_total_info.caption as caption,
total_score,
round(avg_score,2) as avg_score,
round(fail_count/total_count,2) as pass_rate
from
class_total_info
left outer join class_fail_info on class_total_info.class_id=class_fail_info.class_id;
22.查询学过“波多”老师课的同学的学号、姓名。
select student_id,sname from
(select
student_id,
(select sname from student where student.sid=score.student_id) as sname,
(select teacher_id from course where course.cid=score.course_id) as teacher_id
from score) as T
where T.teacher_id=1;
23.查询没学过“波多” 老师课的同学的学号、姓名。
select student_id,sname from
(select
student_id,
(select sname from student where student.sid=score.student_id) as sname,
(select teacher_id from course where course.cid=score.course_id) as teacher_id
from score) as T
where T.teacher_id !=1;
24，查询选修“苍空”老师所授课程的学生中，成绩最高的学生姓名及其成绩(不考虑并列)。
select * from
(select student_id,sname,number from
(select
student_id,
(select sname from student where student.sid=score.student_id) as sname,
number,
(select teacher_id from course where course.cid=score.course_id) as teacher_id
from score) as T
where T.teacher_id =2) as V
where V.number=(select max(number) from((select number from
(select
number,
(select teacher_id from course where course.cid=score.course_id) as teacher_id
from score) as T
where T.teacher_id =2)) as Y)
limit 1;
25、查询选修“苍空”老师所授课程的学生中，成绩最高的学生姓名及其成绩(考虑并列)。
select * from
(select student_id,sname,number from
(select
student_id,
(select sname from student where student.sid=score.student_id) as sname,
number,
(select teacher_id from course where course.cid=score.course_id) as teacher_id
from score) as T
where T.teacher_id =2) as V
where V.number=(select max(number) from((select number from
(select
number,
(select teacher_id from course where course.cid=score.course_id) as teacher_id
from score) as T
where T.teacher_id =2)) as Y);
26.查询只选修了一门课程的全部学生的学号、姓名。
select student_id,sname from (select student_id,sname,count(sname) as select_num from
(select
student_id,
(select sname from student where student.sid=score.student_id) as sname,
(select teacher_id from course where course.cid=score.course_id) as teacher_id
from score) as T
group by student_id
having select_num=1) as V;
27、查询至少选修两门课程的学生学号、学生姓名、选修课程数量。
select student_id,sname,count(sname) as select_num from
(select
student_id,
(select sname from student where student.sid=score.student_id) as sname,
(select teacher_id from course where course.cid=score.course_id) as teacher_id
from score) as T
group by student_id
having select_num>1;
28，查询两门及以上不及格的同学的学号、学生姓名、选修课程数量
select student_id,sname,count(sname) as select_num from
(select
student_id,
(select sname from student where student.sid=score.student_id) as sname,
(select teacher_id from course where course.cid=score.course_id) as teacher_id
from score where number <60) as T
group by student_id
having select_num>1;
29，查询选修了所有课程的学生的学号、姓名。
select student_id,sname from (select student_id,sname,count(sname) as select_num from
(select
student_id,
(select sname from student where student.sid=score.student_id) as sname,
(select teacher_id from course where course.cid=score.course_id) as teacher_id
from score) as T
group by student_id
having select_num=3) as V;
30，查询未选修所有课程的学生的学号、姓名。
select student_id,sname from (select student_id,sname,count(sname) as select_num from
(select
student_id,
(select sname from student where student.sid=score.student_id) as sname,
(select teacher_id from course where course.cid=score.course_id) as teacher_id
from score) as T
group by student_id
having select_num!=3) as V;
31.查询所有学生都选修了的课程的课程号、课程名。
select
course_id,
(select cname from course where course.cid=score.course_id) as cname,
count(course_id) as count
from score group by course_id having count=(select count(sname) as select_num from student);
32、查询选修“生物”和“物理” 课程的所有学生学号、姓名。(因为自己创建的数据库中，所有学生都选择了生物课，
因此将本题改为：查询选修“体育”和“物理” 课程的所有学生学号、姓名。)
select student_id,
(select sname from student where sid=score.student_id) as sname
from score where course_id !=1 group by student_id;
33.查询至少有一门课与学号为“1"的学生所选的课程相同的其他学生学号 和 姓名。
select
sid,
sname,
-- 添加生物列
(select if(exists (
select 1
from score
where student_id = student.sid and course_id=1
), true, 0) as result) as biology，
-- 添加体育列
(select if(exists (
select 1
from score
where student_id = student.sid and course_id=2
), true, 0) as result) as physical，
-- 添加物理列
(select if(exists (
select 1
from score
where student_id = student.sid and course_id=3
), true, 0) as result) as physics
from student;
(1)创建关于生物课的临时表tt_biology
create temporary table tt_biology as
select
sid,
sname,
-- 添加生物列
(select if(exists (
select 1
from score
where student_id = student.sid and course_id=1
), true, 0) as result) as biology
from student;
(2)创建关于体育课的临时表tt_physical
create temporary table tt_physical as
select
sid,
sname,
-- 添加体育列
(select if(exists (
select 1
from score
where student_id = student.sid and course_id=2
), true, 0) as result) as physical
from student;
(3)创建关于物理课的临时表tt_physics
create temporary table tt_physics as
select
sid,
sname,
-- 添加物理列
(select if(exists (
select 1
from score
where student_id = student.sid and course_id=3
), true, 0) as result) as physics
from student;
(4)拼接临时表并创建临时表tt_student_course_info
create temporary table tt_student_course_info as
select T.sid as sid,
T.sname as sname,
biology,
physical,
physics
from
(select tt_biology.sid as sid,
tt_biology.sname as sname,
biology,
physical
from tt_biology
left outer join tt_physical on tt_biology.sid=tt_physical.sid) as T
left outer join tt_physics on T.sid=tt_physics.sid;
(5)问题答案
select sid,sname from tt_student_course_info where biology=1 or physical=1 or physics=1;
34、查询与学号为“2"的同学选修的课程完全相同的其他 学生学号 和 姓名
select sid,sname from tt_student_course_info where biology=1 and physical=1 and physics=0;
35.查询“生物”课程比“物理”课程成绩高的所有学生的学号。
(1)创建临时成绩表tt_course_score_info
create temporary table tt_course_score_info as
select sid,sname,
(select number from score where score.student_id=tt_student_course_info.sid and score.course_id=1) as biology_num,
(select number from score where score.student_id=tt_student_course_info.sid and score.course_id=2) as physical_num,
(select number from score where score.student_id=tt_student_course_info.sid and score.course_id=3) as physics_num
from tt_student_course_info;
(2)使用临时表查找问题结果
select * from tt_course_score_info where (biology_num is not null and physics_num is not null) and biology_num>physics_num;
36.查询每门课程成绩最好的前3名(不考虑成绩并列情况)。
select sid,sname, biology_num from tt_course_score_info order by biology_num desc limit 3;-- 生物
select sid,sname, physical_num from tt_course_score_info order by physical_num desc limit 3;-- 体育
select sid,sname, physics_num from tt_course_score_info order by physics_num desc limit 3;-- 物理
37、查询每门谍程成绩最好的前3名(考虑成绩并列情况)
38.创建一个表 sc，然后将score 表中所有数据插入到 sc 表中。
create table sc as
select * from score;
39.向score表中插入一些记录，这些记录要求符合以下条件:。学生ID为:没上过课程ID为“2”课程的学生的 学号:
。课程ID为:2
。成绩为:80
40，向 score 表中播入一些记录，这些记录要求符合以下条件:。学生ID为:没上过课程ID为“2”课程的学生的 学号,
。课程ID为:2。
。成绩为:谍程ID为3的最高分。
临时表
create temporary table temp_high_value_orders as
select *
from class
where class.cid>1
select * from temp_high_value_orders;
删除临时表
drop temporary table tt_physical;
DROP TEMPORARY TABLE
3 SQL强化-表和数据的导入导出
4 SQL强化-第2至18题
5 SQL强化-第19至34题
6SQL强化-35至结束
7 博客系统-表结构设计
Day4
1 今日概要
2 索引概述
3 常见索引
4 案例:博客系统
5 查询是要命中索引呀
6 执行计划
7 索引小结
8 MySQL函数