SQL SERVER 基础语法三

目录

• select top
• LIKE操作符
• IN 操作符
• BETWEEN操作符
• 别名
• JOIN(连接)
• UNION操作符
• select into语句
• insert into select

 

 

1.select top子句

用于规定要返回的记录的数目。

对于数千条记录的表是非常有用的。

注：不是所有的数据库系统都支持select top语句。MYSQL支持LIMIT语句来选择指定的条数数据，ORACLE可以使用ROWNUM来选取。

 

sql server语法

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;

　　

2.LIKE操作符

作用：在where子句中搜索列中的指定模式

select column_name(s) from table_name where column_name LIKE pattern

　　

(1).通配符

通配符	描述
%	替代一个或多个字符
_	仅替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist]或[!charlist]	不在字符列中的任何单一字符

 

 

如

匹配ue结尾的

select id,name,age,address from people where name LIKE '%ue'

　　

匹配ue开头的

select id,name,age,address from people where name LIKE 'ue%'

　　

匹配中间含有ue的

select id,name,age,address from people where name LIKE '%ue%'

　　

(2).[charlist]通配符

如搜索以"J"或者"W"或者"K"开头的

select id,name,age,address from people where name like '[JWK]%'

　　

如果搜索不以"J"或者"W"或者"K"开头的

就加！

 

select id,name,age,address from people where name like '[!JWK]%'

　　

(3)REGEXP或NOT REGEXP操作正则表达式

如

select * from TEST where name REGEXP '^[GFs]'

　上面SQL选取以G ,F,s开头的所有记录

select * from TEST where name REGEXP '^[A-H]'

　　

上面选取name 中A到H开头的所有记录

select * from TEST where name REGEXP '^[^A-H]'

　　

上面选取不以A-H开头的所有记录

 

3. IN 操作符

IN操作符允许在where子句中规定多个值

语法如下

select column_name(s) from table_name where column_name IN (value1,value2,value3....)　

也就是说where查询的列中允许指定多个值做为过滤记录

如

select * from TEST where name IN('google','baidu')

　　

上面选取name列中google和baidu的所有记录

注:IN可以规定多个值，而=只能规定一个值

但是=号也可以表示，代码量比较多。

上面换成=号如下表示

select * from TEST where name='google' or name='baidu'

　　

4.BETWEEN操作符

作用，选取介于两个值之间的数据范围内的值

语法

select column_name from table_name where column_name BETWEEN value1 and value2

　

选取在范围内的值BETWEEN value1 AND value2

选取不在范围内的值 NOT BETWEEN value1 AND value2

 

(1)使用AND 和IN为范围内的值再次过滤

如

select * from TEST where (name BETWEEN 1 AND 20) AND country NOT IN('USA','IND')

　　

上面选取1-20的不包含USA和IND的记录

 

5.别名

(1)别名

作用，为表名或者列名指定别名

增加可读性

为表名指定别名

select column_name  from table_name AS alias_name

　　

为列名指定别名

select column_name AS alias_name from table_name

　　

如果是为多个列名指定别名，要用逗号隔开。

如

select column_name1 AS alias_name1,column_name2 AS alias_name2 from table_name

　　

(2)使用CONCAT函数将多个列结合别名

CONCAT函数将多个列文本组合成一个新列。再用AS将新的列指定别名

注：因为CONCAT不会自动产生分融符，所以需要自己在托号里添加分隔符。

如下

select anme,CONCAT(url,',',alexa,',',country) AS site_info

　　

上面将url,alexa,country三个列结合成一个新列，别名为site_info

效果如下:

name                      site_info

google              https://google.com,1,USA

 

(3)表的别名实例

如表名为test1,test2两个。test1表有name字段，url字段。test2有count字段，date字段

为了增加可读性。

select w.name,w.url,a.count,a.date 
from test1 AS w,test2 AS a 
where a.site_id=w.id and w.name='google'

　　

不带别名的实例

select test1.name,test1.url,test2.count,test2.date 
from test1 ,test2
where test2.site_id=test1.id and test1.name='google'

　　

注:

以下情况使用别名很有用

在查询中涉及超过一个表

在查询中使用的函数

列名称很长或者可读性差

需要把两个列或者多个列结合在一起

 

6.连接 JOIN

作用：把两个或多个表的行结合起来

JOIN的七种用法

 

 

 

 

注：

• INNER JOIN：如果表中有至少一个匹配，则返回行
• LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
• FULL JOIN：只要其中一个表中存在匹配，则返回行

(1)INNER JOIN

INNER JOIN关键字在表中存在至少一个匹配时返回行

语法

select  column_name(s) from table1 INNER JOIN table2  
ON table1.column_name=table2.column_name
或者
select column_name(s) from table1 JOIN table2
ON table1.column_name=table2.column_name

　　

注：INNER JOIN 和JOIN是相同的。

如 表Websites

+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘宝          | https://www.taobao.com/   | 13    | CN      |
| 3  | 菜鸟教程      | http://www.runoob.com/    | 4689  | CN      |
| 4  | 微博          | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/ |   0 | IND     |
+----+---------------+---------------------------+-------+---------+

 

和表access_log

mysql> SELECT * FROM access_log;
+-----+---------+-------+------------+
| aid | site_id | count | date       |
+-----+---------+-------+------------+
|   1 |       1 |    45 | 2016-05-10 |
|   2 |       3 |   100 | 2016-05-13 |
|   3 |       1 |   230 | 2016-05-14 |
|   4 |       2 |    10 | 2016-05-14 |
|   5 |       5 |   205 | 2016-05-14 |
|   6 |       4 |    13 | 2016-05-15 |
|   7 |       3 |   220 | 2016-05-15 |
|   8 |       5 |   545 | 2016-05-16 |
|   9 |       3 |   201 | 2016-05-17 |
+-----+---------+-------+------------+
9 rows in set (0.00 sec)

查询返回网站的访问记录

select Websites.name,access_log.count,access_log.date
from Websites INNER JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count

　　

 

 

如果不存在匹配时，不会显示这些行。

ON条件是在生成临时表时使用的条件，它不管ON中的条件是否为真，都会返回左边表中的记录

where条件是在临时表生成好这后，再对临时表进行过滤的条件。这个时候已经没有LEFT JOIN(必须返回左边表的记录)的含义。条件不为真的就全部过滤掉。

(2) LEFT JOIN关键字

从左表返回所有的行，即使右表中没有匹配。然后如果右表没有匹配，结果返回NULL

语法:

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

　　

上面语法中可以看出，先从左表table1返回所有的行，然后，如果table2表没有匹配到的结果就为NULL

也就是说，没有匹配到的左表都会显示结果，但是为NULL

 

(3) RIGHT JOIN关键字

 

从右表中返回所有的行，即使左表中没有匹配的行。然后，如果左边没有匹配行，结果为NULL

 

语法:

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

　　

table2所有行都会返回，但是如果Table1表没有匹配，table2那些没有匹配到的结果显示为NULL。

(4) FULL OUTER JOIN关键字

FULL OUTER JOIN关键字只要左表和右表其中一个表中存在匹配，就返回行。那些没有匹配到的，都会显示，但是为NULL。

因此，它结合了LEFT JOIN和RIGHT JOIN的结果。

 

语法:

select column_name(s)
from table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name

　　

 

(5)总结

A inner join B    取A和B匹配到的交集记录

A left join B    取A的全部，在A中没有B表匹配的记录结果为NULL。

A right join B  取B的全部，在B中没有A表匹配的记录结果为NULL

A full outer join B  取A和B的全部，但是他们没有匹配的记录，结果都为NULL

 

 

7.UNION操作符

UNION操作符合并两个或多个select语句的结果集。

但是UNION内部的每个Select语句必须拥有相同数量的列，列也必须相似的数据类型，每个select语句中的列顺序必须相同。

 语法:

select column_name(s) from table1
union
select column_name(s) from table2

　　

union all语法

select column_name(s) from table1
union all
select column_name(s) from table2

　　

注：上面的union结果集中的列名总是等于union中第一个select 语句的列名

 

8. SELECT INTO

从一个表复制数据，插入到另一个新表中。

不支持mysql数据库

不支持的可以用另一个语句来拷贝数据表

create table 新表
as
select * from 旧表

　　

select into语法

（将所有的列插入新表中）

select * into newtable (IN externaldb) from table1

　　

或(只插入指定列到新表中)

select column_name into newtable (IN externaldb) from table1

　　

9.insert into select语句

将一个表数据，复制数据到一个已存在的表中。

语法

insert into table2
select * from table1

　　

或者

insert into table2(column1,column2,......) select column_name from table1

　　

select into from  要求目标表不存在，插入时自动创建新表

insert into select from 要求目标存在。