環(huán)境：rhel7系統(tǒng)mysql
工具：secureCRT、UE

1.mysql的基本操作

1.1數(shù)據(jù)庫CURD

對數(shù)據(jù)庫進(jìn)行增(create)帮毁、刪(delete)涩堤、改(update)、查(Retrieve)操作暮芭。

1.1.1創(chuàng)建數(shù)據(jù)庫

• 創(chuàng)建一個(gè)名稱為mydb1的數(shù)據(jù)庫。
  create database mydb1;

• 創(chuàng)建一個(gè)使用utf-8字符集的mydb2數(shù)據(jù)庫欲低。
  create database mydb2 character set utf8;

• 創(chuàng)建一個(gè)使用utf-8字符集辕宏，并帶校對規(guī)則的mydb3數(shù)據(jù)庫。會(huì)對存入的數(shù)據(jù)進(jìn)行檢查伸头。
  create database mydb3 character set utf8 collate utf8_general_ci;

1.1.2查看數(shù)據(jù)庫

顯示所有數(shù)據(jù)庫
show databases;

顯示創(chuàng)建數(shù)據(jù)庫的語句信息
show create database mydb2;

“` ”（ESC鍵 下面的按鍵）匾效，表示反引號(hào)，默認(rèn)情況下恤磷，反引號(hào)括起來的字符串面哼，區(qū)分大小寫。
show create database mydb1;

注意 ：mysql默認(rèn)語言集是latin1扫步，每次在創(chuàng)建數(shù)據(jù)庫的時(shí)候應(yīng)指定字符集魔策。Oracle是在安裝時(shí)，即指定了字符集河胎。

1.1.3修改數(shù)據(jù)庫

修改mydb1的字符集為utf8(不能修改數(shù)據(jù)庫名)
alter database mydb1 character set utf8;

1.1.4刪除數(shù)據(jù)庫

刪除數(shù)據(jù)庫mydb3
drop database mydb3;

1.2表的CURD

對表本身進(jìn)行操作：創(chuàng)建闯袒，查看，修改游岳，刪除

1.2.1創(chuàng)建表

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

但此時(shí)會(huì)報(bào)錯(cuò)誤：ERROR 1046 (3D000): No database selected政敢。注意，在mysql中對表操作前胚迫，必須先選擇所使用的數(shù)據(jù)庫喷户。
use mydb2;

查看當(dāng)前選擇的數(shù)據(jù)庫中的表：
show tables;

查看表結(jié)構(gòu)：
desc t1;

在Mysql中顯示多行數(shù)據(jù)應(yīng)該在查詢語句結(jié)尾處添加 \G來替換結(jié)束標(biāo)記“；”

查看創(chuàng)建表的語法：
show create table t1; ENGINE=InnoDB 默認(rèn)指定的存儲(chǔ)引擎 innoDB访锻。

• mysql中的數(shù)據(jù)類型

mysql數(shù)據(jù)類型

• bit 1位　 可以指定位數(shù)褪尝，如：bit(3)
• int 2字節(jié) 可以指定最大位數(shù)闹获，如：int<4>　最大為4位的整數(shù)
• float 2個(gè)字節(jié)　可以指定最大的位數(shù)和最大的小數(shù)位數(shù)，如：float<5,2> 最大為一個(gè)5位的數(shù)河哑，小數(shù)位最多2位
• double　4個(gè)字節(jié)　可以指定最大的位數(shù)和最大的小數(shù)位數(shù)避诽，如：float<6,4> 最大為一個(gè)6位的數(shù)沙庐，小數(shù)位最多4位
• char　 必須指定字符數(shù),如char(5) 為不可變字符　即使存儲(chǔ)的內(nèi)容為'ab',也是用5個(gè)字符的空間存儲(chǔ)這個(gè)數(shù)據(jù)
• varchar　必須指定字符數(shù),如varchar(5) 為可變字符　如果存儲(chǔ)的內(nèi)容為'ab',占用2個(gè)字符的空間轨功；如果為'abc',則占用3個(gè)字符的空間
• text: 大文本(大字符串)
• blob：二進(jìn)制大數(shù)據(jù)　如圖片花盐，音頻文件算芯，視頻文件
• date: 日期　如：'1921-01-02'
• datetime: 日期+時(shí)間　如：'1921-01-02 12:23:43'
• timeStamp: 時(shí)間戳职祷，自動(dòng)賦值為當(dāng)前日期時(shí)間

創(chuàng)建一個(gè)員工表：
create table employee(empno int, ename varchar(20), sal int);

1.2.2查看表

查看所有的表：
show tables;

查看指定表的創(chuàng)建語句
show create table employee; 注意，mysql表名稱區(qū)分大小寫

顯示指定表的結(jié)構(gòu)：
desc employee;

1.2.3修改表

更改表名：
rename table employee to worker;

增加一個(gè)字段：
alter table employee add column height double; （column關(guān)鍵字在Oracle中，添加則語法錯(cuò)誤）

修改一個(gè)字段：
alter table employee modify column height float;

刪除一個(gè)字段：
alter table employee drop column height;

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

1.2.4刪除表

刪除employee表
drop table employee; (MySQL中不能使用purge，添加會(huì)出現(xiàn)語法錯(cuò)誤)

1.3表數(shù)據(jù)的CURD

1.3.1create數(shù)據(jù)

創(chuàng)建一個(gè)員工表，新建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','一個(gè)大牛');
insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(2,'李四',1,'1984-02-22',10000,'2012-07-24','一個(gè)中牛');
insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(3,'王五',0,'1985-08-28',7000,'2012-08-24','一個(gè)小蝦');

1.3.2update數(shù)據(jù)

將所有員工薪水都增加500元蜘醋。
update employee set salary=salary+500;

將王五的員工薪水修改為10000元编检，resume改為也是一個(gè)中牛
update employee set salary=10000, resume='也是一個(gè)中牛' where name='王五';

1.3.3delete數(shù)據(jù)

刪除表中姓名為王五的記錄厕怜。
delete from employee where name='王五'; 【注意from不能省略】

刪除表中所有記錄生百。
delete from employee;

使用truncate刪除表中記錄缀程。
truncate employee;--無條件 效率高

1.3.4Retrieve數(shù)據(jù)

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

1.3.5綜合案例

創(chuàng)建一個(gè)學(xué)生表摆昧，插入數(shù)據(jù)

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);
---------------------------------------
基礎(chǔ)SQL
查詢表中所有學(xué)生的信息。
select * from student;

查詢表中所有學(xué)生的姓名和對應(yīng)的英語成績曼追。
select name,english from student;

過濾表中重復(fù)數(shù)據(jù)针史。
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;

在所有學(xué)生英語分?jǐn)?shù)上加10分特長分。
select name,english+10 from student;

統(tǒng)計(jì)每個(gè)學(xué)生的總分脆淹。
select english+chinese+math from student;

使用別名表示學(xué)生分?jǐn)?shù)
select name,english+chinese+math as 總分 from student;
select name,english+chinese+math 總分 from student;

查詢姓名為何東的學(xué)生成績
select * from student where name='何東';

查詢英語成績大于90分的同學(xué)
select * from student where english>90;

查詢總分大于250分的所有同學(xué)
select * from student where english+chinese+math>250;

查詢英語分?jǐn)?shù)在 85－95之間的同學(xué)盖溺。
select * from student where english>=85 and english<=95;
select * from student where english between 85 and 95;

查詢數(shù)學(xué)分?jǐn)?shù)為84,90,91的同學(xué)。
select * from student where math=84 or math=90 or math=91;
select * from student where math in(84,90,91);

查詢所有姓何的學(xué)生成績蝇庭。
select * from student where name like '何%';

查詢數(shù)學(xué)分>85，語文分>90的同學(xué)。
select * from student where math>85 and chinese>90;

對數(shù)學(xué)成績排序后輸出。
select * from student order by math;

對總分排序后輸出，然后再按從高到低的順序輸出
select * from student order by math+chinese+english desc;

對姓何的學(xué)生成績排序輸出
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;

統(tǒng)計(jì)一個(gè)班級(jí)共有多少學(xué)生驳棱？
select count(*) from student;

統(tǒng)計(jì)數(shù)學(xué)成績大于90的學(xué)生有多少個(gè)？
select count(*) from student where math>90;

統(tǒng)計(jì)總分大于250的人數(shù)有多少？
select count(*) from student where math+chinese+english>250;

統(tǒng)計(jì)一個(gè)班級(jí)數(shù)學(xué)總成績？
select sum(math) from student;

統(tǒng)計(jì)一個(gè)班級(jí)語文恨闪、英語轻姿、數(shù)學(xué)各科的總成績
select sum(math), sum(chinese), sum(english) from student;

統(tǒng)計(jì)一個(gè)班級(jí)語文互亮、英語桨吊、數(shù)學(xué)的成績總和
select sum(math+chinese+english)from student;
select sum(math)+sum(chinese)+sum(english) from student;

求一個(gè)班級(jí)數(shù)學(xué)平均分？
select avg(math) from student;

求一個(gè)班級(jí)總分平均分
select avg(math+chinese+english)from student;
select avg(math)+avg(chinese)+avg(english) from student;

求班級(jí)最高分和最低分
select max(math+chinese+english),min(math+chinese+english) from student;

--分組數(shù)據(jù)
為學(xué)生表伸刃，增加一個(gè)班級(jí)列，練習(xí)分組查詢。        
alter table student add column class_id int;        
注意語法：Oracle中不能有“column”關(guān)鍵字，MySQL中有沒有“column”都可以執(zhí)行。

--更新表：
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;)

查出各個(gè)班的總分任连，最高分例诀。
求各個(gè)班級(jí) 英語的平均分：
select classid, avg(english) 
from student
group by class_id
如根據(jù)組函數(shù)的語法要求随抠，將select后增加name列，而不加至group by 之后：
select name, classid, avg(english)
from student
group by classid;
會(huì)發(fā)現(xiàn)MySQL檢查不出錯(cuò)誤繁涂。相比Oracle數(shù)據(jù)庫拱她，MySQL分組檢查不嚴(yán)格。
select sum(math+chinese+english),max(math+chinese+english) from student group by class_id;

查詢出班級(jí)總分大于1300分的班級(jí)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 ;
對于組函數(shù)的應(yīng)用與Oracle類似扔罪，可以應(yīng)用于Having中秉沼，但不能用于where子句中。

1.4日期時(shí)間函數(shù)

MySQL里面時(shí)間分為三類：時(shí)間矿酵、日期唬复、時(shí)間戳(含有時(shí)分秒的sysdate)。
如執(zhí)行：select now(), year(now()) 年, month(now()) 月, day(now()) 日, date(now());

2

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

3

昨天全肮、今天敞咧、明天：
select now()-1 昨天, now() 今天, now()+1 明天 from dual;
發(fā)現(xiàn)與Oracle中的日期加減操作有所不同。

4

select date_add(now(), INTERVAL 2 year) from dual;  //增加兩年
select date_add(now(), INTERVAL -1 day) 昨天, now() 今天, date_add(now(), INTERVAL +1 day) 明天;  

5.字符串相關(guān)函數(shù)

select concat('hello ', 'mysql ', 'haha ', 'hehe ') from dual;   
Oracle默認(rèn)只能拼兩個(gè)倔矾，如需多個(gè)拼接可以使用嵌套妄均。
select 'hello ' || 'mysql ' from dual;  ‘||’ 在 MySQL不可以使用。

5

日期轉(zhuǎn)字符串：
在MySQL中沒有to_date函數(shù)哪自，進(jìn)行日期轉(zhuǎn)換需使用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īng)'%Y-%c-%d %h:%i:%s'
字符串轉(zhuǎn)日期：
select str_to_date('2013-6-04 05:14:15' , '%Y-%c-%d %h:%i:%s') from dual;

6.數(shù)學(xué)相關(guān)函數(shù)

6

7.多表查詢

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

Oracle中連接方法：

• 等值連接
• 不等值連接
• 外連接
• 自連接

MySQL 使用SQL99標(biāo)準(zhǔn)的連接查詢（JOIN..ON..）

7.1交叉連接

叉集，即笛卡爾集

select e.*, d.*
from emp e cross join dept d

無連接條件

7.2內(nèi)連接（等值鏈接）

只返回滿足連接條件的數(shù)據(jù)（兩邊都有的才顯示）胧华。 對應(yīng)等值連接

select e.*, d.*
from emp e inner join dept d
on e.deptno=d.deptno

也可以省略inner關(guān)鍵字寄症。
對應(yīng)Oracle寫法：

select e.*, d.*
from emp e , dept d
where e.deptno=d.deptno

7.3左外連接

左邊有值才顯示。

select e.*, d.*
from emp e left outer join dept d
on e.deptno=d.deptno

也可以省略outer關(guān)鍵字

7.4 右外連接

右邊邊有值才顯示矩动。

select e.*, d.*
from emp e right outer join dept d
on e.deptno=d.deptno

也可以省略outer關(guān)鍵字

7.5 滿外聯(lián)接

任一邊有值就會(huì)顯示有巧。

select e.*, d.*
from emp e full outer join dept d       
on e.deptno=d.deptno

也可以省略outer關(guān)鍵字

7.6 對比練習(xí)

題目1：
查詢員工信息,員工號(hào),姓名,月薪,部門名稱
select ...
from emp e, dept d
where e.deptno = d.deptno;

Oracle實(shí)現(xiàn)：
        select e.deptno, e.ename, e.sal, d.dname
        from emp e, dept d
        where e.deptno = d.deptno
SQL99實(shí)現(xiàn)：
        select e.deptno, e.ename, e.sal, d.dname
        from emp e inner join dept d
        on e.deptno = d.deptno
對比記憶規(guī)律：
        “,” → [inner] join 
        where → on  
對比結(jié)論：mysql能識(shí)別Oracle中使用 = 連接的書寫方法。


題目2：
統(tǒng)計(jì)各個(gè)部門員工總?cè)藬?shù)
分析：部門包括10/20/30 → 分組
各部門人數(shù) → 多表 
       select ...
       from emp e, dept d
       where d.deptno = e.deptno
       group by ...         
(注意：group by后面出現(xiàn)的子集應(yīng)在select下進(jìn)行檢索)
實(shí)現(xiàn)為：
       select d.deptno, d.dname, 
       count(e.deptno)
       from dept d, emp e
       where d.deptno = e.deptno
       group by d.deptno, d.dname 
查詢發(fā)現(xiàn)沒有40號(hào)部門悲没。此時(shí)應(yīng)使用外鏈接保存一側(cè)結(jié)果篮迎。

oracle實(shí)現(xiàn)：
       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實(shí)現(xiàn)：
       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 

對比記憶規(guī)律：
        “,”→ left/right outer join 
        where → on  
    結(jié)論：oracle的語法(+) mysql不支持

7.7 自連接

查詢員工、老板信息，顯示: xxx的老板是xxx
分析：將一張emp表當(dāng)成兩張表看待：員工表甜橱、老板表（員工表的老板 是 老板表的員工）
1.  先按照oracle語法寫    
select e.ename, b.ename
from emp e, emp b
where e.mgr = b.empno

2.  完善顯示格式concat
select concat( e.ename, ' 的老板是 ',  b.ename )
from emp e, emp b
where e.mgr = b.empno

3.  顯示king的老板
select concat( e.ename, ' 的老板是 ',  b.ename )
from emp e, emp b
where e.mgr = b.empno (+)

4.  改用MySQL支持的SQL99語法
select concat( e.ename, ' 的老板是 ',  b.ename )
from emp e left outer join emp b
on e.mgr = b.empno ;

5.  濾空修正nvl
select concat( e.ename, ' 的老板是 ',  nvl(b.ename, '他自己' ) )
from emp e left outer join emp b
on e.mgr = b.empno ;
結(jié)論 nvl 在mysql下不能使用： ERROR 1305 (42000): FUNCTION mydb61.nvl does not exist

6.  濾空修正 ifnull
select concat( e.ename, ' 的老板是 ',  ifnull(b.ename, '他自己' ) )    
from emp e left outer join emp b
on e.mgr = b.empno ;

注意：
    Oracle中有一個(gè)通用函數(shù)逊笆，與MYSQL中的ifnull函數(shù)名字相近：
    nullif：如nullif(a, b) 當(dāng) a = b 時(shí)返回null, 不相等的時(shí)候返回a值。nullif('L9,999.99', 'L9,999.99')
    mysql中nullif（）函數(shù)也存在岂傲。

3.8 表的約束

• 定義主鍵約束　primary key: 不允許為空难裆，不允許重復(fù)
• 定義主鍵自動(dòng)增長　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中語法保留，但沒有效果镊掖。