• MySQL基礎(chǔ)一
  上一節(jié)主要了解了MySQL數(shù)據(jù)庫(kù)的相關(guān)命令和數(shù)據(jù)庫(kù)中存儲(chǔ)的數(shù)據(jù)類型, 這一節(jié)主要是MySQL中對(duì)于表的操作。
  數(shù)據(jù)表(或稱表): 是數(shù)據(jù)庫(kù)的重要儲(chǔ)存部分, 是一張二維表格由行和列組成,行稱呼為記錄,列稱之為字段。

在創(chuàng)建表之前 需要打開(kāi)數(shù)據(jù)庫(kù),使用USE db_name;命令打開(kāi)數(shù)據(jù)庫(kù)裤纹。這樣就能在db_name下創(chuàng)建表了枢纠。

創(chuàng)建表的命令:

CREATE TABLE [IF NOT EXISTS] t_name(
column_name data_type,
...
);

查看當(dāng)前打開(kāi)的數(shù)據(jù)庫(kù) SELECT DATABASE();

我們先創(chuàng)建一張工資表,用來(lái)練手:

CREATE TABLE t_salary(
username VARCHAR(20),
age TINYINT UNSIGNED,
salary FLOAT(8,2) UNSIGNED
);

解釋一下上面的意思 CREATE TABLE 命令為創(chuàng)建表格, UNSIGNED為無(wú)符號(hào)位集绰，即正數(shù)。FLOAT(8,2)表示8位數(shù)保留小數(shù)點(diǎn)后2位赊舶。

ps: 今天聽(tīng)后臺(tái)說(shuō)起淤刃，如果是涉及到錢的話 可以設(shè)置類型為int 然后單位為分。具體的咱也沒(méi)有學(xué)習(xí)到就先不談了~

那么既然創(chuàng)建好了數(shù)據(jù)表, 如何查看數(shù)據(jù)表呢?

查看數(shù)據(jù)表命令
SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr]
查看數(shù)據(jù)表的命令是否影響當(dāng)前使用的數(shù)據(jù)庫(kù)呢吱型，可以使用
SELECT DATABASE();查看當(dāng)前使用的數(shù)據(jù)庫(kù)逸贾。

表有了 怎么查看數(shù)據(jù)表中的各個(gè)字段呢，也就是表結(jié)構(gòu)如何查看津滞。
查看數(shù)據(jù)表結(jié)構(gòu):
SHOW COLUMNS FROM table_name;

工資表結(jié)構(gòu)

好了,既然表已經(jīng)有了接下來(lái)就該如何存儲(chǔ)數(shù)據(jù)了铝侵， 在mysql中可以使用insert 插入一條數(shù)據(jù)(記錄)。
插入命令:
INSERT [INTO] table_name [(column1, column2...)] VALUES(value1, value2, ...)
從命令中可以看出 字段可以省略,那如果字段省略之后, 值一定要跟表中的數(shù)據(jù)一一對(duì)應(yīng),否則會(huì)發(fā)生錯(cuò)誤触徐。
插入數(shù)據(jù)(記錄)之后,可以使用SELECT命令進(jìn)行查找數(shù)據(jù)咪鲜。SELECT 命令在數(shù)據(jù)庫(kù)的使用中 非常普遍。
簡(jiǎn)單查找命令
SELECT expr,... FROM table_name;

表約束

空值與非空值

• NULL, 字段值可以為空
• NOT NULL撞鹉， 字段值禁止為空
  該約束不能同時(shí)出現(xiàn)在同一字段中, 字段默認(rèn)為NULL 我們可以使用一個(gè)小例子來(lái)進(jìn)行測(cè)試

1. 創(chuàng)建t_null表

CREATE TABLE IF NOT EXISTS t_null(
username VARCHAR(20) NOT NULL,
age TINYINT UNSIGNED NULL
);

2. 插入正常數(shù)據(jù)

INSERT t_null VALUES('Tom', NULL);

正常

3. 插入非法數(shù)據(jù)

INSERT t_null VALUES(NULL, 12);

錯(cuò)誤

自增約束

AUTOINCREMENT

• 自動(dòng)編號(hào), 并且必須與主鍵組合使用
• 默認(rèn)情況下 起始為1, 每次的增量為1

主鍵

PRIMARY KEY

• 每張表只能存一個(gè)主鍵
• 主鍵保證記錄的唯一性
• 主鍵自動(dòng)為NOT NULL
• 自增約束必須組合主鍵使用, 但是主鍵不一定要組合自增使用

可以使用例子證明:

1. 創(chuàng)建表格 (主鍵自增)

CREATE TABLE IF NOT EXISTS t_primary(
t_id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) NOT NULL
);

2. 插入數(shù)據(jù)

INSERT t_primary (username) VALUES('c');
INSERT t_primary (username) VALUES('oc');

返回結(jié)果

3. 查看數(shù)據(jù)

SELECT * FROM t_primary;

查詢所有字段結(jié)果

ps: 這里的*號(hào)為過(guò)濾的字段 如果為* 則表示顯示全部的字段

顯示10的那里是因?yàn)槲覟橹麈It_id賦值為10, 因?yàn)槭亲栽鏊韵旅鏋?1 疟丙， 12颖侄， 13..

唯一約束

UNQUE KEY

• 唯一約束
• 唯一約束可以保證記錄的唯一性
• 唯一約束的字段可以為空值(NULL)
• 每張數(shù)據(jù)表可以存在多個(gè)唯一約束

還是以一個(gè)例子來(lái)解釋:

1. 創(chuàng)建表格

CREATE TABLE IF NOT EXISTS t_unique(
t_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
age TINYINT UNSIGNED UNQUE KEY
);

2. 插入數(shù)據(jù)

INSERT t_unique (username, age) VALUES('tom', 20);
INSERT t_unique (username, age) VALUES('tom', 20);

3. 錯(cuò)誤

重復(fù)錯(cuò)誤

默認(rèn)約束

DEFAULT

• 當(dāng)插入記錄時(shí)，如果沒(méi)有明確賦值,則自動(dòng)賦予默認(rèn)值
  例子:

1. 創(chuàng)建表格

CREATE TABLE IF NOT EXISTS t_default(
t_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCRMENT,
username VARCHAR(20) NOT NULL,
sex ENUM('1', '2', '3') DEFAULT '3'
);

2. 插入數(shù)據(jù)

INSERT t_default (username) values ('tom');

3. 查看表中數(shù)據(jù)

SELECT * FROM t_default;

4. 數(shù)據(jù)返回結(jié)果

數(shù)據(jù)返回結(jié)果

外鍵約束

• 父表和字表必須使用相同的存儲(chǔ)引擎享郊，而且禁止使用臨時(shí)表
• 數(shù)據(jù)表的存儲(chǔ)引擎只能是INNODB
• 外鍵列和參照列必須具有相同的數(shù)據(jù)類型览祖，其中數(shù)字類型的長(zhǎng)度或者是否有符號(hào)位必須相同，而字符的長(zhǎng)度則不同.
• 外鍵列和參照列必須創(chuàng)建索引炊琉，如果外鍵列不存在索引的話,MySQL將自動(dòng)創(chuàng)建.
  關(guān)于最后一條, 如果參照列沒(méi)有所以MySQL不會(huì)創(chuàng)建外鍵列的索引
  例子:

1. 創(chuàng)建城市表

CREATE TABLE IF NOT EXISTS t_province(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
p_name VARCAHR(20) NOT NULL
);

2. 創(chuàng)建用戶表

CREATE TABLE IF NOT EXISTS t_users(
id SMALLINT UNSIGNED PRIMARY KEY AUT_INCREMENT,
username VARCHAR(20) NOT NULL,
p_id SMALLINT UNSIGN,
FOREIGN KEY (p_id) REFERENCES t_province (id)
);

如外鍵開(kāi)頭所說(shuō) 如果參照列創(chuàng)建了索引,那么 MySQL會(huì)自動(dòng)為外鍵列創(chuàng)建索引展蒂。雖然我們沒(méi)有明顯的為t_province表的id創(chuàng)建索引,但是id為主鍵, 主鍵會(huì)默認(rèn)創(chuàng)建索引。所以t_users表中的p_id字段也會(huì)有索引苔咪。那么如何證明呢锰悼。 可以使用SHOW INDEXES FROM table_name命令查看表中的索引, 當(dāng)然為了好看還可以使用 \G 讓索引成網(wǎng)格狀顯示。
查看t_province表中的索引

SHOW INDEXES FROM t_province\G;

t_province中的索引

SHOW INDEXES FROM t_users\G;

t_users中的索引

外鍵約束的參照操作

• CASCADE: 從父表刪除或者更新 時(shí)自動(dòng)刪除或者更新子表中匹配的行
• SET NULL: 從父表刪除或者更新行,并設(shè)置字表中的外鍵列為NULL团赏。如果使用該選項(xiàng), 必須保證字表列沒(méi)有指定為NOT NULL
• RESTRICT: 拒絕對(duì)父表的刪除或更新操作
• NO ACTION : 標(biāo)準(zhǔn)的SQL 的關(guān)鍵字, 在MySQL中與RESTRICFT相同

以上參照操作都是指 在創(chuàng)建好外鍵列的時(shí)候, 在更新表的時(shí)候, 字表是否也進(jìn)行相應(yīng)的操作
例子:

1. 創(chuàng)建車庫(kù)表

CREATE TABLE IF NOT EXISTS t_garage(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_CREMENT,
garage_name VARCHAR(20) NOT NULL
);

2. 創(chuàng)建車表

CREATE TABLE IF NOT EXISTS t_cars(
id SMALLINT UNSINGED PRIMARY KEY AUTO_CREMENT,
car_name VARCHAR(20) NOT NULL,
garage_id SMALLINT UNSIGNED,
FOREIGN KEY (garage_id) REFERENCES t_garage (id) ON DELETE CASCADE
);

因?yàn)檐噹?kù)表為父表,車表為字表 所以現(xiàn)在要父表中插入數(shù)據(jù),再在子表中插入數(shù)據(jù).

3. t_garage插入數(shù)據(jù)

INSERT t_garage (garage_name) VALUES('A');
INSERT t_garage (garage_name) VALUES('B');
INSERT t_garage (garage_name) VALUES('C');

使用SELECT * FROM t_garage;查看表中數(shù)據(jù)

t_garage表中數(shù)據(jù)

4. t_cars插入數(shù)據(jù)

INSERT t_cars (car_name, garage_id) VALUES ('紅旗', 3);
INSERT t_cars (car_name, garage_id) VALUES ('models', 1);
INSERT t_cars (car_name, garage_id) VALUES ('吉利', 3);

使用SELECT * FROM t_cars;查看表中數(shù)據(jù)

t_cars中數(shù)據(jù)

5. 刪除父表中的記錄

DELETE FROM t_garage where id = 3;

查看t_garage表中的數(shù)據(jù) SELECT * FROM t_garage;

t_garage中的數(shù)據(jù)

t_cars中的數(shù)據(jù)

由此可以看出 只要是父表中刪除了數(shù)據(jù),字表中的記錄也會(huì)被刪除箕般。
由于在實(shí)際項(xiàng)目中不常使用參照操作，所以也不過(guò)多進(jìn)行了~

表級(jí)約束與列級(jí)約束

• 對(duì)于一個(gè)數(shù)據(jù)列建立的約束,稱之為列級(jí)約束
• 對(duì)多個(gè)數(shù)據(jù)列建立的約束, 稱之為表級(jí)約束
• 列級(jí)約束可以在列定義時(shí)聲明,也可以在列定義后聲明
• 表級(jí)約束只能在列定義后聲明

修改數(shù)據(jù)表

• 添加單列

ALTER TABLE table_name ADD [COLUMN] column_name column_definition [FIRST | AFTER column_name]

• 添加多列

ALTER TABLE table_name ADD [COLUMN] (column_name, column_definition, ...)

添加多列的時(shí)候不能指定位置關(guān)系, 只能在字段的后方馆里。

• 刪除列

ALTER TABLE table_name DROP [COLUMN] column_name

• 刪除多列

ALTER TABLE t_name DROP [COLUMN] c_name, DROP c_name

用一個(gè)例子演示一下:

1. 創(chuàng)建表格

CREATE TABLE IF NOT EXISTS t_action(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL
);

2. 添加salary字段

ALTER TABLE t_action ADD salary FLOAT(8,2) UNSIGNED;

3. 查看表結(jié)構(gòu)

SHOW CLOUMNS FROM t_action;

表結(jié)構(gòu)

3. 添加age字段

ALTER TABLE t_action ADD age SMALLINT UNSIGNED AFTER username;

4. 查看表結(jié)構(gòu)

SHOW COLUMNS FROM t_action;

表結(jié)構(gòu)

可以看到使用AFTER把a(bǔ)ge字段添加到了username字段的后方.

5. 刪除salary字段

ALTER TABLE t_action DROP salary;

6. 查看表結(jié)構(gòu)

SHOW COLUMNS FROM t_action;

表結(jié)構(gòu)

7. 刪除username 添加 area_name

ALTER TABLE t_action DROP username, ADD area_name VARCHAR(20) NOT NULL;

8. 查看表結(jié)構(gòu)

SHOW COLUMNS FROM t_action;

表結(jié)構(gòu)

由此可見(jiàn)如果想進(jìn)行多個(gè)字段的添加和刪除只需要用,隔開(kāi)即可隘世。

• 添加主鍵

ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]] PRIMARY KEY [index_type] (index_col_name, ...)

用一個(gè)例子說(shuō)明:

1. 創(chuàng)建一個(gè)t_user_test表

CREATE TABLE IF NOT EXISTS t_user_test(
id SMALLINT UNSIGNED,
username VARCHAR(20) NOT NULL
);

2. 為id添加主鍵約束

ALTER TABLE t_user_test ADD CONSTRAINT PK_user_test PRIMARY KEY (id);

CONSTRAINT 為設(shè)置別名，目前還不知道

• 添加唯一約束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE KEY [INDEX|KEY] [index_name] [index_type] (index_col_name);

• 添加外鍵約束

ALTER TABLE tb_name ADD [CONSTRINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) reference_definition

• 添加/刪除默認(rèn)約束

ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal| DROP DEFAULET}

• 刪除主鍵約束

ALTER TABLE tbl_name DROP PRIMARY KEY;

• 刪除唯一約束

ALTER TABLE tbl_name DROP {INDEX | KEY} index_name;

• 刪除外鍵約束

ALTER TABLE tbl_name  DROP FOREIGN KEY fk_symbol;

• 修改列定義

ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST|AFTER col_name]

修改列定義需要注意的是 如果修改的數(shù)據(jù)類型是由大往小了更改 那么會(huì)造成部分?jǐn)?shù)據(jù)丟失鸠踪。

• 修改列名稱

ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]

• 數(shù)據(jù)表更名
  方法一

ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name

方法二

RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] ...

方法二可以更換多個(gè)表的名稱

插入 insert

第一種方法

INSERT [INTO] tbl_name [(col_name1, ...)] {VALUES|VALUE} ({expr | DEFAULT}, ...), (...),...

舉個(gè)??:

1. 創(chuàng)建't_user'表

CREATE TABLE IF NOT EXISTS t_user(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password VARCHAR(32) NOT NULL,
age SMALLINT UNSIGNED NOT NULL DEFAULT 10,
sex BOOLEAN
);

ps: 這里聲明age字段時(shí)如果沒(méi)有設(shè)置NOT NULL設(shè)置默認(rèn)約束的時(shí)候 會(huì)出現(xiàn)語(yǔ)法錯(cuò)誤

2. 插入數(shù)據(jù)

• 簡(jiǎn)單插入

INSERT t_user VALUES(NULL, 'Tom', '123', 13, 1);

插入結(jié)果

• 插入(函數(shù))

INSERT t_user VALUES (DEFAULT,'Join', md5('123456'), 14, 2);

插入結(jié)果

• 插入(表達(dá)式)

INSERT t_user VALUES (DEFAULT, 'left', '123456', 5*6-5, 1);

插入結(jié)果

• 插入(多條)

INSERT t_user VALUES (DEFAULT,'right', md5('123456'), 20, 1), (NULL, 'big', '123456', 21, 3);

插入結(jié)果

由此可見(jiàn) mysql即支持表達(dá)式還支持函數(shù), 對(duì)于自增字段可以使用'DEFAULT'或者NULL默認(rèn)值丙者。對(duì)于多條插入可以以','隔開(kāi)。

第二種方法

INSERT [INTO] tbl_name  SET col_name={expr| DEFAULT }, ...

第二種方法較第一種方法來(lái)說(shuō),此方法可以使用子查詢, 但是只能插入一條數(shù)據(jù)营密。

• 插入t_user表數(shù)據(jù)

INSERT t_user SET  username='haha',password='123456';

因?yàn)閕d 和 age 都有默認(rèn)值, sex允許為空 所以可以不賦值械媒。

插入結(jié)果

第三種方法

INSERT [INTO] tbl_name [(column_name, ..)] SELECT ...

該方法可以把查詢出的數(shù)據(jù),插入到數(shù)據(jù)表中.

更新

• 更新記錄(單表)
  UPDATE [LOW_PRIORITY] [IGNORE] tbl_refrenece SET col_name1={expr1|DEFAULT} [, col_name2={expr2}|DEFAULT]... [WHERE where_condition]

UPDATE t_user SET age = age+10, sex = 0;

刪除

• 刪除記錄(單表)

DELETE FROM t_user where id = 6;

查詢

• where 查詢表達(dá)式

• GROUP BY
  查詢結(jié)果分組

[GROUP BY {col_name|position} [ASC|DESC], ...]

ASC為升序 為默認(rèn)
DESC為降序
??

1. 創(chuàng)建t_users表格

CREATE TABLE IF NOT EXISTS t_users(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password VARCHAR(40) NOT NULL,
age TINYINT UNSIGNED NOT NULL DEFAULT 10,
sex BOOLEAN
);

users表結(jié)構(gòu)

2. 插入數(shù)據(jù)

INSERT t_users VALUES (DEFAULT, 'tom', '123456', 20,1), (DEFAULT, 'alex', '123456', 20, 1), (DEFAULT, 'tornado', '123', 19, 1), (DEFAULT, 'fdfd', '123', 10, 0);

插入結(jié)果

3. 以sex分組

SELECT sex FROM t_users GROUP BY sex;

分組結(jié)果

• HAVING分組條件
  分組可以對(duì)全部記錄做分組 也可以指定部分記錄作為分組。
  ??

1. 插入基礎(chǔ)數(shù)據(jù)

INSERT t_users VALUES (DEFAULT,'pp', '123456', 10, 0),
 (DEFAULT,'dd', '123456', 26, 0);

2. group by

SELECT sex,age FROM t_users GROUP BY sex,age having age >10;

• order by排序

[ORDER BY {col_name|expr|position} [ASCC|DESC], ...]

??

1. 按照id降序排列

SELECT *  FROM t_users ORDER BY id DESC;

排序結(jié)果

2. 按照age 降序如果相同按照id升序

SELECT * FROM t_users ORDER BY age DESC , id;

排序結(jié)果

限制查詢LIMIT

[LIMIT{[offset,] row_cout|row_cout OFFSET offset}]

還是使用t_users來(lái)做例子

1. 查詢兩條數(shù)據(jù)

SELECT * FROM t_users LIMIT 2;

2. 從第三條數(shù)據(jù)開(kāi)始 查詢兩條

SELECT * FROM t_users LIMIT 2,2;

因?yàn)閿?shù)據(jù)庫(kù)中記錄的索引從0開(kāi)始 所以索引為2的記錄是第三條數(shù)據(jù)

3. limit只按照查詢結(jié)果來(lái)計(jì)算索引 不管id為大還是小
   =========================================
   暫存有空做修改