MySQL基础操作（未完）

1、数据库及数据表的信息
SHOW DATABASES;显示所有数据库
SELECT DATABASE();显示当前数据库
SHOW ENGINES;显示存储引擎信息
SHOW CREATE DATABASE db_name;显示数据库信息
SHOW WARNINGS;显示警告信息

SHOW TABLES;显示当前数据库所有数据表
SHOW TABLES FROM db_name;显示指定数据库的所有数据表
SHOW CREATE TABLE tb_name;显示数据表信息
SHOW COLUMNS FROM tb_name;显示数据表的结构
SHOW INDEXES FROM tb_name;显示数据表的索引信息
SELECT * FROM tb_name;显示数据表的记录
2、数据库与数据表的创建和删除
CREATE DATABASE db_name;创建数据库
CREATE DATABASE db_name CHARACTER SET charset_name;创建数据库时指定编码方式
DROP DATABASE db_name;删除数据库
ALTER DATABASE db_name CHARACTER SET charset_name修改数据库的编码方式
USE db_name;打开数据库/切换数据库，打开数据库之前需要确保有数据库，与mongodb不同

CREATE TABLE tb_name(column_name data_type,);创建数据表
DROP TABLE tb_name;删除数据表
3、数据表定义的修改
●修改名字
ALTER TABLE tb_name RENAME new_name;修改数据表的名字

●修改列
ALTER TABLE tb_name ADD column_name column_definition;向数据表中添加单列，默认将字段值插入到末尾
ALTER TABLE tb_name ADD column_name column_definition FIRST|AFTER column_name;向数据表中添加单列，并指定插入位置
ALTER TABLE tb_name DROP column_name;删除单列
ALTER TABLE tb_name ADD (column_name column_definition,……….);向数据表中添加多列，无法指定插入位置
ALTER TABLE tb_name DROP column_name1,DROP column_name2,……….;删除多列
ALTER TABLE tb_name MODIFY column_name column_definition [FIRST|AFTER column_name]修改列的定义或位置
ALTER TABLE tb_name CHANGE old_name new_name column_definition 修改列的名称

●修改索引和约束
ALTER TABLE tb_name ADD INDEX (column_name1,column_name2,…..);添加普通索引
ALTER TABLE tb_name ADD PRIMARY KEY (column_name);添加主键约束
ALTER TABLE tb_name ADD UNIQUE KEY [index_name] (column_name);添加唯一约束
ALTER TABLE tb_name ADD FOREIGN KEY (column_name) REFERENCES tb_name (column_name);添加外键约束
ALTER TABLE tb_name ALTER column_name SET DEFAULT value;添加默认约束
ALTER TABLE tb_name DROP PRIMARY KEY;删除主键约束
ALTER TABLE tb_name DROP KEY index_name;删除唯一约束
ALTER TABLE tb_name DROP FOREIGN KEY foreignkey_symbol;删除外键约束
ALTER TABLE tb_name ALTER column_name DROP DEFAULT;删除默认约束
3、单表的增删改查
●插入记录
INSERT tb_name VALUES(column1_value,column2_value,…..)向数据表中插入一条记录且包含所有字段值
INSERT tb_name (column1_name,column2_name,…….) VALUES(column1_value,column2_value,….)向数据表中插入记录且只包含部分字段值
INSERT tb_name VALUES(column1_value,column2_value,…..)，(column1_value,column2_value,…..)……..向数据表中插入多条记录，且包含所有字段值。补充：对于AUTO_INCREMENT类型字段赋值可以使用DEFAULT或NULL
INSERT tb_name SET column_name1=expr|DEFAULT,column_name2=expr|DEFAULT,………; 此法一次只能插入一条记录，但可以使用子查询
INSERT tb_name (column1_name,column2_name,…….) SELECT ….;将查询结果插入到指定数据表
eg

CREAT users(id SMALLINT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(20) NOT NULL ,passwd VARCHAR(10) NOT NULL,age TINYINT UNSIGNED NOT NULL DEFAULT 10);INSERT users(NULL,'Tom','123',12);

●删除记录
DELETE FROM tb_name [WHERE where_condition];单表记录删除

●修改记录
UPDATE tb_name SET column_name1=expr, column_name2=expr,……. [WHERE where_condition];单表记录更新

●查找记录
SELECT * FROM tb_name;查询数据表中所有记录的所有字段
SELECT select_expr,select_expr,……. FROM tb_name;查询数据表中所有记录的部分字段
SELECT select_expr,select_expr,……. FROM tb_name WHERE where_condition;查询数据表中满足特定条件的记录
SELECT select_expr,select_expr,……. FROM tb_name GROUP BY column_name ASC|DESC 对数据表中记录进行分组，并以升序或降序的形式显示
SELECT select_expr,select_expr,……. FROM tb_name GROUP BY column_name ASC|DESC HAVING where_condition;对数据表中记录进行分组，并对分组数据进行过滤（注意：HAVING中的条件字段必须包含在SELECT后的字段中或者HAVING后使用聚合函数）
SELECT select_expr,select_expr,……. FROM tb_name WHERE where_condition GROUP BY column_name ASC|DESC HAVING where_condition;使用where_condition对数据表记录进行过滤，然后对其分组，最后使用HAVING对分组结果进行过滤
SELECT select_expr,select_expr,…….FROM tb_name ORDER BY column_name|expr ASC|DESC;对查询结果进行排序
SELECT select_expr,select_expr,…….FROM tb_name LIMIT row_count;对显示记录数限制为row_count个
SELECT select_expr,select_expr,…….FROM tb_name LIMIT offset row_count;显示包含记录号offset在内的后续总共row_count条记录。注：记录号从0开始
SELECT select_expr,select_expr,……. FROM tb_name WHERE where_condition GROUP BY column_name ASC|DESC HAVING where_condition ORDER BY column_name|expr LIMIT row_count;对查询结果进行详尽过滤
eg

SELECT sex,age FROM users GROUP BY sex HAVING age>10;

4、多表的查找、更新和删除
通过将多张数据表连接可以进行多表的查找、更新和删除，连接主要分为内连接、左外连接、右外连接

内连接
根据ON关键字设定的连接条件，从两张数据表中选择字段值相同的行。
eg
SELECT grade1.name grade2.name FROM grade1 INNER JOIN grade2 ON grade1.score=grade2.score
选择两张数据表中得分相同的学生的名字

左外连接
选择左表全部行，并根据ON关键字设定连接字段连接右表对应的行
eg
SELECT children.name parent.name FROM children LEFT JOIN parent ON children.pid=parent.id;
从parent表中找出children表中所有孩子的父亲的名字

右外连接
选择右表全部行，并根据ON关键字设定连接字段连接左表对应的行
eg
SELECT parent.name children.name FROM children RIGHT JOIN parent ON parent.id=children.pid;
从children表中找出parent表中所有父亲的孩子的名字

4、创建索引
创建索引

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name    [index_type]    ON tbl_name (index_col_name,...)    [index_option]     [algorithm_option | lock_option] ...index_col_name:    col_name [(length)] [ASC | DESC]index_type:    USING {BTREE | HASH}index_option:    KEY_BLOCK_SIZE [=] value  | index_type  | WITH PARSER parser_name  | COMMENT 'string'algorithm_option:    ALGORITHM [=] {DEFAULT|INPLACE|COPY}lock_option:    LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

删除索引
DROP INDEX [indexName] ON mytable;

注意：
●不能使用CREATE INDEX语句创建主键索引
●CREATE INDEX为已存在的表增添索引
●可以为单列或多列创建索引
●对于字符串类型的列，如CHAR 、VARCHAR，可使用col_name(length)只为字段的前一部分创建索引
●对于使用InnoDB、MyISAM、MEMORY、NDB存储引擎创建的数据表，创建索引时，可以指定index_type，InnoDB可以指定BTREE索引，MyISAM可以指定BTREE索引，MEMORY可以指定HASH, BTREE索引，默认为HASH索引，NDB可以指定BTREE索引，MEMORY可以指定HASH, BTREE索引，默认为HASH索引
●创建FULLTEXT INDEX o或SPATIAL索引时，不能指定index_type
●大部分索引使用BTREE存储
eg
CREATE TABLE lookup (id INT) ENGINE = MEMORY;
CREATE INDEX id_index ON lookup (id) USING BTREE;

5、存储过程以及自定义函数
SELECT name FROM mysql.proc;显示所有存储过程名以及自定义函数名
SELECT name FROM mysql.proc WHERE db=’db_name’;显示特定数据库的存储过程名及自定义函数名
SHOW PROCEDURE STATUS;显示所有存储过程的详细信息
SHOW PROCEDURE STATUS WHERE db=’db_name’;显示特定数据库的存储过程的详细信息
CREATE PROCEDURE pro_name([IN|OUT|INOUT parameter_name1 parameter_type,[…]]) [characteristic]
BEGIN
SQL语句
END //
(在创建存储过程之前需要通过DELIMITER //修改默认的语句结束符。存储过程可以返回多个值，即OUT型参数。如果过程体含有多条sql语句，需要使用BEGIN……END)
IN表示输入型参数，调用存储过程前必须声明，对参数的修改不会影响存储过程外的变量，不能被返回
OUT表示输出型参数，调用存储过程前不必声明，调用时变量名首字母应为@
INOUT表示输入输出型参数，调用前声明变量，可以被返回
CALL pro_name([parameter1，parameter2,…..]);调用存储过程(注意参数名以@开头)
DROP PROCEDURE pro_name;删除存储过程
eg

delimiter //CREATE PROCEDURE simpleproc (OUT param1 INT)BEGINSELECT COUNT(*) INTO param1 FROM t;END//delimiter ;CALL simpleproc(@a);  (注意：首先要创建t表)SELECT @a;

SHOW FUNCTION STATUS;显示所有自定义函数的详细信息
SHOW FUNCTION STATUS WHERE db=’db_name’;显示特定数据库的自定义函数的详细信息
SHOW FUNCTION STATUS LIKE function_name;显示特定自定义函数的信息
创建函数
CREATE FUNCTION function_name(parameter_name1 type1,parameter_name2 type2,….)
RETURNS return_type
BEGIN
SQL语句
RETURN value|expr; (注意：此处以及sql语句结尾都有分号）
END //
(在创建自定义函数之前需要通过DELIMITER //修改默认的语句结束符。自定义函数只有一个返回值）
eg：

CREATE FUNCTION test(date1  DATETIME)RETURNS VARCHAR(50)BEGINDECLARE x  VARCHAR(50)  DEFAULT '';SET x =DATE_FORMAT(date1,'%Y年%m月%d日%h时%i分%s秒');RETURN x；END //

SELECT function_name([parameter1，parameter2,…..]);调用自定义函数
DECLARE variable_name datatype [DEFAULT value]; 在BEGIN…..END之间可以声明变量,且必须位于BEGIN…..END之间最前边
SET varaiable_name=value|expr;为变量名赋值

存储过程与函数的区别
●存储过程可以返回多个值，函数只能返回一个值
●存储过程不需要声明返回值类型，函数需要通过RETURNS 声明返回类型
●存储过程不能嵌套在SELECT语句中使用，函数可以
●存储过程只在第一次调用时进行编译，以后调用时不需要再编译，运行速度比函数快，并且可以减少网络流量
6、流程控制
●CASE运算符
CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result …] [ELSE result] END
当compare_value与value相等时，返回相应的result，如果没有相等的compare_value，则返回else后的result，如果没有else，则返回null

CASE WHEN [condition] THEN result [WHEN [condition] THEN result …] [ELSE result] END
返回第一个condition为true的result，如果都不为true，则返回else 后的result，如果没有else，则返回null
eg
SELECT CASE 1 WHEN 1 THEN ‘one’ WHEN 2 THEN ‘two’ ELSE ‘more’ END;
SELECT CASE WHEN 1>0 THEN ‘true’ ELSE ‘false’ END;
●IF函数
IF(expr1,expr2,expr3) 如果expr1为真，则返回expr2，否则返回expr3
eg
SELECT IF(1>2,2,3);
SELECT IF(1<2,’yes’,’no’);
●IF语句

IF search_condition THEN statement_list    [ELSEIF search_condition THEN statement_list] ...    [ELSE statement_list]END IF

eg

DELIMITER //CREATE FUNCTION SimpleCompare(n INT, m INT)RETURNS VARCHAR(20)BEGIN    DECLARE s VARCHAR(20);    IF n > m THEN SET s = '>';    ELSEIF n = m THEN SET s = '=';    ELSE SET s = '<';    END IF;    SET s = CONCAT(n, ' ', s, ' ', m);    RETURN s;END //DELIMITER ;

DELIMITER //CREATE FUNCTION VerboseCompare (n INT, m INT)RETURNS VARCHAR(50)BEGIN    DECLARE s VARCHAR(50);    IF n = m THEN SET s = 'equals';    ELSE      IF n > m THEN SET s = 'greater';      ELSE SET s = 'less';      END IF;      SET s = CONCAT('is ', s, ' than');    END IF;    SET s = CONCAT(n, ' ', s, ' ', m, '.');    RETURN s;END //DELIMITER ;

●IFNULL函数
IFNULL(expr1,expr2) 如果expr1非空，则返回expr1，否则返回expr2
SELECT IFNULL(1,0);返回1
SELECT IFNULL(NULL,10);返回10
●NULLIF函数
NULLIF(expr1,expr2) 如果expr1等于expr2则返回NULL，否则返回expr1
SELECT NULLIF(1,1);返回NULL
●WHILE语句
WHILE search_condition DO
statement_list
END WHILE [end_label]
eg

CREATE PROCEDURE dowhile()BEGIN  DECLARE v1 INT DEFAULT 5;  WHILE v1 > 0 DO    ...    SET v1 = v1 - 1;  END WHILE;END;

7、常用函数和运算符
字符函数
CONCAT( )字符连接
UPPER( )将小写字母转换成大写字母
LOWER（）将大写字母转换成小写字母
LENGTH（）获取字符串长度
LIKE 模式匹配
eg
SELECT CONCAT(“ARE”,”YOU”,”OK”);
SELECT UPPER(“ghG”);
SELECT LOWER(GJJ);
SELECT LENGTH(“HEHE”);
SELECT ‘Mysql’ LIKE ‘M%’ %代表任意零个或多个字符，返回true
SELECT * FROM test WHERE firstname LIKE ‘%o%’ 查询名字中含有字母o的记录

比较运算符和函数
[NOT] BETWEEN……AND [不]在范围之内
[NOT] IN() [不]在列出范围之内
IS [NOT] NULL [不]为空
eg
SELECT 1 BETWEEN 0 AND 10;返回true
SELECT 1 IN (1，2，3); 返回true
SELECT ’ ’ IS NULL;返回false
SELECT * WHERE firstname IS NULL;

日期时间函数
NOW() 当前日期和时间
CURDATE() 当前日期
CURTIME()当前时间
eg
SELECT NOW();

信息函数
CONNECTION_ID()当前链接id
DATABASE()当前数据库
LAST_INSERT_ID()最后插入的记录的id号，前提是表中必须有id字段
USER()当前用户

聚合函数
AVG()求平均值
COUNT()计数
MAX()求最大值
MIN()求最小值
eg
SELECT COUNT(*) FROM grade WHERE score>60;
8、子查询
SELECT column_name1,column_name2,….. FROM tb_name WHERE column_name operator ANY|ALL(subquery);operator代表常用比较运算符，ANY代表子查询结果中任意一个，ALL代表子查询结果所有记录。
SELECT column_name1,column_name2,….. FROM tb_name WHERE column_name [NOT] IN (subquery);从子查询结果中进行选择
SELECT column_name1,column_name2 ,……..FROM (subquery)；

operator ANY SOME ALL \ >=、 > 最小值 最小值 最大值 <、 <= 最大值 最大值 最小值 = 任意值 任意值 <、 >、 != 任意值

参考
MySql之自定义函数
13.4 Control Flow Functions
14.1.14 CREATE INDEX Syntax