MySQL数据库

SQL具有数据库管理系统的所有功能,主要功能如下:数据定义语言、数据操作语言和数据控制语言

创建数据库 create database database_name；

查看数据库 SHOW  DATABASES；

选择数据库  use database_name;

删除数据库 DROP DATABASE database_name；

 

创建表 create table table_name；

查看表结构 describe table_name;

查看表定义SHOW CREATE TABLE table_name;

删除表    DROP TABLE table_name;

修改表名 ALTER TABLE old_table_name RENAME [TO] new_table_name；

增加字段 ALTER TABLE table_name ADD属性名 属性类型；

在表的第一位置增加字段 ALTER TABLE table_name ADD属性名 属性类型FIRST;

在表的指定字段后增加字段 ALTER TABLE table_name ADD属性 属性类型 AFTER 属性名；

删除字段ALTER TABLE table_name DROP属性；

修改字段的数据类型 ALTER TABLE table_name MODIFY 属性名 数据类型；

修改字段名字 ALTER TABLE table_name CHANGE 旧属性名 新属性名 旧数据类型；

同时修改字段的名字和属性 ALTER TABLE table_name CHANGE 旧属性名 新属性名 新数据类型；

修改字段的顺序 ALTER TABLE table_name MODIFY 属性名1数据类型 FIRST|AFTER 属性名2；

 

查询默认存储引擎

SHOW VARIABLES LIKE 'storage_engine%';

数据类型

整数类型	宇节	最小值	最大值
TINYINT	1	有符号-128 无符号0	有符号127 无符号255
SMALUNT	2	有符号-32768 无符号0	有符号32767 无符号65535
MEDIUMINT	3	有符号-8388608 无符号0	有符号8388607 无符号1677215
INT 和INTEGER	4	有符号-2147483648 无符号0	有符号 2147483647 无符号 4294967295
BIGINT	8	有符号-922337203685477580无符号0	有符号9223372036854775807  无符号18446744073709551615

浮点数类型	字节	最小值	m大值
FLOAT	4	± 1.7S4943S1E - 38	±3.402823466E+38
DOUBLE	8	± 2.22S073SS8S072014E - 308	± 1.797693I348623157E + 308

定点数类型	字节	最小值	最大值
DEC（M,D）	M+2	与double相同	与double相同

日期和时间类型	字节	最小值	•大值
DATE	4	1000-01-01	9999-12-31
DATETIME	8	1000-01~0l 00:00:00	9999-12-31 23:59:59
TIMESTAMP	4	19700101080001	2038年的某个时刻
TIME	3	-835:59:59	838:59:59
YEAR	1	1901	2155

char系列字符串类型	字节	描述
CHAR（M）	M	M为0-255之间的整数
VARCHAR（M）	M	M为0-65535之间的整数

TEXT系列字符串类型	字节	描述
TINYTEXT	0-255	值的长度为+2个宇节
TEXT	0-65535	值的长度为+2个字节
MEDIUMTEXT	0-167 772 150	值的长度为+3个字节
LONGTEXT	0~4 294 967 295	值的长度为+4个字节

binary系列字符串类型	字节	描述
Binary（M）	M	允许长度为0~M
Varbinary（M）	M	允许长度为0~M

BLOB系列字符串类型	字节
TINYBLOB	0-255
BLOB	0-216
MEDIUMBLOB	0-224
LONGBLOB	0-232

MySQL支持的完整性约束

完整性约束关键字	含义
Not null	约束字段的值不能为空
Default	设置字段的默认值
Unique key(uk)	约束字段的值是唯一
Primary key（pk）	约束字段为表的主键，可以作为该表记录的唯一标识
Auto_increment	约束字段的值自动增加
Foreign key(fk)	约束字段为表的外键

CFEATE TABLE table_name (属性名 数据类型NOT NULL,……);

CREATE TABLE table_name (属性名 数据类型DEFAULT默认值，……);

CREATE TABLE table_name (属性名 数据类型UNIQUE,……);

CREATE TABLE table_name (属性名 数据类型AUTO_INCREMENT,……);

CREATE TABLE table_name （属性名 数据类型 PRIMARY KEY,……）;

CREATE TABLE table_name (属性 数据类型，……【CONSTRAINT 约束名】PRIMARY KEY(属性名);

CREATE TABLE table_name (属性名 数据类型,属性名 数据类型……【CONSTRAINT 外键约束名】 FOREIGN KEY(属性名1 ) REFERENCES 表名 （属性名2）)；

Alter table 表名  add constraint 外键名 foreign key(添加外键的字段) references bank(目标字段);

 

ON DELETE CASCADE 删除主表中的数据时，从表中的数据随之删除

ON UPDATE CASCADE 更新主表中的数据时，从表中的数据随之更新

ON DELETE SET NULL 删除主表中的数据时，从表中的数据置为空

默认：删除主表中的数据前需先删除从表中的数据，否则主表数据不会被删除

插入数据 INSERT INTO  表名  [ ( 字段1, 字段2, 字段3, … ) ]  VALUES  (  '值1', '值2', '值3', …)

更新数据记录 UPDATE table_name SET field=value  [WHERE CONDITION]；

删除特定数据 DELETE FROM table_name [WHERE CONDTTTON]；

删除所有数据   TRUNCATE table_name ；

where条件句中的运算符

运算符	含义	范例	结果
=	等于	5=6	false
<>或!=	不等于	5!=6	true
>	大于	5>6	false
<	小于	5<6	true
>=	大于等于	5>=6	false
<=	小于等于	5<=6	true
BETWEEN	在某个范围之间	BETWEEN 5 AND 10	-
AND	并且	5>1 AND 1>2	false
OR	或	5>1 OR 1>2	true

单标数据查询

• 简单数据记录查询；

Select * from table_name

SELECT field1 field2 ...fieldn FROM table_name；

• 条件数据记录查询；

Select  field1 field2 ……fieldn from table_name where CONDITION;

在上述语句中通过参数CONDITION对数据进行条件查询，用于检索数据表中符合条件的记录。关于条件数据查询语句可以包含如下功能：

带关系运算符和逻辑运算符的条件数据査询。

带BETWEEN AND关键字的条件数据査询。

带is null关键字的条件数据查询。

带IN关键字的条件数据查询。

带LIKE关键字的条件数据査询。

• 排序数据记录查询；

SELECT field1 field2 ...fieldn FROM table_name WHERE CONDITION ORDER BY fileldm1 [ASC|DESC] [,fileldm2 [ASC|DESC]；

按照单字段排序。

按照多字段排序。

按照表达式排序，按重命名排序。

• 统计函数和分组数据记录查询。

COUNT函数：该统计函数实现统计表中记录的条数。

AVG函数：该统计函数实现计算字段值的平均值。

SUM函数：该统计函数实现计算字段值的总和。

MAX函数：该统计函数实现査询字段值的最大值。

MIN函数：该统计函数实现査询字段值的最小值。

SELECT function() FROM table_name WHERE CONDITION GROUP BY field;

SELECT GROUP_CONCAT (field) , function (field) FROM table_name WHERE CONDITION

GROUP BY fieldl，field2,……fileldn;

    在上述语句中，首先会按照字段field1进行分组，然后针对每组按照字段field2进行分组，以此类推。

• 限制数据记录查询数量；

SELECT field1 field2 ……fieldn FROM table_name WHERE CONDITION

LIMIT OFFSET_START,ROW_COUNT;

 

Select  [all|distinct] 属性列表

from 表名

[left|right|inner join 表名]     ---------------------多表联合查询

[where 条件表达式1] ---------------------指定结果需满足的条件

[grou by 属性名1 [having 条件表达式2]] ---------------指定结果按照哪个字段来分组

[order by 属性名2 [asc|desc]] ----------------------指定查询结果记录按一个或多个字段排序

[limit 数量] ----------------------指定返回记录的条数

 

• 内连接
  • • select 表别名.*/列名，表别名.*/列名…….
    • From 表1 表别名1 inner join表2 表别名2
    • On 连接条件
    • 等价于where的连接查询
• 左外连接查询（左连接）
  • • select 表别名.*/表别名.*…….
    • From 表1 别名1 left (outer)join 表2 别名2
    • On  连接条件
    •  

• 右连接
  • • select 表别名.*/表别名.*…….
    • From 表1 别名1 right (outer) join表2 别名2
    • On  连接条件

索引分类：

主键索引 （primary）

普通索引 （index）

唯一索引 （unique）

全文索引 （fulltext）

创建表时创建唯一索引

CREATE TABLE table_name (属性名 数据类型，属性名 数据类型，……属性名 数据类型UNIQUE INDEX丨KEY【索引名】(属性名1【（长度）】【ASC丨DESC】))；

在已经存在的表上创建普通索引

CREATE INDEX 索引名 ON 表名（属性【（长度)】【ASC|DESC】）;

通过SQL语句ALTER TABLE创建普通索引

ALTER TABLE table_name ADD INDEX | KEY  索引名（属性名【（长度）】【ASC|DESC】）；

创建表时创建全文索引

CREATE TABLE table_name (属性名 数据类型，属性名 数据类型，……

属性名 数据类型，

FULLTEXT INDEX|KEY【索引名】（属性名1【(长度)】【ASCIDESC】）)；

在已经存在的表上创建全文索引

CREATE FULLTEXT INDEX 索引名ON表名 (属性名【（长度）】【ASC|DESC】)；

通过SQL语句ALTER TABLE创建全文索引

ALTER TABLE table_name ADD FULLTEXT INDEX|KEY索引名（属性名【（长度)】【ASC|DESC】）；

Mysql各种索引区别：
普通索引：最基本的索引，没有任何限制
唯一索引：与“普通索引”类似，不同的就是：索引列的值必须唯一，但允许有空值。
主键索引：它 是一种特殊的唯一索引，不允许有空值。 
全文索引：主要用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间

全文索引是用于检索字段中是否包含或不包含指定的关键字

什么是存储过程?

在MySQL中创建存储过程通过SQL语句CREATE PROCEDURE来实现，其语法形式如下：

CREATE PROCEDURE procedure_name([procedure_parameter[,…]])

begin

[characteristic…]

end

   在上述语句中，procedure_name参数表示所要创建的存储过程名字，procedure_parameter参数表示存储过程的参数，characteristic参数表示存储过程的特性，routine_body参数表示存储过程的SQL 语句代码，可以用BEGlN...END来标志SQL语句的开始和结束。

    procedure_parameter中每个参数的语法形式如下：

    [IN|OUT|INOUT] parameter_name type

    在上述语句中，每个参数由3部分组成，分别为输入输出类型、参数名和参数类型。其中输入/ 输出类型有3种类型，分别为IN：表示输入类型；OUT：表示输出类型；INOUT表示输入/输出类型。parameter_name表示参数名。type表示参数类型，可以是MySQL软件所支持的任意一个数据类型。

characteristic参数的取值为:

LANGUAGE SQL

|[NOT] DETERMINISTIC

|{CONTAINS SQL|NO SQL|READS SQL DATA | MODIFIES SQL DATA}

|SQL SECURITY {DEFINER|INVOKER}

|COMMENT 'string'

  当characteristic参数的值为上述各个值时,分别表不：

•  LANGUAGE SQL，表示存储过程的部分由SQL语言的语句组成，为MySQL 软件所有默认的语句。

• [NOT] DETERMINISTIC，表示存储过程的执行结果是否确定。如果值为DETERMINISTIC，表示执行结果是确定的。即每次执行存储过程时，如果输入相同的参数将得到相同的输出； 如果值为NOT DETERMNISTIC，表示执行结果不确定，即相同的输入可能得到不同的输出。默认值为DETERMINISTIC。

• {CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}，表示使用SQL语句的限制。如果值为CONTAINS SQL表示可以包含SQL语句，但不包含写数据的语句;如果值为NO SQL表示不包含SQL语句;如果值为READS SQL DATA表示包含读数据的语句；如果值为 MODIFIES SQL DATA 表示包含写数据的语句。默认值为CONTAINS SQL。

•  SQL SECURITY （DEFINER|INVOKER）,设置谁有权限来执行。如果值为DEFINER，表示只有定义者自己才能够执行；如果值为INVOKER表示调用者可以执行。默认值为DEFINER。

•  COMMENT 'string',表示注释语句。

 

1.声明变量

    在MySQL中定义变量通过关键字DECLARE来实现，其语法形式如下：

DECLARE var_name[, …] type [DEFAULT value]

    在上述语句中，var_name参数表示所要声明的变量名字；参数TYPE表示所要声明变量的类型; DEFAULT value用来实现设置变量的默认值，如果无，该语句默认值为NULL。

2.赋值变量

    变量赋值通过关键字SET来实现，其语法形式如下：

SET var_name=expr[,…]           

      @用户变量            全局变量：定义时，以如下两种形式出现，set GLOBAL 变量名  或者  set @@global.变量名

    在上述语句中，var_name参数表示所要赋值变量名字；参数expr是关于变量的赋值表达式。

   为变量赋值时，除了上述语法外，还可以通过关键字“SELECT...INTO”语句来实现，其语法形式如下：

SELECT field_name[,...] INTO var_name[,…] FROM table_name WHERE condition;

    在上述语句中将查询到结果赋值给变量，参数field_name表示查询的字段名，参数var_name 参数表示变量名。

    注意：当将查询结果赋值给变量时，该查询语句的返回结果只能是单行。

 

使用流程控制

search_condition THEN statement_list

[ELSEIF search_condition THEN statement_list]…

[ELSE search_condition]

END IF

    在MySQL中实现条件判断还可以通过关键字CASE来实现，其语法形式如下：

CASE case_value

WHEN when_value THEN statement_list

[WHEN when_value THEN statement list]...

[ELSE statement_list]

END CASE

2.循环控制语句

    在MySQL软件中可以通过关键字LOOP、WHILE和REPEAT来实现循环控制语句，其中后两个关键字用来实现带有条件的循环控制语句，即对于关键字WHILE，只有在满足条件的基础上才执行循环体，而关键字REPEAT，则是在满足的条件时退出循环体。

MySQL实现循环执行通过关键字LOOP来实现，其语法形式如下：

[begin_label:] LOOP       LOOP只是创建了一个循环的过程，并不进行判断，需要结合leave语句终止循环

statement_list

END LOOP

 

    在MySQL中实现循环执行还可以通过关键字WHILE来实现，不过其是带有条件控制的循环，即当满足条件是才执行循环体语句，具体语法形式如下：

WHILE search_condition DO

statement_list

END WHILE

    还可以通过关键字REPEAT来实现，其同样也是带有条件控制的循环，不过当满足条件则跳出循环体

语句，具体语法形式如下：

[begin_label:] REPEAT search_condition 

statement_list

END REPEAT

注意：该循环开始执行的时候，会先执行一次循环体，之后在判断循环条件是否为真，

如果条件为假则跳出循环，否则继续执行循环。

  在MySQL数据库管理系统中修改存储过程通过SQL语句ALTER PROCEDURE来实现，其语法形式如下：

ALTER PROCEDURE procedure_name [characteristic…]

  在上述语句中，procedure_name参数表示所要修改存储过程的名字，而characteristic参数指定修改后存储过程的特性，与定义存储过程的该参数相比，取值只能是如下值：

|{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}

|SQL SECURITY {DEFINER|INVOKER}

|COMMENT 'string'

 

    在MySQL软件中可以通过两种方式来删除存储过程和函数，分别为通过drop procedure/frunction语句和通过工具实现删除存储过程和函数。

（1）在MySQL中删除存储过程通过SQL语句DROP PROCEDURE来实现，其语法形式如下：

DROP PROCEDURE proce_name;

    在上述语句中，关键字DROP PROCEDURE用来表示实现删除存储过程，proce_name参数表示所要删除的存储过程名称。

 

触发器

    MySQL软件在触发如下语句时，就会自动执行所设罝的操作：

• DELETE语句。

• INSERT语句。

• UPDATE语句。
 

 

    MySQL中创建触发器通过SQL语句CREATE TRIGGER来实现，其语法形式如下：

create trigger trigger_name

BEFORE|AFTER trigger_EVENT ON TABLE_NAME FOR EACH ROW

trigger_STMT；

    MySQL中创建触发器通过SQL语句CREATE TRIGGER来实现，其语法形式如下：

create trigger trigger_name

BEFORE|AFTER trigger_EVENT ON TABLE_NAME FOR EACH ROW

BEGIN

trigger_STMT1；

trigger_STMT2；

END$$

new 关键字  与 old关键字

在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；

在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；

在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；

那么如何查看MySQL软件中已经存在的触发器呢？在MySQL软件中査看已经存在触发器，通过SQL语句SHOW TRIGGERS来实现，其语法形式如下：

SHOW TRIGGERS \G

 

通过drop trigger语句删除触发器

DROP TRIGGER trigger_name；

    在上述语句中，trigger_name参数表示所要删除的触发器名称。

 

什么是事务?

    MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

注意：

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

事务用来管理 insert,update,delete 语句

需要对数据表执行一系列多个操作的情况下，为了防止这些操作中的部分操作执行成功而另一些操作执行失败，从而导致数据不正确，我们就需要使用事务了。

例如有数据表团队和团员两张表，团员表每增加一个团员时，团队表相应团号下的总人数就需要同时增加一人，这样我们就需要分别执行追加和更新两样操作。只有两样操作都同时执行成功才能确保数据记录处在正确的状态。现实的情况是，由于分开操作因种种原因很可能会发生第一个操作执行成功而第二个操作失败的状态，如果使用事务就就可以在系列操作不能全部成功的情况下将已经执行的操作回滚，从而防止中间状态的发生所导致的数据不正确。

事务的原则

1、事务的原子性：一组事务，要么成功；要么撤回。

2、稳定性 ：数据库在事务执行前后状态都必须是稳定的。 有非法数据（外键约束之类），事务撤回。

3、隔离性：通过锁机制实现，事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。

4、可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改

创建事务

SET AUTOCOMMIT

    使用SET语句来改变自动提交模式

SET AUTOCOMMIT  = 0;    # 关闭自动提交模式

 

SET AUTOCOMMIT  = 1;    # 开启自动提交模式

 

注意：

Mysql默认是自动提交

使用事务时先关闭自动提交

SHOW TABLES语句查看视图名

START  TRANSACTION

开始一个事务，标记事务的起点

COMMIT

提交一个事务给数据库

ROLLBACK

将事务回滚，数据回到本次的厨师状态

SET  AUTOCOMMIT  =  1;

还原mysql数据哭的自动提交