操作MySQL数据库
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、定点数
浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。 4、字符串(char,varchar,_text) char和varchar： 5.二进制数据(_Blob) 1._BLOB和_text存储方式不同，_TEXT以文本方式存储，英文存储区分大小写，而_Blob是以二进制方式存储，不分大小写。 6.日期时间类型 若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间。 数据类型的属性 创建数据库 要在MySQL中创建数据库，请使用CREATE DATABASE语句，如下： 我们来更详细地看看CREATE DATABASE语句： CREATE DATABASE语句的后面是要创建的数据库名称。建议数据库名称尽可能是有意义和具有一定的描述性。 IF NOT EXISTS是语句的可选子句。 IF NOT EXISTS子句可防止创建数据库服务器中已存在的新数据库的错误。不能在MySQL数据库服务器中具有相同名称的数据库。 例如，要创建一个名称为mytestdb数据库，可以执行CREATE DATABASE语句后接数据库名称：mytestdb，如果当前MySQL服务器中没有数据库：mytestdb，则创建成功，如下所示： 执行此语句后，MySQL返回一条消息，通知新数据库是否已成功创建。 显示数据库 SHOW DATABASES语句显示MySQL数据库服务器中的所有数据库。您可以使用SHOW DATABASES语句来查看您要创建的数据库，或者在创建新数据库之前查看数据库服务器上的所有数据库，例如： 在此MySQL数据库服务器中有6个数据库。 information_schema，performance_schema和mysql是我们安装MySQL时可用的默认数据库，而yiibaidb是创建的新数据库。 选择要使用的数据库 在使用指定数据库之前，必须通过使用USE语句告诉MySQL要使用哪个数据库。 您可以使用USE语句选择示例数据库(yiibaidb)，如下所示： 从现在开始，所有操作(如查询数据，创建新表或调用存储过程)都将对当前数据库(即yiibaidb)产生影响。 删除数据库 删除数据库意味着数据库中的所有数据和关联对象将被永久删除，并且无法撤消。 因此，用额外的注意事项执行此查询是非常重要的。 要删除数据库，请使用DROP DATABASE语句，如下所示： 遵循DROP DATABASE是要删除的数据库名称。 与CREATE DATABASE语句类似，IF EXISTS是该语句的可选部分，以防止您删除数据库服务器中不存在的数据库。 如果要使用DROP DATABASE语句练习，可以创建一个新数据库，然后将其删除。来看下面的查询： 三个语句的说明如下： 首先，使用CREATE DATABASE语句创建了一个名为tempdb的数据库。 第二，使用SHOW DATABASES语句显示所有数据库。 第三，使用DROP DATABASE语句删除了名为tempdb的数据库。 MySQL CREATE TABLE语法 要在数据库中创建一个新表，可以使用MySQL CREATE TABLE语句。 CREATE TABLE语句是MySQL中最复杂的语句之一。 下面以简单的形式来说明CREATE TABLE语句的语法： 我们来更详细地来查看其语法： 首先，指定要在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_name指定列的名称。每列具有特定数据类型和大小，例如：VARCHAR(255)。 NOT NULL或NULL表示该列是否接受NULL值。 DEFAULT值用于指定列的默认值。 AUTO_INCREMENT指示每当将新行插入到表中时，列的值会自动增加。每个表都有一个且只有一个AUTO_INCREMENT列。 如果要将表的特定列设置为主键，则使用以下语法： MySQL CREATE TABLE语句示例 下面让我们练习一个例子，在示例数据库(testdb)中创建一个名为tasks的新表，如下所示： 可以使用CREATE TABLE语句创建这个tasks表，如下所示： MySQL ALTER TABLE语句简介 可以使用ALTER TABLE语句来更改现有表的结构。 ALTER TABLE语句可用来添加列，删除列，更改列的数据类型，添加主键，重命名表等等。 以下说明了ALTER TABLE语句语法： 要更改现有表的结构： 首先，在ALTER TABLE子句之后指定要更改的表名称。 其次，列出一组要应用于该表的操作。操作可以是添加新列，添加主键，重命名表等任何操作。ALTER TABLE语句允许在单个ALTER TABLE语句中应用多个操作，每个操作由逗号(，)分隔。 让我们创建一个用于练习ALTER TABLE语句的新表。 我们将在示例数据库(yiibaidb)中创建一个名为tasks的新表。 以下是创建tasks表的脚本。 使用MySQL ALTER TABLE语句更改列 使用MySQL ALTER TABLE语句来设置列的自动递增属性 假设您希望在任务表中插入新行时，task_id列的值会自动增加1。那么可以使用ALTER TABLE语句将task_id列的属性设置为AUTO_INCREMENT，如下所示： 可以通过在tasks表中插入一些行数据来验证更改。 您可以查询数据以查看每次插入新行时task_id列的值是否增加1： 使用MySQL ALTER TABLE语句将新的列添加到表中 由于新的业务需求，需要添加一个名为complete的新列，以便在任务表中存储每个任务的完成百分比。 在这种情况下，您可以使用ALTER TABLE将新列添加到tasks表中，如下所示： 使用MySQL ALTER TABLE从表中删除列 假设您不想将任务的描述存储在tasks表中了，并且必须将其删除。 以下语句允许您删除tasks表的description列： 使用MySQL ALTER TABLE语句重命名表 可以使用ALTER TABLE语句重命名表。请注意，在重命名表之前，应该认真考虑以了解更改是否影响数据库和应用程序层，不要因为重命名表之后，应用程序因未找到数据库表而出错。 以下语句将tasks表重命名为work_items表： 1.简单的MySQL INSERT语句 MySQL INSERT语句允许您将一行或多行插入到表中。下面说明了INSERT语句的语法： 首先，在INSERT INTO子句之后，在括号内指定表名和逗号分隔列的列表。 然后，将括号内的相应列的逗号分隔值放在VALUES关键字之后。 在执行插入语句前，需要具有执行INSERT语句的INSERT权限。 让我们创建一个名为tasks的新表来练习INSERT语句，参考以下创建语句 - 例如，如果要将任务插入到tasts表中，则使用INSERT语句如下： 执行该语句后，MySQL返回一条消息以通知受影响的行数。 在这种情况下，有一行受到影响。 现在使用以下语句查询 tasks 中的数据，如下所示 - 执行上面查询语句，得到以下结果 2. MySQL INSERT - 插入多行 想要在表中一次插入多行，可以使用具有以下语法的INSERT语句： 在这种形式中，每行的值列表用逗号分隔。 例如，要将多行插入到tasks表中，请使用以下语句： 执行上面语句后，返回 现在查询tasks表中的数据，如下所示 执行上面查询语句，得到以下结果 如果为表中的所有列指定相应列的值，则可以忽略INSERT语句中的列列表，如下所示： 请注意，不必为自动递增列(例如taskid列)指定值，因为MySQL会自动为自动递增列生成值。 3. 具有SELECT子句的MySQL INSERT 在MySQL中，可以使用SELECT语句返回的列和值来填充INSERT语句的值。 此功能非常方便，因为您可以使用INSERT和SELECT子句完全或部分复制表，如下所示： 假设要将tasks表复制到tasks_bak表。 首先，通过复制tasks表的结构，创建一个名为tasks_bak的新表，如下所示： 第二步，使用以下INSERT语句将tasks表中的数据插入tasks_bak表： 第三步，检查tasks_bak表中的数据，看看是否真正从tasks表复制完成了。 4. MySQL INSERT与ON DUPLICATE KEY UPDATE 如果新行违反主键(PRIMARY KEY)或UNIQUE约束，MySQL会发生错误。 例如，如果执行以下语句： MySQL很不高兴，并向你扔来一个错误消息： 因为表中的主键task_id列已经有一个值为 4 的行了，所以该语句违反了PRIMARY KEY约束。 但是，如果在INSERT语句中指定ON DUPLICATE KEY UPDATE选项，MySQL将插入新行或使用新值更新原行记录。 例如，以下语句使用新的task_id和subject来更新task_id为4的行。 执行上面语句后，MySQL发出消息说2行受影响。现在，我们来看看tasks表中的数据： 新行没有被插入，但是更新了task_id值为4的行。上面的INSERT ON DUPLICATE KEY UPDATE语句等效于以下UPDATE语句： 修改表数据 1. MySQL UPDATE语句简介 我们使用UPDATE语句来更新表中的现有数据。也可以使用UPDATE语句来更改表中单个行，一组行或所有行的列值。 下面说明了MySQL UPDATE语句的语法： 在上面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的电子邮件： 执行上面的查询语句，得到以下结果 第二步，使用UPDATE语句将Mary的电子邮件更新为新的电子邮件：mary.new@yiibai.com，如下查询所示： 因为上面语句中，只想更新一行，所以使用WHERE子句来指定更新的是员工编号1056的行。SET子句将电子邮件列的值设置为新的电子邮件。 第三，再次执行SELECT语句来验证更改。 再次执行上面的查询语句，得到以下结果 2.2 MySQL UPDATE多列 要更新多列中的值，需要在SET子句中指定分配。例如，以下语句更新了员工编号1056的姓氏和电子邮件列： 在执行上面语句之后，查询员工编号为：1056的记录，如下所示 - 2.3 使用SELECT语句的MySQL UPDATE示例 可以使用SELECT语句查询来自其他表的数据来提供给SET子句的值。 例如，在customers表中，有些客户没有任何销售代表。 salesRepEmployeeNumber列的值为NULL，如下所示： 我们可以为这些客户提供销售代表和更新。 为此，需要从employees表中随机选择一个职位为Sales Rep的雇员，并将其更新到employees表中。 下面的查询语句是从employees表中随机选择一个其职位是Sales Rep的员工。 要更新customers表中的销售代表员工编号(employeeNumber)列，我们将上面的查询放在UPDATE语句的SET子句中，如下所示： 如果在执行上面更新语句后，查询employees表中的数据，将看到每个客户都有一个销售代表。 换句话说，以下查询不返回任何行数据。 删除表数据 1. MySQL DELETE语句介绍 要从表中删除数据，请使用MySQL DELETE语句。下面说明了DELETE语句的语法： 在上面查询语句中 首先，指定删除数据的表(table_name)。 其次，使用条件来指定要在WHERE子句中删除的行记录。如果行匹配条件，这些行记录将被删除。 请注意，WHERE子句是可选的。如果省略WHERE子句，DELETE语句将删除表中的所有行。 除了从表中删除数据外，DELETE语句返回删除的行数。 要使用单个DELETE语句从多个表中删除数据，请阅读下一个教程中将介绍的DELETE JOIN语句。 要删除表中的所有行，而不需要知道删除了多少行，那么应该使用TRUNCATE TABLE语句来获得更好的执行性能。 对于具有外键约束的表，当从父表中删除行记录时，子表中的行记录将通过使用ON DELETE CASCADE选项自动删除。 2. MySQL DELETE的例子 我们将使用示例数据库(yiibaidb)中的employees表进行演示。 请注意，一旦删除数据，它就会永远消失。 因此，在执行DELETE语句之前，应该先备份数据库，以防万一要找回删除过的数据。 假设要删除officeNumber为4的员工，则使用DELETE语句与WHERE子句作为以下查询： 要删除employees表中的所有行，请使用不带WHERE子句的DELETE语句，如下所示： 在执行上面查询语句后，employees表中的所有行都被删除。 MySQL DELETE和LIMIT子句 如果要限制要删除的行数，则使用LIMIT子句，如下所示： 请注意，表中的行顺序未指定，因此，当您使用LIMIT子句时，应始终使用ORDER BY子句，不然删除的记录可能不是你所预期的那样。 考虑在示例数据库(yiibaidb)中的customers表，其表结构如下： 例如，以下语句按客户名称按字母排序客户，并删除前10个客户： 类似地，以下DELETE语句选择法国(France)的客户，按升序按信用额度(creditLimit)进行排序，并删除前5个客户： 类似地，以下DELETE语句选择法国(France)的客户，按升序按信用额度(creditLimit)进行排序，并删除前5个客户： 查询表记录(select) 查询语法： 准备数据 where子句: 过滤查询 查询年纪大于24的员工 查询教学部的男老师信息 order：排序 按指定的列进行，排序的列即可是表中的列名，也可以是select语句后指定的别名。 语法： Asc 升序、Desc 降序，其中asc为默认值 ORDER BY 子句应位于SELECT语句的结尾。 示例： 按年龄从高到低进行排序 按工资从低到高进行排序 group by：分组查询（＊＊＊＊＊） GROUP BY 语句根据某个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG等函数进行相关查询。 语法: 示例： -- 查询男女员工各有多少人 -- 查询各个部门的人数 -- 查询每个部门最大的年龄 -- 查询每个部门年龄最大的员工姓名 -- 查询每个部门的平均工资 -- 查询教学部的员工最高工资: 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记录条数限制 正则表达式 多表查询 创建表 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)
decimal(m,d) 参数m<65 是总个数，d<30且 dMySQL数据类型 含义 char(n) 固定长度，最多255个字符 varchar(n) 固定长度，最多65535个字符 tinytext 可变长度，最多255个字符 text 可变长度，最多65535个字符 mediumtext 可变长度，最多2的24次方-1个字符 longtext 可变长度，最多2的32次方-1个字符
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的索引似乎不起作用。
2._BLOB存储的数据只能整体读出。
3._TEXT可以指定字符集，_BLO不用指定字符集。MySQL数据类型 含义 date 日期 '2008-12-2' time 时间 '12:25:36' datetime 日期时间 '2008-12-2 22:06:44' timestamp 自动存储记录修改时间 MySQL关键字 含义 NULL 数据列可包含NULL值 NOT NULL 数据列不允许包含NULL值 DEFAULT 默认值 PRIMARY KEY 主键 AUTO_INCREMENT 自动递增，适用于整数类型 UNSIGNED 无符号 CHARACTER SET name 指定一个字符集 数据库操作
CREATE DATABASE [IF NOT EXISTS] database_name;
CREATE DATABASE IF NOT EXISTS mytestdb;
+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || testdb || yiibaidb |+--------------------+5 rows in set
USE database_name;
USE yiibaidb;
DROP DATABASE [IF EXISTS] database_name;
CREATE DATABASE IF NOT EXISTS tempdb;SHOW DATABASES;DROP DATABASE IF EXISTS tempdb;
数据表操作
CREATE TABLE [IF NOT EXISTS] table_name( column_list) engine=table_type;
column_name data_type[size] [NOT NULL|NULL] [DEFAULT value] [AUTO_INCREMENT]
PRIMARY KEY (col1,col2,...)
CREATE TABLE IF NOT EXISTS tasks ( task_id INT(11) NOT NULL AUTO_INCREMENT, subject VARCHAR(45) DEFAULT NULL, start_date DATE DEFAULT NULL, end_date DATE DEFAULT NULL, description VARCHAR(200) DEFAULT NULL, PRIMARY KEY (task_id)) ENGINE=InnoDB;
ALTER TABLE table_name action1[,action2,…]
DROP TABLE IF EXISTS tasks;CREATE TABLE tasks ( task_id INT NOT NULL, subject VARCHAR(45) NULL, start_date DATE NULL, end_date DATE NULL, description VARCHAR(200) NULL, PRIMARY KEY (task_id), UNIQUE INDEX task_id_unique (task_id ASC));
ALTER TABLE tasksCHANGE COLUMN task_id task_id INT(11) NOT NULL AUTO_INCREMENT;
INSERT INTO tasks(subject, start_date, end_date, description)VALUES('Learn MySQL ALTER TABLE', Now(), Now(), 'Practicing MySQL ALTER TABLE statement');INSERT INTO tasks(subject, start_date, end_date, description)VALUES('Learn MySQL CREATE TABLE', Now(), Now(), 'Practicing MySQL CREATE TABLE statement');SELECT task_id, descriptionFROM tasks;
ALTER TABLE tasks ADD COLUMN complete DECIMAL(2,1) NULLAFTER description;
ALTER TABLE tasksDROP COLUMN description;
ALTER TABLE tasksRENAME TO work_items;
表记录操作
INSERT INTO table(column1,column2...)VALUES (value1,value2,...);
USE testdb;CREATE TABLE IF NOT EXISTS tasks ( task_id INT(11) AUTO_INCREMENT, subject VARCHAR(45) DEFAULT NULL, start_date DATE DEFAULT NULL, end_date DATE DEFAULT NULL, description VARCHAR(200) DEFAULT NULL, PRIMARY KEY (task_id))ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO tasks(subject,start_date,end_date,description)VALUES('Learn MySQL INSERT','2017-07-21','2017-07-22','Start learning..');SELECT * FROM tasks;
+---------+--------------------+------------+------------+------------------+| task_id | subject | start_date | end_date | description |+---------+--------------------+------------+------------+------------------+| 1 | Learn MySQL INSERT | 2017-07-21 | 2017-07-22 | Start learning.. |+---------+--------------------+------------+------------+------------------+1 row in set
INSERT INTO table(column1,column2...)VALUES (value1,value2,...), (value1,value2,...),...;
INSERT INTO tasks(subject,start_date,end_date,description)VALUES ('任务-1','2017-01-01','2017-01-02','Description 1'), ('任务-2','2017-01-01','2017-01-02','Description 2'), ('任务-3','2017-01-01','2017-01-02','Description 3');Query OK, 3 rows affectedRecords: 3 Duplicates: 0 Warnings: 0
select * from tasks;
INSERT INTO tableVALUES (value1,value2,...);或者INSERT INTO tableVALUES (value1,value2,...), (value1,value2,...),...;
INSERT INTO table_1SELECT c1, c2, FROM table_2;
CREATE TABLE tasks_bak LIKE tasks;
INSERT INTO tasks_bakSELECT * FROM tasks;
mysql> select * from tasks;+---------+--------------------+------------+------------+------------------+| task_id | subject | start_date | end_date | description |+---------+--------------------+------------+------------+------------------+| 1 | Learn MySQL INSERT | 2017-07-21 | 2017-07-22 | Start learning.. || 2 | 任务-1 | 2017-01-01 | 2017-01-02 | Description 1 || 3 | 任务-2 | 2017-01-01 | 2017-01-02 | Description 2 || 4 | 任务-3 | 2017-01-01 | 2017-01-02 | Description 3 |+---------+--------------------+------------+------------+------------------+4 rows in set
INSERT INTO tasks(task_id,subject,start_date,end_date,description)VALUES (4,'Test ON DUPLICATE KEY UPDATE','2017-01-01','2017-01-02','Next Priority');
Error Code: 1062. Duplicate entry '4' for key 'PRIMARY' 0.016 sec
INSERT INTO tasks(task_id,subject,start_date,end_date,description)VALUES (4,'Test ON DUPLICATE KEY UPDATE','2017-01-01','2017-01-02','Next Priority')ON DUPLICATE KEY UPDATE task_id = task_id + 1, subject = 'Test ON DUPLICATE KEY UPDATE';
mysql> select * from tasks;+---------+------------------------------+------------+------------+------------------+| task_id | subject | start_date | end_date | description |+---------+------------------------------+------------+------------+------------------+| 1 | Learn MySQL INSERT | 2017-07-21 | 2017-07-22 | Start learning.. || 2 | 任务-1 | 2017-01-01 | 2017-01-02 | Description 1 || 3 | 任务-2 | 2017-01-01 | 2017-01-02 | Description 2 || 5 | Test ON DUPLICATE KEY UPDATE | 2017-01-01 | 2017-01-02 | Description 3 |+---------+------------------------------+------------+------------+------------------+4 rows in set
UPDATE tasks SET task_id = task_id + 1, subject = 'Test ON DUPLICATE KEY UPDATE'WHERE task_id = 4;
UPDATE [LOW_PRIORITY] [IGNORE] table_name SET column_name1 = expr1, column_name2 = expr2, ...WHERE condition;
SELECT firstname, lastname, emailFROM employeesWHERE employeeNumber = 1056;
+-----------+-----------+----------------------+| firstname | lastname | email |+-----------+-----------+----------------------+| Mary | Patterson | mpatterso@yiibai.com |+-----------+-----------+----------------------+1 row in set
UPDATE employees SET email = 'mary.new@yiibai.com'WHERE employeeNumber = 1056;
SELECT firstname, lastname, emailFROM employeesWHERE employeeNumber = 1056;
+-----------+-----------+---------------------+| firstname | lastname | email |+-----------+-----------+---------------------+| Mary | Patterson | mary.new@yiibai.com |+-----------+-----------+---------------------+1 row in set
UPDATE employees SET lastname = 'Hill', email = 'mary.hill@yiibai.com'WHERE employeeNumber = 1056;
+-----------+----------+----------------------+| firstname | lastname | email |+-----------+----------+----------------------+| Mary | Hill | mary.hill@yiibai.com |+-----------+----------+----------------------+1 row in set
mysql> SELECT customername, salesRepEmployeeNumberFROM customersWHERE salesRepEmployeeNumber IS NULL;+--------------------------------+------------------------+| customername | salesRepEmployeeNumber |+--------------------------------+------------------------+| Havel & Zbyszek Co | NULL || Porto Imports Co. | NULL || Asian Shopping Network, Co | NULL || Natrlich Autos | NULL || ANG Resellers | NULL || Messner Shopping Network | NULL || Franken Gifts, Co | NULL || BG&E Collectables | NULL || Schuyler Imports | NULL || Der Hund Imports | NULL || Cramer Spezialitten, Ltd | NULL || Asian Treasures, Inc. | NULL || SAR Distributors, Co | NULL || Kommission Auto | NULL || Lisboa Souveniers, Inc | NULL || Stuttgart Collectable Exchange | NULL || Feuer Online Stores, Inc | NULL || Warburg Exchange | NULL || Anton Designs, Ltd. | NULL || Mit Vergngen & Co. | NULL || Kremlin Collectables, Co. | NULL || Raanan Stores, Inc | NULL |+--------------------------------+------------------------+22 rows in set
SELECT employeeNumberFROM employeesWHERE jobtitle = 'Sales Rep'ORDER BY RAND()LIMIT 1;
UPDATE customers SET salesRepEmployeeNumber = (SELECT employeeNumber FROM employees WHERE jobtitle = 'Sales Rep' LIMIT 1)WHERE salesRepEmployeeNumber IS NULL;
SELECT salesRepEmployeeNumberFROM customersWHERE salesRepEmployeeNumber IS NULL;
DELETE FROM table_nameWHERE condition;
+-------------+--------------+------+-----+---------+----------------+| 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 | |+-------------+--------------+------+-----+---------+----------------+4 rows in set
DELETE FROM employees WHERE officeCode = 4;
DELETE FROM employees;
DELETE FROM tableLIMIT row_count;
DELETE FROM table_nameORDER BY c1, c2, ...LIMIT row_count;
mysql> desc customers;+------------------------+---------------+------+-----+---------+-------+| 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 | |+------------------------+---------------+------+-----+---------+-------+13 rows in set
DELETE FROM customersORDER BY customerNameLIMIT 10;
DELETE FROM customersWHERE country = 'France'ORDER BY creditLimitLIMIT 5;
DELETE FROM customersWHERE country = 'France'ORDER BY creditLimitLIMIT 5;
SELECT *|field1,filed2 ... FROM tab_name WHERE 条件 GROUP BY field HAVING 筛选 ORDER BY field LIMIT 限制条数Mysql在执行sql语句时的执行顺序： -- from where select group by having order by
CREATE TABLE emp( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), gender ENUM("male","female","other"), age TINYINT, dep VARCHAR(20), city VARCHAR(20), salary DOUBLE(7,2));INSERT INTO emp (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 * FROM emp;mysql> SELECT * FROM emp;+----+-----------+--------+------+-----------+-----------+----------+| 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 |+----+-----------+--------+------+-----------+-----------+----------+rows in set (0.00 sec)
where字句中可以使用 比较运算符： > < >= <= <> != between 80 and 100 值在10到20之间 in(80,90,100) 值是10或20或30 like 'yuan%' /* pattern可以是%或者_， 如果是%则表示任意多字符，此例如唐僧,唐国强 如果是_则表示一个字符唐_，只有唐僧符合。两个_则表示两个字符：__ */ 逻辑运算符 在多个条件直接可以使用逻辑运算符 and or not
SELECT * FROM emp WHERE age>24;
SELECT * FROM emp WHERE dep="教学部" AND gender="male";
select *|field1,field2... from tab_name order by field [Asc|Desc]
SELECT * FROM emp ORDER BY age DESC ;
SELECT * FROM emp ORDER BY salary;
SELECT column_name, function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;
SELECT gender 性别,count(*) 人数 FROM emp5 GROUP BY gender;
SELECT dep 部门,count(*) 人数 FROM emp5 GROUP BY dep;
SELECT dep 部门,max(age) 最大年纪 FROM emp5 GROUP BY dep;
SELECT * FROM emp5 WHERE age in (SELECT max(age) FROM emp5 GROUP BY dep);
SELECT dep 部门,avg(salary) 最大年纪 FROM emp GROUP BY dep;
SELECT * from ExamResult limit 1;SELECT * from ExamResult limit 2,5; -- 跳过前两条显示接下来的五条纪录SELECT * from ExamResult limit 2,2;
SELECT * FROM employee WHERE emp_name REGEXP '^yu';SELECT * FROM employee WHERE emp_name REGEXP 'yun$';SELECT * FROM employee WHERE emp_name REGEXP 'm{2}';CREATE TABLE emp( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), salary DOUBLE(7,2), dep_id INT);INSERT INTO emp (name,salary,dep_id) VALUES ("张三",8000,2), ("李四",12000,1), ("王五",5000,2), ("赵六",8000,3), ("猪七",9000,1), ("周八",7000,4), ("蔡九",7000,2);CREATE TABLE dep( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20));INSERT INTO dep (name) VALUES ("教学部"), ("销售部"), ("人事部");
