資料庫應用

概念

什么是資料庫

簡而言之，就是存盤資料，管理資料的倉庫，

常見的資料庫分為：

• 關系型資料庫， Oracle、MySQL、SQLServer、Access
• 非關系型資料庫， MongoDB、Redis、Solr、ElasticSearch、Hive、HBase
  

關系型和非關系型

早期發展的資料庫建立在資料的緊密關系基礎之上（如：父子關系、師生關系），我們稱其為關系型資料庫，也稱為傳統資料庫；現今資料庫建立在資料的松散關系基礎之上（如：中國人和美國人、中國人和印度人、視頻、音頻），我們稱其為非關系型資料庫nosql（not only sql），業界總在爭論nosql能否干掉傳統資料庫，很多初學者也有這個困惑，以我來看，兩者沒有矛盾，它們各有特點，根據業務情況互補才是真諦，但總的來說原來關系型資料庫一統天下的格局早被打破，領土不斷被蠶食，規模一再的縮小，雖然無法全面被替代，但卻早已風光不在，淪落到一偶之地，Oracle的衰落就是最好的證明，早期只要是全球大企業無一例外都是部署Oracle，但現在都在去Oracle化，阿里就已經全面排斥Oracle，

既然干不掉，很多傳統專案的還是圍繞關系型資料庫的居多，所以我們先來學習關系型資料庫，目前最流行的關系型資料庫是MySQL，

關系型資料庫

關系型資料庫有特定的組織方式，其以行和列的形式存盤資料，以便于用戶理解，關系型資料庫這一系列的行和列被稱為表，一組表組成了資料庫，用戶通過查詢來檢索資料庫中的資料，而查詢是一個用于限定資料庫中某些區域的執行代碼，關系模型可以簡單理解為二維表格模型，而一個關系型資料庫就是由二維表及其之間的關系組成的一個資料集合，

Mysql資料庫

1. mysql服務端，它來處理具體資料維護，保存磁盤
2. mysql客戶端，CRUD新增，修改，洗掉，查詢

MySQL資料存放在哪里？

在MySQL的組態檔my.ini中會進行默認配置

MySQL服務端

mysql-5.5.27-winx64.msi

Mysql資料庫默認的編碼是latin1等價于iso-8859-1，修改為utf-8

注意：配置完，mysql開始執行，最后一步出錯有時仍可以使用，使用SQLyog工具測驗，如不行，再執行安裝程式，選擇remove，洗掉，然后重新安裝，同時注意必須是管理員權限，

MySQL客戶端1：DOS視窗

mysql -uroot -proot

語法：mysql.exe執行檔案

代表引數

-u 用戶名，緊接著寫的

-p 密碼，緊接著寫的

MySQL客戶端2：可視化工具

資料庫的結構

資料庫結構

SQL陳述句

定義

結構化查詢語言(Structured Query Language)簡稱SQL(發音：/?es kju? ?el/ "S-Q-L")，是一種特殊目的的編程語言，是一種資料庫查詢和程式設計語言，用于存取資料以及查詢、更新和管理關系資料庫系統；同時也是資料庫腳本檔案的擴展名，

SQL 是1986年10 月由美國國家標準局（ANSI）通過的資料庫語言美國標準，接著，國際標準化組織（ISO）頒布了SQL正式國際標準，

分類

• DML（Data Manipulation Language）資料操縱語言

如：insert，delete，update，select（插入、洗掉、修改、檢索）簡稱CRUD操新增Create、查詢Retrieve、修改Update、洗掉Delete

• DDL（Data Definition Language）資料庫定義語言

如：create table之類

• DCL（Data Control Language）資料庫控制語言

如：grant、deny、revoke等，只有管理員才有相應的權限

• DQL（Data Query Language）資料庫查詢語言
  如: select 語法

注意：SQL不區分大小寫

資料庫常用操作

建庫

• 創建資料庫，資料庫名稱：cgb2022

create database cgb2022 DEFAULT CHARACTER SET utf8;

刪庫

• 洗掉名稱是cgb2022的資料庫

drop database cgb2022;

查看所有資料庫

• 查看所有資料庫

show databases;

表的常用操作

使用資料庫：use cgb2022;

表設計

門店表：** tb_door**

訂單詳情表：** tb_order_detail**

創建表

• 創建tb_door表，有id,door_name,tel欄位

create table tb_door(
id int primary key auto_increment,
door_name varchar(100),
tel varchar(50)
);

修改表

• 添加列

alter table tb_door add column money NUMERIC(7,2)

洗掉表

• 洗掉名稱是tb_door的表

drop table tb_door;

查看所有表

• 查看所有表

show tables;

查看表結構/設計表

• 查看tb_door表結構

desc tb_door;

表記錄的常用操作

插入記錄

• 向tb_door表中插入2條記錄

insert into tb_door values(null,'永和大王1店',666);

insert into tb_door values(null,' 永和大王2店',888);

查詢記錄

• 查詢tb_door表中的所有記錄

SELECT * FROM tb_door;

修改記錄

• 修改tb_door表中id為1的記錄

update tb_door set tel=555 where id=1;

洗掉記錄

• 洗掉tb_door表中id為2的資料

Delete from tb_door where id=2;

排序

• 將tb_door表記錄按照tel排序

Select * from tb_door order by tel desc;

記錄總數

• 查詢tb_door表中的總記錄數

Select count(*) from tb_door;

資料型別

命名規則

• 欄位名必須以字母開頭，盡量不要使用拼音
• 長度不能超過30個字符（不同資料庫，不同版本會有不同）
• 不能使用SQL的保留字，如where,order,group
• 只能使用如下字符a_z、AZ、0~9、$ 等
• Oracle習慣全大寫：USER_NAME，mysql習慣全小寫：user_name
• 多個單詞用下劃線隔開，而非java語言的駝峰規則

字符

• char長度固定，不足使用空格填充，最多容納2000個字符，char(11)存盤abc，占11位，查詢速度極快但浪費空間
• varchar變長字串，最多容納4000個字符，varchar(11)存盤abc，只占3位，查詢稍慢，但節省空間，Oracle為varchar2
• 大文本: 大量文字（不推薦使用，盡量使用varchar替代）

以utf8編碼計算的話，一個漢字在u8下占3個位元組

注：不同資料庫版本長度限制可能會有不同

數字

• tinyint,int整數型別
• float,double小數型別
• numeric(5,2) decimal(5,2)—也可以表示小數,表示總共5位,其中可以有兩位小數
• decimal和numeric表示精確的整數數字

日期

• date 包含年月日
• time時分秒
• datetime包含年月日和時分秒
• timestamp時間戳，不是日期，而是從1970年1月1日到指定日期的毫秒數

圖片

• blob 二進制資料，可以存放圖片、聲音，容量4g，早期有這樣的設計，但其缺點非常明顯，資料庫龐大，備份緩慢，這些內容去備份多份價值不大，同時資料庫遷移時過大，遷移時間過久，所以目前主流都不會直接存盤這樣的資料，而只存盤其訪問路徑，檔案則存放在磁盤上，

準備資料

部門表 dept

CREATE TABLE dept(
deptno int primary key auto_increment ,
dname VARCHAR(20),
loc VARCHAR(13)
);
INSERT INTO dept VALUES(null,'accounting','一區');
INSERT INTO dept VALUES(null,'research','二區');
INSERT INTO dept VALUES(null,'operations','二區');

員工表 emp

Mysql:

CREATE TABLE emp(
empno int primary key auto_increment,
ename VARCHAR(10),
job VARCHAR(10),
mgr int,
hiredate DATE,
sal double,
comm NUMERIC(7,2),
deptno int
);
INSERT INTO emp VALUES(100,'jack','副總',NULL,'2002-05-1',90000,NULL,1);
INSERT INTO emp VALUES(200,'tony','總監',100,'2015-02-02',10000,2000,2);
INSERT INTO emp VALUES(300,'hana','經理',200,'2017-02-02',8000,1000,2);
INSERT INTO emp VALUES(400,'leo','員工',300,'2019-02-22',3000,200.12,2);
INSERT INTO emp VALUES(500,'liu','員工',300,'2019-03-19',3500,200.58,2);

欄位約束

主鍵約束

主鍵約束：如果為一個列添加了主鍵約束，那么這個列就是主鍵，主鍵的特點是唯一且不能為空，通常情況下，每張表都會有主鍵，

添加主鍵約束，例如將id設定為主鍵：

主鍵自增策略** **當主鍵為數值型別時，為了方便維護，可以設定主鍵自增策略（auto_increment），設定了主鍵自增策略后，資料庫會在表中保存一個AUTO_INCREMENT變數值，初始值為1，當需要id值，不需要我們指定值，由資料庫負責從AUTO_INCREMENT獲取一個id值，作為主鍵值插入到表中，而且每次用完AUTO_INCREMENT值，都會自增1. AUTO_INCREMENT=1

create table abc(
id int primary key auto_increment
);
insert into abc values(null);
insert into abc values(null);
insert into abc values(null);
select * from abc;

非空約束

非空約束：如果為一個列添加了非空約束，那么這個列的值就不能為空，但可以重復，

添加非空約束，例如為password添加非空約束：

create table user(
id int primary key auto_increment,
password varchar(50) not null
);
show tables;
insert into user values(null,null);//不符合非空約束
insert into user values(null,123;);//OK

唯一約束

唯一約束：如果為一個列添加了唯一約束，那么這個列的值就必須是唯一的（即不能重復），但可以為空，

添加唯一約束，例如為username添加唯一約束及非空約束：

create table test(
id int primary key auto_increment,
username varchar(50) unique--唯一約束
);
show tables;
insert into test values(null,'lisi');
insert into test values(null,'lisi');--username的值要唯一,重復會報錯的
select * from test;

基礎函式

lower

SELECT 'ABC',LOWER('ABC') from dept; --資料轉小寫

upper

select upper(dname) from dept --資料轉大寫

length

select length(dname) from dept --資料的長度

substr

SELECT dname,SUBSTR(dname,1,3) FROM dept; --截取[1,3]

concat

select dname,concat(dname,'123') X from dept --拼接資料

replace

select dname,replace(dname,'a','666') X from dept --把a字符替換成666

ifnull

select ifnull(comm,10) comm from dept2 #判斷,如果comm是null,用10替換

round & ceil & floor

round四舍五入，ceil向上取整，floor向下取整

–直接四舍五入取整

select comm,round(comm) from emp

–四舍五入并保留一位小數

select comm,round(comm,1) from emp

–ceil向上取整,floor向下取整

select comm,ceil(comm) ,floor(comm) from emp

uuid

SELECT UUID()

回傳uuid：a08528ca-741c-11ea-a9a1-005056c00001

now

select now() -- 年與日 時分秒

select curdate() --年與日

select curtime() --時分秒

year & month & day

–hour()時 minute()分 second()秒

select now(),hour(now()),minute(now()),second(now()) from emp ;

–year()年 month()月 day()日

select now(),year(now()),month(now()),day(now()) from emp ;

轉義字符

'作為sql陳述句符號，內容中出現單撇就會亂套，進行轉義即可

select 'ab'cd' -- 單引號是一個SQL陳述句的特殊字符

select 'ab\'cd' --資料中有單引號時,用一個\轉義變成普通字符