MYSQL的操作命令
一、御前
1 win+R DOS 输入 net start mtsql 和 net stop mysql 启动和停止Mysql 服务，也可通过计算机——管理——服务和应用程序——服务——MYSQL——右击
启动mysql服务出现服务名无效的原因及解决方法【失败】
问题原因：mysql服务没有安装。
解决办法： 在 mysql bin目录下 以管理员的权限 执行 mysqld -install命令
以管理员的权限 mysqld -remove ，卸载mysql服务
2 登录和退出
路径： DOS：mysql -uroot -p
输入密码
exit; 退出
show databases; 查看数据库
Command Line Client登录和退出
3 常见操作
\h 或者 help;
source D:\test.sql 即执行test.sql文件
4 图形 MYSQL Workbench
另外介绍第三方 SQLyog
二、数据库和表的基本操作
1、MySQL支持的数据类型
1）数值类型 字符串类型 日期和时间类型
2）数据库基本操作
CREATE DATABASE 数据库名称;
SHOW DATABASE;
SHOW CREATE DATABASE 数据库名称; 查看已经创建的数据库的创建信息
CREATE DATABASE 数据库名称 CHARACTER SET gbk; 在创建数据库时指定编码方式
USE 数据库名;
ALTER DATABASE 数据库名称 DEFAULT CHARACTER SET 编码方式 COLLATE 编码方式_bin;编码方式为修改后的
DROP DATABASE 数据库名称;
3) 数据表的基本操作
CREATE TABLE stu( stu_id INT(10),stu_name VARCHAR(50) );
SHOW CREATE TABLE 表名;
SHOW CREATE TABLE 表名\G;格式化显示创建信息
DESCRIBE 表名; 表的列相关信息
ALTER TABLE 原表名 RENAME [TO] 新表名;
ALTER TABLE 表名 CHANGE 原字段名 新字段名 新数据类型;
ALTER TABLE 表名 MODIFY 字段名 新数据类型;
ALTER TABLE 表名 ADD 新添字段名 数据类型;
AlTER TABLE 表名 DROP 字段名;
ALTER TABLE 表名 MODIFY 字段名1 数据类型 FIRST|AFTER 字段名2; 调整字段位置
DROP TABLE 表名；
三、表中数据的基本操作
INSERT INTO 表名 (字段1，字段2)VALUES{值1，值2);
INSERT INTO 表名 VALUES(值1,,值2); 为所有的字段都填上数据
INSERT INTO 表名(字段名1,字段名2)VALUES(值1,值2);为指定字段添加数据
INSERT INTO 表名[(字段名1，字段名2，…)]VALUES（值1，值2，…），…，（值1，值2，…）;
UPDATE 表名 SET 字段名1=值1 [字段名2=值2,…] WHERE ;
DELETE FROM 表名 WHERE;删除指定的全部数据
TRUNCATE [TABLE] 表名；删除表中的所有数据
四 单表查询
SELECT 字段名1，…字段名n FROM 表名;
SELECT * FROM 表名;
SELECT * FROM 表名 WHERE ;
SELECT * FROM 表名 WHERE 条件表达式1 AND|OR 条件表达式2 ;
SELECT * FROM 表名 WHERE 字段名 [NOT] IN (元素1，元素2，…，元素n) ;
SELECT * FROM 表名 WHERE 字段名 IS [NOT] NULL ;
SELECT * FROM 表名 WHERE 字段名 [NOT] BETWEEN 值1 AND 值2 ;
SELECT * FROM 表名 WHERE 字段名 [NOT] LIKE '匹配字符串';其中%表示n个字符，_表示一个
SELECT DISTINCT 字段名 FROM 表名 ;去重复
SELECT * FROM 表名 WHERE 字段1 [ASC|DESC],字段2 [ASC|DESC] ;ASC升序
2）聚合函数
SELECT COUNT (*|1|列名) FROM 表名;
SELECT SUM (字段名) FROM 表名;
SELECT AVG (字段名) FROM 表名;
SELECT MAX (字段名) FROM 表名;
SELECT MIN (字段名) FROM 表名;
3）分组
SELECT 字段名1，…字段名n FROM 表名 GROUP BY 字段名1，字段名2，…[HAVING 条件表达式];
4）分页
SELECT 字段名1,字段名2,…FROM 表名 LIMIT [m,]n;【m代表起始数目，n代表个数】
五 数据的完整性
ALTER TABLE 表名 ADD PRIMARY KEY(列名);主键 不空 不重
或者 CREATE TABLE 表名 (字段名 数据类型 PRIMARY KEY);
ALTER TABLE 表名 ADD UNIQUE(列名);唯一约束
或者 CREATE TABLE 表名 (字段名 数据类型 UNIQUE);
ALTER TABLE 表名 ADD PRIMARY KEY AUTO_INCREMENT(列名);自增
或者 CREATE TABLE 表名 (字段名 数据类型 AUTO_INCREMENT);
2）索引
AlTER INDEXE 索引名 ON 表名(字段名[(长度)]);
或者 CREATE TABLE 表名 (字段名 数据类型 ，INDEX [索引名] （字段名[(长度)]）);
唯一索引：【不可有重复值】
在普通的INDEX 前加 UNIQUE
3）域完整性
ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL,…;非空约束
或者 CREATE TABLE 表名 (字段名 数据类型 NOT NULL，);
ALTER TABLE 表名 MODIFY 字段名 数据类型 DEFAULT 默认值;默认值约束
或者 CREATE TABLE 表名 (字段名 数据类型 DEFAULT 默认值，);
4）引用完整性
ALTER TABLE 表名 ADD FOREIGN KEY(外键字段名) REFERENCES 主表表名(主键字段名);
CREATE TABLE 表名 (字段名 数据类型,FOREIGN KEY(外键字段名) REFERENCES 主表表名（主键字段名）);
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
六、多表查询
1）合并结果集
SELECT * FROM test1 UNION SELECT * FROM test2;会过滤重复数据
SELECT * FROM test1 UNION ALL SELECT * FROM test2;不会过滤重复数据
2）连接查询
SELECT 查询字段 FROM 表1 CROSS JOIN 表2 WHERE ；笛卡尔积
SELECT 查询字段 FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段=表2.关系字段 Where ；内连接
SELECT 查询字段 FROM 表1 LEFT [OUTER] JOIN 表2 ON 表1.关系字段=表2.关系字段 Where ；左外连接
SELECT 查询字段 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 表1.关系字段=表2.关系字段 Where ；右外连接
SELECT 查询字段 FROM 表1 [别名] JOIN 表2 [别名] ON 表1.关系字段=表2.关系字段 JOIN 表 m ON…;多表
SELECT 查询字段 FROM 表1 [别名] NATURAL JOIN 表2 [表名];自然连接 列名和数据类型相同的字段自动匹配
SELECT 查询字段 FROM 表1 [别名] NATURAL [LEFT|RIGHT] JOIN 表2 [表名];
SELECT 查询字段 FROM 表名 [别名1]，表名[别名2] WHERE 查询条件;自连接
3）子查询
SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='JONES');子查询做条件
SELECT e.ename,e.sal,d.dname,d.loc FROM emp e,（SELECT dname，loc，deptno FROM dept）d Where
e.deptno=d.deptno AND e.empmo=7788;子查询做表
七、常用函数
1）字符串函数
ASCII();
CONCAT();
INSERT();
LEFT();
RIGHT();
LENGTH();【占用字节】
2）数学函数
ABS();
MOD();
PI();
RAND();
ROUND();
TRUNCATE();【保留指定位数的结果】
3）日期和时间函数
DAY();
WEEK();
MONTH();
YEAR();
NOW();
4）格式化函数
FORMAT();
DATE_FORMAT();
5）系统信息函数
DATABASE();
USER();
SYSTEM_USER();
VERSION();
八、视图
SELECT Drop_priv FROM mysql.user WHERE User='root';查询用户对视图权限
CREATE VIEW view_emp(id,name,sex,age,department) AS SELECT id,name,sex,age,department FROM emp;还有一些可选的权限条件，用到再百度吧
DESCRIBE 视图名;视图字段信息
DESC 视图名;
SHOW TABLE STATUS LIKE '视图名';视图的基本信息
SHOW CREATE VIEW 视图名;视图详细信息
CREATE OR REPLACE VIEW view_emp_detail(id,name,sex) AS SELECT id,name,sex FROM emp;修改
ALTER VIEW view_emp AS SELECT name,age FROM emp;修改
UPDATE 视图名 SET 字段名1=值1 [字段名2=值2,…] [WHERE 条件表达式];
INSERT INTO view_emp VALUES (值1，值2,……);
DELETE FROM 表名 [条件表达式];删除数据
DROP VIEW [IF EXISTS] 视图名 [,视图2]… [RESTRICT|CASCADE];删除视图
九、存储过程
1 查看用户是否拥有创建存储过程的权限
SELECT Create_routine_priv FROM mysql.user Where User='root';
CREATE PROCEDURE sp_name ([proc_parameter[…]]) [characteristic…] routine_body;创建
SHOW PROCEDURE STATUS [LIKE 'pattern'];查看存储过程的状态
SHOW CREATE PROCEDURE sp_name;查看存储过程的创建信息
SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='SP_SEARCH' AND ROUTINE_TYPE='PROCEDURE'\G;
SELECT Alter_routine_priv FROM mysql.user Where User='root';查看修改权限
ALTER PROCEDURE sp_name [characteristic…];修改
DROP PROCEDURE [IF EXISTS] sp_name;删除
具体的 局部变量 定义条件 处理程序 光标的使用 流程控制（IF CASE WHILE） 自己看吧
2事件调度器
数据库安自定义的时间周期来出发某种操作，可以理解为时间触发器。
SELECT @@event_scheduler;查看是否已经开启时间调度器
SET GLOBAL event_scheduler=ON；开启
使用：
CREATE
[DEFINER = { user | CURRENT_USER }]
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
DO event_body;
schedule:
AT timestamp [+ INTERVAL interval] ...
|EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]
interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
来自 <https://www.cnblogs.com/geaozhang/p/6821692.html>
十、触发器
1 创建触发器
CREATE TRIGGER trigger_name【触发器名称】
triggername_time【BEFORE|ALTER】
trihher_event【INSERT|UPDATE|DELETE】 on tablename
FOR EACH ROW
trigger_stmt;
2查看触发器
SHOW TRIGGERS\G【查看所有触发器】
DESC information_schema.triggers;【查看触发器表】
SELECT * FROM information_schema.triggers WHERE trigger_name='t_afterdelete_on_test1'\G;【查看某一个触发器具体信息】
DROP TRIGGER [IF EXISTS] [schema_name.].trigger_name;删除触发器
十一、数据库事务
START TRANSACTION;开启事务
中间写语句
COMMIT；提交事务
ROLLBACK；事务回滚——在提交之前
2事务的属性——数据库是多线程并发访问的
ACID：原子性 一致性 隔离性 持久性
事务的隔离级别：
READ UNCOMMITTED【脏读 读未提交】-所有事务都可看到其他未提交事务
READ COMMITTED【不可重复读 读已提交】—一个事务只能看到已经提交事务改变
REPEATABLE READ 【幻读 可重复度】——同一事务多个实例并发执行同样数据
SERIALIZABLE 【可串行化】-强制事务排序使之不可能互相冲突
SELECT @@tx_isolation;查看当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL (READ UNCOMMITTED)|(READ COMMITTED)|(REPEATABLE READ)|(SERIALIZABLE);设置会话隔离级别
3 分布式事务
XA {START|BEGIN} xid [JOIN|RESUME] 开始一个分布式事务
XA END xid [SUSPEND [FOR MIGRATE]] 操作分布式事务
XA PREPARE xid 准备提交事务
XA COMMIT xid [ONE PHASE] 提交事务
XA ROLLBACK xid 回滚事务
XA RECOVER [CONVERT XID] 查看处于PREPARE状态的事务
十二、MYSQL 高级操作
1 数据的备份
mysql -uusername -ppassword dbname>path:filename.sql;
SQLyog 图形界面sql文件形式转储
2 数据的还原
mysql -uusername -ppassword dbname<path:filename.sql;
SQLyog 图形界面 执行SQL脚本
3 权限管理【百度去吧】
MYSQL的权限与user表
授予权限：
查看权限：SHOW GANTS FOR ‘username’@‘hostname’
收回权限：
4 MYSQL分区【具体自己看书去吧】
RANGE分区 LIST分区 HASH分区 KEY分区
