(复习笔记之数据库)SQL基本语句

数据库的建立

CREATE DATABASE KK

USE KK
GO
CREATE TABLE KK(
id int identity(1,1),
age varchar(10) null)

USE KK
GO
DROP TABLE KK

DROP DATABASE KK

上面这些就不解释了

表的基本操作

准备工作：这里就直接使用网上的代码直接建立数据库吧

if object_id('zhuisuo')is not null
drop table zhuisuo
go
create table zhuisuo
(
id int null,
name varchar(20) null
)
insert into zhuisuo values(1,'追索1')
insert into zhuisuo values(2,'追索2')
insert into zhuisuo values(3,'追索3')
insert into zhuisuo values(4,'追索4')
insert into zhuisuo values(5,'追索5')
insert into zhuisuo values(6,'追索6')
insert into zhuisuo values(7,'追索7')
insert into zhuisuo values(8,'追索8')
insert into zhuisuo values(9,'追索9')
insert into zhuisuo values(10,'追索10')
go

SELECT * FROM zhuisuo

INSERT INTO zhuisuo VALUES(10,'11')

UPDATE zhuisuo SET name ='12' WHERE name ='11'

DELETE FROM zhuisuo WHERE name ='12'

按着顺序来练习一下，也不解释了.

高级查询运算词

UNION: SELECT * FROM zhuisuo UNION SELECT * FROM zhuisuo2 //不重复查询出两个表的行，假如在UNION后面加多个ALL就会全部查出来

EXCEPT: SELECT * FROM zhuisuo EXCEPT  SELECT * FROM zhuisuo2  //查询出zhuisuo表中和zhuisuo2表不相同的行，就是查出zhuisuo表中不重复的行

INTERSECT:  SELECT * FROM zhuisuo INTERSECT  SELECT * FROM zhuisuo2  //查询出zhuisuo表中和zhuisuo2表相同的行，就是查出重复的行

连接：

INNER JOIN: 

SELECT * FROM zhuisuo as a INNER JOIN zhuisuo2 as b ON a.id =b.id

//内连接，查询出符合条件结果行

LEFT OUTER JOIN:  

SELECT * FROM zhuisuo as a LEFT OUTER JOIN zhuisuo2 as b ON a.id =b.id

//左连接:  结果集几包括连接表的匹配行，也包括左连接表的所有行。 

RIGHT OUTER JOIN:

SELECT * FROM zhuisuo as a RIGHT OUTER JOIN zhuisuo2 as b ON a.id =b.id

//右连接:  结果集既包括连接表的匹配连接行，也包括右连接表的所有行。 

FULL OUTER JOIN:

SELECT * FROM zhuisuo as a FULL OUTER JOIN zhuisuo2 as b ON a.id =b.id

//全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。