四、Python-数据库(笔记)


返回

1、数据

  1. 数据
    • 描述事物特征的符号
  2. 记录
    • 数据
    • 文件
    • 文件夹
  3. 数据库管理软件
    • mysql、db2
    • 关系型:有表结构
    • 非关系型:key-value形式存储,没有表结构
  4. 数据库服务器
    • 运行数据库管理软件的计算机

2、mysql安装

1、登录root
* mysql -u root -p
2. 修改root密码
* mysqladmin -uroot -p password "密新码"
3. 密码破解(略)

3、统一字符编码

  1. 默认utf8mb4

4、SQL语句

  1. 操作文件夹(库)
    • create database 数据库名 charset gbk;

    • show create database 数据库名;
      show databases;

    • alter database 数据库名 charset utf8;

    • drop database 数据库名;

    • 切换库

      use 数据库名

    • 查看当前所在库

      select database();

  2. 操作文件(表)
    • create table 表名(字段 类型[(大小)], 字段 类型[(大小)]);
      create table 表名(id int, name char);
      ``

    • show create table 表名;
      show create table 表名\G;
      show tables;
      desc 表名;

    • alter table 表名 modify name char(8);
      alter table 表名 change name NAME char(6);

    • drop table t1;

  3. 操作文件内容(记录)
    • insert 表名(id,NAME) values(1,'name1'),(2,'name2'),(3,'name3');
      insert 表名 values(4,'name4');
      insert 表名(NAME,id) values('name5',5);

    • select id,NAME from 数据库名.表名;
      select * from 表名;

    • update 数据库名.表名 set NAME='小明';
      update 数据库名.表名 set NAME='张三' where id=2;

    • delete from 数据库名.表名 where id=2;
      delete from 数据库名.表名;清空表
      truncate 数据库名.表名清空表

  4. 三种类型
    1. DDL语句(数据库定义语言)
      • create创建库、表
      • alert修改库、表
      • drop删除库、表
    2. DML语句(数据库操作语言)
      • insert插入数据
      • delete删除数据
      • update更新数据
      • select查询数据
    3. DCL语句(数据库控制语言)
      • 控制用户访问权限
      • grant授权
      • revoke移除权限

5、存储引擎

  1. 存储引擎
    • 即表的类型
  2. 查看mysql支持的存储引擎
    • show engines;
  3. 指定表的存储引擎
    • create table 表名(id int, name char)engines=innodb;

6、表的增删改查

  1. 修改表结构
    • 修改表名

      alter table 表名 rename 新表名

    • 增加字段

      alter table 表名 add 字段名 类型[(约束条件)], add 字段名 类型[(约束条件)];
      alter table 表名 add 字段名 类型[(约束条件)] first;
      alter table 表名 add 字段名 类型[(约束条件)] after 字段名;

    • 删除字段

      alter table 表名 drop 字段名;

    • 修改字段

      alter table 表名 modify 字段名 类型[(约束条件)];
      alter table 表名 change 旧字段名 新字段名 类型[(约束条件)];

  2. 复制表
    • create table 表名 select 字段名,字段名 from 表名;
  3. 复制表结构
    • create table 表名 like 表名;

7、数据类型

  1. 数字类型
    • 整型

      宽度指的是显示宽度

    • 浮点型

      float(255,30)
      double(255,30)
      decimal(65,30)

  2. 日期类型
    • year
    • date
    • time
    • datetime(1001-9999)8字节
    • timestamp(1970-2038)4字节
  3. 字符类型
    • char:定长

      宽度指的是字符宽度
      字符+空格
      优点:存取速度快
      缺点:更占用空间

    • varchar:变长

      1bytes+字符
      优点:节省空间
      缺点:存取速度慢

  4. 枚举类型
    • enum(1,2,3)
    • 不在范围内的数据,传空值
  5. 集合类型
    • set(1,2,3)
    • 不在范围内的数据,传空值

8、约束条件

  1. Null、Default

    Null默认YES
    Default默认Null
    create table t1(sex enum('male','female') not null default 'male');

  2. Key
    • unique key(唯一)
    • 单列唯一

      create table t1(id int unique, name char unique);
      create table t1(id int, name char, unique(id), unique(name));

    • 联合唯一

      create table t1(ip char(15), port int, unique(ip, port));

    • primary key(不为空、且唯一)主键
    • 单列主键

      create table t1(id int not null unique);
      create table t1(id int primary key);
      create table t1(id int, primary key(int));

    • 复合主键

      create table t1(ip char(15), port int, primary key(ip, port));

  3. Extra
    • auto_increment(自增长,默认起始值1,默认步长1)
    • 必须是key

      create table t1(id int primary key auto_increment);

9、建立表之间的关系

  1. 多对一
    • foreign key(字段名) references 表名(字段名)

      先建立被关联的表,并保证被关联的字段唯一
      后建立主表
      create table t1(id int primary key, d_id int, foreign key(d_id) references dep(id));
      删除:先删除主表字段,在删除被关联表的对应字段
      直接删除被关联表字段,需要增加约束on delete cascade on update cascade
      create table t1(id int primary key, d_id int, foreign key(d_id) references dep(id)) on delete cascade on update cascade;

  2. 多对多
    • 单独建立关系表
  3. 一对多
    • foreign key + unique

10、单表查询

  1. 语法顺序
    • select distinct 字段1,字段2,字段3 from 库名.表名
    • where 条件
    • group by 分组条件
    • having 过滤
    • order by 排序字段
    • limit n;
  2. distant 避免重复
  3. 四则运算查询
    • select 字段名*12 as 新字段名 from 表名
  4. 定义显示格式
    • select concat('名字:',字段名,'性别:',字段名) as 新名 from 表名
    • select concat(字段名,':',字段名,':',字段名) as 新名 from 表名
    • select concat_ws(':',字段名,字段名,字段名) as 新名 from 表名
  5. where
    • where不能用聚合函数
    • 比较运算符

      >|<|>=|<=|<>|!=

    • between A and B
    • in(A,B,C)
    • like

      like 'abc%'
      like 'abc__

    • 逻辑运算符

      and
      not
      or

  6. group by
    • set global sql_mode='ONLY_FULL_GROUP_BY'只能取分组的字段
    • 聚合函数

      max
      min
      avg
      sum
      count

    • select 字段A, count(字段B) as 字段B别名 from 表名 group by 字段A;
    • 对unique字段分组没有意义
    • 分组之后,只能取分组的字段,和聚合函数结果
    • 没有group by,则默认整体为一个组,也可以用聚合函数
    • group_concat 拼接

      select 字段A,group_concat(字段B) from 表名 group by 字段A

  7. having
    • 分组之后过滤
    • 可以用聚合函数
  8. order by
    • 排序
    • 默认升序

      order by 字段A,字段B asc先排序A,A相同则按B排序

    • 降序

      order by 字段名 desc

  9. limit
    • 限制显示条数
    • limit a,b从第a个开始,往后显示b条记录
  10. 正则表达式
    • where 字段名 regexp '正则表达式';

11、多表查询

  1. 连表操作
    • 内连接:只取两张表的共同部分

      select * from 表A inner join 表B on 表A.id = 表B.id;

    • 左连接:在内连接的基础上保留左边表的记录

      select * from 表A left join 表B on 表A.id = 表B.id;

    • 右连接:在内连接的基础上保留左边表的记录

      select * from 表A right join 表B on 表A.id = 表B.id;

    • 全外连接:在内连接的基础上保留左、右两边表没有对应的记录

      select * from 表A full join 表B on 表A.id = 表B.id;
      左连接 union 右连接

  2. select完整语句
    • select 7
    • distinct <select_list> 8
    • from <左表> 1
    • <join_type> join <右表> 3
    • on <条件> 2
    • where <条件> 4
    • group by <字段名> 5
    • having <条件> 6
    • order by <字段名> 9
    • limit <条件> 10

12、子查询

  1. 把select语句放到括号里
    • in
    • 比较运算符
    • exists

13、权限管理

  1. 创建账号
    • 本地账号

      create user '用户1'@‘localhost’ identified by '密码'
      登录:mysql -u用户1 -p密码

    • 远程账号

      create user '用户2'@‘192.168.1.10’ identified by '密码'
      create user '用户3'@‘192.168.1.%’ identified by '密码'
      create user '用户4'@‘%’ identified by '密码'
      登录:mysql -u用户 -p密码 -h 服务端IP

  2. 授权
    • user:.
    • db:库.*
    • tables_priv:库.表
    • columns_priv:字段A,字段B

      grant all on *.* to '用户'@'localhost'
      grant select on 库.* to '用户'@'localhost'
      revoke select on *.* from '用户'@'localhost'
      grant select(字段A),update(字段B) on 库.表 to '用户'@'localhost'

    • flush privileges;

14、pymysql

  1. 例子
    import pymysql
    
    # 建立连接
    conn = pymysql.connect(
        host='localhost',
        port='3306',
        usr='root',
        password='699050',
        db='db1',
        charset='utf8'
    )
    
    # 拿到游标
    cursor = conn.cursor()
    
    # 执行sql语句
    usr = input('usr:')
    pwd = input('pwd:')
    # sql = 'select * from usr_info where id = %s, pwd = %s;' % (usr, pwd)
    # rows = cursor.execute(sql)
    # sql注入
    sql = 'select * from usr_info where id = %s, pwd = %s;'
    rows = cursor.execute(sql, (usr, pwd))
    cursor.close()
    conn.close()
    
    # 判断
    if rows:
        print('登录成功')
    else:
        print('登录失败')
    
  2. 视图
    • 重命名连表结果

      create view 视图名 as 连表
      alter view 视图名 as 新连表
      drop view 视图名

  3. 触发器
    • 被动触发,表的增、删、改引起触发器
    • 创建
      delimiter //
      create triggger 触发器名 after insert on 表名 for each row
      begin
          if new.字段 = 值 then
              执行语句
          end if;
      end//
      delimiter ;
      
    • 删除

      drop trigger 触发器名;

  4. 存储过程
    • 创建无参存储过程
      delimiter //
      create procedure p1()
      begin
          SQL语句;
          if 条件1 then
              执行语句;
          elif 条件2 then
              执行语句;
          else
              执行语句;
          end if;
      end //
      delimiter ;
      
    • 使用

      mysqlcall p1();
      pythoncursor.callproc('p1')

    • 创建有参存储过程
      delimiter //
      create procedure p1(in n1 int,out res int,inout n3 int)
      begin
          SQL语句;
          set res=1
      end //
      delimiter ;
      
    • 使用

      mysqlset @x=0 call p1(1,@x); select @x
      python
      py cursor.callproc('p1',(1,0)) # @_p1_0=1,@_p1_1=0 cursor.execute('select @_p1_1')

  5. 事务
    start transaction;
    SQL语句1;
    SQL语句2;
    rollback;
    commit;
    
  6. 函数
返回