当前位置:Gxlcms > 数据库问题 > 2_MySQL

2_MySQL

时间:2021-07-01 10:21:17 帮助过:6人阅读

MySQL

关系型数据库

  • 1970s - E.F.Codd - IBM研究员 - System R
  • 理论基础:关系代数和集合论
  • 具体表象:用二维表来保存数据
    • 行:一条记录 - 例如:学生的信息
    • 列:一个字段 - 例如:学生的某个属性(姓名、年龄、性别等)
    • 主键列:能够唯一标识一条记录的列,例如:学生的学号
  • 编程语言:SQL - 结构化查询语言
    • DDL - 数据定义语言 - 建立库、二维表等使用
      • create - 创建
      • drop - 丢弃/删除
      • alter - 修改
    • DML - 数据操作语言 - 库、二维表里的数据进行增、删、改、查
      • insert / delete / update / select
    • DCL - 数据控制语言 - 指定用户权限,分配权限
      • grant - 赋予权限
      • revoke - 召回权限
关系型数据库保证数据的完整性和一致性:
  • 完整性:
    • 实体完整性:每一个实体,都是独一无二的,没有冗余 - 主键/唯一索引
    • 参照完整性:外键
    • 域完整性:存储的数据都是有效的数据 - 数据类型/数据长度/非空约束/默认值约束/检查约束(mysql不支持)

Window下安装

  • 解压:下载的zip文件解压,将解压之后的文件夹放到任意目录下,这个目录就是mysql的安装目录。

  • 配置:打开目录,会看到my-default.ini配置文件,复制这个配置文件可以重命名为my.ini或者my.cnf

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8 
[mysqld]
#设置3306端口
port = 3306 
# 设置mysql的安装目录
basedir=C:\Program Files\mysql-5.6.39-winx64 
# 设置mysql数据库的数据的存放目录
datadir=C:\Program Files\mysql-5.6.39-winx64\data 
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
  • 环境变量:在系统变量PATH后面添加: 你的mysql bin文件夹的路径(如C:\Program Files\mysql-5.6.41-winx64\bin)

  • 安装MySQL服务:以管理员身份打开cmd窗口后,将目录切换到你解压文件的bin目录,输入mysqld install回车运行

  • 启动mysql服务:以管理员身份在cmd中输入:net start mysql

  • 服务启动成功之后,就可以登录了,输入mysql -u root -p(第一次登录没有密码,直接按回车过)

    • net stop mysql # 停止mysql

? 注意:在windows操作系统上没有重启mysql服务的命令,如果要重启服务,只能先stop再start

  • 启动客户端连接server
    • mysql -uroot -p 无密码
    • mysql -uroot -p123 有密码
    • mysql -uroot -p123 -h 192.138.14.12 连接到指定ip的MySQL服务器(远程登录)

MySQL时间配置

1,查看当前时间,时区

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-07-12 15:02:48 |
+---------------------+
1 row in set (0.00 sec)
  
mysql> show variables like "%time_zone%";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | UTC    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.01 sec)

-- time_zone说明mysql使用system的时区,system_time_zone说明system使用UTC时区

2,修改时区

mysql> set global time_zone = ‘+8:00‘;
Query OK, 0 rows affected (0.00 sec)
mysql> set time_zone = ‘+8:00‘;
Query OK, 0 rows affected (0.00 sec)

3,设置重启永久生效

修改配置文件/etc/my.cnf或my.ini

[mysqld]
default-time_zone = ‘+8:00‘

常用命令

用户相关命令

  • select user(); 查看当前登录的用户
  • set password = password(‘123’); 给当前用户设置密码

创建用户

  • create user ‘Jack’@’192.138.12.14’ 指定某机器可以通过用户Jack连接
  • create user ‘Jack’@’192.138.12.%’ 指定某网段可以通过用户Jack连接
  • create user ‘Jack’@’%’ 指定所有机器可以通过用户Jack连接

创建用户并设置密码

  • create user ‘Jack’@’192.138.12.14’ identified by ‘123’ 给Jack设置密码为123

用户授权

  • show grants for ‘Jack’@’192.138.12.%’ - 查看某个用户的权限
  • grant 权限类型 on 数据库.* to ‘Jack’@’192.138.12.%’ - 数据库下的所有表都可以使用
  • grant 权限类型 on . to ‘Jack’@’192.138.12.%’ -所有数据库下的所有表
  • grant 权限类型 on . to ‘Jack’@’192.138.12.%’ identified by ‘123’ -创建用户设置密码并授权使用所有数据库下的所有表
权限类型
  • all 所有权限
  • select 查询权限
  • select,insert 查和改

召回权限

  • 召回用户对数据库hrs的增、删、改权限,只能查询
    -revoke insert,delete,update on hrs.* from ‘ljjadmins‘@‘%‘
刷新
  • flush privileges; 刷新使授权立即生效

数据库相关命令

查看当前数据库
  • select database();
查看所有数据库
  • show databases;
创建一个数据库
  • create database 数据库名;
切换数据库
  • use 数据库名;
查看数据库表
  • show tables;
删除数据库(慎用)
  • drop database 数据库名;

表相关命令

创建表
  • create table 表名(字段名1 类型[(宽度) 约束条件], tb_id int,name char(16) );
查看表结构
  • desc 表名; (describe的缩写)可以看到字段\类型\长度,看不到编码\引擎\约束信息(比较直观)
  • show create table 表名 [/G];能查看到字段\类型\长度\编码\引擎(比较全面)
删除表
  • drop table 表名;

存储引擎

  • 存储数据的方式

  • 一张表:

    • 数据
    • 表结构
    • 索引(查询的时候使用一个目录结构)

mysql5.6支持的存储引擎包括InnoDB、MyISAM、MEMORY、CSV、BLACKHOLE、FEDERATED、MRG_MYISAM、ARCHIVE、PERFORMANCE_SCHEMA。其中NDB和InnoDB提供事务安全表,其他存储引擎都是非事务安全表。

数据存储在硬盘上 ==> 数据持久化存储

Innodb存储引擎(mysql5.6默认存储引擎)

特点:
  • 支持事务 :为了保证数据的完整性,将多个操作变成原子性操作 :保证数据安全
  • 支持行级锁 :修改的行少的时候使用 :修改数据频繁的操作
  • 支持表级锁 :修改的行多的时候使用 : 大量数据同时修改
  • 支持外键 :约束两张表中的关联字段不能随意添加/删除 : 降低数据增删改查的出错率。
数据和索引存储在一起 - 2个文件
  • 数据索引/表结构

Myisam存储引擎(mysql5.5之前默认存储引擎)

特点:
  • 只支持表级锁
数据和索引不存储在一起 - 3个文件
  • 数据/索引/表结构

Memory存储引擎

特点:
  • 都不支持
数据存储在内存中,数据断电消失 - 1个文件
  • 表结构

面试题

  • 你了解mysql的存储引擎吗?
    了解一些,Innodb、Myisam、Memory
  • 你的项目用了什么存储引擎,为什么?
    Innodb
    多个用户操作的过程中对同一张表的数据同时做修改
    Innodb支持行级锁,所以使用了这个存储引擎
    为了适应程序未来的扩展性,扩展新功能的 时候可能会用到...涉及到要维护数据的完整性
    项目中有一两张xx xx表,之间的外键关系是...,一张表的修改或删除比较频繁,怕出错所以使用外键约束。
查看当前的默认存储引擎
  • show variables like “default_storage_engine”;
查看当前数据库的存储引擎
  • show engines;
创建表时指定存储引擎
  • create table ai(id bigint(12),name varchar(128)) ENGINE=MYISAM;
  • create table country(id int(4),cname varchar(64)) ENGINE=InnoDB;
    也可以使用alter table修改已存在的表的存储引擎
  • alter table ai ending = innodb;

Mysql的数据类型

数字类型

整数:

  • tinyint : 1字节(8位) 小整数(默认有符号) 无符号unsigned约束
  • smallint : 2字节(16位) 大整数(默认有符号) 无符号unsigned约束
  • mediumint : 3字节(24位) 大整数(默认有符号) 无符号unsigned约束
  • int或integer : 4字节(32位) 大整数(默认有符号) 无符号unsigned约束
  • bigint : 8字节(64位)

浮点型:

  • float :4字节 单精度浮点数值 float(5,2) 表示总共5位,保留两位小数 153.25 (四舍五入)
  • double :8字节 双精度浮点数值 double(10,3) 表示总共10位,保留三位小数 1531234.252 (四舍五入)
  • decimal : 小数值,精确到小数点后第30位 默认decimal(10,0)十位整数,

字符串

  • char :0-255字节 定长字符串 多余的补空格 浪费空间,节省时间(常用)
  • varchar :0-65535字节 变长字符串 后面默认加字符的长度, 节省空间,存取速度慢
  • tinytext :0-255字节 短文本字符串
  • text : 0-65535字节 长文本字符串
  • longtext :0-4294967295字节 极大文本数据

时间

  • date:三字节 20200613 格式:YYYY-MM-DD
    用途:生日(出生日期),入职日期,生产日期

  • time:三字节 164252 格式HH:MM:SS
    用途:课程表,比赛时长

  • datetime:八字节 20200613164252 格式YYYY-MM-DD HH:MM:SS
    用途:上班打卡,日志,记录用户访问时间

  • year:一字节 1901-2155 格式:YYYY

  • timestamp:四字节 1970-01-01 00:00:00 到2038 格式YYYYMMDDHHMMSS;

    默认约束:
    • NOT NULL # 非空约束
    • DEFAULT CURRENT_TIMESTAMP # 默认当前时间
    • ON UPDATE CURRENT_TIMESTAMP # 更新时使用当前时间自动更新,如果想让其他时间都拥有自动填写和更新可以将约束写成一样的。

enum和set

  • enum :单选行为
    • enum(‘male’,’female’) 约束必须从两个或多个中选择一个,比如:性别,国旗...
  • set : 多选行为
  • set(‘orange’,’apple’,’banana’,’powl’) 约束必须选择多个
  • insert into tb (fruits) values (‘orange,apple,banana’) 插入时写在一个引号中,用逗号隔开

表的完整性约束

  • 无符号 :int unsigned - unsigned约束前面的int位无符号

  • 不为空 :not null - 可能会无效,设置严格模式如下

  • 默认值 : default - 默认设置,性别默认为:default ‘male’,

  • 唯一约束 :unique - 约束为不可重复,用于id,唯一性标识(但可以写入多个Null值)

    • 联合唯一约束:unique()
      • unique (id,id_card) 联合约束,如果id和id_card同时相同会报错,如果单个重复可以写入。
  • 主键 : primary key ,如果没有定义主键,定义非空+唯一的第一个字段会自动成为这张表的主键(primary) 一张表只有一个主键。

    • 联合主键:primary key()
      • primary key(id, id_card) 联合主键,两个键都不能为空,不能重复
  • 自增 :auto_increment

    • 只对数字有效,自带非空约束
    • 至少是unique约束之后才可以使用auto_increment
  • 外键 :foreign key (本表字段) references 外表(外表字段)

    • 外表字段必须至少是唯一的
    • on update cascade 级联修改 - on delete update 级联删除
      • . cascade方式,父表删除/更新时,子表跟着删除/更新
      • . set null 方式,父表删除/更新时,子表匹配列设为null,子表外键烈不能为not null
      • . no action 方式,子表中有匹配记录,则不允许父表删除/更新
      • . restrict 方式,同no action
      • . set default 方式,父表变更,子表外键列设置成一个默认值,但innodb不能识别
  • 补充:not null非空约束无效时(可以插入null时)
    • 设置严格模式:
      • 不支持对not null字段插入null值
      • 不支持对自增长字段插入”值
      • 不支持text字段有默认值
    • 直接在mysql中生效(重启失效):
      mysql>set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
    • 配置文件添加(永久失效):
      sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

修改表结构

1.修改表名

  • alter table 表名 rename 新表名;

2.增加字段

  • alter table 表名
    • add 字段名 数据类型 [完整性约束条件…],
      • add 字段名 数据类型 [完整性约束条件…];

3.删除字段

  • alter table 表名 drop 字段名;

4.修改字段

  • alter table 表名 modify 字段名 数据类型 [完整性约束条件…];
  • alter table 表名 change 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
  • alter table 表名 change 旧字段名 新字段名 新数据类型 [完整性约束条件…];

5.修改字段排列顺序/在增加的时候指定字段位置

  • alter table 表名 add 字段名 数据类型 [完整性约束条件…] first; # 添加到第一个
  • alter table 表名 add 字段名 数据类型 [完整性约束条件…] after 字段名; # 添加到某个字段之后
  • alter table 表名 change 字段名 旧字段名 新字段名 新数据类型 [完整性约束条件…] first;
  • alter table 表名 modify 字段名 数据类型 [完整性约束条件…] after 字段名;

6.alter操作非空和唯一(了解)

  • create table t(id int unique,name char(10) not null);
去掉null约束
  • alter table t modify name char(10) null;
添加null约束
  • alter table t modify name char(10) not null;
去掉unique约束
  • alter table t drop index id;
添加unique约束
  • alter table t modify id int unique;
添加联合唯一
  • alter table t add unique index(aa,bb);

7.alter操作主键(了解)

  • 首先创建一个数据表table_test:
    create table table_test(`id` varchar(100) NOT NULL,`name` varchar(100) NOT NULL,PRIMARY KEY (`name`));

  • 如果发现主键设置错了,应该是id是主键,但如今表里已经有好多数据了,不能删除表再重建了,仅仅能在这基础上改动表结构。

  • 先删除主键

    • alter table table_test drop primary key;
  • 然后再增加主键

    • alter table table_test add primary key(id);

注:在增加主键之前,必须先把反复的id删除掉。

8.为表添加外键

创建press表

CREATE TABLE `press` (`id` int(11) NOT NULL,`name` char(10) DEFAULT NULL,PRIMARY KEY (`id`)) ;

创建book表

CREATE TABLE `book` (`id` int(11) DEFAULT NULL,`bk_name` char(12) DEFAULT NULL,`press_id` int(11) NOT NULL,KEY `press_id` (`press_id`)) ;

为book表添加外键

alter table book add constraint fk_id foreign key(press_id) references press(id);

删除外键

alter table book drop foreign key fk_id;

多表结构的创建和分析

  • 一对多
  • 一对一
  • 多对一

操作数据

插入数据

  • insert into 表名 values (值1,值2);和表名中的字段对应
  • insert into 表名 values (‘值’,’值’),(‘值’,’值’),(‘值’,’值’)...;写入多条数据
  • insert into 表名 (字段1,字段2) values (‘值’,’值’);指定字段名写入,可以写任意的选择表中你需要写入的字段
  • insert into 表名(字段1,字段2) select 字段1,字段2 from 表名2 - 将表名2的数据查询插入到表里面

删除数据

  • delete from 表名 where 条件; 删除某条数据
  • delete from 表; - 清空表
  • 如果有自增id,新增的数据,是以删除的最后一个数据作为起始,不会清空自增字段的offect(偏移量)值
  • truncate table 表; 数据量大,删除速度要比上一条快,offect(偏移量)值直接从零开始

修改数据

  • update 表名 set 字段=值 where 条件; 修改单个字段
  • update 表名 set 字段=值,字段1=值1,... where 条件; 修改多个字段

查询数据

单表查询语法

SELECT DISTINCT 字段1,字段2... FROM 表名
                              WHERE 条件
                              GROUP BY field
                              HAVING 筛选
                              ORDER BY field
                              LIMIT 限制条数
简单查询
  • select */字段 from 表; 查询所有数据或单个字段的数据。
as 重命名(显示时)
  • select 字段1 as 别名1,字段2 as 别名2 from 表;
distinct 去掉重复
  • selelct distinct 字段,字段1 from 表; 多个数据去掉同时重复的
四则运算 :+ - * /
  • select 字段+ - * / number from 表;
定义显示格式
  • concat(); 字符串拼接
  • concat_ws(‘|’, ‘a’,’b’,’c’); - ‘a|b|c’ 以第一个参数为分隔符进行拼接
    • concat(‘a’,’b’,’c’); - ‘abc’
    • select concat(字段1,字段2) from 表; 多个字段拼接显示成一个字段
case语句 相当于if条件
  • case ... end
  • case when 字段1=值1 then 值2 when 字段3=值4 then 值5 else 值5 end
where约束

根据条件筛选符合的行,where字句中可以使用:

  1. 比较运算符:> < >= <= <> !=

    • is / si not 判断是否为Null时不用比较运算符
  2. 范围:between 80 and 100 值在80到100之间 - 闭合区间

    • 独立的值:in(80,90,100) 值是80或90或100
  3. 逻辑运算符:

    • 在多个条件直接可以使用逻辑运算符 and or not
      • 优先级 not > and > or
  4. 模糊匹配

    • like ‘e%‘ :通配符可以是%或_,
      • %表示任意多字符
      • _表示一个字符
  5. REGEXP正则表达式查询

    • SELECT * FROM employee WHERE emp_name REGEXP ‘^ale‘; 以ale开头
    • SELECT * FROM employee WHERE emp_name REGEXP ‘on$‘; 以on结尾
    • SELECT * FROM employee WHERE emp_name REGEXP ‘m{2}‘;
    • 对字符串匹配的方式
      • WHERE emp_name = ‘egon‘;
      • WHERE emp_name LIKE ‘yua%‘;
      • WHERE emp_name REGEXP ‘on$‘;
分组group by
  • 分组可以与聚合函数使用,求总人数,最大值,最小值,平均值,求和,但求出来的值只和分组字段对应,并不和其他字段对应,这个时候查出来的其他字段都不生效。

  • 单独使用GROUP BY关键字分组
    SELECT post FROM employee GROUP BY post;

注意:我们按照post字段分组,那么select查询的字段只能是post,想要获取组内的其他相关信息,需要借助函数

  • GROUP BY关键字和GROUP_CONCAT()函数一起使用
    SELECT post,GROUP_CONCAT(emp_name) FROM employee GROUP BY post;#按照岗位分组,并查看组内成员名拼接返回
    SELECT post,GROUP_CONCAT(emp_name) as emp_members FROM employee GROUP BY post;

  • GROUP BY与聚合函数一起使用
    select post,count(id) as count from employee group by post;#按照岗位分组,并查看每个组有多少人

  • 强调:

    • 如果我们用unique的字段作为分组的依据,则每一条记录自成一组,这种分组没有意义
    • 多条记录之间的某个字段值相同,该字段通常用来作为分组的依据
聚合函数
  • count : 个数 max : 最大值 min : 最小值 sum : 求和 avg : 求平均值
    • 强调:聚合函数聚合的是组的内容,若是没有分组,则默认一组
  • 示例:
    • 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;
过滤having
  • 根据分组之后的内容进行租的过滤;先分组后过滤
  • HAVING与WHERE不一样的地方在于

执行优先级从高到低:where > group by > having

  • Where 发生在分组group by之前,因而Where中可以有任意字段,但是绝对不能使用聚合函数。

  • Having发生在分组group by之后,因而Having中可以使用分组的字段,无法直接取到其他字段,可以使用聚合函数
    SELECT SUM(salary) FROM employee WHERE depart_id=3;

排序order by
  • 默认ASC : 升序

  • 单列排序

    • SELECT * FROM employee ORDER BY salary;
    • SELECT * FROM employee ORDER BY salary ASC; ASC:升序
    • SELECT * FROM employee ORDER BY salary DESC; AESC:降序
  • 多列排序

    • 先按照age排序,如果年纪相同,则按照薪资排序
      SELECT * from employee
      ORDER BY age ASC,
      salary DESC;
LIMIT 限制查询的记录数
  • limit n offset m ==> limit m,n 从m+1条开始取n项

示例:

SELECT * FROM employee ORDER BY salary DESC LIMIT 3;  #默认初始位置为0 从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条

查询顺序

  • from 表 -> where 行 -> group by 分组 -> having 筛选 -> select 列 -> order by 排序 -> limit m,n 限制;
  • where 条件中不能用select字段的重命名
  • order by 或 having 可以使用select字段的重命名
  • having经过了mysql的特殊处理,使可以感知到select字段的重命名
扩展:
  • select now();获取当前时间
  • select id (select now()) from 表名; 显示表id和当前时间

多表查询

连表查询
  • select * from 表1,表2 where 条件; 交叉连接:不适合任何匹配条件,生成笛卡尔积
    • 连接的时候创建一张大表,里面存放两张表的笛卡尔积
    • 在根据条件进行筛选就可以了
语法:
SELECT 字段列表
    FROM 表1 INNER|LEFT|RIGHT JOIN 表2
    ON 表1.字段 = 表2.字段;
表与表之间的连接方式
  • inner join ... on ... 内连接,只连接匹配的行

    • select from 表1 inner join 表2 on 条件; 只显示两表的匹配项,不匹配的不显示。
    • select from 表1 inner join 表2 on 表1.id =表2.id where 条件 group by 分组 having 筛选 order by 排序 limit m,n 偏移量;
  • left join ... on ... 外连接,优先显示左表全部记录

    • select from 左表 left join 右表 on 条件; 根据左表全内容进行匹配,全显示,右表只显示条件相对应的数据
  • right join ... on ... 外连接,优先显示右表全部记录

    • select from 左表 left join 右表 on 条件; 根据右表全内容进行匹配,全显示,左表只显示条件相对应的数据
  • full join 全外连接 使用union关键字,显示左右两个表全部记录(mysql不支持)

    • select from 左表 left join 右表 on 条件 union select from 左表 left join 右表 on 条件;
子查询
  • 子查询是将一个查询语句嵌套在另一个查询语句中。
  • 内层查询语句的查询结果,可以为外层查询语句提供查询条件。
  • 子查询中可以包含:IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
  • 还可以包含比较运算符:= 、 !=、>、>= 、<、<=等

注意 使用exists子查询不返回查询的值,而是返回一个真假值,True或False,当可以使用连表查询也可以使用子查询时,推荐使用连表查询

索引

  • 加快查询速度

  • 函数:处理简单的逻辑

  • 存储过程:处理的逻辑比较复杂

  • 数据准备:

    • 读取一次硬盘的时间开销
    • 大约 0.1ms
    • CPU 0.1ms运行50000条指令
    • 减少读取操作
    • 预读取,linux读文件每次会加载4096字节
  • 新的数据结构 -- 数

    • root 根
    • branch 分支
    • leaf 叶
  • 二叉树:

    • 每个分支只有两个节点
  • 平衡树(btree):- balance tree

    • 让查找每一个之经历的查找速度尽量平衡
  • b+树:

    • 分支节点不存储数据 - 让树的高低尽量矮,让查找一个数据的效率尽量稳定

    • 在所有叶子节点之间加入了双向的地址链接 -- 查找范围非常快

两种索引的差别

  • 聚集索引(聚簇索引)

    • Innodb 必有且仅有一个(索引和数据都存储在一个文件)
    • innodb存储引擎中主键默认创建一个聚集索引
  • 非聚集(簇)索引(辅助索引)

    • innodb(索引和数据分开存储)
    • myisam

索引的创建和删除

  • 创建主键 primary key 聚集索引+非空+唯一

  • 创建唯一约束 unique 辅助索引+唯一

添加一个普通索引:

  • create index 索引名 on 表(字段);

删除索引;

  • drop index 索引名 on 表;

正确使用索引

  • 查询的条件字段不是索引字段
    • 对哪个字段创建索引,就用这个字段条件查询
  • 在创建索引的时候应该对区分度比较大的列进行创建
    • 1/10以下重复率比较适合创建索引
  • 范围
    • 范围越小越快,越大越慢
    • like ‘a%’ 快,like ‘%a’ 慢
  • 条件列不能参与计算/使用函数
  • and和or
    • -and连接多个条件其中一列含有索引,可以加快查找速度。
    • or 连接多个条件必须所有列都含有索引,才能加快查找顺序。
  • 联合索引 - 最左前缀原则(必须带着最左边的列做条件,出现范围后索引失效)

数据备份

数据库的逻辑备份

语法:

mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql

示例:

#单库备份
mysqldump -uroot -p123 db1 > db1.sql
mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql

#多库备份
mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql

#备份所有库
mysqldump -uroot -p123 --all-databases > all.sql 

数据恢复

方法一:
[root@egon backup]# mysql -uroot -p123 < /backup/all.sql
方法二:
mysql> use db1;
mysql> SET SQL_LOG_BIN=0;   #关闭二进制日志,只对当前session生效
mysql> source /root/db1.sql

事务和锁

begin;  # 开启事务
select * from emp where id = 1 for update;  # 查询id值,for update添加行锁;
update emp set salary=10000 where id = 1; # 完成更新
commit; # 提交事务
  • 一致性 - 事务 - 要么全成功要么全失败,操作不可分割 - ACID特性
    • A - Atomicity - 原子性 - 不可分割
    • C - Consistency - 一致性 - 事务前后数据状态要一致
    • I - Isolation - 隔离性 - 并发的多个事务不知道彼此的中间状态
    • D - Duration - 持久性 - 事务完成后数据要做持久化(写进物理存储)

注意事项

  1. 给数据库和表命名时尽量选择全小写

  2. 作为筛选条件的字符串是否区分大小写,看设置的校对规则 collate 例如:

    • utf8_bin 中是区分大小写的
    • utf8_general_ci 中ci (case insensitive) 表示不区分大小写
  3. 删除表注意事项:外键关系,多对一,先删除多的一方

    • emp 员工 dept 部门,员工多对一部门,员工里有外键,先删除员工
      • drop table if exists tb_emp;
      • drop table if exists tb_dept;

视图:查询的快照(简化查询操作)

  • 通过视图可以将用户的访问权限限制到某指定的列上
-- 创建视图
create view vw_na_no as 
select ename,eno,sal from tb_emp;

-- 查询视图

select * from vw_na_no;

-- 删除视图
drop view vw_na_no;

过程

  • (存储)过程/函数:把一系列的SQL可以封装到一个过程中,而且可以加上分支和循环,将来通过过程的名字直接调用过程即可,因为创建过程时已经提前编译了 SQL语句,所以比直接执行SQL语句性能更好
-- 重新定义定界符为$$ - (命令行模式下出现 ; 下直接接受,修改以使语句完整)
delimiter $$;

-- 创建存储过程(查询模式下末尾直接写 ; 命令行模式下需要修改定界符,填写$$)
-- float -> decimal: 有效数字 decimal(6,1) 六位有效数字,1位小数
create procedure sp_dept_avg_sal(deptno int,out avgsal float)
begin 
  select avg(sal) into avgsal from tb_emp where dno=deptno;
end;

-- 调用存储过程
call sp_dept_avg_sal(10,@a);

-- 通过输出参数取出部门平均成绩
select @a;

-- 删除存储过程
drop procedure sp_dept_avg_sal;

-- 将定界符还原回;
delimiter ;

触发器

  • 在执行增删改操作时可以触发其他的级联操作,但是有可能导致“锁表”现象,实际开发中应该尽量避免使用触发器
update tb_dept set dno=11 where dno=10;

-- 重新定义定界符
delimiter $$

-- 创建触发器
create trigger tr_dept_update 
after update on tb_dept for each row 
begin 
  update tb_emp set dno=new.dno where dno=`OLD_PASSWORD`(str).dno;
end;

-- 修改定界符为 ; 
delimiter ;

-- 删除触发器
drop trigger tr_dept_update;

事务(transaction)

  • 把多个增删改操作做成原子性操作(不可分割)
    • 要么全做(全成功),要么不做(全失败)
    • 例如转账信息,一个账户扣钱,一个账户加钱,原子性操作
      • 要么全部成功,要么全部失败
-- 开启事务两种方法 begin/start transaction
-- start transaction; -- 开启事务环境1 
begin; -- 开启事务环境2

-- 删除全表
-- 视图看不到数据,但数据还在
-- 并没有反映到实际的物理存储
delete from tb_emp;

-- 提交(事务中的操作全都生效)
commit;

-- 回滚(事务中的所有操作全部撤销)
rollback;

2_MySQL

标签:注意   ssi   不用   串匹配   一个数据库   cad   iso   class   integer   

人气教程排行