mysql语言九大动词_MySQL数据类型及SQL语句(二)

MySQL数据类型MySQL中定义数据字段的类型对你数据库的优化是非常重要的。MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

数值类型精确数值型整形: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT

十进制: DECIMAL

近似数值型(浮点型):FLOAT

DOUBLE

字符型char使用固定长度的空间进行存储，char(4)存储4个字符，根据编码方式的不同占用不同的字节，gbk编码方式，不论是中文还是英文，每个字符占用2个字节的空间，utf8编码方式，每个字符占用3个字节的空间。如果需要存储的字符串的长度跟所有值的平均长度相差不大，适合用char，如MD5。CHAR,BINARY: 定长数据类型(CHAR不区分字符大小写,BINARY区分字符大小写)

VARCHAR,VARBINARY: 变长数据类型,需要结束符

TEXT: TINYTEXT, TEXT, MEDIUMTEXT, LOGTEXT

BLOB: TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB

ENUM,SET

注意：binary保存二进制字符串，就是说它们包含字节字符串而不是字符字符串，没有字符集限制

注意：CHAR,VARCHAR,TEXT 不区分字符大小写；BINARY,VARBINARY,BLOB 区分字符大小写

日期和时间类型DATE

TIME

DATETIME

TIMESTAMP

YEAR(2), YEAR(4)

特殊类型ENUM:从给定的字符串中选择一个字串

SET:集合,从指定字符集合中选择多个组合成一种变化形式

MySQL中字符大小写SQL关键字及函数名不区字符大小写；SELECT, Select, select

切记：遵循同一种风格

数据库、表、索引及视图的名称是否区分大小写取决于低层的OS及FS；

存储过程、存储函数及事件调度器不区分字符大小写；但触发器区分；

表别名不区分大小写；

字段中字符数据，类型为binary、blog、varbinary时区分大小写；其它的不区分；

数据类型的修饰符

整形修饰符NOT NULL

NULL

DEFAULT NUMBER

AUTO_INCREMENT:自动增长,需要下面三个条件的支持。UNSIGNED: 无符号,必须放在仅放在字段类型后

PRIMARY KEY | UNIQUE KEY

NOT NULL

例如:

CREATE TABLE t1 (id INT UNSIGNED AUTO_INCREMNET PRIMAY KEY NOT NULL,name VARCHAR(5) NOT NULL);

字符型修饰符字符类型的修饰符: 定义在字段类型之后NOT NULL: 非空约束

NULL: 默认为空

DEFAULT ‘STRING’: 指明默认值

CHARACTE SET ‘’: 使用字符集

COLLATION: 使用的排序规则

查看字符集和排序规则: 在数据库上定义后,表可以继承,一般无须定义

mysql>SHOW CHARACTER SET;

mysql>SHOW COLLATION;

字符有通配符：%: 匹配任意长度的任意字符

_: 匹配任意单个字符；

日期时间型的修饰符:NOUT NULL

NULL

DEFAULT

SQL语句SQL(Structured Query Language),结构化查询语言，是关系数据库的标准语言;集数据定义语言( DDL),数据操纵语言( DML),

数据控制语言( DCL)功能于一体。

SQL动词SQL功能动词数据定义CREATE, DROP, ALTER

数据操作SELECT, INSERT, DELETE, UPDATE

数据控制GRANT, REVOKE

MySQL三级模式

DDL 数据定义语言数据定义语言(Data Definition Language，DDL)是SQL语言集中负责数据结构定义与数据库对象定义的语言，由CREATE、ALTER与DROP三个语法所组成，最早是由Codasyl(Conference on Data Systems Languages)数据模型开始，现在被纳入SQL指令中作为其中一个子集。

CREATE对象

CREATE操作的对象包括: 数据库表，索引，视图，用户，存储过程，存储函数，触发器，时间调度器等MariaDB [(none)]> help create

Many help items for your request exist.

To make a more specific request, please type 'help ',

where is one of the following

topics:

CREATE DATABASE //数据库

CREATE EVENT //事件

CREATE FUNCTION //函数

CREATE FUNCTION UDF //自定义函数

CREATE INDEX //索引

CREATE PROCEDURE //存储函数

CREATE SERVER //服务器

CREATE TABLE //表

CREATE TABLESPACE //表空间

CREATE TRIGGER //触发器

CREATE USER //用户

CREATE VIEW //视图

SHOW //看CREATE语句过程

SHOW CREATE DATABASE

SHOW CREATE EVENT

SHOW CREATE FUNCTION

SHOW CREATE PROCEDURE

SHOW CREATE TABLE

SPATIAL

语法:MariaDB [(none)]> help create database

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

[create_specification] ...

create_specification:

[DEFAULT] CHARACTER SET [=] charset_name

| [DEFAULT] COLLATE [=] collation_name

实例:

CREATE DATABASE IF NOT EXISTS test_db DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

语法：CREATE TABLE [IF NOT EXISTS] tb_name

(col1_def, col2_def,

PRIMARY KEY(col_name,...),

UNIQUE (col1,...), INDEX (col1,...))

[table_option]

------------------------

table_option:

ENGINE [=] engine_name

COMMENT [=] 'string'

ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]

显示表上的索引：

SHOW INDEXES FROM tb_name;

注意: Storage Engine是指的表类型,也即在表创建时指明其使用的存储引擎:同一个库中要使用同一种存储引擎类型

范例:

CREATE TABLE tb1 (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,name CHAR(5) NOT NULL);

查看表结构：

DESCRIBE tb1_name

查看表状态信息：

SHOW [FULL] TABLE STATUS [{FROM|IN} db_name] [LIKE 'pattern' | WHERE expr]

查看存储引擎：

SELECT @@SESSION.default_storage_engine;

语法：ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name

[alter_specification [, alter_specification] ...]

[partition_options]

alter_specification:

table_options

| ADD [COLUMN] col_name column_definition

[FIRST | AFTER col_name ]

| ADD [COLUMN] (col_name column_definition,...)

| ADD {INDEX|KEY} [index_name]

[index_type] (index_col_name,...) [index_option] ...

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

| CHANGE [COLUMN] old_col_name new_col_name column_definition

[FIRST|AFTER col_name]

| DROP [COLUMN] col_name

......

添加单列：

ALTER TABLE tb_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name]

范例:MariaDB [db1]> ALTER TABLE tb1 ADD COLUMN sex ENUM('man','woman') AFTER name;

Query OK, 0 rows affected (0.10 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB [db1]> DESC tb1;

+-------+---------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| name | char(5) | NO | | NULL | |

| sex | enum('man','woman') | YES | | NULL | |

+-------+---------------------+------+-----+---------+----------------+

删除列：

ALTER TABLE tb_name DROP [column] col_name

范例：MariaDB [db1]> ALTER TABLE tb1 DROP sex;

Query OK, 0 rows affected (0.14 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB [db1]> DESC tb1;

+-------+------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| name | char(5) | NO | | NULL | |

+-------+------------------+------+-----+---------+----------------+

2 rows in set (0.00 sec)

修改列的类型：

ALTER TABLE tb_name MODIFY [COLUMN] col_name column_definitionMariaDB [db1]> ALTER TABLE tb1 MODIFY name CHAR(6);

Query OK, 0 rows affected (0.09 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB [db1]> DESC tb1;

+-------+------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| name | char(6) | YES | | NULL | |

+-------+------------------+------+-----+---------+----------------+

2 rows in set (0.01 sec)

语法：DROP [TEMPORARY] TABLE [IF EXISTS]

tbl_name [, tbl_name] ...

[RESTRICT | CASCADE]RESTRICT：删除表是有限制的欲删除的基本表不能被其他表的约束所引用

如果存在依赖该表的对象，则此表不能被删除

CASCADE: 在删除基本表的同时，相关的依赖对象一起删除

DML 数据操作语言数据操作语言(Data Manipulation Language, DML)包含了数据库数据的增删改查操作，也是我们经常会见到的’CRUD’操作，其主要包括INSERT、SELECT、UPDATE和DELETE四条命令。

INSERT

语法：INSERT [INTO] tbl_name [(col_name [, col_name] ...)]

{VALUES | VALUE} ( value [, value] ...) [, ( value [, value] ...)] ...

//批量插入

INSERT [INTO] tbl_name [(col_name,...)] SELECT ...

INSERT [INTO] tbl_name SET col_name={expr | DEFAULT}, ...

范例：

单行插入：mysql> INSERT INTO tb1 (name) VALUES ('chenfan');

ERROR 1406 (22001): Data too long for column 'name' at row 1

mysql> INSERT INTO tb1 (name) VALUES ('chen');

Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM tb1;

+----+------+

| id | name |

+----+------+

| 1 | chen |

+----+------+

1 row in set (0.00 sec)

插入查询到的数据:mysql> INSERT INTO tb1 (name) SELECT name FROM tb1;

Query OK, 1 row affected (0.06 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM tb1;

+----+------+

| id | name |

+----+------+

| 1 | chen |

| 2 | chen |

+----+------+

2 rows in set (0.00 sec)

UPDATE

语法:UPDATE [LOW_PRIORITY] [IGNORE] table_reference

SET assignment_list

[WHERE where_condition]

[ORDER BY col1[,...] {ASC|DESC}]

[LIMIT row_count]

assignment:

col_name = value

assignment_list:

assignment [, assignment] ...

判断某字段值为空：IS NULL

不空：IS NOT NULL

ASC：升序

DESC：降序

范例：mysql> SELECT * FROM tb1;

+----+------+

| id | name |

+----+------+

| 1 | chen |

| 2 | chen |

+----+------+

2 rows in set (0.00 sec)

mysql> UPDATE tb1 SET name='fan' WHERE id='2' ;

Query OK, 1 row affected (0.10 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM tb1;

+----+------+

| id | name |

+----+------+

| 1 | chen |

| 2 | fan |

+----+------+

2 rows in set (0.00 sec)

DELETE

语法：DELETE FROM tbl_name [WHERE where_condition]

[ORDER BY ...] [LIMIT row_count]

范例：mysql> DELETE FROM tb1 WHERE name='fan';

Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM tb1;

+----+------+

| id | name |

+----+------+

| 1 | chen |

+----+------+

1 row in set (0.00 sec)