About MySQL database A collection of add, delete, modify and query statements

1. Basic sql statements

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

1. Add data

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(值)

2. Delete data

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

3. Modify data

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

4. Read data

(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

(7) Range query

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

(8) Discrete query

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)去掉数据

(9) Aggregation function (statistical query)

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 #求最小值

(10)Paging query

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

(11) Deduplication query

select distinct brand from car

(12) Group query
Query the number of cars in each series in the car table

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

After grouping, only this column or aggregate function can be queried

Get the series code where the average price of the series is greater than 40

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

Get the series code where the maximum fuel consumption of the series is greater than 8

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

2. Advanced query of MySql (Use external connection)

Connection query

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

--Multi-table join query

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

--The number of fields in joint query must be the same

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')

Description: Use outer join

##A、left （outer） join：

Left outer join (Left join): The result set includes matching rows of the join table and all rows of the left join table.

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

##B：right （outer） join:

Right outer join (right join): The result set includes both the matching join rows of the join table and all rows of the right join table.

##C：full/cross （outer） join：

Full outer join: not only includes matching rows of the symbolic connection table, but also includes all records in the two connected tables.

Group:Group by:

<span style="font-size: 16px"><strong></strong></span>A table , once the grouping is completed, only group-related information can be obtained after querying.

##Group related information: (Statistical information) count,sum,max,min,avg Standards for grouping)

<p class="line number60 index59 alt1"><span style="font-size: 16px"><strong><code class="sql spaces"> The fields in the selecte statistical function cannot be placed together with ordinary fields;

E：Outer join query (table name 1: a table name 2: b)

##select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

F: The usage of between, between includes boundary values ​​when limiting the query data range, not betweenNot including

##select * from table1 where time between time1 and time2

##select a,b,c, from table1 where a not between Value 1 and Value 2

Four-table joint query problem: ##select

* from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where ....H：

##select

10 * form table1 where Range I:Select all the information of the record with the largest a in each group of data with the same b value (can be used for monthly forum rankings, monthly hot-selling product analysis, by subject Score ranking, etc.)

select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)

The above is the detailed content of About MySQL database A collection of add, delete, modify and query statements. For more information, please follow other related articles on the PHP Chinese website!