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

-- 查詢時直接上獨(dú)占寫鎖

select * from users for update;

連接查詢

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

1.準(zhǔn)備表[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); -- 創(chuàng)建customers表

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

-- 插入數(shù)據(jù)

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

-- 插入訂單數(shù)據(jù)

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>-- 內(nèi)連接,查詢符合條件的記錄.

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.查詢--分組

字段列表? 表? ? ? 條件? ? ? ? 分組? ? ? ? 組內(nèi)條件? ? ? 排序? ? 分頁

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>-- 分組查詢(組內(nèi)過濾)

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,閉區(qū)間

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>-- 嵌套子查詢(查詢訂單數(shù)量>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,訂單數(shù)量,最貴訂單，最便宜訂單，平均訂單價(jià)格 where 訂單數(shù)量>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 ;