test

day11_mysql

1. 存储引擎

1.1 innodb

默认的存储引擎
支持事务, 外键, 行级锁
存储为2个文件: 表结构和数据

1.2 myisam

支持表级锁

2. 基础数据类型

int tinyint float(m, n)
char(n) varchar(n)
    * 用char搜索快
 datetime date time
 enum("选项1", "选项2") set("选项")

3. 约束

3.1 默认约束 default

3.2 非空约束 not all

3.3 唯一约束 unique

3.4 自增约束

auto_increment == not null unique auto_increment
自动非空约束
依赖唯一约束

3.5 主键约束

primary key == 第一列 not null unique
每张表只能有一个主键

3.6 联合唯一/联合主键

:::tips
联合唯一:
案例1: ip + port 不能重复, 各自可以重复
unique(ip, port)
:::

:::tips
联合主键：
案例2: 用的非常少, 因为一张表一个主键
create table server_info2(id int unique auto_increment, name char(20), ip char(15) , port int(5) , primary key(ip, port));
:::

3.7 外键

foreign key
即时不通过foreign key 也能关联表, 只不过会产生错误
被关联表必须先创建
被关联键必须是primary key/ unique 
级联更新 on update cascade
关系: 
    多对一: 用的比较多
    一个学生对应一个班级, 一个班级对应多个学生
  多对多: 一个学生对应多个课程, 一个课程对应多个学生
  一对一: 一个学生对应一个客户, 一个客户对一个学生

3.8 最佳实践

:::tips
1, 第一列 not null unique 就是primary key
2, auto_increment 属性即使中间delete 数据依然按照原来都顺序递增, 原因: show create table t_test; 里面有auto_increment 属性
:::

# 主键与自增属性
create table class(cid int not null unique auto_increment);
# 外键
create table stu(id int not null unique auto_increment, name char(12), cid int, foreign key(cid) references class(cid) on update cascade);
# ip + port 不能重复, 各自可以重复
create table server_info(id int not null unique auto_increment, name char(20), ip char(15) not null, port int(5) not null, unique(ip, port));

4 增删改查

4.1 表

增: create table 表名(字段1 数据类型 约束)) default charset=ut8;
删: drop table 表名;
改: 
查: desc 表名;
        show create table 表名;

4.2 数据

增:

insert into 表名 values(值1, 值2),(值1, 值2))
insert into 表名(指定字段1, 指定字段2) values(值1, 值2))
insert into 表名 select 语句; 
   #把查出来的语句添加到对应的表中
   insert into 表2(id, name) select id, name from 表1

删:

delete from 表名 where 条件；

改:

update 表名 set 字段名=值, 字段2=值2 where 条件;

查:

单表查询:
    from --> where --> group by --> having --> select --> distinct --> order by --> limit
多表查询:
    join

5. 单表查询

5.1 select

筛选
四则运算 
重命名 as
字段拼接 concat
    select concat(id,":",name) from 
去重 distinct
    select distinct post from employee;
    select distinct post,emp_name from employee;
        *联合去重

5.2 where

关键字约束
    NULL 为MySQL关键字, 需要is/is not 来判断
值约束
    select * from employee where age=18;
    select * from employee where age='18'; #可以, 但是效率特别差
数值范围查询 
    >,<,>=,<=,!=,<>,between int(a) and int(b)
精准查询 in
    select * from employee where age in (18,28,38);
模糊查询 like
    % 表示字符串任意长度, 任意内容
        select * from employee where emp_name like '程%';
    _ 表示任意一个字符
        select * from employee where emp_name like '程_';
模糊查询 正则 regexp
    select * from employee where emp_name regexp '^程';

5.3 条件组合

and or not 

5.4 聚合

:::tips
avg, sum, min, max, count---> 只计数不为NULL
:::

5.5 分组

group by

求每个部门的平均工资
    select post, from employee group by post;
求每个部门的所有成员信息
    select post, group_concat(post) from employee group by post;
语法分析:
    先from表 每行where, 然后对数据进行分组, 最终 select
结论:
    分组之后, 数据只和被分组的字段有关系, 和其他字段没有关系

5.6 过滤

having

总是在分组之后再执行having
    只能跟着group by
    group by having avg(age) > 58;

5.7 排序

order by 

order by 字段 asc;  升序
order by 字段 desc; 倒序

5.8 限制

limit 

imit 3;
desc limit 3;
limit m,n; limit n offset m;
    * 从m+1开始, 取n个

6. 多表查询

6.1 连表&连表查询

99%都可以用内连接解决

连表：
笛卡尔积:
    表1 * 表2
select * from t1, t2;

6.2 内连接

语法:
    保留两表中的共有数据
    select * from 表1 inner join 表2 on 条件
        select * from emp inner join department as dep on dep_id = dep.id;

6.3 外连接

左外连接
    保留左表中的所有数据
    select * from 表1 left join 表2 on 条件
右外连接
    保留右表的所有数据
    select * from 表1 right join 表2 on 条件
全外连接
    MySQL不支持, 可通过union实现, 但非常不常用

6.4 子查询

:::tips
一个查询的结果是另外一个查询的条件
:::

6.5 最佳实践

:::tips
1, 当连表查询与子查询都可以实现问题都话, 必须用连表查询
2, 连表查询都问题99%都可以用内连接搞定
:::

7 pymysql

:::tips
import pymysql
:::

查:

conn = pymysql.connect(host="ip", user="", password="", databases="")

cur = conn.cursor()
conn.cursor()
cur.execute("select * from t1")

cur.fetchone()
    * 一条一条数据取, 如果没有了就为None

cur.fetchmany(2)
    * 以元祖形式, 每次取2条

cur.fetchall()
    * 全部取出来

建议用fetchone(), 省内存
cur.close()
conn.close()

改：

conn = pymysql.connect(host="ip", user="", password="", databases="")

cur = conn.cursor()
cur.execute("insert into t1 values(1,2,3)")
conn.commit()
cur.close()
conn.close()

最佳实践:

input 一个数据
sql = "select * from t1 where a = %s" %input
千万不要拼sql, sql 注入问题
在cur.execute(sql, input) 在这里做sql 拼接
cur.execute(sql,(num1, num2)) 多个条件在这里拼接
    sql = "select * from t1 where a = %s and b = %s"

8 数据库杂疑

8.1 环境变量

show variables like '%char%';
set character_set_server = utf8;
set character_set_client = utf8;

8.2 关键字

NULL 为MySQL关键字, 需要is/is not 来判断
SQL语句不区分大小写, 关键字也是, NULL null

8.3 备份与恢复

备份【写锁】
    mysqldump -uxxx -pxxx > backup.sql;
恢复
    create database backup;
    use backup;
    source backup.sql

8.4 事务

写锁;

begin;
select a from t1 where b =2 for update;
update t1 set a = 2 where b=2;
commit;


begin;
select a from t1 where a =1 for update;

转载于:https://www.cnblogs.com/leesirc/p/11583683.html