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

一文搞定MySQL基本命令

• • • 1 数据库创建、选择、显示
    • 2 表操作---修改、删除、增加、创建
    • 3 数据操作---增 、删、 改
    • 4.数据完整性 ------4种约束
    • 5 复合约束
    • 6 外键约束
    • 7 级联删除/更新
    • 8 表查询
    • 9 连接方式
    • 10.函数
    • 11.小练习
    • 12.索引、视图
    • 13.索引
    • 14.储存过程
    • 15.流程控制
    • 16.触发器、事件
    • 17.事件
    • 18 事务
    • 19 安全管理
    • 20 数据库维护

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 nullALTER TABLE ts2 MODIFY id INT ;INSERT INTO ts2 VALUE(NULL);  # [Err] 1048 - Column 'id' cannot be nullDESC 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 ts4VALUES (001,'曾勇勇','18401284022'),(002,'帅坤坤','2563424216');SELECT * FROM ts4;INSERT INTO ts4 VALUES(NULL,'邱楠',NULL);  #  Column 'stu_id' cannot be null  非空INSERT INTO ts4VALUES (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 NULLconstraint fk_studnet_course  foreign key(course_id)references course_set_null(course_id)ON DELETE SET NULLON 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 salaryselect 'SS', NULL union allselect 'S', 10000 union allselect 'A', 8000 union allselect 'B', 5000 union allselect 'C', 3000;
select * from salary;insert into departmentselect 1, '董事长','SS' union allselect 2, '人事部','S' union allselect 3, '后勤部', 'B' union allselect 4, '财务部', 'C' union allselect 5, '研发部' , 'A';
SELECT * FROM department;insert into employee(name, department_id)select '张三',1 union allselect '李四',2 union allselect '王五',3 union allselect '赵六',4 union allselect '孙八',3 union allselect '杨九',5 union allselect '吴十',5;SELECT * FROM employee;
-- ------------------ 多表查询-------------------------------------

9 连接方式

-- 9.1 内连接  只显示匹配数据
INSERT INTO employee VALUES(1007,'kk','2000-1-1',NULL);SELECT department.department_name, employee.name FROM employeeINNER JOIN departmentON department.department_id = employee.department_id;
-- 9.2 外连接   显示所有匹配数据，包括nul
-- 4.1.2.1  左连接SELECT department.department_name, employee.nameFROM employeeLEFT JOIN departmentON department.department_id =  employee.department_id;
-- 4.1.2.2  右连接 
SELECT department.department_name, employee.nameFROM employeeRIGHT JOIN departmentON department.department_id =  employee.department_id;-- 9.3  全连接 ： 左连接 UNION  右连接
SELECT department.department_name, employee.nameFROM employeeLEFT JOIN departmentON department.department_id =  employee.department_id UNION
SELECT department.department_name, employee.nameFROM employeeRIGHT JOIN departmentON 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;  -- 包含nullSELECT COUNT(department_id) FROM employee;  -- 对于某一字段，不包含nullSELECT 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 allselect 2,600,'2018-11-11', 'jack' union allselect 3,800,'2018-11-11','johan' union allselect 4,900, '2018-11-14','mary' union allselect 5,600, '2018-11-15','jack' union allselect 6,100, '2018-11-15','johan' union allselect 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_viewAS 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()BEGINSELECT * FROM department;END;
SELECT * FROM orders;--14.2 调用存储过程CALL display_department;-- 14.3 带参函数过程-- 任务：获取指定日期最大单笔订单
CREATE PROCEDURE getMaxOrder(in odate date)
BEGINSELECT customer, MAX(orderprice) FROM ordersWHERE orderdate = odate;
END;CALL getMaxOrder('2018-11-11');
DROP PROCEDURE getMaxOrder;--14.4 使用存储过程插入数据并查询结果

15.流程控制

-- 15.1 局部变量声明/赋值    
CREATE PROCEDURE test1()
BEGINDECLARE x int;DECLARE y date;BEGINSET x = 10;SET y = NOW();SELECT x, y;END;
END;
CALL test1();-- 15.2 if 判断语句
CREATE PROCEDURE test2(in i_x int)
BEGINIF i_x < 60 THENSELECT  i_x AS code,'不及格' AS '成绩';ELSESELECT i_x  AS CODE,'及格'  AS '成绩';END IF;
END;DROP PROCEDURE test2;
CALL test2(61);-- 15.3 case分组判断语句
CREATE PROCEDURE test3_case(in score int)
BEGINCASE scoreWHEN 1 THENSELECT 'A';WHEN 2 THENSELECT 'B';WHEN 3 THENSELECT 'C';ELSESELECT 'D';END CASE;
END;CALL test3_case(3);
CALL test3_case(5);-- 15.4 WHILE 循环语句
CREATE PROCEDURE test4_while(OUT sum INT)
BEGINDECLARE i INT DEFAULT 1;DECLARE s INT DEFAULT 0;WHILE i <= 10 DOSET 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()
BEGINDECLARE i INT DEFAULT 0;l:LOOPSET i = i+1;IF i = 11 THENLEAVE l;END IF;END LOOP;SELECT i;
END;CALL test5_loop();-- 15.6 repeat 循环语句
CREATE PROCEDURE test6_repeat()
BEGINDECLARE i INT DEFAULT 0;REPEATSET i = i + 1;UNTIL i > 11END 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
BEGINif new.number < 0 THENSET new.number = 0;ELSEIF new.number >100 THENSET 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
BEGININSERT 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
BEGININSERT 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=''）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 ALL2.整个数据库，使用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.mysqldump2.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语句来刷新和重新开始所有日志文件

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！