---------------------- <a href="http://edu.csdn.net"target="blank">ASP.Net+Android+IOS开发</a>、<a href="http://edu.csdn.net"target="blank">.Net培训</a>、期待与您交流! ----------------------
今天把SQL基础查询复习了一遍,以下是做的笔记:
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 [id]
,[username]
,[password]
FROM [Spring].[dbo].[UserInfo]
--选择第5-10行数据 (这个是之前我碰到的一个面试题)
select * from (select top 5 * from (select top 10 * from userinfo order by id asc)
as T order by id desc) as t1 order by id asc
--选择用户名不相同的所有用户
select distinct(username) from UserInfo
--选择id在7-10之间的数据
select * from UserInfo WHERE id between 7 and 10
--选择用户名为xcv和密码为dfv或者vb的数据
select * from UserInfo where username = 'xcv' and (password='dfv' or password = 'vb')
--用户名按字母排序
select * from UserInfo order by username
select * from UserInfo order by username desc
--往表中插入数据
insert into UserInfo (username) values (123)
--删除id为15的那行数据
delete from UserInfo where id =15
--选择表百分之五十的数据
SELECT TOP 50 PERCENT * FROM UserInfo
SELECT TOP 30 PERCENT * FROM UserInfo--选择用户名不包含v密码不包含L的所有数据
SELECT * FROM UserInfo WHERE username NOT LIKE '%V' AND password NOT LIKE '%L'
--选择第一个字符以后是nmnm的密码的数据
SELECT * FROM UserInfo WHERE password LIKE '_NMNM'
--选择首字母不是G和X的所有用户
SELECT * FROM UserInfo WHERE username NOT LIKE '[GX]%'
--选择用户名是cv 或者是sdf的数据
SELECT * FROM UserInfo WHERE username IN ('CV','SDF')
--选择首字母不在a-g之间的所有数据
SELECT * FROM UserInfo WHERE username NOT BETWEEN 'aS' AND 'GG'
--表和列别名的用法
SELECT * FROM UserInfo AS YOU ORDER BY YOU.id DESC
SELECT USERNAME AS NAME, password AS P,id AS ID FROM UserInfo
--•JOIN: 如果表中有至少一个匹配,则返回行
--•LEFT JOIN: 即使右表中没有匹配,也从左表返回所有的行
--•RIGHT JOIN: 即使左表中没有匹配,也从右表返回所有的行
--•FULL JOIN: 只要其中一个表中存在匹配,就返回行use Spring
--defualt is inner join
select * from Person, Orders
where Person.P_id = orders.P_id--left join
select * from person left join Orders on
person.P_id = orders.P_id--inner join
select * from person inner join Orders on
person.P_id = orders.P_id--right join
select * from person right join Orders on
person.P_id = orders.P_id--full join
select * from person full join Orders on
person.P_id = orders.P_id--SQL UNION
--UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
--UNION ALL 命令和 UNION 命令几乎是等效的,不过 UNION ALL 命令会列出所有的值。--SELECT INTO 语句
--SELECT INTO 语句从一个表中选取数据,然后把数据插入另一个表中。SELECT *
INTO Persons_backup1
FROM Person
where Person.City='London'
黑马程序员-SQL基础查询
最新推荐文章于 2023-07-20 16:16:43 发布