sql总结

记得按时学习o

已于 2023-02-24 09:47:57 修改

阅读量65

点赞数 1

分类专栏：数据库文章标签： sql 数据库 mysql java

于 2021-12-10 11:24:58 首次发布

本文链接：https://blog.csdn.net/weixin_52610705/article/details/121852226

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

介绍：不管你是零基础的小白，还是上班族都可以在本篇文章中找到你想要的知识，本篇文章涵盖了，常用sql函数，运算符，索引，视图等多方面知识，从创建表，删除表等，数据库的基本操作开始，带你深入浅出的了解sql掌握sql，你可以通过本篇一步一步从零开始学习sql，也可以查漏补缺，找到你想要的内容，在本篇的最后还整理了一些经典的sql案例，可以更好的让大家巩固刚学到的知识，满满干货，希望能够帮到大家！

2.查询所有表中包含zhangh字段的表：

3.like语句中的通配符和转义

对于命令：

4.查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断

一、数据库基本操作

学习sql当然是要先学会，对数据库表的添加和删除，还有对表中的数据的增删改查等操作，只有创建完表表里面有了数据才好进行后续的学习，接下来就开始我们的学习之旅把。

（一）对表进行创建修改和删除

1）CREATE table 创建表

1. 这是创建表的基本模式：

CREATE table 表名(字段1 字段1的数据类型 【约束】,
		          字段2 字段2的数据类型 【约束】,
		          ......
		         )【约束】;

2. 这些是创建表时用到的约束：

（1）NOT NULL：字段中不允许出现NULL
（2）UNIQUE：唯一标识数据库表中的每条记录，也就是约束该字段中没有重复的项，一个表中可以有多个UNIQUE约束
（3）PRIMARY KEY：设置该字段为主键，一个表中只能有一个主键
（4）FOREIGN KEY：一个表中的 FOREIGN KEY 是指向另一个表中的 PRIMARY KEY的
（5）CHECK：限制该字段中的值的范围，如CHECK (id>0)
（6）DEFAULT ：向字段中添加默认值
（7）ENGINE： 设置存储引擎，不写则默认为InnoDB
（8）CHARSET ：设置编码格式，默认为utf8

3.现在我们要创建一个名为student的表：

create table student(  --创建名为student的表
id tinyint  PRIMARY KEY,   --添加id列并设置为主键
name varchar(20) not null,  --添加name列并设置不能为空
age int(3) not NULL,   --添加age列并设置不能为空
phone char(13) default '000000000000'  --添加phone列并设置默认值
)engine=innodb charset=utf8;  设置存储引擎和编码格式

2）alter table 修改表

1.将 student表名修改为 student2；

Alter table student rename student2;

2. 修改列名

Alter table student2 change column name name2 varchar(20);

3.修改列的字段长度

Alter table student2 modify sname varchar(40);

4.在表中添加一个新的列为 gender；

Alter table student2 add gender char(2);

5. 在表中删除gender列

alter table student2 drop column gender

3）DROP TABLE 删除表

1.删除上面创建的student表

drop table student

（二）对表数据进行增删改查

1）insert into 添加数据

模式：

INSERT INTO 表名( 列名, 列名,...列名 )
                       VALUES
                       ( 数据, 数据,...数据 );

1.在上面创建的student表中添加一条数据。

insert into student(id,name,age) values(1,'张三',18);

2）select 查询数据

模式：

SELECT 列名,列名
FROM 表名
[WHERE Clause] --查询时候的查询条件
[LIMIT N][ OFFSET M]

1.查询student表中所有数据

select * from student

注意：

查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
SELECT 命令可以读取一条或者多条记录。
你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
你可以使用 WHERE 语句来包含任何条件。
你可以使用 LIMIT 属性来设定返回的记录数。
你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

3）update 更新数据

模式：

UPDATE 表名 SET 列名=要修改的数据, 列名=要修改的数据
[WHERE Clause]  --添加约束条件

1.将student表中id为1的数据的name值，修改为李四

update student set name='李四' where id=1

4）delete 删除数据

模式：

DELETE FROM 表名 [WHERE Clause] --条件

1.将student表中id为1的数据删除

delete from student where id=1

二、MySql数据类型

学习完上面对数据库表的操作后我们来看一下mysql中的数据类型都有那些，了解了这些可以更好的帮助我们在创建表的时候，根据你要添加的字段的性质选择合适的数据类型，而且在MySQL中定义数据字段的类型对你数据库的优化是非常重要的。

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

（一）数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 byte	(-128，127)	(0，255)	小整数值
SMALLINT	2 bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

（二）日期和时间类型

类型	大小 ( bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

（三）字符串类型

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

三、常用函数汇总

（一）数字函数

1.AVG(字段) 返回一个表达式的平均值

SELECT AVG(age) FROM student;
age=10;
age=12;
age=15;
---- 返回age平均值12

2.MAX(字段)返回字段中的最大值

SELECT MAX(age) AS maxAge FROM Student;
age=10;
age=12;
age=15;
---- 返回age最大值15

3.MIN(字段)返回字段中的最小值

SELECT MIN(age) AS minAge FROM Student;
age=10;
age=12;
age=15;
---- 返回age最小值10

4.SUM(expression)返回指定字段的总和

select sum(age) from user；
age=10;
age=12;
age=15;
---- 返回age的总和37

5.GREATEST(expr1, expr2, expr3, …) 返回列表中的最大值

SELECT GREATEST(3, 12, 34, 8, 25);
---- 返回以下数字列表中的最大值34

6.LEAST(expr1, expr2, expr3, …) 返回列表中的最小值

SELECT LEAST(3, 12, 34, 8, 25);
---- 返回以下数字列表中的最小值3

7.POW(x,y)/POWER(x,y)返回 x 的 y 次方

SELECT POW(2,3);
SELECT POWER(2,3);
---- 返回2 的 3 次方：8

8.RAND()返回 0 到 1 的随机数

SELECT RAND();
---- 返回 0 到 1 的随机数，若()里面有数字,RAND(x),x相同时，返回值相同

9.SQRT(x)返回x的平方根

SELECT SQRT(25);
---- 返回5

10.ABS(x) 返回x的绝对值

SELECT ABS(-1);
---- 返回1

11.ROUND(x)返回离 x 最近的整数（四舍五入）

SELECT ROUND(1.23456);
---- 返回 1

12.SQRT(x)返回x的平方根

SELECT SQRT(25);
---- 返回5

13.LN 返回数字的自然对数

SELECT LN(2);
---- 返回 2 的自然对数：0.6931471805599453

14.SIGN(x)返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1

SELECT SIGN(-10);
---- 返回 -1

15.ceil 向上取整，返回>=该参数的最小整数

select ceil (1.52);
---- 返回值2
SELECT CEIL (1.002);
---- 返回值2

16.floor向下取整，返回<=该参数的最大整数

SELECT FLOOR (9.99); 9 
---- 返回值9
SELECT FLOOR (-9.99); -10
---- 返回值-10

17.TRUNCATE(x,y)返回数值 x 保留到小数点后 y 位的值

SELECT TRUNCATE(1.23456,3);
---- 返回1.234

18.count（x）返回指定列的值的数目（NULL 不计入）

select count(pwd) from user 
---- 返回值2

（二）字符串函数

1.LENGTH(x) 返回指定字段的字段长度

select LENGTH(pwd) from user where id=1
pwd='1234'
---- 返回值4

2. SUBSTR（x，start，length）截取字符x 从起始位置开始，截取几个字符

select SUBSTR(pwd,2,3) from user
pwd='45678'
----返回值567

3.CONCAT(s1,s2…sn)字符串 s1,s2 等多个字符串合并为一个字符串

SELECT CONCAT('hel','llo');
---- 返回hello

4.FIND_IN_SET(s1,s2)返回在字符串s2中与s1匹配的字符串的位置

SELECT FIND_IN_SET("c", "a,b,c,d,e");
---- 返回3

5.FORMAT(x,n)函数可以将数字 x 进行格式化 “#,###.##”, 将 x 保留到小数点后 n 位，最后一位四舍五入

SELECT FORMAT(250500.5634, 2); 
---- 返回250,500.56

6.INSERT(s1,x,len,s2)字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串

SELECT INSERT("google.com", 1, 6, "runnob");
---- 返回runoob.com

7.LOCATE(s1,s)从字符串 s 中获取 s1 的开始位置

SELECT LOCATE('st','myteststring');
---- 返回5

8.LCASE(s)/LOWER(s)将字符串 s 的所有字母变成小写字母

SELECT LOWER('RUNOOB');
---- 返回runoob

9.UCASE(s)/UPPER(s)将字符串 s 的所有字母变成大写字母

SELECT UCASE('runoob');
---- 返回RUNOOB

10.TRIM(s)去掉字符串 s 开始和结尾处的空格

SELECT TRIM('    RUNOOB    ');
---- 返回RUNOOB

11.LTRIM(s)去掉字符串 s 开始处的空格

SELECT LTRIM('    RUNOOB    ');
---- 返回 ’RUNOOB   ‘

12.RTRIM(s)去掉字符串 s 结尾处的空格

SELECT RTRIM('    RUNOOB    ');
---- 返回 ’    RUNOOB‘

13.POSITION(s1 IN s)从字符串 s 中获取 s1 的开始位置

SELECT POSITION('b' in 'abc');
---- 返回2

14.REPEAT(s,n)将字符串 s 重复 n 次

SELECT REPEAT('runoob',3);
---- 返回runoobrunoobrunoob

15.REVERSE(s)将字符串s的顺序反过来

SELECT REVERSE('abc');
---- 返回cba

16.STRCMP(s1,s2)比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1

SELECT STRCMP("runoob", "runoob");
---- 返回0

17.返回字符串 s 的第一个字符的 ASCII 码

SELECT ASCII('AB');
---- 返回A的ASCII码值：65

（三）日期函数

1、CURDATE()/CURRENT_DATE()返回当前日期

SELECT CURDATE();
SELECT CURRENT_DATE();
---- 返回2021-07-19

2、CURRENT_TIME()/CURTIME()返回当前时间

SELECT CURRENT_TIME();
---- 返回15:43:45

3、CURRENT_TIMESTAMP()返回当前日期和时间

SELECT CURRENT_TIMESTAMP();
---- 返回2021-07-19 15:47:32

4、ADDDATE(d,n)计算起始日期 d 加上 n 天的日期

SELECT ADDDATE("2021-07-15", INTERVAL 10 DAY);
---- 返回2021-07-25

5、ADDTIME(t,n)时间 t 加上 n 秒的时间

SELECT ADDTIME('2011-11-11 11:11:11', 5);
---- 返回2011-11-11 11:11:16

6、DATE()从日期或日期时间表达式中提取日期值

SELECT DATE("2021-07-15 11:11:16");
---- 返回2021-07-15

7、DAY(d)返回日期值 d 的日期部分

SELECT DAY("2021-07-15"); 
---- 返回15

8、DATEDIFF(d1,d2)计算日期 d1->d2 之间相隔的天数

SELECT DATEDIFF('2001-01-01','2001-02-02'); 
---- 返回-32

9、DATE_FORMAT按表达式 f的要求显示日期 d

SELECT DATE_FORMAT('2011.11.11 11:11:11','%Y-%m-%d %r');
---- 返回2011-11-11 11:11:11 AM

10、DAYNAME(d)返回日期 d 是星期几，如 Monday,Tuesday

SELECT DAYNAME('2011-11-11 11:11:11');
---- 返回Friday

11、DAYOFMONTH(d)计算日期 d 是本月的第几天

SELECT DAYOFMONTH('2011-11-11 11:11:11');
---- 返回11

12、DAYOFWEEK(d)日期 d 今天是星期几，1 星期日，2 星期一，以此类推

SELECT DAYOFWEEK('2011-11-11 11:11:11');
---- 返回6

13、DAYOFYEAR(d)计算日期 d 是本年的第几天

SELECT DAYOFYEAR('2011-11-11 11:11:11');
---- 返回315

14、EXTRACT(type FROM d)从日期 d 中获取指定的值，type 指定返回的值

type可取值为：
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

SELECT EXTRACT(MINUTE FROM '2011-12-13 14:15:16');
---- 返回15

15、DAYOFWEEK(d)日期 d 今天是星期几，1 星期日，2 星期一，以此类推

SELECT DAYOFWEEK('2011-11-11 11:11:11');
---- 返回6

16、UNIX_TIMESTAMP()得到时间戳

SELECT UNIX_TIMESTAMP('2019-2-19');
SELECT UNIX_TIMESTAMP(expression);
---- 返回1550505600

17、FROM_UNIXTIME()时间戳转日期

SELECT FROM_UNIXTIME(1550505600);
---- 返回2019-02-19 00:00:00
SELECT FROM_UNIXTIME(1550505600, '%Y-%m-%d');
---- 返回2019-02-19

（四）组函数

注意事项：在select列表中所有未包含在组函数中的列(除非是组函数公式sum avg等)都应该包含在 group by子句中，如果少一个就会报错不能在where当中使用组函数(avg等)

1.group by 根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。

原始表：

select 类别, sum(数量) as 数量之和 from A group by 类别

返回结果：

2.count（x）返回指定列的值的数目（NULL 不计入）

select count(pwd) from user 
---- 返回值2

（五）其他函数

1.select version(); 查看数据库版本
2.select database(); 查看当前数据名
3.select user(); 查看当前用户 root
4.password(‘aaa’); 返回字符的密码形式
5.md5(‘字符’); 返回该字符的md5加密形式

四、运算符

运算符介绍：运算符是一个保留字或字符，主要用于连接WHERE后面的条件。

（一）算数运算符

运算符	描述
+ 加法	把运算符两边的值相加
- 减法	左操作数减去右操作数
* 乘法	把运算符两边的值相乘
/ 除法	左操作数除以右操作数
% 取模(除余)	左操作数除以右操作数后得到的余数

（二）比较运算符

运算符	描述
==	检查两个操作数的值是否相等，如果相等则条件为真
=	检查左操作数的值是否不大于右操作数的值，如果是则条件为真
!=	检查两个操作数的值是否相等，如果不相等则条件为真。
<>	检查两个操作数的值是否相等，如果不相等则条件为真
>	检查左操作数的值是否大于右操作数的值，如果是则条件为真
<	检查左操作数的值是否小于右操作数的值，如果是则条件为真
>=	检查左操作数的值是否大于等于右操作数的值，如果是则条件为真
<=	检查左操作数的值是否小于等于右操作数的值，如果是则条件为真
!<	检查左操作数的值是否不小于右操作数的值，如果是则条件为真
!>	检查左操作数的值是否不大于右操作数的值，如果是则条件为真

（三）位运算符

运算符	描述
&	如果同时存在于两个操作数中，二进制 AND 运算符复制一位到结果中
\|\|	如果存在于任一操作数中，二进制 OR 运算符复制一位到结果中
~	二进制补码运算符是一元运算符，具有"翻转"位效应
<<	二进制左移运算符。左操作数的值向左移动右操作数指定的位数
>>	二进制右移运算符。左操作数的值向右移动右操作数指定的位数

（四）逻辑运算符

运算符	描述
AND	AND 运算符允许在一个 SQL 语句的 WHERE 子句中的多个条件的存在。
BETWEEN	BETWEEN 运算符用于在给定最小值和最大值范围内的一系列值中搜索值。
EXISTS	EXISTS 运算符子查询中条件的真假，真则返回主查询，否则不返回。
IN	IN运算符用于把某个值与一系列指定列表的值进行比较。
NOT IN	IN 运算符的对立面，用于把某个值与不在一系列指定列表的值进行比较。
LIKE	LIKE运算符用于把某个值与使用通配符运算符的相似值进行比较。
GLOB	GLOB 运算符用于把某个值与使用通配符运算符的相似值进行比较。GLOB 与 LIKE 不同之处在于，它是大小写敏感的。
NOT	NOT运算符是所用的逻辑运算符的对立面。比如 NOT EXISTS、NOT BETWEEN、NOT IN，等等。它是否定运算符。
OR	OR运算符用于结合一个 SQL 语句的 WHERE 子句中的多个条件。
IS NULL	NULL 运算符用于把某个值与 NULL 值进行比较。
IS	IS 运算符与 = 相似。
IS NOT	IS NOT 运算符与 != 相似。
\|\|	连接两个不同的字符串，得到一个新的字符串。
UNIQUE	UNIQUE运算符搜索指定表中的每一行，确保唯一性（无重复）。

五、常用关键字

1.Like模糊查询

占位符：%  任意个数字符   _一个字符

查询 用户名以‘S’开头的员工信息
   Select * from emp where ename like 'S%'
 
查询用户名第二个字母是‘A’的员工信息
   select * from emp where ename like '_A%'
 
查询用户名第三个字母是‘A’的员工信息
   select * from emp where ename like '__A%'
 
包含A
   select * from emp where ename like '%A%'

2.order by 排序关键字

asc 升序（默认），desc 降序。当按照两列或者两列以上的列数进行排序时，在第一列排序结束后的基础上，对于第一排序列相同值的行，按照第二排序列进行排序，多列情况亦是以此类推。

数据表：

select * from user order by pwd

返回结果：

3.limt a b 强制 SELECT 语句返回指定的记录数,从a行开始取值，要取出的行数为b行。需要注意的是，数据表的行数下标从0开始。

数据表：

只查询第一条数据
select * from user limit 0,1

查询第一条和第三条数据
select * from user limit 0,3

4.= 比较值是否相等（只能传一个参数）

学号等于0001
select * from student where 学号=‘0001’

5.and可以连接两个或者多个判断条件

select * from user where pwd='1234' and name='ZHANGsan'

6.In可以传多个参数：

学号等于0001和0002
select * from student where 学号 in('0001','0002')

7.not in 是用来判断多个字段不等于的

学号不等于0001和0002
select * from student where 学号 not in('0001','0002')

8.any 表示任意一个

0001的课程的成绩至少大于，课程0002其中一个的成绩
select * from score where 课程号='0001' and 成绩>any(select 成绩 from score where 课程号='0002')

9.all表示所有

0001的课程的成绩要大于，课程0002中所有的成绩
select * from score where 课程号='0001' and 成绩>all(select 成绩 from score where 课程号='0002')

10.JOIN: 两张表中都匹配有的数据才会显示

11.LEFT JOIN:左外连接意思是包含左边表所有记录，右边所有的匹配的记录，如果没有则用null补齐

select A.S#,B.Sname,A.dc from(select S#,AVG(score)dc from SC group by S#)A
left join Student B on A.S#=B.S# where A.dc>=60
--2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

12.right join：右外连接和左外连接一样但是相反


select B.S#,B.Sname,A.选课总数,A.总成绩 from
(select S#,COUNT(C#)选课总数,sum(score)总成绩 from SC group by S#)A
right join Student B on A.S#=B.S#
--4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为null)

13.IS NULL 匹配条件为null的值

判断条件参数是否为 null
select * from zhanghb where yinjkh is null;

14. not NULL 匹配条件不为null的值

判断条件参数是否为 null
select * from zhanghb where yinjkh not null;

六、视图

（一）视图的概念

视图介绍：

在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。

视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。

注意：数据库的设计和结构不会受到视图中的函数、where 或 join 语句的影响。

（二）创建视图

原始表：

模式：

create view  视图名  as  select 字段名 from 表名;

实例1：

CREATE VIEW view_name AS SELECT * FROM smbms_user

返回结果：

实例2：

CREATE VIEW view_name AS
SELECT *FROM smbms_user 
WHERE id=1

返回结果：

实例3：

CREATE VIEW view_name3 AS
SELECT id,user_code,username
FROM smbms_user

返回结果：

注意：视图总是显示最近的数据。每当用户查询视图时，数据库引擎通过使用 SQL 语句来重建数据，也就是说如果创建视图所依赖的基础表发生变化，视图也会发生变化

（三）查询视图

查询视图和查询表一样

select * from view_name

（四）修改视图

原始视图表：

模式：

alter view 视图名 as select 语句

实例1：

alter view view_name as select id from smbms_user

返回结果：

（五）删除视图

DROP VIEW 视图名字

七、索引

（一）索引的概念：

索引（Index）是帮助MySQL高效获取数据的数据结构，通俗来讲索引就好比书本的目录，加快数据库的查询速度，SQL索引在数据库优化中占有一个非常大的比例，一个好的索引的设计，可以让你的效率提高几十甚至几百倍

（二）什么是索引？：

SQL索引有两种，聚集索引和非聚集索引，索引主要目的是提高了SQL Server系统的性能，加快数据的查询速度与减少系统的响应时间

下面举两个简单的例子：

图书馆的例子：一个图书馆那么多书，怎么管理呢？建立一个字母开头的目录，例如：a开头的书，在第一排，b开头的在第二排，这样在找什么书就好说了，这个就是一个聚集索引，可是很多人借书找某某作者的，不知道书名怎么办？图书管理员在写一个目录，某某作者的书分别在第几排，第几排，这就是一个非聚集索引

字典的例子：字典前面的目录，可以按照拼音和部首去查询，我们想查询一个字，只需要根据拼音或者部首去查询，就可以快速的定位到这个汉字了，这个就是索引的好处，拼音查询法就是聚集索引，部首查询就是一个非聚集索引.

看了上面的例子，下面的一句话大家就很容易理解了：聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续。就像字段，聚集索引是连续的，a后面肯定是b，非聚集索引就不连续了，就像图书馆的某个作者的书，有可能在第1个货架上和第10个货架上。还有一个小知识点就是：聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个。

（三）索引的作用：

- 提高查询效率

- 消除数据分组、排序

- 避免“回表”查询（索引覆盖）

- 优化聚合查询

- 用于多表JOIN关联查询

- 利用唯一性约束，保证数据唯一性

- InnDB行锁实现

（四）索引的副作用：

- 增加I/O成本

- 增加磁盘空间

- 不合适的索引或索引过多，会降低增删改的效率

（五）什么情况下设置索引：

动作描述	使用聚集索引	使用非聚集索引
外键列	应	应
主键列	应	应
列经常被分组排序(order by)	应	应
返回某范围内的数据	应	不应
小数目的不同值	应	不应
大数目的不同值	不应	应
频繁更新的列	不应	应
频繁修改索引列	不应	应
一个或极少不同值	不应	不应

（六）建立索引的原则：

1) 定义主键的数据列一定要建立索引。

2) 定义有外键的数据列一定要建立索引。

3) 对于经常查询的数据列最好建立索引。

4) 对于需要在指定范围内的快速或频繁查询的数据列;

5) 经常用在WHERE子句中的数据列。

6) 经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。

7) 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8) 对于定义为text、image和bit的数据类型的列不要建立索引。

9) 对于经常存取的列避免建立索引

9) 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。

10) 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

（七）索引的操作

1.创建索引

模式：

CREATE INDEX 索引名 ON 表名(列名1，列名2,...);

实例：

CREATE INDEX name_index ON index_test(name); 
---- 此为在index_test表上的name列上创建一个索引name_index。

2.在表上增加索引

模式：

ALTER TABLE 表名ADD INDEX 索引名 (列名1，列名2,...);

3.创建表时指定索引

模式：

CREATE TABLE 表名 ( [...], INDEX 索引名 (列名1，列名 2,...) );

实例：

CREATE UNIQUE INDEX id_num_index ON index_test(idNum); 
或者
ALTER TABLE index_test ADD UNIQUE id_num_index(idNum); 

----此为在index_test表的idNum列上创建一个唯一索引id_num_index

4.删除索引：以下两种模式都可以删除索引

模式：

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name

实例：

DROP INDEX name_index ON index_test;
----此为删除在index_test表上的name_index索引

5.查询索引：

MySQL：
SHOW INDEX FROM smbms_user
----即返回smbms_user表中的所有索引。

Oracle：
select * from all_indexes
----即返回所有索引。

select * from all_indexes where table_name='smbms_user'
----即返回smbms_user表中的所有索引。

返回的字段：

Table：表的名称

Non_unique：是否不唯一，0为唯一，1不为唯一

Key_name：索引的名称

Seq_in_index：索引中的列序列号，从1开始

Column_name：列名称

Collation：列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。

Cardinality：索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。

Sub_part：如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。

Packed：指示关键字如何被压缩。如果没有被压缩，则为NULL。

Null：如果列含有NULL，则含有YES。如果没有，则该列含有NO。

Index_type：用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。

Comment：更多评注。

工作常用问题解决：

1.查询所有表含有主键的表：

select table_name from user_constraints where owner='YANYIN' and constraint_type='P' group by table_name order by table_name

2.查询所有表中包含zhangh字段的表：

select * from USER_TAB_COLUMNS where column_namne ='zhangh'

3.like语句中的通配符和转义

LIKE比较运算符也可以称为通配符。LIKE运算符可以使用以下两个通配符“%”和“_”，其中“%”代表零个或多个字符，“_”代表一个且只能是一个字符。

对于命令：

SQL>select * from student where sname like ‘%\_%’ escape ‘\’;

其中’%\_%’一头一尾两个%意思是通配符：含有零个或多个字符，中间\是转义运算符，也就是\_表示的是下划线，而不是LIKE通配符，那么’%\_%’的意思就是名字含有下划线的学生，该下划线前后都可以有字符，也可以都没有字符。

另外，$也是转义运算符！作用和\一样。

4.查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断

select * from people
where peopleId in (select peopleId from people group by peopleId having count

(peopleId) > 1)

5.Oracle分页

orecle 分页用到的关键字是rownum“行的数”，它是一个伪列，查询的时候，除非特别指定，否则不会显示，表示行号，常用于控制返回的行号。

select rownum，要分页的表名.* from emp e

如果分页中需要排序的话，需要用子查询来完成，

select rownum, e1.* from(

select*from emp order by sal desc

)e1

(当 rownum 和 order by 一起使用时，会首先选出符合 rownum条件的记录，然后再进行排序.)

然后进行查询数据量，比如查询6到10条数据，此时,也需要使用嵌套子查询来实现，也就是再把它当作一张表，我们再查找。

select *from (select rownum rn，t.*

from (select *

from emp

order by sal asc) t

where rownum <= 10 page*size到第10条结束)e

where e.rn > 5;(rownum<(page－1)*size)从第5条开始

page（当前页数），size（每页显示的记录数

(由于先要进行 where 条件判断, 满足条件后才能生成，rownum, 所以导致 rownum 无法进行大于(>)和大于等于(>=)，的判断. 此时, 需要使用嵌套子查询来实现，也就是再把它当作一张表，我们再查找。)

select count(*) from T_BASE_PROVINCE;

在分页的时候，一般会显示总页数，需要先查询总数据量得到总页数，总页数=总量/每页显示记录数