MySQL基础篇（1）SQL基础

　　SQL是Structure Query Language（结构化查询语言）的缩写，它是使用关系模型的数据库应用语言。

　　一、SQL分类（DDL，DML，DCL）

• DDL（Data Definition Languages）语句：数据库定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。常用的语句关键字主要包括create、drop、alter等。DDL语句更多地由数据库管理员（DBA）使用，开发人员一般很少使用。
• DML（Data Manipulation Languages）语句：数据库操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。常用的语句关键字主要包括insert、delete、updata和select等。
• DCL（Data Control Languages）语句：数据库控制语句，用于控制不同数据段直接的许可和访问级别的语句，定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括grant、revoke等。

　　1.DDL语句

　　（1）数据库

创建数据库：create database test1；
显示数据库：show databases;
选择要使用的数据库：use test1；
删除数据库：drop database test1；

　　（2）数据表

创建表：CREATE TABLE emp(ename varchar(10), hiredate date, sal decimal(10,2),deptno int(2));
查看表定义：DESC emp；
得到更全面的表定义信息：show create table emp G;
删除表：drop table emp；
修改表类型：ALTER TABLE emp MODIFY ename varchar(20);
增加表字段：ALTER TABLE emp ADD COLUMN age int(3);
删除表字段：ALTER TABLE emp DROP COLUMN age;
修改表名称：ALTER TABLE emp CHANGE ename myname varchar(255);（同时修改类型）
将新增的字段birth放在ename之后：ALTER TABLE emp ADD birth date AFTER myname;
修改age字段，将它放在最前面：ALTER TABLE emp modify age int(3) first;
修改表名：ALTER TABLE emp RENAME emp1;

　　2.DML语句

　　（1）插入记录

插入完整数据：INSERT INTO tb_manager VALUES(1,'mr','mrsoft');
插入数据的一部分：INSERT INTO tb_manager(name,PWD) VALUES('Lianjiang','lianjiang');
插入多条记录：INSERT INTO tb_manager(name,PWD) VALUES('lian','111'),('qiao','222'),('tian','333');

　　（2）更新记录

更新记录：UPDATE tb_borrow SET ifback=1 WHERE id=2;

　　（3）删除记录

使用DELETE语句删除：DELETE FROM tb_manager WHERE name='Lianjiang';
使用TRUNVATE语句清空表记录：TRUNCATE TABLE tb_manager;

　　（4）查询记录

• 基础查询

1.查询所有字段：SELECT * FROM tb_bookinfo;
2.查询指定字段：SELECT bookname,author FROM tb_bookinfo;
3.去掉重复记录：SELECT DISTINCT depto FROM emp；
4.查询指定数据：SELECT * FROM tb_bookinfo WHERE bookname='Tian King';
5.带IN关键字的范围查询：SELECT bookname,author,price,page,bookcase FROM tb_bookinfo WHERE bookcase IN(1,3);
6.带BETWEEN AND的范围查询：SELECT * FROM tb_bookinfo WHERE inTime BETWEEN '2017-04-17' and '2017-04-19';
7.带LIKE的字符匹配查询：SELECT * FROM tb_bookinfo WHERE barcode LIKE '%71%';
8.用IS NULL关键字查询空值：SELECT * FROM tb_bookinfo WHERE bookname IS NULL;
9.用ORDER BY关键字对查询结果排序（ASC表示升序，DESC表示降序，默认升序）：
SELECT * FROM tb_bookinfo ORDER BY price DESC;
SELECT * FROM tb_bookinfo ORDER BY typeid ASC;
10.用LIMIT关键字显示一部分，经常和order by一起使用：
SELECT * FROM tb_bookinfo ORDER BY price DESC LIMIT 2;
SELECT * FROM tb_bookinfo ORDER BY price DESC LIMIT 1,2;

• 聚合

1. 语法：

   SELECT [field1,field2,...fieldn] 
   fun_name：表示要做的聚合操作，也就是聚合函数，例如：SELECT COUNT(*)/SUM(price)/AVG(price)/MAX(price)/MIN(price) FROM tb_bookinfo;
   (count(1)就是除了第一行之外，返回非NULL值的行的数目，count(*)返回所有行的数目)
   FROM tablename:表名
   [WHERE where_condition] 
   [GROUP BY field1,field2,...fieldn]：表示要进行分类聚合的字段，比如要按照部门分类统计员工数量，那么部门就应该写在group by的后面
   [WITH ROLLUP]：表示是否对分类聚合后的结果进行再汇总
   [HAVING where_condition}：表示对分类后的结再进行条件的过滤

2. 1.创建表
   mysql> CREATE TABLE emp(ename varchar(10), hiredate date, sal decimal(10,2),deptno int(2));
   Query OK, 0 rows affected (0.02 sec)
   
   mysql> insert into emp value('bjguan','2004-04-02',5000.00,1),('zzx','2000-01-01',2000.00,1),('lisa','2003-02-01',4000.00,2),('bzshen','2005-04-01',4000.00,3);
   Query OK, 4 rows affected (0.00 sec)
   Records: 4  Duplicates: 0  Warnings: 0
   
   mysql> select * from emp;
   +--------+------------+---------+--------+
   | ename  | hiredate   | sal     | deptno |
   +--------+------------+---------+--------+
   | bjguan | 2004-04-02 | 5000.00 |      1 |
   | zzx    | 2000-01-01 | 2000.00 |      1 |
   | lisa   | 2003-02-01 | 4000.00 |      2 |
   | bzshen | 2005-04-01 | 4000.00 |      3 |
   +--------+------------+---------+--------+
   4 rows in set (0.00 sec)
   
   2.使用count函数
   mysql> select count(*) from emp;
   +----------+
   | count(*) |
   +----------+
   |        4 |
   +----------+
   1 row in set (0.00 sec)
   
   mysql> select count(1) from emp;
   +----------+
   | count(1) |
   +----------+
   |        4 |
   +----------+
   1 row in set (0.00 sec)
   
   mysql> select count(depto) from emp;
   ERROR 1054 (42S22): Unknown column 'depto' in 'field list'
   mysql> select count(deptno) from emp;
   +---------------+
   | count(deptno) |
   +---------------+
   |             4 |
   +---------------+
   1 row in set (0.00 sec)
   
   mysql> select count(distinct deptno) from emp;
   +------------------------+
   | count(distinct deptno) |
   +------------------------+
   |                      3 |
   +------------------------+
   1 row in set (0.00 sec)
   
   3.统计各个部分的人数
   mysql> select deptno, count(1) from emp group by deptno;
   +--------+----------+
   | deptno | count(1) |
   +--------+----------+
   |      1 |        2 |
   |      2 |        1 |
   |      3 |        1 |
   +--------+----------+
   3 rows in set (0.00 sec)
   
   4.既要统计各部门人数，又要统计总人数
   mysql> select deptno, count(1) from emp group by deptno with rollup;
   +--------+----------+
   | deptno | count(1) |
   +--------+----------+
   |      1 |        2 |
   |      2 |        1 |
   |      3 |        1 |
   |   NULL |        4 |
   +--------+----------+
   4 rows in set (0.00 sec)
   
   5.统计人数大于1的部门
   mysql> select deptno, count(1) from emp group by deptno;
   +--------+----------+
   | deptno | count(1) |
   +--------+----------+
   |      1 |        2 |
   |      2 |        1 |
   |      3 |        1 |
   +--------+----------+
   3 rows in set (0.00 sec)
   
   mysql> select deptno, count(1) from emp group by deptno having count(1)>1;
   +--------+----------+
   | deptno | count(1) |
   +--------+----------+
   |      1 |        2 |
   +--------+----------+
   1 row in set (0.00 sec)
   
   6.统计所有员工的薪水总额、最高和最低薪水
   mysql> select sum(sal),max(sal),min(sal) from emp;
   +----------+----------+----------+
   | sum(sal) | max(sal) | min(sal) |
   +----------+----------+----------+
   | 15000.00 |  5000.00 |  2000.00 |
   +----------+----------+----------+
   1 row in set (0.00 sec)

• 表连接

　　表连接分为内连接和外连接。内连接仅选出两种表中相互匹配的记录，而外连接会选出其他不匹配的记录。

　　外连接又分为左连接和右连接。

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

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

1.两个表的内容：emp中的dony所在的部门号是4，而dept表中没有编号为4的部门
mysql> select * from emp;
+--------+------------+---------+--------+
| ename  | hiredate   | sal     | deptno |
+--------+------------+---------+--------+
| bjguan | 2004-04-02 | 5000.00 |      1 |
| zzx    | 2000-01-01 | 2000.00 |      1 |
| lisa   | 2003-02-01 | 4000.00 |      2 |
| bzshen | 2005-04-01 | 4000.00 |      3 |
| dony   | 2005-02-05 | 2000.00 |      4 |
+--------+------------+---------+--------+
5 rows in set (0.00 sec)

mysql> select *  from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | tech     |
|      2 | sale     |
|      3 | hr       |
+--------+----------+
3 rows in set (0.00 sec)

2.内连接：仅选出两种表中相互匹配的记录
mysql> select ename,deptname from emp,dept where emp.deptno = dept.deptno;
+--------+----------+
| ename  | deptname |
+--------+----------+
| bjguan | tech     |
| zzx    | tech     |
| lisa   | sale     |
| bzshen | hr       |
+--------+----------+
4 rows in set (0.00 sec)

3.左连接：左是emp，右是dept，那么emp中所有记录都会被查询到
mysql> SELECT ename,deptname FROM emp LEFT JOIN dept ON emp.deptno = dept.deptno;
+--------+----------+
| ename  | deptname |
+--------+----------+
| bjguan | tech     |
| zzx    | tech     |
| lisa   | sale     |
| bzshen | hr       |
| dony   | NULL     |
+--------+----------+
5 rows in set (0.00 sec)

4.右连接，右是emp，左是dept
mysql> SELECT ename,deptname FROM emp RIGHT JOIN dept ON emp.deptno = dept.deptno;
+--------+----------+
| ename  | deptname |
+--------+----------+
| bjguan | tech     |
| zzx    | tech     |
| lisa   | sale     |
| bzshen | hr       |
+--------+----------+
4 rows in set (0.00 sec)

• 子查询

　　当进行查询的时候，需要的条件是另外一个select语句的结果，就要用到子查询，主要有关键字in、not in、=、!=（当子查询记录数唯一时，可以用=代替in，!=代替not in）、exists、not exists

　　在某些情况下，子查询可以转换成表连接，表连接在很多情况下用于优化子查询。

1.两个表的内容：
mysql> select * from emp;
+--------+------------+---------+--------+
| ename  | hiredate   | sal     | deptno |
+--------+------------+---------+--------+
| bjguan | 2004-04-02 | 5000.00 |      1 |
| zzx    | 2000-01-01 | 2000.00 |      1 |
| lisa   | 2003-02-01 | 4000.00 |      2 |
| bzshen | 2005-04-01 | 4000.00 |      3 |
| dony   | 2005-02-05 | 2000.00 |      4 |
+--------+------------+---------+--------+
5 rows in set (0.00 sec)

mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | tech     |
|      2 | sale     |
|      3 | hr       |
+--------+----------+
3 rows in set (0.00 sec)

2.从emp中查询出所有部门在dept表中的所有记录
mysql> select * from emp where deptno in(select deptno from dept);
+--------+------------+---------+--------+
| ename  | hiredate   | sal     | deptno |
+--------+------------+---------+--------+
| bjguan | 2004-04-02 | 5000.00 |      1 |
| zzx    | 2000-01-01 | 2000.00 |      1 |
| lisa   | 2003-02-01 | 4000.00 |      2 |
| bzshen | 2005-04-01 | 4000.00 |      3 |
+--------+------------+---------+--------+
4 rows in set (0.00 sec)

3.子查询数唯一，用=代替in
mysql> select * from emp where deptno = (select deptno from dept limit 1);
+--------+------------+---------+--------+
| ename  | hiredate   | sal     | deptno |
+--------+------------+---------+--------+
| bjguan | 2004-04-02 | 5000.00 |      1 |
| zzx    | 2000-01-01 | 2000.00 |      1 |
+--------+------------+---------+--------+
2 rows in set (0.00 sec)

4.子查询也可以用表连接来转换
mysql> select emp.* from emp, dept where emp.deptno = dept.deptno;
+--------+------------+---------+--------+
| ename  | hiredate   | sal     | deptno |
+--------+------------+---------+--------+
| bjguan | 2004-04-02 | 5000.00 |      1 |
| zzx    | 2000-01-01 | 2000.00 |      1 |
| lisa   | 2003-02-01 | 4000.00 |      2 |
| bzshen | 2005-04-01 | 4000.00 |      3 |
+--------+------------+---------+--------+
4 rows in set (0.00 sec)

• 记录联合

　　将两个表的数据按照一定的查询条件查询出来后，将结果合并到一起并显示出来，这个时候，就要用到UNION和UNION ALL关键字来实现了。

　　UNION ALL是直接把结果集直接合并在一起。

　　而UNION是将UNION ALL之后的结果进行了一次DINTINCT，取出重复记录后的结果。

mysql> select deptno from emp union all select deptno from dept;
+--------+
| deptno |
+--------+
|      1 |
|      1 |
|      2 |
|      3 |
|      4 |
|      1 |
|      2 |
|      3 |
+--------+
8 rows in set (0.00 sec)

mysql> select deptno from emp union select deptno from dept;
+--------+
| deptno |
+--------+
|      1 |
|      2 |
|      3 |
|      4 |
+--------+
4 rows in set (0.00 sec)

• 使用AS为表和字段取别名

1.为表取别名
mysql> SELECT bookname,author,price,page
    -> FROM tb_bookinfo AS book
    -> LEFT JOIN tb_borrow AS type ON book.typeid=type.id;
+-----------+-----------+-------+------+
| bookname  | author    | price | page |
+-----------+-----------+-------+------+
| Java King | LianJiang | 49.80 |  350 |
| Lian      | QiaoJiang | 50.00 |  351 |
| Tian King | TianJiang | 51.10 |  352 |
+-----------+-----------+-------+------+
3 rows in set (0.00 sec)

2.为字段取别名
mysql> SELECT del,COUNT(*) AS degree FROM tb_bookinfo GROUP BY del;
+------+--------+
| del  | degree |
+------+--------+
|    0 |      3 |
+------+--------+
1 row in set (0.00 sec)

　　3.DCL语句

　　DCL语句主要是DBA用来管理系统中的对象权限时使用，一般的开发人员很少使用。

　　例如，使用grant语句赋予某个用户user具有对test数据库中所有表的SELECT/INSERT权限，然后使用revoke收回该用户user对test数据库中所有表的INSERT权限，只保留SELECT权限。

　　二、帮助的使用

　　1.按照层次查看帮助

　　首先使用“？ contents”来显示所有的可供查询的分类

　　然后一级一级地使用“？ 某一个分类”来查看分类中可以提供的帮助，例如，上一级中显示了DATA TYPES，使用“？ DATA TYPES”

　　最后定位到需要查询的最后一级，例如“？ int”

mysql> ? contents
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
   Account Management
   Administration
   Compound Statements
   Data Definition
   Data Manipulation
   Data Types
   Functions
   Functions and Modifiers for Use with GROUP BY
   Geographic Features
   Help Metadata
   Language Structure
   Plugins
   Procedures
   Storage Engines
   Table Maintenance
   Transactions
   User-Defined Functions
   Utility

mysql> ? Data Types
You asked for help about help category: "Data Types"
For more information, type 'help <item>', where <item> is one of the following
topics:
   AUTO_INCREMENT
   BIGINT
   BINARY
   BIT
   BLOB
   BLOB DATA TYPE
   BOOLEAN
   CHAR
   CHAR BYTE
   DATE
   DATETIME
   DEC
   DECIMAL
   DOUBLE
   DOUBLE PRECISION
   ENUM
   FLOAT
   INT
   INTEGER
   LONGBLOB
   LONGTEXT
   MEDIUMBLOB
   MEDIUMINT
   MEDIUMTEXT
   SET DATA TYPE
   SMALLINT
   TEXT
   TIME
   TIMESTAMP
   TINYBLOB
   TINYINT
   TINYTEXT
   VARBINARY
   VARCHAR
   YEAR DATA TYPE

mysql> ? INT
Name: 'INT'
Description:
INT[(M)] [UNSIGNED] [ZEROFILL]

A normal-size integer. The signed range is -2147483648 to 2147483647.
The unsigned range is 0 to 4294967295.

URL: http://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html

　　2.快速查阅帮助

　　例如，向查看CREATE TABLE的语法，使用“？ create table”即可

　　三、查询元数据信息

　　1.先给出两个需求：（1）删除数据库test1下所有前缀为tmp的表，（2）将数据库test1下所有存储引擎为myisam的表改为innodb。

　　解决方法：MySQL使用数据库infomation_schema用来记录元数据信息。元数据是指数据的数据，比如表名、列名、列类型、索引名等表的各种属性名称。

select concat('drop table test1.' , table_name, ';') from tables where table_schema = 'test1' and table_name like 'tmp%';
select concat('alter table test1.' , table_name,' engine = innodb;') from talbes where table_schema='test1' and engine = 'MYISAM';

　　这个库比较特殊，它是一个虚拟数据库，物理上并不存在相关的目录和文件；库里show tables显示的各种“表”也不是实际存在的物理表，而全部是视图。

mysql> use information_schema;
Database changed
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| COLUMNS                               | ：提供了表中的列信息。               
| SCHEMATA                              | ：提供了当前MySQL实例中所有数据库的信息，show databases的结果取自此表                
| STATISTICS                            | ：提供了关于表索引的信息
| TABLES                                  ：提供了关于数据库中的表的信息（包括视图）
...                                        
+---------------------------------------+
61 rows in set (0.00 sec)