技術文章 » MySQL—DDL操作1(創、查、刪、改數據庫和表)

MySQL—DDL操作1(創、查、刪、改數據庫和表)

2020-08-12 17:30:03

MySQL—DDL操作1(創、查、刪、改數據庫和表)

知識大綱

  • SQL語句的分類（什麼是DDL）

  • 操作Database

  • 表結構操作TABLE

學習任務

  • 操作Database
    1. 建立數據庫
    2. 檢視有哪些數據庫
    3. 刪除數據庫
    4. 選擇數據庫
    5. 檢視當前正在使用哪個數據庫
  • 表結構操作TABLE
    1. 檢視當前數據庫所有表
    2. 建立表結構
    3. 檢視錶結構
    4. 刪除表結構
    5. 修改表結構
  • 小練習

SQL語句分類

名字 型別 作用的物件 作用
DDL 英文全稱 (Data Definition Language) 數據定義語言 庫、表、列 建立、刪除、修改、庫或表結構，對數據庫或表的結構操作
DQL 英文全稱(Data Query Language) 數據查詢語言 數據庫記錄（數據） 查、用來查詢數據，對錶記錄的查詢
DCL 英文全稱(Data Control Language) 數據控制語言 數據庫使用者 用來定義存取的許可權和安全級別，對使用者的建立，及授權
DML 英文全稱(Data Manipulation Language 數據操作語言 數據庫記錄（數據） 增、刪、改，對錶記錄進行更新(增、刪、改)

DDL[數據定義語言]

DDL 用於結構定義、操作方法定義等。

包括：數據段、數據庫、表、列、索引等數據庫物件操作。

主要的語句關鍵字包括： create drop alter等

操作Database

注意：database 不能改名。一些視覺化工具可以改名，它是建新庫,把所有表複製到新 庫,再刪舊庫完成的。

在这里插入图片描述

1.建立數據庫

create database 數據庫名 [charset 字元集]; (關鍵字大寫效果：CREATE DATABASE 數據庫名;)

如果不指定字元集，則按照安裝 mysql 服務時選擇的預設字元集。例如：

#建立數據庫
#方式-1：直接建立[採用MySQL預設字元集latin1 -- cp1252 West European]
CREATE DATABASE mysql03;

#方式-2：建立並指定字元集[utf8 -- UTF-8 Unicode]
CREATE DATABASE mysql04 CHARACTER SET utf8;

#檢視校對規則
SHOW CHARACTER SET;

#方式-3：建立並指定字元集[utf8]和校對規則[utf8_general_ci]
CREATE DATABASE mysql05 CHARACTER SET utf8 COLLATE utf8_general_ci;

#建立數據庫[若數據庫不存在]
#IF EXISTS[刪除表使用過]
CREATE DATABASE IF NOT EXISTS mysql05;

2.檢視有哪些數據庫

show databases;

提示：當前使用者有許可權檢視的

#檢視有哪些數據庫
SHOW DATABASES;

3.刪除數據庫

drop database 數據庫名;

#刪除數據庫[直接刪除]
DROP DATABASE mysql03;

#刪除數據庫[判斷刪除]
DROP DATABASE IF EXISTS mysql04;
DROP DATABASE IF EXISTS mysql05;

4.選擇數據庫

use 數據庫名;

#定位數據庫
USE mysql01;

5.檢視當前正在使用哪個數據庫

select database();

注意：要操作表格和數據之前必須先說明是對哪個數據庫進行操作，否則就要對所有 物件加上「數據庫名.」。

#檢視當前正在使用的數據庫
SELECT DATABASE();

#跨數據庫查詢表數據[數據庫名.表名]
#empinfo[mysql02]
#SELECT * FROM mysql01.empinfo;
SELECT * FROM mysql02.empinfo;

表結構操作 TABLE

1.檢視當前數據庫所有表

show tables; #前面必須有 use 數據庫名語句，否則報錯

show tables from 數據庫名;

#前往mysql02數據庫
USE mysql02;#SELECT DATABASE() -> mysql02
#檢視當前數據庫所有表
SHOW TABLES;

#檢視指定數據庫的表內容
SHOW TABLES FROM mysql01;

2.建立表結構

（1）基礎版

在这里插入图片描述

（2）詳細版

在这里插入图片描述
在这里插入图片描述

#建立測試數據庫test
DROP DATABASE IF EXISTS test;
CREATE DATABASE IF NOT EXISTS test CHARACTER SET utf8;
#定位到test[若沒有定位數據庫 那麼表會被存放到當前數據庫中]
USE test;
#在test庫中建立表Student[簡單建表方式]
CREATE TABLE student
(
		stu_id INT,
		stu_name VARCHAR(20),
		stu_sex CHAR(2),
		stu_age INT
);
#插入數據
INSERT INTO student VALUES(1001,'張三','男',16);
INSERT INTO student VALUES(1002,'李四','女',17);
#查詢資訊
SELECT * FROM student;

#SELECT DATABASE();


/*
建表設定 可選參數：
ENGINE=INNODB [當前表格的引擎]
AUTO_INCREMENT=1 [增長的起始值]
DEFAULT CHARSET=utf8; [表數據的預設字元集]
*/
#複雜的建表方式
#建立表emp [要求：定義主鍵、自增欄位、非空約束、預設值]
DROP TABLE IF EXISTS emp;

CREATE TABLE emp
(
		emp_id INT PRIMARY KEY AUTO_INCREMENT,
		emp_name VARCHAR(20) NOT NULL,
		emp_sex CHAR(2) DEFAULT '男',
		emp_age INT NOT NULL
)ENGINE=INNODB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8;

#新增數據測試
INSERT INTO emp(emp_name,emp_sex,emp_age) VALUES('八戒','男',28);
INSERT INTO emp(emp_name,emp_sex,emp_age) VALUES('嫦娥','女',18);

#INSERT INTO emp(emp_name,emp_sex) VALUES('梅超風','女');
INSERT INTO emp(emp_name,emp_age) VALUES('梅超風',38);

#檢視所有內容
SELECT * FROM emp;

3.檢視錶結構

desc 表名稱;

檢視錶的定義：SHOW CREATE TABLE 表名;

#檢視錶結構
DESC emp;

#檢視錶定義[獲得表定的標準SQL程式碼]
SHOW CREATE TABLE emp;

4.刪除表結構

drop table 表名稱;

注意：

數據和結構都被刪除

所有正在執行的相關事務被提交

所有相關索引被刪除

DROP TABLE 語句不能回滾

#刪除表的 數據
DELETE FROM emp;

#刪除表的 數據和結構
DROP TABLE IF EXISTS emp;

#SELECT * FROM emp;

5.修改表結構

（1）重新命名錶

alter table 表名 rename 新表名;

rename table 表名 to 新表名;

#修改表名 語法-1[emp -> employee]
ALTER TABLE emp RENAME employee;
#emp已經被重新命名爲employee(不存在)
#SELECT * FROM emp;
SELECT * FROM employee;

#修改表名 語法-2[employee -> employees]
RENAME TABLE employee to employees;
SELECT * FROM employees;

（2）增加一列

alter table 表名 add 【column】 列名 數據型別; #預設在最後

alter table 表名 add 【column】 列名 數據型別 after 某一列;

alter table 表名 add 【column】 列名 數據型別 first;

注意：**!沒有!**alter table 表名 add 【column】 列名 數據型別 before 某一列;

#修改表 語法-1 新增列[emp_city] [預設新增爲表的最後一列]
ALTER TABLE employees ADD emp_city VARCHAR(20);

#修改表 語法-2 新增列[emp_address] [修飾"first" 使新列成爲首列]
ALTER TABLE employees ADD emp_address VARCHAR(20) FIRST;

#修改表 語法-3 新增列[emp_mail] [修飾"after 列名" 使新列插入在指定的列名後]
ALTER TABLE employees ADD emp_mail VARCHAR(20) AFTER emp_sex;

#ERROR
#ALTER TABLE employees ADD emp_xx VARCHAR(20) BEFORE emp_sex;

（3）刪除列

alter table 表名 drop 【column】 列名;

#刪除列 [將剛剛新增的"emp_mail"列刪除]
ALTER TABLE employees DROP COLUMN emp_mail;
#刪除列 [將剛剛新增的"emp_address"列刪除]
ALTER TABLE employees DROP emp_address;

SELECT * FROM employees;
DESC employees;

（4）修改列型別

alter table 表名 modify 【column】 列名 數據型別;

alter table 表名 modify 【column】 列名 數據型別 after 某一列;

alter table 表名 modify 【column】 列名 數據型別 first;

#修改列數據型別 語法-1 [emp_name varchar(20) -> varchar(30)]
ALTER TABLE employees MODIFY emp_name varchar(30);
#修改列數據型別 語法-1 [emp_age int -> float]
ALTER TABLE employees MODIFY emp_age float;

#修改列數據型別 語法-2 [使用"after 列名" 在修改型別的同時 移動列的位置到指定列的後方]
#修改emp_age列的數據型別爲int
ALTER TABLE employees MODIFY emp_age int AFTER emp_name;

#修改列數據型別 語法-3 [使用"first" 在修改型別的同時 移動列的位置到首列]
ALTER TABLE employees MODIFY emp_age float FIRST;

（5）修改列名等

alter table 表名 change 【column】 列名 新列名 數據型別

#修改列的名稱 [emp_city -> emp_address]
ALTER TABLE employees CHANGE emp_city emp_address VARCHAR(50);

練習

  • 題目：
#建立數據庫mytest1[簡單建立]
#建立數據庫mytest2 指定其編碼格式爲utf8 檢視校對規則 並選擇合適的規則
#檢視有哪些數據庫
#若數據庫mytest1存在 將其移除
#定位數據庫mytest2
#檢視當前正在使用的數據庫是否爲mytest2
#在mytest2中檢視其他數據庫的表數據


#建立學生表
#[具備以下列：學號(自增欄位)、姓名(非空約束)、性別(預設爲女)、年齡(非空約束)、地址、電話]
#[當前表格的引擎爲INNODB 增長的起始值爲1001 表數據的預設字元集爲UTF8]

#進行以下操作：
#檢視錶中所有內容
#檢視錶結構
#檢視錶定義


#修改表 修改表名[2種方式]
#修改表 新增列[city-城市 ...][3種方式]
#修改表 修改列的數據型別[name varchar2(20) -> varchar2(40) ...][3種方式]
#修改表 修改列的名[phone-tell]
#修改表 移除列[address]

#刪除表的 數據
#刪除表的 數據和結構
  • 答案：
#建立數據庫mytest1[簡單建立]
CREATE DATABASE mytest1;

#檢視校對規則
SHOW CHARACTER SET;

#建立數據庫mytest2 指定其編碼格式爲utf8 檢視校對規則 並選擇合適的規則
CREATE DATABASE mytest2 CHARACTER SET utf8 COLLATE utf8_general_ci;

#檢視有哪些數據庫
SHOW DATABASES;

#若數據庫mytest1存在 將其移除
CREATE DATABASE IF EXISTS mytest1;

#定位數據庫mytest2
USE mytest2;

#檢視當前正在使用的數據庫是否爲mytest2
SELECT DATABASE();

#在mytest2中檢視其他數據庫的表數據
SELECT * FROM mysql02.empinfo;

#建立學生表
#[具備以下列：學號(自增欄位)、姓名(非空約束)、性別(預設爲女)、年齡(非空約束)、地址、電話]
#[當前表格的引擎爲INNODB 增長的起始值爲1001 表數據的預設字元集爲UTF8]
CREATE TABLE student
(
       id VARCHAR(12) PRIMARY KEY AUTO_INCREMENT,
       name VARCHAR(30) NOT NULL,
       sex CHAR(2) DEFAULT '女',
       age INT NOT NULL,
       address VARCHAR(50),
       phone VARCHAR(11)
)ENGINE=INNODB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8;

#進行以下操作：
#檢視錶中所有內容
SELECT * FROM student;

#檢視錶結構
DESC student;

#檢視錶定義
SHOW CREATE TABLE student;

#修改表 修改表名[2種方式]
ALTER TABLE student RENAME students;
RENAME TABLE students to student;

#修改表 新增列[city-城市 ...][3種方式]
ALTER TABLE student ADD city VARCHAR(20);
ALTER TABLE student ADD column1 VARCHAR(20) FIRST;
ALTER TABLE student ADD column2 VARCHAR(20) AFTER age;

#修改表 修改列的數據型別[name varchar2(20) -> varchar2(40) ...][3種方式]
ALTER TABLE student MODIFY name varchar2(40);
ALTER TABLE student MODIFY column1 varchar2(22) AFTER name;
ALTER TABLE student MODIFY column2 varchar2(24) FIRST;

#修改表 修改列的名[phone-tell]
ALTER TABLE student CHANGE phone tell VARCHAR(11);

#修改表 移除列[address]
ALTER TABLE student DROP address;

#刪除表的 數據
DELETE FROM student;

#刪除表的 數據和結構
DROP TABLE IF EXISTS student;