一文搞定MySQL基本命令(更新中)

1 数据库创建、选择、显示

创建数据库：create database <database_name>;
选择数据库：use <database_name>;
显示数据库：show databases;
显示数据库所有表：show tables;
显示某个表结构：show columns from <table_name>;  # 等价于  desc <table_name>;

2 表操作—修改、删除、增加、创建

   # 创建表
    CREATE TABLE ts1(
        id  int PRIMARY KEY auto_increment,
        name VARCHAR(20),
        birth DATE DEFAULT '1998-04-12'
    )ENGINE INNODB DEFAULT charset=utf8 auto_increment = 1000;
    DESC ts1;
    #修改表的字段
    ALTER TABLE ts1 MODIFY name VARCHAR(32);
    #增加表的字段
    ALTER TABLE ts1 ADD sex VARCHAR(4) DEFAULT '男';
    # 删除表的字段
    ALTER TABLE ts1 DROP COLUMN sex;
    SELECT * FROM ts1;
    # 删除表
    CREATE TABLE ts2(num int PRIMARY KEY);
    SELECT * FROM ts2;
    DROP TABLE ts2;
    DESC ts1;

3 数据操作—增 、删、 改

#2.3.1数据增加
     INSERT INTO ts1(name,birth) 
     VALUES ('张三','1999-2-14'),('李四','2001-4-12'),('王五','2004-12-4');
    SELECT * FROM ts1;
    INSERT INTO ts1  -- 必须按字段顺序对应插入值
    VALUES (NULL,'赵六','1997-4-9');
-- 2.3.2 数据修改
    UPDATE ts1 SET id = 1004 WHERE name = '张三';
    SELECT * FROM ts1;
-- 2.3.3 数据删除
    DELETE FROM ts1 WHERE id = 1004;
    SELECT * FROM ts1;
-- -------

---

4.数据完整性 ------4种约束

#4.1 非空约束
    CREATE TABLE ts2(
    id INT NOT NULL
    )ENGINE INNODB DEFAULT charset=utf8;
  INSERT INTO ts2 VALUE(9);
    INSERT INTO ts2 VALUE(2),(4);
    SELECT * FROM ts2;
    INSERT INTO ts2 VALUE(NULL);  # [Err] 1048 - Column 'id' cannot be null
    ALTER TABLE ts2 MODIFY id INT ;
    INSERT INTO ts2 VALUE(NULL);  # [Err] 1048 - Column 'id' cannot be null
    DESC ts2;
    ALTER TABLE ts2 MODIFY id INT NOT NULL;  #[Err] 1138 - Invalid use of NULL value 已有空数据，不能改为非空

#4.2 唯一约束

    CREATE TABLE ts3 (
    name VARCHAR(32) UNIQUE,
    phone VARCHAR(11) NOT NULL
)ENGINE INNODB DEFAULT charset=utf8;
    DESC ts3;
    INSERT INTO ts3 VALUES('小红','18423484192'),('小蓝','24593823633');
    SELECT * FROM ts3;
    INSERT INTO ts3 VALUES ('小红','5920142852');   #Duplicate entry '小红' for key 'name'
    ALTER TABLE ts3 MODIFY name VARCHAR(32) ;  # 修改不了
    DESC ts3;
#4.3 主键约束 = 非空+唯一
    CREATE TABLE ts4(
    stu_id int PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    phone VARCHAR(11) UNIQUE
)ENGINE INNODB DEFAULT charset = utf8;
    DESC ts4;
    INSERT INTO ts4
    VALUES (001,'曾勇勇','18401284022'),(002,'帅坤坤','2563424216');
    SELECT * FROM ts4;
    INSERT INTO ts4 
    VALUES(NULL,'邱楠',NULL);  #  Column 'stu_id' cannot be null  非空
    INSERT INTO ts4
    VALUES (002,'帅坤坤','132425155');  # Duplicate entry '2' for key 'PRIMARY' 唯一
    #DELETE FROM ts4 WHERE stu_id = 004;
#4.4 默认值约束 
 CREATE TABLE ts7(id INT DEFAULT 3);
 INSERT INTO ts7 VALUES();
 SELECT * FROM ts7;

5 复合约束

#5.1 多字段的复合约束
    CREATE TABLE ts5(
    id int NOT NULL,
    name VARCHAR(20),
    phone VARCHAR(11),
    CONSTRAINT name_phone unique(name,phone)
)ENGINE INNODB DEFAULT charset=utf8;
    INSERT INTO ts5 VALUES(001,'小明','1782081024'),(002,'小花','1831236127');
    SELECT * FROM ts5;
    INSERT INTO ts5 VALUES(003,'小花','1831236127'); #报错：Duplicate entry '小花-1831236127' for key 'name_phone'
    INSERT INTO ts5 VALUES(004,'小明','1831236127');  #OK，只要(name,phone) 这一组合唯一即可
    SELECT * FROM ts5;

#5.2 主键的复合约束/联合约束
    CREATE TABLE ts6(
    num int PRIMARY KEY,
    name VARCHAR(20),
    CONSTRAINT num_name UNIQUE(num,name) 
)ENGINE INNODB DEFAULT charset=utf8;

    INSERT INTO ts6 VALUES(001,'kk'),(002,'mm');
    SELECT * FROM ts6;
    INSERT INTO ts6 VALUES(NULL,'uu'); # Column 'num' cannot be null   主键的非空约束
    INSERT INTO ts6 VALUES(002,'uu');  # Duplicate entry '2' for key 'PRIMARY'  主键的唯一约束
    INSERT INTO ts6 VALUES(003,NULL);  #成功，未设置非空，可为空
    INSERT INTO ts6 VALUES(004,'kk');  # (num,name) 组合唯一即可

    #总结：对于有主键的复合约束，首先需满足主键的唯一与非空约束，这也限制了它们的组合唯一只有修改非主键的另一个字段值

6 外键约束

CREATE TABLE course(
    course_id int PRIMARY KEY,
    course_name VARCHAR(20) NOT NULL
)ENGINE INNODB DEFAULT charset=utf8;
    DESC course;
   
CREATE TABLE student(
    stu_id  int PRIMARY KEY,
    stu_name VARCHAR(20) NOT NULL,
    course_id int not null,
    constraint fk_std_cou  foreign key(course_id)
    references course(course_id)
)ENGINE INNODB DEFAULT charset=utf8;

INSERT INTO course VALUES(001,'C++'),(002,'Python'),(003,'Java'),(004,'C#');
SELECT * FROM course;

INSERT INTO student VALUES(1001,'小美',001),(1002,'小珍',004);
SELECT * FROM student;

INSERT INTO course VALUES(1003,'小钟',005);  -- 尝试插入一条不存在的课程id
-- error:Column count doesn't match value count at row 1

-- 删除外键    
    ALTER TABLE student DROP FOREIGN KEY fk_std_cou;
 DESC student;

-- 增加外键
    ALTER TABLE student ADD CONSTRAINT fk_std_cou FOREIGN KEY(course_id) REFERENCES course(course_id);

7 级联删除/更新

-- 7.1 CASCADE
CREATE TABLE course_cascade(
    course_id int PRIMARY KEY,
    course_name VARCHAR(20) NOT NULL
)ENGINE INNODB DEFAULT charset=utf8;

CREATE TABLE student_cascade(
    stu_id  int PRIMARY KEY,
    stu_name VARCHAR(20) NOT NULL,
    course_id int not null,
    constraint fk_stu_cour  foreign key(course_id)
    references course_cascade(course_id)
    ON DELETE CASCADE 
    ON UPDATE CASCADE
)ENGINE INNODB DEFAULT charset=utf8;

INSERT INTO course_cascade VALUES(001,'C++'),(002,'Python'),(003,'Java'),(004,'C#');
SELECT * FROM course_cascade;

INSERT INTO student_cascade VALUES(1001,'小美',001),(1002,'小珍',004),(1003,'小钟',002);
SELECT * FROM student_cascade;

#更新父表
UPDATE course_cascade SET  course_id = 5 WHERE course_name = 'C++';
#UPDATE student_cascade SET course_id = 4 WHERE stu_id = 1001;

#删除父表中某项记录
DELETE FROM course_cascade WHERE course_id = 2;
#---     cascade 总结： 父表更新，子表也更新。 父表中某项删除，子表中相关联项也删除

-- 7.2 NO action
CREATE TABLE course_no_action(
    course_id int PRIMARY KEY,
    course_name VARCHAR(20) NOT NULL
)ENGINE INNODB DEFAULT charset=utf8;

CREATE TABLE student_no_action(
    stu_id  int PRIMARY KEY,
    stu_name VARCHAR(20) NOT NULL,
    course_id int not null,
    constraint fk_st_cou  foreign key(course_id)
    references course_no_action(course_id)
    ON DELETE NO action 
    ON UPDATE NO action
)ENGINE INNODB DEFAULT charset=utf8;

INSERT INTO course_no_action VALUES(001,'C++'),(002,'Python'),(003,'Java'),(004,'C#');
SELECT * FROM course_no_action;

INSERT INTO student_no_action VALUES(1001,'小美',001),(1002,'小珍',004),(1003,'小钟',002);
SELECT * FROM student_no_action;

修改父表项 
UPDATE course_no_action SET course_id = 6 WHERE course_name = 'C#';
#Cannot delete or update a parent row: a foreign key constraint fails (`db_test`.
#`student_no_action`, CONSTRAINT `fk_st_cou` FOREIGN KEY (`course_id`) REFERENCES `
#course_no_action` (`course_id`) ON DELETE NO ACTION ON UPDATE NO ACTION)

DELETE FROM course_no_action WHERE course_id = 4;
与上面报的错相同

SELECT * FROM course_no_action;
--- NO ACTION 总结： 不允许修改或删除父表中的项

-- 7.3 SET NULL
CREATE TABLE course_set_null(
    course_id int PRIMARY KEY,
    course_name VARCHAR(20) NOT NULL
)ENGINE INNODB DEFAULT charset=utf8;

CREATE TABLE student_set_null(
    stu_id  int PRIMARY KEY,
    stu_name VARCHAR(20) NOT NULL,
    course_id int ,  # 这里不设置为NOT NULL
    constraint fk_studnet_course  foreign key(course_id)
    references course_set_null(course_id)
    ON DELETE SET NULL
    ON UPDATE SET NULL
)ENGINE INNODB DEFAULT charset=utf8;

INSERT INTO course_set_null VALUES(001,'C++'),(002,'Python'),(003,'Java'),(004,'C#');
SELECT * FROM course_set_null;

INSERT INTO student_set_null VALUES(1001,'小美',001),(1002,'小珍',004),(1003,'小钟',002);
SELECT * FROM student_set_null;

DESC student_set_null;
UPDATE course_set_null SET course_id = 8 WHERE course_name = 'C#';
DELETE FROM course_set_null WHERE course_id = 1;
SELECT * FROM student_set_null;

#-- SET NULL总结:当父表项更新时，子表中相关联的项只会设置为NULL，而不删除

-- 7.4 CASCADE SET NULL  NO action 联合使用

8 表查询

-- 8.1 单表查询
-- 8.1.1 字段查询(所有、指定)
    SELECT * FROM student;  # 所有字段
    SELECT stu_name ,stu_id FROM student;   # 多个字段
-- 8.1.2 某条/多条记录查询  AND(&&) OR(||)  通配符(% _ ) like(搭配通配符使用) 
	BETWEEN..AND  in(x,y,z)  IS NULL  LIMIT(分页查询)  DISTINCT(去重查询)   >= <= > <   <> 
    SELECT stu_name FROM student  WHERE stu_id =1002; # 单条记录
    SELECT * FROM course;
    SELECT course_name FROM course WHERE course_id=001 OR course_id= 003;  # 多条记录
    SELECT course_name FROM course WHERE course_id=001 || course_id= 003;  
    SELECT * FROM student;
    INSERT INTO student VALUES(1003,'小坤',4);
    SELECT stu_name FROM student WHERE course_id = 4 and stu_id = 1002;
    SELECT stu_name FROM student WHERE course_id = 4 && stu_id = 1002;
    SELECT course_name FROM course WHERE course_name LIKE 'C%';  # %：任意个字符
    SELECT course_name FROM course WHERE course_name LIKE 'C_';  # _：单个字符
    SELECT stu_name, stu_id FROM student WHERE stu_id BETWEEN 1001 AND 1002;
    SELECT DISTINCT stu_id FROM student ;  # 去重查询
    SELECT stu_id, stu_name FROM student LIMIT 1,2;   # 从第二条开始查询，查询两条数据
    SELECT stu_id ,stu_name FROM student LIMIT 1;   # 只查询第二条记录(注意是记录索引从0开始)
    
-- 合并查询结果集     UNION (去掉重复的记录)   UNION ALL（不去掉重复记录）  注意前提：列数需相同
    SELECT * FROM student;
    SELECT * FROM student  UNION 
    SELECT * FROM student WHERE course_id = 4;

-- 取别名  as -->可省略
    SELECT stu_id 学号, stu_name 姓名 from student;
    SELECT stu_id  as 学号, stu_name as 姓名 from student;

--8.2 多表查询
-- ------------------ 数据准备-------------------------------------
create table salary(
    grade varchar(4) primary key,
    number int
)engine=innodb default charset=utf8;

create table department(
    department_id int primary key,
    department_name varchar(32),
    salary_grade varchar(4),
    constraint fk_depsal FOREIGN key(salary_grade)
    references salary(grade)
    on delete set null on update cascade
)engine=innodb default charset=utf8;

create table employee(
    employee_id int primary key auto_increment,
    name varchar(32) not null,
    enter_date date default '1995-01-01',
    department_id int,
    constraint fk_empdep foreign key(department_id)
    REFERENCES department(department_id)
    on delete CASCADE on update cascade
)auto_increment = 1000 engine =innodb default charset=utf8;

insert into salary
    select 'SS', NULL union all
    select 'S', 10000 union all
    select 'A', 8000 union all
    select 'B', 5000 union all
    select 'C', 3000;
select * from salary;

insert into department
    select 1, '董事长','SS' union all
    select 2, '人事部','S' union all
    select 3, '后勤部', 'B' union all
    select 4, '财务部', 'C' union all
    select 5, '研发部' , 'A';
SELECT * FROM department;

insert into employee(name, department_id)
    select '张三',1 union all
    select '李四',2 union all
    select '王五',3 union all
    select '赵六',4 union all
    select '孙八',3 union all
    select '杨九',5 union all
    select '吴十',5;

SELECT * FROM employee;
-- ------------------ 多表查询-------------------------------------

9 连接方式

-- 9.1 内连接  只显示匹配数据
INSERT INTO employee VALUES(1007,'kk','2000-1-1',NULL);
    SELECT department.department_name, employee.name 
    FROM employee
    INNER JOIN department
    ON department.department_id = employee.department_id;
-- 9.2 外连接   显示所有匹配数据，包括nul
-- 4.1.2.1  左连接
    SELECT department.department_name, employee.name
    FROM employee
    LEFT JOIN department
    ON department.department_id =  employee.department_id;
-- 4.1.2.2  右连接 
SELECT department.department_name, employee.name
    FROM employee
    RIGHT JOIN department
    ON department.department_id =  employee.department_id;

-- 9.3  全连接 ： 左连接 UNION  右连接
SELECT department.department_name, employee.name
    FROM employee
    LEFT JOIN department
    ON department.department_id =  employee.department_id UNION
SELECT department.department_name, employee.name
    FROM employee
    RIGHT JOIN department
    ON department.department_id =  employee.department_id;

10.函数

-- 10.1 排序
    SELECT * FROM salary;
    SELECT * FROM salary ORDER BY  number;  # 升序
    SELECT * FROM salary ORDER BY number DESC; # 降序

-- 10.2 分组
    SELECT * FROM employee;
    SELECT * FROM employee GROUP BY department_id;  # 升序
    SELECT * FROM department;
    SELECT * FROM department GROUP BY salary_grade; # 升序

-- 10.3 聚合函数   count(exp)   AVG([DISTINCT] expr)     SUM(expr)  MAX(expr)  MIN(expr)
    SELECT COUNT(*) FROM employee;  -- 包含null
    SELECT COUNT(department_id) FROM employee;  -- 对于某一字段，不包含null
    SELECT AVG(number) FROM salary;
    SELECT SUM(number) FROM salary;
    SELECT MAX(number) FROM salary;
    SELECT MIN(number) FROM salary;

-- 练习----- 分组 搭配  聚合函数
create table orders
(
    id int primary key,
    orderprice int,
    orderdate date,
    customer varchar(32)
)engine = INNODB DEFAULT charset=utf8;

insert into orders 
    select 1,1000,'2018-11-11','mary' union all
    select 2,600,'2018-11-11', 'jack' union all
    select 3,800,'2018-11-11','johan' union all
    select 4,900, '2018-11-14','mary' union all
    select 5,600, '2018-11-15','jack' union all
    select 6,100, '2018-11-15','johan' union all
    select 7,16800,'2018-11-15','Andrew';
SELECT * FROM orders;

11.小练习

#任务一：每一个顾客花了多少钱
SELECT customer, sum(orderprice) FROM orders GROUP BY customer;

#多个字段分组时，每个顾客每天的花费总额
select customer,orderdate, sum(orderprice) from orders group by customer ,orderprice;

#任务二： 查询订单总额小于1600顾客——新值 having 用法
SELECT customer, sum(orderprice) FROM orders GROUP BY customer HAVING SUM(orderprice) < 1600;  #注意在 GROUP BY 后不能是 WHERE

#任务三：查询mary是否是订单总额小于2000的顾客
SELECT customer, sum(orderprice) FROM orders GROUP BY customer HAVING SUM(orderprice) < 2000 AND customer = 'mary';  		 # 方法一
SELECT customer, sum(orderprice) FROM orders  WHERE customer = 'mary' GROUP BY customer HAVING SUM(orderprice) < 2000;  #方法二

SELECT customer, sum(orderprice) FROM orders WHERE SUM(orderprice) < 2000 GROUP BY customer HAVING customer = 'mary';  
 [Err] 1111 - Invalid use of group function   因为先分组筛选时，只有一条mary记录 ，此时再进行 sum(orderprice) <2000 无效

-- 总结：分组前where ，分组后having

12.索引、视图

-- 12.1 视图 -         存储结果集的虚拟表
    SELECT * FROM orders;

-- 12.2 创建视图
create view employeeview  
as select e.name,d.department_name,s.number 
  from Employee as e  
  inner join Department as d 
 on e.department_id = d.department_id 
  inner join Salary as s 
  on d.salary_grade = s.grade;

 CREATE VIEW salary_view
  AS SELECT * FROM salary;

-- 12.3 使用视图
    SELECT * FROM salary_view;

-- 12.4 修改视图
    ALTER VIEW employeeview 
    AS SELECT * FROM employee;

-- 12.5 删除视图
    DROP VIEW employeeview;

13.索引

– 比较少使用，以后再补充

14.储存过程

--14.1 创建存储过程
    CREATE PROCEDURE display_department()
    BEGIN
        SELECT * FROM department;
    END;
SELECT * FROM orders;

--14.2 调用存储过程
    CALL display_department;
    
-- 14.3 带参函数过程
    -- 任务：获取指定日期最大单笔订单
CREATE PROCEDURE getMaxOrder(in odate date)
BEGIN
    SELECT customer, MAX(orderprice) FROM orders
    WHERE orderdate = odate;
END;

CALL getMaxOrder('2018-11-11');
DROP PROCEDURE getMaxOrder;

--14.4 使用存储过程插入数据并查询结果

15.流程控制

-- 15.1 局部变量声明/赋值    
CREATE PROCEDURE test1()
BEGIN
    DECLARE x int;
    DECLARE y date;
        BEGIN
            SET x = 10;
            SET y = NOW();
            SELECT x, y;
        END;
END;
CALL test1();

-- 15.2 if 判断语句
CREATE PROCEDURE test2(in i_x int)
BEGIN
    IF i_x < 60 THEN
            SELECT  i_x AS code,'不及格' AS '成绩';
    ELSE
            SELECT i_x  AS CODE,'及格'  AS '成绩';
    END IF;
END;

DROP PROCEDURE test2;
CALL test2(61);

-- 15.3 case分组判断语句
CREATE PROCEDURE test3_case(in score int)
BEGIN
    CASE score
        WHEN 1 THEN
            SELECT 'A';
        WHEN 2 THEN
            SELECT 'B';
        WHEN 3 THEN
            SELECT 'C';
        ELSE
            SELECT 'D';
    END CASE;
END;

CALL test3_case(3);
CALL test3_case(5);

-- 15.4 WHILE 循环语句
CREATE PROCEDURE test4_while(OUT sum INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE s INT DEFAULT 0;
    WHILE i <= 10 DO
        SET s = s + i;
        SET i = i + 1;
    END WHILE;
    SET sum = s;
END;

CALL test4_while(@sum);
SELECT @sum;

-- 15.5 loop循环语句  通过leave退出循环
CREATE PROCEDURE test5_loop()
BEGIN
    DECLARE i INT DEFAULT 0;
    l:LOOP
        SET i = i+1;
        IF i = 11 THEN
            LEAVE l;
        END IF;
    END LOOP;
    SELECT i;
END;

CALL test5_loop();

-- 15.6 repeat 循环语句
CREATE PROCEDURE test6_repeat()
BEGIN
    DECLARE i INT DEFAULT 0;
    REPEAT
        SET i = i + 1;
    UNTIL i > 11
    END REPEAT;
    SELECT i;
END;

CALL test6_repeat();

16.触发器、事件

-- 16.1 触发器
    CREATE TABLE test_tb1(
    id INT PRIMARY KEY auto_increment,
    number DECIMAL(10,2)
)ENGINE INNODB DEFAULT charset = utf8;

INSERT INTO test_tb1 VALUES(NULL,1),(NULL,2),(NULL,3);
SELECT * FROM test_tb1;

CREATE TRIGGER up_tb1_check
BEFORE UPDATE
ON test_tb1
FOR EACH ROW
BEGIN
    if new.number < 0 THEN
        SET new.number = 0;
    ELSEIF new.number >100 THEN
        SET new.number=100;
    END IF;
END;

UPDATE test_tb1 SET number = -1 WHERE id = 1;
UPDATE test_tb1 set number = 120 WHERE id = 2;

-- 16.2 查看触发器
SELECT * FROM information_schema.`TRIGGERS`
WHERE TRIGGER_NAME = 'up_tb1_check';

-- 16.3 删除触发器
DROP TRIGGER up_ta1_check;

-- 16.4 应用举例 ---- 日志记录
SELECT * FROM test_tb1;
CREATE TABLE test_tb2(
id INT PRIMARY KEY,
name VARCHAR(20) NOT NULL
)ENGINE INNODB DEFAULT charset=utf8;

CREATE TABLE review(
    user VARCHAR(20),
    action VARCHAR(20),
    action_time date
)ENGINE INNODB DEFAULT charset=utf8;

CREATE TRIGGER Add_Data_Trigger
AFTER INSERT
ON test_tb2
FOR EACH ROW
BEGIN
    INSERT INTO review VALUES(USER(),'insert',NOW());
END;

INSERT INTO test_tb2 VALUES(001,'张三');
SELECT * FROM review;
ALTER TABLE review MODIFY action_time  datetime;

CREATE TRIGGER Update_Data_trigger
AFTER UPDATE
ON test_tb2
FOR EACH ROW
BEGIN
    INSERT INTO review VALUES(USER(), 'update', NOW());
END;

INSERT INTO test_tb2 VALUES(002,'李四'),(003,'王五');
SELECT * FROM test_tb2;
UPDATE test_tb2 SET name = 'kk' WHERE id = 1;
SELECT * FROM review;

DROP TRIGGER Add_Data_trigger;
DROP TRIGGER Update_Data_trigger;

17.事件

#17.1 事件创建
CREATE TABLE  time_event
(
    time datetime
)ENGINE INNODB DEFAULT charset=utf8;

CREATE EVENT update_time_event
ON SCHEDULE
EVERY 4 SECOND
DO
INSERT INTO time_event VALUE(NOW());

SELECT * FROM time_event;
SHOW EVENTS;
SHOW VARIABLES LIKE '%scheduler%';  
SHOW SESSION VARIABLES;

#需打开事件调度器  event_scheduler
SET GLOBAL event_scheduler = ON;

CREATE EVENT clear_time_event
ON SCHEDULE
EVERY 1 MINUTE
DO
TRUNCATE TABLE time_event;

#17.2 开启/关闭事件
ALTER EVENT update_time_event DISABLE;
ALTER EVENT clear_time_event DISABLE;

SHOW EVENTS;

#17.3 删除事件
DROP EVENT update_time_event;
DROP EVENT clear_time_event;
SHOW EVENTS;

18 事务

-- 4个特性  A： 原子性   C：一致性    I：隔离性  D：持久性

-- 18.1 隐式事务       
    SET autocommit = 0;
    SELECT * FROM orders;
    INSERT INTO orders VALUES(8,500,NOW(),'Danny'); # 这条记录存于缓存中(如果未提交)，但重启软件并未提交缓冲丢失
    COMMIT;

-- 18.2 显示事务         ----- 日常中的工作模式--------有了后悔的可能(未提交即可)
    START TRANSACTION;
    INSERT INTO orders VALUES();
    ...
    ROLLBACK;

    INSERT INTO orders VALUES(10,100,NOW(),'欧广');
    SELECT * FROM orders;
    ROLLBACK;  #上面一条记录从缓存中删除，注意：回滚需要先执行 START  TRANSACTION;

-- 18.3 事务的隔离性
    #1. read_uncommitted
    #2. read_committed
    #3. repeat_read 
    #4.SERIALIZABLE 

--18.4 查看当前事务隔离性
SELECT @@tx_isolation;

#10.VS连接(待完善)

-- --------------------补充----------------------------

SHOW VARIABLES;-- 显示系统变量信息
SHOW PROCESSLIST; -- 显示正在运行的线程
SELECT DATABASE(); -- 显示当前数据库
SELECT NOW(),USER(),VERSION();
查看数据库编码
SHOW CREATE DATABASE <database_name>;
SHOW ENGINES;-- 显示存储引擎
SHOW TABLES FROM zmk;
-- 表重命名
RENAME TABLE test_tb1 TO tb1;

编码有关：
数据库编码查看
    show variables like 'character_set_database';
表编码
    show create table <表名>;
创建数据库时指定数据库编码
    create database <数据库名> character set utf8;

创建数据表时指定数据表的编码格式
    create table tb_books (
    name varchar(45) not null,
    price double not null,
    bookCount int not null,
    author varchar(45) not null ) default charset = utf8;

修改数据库的编码格式
    alter database <数据库名> character set utf8;    

修改字段编码格式
    alter table <表名> change <字段名> <字段名> <类型> character set utf8;
-----------------------------------------------

19 安全管理

##19.1 用户管理
    用户账号和信息——>mysql数据库的user表中
    1.创建用户账号
        create user <user_name> identified by 'p@$$wOrd';
        或者使用GRANT 或 INSERT GRANT语句
        或者 直接插入行到user表--->不安全(user表极为重要，相对直接操作，最好用表极和函数来处理)，
        
    2.查看用户账号
        select user from user;(需切换到mysql数据库)

    3.重命名用户账号
        rename user <old_userName> to <new_userName>   <----前提：MySQL5或之后的版本
        （MySQL5之前版本，可用update user set <new_userName> where user='<old_userName>'）

    4.删除用户账号
        drop user <user_name> (仅支持MySQL5及之后版本，之前的直接drop只能删除账号，而不能删除权限，记得需先使用revoke删除与账号相关的权限，再drop)

##19.2 访问控制
    仅提供用户所需的访问权。日常登录不要使用root，而是创建一系列具有不同访问权限的账号。
    新创建的用户只能登录，没有执行数据库任何操作(包括查看数据)
      
    1.查看用户权限 show grants for <user_name>;
        如：GRANT USAGE ON *.* TO 'Jack'@'%'  USAGE和 *.*表示对任何数据库和表都没有权限
            用户定义“user@host” 这里对应 Jack@%, %表示默认的主机名(即不管主机名)

    2.授予用户权限：GRANT语句
        如 GRANT select on <database_name>.* to <user_name> 授予对应数据库所有表的select权限(即只读权限)

    3.撤销用户权限：REVOKE语句(撤销的权限必须存在，否则出错)
        如：REVOKE select on <database_name>.* to <user_name>

    4.GRANT和REVOKE可在几个层次上控制访问权限
        1.整个服务器，使用GRANT ALL和REVOKE ALL
        2.整个数据库，使用On database.*
        3.特定的表，使用on database.table;
        4.特点的列
        5.特点的存储过程

        注意，可通过列出各权限用逗号分隔，实现多次授权，如
            grant select, insert on database.* to user;

    5.修改用户命名(口令): password()函数
        set password for <user_name> = Password('');
        此外，还可以设置自己的密码： set password = Password('xxxx');    

20 数据库维护

##20.1 备份数据
    问题:普通的备份系统能备份MySQL的数据(基于磁盘的文件)，但由于这些文件总是处于打开和使用状态(供用户使用)，而备份打开的文件是无效的
    解决方案：
        1.mysqldump
        2.mysqlhotcopy(注意并非索引数据库引擎都支持这个实用程序)
        3.backup table 或 select into outfile(将新建的系统文件必须不存在),可用restore table复原
    注意一点：备份前为保证所有数据被写的磁盘(包括索引数据)，需使用flush tables语句

##20.2 数据库维护
    1.analyze table; 检查表键是否正确
    2.check table;  针对许多问题对表进行检查。如changed：最后一次检查以来改动过的表，extended执行最彻底的检查，fast只坚持为正常关闭的表，medium检查所有被删除的链接并进行键检验，quick只进行快速扫描
    例子：check table orders, ordertitems;
    如果一个表删除大量数据，应该使用optimize table来收回所用的空间，从而优化表的性能
    如果MyISAM表访问产生不一致和不正确的结果，可能需要使用repair table修复相应的表，但不应经常使用

##20.3 诊断启动问题
    情景：通常对MySQL配置或服务器本身更改时
    解决：尽量mysqld手动启动服务器
        mysqld 的命令行几个重要的选项：
            --help/ --safe-mode装载减去某些最佳配置的服务器/--verbose与--help联合使用/--version

##20.4 查看日志文件
    日志类型
    1.错误日志(hostname.err)：记录启动和关闭以及任意关键错误的细节
    2.查询日志(hostname.log)：记录MySQL所有活动，在诊断问题时非常有用
        (注意：这文件很快变得很大，不应该长期使用)
    3.二进制日志(hostname-bin)：记录更新过数据的所有语句,MySQL5才添加的
    4.缓慢查询日志(hostname-slow.log)：记录执行缓慢的任何查询，在确定数据库何处需要优化很有用
    以上日志都在data目录，且在使用日志时可用FLUSH LOGS语句来刷新和重新开始所有日志文件