MySQL資料庫基礎

標籤：服務端   數值   primary   mysqld   不為   財務   上下   關係   介紹   

一、MySQL介紹二、MySQL安裝1. linux安裝mysql

使用yum包進行安裝

yum install mysql-server mysql -y

2. windows安裝mysql

在mysql官網上下載windows版本的mysql（MySQL Community Server）安裝包

：https://dev.mysql.com/downloads/mysql/

下載完畢後需要添加mysql的環境變數

a) 初始化：

mysqld --initialise-insecure (--user=mysql)

b) 啟動服務端：

mysqld

c) 串連用戶端：

mysql -u root -p

d) 建立mysql資料庫：

create database sjingx;

e) 製作MySQL的windows服務：

mysql install（建立mysql服務）

mysql remove（刪除mysql服務）

f) 建立好MySQL服務的開啟與關閉

net start mysql（開啟mysql服務）

net stop mysql（關閉mysql服務）

3. OS X安裝mysql4. 基本管理

a) 建立使用者

b) 授權

c) 資料庫的匯入和匯出

mysqldump -u 使用者名稱 -p 密碼 資料庫名稱 > 匯出檔案路徑

三、庫操作1. 系統資料庫

information_schema： 虛擬庫，不佔用磁碟空間，儲存的是資料庫啟動後的一些參數，如使用者表資訊、列資訊、許可權資訊、字元資訊等

performance_schema： MySQL 5.5開始新增一個資料庫：主要用於收集資料庫伺服器績效參數，記錄處理查詢請求時發生的各種事件、鎖等現象

mysql： 授權庫，主要儲存系統使用者的許可權資訊

test： MySQL資料庫系統自動建立的測試資料庫

2. 建立資料庫

文法:

create database 資料庫名 charset utf8;

3. 資料庫相關操作

create database db1 charset utf8;（建立資料庫db1，編碼格式為UTF8）

show create database sjingx;（查看資料庫sjingx資訊）

show databases;（查看所有資料庫）

alter database db1 charset gbk;（修改資料db1的編碼格式為GBK）

drop database db1;（刪除資料庫）

四、表操作1. 儲存引擎2. 表的增刪改查

1)建立表

文法：

create table 表名(欄位名1 類型[(寬度) 約束條件],欄位名2 類型[(寬度) 約束條件],欄位名3 類型[(寬度) 約束條件]);#注意：1. 在同一張表中，欄位名是不能相同2. 寬度和約束條件可選3. 欄位名和類型是必須的

樣本：

create table t1(     id int,    name varchar(50),    sex enum(‘male‘,‘female‘),    age int(3));# 查看db1庫下所有表名show tables; select id,name,sex,age from t1;select * from t1;select * from t1;# 往表中插入資料insert into t1 values(1,‘alex‘,‘male‘,20);# 往id欄位插入資料insert into t1(id) values(2),(3);

2)查看錶結構

# 查看錶結構，可簡寫為desc 表名mysql> desc t1;+-------+-----------------------+------+-----+---------+-------+| Field | Type                  | Null | Key | Default | Extra |+-------+-----------------------+------+-----+---------+-------+| id    | int(11)               | YES  |     | NULL    |       || name  | varchar(50)           | YES  |     | NULL    |       || sex   | enum(‘male‘,‘female‘) | YES  |     | NULL    |       || age   | int(3)                | YES  |     | NULL    |       |+-------+-----------------------+------+-----+---------+-------+# 查看錶詳細結構，可加\Gmysql> show create table t1\G;*************************** 1. row ***************************       Table: t1Create Table: CREATE TABLE `t1` (  `id` int(11) DEFAULT NULL,  `name` varchar(50) DEFAULT NULL,  `sex` enum(‘male‘,‘female‘) DEFAULT NULL,  `age` int(3) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8

3)修改結構表

文法：

1. 修改表名：    alter table 表名 rename 新表名;2. 增加欄位：    alter table 表名 add 欄位名 資料類型[完整性條件約束條件...],                     add 欄位名 資料類型[完整性條件約束條件...];    alter table 表名 add 欄位名  資料類型 [完整性條件約束條件…]  FIRST;    alter table 表名 add 欄位名  資料類型 [完整性條件約束條件…]  AFTER 欄位名;3. 刪除欄位    alter table 表名 drop 欄位名;4. 修改欄位    alter table 表名 modify 欄位名 資料類型 [完整性條件約束條件...];    alter table 表名 change 舊欄位名 新欄位名 舊資料類型 [完整性條件約束條件…];    alter table 表名 change 舊欄位名 新欄位名 新資料類型 [完整性條件約束條件…];

樣本：

1. 修改儲存引擎    mysql> alter table service         -> engine=innodb;2. 添加欄位    mysql> alter table student10        -> add name varchar(20) not null,        -> add age int(3) not null default 22;    mysql> alter table student10        -> add stu_num varchar(10) not null after name;                //添加name欄位之後    mysql> alter table student10                                -> add sex enum(‘male‘,‘female‘) default ‘male‘ first;          //添加到最前面3. 刪除欄位    mysql> alter table student10        -> drop sex;    mysql> alter table service        -> drop mac;4. 修改欄位類型modify    mysql> alter table student10        -> modify age int(3);    mysql> alter table student10        -> modify id int(11) not null primary key auto_increment;    //修改為主鍵5. 增加約束（針對已有的主鍵增加auto_increment）    mysql> alter table student10 modify id int(11) not null primary key auto_increment;    ERROR 1068 (42000): Multiple primary key defined    mysql> alter table student10 modify id int(11) not null auto_increment;    Query OK, 0 rows affected (0.01 sec)    Records: 0  Duplicates: 0  Warnings: 06. 對已經存在的表增加複合主鍵    mysql> alter table service2        -> add primary key(host_ip,port);        7. 增加主鍵    mysql> alter table student1        -> modify name varchar(10) not null primary key;8. 增加主鍵和自動成長    mysql> alter table student1        -> modify id int not null primary key auto_increment;9. 刪除主鍵    a. 刪除自增約束    mysql> alter table student10 modify id int(11) not null;     b. 刪除主鍵    mysql> alter table student10                                         -> drop primary key;

4)複製表

複製表結構＋記錄 （key不會複製: 主鍵、外鍵和索引）mysql> create table new_service select * from service;只複製表結構mysql> select * from service where 1=2;        //條件為假，查不到任何記錄Empty set (0.00 sec)mysql> create table new1_service select * from service where 1=2;  Query OK, 0 rows affected (0.00 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> create table t4 like employees;

5)刪除表

drop table 表名;# 清空表，auto_increment = 0truncate 表名;

3. 資料類型

1）數實值型別

整型作用：儲存年齡，等級，id，各種號碼

浮點型作用：儲存薪資，身高，體重，體質參數

2）日期類型

作用：儲存使用者註冊時間，文章發布時間，員工入職時間，出生時間，到期時間

3）字串類型

char和varchar(char)

4）枚舉和集合類型

4. 完整的約束

1）約束條件not null與default

not null 非空
default 預設值
zerofill:如果插入int(4)，你插入1，顯示0001
unsigned:不能為負數，當你插入負數就顯示為0

create table t1(    id int(11) unsigned zerofill);create table t1(    id int,    name char(6),    sex enum(‘male‘,‘female‘) not null default ‘male‘);

2）約束條件unique

unique約束唯一標識資料庫中的每條記錄

UNIQUE 和 PRIMARY KEY 約束均為列或列集合提供了唯一性的保證。

PRIMARY KEY 約束擁有自動定義的 UNIQUE 約束。

請注意，每個表可以有多個 UNIQUE 約束，但是每個表只能有一個 PRIMARY KEY 約束。

單列唯一：

方式一：create table department(    id int,    name char(10) unique);方式二：create table department(    id int,    name char(10),    unique(name));

聯合唯一：

create table services(    id int,    name char(15),    port int,    unique(id),    unique(name,port));

3）約束條件primary key

約束：not null unique 不為空白且唯一

儲存引擎innodb：對於innodb儲存引擎來說，一張表內必須有一個主鍵

# 單列主鍵create table t1(    id int primary key,    name char(16));# 會自動呢找一個不唯一且為空白的欄位為主鍵create table t1(    id int not null unique,    name char(16));# 複合主鍵create table t1(    id char(15),    port int,    primary key(id,port))

4）約束條件auto_increment

create table t1(    id int primary key auto_crement,    name char(15));# 瞭解show variables like ‘auto_inc%‘; 查看與auto_inc模糊查詢# 步長auto_increment_increment預設為1# 起始位移量auto_increment_offset預設為1# 設定步長set session auto_increment_increment=5;set global auto_increment_increment=5;# 設定起始位移量（起始位移量<=步長）set global auto_increment_offset=3;

5）約束條件foregin key 用來建立表之間的關係

可以發現下表中的部門和部門資訊重複 id	name	sex	dep_name	comment
1	alex	male	技術部	技術
2	sjingx	male	技術部	技術
3	wuxx	female	財務部	錢
4	liunx	male	產生部	生產
5	python	male	技術部	技術

forrgin key的作用：方便管理，節省磁碟空間

樣本：

user_info表 id	name	sex	dep_id
1	alex	male	1
2	sjingx	male	1
3	wuxx	female	2
4	liunx	male	3
5	python	male	1

dep表 id	name	comment
1	技術部	技術
2	財務部	錢
3	產生部	生產

# 先建被關聯的表create table dep(    id int,    name char(16),    comment char(50));# 再建關聯的表create table user_info( id int primary key, name char(10), sex enum(‘male‘,‘female‘), dep_id int, foreign key(dep_id) references dep(id) on delete cascade on update cascade);on delete cascade刪除同步：被關聯一刪除，關聯的表也刪除了on update cascade更新同步：同上# 先往被關聯表插入記錄# 在往關聯表插入記錄# 刪除

5. 表之間的關係五、資料操作

MySQL資料庫基礎