SQL常用总结

最新推荐文章于 2024-07-02 20:06:37 发布

第七范式

最新推荐文章于 2024-07-02 20:06:37 发布

阅读量154

点赞数

文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/MarcoPola/article/details/120334596

版权

数据库表
| sid | sname | sage | ssex |
| — | — | — | — |
| 2 | 钱二 | 19 | 女 |
| 3 | 张三 | 17 | 男 |
| 4 | 李四 | 18 | 女 |
| 5 | 王五 | 17 | 男 |
| 6 | 赵六 | 19 | 女 |

student

sid	cid	score
1	1	56
1	2	78
1	3	null
1	4	58
2	1	79
2	2	null
2	3	92
2	4	68
3	1	91
3	2	null
3	3	88
3	4	56
4	2	88
4	3	90

cid	cname	tid
1	语文	1
2	数学	2
3	英语	3
4	物理	4

course

建表语句

CREATE TABLE student 
  ( 
     sid INT PRIMARY KEY NOT NULL,
     sname VARCHAR(30), 
     sage INT, 
     ssex VARCHAR(8) 
  ) ;
 
CREATE TABLE course 
  ( 
     cid INT PRIMARY KEY NOT NULL, 
     cname VARCHAR(30), 
     tid INT 
  ) ;
 
CREATE TABLE sc 
  ( 
     sid INT NOT NULL, 
     cid INT NOT NULL, 
     score INT 
  ) ;
 
INSERT INTO course VALUES (1, '语文', 1);
INSERT INTO course VALUES (2, '数学', 2);
INSERT INTO course VALUES (3, '英语', 3);
INSERT INTO course VALUES (4, '物理', 4)；


INSERT INTO student VALUES (1, '刘一', 18, '男');
INSERT INTO student VALUES (2, '钱二', 19, '女');
INSERT INTO student VALUES (3, '张三', 17, '男');
INSERT INTO student VALUES (4, '李四', 18, '女');
INSERT INTO student VALUES (5, '王五', 17, '男');
INSERT INTO student VALUES (6, '赵六', 19, '女');

INSERT INTO sc VALUES (1, 1, 56);
INSERT INTO sc VALUES (1, 2, 78);
INSERT INTO sc VALUES (1, 3, null);
INSERT INTO sc VALUES (1, 4, 58);
INSERT INTO sc VALUES (2, 1, 79);
INSERT INTO sc VALUES (2, 2, null);
INSERT INTO sc VALUES (2, 3, 92);
INSERT INTO sc VALUES (2, 4, 68);
INSERT INTO sc VALUES (3, 1, 91);
INSERT INTO sc VALUES (3, 2, null);
INSERT INTO sc VALUES (3, 3, 88);
INSERT INTO sc VALUES (3, 4, 56);
INSERT INTO sc VALUES (4, 2, 88);
INSERT INTO sc VALUES (4, 3, 90);

SELECT、INSERT 批量、UPDATE、CREATE

--DISTINCT可以结合聚合函数去重统计
SELECT COUNT(DISTINCT Country) FROM Customers; 

Insert into Table2(field1,field2,...) select value1,value2,... from Table1;
/*
要求目标表Table2必须存在，并且字段field,field2...也必须存在。
注意Table2的主键约束，如果Table2有主键而且不为空，则 field1， field2...中必须包括主键。
*/

SELECT vale1, value2 into Table2 from Table1;
/*
要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。
*/


--创建主键自增的表
CREATE TABLE Persons                
(                
ID int NOT NULL AUTO_INCREMENT,  //ID字段自增              
LastName varchar(255) NOT NULL,                
FirstName varchar(255),                
Address varchar(255),                
City varchar(255),                
PRIMARY KEY (ID)                
)

SQL AND & OR&NOT 运算符

SELECT sid, sname, sage, ssex FROM student
WHERE ssex='女' AND (sage=18 OR sage=19);
--运行结果
sid	sname sage	ssex
 2	钱二    19	女
 4	李四	  18	女
 6	赵六	  19	女

NULL、NOT NULL
- 使用比较运算符（例如=，<或<>）不能测试NULL值，应该使用IS NULL和IS NOT NULL运算符。

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

DELETE、DROP、TRUNCATE

--DELETE删除表中的所有行，而不需要删除该表。这意味着表的结构、属性和索引将保持不变：
DELETE FROM table_name;
DELETE * FROM table_name;

--DROP 语句删除索引、表和数据库。

--TRUNCATE只需要删除表中的数据，而不删除表本身。
TRUNCATE TABLE table_name;

ORDER BY、GROUP BY
运算符
- 算术运算符
- 比较运算符
- 逻辑运算符
- 否定条件运算符
SQL 表达式
- 布尔表达式
- 数值表达式
- 日期表达式

--获取当前日期和时间值
SELECT CURRENT_TIMESTAMP,
	CURRENT_DATE,
	CURRENT_TIME,
	NOW();

--运行结果

|current_timestamp           |current_date|	 current_time   |   now   |
|2021-07-21 09:55:24.037126+08|2021-07-21|09:55:24.037126+08|2021-07-21 09:55:24.037126+08|

SQL SELECT TOP, LIMIT
- SELECT TOP 子句用于指定要返回的记录数量。
- SELECT TOP子句在包含数千条记录的大型表上很有用。返回大量记录会影响性能。
- MYSQL外部分数据库不支持，

--查询前三条记录
SELECT TOP 3 * FROM student;

--查询前百分之五十的记录
SELECT TOP 50 PERCENT * FROM student;

LIMIT作为一种简单的分页方法，主要是为了减少数据返回的时间，如果您查询一个非常大的表(例如一个有数十万或数百万行的表)而不使用限制，那么您可能会等待很长时间才能显示所有的结果，所以使用LIMIT可以减少查询数据返回的时间，提高效率。

SELECT column1, column2 
FROM TABLE 
LIMIT OFFSET,	//offset参数指定要返回的第一行的偏移量。第一行的偏移量为0，而不是1。
COUNT;//count指定要返回的最大行数。

SQL LIKE 、IN 、BETWEEN运算符
- 有两个通配符与LIKE运算符一起使用：
  - ％ - 百分号表示零个，一个或多个字符
  - _ - 下划线表示单个字符
  - 可以使用AND或OR运算符组合任意数量的条件。例如：’%or%’、‘a_%_%’。
  - 搭配 SQL [charlist] 通配符
- IN 运算符允许您在 WHERE 子句中指定多个值，是多个 OR 条件的简写。

SELECT sname FROM student
WHERE ssex NOT IN (SELECT ssex FROM student where sname = '钱二');

--运行结果
sname
张三
王五

BETWEEN运算符用于选取介于两个值之间的数据范围内的值，值可以是数字，文本或日期，包括开始和结束值，且开始值需小于结束值。

--数字
SELECT sname FROM student
WHERE (sage BETWEEN 10 AND 18)
AND NOT sid IN (1,2,3);
--运行结果
sname
李四
王五

--文本
SELECT sname, sage, ssex FROM student
WHERE sname NOT BETWEEN '张三' AND '王五'
ORDER BY sname;
--运行结果
sname	sage	ssex
张三	    17	   男
赵六  	19	   女

数据类型
- 参考SMD数据要求
连接查询JOIN
- 值得注意的是，连接是在WHERE子句中执行的。可以使用几个操作符连接表，例如=、<、>、<=、>=、！=、BETWEEN、LIKE、和NOT。
- INNER JOIN：内部链接INNER JOIN关键字选择两个表中具有匹配值的记录。INNER JOIN 与 JOIN 是相同的。

--查询张三的所有成绩
SELECT
	course.cname, sc.score 
FROM
	( ( course INNER JOIN sc ON sc.cid = course.cid ) INNER JOIN student ON student.sid = sc.sid ) 
WHERE
	student.sid = 2;
 
 --运行结果
cname	score
 语文 	79
 数学	  null //这个null是数据库对应的字段值为null
 英语 	92
 物理	    68

LEFT JOIN：SQL左链接LEFT JOIN关键字返回左表（表1）中的所有行，即使在右表（表2）中没有匹配。如果在正确的表中没有匹配，结果是NULL。

--查询所有人的所有成绩
SELECT
	student.sname,
	course.cname,
	sc.score 
FROM
	student
	LEFT JOIN sc ON ( student.sid = sc.sid )
	LEFT JOIN course ON course.cid = sc.cid;
  
  --运行结果
  sname	cname	score
   钱二	语文	79
   钱二	数学	81
   钱二	英语	92
   钱二	物理	68
   张三	语文	91
   张三	数学	47
   张三	英语	88
   张三	物理	56
   李四	数学	88
   李四	英语	90
   王五   null null
   赵六	null null

RIGHT JOIN： SQL右链接 RIGHT JOIN 关键字返回右表（table2）的所有行，即使在左表（table1）上没有匹配。如果左表没有匹配，则结果为NULL。

--将sc表的3	2	 47这行数据的成绩字段得值47设置为null，查询所有学科的所有成绩
SELECT
	course.cname,
	sc.score 
FROM
	sc
	RIGHT JOIN course ON sc.cid = course.cid
	ORDER BY course.cname;
  
  --运行结果
  cname	score
    数学	88
    数学	81
    数学	null
    数学	78
    物理	68
    物理	58
    物理	56
    英语	67
    英语	88
    英语	90
    英语	92
    语文	91
    语文	79
    语文	56

FULL JOIN：当左（表1）或右（表2）表记录匹配时，FULL OUTER JOIN关键字将返回所有记录。

FULL OUTER JOIN关键字返回左表（Customers）中的所有行，以及右表（Orders）中的所有行。如果 "Customers"中的行中没有"Orders"中的匹配项，或者"Orders"中的行中没有 "Customers"中的匹配项，那么这些行也会列出。

--查询所有学科对应的所有学生以及所有成绩
SELECT
	student.sname,
	course.cname,
	sc.score 
FROM
	student
	full JOIN sc ON ( student.sid = sc.sid )
	full JOIN course ON course.cid = sc.cid;
  
--运行结果
sname	cname	score
null    语文	   56
null    数学	   78
null    物理	   58
钱二	   语文	  79
钱二	   英语	  92
钱二	   物理	  68
张三	   语文	  91
张三	   英语     88
张三	   物理	  56
李四	   数学	  88
李四	   英语  	  90
张三	   数学	 null
null    英语    null
钱二	   数学	 null
王五	   null   null
赵六	   null   null

SELF JOIN：用于将表连接到自己，就好像该表是两个表一样，临时重命名了SQL语句中的至少一个表

--查询年龄相同的学生
SELECT 
  A.sname AS name1,
	B.sname AS name2,
	A.sage 
FROM
	student
	A JOIN student B 
	ON 
	A.sid < B.sid --去除重复的结果对
	AND 
	A.sage = B.sage 
ORDER BY
	A.sname;
  
--运行结果
  name1	name2	sage
  钱二	赵六	19
  张三	王五	17

SQL UNION、UNION ALL运算符
- UNION运算符用于组合两个或更多SELECT语句的结果集，而不返回任何重复的行。

--查询student、sc两个表所有不重复的sid

select sid from student 
UNION 
select sid from sc
order by sid asc;

--运行结果
sid
  1
  2
  3
  4
  5
  6
  
--查询student、sc两个表所有的sid

select sid from student 
UNION ALL
select sid from sc
order by sid asc;

--运行结果
sid
  1
  1
  1
  1
  2
  2
  2
  2
  2
  3
  3
  3
  3
  3
  4
  4
  4
  5
  6

SQL 约束
- 约束是作用于数据表中列上的规则，用于限制表中数据的类型。约束的存在保证了数据库中数据的精确性和可靠性。约束有列级和表级之分，列级约束作用于单一的列，而表级约束作用于整张数据表。约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。
- SQL创建约束
- 删除约束
- 完整性约束
  - NOT NULL 约束：保证列中数据不能有 NULL 值
  - DEFAULT 约束：提供该列数据未指定时所采用的默认值
  - UNIQUE 约束：保证列中的所有数据各不相同
  - 主键约束：唯一标识数据表中的行/记录
  - 外键约束：唯一标识其他表中的一条行/记录
  - CHECK 约束：此约束保证列中的所有值满足某一条件
  - 索引：用于在数据库中快速创建或检索数据
SQL 索引类型
- 逻辑分类：单列索引、组合索引、唯一索引、非唯一索引，函数索引。
- 物理分类：区分索引、非分区索引、B树索引、正向索引、反向索引，位图索引。
索引失效
- 最佳左前缀原则——如果索引了多列，要遵守最左前缀原则。指的是查询要从索引的最左前列开始并且不跳过索引中的列，违反了最佳左前缀原则，导致索引失效，变为ALL，全表扫描。
- 不可以在索引列上做任何操作（计算，函数，（自动或者手动）类型装换），否则会导致索引失效而导致全表扫描。
- 存储引擎不能使用索引中范围条件右边的列，范围之后索引失效。（< ,> between and）。
- mysql使用不等于(!= 或者<>)的时候，无法使用索引，会导致索引失效。
- mysql中使用is not null 或者 is null会导致无法使用索引。
- mysql中like查询是以%开头，索引会失效变成全表扫描，覆盖索引。
- mysql中，字符串不加单引号索引会失效。
- mysql中，如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引。
避免使用索引
- 数据重复且分布平均的表字段，可以用（该字段不同的数据的数量）/（该字段总的数据量），值越接近1，说明不怎么重复，越有建索引的价值。
- 小的数据表不应当使用索引。
- 需要频繁进行大批量的更新存取或者插入操作的表，因为每次更新不只更新记录还会更新索引。
- 如果列中包含大数或者 NULL 值，不宜创建索引。
- Where里用不到的字段的不创建索引。
优缺点
- 索引能够提高 SELECT 查询和 WHERE 子句的速度，但是却降低了包含 UPDATE 语句、 INSERT 语句、delete语句的数据输入过程的速度。索引的创建与删除不会对表中的数据产生影响。

使用时机

建立索引的原则：

- 定义主键的数据列一定要建立索引。
- 定义有外键的数据列一定要建立索引。
- 对于经常查询的数据列最好建立索引。
- 对于需要在指定范围内的快速或频繁查询的数据列;
- 经常用在WHERE子句中的数据列。
- 经常出现在关键字order by、group by、distinct后面的字段，建立索引。
- 如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。
- 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
- 对于定义为text、image和bit的数据类型的列不要建立索引。
- 装载数据后再建立索引。
- 在联接属性上建立索引（主外键）。
- 经常排序、统计、分组的列。
- 删除不经常使用的索引。
- 指定索引块的参数，如果将来会在表上执行大量的insert操作，建立索引时设定较大的ptcfree。
- 指定索引所在的表空间，将表和索引放在不同的表空间上可以提高性能。
- 对大型索引，使用NOLOGGING子句创建大型索引。
- 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。
- 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

--创建索引
CREATE INDEX index_name ON table_name;

--添加 PRIMARY KEY（主键索引）
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` );

--单列索引基于单一的字段创建，其基本语法如下所示：
CREATE INDEX index_name
ON table_name (column_name);

--唯一索引不止用于提升查询性能，还用于保证数据完整性。唯一索引不允许向表中插入任何重复值。其基本语法如下所示：
CREATE UNIQUE INDEX index_name
on table_name (column_name);

--聚簇索引在表中两个或更多的列的基础上建立。其基本语法如下所示：
CREATE INDEX index_name
on table_name (column1, column2);

/*
创建单列索引还是聚簇索引，要看每次查询中，哪些列在作为过滤条件的 WHERE 子句中最常出现。
如果只需要一列，那么就应当创建单列索引。如果作为过滤条件的 WHERE 子句用到了两个或者更多的列，那么聚簇索引就是最好的选择。
*/

--隐式索引由数据库服务器在创建某些对象的时候自动生成。例如，对于主键约束和唯一约束，数据库服务器就会自动创建索引。

--　索引可以用 SQL DROP 命令删除。删除索引时应当特别小心，数据库的性能可能会因此而降低或者提高。其基本语法如下：
DROP INDEX table_name.index_name;

HAVING与WHERE区别

1、查询中用到的关键词主要包含六个，并且他们的顺序依次为

select --> from --> where --> group by --> having --> order by

其中select和from是必须的，其他关键词是可选的

2、六个关键词的执行顺序是：

from --> where --> group by --> having --> select --> order by

from: 需要从哪个数据表检索数据。

where: 过滤表中数据的条件。

group by: 如何将上面过滤出的数据分组。

having: 对上面已经分组的数据进行过滤的条件。

select: 查看结果集中的哪个列，或列的计算结果。

order by: 按照什么样的顺序来查看返回的数据。

3、使用注意：

①、from后面的表关联，是自右向左解析的，而where条件的解析顺序是自下而上的。也就是说，在写SQL的时候，尽量把数据量小的表放在最右边来进行关联（用小表去匹配大表）；而把能筛选出小量数据的条件放在where语句的最左边（用小表去匹配大表）。

②、使用count（列名）当某列出现null值的时候，count（*）仍然会计算，但是count(列名)不会。

③、group by：select 列a, 聚合函数（聚合函数规范） from 表名 where 过滤条件 group by 列a

group by 子句也和where条件语句结合在一起使用。当结合在一起时，where在前，group by在后。即先对select xx from xx的记录集合用where进行筛选，然后再使用group by对筛选后的结果进行分组。

④、having：having条件表达式，但是需要注意having和where的用法区别：

i. having只能用在group by之后，对分组后的结果进行筛选(即使用having的前提条件是分组)。

ii. where肯定在group by之前，即也在having之前。

iii. where后的条件表达式里不允许使用聚合函数，而having可以。

⑤、当一个查询语句同时出现了where, group by, having, order by的时候，执行顺序和编写顺序是：

i. 执行where xx对全表数据做筛选，返回第1个结果集。

ii. 针对第1个结果集使用group by分组，返回第2个结果集。

iii. 针对第2个结集执行having xx进行筛选，返回第3个结果集。

iv. 针对第3个结果集中的每1组数据执行select xx，有几组就执行几次，返回第4个结果集。

v. 针对第4个结果集排序。
Aggregate 函数：

  - AVG() - 返回平均值
  - COUNT() - 返回行数
  - FIRST() - 返回第一个记录的值
  - LAST() - 返回最后一个记录的值
  - MAX() - 返回最大值
  - MIN() - 返回最小值
  - SUM() - 返回总和

SQL Scalar 函数：

- UCASE() - 将某个字段转换为大写
- LCASE() - 将某个字段转换为小写
- MID() - 从某个文本字段提取字符
- LEN() - 返回某个文本字段的长度
- ROUND() - 对某个数值字段进行指定小数位数的四舍五入
- NOW() - 返回当前的系统日期和时间
- FORMAT() - 格式化某个字段的显示方式

SQL FIELD()函数

FIELD()函数返回的索引（从1开始的位置）的str在str1，str2，str3，…列表中。如果str没有找到，则返回0。

就是用第一个参数str，跟后面的N个字符串参数中寻找，如果寻找到一模一样的字符串，返回其索引位置。
- FIELD(str,str1,str2,str3,…)
  
  返回的索引（从1开始的位置）的str在str1，str2，str3，…列表中。如果str没有找到，则返回0。
SQL 字母大小写转换函数UPPER()、UCASE()、LOWER()和LCASE()
SQL TRIM()函数去除字符串头尾空格