MySQL常用命令

***prompt 提示符 –修改提示符
\D 完整的日期
\d 当前数据库
\h 服务器名称
\u 当前用户
如：prompt \u@\h \d>

---

* select version(); –显示当前MySQL版本

* select now(); –显示当前日期时间

* select user(); –显示当前用户

* select database(); –显示当前打开的数据库

* create {database | schema} [if not exists] db_name; –创建数据库

* create {database | schema} [if not exists] db_name [default]
character set [=] charset_name; –以特定编码方式创建数据库

* show warnings; –显示警告信息

* show create database db_name; –查看数据库创建信息

* show create table tb_name; –查看数据表的创建信息

* alter {database | schema} [db_name] [default] character set [=] charset_name; –修改数据库编码方式

* drop database db_name; –删除数据库

* use db_name –打开数据库

* show databases; –显示当前服务器下所有数据库

* show tables [from db_name]; –查看当前或其他数据库的所有表

* show columns from tb_name; –查看数据表结构

* insert [into] tb_name [(col1,col2,…)] values(val1,val2,…); –插 入

* show indexes from tb_name\G; –显示索引（\G:以网格方式呈现）

* select row_count() ; –查看前面的操作影响的行数

添加删除约束

外键约束的参照操作
1.cascade：从父表删除或更新时自动删除或更新子表中匹配的行
2.set null：从父表更新或删除行时，设置子表中匹配的外键列为null， 但必须保证该列没有约束为not null
3.restrict：拒绝对父表的删除或更新操作
4.no action：在MySQL中与restrict相同

* alter table tb_name add [constraint [symbol]] primary key [index_type] (index_col_name,…); –添加主键约束

* alter table tb_name add [constraint [symbol]] unique [index|key] [index_name] index_type; –添加唯一约束

* alter table tb_name add [constraint [symbol]] foreign key index_name reference_definition; –添加唯外键约 束

* alter table tb_name alter [column] col_name {set default val | drop default}; –添加/删除默认约束

* alter table tb_name drop primary key; –删除主键约束

* alter table tb_name drop {index | key} index_name; –删除唯一约束

* alter table tb_name drop foreign key fk_symbol; –删除外键约束

表结构修改

* alter table tb_name add [column] col_name column_definition [first | after col1_name] –添加列

* alter table tb_name drop cli_name; –删除列

修改数据表
* ALTER TABLE tb_name MODIFY [COLUMN] col_name col_definition [FIRST|AFTER col1_name] –修改列定义

* ALTER TABLE tb_name CHANGE [COLUMN] old_col_name new_col_name col_definition [FIRST|AFTER col1_name] –修改列名称及定义

* ALTER TABLE tb_name RENAME [AS|TO] new_tb_name; –修改表名
* RENAME TABLE tb_name TO new_tb_name[,tb2_name TO new_tb2_name…] –修改表名

记录操作（增删查改）
INSERT插入操作
1、INSERT [INTO] tb_name [(col_name,…)] {VALUES|VALUE}({expr| DEFAULT},…);

2、INSERT [INTO] tb_name SET col_name={expr|DEFAULT},…;

3、

UPDATE更新记录
UPDATE tb_name SET …WHERE…

删除记录
DELETE FROM tb_name WHERE…

限制返回数量
LIMIT [offset,] row_count

子查询
1、使用比较运算符的子查询
=、>、<、>=、<=、<>、!=、<=>

2、使用any、some、all修饰的比较运算符

3、由[not] in/exists引发的子查询

多表更新
1、参照其他表更新本表
使用内连接
UPDATE tb1_name INNER JOIN tb2_name ON 连接条件 SET 更新操作；

2、创建数据表的同时将查询结果写入数据表
CREATE TABLE tb_name
(create_definition)
select_statement;
例：
create table A (
id smallint unsigned primary key auto_increment ,
name varchar(40) not null )
select name from B ; –A表和B表字段名字(name)要相同

表的连接
语法结构：
tb1_name
{ [inner|cross] join | { left|right } [outer] join }
tb2_name
on 连接条件

内连接：仅显示左表及右表符合连接条件的记录
左外连接：显示左表的全部记录及右表符合连接条件的记录
右外连接：显示右表的全部记录及左表符合连接条件的记录

函数
一、字符函数
1、concat() –字符连接
select concat(‘my’,’-‘,’sql’);

2、concat_ws() –使用指定的分隔符进行字符连接
concat_ws(‘|’,’A’,’B’,’C’)

3、format() –数字格式化
select format(12345.678,2) 结果输出：12,345.68

4、lower() –转换成小写字母
lower(‘MySQL’);

5、upper() –转换成大写字母
upper(‘MySQL’)

6、left() –获取左侧字符
select left(‘MySQL’,2); 结果输出：My

7、right() –获取右侧字符
select right(‘MySQL’,2); 结果输出：QL

8、length(0 –获取字符串长度
select length(‘My SQL’); 输出结果：6

9、ltrim() –删除前导空格
ltrim(’ mysql’)

10、rtrim() –删除后续空格
rtrim(‘mysql ‘)

11、trim() –删除前导和后续空格
trim(’ mysql ‘)
–还可以删除特定字符
select trim(leading ‘?’ from ‘??my?sql???’); 输出结果：my?sql???
select trim(trailing ‘?’ from ‘??my?sql???’); 输出结果：??my?sql
select trim(both ‘?’ from ‘??my?sql???’); 输出结果：my?sql

12、substring() –字符串截取
select substring（’lanxiaoye’ , 4 , 3）; 输出结果：xia
select substring（’lanxiaoye’ ,4）; 输出结果：xiaoye
select substring（’lanxiaoye’ ,-2）; 输出结果：ye
select substring（’lanxiaoye’ ,-3 , 2）; 输出结果：oy

13、[not] like –模式匹配
–%代表任意个字符
–_代表任意一个字符
select ‘mysql’ like ‘m%’ ; 输出结果：1
select ‘mysql’ like ‘%s%’ ; 输出结果：1
select ‘my%sql’ like ‘%1%%’ escape ‘1’ ; 输出结果：1

14、replace() –字符串替换
replace(‘??my?sql???’ , ‘?’ , ‘**’)

二、数值运算符和函数

1、ceil() –进一取整，向上取整
select ceil(3.01); 输出结果：4

2、floor() –舍一取整，向下取整
select ceil(3.99); 输出结果：3

3、div –整数除法
select 3 / 4; 输出结果：0.7500
select 3 div 4; 输出结果：0

5、mod –取余（取模）
select 5.3 % 3; 输出结果：2.3
select 5 mod 3; 输出结果：2

6、power() –幂运算
select power(3,2); –输出结果：9

7、round() –四舍五入
select round(3.674,1); –输出结果：3.7

8、truncate() –数字截取
select truncate(127.89 , 1); 输出结果：127.8
select truncate(127.89 , 0); 输出结果：127
select truncate(127.89 , -1); 输出结果：120

三、比较运算符和函数

1、[not] between … and … –[不]在范围内
select 10 between 1 and 30; 输出结果：1

2、[not] in() –在不在列出值范围内
select 10 in(1,10,15,30); 输出结果：1

3、is [not] null –是否为空

四、日期时间函数

1、now() –当前日期和时间
select now();

2、curdate() –当前日期
select curdate();

3、curtime() –当前时间
select curtime();

4、date_add() –日期变化
select date_add(‘2016-8-26’ , interval , 300 day);
select date_add(‘2016-8-26’ , interval , 3 year);
select date_add(‘2016-8-26’ , interval , -3 week);

5、datediff() –日期差值
select datediff(‘2015-8-26’ , ‘2016-8-26’); 输出结果：-366

6、date_format() –日期格式化

五、信息函数

1、connection_id() –连接ID
select connection_id();

2、database() –当前数据库
select database();

3、last_insert_id() –最后插入记录的id号
select last_insert_id();

4、user() –当前用户
select user();

5、version() –版本信息
select version();

六、聚合函数

1、avg() –平均值

2、count() –计数

3、max() –最大值

4、min() –最小值

5、sum() –求和

七、加密函数

1、md5() –信息摘要算法
select md5(‘mysql’);

2、password() –密码算法
select password(‘mysql’);

自定义函数
创建自定义函数
CREATE FUNCTION function_name
RETURNS
{STRING | INTEGER | REAL | DECIMAL}
routine_body

关于函数体
1、函数体由合法的sql语句构成；
2、函数体可以是简单的select或insert语句；
3、函数体如果为复合结构则使用begin…end语句；
4、复合结构可以包含声明、循环、控制结构；

创建不带参数的自定义函数
例子：
CREATE FUNCTION f1() RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW() , ‘%Y年%m月%d日 %H点：%i分：%s秒’);

调用：select f1();

创建带参数的自定义函数
例子：
CREATE FUNCTION f2(num1 SMALLINT UNSIGNED , num2 SMALLINT UNSIGNED)
RETURNS FL0AT(10 , 2) UNSIGNED
RETURN (num1 + num2) / 2 ;

调用：select f2(10 , 15);

创建具有复合结构函数体的自定义函数
例子：
DELIMITER //
CREATE FUNCTION f3(username VARCHAR(20))
RETURNS INT UNSIGNED
BEGIN
INSERT test(username) VALUES(username);
RETURN LAST_INSERT_ID();
END
//

调用：select f3(‘Tom’);

删除函数：
DROP FUNCTION [IF EXISTS] function_name;

存储过程
创建存储过程
CREATE
[DEFINER = { USER | CURRENT_USER }] –省略则默认为当前用户
PROCEDURE sp_name ( [ proc_parameter[,…] ] )
[ characteristic … ] routine_body

proc_parameter:
[ IN | OUT | INOUT ] param_name type

过程体跟函数体基本一致

创建简单的不带参数的存储过程
create procedure sp1() select version() ;

调用：call sp1 ;
call sp1() ;

创建带IN参数的存储过程
DELIMITER //
CREATE PROCEDURE removeUserById( IN uid INT UNSIGNED )
BEGIN
DELETE FROM users WHERE id = uid ; –删除id = uid 的记录
END
//
DELIMITER ;

调用：call removeUserById(10) ;

//删除指定id的记录并返回剩余的记录数
DELIMITER //
CREATE PROCEDURE removeUserAndReturnNums( IN uid INT UNSIGNED , OUT num INT UNSIGNED )
BEGIN
DELETE FROM users WHERE id = uid ;
SELECT count( id ) from users INTO num ;
END
//
DELIMITER ;

调用：call removeUserAndReturnNums( 22 , @nums ) ;
select @nums ;

SET @a = 7 ; –创建变量

//创建带有多个OUT类型参数的存储过程
//根据年龄age字段删除记录并返回删除的用户数和剩余的用户数
DELIMITER //
CREATE PROCEDURE pro1( IN p_age SMALLINT UNSIGNED , OUT del_num SMALLINT UNSIGNED , OUT user_num SMALLINT UNSIGNED )
BEGIN
DELETE FROM users WHERE age = p_age ;
SELECT ROW_COUNT() INTO del_num ;
SELECT COUNT( id ) FROM users INTO user_num ;
END
DELIMITER ;

DROP PROCEDURE [IF EXISTS] sp_name ; –删除存储过程

存储过程可以返回多个值，而函数只能有一个返回值
存储过程一般独立的执行，而函数可以作为其他sql语句的组成部分来出现

存储引擎
MySQL可以将数据以不同的技术存储在文件（内存）中，这种技术成为存储引擎。
每一种存储引擎使用不用的存储机制、索引技巧、锁定水平，最终提供广泛且不同的功能。

MySQL支持的存储引擎
-MyISAM
-InnoDB
-Memory
-CSV
-Archive

* 并发控制：      当多个连接对记录进行修改时保证数据的一致性和完整性。* 锁      -共享锁（读锁）：在同一时间段内，多个用户可以读取同一个资源，读取过程中数据不会发生任何变化。      -排它锁（写锁）：任何时候只能有一个用户写入资源，当进行写锁时会阻塞其他的读锁或写锁操作。* 锁颗粒      -表锁：是一种开销最小的锁策略      -行锁：是一种开销最大的锁策略

特点 MyISAM InnoDB Memory Archive 存储限制 256TB 64TB 有 无 事务安全 - 支持 - - 支持索引 支持 支持 支持 - 锁颗粒 表锁 行锁 表锁 行锁 数据压缩 支持 - - 支持 支持外键 - 支持 - -

---

修改存储引擎的方法

* 通过修改MySQL配置文件实现      -default-storage-engine = engine* 通过创建数据表命令实现      -CREATE TABLE tb_name(       ...       ) ENGINE = engine ;* 通过修改数据表命令实现      -ALTER TABLE tb_name ENGINE [=] engine_name ;