1、SQL简介

*结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统，同时也是数据库脚本文件的扩展名

*编写规范

    1) sql语句的所有表名、字段名全部小写，系统保留字、内置函数名、sql保留字大写。

    2) 连接符or、in、and、以及＝、<=、>=等前后加上一个空格。

    3) 对较为复杂的sql语句、过程、函数加上注释，说明算法、功能。

    4) 多表连接时，使用表的别名来引用列

2、DQL\DML语句

1、DDL（数据定义语言）是用来定义数据结构，完成数据库对象的创建（CREATE）、修改（ALTER）、删除（DROP）

数据库对象包括：数据库实例、数据表、视图、索引、约束、触发器、存储过程

2、创建数据库语法：CREATE DATABASE 数据库名称、查看已有的数据库：SHOW DATABASES、使用已创建的数据库：USE 数据库名称

在使用CREATE语句创建数据库实例时，要求满足数据库命名规范：数据库名称不能重复、数据库命名可以使用字母、数字和下划线，不能出现其他字符、数据库命名不能超过30个字符、数据库命名不能与系统关键字冲突、数据库命名尽量有意义，比如体现数据库属于哪个系统使用

3、创建数据库时，有时为了避免乱码，需要指定字符集

默认使用的latin1字符集，语法：CREATE DATABASE 数据库名    DEFAULT CHARACTER SET 字符集 COLLATE 校对集

例子：CREATE DATABASE test_db DEFAULT CHARACTER SET utf8 COLLATE utf8_generel_ci;

查看MySQL支持的字符集命令：SHOW CHARACTER SET、查看MySQL字符集支持的校对集命令：SHOW COLLATION

MySQL对字符集的支持分为四个层次：服务器、数据库、数据表、连接

查看各层级字符集命令: SHOW VARIABLES LIKE '%char%';查看校对集命令： SHOW VARIABLES LIKE '%collation%';

4、创建数据表

语法：CREATE TABLE 表名(属性名称 数据类型[约束],……)

查看已有的数据表命令：SHOW TABLES、查看已创建的数据表属性命令：DESC 表名，例子：

创建数据表时，也需要遵循命名规范，规范和数据库名称相同

5、MySQL数据库支持的数据类型：整型、浮点型、字符串、日期和时间

其他数据类型：enum数据类型、set数据类型

当数据表中的属性定义为这两种类型时，代表只能从指定的集合中取值；它们之间的区别是：enum类型只能取一个值，set类型可以取多个值。例子：

enum数据类型定义的集合最多允许有65535个成员值、set数据类型定义的集合最多允许有64个成员值

6、数据表约束主要有：

1）主键约束（PRIMARY KEY）：通过数据表中的一个或多个属性能够标识表中的唯一一行记录，这一个或多个属性称为该表的主键

主键约束体现关系数据库的实体完整性：定义为主键的属性不允许为空、定义为主键的属性不允许输入重复值、一个表只能有一个主键、定义主键约束时，会在主键列自动创建索引，加快对主键的查询速度；如果主键由多个属性组成，则必须定义为表级约束

2）外键约束（FOREIGN KEY）：建立和强调两个表之间的关联，确保数据的完整性和一致性

例：A表中的属性是另外一个B表中能够唯一确定一行记录的键，这个属性就称为A表的外键，其中B表为主表或外表，A表为从表

外键约束体现关系数据库的参照完整性：

-主表中被子表外键关联的属性进行更新或删除操作，会关联子表中的外键列

-子表中外键列输入的数据必须是主表关联属性中已有的值

-主表中被外键关联的属性必须建立索引，因此一般主表的主键或唯一键可以作为从表的外键关联属性

-子表中的外键与主表中的关联属性数据类型要一致

-定义外键约束的表类型必须是InnoDB，只有InnoDB类型的表才支持外键

-对于非InnoDB表，外键约束会被忽略

FOREIGN KEY指定test_table1表中的属性name为外键约束

REFERENCES关联父表test_table2中的主键属性n

ON DELETE、ON UPDATE指明父表中对主键进行删除、更新操作，不指定则表示不能进行删除、更新操作

CASCADE表明子表中外键字段值也会被更新或删除，除了CASCADE，还可以使用SET NULL（父表更新或删除时，子表字段值设置为NULL）、NO ACTION（父表更新或删除时，子表不进行任何操作）

3）唯一约束（UNIQUE）：确保不是主键的属性不会出现重复数据；定义唯一约束时，也会自动创建唯一索引

唯一约束与主键约束的区别：

一个表只能定义一个主键约束，但是可以定义超过1个唯一性约束；主键约束不允许属性值为空，而唯一性约束的属性允许为空

4）非空约束（NOT NULL）、空值约束（NULL）           

5）默认值约束（DEFAULT）：即向表中插入数据时，如果用户没有明确给出属性的值，数据库会自动为该属性添加默认值

 6）AUTO_INCREMENT：当属性列定义为AUTO_INCREMENT时，能够为新插入的行赋值为上一次插入的值+1

MySQL要求AUTO_INCREMENT只能用于作为主键的列

数据表中的属性可以同时定义多个约束，但是需要注意：

如果同一个属性有默认值约束和其他约束，默认值要写在其他约束前面；如果同一个属性有多个约束，中间使用空格分隔开

在其他数据库管理工具中，如SQL Server还支持检查约束（check），但是MySQL数据库不支持，如果需要可以通过enum枚举类型或者触发器来实现检查

在MySQL数据库中创建数据表的同时，也可以指定表类型和字符集（语法：CREATE TABLE 表名(……)[ENGINE=表类型][DEFAULT CHARSET=字符集]）

字符集：和数据库支持的字符集相同     表类型：在MySQL数据库中常用的表类型有2个-InnoDB、MyISAM

默认情况下创建的表类型是InnoDB

两种常用的表类型各有优劣，主要区别：

MyISAM类型强调性能，执行速度比InnoDB更快， 但不支持事务等高级处理；InnoDB类型支持事务、外键等高级数据库功能

7、修改数据库（使用alter命令来完成数据库的修改，一般是修改数据库的字符集和校对集）

语法：ALTER DATABASE 数据库名称                                                           DEFAULT CHARACTER 字符集 COLLATE 校对集

8、修改数据表

新增属性，语法： ALTER TABLE 表名 ADD 属性名称 数据类型[约束]

例子： ALTER TABLE test_table ADD name VARCHAR(20) NOT NULL;

修改属性，语法： ALTER TABLE 表名 MODIFY 属性名称 数据类型[约束]

语法： ALTER TABLE 表名 CHANGE 属性名称 属性名称 数据类型[约束]

例子： ALTER TABLE test_table MODIFY name VARCHAR(20);       ALTER TABLE test_table CHANGE name name VARCHAR(20);

9、修改属性名称，语法： ALTER TABLE 表名 CHANGE 属性原名称 属性新名称 数据类型[约束]

例子： ALTER TABLE test_table CHANGE name name_new VARCHAR(20);

删除属性，语法：ALTER TABLE 表名 DROP 属性名称

例子：ALTER TABLE test_table DROP name;如果表中只有一个属性，不允许删除

10、新增属性约束，语法：ALTER TABLE 表名 ADD CONSTRAINT 约束别名 约束

例子：ALTER TABLE test_table ADD CONSTRAINT PRIMARY KEY(id);

ALTER TABLE test_table ADD CONSTRAINT fk FOREIGN KEY test_table(name) REFERENCES test_table2(name);

ALTER TABLE test_table ADD CONSTRAINT un UNIQUE(name);          ALTER TABLE test_table ALTER id SET DEFAULT 20;

删除属性约束，语法：ALTER TABLE 表名 DROP 约束

例子： ALTER TABLE test_table DROP PRIMARY KEY;                          ALTER TABLE test_table DROP FOREIGN KEY fk;

11、修改表名，语法：ALTER TABLE 表名 RENAME TO 新表名            语法：RENAME TABLE 原表名 TO 新表名

例子：ALTER TABLE test_table RENAME TO test_table_new;               RENAME TABLE test_table TO test_table_new;

12、删除数据库

语法：DROP DATABASE 数据库名称                                                       例子： DROP DATABASE test_db;

删除数据表

语法：DROP TABLE 表名                                                                         例子： DROP TABLE test_table;

删除数据库、数据表时，如果数据库、数据表不存在，命令执行会出错

当不确定数据库、数据表是否存在时，可以增加控制流语句来判断，避免命令出错

例子： DROP DATABASE IF EXISTS test_db;                                        DROP TABLE IF EXISTS test_table;

IF EXISTS或者IF NOT EXISTS同样可以用于数据库、数据表的创建

DML（数据操纵语言）是用于维护数据表中的具体数据，完成数据的增加（INSERT）、更新（UPDATE）、删除（DELETE）

13、数据插入，语法：INSERT INTO 表名(属性1,属性2,……) VALUES(值1,值2,……)       

例子：INSERT INTO test_table(id,name)VALUES(1,'zhangsan');

向数据表中插入数据时，值与属性要一一对应，且数据类型要匹配；还需要注意属性的约束要满足；当使用INSERT语句向表中插入数据时，如果不指定具体属性，则表示向所有属性中插入数据

例子： INSERT INTO test_table VALUES(1,'zhangsan');

特殊用法：将一张表中的数据插入到另外一张表；语法：INSERT INTO 表名1(属性1,….) SELECT 表名2.属性1,…. FROM 表名2

例子： INSERT INTO test_table1 SELECT id,name FROM test_table;

需要注意，在当前这种用法中，SELECT语句查询到的属性值和向表1中插入的属性要一一对应，且数据类型要相同，属性约束要满足

14、数据更新

语法：UPDATE 表名 SET 属性1=新值1,属性2=新值2,..[WHERE条件子句]；例子： UPDATE test_table SET name='lisi' WHERE id=1

UPDATE语句会将数据表中满足WHERE条件的对应元组的属性1、属性2…更新为新的值

如果后面不接WHERE条件子句，则会更新整张表的所有数据

15、数据删除

语法：DELETE FROM 表名 [WHERE条件子句]                                      例子： DELETE FROM test_table WHERE id=1;

DELETE语句会将数据表中满足WHERE条件的对应元组删除；如果后面不接WHERE条件子句，则会删除数据表中的所有数据

另外一种删除数据的方法，语法：TRUNCATE TABLE 表名                    例子： TRUNCATE TABLE test_table;

TRUNCATE和DELETE的区别：

-DELETE可以通过WHERE条件选择要删除的部分数据，并返回被删除的记录数，使用灵活，但执行速度不快

-TRUNCATE不能删除指定的部分数据，而且不会返回被删除的记录数，但执行速度非常快