MySQL

　　　　　　　　　　MySQL软件的安装　　　　　　　　　　

　　1.软件下载地址　　

下载地址：去下载

打开上面的连接页面，选择对应箭头所指的压缩包进行下载。

在点击了下之后，跳转至可以看到此内容的页面，不需要去登录注册，直接点击下面的进行下载即可。

　　2.安装　　

安装链接：点击进入参考此安装示范。

　　　　　　　　　　　　MySQL之库操作　　　　　　　　　　　

　　1.创建数据库　　

#语法: CREATE DATABASE 数据库名 charset utf8;

CREATE DATABASE db1 charset utf8;-- 创建数据库,并指定编码

　　2.查看数据库　　

SHOW DATABASES;-- 查看数据库
SHOW CREATE DATABASE  数据库名;查看创建数据库的信息

　　3.选择数据库　　

USE db1;-- 选择进入数据库

　　4.删除数据库　　

DROP DATABASE db1;-- 删除数据库，db1为数据库名

　　5.命名规范　　

可以由字母、数字、下划线、＠、＃、＄
区分大小写
唯一性
不能使用关键字如: CREATE SELECT
不能单独使用数字
最长128位

　　6.注释　　

　　注释格式：#或--,注意"--"必须与文字中间隔开。

　　7.用户权限　　

1.用户管理
    创建用户
        create user '用户名'@'IP地址' identified by '密码';
    删除用户
        drop user '用户名'@'IP地址';
    修改用户
        rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';
2.授权管理
    show grants for '用户'@'IP地址'                  -- 查看权限
    grant  权限 on 数据库.表 to   '用户'@'IP地址'      -- 授权
    revoke 权限 on 数据库.表 from '用户'@'IP地址'      -- 取消权限

#创建新用户
create user 'alex'@'localhost' identified by '123456';

#授权方式一:为alex授权 db1数据库下的所有表的 查询.更新.修改权限
grant select,update,delete on db1.*  to 'alex'@'localhost';

#授权方式二:为alex 授权 所有库的所有权限(除grant权限外)
grant all privileges on *.*  to 'alex'@'localhost';

#刷新用户权限
flush privileges;

创建→授权→刷新

注意，权限赋予完成后，必须进行刷新用户权限。

　　8.修改密码　　

方式一: mysqladmin 命令

mysqladmin -u用户名 -p密码 password 新密码

方式二: 直接设置用户密码

set password for '用户名'@'IP' = password('新密码');           
flush privileges;

方式三:修改mysql库下的user表

5.7版本修改密码方式:
update mysql.user set authentication_string=password('新密码') where user= '用户名'          
flush privileges; -- 刷新权限
             
5.6 版本
update mysql.user set password = password('新密码') where user= '用户名'
flush privileges; -- 刷新权限

版本不同，格式有所差别

　　9.忘记密码　　

在安装好MySQL服务端时，会在cmd窗口中会生成一个唯一密码，如果不幸在安装过程之中没注意或者忘记此root账号的密码，可采用下面方式进行解决:

第一种方法

1.首先打开cmd 窗口,关闭mysql服务

net stop mysql

2.然后跳过权限检查,启动mysql,输入命令

mysqld --skip-grant-tables

3.重新打开一个新的cmd窗口,启动客户端(已跳过权限检查,可以直接登录)

mysql

4.直接进来,修改密码

update mysql.user set authentication_string=password('123456') where user='root'

5. 刷新权限

flush privileges;

第二种方式

1.在安装目录下新建mysql-init.txt，输入

ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';

保存。

注意:5.7.5以及以前版本输入SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');

2.关闭mysql服务，然后在cmd中进入bin目录，输入mysqld --init-file=D:\Program Files\mysql-5.7.16-winx64\mysql-init.txt，执行完成后，删除mysql-init.txt文件，重启mysql服务即可利用新设置的密码重新登录MySQL。

在使用Navicat for MySQL时，如果出现错误：

Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILI

解决方法:

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'));

　　　　　　　　　　　　　　MySQL之表操作　　　　　　　　　　　　　　

　　一.什么是表　　

表(TABLE) 是一种结构化的文件，可用来存储某种特定类型的数据。表中的一条记录有对应的标题，标题称之为表的字段。

　　二.创建表　　

CREATE TABLE 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
)ENGINE=innodb DEFAULT CHARSET utf8;

格式

CREATE TABLE info1(
    id INT NOT NULL auto_increment PRIMARY KEY,-- id 整型 值非空 自增 作为主键
    NAME varchar(50) NOT NULL,                 -- NAME 可变字符类型(虽定义为50，但其取的是最大值，以实际字符大小为实际存储大小) 非空
    sex char(2)                                -- sex 字符型(此值为定值，定义为多少，在数据库中保存为多少，需根据实际情况确定其大小，此大小等于数据库中存储空间占用的大小) 
)ENGINE=innodb DEFAULT CHARSET utf8;           -- 存储引擎为innodb,默认编码为utf8类型。

示例

注意:自增长只能添加在主键或者唯一索引字段上。

主键:一种特殊的唯一索引，不允许有空值，如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一。

create table tb1(
            nid int not null auto_increment primary key,
            num int null
)
或
create table tb1(
           nid int not null,
           num int not null,
           primary key(nid,num)
)

示例

自增:如果为某列设置自增列，插入数据时无需设置此列，默认将自增（表中只能有一个自增列）

　　三.查询表数据　　

select 字段(多个以","间隔) from 表名;-- 查询表数据
desc 表名;                           -- 查看表结构
show create table student;　　       -- 查看创建表信息

SELECT * FROM info;-- 查看表数据
DESC info;-- 表结构
SHOW CREATE DATABASE db1;-- 查看创建信息
DROP TABLE info1;-- 删除表

示例

　　四.修改表结构　　

1.添加表字段

alter table 表名 add 字段名 类型 约束;

2.修改表字段

方式一: alter table student modify 字段 varchar(100) null;
方式二: alter table student change 旧字段 新字段 int not null default 0;

注意：

change 可以改变字段名字和属性。
modify只能改变字段的属性。

3.删除表字段

alter table student drop 字段名;

4.更新表名称

rename table 旧表名 to 新表名;

具体事例：

ALTER TABLE info ADD age INT NOT NULL;-- 添加表字段
ALTER TABLE info DROP age;-- 删除表字段

ALTER TABLE info MODIFY name VARCHAR(50) NOT NULL;-- 修改表字段属性(modify)
ALTER TABLE info CHANGE name names VARCHAR(50) NOT NULL;-- 修改表字段名(change,可改属性+名称)

ALTER TABLE info ADD PRIMARY KEY(id);-- 添加主键
ALTER TABLE info DROP PRIMARY KEY;-- 删除主键

ALTER TABLE info ALTER sex SET DEFAULT '男';-- 设置字段的默认值
ALTER TABLE info ALTER sex DROP DEFAULT;-- 删除字段的默认值

RENAME TABLE info1 to info;-- 修改表名

实例

注意：如果主键为自增长,以上方式则不被允许执行,请先去掉主键自增长属性,然后再移除主键。

alter table student modify id int not null,drop primary key

#添加外键: 
alter table 从表 add CONSTRAINT fk_test foreign key 从表(字段) REFERENCES 主表(字段);

#移除外键: 
alter table 表 drop foreign key 外键名称;

外键更新操作

#添加外键约束
ALTER table person add constraint fk_did FOREIGN key(dept_id) REFERENCES dept(did);
#删除外键约束
ALTER TABLE person drop FOREIGN key fk_did;

已经创建表后,追加外键约束

定义外键的条件：

外键对应的字段数据类型保持一致,且被关联的字段(即references指定的另外一个表的字段)，必须保证唯一
所有tables的存储引擎必须是InnoDB类型.
外键的约束4种类型: 1.RESTRICT 2. NO ACTION 3.CASCADE 4.SET NULL(重要的是1,4)

　　建议:1.如果需要外键约束,最好创建表同时创建外键约束.
　　　　 2.如果需要设置级联关系,删除时最好设置为 SET NULL.
注：插入数据时，先插入主表中的数据，再插入从表中的数据。
　　删除数据时，先删除从表中的数据，再删除主表中的数据。

　　如果外键设置后想修改,那么只能是先删除,再添加

#修改默认值 : 
alter table 表 alter 字段 set default 100;
#删除默认值 :
alter table 表 alter 字段 drop default;

默认值更新操作

#删除表
drop table 表名;
 
#清空表
truncate table 表名;

删除|清空表

　　五.复制表　　

只复制表结构和表中数据。

CREATE TABLE tb2 SELECT * FROM tb1;

注意：主键自增/索引/触发器/外键不会被复制

只复制表结构

create table tb2 like tb1;

注意: 数据/触发器/外键不会被复制。

CREATE TABLE info1 SELECT * FROM info; 

CREATE TABLE info1 SELECT * FROM info WHERE 1!=1;
CREATE TABLE info2 LIKE info;

示例

　　　　　　　　　　　　　　　数据类型　　　　　　　　　　　　　　　

数值、字符串类型、日期/时间和其他类型。

1.二进制类型

bit[(M)]:   二进制位（101001），m表示二进制位的长度（1-64），默认m＝1

2.整数类型

tinyint[(m)] [unsigned] [zerofill]

        小整数，数据类型用于保存一些范围的整数数值范围：
        有符号：-128 ～ 127.
        无符号：255

        注意： MySQL中无布尔值，使用tinyint(1)构造。

int[(m)][unsigned][zerofill]

        整数，数据类型用于保存一些范围的整数数值范围：
        有符号： -2147483648 ～ 2147483647
        无符号：4294967295

bigint[(m)][unsigned][zerofill]
        大整数，数据类型用于保存一些范围的整数数值范围：
        有符号：-9223372036854775808 ～ 9223372036854775807
        无符号：18446744073709551615

整数类型

作用：存储年龄，等级，id，各种号码等

注意：为该类型指定宽度时，仅仅只是指定查询结果的显示宽度，与存储范围无关，所以我们使用默认的就可以了。
有符号和无符号的最大数字需要的显示宽度均为10。
针对有符号的最小值则需要11位才能显示完全，所以int类型默认的显示宽度为11是非常合理的。

3.小数型

decimal[(m[,d])] [unsigned] [zerofill]
        准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。

        特别的：对于精确数值计算时需要用此类型
        decaimal能够存储精确值的原因在于其内部按照字符串存储。

    FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
        单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
        有符号：(-3.402823466E+38 to -1.175494351E-38),0,(1.175494351E-38 to 3.402823466E+38)
        无符号：0，(1.175 494 351 E-38，3.402 823 466 E+38)

        **** 数值越大，越不准确 ****

    DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
        双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。

        有符号：(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，
               (2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
        无符号：0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
        
        **** 数值越大，越不准确 ****

小数类型

作用：存储薪资、身高、体重、体质参数等

4.字符型

char (m)
        char数据类型用于表示固定长度的字符串，可以包含最多达255个字符。其中m代表字符串的长度。
        
        PS: 即使数据小于m长度，也会占用m长度,但是在查询时，查出的结果会自动删除尾部的空格
        
        特点:定长，简单粗暴，浪费空间，存取速度快
            
    varchar(m)
        varchar 数据类型用于变长的字符串，可以包含最多达65535个字符(理论上可以,但是实际上在超出21845长度后,mysql会自动帮您转换数据类型为文本类型)。
        其中m代表该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中。

        PS: varchar类型存储数据的真实内容，例如:如果'ab  ',尾部的空格也会被存起来
        强调：varchar类型会在真实数据前加1-2Bytes的前缀，该前缀用来表示真实数据的bytes字节数（1-2Bytes最大表示65535个数字，正好符合mysql对row的最大字节限制，即已经足够使用）
        如果真实的数据<255bytes则需要1Bytes的前缀（1Bytes=8bit 2**8最大表示的数字为255）
        如果真实的数据>255bytes则需要2Bytes的前缀（2Bytes=16bit 2**16最大表示的数字为65535）
        
        特点:变长，精准，节省空间，存取速度慢
        
        sql优化：创建表时，定长的类型往前放，变长的往后放:eg(性别|地址|描述信息)
        
    text
        text数据类型用于保存变长的大字符串，可以最多到65535 (2**16 − 1)个字符。

字符型

注意:虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。
因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡

5.枚举类型(了解)

enum
     
        An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)
        
        示例：
            CREATE TABLE user (
             name VARCHAR(40),
             sex ENUM('男', '女', '未知')
            );
            INSERT INTO user (name, sex) VALUES ('人1','男'), ('人2','女'),
                                                ('人3','未知'),('人4','人妖');

枚举

人4会插入成功吗??

6.集合类型(了解)

A SET column can have a maximum of 64 distinct members.
        示例：
        CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
        
        INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');

set

7.日期/时间类型

DATE     日期值            YYYY-MM-DD（1000-01-01/9999-12-31）

    TIME  时间值或持续时间     HH:MM:SS（'-838:59:59'/'838:59:59'）

    YEAR     年份值            YYYY（1901/2155）

    DATETIME 混合日期和时间值  YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59）

    TIMESTAMP 时间戳           YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）
        
作用：存储用户注册时间，文章发布时间，员工入职时间，出生时间，过期时间等

date|time

http://www.runoob.com/mysql/mysql-data-types.html.

http://dev.mysql.com/doc/refman/5.7/en/data-type-overview.html

　　　　　　　　　　　　　　MySQL之数据操作　　　　　　　　　　　　　　

对数据库进行的操作可分为四种:

使用INSERT实现数据的插入
UPDATE实现数据的更新
使用DELETE实现数据的删除
使用SELECT查询数据

　　一.插入操作之insert　　

#语法一: 按字段进行插入
insert into 表(字段1,字段2 ...) values (值1,值2 ...);
#语法二:按字段顺序插入
insert into 表 values (值1,值2 ...);
#语法三: 插入多条记录
insert into 表 values (值1,值2 ...) ,(值1,值2 ...)  ,(值1,值2 ...);
#语法四:插入查询结果
insert into 表(字段1,字段2 ...)  select 字段1,字段2 ... from 表;

插入操作

　　二.更新操作之update　　

#语法一: 更新整表数据
update 表 set 字段1= '值1', 字段2='值2' ... ;
#语法二:更新符合条件字段3的数据
update 表 set 字段1= '值1', 字段2='值2' ... where 字段3 = 值3;

update

　　三.删除操作之delete　　

delete from 表 ;#语法一:整表数据删除
delete from 表 where 字段1=值1;#语法二:删除符合 where后条件的数据
TRUNCATE info;-- 清空整张表

删除操作

truncate和delete的区别？[面试题]

TRUNCATE 在各种表上无论是大的还是小的都非常快。而DELETE 操作会被表中数据量的大小影响其执行效率.
TRUNCATE是一个DDL语言而DELETE是DML语句，向其他所有的DDL语言一样，他将被隐式提交，不能对TRUNCATE使用ROLLBACK命令。
TRUNCATE不能触发触发器，DELETE会触发触发器。
当表被清空后表和表的索引和自增主键将重新设置成初始大小，而delete则不能。

　　四.查询操作之SELECT　　

　　根据查询功能的不同,我们可以为查询划分为2类:

　　1.单表查询

　　　　具体操作详见: http://www.cnblogs.com/wangfengming/articles/8064956.html

　　2.多表查询

　　　　具体操作详见: http://www.cnblogs.com/wangfengming/articles/8067220.html

　　五.MySQL的执行顺序　　

　　执行顺序: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY ->LIMITE

(7)     SELECT 
(8)     DISTINCT <select_list>
(1)     FROM <left_table>
(3)     <join_type> JOIN <right_table>
(2)     ON <join_condition>
(4)     WHERE <where_condition>
(5)     GROUP BY <group_by_list>
(6)     HAVING <having_condition>
(9)     ORDER BY <order_by_condition>
(10)    LIMIT <limit_number>

　　六.练习题　　

　　练习题1：https://www.cnblogs.com/wangfengming/articles/7944029.html　　　　　　

1.
CREATE DATABASE liuyan;

2.
DROP TABLE IF EXISTS liuyan;
CREATE TABLE liuyan(
  'id' int NOT NULL AUTO_INCREMENT,
  'title' varchar(32) NOT NULL,
  'author' varchar(16) NOT NULL DEFAULT 'youku',
  'addtime' datetime NOT NULL,
  'content' text NOT NULL,
  'status' char(1) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;　　



3.
ALTER TABLE liuyan ADD status  char(1) DEFAULT 0;

4.
ALTER TABLE liuyan ALTER author SET DEFAULT 'youku';
ALTER TABLE liuyan MODIFY author VARCHAR(16) NOT NULL;

5.
ALTER TABLE liuyan DROP isdelete;

6.
INSERT INTO `liuyan` VALUES ('1', '介绍', '大雄', '2017-02-14 09:59:37', '哥不是一匹好马，但也不是一头普通的毛驴', '0');
INSERT INTO `liuyan` VALUES ('2', '叮当猫', '熊熊', '2016-02-16 09:59:44', '你牙缝里有韭菜，扣出来贼哥吃', '0');
INSERT INTO `liuyan` VALUES ('3', '花花', '苗苗', '2017-05-28 09:59:52', '苗苗问花花:卖萌是褒义词还是贬义词？', '0');
INSERT INTO `liuyan` VALUES ('4', '霞哥', '大雄', '2017-08-29 09:59:57', '斗战色佛', '0');
INSERT INTO `liuyan` VALUES ('5', '晨晨', '逗比', '2010-06-22 10:00:03', '你笑起来像一朵菊花,菊花残，man腚伤', '0');

7.
UPDATE liuyan SET author="admin" WHERE id>3;

8.
DELETE FROM liuyan where id=4;

答案仅供参考

　　练习题2：http://www.cnblogs.com/wangfengming/articles/7889786.html

DROP TABLE IF EXISTS `ren`;
CREATE TABLE `ren` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `age` int NOT NULL,
  `salary` int NOT NULL,
  `leader` int NOT NULL,
  `menpai` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;


INSERT INTO `ren` VALUES ('1', '张丰', '100', '10000', '0', '武当');
INSERT INTO `ren` VALUES ('2', '张无忌', '20', '8000', '0', '明教');
INSERT INTO `ren` VALUES ('3','岳不群','40','6500','0','华山');
INSERT INTO `ren` VALUES ('4', '东方不败', '35', '12000', '0', '日月神教');
INSERT INTO `ren` VALUES ('5', '令狐冲', '21', '4000', '3', '华山');
INSERT INTO `ren` VALUES ('6', '林平芝', '22', '2000', '3', '华山');
INSERT INTO `ren` VALUES ('7','金毛狮王','50','10000','2','明教');
INSERT INTO `ren` VALUES ('8','张翠山','46','10000','1','武当');
INSERT INTO `ren` VALUES ('9', '张远桥', '55', '6500', '1', '武当');
INSERT INTO `ren` VALUES ('10', 'Alex', '12', '350', '0', 'python');

ren表的创建及数据插入

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `did` int NOT NULL AUTO_INCREMENT,
  `dname` varchar(30) NOT NULL,
  `address` varchar(50) NOT NULL,
  PRIMARY KEY (`did`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;


INSERT INTO `dept` VALUES ('1', '武当', '湖北');
INSERT INTO `dept` VALUES ('2', '华山', '陕西');
INSERT INTO `dept` VALUES ('3', '嵩山', '河南');
INSERT INTO `dept` VALUES ('4', '日月神教', '黑木崖');

dept表的创建及数据插入

-- 1.查询所有人员信息
SELECT * FROM ren;

-- 2.只查询人员的姓名和年龄
SELECT ren.name,ren.age FROM ren;

-- 3.查询年龄为20岁的有哪些人员
SELECT * FROM ren WHERE ren.age=20;

-- 4.查询60岁以下的人员有哪些人员
SELECT * FROM ren WHERE ren.age<60;

-- 5.查询50岁以上并且工资大于8000的人员有哪些
SELECT * FROM ren WHERE ren.age>60 AND ren.salary>8000;

-- 6.查询姓[张]的人员有哪些
SELECT * FROM ren WHERE ren.name LIKE '张%';

-- 7.查询哪些人员属于 武当/华山/嵩山
SELECT * FROM ren WHERE ren.menpai in('武当','华山','嵩山');

-- 8.查询工资在 5000-8900 的人员有哪些
SELECT * FROM ren WHERE ren.salary BETWEEN 5000 AND 8900;

-- 9.查询所有人员,要求按工资倒序排列
SELECT * FROM ren ORDER BY ren.salary DESC;

-- 10.查询令狐冲的领导人是谁
SELECT ren.leader FROM ren WHERE ren.name='令狐冲';

-- 11.查询人员表中最高工资是多少
SELECT MAX(ren.salary) as '最高工资' FROM ren;

-- 12.查询人员表中最低工资是多少
SELECT MIN(ren.salary) as '最低工资' FROM ren;

-- 13.查询所有人员的平均工资是多少
select AVG(ren.salary) '平均工资' from ren;

-- 14.查询所有人员的工资总和是多少
select SUM(ren.salary) '工资之和' from ren;

-- 15.查询目前有多少个人员
SELECT COUNT(ren.name) '人员数量' FROM ren;

-- 16.查询当前武林中有哪些门派
SELECT ren.menpai FROM ren GROUP BY ren.menpai;

-- 17.查询 武当派 最高工资是谁
SELECT MAX(ren.salary) FROM ren WHERE ren.menpai='武当' GROUP BY ren.menpai;

-- 18.查询各门派的平均工资是多少
SELECT AVG(ren.salary),ren.menpai FROM ren GROUP BY ren.menpai; 

-- 19.查询当前武林中有哪些门派的平均工资大于8000 并按工资倒序排列
SELECT * from (SELECT AVG(ren.salary)as '平均工资',ren.menpai '门派' FROM ren  GROUP BY ren.menpai) as men_avg WHERE men_avg.平均工资>8000 ORDER BY men_avg.平均工资 DESC;

-- 20.查询当前人员表的中的第3条数据到第7条数据
SELECT * from ren where ren.id in(3,7);

-- 21.查询哪些门派下没有弟子 
SELECT pp1.dname FROM (SELECT ren.name,dept.dname FROM dept LEFT JOIN ren ON dept.dname=ren.menpai)as pp1 WHERE pp1.name is NULL;

-- 22.查询武当派下有哪些弟子
SELECT * FROM ren WHERE ren.menpai='武当';

-- 23.查询各门派的工资总和按倒序/正序排列
SELECT SUM(ren.salary),ren.menpai FROM ren GROUP BY ren.menpai ORDER BY SUM(ren.salary) desc|ASC;

-- 24.删除工资重复的人员,请保留年龄最大的一个人
-- 找到该删除的人员
-- SELECT ren.name '名字',ren.age '年龄',ren.salary '薪资' from ren,(SELECT p1.salary,p1.最大年龄 from (SELECT COUNT(ren.name) '人员数量',MAX(ren.age) '最大年龄',ren.salary FROM ren GROUP BY ren.salary) as p1 WHERE p1.人员数量>1) as pp1 WHERE ren.age!=pp1.最大年龄 AND ren.salary=pp1.salary;

-- 注意:删除这块不能先select出同一表中的某些值，再update这个表(在同一语句中)，即不能依据某字段值做判断再来更新某字段的值。所以必须先将SELECT出的结果通过中间表SELECT一遍。
delete from ren WHERE ren.name in(select data1.名字 FROM (SELECT ren.name '名字' from ren,(SELECT p1.salary,p1.最大年龄 from (SELECT COUNT(ren.name) '人员数量',MAX(ren.age) '最大年龄',ren.salary FROM ren GROUP BY ren.salary) as p1 WHERE p1.人员数量>1) as pp1 WHERE ren.age!=pp1.最大年龄 AND ren.salary=pp1.salary) as data1);

-- 25.将武当派 张丰 修改为 张三丰
UPDATE ren SET ren.name='张三丰' WHERE ren.name='张丰';

-- 26.将所有门派大哥工资上调10%,但不包括Alex.
SELECT (ren.salary)*1.1,ren.name FROM ren WHERE ren.name!='Alex';

-- 27.查看哪些人员的门派已登记地理位置.
SELECT * FROM (SELECT dept.address,ren.menpai FROM ren LEFT JOIN dept ON ren.menpai=dept.dname) as pp1 WHERE pp1.address is NOT NULL; 

-- 28.查询所有人员门派的位置信息,不存在位置信息则不显示
SELECT * from (SELECT ren.name,dept.address FROM ren LEFT JOIN dept ON ren.menpai=dept.dname) as pp1 WHERE pp1.address IS NOT NULL; 

-- 29.在湖北省内的门派中的人员有哪些.
SELECT ren.name FROM ren,(SELECT dept.dname from dept WHERE dept.address='湖北') as pp1 WHERE ren.menpai=pp1.dname;

-- 30.在陕西省内门派中的工资小于5000,年龄大于20岁的人员有哪些,按主键倒序排列
SELECT * FROM ren,(SELECT dept.dname FROM dept WHERE dept.address='陕西') as pp1 WHERE pp1.dname=ren.menpai AND ren.age>20 AND ren.salary<5000 ORDER BY ren.id DESC;

答案仅供参考

　　练习题3：https://www.cnblogs.com/wangfengming/articles/7978183.html

　　练习题4：https://www.cnblogs.com/wangfengming/articles/7891939.html

#1.查询学习课程"python"比课程 "java" 成绩高的学生的学号;
select python.s_id from (SELECT score.s_id,score.num from course,score WHERE course.c_id=score.c_id and course.c_name="python") python,
(SELECT score.s_id,score.num from course,score WHERE course.c_id=score.c_id and course.c_name="java") java WHERE python.s_id=java.s_id AND python.num>java.num;

#2.查询平均成绩大于65分的同学的姓名和平均成绩(保留两位小数);
 SELECT p1.s_name,p2.平均成绩 FROM student p1,(SELECT score.s_id,round(AVG(score.num),2) '平均成绩' FROM score GROUP BY score.s_id) as p2 WHERE p1.s_id=p2.s_id and p2.平均成绩>65;　　

#3.查询所有同学的姓名、选课数、总成绩；
 SELECT p1.s_name,p2.课程数,p2.总成绩 FROM student p1,(SELECT s_id,COUNT(c_name) '课程数',SUM(num) '总成绩' FROM score s1,course c1 WHERE s1.c_id=c1.c_id GROUP BY s_id) as p2 WHERE p1.s_id=p2.s_id;

#4.查询所有的课程的名称以及对应的任课老师姓名;
 SELECT c1.c_name,t1.t_name FROM course c1,teacher t1 WHERE c1.t_id=t1.t_id;

#5.查询没学过“大王”老师课的同学的姓名;
 SELECT s1.s_id FROM score s1,(SELECT c1.c_id FROM course c1,teacher t1 WHERE c1.t_id=t1.t_id AND t1.t_name !='大王') as p2 WHERE s1.c_id=p2.c_id GROUP BY s1.s_id; 

#6.查询学过'python'并且也学过编号'java'课程的同学的姓名;
 SELECT ss1.s_name from student ss1,(select COUNT(s1.s_id) '数量',s_id from score s1,(SELECT c1.c_id FROM course c1 WHERE c1.c_name in('python','java')) as p2 WHERE p2.c_id=s1.c_id GROUP BY s1.s_id) as pp2 WHERE pp2.数量=2 AND pp2.s_id=ss1.s_id;

#7.查询学过“alex”老师所教的全部课程的同学的姓名；
 SELECT ss1.s_name FROM student ss1,(SELECT COUNT(s_id) '数量',s_id FROM score s1,(SELECT c1.c_id from course c1,teacher t1 WHERE c1.t_id=t1.t_id AND t1.t_name="alex") as p2 WHERE p2.c_id=s1.c_id GROUP BY s1.s_id) as pp1 WHERE pp1.数量=2 AND ss1.s_id=pp1.s_id;

#8.查询挂科超过两门(包括两门)的学生姓名;
SELECT s11.s_name FROM student s11,(SELECT s1.s_id,COUNT(s1.c_id) '课程数量' from score s1 WHERE s1.num<60 GROUP BY s1.s_id) as p2 WHERE p2.s_id=s11.s_id AND p2.课程数量>=2;

#9.查询有课程成绩小于60分的同学的姓名;
 SELECT ss1.s_name FROM student ss1,(SELECT s1.s_id FROM score s1 WHERE s1.num<60) as ss2 WHERE ss2.s_id=ss1.s_id; 

#10.查询选修了全部课程的学生姓名;
 SELECT N.s_id from (SELECT COUNT(c1.c_id) '总课程数' FROM course c1) cc1,(SELECT COUNT(s1.s_id) '课程数量',s1.s_id FROM course c1,score s1 WHERE c1.c_id=s1.c_id GROUP BY s1.s_id) as N where N.课程数量=cc1.总课程数;

#11.查询至少有一门课程与“貂蝉”同学所学课程相同的同学姓名；
 SELECT st1.s_name from (select sc1.s_id from score sc1,(SELECT ss1.c_id from score ss1,(SELECT s1.s_id FROM student s1 WHERE s1.s_name='貂蝉') as dc WHERE dc.s_id=ss1.s_id) as s_c_id WHERE s_c_id.c_id=sc1.c_id GROUP BY s_id) as s_iid,student st1 WHERE st1.s_id=s_iid.s_id AND st1.s_name!='貂蝉';
#12.查询学过'貂蝉'同学全部课程 的其他同学姓名；
SELECT s1.s_name from student s1,(SELECT s1.s_id FROM score s1 WHERE s1.c_id in (SELECT score.c_id FROM student,score WHERE student.s_name='貂蝉' and student.s_id=score.s_id) and s1.s_id !=(SELECT student.s_id from student WHERE student.s_name='貂蝉') GROUP BY s1.s_id HAVING COUNT(s1.c_id)>=2) as p2 WHERE s1.s_id=p2.s_id;

#14.按平均成绩倒序显示所有学生的“python”、“java”、“linux”三门的课程成绩，按如下形式显示： 学生ID,python,java,linux,课程数,平均分
SELECT s1.s_id,
    (SELECT score.num from course,score WHERE score.s_id=s1.s_id and course.c_id=score.c_id and course.c_name='python') 'python',
    (SELECT score.num from course,score WHERE score.s_id=s1.s_id and course.c_id=score.c_id and course.c_name='java') 'java',
    (SELECT score.num from course,score WHERE score.s_id=s1.s_id and course.c_id=score.c_id and course.c_name='linux') 'linux',
    COUNT(s1.s_id) '课程数',
    AVG(s1.num) '平均分' 
from score s1 GROUP BY s1.s_id

#15.统计各科各分数段人数.显示格式:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60] 
SELECT s1.c_id,
            SUM(CASE WHEN s1.num BETWEEN 85 AND 100 THEN 1 else 0 END) '[85-100]',
            SUM(CASE WHEN s1.num BETWEEN 70 AND 85     THEN 1 else 0 END ) '[70-85]',
            SUM(CASE WHEN s1.num BETWEEN 60 AND 70     THEN 1 else 0 END ) '[60-70]',
            SUM(CASE WHEN s1.num<60 THEN 1 else 0 END ) '[,<60]'
 FROM score s1 GROUP BY s1.c_id
 
#16.查询每门课程被选修的次数
SELECT s1.c_id,COUNT(s1.c_id) FROM score s1 GROUP BY s1.c_id;

#17.查询出只选修了一门课程的学生的学号和姓名  
SELECT st1.s_id,st1.s_name from student st1,(SELECT s1.s_id,COUNT(s1.c_id) '课程数量' FROM score s1 GROUP BY s1.s_id) as p2 WHERE st1.s_id=p2.s_id AND p2.课程数量=1;

#18.查询学生表中男生、女生各有多少人
SELECT s.s_sex,COUNT(s.s_sex) '男女个数' FROM student s GROUP BY s.s_sex

#19.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
SELECT p1.c_id,p1.平均成绩 from (SELECT s1.c_id,AVG(s1.num) '平均成绩' FROM score s1 GROUP BY s1.c_id) as p1 ORDER BY p1.平均成绩,p1.c_id;

#20.查询课程名称为“python”，且分数低于60的学生姓名和分数
SELECT ss1.s_name,pp1.num FROM student ss1,(SELECT s1.s_id,s1.num FROM score s1,(SELECT c1.c_id FROM course c1 WHERE c1.c_name='python') as p1 WHERE s1.c_id=p1.c_id AND s1.num<60) pp1 WHERE ss1.s_id=pp1.s_id ;

参考答案4

　　　　MySQL之视图、触发器、存储过程、函数、事物、数据库锁　　　　

　　一.视图　　

用来解决虚拟表不能临时存储的问题。有结构无数据，没有实际存储表数据的文件。

视图有如下特点;
　　1. 视图的列可以来自不同的表，是表的抽象和逻辑意义上建立的新关系。
　　2. 视图是由基本表（实表）产生的表（虚表）。
　　3. 视图的建立和删除不影响基本表。
　　4. 对视图内容的更新（添加、删除和修改）直接影响基本表。
　　5. 当视图来自多个基本表时，不允许添加和删除数据。

create view 视图名称  as sql 查询语句;  #创建视图
select * from 视图名称;                 #使用视图
alter view 视图名称 AS SQL语句;         #修改视图
drop view ren_view;                     #删除视图

语法

CREATE VIEW p_view as SELECT * FROM score;
DROP view p_view;
ALTER VIEW p_view as SELECT score.num,score.s_id FROM score;
SELECT * FROM p_view;

示例

二.触发器

监视某种情况，并触发某种操作。

触发器创建语法四要素：1.监视地点(table)
　　　　　　　　　　　2.监视事件(insert/update/delete)
　　　　　　　　　　 3.触发时间(after/before)
　　　　　　　　　　　4.触发事件(insert/update/delete)

1.创建触发器语法

create trigger triggerName  after/before  insert/update/delete  on 表名
for each row #这句话是固定的
 begin
     #需要执行的sql语句
 end

语法

注意1:after/before: 只能选一个 ,after 表示后置触发, before 表示前置触发
注意2:insert/update/delete:只能选一个

#商品表
create table goods(
  id int primary key auto_increment,
  name varchar(20),
  num int
);
#订单表
create table order_table(
    oid int primary key auto_increment,
    gid int,
    much int
);

创建表进行验证

添加三条商品数据:

insert into goods(name,num) values('商品1',10),('商品2',10),('商品3',10);

在没使用触发器之前：假设我们现在卖了3个商品1，我们需要做两件事，“添加订单”和“减少储存”。

1.1 往订单表插入一条记录

insert into order_table(gid,much) values(1,3);

1.2 更新商品表商品1的剩余数量

update goods set num=num-3 where id=1;

现在，我们来创建一个触发器：

create trigger tg1 after insert on  order_table
for each row
begin    
  update goods set num = num -3 where id = 1;
end

触发器tg1

此时我们只要执行:

insert into order_table(gid,much) values(1,3);

会发现商品1的数量变为7了，说明在我们插入一条订单的时候，触发器自动帮我们做了更新操作。

但现在会有一个问题，因为我们触发器里面num和id都是写死的，所以不管我们买哪个商品，最终更新的都是商品1的数量。比如：我们往订单表再插入一条记录：

insert into order_table(gid,much) values(2,3);

执行完后会发现商品1的数量变4了，而商品2的数量没变，这样显然不是我们想要的结果。我们需要改改我们之前创建的触发器。

对于insert而言，新插入的行用new来表示，行中的每一列的值用new.列名来表示。

改变后的触发器:

create trigger tg2 after insert on order_table
for each row
begin
 update goods set num = num-new.much  where id = new.gid;
end

View Code

第二个触发器创建完毕，先把第一个触发器删掉

drop trigger tg1;

再次测试，即插入一条订单记录：

insert into order_table(gid,much) values(2,3)

执行完发现商品2的数量变为7了，现在就对了。

现在还存在两种情况：

1.当用户撤销一个订单的时候，我们这边直接删除一个订单，并且需要把对应的商品数量再加回去。

对于delete而言：原本有一行,后来被删除，想引用被删除的这一行，用old来表示旧表中的值，old.列名可以引用原(旧)表中的值。

那我们的触发器就该这样写：

create trigger tg3 afert delete  on order_table
for each row
bigen
    update goods set num = num + old.much where id = old.gid;-- (注意这边的变化)
end

View Code

2.当用户修改一个订单的数量时，触发器也应该作相应的改变，修改如下:

create trigger tg4 after update on order_table
for each row
begin
    update goods set num = num+old.much-new.much where id = old.gid;
end

View Code

　　三.存储过程　　

　　MySQL数据库在5.0版本后开始支持存储过程，它是类似于函数(方法),简单的说存储过程是为了完成某个数据库中的特定功能而编写的语句集合，该语句集包括SQL语句（对数据的增删改查）、条件语句和循环语句等。

1. 查看现有的存储过程

show procedure status;

2 .删除存储过程

drop procedure 存储过程名称;

3. 调用存储过程

call 存储过程名称(参数入/出类型 参数名 数据类型);

4. 存储过程

4.1创建存储过程(定义函数,无参数形式)

CREATE PROCEDURE p1()
BEGIN
INSERT into goods VALUES(null,'hanshe',50);
SELECT * FROM goods;
END
# 调用存储过程(调用函数)
call p1();

创建并调用

4.2. 有参数形式分三种:

in 入参
out 出参，执行存储过程之后，可以从中拿出结果进行进一步操作。
inout 出入参

参数声明注意：参数类型(in|out|inout) 参数名称参数类型

set @i=0;
SELECT num into @i from goods WHERE id=1;#num→@i
SELECT @i;

into的用法

注意:into不能查询多个字段赋给多个变量，INTO的功能相当于赋值

CREATE PROCEDURE p2(in i INT,INOUT names varchar(50))
BEGIN
    UPDATE goods SET name=names WHERE id=i;
    SELECT names;
END

set @names='大额';
call p2(4,@names);
SELECT @names;

输入输出参数的返回(即将执行结果返回)

CREATE PROCEDURE p3(in flag char(5),in nums INT)
BEGIN
    IF flag='true' THEN
    SELECT * FROM goods WHERE num<nums;
    ELSEIF flag='false' THEN
    SELECT * FROM goods WHERE num>nums;
    ELSE
    SELECT * FROM goods;
    END IF;
END

call p3('true',20);
call p3('false',20);
call p3('wd',20);

执行条件判断

CREATE PROCEDURE p4(in n INT,OUT he INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE sum INT;
    SET sum=0;
    WHILE i <= n DO
    SET sum = sum + i;
    SET i = i +1;

    END WHILE;
    SET he=sum;
END

set @he=0;
call p4(100,@he);
SELECT @he;

出参(out)的使用:1-100的累加和循环

存储过程优点：
1、存储过程增强了SQL语言灵活性。
　　　　　　存储过程可以使用控制语句编写，可以完成复杂的判断和较复杂的运算，有很强的灵活性；
2、减少网络流量，降低了网络负载。
　　　　　　存储过程在服务器端创建成功后，只需要调用该存储过程即可，而传统的做法是每次都将大量的SQL语句通过网络发送至数据库服务器端然后再执行
3、存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译。
　　　　　　一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
存储过程缺点：
　　1、扩展功能不方便
　　 2、不便于系统后期维护

　　四.函数　　

FORMAT(X,D)
    将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若  D 为 0, 则返回结果不带有小数点，或不含小数部分。
    例如：
        SELECT FORMAT(12332.1,4); 结果为： '12,332.1000'

    INSERT(str,pos,len,newstr)
        在str的指定位置插入字符串
            pos：要替换位置真实位置
            len：替换的长度
            newstr：新字符串
        例如:
            SELECT INSERT('abcd',1,2,'tt'); 结果为: 'ttcd'
            SELECT INSERT('abcd',1,4,'tt'); 结果为: 'tt'
        特别的：
            如果pos超过原字符串长度，则返回原字符串
            如果len超过原字符串长度，则由新字符串完全替换
    
    INSTR(str,substr)
        返回字符串 str 中子字符串的第一个出现位置。

    LEFT(str,len)
        返回字符串str 从开始的len位置的子序列字符。
        例如:
            SELECT INSTR('abc','c'); 结果为： 3
            SELECT INSTR('abc','d'); 结果为： 0

#时间和日期函数
NOW()    返回当前的日期和时间

内建函数

 DATE_FORMAT(date,format) 根据format字符串格式化date值

       mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
        -> 'Sunday October 2009'
       mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
        -> '22:23:00'
       mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
        ->                 '%D %y %a %d %m %b %j');
        -> '4th 00 Thu 04 10 Oct 277'
       mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
        ->                 '%H %k %I %r %T %S %w');
        -> '22 22 10 10:23:00 PM 22:23:00 00 6'
       mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
        -> '1998 52'
       mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
        -> '00'

重点

1、自定义函数

CREATE FUNCTION fun1(i1 int,i2 int)
RETURNS INT //设置返回类型
BEGIN
    DECLARE sum int default 0;
    set sum = i1+i2;
    RETURN(sum); //返回结果
end

自定义函数

2.调用自定义函数

select fun1(1,5);

直接调用自定义函数

select fun1(参数1,参数2),name from 表名

在sql语句中使用自定义函数

3.删除自定义函数

DROP FUNCTION fun_name;

View Code

　　五.事物处理　　　　

　　1.定义:一组sql语句的批量执行，这一组要么全部成功，要么全部失败。

　　2.出现这种技术的原因：现在的很多软件都是多用户，多程序，多线程的，对同一个表可能同时有很多人在用，为保持数据的一致性，所以提出了事务的概念。

　　　　eg：A 给B 要还钱，A 的账户-1000元， B 的账户就要+1000元，这两个update 语句必须作为一个整体来执行，不然A 扣钱了，B 没有加钱这种情况很难处理。

　　3.事物的特性:原子性(Atom）、一致性（Consistent）、隔离性（Isolate）、持久性（Durable）。ACID就是数据库事务正确执行的四个特性的缩写。

　　　　1> 原子性：对于其数据修改，要么全都执行，要么全都不执行。即将要执行的SQL语句作为一个整体。但其实这种原子性是可以打破的(通过设置回滚点)。也就是当一个事物在执行过程中因为一些语句出现错误导致整个事务无法继续，但是部分语句是正确的，此时，可以设置回滚点，当出现回滚时，则会回滚至SAVEPOINT sa1;

　　　　2>.一致性：数据库原来有什么样的约束，事务执行之后还需要存在这样的约束，所有规则都必须应用于事务的修改，以保持所有数据的完整性。

　　　　3>.隔离性：一个事务不能知道另外一个事务的执行情况（中间状态）

　　　　4>.持久性：即使出现致命的系统故障也将一直保持。不要告诉我系统说commit成功了，回头电话告诉我，服务器机房断电了，我的事务涉及到的数据修改可能没有进入数据库。

注意:

- 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
- 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
- 事务用来管理 insert,update,delete 语句

　　4.事务控制语句：

- BEGIN 或 START TRANSACTION：显式地开启一个事务；
- COMMIT：也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的；
- ROLLBACK：有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
- SAVEPOINT ：保存点,可以把一个事物分割成几部分.在执行ROLLBACK 时可以指定在什么位置上进行回滚操作.

　　注意: SET AUTOCOMMIT=0 ;禁止自动提交和 SET AUTOCOMMIT=1 开启自动提交.

　　5.例子:A给B转账

　　　　5.1 创建表

创建表

　　　　5.2 插入数据

insert into account (id,name,money) values(1,'A',250),(2,'B',5000);

　　　　5.3 执行转账

转账程序

　　　　5.4 事物回滚点的使用

SQL出错情况

回滚至sa1处

　　六.数据锁　　　　

1. 锁的基本概念

　　当并发事务同时访问一个资源时，有可能导致数据不一致，因此需要一种机制来将数据访问顺序化，以保证数据库数据的一致性。

2. 锁的基本类型

　　多个事务同时读取一个对象的时候，是不会有冲突的。同时读和写，或者同时写才会产生冲突。因此为了提高数据库的并发性能，通常会定义两种锁：共享锁和排它锁。

　　2.1 共享锁（Shared Lock，也叫S锁）

　　　　共享锁(S)表示对数据进行读操作。因此多个事务可以同时为一个对象加共享锁。（如果试衣间的门还没被锁上，顾客都能够同时进去参观）

　　2.2 排他锁(Exclusive Lock，也叫X锁)

　　　　排他锁(X)表示对数据进行写操作。如果一个事务对对象加了排他锁，其他事务就不能再给它加任何锁了。(某个顾客把试衣间从里面反锁了，其他顾客想要使用这个试衣间，就只有等待锁从里面给打开了).

eg：

充值操作

取款操作

由于查询操作是两个用户对同一账号的同时查询，那么查到的都是1000，可是当一个用户进行充值100后，在另一方取款时并没有顺序的优先，导致同一账户金额出现差额情况，通过加锁的方式解决此问题，提供了以下两种锁：

1> 悲观锁即每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block(阻塞)直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制.

注意：要使用悲观锁，我们必须关闭mysql数据库的自动提交属性.因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。关闭自动提交命令为：set autocommit=0;

设置完autocommit后，我们就可以执行我们的正常业务了。具体如下：

注意：其实就是加上(for update)

start transaction;0.开始事务
             
            -- 1.查询账户余额
            set @m = 0; -- 账户余额
            select money into @m from account where id = 1 for update;-- 如果以id为条件，锁住的是一条数据，如果表无主键，则锁住的是整张表
            select @m;

            update account set money = @m -100 where id = 1; -- 2.修改账户余额
            select * FROM account where id = 1;
            commit;-- 3. 提交事务

第一个界面

start transaction;-- 0.开始事务
             #1.查询账户余额
            set @m = 0; -- 账户余额
            select money into @m from account where id = 1 for update;
            select @m;
             
            # 2.修改账户余额
            update account set money = @m +100 where id = 1;
            select * FROM account where id = 1;
            commit;-- 3. 提交事务

在另外的查询页面执行

会发现当前查询会进入到等待状态,不会显示出数据,当上面的sql执行完毕提交事物后,当前sql才会显示结果.

注意:

在使用悲观锁时,如果表中没有指定主键,则会进行锁表操作.
悲观锁的确保了数据的安全性，在数据被操作的时候锁定数据不被访问，但是这样会带来很大的性能问题。因此悲观锁在实际开发中使用是相对比较少的。

2>.乐观锁:即每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。　

使用乐观锁的两种方式:

　　1.使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。

　　　　数据版本:即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。

-- 1.查询账户余额
set @m = 0; -- 账户余额
select money into @m from account where id = 1 ;
select @m;
-- 2.查询版本号
set @version = 0; -- 版本号
select version into @version from account where id = 1 ;
select @version;
 
-- 3.修改账户余额
update account set money = @m -100,version=version+1 where id = 1 and version = @version;
 
select * FROM account where id = 1;

实现方式

　　2.乐观锁的第二种实现方式和第一种差不多，同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间戳（datatime）, 和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。

悲观锁与乐观锁的优缺点:
　　　　单纯对比无优劣。
　　　　乐观锁适用于写入比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。
　　　　但如果经常产生冲突，上层应用会不断的进行重试操作，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适.

　　七.数据库备份　　

　　mysqldump 命令将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本文件中。

　　mysqldump命令的工作原理很简单。它先查出需要备份的表的结构，再在文本文件中生成一个CREATE语句。然后，将表中的所有记录转换成一条INSERT语句。然后通过这些语句，就能够创建表并插入数据。

1.使用mysqldump实现逻辑备份

　　语法

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

　　单库备份

mysqldump -uroot -p123456 db1 > c:/db1.sql
mysqldump -uroot -p123456 db1 table1 table2 > c:/db1-table1-table2.sql

　　多库备份

mysqldump -uroot -p123456 --databases db1 db2 mysql db3 > c:/db1_db2_mysql_db3.sql

　　备份所有库

mysqldump -uroot -p123456 --all-databases > c:/all.sql

--all-databases  , -A
导出全部数据库。
mysqldump  -uroot -p --all-databases
--all-tablespaces  , -Y
导出全部表空间。
mysqldump  -uroot -p --all-databases --all-tablespaces
--no-tablespaces  , -y
不导出任何表空间信息。
mysqldump  -uroot -p --all-databases --no-tablespaces
--add-drop-database
每个数据库创建之前添加drop数据库语句。
mysqldump  -uroot -p --all-databases --add-drop-database
--add-drop-table
每个数据表创建之前添加drop数据表语句。(默认为打开状态，使用--skip-add-drop-table取消选项)
mysqldump  -uroot -p --all-databases  (默认添加drop语句)
mysqldump  -uroot -p --all-databases –skip-add-drop-table  (取消drop语句)
--add-locks
在每个表导出之前增加LOCK TABLES并且之后UNLOCK  TABLE。(默认为打开状态，使用--skip-add-locks取消选项)
mysqldump  -uroot -p --all-databases  (默认添加LOCK语句)
mysqldump  -uroot -p --all-databases –skip-add-locks   (取消LOCK语句)
--allow-keywords
允许创建是关键词的列名字。这由表名前缀于每个列名做到。
mysqldump  -uroot -p --all-databases --allow-keywords
--apply-slave-statements
在'CHANGE MASTER'前添加'STOP SLAVE'，并且在导出的最后添加'START SLAVE'。
mysqldump  -uroot -p --all-databases --apply-slave-statements
--character-sets-dir
字符集文件的目录
mysqldump  -uroot -p --all-databases  --character-sets-dir=/usr/local/mysql/share/mysql/charsets
--comments
附加注释信息。默认为打开，可以用--skip-comments取消
mysqldump  -uroot -p --all-databases  (默认记录注释)
mysqldump  -uroot -p --all-databases --skip-comments   (取消注释)
--compatible
导出的数据将和其它数据库或旧版本的MySQL 相兼容。值可以为ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options等，
要使用几个值，用逗号将它们隔开。它并不保证能完全兼容，而是尽量兼容。
mysqldump  -uroot -p --all-databases --compatible=ansi
--compact
导出更少的输出信息(用于调试)。去掉注释和头尾等结构。可以使用选项：--skip-add-drop-table  --skip-add-locks --skip-comments --skip-disable-keys
mysqldump  -uroot -p --all-databases --compact
--complete-insert,  -c
使用完整的insert语句(包含列名称)。这么做能提高插入效率，但是可能会受到max_allowed_packet参数的影响而导致插入失败。
mysqldump  -uroot -p --all-databases --complete-insert
--compress, -C
在客户端和服务器之间启用压缩传递所有信息
mysqldump  -uroot -p --all-databases --compress
--create-options,  -a
在CREATE TABLE语句中包括所有MySQL特性选项。(默认为打开状态)
mysqldump  -uroot -p --all-databases
--databases,  -B
导出几个数据库。参数后面所有名字参量都被看作数据库名。
mysqldump  -uroot -p --databases test mysql
--debug
输出debug信息，用于调试。默认值为：d:t,/tmp/mysqldump.trace
mysqldump  -uroot -p --all-databases --debug
mysqldump  -uroot -p --all-databases --debug=” d:t,/tmp/debug.trace”
--debug-check
检查内存和打开文件使用说明并退出。
mysqldump  -uroot -p --all-databases --debug-check
--debug-info
输出调试信息并退出
mysqldump  -uroot -p --all-databases --debug-info
--default-character-set
设置默认字符集，默认值为utf8
mysqldump  -uroot -p --all-databases --default-character-set=utf8
--delayed-insert
采用延时插入方式（INSERT DELAYED）导出数据
mysqldump  -uroot -p --all-databases --delayed-insert
--delete-master-logs
master备份后删除日志. 这个参数将自动激活--master-data。
mysqldump  -uroot -p --all-databases --delete-master-logs
--disable-keys
对于每个表，用/*!40000 ALTER TABLE tbl_name DISABLE KEYS */;和/*!40000 ALTER TABLE tbl_name ENABLE KEYS */;语句引用INSERT语句。这样可以更快地导入dump出来的文件，因为它是在插入所有行后创建索引的。该选项只适合MyISAM表，默认为打开状态。
mysqldump  -uroot -p --all-databases 
--dump-slave
该选项将主的binlog位置和文件名追加到导出数据的文件中(show slave status)。设置为1时，将会以CHANGE MASTER命令输出到数据文件；设置为2时，会在change前加上注释。该选项将会打开--lock-all-tables，除非--single-transaction被指定。该选项会自动关闭--lock-tables选项。默认值为0。
mysqldump  -uroot -p --all-databases --dump-slave=1
mysqldump  -uroot -p --all-databases --dump-slave=2
--master-data
该选项将当前服务器的binlog的位置和文件名追加到输出文件中(show master status)。如果为1，将会输出CHANGE MASTER 命令；如果为2，输出的CHANGE  MASTER命令前添加注释信息。该选项将打开--lock-all-tables 选项，除非--single-transaction也被指定（在这种情况下，全局读锁在开始导出时获得很短的时间；其他内容参考下面的--single-transaction选项）。该选项自动关闭--lock-tables选项。
mysqldump  -uroot -p --host=localhost --all-databases --master-data=1;
mysqldump  -uroot -p --host=localhost --all-databases --master-data=2;
--events, -E
导出事件。
mysqldump  -uroot -p --all-databases --events
--extended-insert,  -e
使用具有多个VALUES列的INSERT语法。这样使导出文件更小，并加速导入时的速度。默认为打开状态，使用--skip-extended-insert取消选项。
mysqldump  -uroot -p --all-databases
mysqldump  -uroot -p --all-databases--skip-extended-insert   (取消选项)
--fields-terminated-by
导出文件中忽略给定字段。与--tab选项一起使用，不能用于--databases和--all-databases选项
mysqldump  -uroot -p test test --tab=”/home/mysql” --fields-terminated-by=”#”
--fields-enclosed-by
输出文件中的各个字段用给定字符包裹。与--tab选项一起使用，不能用于--databases和--all-databases选项
mysqldump  -uroot -p test test --tab=”/home/mysql” --fields-enclosed-by=”#”
--fields-optionally-enclosed-by
输出文件中的各个字段用给定字符选择性包裹。与--tab选项一起使用，不能用于--databases和--all-databases选项
mysqldump  -uroot -p test test --tab=”/home/mysql”  --fields-enclosed-by=”#” --fields-optionally-enclosed-by  =”#”
--fields-escaped-by
输出文件中的各个字段忽略给定字符。与--tab选项一起使用，不能用于--databases和--all-databases选项
mysqldump  -uroot -p mysql user --tab=”/home/mysql” --fields-escaped-by=”#”
--flush-logs
开始导出之前刷新日志。
请注意：假如一次导出多个数据库(使用选项--databases或者--all-databases)，将会逐个数据库刷新日志。除使用--lock-all-tables或者--master-data外。在这种情况下，日志将会被刷新一次，相应的所以表同时被锁定。因此，如果打算同时导出和刷新日志应该使用--lock-all-tables 或者--master-data 和--flush-logs。
mysqldump  -uroot -p --all-databases --flush-logs
--flush-privileges
在导出mysql数据库之后，发出一条FLUSH  PRIVILEGES 语句。为了正确恢复，该选项应该用于导出mysql数据库和依赖mysql数据库数据的任何时候。
mysqldump  -uroot -p --all-databases --flush-privileges
--force
在导出过程中忽略出现的SQL错误。
mysqldump  -uroot -p --all-databases --force
--help
显示帮助信息并退出。
mysqldump  --help
--hex-blob
使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用该选项。影响到的字段类型有BINARY、VARBINARY、BLOB。
mysqldump  -uroot -p --all-databases --hex-blob
--host, -h
需要导出的主机信息
mysqldump  -uroot -p --host=localhost --all-databases
--ignore-table
不导出指定表。指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名。例如：--ignore-table=database.table1 --ignore-table=database.table2 ……
mysqldump  -uroot -p --host=localhost --all-databases --ignore-table=mysql.user
--include-master-host-port
在--dump-slave产生的'CHANGE  MASTER TO..'语句中增加'MASTER_HOST=<host>，MASTER_PORT=<port>'  
mysqldump  -uroot -p --host=localhost --all-databases --include-master-host-port
--insert-ignore
在插入行时使用INSERT IGNORE语句.
mysqldump  -uroot -p --host=localhost --all-databases --insert-ignore
--lines-terminated-by
输出文件的每行用给定字符串划分。与--tab选项一起使用，不能用于--databases和--all-databases选项。
mysqldump  -uroot -p --host=localhost test test --tab=”/tmp/mysql”  --lines-terminated-by=”##”
--lock-all-tables,  -x
提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭--single-transaction 和--lock-tables 选项。
mysqldump  -uroot -p --host=localhost --all-databases --lock-all-tables
--lock-tables,  -l
开始导出前，锁定所有表。用READ  LOCAL锁定表以允许MyISAM表并行插入。对于支持事务的表例如InnoDB和BDB，--single-transaction是一个更好的选择，因为它根本不需要锁定表。
请注意当导出多个数据库时，--lock-tables分别为每个数据库锁定表。因此，该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。
mysqldump  -uroot -p --host=localhost --all-databases --lock-tables
--log-error
附加警告和错误信息到给定文件
mysqldump  -uroot -p --host=localhost --all-databases  --log-error=/tmp/mysqldump_error_log.err
--max_allowed_packet
服务器发送和接受的最大包长度。
mysqldump  -uroot -p --host=localhost --all-databases --max_allowed_packet=10240
--net_buffer_length
TCP/IP和socket连接的缓存大小。
mysqldump  -uroot -p --host=localhost --all-databases --net_buffer_length=1024
--no-autocommit
使用autocommit/commit 语句包裹表。
mysqldump  -uroot -p --host=localhost --all-databases --no-autocommit
--no-create-db,  -n
只导出数据，而不添加CREATE DATABASE 语句。
mysqldump  -uroot -p --host=localhost --all-databases --no-create-db
--no-create-info,  -t
只导出数据，而不添加CREATE TABLE 语句。
mysqldump  -uroot -p --host=localhost --all-databases --no-create-info
--no-data, -d
不导出任何数据，只导出数据库表结构。
mysqldump  -uroot -p --host=localhost --all-databases --no-data
--no-set-names,  -N
等同于--skip-set-charset
mysqldump  -uroot -p --host=localhost --all-databases --no-set-names
--opt
等同于--add-drop-table,  --add-locks, --create-options, --quick, --extended-insert, --lock-tables,  --set-charset, --disable-keys 该选项默认开启,  可以用--skip-opt禁用.
mysqldump  -uroot -p --host=localhost --all-databases --opt
--order-by-primary
如果存在主键，或者第一个唯一键，对每个表的记录进行排序。在导出MyISAM表到InnoDB表时有效，但会使得导出工作花费很长时间。 
mysqldump  -uroot -p --host=localhost --all-databases --order-by-primary
--password, -p
连接数据库密码
--pipe(windows系统可用)
使用命名管道连接mysql
mysqldump  -uroot -p --host=localhost --all-databases --pipe
--port, -P
连接数据库端口号
--protocol
使用的连接协议，包括：tcp, socket, pipe, memory.
mysqldump  -uroot -p --host=localhost --all-databases --protocol=tcp
--quick, -q
不缓冲查询，直接导出到标准输出。默认为打开状态，使用--skip-quick取消该选项。
mysqldump  -uroot -p --host=localhost --all-databases 
mysqldump  -uroot -p --host=localhost --all-databases --skip-quick
--quote-names,-Q
使用（`）引起表和列名。默认为打开状态，使用--skip-quote-names取消该选项。
mysqldump  -uroot -p --host=localhost --all-databases
mysqldump  -uroot -p --host=localhost --all-databases --skip-quote-names
--replace
使用REPLACE INTO 取代INSERT INTO.
mysqldump  -uroot -p --host=localhost --all-databases --replace
--result-file,  -r
直接输出到指定文件中。该选项应该用在使用回车换行对（\\r\\n）换行的系统上（例如：DOS，Windows）。该选项确保只有一行被使用。
mysqldump  -uroot -p --host=localhost --all-databases --result-file=/tmp/mysqldump_result_file.txt
--routines, -R
导出存储过程以及自定义函数。
mysqldump  -uroot -p --host=localhost --all-databases --routines
--set-charset
添加'SET NAMES  default_character_set'到输出文件。默认为打开状态，使用--skip-set-charset关闭选项。
mysqldump  -uroot -p --host=localhost --all-databases 
mysqldump  -uroot -p --host=localhost --all-databases --skip-set-charset
--single-transaction
该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎，仅InnoDB。本选项和--lock-tables 选项是互斥的，因为LOCK  TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用--quick 选项。
mysqldump  -uroot -p --host=localhost --all-databases --single-transaction
--dump-date
将导出时间添加到输出文件中。默认为打开状态，使用--skip-dump-date关闭选项。
mysqldump  -uroot -p --host=localhost --all-databases
mysqldump  -uroot -p --host=localhost --all-databases --skip-dump-date
--skip-opt
禁用–opt选项.
mysqldump  -uroot -p --host=localhost --all-databases --skip-opt
--socket,-S
指定连接mysql的socket文件位置，默认路径/tmp/mysql.sock
mysqldump  -uroot -p --host=localhost --all-databases --socket=/tmp/mysqld.sock
--tab,-T
为每个表在给定路径创建tab分割的文本文件。注意：仅仅用于mysqldump和mysqld服务器运行在相同机器上。注意使用--tab不能指定--databases参数
mysqldump  -uroot -p --host=localhost test test --tab="/home/mysql"
--tables
覆盖--databases (-B)参数，指定需要导出的表名，在后面的版本会使用table取代tables。
mysqldump  -uroot -p --host=localhost --databases test --tables test
--triggers
导出触发器。该选项默认启用，用--skip-triggers禁用它。
mysqldump  -uroot -p --host=localhost --all-databases --triggers
--tz-utc
在导出顶部设置时区TIME_ZONE='+00:00' ，以保证在不同时区导出的TIMESTAMP 数据或者数据被移动其他时区时的正确性。
mysqldump  -uroot -p --host=localhost --all-databases --tz-utc
--user, -u
指定连接的用户名。
--verbose, --v
输出多种平台信息。
--version, -V
输出mysqldump版本信息并退出
--where, -w
只转储给定的WHERE条件选择的记录。请注意如果条件包含命令解释符专用空格或字符，一定要将条件引用起来。
mysqldump  -uroot -p --host=localhost --all-databases --where=” user=’root’”
--xml, -X
导出XML格式.
mysqldump  -uroot -p --host=localhost --all-databases --xml
--plugin_dir
客户端插件的目录，用于兼容不同的插件版本。
mysqldump  -uroot -p --host=localhost --all-databases --plugin_dir=”/usr/local/lib/plugin”
--default_auth
客户端插件默认使用权限。
mysqldump  -uroot -p --host=localhost --all-databases --default-auth=”/usr/local/lib/plugin/<PLUGIN>”

参数说明

参数说明

1. 删除my.ini [client]下的 no-beep 参数;
2. 在 mysqldump 后加--no-defaults参数,即:mysqldump --no-defualts -h主机IP -u用户名 -p密码 数据库 > xxx.sql 。

unknown option '--no--keep'解决方法

2.恢复逻辑备份

在mysql命令下，用source命令导入备份文件:

mysql>  USE 数据库名;
mysql>  source 备份文件.sql;

注意：只能在cmd界面下执行source命令，不能在mysql工具里面执行source命令，会报错，因为cmd是直接调用mysql.exe来执行命令的。

　　八.存储引擎　　

存储引擎:指定表如何存储数据，如何为存储的数据 建立索引 以及 如何更新，查询数据等技术实现的方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）。

了解: 在Oracle 和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySql数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。

大容量的数据集时趋向于选择Innodb。因为它支持事务处理和故障的恢复。Innodb可以利用数据日志来进行数据的恢复。主键的查询在Innodb也是比较快的。
大批量的插入语句时（这里是INSERT语句）在MyIASM引擎中执行的比较的快，但是UPDATE语句在Innodb下执行的会比较的快，尤其是在并发量大的时候。
两种引擎所使用的索引数据结构是什么？

答案:都是B+树!
　　　　MyIASM引擎，B+树的数据结构中存储的内容实际上是实际数据的地址值。也就是说它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。
　　　　Innodb引擎的索引的数据结构也是B+树，只不过数据结构中存储的都是实际的数据，这种索引有被称为聚集索引。

面试题:innodb与MyIASM存储引擎的区别:
　　1.innodb 是mysql5.5版本以后的默认存储引擎, 而MyISAM是5.5版本以前的默认存储引擎.
　　2.innodb 支持事物,而MyISAM不支持事物
　　3.innodb 支持行级锁.而MyIASM 它支持的是并发的表级锁.
　　4.innodb 支持外键, 而MyIASM 不支持外键
　　5.innodb与MyIASM存储引擎都采用B+TREE存储数据, 但是innodb的索引与数据存储在一个文件中,这种方式我们称之为聚合索引.
　　　　而MyIASM则会单独创建一个索引文件,也就是说,数据与索引是分离开的　　6.在效率方面MyISAM比innodb高,但是在性能方面innodb要好一点.

　　九.索引　　

　　 1.索引概述

　　简单的说,相当于图书的目录,可以帮助用户快速的找到需要的内容.

　　在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。能够大大提高查询效率。特别是当数据量非常大，查询涉及多个表时，使用索引往往能使查询速度加快成千上万倍.

　　索引本质：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。　　

　　2.索引方法

2.1 B+TREE 索引

　　B+树是一种经典的数据结构，由平衡树和二叉查找树结合产生，它是为磁盘或其它直接存取辅助设备而设计的一种平衡查找树，在B+树中，所有的记录节点都是按键值大小顺序存放在同一层的叶节点中，叶节点间用指针相连，构成双向循环链表，非叶节点（根节点、枝节点）只存放键值，不存放实际数据。

　　　　强烈注意: 索引字段要尽量的小,磁盘块可以存储更多的索引.

2.2 HASH 索引

　　　　hash就是一种（key=>value）形式的键值对,允许多个key对应相同的value，但不允许一个key对应多个value,为某一列或几列建立hash索引，就会利用这一列或几列的值通过一定的算法计算出一个hash值，对应一行或几行数据. hash索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率.

3.HASH与BTREE比较:

hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量越大,范围查询和随机查询快（innodb默认索引类型）

不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 Btree、Hash 等索引，不支持Full-text 索引；
MyISAM 不支持事务，支持表级别锁定，支持 Btree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 Btree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 Btree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 Btree、Hash、Full-text 等索引；

索引比较

　　4.索引类型

MySQL中常见索引有：

普通索引:加速查询
唯一索引:加速查询和唯一约束（可含一个null 值）
主键索引:加速查询和唯一约束（不可含null）注意:一个表中最多只能有一个主键索引。
组合索引:组合索引是将n个列组合成一个索引,其应用场景为：频繁的同时使用n列来进行查询，如：where n1 = 'alex' and n2 = 666。

　　　　4.1 普通索引

#创建表同时添加name字段为普通索引
create table tb1(
   id int not null auto_increment primary key,
   name varchar(100) not null,
   index idx_name(name)  
);

创建表+索引

#单独为表指定普通索引

create index idx_name on tb1(name);

创建索引

drop index idx_name on tb1;

删除索引

show index from tb1;

查看索引

1、Table 表的名称。
2、 Non_unique 如果索引为唯一索引,则为0,如果可以则为1。
3、 Key_name 索引的名称
4、 Seq_in_index 索引中的列序列号，从1开始。
5、 Column_name 列名称。
6、 Collation 列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。
7、Cardinality 索引中唯一值的数目的估计值。
8、Sub_part 如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
9、 Packed 指示关键字如何被压缩。如果没有被压缩，则为NULL。
10、 Null 如果列含有NULL，则含有YES。如果没有，则该列含有NO。
11、 Index_type 用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
12、 Comment 多种评注

查看索引列介绍

　　　　4.2 唯一索引

create table tb2(
  id int not null auto_increment primary key,
  name varchar(50) not null,
  age int not null,
  unique index idx_age (age)   
)

create unique index idx_age on tb2(age);-- 创建唯一索引

创建表+唯一索引unique ，创建唯一索引

　　　　4.3 主键索引

create table tb3(
   id int not null auto_increment primary key,-- 方式1
   name varchar(50) not null,
   age int default 0 
-- primary key(id) -- 方式2
);


alter table tb3 add primary key(id);  -- 创建主键

-- 删除主键
alter table tb3 drop primary key;-- 方式1

#如果当前主键为自增主键,则不能直接删除.需要先修改自增属性,再删除

alter table tb3 modify id int ,drop primary key;-- 方式2

创建表+创建主键索引，创建|删除主键

　　　　4.4 组合索引

create table tb4(
  id int not null ,
  name varchar(50) not null,
  age int not null,
  index idx_name_age (name,age)   
)


create index idx_name_age on tb4(name,age);-- 创建组合索引

创建表+组合索引，创建组合索引

商场的会员卡的系统。

这个系统有一个会员表
有下列字段：
会员编号 INT
会员姓名 VARCHAR(10)
会员身份证号码 VARCHAR(18)
会员电话 VARCHAR(10)
会员住址 VARCHAR(50)
会员备注信息 TEXT

那么这个 会员编号，作为主键，使用 PRIMARY
会员姓名 如果要建索引的话，那么就是普通的 INDEX
会员身份证号码 如果要建索引的话，那么可以选择 UNIQUE （唯一的，不允许重复）

应用

　　5.聚合索引和辅助索引

数据库中的B+树索引可以分为聚集索引和辅助索引.

聚集索引：InnoDB表索引组织表，即表中数据按主键B+树存放，叶子节点直接存放整条数据，每张表只能有一个聚集索引。

1.当你定义一个主键时，InnnodDB存储引擎则把它当做聚集索引
2.如果你没有定义一个主键，则InnoDB定位到第一个唯一索引，且该索引的所有列值均飞空的，则将其当做聚集索引。
3如果表没有主键或合适的唯一索引INNODB会产生一个隐藏的行ID值6字节的行ID聚集索引，
补充：由于实际的数据页只能按照一颗B+树进行排序，因此每张表只能有一个聚集索引，聚集索引对于主键的排序和范围查找非常有利.

辅助索引：（也称非聚集索引）是指叶节点不包含行的全部数据，叶节点除了包含键值之外，还包含一个书签连接，通过该书签再去找相应的行数据。

总结二者区别:

　　相同的是：不管是聚集索引还是辅助索引，其内部都是B+树的形式，即高度是平衡的，叶子结点存放着所有的数据。

　　不同的是：聚集索引叶子结点存放的是一整行的信息,而辅助索引叶子结点存放的是单个索引列信息.

　　6.正确使用索引　　

数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也会不奏效。
即使建立索引，索引也不会生效,例如:

#1. 范围查询(>、>=、<、<=、!= 、between...and)
    #1. = 等号
    select count(*) from userinfo where id = 1000 -- 执行索引,索引效率高
    
    #2. > >= < <= between...and 区间查询
    select count(*) from userinfo where id <100; -- 执行索引,区间范围越小,索引效率越高
    
    select count(*) from userinfo where id >100; -- 执行索引,区间范围越大,索引效率越低
    
    select count(*) from userinfo where id between 10 and 500000; -- 执行索引,区间范围越大,索引效率越低
    
   #3. != 不等于
   select count(*) from userinfo where id != 1000;  -- 索引范围大,索引效率低
   
   
#2.like '%xx%'
    #为 name 字段添加索引
    create index idx_name on userinfo(name);
    
    select count(*) from userinfo where name like '%xxxx%'; -- 全模糊查询,索引效率低
    select count(*) from userinfo where name like '%xxxx';   -- 以什么结尾模糊查询,索引效率低
  
    #例外: 当like使用以什么开头会索引使用率高
    select * from userinfo where name like 'xxxx%'; 

#3. or 
    select count(*) from userinfo where id = 12334 or email ='xxxx'; -- email不是索引字段,索引此查询全表扫描
    
    #例外：当or条件中有未建立索引的列才失效，以下会走索引
    select count(*) from userinfo where id = 12334 or name = 'alex3'; -- id 和 name 都为索引字段时, or条件也会执行索引

#4.使用函数
    select count(*) from userinfo where reverse(name) = '5xela'; -- name索引字段,使用函数时,索引失效
    
    #例外:索引字段对应的值可以使用函数,我们可以改为一下形式
    select count(*) from userinfo where name = reverse('5xela');

#5.类型不一致
    #如果列是字符串类型，传入条件是必须用引号引起来，不然...
    select count(*) from userinfo where name = 454;
        
    #类型一致
    select count(*) from userinfo where name = '454';

#6.order by
    #排序条件为索引，则select字段必须也是索引字段，否则无法命中  
    select email from userinfo ORDER BY name DESC; -- 无法命中索引

    select name from userinfo ORDER BY name DESC;  -- 命中索引
        
    #特别的:如果对主键排序，则还是速度很快：
    select id from userinfo order by id desc;

如何正确使用索引eg

　　7.组合索引

　　定义:指对表上的多个列组合起来做一个索引.

　　组合索引好处:简单的说有两个主要原因：

"一个顶三个"。建了一个(a,b,c)的组合索引，那么实际等于建了(a),(a,b),(a,b,c)三个索引，因为每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，这可是不小的开销！

索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select * from table where a = 1 and b =2 and c = 3,假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W*10%=100w 条数据，然后再回表从100w条数据中找到符合b=2 and c= 3的数据，然后再排序，再分页；如果是组合索引，通过索引筛选出1000w *10% *10% *10%=1w，然后再排序、分页，哪个更高效，一眼便知

最左匹配原则: 从左往右依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用；

select * from mytable where a=3 and b=5 and c=4;
　  #abc三个索引都在where条件里面用到了，而且都发挥了作用

select * from mytable where  c=4 and b=6 and a=3;
　　#这条语句列出来只想说明 mysql没有那么笨，where里面的条件顺序在查询之前会被mysql自动优化，效果跟上一句一样

select * from mytable where a=3 and c=7;
　　#a用到索引，b没有用，所以c是没有用到索引效果的

select * from mytable where a=3 and b>7 and c=3;
　　#a用到了，b也用到了，c没有用到，这个地方b是范围值，也算断点，只不过自身用到了索引

select * from mytable where b=3 and c=4;
　　#因为a索引没有使用，所以这里 bc都没有用上索引效果

select * from mytable where a>4 and b=7 and c=9;
　　#a用到了  b没有使用，c没有使用

select * from mytable where a=3 order by b;
　　#a用到了索引，b在结果排序中也用到了索引的效果

select * from mytable where a=3 order by c;
　　#a用到了索引，但是这个地方c没有发挥排序效果，因为中间断点了

select * from mytable where b=3 order by a;
　　#b没有用到索引，排序中a也没有发挥索引效果

最左匹配原则

　　8.注意事项

1. 避免使用select *
2. 其他数据库中使用count(1)或count(列) 代替 count(*),而mysql数据库中count(*)经过优化后,效率与前两种基本一样.
3. 创建表时尽量时 char 代替 varchar
4. 表的字段顺序固定长度的字段优先
5. 组合索引代替多个单列索引（经常使用多个条件查询时）
6. 使用连接（JOIN）来代替子查询(Sub-Queries)
7. 不要有超过4个以上的表连接（JOIN）
8. 优先执行那些能够大量减少结果的连接。
9. 连表时注意条件类型需一致
10.索引散列值不适合建索引，例：性别不适合

注意事项

　　9.面试题(大数据量分页如何处理)

select * from userinfo limit 3000000,10;

执行此段代码，前提创建一个百万级数据库

一. 简单粗暴，就是不允许查看这么靠后的数据，比如百度就是这样的:

百度最多可以翻到72页，，也就是说它所能展示的数据只有720条(每页10条)，但是它所查出来的数据超过了数十万，甚至百万级数据。这种方式就是从业务上解决。

二.在查询下一页时把上一页的行id作为参数传递给客户端程序，然后sql就改成了

select * from userinfo where id>3000000 limit 10;

这条语句执行也是在毫秒级完成的，id>300w其实就是让mysql直接跳到这里了，不用依次在扫描全面所有的行。

如果你的table的主键id是自增的，并且中间没有删除和断点，那么还有一种方式，比如100页的10条数据

select * from userinfo where id>100*10 limit 10;

三.最后第三种方法：延迟关联

它为什么慢，慢在哪里?

select * from userinfo limit 3000000,10;

玄机就处在这个 * 里面，这个表除了id主键肯定还有其他字段比如 name age 之类的，因为select * 所以mysql在沿着id主键走的时候要回行拿数据，走一下拿一下数据；

如果把语句改成

select id from userinfo limit 3000000,10;

你会发现时间缩短了一半；然后我们在拿id分别去取10条数据就行了,语句就改成这样了：

select table.* from userinfo inner join ( select id from userinfo limit 3000000,10 ) as tmp on tmp.id=userinfo.id;

这三种方法最先考虑第一种其次第二种，第三种是别无选择.

最左匹配原则: 从左往右依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用；

posted on 2020-03-12 17:37 眨眼星舟_Eric 阅读(184) 评论(0) 编辑收藏举报

刷新页面返回顶部

MySQL

MySQL

MySQL软件的安装

1.软件下载地址

2.安装

MySQL之库操作

1.创建数据库

2.查看数据库

3.选择数据库

4.删除数据库

5.命名规范

6.注释

7.用户权限

8.修改密码

9.忘记密码

MySQL之表操作

一.什么是表

二.创建表

三.查询表数据

四.修改表结构

五.复制表

数据类型

MySQL之数据操作

一.插入操作之insert

二.更新操作之update

三.删除操作之delete

四.查询操作之SELECT

五.MySQL的执行顺序

六.练习题

MySQL之视图、触发器、存储过程、函数、事物、数据库锁

一.视图

二.触发器

三.存储过程

四.函数

五.事物处理

六.数据锁

七.数据库备份

八.存储引擎

九.索引

导航

公告

　　　　　　　　　　　　　　　MySQL　　　　　　　　　　　　　　　　

　　　　　　　　　　MySQL软件的安装　　　　　　　　　　

　　1.软件下载地址　　

　　2.安装　　

　　　　　　　　　　　　MySQL之库操作　　　　　　　　　　　

　　1.创建数据库　　

　　2.查看数据库　　

　　3.选择数据库　　

　　4.删除数据库　　

　　5.命名规范　　

　　6.注释　　

　　7.用户权限　　

　　8.修改密码　　

　　9.忘记密码　　

　　　　　　　　　　　　　　MySQL之表操作　　　　　　　　　　　　　　

　　一.什么是表　　

　　二.创建表　　

　　三.查询表数据　　

　　四.修改表结构　　

　　五.复制表　　

　　　　　　　　　　　　　　　数据类型　　　　　　　　　　　　　　　

　　　　　　　　　　　　　　MySQL之数据操作　　　　　　　　　　　　　　

　　一.插入操作之insert　　

　　二.更新操作之update　　

　　三.删除操作之delete　　

　　四.查询操作之SELECT　　

　　五.MySQL的执行顺序　　

　　六.练习题　　

　　　　MySQL之视图、触发器、存储过程、函数、事物、数据库锁　　　　

　　一.视图　　

　　三.存储过程　　

　　四.函数　　

　　五.事物处理　　　　

　　六.数据锁　　　　

　　七.数据库备份　　

　　八.存储引擎　　

　　九.索引　　