mysql从删库到跑路之常用命令

本文主要介绍mysql常用命令

DQL

数据查询语言（Data Query Language）：其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。这些DQL保留字常与其它类型的SQL语句一起使用。

Example：

SELECT name from user; // 查询user表中的name字段
SELECT id,name FROM user;  // 查询user表中的id和name字段
SELECT * FROM user;  // 查询user表中的所有字段
SELECT name AS userName FROM user;  // 查询user表中的name字段以userName作为返回结果(AS可省略)
SELECT CONCAT(id,name) AS newName FROM user;  // 查询user表中的id和name字段并将其拼接为newName字段作为返回结果
SELECT CONCAT(id,name,IFNULL(age,0)) AS output FROM user; // 查询user表中的id,name和age(age为null则视为0)字段并将其拼接为output字段作为返回结果
SELECT DISTINCT id FROM user;  // 查询user表中的id字段并去重
SELECT name FROM user WHERE id=1;  // 查询user表中的id为1的name字段(=,>,<,!=,<>)
SELECT id FROM user WHERE id>=1 AND salary<=10; // 查询user表中的id大于等于1并且小于等于10的id字段(AND,OR,NOT,&&,||,!)
SELECT id FROM user WHERE employee_id BETWEEN 1 AND 10; // 查询user表中的id大于等于1并且小于等于10的id字段(包含两个临界值，并且临界值不能交换顺序)
SELECT name FROM user WHERE name LIKE '%a%'; // 查询user表中的name字段包含a的name字段(%为通配符，表示任意多个字符，包含0个)
SELECT name FROM user WHERE name LIKE '__n_l%'; // 查询user表中的name字段第三个字符为n，第五个字符为l的name字段(_为通配符，表示任意单个字符)
SELECT id FROM user WHERE id IN (1,2,3); // 查询user表中的id字段属于1,2,3列表中的一项的id字段
SELECT age FROM user WHERE age IS NULL;  // 查询user表中的age字段等于NULL的age字段
SELECT age FROM user WHERE age IS NOT NULL;  // 查询user表中的age字段不等于NULL的age字段
SELECT age FROM user WHERE age <=> NULL;  // 查询user表中的age字段等于NULL的age字段
SELECT 2*IFNULL(age,0) AS two_age FROM user;  // 查询user表中的age字段的两倍并将其作为two_age字段返回结果
SELECT * FROM user ORDER BY age DESC;  // 查询user表中的所有字段，并按age降序排列
SELECT * FROM user ORDER BY age ASC;  // 查询user表中的所有字段，并按age升序排列(ASC可省略)
SELECT LENGTH(name) AS name_length,name from user ORDER BY LENGTH(name); // 查询user表中的name字段，将name字段长度作为name_length字段返回，并按name字段长度进行升序排列
SELECT *  FROM user ORDER BY age ASC, id DESC; // 查询user表中的所有字段，并按age升序和id降序排列，以前者为主
SELECT UPPER(name) AS new_name FROM user; // 查询user表中的name字段转成大写，并以new_name作为返回结果
SELECT LOWER(name) AS new_name FROM user; // 查询user表中的name字段转成小写，并以new_name作为返回结果
SELECT SUBSTR(name,1,3) AS new_name user; // 查询user表中的name字段并截取从索引1处字符长度为3的字符，并作为new_name字段作为返回结果(不指定长度则为所有)
SELECT INSTR(name,'a') AS a_index FROM  user; // 查询user表中的name字段中字符串a第一次出现的索引，并以a_index作为返回结果
SELECT TRIM(name) AS new_name FROM user; // 查询user表中的name字段，去除前后空格并作为new_name字段返回
SELECT TRIM('a' FROM name) AS new_name FROM user; // 查询user表中的name字段，去除前后字符串a并作为new_name字段返回
SELECT LPAD(name,10,'*') AS new_name FROM user; // 查询user表中的name字段，以字符*左填充至10位，并作为new_name字段返回
SELECT RPAD(name,10,'*') AS new_name FROM user; // 查询user表中的name字段，以字符*右填充至10位，并作为new_name字段返回
SELECT REPLACE(name,'a','b') AS new_name FROM user; // 查询user表中的name字段，将其中的a替换为b，并作为new_name字段返回
SELECT ROUND(money,2) AS new_money FROM user; // 查询user表中的money字段进行四舍五入保留两位小数，并作为new_money字段返回(不写第二个参数则为整数)
SELECT CEIL(money) AS new_money FROM user; // 查询user表中的money字段向上取整，并作为new_money字段返回
SELECT FLOOR(money) AS new_money FROM user; // 查询user表中的money字段向下取整，并作为new_money字段返回
SELECT TRUNCATE(money,1) AS new_money FROM user; // 查询user表中的money字段截断保留一位小数，并作为new_money字段返回
SELECT MOD(money,30) AS new_money FROM user; // 查询user表中的money字段除以30取余，并作为new_money字段返回
SELECT NOW(); // 返回当前系统日期时间
SELECT CURDATE(); // 返回当前系统日期，不包含时间
SELECT CURTIME(); // 返回当前系统时间，不包含日期
SELECT YEAR(NOW()); // 返回当前系统年份
SELECT MONTH(NOW()); // 返回当前系统月份(数字)
SELECT MONTHNAME(NOW()); // 返回当前系统月份(英文)
SSELECT STR_TO_DATE(NOW(),'%Y-%c-%d'); // 以%Y-%c-%d  格式返回当前系统日期(%Y：四位年份，%y：两位年份，...)
SELECT DATE_FORMAT(NOW(),'%Y年%c月%d日'); // 返回当前系统日期并转换为%Y年%c月%d日格式
SELECT DATEDIFF(NOW(),'1998-7-18'); // 返回当前系统日期距离1997-7-18的天数
SELECT VERSION(); // 查看数据库版本号
SELECT DATABASE(); // 查看当前数据库
SELECT USER(); // 查看当前登录用户
SELECT name,money,IF(money IS NULL,'有钱','没钱') AS money_text FROM user; // 查询user表中的name,money字段并判断有没有钱，并作为money_text字段返回
SELECT money,id, CASE id WHEN 30 THEN money*1.5 ELSE money END AS new_money FROM user; // 查询user表中的id,money字段并判断id是否为30，是则对应得money*1.5否则直接返回，并作为new_money字段返回
SELECT money, CASE WHEN money>2000 THEN 'A' WHEN money>15000 THEN 'B' ELSE 'C' END AS money_star FROM user; // 查询user表中的name,money字段并根据大小评级，并作为money_star字段返回
SELECT SUM(money) FROM user;  // 查询user表中的money总和(SUM:总和,AVG:平均值,MAX:最大值,MIN:最小值,COUNT:总数)
SELECT MAX(money),age FROM user GROUP BY age; // 查询user表中的money字段，根据age分组计算最大值并返回
SELECT COUNT(*),age FROM user GROUP BY age HAVING COUNT(*)>2; // 查询user表中的age字段，根据age分组并计算相应的总和，同事筛选出总数大于2的字段返回
SELECT name,job FROM user,jobs WHERE user.job_id=jobs.id; // 查询user表中的name字段并关联jobs表中的job字段
SELECT name,job FROM user u INNER JOIN jobs j ON u.job_id=j.id; // 查询user表中的name字段并关联jobs表中的job字段(INNER可以省略)
SELECT name,job FROM user u LEFT OUTER JOIN jobs j ON u.job_id=j.id WHERE j.id IS NULL; // 查询user表中的name字段并关联jobs表中的job字段没有则为NULL，然后筛选job为NULL的字段
SELECT name,job FROM jobs j RIGHT OUTER JOIN user u ON u.job_id=j.id WHERE j.id IS NULL; // 查询user表中的name字段并关联jobs表中的job字段没有则为NULL，然后筛选job为NULL的字段
SELECT name,job FROM user u CROSS JOIN jobs j; // 查询user表和jobs表的所有交叉集name和job字段
SELECT * FROM user WHERE age>(SELECT age FROM user WHERE name='Abel'); // 查询user表中age大于name为Abel的所有字段
SELECT MIN(money),name FROM user GROUP BY age HAVING MIN(money)>(SELECT MIN(money) FROM user WHERE age=50); // 查询user表中每个年龄分段的最小money大于age为50分段最小money最小money和name字段
SELECT job_name FROM job j WHERE EXISTS (SELECT * FROM user u WHERE u.job_id=j.id); // 查询job中分配员工的工种job_name字段
SELECT job_name FROM job j WHERE j.id IN (SELECT job_id FROM user); // 查询job中分配员工的工种job_name字段
SELECT * FROM user LIMIT 0,5; // 查询user表中从索引0开始的5条信息(索引为0时可省略)
SELECT * FROM user WHERE name LIKE '%a%' UNION SELECT * FROM user WHERE id>90; // 查询user表中name字段包含a的信息和user表中id大于90的信息合并(UNION默认去重，UNION ALL可以包含重复项)

DML

数据操作语言（Data Manipulation Language）：其语句包括动词INSERT、UPDATE和DELETE。它们分别用于添加、修改和删除。

Example：

INSERT INTO user(id,name,age,money)VALUES(13,'Pick',18,NULL),(14,'Anda',24,500); // 向user表中插入两行对应字段的数据(字段名可省略，省略默认为所有字段)
INSERT INTO user SET id=15,name='Paker',age=27,money=20; // 向user表中插入一行对应字段的数据
INSERT INTO user(id,name,age,money) SELECT 16,'Anna',15,7; // 向user表中插入一行对应字段的数据
UPDATE user SET age=30,money=3000 WHERE name LIKE '唐%'; // 更改user表中name以唐开头的信息中age字段为30，money字段为3000
UPDATE user u INNER JOIN jobs j ON j.id=u.job_id SET u.money=200 WHERE j.job_name='IT'; // 更改user表中job_id与jobs中的id相同并且job_name为IT的money字段为200
DELETE FROM user WHERE phone LIKE '%9'; // 删除user表中phone字段以9结尾的信息
DELETE u FROM user u INNER JOIN jobs j ON u.job_id=j.id WHERE j.job_name='IT'; // 删除user表中的job_id与job表中的id相同并且job_name为IT的
TRUNCATE TABLE jobs; // 清空jobs表(TRUNCATE不能回滚，DELETE可以回滚)

DDL

数据定义语言（DDL）：其语句包括动词CREATE,ALTER和DROP。在数据库中创建新表或修改、删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。

Example：

CREATE DATABASE books; // 创建books数据库(如果books数据库已存在则报错)
CREATE DATABASE IF NOT EXISTS books; // 如果books数据库不存在则创建books数据库(如果books数据库已存在不会报错)
ALTER DATABASE books CHARACTER SET gbk; // 设置books数据库的字符集为gbk
DROP DATABASE books; // 删除books数据库(如果books数据库不存在则报错)
DROP DATABASE IF EXISTS books; // 如果books数据库存在则删除books数据库(如果books数据库不存在不会报错)
CREATE TABLE book(id INT,bName VARCHAR(20),price DOUBLE,authorId INT,publishDate DATETIME); // 创建book表并设置字段id为INT类型，bName为VARCHAR(20)类型，price为DOUBLE类型，authorId为INT类型，publishDate为DATETIME类型
CREATE TABLE author(id INT,au_name VARCHAR(20),nation VARCHAR(10)); // 创建author表并设置字段id为INT类型，au_name为VARCHAR(20)类型，nation为VARCHAR(10)类型
ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME; // 修改book表中的publishDate字段为pubDate
ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP; // 修改book表中的pubDate字段的类型为TIMESTAMP
ALTER TABLE author ADD COLUMN annual DOUBLE; // 向author表中的添加annual字段并设置为DOUBLE类型
ALTER TABLE author DROP COLUMN annual; // 删除author表中的annual字段
ALTER TABLE author RENAME TO book_author; // 修改author表名为book_author
DROP TABLE book_author; // 删除book_author表(如果book_author表不存在则报错)
DROP TABLE IF EXISTS book_author;// 如果book_author表存在则删除book_author表(如果book_author表不存在则不会报错)
CREATE TABLE copy LIKE author; // 复制author表的结构并创建一张名为copy的新表
CREATE TABLE copy2 SELECT * FROM author; // 复制author表的结构和数据并创建一张名为copy2的新表
CREATE TABLE IF NOT EXISTS stuinfo(id INT PRIMARY KEY, stuName VARCHAR(20) NOT NULL,gender CHAR(1),age INT DEFAULT 18,seat INT UNIQUE,majorId INT,CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorId) REFERENCES major(id)); // 创建stuinfo表并添加约束字段(id为INT类型主键，stuName为VARCHAR(20)类型不为空，gender为CHAR(1)类型，age为INT类型默认18，seat为INT类型唯一，majorId为INT类型外键，关联major表中的id)
SHOW INDEX FROM stuinfo; // 查看stuinfo表中所有的索引
ALTER TABLE stuinfo MODIFY COLUMN stuName VARCHAR(20) NOT NULL; // 修改stuinfo表中的stuName字段类型为VARCHAR(20)并且添加不为空的约束
ALTER TABLE stuinfo ADD UNIQUE(stuName); // 修改stuinfo表中的stuName字段添加唯一约束
ALTER TABLE stuinfo DROP PRIMARY KEY; // 删除stuinfo表中主键
ALTER TABLE stuinfo DROP INDEX seat; // 删除stuinfo表中索引为seat的约束
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major; // 删除stuinfo表中索引为fk_stuinfo_major的外键
CREATE TABLE tab_identity(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20)); // 创建tab_identity表并添加约束字段(id为INT类型自增主键，name为VARCHAR(20)类型)

TCL

事务控制语言（TCL）：它的语句能确保被DML语句影响的表的所有行及时得以更新。包括COMMIT（提交）命令、SAVEPOINT（保存点）命令、ROLLBACK（回滚）命令。

Example：

SHOW ENGINES; // 查询mysql的存储引擎

SET autocommit=0; // 关闭自动提交
START TRANSACTION; // 开启事务(可省略)
UPDATE user SET money=500 WHERE username='Anna';
UPDATE user SET money=1500 WHERE username='Pick';

COMMIT; // 提交事务
SET autocommit=0; // 关闭自动提交
START TRANSACTION; // 开启事务(可省略)
UPDATE user SET money=1000 WHERE username='Anna';
UPDATE user SET money=1000 WHERE username='Pick';
ROLLBACK; // 回滚事务

SET autocommit=0; // 关闭自动提交
START TRANSACTION; // 开启事务(可省略)
DELETE FROM account WHERE id=25;
SAVEPOINT a; // 设置回滚点a
DELETE FROM account WHERE id=28;
ROLLBACK TO a; // 回滚到a点

视图

Example：

CREATE VIEW v1 AS SELECT stuName,majorName FROM  stuinfo s INNER JOIN major m ON s.majorid=m.id; // 将查询逻辑创建为视图v1
CREATE OR REPLACE VIEW myv1 AS SELECT AVG(salary),job_id FROM employees GROUP BY jobas_id; // 将查询逻辑创建或替换为视图v1
ALTER VIEW myv1 AS SELECT * FROM employees; // 修改视图myv1
DROP VIEW myv1; // 删除视图myv1

变量

Example：

SHOW GLOBAL VARIABLES; // 查看所有的全局变量 
SELECT @@GLOBAL.autocommit; // 查看全局变量autocommit
SET @@GLOBAL.autocommit=0; // 设置全局变量autocommit为0
SHOW SESSION VARIABLES; // 查看所有会话变量
SELECT @@SESSION.autocommit; // 查看会话变量autocommit
SET @@SESSION.autocommit=0; // 设置会话变量autocommit为0
SET @name='john'; // 设置用户变量
SELECT COUNT(*) INTO @count FROM jobs; // 设置用户变量count
SELECT @count; // 查询用户变量count
SET @m=1; // 设置局部变量m为1
DECLARE n INT DEFAULT 1; // 设置局部变量n默认为1