2.2 SQL语言（一）

分类

---

• 数据操纵语言（DML）

主要用来处理数据库中的数据内容。允许用户对数据库中的数据进行查询 ，插入，更新和删除等操作

DML语句	功能说明
SELECT	从表或视图中检索数据行
INSERT	插入数据到表或视图
UPDATE	更新数据
DELETE	删除数据
CALL	调用过程
MERGE	合并（插入或修改）
COMMIT	将当前事务所做的更改永久化（写入数据库）
ROLLBACK	取消上次提交以来的所有操作

• 数据定义语言（DDL）

是一组 SQL 命令，用于创建和定义数据库对象，并且将对这些对象的定义保存到数据字典中。通过 DDL 语句可以创建数据库对象，修改数据库对象和删除数据库对象等

DDL语句	功能说明
CREATE	创建数据库结构
ALTER	修改数据库结构
DROP	删除数据库结构
RENAME	更改数据库对象的名称
TRUNCATE	删除表的全部内容

• 数据控制语言（DCL）

DCL语句	功能说明
CRANT	授予其他用户对数据库结构的访问权限
REVOKE	收回用户访问数据库结构的权限

数据库对象

---

数据库的组件(对象)：
数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等

命名规则：

• 必须以字母开头
• 可包括数字和三个特殊字符（# _ $）
• 不要使用MySQL的保留字
• 同一 database(Schema)下的对象不能同名

• 库

创建库

语法：CREATE DATABASE|SCHEMA [IF NOT EXISTS]

CREATE DATABASE testdb1;

删除库

DROP DATABASE IF EXISTS testdb1;

查看库

SHOW DATABASES;

• 表

创建表

创建新表

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

CREATE TABLE testable1 ( system_id TINYINT UNSIGNED PRIMARY KEY, system_name CHAR(10) NOT NULL, system_release CHAR(10), system_version CHAR(20) );

查询建表

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

CREATE TABLE testable2 SELECT user,host,password FROM mysql.user;

复制表结构

语法：CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }

CREATE TABLE testable3 LIKE mysql.user;

• 数据类型：

分类	子类型	数据类型	上限	数据类型说明
日期和时间数据类型		date	3字节	日期，格式：2014-09-18
日期和时间数据类型		time	3字节	时间，格式：08:42:30
日期和时间数据类型		datetime	8字节	日期时间，格式：2014-09-18 08:42:30
日期和时间数据类型		timestamp	4字节	自动存储记录修改的时间，格式：2009-09-18 08:42:30
日期和时间数据类型		year	1字节	年份， 格式：2009
数值数据类型	整型	tinyint	1字节	有符号范围（-128~127），无符号（0~255）
数值数据类型	整型	smallint	2字节	有符号范围（-32768~32767），无符号（0~65535）
数值数据类型	整型	mediumint	3字节	有符号范围（-8388608~8388607），无符号（0~16777215）
数值数据类型	整型	Int(integer)	4字节	有符号范围 (-2147483648~2147483647)， 无符号(0~4294967295)
数值数据类型	整型	bigint	8字节	有符号范围（-9223372036854775807~ 9223372036854775807）， 无符号（0~18446744073709551615）
数值数据类型	浮点型	float(m, d)	4字节	单精度浮点型，m总个数，d小数位  单精度实数在内存中占32bit 有效数字为6~7位
数值数据类型	浮点型	numeric(m,d)	4字节	m总个数，d小数位
数值数据类型	浮点型	double(m, d)	8字节	双精度浮点型，m总个数，d小数位  双精度实数占内存单元为64bit 有效数字为15~16位
数值数据类型	浮点型	decimal(m, d)		decimal是存储为字符串的浮点数
字符串数据类型		char(n)	255字节	固定长度 每个汉字3字节
字符串数据类型		varchar(n)	65535字节 5.0以上版本	可变长度 每个汉字3字节 varchar起始位和结束位占去了3个字节 varchar会使用1-3个字节来存储长度 varchar性能略逊char，当长度变化的时候，要重新分配内存
字符串数据类型		tinytext	255字节	可变长度
字符串数据类型		text	65535字节	可变长度 存储的时候不会截断尾部的空格  text不会使用1-3个字节来存储长度 text没有默认值
字符串数据类型		mediumtext	16M	可变长度
字符串数据类型		longtext	4G	可变长度
字符串数据类型	二进制形式	TinyBlob	255字节	可变长度
字符串数据类型	二进制形式	blob	65535字节	可变长度
字符串数据类型	二进制形式	mediumblob	16M	可变长度
字符串数据类型	二进制形式	longblob	4G	可变长度
枚举型		enum(“1″, “2″)	最大65535个 不同的枚举值	enum数据类型就是定义了一种枚举，最多包含65535个不同的成员。 当定义了一个enum的列时，该列的值限制为列定义中声明的值。 如果列声明包含NULL属性，则NULL将被认为是一个有效值，并且是默认值。如果声明了NOT NULL，则列表的第一个成员是默认值。
其它类型		set(“1”, “2″,)	最大64个 不同的值	set数据类型为指定一组预定义值中的零个或多个值提供了一种方法，这组值最多包括64个成员。 值的选择限制为列定义中声明的值。

• 字段修饰符

  数据类型	常见的修饰符
  整型	UNSIGNED：无符号，表示非负（UNSIGNED要定义在NULL或NOT NULL之前） NULL：允许为空 NOT NULL：允许非空 DEFAULT N：定义默认值 AUTO_INCREMNET：自动增长，自动增长的字段必须是整型、非空、非负、唯一键或主键
  浮点型	UNSIGNED：无符号，表示非负（UNSIGNED要定义在NULL或NOT NULL之前） NULL：允许为空 NOT NULL：允许非空 DEFAULT N：定义默认值
  字符型（char，varchar和text）	NULL：允许为空 NOT NULL：允许非空 DEFAULT ‘string’：定义默认值 CHARACTER SET 字符集：设置字符集 show character set可以查看mysql支持的所有字符集 show variables like ‘%charac%’可以查看mysql支持的默认字符集 collation '排序规则'：设定排序规则 show collation可以查看mysql支持的所有排序规则
  字符型（binary、varbinary和blob）	NULL：允许为空 NOT NULL：允许非空 DEFAULT ‘string’：定义默认值
  日期时间型	NULL：允许为空 NOT NULL：允许非空 DEFAULT ‘string’：定义默认值
  枚举型	NULL：允许为空 NOT NULL：允许非空 DEFAULT ‘string’：定义默认值

查看表

语法：SHOW TABLES [FROM db_name]

SHOW TABLES;

SHOW TABLES FROM testdb1;

查看表结构

语法：DESC [db_name.]tb_name

MariaDB [testdb1]> DESC testable1;
+----------------+---------------------+------+-----+---------+-------+
| Field          | Type                | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+-------+
| system_id      | tinyint(3) unsigned | NO   | PRI | NULL    |       |
| system_name    | char(10)            | NO   |     | NULL    |       |
| system_release | char(10)            | YES  |     | NULL    |       |
| system_version | char(20)            | YES  |     | NULL    |       |
+----------------+---------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

查看表状态

语法：SHOW TABLE STATUS FROM db_name

SHOW TABLE STATUS LIKE 'testable1'\G

查看创建表的过程

SHOW CREATE TABLE testable1\G

删除表

DROP TABLE IF EXISTS testable1;

修改表

语法：ALTER TABLE 'tbl_name'

• 添加字段

语法：ADD col1 data_type [FIRST|AFTER col_name]

ALTER TABLE testable1 ADD system_framework VARCHAR(10) AFTER system_release;

• 修改字段名

语法：CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]

ALTER TABLE testable1 CHANGE system_framework system_Framework VARCHAR(10);

• 修改默认值

语法：ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

ALTER TABLE testable1 ALTER system_Framework SET DEFAULT 'x86_64';

• 修改字段属性

语法：MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

ALTER TABLE testable1 MODIFY system_version FLOAT(10,5);

• 删除字段

语法：DROP [COLUMN] col_name

ALTER TABLE testable1 DROP system_Framework;

• 表改名

语法：RENAME [TO|AS] new_tbl_name

ALTER TABLE testable1 RENAME System_List;