MySQL系列之四 SQL语法

系列教程

MySQL系列之开篇 MySQL关系型数据库基础概念
MySQL系列之一 MariaDB-server安装
MySQL系列之二多实例配置
MySQL系列之三基础篇
MySQL系列之五视图、存储函数、存储过程、触发器
MySQL系列之六用户与授权
MySQL系列之七 MySQL存储引擎
MySQL系列之八 MySQL服务器变量
MySQL系列之九 mysql查询缓存及索引
MySQL系列之十 MySQL事务隔离实现并发控制
MySQL系列之十一日志记录
MySQL系列之十二备份与恢复
MySQL系列之十三 MySQL的复制
MySQL系列之十四 MySQL的高可用实现
MySQL系列之十五 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

- INSERT, DELETE, UPDATE

- GRANT, REVOKE

- SELECT

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

COLLATE 'collate name' 设置排序规则

查看支持所有字符集：SHOW CHARACTER SET;
查看支持所有排序规则：SHOW COLLATION;

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

3、查看数据库列表

SHOW DATABASES;

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;

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;

增加属性 ADD

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

MariaDB [testdb]> ALTER TABLE students DROP phone;

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

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    |                |+--------+---------------------+------+-----+---------+----------------+

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]> ALTER TABLE students ADD address TEXT;  #加个字段做测试用

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

五、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'的信息

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

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';  #只显示男生的平均年龄信息

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

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 |+----+-------+

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、用在WHERE子句中的子查询

用于比较表达式中的子查询；子查询仅能返回单个值

MariaDB [testdb]> SELECT name,ages FROM students WHERE ages > (SELECT AVG(ages) FROM students);    #查询大于平均年龄的同学

2、用于FROM子句中的子查询

SELECT tb_alias.col1,... FROM (SELECT clause) AS tb_alias WHERE Clause;

八、数据类型

选择正确的数据类型对于获得高性能至关重要，三大原则：

更小的通常更好，尽量使用可正确存储数据的最小数据类型
简单就好，简单数据类型的操作通常需要更少的CPU周期
尽量避免NULL，包含为NULL的列，对MySQL更难优化

1、数值型

精确数值

INT
- TINYINT 微整型 1
- SMALLINT 小整型 2
- MEDIUMINT 中整型 3
- INT 整型 4
- BIGINT 大整型 8
DECIMAL 精确定点型

近似数值

FLOAT 单精度浮点型 4
DOUBLE 双精度浮点型 8
REAL
BIT

2、字符型

定长

- CHAR（不区分大小写）255
- BINARY（区分大小写）

变长

VARCHAR（不区分大小写）65,535
VARBINNARY（区分大小写）

TEXT（不区分大小写）

TINYTEXT 255
TEXT 65,535
MEDIUMTEXT 16,777,215
LONGTEXT 4,294,967,295

BLOB（区分大小写）

TINYBLOB 微二进制大对象 255
BLOB 二进制大对象 64K
MEDIUMBLOB 中二进制大对象 16M
LONGBLOB 长二进制大对象 4G

ENUM 枚举 65535种变化

SET 集合 1-64个字符串，可以随意组合

3、日期时间型

DATE 3
TIME 3
DATETIME 8
TIMESTAMP 4
YEAR{2|4} 1

4、布尔型

BOOL，BOOLEAN：布尔型，是TINYINT(1)的同义词。zero值被视为假。非zero值视为真。

参考官方文档：https://dev.mysql.com/doc/refman/5.5/en/data-types.html