MySQL 常用的指令

1.mysql 启动停止

mysql.server start 启动 mysql.server stop 关闭 quit 退出

2.设置root账户和用户密码

(1).mysqladmin -u root password 123456(初始密码) (2).修改密码输入：mysqladmin -u root -p password XXXX(新密码)会让输入之前的密码来修改 3.链接数据库 (1).终端输入：mysql -u root -p 然后输入刚才设置的密码 (2).如果是登录远程主机上的mysql数据库:mysql -h 主机地址 -u 用户名 -p 用户密码 4.创建用户 CREATE USER <用户名> [ IDENTIFIED ] BY [ PASSWORD ] <口令> 修改用户账号： RENAME USER <旧用户> TO <新用户> 修改密码：SET PASSWORD FOR 'jack'@'localhost'= PASSWORD('lion'); 删除用户： DROP USER 'jack'@'localhost'; 授权用户权限：

GRANT <权限类型> [ ( <列名> ) ] [ , <权限类型> [ ( <列名> ) ] ] ON <对象> <权限级别> TO <用户> 其中<用户>的格式： <用户名> [ IDENTIFIED ] BY [ PASSWORD ] <口令> [ WITH GRANT OPTION] | MAX_QUERIES_PER_HOUR <次数> | MAX_UPDATES_PER_HOUR <次数> | MAX_CONNECTIONS_PER_HOUR <次数> | MAX_USER_CONNECTIONS <次数>

查询用户权限:

mysql> GRANT SELECT,INSERT ON . -> TO 'testUser'@'localhost' -> IDENTIFIED BY 'testPwd' -> WITH GRANT OPTION;

删除权限： REVOKE <权限类型> [ ( <列名> ) ] [ , <权限类型> [ ( <列名> ) ] ]… ON <对象类型> <权限名> FROM <用户1> [ , <用户2> ]… REVOKE ALL PRIVILEGES, GRANT OPTION FROM user <用户1> [ , <用户2> ]…

mysql> REVOKE INSERT ON . -> FROM 'testUser'@'localhost';

3.创建数据库，表 等

创建一个名为 test_db 的数据库： CREATE DATABASE test_db; （CREATE DATABASE IF NOT EXISTS test_db） 查看所有数据库： SHOW DATABASES; 查看某些数据库： SHOW DATABASES LIKE 'test_db'; 查看包含数据库： SHOW DATABASES LIKE '%test%'; 查看db开头数据库： SHOW DATABASES LIKE 'db%'; 查看db结尾数据库： SHOW DATABASES LIKE '%db'; 查看 test_db 定义声明: SHOW CREATE DATABASE test_db; 创建指定字符集并查看： mysql> CREATE DATABASE test_db -> DEFAULT CHARACTER SET gb2312 -> DEFAULT COLLATE gb2312_chinese_ci; mysql> SHOW CREATE DATABASE test_db;

删除数据库test_db_del：DROP DATABASE IF EXISTS test_db_del; 切换数据库： USE test_db;

创建表tb_emp1： CREATE TABLE tb_emp1 ( id INT(11), name VARCHAR(25), daptId INT(11), salary FLOAT ); 查看表： SHOW TABLES； 查看表机构: DESCRIBE <表名>; (DESC <表名>;) 查看创表时的语句: SHOW CREATE TABLE <表名>\G；

添加字段： ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [FIRST|AFTER 已存在的字段名]； 修改字段数据类型： ALTER TABLE <表名> MODIFY <字段名> <数据类型> 删除字段: ALTER TABLE <表名> DROP <字段名>； 修改字段名称: ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>； 修改表名： ALTER TABLE <旧表名> RENAME [TO] <新表名>； 删除表: DROP TABLE [IF EXISTS] <表名> [ , <表名1> , <表名2>] … 设置主键： <字段名> <数据类型> PRIMARY KEY [默认值] 设置外键： [CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…] REFERENCES <主表名> 主键列1 [，主键列2，…] 删除外键： ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>; 设置唯一约束： <字段名> <数据类型> UNIQUE 修改表时添加唯一约束： ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>); 删除唯一约束： ALTER TABLE <表名> DROP INDEX <唯一约束名>; 检查约束： CHECK <表达式> 修改表时添加检查约束：ALTER TABLE tb_emp7 ADD CONSTRAINT <检查约束名> CHECK(<检查约束>) 删除检查约束： ALTER TABLE <数据表名> DROP CONSTRAINT <检查约束名>; 设置默认值约束： <字段名> <数据类型> DEFAULT <默认值>; 修改时加默认约束： ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <数据类型> DEFAULT <默认值>; 删除默认值约束: ALTER TABLE <数据表名>CHANGE COLUMN <字段名> <字段名> <数据类型> DEFAULT NULL; 设置非空约束： <字段名> <数据类型> NOT NULL; 修改时添加非空约束： ALTER TABLE <数据表名>CHANGE COLUMN <字段名><字段名> <数据类型> NOT NULL; 删除非空约束： ALTER TABLE <数据表名>CHANGE COLUMN <字段名> <字段名> <数据类型> NULL; 查看表中的约束： SHOW CREATE TABLE <数据表名>;

插入数据： INSERT INTO <表名> [ <列名1> [ , … <列名n>] ]VALUES (值1) [… , (值n) ]; 例：mysql> INSERT INTO tb_courses -> (course_id,course_name,course_grade,course_info) -> VALUES(1,'Network',3,'Computer Network');

复制表数据： INSERT INTO…SELECT…FROM 从一个表复制到另一个表 例：mysql> INSERT INTO tb_courses_new -> (course_id,course_name,course_grade,course_info) -> SELECT course_id,course_name,course_grade,course_info -> FROM tb_courses;

修改数据： UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ] [ORDER BY 子句] [LIMIT 子句] 例： mysql> UPDATE tb_courses_new -> SET course_name='DB',course_grade=3.5 -> WHERE course_id=2

删除数据： DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]

创建视图： CREATE VIEW <视图名> AS <SELECT语句> 查询视图： DESCRIBE 视图名； 修改视图内容： ALTER VIEW AS 或 UPDATE <> -> SET age=25 WHERE id=1; 删除视图： DROP VIEW <视图名1> [ , <视图名2> …]

4.存储引擎区别

可以根据以下的原则来选择 MySQL 存储引擎： 如果要提供提交、回滚和恢复的事务安全（ACID 兼容）能力，并要求实现并发控制，InnoDB 是一个很好的选择。 如果数据表主要用来插入和查询记录，则 MyISAM 引擎提供较高的处理效率。 如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存的 MEMORY 引擎中，MySQL 中使用该引擎作为临时表，存放查询的中间结果。 如果只有 INSERT 和 SELECT 操作，可以选择Archive 引擎，Archive 存储引擎支持高并发的插入操作，但是本身并不是事务安全的。Archive 存储引擎非常适合存储归档数据，如记录日志信息可以使用 Archive 引擎

临时修改默认存储引擎： SET default_storage_engine=< 存储引擎名 >

5.常见的数据类型

在 MySQL 中常见的数据类型如下：

1. 整数类型 包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，浮点数类型 FLOAT 和 DOUBLE，定点数类型 DECIMAL。
2. 日期/时间类型 包括 YEAR、TIME、DATE、DATETIME 和 TIMESTAMP。
3. 字符串类型 包括 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET 等。
4. 二进制类型 包括 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。

6.SELECT 基本语法

SELECT {* | <字段列名>} [ FROM <表 1>, <表 2>… [WHERE <表达式> [GROUP BY <group by definition> [HAVING <expression> [{<operator> <expression>}…]] [ORDER BY <order by definition>] [LIMIT[<offset>,] <row count>] ]

{*|<字段列名>}包含星号通配符的字段列表，表示查询的字段，其中字段列至少包含一个字段名称，如果要查询多个字段，多个字段之间要用逗号隔开，最后一个字段后不要加逗号。 FROM <表 1>，<表 2>…，表 1 和表 2 表示查询数据的来源，可以是单个或多个。 WHERE 子句是可选项，如果选择该项，将限定查询行必须满足的查询条件。 GROUP BY< 字段 >，该子句告诉 MySQL 如何显示查询出来的数据，并按照指定的字段分组。 [ORDER BY< 字段 >]，该子句告诉 MySQL 按什么样的顺序显示查询出来的数据，可以进行的排序有升序（ASC）和降序（DESC）。 [LIMIT[<offset>，]<row count>]，该子句告诉 MySQL 每次显示查询出来的数据条数。

查询表中指定字段: SELECT < 列名 > FROM < 表名 >; 去重： SELECT DISTINCT <字段名> FROM <表名>; 设置别名： <表名> [AS] <别名> 显示查询行数： <LIMIT> [<位置偏移量>,] <行数> SELECT * FROM tb_students_info LIMIT 3,5;或者SELECT * FROM tb_students_info LIMIT 4; 查询结果排行： ORDER BY {<列名> | <表达式> | <位置>} [ASC|DESC] 条件查询： WHERE <查询条件> {<判定运算1>，<判定运算2>，…} <表达式1>{=|<|<=|>|>=|<=>|<>|！=}<表达式2> 判定运算有 <表达式1>[NOT]LIKE<表达式2> <表达式1>[NOT][REGEXP|RLIKE]<表达式2> <表达式1>[NOT]BETWEEN<表达式2>AND<表达式3> <表达式1>IS[NOT]NULL

模糊查询： <表达式1> [NOT] LIKE <表达式2> 内连查询: SELECT <列名1，列名2 …>FROM <表名1> INNER JOIN <表名2> [ ON子句] 例 ：ELECT id,name,age,dept_name -> FROM tb_students_info,tb_departments -> WHERE tb_students_info.dept_id=tb_departments.dept_id;

外连接查询： LEFT OUTER JOIN 例：mysql> SELECT name,dept_name -> FROM tb_students_info s -> LEFT OUTER JOIN tb_departments d -> ON s.dept_id = d.dept_id;

子查询： <表达式> [NOT] IN <子查询> <表达式> {= | < | > | >= | <= | <=> | < > | != } { ALL | SOME | ANY} <子查询> EXIST <子查询>

分组查询： GROUP BY { <列名> | <表达式> | <位置> } [ASC | DESC] 例：mysql> SELECT dept_id,GROUP_CONCAT(name) AS names -> FROM tb_students_info -> GROUP BY dept_id; 根据 dept_id 的不同分别统计了 dept_id 相同的姓名。

指定过滤条件： HAVING <条件>

7.正则表达式查询

REGEXP关键字指定政策表达式

8.自定义函数和存储过程

创建自定义函数： CREATE FUNCTION <函数名> ( [ <参数1> <类型1> [ , <参数2> <类型2>] ] … ) RETURNS <类型> <函数主体>

调用函数： SELECT <自定义函数名> ([<参数> [,...]]) 修改函数： ALTER FUNCTION 删除自定义函数： DROP FUNCTION [ IF EXISTS ] <自定义函数名>

创建触发器： CREATE <触发器名> < BEFORE | AFTER ><INSERT | UPDATE | DELETE > ON <表名> FOR EACH Row<触发器主体> 删除触发器： DROP TRIGGER [ IF EXISTS ] [数据库名] <触发器名>

9.索引

索引的类型和存储引擎有关，1,B-树索引 2，哈希索引 创建索引： CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC]) ADD INDEX [<索引名>] [<索引类型>] (<列名>,…)

创建一般索引： INDEX(字段) 创建唯一索引: UNIQUE INDEX(字段) 查看索引： SHOW INDEX FROM <表名> [ FROM <数据库名>] SHOW INDEX FROM tb_stu_info2\G 删除索引: DROP INDEX <索引名> ON <表名>

10.事务

事务具有4个特性：原子性，一致性，隔离性和持续性

开始事务： BEGIN TRANSACTION <事务名称> |@<事务变量名称> 提交事务： COMMIT TRANSACTION <事务名称> |@<事务变量名称> 撤销事务： ROLLBACK [TRANSACTION][<事务名称>| @<事务变量名称> | <存储点名称>| @ <含有存储点名称的变量名>

11.数据库备份

备份数据库：

mysql> SELECT * FROM test_db.tb_students_info -> INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/file.txt' -> FIELDS TERMINATED BY '"' -> LINES TERMINATED BY '?';

恢复数据库：

mysql> LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/ Uploads/file.txt' -> INTO TABLE test_db.tb_students_copy -> FIELDS TERMINATED BY ',' -> OPTIONALLY ENCLOSED BY '"' -> LINES TERMINATED BY '?';

12.数据库操作工具

MySQL Workbench https://dev.mysql.com/downloads/workbench/, Navicat Premium, sqlyog, phpMyAdmin 等等