查詢

增加文本
1. create database test
刪除文本
2. drop database test
3. create table info
(
?? ?code int primary key,
?? ?name varchar(20) not null
)
auto_increment 自增長列
foreign key(列名) references 主表名(列名)? 外鍵關系

4. drop table info

CRUD:
增加語法
1.insert into 表名(列名) values(值)
刪除語句的語法，從哪個表里面刪，后面跟的條件是什么
2.delete from 表名 where 條件
修改的語法，修改哪一張表，列名還有條件
3.update 表名 set 列名=值 where 條件

簡單查詢

1.最簡單查詢（查所有數據）
select * from 表名；??????? 注：* 代表所有列
select * from info（info是一個表名稱）

2.查詢指定列
select code,name from info

3.修改結果集的列名
select code as '代號',name as '姓名' from info

4.條件查詢
select * from info where code='p003'

5.多條件查詢
查詢info表中code為p003或者nation為n001的所有數據
select * from info where code='p003' or nation='n001'
查詢info表中code為p004并且nation為n001的數據
select * from info where code='p004' and nation='n001'

6.范圍查詢
select * from car where price>=40 and price<=60
select * from car where price between 40 and 60

7.離散查詢
查詢汽車價格在（10,20,30,40,50,60）中出現的汽車信息
select * from car where price=10 or price=20 or price=30 or price=40 or price=50 or price=60

select * from car where price in(10,20,30,40,50,60)
select * from car where price not in(10,20,30,40,50,60)

8.模糊查詢（關鍵字查詢）
查詢car表里面名稱包含奧迪的
select * from car where name like '%奧迪%'???????? %任意n個字符
查詢car中名稱第二個字符為‘馬’的汽車
select * from car where name like '_馬%'????????????? _任意一個字符

9.排序查詢
select * from car order by price asc????????????? asc升序（省略）
select * from car order by oil desc??????????????? desc降序

先按照brand升序排，再按照price降序排
select * from car order by brand,price desc

10.去重查詢
select distinct brand from car

11.分頁查詢
一頁顯示10條 當前是第3頁
select * from chinastates limit 20,10

一頁顯示m條 當前是第n頁
limit? (n-1)*m，m

12.聚合函數（統計函數）
select count(areacode) from chinastates? #查詢數據總條數
select sum(price) from car? #求和
select avg(price) from car? #求平均
select max(price) from car? #求最大值
select min(price) from car?? #求最小值

13.分組查詢
查詢汽車表中每個系列下有多少個汽車
select brand,count(*) from car group by brand

查詢汽車表中賣的汽車數量大于3的系列
select brand from car group by brand having count(*)>3