Skip to main content

DML & DDL & DCL

DML

数使用户能够查询数据库以及操作已有数据库中的数据的计算机语言 数据操纵分成数据查询和数据更新两类。数据更新又分成插入、删除、 和修改三种操作
主要命令:Select、Insert、Update、Delete、Merge、Explain、Plan、Call

Select

Mysql Reference

Select Into

Select的基本语法。
语法结构(其中[]是可选项):

SELECT  
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [MAX_STATEMENT_TIME = N]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
      [PARTITION partition_list]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

比较重要而且常用的关键词:

关键词 用途
FROM 表明数据的来源,可以使用select嵌套来创建数据源
WHERE 数据筛选条件
GROUP BY 将数据按照某个字段分组,一般和COUNT、HAVING等函数在一起使用
LIMIT 限制从x条数据开始取y条数据,或者直接取出y条数据
ORDER BY 以某个字段为基准进行排序(ASC 升序,DESC 降序)
HAVING 将GROUP BY的数据按照条件进行汇总

FROM

mysql> select name,sex,department from student;  
+-------+------+--------------+
| name  | sex  | department   |
+-------+------+--------------+
| Tom   | 男   | 计算机系      |
| Jreey | 男   | 中文系        |
| john  | 女   | 中文系        |
| jack  | 男   | 英语系        |
| lucky | 女   | 数学系        |
| scott | 男   | 计算机系      |
| tiger | 男   | 法律         |
+-------+------+--------------+
7 rows in set (0.00 sec)  

Group by & Having
Group by 使用的使用需要注意在Select中需要带上分组的字段。

mysql> select department,count(*) from student group by department;  
+--------------+----------+
| department   | count(*) |
+--------------+----------+
| 中文系       |        2 |
| 数学系       |        1 |
| 法律         |        1 |
| 英语系       |        1 |
| 计算机系     |        2 |
+--------------+----------+
5 rows in set (0.00 sec)  

如果我需要进一步筛选结果大于1的,可以使用having子句:

mysql> select department,count(*) from student group by department having count(*)>1;  
+--------------+----------+
| department   | count(*) |
+--------------+----------+
| 中文系       |        2 |
| 计算机系     |        2 |
+--------------+----------+
2 rows in set (0.00 sec)  

LIMIT
Limit有两种使用方法,从x条记录开始取y条结果和取x条结果:

mysql> select name,sex,department from student limit 4;  
+-------+------+--------------+
| name  | sex  | department   |
+-------+------+--------------+
| Tom   | 男   | 计算机系     |
| Jreey | 男   | 中文系       |
| john  | 女   | 中文系       |
| jack  | 男   | 英语系       |
+-------+------+--------------+
4 rows in set (0.00 sec)

mysql> select name,sex,department from student limit 2,4;  
+-------+------+--------------+
| name  | sex  | department   |
+-------+------+--------------+
| john  | 女   | 中文系       |
| jack  | 男   | 英语系       |
| lucky | 女   | 数学系       |
| scott | 男   | 计算机系     |
+-------+------+--------------+
4 rows in set (0.00 sec)  

Order by

mysql> select name,sex,department,birth from student order by birth desc;  
+-------+------+--------------+-------+
| name  | sex  | department   | birth |
+-------+------+--------------+-------+
| lucky | 女   | 数学系       |  1991 |
| john  | 女   | 中文系       |  1990 |
| jack  | 男   | 英语系       |  1990 |
| scott | 男   | 计算机系     |  1988 |
| Jreey | 男   | 中文系       |  1986 |
| Tom   | 男   | 计算机系     |  1985 |
| tiger | 男   | 法律         |  1970 |
+-------+------+--------------+-------+
7 rows in set (0.00 sec)  

Example:
求出学生年龄,并取出30岁以上的学生。这地方就要注意,select条件中的别名在where中不能使用,在order by中可以使用:

mysql> select name,sex,DATE_FORMAT(CURRENT_DATE(),'%Y') - birth as age from student where (DATE_FORMAT(CURRENT_DATE(),'%Y') - birth) >= 30 order by age desc;  
+-------+------+------+
| name  | sex  | age  |
+-------+------+------+
| tiger | 男   |   46 |
| Tom   | 男   |   31 |
| Jreey | 男   |   30 |
+-------+------+------+
3 rows in set (0.00 sec)  

Select的基本语法同样可以进行多表联合查询,但是查询的匹配方式采用的是Inner Join。

mysql> select s.id,s.name,s.department,g.c_name,g.grade from student as s,score as g where s.id=g.stu_id;  
+-----+-------+--------------+-----------+-------+
| id  | name  | department   | c_name    | grade |
+-----+-------+--------------+-----------+-------+
| 901 | Tom   | 计算机系     | 计算机    |    98 |
| 901 | Tom   | 计算机系     | 英语      |    80 |
| 902 | Jreey | 中文系       | 计算机    |    65 |
| 902 | Jreey | 中文系       | 中文      |    88 |
| 903 | john  | 中文系       | 中文      |    95 |
| 904 | jack  | 英语系       | 计算机    |    70 |
| 904 | jack  | 英语系       | 英语      |    92 |
| 905 | lucky | 数学系       | 英语      |    94 |
| 906 | scott | 计算机系     | 计算机    |    90 |
| 906 | scott | 计算机系     | 英语      |    85 |
| 902 | Jreey | 中文系       | 数学      |   100 |
| 904 | jack  | 英语系       | 数学      |   100 |
| 905 | lucky | 数学系       | 数学      |    76 |
+-----+-------+--------------+-----------+-------+
13 rows in set (0.00 sec)

mysql>  

同时where条件支持嵌套查询:

mysql> select r.c_name,r.grade,s.id,s.name from (select * from score where (c_name,grade)in(select c_name,max(grade) from score group by c_name)) as r,student as s where s.id=r.stu_id;  
+-----------+-------+-----+-------+
| c_name    | grade | id  | name  |
+-----------+-------+-----+-------+
| 计算机    |    98 | 901 | Tom   |
| 中文      |    95 | 903 | john  |
| 英语      |    94 | 905 | lucky |
| 数学      |   100 | 902 | Jreey |
| 数学      |   100 | 904 | jack  |
+-----------+-------+-----+-------+
5 rows in set (0.01 sec)  

Select Join

Select的Join查询语法,主要用于多表查询。Join分为Inner Join,Left Join和Right Join。
Mysql Reference 为了方便解释,我们将from中的表称之为主表,join的表称之为附表。
Inner Join

主表和附表的连接字段完全匹配的数据,才会被返回。

mysql> select r.c_name,r.grade,s.id,s.name from (select * from score where (c_name,grade)in(select c_name,max(grade) from score group by c_name)) as r inner join student as s on s.id=r.stu_id;  
+-----------+-------+-----+-------+
| c_name    | grade | id  | name  |
+-----------+-------+-----+-------+
| 计算机    |    98 | 901 | Tom   |
| 中文      |    95 | 903 | john  |
| 英语      |    94 | 905 | lucky |
| 数学      |   100 | 902 | Jreey |
| 数学      |   100 | 904 | jack  |
+-----------+-------+-----+-------+
5 rows in set (0.01 sec)  

Left Join

主表的数据全部展示,附表和主表连接字段匹配的数据会和主表一起展示。

mysql> select r.c_name,r.grade,s.id,s.name from (select * from score where (c_name,grade)in(select c_name,max(grade) from score group by c_name)) as r left join student as s on s.id=r.stu_id;  
+-----------+-------+------+-------+
| c_name    | grade | id   | name  |
+-----------+-------+------+-------+
| 计算机    |    98 |  901 | Tom   |
| 中文      |    95 |  903 | john  |
| 英语      |    94 |  905 | lucky |
| 数学      |   100 |  902 | Jreey |
| 数学      |   100 |  904 | jack  |
+-----------+-------+------+-------+
5 rows in set (0.00 sec)  

Right Join

附表的数据全部展示,主表和附表连接字段匹配的数据会和附表一起展示。

mysql> select r.c_name,r.grade,s.id,s.name from (select * from score where (c_name,grade)in(select c_name,max(grade) from score group by c_name)) as r right join student as s on s.id=r.stu_id;  
+-----------+-------+------+-------+
| c_name    | grade | id   | name  |
+-----------+-------+------+-------+
| 计算机    |    98 |  901 | Tom   |
| 中文      |    95 |  903 | john  |
| 英语      |    94 |  905 | lucky |
| 数学      |   100 |  902 | Jreey |
| 数学      |   100 |  904 | jack  |
| NULL      |  NULL |  906 | scott |
| NULL      |  NULL | 1000 | tiger |
+-----------+-------+------+-------+
7 rows in set (0.01 sec)  

在这个例子中,我们就可以发现和left join明显的不同
right join将附表的数据完全展示了,而主表中没有相符的数据,采用了填充NULL的方法。
如果不需要填充NULL,可以用IF_NULL函数加入一个默认值

Insert

SQL中的插入语句。
基本语法:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]  
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Example:

insert into A(a1,a2,a3) values('b','c','d');  
insert into A(a1,a2,a3) values('b','c','d'),('b2','c2','d2');  

插入语句并没有过多的条件。需要注意的是 ON DUPLICATE KEY UPDATE。此参数提供了一个若不存在则插入,存在则更新的方法。
而此参数的使用条件是表中必须存在unique键来来保证数据不可重复。
Example:

insert into A(a1,a2,a3) values('b','c','d') on duplicate key update a3='test';  

Update

检索符合的数据并更新数据库中的数据。
基本语法:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference  
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

Example:

update A set a1='cs',a2=CONCAT('plz',a2) where a3='d';  

Delete

从数据库中删除数据。 基本语法:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name  
    [PARTITION (partition_name,...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

Example:

delete from A where a1='cs';  
delete from A where a1='cs' limit 2,4;  

Explain

Explain语句可以用来检测整个SQL语句的执行过程。只用将explain关键字加在要执行的sql语句之前即可。

mysql> explain select r.c_name,r.grade,s.id,s.name from (select * from score where (c_name,grade)in(select c_name,max(grade) from score group by c_name)) as r left join student as s on s.id=r.stu_id;  
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------------+------+----------+---------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                     | rows | filtered | Extra                           |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------------+------+----------+---------------------------------+
|  1 | PRIMARY     | score | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                    |   13 |   100.00 | Using where                     |
|  1 | PRIMARY     | s     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | student_db.score.stu_id |    1 |   100.00 | NULL                            |
|  3 | SUBQUERY    | score | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                    |   13 |   100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------------+------+----------+---------------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql>  

DDL

内容用于描述数据库中要存储的现实世界实体的语言。一个数据库模式 包含该数据库中所有实体的描述定义。这些定义包括结构定义、操作方法定义等
定义主要产生两种类型的数据:数据字典以及数据类型和结构定义

Create

创建语句,用于创建表或者数据库或者存储过程或其他内容。mysql reference

创建数据库

CREATE DATABASE `student_db`;  
如果需要指定数据库的编码:
CREATE DATABASE `student_db` /*!40100 DEFAULT CHARACTER SET utf8 */ ;  

创建表

表创建语句主要由表名、字段定义、索引定义组成。
基本语法:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name  
    (create_definition,...)
    [table_options]
    [partition_options]

create_definition:

col_name column_definition  
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
      [index_option] ...
  | {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
      [index_name] [index_type] (index_col_name,...)
      [index_option] ...
  | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (index_col_name,...) reference_definition
  | CHECK (expr)

column_definition:

data_type [NOT NULL | NULL] [DEFAULT default_value]  
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
      [COMMENT 'string']
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
      [STORAGE {DISK|MEMORY|DEFAULT}]
      [reference_definition]
  | data_type [GENERATED ALWAYS] AS (expression)
      [VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment]
      [NOT NULL | NULL] [[PRIMARY] KEY]

Example:

CREATE TABLE `visit_log` (  
  `vl_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '记录ID',
  `vl_addr` bigint(15) NOT NULL DEFAULT '0' COMMENT '访问者IP',
  `vl_saddr` char(16) NOT NULL DEFAULT '' COMMENT '字符串表示访问者IP',
  `vl_ua` text NOT NULL COMMENT 'User-Agent',
  `vl_logtime` int(11) NOT NULL DEFAULT '0' COMMENT '访问时间',
  `vl_reqtype` char(16) NOT NULL DEFAULT 'GET' COMMENT '请求方式',
  `vl_uri` text NOT NULL COMMENT '请求的地址+请求的GET参数',
  `vl_status` int(11) NOT NULL DEFAULT '0' COMMENT '返回状态',
  `vl_referral` text NOT NULL COMMENT 'referral url',
  `vl_websiteid` int(11) NOT NULL DEFAULT '0' COMMENT '网站ID',
  PRIMARY KEY (`vl_id`)
);

Alter

Alter一般用于添加或修改表中的字段名或者字段定义。也可以用于修改字段顺序等。同样的也可以用于修改数据库的名字或者编码格式。Mysql Reference
基本语法(Alter Table):

ALTER [IGNORE] TABLE tbl_name  
    [alter_specification [, alter_specification] ...]
    [partition_options]

Example:
使用modify的时候不能修改column名字,如果需要对column重命名,需要使用change关键词

修改字段定义:
ALTER TABLE emp MODIFY first_name VARCHAR(20) NOT NULL DEFAULT '-';  
添加字段:
alter table emp add column age int(3) not null default 0;  
修改字段名字,同时修改定义:
alter table emp change age age1 int(4) not null default 0;  
在表Column的尾部追加新的column:
alter table emp add birth date not null after empno;  
移动column到表column的首位:
alter table emp modify age1 int(3) not null default 0 first;  
将某个column移动到另一个columon后面:
alter table department change deptno deptno int(11) NOT NULL after deptname;  
删除主键:
alter visit_log drop primary key;  
修改表名:
alter table emp rename employees;  

Drop

Drop一般用来删除表、数据库、Event、等内容。
语法:

删除表:  
DROP [TEMPORARY] TABLE [IF EXISTS]  
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]
删除数据库:
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name  

Truncate

Truncate用于截断表,并将索引、自增长计数等数据重置。
基本语法:

TRUNCATE [TABLE] tbl_name  

Rename

Rename一般用于对表名修改。
语法:

修改表名:
RENAME TABLE tbl_name TO new_tbl_name  
    [, tbl_name2 TO new_tbl_name2] ...

DCL

设置或更改数据库用户或角色权限的语句
包括对基本表和视图的授权,完整性规则的描述,事务控制等内容

Mysql权限体系

mysql 的权限体系大致分为5个层级:

全局层级

全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。GRANT ALL ON .和REVOKE ALL ON .只授予和撤销全局权限。

数据库层级

数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db和mysql.host表中。GRANT ALL ON dbname.*和REVOKE ALL ON dbname.*只授予和撤销数据库权限。

表层级

表权限适用于一个给定表中的所有列。这些权限存储在mysql.talbespriv表中。GRANT ALL ON dbname.tblname和REVOKE ALL ON dbname.tbl_name只授予和撤销表权限。

列层级

列权限适用于一个给定表中的单一列。这些权限存储在mysql.columns_priv表中。当使用REVOKE时,您必须指定与被授权列相同的列。

子程序层级

CREATE ROUTINE, ALTER ROUTINE, EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且,除了CREATE ROUTINE外,这些权限可以被授予为子程序层级,并存储在mysql.procs_priv表中。

Mysql权限列表

权限 权限级别 权限说明
CREATE 数据库、表或索引 创建数据库、表或索引权限
DROP 数据库或表 删除数据库或表权限
GRANT OPTION 数据库、表或保存的程序 赋予权限选项
ALTER 更改表,比如添加字段、索引等
DELETE 删除数据权限
INDEX 索引权限
INSERT 插入权限
SELECT 查询权限
UPDATE 更新权限
CREATE VIEW 视图 创建视图权限
SHOW VIEW 视图 查看视图权限
ALTER ROUTINE 存储过程 更改存储过程权限
CREATE ROUTINE 存储过程 创建存储过程权限
ALTER 更改表,比如添加字段、索引等
EXECUTE 存储过程 执行存储过程权限
FILE 服务器主机上的文件访问 文件访问权限
LOCK TABLES 服务器管理 锁表权限
CREATE USER 服务器管理 创建用户权限
PROCESS 服务器管理 查看进程权限
REPLICATION CLIENT 服务器管理 复制权限
REPLICATION SLAVE 服务器管理 复制权限
SHOW DATABASES 服务器管理 查看数据库权限
SHUTDOWN 服务器管理 关闭数据库权限
SUPER 服务器管理 执行kill线程权限
RELOAD 服务器管理 执行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload等命令的权限
CREATE TEMPORARY TABLES 服务器管理 创建临时表权限

权限的作用域

权限分布 权限级别
表权限 Select Insert Update Delete Create Drop Grant References Index Alter
列权限 Select Insert Update References
过程权限 Execute Alter Routine Grant

创建用户

CREATE USER [IF NOT EXISTS]  
    user_specification [, user_specification] ...
    [REQUIRE {NONE | tsl_option [[AND] tsl_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...

Example:

CREATE USER 'root'@'%' IDENTIFIED BY 'some_password';  

删除用户

DROP USER IF EXISTS 'root'@'%';  

Grant

Grant用于将权限授权给用户。Mysql Reference
Mysql账户包括 “username” 和 “host” 两部分 即是username@host,后者表示该用户被允许从何地接入。user@’%’表示用户user可以从任何地址访问本地的数据库,默认可以省略。还可以是 “user@198.2.221.%“、”user1@%.example.com” 等。数据库格式为 db.table,可以是 “test.” 或 “.*”,前者表示 test 数据库的所有表,后者表示所有数据库的所有表。
子句 “WITH GRANT OPTION” 表示该用户可以为其他用户分配权限。
语法:

GRANT  
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_specification [, user_specification] ...
    [REQUIRE {NONE | tsl_option [[AND] tsl_option] ...}]
    [WITH {GRANT OPTION | resource_option} ...]

Example:

授权(某个数据库)所有权限给某个用户:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;  
也可以忽略掉 PRIVILEGES:
GRANT ALL ON cloakv4.* TO 'root'@'localhost' WITH GRANT OPTION;

授权某个用户只读权限:
grant select on *.* to 'test'@'localhost';

授权某个用户只能读取表中的某个字段:
grant select(name) on student_db.student to 'test'@'localhost';  

Revoke

Revoke用于将权限从用户手上收回。
语法:

REVOKE  
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user [, user] ...

Example:

回收某个用户的所有权限:
revoke all on *.* from 'test'@'localhost';  

Other

修改用户密码

修改自己的密码:
set password=password('new password');

修改别人的密码:
set password for 'test'@'localhost' = password('new password');  

修改用户名

rename 'test'@'localhost' to 'remote'@'localhost';  

删除用户

drop user 'test'@'localhost';  

查看用户权限

Show grants for 'test'@'localhost' \G  

TCL

用于控制事务内执行流程的语言

Start Transaction|Begin

开始一个事务。

START TRANSACTION  
或者
BEGIN  

Savepoint

创建一个记录点。方便回滚记录到这个地方

SAVEPOINT identifier  

Rollback

回滚事务。

ROLLBACK [[WORK] TO [SAVEPOINT] identifier]  

Commit

提交事务。

COMMIT  

一般来说,一个事务的完整过程:

开始事务
START TRANSACTION  
可选:创建一个存档点
SAVEPOINT sp  
开始操作SQL
select  
insert  
...
操作完成提交数据
COMMIT  
或者,操作失败,回滚数据(回滚到某个存档点)
ROLLBACK  
ROLLBACK TO sp  
释放存档点
RELEASE SAVEPOINT sp  

Mysql数据类型

数值类型

Bit

BIT[(M)]  

位类型,默认是1位长度。Bit值范围1~64位。

Bool

BOOL  
BOOLEAN  

布尔值,实际上是Tinyint(1)的同义词。0->false, 1->true。

TinyInt

 TINYINT[(M)] [UNSIGNED] [ZEROFILL]

M和数据的值范围无关,代表最大显示宽度。Tinyint值范围-128~127。Unsigned范围0~255。(8 Bit Length)

最大显示宽度:
最大有效显示宽度是255。显示宽度与存储大小或类型包含的值的范围无关。该可选显示宽度规定用于显示宽度小于指定的列宽度的值时从左侧填满宽度。

SmallInt

SMALLINT[(M)] [UNSIGNED] [ZEROFILL]  

Smallint值范围-32768~2767。Unsigned范围0~65535。(16 Bit Length)

MediumInt

MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]  

MediumInt值范围-8388608~8388607。Unsigned范围0~16777215。(24 Bit Length)

Int

INT[(M)] [UNSIGNED] [ZEROFILL]  
INTEGER[(M)] [UNSIGNED] [ZEROFILL]  

Int值范围-2147483648~2147483647。Unsigned范围0~4294967295。(32 Bit Length)

BigInt

BIGINT[(M)] [UNSIGNED] [ZEROFILL]  

BigInt值范围-9223372036854775808~9223372036854775807。Unsigned范围0~18446744073709551615。(64 Bit Length)

Decimal

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]  
NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL]  
FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]  

Decimal默认值为DECIMAL(10,0)。这里M代表数据的总长度,D代表小数点后的数据长度。即整数部分长度=M-D。M的最大值为65,D的最大值为30。

Float

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]  

单精度浮点型,占用4字节空间。M代表数据总长度,D代表小数点后数据长度。

Double

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]  

双精度浮点型,占用8字节空间。M代表数据总长度,D代表小数点后数据长度。

日期类型

Date

DATE  

日期类型,精确到日。范围1000-01-01~9999-12-31。

DateTime

DATETIME[(fsp)]  

fsp的值,从0到6。默认值为0。代表秒数的小数部分长度。Datetime值范围1000-01-01 00:00:00.000000~9999-12-31 23:59:59.999999。随时区而改变。

TimeStamp

TIMESTAMP[(fsp)]  

fsp的值,从0到6。默认值为0。代表秒数的小数部分长度。Timestamp会把时间转换为数字格式表示。timestamp不随时区而改变。

Time

Year

YEAR[(4)]  

Year范围1901~2155年,和0000年。

字符串类型

Char

[NATIONAL] CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]

M的范围从0到255,如果没有指定,则为1。Char类型是定长字符串。即数据的长度不随文字长度而改变。M指定的是字符串的字母数量,而非字节数。在Char类型中,存储容量上限为255字节。当指定为Char(0)的时候,字段只接受NULL和''。

Varchar

[NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]

M的范围从0到65535,如果没有指定,则为1。varchar类型是不定长字符串。即数据的长度随文字长度而改变。M指定的是字符串的字母数量,而非字节数。在VarChar类型中,存储容量上限为65535字节。在内容低于255字节的时候,需要额外1字节来存储。在大于255字节时,需要额外2字节来存储。即可用空间视实际内容大小要减去1或者2字节。

Binary

BINARY(M)  

Binary格式存储二进制格式数据,M代表数据的字节数。其存储方式和Char一致,采用定长存储空间。

Varbinary

VARBINARY(M)  


Varbinary格式存储二进制格式数据,M代表存储的最大字节数。其存储方式和Varchar一致。

TINYBLOB

TINYBLOB  

存储二进制格式数据,最大255字节。Tinyblob会使用1字节存储数据长度。

BLOB

BLOB[(M)]  

存储二进制格式数据,最大65535字节。M代表可存储的字节数。BLOB格式会使用2字节来存储数据长度。

MEDIUMBLOB

MEDIUMBLOB  

存储二进制格式数据,最大16777215字节。M代表可存储的字节数。MEDIUMBLOB格式会使用3字节来存储数据长度。

LONGBLOB

LONGBLOB  

存储二进制格式数据,最大 4294967295字节(4G)。M代表可存储的字节数。LONGBLOB格式会使用4字节来存储数据长度。

LONGTEXT

LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]  

存储字符串。最大长度为4294967295个字符(字节)(4G)。LONGTEXT会使用4个字节来存储字符串长度。

MEDIUMTEXT

MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]  

存储字符串。最大长度为16777215个字符(字节)。LONGTEXT会使用3个字节来存储字符串长度。

TEXT

TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]  

存储字符串。最大长度为65535个字符(字节)。LONGTEXT会使用2个字节来存储字符串长度。

TINYTEXT

 TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

存储字符串。最大长度为255个字符(字节)。TINYTEXT会使用1个字节来存储字符串长度。

Mysql字符集

Latin1

Gbk

Utf8

Mysql安全

Mysql函数

数据类型转换

Mysql数据类型转换函数有Cast和Convert,他们主要会转换以下数据类型:

Name Data Type
二进制,同带binary前缀的效果 BINARY
字符型,可带参数 CHAR()
日期 DATE
时间 TIME
日期时间型 DATETIME
浮点数 DECIMAL
整数 SIGNED
无符号整数 UNSIGNED

Cast

CAST(value AS data_type)  

转换整数
此时需要注意的是如果字符串中有英文字符或者小数,在转换整数时会自动的将英文字符和小数点后的内容截取掉。
同时也不会进行四舍六入的取整计算。

mysql> select cast('123123' as signed);  
+--------------------------+
| cast('123123' as signed) |
+--------------------------+
|                   123123 |
+--------------------------+
1 row in set (0.00 sec)

mysql> select cast('123123e7' as signed);  
+----------------------------+
| cast('123123e7' as signed) |
+----------------------------+
|                     123123 |
+----------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select cast('1231e.23e7' as signed);  
+------------------------------+
| cast('1231e.23e7' as signed) |
+------------------------------+
|                         1231 |
+------------------------------+
1 row in set, 1 warning (0.00 sec)  


而转换decimal类型的时候,因为decimal类型本身即为浮点数支持,所以会进行四舍六入的运算

mysql> select cast("2.0000267e5" as decimal(8,1));  
+-------------------------------------+
| cast("2.0000267e5" as decimal(8,1)) |
+-------------------------------------+
|                            200002.7 |
+-------------------------------------+
1 row in set (0.00 sec)  

字符串转换
转换为字符串同样要注意,如果指定了char的长度,那么超出长度的字符串将会被截断

mysql> select cast(16297461 as char(5));  
+---------------------------+
| cast(16297461 as char(5)) |
+---------------------------+
| 16297                     |
+---------------------------+
1 row in set, 1 warning (0.00 sec)

mysql>select cast(16297461 as char);  
+------------------------+
| cast(16297461 as char) |
+------------------------+
| 16297461               |
+------------------------+
1 row in set (0.00 sec)

mysql>  


与一般语言中的函数一样,Mysql的函数同样支持嵌套

mysql> select cast(cast(771 - cast('37' as signed) as char) as binary(8));  
+-------------------------------------------------------------+
| cast(cast(771 - cast('37' as signed) as char) as binary(8)) |
+-------------------------------------------------------------+
| 734                                                         |
+-------------------------------------------------------------+
1 row in set (0.00 sec)  

Convert

Convert大致和Cast相同,主要区别在于函数的用法

CONVERT(value,data_type)  

Sample:

mysql> select convert("2015-09-03",DATETIME);  
+--------------------------------+
| convert("2015-09-03",DATETIME) |
+--------------------------------+
| 2015-09-03 00:00:00            |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select convert("2015-09-03 12:07:00",DATETIME);  
+-----------------------------------------+
| convert("2015-09-03 12:07:00",DATETIME) |
+-----------------------------------------+
| 2015-09-03 12:07:00                     |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> select convert("2.0000263e5",DECIMAL(8,1));  
+-------------------------------------+
| convert("2.0000263e5",DECIMAL(8,1)) |
+-------------------------------------+
|                            200002.6 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> select convert("2.0000267e5",DECIMAL(8,1));  
+-------------------------------------+
| convert("2.0000267e5",DECIMAL(8,1)) |
+-------------------------------------+
|                            200002.7 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql>  

格式化

Mysql提供了日期和字符串等格式化的函数方便进行数据输出的格式化调整。

FORMAT

Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part.
Format函数用于格式化数字类型的输出显示,会自动给数据加上逗号分隔,同时根据指定的小数位数进行四舍六入截取。format输出之后的结果是字符串类型
如果不想对数字格式化又需要四舍五入取精度或者需要的是数值类型的结果,可以尝试round(X,D)

FORMAT(X,D)  

Example:

mysql> select format('1776',0);  
+------------------+
| format('1776',0) |
+------------------+
| 1,776            |
+------------------+
1 row in set (0.00 sec)

mysql> select format('1776',2);  
+------------------+
| format('1776',2) |
+------------------+
| 1,776.00         |
+------------------+
1 row in set (0.00 sec)

mysql> select format('1.776',2);  
+-------------------+
| format('1.776',2) |
+-------------------+
| 1.78              |
+-------------------+
1 row in set (0.00 sec)  

DATE_FORMAT

Formats the date value according to the format string.
The following specifiers may be used in the format string. The “%” character is required before format specifier characters.

Date_Format函数提供了对日期进行格式化的支持。Mysql Reference
Parameter List:

Specifier Description
%a Abbreviated weekday name (Sun..Sat)
%b Abbreviated month name (Jan..Dec)
%c Month, numeric (0..12)
%D Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d Day of the month, numeric (00..31)
%e Day of the month, numeric (0..31)
%f Microseconds (000000..999999)
%H Hour (00..23)
%h Hour (01..12)
%I Hour (01..12)
%i Minutes, numeric (00..59)
%j Day of year (001..366)
%k Hour (0..23)
%l Hour (1..12)
%M Month name (January..December)
%m Month, numeric (00..12)
%p AM or PM
%r Time, 12-hour (hh: mm: ss followed by AM or PM)
%S Seconds (00..59)
%s Seconds (00..59)
%T Time, 24-hour (hh: mm: ss)
%U Week (00..53), where Sunday is the first day of the week; WEEK() mode 0
%u Week (00..53), where Monday is the first day of the week; WEEK() mode 1
%V Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X
%v Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x
%W Weekday name (Sunday..Saturday)
%w Day of the week (0=Sunday..6=Saturday)
%X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y Year, numeric, four digits
%y Year, numeric (two digits)
%% A literal “%” character
%x x, for any “x” not listed above

Command:

DATE_FORMAT(date,format)  

Example:

mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');  
+------------------------------------------------+
| DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y') |
+------------------------------------------------+
| Sunday October 2009                            |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%Y');  
+------------------------------------------+
| DATE_FORMAT('2009-10-04 22:23:00', '%Y') |
+------------------------------------------+
| 2009                                     |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %m %Y');  
+------------------------------------------------+
| DATE_FORMAT('2009-10-04 22:23:00', '%W %m %Y') |
+------------------------------------------------+
| Sunday 10 2009                                 |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%d %m %Y');  
+------------------------------------------------+
| DATE_FORMAT('2009-10-04 22:23:00', '%d %m %Y') |
+------------------------------------------------+
| 04 10 2009                                     |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql>  

STRTODATE

strtodate 提供了将字符串转换为date格式的支持,其参数与date_format相同。

STR_TO_DATE(str,format)  

Example:

mysql> SELECT STR_TO_DATE('15:35:00', '%H:%i:%s');  
+-------------------------------------+
| STR_TO_DATE('15:35:00', '%H:%i:%s') |
+-------------------------------------+
| 15:35:00                            |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT STR_TO_DATE('01,11,2015','%d,%m,%Y');  
+--------------------------------------+
| STR_TO_DATE('01,11,2015','%d,%m,%Y') |
+--------------------------------------+
| 2015-11-01                           |
+--------------------------------------+
1 row in set (0.00 sec)  

Other Time Function

CURRENT_DATE

mysql> select current_date();  
+----------------+
| current_date() |
+----------------+
| 2016-08-02     |
+----------------+
1 row in set (0.00 sec)

NOW

mysql> select now();  
+---------------------+
| now()               |
+---------------------+
| 2016-08-02 22:37:54 |
+---------------------+
1 row in set (0.01 sec)  

字符串操作

CONCAT

Returns the string that results from concatenating the arguments. May have one or more arguments. If all arguments are nonbinary strings, the result is a nonbinary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent nonbinary string form.
CONCAT() returns NULL if any argument is NULL.

CONCAT(str1,str2,...)  

Example:

mysql> select concat("a",155,"c");  
+---------------------+
| concat("a",155,"c") |
+---------------------+
| a155c               |
+---------------------+
1 row in set (0.00 sec)  

LENGTH

LENGTH()用于计算字符串的长度。

LENGTH(str)  

Example:

mysql> select length("hello world");  
+-----------------------+
| length("hello world") |
+-----------------------+
|                    11 |
+-----------------------+
1 row in set (0.00 sec)  

IP地址转换

INET_ATON()

IPV4地址转换为Numeric

mysql> select INET_ATON("192.168.7.2");  
+--------------------------+
| INET_ATON("192.168.7.2") |
+--------------------------+
|               3232237314 |
+--------------------------+
1 row in set (0.01 sec)  

INET_NTOA()

Numeric转换为IPV4地址

mysql> select INET_NTOA(3232237314);  
+-----------------------+
| INET_NTOA(3232237314) |
+-----------------------+
| 192.168.7.2           |
+-----------------------+
1 row in set (0.00 sec)  

INET6_ATON()

IPV6地址转换为Numeric,注意这里如果需要能查看结果,需要使用HEX编码

mysql> SELECT HEX(INET6_ATON('fdfe::5a55:caff:fefa:9089'));  
+----------------------------------------------+
| HEX(INET6_ATON('fdfe::5a55:caff:fefa:9089')) |
+----------------------------------------------+
| FDFE0000000000005A55CAFFFEFA9089             |
+----------------------------------------------+
1 row in set (0.00 sec)  

INET6_NTOA()

Numeric转换为IPV6地址

mysql> SELECT INET6_NTOA(UNHEX('FDFE0000000000005A55CAFFFEFA9089'));  
+-------------------------------------------------------+
| INET6_NTOA(UNHEX('FDFE0000000000005A55CAFFFEFA9089')) |
+-------------------------------------------------------+
| fdfe::5a55:caff:fefa:9089                             |
+-------------------------------------------------------+
1 row in set (0.00 sec)