A。結構化查詢語言（structured query language)
B。SQL語句分類
-在MySql數據庫中，SQL語句主要可以劃分以下幾類：
——DDL（data definition language):數據定義語言，定義對數據庫對象（庫、表、列、索引）的操作
-create,drop,alter,rename,truncate等
——DML(data manipulation language):數據操作語言，定義對數據庫記錄的操作
-insert,delete,update,select等
——DCL(data control language):數據控制語言，定義對數據庫，表，字段，用戶的訪問權限和安全級別
-grant，revoke等
——transaction control:事務控制
-commit,rollback,savepoint等
1.create database創建一個數據庫：
-語法：create database[if not exists] 數據庫名字；
-例子：create database fkjava;
2.drop database 刪除一個數據庫
-語法：-drop database [if not exists] 數據庫名；
-功能：刪除數據庫中所有的表和數據庫
-小心使用這個命令
-例子：drop database fkjava;
3.使用use選用數據庫(選著一個數據庫)
-語法：use 數據庫名；
-功能：把指定的數據庫作為默認的數據庫使用，用于后續語句。
-例子：use fkjava;
4.數據庫對象
-表：基本的數據存儲對象，以行和列的形式存在，列就是字段，行就是記錄
-約束：執行數據校驗，保證了數據的完整性
-數據字典：就是系統表，存放數據庫相關信息的表
-視圖：一個或多個表數據的邏輯顯示
-索引：用于提高查詢的性能
5.數據庫對象的命名規則
-必須以字母開頭
-可包含數字和三個特殊的字符（# _ $）
-不要使用mysql的保留字
-同一Schema下的對象不能同名
6.建表的語句
-標準的建表的語法（列定義之間以英文逗號隔開）
-數據表的每行稱為一條記錄（record），每一列稱為一個字段（field）
-主鍵列：唯一能夠標識每條記錄的列
7.show
-show tables [from 數據庫名][like wild];
-功能：顯示當前數據庫中已有的數據表的信息
-例子：show tables;
8.用create table 創建數據庫表
-語法：create table table_name(
列名 列類型,
列名 列類型,
列名 列類型
);
-功能：在當前數據庫中創建一張表
-例子：
CREATE TABLE tb_dept(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(18),
description VARCHAR(100)
);
9.describe
-語法：describe|desc table_name[列名]；
-功能：查看數據表中各列的信息
-例子：describe tb_dept;
-用show create table table_name\G 可以查看更全面的表定義信息
10.刪除數據表
-語法：drop table[if exists] table_name;
-功能：刪除指定的表
11.用alter table語句修改表的結構
●修改列類型
-alter table table_name modify 列名 列類型；
-例子：ALTER TABLE tb_emp MODIFY NAME VARCHAR(18);
▲不是任何情況都是可以修改的，當字段只包含空值時，類型，大小都可以修改的，否則修改可能不能成功
●增加列
-alter table table_name add 列名 列類型；
-例子：ALTER TABLE tb_emp ADD mobile VARCHAR(18);
●刪除列
-alter table table_name drop (column) 列名；
-例子：ALTER TABLE tb_emp DROP mobile;
●列改名
-alter table table_name change 舊列名 新列名 列類型；
-例子：ALTER TABLE tb_emp CHANGE NAME emp_name VARCHAR(18);
●更改表名
-alter table table_name rename 新表名；
-rename table table_name to 新表名；
-例子：ALTER TABLE tb_emp RENAME emp;
-例子：RENAME TABLE emp TO tb_emp;
12.刪除表
-語法：drop table table_name;
13.約束
-約束是在表上強制執行的數據校驗規則
-約束主要的用于保證數據庫的完整性
-當表中的數據有相互依賴性時，可以保護相關的數據不被刪除
>>>大部分數據庫支持下面五類完整性約束：
-not null 非空
-unique key 唯一鍵
-primary key 主鍵
-foreign key 外鍵
-check 檢查
▲約束作為數據庫對象，存放在系統表中，也有自己的名字
▲創建約束的時機
-在建表的同時創建
-建表后創建（修改表）
>>可定義列級或表級約束
>>有單列約束和多列約束
★★★★★★★★★★★★★★★★★
■定義約束的語法
-列級約束：在定義列的同時定義約束
>>語法：列定義 約束類型
-表級約束：在定義了所有列之后定義的約束
>>語法：
列定義….
[constraint 約束名] 約束類型(列名)
-約束名的取名規則
>> 推薦采用：表名_列名_約束類型簡寫
-約束可以在創建表的同時就定義，也可以在創建完表之后再添加
>>語法：alter table table_name
add constraint 約束名 約束類型(要約束的列名);
■非空約束(not null)
-列級約束，只能使用列級約束語法定義
-確保字段值不允許為空
-只能在字段級定義
>>NULL值
-所有數據類型的值都可以是null
-空字符串不等于NULL
-0也不等于NULL
★例子：
create table tb_student(
id int primary key auto_increment,
name varchar(18) not null
);
■唯一約束
-唯一性約束條件確保所在的字段或者字段組合不出現重復值
-唯一性約束條件的字段允許出現多個NULL
-同一張表內可創建多個唯一約束
-唯一約束可由多列組合而成
-建唯一約束時MySQL會為之建立對應的索引
-如果不給唯一約束起名，該唯一約束默認與列名相同
★例子：
create table tb_student(
id int primary key auto_increment,
name varchar(18) unique not null
);
■主鍵約束
-主鍵從功能上看相當于非空且唯一
-一個表中只允許一個主鍵
-主鍵是表中唯一確定一行數據的字段
-主鍵字段可以是單字段或者是多個字段的組合
-當建立主鍵約束時，MySQL為主鍵創建對應的索引
-主鍵約束名總為primary
■外鍵約束
-外鍵是構建于一個表的兩個字段或者兩個表的兩個字段之間的關系
-外鍵確保了相關的兩個字段的兩個關系
-子（從）表外鍵列的值必須在主表參照列值的范圍內，或者為空（也可以加非空約束，強制不允許為空）
-當主表的記錄被子表參照時，主表記錄不允許被刪除
-外鍵參照的只能是主表主鍵或者唯一鍵，保證子表記錄可以準確定位到被參照的記錄
-格式foreign key （外鍵名） references 主表（參考列）
★例子：#列級約束
create table tb_dept(
dept_id int primary key,
name varchar(18),
description varchar(255)
);
create table tb_employee(
employee_id int primary key,
name varchar(18),
gender varchar(10),
dept_id int references tb_dept(dept_id),
address varchar(255)
);
#表級約束
create table tb_dept(
dept_id int primary key,
name varchar(18),
description varchar(255)
);
create table tb_employee(
employee_id int primary key,
name varchar(18),
gender varchar(10),
dept_id int,
address varchar(255),
constraint foreign key tb_employee_fk(dept_id) references tb_dept(dept_id)
);
■check約束
-既可以作為列級約束，也可以作為表級約束
-定義在字段上的每一條記錄都要滿足的條件
-在check中定義檢查的條件表達式，數據需要符合設置的條件
-條件表達式不允許使用
-參照其他記錄的值
★例子：
create table tb_student(
id int primary key auto_increment,
name varchar(18),
age int check(age > 18 and age < 60)
);
◎列級約束與表級約束
-列級約束直接跟在列后定義，不再需要制定列名，與列定義之間用空格分開
-表級約束通常放在所有的列定義之后定義，要顯示制定對哪些列建立列級約束 與列定義之間采用英文逗號給開
-如果是對多列建聯合約束，只能使用表級約束語法
◎增加約束
-可增加或刪除約束，但不能直接修改
-可使約束啟用和禁用
-非空約束必須使用modify字句增加
-只要是可以使用列級約束語法來定義的約束，都可以通過modify來增加該約束
-alter table table_name
-add [CONSTRAINT constraint] type(column);
★例子：加FOREIGN KEY 約束到EMP表
alter table tb_employee
add constraint
tb_employee_dept_id_fk
foreign key(dept_id)
references tb_dept(dept_id);
*************************************************************
>>>> #建表的同時建立約束
CREATE TABLE tb_emp(
id INT PRIMARY KEY AUTO_INCREMENT,#主鍵，自動增長
NAME VARCHAR(18) NOT NULL,#姓名，非空約束，插入記錄的時候一定要插入姓名
sex VARCHAR(2) CHECK(sex='男' OR sex = '女'),#性別
age INT CHECK(age > 18 AND age < 60),#年齡，檢查約束
address VARCHAR(200),
email VARCHAR(100) UNIQUE,#唯一約束
dept_id INT REFERENCES tb_dept(id)
);
>>>>#建表的最后建立約束
CREATE TABLE tb_emp(
id INT PRIMARY KEY AUTO_INCREMENT,#主鍵，自動增長
NAME VARCHAR(18) NOT NULL,#姓名，非空約束，插入記錄的時候一定要插入姓名
sex VARCHAR(2) CHECK(sex='男' OR sex = '女'),#性別
age INT CHECK(age > 18 AND age < 60),#年齡，檢查約束
address VARCHAR(200),
email VARCHAR(100) UNIQUE,#唯一約束
dept_id INT,
#CONSTRAINT FOREIGN KEY 外鍵名[表名+約束名](外鍵) REFERENCES 表名
CONSTRAINT FOREIGN KEY tb_emp_fk(dept_id) REFERENCES tb_dept(id)
);
>>>> #表建立完之后再建約束
CREATE TABLE tb_emp(
id INT ,
NAME VARCHAR(18),
sex VARCHAR(2) ,
age INT ,
address VARCHAR(200),
email VARCHAR(100) ,
dept_id INT
);
#添加主鍵
alter table tb_emp
add primary key (id);
#添加外鍵
ALTER TABLE tb_emp
ADD CONSTRAINT FOREIGN KEY tb_emp_fk(dept_id) REFERENCES tb_dept(id);
#添加唯一鍵
alter table tb_emp
add unique (email);
#添加檢查約束
alter table tb_emp
add constraint check(age > 18 and age < 60);
#更改自動增長
alter table tb_emp
modify column id int auto_increment;
*************************************************************
■刪除約束
-約束可被刪除，刪除約束不會對數據產生影響
-當刪除被外鍵參照的主鍵時候，應該采用CASCADE關鍵字來級聯刪除外鍵，否則無法刪除主鍵
-語法如下：
alter table talbe_name drop constraint 約束名
◎刪除定義的約束
-刪除NOT NULL
>>alter table table_name modify 列名 列類型;
-刪除UNIQUE約束
>>alter table table_name drop index 唯一約束名;
-刪除PRIMARY KEY約束
>>alter table table_name drop primary key;
-刪除外鍵FOREIGN KEY約束
>>alter table table_name drop foreign key 外鍵名；
==============================================
◎自動增長和默認值
auto_increment:自動增長
-為新的行產生唯一的表識
-一個表只能有一個auto_increment屬性，且該屬性必須為主鍵的一部分，auto_increment屬性可以是任何整數類型
default默認值：指定默認值
★create table depts(
dept_id int primary key auto_increment,
name varchar(18),
description varchar(255) default"how are you now";
);
————————————————————————-
14.insert語句
-insert into table_name[(column[column…])]
values(value[,value…]);
-默認情況下，一次插入操作只插入一行
-一次性插入多條記錄：
insert into table_name[(column[,column…])]
values(value[,value…]),(value[,value…]);
-如果為每列都指定值，則表名后不需要列出插入的列名
-如果不想在表名后列出列名，可以為那些無法指定的值插入NUll
-如果需要插入其他特殊字符，應該采用\轉義字符做前綴
★1.指明字段進行插入，必須一一對應
insert into tb_dept(name,loc,description)
values('開發部','武漢','負責軟件開發');
2.如果插入的values是所有字段，可以不用顯示寫插入的字段名，強烈不推薦，不會影響到自動增長
insert into tb_dept values(3,'財務部','武漢','負責財務工作');
◆一次性插入多行：
#可以從一張表中插入數據
#創建一張表和tb_dept表的結構一樣，通過這種方式創建的表，只是復制表結構，不復制約束
CREATE TABLE tb_dept2
SELECT * FROM tb_dept
CREATE TABLE tb_dept2
SELECT * FROM tb_dept
WHERE id = 3;
INSERT INTO tb_dept2(id,NAME,loc,description)
SELECT id,NAME,loc,description FROM tb_dept;
15.update語句
-語法：update table_name
set column = value[,column = value]…
[where condition];
-修改可以一次修改多行數據，修改的數據可用where子句限定，where子句是一個條件表達式，只有符合該條件的行才會被修改。沒有where子句意味著where子句表達式值為true
-也可以同時修改多列，多列的修改中采用逗號隔開
★例子：
#where的時候建議使用主鍵或者是unique，主鍵最好，因為和業務無關
UPDATE tb_emp SET age = 13;
UPDATE tb_emp SET sex = '女',age = 34 WHERE id = 2;
16.delete語句
-語法：delete [from] table_name
[where condition];
-刪除不需要指定列名，因為刪除總是整行整行刪除，where子句是一個條件表達式，只有符合該條件的行才會被刪除。沒有where子句意味著刪除所有數據
-刪除可以一次刪除多行，刪除哪些行采用where子句限定
★例子：
delete from tb_emp;#刪除表所有數據
delete from tb_emp where id = 3;
17.truncate語句
-語法：truncate table table_name
-完全清空一個表
-DDL語句
★例子：
#截斷表，刪除所有表數據
truncate table tb_emp;
18.drop truncate delete
–delete刪除數據，保留表結構，可以回滾，如果數據量大，很慢
–truncate刪除所有數據，保留表結構，不可以回滾，一次刪除全部數據，速度相對快
–drop刪除數據和表結構，刪除速度最快
19.查詢mysql自動提交設置(1自動提交，0手動提交)
-select @@autocommit;
修改提交方式
set autocommit = 0;
20.最簡單的select語句
-語法：
select {*,column[alias],…}
from table_name;
-說明：*表示所有列
-from提供數據源(表名/視圖名)
-默認選著所有行
#查找 字段,字段… 從 表
★例子：
SELECT * FROM tb_dept;
SELECT NAME,loc FROM tb_dept;
21.select語句中的算術表達式
-對數值型數據列，變量，常量可以使用算數操作符創建表達式(+ – * /)
-對日期型數據列，變量，常量可以使用部分算數操作符創建表達式(+ -)
-運算符不僅可以在列和常量之間進行運算，也可以在多列之間進行運算
22.運算符的優先級
-乘法和除法的優先級高于加法和減法
-同級運算的順序是從左到右
-表達式中使用括號可強行改變優先級的運算順序
★例子：
select ename,sal.sal*12+100
from tb_emp;
SELECT ename,sal,sal*(12+100)
FROM tb_EMP;
23.NULL值的使用
-空值是指不可用，未分配的值
-空值不等于零或空格
-任意類型都可以支持空值
-包含空值的任何算術表達式都等于空
-字符串和null進行連接運算，得到也是null
24.定義字段的別名
-改變列的標題頭
-用于表示計算結果的含義
-作為列的別名
-如果別名中使用特殊字符，或者是強制大小寫敏感，或者有空格時，都可以通過為別名添加""(英文雙引號)實現
★例子：（加AS是標準的寫法）
SELECT ename '姓名',sal*12+100 '年薪'
FROM tb_emp;
SELECT ename AS '姓名',sal*12+400 AS '年薪'
FROM tb_emp;
25.重復記錄
-缺省情況下查詢顯示所有行，包含重復行
>>select deptno from tb_emp;
-使用distinct關鍵字可從查詢結果中清除重復的行
>>SELECT DISTINCT deptno FROM tb_emp;
-distinct的作用范圍是后面所有字段的組合
>>SELECT DISTINCT deptno,job FROM tb_emp;
26.限制所選擇的記錄
-使用where子句限定返回的記錄
-where子句在from子句后
-語法：
select [distinct]{*,column[alias],…}
from table_name
[where condition(s)];
★例子：
SELECT * FROM tb_emp WHERE deptno = 20;
27.where中的字符串和日期值
-字符串和日期要用單引號括起來
-字符串是大小寫敏感的，日期值是格式敏感的
select ename,job,deptno
from tb_emp
where ename = 'king';
28.where中的比較運算符
> >= < <= = <>(不等)
SELECT * FROM tb_emp WHERE sal >= 1500;
SELECT * FROM tb_emp WHERE sal >=1500 AND sal <=3000;
>>>其他比較運算符
between…and… 在兩個值之間(包含)
in(list) 匹配所有列出的值
like 匹配一個字符模式
is 是空值
◎使用between運算符
-使用between運算符顯示某一值域范圍的記錄
select ename,sal
from tb_emp
where sal between 1000 and 1500;
◎使用IN運算符
-使用IN運算符獲得匹配列表值的記錄
select empno,ename,sal,mgr
from tb_emp
where mgr in(7920,7566,7888);
◎使用like運算符
-使用like運算符執行模糊查詢
-查詢條件可包含文字字符或數字
-(%)可表示零或多個字符
-(_)可表示一個字符
SELECT ename
FROM tb_emp
WHERE ename LIKE '_德';
◎使用is null運算符
-查詢包含空值的記錄
select ename,mgr
from tb_emp
where mgr is null;
SELECT ename,mgr
FROM tb_emp
WHERE mgr IS NOT NULL;
◎邏輯運算符
-and
-or
-not
◎優先級規則
-括號將跨越所有優先級
次序 運算符
1 所有比較運算符
2 not
3 and
4 or
29.對結果集排序
-查詢語句執行的查詢結果，數據是按插入順序排序的
-實際上需要按某列的值大小排序排列
-按某列排序采用order by 列名[desc],列名…
-設定排序列的時候可采用列名，列序號和列別名
-如果按多列排序，每列的asc，desc必須單獨設定
★降序
select * from tb_emp
order by sal desc;
★升序(默認)
select * from tb_emp
order by sal asc;
==============================================================
30.常用函數
-字符串函數
#concat連接
SELECT CONCAT(ename,job) FROM tb_emp;
#uppper轉換大寫
SELECT UPPER(ename) FROM tb_emp WHERE EMPNO = 8000;
#返回字符串長度
SELECT LENGTH(ename) FROM tb_emp WHERE EMPNO = 8000;
#返回字符串的子串
SELECT SUBSTR(ename,2,4) FROM tb_emp WHERE EMPNO = 8000;
-數值函數
-日期和時間函數
#返回當前時間
SELECT NOW();
#查詢入職時間是1980年的員工
SELECT * FROM tb_emp
WHERE YEAR(HIREDATE) = 1980;
#查詢入職時間是1980年2月
SELECT * FROM tb_emp
WHERE YEAR(hiredate) = 1981
AND MONTH(HIREDATE) = 2;
#插入時間的兩種方式
INSERT INTO tb_emp(empno,ename,job,hiredate,sal)
VALUES(8001,'劉德華','賣火材的小女孩','2013-9-3',900);
INSERT INTO tb_emp(empno,ename,job,hiredate,sal)
VALUES(8002,'王大海','酒吧玉漱',NOW(),1200);
-流程函數
函數 功能
◎if(expr1,expr2,expr3) 如果expr1是true(expr1<>0 and expr1<>null),
則if()的返回值為expr2,否則返回值為expr3
◎ifnull(expr1,expr2) 如果expr1不為null，則ifnull()的返回值為expr1,
否則其返回值為expr2
◎case
when [value] then result 如果value是真，返回result，否則返回default
else default
end
◎case [expr] 如果expr等于value1，返回result1，
when [value1] then result1 如果等于value2，返回result2，否則返回default
when [value2] then result2
[else default]
end
————————————————————
SELECT empno,ename,job,comm AS '獎金',
CASE
WHEN comm IS NULL THEN 100
ELSE comm
END AS '獎金2'
FROM tb_emp;
SELECT empno,ename,job,comm AS '獎金',
IF(comm,comm,0) AS '獎金2'
FROM tb_emp;
SELECT empno,ename,job,comm AS '獎金',
IFNULL(comm,50) AS '獎金2'
FROM tb_emp;
————————————————————-
-其他常用函數
-組函數
◆聚合函數對一組值進行運算，并返回單個值，也叫組合函數。
-count(*|列名) 統計行數
-avg(數值類型列名) 平均值
-sum(數值類型列名) 求和
-max(列名) 最大值
-min(列名) 最小值
>>除了count()以外，聚合函數都會忽略NULL值
************************************************
SELECT MAX(sal) AS '最大工資',MIN(sal) AS 最低工資,
SUM(sal) AS '總工資',AVG(sal) AS '平均工資'
FROM tb_emp;
SELECT COUNT(*) FROM tb_emp;
SELECT COUNT(job) FROM tb_emp;
SELECT COUNT(deptno) FROM tb_emp;
SELECT COUNT(DISTINCT deptno) FROM tb_emp;
SELECT AVG(comm) FROM tb_emp;#忽略空值
SELECT AVG(IFNULL(comm,0)) FROM tb_emp;
************************************************
◆group by子句
-group by子句的真正作用在于與各種聚合函數配合使用，它用來對查詢出來的數據進行分組
-分組的含義是：把該列具有相同值的多條記錄當成一組記錄處理，最后只輸出一條記錄
-分組函數忽略空值
-結果集隱式按升序排列，如果需要改變排序方式可以使用order by子句
☆語法：
select column,group_function
from table_name
[where conditon]
[group by group_by_expression]
[order by column];
⊙分組函數的重要規則
-如果使用了分組函數，或則使用group by的查詢：出現在select列表中的字段，要么出現在組合函數里，要么出現在group by子句中
-group by子句的字段可以不出現在select列表當中
-使用集合函數可以不適用group by子句，此時所有的查詢結果作為一組
*********************************
#每個部門的平均工資
SELECT deptno,AVG(sal)
FROM tb_emp
GROUP BY deptno;
#查詢每個部門的整體工資情況
#如果select語句中的列未使用組函數，那么它必須出現在group by子句中
#而出現在group by子句中的列，不一定要出現在select語句中
SELECT deptno,MAX(sal),MIN(sal),SUM(sal),AVG(sal),COUNT(*)
FROM tb_emp
GROUP BY deptno;
#每個部門每個職位的平均工資
SELECT deptno,job,AVG(sal)
FROM tb_emp
GROUP BY deptno,job;
*********************************
⊙限定組的結果：having子句
-having子句用來對分組后的結果再進行條件過濾
-語法：
select column group_function
from table_name
[where condition]
[group by group_by_expression]
[having group_condition]
[order by column];
☆having與where的區別
-where是在分組前進行條件過濾
-having子句是在分組后進行條件過濾
-where子句中不能使用聚合函數
-having子句可以使用聚合函數
====================================
#部門平均工資大于1100
SELECT deptno,AVG(sal)
FROM tb_emp
GROUP BY deptno
HAVING AVG(sal) >1100;
====================================
⊙查詢結果限定(mysql特有)
-在select語句最后可以用limit來限定查詢結果返回的起始記錄和總數量
-語法：
select….limit offset_start,row_count;
-offset_start:第一個返回記錄行的偏移量，默認為0
-row_count:要返回記錄行的最大數量
—————————
SELECT * FROM tb_emp LIMIT 5;
SELECT * FROM tb_emp LIMIT 4,2;
—————————
31多表連接查詢
-使用單個select語句從多個表中取出相關的數據，通過多表之間的關系，構建相關數據的查詢
-多表連接通常是建立在相互關系的父子表上的
☆語法：
select … from join_table
join_type join_table
on join_condition
where where_condition;
-join_table 參與連接的表
-join_type 連接類型：內連接，外連接，交叉連接，自連接
-join_condition 連接條件
-where_condition where過濾條件
☆交叉連接/笛卡爾交集
-語法：
select … from join_table cross join join_table2;
-沒有on子句和where子句，它返回連接表中所有數據行的笛卡爾積
-其結果集合中的數據行數等于第一個表中符合查詢條件的數據行數乘以第二個表中符合查詢條件的數據行數
-相當于：select … from table1,table2;
★例子：
SELECT COUNT(*) FROM tb_dept,tb_emp;
SELECT * FROM tb_emp CROSS JOIN tb_dept;
-交叉連接：返回兩張記錄表的乘機
☆內連接
-語法：
select … from join_table
[inner] join join_table2
[on join_condition]
where where_definition;
-只列出這些連接表中與連接條件相匹配的數據行
-內連接分類：
-等值連接：在連接條件中使用等號(=)運算符來比較被連接列的值
-非等值連接：在連接條件中使用除等號運算符以外的其它比較運算符來比較被連接的列的列值
-自然連接：在連接條件中使用等于(=)運算符來比較連接列的類值，但它使用選擇列表指出查詢結果集合中所包含的列，并刪除連接表中的重復列
————————————
內連接，連接條件就是主外鍵關聯
SELECT * FROM tb_emp e,tb_dept d
WHERE e.deptno = d.deptno;
SELECT * FROM tb_emp e INNER JOIN tb_dept d
ON e.deptno = d.deptno;
————————————
☆外連接
-語法：
select … from join_table
(left|right|full) [outer] join join_table2
on join_condition
where where_definition;
-不僅列出與連接條件相匹配的行，還列出左表(左外連接)，右表(右外連接)或兩個表(全外連接)中所有符合where過濾條件的數據行
-分類：
-左外連接(left [outer] join)
-右外連接(right [outer] join)
-全外連接(full [outer] join)
●左外連接
-在外連接中，某些不滿足條件的列也會顯示出來，也就說，只限制其中一個表的行，而不限制另一個表的行
-左連接，左邊的表為主變，左邊的表記錄全部顯示，如果沒找到記錄則補NULL
———————————
SELECT * FROM tb_emp LEFT JOIN tb_dept
ON tb_dept.deptno = tb_emp.deptno;
SELECT * FROM tb_dept LEFT JOIN tb_emp
ON tb_dept.deptno = tb_emp.deptno;
———————————
☆自連接
-參與連接的表都是同一張表(通過給表取別名虛擬出)
select c.name '類別名',c2.name '父類別名'
from tb_course c left join tb_course c2
on c.pid = c2.id;
============================================================
32子查詢
-某些情況下，當進行查詢的時候，需要的條件是另外一個select語句的結果，這個時候，就要用到子查詢
-為了給主查詢(外部查詢)提供數據而首先執行的查詢(內部查詢)被叫做子查詢
-用于子查詢的關鍵字主要包括in ，not in = <>等
-一般說子查詢的效率低于連接查詢，表連接都可以用子查詢替換，但反過來卻不一定
========================================
#查詢工資比艾倫高的員工
SELECT * FROM tb_emp
WHERE sal <(
SELECT sal FROM tb_emp
WHERE ename = '艾倫'
);
#查詢工資最高的員工的名字
SELECT ename,sal FROM tb_emp
WHERE sal =(
SELECT MAX(sal) FROM tb_emp
);
#單行子查詢返回多個結果是 錯誤
SELECT * FROM tb_emp
WHERE sal =(
SELECT MIN(sal) FROM tb_emp
GROUP BY deptno
);
#in 與列表中的任一成員相等（等于子查詢的任意一個結果）
SELECT * FROM tb_emp
WHERE sal IN (
SELECT sal FROM tb_emp
WHERE job = '市場營銷'
);
#any 與子查詢返回的每一個值比較(小于子查詢的任意一個結果 即：小于最大的)
SELECT * FROM tb_emp
WHERE sal < ANY(
SELECT sal FROM tb_emp
WHERE job = '市場營銷'
);
========================================
33.索引
-模式(schema)中的一個數據庫對象
-在數據庫中用來加速對表的查詢
-通過使用快速路徑訪問方法快速定位數據，減少了磁盤的IO
-與表獨立存放，但不能獨立存在，必須屬于某個表
-由數據庫自動維護，表被刪除時，該表上的索引自動被刪除
-索引的作用類似于書的目錄，幾乎沒有一本沒有目錄，因此幾乎沒有一張表沒有索引
☆索引的創建
-自動：當在表上定義一個primary key 或者unique約束條件時，數據庫自動創建一個對應的索引
-手動：用戶可以創建索引以加速查詢
◆創建索引
-在一列或者多列上創建索引
create index index_name
on table_name(column[,column]…);
create index index_tb_student_name
on tb_student(stu_name);
⊙使用索引，在where之后加上索引，提高查詢速度：
select * from tb_student where stu_name = index_tb_student_name;
⊙刪除索引
drop index index_name;
——————————————-
33視圖的好處
-可以限制對數據的訪問
-可以使復雜的查詢變得簡單
-提供了數據的獨立性
-提供了對相同數據的不同顯示
⊙創建視圖
-在create view 語句后加入子查詢
create [or replace] view view_name
[(alias[,alias]…)]–為視圖字段指定別名
as subquery
[with read only];
子查詢中可以是非常復雜的select語句
●創建視圖，EMP_V_10，包括10號部門的所有雇員信息
create view emp_v_10
as select employee_id,name,salary
from employee
where deptt_id = 10;
●可以使用sql*plus的describe命令描述視圖結構
-describe emp_v_10;
CREATE VIEW view_emp
AS SELECT ename AS 姓名,job AS 崗位,HIREDATE AS 入職日期,sal AS 工資
ROM tb_emp WHERE DEPTNO = 30;
使用視圖
SELECT * FROM view_emp;
刪除視圖
DROP VIEW view_emp;
-刪除視圖不會導致數據的丟失，因為視圖是基于數據庫的表之上的一個查詢定義
-創建復雜視圖兩張表以及兩張以上的表查詢的視圖創建
—————————————————————-
34數據庫的設計
-當數據庫比較復雜時我們需要設計數據庫
-良好的數據庫設計
-節約數據的存儲空間
-能夠保證數據的完整性
-方便進行數據庫應用系統的開發
☆數據庫設計規范化
-僅有好的DBMS并不足以避免數據冗余，必須在數據庫的設計中創建好的表結構
-數據庫規范化的三個級別，范式是具有最小冗余的表結構，如下：
-第一范式：(1stNF first normal formate)
-第二范式：(2stNF second normal formate)
-第三范式：(3stNF third normal formate)
◆第一范式(1NF)（第一范式必須有主鍵）
-在關系模式R中的每一個具體關系r中，必須要有主鍵，并且每個屬性都是不可再分的最小數據單位
◆第二范式(2NF)
-如果關系模式R中的所有非主屬性都完全依賴于主關鍵字，則稱關系R是屬于第二范式
◆第三范式(3NF)
-關系模式R中的非主關鍵字不能依賴于其他非主關鍵，即非主關鍵字之間不能有函數(傳遞)依賴關系，則稱關系R是屬于第三范式
************************************************
一對一，一對多的關系可以用主外鍵關聯
多對多的話，就需要建立中間表來處理關系
#創建一個班級表
CREATE TABLE tb_class(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(18)
);
#創建一個學生表
CREATE TABLE tb_student(
id INT PRIMARY KEY AUTO_INCREMENT,
stu_name VARCHAR(20),
sex VARCHAR(2),
age INT,
class_id INT,
CONSTRAINT FOREIGN KEY tb_student_fk(class_id) REFERENCES tb_class(id)
);
#創建一個課程表
CREATE TABLE tb_course(
id INT PRIMARY KEY AUTO_INCREMENT,
course_name VARCHAR(20),
course_grade INT
);
SELECT * FROM tb_student
#創建一個中間表處理關聯關系
CREATE TABLE tb_stu_cour(
id INT PRIMARY KEY AUTO_INCREMENT,
stu_id INT,
cour_id INT,
grade INT,
UNIQUE(stu_id,cour_id),
CONSTRAINT FOREIGN KEY tb_sc_stu_id(stu_id) REFERENCES tb_student(id),
CONSTRAINT FOREIGN KEY tb_sc_cour_id(cour_id) REFERENCES tb_course(id)
);
************************************************
oracle部分
==============================================================
1.數據模型
-數據模型就是描述數據的一組概述的定義：
-層次數據模型
-網狀數據模型
-關系數據模型(RDBMS)
-面向對象數據模型
-關系型數據庫
-關系模型數據庫采用表組織數據(表稱為“關系”)，一個數據庫有許多個表組成
-oracle數據庫的特點
-支持大數據庫，多用戶的高性能的事物處理
-oracle遵循數據存取語言，操作系統，用戶接口網絡通信的工業標準
-實施安全性控制和完整性：數據加密，數據校對，第三方認證服務…
-支持分布式數據庫和分布式處理
-完全向上兼容，具有可移植性，可兼容性和可連接性
-全球化，跨平臺的數據庫
-從兩層模式到多層模式
-oracle10g體系結構
-數據庫的體系結構是指數據庫的組成、工作過程與原理，以及數據在數據庫中的組織與管理機制
-oracle服務器提供開放、全面和綜合的信息管理，它由oracle數據庫和oracle實例實例組成
-管理數據庫的后臺進程和內存結構的集合稱為oracle實例
oracle服務器
↙ ↘
oracle數據庫 oracle實例
-oracle實例
-oracle實例是后臺進程和內存結構的集合
(分配)↗系統全局區
oracle實例
(啟動)↘后臺進程
-oracle數據庫
-oracle數據庫由操作系統文件組成，這些文件為數據庫信息提供實際物理存儲區
-oracle數據庫包含邏輯結構和物理機構
-物理結構包含數據庫中的一組操作系統文件
-邏輯結構指數據庫創建之后形成的邏輯概念之間的關系
oracle數據庫
↙ ↘
物理結構 邏輯結構
-oracle數據庫的物理結構
-物理組件就是oracle數據庫所使用的操作系統物理文件
物理文件可分為三類：
物理組件
↙ ↓ ↘
數據文件 控制文件 日志文件
-日志文件記錄對數據庫的所有修改信息，用于故障恢復
-各種文件的后綴 數據文件.dbf 控制文件.ctl 日志文件.log 參數文件.ora
-數據庫文件與存儲：
-數據文件：信息的物理存儲位置
-控制文件：數據庫的控制信息
-日志文件：記錄日志信息
-初始化參數文件：數據庫啟動時讀取的屬性
-會話session
-會話是用戶與oracle服務器的單個連接
-當用戶與服務器建立連接時創建會話
-當用戶與服務器斷開連接時關閉會話
-oracle是多用戶、多任務、可分布式管理的數據庫，同時可有許多個用戶對數據庫操作
–查詢當前系統時間
select sysdate from dual;
–忘記管理員密碼：
-在命令行下輸入sqlplus/nolog 進入sql*plus
-輸入：conn/as sysdba
-修改密碼：alter user 用戶名 identified by 新密碼;
-oracle默認用戶
-只有合法的用戶賬號才能訪問oracle數據庫
-oracle有幾個默認的數據庫用戶
-sys是系統最高權限的管理員
-system是是默認的系統管理員該用戶擁有oracle管理工具使用的內部表和視圖，通常通過system用戶管理數據庫用戶、權限和存儲等
-scott用戶是oracle數據庫的一個示范賬戶，在數據庫安裝的時創建
-數據庫中所有數據字典和視圖都存在sys模式中。sys用戶主要用來維護系統信息和管理實例
>>>解除scott用戶的鎖定的方法：
alter user scott account unlock;
重新設置密碼：
alter user scott identified by tiger;
-數據字典：
-在數據庫創建的時候，oracle服務器會在數據文件里創建一些額外的對象結構
-數據字典表
-動態性能表
–查詢當前系統時間
select sysdate from dual;
select * from dba_tablespaces;
–查新所有的表空間
select distinct tablespace_name from dba_free_space;
–創建表空間myspace
–表空間的數據文件保存路徑、大小
–是否自動擴展
create tablespace myspace
datafile 'F:\oracle\product\10.2.0\oradata\orcl\myspace.dbf'
size 15M
autoextend on;
–刪除表空間同時刪除表空間的所在物理文件
drop tablespace myspace including contents and datafiles;
–創建用戶
create user myspace
identified by myspace
default tablespace myspace
temporary tablespace temp;
–查看用戶
select * from dba_users;
–刪除用戶
drop user myspace;
drop user mytest;
–賦予權限
grant connect to MYSPACE;
grant resource to MYSPACE;
create table tb_calss(
id number,
name varchar2(20)
);
create table tb_student(
id number,
name varchar2(20),
sex char(2),
age number,
address varchar(50),
mobile varchar(11)
);
/*
使用alter table語句增加，修改或者刪除行
假如只是增加或者修改一行，可以不加()
drop就算只有一列都應該加()
*/
–增加字段
alter table tb_student
add (email varchar2(20),faname varchar2(20));
–刪除字段 多了一個column關鍵字
alter table tb_student
drop column faname;
–修改字段
alter table tb_student
modify email varchar2(300);
select * from tb_student;
/*
修改列名 如果覺得列名不合適，那么可以使用alter table語句的raname column
子句改變列名
語法：
alter table table_name
rename column column_name
to nwe_column_name;
*/
alter table tb_student
rename column address
to addr;
/* 修改表名
-要想改變表，視圖，序列或者同義字的名稱，可以執行rename語句
語法：
rename table_name
to new_table_name;
*/
rename tb_student
to tb_stu;
rename tb_stu
to tb_student;
/*
-增加注釋
-當開發數據庫應用程序的時候，為了描述表，列的作用，可以使用comment語句為
表和列增加注釋
語法：
comment on table table_name is 'text';
comment on column table.column is 'text';
*/
comment on table tb_student is '學生表';
comment on column tb_student.name is '學生姓名';
–查看數據字典,可以查看用戶所擁有的表
select * from user_tables;
select * from user_tab_comments;
select * from user_tab_comments where comments is not null;
select * from user_col_comments;
select * from user_col_comments where comments is not null;
/*
刪除一張表
-表中的數據和結構會被刪除 語法：
-任何未決的事務被提交 drop table table_name;
-所有的索引被刪除
-不能回滾drop table 語句
drop table table_name [cascade constraints] [purge];
-cascade constraints用于指定級聯刪除從表的外鍵約束
-purge用于指定徹底刪除表（oracle10g新有的特性）
注意：如果在兩張表之間具有主外鍵關系，那么當刪除主表的時候，必須指定cascade constraint子句
恢復被刪除的表
-.在執行drop table 語句的時候，oracle會將被刪除的表存放到數據庫回收站中，
從oracle10g開始，使用flashback table語句可以快速恢復被刪除的表
語法：
flashback table table_name to before drop;
*/
/*
截斷表
-當表結構必須保留，而表數據不再需要的時候，可以使用truncate table語句
-刪除表中所有的行
語法：
truncate table table_name;
-當你使用truncate 不可以回滾
*/
–徹底刪除一張表
drop table table_name purge;
=======================================================================
–將commit 到 commit 之間的多條sql語句看做一個事務
–建表表的另外一種方式
create table tb_class2
as select * from tb_class;
select * from tb_class2;
–插入數據的另外一種方式
insert into tb_class2(id,name)
select id,name from tb_class;
commit;
–更新數據
update tb_class2
set name = 'class4' where id = 1;
–一次可以修改多列數據
update tb_class2
set id = 5,name = '美好'
where id = '2';
–刪除數據
–刪除 從 表 條件
delete from tb_class2
where id = 5;
–刪除所有數據保留表結構（不加where條件），可以回滾
delete from tb_class2;
rollback;
–截斷表，刪除所有數據保留表結構，不可以回滾
truncate table tb_class2;
–刪除表所有數據和表結構，不可以回滾
drop table tb_class2;
select * from tb_student;
select * from tb_class;
drop table tb_student purge;
drop table tb_class purge;
create table tb_class(
id number primary key,
name varchar2(30) not null,
monitor varchar(30) default 'wangyan'
);
create table tb_student(
id number primary key,
name varchar2(20) not null,
sex char(2) not null check(sex = '男' or sex = '女'),
age number check(age > 18 and age < 60),
email varchar2(50) unique,
class_id number references tb_class(id)
);
insert into tb_class(id,name)
values (1,'network');
insert into tb_class(id,name)
values (2,'application');
insert into tb_class(id,name)
values (3,'boolean');
===================================================================
-對幾種關系的解決方式
–一對一 、一對多（多對一）、多對多
—
–一對一 1.主鍵關聯
create table tb_card1(
id number primary key,
code varchar2(19)
);
create table tb_person1(
id number primary key,
name varchar2(20),
constraints tb_person1_fk foreign key (id) references tb_card1(id)
);
–多一字段來做外鍵關聯
create table tb_card2(
id number primary key,
code varchar2(19)
);
create table tb_person2(
id number primary key,
name varchar2(20),
card_id number unique,
constraints tb_person2_fk foreign key (card_id) references tb_card2(id)
);
–學生表、班級表、課程表的關系處理
–學生表 班級表 多對一
–班級表 學生表 一對多
–學生表 課程表 多對多
–班級表
create table tb_class1(
id number primary key,
name varchar2(30) not null
);
–學生表
create table tb_student(
id number primary key,
name varchar2(20) not null,
sex char(2) not null check(sex = '男' or sex = '女'),
age number check(age > 18 and age < 60),
email varchar2(50) unique,
class_id number,
constraints tb_student_fk foreign key (class_id) references tb_class1(id)
);
–課程表
create table tb_course(
id number primary key,
name varchar2(30),
credit number
);
–創建一個中間表來處理多對多的關系
create table tb_student_course(
student_id number,
course_id number,
constraints tb_student_course_pk primary key (student_id,course_id),
constraints tb_st_cour_st_fk foreign key (student_id) references tb_student(id),
constraints tb_st_cour_co_fk foreign key (course_id) references tb_course(id)
);
create table tb_student_course1(
id number primary key,
student_id number,
course_id number,
constraints tb_student_course_unique unique (student_id,course_id),
constraints tb_st_cour_st_fk1 foreign key (student_id) references tb_student(id),
constraints tb_st_cour_co_fk1 foreign key (course_id) references tb_course(id)
);
==================================================================================
–字符串的連接操作
select ename || '的年薪是' || sal*12 from emp;
–null的使用
select * from emp where comm is null;
select * from emp where comm is not null;
–定義別名 as可以有，也可沒有 ""可以有，也可沒有 ，但不能使用‘’
select ename as 姓名,sal 月薪,sal*12 "年薪" from emp;
–distinct
select deptno from emp;
select distinct deptno from emp;
select * from emp where ename like '%A%';
–between…and…
select * from emp where sal between 1200 and 3000;
–in
select * from emp where deptno in(20,30);
–排序
select * from emp order by sal desc;
–函數的使用
–lower把大寫轉小寫，upper把小寫轉大寫
select * from emp where lower(ename)='allen';
select upper('nijljfdljfld') from dual;
–initcap使字符串中的所有的單詞的首字母變為大寫
select initcap('tony allen') from dual;
–concat連接兩個字符串
select concat('lan','guage') from emp;
–substr截取字符串，返回子串 從1開始 d但0、1效果一樣
select substr('zhanghaicheng',6) from dual;
select substr('zhanghaicheng',0,5) from dual;
select substr('zhanghaicheng',1,5) from dual;
–length返回字符串的長度
select length('zhanghaicheng') from dual;
–instr(string,char)在一個字符串中搜索指定的字符，返回發現指定的字符的位置，從1開始
select instr('zhanghaicheng','e') from dual;
–rpad 在列的右邊粘貼字符 lpad 在列的左邊粘貼字符 9是顯示的位數 '$'所粘貼的字符
select rpad(sal,9,'$') from emp;
–trim 刪除首尾的空字符串
select trim(' zhanghaicheng ') from dual;
–trim 刪除首尾的h;
select trim('h'from 'helloWordh') from dual;
–trim 刪除首的h
select trim(leading 'h' from 'helloWordh') from dual;
select trim(both 'h' from 'helloWordh') from dual;
–trim 刪除尾的h
select trim(trailing 'h' from 'helloWordh') from dual;
–replace('string','s1','s2')
–string 希望被替換的字符或變量
–s1 需要被替換的字符串，s2替換的字符串
select replace('zhanghaicheng','hai','iah') from dual;
–數值函數round 進行四舍五入 3 是保留的小數位數
select round(89.3468,3) from dual;
–trunc截斷數據 不進行四舍五入 3 是保留的小數位數
select trunc(89.3468,3) from dual;
–mod 兩個數取模
select mod(3,2) from dual;
–日期函數
–sysdate 返回系統當前時間
select sysdate from dual;
–轉換函數：to_number ,to_char, to_date
–to_number可將數字型字符串轉化為number型數據
select to_number('344') from dual;
–to_char可將number型數據轉化為字符串
select to_char(344) from dual;
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;
–to_date 將日期型字符串轉換為date
select to_date('2013-3-23','yyyy-mm-dd') from dual;
select to_date('2013-3-23','yyyy-mm-dd') as currdate from dual;
–常用函數
–nvl(expr1,expr2) 如果expr1不為null，返回expr1,為null，返回expr2
select ename,sal,nvl(comm,0) from emp;
select sal,(sal+nvl(comm,0))*12 from emp;
–nvl2(expr1,expr2,expr3) 如果expr1不為null，返回expr2 ，為null ，返回expr3
select sal,(sal+nvl2(comm,comm,0))*12 from emp;
–coalesce(expr1,expr2,…..)返回第一個不為null的表達式
–case表達式功能類似于if… else…
select ename,job,sal,
case job when 'SALEMAN'then sal*0.4
when 'MANAGER'then sal*0.7
when 'CLERK'then sal+100000
else sal end as salary
from emp;
–decode
=====================================================================
原創文章，作者：xiangx，如若轉載，請注明出處：http://www.www58058.com/68661
