MySQL 數(shù)據(jù)庫(kù)和表

MySQL基礎(chǔ)之?dāng)?shù)據(jù)庫(kù)和表管理

老師筆記

數(shù)據(jù)庫(kù)中的默認(rèn)庫(kù)
INFORMATION_SCHEMA提供對(duì)數(shù)據(jù)庫(kù)元數(shù)據(jù)的訪問 褥蚯,有關(guān)MySQL服務(wù)器的信息译秦,例如數(shù)據(jù)庫(kù)或表的名稱,列的數(shù)據(jù)類型或訪問權(quán)限茂洒。有時(shí)用于此信息的其他術(shù)語是 數(shù)據(jù)字典和 系統(tǒng)目錄威根。
官方網(wǎng)頁(yè)

一、數(shù)據(jù)庫(kù)管理

常用操作語句

-- 查看當(dāng)前數(shù)據(jù)庫(kù)
SELECT DATABASE();

-- 顯示當(dāng)前時(shí)間嘴脾、用戶名男摧、數(shù)據(jù)庫(kù)版本
SELECT now(), user(), version();

-- 創(chuàng)建庫(kù)
CREATE DATABASE[ IF NOT EXISTS] 數(shù)據(jù)庫(kù)名 數(shù)據(jù)庫(kù)選項(xiàng)
    數(shù)據(jù)庫(kù)選項(xiàng):
   
        DEFAULT CHARACTER SET charset_name
        COLLATE collation_name

-- 查看已有庫(kù)
SHOW DATABASES[ LIKE 'PATTERN']

-- 查看當(dāng)前庫(kù)信息
SHOW CREATE DATABASE 數(shù)據(jù)庫(kù)名

-- 修改庫(kù)的選項(xiàng)信息
ALTER DATABASE 庫(kù)名 選項(xiàng)信息

-- 刪除庫(kù),同時(shí)刪除該數(shù)據(jù)庫(kù)相關(guān)的目錄及其目錄內(nèi)容
DROP DATABASE[ IF EXISTS] 數(shù)據(jù)庫(kù)名

創(chuàng)建庫(kù)時(shí)指定字符集(utf-8, gb2312)

charset_name 就是字符集,比如:

  • gbk
  • utf8
  • utf8mb4

collation_name排序規(guī)則译打,比如:

  • gbk_chinese_ci
  • utf8_general_ci
  • utf8mb4_general_ci
# utf-8
CREATE DATABASE 數(shù)據(jù)庫(kù)名稱 DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
 
# gbk
CREATE DATABASE 數(shù)據(jù)庫(kù)名稱 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

例如:

mysql> create database teacher default charset utf8mb4 collate utf8mb4_general_ci;

使用數(shù)據(jù)庫(kù)

USE 數(shù)據(jù)庫(kù)名字;

查看當(dāng)前庫(kù)

select database();
/*或*/
status;

查看庫(kù)的默認(rèn)字符集

mysql> show  create database teacher\G

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

drop database  數(shù)據(jù)庫(kù)名稱;

二耗拓、 表的管理

1、MySQL 中的數(shù)據(jù)類型

int(M)[UNSIGNED][ZEROFILL]
UNSIGNED 表示符號(hào)
ZEROFILL 表示不夠的位使用 0 填充
number int(2) UNSIGNED ZEROFILL
number 的值假如你存1奏司,顯示的時(shí)候不夠 2 位乔询,就會(huì)使用 0 填充,變成 02
整數(shù)韵洋,數(shù)據(jù)類型用于保存一些范圍的整數(shù)數(shù)值范圍:
有符號(hào):
-2147483648 ~ 2147483647
無符號(hào):
0 ~ 4294967295

FLOAT[(M.D) [UNSIGNED] [ZEROFILL]
單精度浮點(diǎn)數(shù)(非準(zhǔn)確小數(shù)值)竿刁,m是數(shù)字總個(gè)數(shù)黄锤,d是小數(shù)點(diǎn)后個(gè)數(shù)。
無符號(hào):
-3.402823466E+38 to -1.175494351E-38,
0
1.175494351E-38 to 3.402823466E+38
有符號(hào):
0
1.175494351E-38 to 3.402823466E+38
** ** 數(shù)值越大食拜,越不準(zhǔn)確 ** **

char (m)
char數(shù)據(jù)類型用于表示固定長(zhǎng)度的字符串鸵熟,可以包含最多達(dá)255個(gè)字符。其中m代表字符串的長(zhǎng)度负甸。
PS: 即使數(shù)據(jù)小于m長(zhǎng)度流强,也會(huì)占用m長(zhǎng)度

varchar(m)
varchars數(shù)據(jù)類型用于變長(zhǎng)的字符串,
可以包含最多達(dá)255個(gè)字符呻待。
其中m代表該數(shù)據(jù)類型所允許保存的字符串的最大長(zhǎng)度
只要長(zhǎng)度小于該最大值的字符串都可以被保存在該數(shù)據(jù)類型中打月。

注:
雖然varchar使用起來較為靈活,但是從整個(gè)系統(tǒng)的性能角度來說蚕捉,char數(shù)據(jù)類型的處理速度更快奏篙,有時(shí)甚至可以超出varchar處理速度的50%。因此迫淹,用戶在設(shè)計(jì)數(shù)據(jù)庫(kù)時(shí)應(yīng)當(dāng)綜合考慮各方面的因素报破,以求達(dá)到最佳的平衡

text
text數(shù)據(jù)類型用于保存變長(zhǎng)的大字符串,可以組多到65535 (2* *16 ? 1)個(gè)字符千绪。
mediumtext
A TEXT column with a maximum length of 16,777,215 (2* *24 ? 1) characters.
longtext
A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 ? 1) characters.

Enum

枚舉類型
 An Enum column can have a maximum of 65,535 distinct >elements. (The practical limit is less than 3000.)

示例:
   CREATE TABLE shirts (
         name VARCHAR(40),
         size Enum('x-small', 'small', 'medium', 'large', 'x-large')
        );
         INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');

set

集合類型
           A SET column can have a maximum of 64 distinct members.
           示例:
               CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
              INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');

定義數(shù)據(jù)庫(kù)列時(shí),可以使用Enum(Enumeration荸型,枚舉)和SET(集合)類型:變通的實(shí)現(xiàn)CHECK約束
兩者的區(qū)別是:

  • 使用Enum盹靴,只能選一個(gè)值;
  • 使用SET瑞妇,可以選多個(gè)值稿静;
  • Enum和SET中的值都必須是字符串類型。

注意:
  在內(nèi)部存儲(chǔ)Enum值時(shí)辕狰,MYSQL給Enum中的每個(gè)值一個(gè)順序號(hào)碼:第一個(gè)值的順序號(hào)碼是1改备,第二個(gè)值的順序號(hào)碼是2,以此類推蔓倍。當(dāng)排序或比較Enum的時(shí)候悬钳,使用這些順序號(hào)碼進(jìn)行。

關(guān)于日期和時(shí)間類型的字段
字段類型: DATE
表現(xiàn)形式: YYYY-MM-DD
取值范圍: 1000-01-01/9999-12-31
創(chuàng)建方法: 字段名 DATE
添加數(shù)據(jù):values("20191010")
查詢到的結(jié)果:2019-10-10

2偶翅、MySQL 表的操作

CREATE     創(chuàng)建表,在數(shù)據(jù)庫(kù)創(chuàng)建對(duì)象
ALTER      修改表,修改數(shù)據(jù)庫(kù)結(jié)構(gòu)(擴(kuò)展自修)
DROP       刪除表,從數(shù)據(jù)庫(kù)中刪除對(duì)象
TRUNCATE   刪除表中所有行(無法回退)
COMMENT    注釋(為數(shù)據(jù)字典添加備注)
RENAME     重命名表(擴(kuò)展自修)

關(guān)于存儲(chǔ)引擎

-- 存儲(chǔ)引擎
  ENGINE = engine_name
  表在管理數(shù)據(jù)時(shí)采用的不同的數(shù)據(jù)結(jié)構(gòu)默勾,結(jié)構(gòu)不同會(huì)導(dǎo)致處理方式、提供的特性操作等不同
  常見的引擎:InnoDB MyISAM Memory/Heap BDB Merge Example CSV MaxDB Archive
  不同的引擎在保存表的結(jié)構(gòu)和數(shù)據(jù)時(shí)采用不同的方式
  MyISAM表文件含義:.frm表定義聚谁,.MYD表數(shù)據(jù)母剥,.MYI表索引
  InnoDB表文件含義:.frm表定義,表空間數(shù)據(jù)和日志文件
  SHOW ENGINES -- 顯示存儲(chǔ)引擎的狀態(tài)信息
  SHOW ENGINE 引擎名 {LOGS|STATUS} -- 顯示存儲(chǔ)引擎的日志或狀態(tài)信息
    -- 自增起始數(shù)
        AUTO_INCREMENT = 行數(shù)
    -- 數(shù)據(jù)文件目錄
        DATA DIRECTORY = '目錄'
    -- 索引文件目錄
        INDEX DIRECTORY = '目錄'
    -- 表注釋
        COMMENT = 'string'
    -- 分區(qū)選項(xiàng)
        PARTITION BY ... (詳細(xì)見手冊(cè))

a. 創(chuàng)建表

create table 表名[ IF NOT EXISTS](
    列名  類型  [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'],
    列名  類型
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

創(chuàng)建表時(shí)可以單獨(dú)指定其使用的存儲(chǔ)引擎和默認(rèn)字符編碼。
ENGINE=InnoDB DEFAULT CHARSET=utf8

默認(rèn)值环疼,創(chuàng)建列時(shí)可以指定默認(rèn)值习霹,當(dāng)插入數(shù)據(jù)時(shí)如果未主動(dòng)設(shè)置,則自動(dòng)添加默認(rèn)值
create table tb1(
    id int not null ,                                                                                
    age int not null default 20
)
是否可空炫隶,null表示空序愚,并不是字符串類型
not null    - 不可空,在添加數(shù)據(jù)時(shí)必須給值
null        - 可空(這是非主鍵的默認(rèn)屬性)
主鍵等限,一種特殊的唯一索引,不允許有空值芬膝,如果主鍵使用單個(gè)列望门,則它的值必須唯一,如果是多列锰霜,則其組合必須唯一筹误。
create table tb1(
    id int   primary key,
    age int null
)
或
create table tb1(
    id int not null,
    age int not null,
    primary key(id,age)
)
自增,如果為某列設(shè)置自增列癣缅,插入數(shù)據(jù)時(shí)無需設(shè)置此列的值厨剪,默認(rèn)將自增(表中只能有一個(gè)自增列)
create table 表名(
    id int auto_increment primary key,
    age int not null
)

創(chuàng)建日期和時(shí)間類型和存值

mysql> create table tb_datas(
    -> date1 date,
    -> time2 time,
    -> yyyy year,
    -> yyyy_mm_ddThh datetime,
    -> yyyymmddhh  timestamp);

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

insert into tb_datas(date1,time2,yyyy,yyyy_mm_ddThh,yyyymmddhh) values( 20191010,"20:00:00",2019,"2019-10-10 20:00:00","2019-10-11 20:00:00");

查詢到的數(shù)據(jù)

mysql> select * from tb_datas\G
*************************** 1. row ***************************
        date1: 2019-10-10
        time2: 20:00:00
         yyyy: 2019
yyyy_mm_ddThh: 2019-10-10 20:00:00
   yyyymmddhh: 2019-10-11 20:00:00
1 row in set (0.00 sec)
  • 創(chuàng)建表時(shí),定義外鍵,一對(duì)多關(guān)系

首先創(chuàng)建主表友存,主表就是祷膳,表中的一條數(shù)據(jù)會(huì)對(duì)應(yīng)到從表中的多條數(shù)據(jù),比如班級(jí)表中的一條數(shù)據(jù)就代表了一個(gè)班級(jí)屡立,一個(gè)班級(jí)中可以有多個(gè)學(xué)生直晨,所以班級(jí)表就是主表,學(xué)生表就是從表膨俐。

那需要先創(chuàng)建一個(gè)主表: class

# 首先切換到一個(gè)庫(kù)中再創(chuàng)建表
mysql> use  db1;

# 接著創(chuàng)建表
create table class(
id  int auto_increment primary key,
name varchar(12)
)

接下來可以創(chuàng)建從表了勇皇,并在創(chuàng)建從表的時(shí)候設(shè)置外鍵

create table student2(
    id int auto_increment primary key,
    class_id int,
    foreign key(class_id) REFERENCES class(id)
);

創(chuàng)建表時(shí),定義多個(gè)外鍵焚刺,多對(duì)多關(guān)系(擴(kuò)展)

create table class2teacher(
    id int auto_increment primary key, 
    class_id int, teacher_id int, 
    foreign key(class_id) REFERENCES class(id),
    foreign key(teacher_id) references teacher(id)
);

b. 查看當(dāng)前數(shù)據(jù)庫(kù)中所有表

mysql> show tables;
+--------------------+
| Tables_in_shark_db |
+--------------------+
| student            |
+--------------------+
1 row in set (0.00 sec)

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

/*查看當(dāng)前庫(kù)中指定表的表結(jié)構(gòu)*/
mysql> desc 表名;

/*查看指定庫(kù)中指定表的表結(jié)構(gòu)*/
mysql> desc 庫(kù)名.表名;

d. 清空表的內(nèi)容敛摘,表本身還在。

delete from 表名    /*自增列的值繼續(xù)遞增乳愉,可以加 where 子句*/
truncate table 表名  /*自增列的值重新從 1 開始*/
/*刪除表中的所有數(shù)據(jù)兄淫,自增列的值繼續(xù)遞增*/
delete from tb1;

/*刪除表中的某些數(shù)據(jù),被刪除的數(shù)據(jù)的自增列的值將不會(huì)再次出現(xiàn)蔓姚,自增列的值繼續(xù)遞增*/
delete  from  tb1   where id < 20;

e. 刪除表拖叙,表將消失

drop table  表名     /*整個(gè)表將不復(fù)存在*/

f. 修改表結(jié)構(gòu)(以下自修)

  • 添加列
alter table 表名 add 列名 類型

示例:
alter table stadent add gender Enum("男", "女");
alter table stadent add hobby set("girl","car","yacht");
update student set hobby = "girl,car";

  • 刪除列
alter table 表名 drop column 列名
  • 修改列
修改類型
alter table 表名 modify column 列名 類型;  

修改列名和類型
alter table 表名 change 原列名 新列名 類型;
  • 添加主鍵
alter table 表名 add primary key(列名;
alter table students add id int not null auto_increment, add primary key (id);
  • 刪除主鍵
 alter table 表名 drop primary key;
    - 刪除主鍵屬性,保留原值和列
    alter table 表名  modify  列名 int, drop primary key;
  • 添加一個(gè)列的唯一屬性
alter table student2 modify column email varchar(32) unique;
  • 修改自增列
 修改自增屬性的列赂乐,必須具備主鍵的屬性薯鳍。
   alter table 表名 modify column id int AUTO_INCREMENT;
  • 外鍵的添加和刪除

向從表對(duì)一個(gè)字段增加外鍵屬性時(shí),從表中的這個(gè)字段必須已經(jīng)存在,且不能有數(shù)據(jù)挖滤。

注意:
從表的的外鍵列的屬性必須主表的主鍵列名屬性一致崩溪。

添加外鍵:
alter table 從表 add constraint 外鍵名稱(形如:FK_從表_主表) foreign key 從表(從表的列名) references 主表(主鍵列名);

刪除外鍵:
alter table 表名 drop foreign key 外鍵名稱

  • 默認(rèn)值的修改和刪除
修改默認(rèn)值:ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;

刪除默認(rèn)值:ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個(gè)濱河市斩松,隨后出現(xiàn)的幾起案子伶唯,更是在濱河造成了極大的恐慌,老刑警劉巖惧盹,帶你破解...
    沈念sama閱讀 222,627評(píng)論 6 517
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件乳幸,死亡現(xiàn)場(chǎng)離奇詭異,居然都是意外死亡钧椰,警方通過查閱死者的電腦和手機(jī)粹断,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 95,180評(píng)論 3 399
  • 文/潘曉璐 我一進(jìn)店門,熙熙樓的掌柜王于貴愁眉苦臉地迎上來嫡霞,“玉大人瓶埋,你說我怎么就攤上這事≌锘Γ” “怎么了养筒?”我有些...
    開封第一講書人閱讀 169,346評(píng)論 0 362
  • 文/不壞的土叔 我叫張陵,是天一觀的道長(zhǎng)端姚。 經(jīng)常有香客問我晕粪,道長(zhǎng),這世上最難降的妖魔是什么渐裸? 我笑而不...
    開封第一講書人閱讀 60,097評(píng)論 1 300
  • 正文 為了忘掉前任兵多,我火速辦了婚禮,結(jié)果婚禮上橄仆,老公的妹妹穿的比我還像新娘剩膘。我一直安慰自己,他們只是感情好盆顾,可當(dāng)我...
    茶點(diǎn)故事閱讀 69,100評(píng)論 6 398
  • 文/花漫 我一把揭開白布怠褐。 她就那樣靜靜地躺著,像睡著了一般您宪。 火紅的嫁衣襯著肌膚如雪奈懒。 梳的紋絲不亂的頭發(fā)上,一...
    開封第一講書人閱讀 52,696評(píng)論 1 312
  • 那天宪巨,我揣著相機(jī)與錄音磷杏,去河邊找鬼。 笑死捏卓,一個(gè)胖子當(dāng)著我的面吹牛极祸,可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播,決...
    沈念sama閱讀 41,165評(píng)論 3 422
  • 文/蒼蘭香墨 我猛地睜開眼遥金,長(zhǎng)吁一口氣:“原來是場(chǎng)噩夢(mèng)啊……” “哼浴捆!你這毒婦竟也來了?” 一聲冷哼從身側(cè)響起稿械,我...
    開封第一講書人閱讀 40,108評(píng)論 0 277
  • 序言:老撾萬榮一對(duì)情侶失蹤选泻,失蹤者是張志新(化名)和其女友劉穎,沒想到半個(gè)月后美莫,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體页眯,經(jīng)...
    沈念sama閱讀 46,646評(píng)論 1 319
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡,尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 38,709評(píng)論 3 342
  • 正文 我和宋清朗相戀三年厢呵,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了窝撵。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點(diǎn)故事閱讀 40,861評(píng)論 1 353
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡述吸,死狀恐怖,靈堂內(nèi)的尸體忽然破棺而出锣笨,到底是詐尸還是另有隱情蝌矛,我是刑警寧澤,帶...
    沈念sama閱讀 36,527評(píng)論 5 351
  • 正文 年R本政府宣布错英,位于F島的核電站入撒,受9級(jí)特大地震影響,放射性物質(zhì)發(fā)生泄漏椭岩。R本人自食惡果不足惜茅逮,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 42,196評(píng)論 3 336
  • 文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望判哥。 院中可真熱鬧献雅,春花似錦、人聲如沸塌计。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,698評(píng)論 0 25
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽锌仅。三九已至章钾,卻和暖如春,著一層夾襖步出監(jiān)牢的瞬間热芹,已是汗流浹背贱傀。 一陣腳步聲響...
    開封第一講書人閱讀 33,804評(píng)論 1 274
  • 我被黑心中介騙來泰國(guó)打工, 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留伊脓,地道東北人府寒。 一個(gè)月前我還...
    沈念sama閱讀 49,287評(píng)論 3 379
  • 正文 我出身青樓,卻偏偏與公主長(zhǎng)得像,于是被迫代替她去往敵國(guó)和親椰棘。 傳聞我的和親對(duì)象是個(gè)殘疾皇子纺棺,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 45,860評(píng)論 2 361

推薦閱讀更多精彩內(nèi)容