MySQL講義

1????????? MySQL基礎知識

瑞典MySQL AB公司開發，由SUN收購，而后SUN被甲骨文并購，目前屬于Oracle公司。

MySQL是一種關聯數據庫管理系統 由于其體積小、速度快、總體擁有成本低、MySQL軟件采用了雙授權政策，分為社區版和企業版。

1.1??????? MySQL版本及下載

MySQL數據庫版本相對比較繁雜。常見的有：Community社區版、Enterprise企業版。

Community版是開源免費的，這也是我們通常用的MySQL的版本。可以滿足絕大多數用戶需求。

Enterprise版，官方指出提供30天免費試用期。可進一步劃分為MySQL標準版、MySQL企業版、MySQL集群版。官方提供付費服務。

其中Community Server 可以直接從mysql 的官網下載。但Enterprice Edition只能從Oracle edelivery上下載，而Edelivery有時會屏蔽中國IP。

MySQL各版本區別參見：

http://www.admin10000.com/Document/62.html

下載mysql時注意區分版本細節及所應用的系統平臺：linux(32/64) 、win(32/64)

舉例：MySQL Community Server 5.6.20 win版本

???????? GA 是指軟件的通用版本，一般指正式發布的版本 (Generally Available (GA) Release)

???????? mysql-essential-5.1.60-win32.msi ???????? 是精簡版，如果只需要mysql服務，就選擇此版本。

???????? mysql-5.1.60-win32.msi ?????????????????????????? 是完整版，包含安裝程序和配置向導，有MySQL文檔。??

???????? mysql-noinstall-5.1.60-win32.zip 是非安裝的zip壓縮包，沒有自動安裝程序和配置向導，無安裝向導

???????? mysql-5.1.60.zip 是用于windows的Mysql源碼壓縮包

linux版本

???????? 在http://www.mysql.com/downloads/網站上下載不了

???????? 在 www.oracle.com/downloads 找mysql 注冊用戶, 選擇操作系統平臺和mysql版本 進行下載

官方文檔上有關MySQL安裝，介紹了3種類型及其對應安裝方式來安裝MySQL數據庫：

　　Linux supports anumber of different solutions for installing MySQL. The recommended method isto use one of the distributions from Oracle. If you choose this method, thereare three options available:

(1)??? Installingfrom a generic binary package in .tar.gz format. See Section 2.2,“Installing MySQL from Generic Binaries on Unix/Linux” for moreinformation.

(2)??? Extractingand compiling MySQL from a source distribution. For detailed instructions,see Section 2.9, “InstallingMySQL from Source”.

(3)??? Installingusing a pre-compiled RPM package. For more information on using the RPMsolution, see Section 2.5.1,“Installing MySQL from RPM Packages on Linux”

我們選用較簡單的RPM 包來安裝。

1.2??????? 下載安裝包

　　Community Server下載地址：

???????? MySQL Community Server?????? mysql社區版，開源、免費

???????? MySQL Enterprise Edition??????? mysql企業版，非免費

???????? MySQL Cluster?????????????????? mysql集群版，開源、免費

2????? MySQL服務器安裝和啟動??

2.1??????? 查詢服務器上已經安裝的mysql

???????? 在終端提示符輸入：rpm -aq | grep mysql?? 命令。查詢結果如下顯示：

???????? qt-mysql-4.6.2-25.el6.x86_64

???????? mysql-connector-odbc-5.1.5r1144-7.el6.x86_64

???????? mysql-bench-5.1.66-2.el6_3.x86_64

???????? mysql-devel-5.1.66-2.el6_3.x86_64

???????? libdbi-dbd-mysql-0.8.3-5.1.el6.x86_64

???????? mysql-test-5.1.66-2.el6_3.x86_64

???????? mysql-libs-5.1.66-2.el6_3.x86_64

???????? mysql-5.1.66-2.el6_3.x86_64

???????? mysql-connector-java-5.1.17-6.el6.noarch

???????? mysql-server-5.1.66-2.el6_3.x86_64

???????? 注：各機器有可能不相同，軟件列表視實際情況。

2.2??????? 卸載舊的版本

???????? rpm -e 軟件包名 --nodeps --allmatches (不理會依賴關系，刪除所有上一步查出來的相同的mysql)

???????? [root@localhost home]#? rpm -e mysql-connector-odbc-3.51.26r1127-1.el5 --nodeps --allmatches

???????? [root@localhost home]#? rpm -e libdbi-dbd-mysql-0.8.1a-1.2.2 --nodeps --allmatches

???????? [root@localhost home]#? rpm -e mysql-server-5.0.77-3.el5 --nodeps --allmatches

???????? [root@localhost home]#? rpm -aq | grep mysql

???????? [root@localhost home]#

???????? 將老版本的幾個文件手動刪除

???????? #rm -f /etc/my.cnf

???????? #rm -rf /var/lib/mysql

???????? #rm -rf /var/share/mysql

???????? #rm -rf /usr/bin/mysql*

2.3??????? 安裝

解壓.zip安裝包

unzip V46610-01-MySQL Database 5.6.20 RPM for Oracle Linux? RHEL 6 x86 (64bit).zip

得到如下軟件包：

???????? MySQL-client-advanced-5.6.20-1.el6.x86_64.rpm

???????? MySQL-devel-advanced-5.6.20-1.el6.x86_64.rpm

???????? MySQL-server-advanced-5.6.20-1.el6.x86_64.rpm

???????? MySQL-embedded-advanced-5.6.20-1.el6.x86_64.rpm

???????? MySQL-shared-advanced-5.6.20-1.el6.x86_64.rpm

???????? MySQL-shared-compat-advanced-5.6.20-1.el6.x86_64.rpm

???????? MySQL-test-advanced-5.6.20-1.el6.x86_64.rpm

???????? README.txt

2.3.1????????? 安裝服務器

???????? #rpm -ivh MySQL-server-advanced-5.6.****-1.el6.x86_64.rpm

???????? A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !

???????? You will find that password in '/root/.mysql_secret'.

???????? You must change that password on your first connect,

???????? no other statement but 'SET PASSWORD' will be accepted.

???????? See the manual for the semantics of the 'password expired' flag.

???????? Also, the account for the anonymous user has been removed.

???????? In addition, you can run:

?????????????????? /usr/bin/mysql_secure_installation

???????? which will also give you the option of removing the test database.

???????? This is strongly recommended for production servers.

???????? See the manual for more instructions.

???????? Please report any problems at http://bugs.mysql.com/

???????? The latest information about MySQL is available on the web at

???????? http://www.mysql.com

???????? Support MySQL by buying support/licenses at http://shop.mysql.com

???????? New default config file was created as /usr/my.cnf and

???????? will be used by default by the server when you start it.

???????? You may edit this file to change server settings

2.3.2????????? 安裝客戶端：

???????? #rpm -ivh MySQL-client-advanced-5.6.****-1.el6.x86_64.rpm

?????????????????? 說明:不安裝mysql-client是不能使用mysql工具登陸到mysql數據庫

???????? 其他軟件包選擇性安裝：

?????????????????? #rpm -ivh MySQL-devel-advanced-5.6.20-1.el6.x86_64.rpm

2.4??????? 登錄Mysql

oracle收購Mysql后,新版的mysql剛安裝完mysql后不能直接輸入mysql登錄數據庫，???????? 而是設置了默認free password密碼，默認密碼放在了/root/.mysql_secret文件中，登陸后需要修改密碼。

1. 查看文件尋找密碼

??????? cat /root/.mysql_secret

1. 以root用戶身份登陸MySQL

?????????????????? 服務啟動和關閉：service mysql start ?????????????????? service mysql stop

?????????????????? ps -u mysql 可以查看mysql用戶（安裝MySQL時，系統自動創建該用戶）的進程

???????????????????????????????????? ? PID TTY????????? TIME CMD

??????????????????????????????????? ? 7586 ??????? ??? ???????? 00:00:50 mysqld??????????????? 守護進程

?????????????????? 登陸服務器：

??????????????????????????? mysql -u root –p

??????????????????????????? 輸入從.mysql_secret中讀到的密碼登錄。

?????????????????? 如果登錄期間發生異常，無法登陸：

??????????????????????????? 使用root將mysql的后臺進程kill -9 PID

1. 修改密碼

??????????????????????????? mysql> set password=password('123456'); ? 將密碼設置為：123456

??????????????????????????? 密碼修改成后，再次登錄使用：

??????????????????????????? $mysql -uroot -p123456? 即可。

1. 查看數據庫

??????????????????????????? mysql> show databases;

???????????????????????????

1. 退出登錄 quit/exit

3????? mysql的基本操作

3.1??????? 數據庫CURD

對數據庫進行增(create)、刪(delete)、改(update)、查(Retrieve)操作。

3.1.1????????? 創建數據庫

創建一個名稱為mydb1的數據庫。

???????? create database mydb1;

創建一個使用utf-8字符集的mydb2數據庫。

???????? create database mydb2 character set utf8;

創建一個使用utf-8字符集，并帶校對規則的mydb3數據庫。會對存入的數據進行檢查。

???????? create database mydb3 character set utf8 collate utf8_general_ci;

3.1.2????????? 查看數據庫

顯示所有數據庫

???????? show databases;

顯示創建數據庫的語句信息

???????? show create database mydb2;

???????? “ ` ”（ESC鍵 下面的按鍵），表示反引號，默認情況下，反引號括起來的字符串，區分大小寫。

???????? show create database mydb1;

注意 ：mysql默認語言集是latin1，每次在創建數據庫的時候應指定字符集。Oracle是在安裝時，即指定了字符集。

3.1.3????????? 修改數據庫

修改mydb1的字符集為utf8(不能修改數據庫名)

???????? alter database mydb1 character set utf8;??????

3.1.4????????? 刪除數據庫

???????? 刪除數據庫mydb3

?????????????????? drop database mydb3;???

3.2??????? 表的CURD

對表本身進行操作：創建，查看，修改，刪除

3.2.1????????? 創建表

???????? create table t1 (id int, name varchar(20))?

???????? 但此時會報錯誤：ERROR 1046 (3D000): No database selected。注意，在mysql中對表操作前，必須先選擇所使用的數據庫。

?????????????????? use mydb2;

???????? 查看當前選擇的數據庫中的表：

?????????????????? show tables;

???????? 查看表結構：

?????????????????? desc t1;

在Mysql中顯示多行數據應該在查詢語句結尾處添加 \G來替換結束標記“；”

???????? 查看創建表的語法：

???????? ???????? show create table t1;??????????????? ENGINE=InnoDB 默認指定的存儲引擎 innoDB。

mysql中的數據類型：

?

bit???? 1位　??????? 可以指定位數，如：bit(3)

int???? 2字節 ???? 可以指定最大位數，如：int<4>　最大為4位的整數

float?? 2個字節　可以指定最大的位數和最大的小數位數，如：float<5,2> 最大為一個5位的數，小數位最多2位

double　4個字節　可以指定最大的位數和最大的小數位數，如：float<6,4> 最大為一個6位的數，小數位最多4位

char　? 必須指定字符數,如char(5) 為不可變字符　即使存儲的內容為'ab',也是用5個字符的空間存儲這個數據

varchar　必須指定字符數,如varchar(5) 為可變字符　如果存儲的內容為'ab',占用2個字符的空間；如果為'abc',則占用3個字符的空間

text: 大文本(大字符串)

blob：二進制大數據　如圖片，音頻文件，視頻文件

date: 日期　如：'1921-01-02'

datetime: 日期+時間　如：'1921-01-02 12:23:43'

timeStamp: 時間戳，自動賦值為當前日期時間

創建一個員工表：

create table employee(empno int, ename varchar(20), sal int);

3.2.2????????? 查看表

???????? 查看所有的表：

?????????????????? show tables;

???????? 查看指定表的創建語句

?????????????????? show create table employee;

?????????????????? 注意，mysql表名稱區分大小寫

???????? 顯示指定表的結構：

?????????????????? desc employee;

3.2.3????????? 修改表

???????? 更改表名：??? rename table employee to worker;

???????? 增加一個字段：alter table employee add column height double;?? （column關鍵字在Oracle中，添加則語法錯誤）

???????? 修改一個字段：alter table employee modify column height float;

???????? 修改字段名: alter table employee change column height height1 float;

???????? 刪除一個字段：alter table employee drop column height1;

???????? 修改表的字符集:alter table employee character set gbk;

3.2.4????????? 刪除表

???????? 刪除employee表

???????? drop table employee;??????????????? (MySQL中不能使用purge，添加會出現語法錯誤)

3.3??????? 表數據的CURD

3.3.1????? create數據

???????? 創建一個員工表，新建employee表并向表中添加一些記錄：

create table employee(

id int,

name varchar(20),

sex int,

birthday date,

salary double,

entry_date date,

resume text

);

insert into employee values(1,'張三',1,'1983-04-27',15000,'2012-06-24','一個大牛');

insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(2,'李四',1,'1984-02-22',10000,'2012-07-24','一個中牛');

insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(3,'王五',0,'1985-08-28',7000,'2012-08-24','一個小蝦');

3.3.2????? update數據

???????? 將所有員工薪水都增加500元。

?????????????????? update employee set salary=salary+500;

???????? 將王五的員工薪水修改為10000元，resume改為也是一個中牛

?????????????????? update employee set salary=10000, resume='也是一個中牛' where name='王五';

3.3.3????? delete數據

???????? 刪除表中姓名為王五的記錄。

?????????????????? delete from employee where name='王五';??????????? 【注意from不能省略】

???????? 刪除表中所有記錄。

?????????????????? delete from employee;

???????? 使用truncate刪除表中記錄。

?????????????????? truncate employee;--無條件 效率高

3.3.4????? Retrieve數據

???????? select id, name as "名字", salary "月薪", salary*12 年薪? from employee where id >=2;

3.3.5????? 綜合案例

創建一個學生表，插入數據

create table student(

id int,

name varchar(20),

chinese int,

english int,

math int

);

insert into student(id,name,chinese,english,math) values(1, '范建',80,85,90);

insert into student(id,name,chinese,english,math) values(2,'羅況',90,95,95);

insert into student(id,name,chinese,english,math) values(3,'杜子騰',80,96,96);

insert into student(id,name,chinese,english,math) values(4,'范冰',81,97,85);

insert into student(id,name,chinese,english,math) values(5,'申晶冰',85,84,90);

insert into student(id,name,chinese,english,math) values(6,'郝麗海',92,85,87);

insert into student(id,name,chinese,english,math) values(7,'郭迪輝',75,81,80);

insert into student(id,name,chinese,english,math) values(8,'拎壺沖',77,80,79);

insert into student(id,name,chinese,english,math) values(9,'任我行',95,85,85);

insert into student(id,name,chinese,english,math) values(10,'史泰香',94,85,84);

基礎SQL

查詢表中所有學生的信息。

??????????????????????????? select * from student;

?????????????????? 查詢表中所有學生的姓名和對應的英語成績。

??????????????????????????? select name,english from student;

?????????????????? 過濾表中重復數據。

??????????????????????????? select english from student;

??????????????????????????? select DISTINCT english from student;

??????????????????????????? select DISTINCT english,name from student;

??????????????????????????? select english+chinese+math from student;

??????????????????????????? select english+chinese+math as 總分 from student;

??????????????????????????? select name,english+chinese+math as 總分 from student;

?????????????????? 在所有學生英語分數上加10分特長分。

??????????????????????????? select name,english+10 from student;

?????????????????? 統計每個學生的總分。

??????????????????????????? select english+chinese+math from student;

?????????????????? 使用別名表示學生分數

??????????????????????????? select name,english+chinese+math as 總分 from student;

??????????????????????????? select name,english+chinese+math 總分 from student;

?????????????????? 查詢姓名為何東的學生成績

??????????????????????????? select * from student where name='何東';

?????????????????? 查詢英語成績大于90分的同學

??????????????????????????? select * from student where english>90;

?????????????????? 查詢總分大于250分的所有同學

??????????????????????????? select * from student where english+chinese+math>250;

?????????????????? 查詢英語分數在 85－95之間的同學。

??????????????????????????? select * from student where english>=85 and english<=95;

??????????????????????????? select * from student where english between 85 and 95;

?????????????????? 查詢數學分數為84,90,91的同學。

??????????????????????????? select * from student where math=84 or math=90 or math=91;

??????????????????????????? select * from student where math in(84,90,91);

?????????????????? 查詢所有姓何的學生成績。

??????????????????????????? select * from student where name like '何%';

?????????????????? 查詢數學分>85，語文分>90的同學。

??????????????????????????? select * from student where math>85 and chinese>90;

?????????????????? 對數學成績排序后輸出。

??????????????????????????? select * from student order by math;

?????????????????? 對總分排序后輸出，然后再按從高到低的順序輸出

??????????????????????????? select * from student order by math+chinese+english desc;

?????????????????? 對姓何的學生成績排序輸出

??????????????????????????? select * from student where name like '何%' order by math+chinese+english desc;

??????????????????????????? select name, math+chinese+english from student where name like '何%' order by math+chinese+english desc;

?????????????????? 統計一個班級共有多少學生？

??????????????????????????? select count(*) from student;

?????????????????? 統計數學成績大于90的學生有多少個？

??????????????????????????? select count(*) from student where math>90;

?????????????????? 統計總分大于250的人數有多少？

??????????????????????????? select count(*) from student where math+chinese+english>250;

?????????????????? 統計一個班級數學總成績？

??????????????????????????? select sum(math) from student;

?????????????????? 統計一個班級語文、英語、數學各科的總成績

??????????????????????????? select sum(math), sum(chinese), sum(english) from student;

?????????????????? 統計一個班級語文、英語、數學的成績總和

??????????????????????????? select sum(math+chinese+english)from student;

??????????????????????????? select sum(math)+sum(chinese)+sum(english) from student;

?????????????????? 求一個班級數學平均分？

??????????????????????????? select avg(math) from student;

?????????????????? 求一個班級總分平均分

??????????????????????????? select avg(math+chinese+english)from student;

??????????????????????????? select avg(math)+avg(chinese)+avg(english) from student;

?????????????????? 求班級最高分和最低分

??????????????????????????? select max(math+chinese+english),min(math+chinese+english) from student;

分組數據

為學生表，增加一個班級列，練習分組查詢。??????????????????

?????????????????? ???????? alter table student add column class_id int;???????????

?????????????????? 注意語法：Oracle中不能有“column”關鍵字，MySQL中有沒有“column”都可以執行。

?????????????????? 更新表：

??????????????????????????? update student set class_id=1 where id<=5;

??????????????????????????? update student set class_id=2 where id>5;

??????????????????????????? (update student set class_id=2 where id between 6 and 10;)

?????????????????? 查出各個班的總分，最高分。

?????????????????? 求各個班級 英語的平均分：

??????????????????????????? select classid, avg(english)

??????????????????????????? from student

??????????????????????????? group by class_id

?????????????????? 如根據組函數的語法要求，將select后增加name列，而不加至group by 之后：

??????????????????????????? select name, classid, avg(english)

??????????????????????????? from student

??????????????????????????? group by classid;

?????????????????? 會發現MySQL檢查不出錯誤。相比Oracle數據庫，MySQL分組檢查不嚴格。

??????????????????????????? select sum(math+chinese+english),max(math+chinese+english) from student group by class_id;

?????????????????? 查詢出班級總分大于1300分的班級ID

??????????????????????????? select class_id from student group by class_id having sum(math+chinese+english)>1300;

??????????????????????????? select class_id from student where sum(math+chinese+english)>1300 group by class_id ;

?????????????????? 對于組函數的應用與Oracle類似，可以應用于Having中，但不能用于where子句中。

3.4??????? 日期時間函數

?????????????????? MySQL里面時間分為三類：時間、日期、時間戳(含有時分秒的sysdate)。

如執行：select now(), year(now()) 年, month(now()) 月, day(now()) 日, date(now());

??????????????????

select CURRENT_DATE() , CURRENT_TIME(),? CURRENT_TIMESTAMP() from dual;

??????????????????

昨天、今天、明天：

?????????????????? ???????? select now()-1 昨天, now() 今天, now()+1 明天 from dual;

?????????????????? 發現與Oracle中的日期加減操作有所不同。

?

???????? select date_add(now(), INTERVAL 2 year) from dual;???? //增加兩年

???????? select date_add(now(), INTERVAL -1 day) 昨天, now() 今天, date_add(now(), INTERVAL +1 day) 明天; ?

3.5??????? 字符串相關函數

???????? select concat('hello ', 'mysql ', 'haha ', 'hehe ') from dual;??

???????? Oracle默認只能拼兩個，如需多個拼接可以使用嵌套。

???????? select 'hello ' || 'mysql ' from dual;???????? ‘||’ 在 MySQL不可以使用。

?

日期轉字符串：

???????? 在MySQL中沒有to_date函數，進行日期轉換需使用date_format()來代替。

???????? select date_format('2013-5-11', 'yyyy-mm-dd') from dual;? 在Oracle中的‘yyyy-mm-dd’MySQL下不支持。

???????? select date_format(now(), '%Y-%m-%d') from dual; ?????????????????????? ?y和Y不一樣。

???????? select date_format(now(), '%Y-%c-%d %h:%i:%s') from dual; ????? ?c和m、M不一樣

???????? 所以yyyy-mm-dd hh24:mi:ss格式在MySQL中對應'%Y-%c-%d %h:%i:%s'

字符串轉日期：

???????? select str_to_date('2013-6-04 05:14:15' , '%Y-%c-%d %h:%i:%s') from dual;

3.6??????? 數學相關函數

?

3.7??????? 多表查詢

創建多表查詢案例——MySQL建表_仿照oracle建表腳本.sql? 【mysql> source 絕對路徑/腳本名】

Oracle中連接方法：

???????? ???????? 等值連接

???????? ???????? 不等值連接????

???????? ???????? 外連接

???????? ???????? 自連接

MySQL 使用SQL99標準的連接查詢（JOIN..ON..）

3.7.1????? 交叉連接：

???????? 叉集，即笛卡爾集

?????????????????? select e.*, d.*

?????????????????? from emp e cross join dept d

???????? 無連接條件

3.7.2????? 滿外聯接

???????? 任一邊有值就會顯示。

?????????????????? select e.*, d.*

?????????????????? from emp e full outer join dept d????????????

?????????????????? on e.deptno=d.deptno

???????? 也可以省略outer關鍵字

3.7.3????? 內連接

???????? 只返回滿足連接條件的數據（兩邊都有的才顯示）。 對應等值連接

?????????????????? select e.*, d.*

?????????????????? from emp e inner join dept d

?????????????????? on e.deptno=d.deptno

???????? 也可以省略inner關鍵字。

???????? 對應Oracle寫法：

select e.*, d.*

from emp e , dept d

where e.deptno=d.deptno

3.7.4????? 左外連接

???????? 左邊有值才顯示。

?????????????????? select e.*, d.*

?????????????????? from emp e left outer join dept d

?????????????????? on e.deptno=d.deptno

???????? 也可以省略outer關鍵字

3.7.5????? 右外連接

???????? 右邊邊有值才顯示。

?????????????????? select e.*, d.*

?????????????????? from emp e right outer join dept d

?????????????????? on e.deptno=d.deptno

???????? 也可以省略outer關鍵字

???????? 【注意】SQL99中，外鏈接取值與關系表達式=號左右位置無關。取值跟from后表的書寫順序有關。

“xxx left outer join yyy” 則為取出xxx的內容。

??????????????????????????? “xxx right outer join yyy”則為取出yyy的內容

3.7.6????? 對比練習

題目1：

查詢員工信息,員工號,姓名,月薪,部門名稱

select ...

from emp e, dept d

where e.deptno = d.deptno;

Oracle實現：

?????????????????? select e.deptno, e.ename, e.sal, d.dname

?????????????????? from emp e, dept d

?????????????????? where e.deptno = d.deptno

SQL99實現：

?????????????????? select e.deptno, e.ename, e.sal, d.dname

?????????????????? from emp e inner join dept d

?????????????????? on e.deptno = d.deptno

???????? 對比記憶規律：

?????????????????? “,” → [inner] join

?????????????????? where → on?

???????? 對比結論：mysql能識別Oracle中使用 = 連接的書寫方法。

題目2：

統計各個部門員工總人數

分析：部門包括10/20/30 → 分組

各部門人數 → 多表

select ...

from emp e, dept d

where d.deptno = e.deptno

group by ...??????????????? ????????

(注意：group by后面出現的子集應在select下進行檢索)

實現為：

select d.deptno, d.dname, count(e.deptno)

from dept d, emp e

where d.deptno = e.deptno

group by d.deptno, d.dname

???????? 查詢發現沒有40號部門。此時應使用外鏈接保存一側結果。

oracle實現：

select d.deptno, d.dname , count(e.deptno)

from dept d, emp e

where d.deptno = e.deptno (+)

group by d.deptno, d.dname

SQL99實現：

select d.deptno, d.dname , count(e.deptno)

from dept d left outer join emp e

on d.deptno = e.deptno

group by d.deptno, d.dname

對比記憶規律：

?????????????????? “,”→ left/right outer join

?????????????????? where → on?

???????? 結論：oracle的語法(+) mysql不支持

3.7.7????? 自連接

查詢員工、老板信息，顯示: xxx的老板是xxx

分析：將一張emp表當成兩張表看待：員工表、老板表（員工表的老板 是 老板表的員工）

1. 先按照oracle語法寫???

select e.ename, b.ename

from emp e, emp b

where e.mgr = b.empno

1. 完善顯示格式concat

select concat( e.ename, ' 的老板是 ',? b.ename )

from emp e, emp b

where e.mgr = b.empno

1. 顯示king的老板

select concat( e.ename, ' 的老板是 ',? b.ename )

from emp e, emp b

where e.mgr = b.empno (+)

1. 改用MySQL支持的SQL99語法

select concat( e.ename, ' 的老板是 ',? b.ename )

from emp e left outer join emp b

on e.mgr = b.empno ;

1. 濾空修正nvl

select concat( e.ename, ' 的老板是 ',? nvl(b.ename, '他自己' ) )

from emp e left outer join emp b

on e.mgr = b.empno ;

結論 nvl 在mysql下不能使用： ERROR 1305 (42000): FUNCTION mydb61.nvl does not exist

1. 濾空修正 ifnull

select concat( e.ename, ' 的老板是 ',? ifnull(b.ename, '他自己' ) )???

from emp e left outer join emp b

on e.mgr = b.empno ;

注意：

???????? Oracle中有一個通用函數，與MYSQL中的ifnull函數名字相近：

???????? nullif：如nullif(a, b) 當 a = b 時返回null, 不相等的時候返回a值。nullif('L9,999.99', 'L9,999.99')

???????? mysql中nullif（）函數也存在。

3.8??????? 表的約束

???????? *定義主鍵約束　primary key:?????? 不允許為空，不允許重復

???????? *定義主鍵自動增長　auto_increment

???????? *定義唯一約束　unique

???????? *定義非空約束　not null

???????? *定義外鍵約束　constraint ordersid_FK foreign key(ordersid) references orders(id)

???????? *刪除主鍵：alter table tablename drop primary key ;

MySQL中約束舉例：

???????? create table myclass (

?????????????????? id INT(11) primary key auto_increment,

?????????????????? name varchar(20) unique

???????? );

???????? create table student (

?????????????????? id INT(11) primary key auto_increment,

?????????????????? name varchar(20) unique,

?????????????????? passwd varchar(15) not null,

?????????????????? classid INT(11),? ???????????????????????????????????????????????????????????

?????????????????? constraint stu_classid_FK foreign key(classid) references myclass(id)

???????? );

???????? check約束在MySQL中語法保留，但沒有效果。

??? 可以通過SELECT * FROM information_schema.`TABLE_CONSTRAINTS`;查看表的約束。

4????? mysql中文亂碼問題

三層因素：

因素1： MySQL自身的設計

【實驗步驟1】：???

mysql> show variables like 'character%';??????? 查看所有應用的字符集

【實驗步驟2】：???

$ mysql -uroot -p123456 --default_character_set=gbk 指定字符集登錄數據庫

???????? mysql> show variables like 'character%';

???????? 影響了與客戶端相關聯的 3處 (最外層)

在這種狀態下執行use mydb2;

???????? mysql> select * from employee;???????????????????????

???????? 查看輸出，會出現亂碼。

???????? 原來的三條數據，是以utf8的形式存儲到數據庫中，當使用gbk連接以后，數據庫仍按照utf8的形式將數據返回，出錯。

【實驗步驟3】：

在該環境下插入帶有中文的一行數據。

???????? mysql> insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(10,'張三瘋',1,'1983-09-21',15000,'2012-06-24','一個老牛');

???????? ERROR 1366 (HY000): Incorrect string value: '\x80\xE4\xB8\xAA\xE8\x80...' for column 'resume' at row 1

因素2：操作系統的語言集

???????? linux操作系統 是一個 多用戶的操作

???????? [root@localhost ~]# cat /etc/sysconfig/i18n

???????? LANG="zh_CN.UTF-8"?????

???????? 操作系統的菜單按照zh_CN顯示,? 文件存儲按照utf8

???????? linux操作系統語言環境 和 用戶的配置的語言環境LANG 相互影響

???????? [mysql01@localhost ~]$ echo $LANG

???????? zh_CN.UTF-8

【實驗步驟4】：

修改用戶下的.bash_profile 中的LANG，屏蔽操作系統的LANG設置。再查數據庫

?????????????????? mysql> select * from employee;

結論： 用戶的LANG設置，影響了應用程序的語言環境,導致myql的語言環境發生了改變：

???????? mysql> show variables like 'character%';

???????? 在此環境下，檢索中文會出現亂碼。

【實驗步驟5】：在上述環境之下，向數據庫中插入中文。

insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(5,'張三瘋',1,'1987-05-21',15000,'2014-06-24','一個老牛');

數據能插入到數據庫中，沒 有 報 任 何 錯 誤！但顯示不正確。

因素3：文件存儲格式

?