chp4T—SQL语言基础.pptVIP

Chp4 T-SQL语言基础;主要内容;常量和变量;常量;变量;局部变量;全局变量;练习;运算符和表达式;常规方式 use pubs go select pub_id,pub_name,country from publishers go;用*表中所有的列 select * from authors 使用top关键字 eg1: select top 6 * from authors eg2: select top 10 percent * from authors ;使用distinct关键字 eg1: select state from authors eg2: select distinct state from authors eg3: select distinct state,city from authors eg4: select state,city from authors;使用计算列 eg1: select title_id,price,price*0.7 from titles eg2: select au_lname+.+au_fname,city+,+state from authors; eg1: select title_id代号,price原价,price*0.7现价 from titles eg2: select title_id as 代号,price as 原价,price*0.7 as现价 from titles eg3: select 代号 =title_id, 原价= price, 现价 =price*0.7 from titles eg4: select title_id 代号,price 原价,price*0.7 现价 from titles;语法格式为： ORDER BY 表达式1 [ASC|DESC][,表达式2[ASC|DESC][,…n]] select * from titles order by price select * from titles order by price asc select * from titles order by price desc ; select * from titles order by type,price ;练习1;练习2;练习3; ;select * from titles where type=business and price10 select * from titles where price20 or price10 select * from titles where not (price10 and price20);使用between关键字 查询书价在15和20员之间的图书 select title,price from titles where price between 15 and 20 等价于： select title,price from titles where price =15 and price=20 ;select title,price from titles where price not between 15 and 20 等价于： ？？？;使用in关键字 select * from authors where state in(CA,KS,MI,IN) select * from authors where state =CAor state=KSor state=MIor state=IN;练习4;通配符% 表示0~n个任意字符 select au_lname,au_fname from authors where au_fname like D% select * from titles where title like %Computer%;通配符_(下划线) 表示单个字符 select au_lname,au_fname, phone, au_id from authors where au_id like ‘72_-%’ select au_lname,au_fname, phone, au_id from authors where au_id like _7% ;通配符[ ] 方括号列出的任意一个字符 select au_lname,au_lname, phone, au_id fr