1、創(chuàng)建數(shù)據(jù)庫(kù)

mysql>?CREATE DATABASE testdb DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

2透典、創(chuàng)建數(shù)據(jù)庫(kù)專屬用戶

mysql>grant all privileges on testdb.* to test@'%' identified by '123156';

3、創(chuàng)建一個(gè)表

mysql> create table test(

? ? -> id int,

? ? -> name varchar(100),

? ? -> age int,

? ? -> create_time timestamp);

4泻骤、插入數(shù)據(jù)

mysql>insert into?? test(id,name,age,create_time)? values(1,'liuzd',20,now());

5围肥、查詢數(shù)據(jù)

mysql>select * from? test;//全表查詢

mysql>select? id,name? from test;//指定列查詢

mysql>select? *? from test? where age>10;//條件查詢，where關(guān)鍵字婆芦，后方跟條件怕磨，結(jié)果為true的顯示

mysql>select? * from? test? where age>10? and? name='liuzd'; //多條件查詢,查詢年齡大于10且名字叫l(wèi)iuzd的

mysql>select? * from? test where age>10 or name='liuzd';//查詢年齡大于10或姓名為liuzd的

mysql>select * from? test where?? name like? '%zd%' ;//查詢所有姓名包含zd的數(shù)據(jù)，%代表任意數(shù)量任意字符

mysql>select *? from test where name like? '_iu%';//查詢姓名第二三位是iu的所有數(shù)據(jù)消约，_是占位符肠鲫，代表任意單個(gè)字符

mysql>select? * from test? order by age;//按照age對(duì)查詢結(jié)果排序，默認(rèn)是asc或粮，升序

mysql>select * from? test? order by age desc;//按照age對(duì)查詢結(jié)果降序排列

mysql>select * from test limit 1;//查詢結(jié)果只顯示1條

mysql>select * from test limit 5,10;//分頁(yè)查詢常用导饲，查詢結(jié)果從第五條開(kāi)始，顯示10條

mysql>select count(1) from test where age>10;//查詢年齡大于10的數(shù)據(jù)有幾條

mysql>select count(1)? c,name from? test group by? name//查看每個(gè)名字重復(fù)的次數(shù),c是別名

mysql>select? * from? (select count(1)? c,name from? test group by? name) t where? t.c>1//查詢出所有名字重復(fù)的姓名和重復(fù)次數(shù)

mysql>select count(0),name from test group by name having count(1) >1;//該sql和上一個(gè)sql等效

mysql>select

-> a.aid,a.aname,

-> b.bid,b.bname,b.age

-> from testa as a

-> left join testb as b on a.aid=b.bid

-> left join testc as c on b.aid=c.bid;//以testa為標(biāo)準(zhǔn)氯材，testb渣锦、testc分別匹配顯示，假設(shè)testa有10條數(shù)據(jù)氢哮，那最終查詢結(jié)果肯定是10條袋毙，testb、tsetc匹配部分不存在則為空冗尤，多余的不顯示听盖。left join對(duì)應(yīng)的right join，left join只需要調(diào)換表的順序即可直接實(shí)現(xiàn)right join裂七，即a left join b等價(jià)于b rigth? join a

mysql>select

->a.aid,a.aname,

->b.bid,b.bname,b.age

->from testa as a

->inner join testb as b on a.aid=b.bid;//用testb匹配testa皆看，如果testb中不存在testa的匹配，則不顯示該數(shù)據(jù)碍讯，類似于where的關(guān)聯(lián)查詢悬蔽，inner? join等價(jià)于join

mysql>from

->(select '2018-05-12' as day,aid,aname from testa

->union

->select '2018-05-11' as day,aid,aname? from testa)? a

->left join testc as c

->on a.day=DATE_FORMAT(c.cretime, '%Y-%m-%d')

->and a.aid=c.cid

->where c.cid is not null;//union用來(lái)把查詢結(jié)果合并，在這里是為了人為的把維度數(shù)據(jù)加個(gè)時(shí)間維度捉兴，然后再去左連接數(shù)據(jù)表蝎困，得到時(shí)間維度的數(shù)據(jù)情況，然后根據(jù)需要過(guò)濾掉不合條件的數(shù)據(jù)

6倍啥、更新數(shù)據(jù)

mysql>update? test set name='liuzd2' where id=1;

7禾乘、刪除數(shù)據(jù)

delete? from test where id=1;