3. 字符串(重要) 字符串后面的括号中需要指定zi
char(4): 定长类型，超出4位，就报错，不够4位，使用空格填充
varchar(4): 可变长类型，超出4位，报错，不够4位的，有几位存几位
create table t10 (idint, name char(4));
create table t11 (idint, name varchar(4));
insert into t10 values(1,'jerry');
insert into t11 values(1,'jerry');"""如果你想超出范围之后，直接报错，需要设置严格模式!!!"""
show variables like "%mode%";
sql_mode
# 设置严格模式1. 命令行模式：临时修改
setglobal sql_mode='STRICT_TRANS_TABLES';# 不区分大小写2. 配置文件修改：永久修改
## 研究定长和不定长
create table t12 (idint, name char(4));
create table t13 (idint, name varchar(4));
insert into t12 values(1,'ke');
insert into t13 values(1,'ke');## 验证是否补充了空格
select char_length(name)from t12;
select char_length(name)from t13;"""默认情况下，没有对char类型填充空格，如果想看填充了空格，需要设置严格模式"""# 设置严格模式1. 命令行模式：临时修改
setglobal sql_mode='STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';# 不区分大小写2. 配置文件修改：永久修改
日期
4. 日期
date datetime time year
年月日 年月日 十分秒 十分秒 年
create table t14 (idint,
reg_time date,
reg1_time datetime,
reg2_time time,
reg3_time year
);
insert into t14 values(1,'2023-10-1','2023-11-11 11:11:11','11:11:11',2023);
1. 针对库的SQL语句
# 增加
create database 库名;# 查看库
show databases;
show create database db1;# 修改库(基本上不用，直接删掉直接创建)
alter database db1 charset='utf8';#修改的是库的字符编码# 删库跑路
drop database db1;# 不要轻易使用(测试环境随便使用，线上环境一般情况下你是没有权限)
针对表的sql语句
1.创建表的完整语法
create table t1(idint,
name varchar(43),
age int);
create table 库名.表名(
字段名1 数据类型 约束条件 约束条件 约束条件 约束条件,
字段名2 数据类型 约束条件 约束条件 约束条件 约束条件,
字段名3 数据类型 约束条件 约束条件 约束条件 约束条件,
字段名4 数据类型 约束条件 约束条件 约束条件 约束条件,
字段名5 数据类型 约束条件 约束条件 约束条件 约束条件,
字段名6 数据类型 约束条件 约束条件 约束条件 约束条件
);
insert into 库名.t1 values('1',2,3,4,5,6);1. 字段名和数据类型必须写的，不能省略
2. 约束条件是可选的，可有可无，而且可有有多个
3. 表结构中最后一个字段不能有逗号
2. 针对表的SQL语句
"""如何选择库"""
use 库名;
use db1;# 双击db1文件夹了# 增加表
create table 表名 (id 数据类型, name 数据类型, gender 数据类型)
create table t1(idint, name varchar(32), gender varchar(32));# 修改表
alter table t1 rename t2;# 修改表名# 查看表
show tables;#查看库下的所有表名
show create table t1;# 查看表结构
desc t1;# 查看表结构，格式化了# 删除表
drop table t2;
针对记录的sql语句
3.针对记录的sql语句
记录:表中的一行一行的数据称之为是一条记录
# 需要先有库、在有表、最后操作记录1. 查看记录
select *from t1;# 查所有
select t1(表头中的一个或几个)from t1;
select id,name,age from t1;2. 增加数据
insert into t1 values(1,'kevin',20);# 第一种方式，全字段增加, 单条增加
insert into t1 values(2,'kevin1',20),(3,'kevin2',20),(4,'kevin3',20),(5,'kevin4',20),(6,'kevin5',20);# 第二种方式，全字段增加, 批量增加
insert into t1(id, name) values(7,'kevin7');# 空的使用NULL填充3. 修改
update t1 set name='tank' where id=1;
update t1 set age=30 where name='tank';
update t1 set name='jerry',age=30 where id=3;
update t1 set name='oscar' where name='tank'and age=30;
update t1 set name='oscar' where name='kevin3'or name='kevin4';
update t1 set name='tony';"""以后再自行更新和删除的sql语句的时候，一定要细心、好好看看你的条件是否正确"""
案例：
update t1 set age=age+40 where id=1;
update t1 set price=price+10;"""一定要谨慎!!!!"""4. 删除
delete from t1 where id=1;
delete from t1 where id=2orid=7;
delete from t1;# 这是清空表
约束条件
约束条件其实就是在数据类型的基础之上在做约束
1. unsigned # 无符号，整型默认带符号，这个就可以直接修改不带符号idint unsigned
2. zerofill # 0填充，经常与整型数据结合一起使用
create table t3(idint(9) zerofill);3. default # 默认值
相当于形参中的默认参数
create table t4 (idint, name varchar(32) default 'kevin');
insert into t4 values(1,'jerry');
insert into t4(id) values(1);'如果不指定字段，会报错，因为不指定字段，会默认所有的字段都需要传参，但是因为有默认值在，所以如果想不传参，就要在前边指定那些字段需要传参，那些不需要，默认字段如果指定不需要传参，那就是默认值，这个和形参的默认值还是有一点点差别的'4.not null # 非空，即必须保证被限制的这个字段被传了参数
create table t5 (idint, name varchar(32)not null);
insert into t5(id) values(1);5. unique # 唯一
单列唯一：'被约束的那一列不能出现重复的数据'
create table t6 (idint, name varchar(32) unique);
多列唯一：'被限制的那几列加起来不能出现重复'
create table t7 (idint,
ip varchar(32),
port varchar(32),
unique(ip, port));6. 主键(primary key)"""主键单纯从约束上来看，它相当于是非空且唯一 unique not null"""id unique not null ---等价于-->id primary key
create table t8 (idint primary key);
create table t8 (idint unique not null);# 主键本身是一种索引，索引能够加快查询速度
InnoDB存储引擎规定每一张表都要有一个主键，但是，我之前创建的表都没有指定主键, 表是怎么创建成功的?
"""
是因为InnoDB存储引擎内部有一个隐藏的主键，这个主键我们看不到，它也不能够加快查询速度，仅仅是为了帮助我们把表创建成功. 所以，以后我们创建表的时候都主动的创建一个主键，我们自己创建的主键能够加快查询速度，因为是一个索引.
一般情况下，主键应该创建哪个字段? 大多都给id字段加了，所以，每一张表都要有一个id字段，并且一张表中不只是有一个主键，可以有多个主键，但是，大多数情况下，都只有一个
"""
主键一般都给id aid sid uid pid ...
create table t(idint primary key,
name varchar(32))# 我们可以通过主键确定一张表中得唯一一条记录!!!因为主键不能重复，所以，我们就可以查询出唯一的一条信息7. auto_incrment
# 自增：每一次主动比上一次加1"""一般情况下，它配合主键使用"""
create table t9 (idint primary key auto_increment,
name varchar(32));
'只要增加过，不管删了还是没删，都会基于上次的值增加，比如，增加到8，然后我把第8个数据删了，在新增加的时候，就会是9，而不是继续是8
清空表的两种方式
1. delete from t;# 不会重置自增设置的id值2. truncate t9;# 清空表、会重置自增修改的id值"""truncate：建议使用truncate，使用这个，万一你清空错了，还有机会恢复"""
mysql它有很多个日志文件，binlog日志-----》可以恢复数据，记录了你所有的SQL语句
补充一些其他的SQL语句
语法：
1. 修改表名
ALTER TABLE 表名 RENAME 新表名;
alter table 表名 rename 新表名;2. 增加字段
ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…],#默认是在字段之后添加字段
ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] FIRST;#在字段之前添加字段
ALTER TABLE 表名ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;#在字段之后添加字段名 3. 删除字段
ALTER TABLE 表名 DROP 字段名;
alter table 表名 drop 字段名;4. 修改字段 # modify只能改字段数据类型，完整约束条件，不能改字段名，但是change可以！
ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];"""除了以上的SQL语句，还有很多其他的，如果遇到了不会写，就直接搜索引擎"""
以上命令小写也可以
关键字练习题
数据准备
# 数据准备
create table emp(idint primary key auto_increment,
name varchar(20)not null,
sex enum('male','female')not null default 'male',#大部分是男的
age smallint(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int,#一个部门一个屋子
depart_id int);
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('tom','male',78,'20150302','teacher',1000000.31,401,1),#以下是教学部('kevin','male',81,'20130305','teacher',8300,401,1),('tony','male',73,'20140701','teacher',3500,401,1),('owen','male',28,'20121101','teacher',2100,401,1),('jack','female',18,'20110211','teacher',9000,401,1),('jenny','male',18,'19000301','teacher',30000,401,1),('sank','male',48,'20101111','teacher',10000,401,1),('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门('呵呵','female',38,'20101101','sale',2000.35,402,2),('西西','female',18,'20110312','sale',1000.37,402,2),('乐乐','female',18,'20160513','sale',3000.29,402,2),('拉拉','female',28,'20170127','sale',4000.33,402,2),('僧龙','male',28,'20160311','operation',10000.13,403,3),#以下是运营部门('程咬金','male',18,'19970312','operation',20000,403,3),('程咬银','female',18,'20130311','operation',19000,403,3),('程咬铜','male',18,'20150411','operation',18000,403,3),('程咬铁','female',18,'20140512','operation',17000,403,3);
查询关键字之where
# where------>筛选条件的
where筛选功能
"""
模糊查询:没有明确的筛选条件
关键字:like
关键符号:
%:匹配任意个数任意字符
_:匹配单个个数任意字符
#查询员工姓名中包含o字母的员工姓名和薪资
# 在你刚开始接触mysql查询的时候，建议你按照查询的优先级顺序拼写出你的sql语句
"""
先是查哪张表 from emp
再是根据什么条件去查 where name like ‘%o%’
'%o%表示，o前后是什么都可以'
再是对查询出来的数据筛选展示部分 select name,salary
"""
select name,salary from emp where name like '%o%';
"""# 1.查询id大于等于3小于等于6的数据
select id,name from emp where id>=3andid<=6;
select *from emp where id between 3and6;# 2.查询薪资是20000或者18000或者17000的数据
select *from emp where salary =20000or salary =18000or salary =17000;
select *from emp where salary in(20000,18000,17000);# 简写# 4.查询员工姓名是由四个字符组成的员工姓名与其薪资
select name,salary from emp where name like '____';#四个下划线，表示四个长度，一个下划线代表一个
select name,salary from emp where char_length(name)=4;#char_lenth()内置函数，相当于python中的len()# 5.查询id小于3或者大于6的数据
select *from emp where idnot between 3and6;# 6.查询薪资不在20000，18000，17000范围的数据
select *from emp where salary notin(20000,18000,17000);# 7.查询岗位描述为空的员工名与岗位名 '针对null不能用等号，只能用is'
select name,post from emp where post_comment '=' NULL;# 查询为空！
select name,post from emp where post_comment is NULL;
select name,post from emp where post_comment isnot NULL;'''在sql中，NULL和 '' 不一样'' 所以要用is而不是=
查询关键字之group by分组
分组: 按照某个指定的条件将单个单个的个体分成一个个整体
# 单纯的分组是没有意义的
在MySQL中分组之后，只能够获得分组的依据字段的值（严格模式）! 按照哪个字段分组就只能获取这个字段的值，别的字段不能拿到，分组一般配合聚合函数使用：
summaxmin avg（平均值） count（计数）
分组的关键字：group by
一、非严格模式
1. 分组之后默认可以获取所有的字段信息（不是严格模式下）
2. 分组之后，展示的数据都是每个组的第一条数据
#按部门分组
select *from emp group by post;# 分组后取出的是每个组的第一条数据
select id,name,sex from emp group by post;# 验证"""
如果设置了严格模式，即设置sql_mode为only_full_group_by，意味着以后但凡分组，只能取到分组的依据，
不应该在去取组里面的单个元素的值，那样的话分组就没有意义了，因为不分组就是对单个元素信息的随意获取
"""
二、分组之后默认只能够直接得到分组的依据字段的值（严格模式下） 其他数据都不能直接获取，需要结合聚合函数使用
setglobal sql_mode="strict_trans_tables,only_full_group_by";# 重新链接客户端
select *from emp group by post;# 报错
select id,name,sex from emp group by post;# 报错
select post from emp group by post;# 获取部门信息# 强调:只要分组了，就不能够再“直接”查找到单个数据信息了，只能获取到组名
针对严格模式需要自己设置sql_mode
setglobal sql_mode ='only_full_group_by,STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';
三、严格模式下，得到非分组依据字段的信息，就要聚合函数配合分组一起使用
maxminsum count avg
例：
# 2.获取每个部门的最高工资 # 以组为单位统计组内数据>>>聚合查询(聚集到一起合成为一个结果)# 每个部门的最高工资
select post,max(salary)from emp group by post;
补充:在显示的时候还可以给字段取别名
select post as'部门',max(salary)as'最高工资'from emp group by post;as也可以省略 但是不推荐省 因为寓意不明确
# 每个部门的最低工资
select post,min(salary)from emp group by post;# 每个部门的平均工资
select post,avg(salary)from emp group by post;# 每个部门的工资总和
select post,sum(salary)from emp group by post;# 每个部门的人数
select post,count(id)from emp group by post;
统计的时候只要是非空字段 效果都是一致的
这里显示age，salary，id最后演示特殊情况post_comment
group by 分组补充函数
# group_concat 分组之后使用
如果真的需要获取分组以外的数据字段 可以使用group_concat()# 每个部门的员工姓名
select post,group_concat(name)from emp group by post;
select post,group_concat(name,'|',sex)from emp group by post;
select post,group_concat(name,'|',sex,'|', gender)from emp group by post;
select post,group_concat(distinct name)from emp group by post;
select post,group_concat(distinct name separator '%')from emp group by post;# concat 不分组使用
select concat(name,sex)from emp;
select concat(name,'|',sex)from emp;# concat_ws()
select post,concat_ws('|', name, age, gender)from emp group by post;
关键字之having过滤
where与having都是筛选功能 但是有区别
where在分组之前对数据进行筛选
having在分组之后对数据进行筛选
1.统计各部门年龄在30岁以上的员工平均薪资,并且保留平均薪资大于10000的部门.# 先筛选出年龄在30岁以上的
select *from emp where age >30;# 在进行分组，按照部门分组
select avg(salary)as avg_salary from emp where age >30 group by post;# 保留平均薪资大于10000的部门
select avg(salary)as avg_salary from emp where age >30 group by post having avg(salary)>10000;
关键字之distinct去重
distinct:去重
"""带主键的数据去重有没有意义? 没有，主键本身就是唯一的"""
select distinct id,age from emp;
关键字之order by排序
select *from emp order by salary;#默认升序排
select *from emp order by salary desc;#降序排#先按照age降序排，在年轻相同的情况下再按照薪资升序排
select *from emp order by age desc,salary;'''多字段排序，如果想让后面的字段排序生效，前提：前面的排序字段必须一样'''# 统计各部门年龄在20岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序# 20岁以上的员工
select *from emp where age >20;# 各部门的平均薪资
select avg(salary)from emp where age >20 group by post having avg(salary)>1000;#
select avg(salary)from emp where age >20 group by post having avg(salary)>1000 order by avg(salary) desc;
1.子查询
所谓子查询就是，先在有外键的表中查询出外键，然后拿查询到的结果当作条件在去另外一张表中查询
"""子查询就是：一条SQL的执行结果就是另外一条SQL的执行条件!"""
例：
# 查询kevin的部门名称1. 应该先查询kevin 的部门编号(部门表的id)
select dep_id from emp where name='kevin';2. 然后拿着查询出来的部门id去dep表中查询部门名称
select *from dep where id=(select dep_id from emp where name='kevin';);2.连表查询
所谓连表查询，就是 """把多张有关系的表链接成一张大的虚拟表，连接出来的虚拟表不是实际存在的，它是在内存中存储，然后按照单表查询."""
专业的连表语法：
inner join # 内连接，查询的是两张表中都有的数据
left join # 左连接，以左表为基准（即写在left join 左边的那孩子那个表），查询左表中所有的数据，右表没有的数据，使用NULL填充
right join # 右连接，以右表为基准（即写在right join 右边的那张表），查询右表中所有的数据，右表没有的数据，使用NULL填充
union # 连接两个SQL语句的结果
select *from emp left join dep on emp.dep_id=dep.id
union
select *from emp right join dep on emp.dep_id=dep.id;"""连表可以连很多张表，不只是两张，大多数都是两张，如果有多张表，就多用几个 union"""
在上述第三步书写sql命令语句的时候，因为命令是书写在引号里面的，就会与sql 原本的引号在识别的时候冲突，导致识别的不正确，从而利用字符之间的拼接，让原本的sql语句产生错误的效果，然后，比如在登陆注册的时候；就会有人利用这个，跳过登陆环节，在密码不正确的情况下也登陆进去
综上所述：
"""
SQL注入的原因 是由于特殊符号的组合会产生特殊的效果
实际生活中 尤其是在注册用户名的时候 会非常明显的提示你很多特殊符号不能用
原因也是一样的
结论:涉及到敏感数据部分 不要自己拼接 交给现成的方法拼接即可
"""
例：
# 连接MySQL服务端
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123',
database='db8_3',
charset='utf8',
autocommit=True# 针对增 改 删自动二次确认)# 产生一个游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)# 编写SQL语句
username =input('username>>>:').strip()
password =input('password>>>:').strip()# sql = "select * from userinfo where name='%s' and pwd='%s'" % (name,pwd)
sql ="select * from userinfo where name=%s and pwd=%s"
cursor.execute(sql,(username,password))
data = cursor.fetchall()if data:print(data)print('登录成功')else:print('用户名或密码错误')# 1.只需要用户名也可以登录
username:>>> kevin ' -- ddasfdfsdfdsfsdfsdfsdfdsfsdfsd
username:>>> xxx ' or1=1# 2.不需要用户名和密码也可以登录
试图
1、什么是试图
视图就是通过查询得到一张虚拟表，然后保存下来，下次直接使用即可
2、为什么要用试图
如果要频繁使用一张虚拟表，可以不用重复查询
3、如何创建视图
reate view 试图表名 as（后面跟的的多表查询的时候的查询语句）
select *from teacher inner join course on teacher.tid = course.teacher_id;"""
创建好了之后 验证它的存在navicat验证 cmd终端验证
最后文件验证 得出下面的结论 视图只有表结构数据还是来源于之前的表
delete from teacher2course where id=1;
"""**强调**1、在硬盘中，视图只有表结构文件，没有表数据文件
2、视图通常是用于查询，尽量不要修改视图中的数据
4、如何删除试图
drop view 试图表名;5、思考:开发过程中会不会去使用视图？**
不会！视图是mysql的功能，如果你的项目里面大量的使用到了视图，那意味着你后期想要扩张某个功能的时候这个功能恰巧又需要对视图进行修改，意味着你需要先在mysql这边将视图先修改一下，然后再去应用程序中修改对应的sql语句，这就涉及到跨部门沟通的问题，所以通常不会使用视图，而是通过重新修改sql语句来扩展功能
触发器
在满足对某张表数据的增、删、改的情况下，自动触发的功能称之为触发器，触发器专门针对我们对某一张表数据增insert、删delete、改update的行为，这类行为一旦执行
触发器的语法
"""语法结构
create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row
begin
sql语句
end
"""# 针对插入
create trigger tri_after_insert_t1 after insert on 表名 for each row
begin
sql代码。。。
end
create trigger tri_after_insert_t2 before insert on 表名 for each row
begin
sql代码。。。
end
# 针对删除
create trigger tri_after_delete_t1 after delete on 表名 for each row
begin
sql代码。。。
end
create trigger tri_after_delete_t2 before delete on 表名 for each row
begin
sql代码。。。
end
# 针对修改
create trigger tri_after_update_t1 after update on 表名 for each row
begin
sql代码。。。
end
create trigger tri_after_update_t2 before update on 表名 for each row
begin
sql代码。。。
end
"""
需要注意 在书写sql代码的时候结束符是; 而整个触发器的结束也需要分号;
这就会出现语法冲突 需要我们临时修改结束符号
delimiter $$
delimiter ;
该语法只在当前窗口有效
"""# 案例
CREATE TABLE cmd (id INT PRIMARY KEY auto_increment,
USER CHAR (32),
priv CHAR (10),
cmd CHAR (64),
sub_time datetime,#提交时间
success enum ('yes','no')#0代表执行失败);
CREATE TABLE errlog (id INT PRIMARY KEY auto_increment,
err_cmd CHAR (64),
err_time datetime
);
delimiter $$ # 将mysql默认的结束符由;换成$$
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
if NEW.success ='no' then # 新记录都会被MySQL封装成NEW对象
insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
end if;
end $$
delimiter ;# 结束之后记得再改回来，不然后面结束符就都是$$了
事务
1、什么是事务
开启一个事务可以包含一些sql语句，这些sql语句要么同时成功
要么一个都别想成功，称之为事务的原子性
2、 事务的作用
保证了对数据操作的'数据安全性'
案例:用交行的卡操作建行ATM机给工商的账户转钱
**事务应该具有4个属性：**原子性、一致性、隔离性、持久性。这四个属性通常称为**ACID特性**。
原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（durability）。持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
3、如何用
# 先介绍事务的三个关键字 再去用表实际展示效果
start transaction;...
commit;
rollback;
create table user(idint primary key auto_increment,
name char(32),
balance int);
insert into user(name,balance)
values
('jason',1000),('egon',1000),('tank',1000);# 修改数据之前先开启事务操作
start transaction;# 修改操作
update user set balance=900 where name='jason';#买支付100元
update user set balance=1010 where name='egon';#中介拿走10元
update user set balance=1090 where name='tank';#卖家拿到90元# 回滚到上一个状态
rollback;# 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
commit;"""开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作"""# 站在python代码的角度，可以用以下代码去解释：try:
update user set balance=900 where name='jason';#买支付100元
update user set balance=1010 where name='egon';#中介拿走10元
update user set balance=1090 where name='tank';#卖家拿到90元except 异常:
rollback;else:
commit;
436
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
