數(shù)據(jù)庫(kù)基本概念

• 數(shù)據(jù)庫(kù)(DataBase)：數(shù)據(jù)庫(kù)就是存儲(chǔ)數(shù)據(jù)的倉(cāng)庫(kù)
• 數(shù)據(jù)庫(kù)管理系統(tǒng)(DBMS)：可以獨(dú)立運(yùn)行的軟件，維護(hù)磁盤上的數(shù)據(jù)，用統(tǒng)一的方式維護(hù)不同種類的數(shù)據(jù)，做到通用且高效。
• 常見(jiàn)的DBMS:
  • mysql
  • oracle
  • db2
  • sqlserver
• 數(shù)據(jù)庫(kù)實(shí)例可以被視為數(shù)據(jù)庫(kù)管理系統(tǒng)的一個(gè)運(yùn)行實(shí)體。

數(shù)據(jù)庫(kù)與表的概念

• 在DBMS中，一個(gè)項(xiàng)目中用到的所有數(shù)據(jù)都可以以一個(gè)數(shù)據(jù)庫(kù)形式保存在一起。每種不同的數(shù)據(jù)都是以表的形式保存。

數(shù)據(jù)庫(kù)中表的概念

• 表用來(lái)保存數(shù)據(jù)，一張表是由行和列構(gòu)成
• 列被稱為字段：保存一組數(shù)據(jù)中各個(gè)屬性
• 行被稱為記錄：保存具體的一條信息。

如下是一張用戶表

如何操作數(shù)據(jù)庫(kù)系統(tǒng)

所有的DBMS都支持一種結(jié)構(gòu)化查詢語(yǔ)句進(jìn)行對(duì)應(yīng)的操作。我們以客戶端的角度與DBMS進(jìn)行連接，并發(fā)送對(duì)應(yīng)的語(yǔ)句進(jìn)行操作。

結(jié)構(gòu)化查詢語(yǔ)言：Structured Query Language簡(jiǎn)稱為SQL
SQL是有標(biāo)準(zhǔn)的：SQL92已經(jīng)SQL99，所有數(shù)據(jù)庫(kù)都支持SQL92標(biāo)準(zhǔn)

數(shù)據(jù)庫(kù)連接方式

數(shù)據(jù)庫(kù)連接有：命令行、圖形化界面軟件、JDBC連接

• 命令行形式
  
• 圖形化界面

• 使用JDBC連接以及數(shù)據(jù)庫(kù)連接池的形式
  • 在集成開(kāi)發(fā)環(huán)境中使用(ECLIPSE,IDEA)
  • 我們編寫的JAVA程序也是通過(guò)JDBC連接

SQL

SQL主要分為以下幾種類：DDL、DML、DQL、TCL、DCL，接下來(lái)我們會(huì)一一的詳細(xì)介紹這幾種語(yǔ)言

DDL

DDL語(yǔ)言詳細(xì)名稱為data definition language，是數(shù)據(jù)定義語(yǔ)言，用來(lái)進(jìn)行數(shù)據(jù)庫(kù)對(duì)象操作的。
數(shù)據(jù)庫(kù)對(duì)象包括不限于：數(shù)據(jù)庫(kù)，表，視圖，索引等

主要需要認(rèn)識(shí)的關(guān)鍵字如下：CREATE、ALTER、DROP

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

查看當(dāng)前DBMS中已有的數(shù)據(jù)庫(kù)：SHOW DATABASES

新建一個(gè)數(shù)據(jù)庫(kù)

-- 基本語(yǔ)法
CREATE DATABASE 數(shù)據(jù)庫(kù)名 [charset=字符集名稱]-- 例子1 創(chuàng)建一個(gè)名為mydb的數(shù)據(jù)庫(kù)
CREATE DATABASE mydb;-- 例子2 創(chuàng)建數(shù)據(jù)庫(kù)時(shí)指定字符集
CREATE DATABASE mydb1 CHARSET=UTF8;
CREATE DATABASE mydb2 CHARSET=GBK;

查看數(shù)據(jù)庫(kù)信息

-- 語(yǔ)法
SHOW CREATE DATABASE 數(shù)據(jù)庫(kù)名-- 例子 查看到曾經(jīng)創(chuàng)建mydb庫(kù)時(shí)的SQL，查看指定的字符集信息。
SHOW CREATE DATABASE mydb

刪除數(shù)據(jù)庫(kù)

刪庫(kù)是一個(gè)危險(xiǎn)操作，需謹(jǐn)慎，最好備份，skr

-- 語(yǔ)法
DROP DATABASE 數(shù)據(jù)庫(kù)名-- 例子 刪除數(shù)據(jù)庫(kù)mydb1
DROP DATABASE mydb1;-- 查詢時(shí)發(fā)現(xiàn)mydb1已經(jīng)被刪除了
SHOW DATABASES;

使用數(shù)據(jù)庫(kù)(切換到另一個(gè)數(shù)據(jù)庫(kù))

要保存數(shù)據(jù)都是要將數(shù)據(jù)保存在表中，而數(shù)據(jù)庫(kù)相當(dāng)于是表的集合。我們?yōu)椴煌捻?xiàng)目創(chuàng)建不同的數(shù)據(jù)庫(kù)，在DBMS上要為不同的數(shù)據(jù)庫(kù)的表進(jìn)行操作時(shí)要先切換到對(duì)應(yīng)的數(shù)據(jù)庫(kù)上再進(jìn)行相關(guān)操作。
語(yǔ)法:

-- 語(yǔ)法
USE 數(shù)據(jù)庫(kù)名-- 例子切換到一個(gè)名為mydb的數(shù)據(jù)庫(kù)上
USE mydb;

操作表

查看一個(gè)數(shù)據(jù)庫(kù)中創(chuàng)建了多少?gòu)埍?/h5>

SHOW TABLES

創(chuàng)建表

-- 語(yǔ)法
CREATE TABLE 表名(字段名1 類型[(長(zhǎng)度)][默認(rèn)值] [約束],字段名2 ...
)-- 例子 切換到mydb數(shù)據(jù)庫(kù)上，將userinfo表創(chuàng)建在該庫(kù)中
USE mydb;-- 創(chuàng)建userinfo表
CREATE TABLE userinfo(id INT primary key,								-- 一張表第一個(gè)字段通常為ID(主鍵，唯一標(biāo)識(shí))				username VARCHAR(30),							-- VARCHAR在數(shù)據(jù)庫(kù)中是字符串類型password VARCHAR(30),             -- VARCHAR后面指定的長(zhǎng)度為最多占用的字節(jié)數(shù)nickname VARCHAR(30),             -- 若使用UTF8編碼，意味著最多保存10個(gè)漢字(每個(gè)漢字3字節(jié))age INT(3),                       -- 在MySQL中整數(shù)使用INT類型 注:不同數(shù)據(jù)庫(kù)不同sex TINYINT(1) DEFAULT 0					-- 創(chuàng)建TINYINT，如果新增值時(shí)為空，則默認(rèn)為0
)

查看表結(jié)構(gòu)和已創(chuàng)建表的信息

-- 語(yǔ)法 查看表結(jié)構(gòu)
DESC 表名
-- 例子 查看userinfo表的結(jié)構(gòu)
DESC userinfo;-- 語(yǔ)法 查看已創(chuàng)建的表的創(chuàng)建語(yǔ)句
SHOW CREATE TABLE 表名-- 例子 查看創(chuàng)建userinfo表的信息
SHOW CREATE TABLE userinfo;

刪除表

-- 語(yǔ)法
DROP TABLE 表名-- 例子 刪除userinfo表
DROP TABLE userinfo;

修改表

修改表名

-- 語(yǔ)法
RENAME TABLE 原表名 TO 新表名-- 例子 將userinfo表改名為user
RENAME TABLE userinfo TO user

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

-- 準(zhǔn)備一張表
CREATE TABLE hero(name VARCHAR(30),age INT(3)
)

添加字段

-- 語(yǔ)法
-- 添加新字段到表的第一行
ALTER TABLE 表名 ADD 字段名 類型 FIRST-- 向表的末尾追加一個(gè)新的字段
ALTER TABLE 表名 ADD 字段名 類型-- 將新字段添加到表中現(xiàn)有某個(gè)字段之后
ALTER TABLE 表名 ADD 字段名 類型 AFTER 表中現(xiàn)有某字段-- ------------------------------------------------- 例子
-- 添加地址字段到hero表第一行
ALTER TABLE hero ADD address varchar(20) FIRST-- 向hero表的末尾追加一個(gè)地址字段
ALTER TABLE hero ADD address varchar(20)-- 將新字段地址添加到hero表中name字段之后
ALTER TABLE 表名 ADD address varchar(20) AFTER name

刪除字段

-- 語(yǔ)法
ALTER TABLE 表名 DROP 字段名-- 例子 刪除hero表中地址字段
ALTER TABLE hero DROP address

修改字段

-- 語(yǔ)法
ALTER TABLE 表名 CHANGE 原字段名 新字段名 類型-- 例子1 將hero表中的age字段長(zhǎng)度改為5
ALTER TABLE hero CHANGE age age INT(5)-- 例子2 將hero表中的age字段類型改為VARCHAR(10)
ALTER TABLE hero CHANGE age age VARCHAR(10) -- 例子3 將hero表中的gender字段改為phonenumber
ALTER TABLE hero CHANGE gender phonenumber INT(11)-- 例子4 將hero表中phonenumber字段添加非空約束
ALTER TABLE hero CHANGE phonenumber phonenumber INT(11) NOT NULL 

修改表結(jié)構(gòu)應(yīng)當(dāng)在表中還沒(méi)有數(shù)據(jù)時(shí)進(jìn)行，否則可能出現(xiàn)因修改與表中現(xiàn)有數(shù)據(jù)沖突導(dǎo)致修改失敗

• 修改字段類型時(shí)，如果表中該字段原數(shù)據(jù)類型與新類型不匹配時(shí)，會(huì)導(dǎo)致原數(shù)據(jù)無(wú)法轉(zhuǎn)換為新類型
  • 性別字段，原類型VARCHAR，記錄的值為"男"或"女"。現(xiàn)希望將性別字段改為INT型。會(huì)發(fā)生類型沖突
• 修改字段長(zhǎng)度,長(zhǎng)度減少可能導(dǎo)致失敗
  • 手機(jī)號(hào)字段，原有長(zhǎng)度11位?，F(xiàn)在想將其改為5位，會(huì)因?yàn)楝F(xiàn)有數(shù)據(jù)該字段值長(zhǎng)度都為11不能縮短導(dǎo)致修改失敗。
• 為現(xiàn)有字段添加約束，可能導(dǎo)致表中該現(xiàn)有記錄該字段值存在不滿足約束要求，導(dǎo)致修改失敗。
  • 性別字段開(kāi)始沒(méi)有非空約束，表中部分記錄該字段值為NULL，此時(shí)若修改該字段并追加非空約束，會(huì)導(dǎo)致現(xiàn)有該字段為NULL值的記錄不滿足該約束，導(dǎo)致約束無(wú)法修改。

索引操作

創(chuàng)建索引

1. 創(chuàng)建表的同時(shí)創(chuàng)建索引.(例如 create table tablename(…,index 索引名 (字段名)))

drop table if exists student;
create table if not exists student
(id bigint auto_increment,first_name varchar(50) not null comment '學(xué)生名字',last_name varchar(20) not null comment '學(xué)生姓',phone varchar(15) not null comment '手機(jī)號(hào)',email varchar(50) default '' comment '郵箱',birthday date comment '出生日期',create_time datetime default current_timestamp comment '注冊(cè)日期',primary key (id),unique key (phone),index h_index (email)
)engine = InnoDB character set utf8mb4;

2. 創(chuàng)建表后通過(guò)create語(yǔ)句創(chuàng)建索引(例如 create index 索引名 on 表名(字段名))
3. 創(chuàng)建表后通過(guò)alter語(yǔ)句創(chuàng)建索引(例如 alter table add index 索引名(字段名))

• 創(chuàng)建普通索引例子

create index index_first_name on student(first_name);
alter table student add index index_last_name (last_name);

• 創(chuàng)建唯一索引例子

create unique index index_first_name on student(first_name);
alter student add unique index index_email (email)

• 創(chuàng)建組合(聯(lián)合)索引例子

create index index_first_last on student(first_name,last_name);
alter table student add index index_first_last (first_name,last_name);

查看索引

-- 語(yǔ)法，查看某張表上的索引
show index from 表名

刪除索引

-- 語(yǔ)法
drop index 索引名 on 表名;
-- 例子 刪除student表中的index_first_last索引
drop index index_first_last on student;

DML

DML語(yǔ)言詳細(xì)名稱為data manipulation language，是數(shù)據(jù)操作語(yǔ)言，用來(lái)對(duì)表中的數(shù)據(jù)進(jìn)行操作的。

主要需要認(rèn)識(shí)的關(guān)鍵字如下：INSERT、UPDATE、DELETE

-- 準(zhǔn)備一張表:
CREATE TABLE person(name VARCHAR(30),age INT(3)
)

INSERT 插入數(shù)據(jù)

-- 語(yǔ)法 注:在語(yǔ)法定義上[]中的內(nèi)容是可寫可不寫的。
INSERT INTO 表名 [(字段1,字段2,字段3...)] VALUES (值1,值2,值3...)-- 例子
INSERT INTO person (name,age) VALUES ('張三',22);
INSERT INTO person (age,name) VALUES (33,'李四');

注：

• 在SQL中字符串的字面量使用**單引號(hào)(‘’)**括起來(lái)。
• VALUES后面指定的值的類型，順序，個(gè)數(shù)要與前面指定的字段完全一致，且一一對(duì)應(yīng)

插入默認(rèn)值

當(dāng)插入一條記錄時(shí)，表中某字段沒(méi)有指定時(shí)，則是插入該字段的默認(rèn)值。若該字段沒(méi)有明確指定過(guò)默認(rèn)值時(shí)，默認(rèn)值為NULL。

-- 例如
INSERT INTO person (name) VALUES ('王五')
-- 上述SQL中沒(méi)有指定age字段，此時(shí)該條記錄插入后，age使用該字段的默認(rèn)值NULL。

可以在創(chuàng)建表或后期修改表結(jié)構(gòu)時(shí)，使用DEFAULT關(guān)鍵字為某個(gè)字段指定默認(rèn)值

-- 為person表中的age字段添加默認(rèn)值:20
ALTER TABLE person CHANGE age age INT(3) DEFAULT 20;-- 在創(chuàng)建表時(shí)為字段指定默認(rèn)值:
CREATE TABLE person(name VARCHAR(30) DEFAULT '無(wú)名氏',age INT(3) DEFAULT 20
)

修改表為age添加默認(rèn)值20

-- 此時(shí)插入后的趙六記錄中age應(yīng)當(dāng)已經(jīng)采取了指定的默認(rèn)值:20
INSERT INTO person(name) VALUES('趙六');

全列插入

在INSERT語(yǔ)句中沒(méi)有指定字段名時(shí)就是全列插入，此時(shí)需要注意VALUES子句中指定的值的個(gè)數(shù)，順序，類型必須與表中定義時(shí)指定的字段一致。

例

INSERT INTO person VALUES('錢七',38)-- 若想使用某個(gè)字段的默認(rèn)值,可以使用DEFAULT關(guān)鍵字表達(dá)
INSERT INTO person VALUES('錢七',DEFAULT)-- 若希望插入NULL值，可以使用NULL表達(dá)
INSERT INTO person VALUES('錢七',NULL)-- 下面是錯(cuò)誤的情況:
INSERT INTO person VALUES('錢七')			列的個(gè)數(shù)不匹配
INSERT INTO person VALUES(22,'錢七')      列的類型不匹配

查詢插入

查詢另一張表的數(shù)據(jù)插入到person表

INSERT INTO person SELECT name,age FROM user

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

-- 語(yǔ)法
UPDATE 表名 SET 字段1=新值1[,字段2=新值2,...] [WHERE 過(guò)濾條件]-- 例子 當(dāng)缺少WHERE子句時(shí)，數(shù)據(jù)庫(kù)會(huì)將person表中每條記錄的age字段都改為15
UPDATE person SET age=15

修改指定的記錄

僅將滿足WHERE條件的記錄進(jìn)行修改。

-- 將張三的年齡修改為22歲
UPDATE person SET age=22 WHERE name='張三'-- 只要滿足WHERE條件的記錄都會(huì)被修改。
UPDATE person
SET age=36
WHERE age=15

WHERE子句常用的條件

=,>,>= >,<,<=,<>(不等于:<>。實(shí)際上!=也可以用。)

-- 例子1 將年齡大于35歲的人年齡修改為30
UPDATE person
SET age=30
WHERE age>35-- 例子2 將person表中每個(gè)人的年齡漲一歲
UPDATE person
SET age=age+1

修改多個(gè)字段的值

-- 例子 將'李四'的名字改為'李老四'并且年齡改為50
UPDATE person
SET name='李老四',age=50
WHERE name='李四'

DELETE 刪除數(shù)據(jù)

-- 語(yǔ)法
DELETE FROM 表名 [WHERE 過(guò)濾條件]

注:DELETE語(yǔ)句中通常都要添加WHERE條件，否則是清空表操作!

--例子1 刪除'李老四'
DELETE FROM person
WHERE name='李老四'-- 例子2 刪除年齡大于30的人
DELETE FROM person
WHERE age>30

清空表操作

DELETE FROM person

處理DELETE可以刪除表的數(shù)據(jù)，還有一個(gè)TRUNCATE也可以清空表，但是并不常用，如果要使用最好查詢這個(gè)關(guān)鍵字的用法和使用后果

DQL

DQL語(yǔ)言詳細(xì)名稱為data query language，是數(shù)據(jù)查詢語(yǔ)言，用來(lái)查詢表中數(shù)據(jù)的。

主要需要認(rèn)識(shí)的關(guān)鍵字如下：SELECT

-- 基本語(yǔ)法
SELECT 字段1, 字段2
FROM 表名
WHERE 條件表達(dá)式;-- 例子 查詢person表中年齡為16的數(shù)據(jù)，且只展示name、sex、age字段
SELECT name, sex, age
FROM person
WHERE age = 16;

本章只是做介紹，所以并不會(huì)深入講解DQL的其他關(guān)鍵字，例如：DISTINCT、GROUP BY、HAVING、ORDER BY、LIMIT

TCL

TCL語(yǔ)言詳細(xì)名稱為transaction control lauguage，是事務(wù)控制語(yǔ)言，用來(lái)保證一些列數(shù)據(jù)庫(kù)操作具有原子性，隔離性，一致性，持久性

主要需要認(rèn)識(shí)的關(guān)鍵字如下：COMMIT、ROLLBACK
事務(wù)需要在對(duì)數(shù)據(jù)庫(kù)有一定了解后再去深入了解。

DCL

DCL語(yǔ)言詳細(xì)名稱為data control language，數(shù)據(jù)控制語(yǔ)言，進(jìn)行數(shù)據(jù)庫(kù)管理操作的，比如用戶創(chuàng)建，權(quán)限分配等，是DBA經(jīng)常使用的操作，DCL通常是用來(lái)確保使用合適的權(quán)限控制來(lái)保護(hù)數(shù)據(jù)庫(kù)的安全性

主要需要認(rèn)識(shí)的關(guān)鍵字如下：GRANT、REVOKE

授予用戶權(quán)限

-- 語(yǔ)法
GRANT 權(quán)限 ON 數(shù)據(jù)庫(kù)名[.表名] TO '用戶名'@'主機(jī)名';

其中權(quán)限可以有：ALL PRIVILEGES 或具體的權(quán)限如 SELECT, INSERT, UPDATE 等

-- 例子
GRANT SELECT,INSERT ON mydatabase.mytable TO 'pp'@'localhost';

撤銷用戶權(quán)限

-- 語(yǔ)法
REVOKE 權(quán)限 ON 數(shù)據(jù)庫(kù)名[.表名] TO '用戶名'@'主機(jī)名';

其中權(quán)限可以有：ALL PRIVILEGES 或具體的權(quán)限如 SELECT, INSERT, UPDATE 等

-- 例子
REVOKE SELECT,INSERT ON mydatabase.mytable TO 'pp'@'localhost';