MySQL\SQL的语言基础

　作为一个关系型数据库，　MySQL通过使用SQL语言(Structure Query Language)与之交互，用来完成用户所需要的各种工作。SQL是关系模型的数据库应用语言，由IBM在20世纪70年代为其关系型数据库 System R 所开发。后来美国佬又制定了SQL标准，到现在，SQL已经很完善了。

　　一、SQL分类

　　SQL语句按照其功能范围不同可分为以下3种类别：

1. 　　DDL(Data Defintion Language)语句：数据定义语句，用于定义不同的数据段、数据库、表、列、索引等。常用的语句关键字包括create、drop、alter等。
2. 　　DML(Data Manipulation Language)语句：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据的完整性。常用的语句关键字主要包括insert、delete、update和select等。
3. 　　DCL(Data Control Language)语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括grant、revoke等。

　　二、连接数据库

　　mysql -h localhost -u root -p

　　说明：

•     mysql 表示 mysql 数据库启动工具
•     -h表示数据库连接地址(同一个系统环境下可以省略)
•     -u 表示要登录的用户
•     -p 表示使用密码登录。

    回车之后，输入密码，密码输入时的字符是不可见的，输完密码直接回车登录。

　　如果没有什么别的问题，登陆成功之后会出现以下所示的内容：

　　➜ ~ mysql -u root -p

　　Enter password:

　　Welcome to the MySQL monitor. Commands end with ; or \g.

　　Your MySQL connection id is 7

　　Server version: 5.6.25 MySQL Community Server (GPL)

　　Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

　　Oracle is a registered trademark of Oracle Corporation and/or its

　　affiliates. Other names may be trademarks of their respective

　　owners.

　　Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

　　mysql>

　　mysql> 表示等待输入指令。最后一行的意思是，通过 ‘help;’ 或者 ‘\h’ 命令来显示帮助内容，通过 ‘\c’ 命令来清除命令行buffer。

　　二、DDL语句

　　DDL为数据定义语言，简单来说，就是对数据库内容的对象进行创建、修改、删除和操作语言，它与DML最大的区别在于DML是对表内部数据的操作，不涉及表的定义、结构的修改，也不涉及其他对象。

　　1.创建数据库

　　mysql> create database test;

　　Query OK, 1 row affected (0.00 sec)

　　“Query OK” 表示上面的命令执行成功，所有的 DDL 与 DML(不包 括 SELECT)操作执行成功后都显示“Query OK”，这里理解为执行成功就可以了;“1 row affected” 表示操作只影响了数据库中一行的记录，“0.00 sec”则记录了操作执行的时间。

　　如果已经存在这个数据库，系统会出现如下所示的:

　　mysql> create database test;

　　ERROR 1007 (HY000): Can't create database 'test'; database exists

　　2.查看数据库

如下面所示的代码：

　图1

　　3.选中数据库

　　mysql> use test;

　　Database changed

　　根据以上的代码，这样就进入到了 test 数据库中了。当然你可以使用 use 语句随时切换要操作的数据库，刚刚选中了 test ，现在我们切换到mysql内容的 mysql 数据库看看，如以下所示：

　　mysql> use mysql;

　　Reading table information for completion of table and column names

　　You can turn off this feature to get a quicker startup with -A

　　Database changed

　　出现 ” Database changed“ 表示切换成功。然后，看看 mysql数据库里面有什么内容(和查看当前数据库服务器数据库一样使用 show 语句)：

　图2

　　这些表里面的内容是关系数据库服务器相关的权限、用户、数据库状态、设置等相关的信息数据。

　　4.删除数据库

　　mysql> drop database test;

　　Query OK, 0 rows affected (0.01 sec)

　　说明：数据库删除后，下面的所有数据都会全部删除，所以删除前一定要慎重并做好相应的备份。

　　5.创建表

   代码如下所示：

　　mysql> create table emp(

　　ename varchar(10),

　　hiredate date,

　　sal decimal(10,2),

　　deptno int(2)

　　);

　　Query OK, 0 rows affected (0.63 sec

　　mysql> create table dept( deptno int(4), deptname varchar(20));

　　Query OK, 0 rows affected (0.12 sec)

　　6.查看表字段结构信息

如下所示：

　  图3

　　7.查看表的创建SQL语句

    代码如下所示：

　　mysql> show create table emp \G;

　　*************************** 1. row ***************************

　　Table: emp

　　Create Table: CREATE TABLE `emp` (

　　`ename` varchar(10) DEFAULT NULL,

　　`hiredate` date DEFAULT NULL,

　　`sal` decimal(10,2) DEFAULT NULL,

　　`deptno` int(2) DEFAULT NULL

　　) ENGINE=InnoDB DEFAULT CHARSET=latin1

　　1 row in set (0.00 sec)

　　ERROR:

　　No query specified

　　上面表的创建 SQL 语句中，除了可以看到表定义以外，还可以看到表的 engine(存储引擎) 和 charset(字符集)等信息。“\G”选项的含义是使得记录能够按照字段竖着排列，对于内 容比较长的记录更易于显示。

　　8.删除表

    代码如下所示：

　　mysql> drop table emp;

　　Query OK, 0 rows affected (0.34 sec)

　　9.修改表

　　9.1修改表字段类型

   代码如下所示：

　图4

　　9.2删除表字段

   代码如下所示：

   图5

　　9.3表字段改名

   代码如下所示：

　　图6

　　9.4修改表字段排列顺序

　　在前的字段增加与修改语句(add/change/modify)中，最后都可以加一个可选项 first|after column_name，这个选项可以用来修改字段在表中的位置，默认add增加的新字段加在表的最后，change/modify不会改变字段位置。下例将新增能加的字段放在第二位，如下所示：

  　图7

　　修改字段 age,将它放在最前面，如下所示:

    图8

　　9.5修改表名

   如下所示：

　　图9

　　10.DML语句

　　DML操作是指对数据中表记录的操作，主要包括以下内容：

•    记录的插入(insert)
•    更新(update)
•    删除(delete)
•    查询(select)。

　　11.插入记录

   如下所示：

　　mysql> insert into emp (ename,hiredate,sal,deptno) values('zzx1','2000-01-01','2000',1);

　　Query OK, 1 row affected (0.08 sec)

　　也可以不指定字段名称，但是 values 后面的顺序应该和字段的排序一致，如下所示：

  　图10

　　当然，你也可以一次插入对条记录，各条语句之间使用都好隔开，如下所示：

　　mysql> insert into emp

　　-> values(null, 'test', '2010-01-1', 5),

　　-> (null, 'test', '2010-01-1', 5),

　　-> (null, 'test', '2010-01-1', 5),

　　-> (null, 'test', '2010-01-1', 5);

　　Query OK, 4 rows affected (0.31 sec)

　　Records: 4 Duplicates: 0 Warnings: 0

　　12、查询记录

　12.1基础查询

 如下所示：

   图11

　　”*” 是一种正则表达式的写法，表示匹配所有，上面的查询语句和下面的是等价，如下所示：

　  图12

　　12.2查询指定的字段

  　图13

　　12.3条件查询

   　图14

　　结果集中将符合条件的记录列出来。上面的例子中，where 后面的田间是一个字段的 ‘=’ 比较，除此之外，还可以使用>、<、>=、<=、!=等比较运算符;多个条件还可以使用 or 、 and 等逻辑运算符进行多条件联合查询，运算符后面再说。下面是一个使用多字段条件查询的例子，如下所示：

  　图15

　　13.结果集排序

　　在 select 出来之后的结果集中排序使用 order by ，其中 desc 和 asc 是排序顺序中的关键字。desc 表示按照字段进行降序排列，asc 表示升序排列，如果不写关键字默认升序排列。如下所示：

　  图16

　　order by 后面可以跟多个不同的字段排序，并且排序字段的不同结果集的顺序也不同，如果排序字段的值一样，则值相同的字段按照第二个排序字段进行排序。如下所示：

  　图17

　　如果只有一个排序字段，则字段相同的记录将会无序排列。

　　图18

　　14.结果集限制

　　对于查询或者排序后的结果集，如果希望只显示一部分而不是全部，使用 limit 关键字结果集数量限制。如下面所示的5条记录：

   　图19

　　堆排序的结果集限制，如下所示：

    图20

　　在结果集中取一个区间，这个功能经常用来记录的分页显示，如下所示：

　　图21

　　注：limit数据MySQL扩展SQL92后的语法，在其他数据库并不能通用。

　　15.聚合

　　使用聚合操作进行一些汇总操作，比如统计整个公司的人数或者统计每个部门的人数。聚合操作语法如下所示：

　　SELECT [field1,field2,......fieldn] fun_name FROM tablename

　　[WHERE where_contition]

　　[GROUP BY field1,field2,......fieldn

　　[WITH ROLLUP]]

　　[HAVING where_contition]

　　下面所说的是聚合所需要使用的参数关键字：

• 　　fun_name：表示要做的聚合操作，也就是聚合函数，常用的有 sun(求和)、count(*)(记录数)、max(最大值)、min(最小值)。
• 　　group by：表示要进行分类聚合的字段，比如按照部门分类统计员工数量，部门卸载 group by 后面。
• 　　with rollup：可选语法，表示是否对分类聚合后的结果进行再汇总。
• 　　having：表示对分类后的结果再进行条件的过滤。

　　注意:having 和 where 的区别在于 having 是对聚合后的结果进行条件的过滤,而 where 是在聚 合前就对记录进行过滤,如果逻辑允许,我们尽可能用 where 先过滤记录,这样因为结果 集减小,将对聚合的效率大大提高,最后再根据逻辑看是否用 having 进行再过滤。

　　统计总数：

• 　　count(*)   如下所示

　　图22

　　count(1) 中的’1’表示按照第一个字段统计。下面看看统计的另一种情况，当 count 括号中为 ‘null’ 会是什么情况?  如下所示

　　图23

　　结果出现了 0 ，这个说明当括号中为null时，忽略所有字段为空的记录。想了解更多原因，请百度。

　　group by

　　统计分组(分类)各总数， 如下所示：

　图24

　　with rollup

　　在上面的基础上统计总数，如下所示：

  　图25

　　having

　　统计人数大于2的分类，如下所示：

　  图26

　　sun、max、min

　　统计累加、最高与最低：

　  图27

　　16.表连接

　　当需要查询多个表中的字段时，就可以使用表连接来实现。表连接分为内连接和外连接，他们之间最主要的区别是内连接仅选出两张表中相互匹配的记录，而外连接会选出其他不匹配的记录。我们常用的十内连接。

　　16.1内连接

图28

　　16.2外连接

　　外连接又分为左连接和右链接，具体定义如下所示：

　　左连接：包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录

　图29

　　右连接：包含所有的右边表中的记录甚至是右边表中没有和它匹配的记录

　图30

　　17.子查询

　　有时候，当我们查询的时候，需要的条件是另外一个select语句的结果，这时就需要使用子查询。用于子查询的关键字包括in、not in、=、!=、exists、not exists等。

　图31

　　如果子查询记录数唯一，可以用 = 代替 in：

　图32

　　某些情况下，子查询可以转化为表连接如下所示：

图33

　　18.记录联合

　　使用 union 与 union all 关键字，将两个表的数据按照一定的查询条件查询出来后，将结果合并到一起显示。两者主要的区别是把结果直接合并在一起，而 union 是将 union all 后的结果进行一次distinct，去除重复记录后的结果。

  图34

　　17.更新记录

 　图35

　　18.修改多个字段

 　图36

　　同时对两个表进行更新：

　　mysql> update emp a,dept b set a.sal=a.sal*b.deptno,b.deptname=a.ename where a.deptno=b.deptno;

　　Query OK, 3 rows affected (0.04 sec) Rows matched: 5 Changed: 3 Warnings: 0

　　删除记录

　　# 使用 delete 删除记录

　　mysql> delete from emp where ename='a';

　　Query OK, 1 row affected (0.08 sec)

　　关联删除，如下所示：

　　mysql> delete emp, dept from emp, dept where emp.deptno=dept.deptno and emp.deptno=2;

　　Query OK, 6 rows affected (0.03 sec)

　　DCL语句

　　创建库用户

　　# 创建数据库用户 anoxia ，具有对test数据库中所有标的 select / insert 权限

　　mysql> grant select, insert on test.* to 'anoxia'@'localhost' identified by '4311';

　　Query OK, 0 rows affected (0.00 sec)

　　移除权限，如下所示：

　　mysql> revoke insert on test.* from 'anoxia'@'localhost';

　　Query OK, 0 rows affected (0.30 sec)