SQL基础常考

SQL基础

SQL语句分为哪几类

• 数据库定义语言DDL（Data Definition Language）：操纵数据库的结构；

  主要有CREATE，DROP，ALTER等对逻辑结构有操作的，包括表结构、视图和索引。

• 数据库查询语言DQL（Data Query Language）：查数据；

  主要以SELECT为主。

• 数据操纵语言DML（Data Manipulation Language）：增删改数据；

  主要包括INSERT，UPDATE，DELETE

• 数据控制功能DCL（Data Control Language）：权限控制；

  主要是权限控制能操作，包括GRANT，REVOKE，COMMIT，ROLLBACK等。

SQL语句的执行顺序

select [id,name]										#6

from [table1] [tablename1]								#1

left join table2 [tablename2] on [conditions]			#2
inner join table3 [tablename3] on [conditions]	
……

where [conditions]										#3

group by [conditions]									#4

having [conditions]								 		#5

order by [conditions]									#7

临时表的生命周期

MySQL在执行SQL语句的时候会临时创建一些存储中间结果集的表，这种表被称为临时表。

临时表只对当前连接可见，在连接关闭后，临时表会被删除并释放空间。

临时表主要分为内存临时表和磁盘临时表两种。内存临时表使用的是MEMORY存储引擎，磁盘临时表使用的是MyISAM存储引擎。

一般在以下几种情况中会使用到临时表：

• FROM中的子查询
• DISTINCT查询并加上ORDER BY
• ORDER BY和GROUP BY的子句不一样时会产生临时表
• 使用UNION查询会产生临时表

数据类型

varchar和char的区别

• varchar表示变长，char表示长度固定。当所插入的字符超过他们的长度时，在严格模式下，会拒绝插入并提示错误信息，在一般模式下，会截取后插入。如char(5)，无论插入的字符长度是多少，长度都是5，插入字符长度小于5，则用空格补充。对于varchar(5)，如果插入的字符长度小于5，则存储的字符长度就是插入字符的长度，不会填充。
• 存储容量不同，对于char来说，最多能存放的字符个数为255。对于varchar，最多能存放的字符个数是65532。
• 存储速度不同，char长度固定，存储速度会比varchar快一些，但在空间上会占用额外的空间，属于一种空间换时间的策略。而varchar空间利用率会高些，但存储速度慢，属于一种时间换空间的策略。

MySQL中int(10)和char(10)和varchar(10)的区别？

int(10)中的10表示的是显示数据的长度，而char(10)和varchar(10)表示的是存储数据的大小。

NULL和空值

• NULL也就是在字段中存储NULL值，空值也就是字段中存储空字符(’’)。
• 空值(’’)的长度是0，是不占用空间的；而的NULL长度是NULL。
• 要单纯查NULL值列，则使用 is NULL去查，单纯去查空值(’’)列，则使用 =''
• count(vol)不会统计值为NULL的对象，count(mainkey)，count(1)才能

Null的特性

• 如果null参与算术运算，则该算术表达式的值为null。（例如：+，-，*，/ 加减乘除）
• 如果null参与比较运算，则结果可视为false。（例如：>=,<=,<> 大于，小于，不等于）
• 如果null参与聚集运算，则聚集函数都置为null（使用isnull(字段,0)等方式可以避免这种情况）。除count(*), count(1), count(0)等之外（count(字段) 字段为null的行不参与计数）。
• 如果在not in子查询中有null值的时候,则不会返回数据。

子查询

把一个查询的结果在另一个查询中使用，子查询的结果是一个临时表，细分结果类型对应不同的应用，如：

• 标量子查询：返回一个值。如用于合成一个全局变量，在上级查询中对所有对象进行比较判断。

  SELECT * FROM user WHERE age = (SELECT max(age) from user)  //查询年纪最大的人
  

• 列子查询：返回一个列。如用于合成一个指标字段，在上级查询筛选符合该指标的对象。

  SELECT num1 FROM table1 WHERE num1 > ANY (SELECT num2 FROM table2)
  

• 行子查询：返回一个行。相比标量子查询，相当于合成一个对象，在上级查询中对所有对象进行比较判断。

  SELECT * FROM user WHERE (age,sex) = (SELECT age,sex FROM user WHERE name="zhangsan")
  

• 表子查询：返回一个表。相当于返回一个对象集合，上级查询中可筛选符合集合属性的对象。

  SELECT * FROM student WHERE (name,age,sex) IN (SELECT name,age,sex FROM class1) //在学生表中找到班级在1班的学生
  

连接查询

如图：

• 连接查询的目的是利用多个表的信息。<select_list>的值域，为表A和表B字段张成的空间。
• 在没有WHERE筛选条件的情况下，左外连接的左表的所有对象都被表达，对象空间被B表所含字段扩充。
• WHERE是对查询结果的筛选，建立在关联表结果之上。

设L表

A	B
a1	b1
a2	b2
a3	b3

设R表

B	C
b1	c1
b2	c2
b4	c3

• 左外连接：Aleft joinB，关键字左边的A为主角，依某种条件连接上B的信息

  select L.`*`,R.`*` from L left join R on L.b=R.b
  

  A	B	B	C
  a1	b1	b1	c1
  a2	b2	b2	c2
  a3	b3	null	null

• 右外连接：Aright joinB，关键字右边的B为主角，依某种条件连接上A的信息

  select L.`*`,R.`*` from L right join R on L.b=R.b
  

  B	C	A	B
  b1	c1	a1	b1
  b2	c2	a2	b2
  b4	c3	null	null

• 全外连接：取并集

  MySQL没有FULL JOIN,用两次外连接加去重联合得到

  SELECT * FROM L LEFT JOIN R ON L.b=R.b
  UNION
  SELECT * FROM L RIGHT JOIN R ON L.b=R.b
  

• 内连接：Ainner joinB，取交集

  select L.`*`,R.`*` from L inner join R on L.b=R.b
  

• 交叉连接：笛卡尔积

  select L.`*`,R.`*` from L,R
  

  A	B	B	C
  a1	b1	b1	c1
  a1	b1	b2	c2
  a1	b1	b4	c3
  a2	b2	b1	c1
  a2	b2	b2	c2
  a2	b2	b4	c3
  a3	b3	b1	c1
  a3	b3	b2	c2
  a3	b3	b4	c3

关键字

mysql中in和exists的区别？

in和exists一般用于子查询。

• 使用exists时会先进行外表查询，将查询到的每行数据带入到内表查询中看是否满足条件；
• 使用in一般会先进行内表查询获取结果集，然后对外表查询匹配结果集，返回数据。
• in在内表查询或者外表查询过程中都会用到索引。
• exists仅在内表查询时会用到索引
• 一般来说，当子查询的结果集比较大，外表较小使用exist效率更高；当子查询寻得结果集较小，外表较大时，使用in效率更高。
• 对于not in和not exists，not exists效率比not in的效率高，与子查询的结果集无关，因为not in对于内外表都进行了全表扫描，没有使用到索引。not exists的子查询中可以用到表上的索引。

select * from TableIn where exists(select BID from TableEx where BNAME=TableIn.ANAME)
select * from TableIn where ANAME in(select BNAME from TableEx)
select * from TableIn where ANAME=ANY(select BNAME from TableEx)

UNION和UNION ALL的区别？

union和union all的作用都是将两个结果集合并到一起。

• union会对结果去重并排序，union all直接直接返回合并后的结果，不去重也不进行排序。
• union all的性能比union性能好。

drop、delete和truncate的区别？

	drop	delete	truncate
速度	快	逐行删除，慢	较快
类型	DDL	DML	DDL
回滚	不可回滚	可回滚	不可回滚
删除内容	删除整个表，数据行、索引都会被删除	表结构还在，删除表的一部分或全部数据	表结构还在，删除表的全部数据

一般来讲，删除整个表，使用drop，删除表的部分数据使用delete，保留表结构删除表的全部数据使用truncate。