MySQL之SQL基础

renshanshan

　　SQL是Structure Query Language(结构化查询语言）的缩写，它是关系型数据库的应用语言，由IBM在20世纪70年代开发，以实现关系型数据库中的信息检索。
　　在20世纪80年代初，美国国家标准局（ANSI)开始着手制定SQL标准，最早的ANSI标准于1986年完成，就被叫做SQL-86。正是由于SQL语言的标准化，所以大多数关系型数据库都支持SQL语言，它已经发展成为多种平台进行交互操作的底层会话语言。
　　SQL的分类：

• 　　DDL：数据定义语言，即是对数据库内部对象进行创建、删除、修改等操作的语言，和DML最大区别在于DML仅对表内数据进行操作，而不涉及到表的定义、结构的修改，更不会涉及其它对象，DBA使用较多。常用关键字包括create、drop、alter等。
  
• 　　DML：数据操作语言，用于添加、删除、更新和查询表中的记录，并检查数据的完整性，开发人员使用较多。常用的语句包括insert、delete、update、和select等。
  
• 　　DCL：数据控制语言，用于管理系统中的对象权限时使用，常用语句有grant、revoke等。
  

　　1 DDL语句示例：
　　1）创建数据库
　　mysql> show engines;                        #查看支持的引擎，包括默认的引擎
　　+------------+---------+------------------------------------------------------------+--------------+------+------------+
　　| Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |
　　+------------+---------+------------------------------------------------------------+--------------+------+------------+

　　| MRG_MYISAM | YES     | Collection of>　　| CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |
　　| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |
　　| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
　　| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
　　+------------+---------+------------------------------------------------------------+--------------+------+------------+
　　mysql> show character set;                   #查看系统支持的字符集
　　mysql> show variables like "character%";     #查看当前字符集设置
　　mysql> show variables like "collation%";     #查看字符集校验设置
　　mysql> create database test1;                #创建test1数据库
　　Query OK, 1 row affected (0.05 sec)
　　mysql> show databases;                       #查看数据库
　　+--------------------+
　　| Database           |
　　+--------------------+
　　| information_schema |
　　| mysql              |
　　| performance_schema |
　　| test               |
　　| test1              |
　　| testdb             |
　　mysql> create database t121 default character set utf8 collate utf8_general_ci;
　　Query OK, 1 row affected (0.00 sec)          #创建时，也可以指定字符集
　　2）删除数据库
　　mysql> drop database test1;
　　Query OK, 0 rows affected (0.01 sec)
　　mysql> show databases;
　　+--------------------+
　　| Database           |
　　+--------------------+
　　| information_schema |
　　| mysql              |
　　| performance_schema |
　　| test               |
　　| testdb             |
　　备注：数据库一旦被删除，库中所有的表也将被删除，因此，备份非常重要
　　3）创建表
　　mysql> use zwj;                              #选择数据库
　　mysql> create table emp                      #varchar(n)其中n代表字符数
　　-> (ename varchar(10),
　　-> hiredate date,
　　-> sal decimal(10,2),
　　-> deptno int(2));
　　Query OK, 0 rows affected (0.11 sec)
　　mysql> desc zwj.emp;                        #查看zwj库中的emp表的结构
　　+----------+---------------+------+-----+---------+-------+
　　| Field    | Type          | Null | Key | Default | Extra |
　　+----------+---------------+------+-----+---------+-------+
　　| ename    | varchar(10)   | YES  |     | NULL    |       |
　　| hiredate | date          | YES  |     | NULL    |       |
　　| sal      | decimal(10,2) | YES  |     | NULL    |       |
　　| deptno   | int(2)        | YES  |     | NULL    |       |
　　+----------+---------------+------+-----+---------+-------+
　　mysql> show create table zwj.emp\g          #查看创建表的SQL语句，包括使用的字符集
　　+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
　　| Table | Create Table                                                                                                                                                                                     |
　　+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
　　| emp   | 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=utf8 |
　　复制一张表
　　mysql> create table t119 like zwj.t118;
　　Query OK, 0 rows affected (0.04 sec)
　　mysql> insert into t119 select * from zwj.t118;
　　Query OK, 12 rows affected (0.00 sec)
　　Records: 12  Duplicates: 0  Warnings: 0
　　查看表的状态，了解两张表是否一致
　　mysql> use zwj;
　　Database changed
　　mysql> show table status\G
　　*************************** 1. row ***************************
　　Name: t118
　　Engine: MyISAM
　　Version: 10
　　Row_format: Fixed
　　Rows: 12
　　Avg_row_length: 30
　　Data_length: 360
　　Max_data_length: 8444249301319679
　　Index_length: 1024
　　Data_free: 0
　　Auto_increment: NULL
　　Create_time: 2017-04-29 08:20:18
　　Update_time: 2017-04-29 08:29:50
　　Check_time: NULL
　　Collation: latin1_swedish_ci
　　Checksum: NULL
　　Create_options:
　　Comment:
　　*************************** 2. row ***************************
　　Name: t119
　　Engine: MyISAM
　　Version: 10
　　Row_format: Fixed
　　Rows: 12
　　Avg_row_length: 30
　　Data_length: 360
　　Max_data_length: 8444249301319679
　　Index_length: 1024
　　Data_free: 0
　　Auto_increment: NULL
　　Create_time: 2017-04-29 09:09:12
　　Update_time: 2017-04-29 09:09:38
　　Check_time: NULL
　　Collation: latin1_swedish_ci
　　Checksum: NULL
　　Create_options:
　　Comment:
　　2 rows in set (0.00 sec)
　　查看指定表的状态信息
　　mysql> show table status like 't118'\G
　　*************************** 1. row ***************************
　　Name: t118
　　Engine: MyISAM
　　Version: 10
　　Row_format: Fixed
　　Rows: 12
　　Avg_row_length: 30
　　Data_length: 360
　　Max_data_length: 8444249301319679
　　Index_length: 1024
　　Data_free: 0
　　Auto_increment: NULL
　　Create_time: 2017-04-29 08:20:18
　　Update_time: 2017-04-29 08:29:50
　　Check_time: NULL
　　Collation: latin1_swedish_ci
　　Checksum: NULL
　　Create_options:
　　Comment:
　　1 row in set (0.00 sec)
　　4）删除表
　　mysql> drop table zwj.emp;
　　Query OK, 0 rows affected (0.05 sec)
　　5）修改表，需要用到alter table语句
　　修改表ename字段的定义，把varchar(10)改为varchar(20)

　　mysql>>　　Query OK, 0 rows affected (0.17 sec)
　　Records: 0  Duplicates: 0  Warnings: 0
　　mysql> desc emp;
　　+----------+---------------+------+-----+---------+-------+
　　| Field    | Type          | Null | Key | Default | Extra |
　　+----------+---------------+------+-----+---------+-------+
　　| ename    | varchar(20)   | YES  |     | NULL    |       |
　　| hiredate | date          | YES  |     | NULL    |       |
　　| sal      | decimal(10,2) | YES  |     | NULL    |       |
　　| deptno   | int(2)        | YES  |     | NULL    |       |
　　+----------+---------------+------+-----+---------+-------+
　　在表emp中新增字段age，类型为int(3):

　　mysql>>　　Query OK, 0 rows affected (0.08 sec)
　　Records: 0  Duplicates: 0  Warnings: 0
　　mysql> desc emp;
　　+----------+---------------+------+-----+---------+-------+
　　| Field    | Type          | Null | Key | Default | Extra |
　　+----------+---------------+------+-----+---------+-------+
　　| ename    | varchar(20)   | YES  |     | NULL    |       |
　　| hiredate | date          | YES  |     | NULL    |       |
　　| sal      | decimal(10,2) | YES  |     | NULL    |       |
　　| deptno   | int(2)        | YES  |     | NULL    |       |
　　| age      | int(3)        | YES  |     | NULL    |       |
　　+----------+---------------+------+-----+---------+-------+
　　5 rows in set (0.00 sec)
　　删除一个字段

　　mysql>>　　Query OK, 0 rows affected (0.08 sec)
　　Records: 0  Duplicates: 0  Warnings: 0
　　修改字段名称

　　mysql>>　　Query OK, 0 rows affected (0.03 sec)
　　Records: 0  Duplicates: 0  Warnings: 0
　　mysql> desc emp;
　　+----------+---------------+------+-----+---------+-------+
　　| Field    | Type          | Null | Key | Default | Extra |
　　+----------+---------------+------+-----+---------+-------+
　　| ename    | varchar(20)   | YES  |     | NULL    |       |
　　| hiredate | date          | YES  |     | NULL    |       |
　　| sal      | decimal(10,2) | YES  |     | NULL    |       |
　　| deptno   | int(2)        | YES  |     | NULL    |       |
　　| age1     | int(4)        | YES  |     | NULL    |       |
　　+----------+---------------+------+-----+---------+-------+
　　修改字段排列顺序

　　mysql>>　　Query OK, 0 rows affected (0.08 sec)
　　Records: 0  Duplicates: 0  Warnings: 0
　　mysql> desc emp;
　　+----------+---------------+------+-----+---------+-------+
　　| Field    | Type          | Null | Key | Default | Extra |
　　+----------+---------------+------+-----+---------+-------+
　　| ename    | varchar(20)   | YES  |     | NULL    |       |
　　| birth    | date          | YES  |     | NULL    |       |
　　| hiredate | date          | YES  |     | NULL    |       |
　　| sal      | decimal(10,2) | YES  |     | NULL    |       |
　　| deptno   | int(2)        | YES  |     | NULL    |       |
　　| age1     | int(4)        | YES  |     | NULL    |       |
　　+----------+---------------+------+-----+---------+-------+
　　6 rows in set (0.00 sec)
　　把字段deptno放在age1后面

　　mysql>>　　Query OK, 0 rows affected (0.14 sec)
　　Records: 0  Duplicates: 0  Warnings: 0
　　mysql> desc emp1;
　　+--------+-------------+------+-----+---------+-------+
　　| Field  | Type        | Null | Key | Default | Extra |
　　+--------+-------------+------+-----+---------+-------+
　　| age1   | int(4)      | YES  |     | NULL    |       |
　　| deptno | int(2)      | YES  |     | NULL    |       |
　　| ename  | varchar(20) | YES  |     | NULL    |       |
　　| birth  | date        | YES  |     | NULL    |       |
　　+--------+-------------+------+-----+---------+-------+
　　4 rows in set (0.00 sec)
　　把字段age1放在最前面

　　mysql>>　　Query OK, 0 rows affected (0.15 sec)
　　Records: 0  Duplicates: 0  Warnings: 0
　　mysql> desc emp;
　　+----------+---------------+------+-----+---------+-------+
　　| Field    | Type          | Null | Key | Default | Extra |
　　+----------+---------------+------+-----+---------+-------+
　　| age1     | int(4)        | YES  |     | NULL    |       |
　　| ename    | varchar(20)   | YES  |     | NULL    |       |
　　| birth    | date          | YES  |     | NULL    |       |
　　| hiredate | date          | YES  |     | NULL    |       |
　　| sal      | decimal(10,2) | YES  |     | NULL    |       |
　　| deptno   | int(2)        | YES  |     | NULL    |       |
　　+----------+---------------+------+-----+---------+-------+
　　修改表名：

　　mysql>>　　Query OK, 0 rows affected (0.02 sec)
　　mysql> show tables;
　　+---------------+
　　| Tables_in_zwj |
　　+---------------+
　　| emp1          |
　　+---------------+
　　1 row in set (0.00 sec)
　　2 DML语句示例
　　1）插入记录
　　mysql> insert into emp1(age1,ename,birth,deptno) values('555','aaa','2016-10-30','5');
　　Query OK, 1 row affected (0.03 sec)
　　也可以不指定字段名称，但values后面的顺序应该和字段的排列顺序一致
　　mysql> insert into emp1 values('666','bbb','2016-12-30','8');
　　mysql> select * from emp1;
　　+------+-------+------------+--------+
　　| age1 | ename | birth      | deptno |
　　+------+-------+------------+--------+
　　|  555 | aaa   | 2016-10-30 |      5 |
　　|  666 | bbb   | 2016-12-30 |      8 |
　　+------+-------+------------+--------+
　　一次插入多条记录
　　mysql> insert into emp1(age1,ename,birth,deptno)
　　-> values ('111','ccc','2011-11-30','4'),
　　-> ('666','ddd','2014-12-22','11'),
　　-> ('888','eee','2015-11-30','22'),
　　-> ('333','fff','2011-04-30','8');
　　Query OK, 4 rows affected (0.02 sec)
　　Records: 4  Duplicates: 0  Warnings: 0
　　mysql> select * from emp1;
　　+------+-------+------------+--------+
　　| age1 | ename | birth      | deptno |
　　+------+-------+------------+--------+
　　|  555 | aaa   | 2016-10-30 |      5 |
　　|  666 | bbb   | 2016-12-30 |      8 |
　　|  111 | ccc   | 2011-11-30 |      4 |
　　|  666 | ddd   | 2014-12-22 |     11 |
　　|  888 | eee   | 2015-11-30 |     22 |
　　|  333 | fff   | 2011-04-30 |      8 |
　　+------+-------+------------+--------+
　　6 rows in set (0.00 sec)
　　2）更新记录，通过update命令进行更改
　　mysql> update emp1 set age1=1000 where ename='aaa';
　　Query OK, 1 row affected (0.03 sec)
　　Rows matched: 1  Changed: 1  Warnings: 0
　　mysql> select * from emp1;
　　+------+-------+------------+--------+
　　| age1 | ename | birth      | deptno |
　　+------+-------+------------+--------+
　　| 1000 | aaa   | 2016-10-30 |      5 |
　　|  666 | bbb   | 2016-12-30 |      8 |
　　|  111 | ccc   | 2011-11-30 |      4 |
　　|  666 | ddd   | 2014-12-22 |     11 |
　　|  888 | eee   | 2015-11-30 |     22 |
　　|  333 | fff   | 2011-04-30 |      8 |
　　+------+-------+------------+--------+
　　6 rows in set (0.00 sec)
　　3）删除记录：
　　mysql> delete from emp1 where ename='bbb';
　　Query OK, 1 row affected (0.02 sec)
　　mysql> select * from emp1;
　　+------+--------+-------+------------+
　　| age1 | deptno | ename | birth      |
　　+------+--------+-------+------------+
　　|  111 |      4 | ccc   | 2011-11-30 |
　　|  666 |     11 | ddd   | 2014-12-22 |
　　|  888 |     22 | eee   | 2015-11-30 |
　　|  333 |      8 | fff   | 2011-04-30 |
　　+------+--------+-------+------------+
　　4 rows in set (0.00 sec)
　　4）查询记录
　　mysql> select age1,ename from zwj.emp1;
　　+------+-------+
　　| age1 | ename |
　　+------+-------+
　　|  666 | bbb   |
　　|  111 | ccc   |
　　|  666 | ddd   |
　　|  888 | eee   |
　　|  333 | fff   |
　　+------+-------+
　　把表中的记录去掉重复后显示出来，
　　mysql> select distinct age1 from emp1;        #distinct是关键字，age1是字段名
　　条件查询
　　mysql> select * from emp1 where age1='666';
　　+------+-------+------------+--------+
　　| age1 | ename | birth      | deptno |
　　+------+-------+------------+--------+
　　|  666 | bbb   | 2016-12-30 |      8 |
　　|  666 | ddd   | 2014-12-22 |     11 |
　　+------+-------+------------+--------+
　　组合条件查询：
　　mysql> select * from emp1 where ename='bbb' and birth select * from emp1 where ename='bbb' or birth select * from zwj.emp1 where ename regexp '^c';    #关键字regexp支持正则表达式
　　+------+--------+-------+------------+
　　| age1 | deptno | ename | birth      |
　　+------+--------+-------+------------+
　　|  111 |      4 | ccc   | 2011-11-30 |
　　+------+--------+-------+------------+
　　1 row in set (0.00 sec)
　　mysql> select * from zwj.emp1 where ename like 'c_c';     #短横表示匹配任意单个字符
　　+------+--------+-------+------------+
　　| age1 | deptno | ename | birth      |
　　+------+--------+-------+------------+
　　|  111 |      4 | ccc   | 2011-11-30 |
　　+------+--------+-------+------------+
　　1 row in set (0.01 sec)
　　mysql> select * from zwj.emp1 where ename like 'c%';     #%表示任意字符
　　+------+--------+-------+------------+
　　| age1 | deptno | ename | birth      |
　　+------+--------+-------+------------+
　　|  111 |      4 | ccc   | 2011-11-30 |
　　+------+--------+-------+------------+
　　1 row in set (0.00 sec)
　　排序和限制：关键字order by（默认升序排序）
　　mysql> select * from emp1 order by age1;
　　+------+-------+------------+--------+
　　| age1 | ename | birth      | deptno |
　　+------+-------+------------+--------+
　　|  111 | ccc   | 2011-11-30 |      4 |
　　|  333 | fff   | 2011-04-30 |      8 |
　　|  666 | bbb   | 2016-12-30 |      8 |
　　|  666 | ddd   | 2014-12-22 |     11 |
　　|  888 | eee   | 2015-11-30 |     22 |
　　+------+-------+------------+--------+
　　5 rows in set (0.00 sec)
　　对age1相同的记录，如果把字段deptno从高到低排列，可使用如下命令，desc表示降序。
　　mysql> select * from emp1 order by age1,deptno desc;
　　+------+-------+------------+--------+
　　| age1 | ename | birth      | deptno |
　　+------+-------+------------+--------+
　　|  111 | ccc   | 2011-11-30 |      4 |
　　|  333 | fff   | 2011-04-30 |      8 |
　　|  666 | ddd   | 2014-12-22 |     11 |
　　|  666 | bbb   | 2016-12-30 |      8 |
　　|  888 | eee   | 2015-11-30 |     22 |
　　+------+-------+------------+--------+
　　5 rows in set (0.01 sec)
　　对age1相同的记录，如果把字段deptno从低到高排列，可使用如下命令，asc表示升序。
　　mysql> select * from emp1 order by age1,deptno asc;
　　+------+-------+------------+--------+
　　| age1 | ename | birth      | deptno |
　　+------+-------+------------+--------+
　　|  111 | ccc   | 2011-11-30 |      4 |
　　|  333 | fff   | 2011-04-30 |      8 |
　　|  666 | bbb   | 2016-12-30 |      8 |
　　|  666 | ddd   | 2014-12-22 |     11 |
　　|  888 | eee   | 2015-11-30 |     22 |
　　+------+-------+------------+--------+
　　5 rows in set (0.01 sec)
　　选择排序后的前3条记录
　　mysql> select * from emp1 order by age1 limit 3;
　　+------+-------+------------+--------+
　　| age1 | ename | birth      | deptno |
　　+------+-------+------------+--------+
　　|  111 | ccc   | 2011-11-30 |      4 |
　　|  333 | fff   | 2011-04-30 |      8 |
　　|  666 | bbb   | 2016-12-30 |      8 |
　　+------+-------+------------+--------+
　　3 rows in set (0.00 sec)
　　降序排列后的前3条记录
　　mysql> select * from emp1 order by age1 desc limit 3;
　　+------+-------+------------+--------+
　　| age1 | ename | birth      | deptno |
　　+------+-------+------------+--------+
　　|  888 | eee   | 2015-11-30 |     22 |
　　|  666 | bbb   | 2016-12-30 |      8 |
　　|  666 | ddd   | 2014-12-22 |     11 |
　　+------+-------+------------+--------+
　　3 rows in set (0.00 sec)
　　显示以age1字段排序后，从第2条记录开始的前4条记录
　　mysql> select * from emp1 order by age1 limit 1,4;
　　+------+-------+------------+--------+
　　| age1 | ename | birth      | deptno |
　　+------+-------+------------+--------+
　　|  333 | fff   | 2011-04-30 |      8 |
　　|  666 | bbb   | 2016-12-30 |      8 |
　　|  666 | ddd   | 2014-12-22 |     11 |
　　|  888 | eee   | 2015-11-30 |     22 |
　　+------+-------+------------+--------+
　　4 rows in set (0.00 sec)
　　3 DCL语句示例：
　　新建用户并且授权

　　mysql> grant select,insert on mysql.* to 'abc'@'localhost'>　　Query OK, 0 rows affected (0.08 sec)
　　撤消权限
　　mysql> revoke insert on mysql.* from 'abc'@'localhost';
　　Query OK, 0 rows affected (0.00 sec)
　　查看当前用户权限
　　mysql> show grants;
　　+---------------------------------------------------------------------+
　　| Grants for root@localhost                                           |
　　+---------------------------------------------------------------------+
　　| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
　　| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
　　+---------------------------------------------------------------------+
　　查看某个用户权限
　　mysql> show grants for 'abc'@'localhost';
　　+------------------------------------------------------------------------------------------------------------+
　　| Grants for abc@localhost                                                                                   |
　　+------------------------------------------------------------------------------------------------------------+

　　| GRANT USAGE ON *.* TO 'abc'@'localhost'>　　| GRANT SELECT ON `mysql`.* TO 'abc'@'localhost'                                                             |
　　+------------------------------------------------------------------------------------------------------------+
　　2 rows in set (0.00 sec)