數(shù)據(jù)庫(kù)中的默認(rèn)庫(kù)

INFORMATION_SCHEMA提供對(duì)數(shù)據(jù)庫(kù)元數(shù)據(jù)的訪問(wèn) ，有關(guān)MySQL服務(wù)器的信息谁不，例如數(shù)據(jù)庫(kù)或表的名稱(chēng)痰憎，列的數(shù)據(jù)類(lèi)型或訪問(wèn)權(quán)限。有時(shí)用于此信息的其他術(shù)語(yǔ)是 數(shù)據(jù)字典和 系統(tǒng)目錄珍策。庫(kù)中表詳情

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

prompt="MySQL [\d]> "
no-auto-rehash

image.png

常用操作語(yǔ)句

-- 查看當(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ù)名稱(chēng) DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
 
# gbk
CREATE DATABASE 數(shù)據(jù)庫(kù)名稱(chēng) DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

Example

mysql> create database teacher default charset utf8mb4 collate utf8mb4_general_ci;

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

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

Example

use teacher;

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

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

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

mysql> show  create database teacher\G

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

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

Example

drop database teacher;

二蹭劈、 表的管理

1. MySQL 表的基本概念

MySQL 的表存放了實(shí)際的數(shù)據(jù)。

表大體可以分為表頭和表體线召， 一張表只有一個(gè)表頭铺韧，也就是一行數(shù)據(jù)，
這行數(shù)據(jù)的每一列的值成為字段缓淹。

表體可以有多行數(shù)據(jù)組成哈打，每一行代表了一個(gè)要表現(xiàn)的實(shí)際對(duì)象。

每個(gè)字段代表了一條數(shù)據(jù)都有那些屬性讯壶，比如存一個(gè)學(xué)生的信息料仗，需要有姓名、年齡伏蚊、學(xué)號(hào)等立轧。

對(duì)于這些屬性，你會(huì)發(fā)現(xiàn)它們是不同類(lèi)型的數(shù)據(jù)躏吊，比如 姓名 是普通的文本氛改，就是字符串，而年齡就是數(shù)字比伏，也就是整型平窘。

為了保證這些數(shù)據(jù)存放到數(shù)據(jù)庫(kù)時(shí)，不會(huì)出現(xiàn)類(lèi)型的錯(cuò)誤而導(dǎo)致以后影響使用凳怨，在創(chuàng)建表時(shí)，必須對(duì)每個(gè)字段指定要存放數(shù)據(jù)的數(shù)據(jù)類(lèi)型是鬼。

學(xué)生表

2. MySQL 中的數(shù)據(jù)類(lèi)型

int(M)[UNSIGNED][ZEROFILL]
             UNSIGNED 表示符號(hào)
             ZEROFILL   表示不夠的位使用 0 填充
          number  int(2)  UNSIGNED  ZEROFILL 
         number 的值假如你存1肤舞，顯示的時(shí)候不夠 2 位，就會(huì)使用 0 填充均蜜，變成 02
            整數(shù)李剖，數(shù)據(jù)類(lèi)型用于保存一些范圍的整數(shù)數(shù)值范圍：
                有符號(hào)：
                    -2147483648 ～ 2147483647
                無(wú)符號(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ù)篙顺。
                無(wú)符號(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ù)類(lèi)型用于表示固定長(zhǎng)度的字符串，可以包含最多達(dá)255個(gè)字符德玫。其中m代表字符串的長(zhǎng)度匪蟀。
            PS: 即使數(shù)據(jù)小于m長(zhǎng)度，也會(huì)占用m長(zhǎng)度
varchar(m)
            varchars數(shù)據(jù)類(lèi)型用于變長(zhǎng)的字符串宰僧，可以包含最多達(dá)255個(gè)字符材彪。其中m代表該數(shù)據(jù)類(lèi)型所允許保存的字符串的最大長(zhǎng)度，只要長(zhǎng)度小于該最大值的字符串都可以被保存在該數(shù)據(jù)類(lèi)型中琴儿。

            注：雖然varchar使用起來(lái)較為靈活段化，但是從整個(gè)系統(tǒng)的性能角度來(lái)說(shuō)，char數(shù)據(jù)類(lèi)型的處理速度更快造成，有時(shí)甚至可以超出varchar處理速度的50%显熏。因此，用戶在設(shè)計(jì)數(shù)據(jù)庫(kù)時(shí)應(yīng)當(dāng)綜合考慮各方面的因素晒屎，以求達(dá)到最佳的平衡

text
            text數(shù)據(jù)類(lèi)型用于保存變長(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.

/* 定義數(shù)據(jù)庫(kù)列時(shí)夷磕，可以使用Enum（Enumeration履肃，枚舉）和SET（集合）類(lèi)型：變通的實(shí)現(xiàn)CHECK約束

兩者的區(qū)別是：

　　使用Enum，只能選一個(gè)值坐桩；

　　使用SET尺棋，可以選多個(gè)值；

Enum和SET中的值都必須是字符串類(lèi)型绵跷。

注意：

　　在內(nèi)部存儲(chǔ)Enum值時(shí)膘螟，MYSQL給Enum中的每個(gè)值一個(gè)順序號(hào)碼：第一個(gè)值的順序號(hào)碼是1，第二個(gè)值的順序號(hào)碼是2碾局，以此類(lèi)推荆残。當(dāng)排序或比較Enum的時(shí)候，使用這些順序號(hào)碼進(jìn)行净当。
*/

Enum
            枚舉類(lèi)型内斯，
            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
            集合類(lèi)型
            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');

關(guān)于日期和時(shí)間類(lèi)型的字段

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

DATE
            YYYY-MM-DD（1000-01-01/9999-12-31）

TIME
            HH:MM:SS（'-838:59:59'/'838:59:59'）

YEAR
            YYYY（1901/2155）

DATETIME

            YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59    Y）

TIMESTAMP

            YYYYMMDD HHMMSS（1970-01-01 00:00:00/2038-01-19 11:14:07）

時(shí)間默認(rèn)值

--insert時(shí)和update時(shí)若不指定updated值，則插入當(dāng)前時(shí)間
       CREATE TABLE test_up_dt (
           id int PRIMARY KEY,
            last_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
         ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP # 這個(gè)是更新的時(shí)候也會(huì)自動(dòng)記錄當(dāng)時(shí)的時(shí)間
DEFAULT CURRENT_TIMESTAMP # 這個(gè)只有在第一次創(chuàng)建時(shí)間的時(shí)候填充當(dāng)前時(shí)間像啼。

創(chuàng)建表

create table phy_server(
  id int auto_increment primary key,
  host_name  varchar(36)  unique key not null, 
  server_model  varchar(96) not null,
  server_sn varchar(128) not null,
  server_vendor varchar(96) not null  comment "廠商",
  board_sn varchar(96),
  bios_version varchar(96) not null,
  os_version varchar(96) not null,
  kernel_version varchar(96) not null,
  cpu_phy_num int,
  cpu_core_of_phy int,
  cpu_model  varchar(32),
  add_date datetime,
  change_date  datetime default now()
);

添加數(shù)據(jù)

insert  into phy_server(
    host_name,server_model,server_sn,
    server_vendor,
    board_sn,
    bios_version,
    os_version,
    kernel_version,
    cpu_phy_num,
    cpu_core_of_phy,
    cpu_model,
    add_date
)
values(
'kvm-docker',
'PowerEdge_R710',
'PowerEdge_R720',
"4c4c4544-0059",
"Dell_Inc",
"CN1374006T00B7",
"Centos7",
"3.4",
2,
4,
"Intel_E5506",
"20190819"
)

增加多條數(shù)據(jù)

insert  into phy_server(
    host_name,server_model,server_sn,
    server_vendor,
    board_sn,
    bios_version,
    os_version,
    kernel_version,
    cpu_phy_num,
    cpu_core_of_phy,
    cpu_model,
    add_date
)
values(
'redis1',
'PowerEdge_R710',
'PowerEdge_R720',
"4c4c4544-0059",
"Dell_Inc",
"CN1374006T00B7",
"Centos7",
"3.4",
2,
4,
"Intel_E5506",
"20190819"
),(
'redis2',
'PowerEdge_R710',
'PowerEdge_R720',
"4c4c4544-0059",
"Dell_Inc",
"CN1374006T00B7",
"Centos7",
"3.4",
2,
4,
"Intel_E5506",
"20190819"
),
(
'redis3',
'PowerEdge_R710',
'PowerEdge_R720',
"4c4c4544-0059",
"Dell_Inc",
"CN1374006T00B7",
"Centos7",
"3.4",
2,
4,
"Intel_E5506",
"20190819"
)

3. MySQL 表的操作 DDL

DDL（Data Definition Language 數(shù)據(jù)定義語(yǔ)言）

用在定義或改變表（TABLE）的結(jié)構(gòu)俘闯，數(shù)據(jù)類(lèi)型，表之間的鏈接和約束等初始化工作上忽冻，他們大多在建立表時(shí)使用真朗。

DDL不需要commit（DDL都是被隱式提交，不能對(duì)DDL語(yǔ)句使用ROLLBACK命令）僧诚。

CREATE     創(chuàng)建表,在數(shù)據(jù)庫(kù)創(chuàng)建對(duì)象
ALTER      修改表,修改數(shù)據(jù)庫(kù)結(jié)構(gòu)(擴(kuò)展自修)
DROP       刪除表,從數(shù)據(jù)庫(kù)中刪除對(duì)象
TRUNCATE   刪除表中所有行（無(wú)法回退）
COMMENT    注釋?zhuān)閿?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)致處理方式蝗碎、提供的特性操作等不同
  常見(jiàn)的引擎：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ì)見(jiàn)手冊(cè))

a. 創(chuàng)建表

create table 表名[ IF NOT EXISTS](
    列名  類(lèi)型  [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'],
    列名  類(lèi)型
)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表示空琼锋，并不是字符串類(lèi)型
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í)無(wú)需設(shè)置此列的值急凰，默認(rèn)將自增（表中只能有一個(gè)自增列）
create table 表名(
    id int auto_increment primary key,
    age int not null
)

Example

create table student(
    id int  auto_increment primary key,
    name varchar(12) not null,
    age int not null,
    phone char(11)
)

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

創(chuàng)建

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)
)

接下來(lái)可以創(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 開(kāi)始*/

/*刪除表中的所有數(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ù)存在*/

作業(yè)

創(chuàng)建三個(gè)表

class
 id
 name

student
id
name
gender   枚舉(不強(qiáng)制要求)
hobby    集合(不強(qiáng)制要求)  有默認(rèn)值 (大寶劍)
class_id  外鍵  
teacher_id 外鍵

teacher
id
name
phone  11位電話號(hào)碼 可以是空值

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

• 添加列

alter table 表名 add 列名 類(lèi)型

示例：
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 列名

• 修改列

修改類(lèi)型
alter table 表名 modify column 列名 類(lèi)型;  

修改列名和類(lèi)型
alter table 表名 change 原列名 新列名 類(lèi)型;

• 添加主鍵

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 外鍵名稱(chēng)（形如：FK_從表_主表） foreign key 從表(從表的列名) references 主表(主鍵列名);

刪除外鍵：
alter table 表名 drop foreign key 外鍵名稱(chēng)

注意：
從表的的外鍵列的屬性必須主表的主鍵列名屬性一致闭专。

• 默認(rèn)值的修改和刪除

修改默認(rèn)值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;

刪除默認(rèn)值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

• 修改字符集(編碼)

修改數(shù)據(jù)庫(kù)字符集：

ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE ...];

把表默認(rèn)的字符集和所有字符列（CHAR,VARCHAR,TEXT）改為新的字符集：

ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE ...] 

如：
ALTER TABLE logtest CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

只是修改表的默認(rèn)字符集：

ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE...]; 

如：
ALTER TABLE student DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

查看數(shù)據(jù)庫(kù)字符集：

SHOW CREATE DATABASE db_name;

查看表字符集：

SHOW CREATE TABLE tbl_name;

設(shè)置存儲(chǔ)引擎

MySQL 5.7中 ENGINE = INNODB 是數(shù)據(jù)庫(kù)默認(rèn)的

配置文件 my.cnf 中指定數(shù)據(jù)庫(kù)服務(wù)器默認(rèn)存儲(chǔ)引擎

default-storage-engine=InnoDB

創(chuàng)建新表時(shí)，可以通過(guò)向語(yǔ)句添加ENGINE表選項(xiàng)來(lái) 指定要使用的存儲(chǔ)引擎

CREATE TABLE t3 (i INT) ENGINE = MEMORY

修改一個(gè)表的存儲(chǔ)引擎

ALTER TABLE t ENGINE = InnoDB;