MySQL系列之四 SQL语法_Mysql

一、SQL语言的简介和规范

是一种特定目的程序语言，用于管理关系数据库管理系统（RDBMS），或在关系流数据管理系统（RDSMS）中进行流处理。

20世纪70年代，IBM开发出SQL，用于DB2
1981年，IBM推出SQL/DS数据库
业内标准微软和Sybase的T-SQL，Oracle的PL/SQL
SQL作为关系型数据库所使用的标准语言，最初是基于IBM的实现在1986年被批准的。1987年，“国际标准化组织(ISO)”把ANSI(美国国家标准化组织) SQL作为国际标准。
SQL：ANSI SQL ——SQL-86, SQL-89, SQL-92, SQL-99, SQL-03

SQL语言的规范

在数据库系统中，SQL语句不区分大小写(建议用大写)
但字符串常量区分大小写
SQL语句可单行或多行书写，以“;”结尾
关键词不能跨多行或简写
用空格和缩进来提高语句的可读性
子句通常位于独立行，便于编辑，提高可读性
注释：

SQL标准：
- /* 注释内容 */ 多行注释
- -- 注释内容单行注释，注意有空格
MySQL注释： #

数据库对象的命名规则

- 必须以字母开头
- 可包括数字和三个特殊字符（# _ $）
- 不要使用MySQL的保留字
- 同一database(Schema)下的对象不能同名

SQL语句的分类

DDL: Data Defination Language 数据定义语言

- CREATE, DROP, ALTER

DML: Data Manipulation Language 数据操作语言

- INSERT, DELETE, UPDATE

DCL：Data Control Language 数据控制语言

- GRANT, REVOKE

DQL：Data Query Language 数据查询语言

- SELECT

二、数据库操作

1、创建库

CREATE DATABASE [IF NOT EXISTS] db_name; 创建数据库

CHARACTER SET 'character set name' 设置字符集类型

COLLATE 'collate name' 设置排序规则

查看支持所有字符集：SHOW CHARACTER SET;

查看支持所有排序规则：SHOW COLLATION;

MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS testdb;

2、删除库

我不会

3、查看数据库列表

SHOW DATABASES;

三、表操作

1、创建表

方法一：直接创建
CREATE TABLE [IF NOT EXISTS] tbl_name (create_definition,...)

MariaDB [testdb]> CREATE TABLE IF NOT EXISTS students (id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(30) NOT NULL,phone CHAR(11),gender ENUM('M','F'));

方法二：通过查询现存表创建；新表会被直接插入查询而来的数据

CREATE TABLE [IF NOT EXISTS] tbl_name select_statement

MariaDB [testdb]> CREATE TABLE user SELECT user,host,password FROM mysql.user;

如果只想模仿查询旧表创建一个无记录的表我们可以加入条件 WHERE 0=1;

MariaDB [testdb]> CREATE TABLE user2 SELECT user,host,password FROM mysql.user WHERE 0=1;

方法三：通过复制现存的表的表结构创建，但不复制数据

CREATE TABLE [IF NOT EXISTS] tbl_name LIKE old_tbl_name

MariaDB [testdb]> CREATE TABLE user3 LIKE mysql.user;

2、修改表

ALTER TABLE tbl_name [alter_specification [, alter_specification] ...]

增加属性 ADD

MariaDB [testdb]> ALTER TABLE students ADD age TINYINT AFTER name;

删除属性 DROP

MariaDB [testdb]> ALTER TABLE students DROP phone;

修改属性 CHANGE, MODIFY

MariaDB [testdb]> ALTER TABLE students CHANGE age ages TINYINT(2) NOT NULL;

MariaDB [testdb]> ALTER TABLE students MODIFY gender ENUM('M','F');

3、删除表

MariaDB [testdb]> DROP TABLE user3;

4、查看表

SHOW TABLES; 列出库中所有的表

DESC [db_name.]tb_name; 查看表结构

SHOW CREATE TABLE tbl_name; 查看创建表的命令

SHOW TABLE STATUS LIKE 'tbl_name'; 查看表状态

SHOW TABLE STATUS FROM db_name; 查看指定库中所有表状态

SHOW ENGINES; 查看所有存储引擎

四、DML: 数据操作语言

MariaDB [testdb]> DESC students; #示例表
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| ages | tinyint(2) | NO | | NULL | |
| gender | enum('M','F') | YES | | NULL | |
+--------+---------------------+------+-----+---------+----------------+

1、INSERT 插入数据

单条记录插入

INSERT INTO tb_name (col1,col2,...) VALUES (val1,val2,...);

MariaDB [testdb]> INSERT students(id,name,ages,gender) VALUES (1,'tom',26,'M');
MariaDB [testdb]> INSERT students(name,ages,gender) VALUES ('jerry',19,'M');
MariaDB [testdb]> INSERT students(name,ages,gender) VALUES ('maria',19,'M');
MariaDB [testdb]> INSERT students SET name='ouyangfeng',ages=56,gender='M';

多条记录插入

INSERT INTO tb_name (col1,col2,...) VALUES (val1,val2,...)[,(val1,val2,...),...];

MariaDB [testdb]> INSERT students(name,ages,gender) VALUES ('xiaolongnv',18,'F'),('dongfangbubai',28,'F');

MariaDB [testdb]> SELECT * FROM students;
+----+---------------+------+--------+
| id | name | ages | gender |
+----+---------------+------+--------+
| 1 | tom | 26 | M |
| 2 | jerry | 19 | M |
| 3 | maria | 19 | M |
| 4 | xiaolongnv | 18 | F |
| 5 | dongfangbubai | 28 | F |
| 6 | ouyangfeng | 56 | M |
+----+---------------+------+--------+

从其他表查询数据保存到此表中

MariaDB [testdb]> ALTER TABLE students ADD address TEXT; #加个字段做测试用

MariaDB [testdb]> INSERT students(name,address) SELECT user,host FROM mysql.user;

MariaDB [testdb]> SELECT * FROM students;
+----+---------------+------+--------+-----------+
| id | name | ages | gender | address |
+----+---------------+------+--------+-----------+
| 1 | tom | 26 | M | NULL |
| 2 | jerry | 19 | M | NULL |
| 3 | maria | 19 | M | NULL |
| 4 | xiaolongnv | 18 | F | NULL |
| 5 | dongfangbubai | 28 | F | NULL |
| 6 | ouyangfeng | 56 | M | NULL |
| 7 | root | 0 | NULL | 127.0.0.1 |
| 8 | root | 0 | NULL | ::1 |
| 9 | | 0 | NULL | centos7 |
| 10 | root | 0 | NULL | centos7 |
| 11 | | 0 | NULL | localhost |
| 12 | root | 0 | NULL | localhost |
+----+---------------+------+--------+-----------+

2、UPDATE 修改数据

UPDATE tbl_name SET col1=value1,col2=value2,... WHERE col=value;

MariaDB [testdb]> UPDATE students SET gender='F' WHERE id=3;

3、DELETE 删除数据

MariaDB [testdb]> DELETE FROM students WHERE name=''; #删除名字为空的记录
MariaDB [testdb]> TRUNCATE TABLE user; #情况表记录

注意：一定要有限制条件（WHERE | LIMIT），否则将修改所有行的指定字段

五、SELECT：数据查询

AS：别名
WHERE：指明过滤条件以实现“选择”的功能
- +, -, *, /, %：算术操作符
- =, !=, <>, >, <, >=, <=：比较操作符
- BETWEEN min_num AND max_num：在min_num和max_mun之间
- IN (element1,element2,...)：在element...中的
- IS NULL：为空
- IS NOT NULL：不为空
- LIKE：做匹配，像。。。
  %：任意长度的任意字符
  _：单个任意字符
- RLIKE：正则表达式，不建议用
- REGEXP：同上
- NOT, AND, OR, XOR：逻辑操作符
GROUP BY：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算
- AVG() 平均数
- MAX() 最大数
- MIN() 最小数
- COUNT() 统计
- SUM() 求和
- HAVING ：对分组聚合运算后的结果指定过滤条件。类似WHERE的作用，但只能在分组中使用
ORDER BY：排序
- ASC：正序，默认
- DESC：倒序
- -KEYWORD：在排序时在关键字前加-可以避免把NULL排在前边
LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制

1、选择

MariaDB [testdb]> SELECT * FROM students WHERE name='maria'; #查询maria的信息
MariaDB [testdb]> SELECT * FROM students WHERE id BETWEEN 2 AND 5; #查询2到5号学生的信息
MariaDB [testdb]> SELECT * FROM students WHERE name IN ('jerry','xiaolongnv'); #查询jerry和xiaolongnv的信息
MariaDB [testdb]> SELECT * FROM students WHERE gender IS NOT NULL; #查询年龄不为空的信息
MariaDB [testdb]> SELECT * FROM students WHERE name LIKE '%o%'; #查询姓名中包含'o'的信息

2、投影

MariaDB [testdb]> SELECT user AS 用户,host AS 主机,password AS 密码 FROM mysql.user;

3、分组

MariaDB [testdb]> SELECT gender,AVG(ages) FROM students GROUP BY gender; #查询男生、女生年龄的平均值
MariaDB [testdb]> SELECT gender,AVG(ages) FROM students GROUP BY gender HAVING gender='M'; #只显示男生的平均年龄信息

4、排序

MariaDB [testdb]> SELECT * FROM students ORDER BY ages DESC; #按年龄排序，倒序显示
MariaDB [testdb]> SELECT * FROM students WHERE ages > 0 ORDER BY ages LIMIT 3; #按年龄排序，过滤年龄大于0的，正序排序，取前三条记录

六、多表查询

MySQL系列之四 SQL语法

为了练习，我们将表在扩展一下

MariaDB [testdb]> DELETE FROM students WHERE id BETWEEN 7 AND 12;
MariaDB [testdb]> CREATE TABLE score (id TINYINT(2) UNSIGNED AUTO_INCREMENT PRIMARY KEY,score TINYINT(3));
MariaDB [testdb]> ALTER TABLE students ADD sid TINYINT(2);
MariaDB [testdb]> UPDATE students SET sid=6 WHERE id=6;
MariaDB [testdb]> INSERT score SET score=87;
MariaDB [testdb]> SELECT * FROM students;
+----+---------------+------+--------+---------+------+
| id | name | ages | gender | address | sid |
+----+---------------+------+--------+---------+------+
| 1 | tom | 26 | M | NULL | 1 |
| 2 | jerry | 19 | M | NULL | 2 |
| 3 | maria | 19 | F | NULL | 3 |
| 4 | xiaolongnv | 18 | F | NULL | 4 |
| 5 | dongfangbubai | 28 | F | NULL | 5 |
| 6 | ouyangfeng | 56 | M | NULL | 6 |
+----+---------------+------+--------+---------+------+
MariaDB [testdb]> SELECT * FROM score;
+----+-------+
| id | score |
+----+-------+
| 1 | 99 |
| 2 | 98 |
| 3 | 88 |
| 4 | 68 |
| 5 | 78 |
| 6 | 87 |
+----+-------+

JOIN ON：交叉连接

INNER JOIN ON：内连接

LEFT OUTER JOIN ON：左外连接

RIGHT OUTER JOIN ON：右外连接

UNION ON：完全外连接

MariaDB [testdb]> SELECT * FROM students AS s,score AS o WHERE s.sid=o.id; #俩张表取交集

1、交叉连接

MariaDB [testdb]> SELECT * FROM students JOIN score;

2、内连接

MariaDB [testdb]> SELECT t.name,s.score FROM students AS t INNER JOIN score AS s ON t.sid=s.id;
+---------------+-------+
| name | score |
+---------------+-------+
| tom | 99 |
| jerry | 98 |
| maria | 88 |
| xiaolongnv | 68 |
| dongfangbubai | 78 |
| ouyangfeng | 87 |
+---------------+-------+

3、外连接

MariaDB [testdb]> SELECT t.name,s.score FROM students AS t LEFT JOIN score AS s ON t.sid=s.id; #左外连接
+---------------+-------+
| name | score |
+---------------+-------+
| tom | 99 |
| jerry | 98 |
| maria | 88 |
| xiaolongnv | 68 |
| dongfangbubai | 78 |
| ouyangfeng | 87 |
+---------------+-------+

MariaDB [testdb]> SELECT * FROM students AS t RIGHT JOIN score AS s ON t.sid=s.id; #右外连接

4、完全外连接

MariaDB [testdb]> SELECT name,address FROM students
-> UNION
-> SELECT user,host FROM mysql.user;
+---------------+-----------+
| name | address |
+---------------+-----------+
| tom | NULL |
| jerry | NULL |
| maria | NULL |
| xiaolongnv | NULL |
| dongfangbubai | NULL |
| ouyangfeng | NULL |
| root | 127.0.0.1 |
| root | ::1 |
| | centos7 |
| root | centos7 |
| | localhost |
| root | localhost |
+---------------+-----------+

5、自连接

MariaDB [testdb]> ALTER TABLE students ADD tid TINYINT(2); #再加一个tid字段
MariaDB [testdb]> SELECT * FROM students;
+----+---------------+------+--------+---------+------+------+
| id | name | ages | gender | address | sid | tid |
+----+---------------+------+--------+---------+------+------+
| 1 | tom | 26 | M | NULL | 1 | 2 |
| 2 | jerry | 19 | M | NULL | 2 | 1 |
| 3 | maria | 19 | F | NULL | 3 | 4 |
| 4 | xiaolongnv | 18 | F | NULL | 4 | 5 |
| 5 | dongfangbubai | 28 | F | NULL | 5 | 4 |
| 6 | ouyangfeng | 56 | M | NULL | 6 | 4 |
+----+---------------+------+--------+---------+------+------+

MariaDB [testdb]> SELECT s1.name AS studentname,s2.name AS teachername FROM students AS s1 INNER JOIN students AS s2 ON s1.id=s2.tid;
+---------------+---------------+
| studentname | teachername |
+---------------+---------------+
| jerry | tom |
| tom | jerry |
| xiaolongnv | maria |
| dongfangbubai | xiaolongnv |
| xiaolongnv | dongfangbubai |
| xiaolongnv | ouyangfeng |
+---------------+---------------+