SQL语法基础(一)——基本知识

---

SQL语言在功能上主要分为如下4大类：

一、DDL：数据定义语言

定义和管理数据库、表、视图、索引等数据库对象
主要的语句关键字包括 CREATE 、 DROP 、 ALTER 等。

1.数据库操作

(1).创建数据库

create database 数据库名称;

(2).删除数据库

drop database 数据库名称;

(3).查看所有数据库

show database;
mysql中允许同时打开多个数据库，不同的数据库管理系统不一样。

(4).使用或者切换数据库

use 数据库名称;

(5).查看当前数据库

select database();

(6).查看当前数据库的创建语句

show create database 数据库名称;

2.数据表操作

(1).创建表

create table 数据库名称(数据列名称 数据类型 约束规则 ......);

CREATE TABLE mytable (
  id INT NOT NULL AUTO_INCREMENT,
  col1 INT NOT NULL DEFAULT 1,
  col2 VARCHAR(45) NULL,
  col3 DATE NULL,
  PRIMARY KEY (`id`));

克隆表结构
快速创建一个表和另外一个表的结构相同，只是克隆表结构，不会克隆数据。
create table 新表名称 like 已经存在的旧表名称;

(2).修改表

alter table 表名称 add/drop/modify 列名称 [类型 约束];
修改表名
rename table 原始表名称 to 新的表名称;
添加列

alter table mytable
add col char(20);

修改列和属性
alter table 表名称 change 旧有列名称 新列名称 类型 约束;

---ALTER TABLE 表名 CHANGE 原字段名 新字段名 字段类型 约束条件
ALTER TABLE mytable 
CHANGE col col1 CHAR(32) NOT NULL DEFAULT '123';

删除列

ALTER TABLE mytable
DROP COLUMN col;

(3).删除表

drop table 表名称;

DROP TABLE mytable;

二、DML：数据操作语言

用于操作数据库对象中所包含的数据，进行增删改查
主要的语句关键字包括 INSERT 、 DELETE 、 UPDATE 、 SELECT 等。

1.插入

插入所有的数据
insert into 表名称 values(值1,值2,...)

insert into mytable values(val1, val2);

插入部分数据

insert into mytable(col1, col2)
values(val1, val2);

插入检索出来的数据
insert into mytable(列名1,列名2,...) select 列1,列2,...from mytable2;

insert into mytable1(col1, col2)
select col1, col2
from mytable2;

将一个表的内容插入到一个新表

CREATE TABLE newtable AS
SELECT * FROM mytable;

2.更新

update 表名称 set 列名1=值1,列名2=值2,...

update mytable
set col = val,col2 = val2
where id = 1;

3.删除

删除表中所有数据
delete from 表名;
选择性删除
delete from 表名 where 条件;

DELETE FROM mytable
WHERE id = 1;

清空表(删除所有行)

truncate table mytable;

使用更新和删除操作时一定要用 WHERE 子句，不然会把整张表的数据都破坏。可以先用 SELECT 语句进行测试，防止错误删除。

三、DQL：数据查询语言

用于查询数据库数据
基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块：
SELECT <字段名表>
FROM <表或视图名>
WHERE <查询条件>

1.单表查询

(1).普通查询

查询表中所有数据

SELECT *
FROM mytable
LIMIT 5;

LIMIT
限制返回的行数。可以有两个参数
第一个参数为起始行，从 0 开始；第二个参数为返回的总行数。
返回前 5 行:

SELECT *
FROM mytable
LIMIT 5;

SELECT *
FROM mytable
LIMIT 0, 5;

返回第 3 ~ 5 行:

SELECT *
FROM mytable
LIMIT 2, 3;

DISTINCT/ALL
消除取值相同的行,如果没有指定DISTINCT关键词，则默认为ALL

SELECT DISTINCT col1, col2
FROM mytable;

(2).排序

• ASC : 升序(默认)
• DESC : 降序
  可以按多个列进行排序，并且为每个列指定不同的排序方式:

SELECT *
FROM mytable
ORDER BY col1 DESC, col2 ASC;

(3).where子句

通过where子句可以对数据进行过滤和筛选
where子句常用的查询条件

SELECT *
FROM mytable
WHERE col IS NULL;

AND 和 OR 用于连接多个过滤条件。优先处理 AND，当一个过滤表达式涉及到多个 AND 和 OR 时，可以使用 () 来决定优先级，使得优先级关系更清晰。
IN 操作符用于匹配一组值，其后也可以接一个 SELECT 子句，从而匹配子查询得到的一组值。
NOT 操作符用于否定一个条件

(4).字符匹配

通配符也是用在过滤语句中，但它只能用于文本字段。

• % 匹配 >=0 个任意字符；
• _ 匹配 ==1 个任意字符；
• 可以匹配集合内的字符，例如 [ab] 将匹配字符 a 或者 b。用脱字符 ^ 可以对其进行否定，也就是不匹配集合内的字符。 使用 Like 来进行通配符匹配。

SELECT *
FROM mytable
WHERE col LIKE '[^AB]%'; -- 不以 A 和 B 开头的任意文本

不要滥用通配符，通配符位于开头处匹配会非常慢。

(5).AS关键字

在数据库服务器上完成数据的转换和格式化的工作往往比客户端上快得多，并且转换和格式化后的数据量更少的话可以减少网络通信量。
计算字段通常需要使用 AS 来取别名，否则输出的时候字段名为计算表达式

SELECT col1 * col2 AS alias
FROM mytable;

CONCAT() 用于连接两个字段。许多数据库会使用空格把一个值填充为列宽，因此连接的结果会出现一些不必要的空格，使用 TRIM() 可以去除首尾空格

SELECT CONCAT(TRIM(col1), '(', TRIM(col2), ')') AS concat_col
FROM mytable;

(6).函数

<1>.聚集函数

AVG() 会忽略 NULL 行。
使用 DISTINCT 可以让汇总函数值汇总不同的值。

SELECT AVG(DISTINCT col1) AS avg_col
FROM mytable;

查询一个表有多少行

SELECT count(*)
FROM mytable;

还可以指定distinct取消重复值

SELECT count(distinct)
FROM mytable;

<2>.文本处理

其中， SOUNDEX() 可以将一个字符串转换为描述其语音表示的字母数字模式,直白一点讲就是可以根据发音的相似程度进行匹配和查找

SELECT *
FROM mytable
WHERE SOUNDEX(col1) = SOUNDEX('apple')

<3>.日期和时间处理

• 日期格式: YYYY-MM-DD
• 时间格式: HH:MM:SS
  
  返回当前时间

mysql> SELECT NOW();

<4>.数值处理

(7).分组

分组就是把具有相同的数据值的行放在同一组中。
可以对同一分组数据使用汇总函数进行处理，例如求分组数据的平均值等。
指定的分组字段除了能按该字段进行分组，也会自动按该字段进行排序。
对查询结果按col的值进行分组

SELECT col, COUNT(*) AS num
FROM mytable
GROUP BY col;

GROUP BY 自动按分组字段进行排序，ORDER BY 也可以按汇总字段来进行排序。

SELECT col, COUNT(*) AS num
FROM mytable
GROUP BY col
ORDER BY num;

WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤。

SELECT col, COUNT(*) AS num
FROM mytable
WHERE col > 2
GROUP BY col
HAVING num >= 2;

分组规定:

• GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前；
• 除了汇总字段外，SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出；
• NULL 的行会单独分为一组；
• 大多数 SQL 实现不支持 GROUP BY 列具有可变长度的数据类型。

2.子查询（嵌套）

子查询中只能返回一个字段的数据。
可以将子查询的结果作为 WHRER 语句的过滤条件:

SELECT *
FROM mytable1
WHERE col1 IN (SELECT col2
               FROM mytable2);

下面的语句可以检索出客户的订单数量，子查询语句会对第一个查询检索出的每个客户执行一次:

SELECT cust_name, (SELECT COUNT(*)
                   FROM Orders
                   WHERE Orders.cust_id = Customers.cust_id)
                   AS orders_num
FROM Customers
ORDER BY cust_name;

3.连接查询

连接用于连接多个表，使用 JOIN 关键字，并且条件语句使用ON而不是WHERE
连接可以替换子查询，并且比子查询的效率一般会更快。
可以用 AS 给列名、计算字段和表名取别名，给表名取别名是为了简化 SQL 语句以及连接相同表

(1).内连接

内连接又称等值连接，使用 INNER JOIN 关键字

SELECT A.value, B.value
FROM tablea AS A INNER JOIN tableb AS B
ON A.key = B.key;

可以不使用 INNER JOIN，而使用普通查询,在 WHERE 中将两个表中要连接的列用等值方法连接起来。

SELECT A.value, B.value
FROM tablea AS A, tableb AS B
WHERE A.key = B.key;

(2).自连接

自连接可以看成内连接的一种，只是连接的表是自身而已
假设一张员工表，包含员工姓名和员工所属部门，要找出与 Jim 处在同一部门的所有员工姓名。
子查询版本

SELECT name
FROM employee
WHERE department = (
      SELECT department
      FROM employee
      WHERE name = "Jim");

自连接版本

SELECT e1.name
FROM employee AS e1 INNER JOIN employee AS e2
ON e1.department = e2.department
      AND e2.name = "Jim";

(3).自然连接

内连接和自然连接的区别: 内连接会保留重复的列，而自然连接会去掉重复列

SELECT A.value, B.value
FROM tablea AS A NATURAL JOIN tableb AS B;

(4).外连接

外连接保留了没有关联的那些行。分为左外连接，右外连接以及全外连接，左外连接就是保留左表没有关联的行。
举个例子：
customers 表:

orders 表:

检索所有顾客的订单信息，包括还没有订单信息的顾客。

SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

结果:

4.组合查询

使用 UNION 来组合两个查询，如果第一个查询返回 M 行，第二个查询返回 N 行，那么组合查询的结果一般为 M+N 行。 每个查询必须包含相同的列、表达式和聚集函数。 默认会去除相同行，如果需要保留相同行，使用 UNION ALL。 只能包含一个 ORDER BY 子句，并且必须位于语句的最后。

SELECT col
FROM mytable
WHERE col = 1
UNION
SELECT col
FROM mytable
WHERE col =2;

四、DCL：数据库控制语言

用于管理数据库的语言，包括管理权限和数据更改
用于定义数据库、表、字段、用户的访问权限和安全级别。
主要的语句关键字包括 GRANT 、 REVOKE 、 COMMIT 、 ROLLBACK 、 SAVEPOINT 等。

1.用户管理

(1).创建账户

（新创建的账户没有任何权限）
CREATE USER ‘用户名’@‘主机名’ identified by ‘密码’;

CREATE USER 'xt'@'localhost' IDENTIFIED BY '密码';

(2).修改账户名

RENAME myuser TO newuser;

(3).删除账户

DROP USER ‘用户名’@‘主机名’

DROP USER 'xt'@'localhost' 

(4).更改密码

必须使用 Password() 函数
SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码')

SET PASSWROD FOR 'xt'@'localhost' = Password('new_password');

(5).查询用户

用户信息储存在mysql数据库USER表中
因此需要先切换到mysql数据库,再查询

USE mysql;
SELECT * FROM USER;

2.权限管理

(1).查看权限

SHOW GRANTS FOR '用户名'@'主机名';

SHOW GRANTS FOR 'xt'@'localhost';

(2).授予权限

GRANT 权限列表 on 数据库名.表名 to '用户名'@'主机名';

GRANT SELECT, INSERT ON mydatabase.* TO 'xt'@'localhost';

all 表示所有权限
权限有 select，insert，drop等等
*表示所有， mydatabase.*表示数据库中的所有表
*.*表示所有库的所有表

(3).删除权限

revoke 权限列表on 数据库名.表名 FROM '用户名'@'主机名';
GRANT 和 REVOKE 可在几个层次上控制访问权限:

• 整个服务器，使用 GRANT ALL 和 REVOKE ALL；
• 整个数据库，使用 ON database.*；
• 特定的表，使用 ON database.table；
• 特定的列；
• 特定的存储过程。

REVOKE SELECT, INSERT ON mydatabase.* FROM 'xt'@'localhost';

---

总结

最近在复习sql，之前没好好学，网上有很多博主写的都很好，但我不知道为啥不怎么看得进去，而且我觉得适合自己的才是最好的，所以我就按照自己的逻辑整理一遍，顺便加深一下印象。问题可能会有很多，如有错误，敬请斧正。