SQL 语言

1. SQL 语言概述

SQL : 结构化查询语言, Structured Query Language;
SQL 的作用: 客户端使用 SQL 来操作服务器;
SQL 标准, 对 DBMS 的同一操作方式;
SQL 方言, 某种 DBMS 自己所独有的语法. 例如, limit 语句只在 MySQL 中可以使用.

2. SQL 语法

SQL 语句可以在单行或多行书写, 以分号结尾;
可使用空格和缩进来增强语句的可读性;
MySQL 不区分大小写, 建议关键字使用大写;

3. SQL 语句分类

DDL (Data Definition Language), 数据库定义语言
- 用来定义数据库对象. 可以创建, 删除, 修改: 库, 表结构!!
DML (Data Manipulation Language), 数据操作语言
- 用来定义数据库记录(数据). 可以增, 删,改: 表记录.
DQL (Data Query Language), 数据查询语言
- 用来查询记录(数据).
DCL (Data Control Language), 数据控制语言
- 用来定义访问权限和安全级别. 对用户的创建及授权.

3.1 DDL --- 操作数据库

3.1.1 对数据库的操作

查看所有数据库: SHOW DATABASES;
切换(选择要操作的)数据库: USE 数据库名;
创建数据库(方括号中内容可选): CREATE DATABASE [IF NOT EXISTS] mydb1 [CHARSET=utf8];
删除数据库: DROP DATABASE [IF EXISTS] mydb1;
修改数据库编码: ALTER DATABASE mydb1 CHARACTER SET utf8; (较少使用)

3.1.2 数据类型(列类型)

int : 整型;
double : 浮点型, 例如 double(5,2)表示最多5位, 其中必须有 2 位小数, 即最大值为 999.99;
decimal : 浮点型, 在表单是钱的时候, 使用该类型, 不会出现精度缺失问题;
char : 固定长度字符串类型, 最大值为 255.
例如 char(11)表示存储长度为 11 的字符串, 数据长度不足, 使用空格补足到指定长度.
varchar : 可变长度字符串类型, 最大值为 65535.
text : 字符串数据,MySQL 中特有的类型. SQL 标准中为 clob.
分为: tinytext(256B), text(64K), mediumtext(16M), longtext(4G);
blob : 字节数据, 分为: tineblob(256B), blob(64K), mediumblob(16M), longblob(4G);
date : 日期类型, 格式为: yyyy-MM-dd;
time : 时间类型, 格式为: hh:mm:ss;
timestamp : 时间戳类型;
在数据库中, 所有的字符串类型必须使用单引号!!! 不能使用双引号, 日期类型也要使用单引号!

3.1.3 对表的操作

创建表(需要指定列名, 列类型): CREATE TABLE [IF NOT EXISTS] 表名(列名1 列类型1, 列名2 列类型2,...);
查看当前数据库中所有表名称: SHOW TABLES;
查看指定表的创建语句: SHOW CREATE TABLE 表名; (较少使用)
查看表结构(列名+列类型): DESC 表名;
删除表: DROP TABLE 表名;
修改表分为: 添加列,修改列类型,修改列名,删除列,修改表名称, 前缀都是: ALTER TABLE 表名具体操作
添加列: ALTER TABLE 表名 ADD(列名1 列类型, 列名2 列类型, ...);
修改列类型: ALTER TABLE 表名 MODIFY 列名列的新类型;
修改列名: ALTER TABLE 表名 CHANGE 原列名新列名列类型;
删除列: ALTER TABLE 表名 DROP 列名;
修改表名称: ALTER TABLE 原表名 RENAME TO 新表名;

3.2 DML --- 操作表记录(数据)

3.2.1 插入数据

INSERT INTO 表名 (列名1, 列名2, ...) VALUES (列值1, 列值2, ...);
- 表名后给出要插入数据的列名.插入记录总是插入一行,不可能插入半行.所以如果未指定列值的列, 默认插入NULL.
- 在 VALUES 后给出列值, 值的顺序和个数必须与前面指定的列对应.
INSERT INTO 表名 VALUES(列值1, 列值2, ...);
- 没有给出要插入的列, 那么表示插入所有列.
- 值的个数必须是该表列的个数.
- 值的顺序必须与表创建时给出的列的顺序相同.
- 在数据库中,所有的字符串类型必须使用单引号!! 不能使用双引号,日期类型也要使用单引号!!

3.2.2 修改数据

UPDATE 表名 SET 列名1=列值1, 列名2=列值2, ....[WHERE 条件];
- 其中条件是可选的.
- 条件必须是一个 boolean 类型的值或表达式, 只有条件为 true 时,数据才可以被修改.
  例如 UPDATE t_stu SET gender='male' age=age+1 WHERE sid='10011101';
- 运算符: = != <>(不等于) < >= <= > BETWEEN...AND IN(....) IS NULL NOT OR AND
- UPDATE t_stu SET age=18 WHERE age=null; 无法完成修改, "age=null" 返回结果为 false;
  如果想要完成修改, 需要使用 UPDATE t_stu SET age=18 WHERE age IS NULL;

3.2.3 删除数据

DELETE FROM 表名 [WHERE 条件]; 不使用 where, 表示删除整张表;使用where, 删除行数据.
TRUNCATE TABLE 表名; TRUNCATE 是 DDL 语句, 它是先删除整张表(drop), 在创建该表(空白表), 无法回滚!!

3.3 DCL --- 用户访问权限

3.3.1 创建用户

CREATE USER 用户名@IP地址 IDENTIFIED BY '密码';
- 用户只能在指定的 IP 地址上登录
CREATE USER 用户名@'%' IDENTIFIED BY '密码';
- 用户可以在任意 IP 地址上登录

3.3.2 给用户授权

GRANT 权限1,....,权限n ON 数据库.* TO 用户名@IP地址;
- 权限, 用户, 数据库
- 给用户分派在指定的数据库上的指定的权限
- 例如: GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON mydb1.* TO user1@localhost;
- ```
 给 user1 用户分派在 mydb1 数据库上的 create, alter, drop, insert, update, delete, select 权限
```
GRANT ALL ON 数据库.* TO 用户名@IP地址;
- 给用户分派指定数据库上的所有权限

3.3.3 撤销授权

REVOKE 权限1, ... , 权限n ON 数据库.* FROM 用户名@IP地址;
- 撤销指定用户在指定数据库上的指定权限
- 例如: REVOKE CREATE,ALTER,DROP ON mydb1.* FROM user1@localhost;
  撤销 user1 用户在 mydb1 数据库上的 create, alter, drop 权限

3.3.4 查看权限

SHOW GRANTS FOR 用户名@IP地址;
- 查看指定用户的权限

3.3.5 删除用户

DROP USER 用户名@IP地址;

3.4 DQL --- 数据查询语言

查询不会修改数据库表记录!!!

3.4.1 基本查询

字段(列)控制

a) 查询所有列 : SELECT * FROM 表名;

b) 查询指定列 : SELECT 列1 [,列2...列N] FROM 表名;

c) 完全重复的记录只显示一次 : SELECT DISTINCT 列1 [,列2, ... 列N] FROM 表名;

- 当查询结果中的多行记录一模一样时,只显示一行. 一般查询所有列时很少会有这种情况,
但只查询一列(或几列)时,这种可能就大了!!(不会对源表产生影响, 只是在显示的时候, 不显示)
列运算(源表没有变化, 查询不会修改数据库表记录)

a) 数量型的列可以做加, 减, 乘, 除运算: SELECT sal*1.5 FROM emp;

b) 字符串类型的列可以做连接运算: SELECT CONCAT(ename, job) FROM emp;

c) 转换 NULL 值: SELECT IFNULL(comm,0)+100 FROM emp;

表示如果 comm 中存在 NULL 值, 那么当成 0 来运算.
d) 给列起别名: SELECT IFNULL(comm,0)+1000 AS 奖金 FROM emp; 其中 AS 可以省略.
条件控制

a) 条件查询
- SELECT empno,ename,sal,comm FROM emp WHERE sal>10000 AND comm IS NOT NULL;
- SELECT empno,ename,sal FROM emp WHRER sal BETWEEN 20000 AND 30000;
- SELECT empno,ename,job FROM emp WHERE job IN ('经理','董事长');
b) 模糊查询
- 查询姓张, 并且姓名一共两个字的员工时, 这时就可以使用模糊查询
SELECT * FROM emp WHERE ename LIKE '张_';
模糊查询需要使用运算符: LIKE, 其中 "_" 匹配一个任意字符, 注意, 只匹配一个字符而不是多个.
- 查询姓张, 名字几个字都可以的员工
SELECT * FROM emp WHERE ename LIKE '张%';
其中, "%" 匹配 0~N 个任意字符.
- 查询姓名中带有"小"字的员工
SELECT * FROM emp WHERE enmae LIKE '%小%';
因为 % 匹配 0~N 个字符,所以姓名以"小"开头和结尾的员工都会被查询到.
- SELECT * FROM emp WHERE ename LIKE '%'; 这个条件等同于不存在, 但姓名为 NULL 的查询不出来!!

3.4.2 排序

a) 升序 : SELECT * FROM WHERE emp ORDER BY sal ASC;
- 按 sal 升序排列, 其中 ASC 可以省略;
b) 降序 : SELECT * FROM WHERE emp ORDER BY comm DESC;
- 按 comm 降序排列;
c) 使用多列作为排序条件 : SELECT * FROM WHERE emp ORDER BY sal ASC, comm DESC;
- 使用 sal 做升序排列, 如果 sal 相同, 则使用 comm 做降序排列;

3.4.3 聚合函数

聚合函数用来做某列的纵向查询.
COUNT
- 计算 emp 表中所有列都不为 NULL 的记录的行数: SELECT COUNT(*) FROM emp;
- 计算 emp 表中 comm 列不为 NULL 的记录行数: SELECT COUNG(comm) FROM emp;
MAX
- 查询最高工资: SELECT MAX(sal) FROM emp;
MIN
- 查询最低工资: SELECT MIN(sal) FROM emp;
SUM
- 查询工资总和: SELECT SUM(sal) FROM emp;
AVG
- 查询平均工资: SELECT AVG(sal) FROM emp;

3.4.4 分组查询

分组查询是把记录使用某一列进行分组, 然后查询组信息;
查询所有部门的人数: SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;
使用 deptno 分组, 查询部门编号和每个部门的人数
查询每种工作的最高工资: SELECT job, MAX(sal) FROM emp GROUP BY job;
分组前,条件查询
- 查询每个部门工资大于 15000 的员工个数
  SELECT deptno,COUNT(*) FROM emp WHERE sal > 15000 GROUP BY deptno;
  先查询工资大于 15000 的员工, 然后使用部门进行分组.
分组后,条件查询
- 查询人数多于 3 人的部门
  SELECT deptno, COUNT(*) FROM emp GROUP BY depton HAVING COUNG(*) >3;
规律:
- SELECT 使用哪一组进行分组, 聚合函数
- FROM 表名
- WHERE 分组前条件
- GROUP BY 使用哪一组进行分组
- HAVING 分组后条件(以聚合函数做条件)
- ORDER BY 对查询结果进行排序

3.5 limit 方言

LIMIT 用来限定查询结果的起始行, 以及总行数.
- SELECT * FROM emp LIMIT 4,3;
  其中 4 表示从第 5 行开始查询, 其中 3 表示一共查询 3 行. 即第 5,6,7 行记录.

参考资料:

JavaWeb 视频教程