摘要：列與值必須按一一對(duì)應(yīng)增加多行劉備皇室成員孫策江東集團(tuán)曹操宦官后裔縱橫結(jié)合定位，增加數(shù)據(jù)。劉備每條記錄都有字段來約束。代表填充必須是和配合使用場景學(xué)號(hào)，。范圍的滿足取出不在第個(gè)欄目和不在第個(gè)欄目的商品或分別實(shí)現(xiàn)。

一臺(tái)服務(wù)器下有多個(gè)庫，一個(gè)庫下有1到多張表，表有多行多列的數(shù)據(jù)。
postgresql也是一個(gè)開源數(shù)據(jù)庫，而且sql標(biāo)準(zhǔn)執(zhí)行方面，比mysql要嚴(yán)格。

表格 --> 檔案袋 --> 人管理(MySql)

MySql5.1 & MySql5.5 穩(wěn)定版

鏈接數(shù)據(jù)庫

mysql -uusername -ppassworld
// 鏈接上服務(wù)器之后面對(duì)的是庫。 庫有一個(gè)或多個(gè).

查看所有數(shù)據(jù)庫

show databases
// 選擇庫之后，面對(duì)的是表。 表有一個(gè)表或多個(gè)表

創(chuàng)建庫

create database zf [charset utf8]

刪除庫

drop database zf;
// Mysql 表/列可以修改名字，庫名字不可以修改.

選擇數(shù)據(jù)庫

use test 
// use 庫名   

查看表

show tables; 
// 查看庫底下有幾張表

創(chuàng)建表

create table stu ( snum int,  sname  varchar(10)  )engine  myisam charset utf8;

刪除表

drop table stu;

改表名

rename table oldTable to newTable;

清空表

truncate stu;
/**
truncate 與 delete 區(qū)別:
truncate 相當(dāng)于刪除表再重建一張同樣結(jié)構(gòu)的表，操作之后得到一張全新的表.
delete 是從刪除數(shù)據(jù)行的層面來操作的，如果有自增的影響還在.
**/

插入數(shù)據(jù)

insert into stu values ( 1, "zhangsan" ), ( 2, "wangwu" );

查詢簡單數(shù)據(jù)

select * from stu;

查看表結(jié)構(gòu) desc tablename；

insert into

往哪張表，添加信息(行)。

給哪幾列添加值。

分別是什么值。

# 創(chuàng)建表
create table class (
    id int primary key auto_increment,
    sname varchar(10) not null default "",
    gender char(1) not null default "",
    company varchar(20) not null default "",
    salary decimal(6,2) not null default 0.00,
    fanbu smallint not null default 0
) engine myisam charset utf8;

# 插入數(shù)據(jù)
insert into class (id, sanme, gender, company, salary, fanbu) values (1, "天機(jī)老人", "男", "zf", 9000.00, 200);

插入所有列

如果插入所有的列，則可以不聲明待插入的列。
不聲明插入的列，則理解為依次插入"所有"列

insert into class (2, "李尋歡", "男", "jh", 7000, 130);

注意:
id是自增型，插入時(shí)不必為其賦值。是錯(cuò)誤的，需要對(duì)應(yīng)，添加該值或者null。
列與值必須按一一對(duì)應(yīng)

增加多行

insert into class (sname, company, salary) values ("劉備", "皇室成員", 15.23), ("孫策", "江東集團(tuán)", 56.32), ("曹操", "宦官后裔", 88.43)

縱橫 結(jié)合定位， 增加數(shù)據(jù)。

update
修改的是字段，并不是記錄。
需要添加的where表達(dá)式.

修改那張表

需要改哪幾列的值

分別改為什么值

在那些行生效

where expression , 表達(dá)式。
只要where 表達(dá)式為真，則該條記錄發(fā)揮作用。

update class set commpany = "qd" where sname = "劉備" and salary > 5000;

每條記錄都有 字段 來約束。

刪除，就是刪除每條記錄，不存在刪除一條記錄中的某幾個(gè)字段.

要?jiǎng)h哪張表的數(shù)據(jù)

要?jiǎng)h除那幾條數(shù)據(jù) (while + 表達(dá)式)

delete form class where salary>7000;

#表中的所有數(shù)據(jù)刪除
delete form class;

select基礎(chǔ)查詢

查詢?nèi)?/p>

查詢那張表的數(shù)據(jù)

查詢那些記錄

查詢那些字段

select sname, company, salary from class where id = 2;

# 暴力查找，所有記錄，所有字段 
# * 表示所有記錄，表名后不加where條件，則選所有記錄，認(rèn)為 是true。
# 效率低下
select * from class;

怎么理解where ?
where 后面的是表達(dá)式，表達(dá)式為true，則此行被取出

如何建表
如何修改表(增加或減少列)
多表聯(lián)查
子查詢
解決觸發(fā)器
事務(wù)
存儲(chǔ)過程
備份恢復(fù)

列類型意義
建表就是一個(gè)畫表頭的過程。從術(shù)語上說，建表的過程，是 定義/聲明 字段的屬性/過程。

建表和列類型又什么關(guān)系 ？
分析: 例如一張A4紙，畫一個(gè)學(xué)生報(bào)名表。 A4紙是數(shù)據(jù)的存儲(chǔ)空間，而A4的大小是有限的。
準(zhǔn)備預(yù)留學(xué)號(hào)留多寬，給姓名預(yù)留多寬，給自我介紹預(yù)留多寬。
自然的，姓名如果留的過寬，比如20個(gè)字能夠存下，但是一般人的姓名，就三四個(gè)字 就浪費(fèi)了。如果預(yù)留過窄，導(dǎo)致存不下，更有問題。

建立列的時(shí)候，自然是:能夠容納放置的內(nèi)容，但是又不浪費(fèi)。

建表的意義: 存儲(chǔ)同樣的數(shù)據(jù)，不同的類型，所占用的空間和效率是不一樣的
重點(diǎn) : 列類型的存儲(chǔ)范圍與占據(jù)的字節(jié)關(guān)系。

數(shù)值型

整型

  tinyint
smallint
mediuint
int
bigint 

分析 tinyint

占據(jù)字節(jié)： 1一個(gè)字節(jié)
存儲(chǔ)范圍：-128 -- 127 (無符號(hào))， 0-255     

tinyint 1字節(jié)

1個(gè)字節(jié) 8個(gè)位

# 模擬  -- 每個(gè)位，只能存儲(chǔ)0,1
[][][][][][][][]


0000 0000 ---> 0
1111 1111 ---> 2^8-1=255


計(jì)算機(jī)為了表述一個(gè)數(shù)是負(fù)數(shù)，會(huì)把最高位(左側(cè))的 0/1,當(dāng)成符號(hào)位來看。 如為0， 則是正數(shù)，如為1,則是負(fù)數(shù)

0 0000000
0 1111111 ---> 0-->127(2^7-1)


1 0000000 ---> -128
1 1111111 ---> -127

-127 -- 127


二進(jìn)制補(bǔ)碼的問題

+0，-0 則重復(fù)了，浪費(fèi)了一種存儲(chǔ)的可能性。
因此計(jì)算機(jī)中的負(fù)數(shù)，不是照著 "后面的絕對(duì)值直接乘以-1得到的"，而是補(bǔ)碼規(guī)則換算的。

 
負(fù)數(shù) = 絕對(duì)值位 - 128;

1111 1111 ---> -1
1000 0000 ---> -128


因此最終的結(jié)果： -128 --> 127

1個(gè)字節(jié) 8個(gè)位 0~2^8-1 0~255 / -2^7 --> +2^7-1

分析：
2個(gè)字節(jié)， 16位 0~2^16-1 = 65535

                    -2^15 ~ +2^15-1  -- -32768 ~ 32767 
                    

對(duì)于int型，占字節(jié)越多，存儲(chǔ)的范圍也越大

int系列的聲明時(shí)的參數(shù)

tinyint 的參數(shù)并驗(yàn)證字節(jié)與范圍的關(guān)系
(M) unsigned zerofill

int系列，不需要特殊說明，默認(rèn)是有符號(hào)。
加 unsigned 表示無符號(hào)， 可以影響存儲(chǔ)的范圍

M 參數(shù)

zerofill：zero零， fill是填充。代表：0填充
M必須是和zerofill 配合使用
場景： 學(xué)號(hào)，00001 。 這種是需要補(bǔ)0.

alter table class add snum smallint(5) zerofill not null default 0;
insert into class (sname, snum) values ("呂布", 1);

學(xué)號(hào)會(huì)顯示成： 00001
為什么會(huì)補(bǔ)5位， 因?yàn)镸為 5.
總結(jié)：M表示補(bǔ)0寬度， 和zerofill配合使用才有意義.
如果設(shè)置，zerofill ，同時(shí)必須是 unsigned參數(shù). 0填充，自動(dòng)unsigned

小數(shù)(浮點(diǎn)型/定點(diǎn)型)

float(D, M)
decimal(D, M)

M --> 標(biāo)度。 表示小數(shù)的"總位數(shù)"
D --> 精度。 代表小數(shù)位(小數(shù)點(diǎn)右邊的尾數(shù))

float(6, 2) 表示 -9999.99 ~ 9999.99

也可以添加 unsinged ，zerofill 參數(shù)。

浮點(diǎn)數(shù)占多大的空間呢?
float 能存儲(chǔ) 10^38 。小數(shù)點(diǎn)右邊可以精確到： 10^38分之一
如果：M<=24 占4個(gè)字節(jié)存儲(chǔ)，否則占用8個(gè)字節(jié)存儲(chǔ)。

浮點(diǎn)和定點(diǎn)區(qū)別：
定點(diǎn)(decimal)是把整數(shù)部分和小數(shù)部分，分開存儲(chǔ)的。比 float精確.
float 有時(shí)候會(huì)損失精度

字符串型

char, varchar, text, blod

char(6) 定長字符串。
例如， 姓名 char(6)
查找行記錄時(shí)，如果都是定長，完全可以通過行數(shù)與行的長度計(jì)算出來，文件指針的偏移量.
對(duì)于定長N，不論是否夠不夠指定長度，實(shí)際都占據(jù)N個(gè)長度。如果不夠N個(gè)長度，用空格在末尾補(bǔ)至N個(gè)長度。
利用率中，char(),可能達(dá)到100%;
char型，如果不夠M個(gè)字符，內(nèi)部用空格補(bǔ)齊，取出時(shí)再把右側(cè)空格刪除掉。（注意：如果右側(cè)本身有空格，將會(huì)丟失）

varchar(100) 變長類型。 能夠存儲(chǔ)0-100個(gè)字符。
每個(gè)字符都要讀取前綴，就算是空字符串，都是需要存儲(chǔ)空間。
varchar(N),不用空格補(bǔ)齊，但是列內(nèi)容前，有1-2個(gè)字節(jié)來標(biāo)志該列的內(nèi)容長度。
利用率中，varchar() 不可能達(dá)到100%;

注意：char(M),varchar(M) 限制的是字符，不是字節(jié)。
即：char(2) charset utf-8; 能存儲(chǔ) 2個(gè)utf8 的字符.

錯(cuò)誤認(rèn)識(shí)：既然是字符，6個(gè)utf8字符串，18個(gè)字節(jié)。
并不是18個(gè)字節(jié)。能夠存儲(chǔ)6個(gè)字符，就是在utf8中能夠存儲(chǔ)6個(gè)字節(jié)

text 文本類型，可以存儲(chǔ)比較大的文本段，搜索速度稍慢。
text 不用加默認(rèn)值 (加了也沒有用)

create table test ( article text );

blod , 二進(jìn)制類型，用來存儲(chǔ)圖像，音頻等二進(jìn)制信息。
意義：二進(jìn)制， 0-255 每個(gè)字節(jié)都有可能出現(xiàn)。
blod類型，在于防止因?yàn)樽址膯栴}，導(dǎo)致信息丟失。
比如：一張圖片中有 0xFF 字節(jié)， 這個(gè)在ascii字符集中，在入庫的時(shí)候被過濾了。 因?yàn)楫?dāng)再次取出的時(shí)候，被損壞了。
如果是二進(jìn)制，那么給你什么東西，你就存儲(chǔ)什么東西，并不需要考慮字符集問題。

日期/時(shí)間類型

date, time, datetime, year

date 日期， date 類型 能存儲(chǔ)哪年到哪年 ?
1000-01-01 ~ 9999-12-31

time 時(shí)間，time 時(shí)間類型
專款專用，專列專用

datetime 日期時(shí)間， 占8個(gè)字節(jié)

year 年類型 只占1字節(jié)，最多能夠存儲(chǔ)256中變化。
范圍：1901 - 2155 ，還有一種 是：0000

create table test5 (thing varchar(20) not null default "", ya year  not null default "0000") engine myisam charset utf8;

社交平臺(tái)

主鍵 id, 用戶名, 性別, 體重KG, 生日, 收入, 上次登陸時(shí)間, 個(gè)人簡介

create table user (
    u_id int unsinged PRI,
    user varchar(20) not null default "",
    sex char(1) not null default "",
    wieght tinyint unsinged not null,
    birth date not null default "00",
    salary decimal(8,2) not null default 0.00,
    lastlogin datetime not null default "0000-00-00 00:00:00 ",
    intro varchar(200) not null default ""
)

優(yōu)化

分析：這張表除了username intro 列之外，每一列都是定長的。不妨讓其所有列都定長，可以極大提高查詢速度。

create table user (
    u_id int unsinged PRI,
    username char(20) not null default "",
    sex char(1) not null default "",
    wieght tinyint unsinged not null,
    birth date not null default "00",
    salary decimal(8,2) not null default 0.00,
    lastlogin int unsigned not null default "0000-00-00 00:00:00 "
)

create table inrto (
    i_id int unsinged PRI,
    username char(20) not null default "",
    intro varchar(1500) not null default ""
);

username char(20) 是會(huì)造成存儲(chǔ)空間的浪費(fèi)，但是提高了速度，值得。
intro char(1500) 卻浪費(fèi)的太多了，另一方面，人的簡介，一旦注冊完，修改的頻率也并不高。可以把intro列多帶帶拿出來，另放一張表里。
修改完之后，主表全部都是定長，容易被人查看，修改，速度快。
lastlogin 定義成 datetime計(jì)算的時(shí)候麻煩，一般使用的是，時(shí)間戳。

總結(jié)：
時(shí)間與空間是一對(duì)矛盾體。

優(yōu)化的方法：時(shí)間換空間, 空間換時(shí)間 .

在開發(fā)中，會(huì)員的信息優(yōu)化往往是，把頻繁用到的信息，優(yōu)先考慮效率，存儲(chǔ)到一張表中。不常用的信息和比較占據(jù)空間的信息，優(yōu)先考慮空間占用，存儲(chǔ)到另外一張輔表中。

create table member (
    id int unsigned auto_increment primary key,
    username char(20) not null default "",
    gender char(1) not null default "",
    weight tinyint unsigned not null default 0,
    birth date not null default "0000-00-00",
    salary decimal(8.2) not null default 0.00,
    lastlogin int unsigned not null default 0
) engine myisam charset utf8;

create table inrto (
    i_id int unsinged auto_increment primary key,
    username char(20) not null default "",
    intro varchar(1500) not null default ""
);

一張表，創(chuàng)建完畢，有了N列。
之后還有可能要增加或刪除或修改列

新增列

alter table 表名 add 列名稱 列類型 列參數(shù); [增加在表的最后]
alter table 表名 add 列名稱 列類型 列參數(shù) after; 某列[把新列指定增加到某列后面]

新建一個(gè)列在表的最前面，使用first

alter table 表名 add 列名稱 列類型 列參數(shù) first;
alter table m1 add pid int not null defalut "" first;

alter table m1 add username char(20) not null default "";
alter table m1 add birth date not null default "0000-00-00";

刪除列

alter table 表名 drop 列名;

alter table m1 drop pid;

修改列

修改列類型

alter table 表名 modify 列名 新類型 新參數(shù);

alter table m1 modify gener char(4) not null default ""; 

修改列類型和列名
alter table 表名 change 舊列名 新列名 新類型 新參數(shù)

alter table m1 change id uid not null auto_increment primary key; 

如果列類型改變了，導(dǎo)致數(shù)據(jù)存儲(chǔ)不下了怎么處理?
比如：int 修改成 smallint 列. 如果不匹配，數(shù)據(jù)將會(huì)丟失，或者在mysql嚴(yán)格模式下 (strict mode) 下修改不成功.

# shop
# 分析商品表
# 建立類似的
# 小型表
# 
# 商品表
# 
# goods_id 商品主鍵
# cat_id 欄目id
# goods_sn 貨號(hào)
# goods_name 商品名稱
# click_count 點(diǎn)擊量
# brand_id 品牌
# goods_number 庫存量
# goods_weight 重量
# market_price 市場價(jià)格
# shop_price 本店價(jià)格
# promote_price 優(yōu)惠價(jià)格
# warn_number 報(bào)警數(shù)量
# keywords 關(guān)鍵字
# goods_thumb 小圖
# goods_img 中等圖
# original_img 原始圖
# is_real 真實(shí)商品，與：extension_code 有關(guān)，還有虛擬商品
# extension_code  
# is_on_sale 是否上架狀態(tài)
# is_alone_sale  是否多帶帶銷售，贈(zèng)品
# is_shipping 是否包郵
# integral 送的點(diǎn)數(shù) 
# add_time 添加的時(shí)間戳
# sort_order 排序的權(quán)重
# is_delete 是否刪除
# is_best 是否精品
# is_new 是否新品
# is_hot 是否熱賣
# is_promote 
# bounts_type_id 優(yōu)惠券
# last_update 上次修改時(shí)間
# goods_type 商品類型
# seller_note 買家備注

# 商品表
# goods_id, cat_id, goods_sn, goods_name, click_count, 
# goods_number, market_price, shop_price, add_time, is_best, is_new, is_hot

# 查看其它 表創(chuàng)建
# show create table 表名 
# show create table ecs_goods; 

CREATE TABLE `goods` (
  `goods_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `cat_id` smallint(5) unsigned NOT NULL DEFAULT "0",
  `goods_sn` varchar(60) NOT NULL DEFAULT "",
  `goods_name` varchar(120) NOT NULL DEFAULT "",
  `click_count` int(10) unsigned NOT NULL DEFAULT "0",
  `goods_number` smallint(5) unsigned NOT NULL DEFAULT "0",
  `market_price` decimal(10,2) unsigned NOT NULL DEFAULT "0.00",
  `shop_price` decimal(10,2) unsigned NOT NULL DEFAULT "0.00",
  `add_time` int(10) unsigned NOT NULL DEFAULT "0",
  `is_best` tinyint(1) unsigned NOT NULL DEFAULT "0",
  `is_new` tinyint(1) unsigned NOT NULL DEFAULT "0",
  `is_hot` tinyint(1) unsigned NOT NULL DEFAULT "0",
  PRIMARY KEY (`goods_id`)
) ENGINE=MyISAM AUTO_INCREMENT=48 DEFAULT CHARSET=utf8


# 導(dǎo)入其它表中的相同字段的數(shù)據(jù)
# insert into 導(dǎo)入表 select 導(dǎo)入字段 被導(dǎo)入表

insert into zf.goods select goods_id, cat_id, goods_sn, goods_name, click_count, goods_number, market_price, shop_price, add_time, is_best, is_new, is_hot from ec_goods;

查詢練習(xí)

查詢商品主鍵是32的商品

    select goods_id, goods_name, shop_price from goods where goods_id = 32;

不屬于第三個(gè)欄目的所有商品 即cat_id 不等于 3
!= 等價(jià)于 <>

    select goods_id, goods_name, shop_price, cat_id from goods where cat_id != 3;
    select goods_id, goods_name, shop_price, cat_id from goods where cat_id <> 3;

本店商品價(jià)格高于三千的商品

    select goods_id, goods_name, goods_price from goods where goods_price > 3000;

本店商品價(jià)格小于或等于100的商品

    select goods_id, goods_name, shop_price from goods where shop_price<=100;

取出第4欄目和第11欄目的商品(不許用or)

    select goods_id, cat_id, goods_name from goods where cat_id in(4, 11);

取出100<=價(jià)格<=500 的商品(不許用and)

    select goods_id, goods_name, shop_price from goods where shop_price between 100 and 500;

注意：in是 是散的滿足。 between 范圍的滿足.

取出不在第3個(gè)欄目和不在第11個(gè)欄目的商品(and或not in 分別實(shí)現(xiàn)) 。 即欄目的id，是3的不要，是11的也不要。

// $cat_id !=3 && $cat_id != 11;

    select goods_id, goods_name, shop_price, cat_id from goods where cat_id not in(3, 11);
    select goods_id, goods_name, shop_price, cat_id from goods where cat_id != 3 and cat_id != 11;

取出價(jià)格大于100且小于300，或者大于4000且小于5000的商品

在[100, 300] 之間，或者 是 [4000,5000]

    select goods_id, goods_name, shop_price, cat_id from goods where (shop_price>100 and shop_price<300) or (shop_price>4000 and shop_price<5000);

and 的優(yōu)先級(jí)比 or 高

取出第3個(gè)欄目下面價(jià)格 < 1000 或 > 3000, 并且點(diǎn)擊量 >5 的系列商品

    select goods_id, goods_name, shop_price, cat_id, click_count from goods where cat_id = 3 and ( shop_price < 1000 or shop_price>3000 ) and click_count >= 5;    

怎么要求就怎么寫

取出1號(hào)欄目下的商品 (無限級(jí)分類) 大分類 --> 小分類 --> 小分類 。。。

    select goods_id, goods_name, cat_id from goods where cat_id = 1;

取出名字以"諾基亞"開頭的商品

    select goods_name, shop_price from goods where goos_name like "諾基亞%";

% 匹配任意字符.

取出已名字"諾基亞N"后面固定的二位字符.

    select goods_name, goods_id from goods where goods_name link "諾基亞N__";

_ 匹配任意單一字符

把列看成變量，把where看成PHP中 if ( exp ) 里的 exp 表達(dá)式
哪些行被取出，哪一行能夠讓表達(dá)式為true，那一行就能夠取出來。

判斷這一行取出什么?

select goods_id, goods_name from goods where 1>2;

取出為空

把列看成變量。
既然變量，變量之間也是可以運(yùn)算，可以作為表達(dá)式。可以當(dāng)作參數(shù)，作為函數(shù)的參數(shù)。

取出商品id，商品名，本店價(jià)格比市價(jià)價(jià)格省的錢

select goods_id, goods_name, (market_price - shop_price) from goods;
select goods_id, goods_name, (market_price - shop_price) from goods where 1;

表中原本就沒有(market_price - shop_price) 的列
這一列其實(shí)是一個(gè)運(yùn)算結(jié)果，術(shù)語叫"廣義投影". 理解成字段運(yùn)算.

列的運(yùn)算結(jié)果，可以當(dāng)成列看，還可以起個(gè)列別名。 (偽列)

select goods_id, goods_name, (market_price - shop_price) as discount from goods where cat_id!=3;

查出本店價(jià)比市場省的錢，而且省200以上的商品.

select goods_id, goods_name, (market_price - shop_price) as discount from goods where (market_price - shop_price) > 200;



select goods_id, goods_name, (market_price - shop_price) as discount from goods where discount > 200; # 報(bào)錯(cuò)
# where 后面不能使用別名。

# where 查詢 --> 結(jié)果 (多行多列的二維結(jié)構(gòu))

# where 查詢的結(jié)果是對(duì) 表 中的數(shù)據(jù)發(fā)揮作用， 查詢出數(shù)據(jù)。
# discount 是在結(jié)果中數(shù)據(jù)算出. 
# where發(fā)揮作用時(shí)，表上并沒有discount列。發(fā)揮完作用，形成的結(jié)果里才能discount。 
# 對(duì)于結(jié)果中的列，如果再想篩選需用使用having

# where 字句就沒有discount列，所以報(bào)錯(cuò).

where 條件是表達(dá)式，在哪一行表達(dá)式為真，哪一行就取出來。

題目：
把num的值處于[20, 29]之間的，改為20
num值處于[30, 39] 之間的，改為30

create table number ( num smallint not null default 0 ) engine myisam charset utf8;

inset into number values (3), (12), (23), (25), (29), (32), (34), (38);

update number set num = floor(num / 10) * 10 where num>=20 and num<=30;
# 字段看成變量，可以運(yùn)算，可以使用函數(shù)，可以做成表達(dá)式.
   

統(tǒng)計(jì)函數(shù)

max: 求最大。min: 求最小。sum: 求總和。avg: 求平均。count: 求總行數(shù)。

查出最貴的商品的價(jià)格

select max(shop_price) from goods;  

查處最便宜的商品的價(jià)格

select min(shop_price) from goods;

查詢出發(fā)布最早的商品。即goods_id 最小的值.

select min(goods_id) from goods;

goods_number 是指庫存量，統(tǒng)計(jì)一下，本店一共庫存多少件商品

select sum(goods_number) from goods;

查看一個(gè)店中的所有商品的平均價(jià)格.

select avg(shop_price) from goods;

統(tǒng)計(jì)一下本店中有多少的商品。(不是多少個(gè))

count 計(jì)算 多少條記錄.
如果使用字段，來count()計(jì)算總共，null并不會(huì)計(jì)算在其中.

select count(*) from goods; # 查詢的就是絕對(duì)的行數(shù)，哪怕某一行所有字段全為null，也計(jì)算在內(nèi)。
select count(goods_id) from goods; # 字段如果是 null，并不會(huì)計(jì)算在其中。

用 count(*)，count(1)，誰更好呢?
對(duì)于myisam引擎的表，沒有區(qū)別的。因?yàn)椋@中引擎內(nèi)部，有一個(gè)計(jì)數(shù)器在維護(hù)著行數(shù)。
innodb的表，用count(*)直接讀取行數(shù)，效率低下，因?yàn)閕nnodb真的去數(shù)一邊。

5個(gè)統(tǒng)計(jì)函數(shù)，多帶帶使用，意義不大，要和分組配合起來使用，威力更大。

計(jì)算，第3個(gè)欄目下所有商品的庫存量之和 sum(goods_num);

select sum(goods_number)  from goods where cat_id = 3;  

計(jì)算，第4個(gè)欄目下所有商品的庫存量之和

select sum(goods_number) from goods where cat_id = 4;

group 子句

計(jì)算，一次計(jì)算完，每個(gè)欄目下的庫存量之和
按照規(guī)則，分組，然后 組內(nèi)解決總和。

分組之后再統(tǒng)計(jì)，結(jié)合起來使用。

select sum(goods_number) from goods group by cat_id;

是否正確：

select goods_id, sum(goods_number) from goods;

這條語句執(zhí)行了，把 goods_id，第一次出現(xiàn)的值 取出來。
對(duì)于SQL標(biāo)準(zhǔn)來說，這個(gè)語句是錯(cuò)誤的，不能執(zhí)行的。但是在MySQL中可以這么做。(MySQL特征)
這是MySQL的一個(gè)特點(diǎn)，出于可移植性和規(guī)范性，不推薦這樣寫。
嚴(yán)格的講，select 的a, b 列必須在 group by a, b, c的列出現(xiàn)。

按cat_id分組，計(jì)算每個(gè)欄目下的商品的平均價(jià)格

select goods_id, cat_id, avg(shop_price) from goods group by cat_id;

having篩選

having 發(fā)揮的時(shí)間，是where對(duì)硬盤上的表文件進(jìn)行查詢之后，內(nèi)存形成一張結(jié)果的虛擬表(偽列存在于此表中)， having在此時(shí)生效。

查詢本店價(jià)比市場價(jià)省的錢，并且要求省錢200元以上的取出來。

select goods_id, market_price, shop_price, (market_price - shop_price) as discount from goods having discount > 200;
# where 沒有寫出，表示where 1;

查詢每種商品所積壓的貸款(提示: 庫存 +單價(jià))
goods_number + shop_price

select goods_id, goods_number, shop_price , (goods_number * shop_price) as hk from goods ;

查詢該店積壓的總貨款。

即，每個(gè)商品積壓的貸款之和。考慮：sum那個(gè)列?

select sum(shop_price * goods_number) as zhk from goods;

查詢每個(gè)欄目下，積壓的貨款。

select cat_id, shop_price, goods_number, sum(shop_price * goods_number) as hk from goods group by cat_id;

查詢積壓貨款超過2w元的欄目，以及該欄目積壓的貸款。

select cat_id, shop_price, goods_number, sum(shop_price * goods_number) as hk from goods group by cat_id having hk > 20000;

查詢本店價(jià)比市場價(jià)省的錢，且篩選出省錢200以上的商品. (用where 和 having 分別來實(shí)現(xiàn))

#where
select goods_id, market_price, shop_price from goods where (market_price - shop_price) > 200;

#having
select goods_id, market_price, shop_price from goods having (market_price - shop_price) > 200;
select goods_id, market_price, shop_price, (market_price - shop_price) as discount from goods having discount > 200;

查詢出2門及2門以上的不及格者的平均成績

CREATE TABLE `reslute` (
  `name` varchar(20) DEFAULT NULL,
  `subject` varchar(20) DEFAULT NULL,
  `score` tinyint(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8

insert into reslute values ("zhangsan", "math", 90), ("zhangsan", "ch", 50), ("zhangsan", "dili", 40), ("lisi", "ch", 55), ("lisi", "zhengzhi", 45), ("wangwu", "zhengzhi", 30);

錯(cuò)誤

select name, avg(score), count(score < 60) as sc from reslute group by name having sc >= 2;

正確查詢

# 1: 查詢所有的平均分
select name, avg(score) from reslute group by name;

# 2: 想辦法計(jì)算出每個(gè)人掛科的情況
select name, subject, score, score<60 as g from reslute;

# 3: 掛科數(shù)目，就是g 列的 sum() 結(jié)果.
# 總結(jié)下
select name, avg(score), sum(score < 60) as sc from reslute group by name having sc >= 2;

order by
語法: order by 列名 desc/asc. (列名，結(jié)果集中的列名)
例如: order by add_time asc . 按發(fā)布時(shí)間升序排列.

取出第4個(gè)欄目下的商品，并按價(jià)格由高到低排序。

select goods_id, goods_name, shop_price from goods where cat_id = 4 order by shop_price desc;

按欄目升序排列，同一個(gè)欄目下的商品，再按商品的價(jià)格降序排列

select goods_id,cat_id, goods_name, shop_price from goods order by cat_id asc, shop_price desc;
# 多個(gè)字段排序，通過`,` 逗號(hào)隔開。

limit
限制條目
limit 數(shù)量 限制條數(shù)
limit [offset] N
offset 偏移量 (可選參數(shù)),跳過幾行。 N 取出條數(shù)。
offset 如果不寫則相當(dāng)于 limit 0 N； offset 是跳過的個(gè)數(shù)，N是實(shí)際取的個(gè)數(shù)。

本店商品價(jià)格最高的商品。

select goods_id, goods_name, shop_price from goods order by shop_price limit 3;

本店最高的第三名到第五名 商品。

select goods_id, goods_name, shop_price from goods order by shop_price desc limit 2,3;
# 取第3到第5，即意味著跳過， 第1，第2, 因此偏移量offset是2.
# 取第3,4,5條。即取3條，因此N=3；

取出價(jià)格最高的那條商品

select goods_id, goods_name, shop_price from goods order by shop_price desc limit 1;

查詢出每個(gè)欄目下id號(hào)最大(最新)的一條商品.

# 錯(cuò)誤語句分析
第一種錯(cuò)誤：group by cat_id;
group by cat_id 從語義上來分析，select 的列，只能是cat_id, max/min/avg/sum/count .
這個(gè) goods_id, goods_name 從語義上分析就不對(duì)，也不符合sql標(biāo)準(zhǔn)。
只是MySQL允許這種語法。
取出 欄目中 第一次 出現(xiàn)的記錄。

第二種錯(cuò)誤：name 匹配第一次出現(xiàn)的
select max(goods_id) as max_cat_id, goods_id, goods_name from goods group by cat_id;

第三種錯(cuò)誤：先 order by 再 group by. 語法錯(cuò)誤。
select goods_id, cat_id, goods_name from goods order by cat_id asc, goods_id desc;
在此基礎(chǔ)上，再分組。
select goods_id, cat_id, goods_name from goods order by cat_id asc, goods_id desc group by cat_id;

需要使用到 子查詢.

把內(nèi)層查詢的結(jié)果，作為外層查詢的比較條件
典型的：查詢最大商品，最貴商品。

如果 where 列 = (內(nèi)層 sql)。 則內(nèi)層sql返回的必然是單行單列.
如果 where 列 in (內(nèi)層 sql)。 則內(nèi)層sql返回單列， 可以多行。

查詢出本店最新的(goods_id最大) 的一條商品。

# 思路，按照goods_id desc 排序，再去 第一行.
select goods_id, goods_name from goods order by goods_id desc limit 1;

查詢出本店最新的(goods_id最大) 的一條商品。要求不要使用排序.

# 思路，求出最大的id， 然后在where判斷
select goods_id, goods_name from goods where goods_id = (select max(goods_id) from goods);

查詢出每個(gè)欄目下id號(hào)最大(最新)的一條商品.

# 第一步：查詢出每個(gè)欄目下goods_id 最大的。
select max(goods_id) from goods group by cat_id;
# 只需要再把goods_id = 第一步值.
select goods_name, goods_id from goods where goods_id in (select max(goods_id) from goods group by cat_id);

內(nèi)層sql的查詢結(jié)果，當(dāng)成一張臨時(shí)表，供外層sql再次查詢.

查詢模型：查詢結(jié)果集 ---> 在結(jié)構(gòu)上可以當(dāng)成表看.

查詢出每個(gè)欄目下id號(hào)最大(最新)的一條商品.

select * from (select goods_id, cat_id, goods_name from goods
order by cat_id asc, goods_id desc) as tmp group by cat_id;

把外層sql的結(jié)果，拿到內(nèi)層sql去測試。
如果內(nèi)層的sql成立，則該行取出。

查詢有商品的欄目.取 欄目標(biāo)，且只取下面有商品的欄目
思考：什么樣的表，叫做 下面欄目有商品?
設(shè)某欄目cat_id 為N,則 select * from goods where cat_id = N;
能取出數(shù)據(jù)，則說明該欄目有商品

select cat_id, cat_name from category where exists (select * from goods where goods.cat_id = category.cat_id);
# 從外層SQL取出內(nèi)層SQL需要的字段值，然后再通過內(nèi)層SQL來判斷.

建表時(shí)，列后面 not null defalut ""/default 0; 這是什么意思
就是讓這個(gè)列值不為NULL， 如果某個(gè)列確實(shí)沒填，也有默認(rèn)值，值不為NULL

為什么列的值不為NULL。

create table test5 ( sname varchar(20) ) engine myisam charset utf8;

insert into test5 values ("tianjilaoren"), ("lixunhuan"), ("afei"), NULL;

# 查詢出用戶名不為null的行

select * from test5 where sname != null;

# tianjilaoren lixunhuan afei 沒有查詢出來?

#查詢 sname 為null 的行

select * from test5 whre sname = null;

#也是空

select "afei" = null; # 查詢結(jié)果： NULL

# NULL為家， list=null 是假

select null = null; #查詢結(jié)果： NULL

# null = null , 還是 null， 還是假

select null != null; #查詢結(jié)果：NULL

# null是空

# null 的比較需要用特殊的運(yùn)算符 is null 和 is not null

select * from test5 where sname is not null;

# 取出值為null

select * from test5 where sname is null;

null 是一種類型，比較時(shí)，只能用專門的is null 和 is not null 來比較 碰到運(yùn)算符， 一律返回 null.
效率不高，不利于提高索引效率。

因此，往往在建表的時(shí)候聲明 not null default ""/defalut 0

新手1+N模式查詢

$conn = mysql_connect("localhost", "root", "");

$sql = "use zf";

mysql_query($sql, $conn);

$sql = "set names utf8";

mysql_query($sql, $conn);

$sql = "select goods_name, cat_id, goods_number, shop_price from goods";

$rs = mysql_query($sql, $conn);

$list = array();
while ( $row = mysql_fetch_assoc($rs) ) {

    /**
     * 根據(jù)$row 中的cat_id 在此查詢category表
     * 每循環(huán)一次，又要查詢另一張表
     * 因此，查詢 1+N 次.
     */
    $sql = "select cat_name from category where cat_id = " . $row["cat_id"];
    
    $rs2 = mysql_query($sql, $conn);
    
    $cat = mysql_fetch_assoc($rs2);
    
    $row["cat_name"] = $cat["cat_name"];
    
    $list[] = $row;
    
}

集合知識(shí)

集合的特點(diǎn)： 無序性 ， 唯一性
集合的運(yùn)算：求交集，求并集，笛卡爾積(相乘)

笛卡爾積，即集合的元素，做兩兩的組合。

表與集合的關(guān)系

一張表就是一個(gè)集合
每一行就是一個(gè)元素.

集合不能重復(fù)，但可能有兩行數(shù)據(jù)完全一樣。
MySQL內(nèi)部每一行，還有一個(gè)rowid.

場景：

create table test7 ( id int, name varchar(20) ) engine myisam charset utf8;
create table test8 ( cat_id int, cat_name varchar(20) ) engine myisam charset utf8;

insert into test7 values (1, "桃子"), (2, "蘋果"), (3, "梨");
insert into test8 values (95, "月亮"), (96, "星星");

在數(shù)據(jù)庫的操作上，如何操作表，完成集合的笛卡爾積的效果。
直接使用","隔開表名，查詢即可。

select * from test7,test8;

兩表做全相乘
從行的角度來看：就是2表每一行，兩兩組合
從列的角度來看：結(jié)果集中的列，是兩表的列名的相加

做全相乘

create table minigoods like select * from goods;

select goods_id, minigoods minigoods.cat_id, goods_name, category.cat_id, cat_name from minigoods,category;

# 全相乘
# 加了條件之后
select goods_id, minigoods minigoods.cat_id, goods_name, category.cat_id, cat_name from minigoods,category where minigoods.cat_id = category.cat_id;

Mysql 優(yōu)化：索引優(yōu)化, 字段優(yōu)化, 存儲(chǔ)引擎優(yōu)化 (索引是發(fā)生在查詢過程中)

假設(shè)A表在左，B表在A表的右邊滑動(dòng)。
A表與B表通過一個(gè)關(guān)系來篩選B表的行

語法： A left join B on 條件
條件為true，則B表對(duì)應(yīng)的行，取出.

A left join B on 條件 這一塊形成的也是一個(gè)結(jié)果集,也可以看成一張表。 可以對(duì) A left join B on 條件 as C 。可以對(duì)C表做查詢， 自然where, group, having, order by, limit 照常使用。 還可以繼續(xù) left join. A left join B on 條件 left join D on 條件

C 表的可以查詢的列有哪些列 ?
A,B 的列都可以查詢。

select goods_name, goods_number, shop_price, cat_name from goods left join category on goods.cat_id = category.cat_id;

沒有另一張表對(duì)應(yīng)的行，使用NULL補(bǔ)齊。
多行對(duì)應(yīng)，該如何解決。

create table boy (
bname varchar(20),
other char(1) 
) engine myisam charset utf8;

insert into boy 
values 
("lin","A"),
("李尋歡","B"),
("阿飛","C"),
("呆鵝","D"),
("Weibo","E"),
("賈寶玉","F");

create table girl (
gname varchar(20),
other char(1)
) engine myisam charset utf8;

insert into girl
values 
("陳志芳","A"),
("飛兒","C"),
("飛飛","C"),
("石頭","D");

select boy.*, girl.* from boy left join girl on boy.other = girl.other;

結(jié)果：

是以boy表, 為基準(zhǔn)。

select boy.*, girl.* from girl left join on boy.other = girl.other;

結(jié)果:

是以 girl表 為基準(zhǔn)

注意：a left join b, 并不是說a表的就一定是在左邊，只是說在查詢數(shù)據(jù)時(shí)，以a表為準(zhǔn)。
NULL,是補(bǔ)齊，而不是查詢出來的。

左右連接是可以互換的.
A left join B, 就等價(jià)于 B right join A;

select boy.*, girl.* from boy right join girl on boy.other = girl.other;

注意：左右連接 可以互換， 盡量使用 左鏈接， 出于移植時(shí)兼容性方面考慮。

內(nèi)連接的特點(diǎn)

select boy.*, girl.* from boy inner join girl on boy.other = girl.other;

兩個(gè)表中符合條件且都有值，拿出來。

結(jié)果：

從集合的角度看：
A inner join B 和 left join/right join 的關(guān)系：
內(nèi)連接是左右連接的交集

外連接

左右連接的并集, 外連接。
但是，在mysql中不支持外連接。

題目：

create table m (
mid int,
hid int,
gid int,
mres varchar(10),
matime date
) engine myisam charset utf8;


create table t (
tid int,
tname varchar(20)    
) engine myisam charset utf8;

insert into m 
values 
(1,1,2,"2:0","2006-05-21"),
(2,2,3,"1:2","2006-06-21"),
(3,3,1,"2:5","2006-06-25"),
(4,2,3,"3:2","2006-07-21");


insert into t 
values 
(1,"guoan"),
(2,"shenhua"),
(3,"gongyiliandong");

查詢 :

# 取出出主客隊(duì)的ID， 并不做特殊處理.
select hid, mres, gid, matime from m;

# 根據(jù)hid, 左聯(lián)t表 ，查出主隊(duì)的隊(duì)伍名稱
select hid,tname, mres, gid, matime from m left join t on m.hid = t.tid;

# 根據(jù)gid， 查出客隊(duì)的隊(duì)伍名稱.

select hid, tname, mres, tname, gid, tname, matime from 
(m left join t on m.hid = t.tid) left join t on m.gid = t.tid;
# 錯(cuò)誤的原因 是 m t t 相連， 名字沖突，起個(gè)別名，就可以解決.
# select 列名 as 別名。也可以 .


select hid, t1.tname, mres, gid, t2.tname, matime from 
(m left join t as t1 on m.hid = t1.tid) 
left join t as t2 on m.gid = t2.tid;

# 最后拼接結(jié)果：
select hid, t1.tname, mres, gid, t2.tname, matime from
(m left join t as t1 on m.hid = t1.tid)
left join t as t2 on m.hid = t2.tid
where matime > "2006-06-01" and matime < "2016-07-01";

#三表聯(lián)查 + where.

# 第二種方法：使用 inner join 
select hid, t1.tname, mres, gid, t2.tname, matime from m inner join t as
t1 on m.hid = t1.tid inner join t as t2 on m.gid = t2.tid where matime > "2006-0
6-01" and matime < "2016-07-01";

PHP 邏輯來完成留言板查詢

header("Content-type: text/html; charset=utf-8");

/**
 * 商城留言板
 * 
 * 一般情況下，做留言板的顯示很容易。
 * 直接select 查詢，再顯示出來。
 * 
 * 但是ecshop中的留言板難點(diǎn)在于
 * 留言條數(shù)來自于2張表。
 * feedback 留言表
 * comment 評(píng)論表
 * 
 * 需要把兩張表中的數(shù)據(jù)都取出來，顯示結(jié)果。
 */
 
/**
 * 思路：從業(yè)務(wù)邏輯層，用PHP來解決問題
 * 1. 先取出feedback 表，循環(huán)取出數(shù)據(jù)，放入一個(gè)數(shù)組
 * 2. 再取出comment 表，循環(huán)取出數(shù)據(jù)，放入一個(gè)數(shù)組
 * 3. 取出的兩個(gè)數(shù)組合并
 * 4. 循環(huán)合并后的數(shù)組
 */ 

$counn = mysql_connect("localhost", "root", "");

$sql = "use ecshop";
mysql_query($sql, $counn);    
 
$sql = "set names utf8";
mysql_query($sql, $counn);

// 取出feedback表中數(shù)據(jù)
$sql = "select user_name, msg_content, msg_time from ecs_feedback where msg_status = 1";

$feeds = array();
$rs =    mysql_query($sql, $counn);

while ( $row = mysql_fetch_assoc($rs) ) {
    
    $feeds[] = $row; 
    
}

// 取出comment表中數(shù)據(jù)
$sql = "select user_name, content as msg_content, add_time as msg_time from ecs_comment where status = 1";
$rs = mysql_query($sql, $counn);

$comm = array();
while ( $row = mysql_fetch_assoc($rs) ) {
    
    $comm[] = $row;
            
}

$all = array_merge($feeds, $comm);

union : 合并 2 條 或多條語句的結(jié)果.

語法：sql1 union sql2;

查詢出價(jià)格低于100元 和 價(jià)格高于4000元的商品.(不能使用 or)

# 先查詢<100的商品
select goods_id, goods_name, shop_price from goods where shop_price < 100;

# 查詢 > 4000 的商品
select goods_id, goods_name, shop_price from goods where shop_price > 4000;

# 使用，union  聯(lián)合查詢 、

select goods_id, goods_name, shop_price from goods where shop_price < 100 union select goods_id, goods_name, shop_price from goods where shop_price > 4000;

能否從2張表查詢在union呢?

合并的是 "結(jié)果集"，不區(qū)分來自于那一張表。

取自于2張表，通過別名讓2個(gè)結(jié)果集的列一致。
那么，如果取出的結(jié)果集，列名字不一樣，還能否union。
可以使用 union，而且取出的最終列名，以第一條SQL為準(zhǔn).

union 滿足什么條件就可以使用 ?
結(jié)果集中的字段數(shù)量一致

列的類型不一致，不一樣，沒關(guān)系，也是可以合并的。合并的意義不大。

union 后的結(jié)果集，可否再排序呢?
可以再排序。 sql1 union sql2 order by 字段;

select goods_id, goods_name, shop_price from goods where shop_price < 100 union select goods_id, goods_name, shop_price from goods where shop_price > 4000 order by shop_price asc;

用union,取出第4個(gè)欄目的商品和第5個(gè)欄目的商品，并按價(jià)格升序排列.

select goods_id, goods_name, shop_price from goods where where cat_id = 4 union select goods_id, goods_name, shop_price from goods where cat_id = 5 order by shop_price asc;    

使用 order by 的注意事項(xiàng)

sql 語句中的 desc 沒有發(fā)揮作用.

外層的語句還要對(duì)最終結(jié)果，再次排序。因此，內(nèi)層的語句的排序，就沒有意義。
因此: 內(nèi)層的order by 語句多帶帶使用， 不會(huì)影響結(jié)果集，僅排序。在執(zhí)行期間，就被MySQL的代碼分析器優(yōu)化。
內(nèi)層的order by 必須能夠影響結(jié)果集時(shí)，才有意義。 比如 配合 limit 使用。
根據(jù)是否影響結(jié)果集，選擇性優(yōu)化。

第3個(gè)欄目下，價(jià)格前3高的商品 和 第4個(gè)欄目下， 價(jià)格前2 高的商品。(使用union完成)

(select goods_id, cat_id, goods_name, shop_price from goods where cat_id = 3 order by shop_price asc limit 3)
union
(select goods_id, cat_id, goods_name, shop_price from goods where cat_id = 4 order by shop_price asc limit 2);

內(nèi)層的order by 發(fā)揮了作用， 因?yàn)橛?limit。order by 會(huì)實(shí)際影響結(jié)果集，有意義。

如果 union后的結(jié)果有重復(fù)(即某2行，或N行， 所有的列，值都一樣),如何變化。

這種情況下，是比較常見的，默認(rèn)會(huì)去重復(fù)。

如果不想去重復(fù)。
可以使用 union all;

union 面試題
懶，能寫一行絕不寫二行。

CREATE TABLE `num1` (
  `id` varchar(10) DEFAULT NULL,
  `num` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;



CREATE TABLE `num2` (
  `id` varchar(10) DEFAULT NULL,
  `num` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


insert into num1 values ("a", 5), ("b", 10), ("c",15), ("d", 10);

insert into num2 values ("b", 5), ("c", 15), ("d", 20), ("e", 99);

# 查詢
select id, sum(num) from (select * from num1 union all select * from num2) as tmp order by id;

MySQL函數(shù)

數(shù)學(xué)函數(shù)
sqrt(); 計(jì)算指定數(shù)字的平方根
rand(); 生成隨機(jī)數(shù)

聚合函數(shù)

avg(col); 返回指定列的平均值
count(col); 返回指定列中非null值的個(gè)數(shù)
min(col); 返回指定列的最小值
max(col); 返回指定列的最大值
sum(col); 返回指定列的所有值之和
group_concat(col); 返回由屬于一組的列值連接組合而成的結(jié)果

字符串函數(shù)

ascii(); 計(jì)算字符的ascii碼
length(col); 計(jì)算字符串的字節(jié)長度
char_length(col); 計(jì)算字符數(shù)
reverse(col); 字符串反轉(zhuǎn)
position("@" in "abc@sina.com") 返回字符串位置
right("abc@sina.com", 8); 返回 后邊的 字符串

create table test14 (uname varchar(20), email varchar(30))engine myisam charset utf8;
inesrt into test14 values ("lily", "lily@163.com"), ("zhifang", "zhifang@qq.com");
select email, right( email, length(email) - position("@" in email) ) from test14;

# 對(duì)于email，并非全部的存儲(chǔ)一個(gè)字段，而是分開存儲(chǔ)了，變得更加高效。
# 把 email拆成 @ 前后2部分，放在2個(gè)列。

時(shí)間函數(shù)

now(); 返回 年月日時(shí)分秒
curdate(); 返回 年月日
curtime(); 返回 時(shí)分秒
dayofweek("2016-09-18"); 查看某一天，是那一周的第幾天 . (西方的周日第一天).
week( curdate() ); 查看某一年的第幾周. 38

create table jiaban (
num int,
dt date    
)engine myisam charset utf8;

insert into jiaban 
values
(5, "2012-09-01"),
(6, "2012-09-02"),
(7, "2012-09-03"),
(8, "2012-09-04"),
(9, "2012-09-05"),
(10, "2012-09-06"),
(11, "2012-09-07"),
(12, "2012-09-08"),
(13, "2012-09-09"),
(14, "2012-09-10"),
(15, "2012-09-11"),
(16, "2012-09-12");

# 按周統(tǒng)計(jì)加班時(shí)間

select sum(num), week(dt) as wk from jiaban group by wk;

加密函數(shù)

md5(); md5加密，不可逆，單向加密，不可逆。

良好的加密：
1， 不可逆性

碰撞性低 （重復(fù)的概率低）

控制流程函數(shù)

case 值 when 某種可能 then 返回值 when 另一種可能值 then 返回值 else 默認(rèn)值 end

select sname, case gender when 1 then "男" when 0 then "女" end as xingbie from test15;

if ( 條件, "", "" ); 三元運(yùn)算符

select sanme, if (gender=0, "優(yōu)先", "等待") as vip from test15;

ifnull(expr1, epxr2); 判斷第一個(gè)表達(dá)是否為null，如為null， 返回第2個(gè)表達(dá)式的值; 如不為null，返回自身，即表達(dá)式1.

ifnull(null, 0);
ifnull("",0);

user(); 返回用戶即所在主機(jī) , 判斷自己的身份
database(); 當(dāng)前所在的庫
version(); 服務(wù)器版本 , 服務(wù)器比較古老，某些兼容性問題，需要判斷。

PHP中和MySQL中都有相同的函數(shù)，那么優(yōu)先使用哪種?

MySQL的函數(shù)肯定是要影響查詢速度.
應(yīng)該在建表的時(shí)候，通過合理的表結(jié)構(gòu)減少函數(shù)的使用。 比如：emial, 按 @ 前后拆分。

如果確實(shí)需要使用函數(shù)。
比如時(shí)間的格式化， 在 mysql里用date_format, 在php里用date可以實(shí)現(xiàn)。

優(yōu)先放在業(yè)務(wù)邏輯層，即PHP層處理。

在查詢時(shí)使用了函數(shù)，最大的一個(gè)壞處。
以 date_format(A); 則A列的索引將無法使用。

如果針對(duì)某些查詢，而此列，用上了函數(shù)來判斷。
此列將不再使用索引。

例如：select name, email from table where right("@", length(email) - position("@" in email) );
email 列使用索引，可以加快查詢速度。
但因?yàn)槭褂玫牟⒉皇莈mail列，而是函數(shù)處理后的email的返回值。
因此，email列的查詢就 非常緩慢。

總結(jié)：在where條件中，對(duì)某列的使用了函數(shù)，由此列的索引不發(fā)揮作用。

在查詢中，經(jīng)常把查詢結(jié)果 當(dāng)成臨時(shí)表來看。
View是什么? View 可以看一張?zhí)摂M表。是表通過某種運(yùn)算得到的一個(gè)投影(映射)。
View 沒有實(shí)實(shí)在在的數(shù)據(jù)，只是通過表，經(jīng)過一系列的運(yùn)算，運(yùn)算出來的一個(gè)結(jié)果。

表的變化，會(huì)影響到視圖。

查詢每個(gè)欄目下，商品的平均價(jià)格,并取平均價(jià)前3高的欄目

select avg(shop_price) as pj, cat_id from goods group by cat_id order by pj desc limit 3;

group by cat_id,查詢出每個(gè)欄目下的平均價(jià)，設(shè)為結(jié)果集A。無論是查詢前3高，還是前3低，都要用到結(jié)果集A。結(jié)果集A頻繁使用，因此可以把結(jié)果保存一張表，下次來查這張表。但是，如果goods表又添加了商品，A結(jié)果集就與保存的臨時(shí)表不一樣了，不會(huì)單項(xiàng)更新數(shù)據(jù)，這時(shí)，可以用視圖解決。

創(chuàng)建視圖
建視圖的，要指定視圖的列名與列類型么?
不需要，它是個(gè)影子，繼承了上面的字段。 只是一種關(guān)系。

既然視圖只是表的某種查詢的投影，所以主要的步驟在于查詢表上，查詢的結(jié)果命名為視圖就可以了。

創(chuàng)建視圖的語法
create view 視圖名 as select 語句;

視圖的作用：

可以簡化查詢.

比如:復(fù)雜的統(tǒng)計(jì)時(shí)，先用視圖審查一個(gè)中間結(jié)果，在查詢視圖。

更精細(xì)的權(quán)限控制。

比如:某張用戶表為例。 2個(gè)網(wǎng)站搞合作，可以查詢對(duì)方網(wǎng)站的用戶.
需要向?qū)Ψ介_發(fā)用戶表的權(quán)限，但是呢，又不想開放用戶表中的密碼字段。創(chuàng)建一個(gè)視圖，除去密碼字段。

create view vuser
as
select user_id, username, emial from user;

開放這個(gè)視圖的權(quán)限給對(duì)方。

數(shù)據(jù)多，分表時(shí)可以用到。

比如：小說站 novel 表， 1000多萬篇。(一張表放200萬數(shù)據(jù)，大概)
可以分成 novel1, novel2, novel3 ... 直到放完。每張表放200萬。
查詢小說時(shí)，不知在哪張表。

create view novel as 
select title from novel1 union select title from novel2 ... union title from 最后一張表;

視圖是表的一個(gè)影子。
表與視圖，數(shù)據(jù)變化時(shí)的相互影響問題

表的數(shù)據(jù)變化，要影響到視圖的變化。

視圖如果變化了，表如何變?
視圖某種情況下，也是可以修改的
要求：視圖的數(shù)據(jù)和表的數(shù)據(jù) 一一對(duì)應(yīng)。 就像函數(shù)的映射才行。
表--> 退出視圖的對(duì)應(yīng)的數(shù)據(jù)
視圖-->推出表對(duì)應(yīng)的數(shù)據(jù)

視圖往往是用來查詢。

視圖的定義，是一直存在的，視圖并沒有占用空間。

.frm 表結(jié)構(gòu) 定義文件
.MYD 表的數(shù)據(jù)
.MYI 表的索引

刪除視圖

drop view gui;

一一對(duì)應(yīng)是指：根據(jù)select關(guān)系，從表中取出的行，只能計(jì)算出視圖中確定的一行。反之，視圖中任意抽一行，能夠反推出表中的確定一行。

cretae view v1
as
select * from goods where shop_price>300;

下次查詢價(jià)格>300 且小于500

select * from v1 where shop_price<500;


select * from goods where shop_price > 300 and shop_price < 500;

把建視圖時(shí)的條件 和 查視圖的條件 疊加起來， 直接去查表。

對(duì)于一些簡單視圖，在發(fā)揮作用的過程中，并沒有建立臨時(shí)表，而只是 把條件存起來，下次來查詢，把條件一合并，直接去查表。

條件疊加相比于建臨時(shí)表，那個(gè)快。
建表：查詢->形成臨時(shí)表->查詢臨時(shí)表

疊加：合并條件->查詢表

需要建立臨時(shí)表么，還是合并語句。

algorigthm作用就是是否需要合并條件查詢語句

algorigthm=merge (合并查詢語句)
temptable 臨時(shí)表
undefined 未定義，由系統(tǒng)判斷

create algorithm = merge view v2
as 
select * from goods where shop_price > 300;

select goods_id, goods_name, shop_price from v2 from where shop_price < 500;

v2視圖，并沒有建立臨時(shí)表
有時(shí)候，在復(fù)雜查詢下，必須建立臨時(shí)表。
比如，每個(gè)欄目的平均價(jià)格。

create view v3 as select *  from goods order by cat_id asc, shop_price desc;

下次再查，每個(gè)欄目最高的商品價(jià)格

select * from v3 group by cat_id;

思考：如何合并這兩個(gè)語句。

合并后，語句 出錯(cuò)了.
此時(shí)，語句不能合并，只能先建立臨時(shí)表。

create algorithm=temptable view v3 as
select goods_id, cat_id, goods_name, shop_price from goods order by cat_id asc, shop_price desc;

這張表，明確指定了生成臨時(shí)表。
如果拿不準(zhǔn)用什么，或者不愿意考慮。alogrithm=undefind .讓系統(tǒng)做決定.

對(duì)于簡單查詢使用 alogrihtm=merge

字符集

計(jì)算機(jī)中，只有 01010101
而人的世界中，有文字，有圖片，有聲音。

01 ----> 文字對(duì)應(yīng)起來

認(rèn)為的約定
65-->A
66-->B
...

二進(jìn)制編碼 到 字符的映射，就是字符集

ASSIC

1個(gè)字節(jié)8個(gè)位，就足夠。實(shí)際上ASSIC ，使用7個(gè)位表示就足夠。
0-127來表示
0xxx xxxx 來表示。最高位始終是0

gb2312 --> gbk
漢字2個(gè)字節(jié)
碰到>128的，就再往后找一字節(jié)，2個(gè)字節(jié)理解成中文。
繼續(xù)找，找到>128,就帶一個(gè)字節(jié)。<127 一個(gè)字節(jié)表示。

解決了多字節(jié)之后，又引來一個(gè)問題--世界各國的字符集，兼容問題。

Uincode
Unicode是一個(gè)世界通用的碼表

unicode 與 utf-8 的關(guān)系?
unicode 是 4個(gè)字節(jié)存儲(chǔ)。
unicode 只負(fù)責(zé)分配編號(hào)，而不負(fù)責(zé)在網(wǎng)絡(luò)上傳輸，需要經(jīng)過一定的規(guī)則進(jìn)行簡化。
轉(zhuǎn)換格式：UTF。
壓縮方式：UTF-8。

就像原文件 --> 壓縮文件的關(guān)系

給定unicode字符 --> uft-8 的二進(jìn)制值.
utf-8的二進(jìn)制值 --> unicode字符。解碼與編碼問題.

utf8占用幾個(gè)字節(jié)呢?
不可能定長，壓縮規(guī)則才有效果。 1-6個(gè)字節(jié).

GBK中文經(jīng)常在java中，被轉(zhuǎn)為utf-8，如何轉(zhuǎn)?
GBK和 unicode有對(duì)應(yīng)關(guān)系

GBK->unicode->utf-8

亂碼如何形成的?

解碼時(shí)，與實(shí)際編碼不一致，可修復(fù)。

傳輸過程中，編碼不一致，導(dǎo)致字節(jié)丟失不可修復(fù)。

set names作用

客戶端的字符集，設(shè)為A GBK
數(shù)據(jù)庫的字符集設(shè)為B UTF8(建表的時(shí)候設(shè)置的編碼)

客戶端收集到是什么編碼 和 服務(wù)器想存成什么編碼.

連接器的特性：鏈接客戶端與服務(wù)器
客戶端的字符先發(fā)給連接器，連接器選擇一種編碼將其轉(zhuǎn)換，臨時(shí)存儲(chǔ)。
再次轉(zhuǎn)換成，服務(wù)器需要的編碼

clientGBK --> 轉(zhuǎn)connUTF8, connUTF8-->不轉(zhuǎn)換，服務(wù)器UTF8
clientGBK --->GBK不轉(zhuǎn)給連接器， connGBK ， 轉(zhuǎn)connUTF8 --> 服務(wù)器UTF8

設(shè)置階段編碼

要想不亂碼，需要指定客戶端的編碼，讓連接器不理解錯(cuò)誤。這樣就不會(huì)存入錯(cuò)誤數(shù)據(jù)。往回去的時(shí)候，還要告訴連接器，如果從服務(wù)器返回?cái)?shù)據(jù)，應(yīng)該轉(zhuǎn)成什么格式。

一共3個(gè)參數(shù):

客戶端發(fā)送的編碼

連接器使用的編碼

獲取的返回?cái)?shù)據(jù)的編碼

需要明確告知服務(wù)器，客戶端編碼:

set character_set_client = gbk;

告訴連接器使用utf8

set character_set_connection = utf8;

告訴服務(wù)器返回結(jié)果使用GBK

set character_set_reslutes=gbk;

如果服務(wù)器設(shè)置嚴(yán)格，不允許插入。不嚴(yán)格，插入會(huì)造成數(shù)據(jù)丟失。

收到客戶端編碼 和 往服務(wù)器送， 兩個(gè)過程.

gb2312 < gbk < utf8

sever >= conntion >= client

如果3者都是 GBK，可以簡寫。簡寫成 set names gbk

牽涉到數(shù)據(jù)庫，不亂碼

正確指定客戶端的編碼

合理選擇連接器的編碼

正確指定返回內(nèi)容編碼

網(wǎng)頁文件本身編碼

mate 信息編碼.

一個(gè)中文在UTF8下是三個(gè)字節(jié)

記事本在打開的時(shí)候，分辨不出來要使用什么編碼解析。
分析編碼的特點(diǎn)，推測出來使用什么編碼。

如果字節(jié)比較少，容易推測錯(cuò)誤。

在utf8的BOM，是3個(gè)字節(jié)一個(gè)中文。

utf8文件的時(shí)候，前面多了三個(gè)字節(jié)：EF BB BF。
這3個(gè)字節(jié)不用來顯示，是用來辨識(shí)utf8。是BOM信息。

在session cookie 使用前，不能有任何輸出，空行空格都不行。
在utf8有BOM頭的信息下，多個(gè)三個(gè)字節(jié)。會(huì)在session 或 cookie 會(huì)出現(xiàn)錯(cuò)誤。

utf8和utf-8什么區(qū)別?
utf-8是一種編碼，世界上utf-8只有一個(gè)。

表達(dá)這個(gè)編碼方式. UTF-8 utf-8 utf8 UTF8。都是指選用utf-8這種方式。名稱不一樣，別名。推薦UTF 大寫。

mysql引擎

例子：
一份歌詞，保存方式。
可以怎么保存。

記載腦子里，

用鉛筆寫紙上

用鋼筆寫

刻石頭上

美國科學(xué)家，收集了人類的音樂，符號(hào)。(二泉映月)

不變的是數(shù)據(jù)，變化的是"存儲(chǔ)的格式"

如果說有：
sanme,age
三 22
四 23

這個(gè)信息，無論用什么engine來存儲(chǔ)，都一樣。
但是，不同engine，都有存儲(chǔ)信息功能，則必須不一樣的地方。

總結(jié)：engine 引擎，就是mysql存儲(chǔ)數(shù)據(jù)的不同方式。

數(shù)據(jù)庫對(duì)同樣的數(shù)據(jù)，有著不同的存儲(chǔ)方式和管理方式，在mysql中，稱為存儲(chǔ)引擎。

事務(wù)

# 建立2張一樣結(jié)構(gòu)的表，但是引擎不一樣
create table a1 ( uname varchar(20), money int ) engine myisam charset utf8;
create table a2 ( uname varchar(29), money int ) engine innodb charset utf8;

insert into a1 values ("zhangsan", 3000), ("lisi", 2000);
insert into a2 values ("zhangsan", 3000), ("lisi", 2000);

# 在存儲(chǔ)數(shù)據(jù)上是一樣的。

銀行轉(zhuǎn)賬

張三給李四轉(zhuǎn)500元

張三 -500
李四 +500

這2步，必須都完成了，轉(zhuǎn)賬才完成。

想這種，2步或n步，從邏輯上來講，是一個(gè)“原子操作”
即，要么成功，要么都不成功。

如何保障這種特性。
使用事務(wù). 在這種環(huán)境下誕生的一個(gè)概念，操作。

事務(wù)特性：原子性，一致性，隔離性，持久性。

原子性就是：2步或N步操作，邏輯上不可分割。
通俗說，要么成功，要么都不成功。

# 如何使用事務(wù)?
# strat transaction; 開啟事務(wù)。
# ... 中間事務(wù)操作 
# commit; 提交事務(wù)

# 原子性
strat transaction;

update a1 set money = money + 1000 where uname = "zhangsan";
update a2 set moneys = money - 1000 where uname = "lisi"; # 失敗, 故意表名打錯(cuò)，模擬網(wǎng)絡(luò)故障等失敗場景。

# 整體的轉(zhuǎn)賬操作，從邏輯上講，應(yīng)該失敗，即zhangsan的錢，不能多500
# 部分失敗，則之前的成功操作，如何處理。
# 回滾 
rollback;

# 一致性
# 是指操作前后，值的變化，邏輯上成立。

# 隔離性
事務(wù)結(jié)束前，每一步的操作帶來的影