À propos de la base de données MySQL Une collection d'instructions d'ajout, de suppression, de modification et de requête

CRUD操作：
create 创建（添加）
read 读取
update 修改
delete 删除

insert into Info values('p009','张三',1,'n001','2016-8-30 12:9:8') ; 
给特定的列添加数据
insert into Info (code,name) values('p010','李四');
自增长列的处理
insert into family values('','p001','数据','T001','数据',1);
insert into 表名 values(值)

删除所有数据
delete from family
删除特定的数据
delete from Info where code='p001'
delete from 表名 where 条件

修改所有数据
update Info set name='徐业鹏' 
修改特定数据
update Info set name='吕永乐' where code='p002' 
修改多列
update Info set name='吕永乐',sex=1 where code='p003' 
update 表名 set 要修改的内容 where 条件  tno =

(1)简单读取,查询所有列(*)  所有行（没有加条件）
select * from Info
(2)读取特定列
select code,name，class from Info
(3)条件查询
select * from Info where code='p003'
(4)多条件查询
select * from Info where code='p003' or nation='n002' #或的关系
select * from Info where sex=0 and nation='n002' #与的关系
(5)关键字查询（模糊查询）
查所有包含奥迪的汽车
select * from car where name like '%奥迪%'; #百分号%代表任意多个字符 
查以'皇冠'开头的所有汽车
select * from car where name like '皇冠%';
查询汽车名称中第二个字符是'马'的
select * from car where name like '_马%'; #下划线_代表任意一个字符
(6)排序查询
select * from car order by powers  #默认升序排列
select * from car order by powers  #升序asc 降序 desc
先按brand升序排，再按照price降序排
select * from car order by brand,price desc

select * from car where price9()>40 and price<60
select * from car where price between 40 and 60

select * from car where price=30 or price=40 or price=50 or price=60;
select * from car where price in(30,40,50,60)取出数据
select * from car where price not in(30,40,50,60)去掉数据

select count(*) from car
select count(code) from car #取所有的数据条数
select sum(price) from car #求价格总和
select avg(price) from car #求价格的平均值
select max(price) from car #求最大值
select min(price) from car #求最小值

select * from car limit 0,10  #分页查询，跳过几条数据(0)取几条(10)
规定一个每页显示的条数：m
当前页数：n]
select * from car limit (n-1)*m,m

select distinct brand from car

select brand,count(*) from car group by brand

select brand from car group by brand having（加条件） avg(price)>40

select brand from car group by brand having max(oil)>8

SELECT t1.Name,t2.Brand_Name FROM brand t2,car t1 -- 笛卡尔乘积
WHERE t2.Brand = t1.Brand

SELECT t1.Name,t2.Brand_Name,t3.prod_name  FROM car t1 LEFT JOIN brand t2 ON t1.Brand = t2.Brand
LEFT JOIN productor t3 ON t2.Prod = t3.Prod

SELECT `Name`,Price FROM car 
UNION
 SELECT Brand_Name,Brand_Memo FROM brand
-- 子查询（***）
SELECT * FROM car WHERE car.brand in (SELECT Brand FROM brand WHERE Prod = 'p001')