數(shù)據(jù)庫管理
新建數(shù)據(jù)庫
CREATE DATABASE [IF NOT EXISTS] 數(shù)據(jù)庫名
實(shí)例：
CREATE DATABASE IF NOT EXISTS MySQLDB;
數(shù)值類型
日期類型：
字符類型：
特殊字符序列
約束
重要，記下
非空約束（NOT NULL）
強(qiáng)制列不能為 NULL 值虫埂，約束強(qiáng)制字段始終包含值俱萍。這意味著，如果不向字段添加值告丢，就無法插入新記錄或者更新記錄
唯一約束（UNIQUE）
- 唯一約束可以保證記錄的唯一性枪蘑，即就是同一個(gè)表中，相同字段的值不會(huì)出現(xiàn)重復(fù)岖免。
- 唯一約束的字段可以為空值（NULL）岳颇。
- 每一張數(shù)據(jù)表可以存在多個(gè)唯一約束字段。
主鍵約束（PRIMARY KEY）
數(shù)據(jù)庫表要求表中的每一行記錄都必須是唯一的颅湘，即在同一張表中不允許出現(xiàn)完全相同的兩條記錄话侧。在設(shè)計(jì)數(shù)據(jù)庫時(shí)，為了保證記錄的“唯一性”闯参，最為普遍瞻鹏、最為推薦的做法是為表定義一個(gè)主鍵（primary key）悲立。數(shù)據(jù)庫表中主鍵有以下兩個(gè)特征：
- 表的主鍵可以由一個(gè)字段構(gòu)成，也可以由多個(gè)字段構(gòu)成（這種情況稱為復(fù)合主鍵）新博。
- 數(shù)據(jù)庫表中主鍵的值具有唯一性且不能取空值（NULL）薪夕，當(dāng)數(shù)據(jù)庫表中的主鍵由多個(gè)字段構(gòu)成時(shí)，每個(gè)字段的值不能取NULL值赫悄。
CREATE TABLE classes (
classes_id INT PRIMARY KEY,
classes_name VARCHAR(10),
classes_dept varchar(10)
);
外鍵約束（FOREIGN KEY）
- 班級(jí)實(shí)體和班主任實(shí)體之間為一對一關(guān)系原献，班級(jí)實(shí)體和學(xué)生實(shí)體之間為一對多關(guān)系，學(xué)生實(shí)體和課程實(shí)體之間為多對多關(guān)系埂淮。
- 實(shí)體間的關(guān)系可以通過外鍵來表示姑隅。如果表A中的一個(gè)字段a對應(yīng)于表B的主鍵b，則字段a稱為表A的外鍵倔撞。此時(shí)存儲(chǔ)在表A中字段a的值讲仰，同時(shí)這個(gè)字段值也是表B主鍵b的值。
CREATE TABLE teacher (
teacher_id INT PRIMARY KEY ,
teacher_name VARCHAR(10),
cid INT ,
FOREIGN KEY(cid) REFERENCES classes(classes_id)
);
練習(xí)
-- 學(xué)校想做一個(gè)選課系統(tǒng)痪蝇，其中涉及到課程表叮盘，學(xué)生表，
-- 請分別創(chuàng)建這兩個(gè)表霹俺，自己思考表中應(yīng)有的列及數(shù)據(jù)類型柔吼。
-- 學(xué)校有一個(gè)選課系統(tǒng)，其中包括如下關(guān)系模式：
-- 系(系編號(hào)： 主鍵丙唧，系名稱: 唯一鍵愈魏，系主任： 非空約束，系所在校去：默認(rèn)為渾南區(qū))
-- 班級(jí)(班級(jí)編號(hào)： 主鍵想际，班級(jí)名稱： 唯一鍵培漏，所屬系： 外鍵)
-- 創(chuàng)建學(xué)生表，包含如下屬性:
-- 學(xué)號(hào) 定長字符型 10位 主鍵
-- 姓名 變長字符型 20位 非空
-- 性別 定長字符型 2位
-- 出生日期 日期型
-- 所在班級(jí)
CREATE TABLE
IF NOT EXISTS xi (
XINumber VARCHAR (20) NOT NULL PRIMARY KEY,
XIName VARCHAR (20) NOT NULL UNIQUE,
XIZhuren VARCHAR (20) NOT NULL,
Xiaoqu enum ('渾南區(qū)')
);
CREATE TABLE
IF NOT EXISTS class (
classid VARCHAR (20) NOT NULL PRIMARY KEY,
cname VARCHAR (20) NOT NULL UNIQUE,
xiname VARCHAR (10) NOT NULL,
FOREIGN KEY (xiname) REFERENCES xi (xiname)
);
CREATE TABLE
IF NOT EXISTS student (
xh VARCHAR (20) NOT NULL PRIMARY KEY,
xm VARCHAR (20) NOT NULL,
gender CHAR (2),
birthday DATE,
cname VARCHAR (20) NOT NULL UNIQUE,
FOREIGN KEY (cname) REFERENCES class (cname)
);
刪除數(shù)據(jù)庫
- DROP DATABASE語句的語法如下：
DROP DATABASE 數(shù)據(jù)庫名
- 可以在MySQL命令行工具中使用下面語句刪除數(shù)據(jù)庫MySQLDB：
DROP DATABASE MySQLDB;
復(fù)制表
- 方法一：在CREATE TABLE語句的末尾添加LIKE子句胡本，可以將源表的表結(jié)構(gòu)復(fù)制到新表中牌柄，語法格式如下。
CREATE TABLE 新表名 LIKE 源表
- 方法二侧甫、在CREATE TABLE 語句的末尾添加一個(gè)SELECT語句珊佣，可以實(shí)現(xiàn)表結(jié)構(gòu)的復(fù)制，甚至可以將源表的表記錄拷貝到新表中披粟。下面的語法格式將源表的表結(jié)構(gòu)以及源表的所有記錄拷貝到新表中咒锻。
CREATE TABLE 新表名 SELECT - FROM 源表
CREATE TABLE emphaha select * from emp;
刪除表
使用DROP TABLE語句刪除表，語法如下：
DROP TABLE 表名
表的重命名
RENAME TABLE 舊表名 TO 新表名
等效
ALTER TABLE person RENAME human;
創(chuàng)建與dept表相同表結(jié)構(gòu)的表dtest守屉，將dept表中部門編號(hào)在40之前的信息插入該表惑艇。
CREATE TABLE dtest SELECT * from dept WHERE DEPTNO < 40;
創(chuàng)建與emp表結(jié)構(gòu)相同的表empl，并將其部門編號(hào)為前30號(hào)的員工信息復(fù)制到empl表。
CREATE TABLE empl SELECT * from emp WHERE DEPTNO < 30;
數(shù)據(jù)操作與事務(wù)控制
- Data Manipulation Language ,簡稱DML滨巴，主要用來實(shí)現(xiàn)對數(shù)據(jù)庫表中的數(shù)據(jù)進(jìn)行操作思灌。
- 數(shù)據(jù)操作語言主要包括如下幾種：
增加行數(shù)據(jù)：使用INSERT語句實(shí)現(xiàn)
修改行數(shù)據(jù)：使用UPDATE語句實(shí)現(xiàn)
刪除行數(shù)據(jù)：使用DELETE語句實(shí)現(xiàn)
插入數(shù)據(jù)
INSERT INTO table [(column [, column...])]
VALUES (value [, value...]);
- 采用這種語法一次只能追加一條記錄；
- column部分叫做列名列表恭取，value部分叫做值列表泰偿，列名列表和值列表必須在個(gè)數(shù)及數(shù)據(jù)類型上保持一致；
- 列名列表部分可以省略秽荤，如果省略，默認(rèn)包括該表的所有列柠横，列的順序?yàn)槭褂?desc 表名 命令所查看的順序窃款；
- 列名列表部分也可以指定部分非空的列，注意值列表必須和列名列表對應(yīng)牍氛；
- 字符和日期型數(shù)據(jù)必須要用單引號(hào)括起來
INSERT into dept VALUES(50, 'IT', '北京')
插入空值NULL
INSERT into dept VALUES(60, 'Test', null);
向部門表新增一個(gè)部門晨继，部門編號(hào)為50，部門名稱為HR搬俊，工作地點(diǎn)為SY紊扬。
insert into dept values(50,"HR","SY");
向部門表新增一個(gè)部門，部門編號(hào)為60唉擂，部門名稱為MARKET餐屎。
insert into dept values(60,"MARKET","");
插入日期值
SYSDATE()
insert into emp values(8888,'haha','該溜子',null,SYSDATE(),4000,12,50 );
插入日期值
insert into emp values(8889,'李會(huì)長','小青年',null,'1888-01-01
',4000,12,50 );
向員工表中新增一個(gè)員工，員工編號(hào)為8888玩祟，姓名為BOB腹缩，崗位為CLERK，經(jīng)理為號(hào)7788空扎，入職日期為1985-03-03藏鹊，薪資3000，獎(jiǎng)金和部門為空转锈。
insert into emp values(8899, 'BOB', 'clerk', 7788, '1985-03-03', 3000, null, null);
批量插入數(shù)據(jù)
INSERT INTO EMP(EMPNO, ENAME, JOB, SAL)
VALUES
('8881', '張三', '部門經(jīng)理', 6000),
('8882', '李四', '職員', 3000),
('8883', '王五', '職員', 3500),
('8884' ,'趙六', '部門經(jīng)理', 6500),
('8885', '高七', '職員', 2500),
('8886', '馬八', '職員', 3100),
('8887', '錢九', '部門經(jīng)理', 5000),
('8888', '孫十', '職員', 2800);
通過子查詢插入多行數(shù)據(jù)
- INSERT子句和數(shù)據(jù)類型必須和子查詢中列的數(shù)量和類型相匹配中列的數(shù)量
創(chuàng)建一個(gè)manager表(where 1 = 0 的含義是只復(fù)制表結(jié)構(gòu)盘寡，而不復(fù)制表的內(nèi)容)
create table manager as select * from emp where 1=0;
插入數(shù)據(jù)， 全部工作為salesman的數(shù)據(jù)
insert into manager select * from emp where job='SALESMAN';
使用CREATE TABLE emp_back as SELECT - FROM EMP WHERE 1=0撮慨，創(chuàng)建emp_back表,拷貝下來即可竿痰。
create table emp_back as select * from emp where 1=0;
把emp表中入職日期大于1982年1月1日之前的員工信息復(fù)制到emp_back表中
insert into manager select * from emp where hiredate >'1982-01-01';
修改數(shù)據(jù)
UPDATE table
SET column = value [, column = value]
[WHERE condition];
Mysql日期計(jì)算
- DATE_ADD(date,INTERVAL expr unit) 返回一個(gè)日期加上指定的時(shí)間單元
DATE_ADD(HIREDATE,INTERVAL '10' DAY) 給hiredate加10天
- DATE_SUB(date,INTERVAL expr unit) 返回一個(gè)日期減去指定的時(shí)間單元
DATE_SUB(HIREDATE,INTERVAL '10' DAY) 給hiredate減10天
unit 可選字段:
修改部門20的員工信息，把82年之后入職的員工入職日期向后調(diào)整10天
UPDATE emp
SET hiredate = DATE_ADD(HIREDATE,INTERVAL '10' DAY)
WHERE
deptno = 20
AND hiredate > '1982-12-31';
修改獎(jiǎng)金為null的員工砌溺，獎(jiǎng)金設(shè)置為0
update emp set comm = 0 where comm is null;
修改工作地點(diǎn)在NEW YORK或CHICAGO的員工工資菇曲，工資增加500
update dept d ,emp e set e.sal = e.sal + 500 where
e.deptno = d.deptno and d.loc in('NEW YORK','CHICAGO');
刪除數(shù)據(jù)語法
DELETE FROM table
[WHERE condition];
截?cái)啾碚Z法
TRUNCATE TABLE table;
TRUNCATE和DELETE區(qū)別
TRUNCATE是DDL，只能刪除表中所有記錄抚吠，釋放存儲(chǔ)空間常潮，使用ROLLBACK不可以回滾。
DELETE是DML楷力，可以刪除指定記錄喊式，不釋放存儲(chǔ)空間孵户，使用ROLLBACK可以回滾。
刪除經(jīng)理編號(hào)為7566的員工記錄
DELETE FROM empl where mgr = 7566;
刪除工作在NEW YORK的員工記錄
DELETE FROM empl where deptno in(select deptno from deptl where loc = 'NEW YORK');
刪除工資大于所在部門平均工資的員工記錄
DELETE FROM emp_copy WHERE empno in (select * from (SELECT
a.empno
FROM
emp_copy AS a,
(
SELECT
deptno,
AVG(sal) AS avgsal
FROM
emp_copy
GROUP BY
deptno
) as b
WHERE
a.deptno = b.deptno
AND a.sal > b.avgsal) as query)
delete from emp22 where sal>(select avg(sal) from emp e where emp22.deptno=e.deptno);
Mysql 事物
在數(shù)據(jù)庫中岔留，事務(wù)由一組相關(guān)的DML或SELECT語句夏哭，加上一個(gè)TPL語句（COMMIT、ROLLBACK）或一個(gè)DDL語句(CREATE献联、ALTER竖配、DROP、TRUNCATE等)或一個(gè)DCL（GRANT里逆、REVOKE）語句进胯。
事務(wù)特征可用四個(gè)字母的縮寫表示：即ACID
- 原子性（Atomicity）
事務(wù)就像“原子”一樣，不可被分割原押，組成事務(wù)的DML操作語句要么全成功胁镐，要么全失敗，不可能出現(xiàn)部分成功部分失敗的情況诸衔。- 一致性（Consistency）
一旦事務(wù)完成盯漂，不管是成功的，還是失敗的笨农，整個(gè)系統(tǒng)處于數(shù)據(jù)一致的狀態(tài)就缆。- 隔離性（Isolation）
一個(gè)事務(wù)的執(zhí)行不會(huì)被另一個(gè)事務(wù)所干擾。比如兩個(gè)人同時(shí)從一個(gè)賬戶從取錢谒亦，通過事務(wù)的隔離性確保賬戶余額的正確性违崇。- 持久性（Durability）
也稱為永久性，指事務(wù)一旦提交诊霹，對數(shù)據(jù)的改變就是永久的羞延，不可以再被回滾。
存儲(chǔ)引擎
- InnoDB存儲(chǔ)引擎的特點(diǎn):
1.支持外鍵（Foreign Key）
2.支持事務(wù)（Transaction）：如果某張表主要提供OLTP支持脾还，需要執(zhí)行大量的增伴箩、刪、改操作（insert鄙漏、delete嗤谚、update語句），出于事務(wù)安全方面的考慮怔蚌，InnoDB存儲(chǔ)引擎是更好的選擇巩步。
3.最新版本的MySQL已經(jīng)開始支持全文檢索。 - MyISAM存儲(chǔ)引擎的特點(diǎn)：
MyISAM具有檢查和修復(fù)表的大多數(shù)工具桦踊。
MyISAM表可以被壓縮
MyISAM表最早支持全文索引
但MyISAM表不支持事務(wù)
但MyISAM表不支持外鍵（Foreign Key）椅野。
如果需要執(zhí)行大量的select語句，出于性能方面的考慮，MyISAM存儲(chǔ)引擎是更好的選擇竟闪。
事物演示
建立一個(gè)account表
CREATE TABLE `account` (
`id` int(12) NOT NULL AUTO_INCREMENT,
`name` varchar(12) NOT NULL,
`balance` int(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
- 1.手動(dòng)開啟事物
start transaction ;
-
2.執(zhí)行一系列操作
給張三賬戶轉(zhuǎn)50塊錢
