本文主要介绍 SQL 语言以及部分 MySQL 的基础知识。

MySQL

MySQL 的默认的端口号是 3306 超级用户是root

MySql 引擎

MySQL 提示符命令与语法规范

MySQL 的所有的指令都是以结束符结束的, 回车提交命令. 默认的提示符是;号.

修改提示符

1 2	prompt localhost; 将提示符修改为localhost

之后每一行的提示符由mysql>变为localhost>，还可以使用通配符修改提示符。

常用指令

通过SELECT执行内置函数

SELECT VERSION(); //显示当前服务器版本
+------------+
| VERSION()  |
+------------+
| 5.5.19-log |
+------------+

SELECT NOW(); //显示当前日期时间
+---------------------+
| NOW()               |
+---------------------+
| 2016-04-12 08:51:44 |
+---------------------+

SELECT USER(); //显示当前用户
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+

语法规范：

关键字和函数名称必须全部大写
数据库名称表名称字段名称全部小写
SQL 语句必须以分号结尾

命名规则：

可读性原则：驼峰提高可读性
表意性原则：对象的名字能反应对象的意义
长名原则：少用缩写

操作数据库

创建数据库

1
2
3

CREATE {DATABASE|SCHEMA}[IF NOT EXISTS] db_name
[DEFAULT] CHARACTER SET [=] charset_name
--- MySQL中SCHEMA与DATABASE名称相同

查看当前 MySql 服务器下的数据库

1 2	SHOW {DATABASES\|SCHEMAS} [LIKE 'pattern'\|WHERE EXPR]

修改数据库

1 2	ALTER {DATABASE\|SCHEMA}[db_name] [DEFAULT]CHARACTER SET [=] charset_name

删除数据库

1	DROP {DATABASE\|SCHEMA}[IF NOT EXISTS] db_name

数据类型

MySQL 中的数据类型主要有整形浮点型字符型和日期时间型.
字段的类型影响着存储容量和数据查询性能。当可以选择多种类型时，优先选择数字类型，
其次是日期和二进制类型，最后是字符类型。相同级别的类型优先选择省空间的类型。

对于上述原则主要从下面两个方面考虑：

在对数据进行比较时(查询条件、JOIN 条件及排序)操作时，
同样的数据，字符处理往往比数字处理慢
在数据库中，数据处理以页为单位，列的长度越小，利于性能提升

char 与 varchar 选择:

如果列中要存储的数据长度差不多是一致的，则应该考虑用 char；否则应该用 varchar。
如果列中的最大数据长度小于 50Byte，则一般也考虑用 char.(当然，如果这个列很少用，
则基于节省空间和较少 I/O 的考虑，还是可以选择用 varchar)
一般不宜定义大于 50Byte 的 char 类型列。

整形

浮点型

日期时间型

字符型

操作数据表

打开数据库

1	USE db_name; //打开数据库

创建数据表

CREATE TABLE [IF NOT EXISTS] table_name(
  column_name data_type,
  ....
)

查看创建当前数据库的语句:

CREATE TABLE provinces(
    id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    pname VARCHAR(20) NOT NULL
  );
显示创建数据库的语句
SHOW CREATE TABLE provinces;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| provinces | CREATE TABLE `provinces` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `pname` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

以表格的形式显示列和列的属性

show columns from provinces;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type                 | Null | Key | Default | Extra          |
+-------+----------------------+------+-----+---------+----------------+
| id    | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| pname | varchar(20)          | NO   |     | NULL    |                |
+-------+----------------------+------+-----+---------+----------------+
2 rows in set (0.03 sec)
desc provinces;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type                 | Null | Key | Default | Extra          |
+-------+----------------------+------+-----+---------+----------------+
| id    | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| pname | varchar(20)          | NO   |     | NULL    |                |
+-------+----------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

查看数据库中的数据表列表

1 2	SHOW TABLES [FROM db_name] [LIKE 'pattern'\|WHERE expr]

插入记录

1	INSERT [INTO] table_name [(column_name,...)] VALUES(var,...)

记录查找

1	SELECT expr,... FROM table_name

select 子句中可以包含子查询

约束

约束保证数据的完整性和一致性
约束分为表级约束和列级约束
约束类型包括:
- NOT NULL 非空元素
- PRIMARY KEY 主键约束
- UNIQUE KEY 唯一约束
- DEFAULT 默认约束
- FOREIGN KEY 外键约束.

自增长 AUTO_INCREMENT

自动编号，且该字段必须是主键：
AUTO_INCREMENT 必须是主键，主键不一定 AUTO_INCREMENT
默认情况下，其实值是 1，每次增量是 1

插入数据时可以使用DEFAULT或者NULL为自增长字段赋值，以生成自增长数据。

例如在上面的provinces表中, 插入数据

insert into provinces values(null,'');
Query OK, 1 row affected (0.08 sec)
insert into provinces values(DEFAULT,'');
Query OK, 1 row affected (0.08 sec)
 select * from provinces;
+----+-------+
| id | pname |
+----+-------+
|  1 |       |
|  2 |       |
+----+-------+
2 rows in set (0.00 sec)

主键约束 PRIMARY KEY

主键约束
每张数据表只能存在一个主键
主键保证记录的唯一性
主键自动为NOT NULL

唯一约束

保证记录的唯一性
字段可以为空值(NULL) 且可以多个为空
每张表可以有多个唯一约束

默认值约束 DEFAULT

当插入记录时，如果没有明确为字段赋值，则自动赋值为默认值。

CREATE TABLE tb6(
    id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(20) NOT NULL UNIQUE KEY,
    sex ENUM('1','2','3') DEFAULT '3'
);

查看表格结构

SHOW COLUMNS FROM tb6;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL    |                |
| sex      | enum('1','2','3')    | YES  |     | 3       |                |
+----------+----------------------+------+-----+---------+----------------+

使用默认值插入记录

//使用DEFAULT来生成AUTO_INCREMENT
INSERT INTO tb6 VALUES(Default,"李四",DEFAULT);
Query OK, 1 row affected (0.08 sec)

Select *  from tb6;
+----+----------+------+
| id | username | sex  |
+----+----------+------+
|  1 | 李四   | 3    |
+----+----------+------+
1 row in set (0.00 sec)

指定字段为枚举类型: 与 Java 中的枚举类型不同, sql 中的枚举只是限制取值范围.

外键约束

要求

父表和字表必须使用相同的存储引擎, 而且禁止使用临时表
数据表的引擎必须只能是 InnoDB
外键列和参照列必须具有相似的数据类型, 其中数字的长度或是否有符号位必须相同;
而字符的长度则可以不同.
外键列和参照列必须创建索引.如果外键列不存在索引, MySQL 将自动创建索引.

实例

CREATE TABLE provinces(
    id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    pname VARCHAR(20) NOT NULL
  );

CREATE TABLE users(
   id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
   username VARCHAR(10) NOT NULL,
   pid SMALLINT UNSIGNED,
   FOREIGN KEY(pid) REFERENCES provinces(id) );
Query OK, 0 rows affected (0.08 sec)

SHOW INDEXES FROM provinces\G
*************************** 1. row ***************************
       Table: provinces
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: id
   Collation: A
 Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
Index_comment:
1 row in set (0.00 sec)

外键约束的操作操作

CASCADE: 从父表删除或更新且自动删除或更新子表中匹配的行
SET NULL: 从父表删除或更新行, 并设置子表中的外键列为 NULL. 如果使用该选项, 必须保证子表列没有指定 NOT NULL
RESTRICT: 拒绝对父表的删除或更新操作
NO ACTION: 标准 SQL 关键字, 在 MySQL 中与 RESTRICT 相同

表级约束与列级约束 ??

对于一个数据列建立的约束, 称为列级约束.
对多个数据列建立的约束, 称为表级约束.
列级约束既可以在列定义时声明,也可以在列定义后声明.
表级约束只能在列级约束后声明.

修改数据表

ALTER TABLE table_name ADD [COLUMN] column_name
  column_definition [FIRST|AFTER column_name]
添加多列
ALTER TABLE table_name ADD [COLUMN]
  (column_name column_definition, ....)
删除列
ALTER TABLE table_name DROP [COLUMN] column_name
添加主键约束
ALTER TABLE table_name ADD [CONSTRAINT [symbol]]
  PRIMARY KEY [Index_type](index_column_name,...)
添加唯一约束
ALTER TABLE table_name ADD [CONSTRAINT [symbol]]
  UNIQUE [INDEX|KEY][index_name][index_type]
  (index_column_name,...)
添加/删除默认约束
ALTER TABLE table_name ALTER [COLUMN] column_name
{SET DEFAULT litera}
如:
  ALTER TABLE users2 ALTER age DROP DEFAULT;
删除主键约束
ALTER TABLE table_name DROP PRIMARY Key
删除唯一约束
ALTER TABLE table_name DROP {INDEX|KEY} index_name
删除外键约束
ALTER TABLE table_name DROP FOREIGN KEY fk_symbol
修改列定义
ALTER TABLE table_name MODIFY [COLUMN] column_name
  column_definition [FIRST|AFTER column_name]
修改列名称
ALTER TABLE table_name CHANGE [COLUMN] old_column_name
  new_column_name column_definition [FIRST|AFTER column_name]
数据表更名
ALTER TABLE table_name RENAME [TO|AS] new_table_name
或
RENAME TABLE table_name TO new_table_name
  [, table_name2 TO new_table_name2] ...

操作数据表中的记录

插入记录

INSERT [INTO] table_name [(column_name,....)] {VALUES|VALUE}
({expr|DEFAULT},...),(...),...

INSERT [INTO] table_name SET column_name={expr|DEFAULT},...
此方法可以使用子查询

INSERT [INTO] table_name [(column_name,...)] SELECT ...

单表更新

单表更新，可以根据字段的当前值，进行判断。

-- 将user_info表中，user_id>1000的信息状态置反。
UPDATE user_info
SET `status`= (CASE WHEN 'status'=1 THEN 0 WHEN `status`=0 THEN 1 END)
WHERE `user_id` > 1000 ;
-- 将user_info表中的real_name去掉空格
UPDATE user_info
SET real_name=TRIM(real_name);

单表删除

查询表达式解析

where 语句进行条件查询

group by 语句对查询结果分组

having 语句设置分组条件

order by 语句对查询结果排序

limit 语句限制查询数量

--语法：
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
--举例：
select * from table limit 5; --返回前5行
select * from table limit 0,5; --同上，返回前5行
---为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：
mysql> SELECT * FROM table LIMIT 95,-1; --- 检索记录行 96-last.
select * from table limit 5,10; --返回6-15行

当一个查询语句偏移量 offset 很大的时候，如select * from table limit 10000,10 ,
最好不要直接使用 limit，而是先获取到 offset 的 id 后，再直接使用 limit size 来获取数据。
效果会好很多。** 想办法减少 offset **

select * From customers Where customer_id >=(
  --- 查找第1000条记录对应的id, id是索引, 检索速度快
  select customer_id From customers Order By customer_id limit 10000,1
) limit 10;

** 通过将查询条件(即 where 子句)的字段加索引,可以大大提高查询速度. **

子查询与连接

内连接 join 从句

SQL 标准中有 5 种连接：

内连接 inner
全外连接 full outer
左外连接 left outer
右外连接 right outer
交叉连接 cross

内连接 inner join

取两个表的交集，即公共部分

SELECT a.`column1`, a.`column2`, b.`column3`
FROM `table_name1` AS a
INNER JOIN `table_name2` AS b
ON a.`column4`=b.`column5`;

左外连接 left outer join

右图中的部分，可以用于替换NOT IN

SELECT a.`column1`, a.`column2`, b.`column3`
FROM `table_name1` AS a
LEFT JOIN `table_name2` AS b
ON a.`column4`=b.`column5`
WHERE b.`column3` IS NOT NULL;

右外连接 right outer join

右图中的部分，可以用于替换NOT IN

SELECT a.`column1`, a.`column2`, b.`column3`
FROM `table_name1` AS a
RIGHT JOIN `table_name2` AS b
ON a.`column4`=b.`column5`
WHERE a.`column3` IS NOT NULL;

全外连接 FULL OUTER JOIN

MySQL 中默认是不包含全外连接查询的，可以通过其他方式实现同样的效果。

SELECT a.`column1`, a.`column2`, b.`column3`
FROM `table_name1` AS a
LEFT JOIN `table_name2` AS b
ON a.`column4`=b.`column5`
UNION ALL
SELECT a.`column1`, a.`column2`, b.`column3`
FROM `table_name1` AS a
RIGHT JOIN `table_name2` AS b
ON a.`column4`=b.`column5`

交叉连接 CROSS JOIN

笛卡尔积
A 表中的每一条记录和 B 表中的每一条记录组合: 结果的长度为 A 的长度 ×B 的长度.

1
2
3

SELECT a.`column1`, a.`column2`, b.`column3`
FROM `table_name1` AS a
CROSS JOIN `table_name2` AS b

函数

函数主要分为字符函数数值函数日期时间函数加密函数

字符函数

对特殊字符的 Escape

1 2	SELECT * FROM test WHERE first_name LIKE '%1%%' ESCAPE '1' 表示1后面的%是真实的字符而不是转意字符

substring

SUBSTR (str, pos)

由中，选出所有从第位置开始的字元。请注意，这个语法不适用于 SQL Server 上。

SUBSTR (str, pos, len)

由中的第位置开始，选出接下去的个字元。

数值函数

比较运算符和函数

日期时间函数

详细请看

SELECT DATE_ADD('2016-04-12', INTERVAL 1 YEAR);
+-----------------------------------------+
| DATE_ADD('2016-04-12', INTERVAL 1 YEAR) |
+-----------------------------------------+
| 2017-04-12                              |
+-----------------------------------------+

SELECT DATE_ADD('2016-04-12', INTERVAL 3 WEEK);
+-----------------------------------------+
| DATE_ADD('2016-04-12', INTERVAL 3 WEEK) |
+-----------------------------------------+
| 2016-05-03                              |
+-----------------------------------------+

SELECT DATE_FORMAT('2016-3-2','%m/%d/%Y');
+------------------------------------+
| DATE_FORMAT('2016-3-2','%m/%d/%Y') |
+------------------------------------+
| 03/02/2016                         |
+------------------------------------+

时间与时间戳的转换

unix_timestamp

将时间转化为时间戳。（date 类型数据转换成 timestamp 形式整数）

没传时间参数则取当前时间的时间戳

MySQL> select unix_timestamp();

+------------------+
| unix_timestamp() |
+------------------+
|       1361586358 |
+------------------+
1 row in set (0.01 sec)
mysql> select unix_timestamp('2013-01-01 10:10:10');
+---------------------------------------+
| unix_timestamp('2013-01-01 10:10:10') |
+---------------------------------------+
|                            1357006210 |
+---------------------------------------+
1 row in set (0.00 sec)

from_unixtime

将 timestamp 形式整数转化为 date 类型

mysql>  select from_unixtime(1355272360);
+---------------------------+
| from_unixtime(1355272360) |
+---------------------------+
| 2012-12-12 08:32:40       |
+---------------------------+
1 row in set (0.00 sec)

当然也可以指定输出的时间格式：

mysql>  select from_unixtime(1355272360,'%Y%m%d');
+------------------------------------+
| from_unixtime(1355272360,'%Y%m%d') |
+------------------------------------+
| 20121212                           |
+------------------------------------+

关于 mysql 时间戳的限制

目前 timestamp 所能表示的范围在 1970 年 - 2038 年之间。

超过这个范围得到的时间将会溢出得到的时间是 null.

mysql>  select from_unixtime(0);
+---------------------+
| from_unixtime(0)    |
+---------------------+
| 1970-01-01 08:00:00 |
+---------------------+
mysql> select from_unixtime(2147483647);
+---------------------------+
| from_unixtime(2147483647) |
+---------------------------+
| 2038-01-19 11:14:07       |
+---------------------------+
1 row in set (0.00 sec)

SQL 中的 timestamp 与 Java 中的转换

SQL 中的时间戳是从 1970 年 1 月 1 日 8:00:00 开始的毫秒数

SQL 中的时间戳	1970-1-1 8:00:00 开始的毫秒数
java.util.Date()	内置时间 getYear()是从 1900 年开始的
java.util.Date().getTime()	从 1970-1-1 8:00:00 开始的微妙数
java.sql.Timestamp()	与 java.util.Date()相同
java.sql.Date	只保存年月日，没有时分秒

Timestamp是一个与 java.util.Date 类有关的瘦包装器 (thin wrapper)，它允许 JDBC API 将该类标识为 SQL TIMESTAMP 值。它添加保存 SQL TIMESTAMP 毫微秒值和提供支持时间戳值的 JDBC 转义语法的格式化和解析操作的能力。

因此，在 SQL 中的时间戳与 Java 中传递进来的 long 型时间比较时，需要乘以 1000.

信息函数

聚合函数

加密函数

password 函数只适用于设置 MySQL 的密码

XML 支持

xml 支持 :xml 的函数主要有两个 ExtractValue 和 UpdateXML

ExtractValue

mysql> SELECT
    ->   ExtractValue('<a>ccc<b>ddd</b></a>', '/a') AS val1,
    ->   ExtractValue('<a>ccc<b>ddd</b></a>', '/a/b') AS val2,
    ->   ExtractValue('<a>ccc<b>ddd</b></a>', '//b') AS val3,
    ->   ExtractValue('<a>ccc<b>ddd</b></a>', '/b') AS val4,
    ->   ExtractValue('<a>ccc<b>ddd</b><b>eee</b></a>', '//b') AS val5;

+------+------+------+------+---------+
| val1 | val2 | val3 | val4 | val5    |
+------+------+------+------+---------+
| ccc  | ddd  | ddd  |      | ddd eee |
+------+------+------+------+---------+

UpdateXML

mysql> SELECT
    ->   UpdateXML('<a><b>ccc</b><d></d></a>', '/a', '<e>fff</e>') AS val1,
    ->   UpdateXML('<a><b>ccc</b><d></d></a>', '/b', '<e>fff</e>') AS val2,
    ->   UpdateXML('<a><b>ccc</b><d></d></a>', '//b', '<e>fff</e>') AS val3,
    ->   UpdateXML('<a><b>ccc</b><d></d></a>', '/a/d', '<e>fff</e>') AS val4,
    ->   UpdateXML('<a><d></d><b>ccc</b><d></d></a>', '/a/d', '<e>fff</e>') AS val5
    -> \G

*************************** 1. row ***************************
val1: <e>fff</e>
val2: <a><b>ccc</b><d></d></a>
val3: <a><e>fff</e><d></d></a>
val4: <a><b>ccc</b><e>fff</e></a>
val5: <a><d></d><b>ccc</b><d></d></a>

自定义函数

自定义函数(user-defined function,UDF)是一种对 MySQL 扩展的途径,
其用法与内置函数相同. 自定义函数的两个必要条件:
参数和返回值. 函数可以返回任意类型的值, 同样也可以接受这些类型的参数

1
2
3

CREATE FUNCTION function_name
RETURNS {STRING|INTEGER|REAL|DECIMAL}
routine_body

函数体

由合法 SQL 语句构成
可以是简单的 SELECT 或 INSERT 语句
如果是复合结构,必须使用BEGIN…END语句
复合结构可以包括声明,循环,控制结构

CREATE FUNCTION f1()
RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点%i分%s秒');

select f1();
+--------------------------------+
| f1()                           |
+--------------------------------+
| 2016年04月12日 10点51分24 |
+--------------------------------+
CREATE FUNCTION f2(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED)
RETURNS FLOAT(10,2) UNSIGNED
RETURN (num1+num2)/2;

SELECT f2(35,46);
+-----------+
| f2(35,46) |
+-----------+
|     40.50 |
+-----------+

DELIMITER //
CREATE FUNCTION adduser(username VARCHAR(20))
RETURNS INT UNSIGNED
BEGIN
INSERT test(username) VALUES(username);
RETURN LAST_INSERT_ID();
END
//
DELIMITER ;

存储过程

SQL 命令的执行过程

存储过程是 SQL 语句和控制语句的预编译集合, 以一个名称存储并作为一个单元处理. 其优点有:

增强 SQL 语句的功能和灵活性
实现较快的执行速度
减少网络流量

语法格式为:

CREATE
[DEFINER={USER|CURRENT_USER}]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

proc_parameter:
[IN|OUT|INOUT]parameter_name type

调用
CALL sp_name([parameter[,...]])
CALL sp_name[()]

参数

IN 表示该参数的值必须在调用存储过程时指定
OUT 表示该参数的值可以被存储过程改变, 并且可以返回
INOUT 表示该参数的调用时指定,并且可以被改变和返回

过程体

过程体由合法的 SQL 语句构成
过程体可以是任意 SQL 语句
过程体如果为复合结构则使用 BEGIN…END 语句
复合结构可以包含声明,循环,控制结果

实例

mysql> delimiter //
mysql> CREATE PROCEDURE removeUserById(IN ppid INT UNSIGNED)
    -> BEGIN
    -> DELETE FROM users WHERE id=ppid;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

delimiter //
create procedure removeUserAndReturnUserNums(in pid int unsigned,out useNums int unsigned)
	begin
	delete from user where id=pid;
	select count(id) from user into useNums;
	end
	//
delimiter ;

调用
call removeUserAndReturnUserNums(27,@nums);
select @nums;
通过declare语句声明的变量是局部变量, 作用域只能在begin和end之间.
select @nums;
set @num=7;
这种声明方式声明的变量成为用户变量. 作用域是客户端,只在当前的客户端有效.

获取插入删除更新的记录总数.
select row_count();


delimiter //
create procedure removeUserAndReturnInfos(in p_age smallint unsigned,
    out deleteUsers smallint unsigned, out userCounts smallint unsigned)
begin
delete from user where age=p_age;
select row_count() into deleteUsers;
select count(id) from user into userCounts;
end
//
delimiter ;

call removeUserAndReturnInfos(20,@rm,@rl);
select @rm;
select @rl;

删除存储过程

1	DROP PROCEDURE [IF EXISTS] sp_name

存储过程与自定义函数的区别

存储过程实现的功能更复杂一些而函数的针对性更强
存储过程可以返回多个值;函数只能有一个返回值
存储过程一般独立的执行;而函数可以作为其他 SQL 语句的组成部分来出现.

创建存储过程或者自定义函数时需要通过delimiter语句修改定界符.

用户权限

在 MySQL5.7 中 user 表的 password 已换成了 authentication_string。
注意：在注意需要执行 FLUSH PRIVILEGES 语句。这个命令执行后会重新载入授权表。
如果你不使用该命令，你就无法使用新创建的用户来连接 mysql 服务器，除非你重启 mysql 服务器。
你可以在创建用户时，为用户指定权限，在对应的权限列中，在插入语句中设置为 ‘Y’ 即可，用户权限列表如下：

Select_priv
Insert_priv
Update_priv
Delete_priv
Create_priv
Drop_priv
Reload_priv
Shutdown_priv
Process_priv
File_priv
Grant_priv
References_priv
Index_priv
Alter_priv

除非你使用 LIKE 来比较字符串，否则 MySQL 的 WHERE 子句的字符串比较是不区分大小写的。你可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。
如下实例

root@host# mysql -u root -p password;
Enter password:*******
mysql> use RUNOOB;
Database changed
mysql> SELECT * from runoob_tbl \
          WHERE BINARY runoob_author='sanjay';
Empty set (0.02 sec)

为了处理这种情况，MySQL 提供了三大运算符:

IS NULL: 当列的值是 NULL,此运算符返回 true。
IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
<=>: 比较操作符（不同于=运算符），当比较的的两个值为 NULL 时返回 true。
关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值。
在 MySQL 中，NULL 值与任何其它值的比较（即使是 NULL）永远返回 false，即 NULL = NULL 返回 false 。
MySQL 中处理 NULL 使用 IS NULL 和 IS NOT NULL 运算符。

正则表达式

http://www.runoob.com/mysql/mysql-regexp.html

1	SELECT name FROM person_tbl WHERE name REGEXP '^st';

索引

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。
创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。

创建索引

有四种方式来添加数据表的索引：

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):

该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

创建临时表

如果你退出当前 MySQL 会话，再使用 SELECT 命令来读取原先创建的临时表数据，那你会发现数据库中没有该表的存在，因为在你退出时该临时表已经被销毁了。

CREATE TEMPORARY TABLE SalesSummary (
    -> product_name VARCHAR(50) NOT NULL
    -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
    -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
    -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);

复制表

1	SHOW CREATE TABLE runoob_tbl \G;

修改数据表名，执行 SQL 语句

INSERT INTO clone_tbl (runoob_id,
    ->                        runoob_title,
    ->                        runoob_author,
    ->                        submission_date)
    -> SELECT runoob_id,runoob_title,
    ->        runoob_author,submission_date
    -> FROM runoob_tbl;

元数据

查询结果信息数据库和数据表的信息服务器信息

SELECT VERSION( ) 服务器版本信息
SELECT DATABASE( )  当前数据库名 (或者返回空)
SELECT USER( )  当前用户名
SHOW STATUS 服务器状态
SHOW VARIABLES  服务器配置变量
show processList; 连接MySQL的所有线程的信息
SHOW  OPEN TABLES  WHERE In_use>0 所有打开的数据表

自增序列

在 MySQL 的客户端中你可以使用 SQL 中的 LAST_INSERT_ID( ) 函数来获取最后的插入表中的自增列的值 Java 如何获取

重置序列

mysql> ALTER TABLE insect DROP id;
mysql> ALTER TABLE insect
    -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
    -> ADD PRIMARY KEY (id);

设置序列的开始值

一般情况下序列的开始值为 1，但如果你需要指定一个开始值 100，那我们可以通过以下语句来实现：

mysql> CREATE TABLE insect
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, # type of insect
    -> date DATE NOT NULL, # date collected
    -> origin VARCHAR(30) NOT NULL # where collected
);

或者你也可以在表创建成功后，通过以下语句来实现：

1	mysql> ALTER TABLE t AUTO_INCREMENT = 100;

防止数据重复

设置字段为 primary key 或者 unique 索引

INSERT IGNORE

INTO 当插入数据时，在设置了记录的唯一性后，如果插入重复数据，将不返回错误，只以警告形式返回。而 REPLACE INTO into 如果存在 primary 或 unique 相同的记录，则先删除掉。再插入新记录。

添加一个 UNIQUE 索引，如下所示：

CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10)
   UNIQUE (last_name, first_name)
);

统计重复数据

1	SELECT COUNT(*) as repetitions, last_name, first_name FROM person_tbl GROUP BY last_name, first_name HAVING repetitions > 1;

过滤重复数据

1
2
3

mysql> SELECT DISTINCT last_name, first_name
    -> FROM person_tbl
    -> ORDER BY last_name;

你也可以使用 GROUP BY 来读取数据表中不重复的数据：

1
2
3

mysql> SELECT last_name, first_name
    -> FROM person_tbl
    -> GROUP BY (last_name, first_name);

删除重复数据

如果你想删除数据表中的重复数据，你可以使用以下的 SQL 语句：

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex
    ->                  FROM person_tbl;
    ->                  GROUP BY (last_name, first_name);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

当然你也可以在数据表中添加 INDEX（索引）和 PRIMAY KEY（主键）这种简单的方法来删除表中的重复记录。方法如下：

1 2	mysql> ALTER IGNORE TABLE person_tbl -> ADD PRIMARY KEY (last_name, first_name);

SQL 注入

防止 SQL 注入，我们需要注意以下几个要点：

永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和双”-“进行转换等。
永远不要使用动态拼装 sql，可以使用参数化的 sql 或者直接使用存储过程进行数据查询存取。
永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
不要把机密信息直接存放，加密或者 hash 掉密码和敏感的信息。
应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装
sql 注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用 sql 注入检测工具 jsky，网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN 等。采用 MDCSOFT-IPS 可以有效的防御 SQL 注入，XSS 攻击等。

SQL like 语句注入

like 查询时，如果用户输入的值有”“和”%”，则会出现这种情况：用户本来只是想查询”abcd“，查询结果中却有”abcd_“、”abcde”、”abcdf”等等；用户要查询”30%”（注：百分之三十）时也会出现问题。

备份与导入

mysqldump -u <USER_HOME> -p <DATABASE_NAME > news.sql   -- 输入后会让你输入进入MySQL的密码
-- 登陆
mysql> use <DATABASE_NAME>;
mysql> source news.sql;
-- 或者
mysql -u <USER_HOME> -p <DATABASE_NAME> < news.sql
-- 输入密码即可

更新记录：

创建 2016-04-11

添加时间戳与 Java 中时间的区别 2016-12-27

[参考文献]:

MySQL 日期时间函数大全

MySQL

MySQL 提示符 命令与语法规范

修改提示符

常用指令

语法规范：

操作数据库

数据类型

整形

浮点型

日期时间型

字符型

操作数据表

打开数据库

创建数据表

查看数据库中的数据表列表

插入记录

记录查找

约束

自增长 AUTO_INCREMENT

主键约束 PRIMARY KEY

唯一约束

默认值约束 DEFAULT

外键约束

表级约束与列级约束 ??

修改数据表

操作数据表中的记录

插入记录

单表更新

单表删除

查询表达式解析

where 语句进行条件查询

group by 语句对查询结果分组

having 语句设置分组条件

order by 语句对查询结果排序

limit 语句限制查询数量

子查询与连接

内连接 join 从句

内连接 inner join

左外连接 left outer join

右外连接 right outer join

全外连接 FULL OUTER JOIN

交叉连接 CROSS JOIN

函数

字符函数

substring

数值函数

比较运算符和函数

日期时间函数

时间与时间戳的转换

信息函数

聚合函数

加密函数

XML 支持

ExtractValue

UpdateXML

自定义函数

存储过程

用户权限

正则表达式

索引

创建索引

创建临时表

复制表

元数据

自增序列

重置序列

设置序列的开始值

防止数据重复

INSERT IGNORE

添加一个 UNIQUE 索引，如下所示：

统计重复数据

过滤重复数据

删除重复数据

SQL 注入

SQL like 语句注入

备份与导入

MySQL 提示符命令与语法规范