MySql基礎使用

標籤：restrict   記錄   表串連   左串連   注意   outer   簡單的   _for   str   

1. 修改密碼

mysqladmin -u root -p password 123456   //修改密碼為123456

2. 進入mysql

mysql -u root -p

3. 顯示資料庫

show databases

4. 建立school資料庫

create database school;

5. 使用資料庫

use 資料庫名

6. 建立表格

CREATE TABLE demo(    ->id int PRIMARY KEY AUTO_INCREMENT,    ->name char(20) NOT NULL,    ->age int);

7. 顯示表格

show tables;

8. 插入資料

insert into demo (id,name,age) values(1, "mike", 19);

9. 查看插入的內容

select * from demo;

10. 顯示表格資訊

//方法一desc demo;//方法二show columns from demo;

11. 增加列操作

alter table demo add hobby char(20);  //加的列在表的最後面alter table demo add qq int after age;  //加在某列的前面alter table demo add mail char(30) first;  //加在列的最前面

12. 刪除列操作

alter table demo drop hobby;

13. 修改列

alter table 表名 modify 列名 新類型 新參數  //修改列類型alter table 表名 change 舊列名 新列名 新類型 新參數  //修改列名和列類型

14. 刪除資料庫

drop database 資料庫名

15. 查看資料庫儲存引擎

show engines;//support列為YES表示引擎可用，DEFAULT表示資料庫當前預設的引擎

16. 改變列的順序

alter table demo modify id int first;  //修改順序為第一位alter table demo modify mail char after hobby;  //移動到其他列的後面

17. 刪除

delete from demo where name="mike";

18. 修改

update demo set id=1 where name="mike";

19. 尋找

select * from demo;  //*代表所有列select * from demo where id=1;  //查詢單行select * from demo where id>1;  //多行查詢select name,mail from demo where id>=1;  //指定列尋找

20. 投影運算和廣義運算
取出部分列叫投影運算
取出部分列作運算叫廣義運算

select id,name,age+1 from demo

21. 比較子
between and 在某範圍內

between 100 and 500

int<4,11> 在某集合內
and
not in

where <price between 100 and 300> or <price between 400 and 500>;select * from demo where 0;select * from demo where 1;

22. null查詢

select *from demo where qq is null;select *from demo where qq is not null;

null的特性不利於最佳化，一般設定為not null

23. group分組與統計函數

//count求行select count(*) from demo;//avg求平均值select avg(age) from demo;//sum求和select sum(age) from demo;//min求最大//max求最小//groupselect max(price),cat_id from goods group by cat_id;

24. as取別名

select id,name,age-id as z from demo;

25. having

select id,name,qq-age as sheng from demo having sheng>200;

26. 約束
a>. 約束保證資料的完整性和一致性。
b>. 約束分為表級約束和資料行層級條件約束。
c>. 約束類型包括：

NOT NULL（非空約束）PRIMARY KEY（主鍵約束）UNIQUE KEY（唯一約束）DEFAULT（預設約束）PROEIGN KEY（外鍵約束）

外鍵約束
保持資料一致性，完整性。
實現一對一或一對多的約束。

外鍵約束的要求
a>. 父表和子表必須使用相同的儲存引擎，而且禁止使用暫存資料表。
b>. 資料表的儲存引擎只能為InnoDB。
c>. 外鍵列和參照列必須具有相似的資料類型。其中數位長度或是否有符號位必須相同，而字元的長度則可以不同。
d>. 外鍵列和參照列必須建立索引。如果外鍵列不存在索引的話，mysql將自動建立索引。

編輯資料表的預設儲存引擎
MySQL設定檔：
default-storage-engine=INNODB

外鍵約束的參照操作
a>. CASCADE：從父表刪除或更新且自動刪除或更新子表中匹配的行
b>. SET NULL：從父表刪除或更新行，並設定子表中的外鍵列為NULL。如果使用該選項，必須保證子表列沒有指定NOT NULL。
c>. RESTRICT：拒絕對父表的刪除或更新操作。
d>. NO ACTION：標準SQL關鍵字，在mysql中與RESTRICT相同。

表級約束和資料行層級條件約束
對一個資料列建立的約束，稱為資料行層級條件約束。
對多個資料列建立的約束，稱為表級約束。

資料行層級條件約束既可以在列定義時聲明，也可以在列定義後聲明。
表級約束只能在列定義後聲明。

添加主鍵約束

alter table 表名 add primary key (列名);

添加外鍵約束

alter table 表名 add foreign key (外鍵列) references 參照表 (參照列);

添加唯一約束

alter table 表名 add unique key (列名);

添加預設約束

alter table 表名 set default 預設值;

刪除預設約束

alter table 表明 drop default;

查索引與查約束

SHOW INDEX  //查索引SHOW INDEXES  //查約束

刪除主鍵約束

alter table 表名 drop primary key;

刪除唯一約束

alter table 表名 drop index 列名;  //注意刪除的是約束不是欄位

刪除外鍵約束

alter table 表名 foreign key 外鍵別名(constraint);

27. 修改列定義

alter table 表名 modify 列名 資料類型 其他屬性alter table 表名 change 列名 新列名 資料類型 其他屬性

28. 表重新命名的兩種方法

alter table 表名 rename 新表名rename table 表名 to 新表名

29. 記錄操作

增

插入記錄

insert 表名 列名稱（可省略） values|value (可以一次插入多條記錄)insert 表名 set 值 （只能一次插入單條記錄）  //與第一種的區別在於，此方法可以使用子查詢（SubQuery)insert 表名 select  //此方法可以將查詢結果插入到指定資料表

刪

刪除記錄

delete from 表名 where 條件  //若省略where，所有記錄都將刪除

改

更新記錄（單表更新）

update 表名 set 更新的列值 where 條件  //若省略where，所有記錄都將更新

查

尋找記錄

select version();  //查看mysql版本select now();  //查看目前時間select id,name from info;select name,age from info  where id>=2;select info.id,info.name from info;  //有多張表時可以清楚使用

30. as別名

select id as userId, username as uname from info;

31. where條件運算式
< > = % * / 函數等待

32. 查詢結果分組（GROUP BY）

select sex from info group by sex;  //通過性別分組

分組條件

select age from info having age > 16;

對結果進行排序（ORDER BY）

select * from info order by id desc;  //id號降序排列

限制結果返回的數量（LIMIT）

select * fom info LIMIT 2;

insert test(name) select name from info where age>30

33. 子查詢與串連

set names gbk;  //以gbk編碼顯示用戶端

子查詢
指出現在其他SQL語句內的select子句。

子查詢只嵌套在查詢內部，且必須始終出現在圓括弧內。

子查詢可以包含多個關鍵字或條件，如：DISTINCT, GROUP BY, LIMIT，函數等。

子查詢的外層查詢可以是：SELECT, INSERT, UPDATE, SET或DO。

子查詢可以返回標題，一行，一列或子查詢。

使用比較子的子查詢
=, >, <, >=, <=, <>, !=, <=>

文法結構

operand comparison_operator subquery;

AVG()未平均值

select ROUND(AVG(price), 2) FROM goods;  //平均值留兩位小數，四捨五入

使用[NOT] IN的子查詢
文法結構：
operand comparison operator [NOT] IN (subquery) = ANY運算子與IN等效。
!=ALL 或<>ALL運算子與NOT IN等效。

使用[NOT] EXISTS的子查詢（用的比較少）
若子查詢返回任何行，EXISTS將返回TRUE；否則返回FALSE。

34. 將查詢結果寫入資料表

INSERT [INTO] 表名 [（列名，...)] SELECT ...

35. 多表更新
連線類型：
INNER JOIN 內串連
在mysql中，JOIN,CROSS JOIN和INNER JOIN是等價的。
LEFT[OUTER] JOIN，左外串連
RIGHT[OUTER] JOIN，右外串連

UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate=cate_name SET goods_cate=cate_id;

INNER JOIN是內串連
是更新表內部與參照表中的對照。
ON是條件
SET是修改成什麼樣的值 。

36. CREATE....SELECT
建立資料表同時將查詢結果寫入到資料表。

1. 建立
2. 寫入
3. 更新
   為了一步到位
   可以在建立時寫入資訊

```sql
CREATE TABLE tdb_brands
(
brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
brand_name VARCHAR(40) NOT NULL
)

SELECT brand_name FROM tdb_goods GROUP BY brand_name;

UPDATE tdb_brands INNER JOIN tdb_goods ON tdb_brands.brand_name=tdb_goods.brand_name set tdb_brands.brand_name=tdb_goods.brand_id;
``
在聯合修改後有nma->id類型和名字要跟著改變一樣
用alter change

alter table tdb_goods change good_cate cate_id SMALLINT UNSIGNED NOT NULL,change brand_name brand_id smallint unsigned not null;

37. 串連
文法結構
JOIN
INNER JOIN：內連結和交叉串連等價，都是笛卡爾積運算。
CROSS JOING：交叉串連
LEFT [OUTER] JOIN 左串連：是根據最左表的記錄，在被串連右表中找出合格記錄與之匹配，找不到與左表匹配的，用null填充。
RIGHT [OUTER] JOIN 右串連：是根據是右表的記錄，在被串連左表中找出合格記錄與之串連，找不到與右表匹配的，用null填充。

串連條件
使用ON關鍵字來設定串連條件，
使用WHERE關鍵字進行結果集記錄的過濾。

LEFT JOIN：顯示左表全部和左右符合串連條件的記錄。
RIGHT JOIN：顯示左右符合串連條件的記錄和右表全部記錄。

如果使用LEFT JOIN，左表中存在一條記錄A，在右表中沒有找到相應的記錄，則在返回結果用會出現一條只有記錄A中的相應欄位內容，其他欄位都為NULL在記錄（RIGHT JOIN類似。

38. 查詢所有商品的詳細資料（通過左外串連實現）

SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g LEFT JOIN tdb_goods_cates AS c ON g.cate_id=c.cate_id LEFT JOIN tdb_goods_brands AS b ON g.brand_id=b.brand_id\G;

39. 查詢所有商品的詳細資料（通過右外串連實現）

SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g RIGHT JOIN tdb_goods_cates AS c ON g.cate_id=c.cate_id RIGHT JOIN tdb_goods_brands AS b ON g.brand_id=b.brand_id\G;

40. 多表串連

SELECT col_name1,col_name2,col_name3,col_name4,col_name5 FROM tbl_name1 AS t1 INNER JOIN tbl_name2 AS t2 ON join_condition INNER JOIN tbl_name3 AS t3 ON join_condition\G;

SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g    ->INNER JOIN tdb_goods_cate AS c ON g.cate_id=c.cate_id    ->INNER JOIN tdb_goods_brands AS b ON g.brand_id=b.brand_id\G;

串連三個表聯合顯示查詢結果，注意不同的INNER JOIN中間沒有逗號！！！沒有逗號！！！沒有逗號！！！

顯示的結果和商品表tdb_goods的記錄一樣，不過之前是通過單表的查詢來顯示，這次是通過三張表的串連來顯示。

多表的串連實際上是外鍵的逆向約束。外鍵把資料分開儲存，多表串連又把資料聯絡在一起。

41. 外串連

A LEFT JOIN B join_condition

資料表B的結果集依賴資料表A。
資料表A的結果集根據左串連條件依賴所有資料表（B表除外）

左外串連條件決定如何檢索資料表B（在沒有制定WHERE條件的情況下）
如果資料表A的某條記錄符合WHERE條件，但是在資料表B不存在符合串連條件的記錄，將產生一個所有列為空白的額外的B行。

如果使用內串連尋找的記錄在串連資料庫表中不存在，並且在WHERE子句中嘗試以下操作：col_name IS NULL時，如果col_name被定義為NOT NULL，mysql將在找到符合連執著條件的記錄後停止搜尋更多的行。

42. 自身串連
同一個資料表對其自身進行串連。

SELECT s.type_id, s.type_name, p.type.name AS parent_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p    -> ON s.parent_id=p.type_id;

注意自身串連一定要用別名，其中s代表son子表，p代表parent父表，同時為了顯示出一樣商品不存在父類的這種情況要用LEFT JOIN而不能用INNER JOIN。

尋找所有分類及其父類

SELECT s.type_id, s.type_name, p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p ON s.parent_id=p.type_id;

尋找所有分類及其子類

SELECT p.type_id,p.type_name FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id=p.type_id;

尋找所有分類及其子類的數目

SELECT p.type_id, p.type_name,count(s.type_name) AS children_count FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id=p.type_id GROUP BY p.type_name ORDER BY p.type_id;

43. 多表刪除

DELETE t1 FROM tdb_goods as t1 LEFT JOIN .....

這兒臨時的t1指的並不是原表，而是原表的副本t1，在與t2進行連結後得到的新的t1.
這個臨時的t1表的資料即是要刪除的多餘的資料。

多表刪除（單表類比多表重複資料刪除資料）

DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2) AS t2 ON t1.goods_name=t2.goods_name WHERE t1.goods_id > t2.goods_id;

44. 運算子與函數

字元函數

%：任意個字元
_：任意一個字元

數值運算函數

日期時間函數

SELECT DATE_FORMAT(‘2017-7-15‘, ‘%m/%d/%Y‘);  //07/15/2017

資訊函數

彙總函式

加密函數

MD5()：資訊摘要演算法，為以後的web頁面做準備，盡量使用MD5()

SELECT MDT(‘admin‘);

PASSWORD()：密碼演算法，通過PASSWORD()修改（重要用於MYSQL資料庫）目前使用者和其他和其他使用者的密碼，修改用戶端自己的密碼。

SET PASSWORD=PASSWORD(‘dimitar‘);  //把密碼修改成dimitar

45. 自訂函數
使用者自訂函數（user-defined function, UDF)是一種對MySQL擴充的途徑，其演算法與內建函數相同。
自訂函數的兩個必要條件：參數（零個或多個） 傳回值（只能有一個）
函數可以返回任意類型的值，同樣可以接收這些類型的參數。

建立自訂函數：

CREATE FUNCTION 函數名() RETURNS VARCHAR(30) RETURNSET NAMES gbk;USE test;SELECT NOW();SELECT DATA_FORMAT(NOW(), ‘%Y年%m月%d日 %H點：%i分：%s秒‘);//以上函數可以寫成函數方便以後調用：CREATE FUNCTION date() RETURNS VARCHAR(30)RETURN DATE_FORMAT(NOW(),‘%Y年%m月%d日 %H點：%i分：%s秒);SELECT date();  //調用

關於函數體
a>. 函數體由合法的SQL語句構成。
b>. 函數體可以是簡單的SELECT或INSERT語句。
c>. 函數體如果為複合結構則使用BEGIN...END語句。
d>. 複合結構可以包含聲明，迴圈，控制結構。

帶參數的自訂函數：
CREATE FUNCTION f1(num1 )

wating....

MySql基礎使用