MySQL：记录的增删改查、单表查询、约束条件、多表查询、连表、子查询、pymysql模块、MySQL内置功能

数据操作

插入数据（记录）： 用insert；

补充：插入查询结果： insert into 表名(字段1,字段2,...字段n) select (字段1,字段2,...字段n) where ...;

更新数据update

语法： update 表名 set 字段1=值1,字段2=值2 where condition;

删除数据delete：delete from 表名 where condition;

 

查询数据select：

单表查询：

语法：

 select distinct 字段1,字段2... from 表名

　　　　　　　　　　　　where 条件

　　　　　　　　　　　　group by field

　　　　　　　　　　　　having 筛选

　　　　　　　　　　　　order by field

　　　　　　　　　　　　limit 限制条数;

关键字的执行优先级：

from
where
group by
having
select
distinct
order by
limit

# 1.找到表：from
# 2.通过where指定的约束条件，去文件/表中取出一条条记录
# 3.将取出的一条条记录进程分组 group by，如果没有group by，则整体作为一组
# 4.将分组的结果进行having过滤
# 5.执行 select
# 6.去重
# 7.将结果按顺序排序：order by
# 8.限制结果的显示条数

简单查询：

#创建表
create table employee(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male',  # 大部分是男的
age int(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 employee(name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), 
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','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)
;

查询操作：

# 避免重复 distinct
select distinct post from employee;

# 通过四则运算查询
select name,salary*12 from employee;
select name,salary*12 as Annual_salary from employee;
select name,salary*12 Annual_salary from employee;  # as Annual_salary是给 salary*12 起了一个别名；as 可省略

# 定义显示格式 （只是改变了显示格式，不会改变数据在数据库的保存格式）
   concat() 函数用于链接字符串
select concat("员工号：",id,"，","姓名：",name) as info,concat("年薪:",salary*12) as annual_salary from employee;
   concat_ws()  # 第一个参数可以作为分隔符
select concat_ws(":",name,salary*12) as annual_salary from employee;   
 

 

where约束：

where语句中可以使用：
    1. 比较运算符：>、<、>=、<=、 !=、（ <>也表示不等于）
    2. between 10 and 20   # 值在10到20之间
    3. in(80,90,100)  # 值是80或90或100
    4. like "neo%"
            pattern可以是%或_，
            %表示任意个任意字符
            _表示一个任意字符
    5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and, or, not

主要用法：

where约束：

# 单条件查询：
select name from employee where post="sale";

# 多条件查询：
select name,salary from employee where post="teacher" and salary>10000;

# 关键字between and
select name,salary from employee where salary between 10000 and 20000;
select name,salary from employee where salary not between 10000 and 20000;

# 关键字 is Null：（判断某个字段是否为NULL不能用等号，要用is）
select name,post_comment from employee where post_comment is Null;
select name,post_comment from employee where post_comment is not Null;
# MySQL中，空字符串不等于 NULL，NULL是单独的数据类型；判断Null的时候必须用 is，如： where id is Null; 

# 关键字in集合查询：
select name,salary from employee where salary in (3000,4000,9000);
select name,salary from employee where salary not in (3000,4000,9000);

# 关键字like模糊查询：
通配符：%
select * from employee where name like "eg%";
通配符：_
select * from employee where name like "ale_";

分组查询：group by

# 分组发生在where之后，即分组是基于where之后得到的记录而进行的
# 分组指的是将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等

 

ONLY_FULL_GROUP_BY

# 查看MySQL 5.7默认的sql_mode如下：
mysql> select @@global.sql_mode;
#ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

# 如果不设置ONLY_FULL_GROUP_BY,select的查询结果默认值是组内的第一条记录，这样显然是没有意义的；

# 设置 ONLY_FULL_GROUP_BY模式：
set global sql_mode="ONLY_FULL_GROUP_BY";

# 注意： ONLY_FULL_GROUP_BY 的语义就是确定 select target list中的多有的值都是明确语义，简单来说，在ONLY_FULL_GROUP_BY模式下，target list中的值要么来自聚合函数的结果，要么来自 group by list中的表达式的值（group_concat）

# 去掉ONLY_FULL_GROUP_BY模式的设置方法：
mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

 

聚合函数

# 聚合函数聚合的是组的内容；如果没有进行 group by分组，则默认所以记录是一组，所以此时也能用聚合函数
max()
min()
avg()
sum()
count()

示例：

select post,count(id) from employee group by post; # 只能查看分组依据的字段和使用聚合函数
# 注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数

# group by关键字和 group_concat() 函数一起使用
select post,group_concat(name) as emp_members from employee group by post;  # 按照岗位分组，并查看组内成员名

# group by和聚合函数一起使用
select post,avg(salary) as average_salary from employee group by post; # 按照岗位分组，并查看每个组的平均工资

# 没有分组的聚合函数：
select count(*) from employee;
select avg(salary) from employee;

另外：如果我们用unique的字段作为分组的依据，则每条记录自成一组，这种分组也就没了意义；多条记录之间的某个字段值相同，该字段通常用来作为分组的依据

 

having过滤：

# having和where不一样的地方：
# 1. 执行优先级：where>group by >having
# 2. where 发生在分组 group by 之前，因而where中可以有任意字段，但是绝对不能使用聚合函数
# 3. having发生在分组group by之后，因而having中可以使用分组的字段，但却无法直接取到其他字段，其他字段需要使用聚合函数

having中也可以用where中的逻辑，例如 and,or 等；having 跟where 用法一样，只不过having是分组之后的过滤

错误用法示例：

mysql> select * from employee having salary > 100000;
ERROR 1463 (42000): Non-grouping field 'salary' is used in HAVING clause  # 报错； having前面必须要有 group by

mysql> select post,group_concat(name) from employee group by post having salary > 10000; #错误，分组后无法直接取到salary字段
ERROR 1054 (42S22): Unknown column 'salary' in 'having clause'

正确用法如下：

# 1. 查询各岗位平均薪资大于10000的岗位名、平均工资
select post,avg(salary) as average_salary from employee group by post having avg(salary) > 10000;

# 2. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
select post,avg(salary) as average_salary from employee group by post having avg(salary) between 10000 and 20000;

# having的用法就是英语里面的定语从句

 

order by排序：

select * from employee order by 字段 asc;  #升序排；默认
select * from employee order by 字段 desc;  #降序排

order by 字段1 asc,字段2 desc;  # 先按照字段1升序排，如果字段1的值相同则按照字段2降序排
e.g. select * from employee order by age asc,id desc;

执行顺序证明：

select distinct post,count(id) as emp_number from db1.employee
    where salary>1000
    group by post
    having count(id)>2  # having中的count(id)不能用 emp_number 来代替，因为是先执行 having后执行 distinct，所以此时还没有 emp_number这个东西
    order by emp_number desc  # order by 中的count(id) 可以用 emp_number来代替，因为是先执行distinct后执行的order，执行完distinct之后就已经有了 emp_number
    ;

# 所以，优先级顺序是： from > where > group by > having > distinct > order by

 

limit限制条数：不管是书写顺序还是执行顺序，limit都是在最后

select * from employee limit 3;  # 3是限制条数；默认初始位置为0
select * from employee limit 0,3;  # 从0开始打印3个 （不包含0）

# 工资最高的那三个人的信息：
select * from employee order by salary desc limit 3;

#  分页打印：
select * from employee limit 0,5;
select * from employee limit 5,5;
select * from employee limit 10,5;
select * from employee limit 15,5;

 

正则查询regexp： （regexp应该是regular expressioin的缩写吧）

# select * from employee where name regexp "^jin.*(g|n)$";   # jin开头，并且 g或者n结尾

 

多表查询：（本质就是连表，通过连表将多张有关系的表连接在一起，得到一张虚拟表）

先建两个表，用于下面所有的操作测试

# 建表
create table department(
id int,
name varchar(20) 
);

create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;

 连接方式：

# 1. 内连接： 只取两张表的共同部分
select * from employee inner join department on employee.dep_id = department.id;  # 表employee内连接到表department，按照表employee中dep_id字段等于表department中id字段的方式连接

# 2. 左链接：在内链接的基础上保留左表的记录
select * from employee left join department on employee.dep_id = department.id;

# 3. 右链接：在内链接的基础上保留右表的记录
select * from employee right join department on employee.dep_id = department.id;

# 4. 全外链接： 在内连接的基础上左右两表的记录都保存
select * from employee left join department on employee.dep_id = department.id union select * from employee right join department on employee.dep_id = department.id;

内连接：

左连接：

右连接：

全外连接：

 

多表查询示例：

笛卡尔积：

select * from employee,department;

 

多表查询原理：

select * from employee inner join department on employee.dep_id = department.id;

# 通过这种方式能得到一个整合了表employee和表department的虚拟表

# 再对上面得到的虚拟表进行操作
select department.name,avg(age) from employee inner join department on employee.dep_id = department.id group by department.name having avg(age) > 30;  

# 多表查询：把有关系的表通过连接的方式拼成一个整体（虚拟表），进而进行相应的关联查询（因为此时已经是一长表了）

 

SQL逻辑查询语句执行顺序：

一、SELECT语句关键字的定义顺序：

select distinct <select_list>
    from <left_table>
    <join type> join <right_table> 
    on <join_condition>
    where <where_condition>
    group by <group_by_list>
    having <having_condition>
    order by <order_by_condition>
    limit <limit_number>;

二、SELECT语句关键字的执行顺序：

第一步： from <left_table>
第二步： on <join_condition>
第三步： <join_type> join  <right_table>
第四步： where <where_condition>
第五步： group by <group_by_list>
第六步： having <having_condition>
第七步： select
第八步： distinct <select_list>
第九步： order by <order_by_condition>
第十步： limit <limit_number>

具体可参考： http://www.cnblogs.com/linhaifeng/articles/7372774.html

 

子查询：

1. 带 in 关键字的查询：

# 查询平均年龄在25岁以上的部门名
select name from department where id in
(select dep_id from employee group by dep_id having avg(age) > 25);
# (select dep_id from employee group by dep_id having avg(age) > 25)会有一个返回值，符合过滤条件的 dep_id；where id in (select dep_id from employee group by dep_id having avg(age) > 25) 就类似于 where id in (1,2,3)

# 查看技术部员工姓名
select name from employee where dep_id =
(select id from department where name="技术");

# 查看不足一人的部门名
# 分析：不足1人就是没有人
select name from department where id not in
(select distinct dep_id from employee) ;
# (select distinct dep_id from employee) 通过去重得到有人的部门id， where id not in ...取反，即 department的id没有在有人的部门id里面

2. 带比较运算符的子查询

# 查询大于所有人平均年龄的员工名和年龄
select name,age from employee where age >
(select avg(age) from employee);
# where后面不能直接写成 where age > avg(age)，因为where里面不能使用聚合函数；所以先通过 (select avg(age) from employee)拿到 avg(age)

3. 带exists关键字的子查询 （exists是用于判断是否存在的，返回的类似于bool值）

select * from employee where exists
(select id from department where name="技术");  # 如果(select id from department where name="技术")成立（存在，此时where exists语句返回True），就执行 select * from employee; 如果不存在，就不执行select * 语句
# exists也可以not 取反

 

select 查询语句可以用括号括起来，再用 as 起一个别名，就能当作一张表（临时表）来使用,如下：

select * from (select name,age,sex from employee) as t1;

以另外一张employee表为例说明：

# 查询每个部门最新入职的那名员工

报错：
select * from employee as t1 
inner join
(select post,max(hire_date) from employee group by post) as t2 
on t1.post=t2.post 
where t1.hire_date=t2.max(hire_date);   # 报错原因：where中不能有聚合函数

正确：
select * from employee as t1
inner join
(select post,max(hire_date) as new_hire from employee group by post) as t2
on t1.post = t2.post
where t1.hire_date = t2.new_hire;  # 取别名后就是单纯的调用了

 

权限管理：略

 

Navicat工具：

批量加注释：ctrl+？键

批量去注释：ctrl+shift+？键

 

pymysql模块

pymysql基本使用：

通过pymysql模块能够在python程序中操作MySQL数据库；pymysql模块本质就是一个套接字客户端软件

import pymysql

username = input("username>>>:").strip()
password = input("password>>>:").strip()


# 建链接 
conn = pymysql.connect(host="192.168.18.2",port=3306,user="root",password="123",db="db4",charset="utf8")  # 得到一个链接对象； # charset中的utf8不能加 - ，因为mysql中没加

# 拿到一个游标（cursor)
cursor = conn.cursor()  # 得到一个游标对象

# 给游标提交命令，执行sql语句
sql = "select * from userinfo where username='%s' and password='%s' " %(username,password)  # sql语句中的username和password要和db4.userinfo这张表中的字段一样
print(sql)

rows = cursor.execute(sql) # 把sql语句提交给cursor去执行； #  execute() 不是执行的结果，而是受影响的行数（rows）

cursor.close()
conn.close()  # 把资源回收


# 进行判断
if rows:
    print("登录成功")
else:
    print("登录失败")

但上面的程序有一个漏洞：

# 在MySQL中， --空格 后面的内容都会被注释掉（两个横杠后面跟一个空格），所以在你的python程序中输入：
username>>>:neo' -- xxxx
不输密码，也能够成功登录

并且

# 输入：
username>>>:xxx' or 1=1 -- hahahaah
不输密码，也可以登录 

解决办法：利用pymysql模块的sql注入

 

pymysql模块之sql注入：

import pymysql

username = input("username>>>:").strip()
password = input("password>>>:").strip()


# 建链接 
conn = pymysql.connect(host="192.168.18.2",port=3306,user="root",password="123",db="db4",charset="utf8")  

# 拿到一个游标（cursor)
cursor = conn.cursor()  # 得到一个游标对象

# 给游标提交命令，执行sql语句
sql = “select * from userinfo where username=%s and password=%s”  # 不要自己拼接字符串，利用 pymysql的execute拼接字符串； # 占位符也不要再加引号
rows = cursor.execute(sql,(username,password))  # 第一个参数还是传入要执行的sql语句；第二个参数传入一个元组，元组里面放入sql语句里面的占位符，通过这种方式拼接字符串，能把其中的特殊字符处理掉

cursor.close()
conn.close()  


# 进行判断
if rows:
    print("登录成功")
else:
    print("登录失败")

 

pymysql模块之增删改：

import pymysql

# 建链接
conn = pymysql.connect(host="192.168.18.2",port=3306,user="root",password="123",db="db4",charset="utf8")

# 拿到游标
cursor = conn.cursor()

# 执行sql语句
# 增删改
sql = "insert userinfo(username,password) values(%s,%s)"
print(sql)
rows = cursor.execute(sql,("abc","123"))

conn.commit()  # 修改的数据要生效，必须在cursor.conn关闭之前 conn.commit()

# 关闭
cursor.close()
conn.close()

插入多条记录：

# 插入多条记录
rows = cursor.executemany(sql,[("egon1","456"),("egon2","123"),("egon3","789")])   # 利用executemany(),列表中放入多个元组

lastrowid用法：查询你即将插入的数据是从第几行开始的

import pymysql

# 建链接
conn = pymysql.connect(host="192.168.18.2",port=3306,user="root",password="123",db="db4",charset="utf8")

# 拿到游标
cursor = conn.cursor()

# 执行sql语句
sql = "insert userinfo(username,password) values(%s,%s)"


# 插入多条记录
rows = cursor.executemany(sql,[("egon7","456"),("egon8","123"),("egon9","789")])   # 利用executemany(),列表中放入多个元组
print(cursor.lastrowid)  # cursor.lastrowid 是你上面代码插入的时候，是从第几行开始插入的

conn.commit()  # 修改的数据要生效，必须在cursor,conn关闭之前 conn.commit()

# 关闭
cursor.close()
conn.close()

删改就是把上述例子中的sql语句改成删改的sql语句就行了

 

pymysql模块之查询

import pymysql

conn = pymysql.connect(host="192.168.18.2",port=3306,user="root",password="123",db="db4",charset="utf8")

cursor = conn.cursor(pymysql.cursors.DictCursor)  # cursor()中如果什么都不写，查询出来的数据是元组的形式；如果指明了 pymysql.cursors.DictCursor,查询结果是字典的形式，字典的key是表的字段

rows = cursor.execute("select * from userinfo")
print(cursor.fetchone())
print(cursor.fetchone())
print(cursor.fetchone())
print(cursor.fetchone())
print(cursor.fetchone())
print(cursor.fetchone())
print(cursor.fetchone())
print(cursor.fetchone())
# 运行过程分析： cursor.execute("select * from userinfo")给MySQL服务端发送了查询语句，服务端查完之后把查询结果返回给服务端，服务端收到后把全部结果放到了管道里面，fetchone()一次就取出一条结果；取完之后再去就是None


# fetch还有两种用法：
# 1. cursor.fetchmany(3)  # 一次取3条;取出来的结果放到一个列表中，由于已经指定了 pymysql.cursors.DictCursor,所以列表中是一个个字典
# 2. cursor.fetchall()  # 一次全部取完,结果放到一个列表中;取完之后再fetchall会得到一个空列表

cursor.close()
conn.close()

fetchone:

fetchmany:

fetchall:

cursor.scroll用法：移动管道中的光标

import pymysql

conn = pymysql.connect(host="192.168.18.2",port=3306,user="root",password="123",db="db4",charset="utf8")

cursor = conn.cursor(pymysql.cursors.DictCursor) 

rows = cursor.execute("select * from userinfo")


# cursor.scroll(3,mode="absolute")  # 相对绝对位置移动：从管道最开始的位置跳过去3条
# cursor.scroll(3,mode="relative")  # 相对当前位置移动：从光标所在管道的当前位置跳过去3条

cursor.scroll(3,mode="absolute")
print(cursor.fetchone())  # 跳过前三条，直接从第四条开始取


cursor.close()
conn.close()

相对绝对位置移动

相对当前位置移动

print(cursor.fetchone())
cursor.scroll(3,mode="relative")
print(cursor.fetchone())   # 从第二条开始跳过取3个开始取

 

MySQL内置功能：

视图：

视图一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，可以将结果当作表来使用；但是不推荐使用视图，因为扩展SQL极不方便

创建视图：

# 语法： create view 视图名称 as sql语句
create view teacher_view as select tid from teacher where tname='李平老师';

#于是查询李平老师教授的课程名的sql可以改写为
mysql> select cname from course where teacher_id = (select tid from teacher_view);

 

 修改视图（往视图中插入数据），原始表也跟着改

修改视图：

语法：ALTER VIEW 视图名称 AS SQL语句
mysql> alter view teacher_view as select * from course where cid>3;

删除视图：

# 语法：DROP VIEW 视图名称

DROP VIEW teacher_view

 

函数：

date_format(date相关字段,date格式)  # 第一个参数写date的相关字段，第二个参数写所需要的date格式，如："%Y-%m-%d"；

datediff(current_date,sale_date)   #  current_date和sale_date之间的天数间隔

示例：

select item_id,count(distinct date_format(sale_date,"%Y-%m-%d")) as day_num from txn 
where datediff(current_date,sale_date) <=10
group by item_id 
having day_num >=5 
order by day_num desc;

# current_date也是一个函数，表示当天的日期

 

 

控制流函数： 

1、case when condition1 then result1 ... else default end

# 如果 conditionN是真，则返回 resultN，否则返回default

2、case test when value1 then result1... else default end

# 如果test 和valueN相等，则返回 resultN，否则返回default

如下：

查询班级信息，包括班级id、班级名称、年级、年级级别(1为低年级，2为中年级，3为高年级)

select cid as 班级id,caption as 班级名称,gname as 年级,
(case grade_id when 1 then "低" when 2 then "中" else "高" end) as "年级级别" from class inner join 
class_grade on class.grade_id=class_grade.gid;