MySQL基础SQL编程学习1

全栈工程师修炼指南

发布于 2022-09-29 15:32:49

4.6K0

发布于 2022-09-29 15:32:49

[TOC]

0x00 前言

什么是 SQL语言？答：SQL指结构化查询语言，全称是 Structured Query Language，是一种 ANSI（American National Standards Institute 美国国家标准化组织）标准的计算机语言。它是用于访问和处理数据库的标准的计算机语言。

SQL 能做什么？

SQL 面向数据库执行查询
SQL 可从数据库取回数据
SQL 可在数据库中插入新的记录
SQL 可更新数据库中的数据
SQL 可从数据库删除记录
SQL 可创建新数据库
SQL 可在数据库中创建新表
SQL 可在数据库中创建存储过程
SQL 可在数据库中创建视图
SQL 可以设置表、存储过程和视图的权限

MySQL是属于关系型数据库 RDBMS中的数据存储在被称为表的数据库对象中，表是相关的数据项的集合，它由列cols和行rows组成。

什么是 RDBMS ? 答：指关系型数据库管理系统，全称 Relational Database Management System;RDBMS 是 SQL 的基础同样也是所有现代数据库系统的基础，比如 MSSQL Server、IBM DB2、Oracle、MySQL 以及 Microsoft Access。

如何学习SQL和查看命令帮助? 答:linux中采用man,help与info命令，在mysql中是help建议在学习的时候多采用帮助文档；

mysql> help contents
   Account Management
   Administration
   ...

mysql> help Account Management
   CREATE USER
   DROP USER
   GRANT
   RENAME USER
   REVOKE
   SET PASSWORD

注释：除了 SQL 标准之外，大部分 SQL 数据库程序都拥有它们自己的专有扩展，比如MySQL 与 Oracle 之间扩展模块是有所不同的！

0x01 学习环境

学习SQL环境快速安装：

系统:CentOS7
容器:Docker , Docker-Compose
数据库版本: MySQL 8.0
数据库管理: Adminer 简单数据库管理
镜像来源:https://hub.docker.com/_/mysql

#安装Docker-ce (注意安装前删除老版本)
yum install -y docker-ce python3
pip3 install pip --upgrade

#安装Docker-compose
pip install docker-compose

#验证运行docker-compose.yml配置文件
docker-compose config
docker-compose up -d  #后台运行

docker-compose.yml

# Use root/example as user/password credentials 默认MySQL版本是8.x
version: '3.1'
services:
  db:
    image: mysql
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: example
    ports:
      - 3308:3306
  db5:
    image: mysql:5.7.28
    container_name: mysql5
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: example
    ports:
      - 3305:3306
  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080

WeiyiGeek.

运行和查询

#手动运行容器
$ docker run --name mysql-test -e MYSQL_ROOT_PASSWORD=my-secret-pw --default-authentication-plugin=mysql_native_password -d mysql:tag
$ docker run -it --network some-network --rm mysql mysql -hsome-mysql -uexample-user -p #对Docker网络中的MySQL进行连接
$ docker run -it --rm mysql mysql -hsome.mysql.host -usome-mysql-user -p  #连接其他MySQL

#使用自定义MySQL配置文件
$ docker run --name some-mysql -v /my/custom:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag
$ docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

#在主机系统的适当卷上创建一个数据目录，例如/my/own/datadir。
$ docker run --name some-mysql -v /my/own/datadir:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag

#测试查询
docker ps
# CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
# 4ba28666e63f        adminer             "entrypoint.sh doc..."   45 hours ago        Up 44 hours         0.0.0.0:9080->8080/tcp              data_adminer_1
# 2172955ddedc        mysql               "docker-entrypoint..."   45 hours ago        Up 44 hours         33060/tcp, 0.0.0.0:9001->3306/tcp   data_db_1
...

0x02 SQL基础

SQL标准语句

1.建议在每一句SQL后加上一个分号(在数据库系统中分隔每条 SQL 语句的标准方法,这样在对服务器的相同请求中执行一条以上的 SQL 语句),因为某些数据库系统要求在每条 SQL 语句的末端使用分号。
2.SQL 对大小写不敏感SELECT 与 select 是相同的；
3.SQL 数据库备份文件中及其交互命令行中的注释是/* 注释案例 */ 和 --注释案例；

常见的SQL语句:

//0.CRUD (创建/查询/更新/删除)
//1.数据定义语言 - DDL 
CREATE DATABASE|TABLE|INDEX|USER|VIEW  - 创建新数据库 / 创建新表 /  创建索引（搜索键）/ 创建用户 / 创建视图
ALTER DATABASE|TABLE|INDEX|USER - 修改数据库 / 变更（改变）数据库表 / 修改索引 / 更改用户
DROP DATABASE|TABLE|INDEX|USER - 删除数据库 / 删除表 / 删除索引 / 删除用户
RENAME DATABASE|TABLE|USER  - 重命名数据库和数据表 / 重命名用户
TRUNCATE TABLE - 清空表数据

//2. 数据查询语言- DQL(查询)
SELECT - 从数据库中提取数据 
FROM 字句
WHERE 字句


//3.数据操作语言 - DML(增删改)
UPDATE - 更新数据库中的数据
DELETE - 从数据库中删除数据
INSERT INTO - 向数据库中插入新数据


//4.数据控制语言 - DCL(权限)
GRANT - 设置数据库表rw权限设置（访问、安全权限）
REVOKE - 取消数据库表rw权限设置
IF...ELSE
WHILE
BEGIN
TRANSACTION


//5.事务处理语言 - TPL
COMMIT - 事务提交
ROLLBACK - 事务回滚

SQL分类 SQL语言大致分为以下几类:

数据定义语言（DDL）:用于创建、修改、和删除数据库内的数据结构(create drop alter(修改) rename);
数据查询语言（DQL）: 从数据库中的一个或多个表中查询数据(SELECT)条件包 WHERE (条件) ，ORDER BY (排序) ，having , Group By (分组), limit(限制显示)
数据操作语言（DML）:增删修改数据库中的数据，包括插入(INSERT)、更新(UPDATE)和删除(DELETE);
数据控制语言（DCL）:用于对数据库的访问权限进行控制，如1.给用户授予访问权限（GRANT）;2.取消用户访问权限（REVOKE）
指针控制语言（CCL）: CURSOR Control Language 语句DECLARE CURSOR ， FETCH INITO 和 update where current 用于对一个或多个表单独行操作；
事务处理语言（TPL）: 能够确保被DML语句影响的表的所有行得到及时更新，TPL语句包括BEGIN ，TRANSACTION ，COMMIT(提交) ，ROLLBACK(回滚)等等;

补充学习:

1.大多数数据库软件系统都允许使用编程函数在结果集中进行导航，比如：Move-To-First-Record、Get-Record-Content、Move-To-Next-Record 等等。

0x03 SQL语句

SELECT 语句

描述:SELECT 语句用于从数据库中选取数据,可以加上条件或者语句参数进行过滤显示数据; SQL 查询的五种子句where(条件查询)、having（筛选）、group by（分组）、order by（排序 ASC | DESC）、limit（限制结果数）;

/* SQL SELECT 语法 */
SELECT [DISTINCT] column_name,column_name
FROM table_name
WHERE column_name operator value;

/* DISTINCT 语句用于返回唯一不同的值，在表中一个列可能会包含多个重复值仅仅列出不同的值 */
/* WHERE 子句用于提取那些满足指定标准的记录。*/
SELECT DISTINCT 字段名 FROM 表名 WHERE 查询条件;

/* LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式 */
SELECT column_name(s)
FROM table_name
WHERE column_name [LIKE|REGEXP] pattern;

/* BETWEEN AND (在之间的值) 字句在此之间的值*/
SELECT 字段名 FROM 表名 WHERE 字段名 BETWEEN 初始值 AND 终止值;

/* ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序。（注意这里无WHERE）*/
/* DESC 降序 , ASC 升序（默认），即 desc 或者 asc 只对它紧跟着的第一个列名有效其他不受影响，仍然是默认的升序*/
SELECT column_name,column_name
FROM table_name
ORDER BY column_name1 ASC|DESC,column_name2 ASC|DESC;  

/* SQL limit 子句，查询前n条/后n条记录（分页常用） */
SELECT 字段 FROM 表名 WHERE 查询条件 LIMIT 起始,结束

/* SQL 多表查询 */
SELECT 数据库.表,mysql.user FROM 数据库,mysql WHERE 1

/* GROUP BY 语法 */
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name; 

/* HAVING 语法筛选分组后的各组数据*/
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

WHERE 子句中的运算符:

(1) 比较运算符:= > < >= ,<=, !=,<> 表示（不等于）, BETWEEN(在某个范围内), LIKE(搜索某种模式), IN(指定针对某个列的多个可能值)，IS(是否赛某列的值)
(2) 逻辑运算: (优先级排列), not (满足不包含该条件的值) , and, or

NOT与谓词进行组合条件的查询：

(1) NOT BERWEEN…AND…对介于起始值和终止值间的数据时行查询可改成<起始值AND>终止值
(2) IS NOT NULL 对非空值进行查询
(3) NOT IN 该式根据使用的关键字是包含在列表内还是排除在列表外，指定表达式的搜索，搜索表达式可以是常量或列名，而列名可以是一组常量，但更多情况下是子查询
(4) NOT LIKE 匹配显示不满足条件的行
(5) NOT REGEXP 不显示正则表达式匹配的行

NULL 值判断

(1) IS NULL 对空值进行查询
(2) IS NOT NULL 对非空值进行查询

基础示例:

set names utf8;  /*命令用于设置使用的字符集（如果在配置文件中设置了Server或者Client端的字符集则不需要指定）*/   
-- 查询用户数据库.表
mysql> select user,host from mysql.user;
mysql> select id,name,country from websites;

-- 重复去掉 distinct： 从 "Websites" 表的 "country" 列中选取唯一不同的值，也就是去掉 "country" 列重复值
mysql> select distinct country from websites;

-- 比较运算符
mysql> SELECT * FROM websites WHERE country="CN";  
mysql> SELECT * FROM websites WHERE id=1; /* SQL 可以直接输入 数字 表示数值**/
mysql> SELECT * FROM websites WHERE id <> 1; /*显示不包括id=1的数据;*/
mysql> SELECT * FROM websites WHERE id>=2 and country != "CN"; /**采用逻辑运算符和比较运算符连用*/   

-- 不带比较运算符的 WHERE 子句
mysql> SELECT host FROM mysql.user WHERE 0; /*则会返回一个空集，因为每一行记录 WHERE 都返回 false。*/
mysql> SELECT host FROM mysql.user WHERE 1; /*返回MySQL表中host字段,因为每一行记录 WHERE 都返回 true。*/

-- 空值判断is null：打印编写满足列某值的某行是,如果不满足则返回该标字段及其字段的值为NULL;)
mysql> SELECT * FROM information_schema.FILES WHERE CHECKSUM IS NULL 
mysql> SELECT * FROM information_schema.FILES WHERE CHECKSUM IS NOT NULL  

-- 条件语句between.and. ：查询 emp 表中 SAL 列中大于等于 1500 的小于 3000 的值
mysql> SELECT * FROM  emp WHERE sal between 1500 and 3000;  

-- 运算符 In (包含运算). 查询 EMP 表 SAL 列中等于 5000，3000，1500 的值。
mysql>  SELECT * FROM  emp WHERE sal in (5000,3000,1500); 

-- 模糊查询Like：查询 EMP 表中 Ename 列中有 M 的值，M 为要查询内容中的模糊信息。
mysql> SELECT * FROM emp WHERE ename like 'Java%';
mysql> SELECT * FROM tb_stu WHERE name like'%程序_';
mysql> SELECT * FROM tb_stu WHERE name like'%PHP%';

-- 模糊查询REGEXP：使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式查询匹配的模糊信息。
mysql> SELECT host,user FROM user WHERE user REGEXP  '[^root]'
mysql> SELECT host,user FROM user WHERE user REGEXP  '^root'

-- 联合使用() 比较 条件 逻辑 运算符
SELECT * FROM websites WHERE alexa > 15 AND (country='CN' OR country='USA');
SELECT * FROM websites WHERE (alexa > 15 and alexa % 2 != 0) AND (country='CN' OR country='USA');  

-- 排序语句 ORDER BY:多列排序的时候，先按照第一个column name排序，在按照第二个column name排序；
SELECT * FROM tb_name WHERE address <> '' order by addtime desc, id asc;  /**查询不为空的数据*/

-- 显示行数 limit语句：查询前n条/后n条记录
SELECT Host,User,password_last_changed FROM mysql.user WHERE 1 limit 0,3; --前三条
SELECT Host,User,password_last_changed FROM mysql.user WHERE 1 ORDER BY HOST DESC limit 0,3; --后三条

-- 子查询也叫内部查询如select avg(score) from studentscore就是子查询
--  查询学生成绩表里分数低于平均成绩的学生姓名和成绩，并根据分数降序排列
select name,score form studentscore where score < (select avg(score) from studentscore) order by score DESC

WeiyiGeek.

多表查询

CREATE DATABASE student;
USE student;

CREATE TABLE `user`(
     xh int(8) NOT NULL auto_increment  COMMENT '主键学号',
     xm  VARCHAR(62) NOT NULL COMMENT '姓名',
     nj  VARCHAR(10) NOT NULL COMMENT '班级',
     age  INT(3) NOT NULL DEFAULT 0 COMMENT '年龄',
     zy  VARCHAR(256) NOT NULL COMMENT '专业',
     PRIMARY KEY(xh)
)ENGINE = InnoDB DEFAULT CHARSET = utf8;

CREATE TABLE `user_kc`(
  `xh` int(8) NOT NULL DEFAULT 0 COMMENT '主键学号',
  `kcm` VARCHAR(256) NOT NULL COMMENT '课程名',
  `grade` float(8) NOT NULL COMMENT '成绩' -- 注意最后一个字段没有','
)ENGINE = InnoDB DEFAULT CHARSET = utf8;


INSERT INTO user VALUES (1001,'张晓华','2014级',19,'计算机技术'), (1002,'则新华','2015级',39,'计算机技术'), (1003,'线程华','2015级',19,'网络工程技术'),(1004,'台时华','2016级',19,'网络工程技术'),(1005,'爱是','2013级',19,'物联网工程');

INSERT INTO user_kc VALUES (1002,"Python编程",79.1),(1001,"JAVA编程",89.5),(1003,"LINUX编程",69.5),(1002,"Mysql数据库编程",69.9),(1005,"网络编程",79.5),(1004,"前端HTML5",79.5),(1002,"Python编程",79.5),(1004,"C语言之精华",59.5);

基础示例:

-- 联表查询
SELECT user.xh,user.xm,user.nj,user_kc.kcm,user_kc.grade 
FROM  user,user_kc 
WHERE user.xh=user_kc.xh;

WeiyiGeek.联表查询

函数使用

user() ：当前用户
now() ：当前时间

基础示例:

select user();
select now();

root@212.17.8.29	2020-01-06 03:28:45

注意事项:

注:SQL语句中的DISTINCT必须与WHERE子句联合使用，否则输出的信息不会有变化且字段不能用*代替;
注:SQL语句中的BETWEEN .. AND …条件语句，下限在前，上限在后，查询的范围包涵有上下限的值。
注:SQL语句中文本与数值字段，使用单引号来环绕文本值（大部分数据库系统也接受双引号），数值是不需要加上单双引号，但是注意字段类型的影响;
注:WHERE 子句并不一定带比较运算符，当不带运算符时会执行一个隐式转换 WHERE 0|1。当 0 时转化为 false，1 转化为 true。
注:我们可以把比较运算符，逻辑运算符，以及条件运算符，加上括号()来组成复杂的表达式。
注:对字段进行排序时若不指定排序方式，则默认为ASC升序；对查询信息进行多条件排序是为了共同限制记录的输出，一般情况下，由于不是单一条件限制，所以在输出效果上有一些差别。
注:在对数据库中的日期字段进行查询筛选的时候，注意不同数据库对日期型数据存在差异；
注:在使用REGEXP子句后后面的正则匹配需要采用’ ‘进行包含;

INSERT 语句

描述:INSERT INTO 语句用于向表中插入新记录。

-- INSERT INTO 语句可以有两种编写形式。
-- 1.无需指定要插入数据的列名，只需提供被插入的值即可(需要注意他是按照字段顺序进行插入的)：
INSERT INTO table_name
VALUES (value1,value2,value3,...);

-- 2.需要指定列名及被插入的值：
INSERT INTO table_name
VALUES (value1,value2,value3,...);

-- 3. 将旧表中的数据灌入新表
CREATE table 新表名 LIKE book; -- 复制表结构，含有主键等信息的完整表结构;
INSERT INTO 新表 SELECT * FROM 旧表；

基础示例:

-- 向 "Websites" 表中插入一个新行。
INSERT INTO Websites (name, url, alexa, country)
VALUES ('百度','https://www.baidu.com/','4','CN');

-- 为了数据库优化更好我们建议将添加的字符进行拼接然后再一起写入数据库中：
mysql> INSERT INTO sb_user(name,pass) VALUES ('test','test'),('admin','admin');

-- 旧表数据灌入新表之中（但是此种方法新表必须存在）
mysql> create table newUser like user;
mysql> desc newUser;
+-------------+--------------+------+-----+----------+-------+
| Field       | Type         | Null | Key | Default  | Extra |
+-------------+--------------+------+-----+----------+-------+
| id          | int(5)       | NO   |     | 10086    |       |
| name        | char(128)    | NO   |     | undefine |       |
| sex         | int(1)       | YES  |     | 0        |       |
| qq_id       | int(10)      | YES  |     | NULL     |       |
| addr        | varchar(256) | YES  |     | NULL     |       |
| description | varchar(512) | YES  |     | NULL     |       |
+-------------+--------------+------+-----+----------+-------+
mysql> INSERT INTO newUser SELECT * FROM user;
mysql> SELECT * FROM newUser;

WeiyiGeek.

注意事项:

注:在ID或者其他字段设置自增长的时候可以不用向id插入数值，因为id 列是自动更新的，表中的每条记录都有一个唯一的数字。
注:insert into select 和select into from 的区别（MySQL 数据库不支持，Mssql支持），前则要求行表scorebak 必须存在，后者要求表scorebak 不存在；

UPDATE 语句

描述:UPDATE 语句用于更新表中已存在的记录。

语法:

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

基础示例:

-- 假设我们要把 "菜鸟教程" 的 alexa 排名更新为 5000，country 改为 USA。
UPDATE websites SET alexa = '5000', country = 'CN' WHERE name = "菜鸟教程";  /* WHERE 语句很重要 */

补充说明:

1.在 MySQL 中可以通过设置 sql_safe_updates 这个自带的参数来解决，当该参数开启的情况下，你必须在update 语句后携带 where 条件，否则就会报错。

mysql> SHOW VARIABLES LIKE 'sql_safe_updates';  -- 值得(注意)
Variable_name	Value
sql_safe_updates OFF

--  表示开启该参数
set sql_safe_updates=ON;
set sql_safe_updates=1;  
-- 开启后必须在更新语句后加入Where条件,否则在进行删除时候会产生错误;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

注意事项:

注:WHERE 子句规定哪条记录或者哪些记录需要更新,WHERE 子句规定哪条记录或者哪些记录需要更新;

DELETE 语句

描述:DELETE 语句用于删除表中的行。

DELETE FROM table_name
WHERE some_column=some_value;

基础数据:

-- 假设我们要从 "Websites" 表中删除网站名为 "ORACLE" 且国家为 USA 的网站 。
DELETE FROM websites WHERE name='oracle' and country='USA';

-- 删除所有数据但是表结构、属性、索引将保持不变：
DELETE FROM table_name;
DELETE * FROM table_name;

WeiyiGeek.

补充说明: 1.SQL关于删除的三个语句：DROP、TRUNCATE、 DELETE的区别:

删除表: drop table 表名;
清空表数据: delete from 表名;
清空表数据: truncate table 表名;  

#1.删除表test并释放空间，将test删除的一干二净。
DROP table test;  

#2.删除表test里的内容并释放空间，但不删除表的定义，表的结构还在。
TRUNCATE test;  

#3.删除整个表:仅删除表test内的所有内容，保留表的定义，不释放空间。
DELETE FROM test 或者 DELETE * FROM test ; @
#删除指定数据:删除表test中年龄等于30的且国家为US的数据
DELETE FROM test WHERE age=30 AND country='US';

WeiyiGeek.

2.什么时候使用DROP?什么时候使用DELETE? 答：对于结构删除，如数据库删除、表删除、索引删除等当使用DROP，而对于数据的删除，和事务有关, 或者想触发 trigger则用DELETE，想保留表而将所有数据删除和事务无关用 truncate 即可，如果是整理表内部的碎片,可以用 truncate 跟上 reuse stroage再重新导入/插入数据；;

3.总结

1.truncate和delete 只删除数据不会影响表结构，drop语句删除表的结构被依赖的约束(constrain), 触发器(trigger), 索引(index)，依赖于该表的存储过程/函数将保留, 但是变为 invalid 状态。
2.delete语句是DML改操作会放到rollback segment中事务提交后才生效（如果有与之对应的触发器执行时候将被触发），而truncate和drop是DDL操作会立即生效，源数据不妨到rollback segment中既不能回滚也不能触发trigger；
3.delete 语句不影响表所占用的 extent, 高水线(high watermark)保持原位置不动，drop 语句将表所占用的空间全部释放，truncate 语句缺省情况下见空间释放到 minextents 个 extent, 除非使用 reuse storage， truncate会将高水线复位(回到最开始)。
4.速度：drop > truncate > delete ，安全性: 小心使用 drop 和 truncate, 尤其没有备份的时候否则哭都来不及。
5.不带where参数的delete语句是删除mysql表中所有内容，delete的效果有点像将mysql表中所有记录一条一条删除到删完，而使用truncate table也可以清空mysql表中所有内容，相当于保留mysql表的结构，重新创建了这个表，所有的状态都相当于新表。

注意事项:

注:与update语句一样，WHERE 子句规定哪条记录或者哪些记录需要删除;如果您省略了WHERE子句，所有的记录都将被删除！
注:在效率上truncate比delete快，但truncate删除后不记录mysql日志不可以恢复数据;

0x04 SQL进阶

描述:SQL语句联合使用的其他子语句，是相当的重要的；

TOP 子句

描述:SELECT TOP 子句用于规定要返回的记录的数目，它对于拥有数千条记录的大型表来说，是非常有用的。

-- #SQL Server / MS Access 语法
SELECT TOP number|percent column_name(s)
FROM table_name;

-- #MySQL 语法
SELECT column_name(s)
FROM table_name
LIMIT number;

-- #Oracle 语法
SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

基础示例:

-- 从 websites 表中选取前面百分之 50 的记录(MSSQL)：
SELECT TOP 50 PERCENT * FROM Websites;
select top 5 * from table --前5行
select top 5 * from table order by id desc  --后5行 desc 表示降序排列 asc 表示升序

注意事项:

注:并非所有的数据库系统都支持 SELECT TOP 语句。 MySQL 支持 LIMIT 语句来选取指定的条数数据， Oracle 可以使用 ROWNUM 来选取。

LIKE 和 REGEXP 子句

基础语法:

/* LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式 */
SELECT column_name(s)
FROM table_name
WHERE column_name [LIKE|REGEXP] pattern;

LIKE 模糊查询通配符说明: % 表示多个字值，_ 下划线表示一个字符;

REGEXP 模糊查询通配符说明:'[abc]'字符列中的任何单一字符，[!abc]或者[^abc]不在字符列中的任何单一字符；

M% : 为能配符，正则表达式，表示的意思为模糊查询信息为 M 开头的。
%M% : 表示查询包含M的所有内容。
%M_ : 表示查询以M在倒数第二位的所有内容。
'^[a-zA-Z0-9]':表示查询以a-z或者A-Z以及0-9开头的内容；

基础示例:

-- LIKE子句的模糊查询
SELECT host,user FROM user WHERE user LIKE  '%ro%'

-- REGEXP子句显示user字段以root开头的行（注意引号包含表达式）
SELECT host,user FROM user WHERE user REGEXP  '^root'

WeiyiGeek.

IN 子句

描述:IN 操作符允许您在 WHERE 子句中规定多个值。

-- SQL IN 语法（以 , 号分割）
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

IN 与 = 的异同说明?

相同点：均在WHERE中使用作为筛选条件之一、均是等于的含义；
不同点：IN可以规定多个值，等于规定一个值；

基础示例:

-- in 与 = 的转换
select * from Websites where name in ('Google','Baidu');
select * from Websites where name='Google' or name='Baidu';

-- 注意数值和字符串对查询的结果没有影响
select * from user where xh in (1001,'1003');

WeiyiGeek.

BETWEEN 操作符

描述:BETWEEN 操作符用于选取介于两个值之间的数据范围内的值(值可以是数值，文本或者日期)。

-- SQL BETWEEN 语法
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

基础演示:

-- SQL 语句选取 alexa 介于 1 和 20 之间的所有网站：
SELECT * FROM Websites
WHERE alexa BETWEEN 1 AND 20; 

-- 如需显示不在上面实例范围内的网站，请使用 NOT BETWEEN：
SELECT * FROM Websites
WHERE alexa NOT BETWEEN 1 AND 20; 

-- 选取 alexa 介于 1 和 20 之间但 country 不为 USA 和 IND 的所有网站(注意ADD子句)
SELECT * FROM Websites
WHERE (alexa BETWEEN 1 AND 20)
AND country NOT IN ('USA', 'IND'); 

-- 选取 name 以介于 'A' 和 'H' 之间字母开始的所有网站(带有文本值的 BETWEEN 操作符)
SELECT * FROM Websites
WHERE name BETWEEN 'A' AND 'H'; 

-- SQL 语句选取 date 介于 '2016-05-10' 和 '2016-05-14' 之间的所有访问记录：
SELECT * FROM access_log
WHERE date BETWEEN '2016-05-10' AND '2016-05-14'; 

-- MySQL用户的修改时间范围
SELECT User,password_last_changed FROM user 
WHERE password_last_changed 
BETWEEN '	2020-01-03 09:22:32' AND '	2020-01-03 09:22:38';

WeiyiGeek.

注意事项:

注意在不同的数据库中，BETWEEN 操作符会产生不同的结果！
- 某些数据库中BETWEEN 选取介于两个值之间（但不包括两个测试值的字段，且包括两个测试值的字段，且包括第一个测试值但不包括最后一个测试值的字段）

AS 别名

描述:通过使用 SQL，可以为表名称或列名称指定别名(创建别名是为了让列名称的可读性更强)。在下面的情况下使用别名很有用：

在查询中涉及超过一个表
在查询中使用了函数
列名称很长或者可读性差
需要把两个列或者多个列结合在一起

基础语法:

-- 列的 SQL 别名语法
SELECT column_name AS alias_name
FROM table_name;

-- 表的 SQL 别名语法
SELECT column_name(s)
FROM table_name AS alias_name;

基础演示:

-- 字段别名的示例
SELECT xh AS '学号',kcm AS '课程', grade AS '成绩' 
FROM user_kc
WHERE grade > 70;

WeiyiGeek.AS别名字段

-- 表的别名实例
SELECT u.xh,u.xm,kc.kcm,kc.grade 
FROM user AS u,user_kc AS kc 
WHERE kc.xh = u.xh 
ORDER BY kc.xh;

WeiyiGeek.

-- 字段拼接（这里采用CONCAT函数后面会深入讲解）
SELECT CONCAT( '学号:', u.xh , '姓名:', u.xm) AS '基础信息',CONCAT( '课程:', kc.kcm , '成绩:', kc.grade) AS '成绩信息',
FROM user AS u,user_kc AS kc 
WHERE kc.xh = u.xh AND kc.kcm = 'Python编程'
ORDER BY xh

WeiyiGeek.

JOIN 连接

描述:SQL join 用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。。下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。

1.A : 属于集合A的所有的元素的集合
2.CuB U A :属于集合A的元素同时满足不属于集合B的元素
3.交集：以属于A且属于B的元素为元素的集合称为A与B的交（集），记作A∩B（或B∩A），读作“A交B”（或“B交A”），即 A∩B={x|x∈A,且x∈B}
4.并集：以属于A或属于B的元素为元素的集合称为A与B的并（集），记作AUB（或BUA）读作“A并B”（或“B并A”），即A∪B={x|x∈A,或x∈B}。
5.补集：属于全集U不属于集合A的元素组成的集合称为集合A的补集，记作CuA，即CuA={x|x∈U,且x不属于A},Cu(A∩B)
6.B :属于集合B的所有的元素的集合
7.CuA U B : 属于集合B的元素同时满足不属于集合A的元素

WeiyiGeek.

不同的 SQL JOIN 分类:

INNER JOIN (内连接)：如果表中有至少一个匹配，则返回行
OUTER JOIN (外连接):
LEFT JOIN(左连接)：即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN(右连接)：即使左表中没有匹配，也从右表返回所有的行
FULL JOIN(全连接)：只要其中一个表中存在匹配，则返回行

INNER JOIN（内连接-笛卡尔积） 描述:SQL INNER JOIN 从多个表中返回满足 JOIN 条件的所有行,在表中存在至少一个匹配时返回行。

-- 在表中存在至少一个匹配时返回行。
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

-- 或:(INNER JOIN 与 JOIN 是相同的)

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

基础示例:

-- 显示满足ON条件的行，否则以左边基准表 + user_kc 每一条
SELECT u.xh,u.xm,kc.kcm,kc.grade 
FROM user AS u 
INNER JOIN user_kc AS kc ON u.xh = kc.xh 
ORDER BY u.xh

WeiyiGeek.

LEFT JOIN 关键字 描述:LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

SQL LEFT JOIN 语法

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

或：
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

基础实例：

-- LEFT JOIN 关键字演示
INSERT INTO user VALUES (1006,'WeiyiGeek','2019',21,'网络安全工程');

SELECT u.xh,u.xm,kc.kcm,kc.grade 
FROM user AS u 
LEFT JOIN user_kc AS kc ON u.xh = kc.xh 
ORDER BY u.xh

WeiyiGeek.

RIGHT JOIN 关键字 描述:RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。实际就是与LEFT相似只不过是左边关联不上的为NULL

SQL RIGHT JOIN 语法:

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

-- 或

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

基础实例:

-- 这次将user_kc作为左表,而user作为右表
SELECT u.xh,u.xm,kc.kcm,kc.grade 
FROM user_kc AS kc                     -- 注意此处与上面是不同的
RIGHT JOIN user AS u ON u.xh = kc.xh 
ORDER BY u.xh

WeiyiGeek.

FULL OUTER JOIN 关键字 描述:FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行它结合了 LEFT JOIN 和 RIGHT JOIN 的结果即 A 并 B。

FULL OUTER JOIN 语法:

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

基础实例:

#MySQL中不支持 FULL OUTER JOIN，你可以在 SQL Server 测试以下实例。
SELECT Websites.name, access_log.count, access_log.date
FROM Websites
FULL OUTER JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count DESC;

总结:

1.首先连接的结果可以在逻辑上看作是由SELECT语句指定的列组成的新表。左连接与右连接的左右指的是以两张表中的哪一张为基准它们都是外连接。

2.外连接就好像是为非基准表添加了一行全为空值的万能行，用来与基准表中找不到匹配的行进行匹配,两个没有空值的表进行左连接，左表是基准表，左表的所有行都出现在结果中，右表则可能因为无法与基准表匹配而出现是空值的字段。

3.JOIN 分类得到的结果数：

inner join <= min(left join, right join)
full join >= max(left join, right join)
当 inner join < min(left join, right join) 时  full join > max(left join, right join)

4.在使用 join 时，on 和 where 条件的区别如下:

- on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真都会返回左边表中的记录。
- where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

5.MySQL中不支持 FULL OUTER JOIN，你可以在 SQL Server 测试以下实例。

UNION 操作符

描述:SQL UNION 操作符合并两个或多个 SELECT 语句的结果。

-- SQL UNION 语法
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

---SQL UNION ALL 语法
SELECT column_name(s) FROM table1
UNION ALL -- 默认地UNION 操作符选取不同的值。如果允许重复的值请使用 UNION ALL。
SELECT column_name(s) FROM table2;

基础实例:

-- 从 "Websites" 和 "apps" 表中选取所有不同的country（只有不同的值）：
SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country; 

-- 使用 UNION ALL 从 "Websites" 和 "apps" 表中选取所有的中国(CN)的数据（也有重复的值）：
SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country; 

-- union 与 union all 在于前者可以剔除重复的字段的行，后者则是显示所有不管是否重复
SELECT xh,'#',xm FROM user
UNION ALL
SELECT xh,kcm,'-' FROM user_kc
ORDER BY xh

WeiyiGeek.

注意事项：

注:UNION 内部的每个 SELECT 语句必须拥有相同数量的列
注:列也必须拥有相似的数据类型，同时每个 SELECT 语句中的列的顺序必须相同。
注:UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

GROUP BY 语句

描述：GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。

基础语法:

-- SQL GROUP BY 语法
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

基础应用:

-- 统计 access_log 各个 site_id 的访问量
SELECT site_id, SUM(counts) AS nums FROM access_log GROUP BY site_id;

-- SQL GROUP BY 多表连接统计有记录的网站的记录数量：
SELECT Websites.name,COUNT(access_log.aid) AS nums FROM access_log
LEFT JOIN Websites  -- 关键点-表字段结合
ON access_log.site_id=Websites.id
GROUP BY Websites.name; -- 关键点


-- 基础实例
SELECT Aggregate.NAME,COUNT(Persons.ID) AS 'NUMS'
FROM Persons 
LEFT JOIN Aggregate
ON Aggregate.ID = Persons.ID
GROUP BY Aggregate.NAME;

SELECT Aggregate.NAME,COUNT(Persons.ID) AS 'NUMS'
FROM Persons 
LEFT JOIN Aggregate
ON Aggregate.NAME = Persons.LastName
GROUP BY Aggregate.NAME;

WeiyiGeek.

HAVING 语句

描述:HAVING 子句可以让我们筛选分组后的各组数据; 在 SQL 中增加 HAVING 子句原因是 WHERE 关键字无法与聚合函数一起使用。

-- SQL HAVING 语法
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

基础示例：

-- 查找总访问量大于 200 的网站，并且 alexa 排名小于 200。
SELECT Websites.name, SUM(access_log.count) AS nums FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id
WHERE Websites.alexa < 200
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200; 


-- 过滤掉分组结果为NULL的rows
SELECT Aggregate.NAME,COUNT(Persons.ID) AS 'NUMS'
FROM Persons 
LEFT JOIN Aggregate
ON Aggregate.NAME = Persons.LastName
GROUP BY Aggregate.NAME
HAVING Aggregate.NAME IS NOT NULL;

WeiyiGeek.HAVING

SELECT INTO 语句

描述:SELECT INTO 语句从一个表复制数据，然后把数据插入到另一个新表中,但是需要注意 MySQL 数据库不支持 SELECT ... INTO 语句，但支持 INSERT INTO ... SELECT , 当然你可以使用以下语句来拷贝表结构及数据(后面讲解CREATE会讲)：

CREATE TABLE 新表
AS
SELECT * FROM 旧表

SELECT INTO 语法：

-- 复制所有的列插入到新表中：
SELECT *
INTO newtable [IN externaldb]
FROM table1; 

-- 只复制希望的列插入到新表中：
SELECT column_name(s)
INTO newtable [IN externaldb]
FROM table1;

基础实例:

-- 创建 Websites 的备份复件,只复制中国的网站插入到新表中：
SELECT *
INTO WebsitesBackup2016
FROM Websites;
WHERE country='CN';

-- 复制多个表中的数据插入到新表中：
SELECT Websites.name, access_log.count, access_log.date
INTO WebsitesBackup2016
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id;

-- SELECT INTO 语句可用于通过另一种模式创建一个新的空表。
SELECT *
INTO newtable
FROM table1
WHERE 1=0;  -- 只需要添加促使查询没有数据返回的 WHERE 子句即可

注意事项:

注:新表将会使用 SELECT 语句中定义的列名称和类型进行创建。但是可以使用 AS 子句来应用新名称。

INSERT INTO SELECT 语句

描述:INSERT INTO SELECT 语句从一个表复制数据，目标表中任何已存在的行都不会受影响。然后把数据插入到一个已存在的表中(非常注意不同于SELECT INTO语句转存的表必须存在，而且字段类型一致)。

SQL INSERT INTO SELECT 语法：

-- 从一个表中复制所有的列插入到另一个已存在的表中：
INSERT INTO table2
SELECT * FROM table1;

-- 只复制希望的列插入到另一个已存在的表中：
INSERT INTO table2 (column_name(s))
SELECT column_name(s)
FROM table1;

基础实例:

-- 复制 "apps" 中的数据插入到 "Websites" 中：
INSERT INTO Websites (name, country)
SELECT app_name, country FROM apps;

select into from 和 insert into select 两则的区别？

相同点都是用来复制表，不同点前者MySQL数据库不支持，而后者语句语法支持以及复制的时候是否要目标表存在；

注意事项:

注:select into [newTable] from ：要求目标表不存在，因为在插入时会自动创建；将查询出来的数据整理到一张新表中保存，表结构与查询结构一致。
注:insert into [newTable] select from：要求目标表存在，为已经存在的表批量添加新数据。

-- 即，查询出来结果--->复制一张同结构的空表--->将数据拷贝进去。
select *（查询出来的结果） into newtable（新的表名）form where （后续条件）

-- 即，指定一张想要插入数据的表格(注意建立的表的字段结构)--->对数据进行加工筛选--->填入一张准备好的表格。
insert into  (已准备好的表) select *（或者取用自己想要的结构）frome 表名 where 各种条件

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2020-01-05，如有侵权请联系 cloudcommunity@tencent.com 删除

sql server