MySQL2

MySQL

单表查询

• 查询语法

  SELECT 字段1,字段2... FROM 表名
                    WHERE 条件
                    GROUP BY 分组
                    HAVING 筛选
                    ORDER BY 排序
                    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.限制结果的显示条数
  

  #简单查询
      SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id 
      FROM employee;
  
      SELECT * FROM employee;
  
      SELECT name,salary FROM employee;
  
  #避免重复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; # 四则运算后重新命名
  
  #定义显示格式
     CONCAT() 函数用于连接字符串
     SELECT CONCAT('姓名: ',name,'  年薪: ', salary*12)  AS Annual_salary 
     FROM employee;
     
     CONCAT_WS() 第一个参数为分隔符
     SELECT CONCAT_WS(':',name,salary*12)  AS Annual_salary 
     FROM employee;
  
     结合CASE语句：
     SELECT
         (
             CASE
             WHEN NAME = 'egon' THEN
                 NAME
             WHEN NAME = 'alex' THEN
                 CONCAT(name,'_BIGSB')
             ELSE
                 concat(NAME, 'SB')
             END
         ) as new_name
     FROM
         emp;
  

  where

  1. 比较运算符：> < >= <= <> !=
  2. between 80 and 100 值在10到20之间
  3. in(80,90,100) 值是10或20或30
  4. like 'egon%'
      pattern可以是%或_，
      %表示任意多字符
      _表示一个字符
  5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
  
  #1:单条件查询
      SELECT name FROM employee
          WHERE post='sale';
          
  #2:多条件查询
      SELECT name,salary FROM employee
          WHERE post='teacher' AND salary>10000;
  
  #3:关键字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;
      
  #4:关键字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;
          
      SELECT name,post_comment FROM employee 
          WHERE post_comment=''; 注意''是空字符串，不是null
      ps：
          执行
          update employee set post_comment='' where id=2;
          再用上条查看，就会有结果了
  
  #5:关键字IN集合查询
      SELECT name,salary FROM employee 
          WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;
      
      SELECT name,salary FROM employee 
          WHERE salary IN (3000,3500,4000,9000) ;
  
      SELECT name,salary FROM employee 
          WHERE salary NOT IN (3000,3500,4000,9000) ;
  
  #6:关键字LIKE模糊查询
      通配符’%’
      SELECT * FROM employee 
              WHERE name LIKE 'eg%';
  
      通配符’_’
      SELECT * FROM employee 
              WHERE name LIKE 'al__';
  

  group by

  #1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的
  #2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等
  #3、为何要分组呢？
      取每个部门的最高工资
      取每个部门的员工数
      取男人数和女人数
  小窍门：‘每’这个字后面的字段，就是我们分组的依据
  #4、大前提：
      可以按照任意字段分组，但是分组完毕后，比如group by post，#只能查看post字段，如果想查看组内信息，需要借助于聚合函数
         
      独使用GROUP BY关键字分组
      SELECT post FROM employee GROUP BY post;
      注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数
  
  GROUP BY关键字和GROUP_CONCAT()函数一起使用
      SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照岗位分组，并查看组内成员名
      SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;
  
  GROUP BY与聚合函数一起使用
      select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人
      
  #强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组,即开始的时候是一整个大组
  示例：
      SELECT COUNT(*) FROM employee;
      SELECT COUNT(*) FROM employee WHERE depart_id=1;
      SELECT MAX(salary) FROM employee;
      SELECT MIN(salary) FROM employee;
      SELECT AVG(salary) FROM employee;
      SELECT SUM(salary) FROM employee;
      SELECT SUM(salary) FROM employee WHERE depart_id=3;
  """
  如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义
  多条记录之间的某个字段值相同，该字段通常用来作为分组的依据
  """
  

  having

  #！！！执行优先级从高到低：where > group by > having 
  #1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
  #2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
  

  order by 默认从小到大排序

  先按照age排序，如果年纪相同，则按照薪资排序   asc表示升序 desc 表示从降序
      SELECT * from employee
          ORDER BY age,
          salary DESC;
  

  limit

  SELECT * FROM employee ORDER BY salary DESC 
          LIMIT 3;                    #默认初始位置为0 
      
      SELECT * FROM employee ORDER BY salary DESC
          LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条
  
      SELECT * FROM employee ORDER BY salary DESC
          LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条
  

  正则限制

  SELECT * FROM employee WHERE name REGEXP '^ale';
  SELECT * FROM employee WHERE name REGEXP 'on$';
  SELECT * FROM employee WHERE name REGEXP 'm{2}';
  
  小结：对字符串匹配的方式
  WHERE name = 'egon';
  WHERE name LIKE 'yua%';
  WHERE name REGEXP 'on$';
  

多表查询

• 多表连接查询

  """
  SELECT 字段列表
      FROM 表1 INNER|LEFT|RIGHT JOIN 表2
      ON 表1.字段 = 表2.字段;SELECT 字段列表
      FROM 表1 INNER|LEFT|RIGHT JOIN 表2
      ON 表1.字段 = 表2.字段;
  """
  1.交叉连接 不适用任何匹配条件，生成笛卡尔积select * from t1,t2;
  2.内连接 只连接匹配的行 select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id; 
  3.外链接之左连接  优先显示左表全部记录，以左表为准，找出左表所有的信息，其本质是在内连接的基础上增加左边有右边没有的结果  select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;
  4.外链接之右连接 优先显示右表全部记录  其本质是在内连接的基础上增加右边有左边没有的结果  select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;
  5.全部连接 显示左右两个表全部记录      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;
  #注意 union与union all的区别：union会去掉相同的纪录
  

• 符合条件连接查询

  #示例1：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
  select employee.name,department.name from employee inner join department
      on employee.dep_id = department.id
      where age > 25;
  
  #示例2：以内连接的方式查询employee和department表，并且以age字段的升序方式显示
  select employee.id,employee.name,employee.age,department.name from employee,department
      where employee.dep_id = department.id
      and age > 25
      order by age asc;
  

• 子查询

  #1：子查询是将一个查询语句嵌套在另一个查询语句中。
  #2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
  #3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
  #4：还可以包含比较运算符：= 、 !=、> 、<等
  1.带in关键字的子查询
  查看技术部员工姓名 select name from employee where dep_id in (select id from department where name='技术');
  # 在 SQL 中 ANY 和 SOME 是同义词，SOME 的用法和功能和 ANY 一模一样。
  # all同any类似，只不过all表示的是所有，any表示任一
  2.带比较运算符的子查询  
  查询大于部门内平均年龄的员工名、年龄  select t1.name,t1.age from emp t1 inner join (select dep_id,avg(age) avg_age from emp group by dep_id) t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age;
  3.带exists关键字的子查询，使用这个关键字时，内层查询语句不返回查询的记录，而是返回True或False,当in和exists在查询效率上比较时，in查询的效率快于exists的查询效率,not exists查询的效率远远高与not in查询的效率。
  

• 关键字的定义顺序和执行顺序

  # 定义顺序
  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>
  # 执行顺序  根据下面的序号来
  (7)     SELECT 
  (8)     DISTINCT <select_list>
  (1)     FROM <left_table>
  (3)     <join_type> JOIN <right_table>
  (2)     ON <join_condition>
  (4)     WHERE <where_condition>
  (5)     GROUP BY <group_by_list>
  (6)     HAVING <having_condition>
  (9)     ORDER BY <order_by_condition>
  (10)    LIMIT <limit_number>
  

pymysql

Python操作MySQL主要使用两种方式 1.原生模块 pymsql  2.ORM框架 SQLAchemy

#链接
conn=pymysql.connect(host='localhost',user='root',password='123',database='egon',charset='utf8')
#游标
cursor=conn.cursor() #执行完毕返回的结果集默认以元组显示
#cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)  可以修改成默认为字典显示

#执行sql语句
sql='select * from userinfo where name="%s" and password="%s"' %(user,pwd) #注意%s需要加引号
print(sql)
res=cursor.execute(sql) #执行sql语句，返回sql查询成功的记录数目
print(res)

cursor.close()
conn.close()

execute之sql注入

在一条sql语句中如果遇到select * from t1 where id > 3 -- and name='egon';则--之后的条件被注释掉了
#1、sql注入之：用户存在，绕过密码
egon' -- 任意字符
#2、sql注入之：用户不存在，绕过用户与密码 ，无论用户是什么
xxx' or 1=1 -- 任意字符

#改写为（execute帮我们做字符串拼接，我们无需且一定不能再为%s加引号了）
sql="select * from userinfo where name=%s and password=%s" #！！！注意%s需要去掉引号，因为pymysql会自动为我们加上
res=cursor.execute(sql,[user,pwd]) #pymysql模块自动帮我们解决sql注入的问题，只要我们按照pymysql的规矩来。

conn.commit

import pymysql
#链接
conn=pymysql.connect(host='localhost',user='root',password='123',database='egon')
#游标
cursor=conn.cursor()

#执行sql语句
#part1
# sql='insert into userinfo(name,password) values("root","123456");'
# res=cursor.execute(sql) #执行sql语句，返回sql影响成功的行数
# print(res)

#part2
# sql='insert into userinfo(name,password) values(%s,%s);'
# res=cursor.execute(sql,("root","123456")) #执行sql语句，返回sql影响成功的行数
# print(res)

#part3
sql='insert into userinfo(name,password) values(%s,%s);'
res=cursor.executemany(sql,[("root","123456"),("lhf","12356"),("eee","156")]) #执行sql语句，返回sql影响成功的行数
print(res)

res = cursor.executemany("insert into student values(%s,%s)",[(6,'xxx'),(7,'yyy'),(8,'zzzz')]) # 注意在填充占位符时execute会默认自动填充，而不需要像python中进行占位符格式化，即需要像上面的格式填充，

print(cursor.lastrowid) #在插入语句后查看  ,获取最后一条数据的自增id
conn.commit() #提交后才发现表中插入记录成功
cursor.close()
conn.close()

========================================================================================
import pymysql
#链接
conn=pymysql.connect(host='localhost',user='root',password='123',database='egon')
#游标
cursor=conn.cursor()

#执行sql语句
sql='select * from userinfo;'
rows=cursor.execute(sql) #执行sql语句，返回sql影响成功的行数rows,将结果放入一个集合，等待被查询

# cursor.scroll(3,mode='absolute') # 相对绝对位置移动即表里面最开始的位置
# cursor.scroll(3,mode='relative') # 相对当前位置移动
res1=cursor.fetchone()   # 取出一个值
res2=cursor.fetchone()
res3=cursor.fetchone()
res4=cursor.fetchmany(2)  # 取出多个  2个
res5=cursor.fetchall()   # 取出剩下的所有，可以将游标移动，重新获取多个值

SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。# 详略，见业内大佬博客