SQL小结

SQL

--------------------

insert into users(name,age,...) values('',12,..) ; -- insert

update users set name = 'xxx',age = xxx ,... where id = xxx ; -- update

delete from users where id = xxx -- delete

-- 投影查询 projection.

select id,name from users where ... order by limit xxx --select

-- 查询时直接上独占写锁

select * from users for update;

连接查询

---------------------

1.准备表[mysql.sql]

drop table if exists customers; -- 删除表

drop table if exists orders ; -- 删除表

create table customers(id int primary key auto_increment , name varchar(20) , age int); -- 创建customers表

create table orders(id int primary key auto_increment , orderno varchar(20) , price float , cid int); -- 创建orders表

-- 插入数据

insert into customers(name,age) values('tom',12);

insert into customers(name,age) values('tomas',13);

insert into customers(name,age) values('tomasLee',14);

insert into customers(name,age) values('tomason',15);

-- 插入订单数据

insert into orders(orderno,price,cid) values('No001',12.25,1);

insert into orders(orderno,price,cid) values('No002',12.30,1);

insert into orders(orderno,price,cid) values('No003',12.25,2);

insert into orders(orderno,price,cid) values('No004',12.25,2);

insert into orders(orderno,price,cid) values('No005',12.25,2);

insert into orders(orderno,price,cid) values('No006',12.25,3);

insert into orders(orderno,price,cid) values('No007',12.25,3);

insert into orders(orderno,price,cid) values('No008',12.25,3);

insert into orders(orderno,price,cid) values('No009',12.25,3);

insert into orders(orderno,price,cid) values('No0010',12.25,NULL);

2.查询--连接查询

mysql>-- 笛卡尔积查询,无连接条件查询

mysql>select a.*,b.* from customers a , orders b ;

mysql>-- 内连接,查询符合条件的记录.

mysql>select a.*,b.* from customers a , orders b where a.id = b.cid ;

mysql>-- 左外连接,查询符合条件的记录.

mysql>select a.*,b.* from customers a left outer joinorders b on a.id = b.cid ;

mysql>-- 右外连接,查询符合条件的记录.

mysql>select a.*,b.* from customers a right outer joinorders bona.id = b.cid ;

mysql>-- 全外连接,查询符合条件的记录(mysql不支持全外链接)

mysql>select a.*,b.* from customers afull outer join orders bon a.id = b.cid ;

2.查询--分组

字段列表  表      条件        分组        组内条件      排序    分页

mysql>select ...    from ... where ... group by ... having ...  order by ... limit ..

mysql>-- 去重查询

mysql>selectdistinctprice,cid from orders ;

mysql>-- 条件查询

mysql>select price,cid from orderswhereprice > 12.27 ;

mysql>-- 聚集查询

mysql>select max(price) from orders ;

mysql>select min(price) from orders ;

mysql>select avg(price) from orders ;

mysql>select sum(price) from orders ;

mysql>select count(id) from orders ;

mysql>-- 分组查询

mysql>select max(price) from orders where cid is not nullgroup bycid ;

mysql>-- 分组查询(组内过滤)

mysql>select cid ,orderno,max(price) as max_price,min(price) from orders where cid is not null group by cidhaving max_price > 20 ;

mysql>-- 降序查询

mysql>select cid ,orderno,max(price) as max_price,min(price) from orders where cid is not null group by cid having max_price > 20 order by max_price desc;

mysql>--模糊查询

mysql>select  * from customers where namelike'toma%'

mysql>select  * from customers where namenot like'toma%'

mysql>--范围查询

mysql>select  * from customers where idin(1,2,3)

mysql>select  * from customers where idnot in(1,2,3)

mysql>-- between 1 and 10,闭区间

mysql>select  * from customers where id between 1 and 3 ;

mysql>select  * from customers where id >= 1 and id <= 3 ;

mysql>-- 嵌套子查询(查询没有订单的客户)

mysql>select  * from customers where id not in (select distinct cid from orders where cid is not null);

mysql>-- 嵌套子查询(查询订单数量>2的客户)

mysql>select * from customers where id in (select cid from orders group by cid having count(cid) > 2);

mysql>select * from customers where id in ( select t.cid from (select cid,count(*) as c from orders group by cid having c > 2) as t);

mysql>-- 嵌套子查询(查询客户id,客户name,订单数量,最贵订单，最便宜订单，平均订单价格 where 订单数量>2的客户)

mysql>select a.id,a.name,b.c,b.max,b.min,b.avg

  from customers a,((select cid,count(cid) c , max(price) max ,min(price) min,avg(price) avg from orders group by cid having c > 2) as b)

  where a.id = b.cid ;