在開始介紹數據庫基本操作之前，我們需要瞭解什麼是數據庫。
數據庫是按照數據結構來組織、儲存和管理數據的倉庫。是一個長期儲存在計算機內的、有組織的、可共用的、統一管理的大量數據的集合。
簡單的說，數據庫就是儲存數據的倉庫，它的作用就是儲存數據，因此，我們操作數據庫實質是想要操作數據庫的數據。因此對數據庫的操作實質就是對數據庫進行增刪改查數據。
（我們老師說三天講完數據庫相關內容，看能不能做到，哈哈哈哈）
-- 建立數據庫
CREATE DATABASE company;
-- 進入數據庫
use company;
-- 建立數據庫中的表一 offices
CREATE table offices(
officeCode int(10) UNIQUE NOT NULL,
city int(11) NOT NULL,
address VARCHAR(50),
country VARCHAR(50) NOT NULL,
postalCode VARCHAR(50) UNIQUE,
-- 設定主鍵
PRIMARY KEY(officeCode)
);
-- 建立數據庫中的表二 employees
CREATE table employees(
employeeNumber INT(11) UNIQUE NOT NULL auto_increment,
lastName VARCHAR(50) NOT NULL,
firstName VARCHAR(50) NOT NULL,
mobile VARCHAR(25) UNIQUE,
officeCode INT(10)NOT NULL,
jobTitle VARCHAR(50) NOT NULL,
birth DATETIME NOT NULL,
note VARCHAR(255),
sex VARCHAR(5),
PRIMARY KEY(employeeNumber),
-- 設定外來鍵
CONSTRAINT fk_office_employee FOREIGN KEY (officeCode) REFERENCES offices(officeCode)
);
-- 修改位置（將mobile欄位移動到officeCode之後）
ALTER table employees MODIFY mobile VARCHAR(25) AFTER officeCode;
-- 修改欄位名字（將birth欄位改名爲employee_birth）
ALTER TABLE employees CHANGE birth employee_birth DATE;
-- 修改欄位的數據型別（將sex從varchar修改爲char）
ALTER TABLE employees MODIFY sex CHAR(1) NOT NULL;
-- 刪除表中欄位(將note欄位刪除)
ALTER TABLE employees DROP note;
-- 新增新欄位（新增favoriate_activity）
ALTER TABLE employees add favoriate_activity VARCHAR(100);
-- 刪除一個作爲外來鍵的表，需要先將關聯外來鍵的關聯解除，然後刪除表
ALTER table employees DROP FOREIGN KEY fk_office_employee;
-- 刪除表
DROP TABLE offices;
-- 修改數據庫引擎
ALTER TABLE employees ENGINE=MyISAM;
-- 修改表名
ALTER TABLE employees RENAME employees_info;
-- 檢查表的情況
SELECT * FROM offices;
SELECT * FROM employees;
-- 檢查表的結構
DESC offices;
DESC employees;
上面的例題是對數據庫表進行建立修改刪除的一些簡單命令。
在列出常用命令之前還是要先對數據庫有一定的瞭解。
數據庫分爲關係型數據庫和非關係型數據庫。
關係型數據庫就是指表與表之間有很多複雜的關聯關係的數據庫。常見的關係型數據庫有MySQL、SqlServer和Oracle等。
非關係型數據庫又被稱爲NoSQL(Not Only SQL )，意爲不僅僅是SQL( Structured QueryLanguage，結構化查詢語言)，不需要事先定義結構，也就是不需要建表建庫等，每條記錄可以又不同的型別和約束條件。非關係型數據庫分爲四種：
1是使用鍵值對儲存：代表軟體爲redis
2是使用列儲存：代表軟體爲Hbase
3是文件數據庫儲存：代表軟體爲MongoDB
4是使用圖形護具庫儲存：代表軟體爲InfoGrid
建立數據庫和數據表相關的命令
建立數據庫
-- CREATE DATABASE 數據庫名
檢視數據庫
-- show DATABASES;
刪除數據庫
-- DROP DATABASE IF EXISTS 數據庫名;
切換數據庫
-- USE 數據庫名;
檢視數據庫引擎
-- show ENGINES;
檢視當前預設引擎
-- show VARIABLES like 'storage_engine';
建立表
-- CREATE table 表名(
-- 列名 數據型別 [約束] [預設值]
-- )[ENGINE=儲存引擎][DEFAULT CHARSET=字元編碼集];
-- 儲存引擎的選擇：MYISAM(支援全文索引) INNODB(支援事務，外來鍵) memory(雜湊索引)
mysql支援的數據型別
-- 數值型別：
-- 整數：int(n)
-- 小數型別：decinal(m,n)
字串型別：
-- 定長：char(n) 若長度小於n,則使用空格填充
-- 可變長：varchar(n)
-- SET(值1，值2，……) 類似列舉
-- 日期型別 DATE TIME
數據庫中的約束概念和新增刪除主外來鍵的方法
3個列級別
-- 非空約束
-- 唯一鍵約束
-- 預設值約束
兩個表級別
-- 主鍵約束
-- 外來鍵約束
表已經建立的情況下新增主外來鍵約束
-- ALTER TABLE 表名 ADD PRIMARY KEY/FOREIGN KEY;
-- ALTER table 從表名 ADD CONSTRAINT fk_xx_xxx FOREIGN KEY(外來鍵欄位名) REFERENCES 主表名(主表主鍵);
表已經建立的情況下刪除主外來鍵
-- ALTER table 表名 DROP PRIMARY KEY/FOREIGN KEY;
-- ALTER TABLE 從表名 DROP FOREIGN KEY fk_xx_xxx;
修改操作的命令
修改表名
-- alter table 舊錶名 RENAME [TO] 新表名;
修改欄位的數據型別
-- ALTER TABLE 表名 MODIFY 欄位名 欄位型別 [約束];
檢視錶結構
-- DESC tb_temp1;
修改欄位名
-- ALTER TABLE 表名 CHANGE 舊欄位名 新欄位名 數據型別;
新增欄位
-- ALTER TABLE 表名 ADD 新欄位名 數據型別 約束;
刪除欄位
-- ALTER TABLE 表名 DROP 欄位名;
修改欄位的排列位置
-- ALTER TABLE 表名 MODIFY 欄位名 數據型別 FIRST|AFTER 已經存在的列名;
修改表的儲存引擎
-- ALTER TABLE 表名 ENGINE=值;
數據庫中的內容比較複雜的是查詢，本次的例題就都是查詢的使用，其中相對較爲複雜的又是多表查詢的內容。
所以，就簡單的放點常用命令在下面 下麪好了。
create table employee(
num int primary key auto_increment,
name varchar(20) not null,
addr varchar(30) not null,
zip varchar(50) not null,
tel varchar(50) not null,
email varchar(20),
depno int not null,
birth date not null,
sex set('男','女') not null
)auto_increment=001;
insert into employee(name,addr,zip,tel,email,depno,birth,sex)
values('王林','武漢大學','430074','87598405',null,2,'1985-2-1','男'),
('王芳','華中科大','430073','62534231',null,1,'1966-3-28','男'),
('張曉','武漢理工大','430072','87596985',null,1,'1972-12-9','男'),
('王小燕','武漢交大',' 430071','85743261','[email protected]',1,'1950-7-30','女'),
('李華','華中農大','430070','87569865',null,5,'1962-10-18','男'),
('李明','華中師大','430075','85362143',' [email protected]',5,'1955-09-28','男'),
('田麗','中商財大','430076','87596985','[email protected]',3,'1968-08-10','女'),
('吳天','武漢電力','430077','36985612',' [email protected]',5,'1964-10-01','男'),
('劉備','武漢郵科院','430078','69865231',null,3,'1967-04-02','男'),
('趙雲','學府家園','430071','68592312',null,4,'1968-11-18','男'),
('貂蟬','湖北工大','430074','65987654',null,4,'1959-09-03','女');
create table salary(
Num int PRIMARY KEY auto_increment,
InCome DECIMAL(6,2),
OutCome DECIMAL(5,2)
)auto_increment=001;
INSERT into salary
VALUES(0,2100.8,123.09),
(0,1582.62,88.03),
(0,2569.88,185.65),
(0,1987.01,79.58),
(0,2066.15,108.0),
(0,2980.7,210.2),
(0,3259.98,281.52),
(0,2860.0,198),
(0,2347.68,180),
(0,2531.98,199.08),
(0,2240.0,121.0);
create table Department(
Depno int primary key auto_increment,
DepName varchar(20),
Remark varchar(20)
);
insert into Department values(0,'財務部',null),
(0,'人事資源部',null),
(0,'經理辦公室',null),
(0,'研發部',null),
(0,'市場部',null);
ALTER table Employee add CONSTRAINT fk_Department_employee FOREIGN KEY (Depno) REFERENCES Department(Depno);
ALTER table Employee add CONSTRAINT fk_salary_employee FOREIGN KEY (Num) REFERENCES salary(Num);
-- 查詢每個僱員的所有記錄
SELECT * FROM Employee;
-- 查詢前5個會員的所有記錄
SELECT * FROM Employee LIMIT 0,5;
-- 查詢每個僱員的地址和電話
SELECT Addr,Tel FROM Employee;
-- 查詢num爲001的僱員地址和電話
SELECT Addr,Tel FROM Employee WHERE num=1;
-- 查詢表Employee
SELECT Name,InCome as '實際工資' FROM salary s,Employee e WHERE s.num=e.num;
-- 查詢女僱員的地址和電話
SELECT Addr as '地址',Tel as '電話' FROM Employee where sex='女';
-- 計算每個僱員的實際收入
SELECT Name,InCome-OutCome as '實際收入' FROM salary s,Employee e WHERE s.num=e.num;
-- 找出所有姓王的僱員的部門號
SELECT e.`name`,d.Depno from Employee e,Department d WHERE e.depno=d.Depno and e.name like '王%';
-- 找出比所有財務部僱員收入都高的僱員姓名
SELECT DISTINCT Name FROM Employee e,salary s WHERE e.num=s.Num AND s.InCome > ALL(SELECT InCome FROM Employee e,Department d,salary s WHERE s.num=e.num and d.Depno=e.depno and DepName='財務部');
-- 找出所有收入在2000-3000之間僱員的編號
SELECT Num FROM employee,salary where InCome BETWEEN 2000 AND 3000;
-- 練習2
-- 查詢在財務部工作的僱員的情況
SELECT * FROM Employee e,department d WHERE e.depno=d.Depno;
-- 查詢在財務部且年齡不低於研發部任一僱員年齡的僱員的姓名
SELECT DISTINCT Name FROM Employee e,Department d where e.Depno=1 and e.birth < ALL(SELECT birth FROM Employee e,Department d WHERE e.depno=d.Depno and DepName='研發部');
-- 查詢比所有財務收入都高的僱員姓名
SELECT DISTINCT Name FROM Employee e,salary s WHERE s.num=e.num and s.InCome > ALL(SELECT income FROM Employee e,Department d,salary s WHERE s.num=e.num and d.Depno=e.depno and DepName='財務部');
-- 練習3
-- 查詢每個僱員的情況和薪水情況
SELECT *,income from employee e,salary s WHERE e.num=s.Num;
-- 查詢財務部收入在2200元以上的僱員姓名及其薪水詳細情況
SELECT name,income,outcome from employee e ,salary s,department d WHERE e.num=s.Num AND d.Depno=e.depno and d.DepName='財務部' AND InCome>2200;
-- 練習4
-- 求財務部僱員的平均實際收入
SELECT AVG(income-come) as '財務部實際收入' from salary s,employee e,department d where s.num=e.num and e.depno=d.Depno and d.DepName='財務部';
-- 求財務部僱員總人數
SELECT COUNT(*) FROM employee e,department d WHERE e.depno=d.Depno AND d.DepName='財務部';
-- 練習5
-- 求各部門的僱員數
SELECT count(e.depno),e.depno,d.DepName from department d,employee e WHERE d.Depno=e.depno GROUP BY e.depno;
-- 求部門的平均薪水大於2500的部門資訊
SELECT d.DepName,d.Depno,AVG(s.income) from salary s,employee e,department d where s.num=e.num and e.depno=d.Depno GROUP BY e.depno HAVING AVG(s.income)>2500;
select * from Department;
select * from salary;
增刪改數據的語法
語法:
插入
-- INSERT INTO 表名（列名...） VALUES （值...）;
-- 注意：沒有指定列名，預設插入全部列的值
-- 指定的列的個數和值的個數要一致，順序也要一致，數據型別也要匹配
修改
-- UPDATE 表名 SET 列名1=新值，列名2=新值...[WHERE 條件];
-- 注意：若沒有where子句，則代表修改全部
-- 有where子句代表修改滿足條件的記錄
刪除
-- DELETE FROM 表名 [WHERE 條件];
-- 注意：drop DELETE 表名;刪除表
-- TRUNCATE TABLE 表名;清空表中的數據，快速刪除一張表，然後建立這張表
-- DELETE FROM 表名;刪除表中的記錄
單表查詢的語法
語法：
SELECT DISTINCT *|欄位列表
FROM 表名
[WHERE 過濾條件]
[GROUP BY 分組欄位1,分組欄位2...]
[HAVING 分組後的過濾條件]
[ORDER BY 排序欄位1 asc|DESC,排序欄位2 ASC|DESC]
[LIMIT m,n]
sql語句的執行順序：1.from 2. where 3.group by 4. select 5.order by 6.limit
-- 單表查詢
-- ---單列查詢
SELECT sname from student;
-- ---多列查詢
SELECT sname,sage FROM student;
-- ---查詢所有
SELECT * from student;
SELECT sno,sname,ssex,sage,sdept FROM student;
SELECT studnet.sno from student;
SELECT s.sno FROM student as s;-- 給表取一個別名 s
-- 排序 ORDER BY 列名1 ASC|DESC，列名2 ASC|DESC，...
-- ---預設是asc
SELECT * FROM student ORDER BY sage DESC,sno [ASC];
-- 過濾條件（where）；
-- ---= ！= <> > < >= <= BETWEEN 值 and 值
-- ---AND OR in NOT
-- --- is NULL
-- --- is not null
-- 模糊查詢 LIKE
-- 分組 GROUP BY 列名1，列名2，列名...
-- 分頁 limit m,n; n表示pageSize，代表每頁顯示的個數
-- m表示pageSize*(pageNum-1) 是當前頁的前面的所有記錄個數
-- DISTINCT 去重
多表查詢的語法
-- 1.笛卡爾積 select * from 表1,表2;
select * from department; -- 4條記錄
select * from employee; -- 14條記錄
select * from employee,department; -- 56條記錄
-- 2. 等值連線 (通過兩張表之間的外來鍵關鍵起來)
select * from employee a ,department b where a.department_id=b.id;
-- 3. 內連線 表1 inner join 表2 on 關聯條件
select * from employee a inner join department b on a.department_id=b.id;
-- 4. 外連線 (需要分清楚左右表)
-- 左外連線 left [outer] join 左表全部數據 + 滿足條件的數據
select * from 左表 left join 右表 on 條件;
-- 右外連線 right [outer] join 滿足條件的數據+ 右表所有的數據
-- 範例： 求出每個部門下的員工人數，若沒有員工顯示0
-- 左連線，並且左表 department表
select a.id,count(b.id) from department a left join employee b
on a.id=b.department_id
group by a.id
-- 5 巢狀查詢
create table tb1(num1 int not null);
create table tb2(num2 int not null);
insert into tb1(num1) values(1),(5),(13),(27);
insert into tb2(num2) values(6),(14),(11),(20);
select * from tb1; select * from tb2;
-- num1 只要比tb2中某一個值大就返回true，就可以查出來
select num1 from tb1 where num1 > any( select num2 from tb2);
select num1 from tb1 where num1 > (select min(num2) from tb2);
-- num1需要比tb2中所有值都大，纔可以查出來
select num1 from tb1 where num1 > all( select num2 from tb2);
select num1 from tb1 where num1 > (select max(num2) from tb2);
-- 6 聯合查詢 並集 union 和 union all 區別：是否合併重複的記錄
select name,salary from employee where salary>3000 -- 1 KING 5000
union all
select name,salary from employee where department_id in(1,2); --8