Mysql-基础-04

MySQL-Day03回顾

1、索引

 1、普通索引 index

 2、唯一索引(UNI,字段值不允许重复,但可以为NULL)

  1、创建

   1、字段名 数据类型 unique

   2、unique(字段名),

      unique(字段名)...

   3、create unique index 索引名 on 表名(字段名);

  2、删除(只能一个一个删)

   show index from 表名;

   drop index 索引名 on 表名;

 3、主键索引&自增长属性(PRI,字段值不允许重复且不能为NULL)

  1、注意：一个表中只能有一个字段为主键字段

  2、创建

   1、id int primary key auto_increment,

   2、primary key(字段名)

   3、alter table 表名 add primary key(字段名)

  3、删除

   1、先删除自增长属性

    alter table 表名 modify 字段名 数据类型;

   2、删除主键

    alter table 表名 drop primary key;

  4、已有表添加自增长属性

   alter table 表名 modify 字段名 数据类型 auto_increment;

 4、外键索引

  1、作用：让当前表字段值在另一个表字段的范围内去选择

  2、创建

   1、foreign key(参考字段名)

      references 被参考表名(被参考字段名)

     on delete 级联动作

     on update 级联动作

   2、alter table 表名 add foreign key(...)...

    ## 会受到表中原有数据的限制 ##

  3、级联动作

   1、cascade

   2、restrict(默认)

   3、set null

   4、no action

  4、使用规则

   1、两张表被参考字段和参考字段数据类型要一致

   2、被参考字段必须是 主键

  5、删除外键

   1、查看外键名

    show create table 表名;

   2、删除外键

    alter table 表名 drop foreign key 外键名;

2、数据导入

 1、load data infile "/var/lib/mysql-files/文件名"

    into table 表名

   fields terminated by "分隔符"

   lines terminated by "\n"

 2、步骤

  1、在数据库中创建对应的表

  2、查看数据库的搜索路径

   show variables like "secure_file_priv";

  3、将文件拷贝到搜索路径中

   sudo cp 文件 /var/lib/mysql-files/

  4、执行数据导入语句

3、数据导出

 1、select ... from 表名

    into outfile "/var/lib/mysql-files/文件名"

   fields terminated by "分隔符"

   lines terminated by "\n";

 2、注意

  1、导出内容完全由select查询语句决定

  2、路径必须指定数据库搜索路径

4、表复制

 1、create table 表名 select 查询语句 [where false];

 2、复制表不会把原表的 key 属性复制过来

5、嵌套查询(子查询)

6、多表查询

 1、笛卡尔积

  select 字段名列表 from 表名列表;

 2、加 where 条件 == inner join

7、连接查询

 1、內连接(只显示匹配到的记录)

 2、外连接

  1、左连接(以左表为主去显示查询结果)

  2、右连接

 3、select 字段名列表 from 表1 left join 表2 on 条件;

MySQL-Day04笔记

1、MySQL用户账户管理

 1、开启MySQL远程连接

  1、获取root权限

   sudo -i

  2、cd到配置文件所在路径

   cd /etc/mysql/mysql.conf.d/

  3、vi mysqld.cnf

   #bind-address = 127.0.0.1

   浏览模式 -> a(插入模式)

   -> esc(浏览模式) ->

   shift + :(命令行模式) ->

   :wq(保存并退出)

  4、重启mysql服务

   /etc/init.d/mysql restart

 2、添加授权用户

  1、使用root用户连接到服务器

   mysql -uroot -p123456

  2、添加新的授权用户

   create user "用户名"@"IP地址" identified by "密码";

   create user "tiger"@"%" identified by "123456";

   IP地址的表示方式：

    1、% 表示用户可以从任何地址连接到服务器

    2、localhost 用户只能从本地连接

    3、指定一个IP 表示用户只能从此IP连接到服务器

 3、给用户授权

  grant 权限列表 on 库.表 to "用户名"@"IP地址" with grant option;

  grant all privileges on *.* to "tiger"@"%" with grant option;

  权限列表：select,update,delete,insert,alter,drop,create,...

  库.表： *.* 表示所有库的所有表

  ### 写入到 库mysql下的user表, user、host两个字段

 4、练习

  添加一个授权用户 monkey ,所有人都可以连接,只对 db1库有查询权限

  1、添加授权用户 monkey

   create user "monkey"@"%" identifited by "123456";

  2、给 monkey 用户授权

   grant select on db1.* to "monkey"@"%" with grant option;

  3、验证：mysql -hIP地址 -umonkey -p

 5、删除授权用户

  drop user "用户名"@"IP地址";

  drop user "tiger"@"%";

2、数据备份(mysqldump,在Linux终端中操作)

 1、命令格式

  mysqldump -uroot -p 源库名 > 路径/xxx.sql

 2、示例

  1、备份db2库

   mysqldump -uroot -p db2 > /home/tarena/db2.sql

 3、源库名的表示方式

  --all-databases 备份所有库

  库名            备份单个库

  -B 库1 库2 ...  备份多个库

  库名 表1 表2 ...备份指定库的指定表

 4、练习

  1、备份所有库 all_mysql.sql,放到用户主目录下的 mydata 目录中

   mysqldump -uroot -p --all-databases > ~/mydata/all_mysql.sql

  2、备份 db2 库中的sheng、city、xian三张表 db2scx.sql

   mysqldump -uroot -p db2 sheng city xian > ~/mydata/db2scx.sql

  3、备份 MOSHOU 和 db2 库,MSdb2.sql

   mysqldump -uroot -p -B MOSHOU db2 > ~/mydata/MSdb2.sql

3、数据恢复

 1、命令格式

  mysql -u用户名 -p 目标库名 < 路径/xxx.sql

 2、示例

  1、先备份库

   mysqldump -uroot -p db2 > db2.sql

  2、删除库

   drop database db2;

  3、先创建空库

   create database db2 default charset=utf8;

  4、恢复命令

   mysql -uroot -p db2 < db2.sql

 3、备份分为 完全备份 和 增量备份

   完全备份：mysqldump

   增量备份：binlog日志、xbackup工具

 4、从所有库的备份文件中恢复某一个库(--one-database)

  mysql -u用户名 -p --one-database 目标库名 < all_mysql.sql

  示例：

   mysql -uroot -p --one-database db2 < all_mysql.sql

  注意：

   1、恢复库时库中新增的表不会删除

   2、恢复时必须先创建空库

4、事务和事务回滚

 1、定义

  一件事从开始发生到结束的整个过程

 2、作用

  确保数据的一致性

 3、事务和事务回滚的应用

  1、mysql中默认sql语句会自动commit到数据库

   show variables like "autocommit";

  2、事务应用

   1、开启事务

    mysql> start transaction;

    mysql> ... SQL命令

    ## 此时autocommit被禁用,SQL命令不会对数据库中数据做修改

   2、终止事务

    mysql> commit;

    或者

    mysql> rollback;

   3、注意

    1、事务回滚rollback只针对于对表记录的操作,增、删、改,对创建库、创建表的操作无效

  3、案例

   1、背景

    你：建行卡

    你朋友：工商卡

    你在建行的自动提款机给你朋友工商卡转5000元

   2、过程

    1、转账中...

     1、先到建行的数据库把你的余额 -5000

     2、再到工行的数据库把你朋友的余额 +5000

     3、commit; 转账成功

       rollback; 回滚,转账失败

    2、过程

     表1、CCB

      create table CCB(

      name varchar(20),

      money int

      );

      insert into CCB values("Zhuanqian",10000);

     表2、ICBC

      create table ICBC(

      name varchar(20),

      money int

      );

      insert into ICBC values("Shouqian",4000);

     开始转账

      mysql>start transaction;

      mysql>update CCB set money=5000 where name="Zhuanqian";

      mysql>update ICBC set money= 断电了,宕机了..;

      mysql>rollback;

5、存储引擎

 1、定义

  是用来处理表的处理器

 2、存储引擎基本操作

  1、查看已有表的存储引擎

   show create table 表名;  # engine=...

  2、创建表时指定存储引擎

   create table 表名(...)engine=myisam;

  3、查看所有的存储引擎

   mysql> show engines;

 3、工作中使用的存储引擎

  innodb  myisam

 4、常用存储引擎特点

  1、innodb特点

   1、共享表空间

    表名.frm 表结构

    表名.ibd 表记录&索引信息

   2、支持行级锁

  2、myisam特点

   1、独享表空间

    表名.frm 表结构

    表名.myd 表记录

    表名.myi 索引信息

   2、支持表级锁

 5、锁

  1、加锁的目的

   解决客户端并发访问的冲突问题

  2、锁类型

   读锁(select)共享锁

    加读锁之后不能更改表中内容,但可以进行查询

   写锁(insert,update,delete)互斥锁、排他锁

  3、锁粒度

   表级锁

   行级锁

   ####操作完成后会自动释放锁

 6、如何决定使用哪种存储引擎

  1、执行查询操作多的表使用myisam存储引擎(使用innodb浪费资源)

  2、执行写操作比较多的表使用innodb存储引擎

 7、如何更改表的默认存储引擎

  1、sudo -i

  2、cd /etc/mysql/mysql.conf.d/

  3、vi mysqld.cnf

     [mysqld]

    defalut-storage-engine = myisam

  4、/etc/init.d/mysql restart

 8、memory存储引擎

  memory: 表记录存储在内存中

   表名.frm 表结构

   ## 服务重启之后表结构在,表记录都消失

6、MySQL调优

 1、选择合适的存储引擎

  1、经常用来读的表使用myisam存储引擎

  2、其余的表都用innodb存储引擎

 2、SQL语句调优(尽量避免全表扫描)

  1、在select where order by常涉及到的字段上建立索引

  2、where子句中不使用 !=,否则将放弃使用索引进行全表扫描

  3、尽量避免用NULL值判断,否则会全表扫描

   示例：

    select id from t1 where number is null;

   优化：

    在number字段设置默认值0

  4、尽量避免 or 来连接条件,导致全表扫描

   示例(优化前)：

    select id from t1 where id=10 or id=20;

   优化后：

    select id from t1 where id=10

    union all

    select id from t1 where id=20;

  5、模糊查询尽量避免使用前置 %,导致全表扫描

   select id from t1 where name like "a%";

  6、尽量避免 in 和 not in,导致全表扫描

   select id from t1 where id in(1,2,3);

   select id from t1 where id between 1 and 3;

  7、尽量避免使用 select * ...,要用具体的字段列表代替 *,不要返回用不到的任何字段

7、Python数据库编程

 1、python数据库接口(Python DB-API)

  1、为开发人员提供的数据库应用编程接口

  2、支持的数据库服务软件

   mysql、Oracle、SQL_Server、mongodb...

  3、python提供的操作mysql模块

   python3: pymysql

   python2: MySQLdb

  4、pymsql模块使用流程

   1、建立数据库连接

   2、创建游标对象

   3、使用游标对象的方法操作数据库

   4、提交commit

   5、关闭游标对象

   6、关闭数据库连接

  5、建立数据库连接

   1、语法格式

    对象名 = pymysql.connect("主机地址","用户名","密码","库名",charset="utf8")

   2、connect连接对象支持的方法

    1、cursor() 创建一个游标对象db.cursor()

    2、commit() 提交到数据库执行(表记录增删改)

    3、rollback() 回滚

    4、close() 关闭数据库连接

   3、游标对象支持的方法

    1、execute("SQL命令") 执行SQL命令

    2、fetchone() 取得结果集的第一条记录

    3、fetchmany(n) 取得结果集的 n 条记录

    4、fetchall() 取得结果集的所有记录

    5、close() 关闭游标对象

8、Mysql workbench(图形化界面管理工具)

9、ER模型&ER图

 1、定义

  ER模型即 实体 - 关系 模型

  ER图即 实体关系图

 2、三个概念

  1、实体

  2、属性

  3、关系

   1、定义 ：实体之间的关系

   2、分类

    一对一关系(1:1) : 班级和班长

    一对多关系(1:n) : 公司和职工、班级和学生

    多对多关系(m:n) : 学生和课程

  4、ER图的绘制

   1、矩形框代表实体,菱形框代表关系,椭圆形代表属性

   2、见 ER图.png