一文搞定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 ，分组后having12.索引、视图
-- 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语句来刷新和重新开始所有日志文件
扫一扫
1216
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
