MySQL

MySQL基础

1、数据库概念

数据库

• 存储数据的仓库（逻辑概念，并未真实存在）

数据库软件

• 真实软件，用来实现数据库这个逻辑概念

数据仓库

• 数据量更加庞大，更加侧重数据分析和数据挖掘，供企业决策分析之用，主要是数据查询，修改和删除很少

2、MySQL的特点

• 关系型数据库
• 跨平台
• 支持多种编程语言（python、java、php）
• 基于磁盘存储，数据是以文件形式存放在数据库目录/var/lib/mysql下

3、启动连接

• 服务端启动

sudo /etc/init.d/mysql start|stop|restart|status
sudo service mysql start|stop|restart|status

• 客户端连接

mysql -hIP地址 -u用户名 -p密码
本地连接可省略 -h 选项

4、基本SQL命令

库管理

    1、查看已有库；
   		show databases;
   		
    2、创建库并指定字符集；
		create database 库名 charset utf8;
		create database 数据库名 default charset utf8 collate utf8_general_ci; 忽略大小写
		
    3、查看当前所在库；
      	select database();
      
    4、切换库；
      	use 库名;
      
      
    5、查看库中已有表；
      	show tables
      
      
    6、删除库；
      
        drop database 库名
      

表管理

    1、创建表并指定字符集；
    	create table 表名(字段名 类型 xxx)charset=utf8;
      
    2、查看创建表的语句 (字符集、存储引擎)；
      	show create table 表名
      
    3、查看表结构;
      	desc 表名
      	
    4、删除表;
		drop table 表名1,表名2      

表记录管理

    1、增 ： insert into 表名(字段名) values(字段1值,字段2值),()....
    2、删 ： delete from 表名 where 条件
    3、改 ： update 表名 set 字段名=值 where 条件
    4、查 ： select 字段名 或者 * from 表名 where 条件

表字段管理（alter table 表名）

    1、增 ： alter table 表名 add 字段名 类型 first|after xxx
    2、删 ： alter table 表名 drop 字段名
    3、改 ： alter table 表名 modify 字段名 新类型
    4、表重命名： alter table 表名 rename 新表名

5、数据类型

四大数据类型

• 数值类型

 int 4  smallint 2  tinyint 1  bigint 8
 float  decimal(7,3) 0000.000 

• 字符类型

char()  varchar()  text  blob 

varchar 预留1-2字节 用于存储 当前字段实际存储的数据长度

• 枚举类型

enum()   set() 不常用

• 日期时间类型

date  datetime timestamp time year

created_time - datetime 
updated_time - datetime  

日期时间函数

NOW()  CURDATE()  CURTIME()  YEAR(字段名) 

日期时间运算

select * from 表名 where 字段名 运算符(NOW()-interval 间隔);
间隔单位: 1 day | 3 month | 2 year
eg1:查询1年以前的用户充值信息
  select * from user_pay where created_time < (NOW() - interval 1 year)

6、MySQL运算符

• 数值比较

> >= < <= = !=
eg1 : 查询成绩不及格的学生
      select * from students where score < 60;
eg2 : 删除成绩不及格的学生
      delete from students where score < 60;      
eg3 : 把id为3的学生的姓名改为 周芷若
      update students set name='周芷若' where id=3;

• 逻辑比较

and  or             gender 'M'男  'F'女
eg1 : 查询成绩不及格的男生
		select * from students where score < 60 and gender = 'M';
  
eg2 : 查询成绩在60-70之间的学生
  		select * from students where score > 60 and score < 70;

• 范围内比较

between 值1 and 值2 、in() 、not in()
eg1 : 查询不及格的学生姓名及成绩
  		select name,score from students where score between 0 and 59 
  		
eg2 : 查询AID19和AID18班的学生姓名及成绩
  		select name,score from students where class in('AID19','AID18');

• 模糊比较（like）

where 字段名 like 表达式( %和 _ )  （‘%’匹配所有 ‘_’匹配一位
eg1 : 查询北京的姓赵的学生信息
  		select * from students where address='bj' and name like '赵%';

• NULL判断

is NULL 、is not NULL
eg1 : 查询姓名字段值为NULL的学生信息
  select * from students where name is NULL;

7、查询

• order by

给查询的结果进行排序(永远放在SQL命令的倒数第二的位置写)

order by 字段名 ASC/DESC
eg1 : 查询成绩从高到低排列
  select * from students order by score DESC

• limit

限制显示查询记录的条数（永远放在SQL命令的最后写）

limit n ：显示前n条
limit m,n ：从第(m+1)条记录开始，显示n条
分页：每页显示10条，显示第6页的内容
     limit (6-1)*10 , 10

---

MySQL基础巩固

• 创建库 ：country（指定字符编码为utf8）

• 创建表 ：sanguo 字段：id 、name、attack、defense、gender[M-男，F-女]、country[蜀国 魏国 吴国]
  ​ 要求 ：id设置为主键,并设置自增长属性

           ​                **id int primary key auto_increment,**
  

• 插入5条表记录（id 1-5,name-诸葛亮、司马懿、貂蝉、张飞、赵云），攻击>100,防御<100）

• 查找所有蜀国人的信息

  select * from sanguo where country='蜀国';
  

• 将赵云的攻击力设置为360,防御力设置为68

  update sanguo set attack=360,defence=68 where name='赵云';
  

• 将吴国英雄中攻击值为110的英雄的攻击值改为100,防御力改为60

  update sanguo set attack=100, defence=60 where country='吴国';
  

• 找出攻击值高于200的蜀国英雄的名字、攻击力

  select name,attack from sanguo where country='蜀国' and  attack > 200;
  

• 将蜀国英雄按攻击值从高到低排序

  select * from sanguo where country='蜀国' order by attack DESC;
  

• 魏蜀两国英雄中名字为三个字的按防御值升序排列

  select * from sanguo where country in('魏国','蜀国') and  name like '___' order by defence;
  

• 在蜀国英雄中,查找攻击值前3名且名字不为 NULL 的英雄的姓名、攻击值和国家

  select name, attack,country from sanguo
  where country='蜀国' and name is not NULL
  order by attack DESC
  limit 3;
  

MySQL普通查询

顺序:
    3、select ...聚合函数 from 表名
    1、where ...
    2、group by ...
    4、having ...
    5、order by ...
    6、limit ...;

• 聚合函数

方法	功能
avg(字段名)	该字段的平均值
max(字段名)	该字段的最大值
min(字段名)	该字段的最小值
sum(字段名)	该字段所有记录的和
count(字段名)	统计该字段记录的个数
count(*) 可统计所有数据，包括值为null的数据

eg1 : 找出表中的最大攻击力的值？

select max(attack) from sanguo;

eg2 : 表中共有多少个英雄？

select count(*) as number from sanguo;

eg3 : 蜀国英雄中攻击值大于200的英雄的数量

select count(id) from sanguo where country='蜀国' and attack > 200;

• group by

给查询的结果进行分组
eg1 : 计算每个国家的平均攻击力

select  country , avg(attack) from sanguo group by country;

魏国   司马懿
蜀国   诸葛亮。。。
吴国   貂蝉。。。

eg2 : 所有国家的男英雄中 英雄数量最多的前2名的 国家名称及英雄数量

select country, count(id) as number from sanguo 
where gender = 'M' 
group by country
order by number DESC
limit 2

​ group by后字段名必须要为select后的字段
​ 查询字段和group by后字段不一致,则必须对该字段进行聚合处理(聚合函数)

• having语句

对分组聚合后的结果进行进一步筛选

eg1 : 找出平均攻击力大于105的国家的前2名,显示国家名称和平均攻击力
	select country, avg(attack) from sanguo
	group by country
	having avg(attack) > 105
	order by avg(attack) DESC
	limit 2;

注意

having语句通常与group by联合使用,过滤由group by语句返回的记录集
having语句存在弥补了where关键字不能与聚合函数联合使用的不足,where只能操作表中实际存在的字段,having操作的是聚合函数生成的显示列

• distinct语句

不显示字段重复值

eg1 : 表中都有哪些国家
		select distinct country from sanguo;
  
eg2 : 计算一共有多少个国家
        select count(distinct country) from sanguo;
        

注意

distinct和from之间所有字段都相同才会去重
distinct不能对任何字段做聚合处理

• 查询表记录时做数学运算

运算符 ： + - * / % **

eg1: 查询时显示攻击力翻倍
  
eg2: 更新蜀国所有英雄攻击力 * 2

索引概述

• 定义

对数据库表的一列或多列的值进行排序的一种结构(Btree方式)

• 优点

加快数据检索速度

• 缺点

占用物理存储空间(/var/lib/mysql)
当对表中数据更新时,索引需要动态维护,降低数据维护速度

• 索引示例

# cursor.executemany(SQL,[data1,data2,data3])
# 以此IO执行多条表记录操作，效率高，节省资源
1、开启运行时间检测
  mysql>show variables like '%pro%';
  mysql>set profiling=1;
2、执行查询语句(无索引)
  select name from students where name='Tom99999';
3、查看执行时间
  show profiles;
4、在name字段创建索引
  create index name on students(name);
5、再执行查询语句
  select name from students where name='Tom88888';
6、查看执行时间
  show profiles;

索引分类

普通(MUL) and 唯一(UNI)

• 使用规则

1、可设置多个字段
2、普通索引 ：字段值无约束,KEY标志为 MUL
3、唯一索引(unique) ：字段值不允许重复,但可为 NULL
                    KEY标志为 UNI
4、哪些字段创建索引:经常用来查询的字段、where条件判断字段、order by排序字段

• 创建普通索引and唯一索引

创建表时

create table 表名(
字段名 数据类型，
字段名 数据类型，
index(字段名),
index(字段名),
unique(字段名)
);

已有表中创建

create [unique] index 索引名 on 表名(字段名);

• 查看索引

1、desc 表名;  --> KEY标志为：MUL 、UNI
2、show index from 表名\G;

• 删除索引

drop index 索引名 on 表名;

主键(PRI)and自增长(auto_increment)

• 使用规则

1、只能有一个主键字段
2、所带约束 ：不允许重复,且不能为NULL
3、KEY标志(primary) ：PRI
4、通常设置记录编号字段id,能唯一锁定一条记录

• 创建

创建表添加主键

create table student(
id int auto_increment,
name varchar(20),
primary key(id)
)charset=utf8,auto_increment=10000;##设置自增长起始值

已有表添加主键

alter table 表名 add primary key(id);

已有表操作自增长属性

1、已有表添加自增长属性
  alter table 表名 modify id int auto_increment;
2、已有表重新指定起始值：
  alter table 表名 auto_increment=20000;

• 删除

1、删除自增长属性(modify)
  alter table 表名 modify id int;
2、删除主键索引
  alter table 表名 drop primary key;

---

有一张文章评论表comment如下

comment_id	article_id	user_id	date
1	10000	10000	2018-01-30 09:00:00
2	10001	10001	… …
3	10002	10000	… …
4	10003	10015	… …
5	10004	10006	… …
6	10025	10006	… …
7	10009	10000	… …

以上是一个应用的comment表格的一部分，请使用SQL语句找出在本站发表的所有评论数量最多的10位用户及评论数，并按评论数从高到低排序

备注：comment_id为评论id

​ article_id为被评论文章的id

​ user_id 指用户id

• 3、操作题

综述：两张表，一张顾客信息表customers，一张订单表orders

表1：顾客信息表，完成后插入3条表记录

c_id 类型为整型，设置为主键，并设置为自增长属性
c_name 字符类型，变长，宽度为20
c_age 微小整型，取值范围为0~255(无符号)
c_sex 枚举类型，要求只能在('M','F')中选择一个值
c_city 字符类型，变长，宽度为20
c_salary 浮点类型，要求整数部分最大为10位，小数部分为2位

表2：顾客订单表（在表中插入5条记录）

o_id 整型
o_name 字符类型，变长，宽度为30
o_price 浮点类型，整数最大为10位，小数部分为2位
设置此表中的o_id字段为customers表中c_id字段的外键,更新删除同步
insert into orders values(1,"iphone",5288),(1,"ipad",3299),(3,"mate9",3688),(2,"iwatch",2222),(2,"r11",4400);

增删改查题

1、返回customers表中，工资大于4000元，或者年龄小于29岁，满足这样条件的前2条记录
2、把customers表中，年龄大于等于25岁，并且地址是北京或者上海，这样的人的工资上调15%
3、把customers表中，城市为北京的顾客，按照工资降序排列，并且只返回结果中的第一条记录
4、选择工资c_salary最少的顾客的信息
5、找到工资大于5000的顾客都买过哪些产品的记录明细			
6、删除外键限制			
7、删除customers主键限制
8、增加customers主键限制c_id

1、having语句通常和group by语句联合使用,过滤由group by语句返回的记录集
2、where只能操作表中实际存在字段,having可操作由聚合函数生成的显示列

• distinct

select distinct 字段1,字段2 from 表名;

• 查询时做数学运算

select 字段1*2,字段2+5 from 表名;

update 表名 set attack=attack*2 where 条件;

• 索引(BTree)

primary key 是 主键索引【聚簇索引】 - 完美B+树， 叶子节点上存储着数据

非聚簇索引 叶子节点 - 存储主键的引用

students

id[主键 聚簇索引] name[非聚簇索引]

1 Tom_1

…

1） select * from students where id=1 -> 聚簇索引的查询

​ 查 聚簇索引 树 -> 带着数据的B+ -> 叶子节点 -> 找到数据

2） select * from students where name=Tom_1 -> 非聚簇索引的查询

​ 查 非聚簇索引 树 -> 不带数据的 且 叶子节点带有对应行的主键的 B+

​ -> 找到叶子节点中存储的主键值 -> 触发聚簇索引查询

​ 查 聚簇索引 树 -> 带着数据的B+ -> 叶子节点 -> 找到数据

1， 2叉树： 树太高/每次向下查找都是一次磁盘IO,

2， 多路查找：

​ 1） B树 - 每个节点多叉/路; 即存储索引也存储了数据

​ 2） B+树 - 每个节点只存索引 / 数据统一存放在叶子节点/

​ 叶子节点索引是有序的且相连的；

优点 ：加快数据检索速度
缺点 ：占用物理存储空间,需动态维护,占用系统资源
SQL命令运行时间监测

​ mysql>show variables like ‘%pro%’;

​ 1、开启 ：mysql> set profiling=1;
​ 2、查看 ：mysql> show profiles;
​ 3、关闭 ：mysql> set profiling=0;

• 普通(MUL)、唯一(UNI,字段值不能重复,可为NULL)

  创建
  ​ index(字段名),index(字段名)
  ​ unique(字段名),unique(字段名)

  ​ create [unique] index 索引名 on 表名(字段名);

  查看
  ​ desc 表名;
  ​ show index from 表名\G;
  ​ Non_Unique:1 :index
  ​ Non_Unique:0 :unique

  删除
  ​ drop index 索引名 on 表名; (只能一个一个删)

MySQL高级

外键（foreign key）

• 定义

  让当前表字段的值在另一个表的范围内选择

• 语法

  foreign key(参考字段名 stu_id)
  references 主表 财务表 (被参考字段名 id)
  on delete 级联动作
  on update 级联动作
  

• 使用规则

1、主表、从表字段数据类型要一致
2、主表被参考字段 ：KEY的一种，一般为主键

• 示例

表1、缴费信息表(财务)

id   姓名     班级     缴费金额
1   唐伯虎   AID19     300
2   点秋香   AID19     300
3   祝枝山   AID19     300

create database db2 charset utf8;
use db2;
create table master(
id int primary key,
name varchar(20),
class char(5),
money decimal(6,2)
)charset=utf8;

insert into master values(1, '唐伯虎', 'AID19', 300),(2, '点秋香', 'AID19', 300),(3, '祝枝山', 'AID19', 300);

表2、学生信息表(班主任) – 做外键关联

stu_id   姓名   缴费金额
  1     唐伯虎    300
  2     点秋香    300
 
 create table slave(
 	stu_id int,
 	name varchar(20),
    money decimal(6,2),
    foreign key(stu_id) references master(id)
    on delete cascade on update cascade
 )charset=utf8;
 
 insert into slave values(1, '唐伯虎', 300),(2,'点秋香',300),(3,'祝枝山',300);
 
 
 
 
  create table slave_2(
 	stu_id int,
 	name varchar(20),
    money decimal(6,2),
    foreign key(stu_id) references master(id)
    on delete restrict on update restrict
 )charset=utf8;
 
 insert into slave_2 values(2,'点秋香',300),(8,'祝枝山',300);
 
 
 
 
   create table slave_3(
 	stu_id int,
 	name varchar(20),
    money decimal(6,2),
       
       
    foreign key(stu_id) references master(id)
    on delete set null on update set null
 )charset=utf8;
 
 insert into slave_3 values(2,'点秋香',300),(8,'祝枝山',300);
 
 

• 删除外键

alter table 表名 drop foreign key 外键名;
外键名 ：show create table 表名;

• 级联动作

cascade
数据级联删除、更新(参考字段)
restrict(默认)
从表有相关联记录,不允许主表操作
set null
主表删除、更新,从表相关联记录字段值为NULL

• 已有表添加外键

alter table 表名 add foreign key(参考字段) references 主表(被参考字段) on delete 级联动作 on update 级联动作

嵌套查询(子查询)

定义

把内层的查询结果作为外层的查询条件

语法格式

select ... from 表名 where 条件(select ....);

示例

1、把攻击值小于平均攻击值的英雄名字和攻击值显示出来

	select name, attack from sanguo where attack <(select avg(attack) from sanguo);
  
2、找出每个国家攻击力最高的英雄的名字和攻击值(子查询)
  
  select name, attack from sanguo where (country, attack) in(select country, max(attack) from sanguo group by country);
  
  select name, attack from sanguo where (country, attack) in(('魏国',110), ('蜀国',360), ('吴国',210));
 

多表查询

sql脚本资料：join_query.sql
执行sql脚本直接导入数据到数据库中

mysql -uroot -p123456
mysql>use db2;
mysql>source /home/tarena/join_query.sql

也可以将以下内容插入到数据库中

create table if not exists province(
id int primary key auto_increment,
pid int,
pname varchar(15)
)default charset=utf8;

insert into province values
(1, 130000, '河北省'),
(2, 140000, '陕西省'),
(3, 150000, '四川省'),
(4, 160000, '广东省'),
(5, 170000, '山东省'),
(6, 180000, '湖北省'),
(7, 190000, '河南省'),
(8, 200000, '海南省'),
(9, 200001, '云南省'),
(10,200002,'山西省');

create table if not exists city(
id int primary key auto_increment,
cid int,
cname varchar(15),
cp_id int
)default charset=utf8;

insert into city values
(1, 131100, '石家庄市', 130000),
(2, 131101, '沧州市', 130000),
(3, 131102, '廊坊市', 130000),
(4, 131103, '西安市', 140000),
(5, 131104, '成都市', 150000),
(6, 131105, '重庆市', 150000),
(7, 131106, '广州市', 160000),
(8, 131107, '济南市', 170000),
(9, 131108, '武汉市', 180000),
(10,131109, '郑州市', 190000),
(11,131110, '北京市', 320000),
(12,131111, '天津市', 320000),
(13,131112, '上海市', 320000),
(14,131113, '哈尔滨', 320001),
(15,131114, '雄安新区', 320002);

create table if not exists county(
id int primary key auto_increment,
coid int,
coname varchar(15),
copid int
)default charset=utf8;

insert into county values
(1, 132100, '正定县', 131100),
(2, 132102, '浦东新区', 131112),
(3, 132103, '武昌区', 131108),
(4, 132104, '哈哈', 131115),
(5, 132105, '安新县', 131114),
(6, 132106, '容城县', 131114),
(7, 132107, '雄县', 131114),
(8, 132108, '嘎嘎', 131115);

• 笛卡尔积
  每个表的数据量做乘法（谨慎使用）

select 字段名列表 from students, students2, setudents3

• 多表查询

select 字段名列表 from 表名列表 where 条件;

• 示例

1、显示省和市的详细信息
   河北省  石家庄市
   河北省  廊坊市
   湖北省  武汉市
   
   select province.pname , city.cname from province, city where city.cp_id = province.pid;
   
   
2、显示 省 市 县 详细信息
  select province.pname, city.cname , county.coname from province, city, county where city.cp_id = province.pid and county.copid = city.cid;
  

连接查询

• 内连接（结果同多表查询，显示匹配到的记录）

select 字段名 from  表1 inner join 表2 on 条件 inner join 表3 on 条件;

eg1 : 显示省市详细信息
   	select province.pname, city.cname from province inner join city on province.pid = city.cp_id;
     
eg2 : 显示 省 市 县 详细信息
  	select province.pname, city.cname, county.coname from province inner join city on province.pid = city.cp_id inner join county on city.cid = county.copid;
  

• 左外连接

以 左表 为主显示查询结果

select 字段名 from 表1 left join 表2 on 条件 left join 表3 on 条件;
eg1 : 显示 省 市 详细信息（要求省全部显示）

 	select province.pname, city.cname from province left join city on province.pid = city.cp_id;
 	

• 右外连接

用法同左连接,以右表为主显示查询结果

select 字段名 from 表1 right join 表2 on 条件 right join 表3 on 条件;

数据导入

source 文件名.sql

作用

把文件系统的内容导入到数据库中
语法（方式一）

load data infile “文件名”
into table 表名
fields terminated by “分隔符”
lines terminated by “\n”
示例
scoretable.csv文件导入到数据库db2的表

1、将scoretable.csv放到数据库搜索路径中
   mysql>show variables like 'secure_file_priv'; 
         /var/lib/mysql-files/
   Linux: sudo cp /home/tarena/scoreTable.csv /var/lib/mysql-files/
2、在数据库中创建对应的表
  create table scoretab(
  rank int,
  name varchar(20),
  score float(5,2),
  phone char(11),
  class char(7)
  )charset=utf8;
3、执行数据导入语句
load data infile '/var/lib/mysql-files/scoreTable.csv'
into table scoretab
fields terminated by ','
lines terminated by '\n';

4、练习
  添加id字段,要求主键自增长,显示宽度为3,位数不够用0填充
  alter table scoretab add id int(3) zerofill primary key auto_increment first;

sudo  cp -fr  文件/文件夹   目的地址   强制递归复制 

操作数据库文件有权限时,可以通过chmod命令修改权限

sudo su 超级管理员权限更改
chmod 666 文件   让当前文件具备 rw-rw-rw
chmod 777 文件  让当前文件具备全部权限  rwx - rwx - rwx

语法（方式二）

source 文件名.sql

数据导出

作用

将数据库中表的记录保存到系统文件里

语法格式

select … from 表名
into outfile “文件名”
fields terminated by “分隔符”
lines terminated by “分隔符”;

练习

1、把sanguo表中英雄的姓名、攻击值和国家三个字段导出来,放到 sanguo.csv中  并将导出的文件 移动到 /home/tarena/
 	select name, attack, country from country.sanguo
 	into outfile '/var/lib/mysql-files/sanguo.csv'
 	fields terminated by ','
 	lines terminated by '\n';
 
 2、将mysql库下的user表中的 user、host两个字段的值导出到 user2.txt，将其存放在数据库目录下
    
 	select user, host from mysql.user
 	into outfile '/var/lib/mysql-files/user2.txt'
 	fields terminated by ' '
 	lines terminated by '\n';

注意

1、导出的内容由SQL查询语句决定
2、执行导出命令时路径必须指定在对应的数据库目录下

表的复制

1、表能根据实际需求复制数据

2、复制表时不会把KEY属性复制过来

语法

create table 表名 select 查询命令;

练习

1、复制sanguo表的全部记录和字段,sanguo2
  
2、复制sanguo表的 id,name,country 三个字段的前3条记录,sanguo4
  

注意

复制表的时候不会把原有表的 KEY 属性复制过来

复制表结构
create table 表名 select 查询命令 where false;

锁（自动加锁和释放锁）

目的

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

锁类型分类

读锁(共享锁)：select 加读锁之后别人不能更改表记录,但可以进行查询
写锁(互斥锁、排他锁)：加写锁之后别人不能查、不能改

锁粒度分类

表级锁 ：myisam
行级锁 ：innodb

存储引擎

定义
处理表的处理器
基本操作

1、查看所有存储引擎
mysql> show engines;
2、查看已有表的存储引擎
mysql> show create table 表名;
3、创建表指定
create table 表名
(...)engine=MyISAM,charset=utf8,auto_incremen
t=10000;
4、已有表指定
alter table 表名 engine=InnoDB;

如何选择存储引擎

1. 执行查操作多的表用MyISAM
2. 执行写操作多的表用InnoDB
3. 临时表 : MEMORY

mvcc多版本控制协议

1. 数据行中预留一个version字段　该字段在数据更新时＋１
2. 当执行有version字段的数据更新时　我们需要查询一下起初select获取到的该数据行的version是否改变;若改变，则放弃此次更新［证明在我执行更新操作之前，已经有其他客户端进行了该行数据的更新］后续；可重复执行若干次相同的更新方式；若多行执行失败，可考虑更改方案

MySQL的用户账户管理管理

开启MySQL远程连接

更改配置文件,重启服务!
1、sudo su
2、cd /etc/mysql/mysql.conf.d
3、cp mysqld.cnf mysqld.cnf.bak
4、vi mysqld.cnf #找到bind-address = 127.0.0.1 注释上
5、保存退出
6、service mysql restart
vi使用 : 按i ->编辑文件 ->ESC ->shift+: ->wq

添加授权用户

1、用root用户登录mysql
mysql -uroot -p123456
2、授权
grant 权限列表 on 库.表 to "用户名"@"地址"
identified by "密码"
3、刷新权限
flush privileges;

授权关键字
all privileges,select,insert ... ...
库.表 : *.* 代表所有库的所有表
ex: db2.*
地址: localhost 或者 具体ip 或者 %
当地址 用 % 代表可以用任何地址进入mysql

示例
1、添加授权用户work,密码123,对所有库的所有表有所有权
限

mysql>grant all privileges on *.* to'work'@'%' identified by '123';
mysql>flush privileges;

2、添加授权用户work2,密码123,对(country)库的所有表只有(select,update)权限

mysql>grant select,update on country.* to 'work2'@'%' identified by '123';
mysql>flush privileges;

事务四大特性(ACID)

1、原子性(atomicity)
一个事务必须视为一个不可分割的最小工作单元,整个事务中
的所有操作要么全部提交成功,要么全部失败回滚,对于一个
事务来说,不可能只执行其中的一部分操作
2、一致性(consistency)
数据库总是从一个一致性的状态转换到另一个一致性的状态
3、隔离性(isolation)
一个事务所做的修改在最终提交以前,对其他事务是不可见的
4、持久性(durability)
一旦事务提交,则其所做的修改就会永久保存到数据库中。此时即使系统崩溃,修改的数据也不会丢失
注意
1、事务只针对于表记录操作(增删改)有效,对于库和表的操
作无效
2、事务一旦提交结束,对数据库中数据的更改是永久性的
mysql.user - 显示全局的mysql用户;该表显示的具体权限只针对于全局用户有效［当前用户 - 指对当前mysql中所有库及所有表都有响应相应的用户］

mysql.db - 显示只有对特定db有操作权限的用户［grant时被指定的数据库的用户］

当mysql服务接到sql语句的时候，权限排查mysql.user -> mysql.db

DCL

-- 创建可以远程登录的root账号并为其指定口令
create user 'root'@'%' identified by '123456';

-- 为远程登录的root账号授权操作所有数据库所有对象的所有权限并允许其将权限再次赋予其他用户
grant all privileges on *.* to 'root'@'%' with grant option;

-- 创建名为hellokitty的用户并为其指定口令
create user 'hellokitty'@'%' identified by '123123';

-- 将对school数据库所有对象的所有操作权限授予hellokitty
grant all privileges on school.* to 'hellokitty'@'%';

-- 召回hellokitty对school数据库所有对象的insert/delete/update权限
revoke insert, delete, update on school.* from 'hellokitty'@'%';

说明：创建一个可以允许任意主机登录并且具有超级管理员权限的用户在现实中并不是一个明智的决定，因为一旦该账号的口令泄露或者被破解，数据库将会面临灾难级的风险。

MySQL调优

存储引擎优化
1、读操作多:MyISAM
2、写操作多:InnoDB
索引优化
在 select、where、order by 常涉及到的字段建立索引
SQL语句优化

1. 单条查询最后添加 LIMIT 1,停止全表扫描
2. where子句中不使用 != ,否则放弃索引全表扫描
3. 尽量避免 NULL 值判断,否则放弃索引全表扫描

优化前:select number from t1 where numberis null;
优化后:select number from t1 where number=0;
#在number列上设置默认值0,确保number列无NULL值

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 name from t1 where name like "c%";

6. 尽量避免使用 in 和 not in,否则全表扫描

优化前:select id from t1 where id in(1,2,3,4);
优化后:select id from t1 where id between 1 and 4;

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

文件权限修改

sudo  cp -fr  文件/文件夹   目的地址   强制递归复制 

chmod 666 文件   让当前文件具备 rw-rw-rw

chmod 777 文件  让当前文件具备全部权限  rwx - rwx - rwx

vim一些简单的操作

		执行u进行编辑撤回  

		行数 + shift + g    跳转到指定行数

		连续按两次d  可进行当前行的删除

		n[行数] + 两次d    可删除从当前行 + n 行的数据  

			ex:    3   dd    删除当前行往下3行数据【包含当前行】

:sp  水平分屏
:vs  垂直分屏 
退出当前光标所在分屏[等同于退出vim命令]  :q

o 直接在当前行新起一空行且进入插入模式

注意：光标移动  ctrl + w  + H or J or K or L

单行复制:
	yy 复制当前光标行整行内容
	p  粘贴
多行复制:
	n + yy  复制当前行 + [n-1]行内容   

选择复制:
	v +  H or J or K or L  移动光标至欲复制内容结尾， 点击 y 进行复制；复制后执行 v + H or J or K or L  + p  可把复制的内容粘贴至当前光标位置

	:E  打开当前文件所在路径下的 列表页， 在当前页可进行其他文件的查看；将光标移动到想查看的文件 -> 执行回车即可进入其vi模式
		
	最终 做完内容修改后  切记需要执行保存
	:w   
	:wq	
		
  :$    立刻跳转到当前文件最后一行
  shift + g  同上