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;