1. SELECT TOP子句
1. SQL Server | MS Access 語法
sekect top number | percent 字段名 from 表名;
2. MySQL 和 Oracle 中的 select top 等價(jià)
select * from school limit 8;
select 字段名 | * from 表名 limit number;
select 字段名 | * from 表名 where rownum <= number;
3. MySQL select top percent 實(shí)例
在 Microsoft SQL Server 數(shù)據(jù)庫(kù)中可執(zhí)行
select top number percent * from 表名;
2. LIKE語法
select 表名 from where 字段名 like pattern;
例: select school from where name 'G%';G開頭的所有名字
[%]在模式的前后定義通配符
select * from school where name like '%k'; 以k結(jié)尾
select * from school where name like 'c%'; 以c開頭
select * from school where name like '%oo%'; 含有oo
select * from school -> where name like '%00%'; 不含有oo的所有數(shù)據(jù)
3. 通配符
1. % 替代 0 個(gè)或多個(gè)字符
select * from 表名 where 字段名 like '值';
select * from school where name like 't%';
2. _ 替代一個(gè)字符
select * from 表名 where 字段 like '_值'; 值的首字符用_代替,
select * from school where name like '_ongzhu'; 也可以_ on_zh_
3. [charlist] 字符列中的任何單一字符
select * from 表名 where 字段名 regexp '^[字符]';
select * from school where name regexp '^[ty]';
select * from school where name regexp '^[a-z]'; 選取 name以 a 到 z 字母開頭的名字
4. [^charlist] 或 [!charlist] 不在字符列中的任何單一字符
select * from school where name regexp '^[^a-z]'; 選取 name 不以 A 到 H 字母開頭的名字
4. 操作符
1. [in]
允許在 where 子句中規(guī)定多個(gè)值
select * from 表名 where 字段名 in (values1,values2,...);
select * from school where remark in ('keai','eef');
2. [between]
選取介于兩個(gè)值之間的數(shù)據(jù)范圍內(nèi)的值
select * from 表名 where 字段名 between value1 and value2;
select * from school where number between 700 and 900;
3. [not between]
選取不在這個(gè)范圍內(nèi)的值
select * from 表名 where 字段名 not between value1 and value2;
select * from school where number not between 700 and 900;
4. [帶有in 的 between]
select * from 表名 where (字段名 between value and value ) and not 字段名 in ( 'value','value');
select * from school where (字段名 between value and value ) and not 字段名 in ( 'value','value');
5. [帶有文本值的not between]
select * from 表名 where 字段名 not between '值' and '值';
select * from school where name not between 'a' and 'h';
6. [帶有日期值的between]
select * from 表名 where date between '日期值' and ‘日期值’;
select * from dates where date bewttn '2017-03-25' and '2017-04-19';
7. [union]
操作符合并兩個(gè)或多個(gè) SELECT 語句的結(jié)果
select 字段名 from 表1 union select 字段名 from 表2 order by 字段名; 三個(gè)字段名為同名
select country from website union select country from apps order by country;
8. [union all]
選取所有的country
select 字段名 from 表1 union all select 字段名 from 表2 order by 字段名; 三個(gè)字段名為同名
select country from website union all select country from apps order by country;
9. [帶有where 的 union all]
選取所有的某一個(gè)值
select 字段名 , 字段名 from 表1 where 字段名='值' union all select 字段名, 字段名 from 表2 where 字段名='值' order by 字段名; 三個(gè)字段名為同名
select country, name from website where country='CN' union all select country, appname from apps where country='CN' order by country;
5. SQL別名
1. [列別名]
select 字段名 as 別名 from 表名;
select count as number from dates;
多列合一列如下:
select name, concat(字段名, ',' , 字段名,.....）as 別名 from 表名;
select name, concat (name, ',' , count) as nc from dates;
2. [表別名]
select 別名.字段名声滥，別名.字段名炊邦，別名.字段名谜酒，... from 表名 as 別名 where 別名.字段名='值';
select d.id, d.name, d.count, d.date from dates as d where d.name='safari';
[下面的情況下使用別名很有用:]
- 在查詢中涉及超過一個(gè)表
- 在查詢中使用了函數(shù)
- 列名稱很長(zhǎng)或者可讀性差
- 需要把兩個(gè)列或者多個(gè)列結(jié)合在一起
3. SQL JOIN
1. 可以使用的不同的 SQL JOIN 類型:
** INNER JOIN**
如果表中有至少一個(gè)匹配，則返回行
select 表名.字段名, 表名.字段名, 表名.字段名, ... from 表名1 inner join 表名2 on 表名1.字段名=表名2.字段名;
select dates.id, dates.name, dates.date, log.alexa from dates inner join log on dates.id=log.sid;
or
select dates.id, dates.name, dates.date, log.alexa from dates join log on dates.id=log.sid;
LEFT JOIN
即使右表中沒有匹配，也從左表返回所有的行
select 表名.字段名, 表名.字段名, 表名.字段名, ... from 表名1 left join 表名2 on 表名1.字段名=表名2.字段名;
select dates.id, dates.name, dates.date, log.alexa from dates left join log on dates.id=log.sid;
or
select dates.id, dates.name, dates.date, log.alexa from dates left outer join log on dates.id=log.sid;
** RIGHT JOIN**
即使左表中沒有匹配，也從右表返回所有的行
select 表名.字段名, 表名.字段名, 表名.字段名, ... from 表名1 right join 表名2 on 表名1.字段名=表名2.字段名;
select dates.id, dates.name, dates.date, log.alexa from dates right join log on dates.id=log.sid;
or
select dates.id, dates.name, dates.date, log.alexa from dates right outer join log on dates.id=log.sid;
FULL OUTDER JOIN
只要其中一個(gè)表中存在匹配，則返回行
MySQL中不支持 FULL OUTER JOIN呼盆，可以在 SQL Server 測(cè)試以下實(shí)例。
select 表名.字段名, 表名.字段名, 表名.字段名, ... from 表名1 full outer join 表名2 on 表名1.字段名=表名2.字段名 order by 表2.字段名 desc;
select dates.name, dates.date, log.count from dates full outer join log on dates.id=log.sid order by log.count desc;
4. INSERT INTO SELECT
復(fù)制A表中的數(shù)據(jù)到B表
insert into A表 (字段名蚁廓，字段名宿亡，...) select 字段名，字段名 from B表;
insert into website (name, country) select appname, country from apps;
