?MySql 學(xué)習(xí)之路-基礎(chǔ)

第一時(shí)間關(guān)注技術(shù)干貨！

一.基礎(chǔ)

數(shù)據(jù)庫操作

Show?databases；顯示所有的數(shù)據(jù)庫
Show?tables；?顯示所有的數(shù)據(jù)庫表
Use?databasename；切換數(shù)據(jù)庫
Desc?tablename；?顯示表中所有的字段信息

表1：websites

+----+--------------+---------------------------+-------+---------+
|?id?|?name?????????|?url???????????????????????|?alexa?|?country?|
+----+--------------+---------------------------+-------+---------+
|?1??|?Google???????|?https://www.google.cm/????|?1?????|?USA?????|
|?2??|?淘寶??????????|?https://www.taobao.com/???|?13????|?CN??????|
|?3??|?菜鳥教程??????|?http://www.runoob.com/????|?4689??|?CN??????|
|?4??|?微博??????????|?http://weibo.com/?????????|?20????|?CN??????|
|?5??|?Facebook?????|?https://www.facebook.com/?|?3?????|?USA?????|
|?7??|?stackoverflow?|?http://stackoverflow.com/?|???0?|?IND?????|
+----+---------------+---------------------------+-------+---------+

表2：access_log

+-----+---------+-------+------------+
|?aid?|?site_id?|?count?|?date???????|
+-----+---------+-------+------------+
|???1?|???????1?|????45?|?2016-05-10?|
|???2?|???????3?|???100?|?2016-05-13?|
|???3?|???????1?|???230?|?2016-05-14?|
|???4?|???????2?|????10?|?2016-05-14?|
|???5?|???????5?|???205?|?2016-05-14?|
|???6?|???????4?|????13?|?2016-05-15?|
|???7?|???????3?|???220?|?2016-05-15?|
|???8?|???????5?|???545?|?2016-05-16?|
|???9?|???????3?|???201?|?2016-05-17?|
+-----+---------+-------+------------+

1.LIKE 操作符

1?select?*?from?websites?where?url?like?'https%';--?url包含https的數(shù)據(jù)
2?select?*?from?websites?where?name?like?'G%';?--?name以G開頭
3?select?*?from?websites?where?url?like?'%h%';--?url包含h的字段數(shù)據(jù)
4?select?*?from?websites?where?name?like?'_o%';--?name中o在第二個(gè)字符的數(shù)據(jù)

2.ORDER BY 關(guān)鍵字

1?select?name,alexa?from?websites?
2?order?by?alexa?desc;?--?默認(rèn)時(shí)升序?desc?表示降序
3?????
4?select?*?from?websites
5?order?by?country,?alexa;?--?多列排序時(shí)，先排country?在排alexa

3.INSERT INTO

1?insert?into?websites?
2?values?(6,'百度','http://www.baidu.com',22,'ZG');--?可以省略列，但要所有的值都插入
3?????
4?insert?into?websites?(id?,name,url,alexa,country)
5?values?('7','網(wǎng)易','www.wangyi.com','25','ZG');--?可以指定插入哪一列

4.UPDATE

update?websites?
set?url?=?'http://www.wangyi.com'
where?id?=?7;

update?websites
set?alexa?=?5000,?country?=?'USA'
where?id?=?3;

5.DISTINCT

SELECT?DISTINCT?country?FROM?Websites;

6.DELETE

--?刪除一行數(shù)據(jù)
SELECT?FROM?WEBSITES?
WHERE?ID?=?7;
--?刪除整個(gè)表，但是表的結(jié)構(gòu)仍存在
SELECT?FROM?WEBSITES;

7.AND, OR ,IN

1?SELECT?*?FROM?WEBSITES?
2?WHERE?ID?BETWEEN?1?AND?3;
3?
4?SELECT?*?FROM?WEBSITES
5?WHERE?ID?=?1?OR?ID?=3;--?篩選id為1和3?的數(shù)據(jù)
6?
7?SELECT?*?FROM?WEBSITES
8?WHERE?ID?IN?(1,3);