sql最简单的查询语句

– 2 ******************************************
– 最简单的查询语句

– 2.1 ————————————————–

– 使用 select 语句查询表中的数据

– SELECT * FROM table_name

use pubs

– 切换当前数据库 select * from authors

– 2.2 ————————————————–

– 使用完全限定名称

– SELECT * FROM [server_name].[database_name].[owner].[object]

select * from Northwind.dbo.Employees

select * from Northwind..Orders

– 2.3 ————————————————–

– 查询数据表中指定的列 – SELECT column_1,column_2 FROM table_name

select * from authors select au_id,au_lname,au_fname,city from authors

– 2.4 ————————————————–

– 使用 where 字句筛选指定的行

– SELECT * FROM table_name WHERE

select * from jobs

select * from jobs where job_id=7

select * from authors

select * from authors where au_lname = ‘White’

– 2.5 ————————————————–

– where 字句中的搜索条件

– 比较操作符 =, <, >, <=, >=, <>

– 字符串比较符 like, not like

– 逻辑操作符 and, or, not

– 值的域 between, not between

– 值的列表 in, not in

– 未知的值 is null, is not null

select * from jobs where job_id >=10

select * from jobs where job_desc like ‘%manager%’

select * from jobs where job_id >=10 AND job_desc like ‘%manager%’

select * from jobs where min_lvl between 100 and 150

select * from jobs where job_id in (1,3,5,7,11,13)

select * from discounts where stor_id is null

– 2.6 ————————————————–

– 使用 like

– % 代表 0 个或多个字符串

– _ 代表任何单个的字符

– [] 代表指定区域内的任何单个字符

– [^] 代表不在指定区域内的任何单个字符

select * from authors where au_lname like ‘G%’

select * from authors where address like ‘%Av.%’

select * from authors where au_fname like ‘A__’

select * from authors where au_fname like ‘[AS]%’

select * from authors where au_lname like ‘S[^m]%’

– 2.7 ————————————————–

– 格式化结果集

– order by 排序结果集

– distinct 消除重复的行

– as 改变字段的名字

select * from employee order by fname

select * from employee order by hire_date desc

select distinct type from titles

select au_id,au_lname as [Last Name],au_fname as [First Name] from authors