mysql高级

mysql 高级语法

一,mysql查询

.1.1 mysql查询完整语法

mysql查询完整语法
select [选项]字段表达式 from 子句,单张表名,多张表名 where 子句,条件表达式 group by 子句 having 子句 order by 子句 limit 子句

注:每个子句可以不写,但一定要写在正确的位置,不可颠倒前后顺序

1.1 选项

	1.	all 选项
		作用：显示全部记录，默认选项。
    2.	distinct选项
		作用：去掉重复的记录。
		位置：字段列表与select 中间。

1.2字段表达式

1,* 表示通配符
2,字段列表,如果同时查询多张表中的字段,则用表名.字段名格式列出
3,as 列别名,可以不写
4,聚合函数,如:count(),min(),max(),sum(),avg(),一般与group by 一起用

1.3 from 子句

1,from 表名
2,from 表1,表2,表3,
3,给表列别名 from 表1 别名1

1.4 where 子句

1,基础语法:
 	where 条件表达式
2,关系运算符
  > 大于
  <小于
  >=,大于等于
  <=,小于等于
  =等于
  != ,<> 不等于
  例:select * from student where name='张三'#查名字等于张三的学生信息
3,like 查询,模糊查询
like '%__'
例:select * from student where name like '张_'
4,in,not in ,判断字段值是否在一个集合类
select [选项] 字段表达式 from 子句 where 字段 in|not in 集合
例: select * from student where age in (19,20,30);
4,between and ,not between and,判断值是否在区间内
select [选项] 字段表达式 from 子句 where 字段 between 值1 and 值2;
例:select * from student where age between 18 and 30;
5,is null,is not null,判断值是否为空
例: select * from student where age is null
6逻辑运算符 and ,or not 
例:select * from student where not name='张三'--->查询除了张三之外的所有人的信息

1.5group by 子句

1 group by 字段,以字段作为分组条件,group by 一般与聚合函数一起使用,代表分组统计
例:
	select count(*) from student group by class_id --->统计每个班的人数
	

1.6having 子句

having 一般与group by 一起用,是对分组后的查询结果再一次过滤,其左右与where 相同

1.7order by 子句

order by 是对查询的结果进行排序
语法: order by 字段 选项,字段 选项

1.8limit 子句

Limit 子句用于限制结果数量
语法： limit [$start, ] $size;
如 select * from student limit 0,3;将查询结果每次显示3条

1.9联合查询

1,联合查询的应用场景:
	1).多个表的查询结果合并。
	2).一个表的按照不同的排序方案查询合并。
2,联合查询语法
select 查询语句
union [选项 all|distinct(默认)]
select 查询语句
union [选项 all|distinct(默认)]
select 查询语句
如:
(select * from student where sex='男' ORDER BY age DESC limit 100)
UNION
(select * from student where sex='女' order by age asc limit 100);
3,注意点
	1).UNION联合查询返回的列名是由第一个SELECT查询的字段名确定。
	2).所有的SELECT查询的字段数量必须一致，类型不做要求。
	3).有排序需求时，每个SELECT语句需要使用()和LIMIT子句。

1.10子查询

1,定义:子查询是指：一个查询语句出现在另一个语句内，就是子查询语句！
2,分类:按位置分:where 子查询
		      from 子查询
		按查询结果分:列子查询
					行子查询
					表子查询
3,where子查询 
  where select 查询语句
4,from 子查询
 from select 查询语句
 注:1,from 子句中查询语句必须用() 包裹,且取一个别名,才能做完临时表使用
5,列子查询:查询出来的结果为一列多行
案例: 查询学生表中的年龄排前5的学生, 要求结果是按照年龄升序排列.
select * from student ORDER BY age DESC limit 0,5;
select * from (select * from student ORDER BY age DESC limit 0,5) as t order by age asc;
6,行子查询:查询出来的结果为一行多列,需要使用 小括号() 行构建符, 将多个字段的值构建成一行, 与行子查询的结果做对比.
案例: 请问学生中是否有班级id最大且年龄也最大的学生?
select max(class_id),max(age) from student

select * from student where (class_id,age)=(select max(class_id),max(age) from student)
7,表子查询:子句查询出来的结果为多行多列
案例: 查询每个班中年龄最大的学生的信息.
select class_id ,max(age) from student GROUP BY class_id
select * from student where (class_id,age) in (select class_id ,max(age) from student GROUP BY class_id)

1.11 索引

1. 什么是索引
	 索引可以理解为新华字典的目录, 作用是 大大大的提高查询效率.

2. 常见索引
		主索引(主键 primary key)
		唯一索引(唯一键 unique key)
		普通索引(普通键 key)
		全文索引(fulltext)

二 外键约束

概念: 外键约束指两个表之间的跨表的条件约束
要求:
  1. 表引擎必须为 innodb
  2. 分为主表(parent table)和从表(child table), 从表引用主表数据;
  3. 要求主表和从表关联字段的数据类型严格一致.(大小,类型是否有无符号 都要一致)
  
  
外键约束作用:
  主要是保证主表和从表数据的完整性:
  1. 从表的关联字段不能随意添加数据,必须以主表为准
  2. 主表的关联字段不能随意修改和删除(从表如果使用就不能随意动)

外键使用的语法:
  1. 添加外键
     alter table 从表名 add [constraint `约束名`] foreign key (从表关联字段)
     references 主表名(主表关联字段) [高级语法];
     案例: 学生表中添加外键
    alter table student add foreign key(class_id) references class(class_id);
        # 查看外键是否添加成功
    show create table student;
  2. 删除外键
    alter table 从表名 drop foreign key 约束名字;

    删除学生表中的外键;
    alter table student drop foreign key `student_ibfk_1`
   
 3 高级语法
    alter table 从表名 add [constraint `约束名`] foreign key (从表关联字段)
     references 主表名(主表关联字段)
     [on update 模式 on  delete 模式];

    模式三种：
        1. 默认模式：strict 严格
        2. 级联模式：cascade , 如果主表字段发生改变， 从表跟着一起改变
        3. 置空模式：set null, 如果主表字段发生改变， 从表设置为空

    学生表添加一个外键: 要求主表更新从表一起更新,主表删除从表设置为null
    alter table student add foreign key (class_id) references class(class_id)
    on update cascade on delete set null ;

三,事物的用法

概念: 事务是指连续不可分割的多条sql语句,要么全部都执行成功,要么全部回滚到初始状态.

作用: 事务是保证数据完整性,一致性,安全性的重要技术.

事务操作 三步:
1. 开启事务: begin; start transaction;
2. 提交事务: commit;
3. 回滚事务: rollback;
事务的特点，ACID

事务的四个特性：原子性，一致性，隔离性，持久性！
1）原子性（Atomicity）
	事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
2）一致性（Consistency）
	事务前后数据的完整性必须保持一致。
3）隔离性（Isolation）
	多个用户并发访问数据库时，一个用户的事务不能被其它用户的事物所干扰，多个并发事务之间的数据要相互隔离。
4）持久性（Durability）
	一个事务一旦被提交，它对数据库中的数据改变就是永久性的。

四,实体关系

三种关系:
一对一:
      使用相同的主键即作为各自主键也作用关联字段
一对多:
       在多的一方的表中设计一个字段保存一的一方的主键id.
多对多:
        新建一张中间关系表,保存两个表的主键id

五,pycharm连接数据库

1,基本步骤;
1)导入pymsql模块
2)根据pymysql创建连接对象
3)根据连接对象创建游标对象
4)准备sql数据
5)用游标对象执行sql数据
6)用游标对象获取数据
7)关闭游标对象和连接对象
5.1. 创建连接对象
    语法:
    连接对象 = pymysql.connect(
    host="连接地址 localhost, ip 地址",
    port=端口号,默认3306,
    user="用户名",
    password="密码",
    database="数据库名",
    charset="utf8"
    )
5.2 使用连接对象
  a. 创建游标对象
      游标对象 = 连接对象.cursor(cursor=None)
      参数说明: 指定游标的类型
       `Cursor`: 默认
       `SSCursor`: 默认基础上采用生成器
       `DictCursor`: 字典类型游标
       `SSDictCursor`: 在字典采用生成器方式

        例如: cnn.cursor(cursor=pymysql.cursors.SSDictCursor)#创建游标对象时,也可以直接输入游标类型,然后用alt+enter键反导模块

    b. 切换数据库
        数据库连接对象.select_db(数据库名)

    c. 关闭连接
        连接对象.close()

    d. 事务操作的方法
        连接对象.begin(): 开启事务,默认开启
        连接对象.commit(): 提交事务
        连接对象.rollback(): 回滚事务

        注意: pymysql 鉴于安全考虑,所有的写(insert,delete,update)操作都必须使用事务
5.3 游标对象的使用
1,游标对象用于执行sql语句
    语法:
        游标对象.execute(sql,args=None)
    传参数的方式说明:
        1. 如果是完整的sql语句, 就直接传第一个参数即可
            备注: 如果通过传入的形式拼接的sql语句,容易造成sql注入的风险.
            sql注入: 恶意用户构建特殊sql参数传入,从而非法获取数据.

        2. 如果sql语句是使用 %s 占位的时候,必须使用第二个参数args(实际的数据)
            例子: sql = "select * from student where name=%s and age=%s"
                  args =  ["张飞",51] #列表或者元组
                  游标对象.execute(sql,args)

    了解:
        游标对象.executemany(sql,args=[(数据1,数据2),(数据1,数据2),(数据1,数据2)])
        同一条sql执行多次

2. 游标对象获取数据
    游标对象.fetchall(): 获取所有
    游标对象.fetchone(): 获取下一条(每执行一次获取一条)
    游标对象.fetchmany(number): 获取指定条数据

3. 游标的关闭
    游标对象.close()

    备注: 建议每次使用完游标执行和获取数据之后立马关闭游标

    提供一个上下文管理器的语法, 自动关闭(任何打开的东西)
    with 连接对象.cursor() as cursor:
        # 使用游标cursor  执行和获取数据, 自动关闭游标

    文件操作
    with open("./data.txt","r",encoding="utf-8") as fp:
        fp.read()
"""

案例:
# 导入pymysql
import pymysql
from config import db#连接参数

# 创建连接对象
cnn = pymysql.connect(**db)

# 上下文管理器
with cnn.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
    # 准备sql
    sql = "insert into student(name,age,sex,class_id,money) values (%s,%s,%s,%s,%s)"
    args = [
        ("小黄黄1", 25, "男", 2, 250),
        ("小黄黄2", 25, "男", 2, 250),
        ("小黄黄3", 25, "男", 2, 250)
    ]
    # 一次执行多条数据
    num = cursor.executemany(sql, args)
    print(num)
    if num > 0:
        # 提交事务
        cnn.commit()
        print("添加成功!")
    # 自动关闭
    cnn.close()