SQL是什么?

SQL是结构化查询语言，这是一种计算机语言，用于存储，操纵和检索存储在关系数据库中的数据。

SQL是关系数据库系统的标准语言。所有关系型数据库管理系统，如MySQL, MS Access, Oracle, Sybase, Informix, postgres 和SQL Server使用SQL作为标准数据库语言。

此外，它们也使用不同的方言，如：

MS SQL Server 使用 T-SQL,

Oracle 使用 PL/SQL,

MS Access 的SQL版本叫 JET SQL (本地格式) 等

为什么使用SQL?

允许用户访问在关系数据库管理系统的数据。

让用户来描述数据。

允许用户定义数据库中的数据和处理数据。

允许使用SQL模块，库和预编译器的其他语言中嵌入。

允许用户创建和删除数据库和表。

允许用户创建视图，存储过程，函数在数据库中。

允许用户设置表，过程和视图的权限

历史:

1970 -- Dr. Edgar F. "Ted" IBM的科德被称为关系数据库之父，是他描述了数据库的关系模型。

1974 -- 结构化查询语言出现。

1978 -- IBM合作开发Codd的想法并发布了名为System/R的产品。

1986 -- IBM开发了关系型数据库的第一台样机，并通过ANSI标准化。第一个关系型数据库是由关系型软件及其后来成为甲骨文发布。

SQL处理:

当你对任何RDBMS执行SQL命令，系统决定开展您的要求的最佳途径和SQL引擎计算出如何解析任务。

有包括在过程中的各种组件。这些组件查询调度，优化引擎，经典查询引擎和SQL查询引擎等等。经典查询引擎处理所有非SQL查询，但SQL查询引擎不会处理逻辑文件。

以下是显示SQL架构一个简单的图表：







sql命令

标准的SQL命令进行互动使用在关系型数据库有：CREATE, SELECT, INSERT, UPDATE, DELETE 和 DROP。这些命令可分为基于其性质组。

DDL - 数据定义语言

命令描述CREATE创建一个新的表，表的视图，或者在数据库中的对象ALTER修改现有的数据库对象，例如一个表DROP删除整个表，数据库中的表或其他对象或视图

DML - 数据操纵语言

命令描述SELECT从一个或多个表中检索特定的记录INSERT创建记录UPDATE修改记录DELETE删除记录

DCL - 数据控制语言

命令描述GRANT授予用户权限REVOKE收回用户授予的权限

MySQL数据类型

1、整型

MySQL数据类型含义（有符号）tinyint(m)1个字节 范围(-128~127)smallint(m)2个字节 范围(-32768~32767)mediumint(m)3个字节 范围(-8388608~8388607)int(m)4个字节 范围(-2147483648~2147483647)bigint(m)8个字节 范围(+-9.22*10的18次方)

取值范围如果加了unsigned，则最大值翻倍，如tinyint unsigned的取值范围为(0~256)。
int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，没有影响到显示的宽度，不知道这个m有什么用。

2、浮点型(float和double)

MySQL数据类型含义float(m,d)单精度浮点型 8位精度(4字节) m总个数，d小数位double(m,d)双精度浮点型 16位精度(8字节) m总个数，d小数位

设一个字段定义为float(5,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位。

3、定点数

浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。
decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位。

4、字符串(char,varchar,_text)

MySQL数据类型含义char(n)固定长度，最多255个字符varchar(n)固定长度，最多65535个字符tinytext可变长度，最多255个字符text可变长度，最多65535个字符mediumtext可变长度，最多2的24次方-1个字符longtext可变长度，最多2的32次方-1个字符

char和varchar：
1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。
2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，所以varchar(4),存入3个字符将占用4个字节。
3.char类型的字符串检索速度要比varchar类型的快。

varchar和text：
1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，text是实际字符数+2个字节。
2.text类型不能有默认值。
3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text,在都创建索引的情况下，text的索引似乎不起作用。

5.二进制数据(_Blob)

1._BLOB和_text存储方式不同，_TEXT以文本方式存储，英文存储区分大小写，而_Blob是以二进制方式存储，不分大小写。
2._BLOB存储的数据只能整体读出。
3._TEXT可以指定字符集，_BLO不用指定字符集。

6.日期时间类型

MySQL数据类型含义date日期 '2008-12-2'time时间 '12:25:36'datetime日期时间 '2008-12-2 22:06:44'timestamp自动存储记录修改时间

若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间。

数据类型的属性

MySQL关键字含义NULL数据列可包含NULL值NOT NULL数据列不允许包含NULL值DEFAULT默认值PRIMARY KEY主键AUTO_INCREMENT自动递增，适用于整数类型UNSIGNED无符号CHARACTER SET name指定一个字符集




数据库操作

创建数据库

要在MySQL中创建数据库，请使用CREATE DATABASE语句，如下：

CREATEDATABASE[IFNOTEXISTS]database_name;

我们来更详细地看看CREATE DATABASE语句：

CREATE DATABASE语句的后面是要创建的数据库名称。建议数据库名称尽可能是有意义和具有一定的描述性。

IF NOT EXISTS是语句的可选子句。 IF NOT EXISTS子句可防止创建数据库服务器中已存在的新数据库的错误。不能在MySQL数据库服务器中具有相同名称的数据库。

例如，要创建一个名称为mytestdb数据库，可以执行CREATE DATABASE语句后接数据库名称：mytestdb，如果当前MySQL服务器中没有数据库：mytestdb，则创建成功，如下所示：

CREATEDATABASEIFNOTEXISTSmytestdb;

执行此语句后，MySQL返回一条消息，通知新数据库是否已成功创建。

显示数据库

SHOW DATABASES语句显示MySQL数据库服务器中的所有数据库。您可以使用SHOW DATABASES语句来查看您要创建的数据库，或者在创建新数据库之前查看数据库服务器上的所有数据库，例如：

+--------------------+|Database|+--------------------+|information_schema||mysql||performance_schema||testdb||yiibaidb|+--------------------+5rowsinset

在此MySQL数据库服务器中有6个数据库。 information_schema，performance_schema和mysql是我们安装MySQL时可用的默认数据库，而yiibaidb是创建的新数据库。

选择要使用的数据库

在使用指定数据库之前，必须通过使用USE语句告诉MySQL要使用哪个数据库。

USEdatabase_name;

您可以使用USE语句选择示例数据库(yiibaidb)，如下所示：

USEyiibaidb;

从现在开始，所有操作(如查询数据，创建新表或调用存储过程)都将对当前数据库(即yiibaidb)产生影响。

删除数据库

删除数据库意味着数据库中的所有数据和关联对象将被永久删除，并且无法撤消。 因此，用额外的注意事项执行此查询是非常重要的。

要删除数据库，请使用DROP DATABASE语句，如下所示：

DROPDATABASE[IFEXISTS]database_name;

遵循DROP DATABASE是要删除的数据库名称。 与CREATE DATABASE语句类似，IF EXISTS是该语句的可选部分，以防止您删除数据库服务器中不存在的数据库。

如果要使用DROP DATABASE语句练习，可以创建一个新数据库，然后将其删除。来看下面的查询：

CREATEDATABASEIFNOTEXISTStempdb;SHOWDATABASES;DROPDATABASEIFEXISTStempdb;

三个语句的说明如下：

首先，使用CREATE DATABASE语句创建了一个名为tempdb的数据库。

第二，使用SHOW DATABASES语句显示所有数据库。

第三，使用DROP DATABASE语句删除了名为tempdb的数据库。

数据表操作

MySQL CREATE TABLE语法

要在数据库中创建一个新表，可以使用MySQL CREATE TABLE语句。 CREATE TABLE语句是MySQL中最复杂的语句之一。

下面以简单的形式来说明CREATE TABLE语句的语法：

CREATETABLE[IFNOTEXISTS]table_name(column_list)engine=table_type;

我们来更详细地来查看其语法：

首先，指定要在CREATE TABLE子句之后创建的表的名称。表名在数据库中必须是唯一的。 IF NOT EXISTS是语句的可选部分，允许您检查正在创建的表是否已存在于数据库中。 如果是这种情况，MySQL将忽略整个语句，不会创建任何新的表。 强烈建议在每个CREATE TABLE语句中使用IF NOT EXISTS来防止创建已存在的新表而产生错误。

其次，在column_list部分指定表的列表。字段的列用逗号(，)分隔。我们将在下一节中向您展示如何更详细地列(字段)定义。

第三，需要为engine子句中的表指定存储引擎。可以使用任何存储引擎，如：InnoDB，MyISAM，HEAP，EXAMPLE，CSV，ARCHIVE，MERGE， FEDERATED或NDBCLUSTER。如果不明确声明存储引擎，MySQL将默认使用InnoDB。

注：InnoDB自MySQL 5.5之后成为默认存储引擎。 InnoDB表类型带来了诸如ACID事务，引用完整性和崩溃恢复等关系数据库管理系统的诸多好处。在以前的版本中，MySQL使用MyISAM作为默认存储引擎。

要在CREATE TABLE语句中为表定义列，请使用以下语法：

column_namedata_type[size][NOTNULL|NULL][DEFAULTvalue][AUTO_INCREMENT]

以上语法中最重要的组成部分是：

column_name指定列的名称。每列具有特定数据类型和大小，例如：VARCHAR(255)。

NOT NULL或NULL表示该列是否接受NULL值。

DEFAULT值用于指定列的默认值。

AUTO_INCREMENT指示每当将新行插入到表中时，列的值会自动增加。每个表都有一个且只有一个AUTO_INCREMENT列。

如果要将表的特定列设置为主键，则使用以下语法：

PRIMARYKEY(col1,col2,...)

MySQL CREATE TABLE语句示例

下面让我们练习一个例子，在示例数据库(testdb)中创建一个名为tasks的新表，如下所示：

可以使用CREATE TABLE语句创建这个tasks表，如下所示：

CREATETABLEIFNOTEXISTStasks(task_idINT(11)NOTNULLAUTO_INCREMENT,subjectVARCHAR(45)DEFAULTNULL,start_dateDATEDEFAULTNULL,end_dateDATEDEFAULTNULL,descriptionVARCHAR(200)DEFAULTNULL,PRIMARYKEY(task_id))ENGINE=InnoDB;

MySQL ALTER TABLE语句简介

可以使用ALTER TABLE语句来更改现有表的结构。 ALTER TABLE语句可用来添加列，删除列，更改列的数据类型，添加主键，重命名表等等。 以下说明了ALTER TABLE语句语法：

ALTERTABLEtable_nameaction1[,action2,…]

要更改现有表的结构：

首先，在ALTER TABLE子句之后指定要更改的表名称。

其次，列出一组要应用于该表的操作。操作可以是添加新列，添加主键，重命名表等任何操作。ALTER TABLE语句允许在单个ALTER TABLE语句中应用多个操作，每个操作由逗号(，)分隔。

让我们创建一个用于练习ALTER TABLE语句的新表。

我们将在示例数据库(yiibaidb)中创建一个名为tasks的新表。 以下是创建tasks表的脚本。

DROPTABLEIFEXISTStasks;CREATETABLEtasks(task_idINTNOTNULL,subjectVARCHAR(45)NULL,start_dateDATENULL,end_dateDATENULL,descriptionVARCHAR(200)NULL,PRIMARYKEY(task_id),UNIQUEINDEXtask_id_unique(task_idASC));

使用MySQL ALTER TABLE语句更改列

使用MySQL ALTER TABLE语句来设置列的自动递增属性

假设您希望在任务表中插入新行时，task_id列的值会自动增加1。那么可以使用ALTER TABLE语句将task_id列的属性设置为AUTO_INCREMENT，如下所示：

ALTERTABLEtasksCHANGECOLUMNtask_idtask_idINT(11)NOTNULLAUTO_INCREMENT;

可以通过在tasks表中插入一些行数据来验证更改。

INSERTINTOtasks(subject,start_date,end_date,description)VALUES('LearnMySQLALTERTABLE',Now(),Now(),'PracticingMySQLALTERTABLEstatement');INSERTINTOtasks(subject,start_date,end_date,description)VALUES('LearnMySQLCREATETABLE',Now(),Now(),'PracticingMySQLCREATETABLEstatement');

您可以查询数据以查看每次插入新行时task_id列的值是否增加1：

SELECTtask_id,descriptionFROMtasks;

使用MySQL ALTER TABLE语句将新的列添加到表中

由于新的业务需求，需要添加一个名为complete的新列，以便在任务表中存储每个任务的完成百分比。 在这种情况下，您可以使用ALTER TABLE将新列添加到tasks表中，如下所示：

ALTERTABLEtasksADDCOLUMNcompleteDECIMAL(2,1)NULLAFTERdescription;

使用MySQL ALTER TABLE从表中删除列

假设您不想将任务的描述存储在tasks表中了，并且必须将其删除。 以下语句允许您删除tasks表的description列：

ALTERTABLEtasksDROPCOLUMNdescription;

使用MySQL ALTER TABLE语句重命名表

可以使用ALTER TABLE语句重命名表。请注意，在重命名表之前，应该认真考虑以了解更改是否影响数据库和应用程序层，不要因为重命名表之后，应用程序因未找到数据库表而出错。

以下语句将tasks表重命名为work_items表：

ALTERTABLEtasksRENAMETOwork_items;

表记录操作

1.简单的MySQL INSERT语句

MySQL INSERT语句允许您将一行或多行插入到表中。下面说明了INSERT语句的语法：

INSERTINTOtable(column1,column2...)VALUES(value1,value2,...);

首先，在INSERT INTO子句之后，在括号内指定表名和逗号分隔列的列表。

然后，将括号内的相应列的逗号分隔值放在VALUES关键字之后。

在执行插入语句前，需要具有执行INSERT语句的INSERT权限。

让我们创建一个名为tasks的新表来练习INSERT语句，参考以下创建语句 -

USEtestdb;CREATETABLEIFNOTEXISTStasks(task_idINT(11)AUTO_INCREMENT,subjectVARCHAR(45)DEFAULTNULL,start_dateDATEDEFAULTNULL,end_dateDATEDEFAULTNULL,descriptionVARCHAR(200)DEFAULTNULL,PRIMARYKEY(task_id))ENGINE=InnoDBDEFAULTCHARSET=utf8;

例如，如果要将任务插入到tasts表中，则使用INSERT语句如下：

INSERTINTOtasks(subject,start_date,end_date,description)VALUES('LearnMySQLINSERT','2017-07-21','2017-07-22','Startlearning..');

执行该语句后，MySQL返回一条消息以通知受影响的行数。 在这种情况下，有一行受到影响。

现在使用以下语句查询 tasks 中的数据，如下所示 -

SELECT*FROMtasks;

执行上面查询语句，得到以下结果

+---------+--------------------+------------+------------+------------------+|task_id|subject|start_date|end_date|description|+---------+--------------------+------------+------------+------------------+|1|LearnMySQLINSERT|2017-07-21|2017-07-22|Startlearning..|+---------+--------------------+------------+------------+------------------+1rowinset

2. MySQL INSERT - 插入多行

想要在表中一次插入多行，可以使用具有以下语法的INSERT语句：

INSERTINTOtable(column1,column2...)VALUES(value1,value2,...),(value1,value2,...),...;

在这种形式中，每行的值列表用逗号分隔。 例如，要将多行插入到tasks表中，请使用以下语句：

INSERTINTOtasks(subject,start_date,end_date,description)VALUES('任务-1','2017-01-01','2017-01-02','Description1'),('任务-2','2017-01-01','2017-01-02','Description2'),('任务-3','2017-01-01','2017-01-02','Description3');

执行上面语句后，返回

QueryOK,3rowsaffectedRecords:3Duplicates:0Warnings:0

现在查询tasks表中的数据，如下所示

select*fromtasks;

执行上面查询语句，得到以下结果

如果为表中的所有列指定相应列的值，则可以忽略INSERT语句中的列列表，如下所示：

INSERTINTOtableVALUES(value1,value2,...);或者INSERTINTOtableVALUES(value1,value2,...),(value1,value2,...),...;

请注意，不必为自动递增列(例如taskid列)指定值，因为MySQL会自动为自动递增列生成值。

3. 具有SELECT子句的MySQL INSERT

在MySQL中，可以使用SELECT语句返回的列和值来填充INSERT语句的值。 此功能非常方便，因为您可以使用INSERT和SELECT子句完全或部分复制表，如下所示：

INSERTINTOtable_1SELECTc1,c2,FROMtable_2;

假设要将tasks表复制到tasks_bak表。

首先，通过复制tasks表的结构，创建一个名为tasks_bak的新表，如下所示：

CREATETABLEtasks_bakLIKEtasks;

第二步，使用以下INSERT语句将tasks表中的数据插入tasks_bak表：

INSERTINTOtasks_bakSELECT*FROMtasks;

第三步，检查tasks_bak表中的数据，看看是否真正从tasks表复制完成了。

mysql>select*fromtasks;+---------+--------------------+------------+------------+------------------+|task_id|subject|start_date|end_date|description|+---------+--------------------+------------+------------+------------------+|1|LearnMySQLINSERT|2017-07-21|2017-07-22|Startlearning..||2|任务-1|2017-01-01|2017-01-02|Description1||3|任务-2|2017-01-01|2017-01-02|Description2||4|任务-3|2017-01-01|2017-01-02|Description3|+---------+--------------------+------------+------------+------------------+4rowsinset

4. MySQL INSERT与ON DUPLICATE KEY UPDATE

如果新行违反主键(PRIMARY KEY)或UNIQUE约束，MySQL会发生错误。 例如，如果执行以下语句：

INSERTINTOtasks(task_id,subject,start_date,end_date,description)VALUES(4,'TestONDUPLICATEKEYUPDATE','2017-01-01','2017-01-02','NextPriority');

MySQL很不高兴，并向你扔来一个错误消息：

ErrorCode:1062.Duplicateentry'4'forkey'PRIMARY'0.016sec

因为表中的主键task_id列已经有一个值为 4 的行了，所以该语句违反了PRIMARY KEY约束。

但是，如果在INSERT语句中指定ON DUPLICATE KEY UPDATE选项，MySQL将插入新行或使用新值更新原行记录。

例如，以下语句使用新的task_id和subject来更新task_id为4的行。

INSERTINTOtasks(task_id,subject,start_date,end_date,description)VALUES(4,'TestONDUPLICATEKEYUPDATE','2017-01-01','2017-01-02','NextPriority')ONDUPLICATEKEYUPDATEtask_id=task_id+1,subject='TestONDUPLICATEKEYUPDATE';

执行上面语句后，MySQL发出消息说2行受影响。现在，我们来看看tasks表中的数据：

mysql>select*fromtasks;+---------+------------------------------+------------+------------+------------------+|task_id|subject|start_date|end_date|description|+---------+------------------------------+------------+------------+------------------+|1|LearnMySQLINSERT|2017-07-21|2017-07-22|Startlearning..||2|任务-1|2017-01-01|2017-01-02|Description1||3|任务-2|2017-01-01|2017-01-02|Description2||5|TestONDUPLICATEKEYUPDATE|2017-01-01|2017-01-02|Description3|+---------+------------------------------+------------+------------+------------------+4rowsinset

新行没有被插入，但是更新了task_id值为4的行。上面的INSERT ON DUPLICATE KEY UPDATE语句等效于以下UPDATE语句：

UPDATEtasksSETtask_id=task_id+1,subject='TestONDUPLICATEKEYUPDATE'WHEREtask_id=4;

修改表数据

1. MySQL UPDATE语句简介

我们使用UPDATE语句来更新表中的现有数据。也可以使用UPDATE语句来更改表中单个行，一组行或所有行的列值。

下面说明了MySQL UPDATE语句的语法：

UPDATE[LOW_PRIORITY][IGNORE]table_nameSETcolumn_name1=expr1,column_name2=expr2,...WHEREcondition;

在上面UPDATE语句中：

首先，在UPDATE关键字后面指定要更新数据的表名。

其次，SET子句指定要修改的列和新值。要更新多个列，请使用以逗号分隔的列表。以字面值，表达式或子查询的形式在每列的赋值中来提供要设置的值。

第三，使用WHERE子句中的条件指定要更新的行。WHERE子句是可选的。 如果省略WHERE子句，则UPDATE语句将更新表中的所有行。

请注意，WHERE子句非常重要，所以不应该忘记指定更新的条件。 有时，您可能只想改变一行; 但是，可能会忘记写上WHERE子句，导致意外更新表中的所有行。

MySQL在UPDATE语句中支持两个修饰符。

LOW_PRIORITY修饰符指示UPDATE语句延迟更新，直到没有从表中读取数据的连接。 LOW_PRIORITY对仅使用表级锁定的存储引擎(例如MyISAM，MERGE，MEMORY)生效。

即使发生错误，IGNORE修饰符也可以使UPDATE语句继续更新行。导致错误(如重复键冲突)的行不会更新。

2. MySQL UPDATE示例

我们使用MySQL示例数据库(yiibaidb)中的一些表来练习使用UPDATE语句。

2.1 MySQL UPDATE一个单列示例

在这个例子中，我们将把 Mary Patterson 的电子邮件更新为新的电子邮件mary.patterso@yiibai.com。

首先，为了确保更新电子邮件成功，使用以下SELECT语句从employees表查询Mary的电子邮件：

SELECTfirstname,lastname,emailFROMemployeesWHEREemployeeNumber=1056;

执行上面的查询语句，得到以下结果

+-----------+-----------+----------------------+|firstname|lastname|email|+-----------+-----------+----------------------+|Mary|Patterson|mpatterso@yiibai.com|+-----------+-----------+----------------------+1rowinset

第二步，使用UPDATE语句将Mary的电子邮件更新为新的电子邮件：mary.new@yiibai.com，如下查询所示：

UPDATEemployeesSETemail='mary.new@yiibai.com'WHEREemployeeNumber=1056;

因为上面语句中，只想更新一行，所以使用WHERE子句来指定更新的是员工编号1056的行。SET子句将电子邮件列的值设置为新的电子邮件。

第三，再次执行SELECT语句来验证更改。

SELECTfirstname,lastname,emailFROMemployeesWHEREemployeeNumber=1056;

再次执行上面的查询语句，得到以下结果

+-----------+-----------+---------------------+|firstname|lastname|email|+-----------+-----------+---------------------+|Mary|Patterson|mary.new@yiibai.com|+-----------+-----------+---------------------+1rowinset

2.2 MySQL UPDATE多列

要更新多列中的值，需要在SET子句中指定分配。例如，以下语句更新了员工编号1056的姓氏和电子邮件列：

UPDATEemployeesSETlastname='Hill',email='mary.hill@yiibai.com'WHEREemployeeNumber=1056;

在执行上面语句之后，查询员工编号为：1056的记录，如下所示 -

+-----------+----------+----------------------+|firstname|lastname|email|+-----------+----------+----------------------+|Mary|Hill|mary.hill@yiibai.com|+-----------+----------+----------------------+1rowinset

2.3 使用SELECT语句的MySQL UPDATE示例

可以使用SELECT语句查询来自其他表的数据来提供给SET子句的值。

例如，在customers表中，有些客户没有任何销售代表。 salesRepEmployeeNumber列的值为NULL，如下所示：

mysql>SELECTcustomername,salesRepEmployeeNumberFROMcustomersWHEREsalesRepEmployeeNumberISNULL;+--------------------------------+------------------------+|customername|salesRepEmployeeNumber|+--------------------------------+------------------------+|Havel&ZbyszekCo|NULL||PortoImportsCo.|NULL||AsianShoppingNetwork,Co|NULL||NatrlichAutos|NULL||ANGResellers|NULL||MessnerShoppingNetwork|NULL||FrankenGifts,Co|NULL||BG&ECollectables|NULL||SchuylerImports|NULL||DerHundImports|NULL||CramerSpezialitten,Ltd|NULL||AsianTreasures,Inc.|NULL||SARDistributors,Co|NULL||KommissionAuto|NULL||LisboaSouveniers,Inc|NULL||StuttgartCollectableExchange|NULL||FeuerOnlineStores,Inc|NULL||WarburgExchange|NULL||AntonDesigns,Ltd.|NULL||MitVergngen&Co.|NULL||KremlinCollectables,Co.|NULL||RaananStores,Inc|NULL|+--------------------------------+------------------------+22rowsinset




我们可以为这些客户提供销售代表和更新。

为此，需要从employees表中随机选择一个职位为Sales Rep的雇员，并将其更新到employees表中。

下面的查询语句是从employees表中随机选择一个其职位是Sales Rep的员工。

SELECTemployeeNumberFROMemployeesWHEREjobtitle='SalesRep'ORDERBYRAND()LIMIT1;

要更新customers表中的销售代表员工编号(employeeNumber)列，我们将上面的查询放在UPDATE语句的SET子句中，如下所示：

UPDATEcustomersSETsalesRepEmployeeNumber=(SELECTemployeeNumberFROMemployeesWHEREjobtitle='SalesRep'LIMIT1)WHEREsalesRepEmployeeNumberISNULL;

如果在执行上面更新语句后，查询employees表中的数据，将看到每个客户都有一个销售代表。 换句话说，以下查询不返回任何行数据。

SELECTsalesRepEmployeeNumberFROMcustomersWHEREsalesRepEmployeeNumberISNULL;

删除表数据




1. MySQL DELETE语句介绍

要从表中删除数据，请使用MySQL DELETE语句。下面说明了DELETE语句的语法：

DELETEFROMtable_nameWHEREcondition;

在上面查询语句中

首先，指定删除数据的表(table_name)。

其次，使用条件来指定要在WHERE子句中删除的行记录。如果行匹配条件，这些行记录将被删除。

请注意，WHERE子句是可选的。如果省略WHERE子句，DELETE语句将删除表中的所有行。

除了从表中删除数据外，DELETE语句返回删除的行数。

要使用单个DELETE语句从多个表中删除数据，请阅读下一个教程中将介绍的DELETE JOIN语句。

要删除表中的所有行，而不需要知道删除了多少行，那么应该使用TRUNCATE TABLE语句来获得更好的执行性能。

对于具有外键约束的表，当从父表中删除行记录时，子表中的行记录将通过使用ON DELETE CASCADE选项自动删除。

2. MySQL DELETE的例子

我们将使用示例数据库(yiibaidb)中的employees表进行演示。

+-------------+--------------+------+-----+---------+----------------+|Field|Type|Null|Key|Default|Extra|+-------------+--------------+------+-----+---------+----------------+|emp_id|int(11)|NO|PRI|NULL|auto_increment||emp_name|varchar(255)|NO||NULL|||performance|int(11)|YES|MUL|NULL|||salary|float|YES||NULL||+-------------+--------------+------+-----+---------+----------------+4rowsinset

请注意，一旦删除数据，它就会永远消失。 因此，在执行DELETE语句之前，应该先备份数据库，以防万一要找回删除过的数据。

假设要删除officeNumber为4的员工，则使用DELETE语句与WHERE子句作为以下查询：

DELETEFROMemployeesWHEREofficeCode=4;

要删除employees表中的所有行，请使用不带WHERE子句的DELETE语句，如下所示：

DELETEFROMemployees;

在执行上面查询语句后，employees表中的所有行都被删除。

MySQL DELETE和LIMIT子句

如果要限制要删除的行数，则使用LIMIT子句，如下所示：

DELETEFROMtableLIMITrow_count;

请注意，表中的行顺序未指定，因此，当您使用LIMIT子句时，应始终使用ORDER BY子句，不然删除的记录可能不是你所预期的那样。

DELETEFROMtable_nameORDERBYc1,c2,...LIMITrow_count;

考虑在示例数据库(yiibaidb)中的customers表，其表结构如下：

mysql>desccustomers;+------------------------+---------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+------------------------+---------------+------+-----+---------+-------+|customerNumber|int(11)|NO|PRI|NULL|||customerName|varchar(50)|NO||NULL|||contactLastName|varchar(50)|NO||NULL|||contactFirstName|varchar(50)|NO||NULL|||phone|varchar(50)|NO||NULL|||addressLine1|varchar(50)|NO||NULL|||addressLine2|varchar(50)|YES||NULL|||city|varchar(50)|NO||NULL|||state|varchar(50)|YES||NULL|||postalCode|varchar(15)|YES||NULL|||country|varchar(50)|NO||NULL|||salesRepEmployeeNumber|int(11)|YES|MUL|NULL|||creditLimit|decimal(10,2)|YES||NULL||+------------------------+---------------+------+-----+---------+-------+13rowsinset

例如，以下语句按客户名称按字母排序客户，并删除前10个客户：

DELETEFROMcustomersORDERBYcustomerNameLIMIT10;

类似地，以下DELETE语句选择法国(France)的客户，按升序按信用额度(creditLimit)进行排序，并删除前5个客户：

DELETEFROMcustomersWHEREcountry='France'ORDERBYcreditLimitLIMIT5;

类似地，以下DELETE语句选择法国(France)的客户，按升序按信用额度(creditLimit)进行排序，并删除前5个客户：

DELETEFROMcustomersWHEREcountry='France'ORDERBYcreditLimitLIMIT5;

查询表记录(select)


查询语法：

SELECT*|field1,filed2...FROMtab_nameWHERE条件GROUPBYfieldHAVING筛选ORDERBYfieldLIMIT限制条数Mysql在执行sql语句时的执行顺序：--fromwhereselectgroupbyhavingorderby

准备数据


CREATETABLEemp(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(20),genderENUM("male","female","other"),ageTINYINT,depVARCHAR(20),cityVARCHAR(20),salaryDOUBLE(7,2));INSERTINTOemp(name,gender,age,dep,city,salary)VALUES("yuan","male",24,"教学部","河北省",8000),("egon","male",34,"保安部","山东省",8000),("alex","male",28,"×××部","山东省",10000),("景丽阳","female",22,"教学部","北京",9000),("张三","male",24,"教学部","河北省",6000),("李四","male",32,"保安部","北京",12000),("王五","male",38,"教学部","河北省",7000),("赵六","male",19,"保安部","河北省",9000),("猪七","female",24,"×××部","北京",9000);SELECT*FROMemp;

mysql>SELECT*FROMemp;+----+-----------+--------+------+-----------+-----------+----------+|id|name|gender|age|dep|city|salary|+----+-----------+--------+------+-----------+-----------+----------+|1|yuan|male|24|教学部|河北省|8000.00||2|egon|male|34|保安部|山东省|8000.00||3|alex|male|28|×××部|山东省|10000.00||4|景丽阳|female|22|教学部|北京|9000.00||5|张三|male|24|教学部|河北省|6000.00||6|李四|male|32|保安部|北京|12000.00||7|王五|male|38|教学部|河北省|7000.00||8|赵六|male|19|保安部|河北省|9000.00||9|猪七|female|24|×××部|北京|9000.00|+----+-----------+--------+------+-----------+-----------+----------+rowsinset(0.00sec)

where子句: 过滤查询

where字句中可以使用比较运算符：><>=<=<>!=between80and100值在10到20之间in(80,90,100)值是10或20或30like'yuan%'/*pattern可以是%或者_，如果是%则表示任意多字符，此例如唐僧,唐国强如果是_则表示一个字符唐_，只有唐僧符合。两个_则表示两个字符：__*/逻辑运算符在多个条件直接可以使用逻辑运算符andornot

查询年纪大于24的员工

SELECT*FROMempWHEREage>24;

查询教学部的男老师信息

SELECT*FROMempWHEREdep="教学部"ANDgender="male";

order：排序

按指定的列进行，排序的列即可是表中的列名，也可以是select语句后指定的别名。

语法：

select*|field1,field2...fromtab_nameorderbyfield[Asc|Desc]

Asc 升序、Desc 降序，其中asc为默认值 ORDER BY 子句应位于SELECT语句的结尾。

示例：

按年龄从高到低进行排序

SELECT*FROMempORDERBYageDESC;

按工资从低到高进行排序

SELECT*FROMempORDERBYsalary;

group by：分组查询（＊＊＊＊＊）

GROUP BY 语句根据某个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG等函数进行相关查询。

语法:

SELECTcolumn_name,function(column_name)　　　　　　FROMtable_name　　　　　　WHEREcolumn_nameoperatorvalue　　　　　　GROUPBYcolumn_name;

示例：

-- 查询男女员工各有多少人

SELECTgender性别,count(*)人数FROMemp5GROUPBYgender;

-- 查询各个部门的人数

SELECTdep部门,count(*)人数FROMemp5GROUPBYdep;

-- 查询每个部门最大的年龄

SELECTdep部门,max(age)最大年纪FROMemp5GROUPBYdep;

-- 查询每个部门年龄最大的员工姓名

SELECT*FROMemp5WHEREagein(SELECTmax(age)FROMemp5GROUPBYdep);

-- 查询每个部门的平均工资

SELECTdep部门,avg(salary)最大年纪FROMempGROUPBYdep;

-- 查询教学部的员工最高工资:

SELECT dep,max(salary) FROM emp11 GROUP BY dep HAVING dep="教学部";

-- 查询平均薪水超过8000的部门

SELECT dep,AVG(salary) FROM emp GROUP BY dep HAVING avg(salary)>8000;

-- 查询每个组的员工姓名

SELECT dep,group_concat(name) FROM emp GROUP BY dep;

-- 查询公司一共有多少员工(可以将所有记录看成一个组)

SELECT COUNT(*) 员工总人数 FROM emp;

-- KEY: 查询条件中的每个后的词就是分组的字段

limit记录条数限制

SELECT*fromExamResultlimit1;SELECT*fromExamResultlimit2,5;--跳过前两条显示接下来的五条纪录SELECT*fromExamResultlimit2,2;

正则表达式

SELECT*FROMemployeeWHEREemp_nameREGEXP'^yu';SELECT*FROMemployeeWHEREemp_nameREGEXP'yun$';SELECT*FROMemployeeWHEREemp_nameREGEXP'm{2}';

多表查询

创建表


CREATETABLEemp(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(20),salaryDOUBLE(7,2),dep_idINT);INSERTINTOemp(name,salary,dep_id)VALUES("张三",8000,2),("李四",12000,1),("王五",5000,2),("赵六",8000,3),("猪七",9000,1),("周八",7000,4),("蔡九",7000,2);CREATETABLEdep(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(20));INSERTINTOdep(name)VALUES("教学部"),("销售部"),("人事部");




mysql> select * from emp;

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

| id | name| salary| dep_id |

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

| 1 | 张三| 8000.00 | 2 |

| 2 | 李四| 12000.00 | 1 |

| 3 | 王五| 5000.00 | 2 |

| 4 | 赵六| 8000.00 | 3 |

| 5 | 猪七| 9000.00 | 1 |

| 6 | 周八| 7000.00 | 4 |

| 7 | 蔡九| 7000.00 | 2 |

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

7 rows in set (0.00 sec)




mysql> select * from dep;

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

| id | name |

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

| 1 | 教学部|

| 2 | 销售部|

| 3 | 人事部|

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

3 rows in set (0.00 sec)




1.笛卡尔积查询

select * from emp,dep;

mysql> select * from emp,dep;

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

| id | name| salary| dep_id | id | name |

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

| 1 | 张三| 8000.00 | 2 | 1 | 教学部|

| 1 | 张三| 8000.00 | 2 | 2 | 销售部|

| 1 | 张三| 8000.00 | 2 | 3 | 人事部|

| 2 | 李四| 12000.00 | 1 | 1 | 教学部|

| 2 | 李四| 12000.00 | 1 | 2 | 销售部|

| 2 | 李四| 12000.00 | 1 | 3 | 人事部|

| 3 | 王五| 5000.00 | 2 | 1 | 教学部|

| 3 | 王五| 5000.00 | 2 | 2 | 销售部|

| 3 | 王五| 5000.00 | 2 | 3 | 人事部|

| 4 | 赵六| 8000.00 | 3 | 1 | 教学部|

| 4 | 赵六| 8000.00 | 3 | 2 | 销售部|

| 4 | 赵六| 8000.00 | 3 | 3 | 人事部|

| 5 | 猪七| 9000.00 | 1 | 1 | 教学部|

| 5 | 猪七| 9000.00 | 1 | 2 | 销售部|

| 5 | 猪七| 9000.00 | 1 | 3 | 人事部|

| 6 | 周八| 7000.00 | 4 | 1 | 教学部|

| 6 | 周八| 7000.00 | 4 | 2 | 销售部|

| 6 | 周八| 7000.00 | 4 | 3 | 人事部|

| 7 | 蔡九| 7000.00 | 2 | 1 | 教学部|

| 7 | 蔡九| 7000.00 | 2 | 2 | 销售部|

| 7 | 蔡九| 7000.00 | 2 | 3 | 人事部|

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

21 rows in set (0.00 sec)




2、内连接

查询两张表中都有的关联数据,相当于利用条件从笛卡尔积结果中筛选出了正确的结果。

SELECT * FROM emp,dep WHERE emp.dep_id=dep.id;

OR

SELECT * FROM emp INNER JOIN dep ON emp.dep_id=dep.id;

查询结果：

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

| id | name| salary| dep_id | id | name |

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

| 1 | 张三| 8000.00 | 2 | 2 | 销售部 |

| 2 | 李四| 12000.00 | 1 | 1 | 教学部 |

| 3 | 王五| 5000.00 | 2 | 2 | 销售部 |

| 4 | 赵六| 8000.00 | 3 | 3 | 人事部 |

| 5 | 猪七| 9000.00 | 1 | 1 | 教学部 |

| 7 | 蔡九| 7000.00 | 2 | 2 | 销售部 |

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

6 rows in set (0.00 sec)

这时，我们就可以利用两张表中所有的字段进行查询了

示例：

-- 查询李四所在的部门名称

SELECT emp.name,dep.name FROM emp INNER JOIN dep ON emp.dep_id=dep.id WHERE emp.name="李四";

-- 查询销售部所有员工姓名以及部门名称

-- SELECT name FROM emp WHERE dep_id in (SELECT id FROM dep WHERE name="销售部");

SELECT emp.name,dep.name FROM emp INNER JOIN dep ON emp.dep_id=dep.id WHERE dep.name="销售部";

3、外连接

（1）左外连接：在内连接的基础上增加左边有右边没有的结果

SELECT * FROM emp LEFT JOIN dep ON dep.id=emp.dep_id;

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

| id | name| salary| dep_id | id| name |

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

| 2 | 李四| 12000.00 | 1 |1 | 教学部|

| 5 | 猪七| 9000.00 | 1 |1 | 教学部|

| 1 | 张三| 8000.00 | 2 |2 | 销售部|

| 3 | 王五| 5000.00 | 2 |2 | 销售部|

| 7 | 蔡九| 7000.00 | 2 |2 | 销售部|

| 4 | 赵六| 8000.00 | 3 |3 | 人事部|

| 6 | 周八| 7000.00 | 4 | NULL | NULL |

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

7 rows in set (0.00 sec)




（1）外右连接：在内连接的基础上增加右边有左边没有的结果

SELECT * FROM emp RIGHT JOIN dep ON dep.id=emp.dep_id;

mysql> SELECT * FROM emp RIGHT JOIN dep ON dep.id=emp.dep_id;

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

| id| name| salary| dep_id | id | name |

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

|1 | 张三| 8000.00 | 2 | 2 | 销售部|

|2 | 李四| 12000.00 | 1 | 1 | 教学部|

|3 | 王五| 5000.00 | 2 | 2 | 销售部|

|4 | 赵六| 8000.00 | 3 | 3 | 人事部|

|5 | 猪七| 9000.00 | 1 | 1 | 教学部|

|7 | 蔡九| 7000.00 | 2 | 2 | 销售部|

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

6 rows in set (0.00 sec)