Mysql - 使用入門

EdenWu發表於2021-04-28

本文是學習 Mysql必知必會後的筆記
學習之前需要建立一個資料庫，然後匯入下面兩個mysql指令碼

create database db1 charset utf8;

########################################
# MySQL Crash Course
# Example table creation scripts
########################################


########################
# Create customers table
########################
CREATE TABLE customers
(
  cust_id      int       NOT NULL AUTO_INCREMENT,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

#########################
# Create orderitems table
#########################
CREATE TABLE orderitems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL ,
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;


#####################
# Create orders table
#####################
CREATE TABLE orders
(
  order_num  int      NOT NULL AUTO_INCREMENT,
  order_date datetime NOT NULL ,
  cust_id    int      NOT NULL ,
  PRIMARY KEY (order_num)
) ENGINE=InnoDB;

#######################
# Create products table
#######################
CREATE TABLE products
(
  prod_id    char(10)      NOT NULL,
  vend_id    int           NOT NULL ,
  prod_name  char(255)     NOT NULL ,
  prod_price decimal(8,2)  NOT NULL ,
  prod_desc  text          NULL ,
  PRIMARY KEY(prod_id)
) ENGINE=InnoDB;

######################
# Create vendors table
######################
CREATE TABLE vendors
(
  vend_id      int      NOT NULL AUTO_INCREMENT,
  vend_name    char(50) NOT NULL ,
  vend_address char(50) NULL ,
  vend_city    char(50) NULL ,
  vend_state   char(5)  NULL ,
  vend_zip     char(10) NULL ,
  vend_country char(50) NULL ,
  PRIMARY KEY (vend_id)
) ENGINE=InnoDB;

###########################
# Create productnotes table
###########################
CREATE TABLE productnotes
(
  note_id    int           NOT NULL AUTO_INCREMENT,
  prod_id    char(10)      NOT NULL,
  note_date datetime       NOT NULL,
  note_text  text          NULL ,
  PRIMARY KEY(note_id),
  FULLTEXT(note_text)
) ENGINE=MyISAM;


#####################
# Define foreign keys
#####################
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

########################################
# MySQL Crash Course
# Example table population scripts
########################################


##########################
# Populate customers table
##########################
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'rabbit@wascally.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', 'sam@yosemite.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd');


########################
# Populate vendors table
########################
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1002,'LT Supplies','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1003,'ACME','555 High Street','Los Angeles','CA','90046', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1004,'Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1005,'Jet Set','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1006,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'45678', 'France');


#########################
# Populate products table
#########################
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV02', 1001, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('OL1', 1002, 'Oil can', 8.99, 'Oil can, red');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT2', 1003, 'TNT (5 sticks)', 10, 'TNT, red, pack of 10 sticks');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FB', 1003, 'Bird seed', 10, 'Large bag (suitable for road runners)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SAFE', 1003, 'Safe', 50, 'Safe with combination lock');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('DTNTR', 1003, 'Detonator', 13, 'Detonator (plunger powered), fuses not included');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP1000', 1005, 'JetPack 1000', 35, 'JetPack 1000, intended for single use');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP2000', 1005, 'JetPack 2000', 55, 'JetPack 2000, multi-use');



#######################
# Populate orders table
#######################
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20005, '2005-09-01', 10001);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20006, '2005-09-12', 10003);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20007, '2005-09-30', 10004);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20008, '2005-10-03', 10005);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20009, '2005-10-08', 10001);


###########################
# Populate orderitems table
###########################
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'ANV01', 10, 5.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'ANV02', 3, 9.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 3, 'TNT2', 5, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 4, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'JP2000', 1, 55);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'TNT2', 100, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'FC', 50, 2.50);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'OL1', 1, 8.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'SLING', 1, 4.49);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 4, 'ANV03', 1, 14.99);

#############################
# Populate productnotes table
#############################
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(101, 'TNT2', '2005-08-17',
'Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(102, 'OL1', '2005-08-18',
'Can shipped full, refills not available.
Need to order new can if refill needed.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(103, 'SAFE', '2005-08-18',
'Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(104, 'FC', '2005-08-19',
'Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(105, 'TNT2', '2005-08-20',
'Included fuses are short and have been known to detonate too quickly for some customers.
Longer fuses are available (item FU1) and should be recommended.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(106, 'TNT2', '2005-08-22',
'Matches not included, recommend purchase of matches or detonator (item DTNTR).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(107, 'SAFE', '2005-08-23',
'Please note that no returns will be accepted if safe opened using explosives.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(108, 'ANV01', '2005-08-25',
'Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(109, 'ANV03', '2005-09-01',
'Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(110, 'FC', '2005-09-01',
'Customer complaint: rabbit has been able to detect trap, food apparently less effective now.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(111, 'SLING', '2005-09-02',
'Shipped unassembled, requires common tools (including oversized hammer).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(112, 'SAFE', '2005-09-02',
'Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(113, 'ANV01', '2005-09-05',
'Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(114, 'SAFE', '2005-09-07',
'Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
Comment forwarded to vendor.'
);

1.0 初入Mysql

1.1 Mysql的連線

mysql -u 使用者名稱 -p -h 主機名 -P 埠號

-u 指名是誰來連線資料庫
-p 連線資料庫使用者的密碼
-h ip地址或主機名(也就是dns能解析到ip地址的網址)
-P 埠號，預設Mysql為 3306

1.2 成功連線資料後

當然了，連線上了，會顯示一大串的英語什麼的，這裡就說到幾個會用上的把

命令用 ';' or '\g' 結束，換句話說，僅按下Enter後不執行任何命令
輸入 'help' or '\h' 獲取幫助，例如: '\h select' or 'help select' 注意這裡結尾不需要加 ';'
輸入 'quit' or 'exit' or '\q' 退出連線

1.2.1 Mysql瞭解資料庫與表

檢視當前使用者或指定使用者的許可權

show grants; --當前使用者
show grants for 使用者名稱; --特定使用者

mysql> show grants;
+-------------------------------------------------------------+
| Grants for root@%                                           |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+
ALL PRIVILEGES : 表示那些許可權，這裡是所有許可權
*.* : 中第一個*是哪個資料庫，如果為*指所有資料庫，第二個*指選中庫的哪個表，為*指所有表
root : 指使用者名稱
@後面的 % : 指授權地址，%指所有地址，localhost指本地，也可以為127.0.0.1
WITH GRANT OPTION : 這個選項表示該使用者可以將自己擁有的許可權授權給別人

檢視當前有那些資料庫

show databases;

檢視用於建立資料庫的命令

show create database 資料庫名字;

檢視伺服器狀態資訊

show status \G;
--\G: 垂直顯示, 用於顯示得更加清洗，可不加

檢視伺服器錯誤或警告

show errors\G;
show warnings \G;

使用資料庫

use 資料庫名字;

檢視資料庫內的表

show tables;

檢視建立這個表的語句

show create table 表名\G;

檢視錶結構

show columns from 表名;
desc 表名;

2.0 簡單的 select 語句

檢視單個列的資料

select 列名 from 表名;
use mysql;
select User from user;

檢視多個列的資料

select 列名1,列名2,...,列名N from 表名;
select User, Host from user;

檢視錶內所有資料

select * from 表名;
select * from user\G;

注意：除非你確實是需要表中的所有資料，否則最好不要使用 * 萬用字元, 使用 * 會檢索出所有資料,這會降低檢索的效能。當然了，使用 * 萬用字元也可以檢索出來未知的列

2.1 去重 distinct

當然了去掉重複的資料可以使用下面的命令

-- distinct不能多列使用，除非多列檢測出來的一行或者多行的組合有重複，不然就都會顯示出來
select distinct 列名 from 表名;
select distinct Host from user;

2.2 限制顯示結果 limit

select 列名 from 表名 limit x,y\G;
x : 開始的位置
y : 開始位置後的行數

-- 這裡x預設為0，即開始的 第0行 到即到它後面的 2行
select * from user limit 2\G;

-- 這裡還有另外一種寫法
select * from user limit 2 offset 0 \G;

-- 即limit y offset x

2.3 完全限制表庫

這樣做的目的是限制唯一的列名

select 表名.列名 from 資料庫名.表名;
select user.User from mysql.user;

3.0 資料的排序

排序的資料在很多場合可能都會用上，可以下面下面的方式進行檢索排序

select 列名 from 表名 order by 列名;
select prod_name from products order by prod_name;
-- order by 同時也可以按照非檢索的列名進行排序，通常不這樣做

3.1 多個列排序

select 列1, 列2, 列3 from 表名 order by 列1, 列2;
select prod_id, prod_price,prod_name from products order by prod_price, prod_name;

3.2 指定排序方向 desc(降序) asc(預設升序)

select 列1, 列2, 列3 from 表名 order by 列1 desc, 列2 asc;
select prod_id, prod_price,prod_name from products order by prod_price desc, prod_name asc;

3.3 order by 於 limit的組合

select 列名 from products order by 列名 limit x,y;
select prod_price from products order by prod_price limit 5;

4.0 過濾行資料(where子句)

where 字句操作符號

操作符	說明
>	大於
!=(<>)	不等於
=	等於
<	小於
>=	大於等於
<=	小於等於
between	在兩者之間
and	兩種條件都要滿足
or	只要滿足其中的一種條件
in	指定檢索的範圍
not	否定後面跟的條件

基本使用方法

select 列1, 列2 from 表名 where 列資料 = 2.5;
select prod_name, prod_price from products where prod_price = 2.5;

4.1 檢索值範圍 between x and y 即(x <= value <= y)

select 列1, 列2 from products where 列1 between X and Y;

select prod_name, prod_price from products where prod_price between 5 and 10;

4.2 空值檢索

-- 應為空值具有特殊含義, 所以這裡用的是 is

select 列名 from 表名 where 列名 is null;
select cust_id from customers where cust_email is null;

4.3 or操作符

select 列名, 列名 from 表名 where 列名 = 值 or 列名 = 值;
select prod_name, prod_price from products where vend_id = 1002 or vend_id = 1003;

4.4 or與and的組合

-- 這裡需要的注意的是在進行組合運算的時候新增個 () 不會有錯的
-- 如果不新增 () ，下面的例子就會出現很奇怪的事
select prod_name, prod_price from products where  prod_price >=10 and (vend_id = 1002 or vend_id = 1003);

4.5 in操作符

上面的句子其實是可以這麼化簡的

select 列名 from 表名 where 列名 in (值1, 值2, ..., 值n);
select prod_name, prod_price from products where  prod_price >=10 and vend_id in (1002, 1003);

4.6 not操作符

對上面的 vend_in 篩選的結果取反

select 列名 from 表名 where 列名 not in (值1, 值2, ..., 值n);
select prod_name, prod_price from products where  prod_price >=10 and vend_id not in (1002, 1003);

5.0 萬用字元的使用

萬用字元

% 表示任何字元出現任何次
_ 表示任何字元出現一次

5.1 like操作符

select 列名 from 表名 where 列名 like 帶有萬用字元的值;

-- 如果我只記得prod_name的值有je開頭的，我應該咋匹配呢？
select prod_id, prod_name from products where prod_name like 'je%';

或者我想搜尋一個文字中包含什麼的

select prod_id, prod_name from products where prod_name like '%se%';

......

5.2 萬用字元的使用技巧

萬用字元確實很好用，但是這個開銷會比前面的檢索方式慢太多

通過其他方式能檢索到的，就完全沒比較使用萬用字元了
在確實需要使用的時候，最好不用把萬用字元置於搜尋模式的開始處，這樣搜尋是最慢的

6.0 正規表示式進行檢索(儘量少用)

select 列名 from 表名 where 列表 regexp 正規表示式;

select prod_name from products where prod_name regexp '.000' order by prod_name;

mysql> select prod_name from products where prod_name regexp '1000' order by prod_name;
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
+--------------+
1 row in set (0.00 sec)

mysql> 
mysql> select prod_name from products where prod_name like '1000' order by prod_name;
Empty set (0.00 sec)

由上面可以發現，like 是匹配整個列的，當列資料不一致，即不返回資料，
而 regexp 是在列值內進行匹配，如果被匹配上了，當然就返回資料了

6.1 正規表示式進行or匹配 ' | '

select prod_name from products where prod_name regexp '1000|2000' order by prod_name;

6.2 匹配幾個字元之一 ' [] '

select prod_name from products where prod_name regexp '[123] Ton' order by prod_name;

6.3 匹配範圍 ' [a-g] ' ... '[0-4]'

select prod_name from products where prod_name regexp '[1-5] Ton' order by prod_name;

6.4 匹配特殊字元

在mysql需要使用 '\\' 為前導的匹配方式，如匹配 '.' 則表示式為 '\\.'

元字元	說明
\\f	換頁
\\n	換行
\\r	回車
\\t	製表
\\v	縱向製表

-- 匹配 vend_name 列中含有小數點記錄
select vend_name from vendors where vend_name regexp '\\.';

6.5 匹配字元類

字元類

類	說明
[:alnum:]	匹配字元和數字（同 [a-zA-Z0-9]）
[:alpha:]	任意字元（同[a-zA-Z]）
[:blank:]	空格和製表（同[\\t]）
[:cntrl:]	ASCII控制字元（ASCII 0到31和127）
[:digit:]	任意數字（同[0-9]）
[:graph:]	與[:print:]相同但不包括空格
[:lower:]	任意小寫字母（同[a-z]）
[:print:]	任意可列印字元
[:punct:]	既不在[:alnum:]又不在[:cntrl:]中的任意字元
[:space:]	包括空格在內的任意空白字元（同[\\f\\n\\r\\t\v]）
[:upper:]	任意大寫字母（同[A-Z]）
[:xdigit:]	任意十六進位制數字（同[a-fA-F0-9]）

6.5 匹配多個示例

元字元	說明
*	0個或多個匹配
+	1個或多個匹配（等於 {1,}）
?	0個或1個匹配（等於 {0,1}）
{n}	n 是一個非負整數。匹配確定的 n 次
{n,m}	最少匹配 n 次且最多匹配 m 次 (m<=255)

select prod_name from products where prod_name regexp '\\([0-9] sticks?\\)';

select prod_name from products where prod_name regexp '[[:digit:]]{4}';

6.6 定位符

定位元字元

元字元	說明
^	文字的開始( ^在[]內表示非例如 [^a-z] 即非 a-z )
$	文字的結尾
[[:<:]]	詞的開始
[[:>:]]	詞的結尾

select prod_name from products where prod_name regexp '^[0-9\\.]';

7.0 建立計算欄位

7.1 concat() 拼接欄位

使用 concat() 函式用於把多個列拼接起來

select concat(列1, 列2, ..., 列n) from 表名;
select concat(vend_name, '(', vend_country, ')') from vendors;

7.2 as 使用別名

上面輸出的表抬頭是不是感覺好醜，那麼就可以使用 as

select concat(列1, 列2, ..., 列n) as 別名 from 表名;
select concat(vend_name, '(', vend_country, ')')  as vend_tittle from vendors;

7.2.1 trim() 刪除左右兩邊的空格 rtrim(),ltrim()

select concat(trim(vend_name), '(', trim(vend_country), ')')  as vend_tittle from vendors;

7.3 算式計算

SELECT
	prod_id,
	quantity,
	item_price,
	quantity * item_price AS expanded_price 
FROM
	orderitems 
WHERE
	order_num = 20005;

操作符	說明
+	加
-	減
*	乘
/	除

函式的測試

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-03-23 18:15:02 |
+---------------------+
1 row in set (0.00 sec)

mysql> select trim('  aaa');
+---------------+
| trim('  aaa') |
+---------------+
| aaa           |
+---------------+
1 row in set (0.00 sec)

mysql> select 'hello' regexp '[a-z]';
+------------------------+
| 'hello' regexp '[a-z]' |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

mysql> select 'hello' regexp '[0-9]';
+------------------------+
| 'hello' regexp '[0-9]' |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.00 sec)

8.0 函式

8.1 常見文理處理函式

函式	說明
length(s)	返回串s的長度
trim(s)	去掉字串 s 開始和結尾處的空格
upper(s)	將字串轉換為大寫
left(s,n)	返回字串 s 的前 n 個字元
lower(s)	將字串 s 的所有字母變成小寫字母
locate(s1,s)	從字串 s 中獲取 s1 的開始位置
right(s,n)	返回字串 s 的後 n 個字元
ltrim(s)	去掉字串 s 開始處的空格
rtrim(s)	去掉字串 s 結尾處的空格
substring(s, start, length)	從字串 s 的 start 位置擷取長度為 length 的子字串

8.1 常見日期和時間處理函式

函式名	描述
addDate(d,n)	計算起始日期 d 加上 n 天的日期
addTime(t,n)	n 是一個時間表示式，時間 t 加上時間表示式 n
curDate()	返回當前日期
curTime()	返回當前時間
date()	從日期或日期時間表示式中提取日期值
CURRENT_TIME	返回當前時間
CURRENT_DATE()	返回當前日期
CURRENT_TIMESTAMP()	返回當前日期和時間
dateDiff(d1,d2)	計算日期 d1->d2 之間相隔的天數
date_add(d，INTERVAL expr type)	計算起始日期 d 加上一個時間段後的日期
date_format(d,f)	按表示式 f的要求顯示日期 d
day(d)	返回日期值 d 的日期部分
dayOfMonth(d)	計算日期 d 是本月的第幾天
dayOfWeek(d)	日期 d 今天是星期幾，1 星期日，2 星期一，以此類推
dayOfYear(d)	計算日期 d 是本年的第幾天
hour(t)	返回 t 中的小時值
minute(t)	返回 t 中的分鐘值
month(d)	返回日期d中的月份值，1 到 12
now()	返回當前日期和時間
time(expression)	提取傳入表示式的時間部分
year(d)	返回年份

select cust_id, order_num from orders where order_date = '2005-09-01';

select cust_id, order_num from orders where date(order_date) = '2005-09-01';

select cust_id, order_num from orders where year(order_date) = 2005 and month(order_date)=9;

SELECT
	cust_id,
	order_num 
FROM
	orders 
WHERE
	date( order_date ) BETWEEN '2005-09-01' 
	AND '2005-09-30';

8.2 常見數值處理函式

函式名	描述
ABS(x)	返回 x 的絕對值
COS(x)	求餘弦值(引數是弧度)
EXP(x)	返回 e 的 x 次方
MOD(x,y)	返回 x 除以 y 以後的餘數
PI()	返回圓周率(3.141593）
ROUND(x)	返回離 x 最近的整數
SIN(x)	求正弦值(引數是弧度)
SQRT(x)	返回x的平方根
TAN(x)	求正切值(引數是弧度)

9.0 聚合or集合函式

聚集函式（aggregate function）執行在行組上，計算和返回單個值的函式

聚集函式	說明
AVG([distinct] expr)	求平均值
COUNT({*	[distinct] } expr)
MAX([distinct] expr)	求最大值
MIN([distinct] expr)	求最小值
SUM([distinct] expr)	求累加和

select avg(列名) from 表名;
-- ......

-- avg() 僅僅用於單列，多列著多個 avg()函式
-- 忽略列值為 NULL 的行
select avg(prod_price) as avg_price from products;

-- count()
select count(*) from products; --所有行數
select count(prod_name) from products; --忽略NULL的所有行

-- max() 略NULL的行
select max(prod_price) as max_price from products;

-- min() 略NULL的行
select min(prod_price) min_price from products;

-- sum() 略NULL的行
select sum(prod_price) total_price from products;

9.1 聚合or集合不同的值,預設為all

巢狀的 distinc

select avg(distinct prod_price) as avg_price from products;

10.0 分組

10.1 建立分組 group by

select 列名 from 表名 group by 列名;

select vend_id, count(*) as nums_prods from products group by vend_id;

關於 group by 的使用說明

1. group by 子句可以包含任意數目的列
1. 如果在group by子句中巢狀了分組,資料將在最後規定的分組上進行分組
1. group by 子句中列出的每個列都必須是檢索列或有效的表示式(但不能是聚合函式)
  可以這樣想即 select 列1 ... group by 列1 注意不能 group by 別名
1. 如果列中含有 NULL 值，則分組單獨顯示出來，有多個Null，它們將分為一組
1. group by 子句必須出現在 where 子句後，order by 子句之前

使用 with rollup 關鍵字，可以得到每個分組以及每個分組彙總級別的值

-- 返回單獨的vend_id供貨總數
select count(vend_id)  from products where vend_id = '1001';
+----------------+
| count(vend_id) |
+----------------+
|              3 |
+----------------+
1 row in set (0.00 sec)


-- 返回每個vend_id供貨總數
select vend_id, count(*) as nums_prods from products group by vend_id with rollup;
+---------+------------+
| vend_id | nums_prods |
+---------+------------+
|    1001 |          3 |
|    1002 |          2 |
|    1003 |          7 |
|    1005 |          2 |
|    NULL |         14 |
+---------+------------+
5 rows in set (0.00 sec)

10.1 過濾分組 having

注意 where 為過濾行，但是 having 支援 where 的所有操作

select cust_id, count(*) as orders from orders group by cust_id having count(*) >= 2;

SELECT
	vend_id,
	COUNT(*) AS num_prods 
FROM
	products 
WHERE
	prod_price >= 10 
GROUP BY
	vend_id 
HAVING
	COUNT(*) >= 2;

10.2 分組和排序

檢索總訂單價格大於50的訂單號和總計訂單價格在按照總計訂單價格排序輸出

SELECT
	order_num,
	SUM( quantity * item_price ) AS order_total 
FROM
	orderitems 
GROUP BY
	order_num 
HAVING
	order_total >= 50 
ORDER BY
	order_total;

SELECT
	order_num,
	SUM( quantity * item_price ) AS order_total 
FROM
	orderitems 
GROUP BY
	order_num 
HAVING
	order_total >= 50 
ORDER BY
	order_total 
LIMIT 2;

11.0 使用子查詢

子查詢：即巢狀在其它查詢中的查詢

現在需要列出訂購物品TNT2的所有客戶

檢索包含物品TNT2的所有訂單的編號
檢索具有前一步驟列出的訂單編號的所有客戶ID
檢索前一步驟返回的所有客戶ID的客戶資訊

-- 1）
select order_num from orderitems where prod_id = 'TNT2';
+-----------+
| order_num |
+-----------+
|     20005 |
|     20007 |
+-----------+
2 rows in set (0.00 sec)

-- 2)
select cust_id from orders where order_num in (20005, 20007);
+---------+
| cust_id |
+---------+
|   10001 |
|   10004 |
+---------+
2 rows in set (0.00 sec)

-- 3）
select cust_name, cust_contact from customers where cust_id in (10001, 10004);
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
2 rows in set (0.01 sec)

現在呢，我們就可以把這三個查詢合併為一個查詢

SELECT
	cust_name,
	cust_contact 
FROM
	customers 
WHERE
	cust_id IN (
	SELECT
		cust_id 
	FROM
		orders 
WHERE
	order_num IN ( SELECT order_num FROM orderitems WHERE prod_id = 'TNT2' ));

像上面的語句可能不是最有效率的方式，可以在後面參考（聯結表）

11.1 作為計算欄位使用子查詢

假如需要顯示customers表中每個客戶的總訂單數。訂單與相應的客戶ID儲存在orders表中

從customers表中檢索客戶列表
對於檢索出來的每個客戶，統計其在orders表中的訂單數目

SELECT
	cust_name,
	cust_state,
	( SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id ) AS orders 
FROM
	customers 
ORDER BY
	cust_name;

12.0 聯結表

Mysql聯結的表越多會導致效能的下降

12.1 建立聯結表

-- 等值聯結
SELECT
	vend_name,
	prod_name,
	prod_price 
FROM
	vendors,
	products 
WHERE
	vendors.vend_id = products.vend_id 
ORDER BY
	vend_name,
	prod_name;

如果上面的句子刪除掉 where 就會出現笛卡爾積，這並非是我們需要的資料

-- 笛卡爾積
SELECT
	vend_name,
	prod_name,
	prod_price 
FROM
	vendors,
	products 
ORDER BY
	vend_name,
	prod_name;

12.2 內部聯結

此聯結與上面的等值接連一致,唯一不同的是這裡使用關鍵子 inner join ... on 進行限定

SELECT
	vend_name,
	prod_name,
	prod_price 
FROM
	vendors
	INNER JOIN products ON vendors.vend_id = products.vend_id;

12.3 聯結多個表

SELECT
	vend_name,
	prod_name,
	quantity,
	prod_price 
FROM
	vendors,
	products,
	orderitems 
WHERE
	vendors.vend_id = products.vend_id 
	AND orderitems.prod_id = products.prod_id 
	AND order_num = 20005;

11.0 中出現的問題現在需要列出訂購物品TNT2的所有客戶即可以通過下面的方式處理

SELECT
	cust_name,
	cust_contact 
FROM
	customers 
WHERE
	cust_id IN (
	SELECT
		cust_id 
	FROM
		orders 
WHERE
	order_num IN ( SELECT order_num FROM orderitems WHERE prod_id = 'TNT2' ));

SELECT
	cust_name,
	cust_contact 
FROM
	orderitems,
	orders,
	customers 
WHERE
	customers.cust_id = orders.cust_id 
	AND orderitems.order_num = orders.order_num 
	AND prod_id = 'TNT2';

12.4 使用別名

這條語句和上面的查詢結果一致，但使用了別名

SELECT
	cust_name,
	cust_contact 
FROM
	orderitems AS oi,
	orders AS o,
	customers AS c 
WHERE
	c.cust_id = o.cust_id 
	AND oi.order_num = o.order_num 
	AND prod_id = 'TNT2';

12.5 自聯結

自聯結: 同一張表的聯結

SELECT
	p1.prod_id, p1.prod_name 
FROM
	products AS p1,
	products AS p2 
WHERE
	p1.vend_id = p2.vend_id 
	AND p2.prod_id = 'DTNTR';

12.6 外聯結

許多聯結將一個表中的行與另外一個表中的行進行關聯。但有時會需要包含沒有關聯的那些行，這就用到了外聯結

LEFT OUTER JOIN : OUTER JOIN左邊的表
RIGHT OUTER JOIN : OUTER JOIN右邊的表

SELECT
	customers.cust_id, orders.order_num 
FROM
	customers
	LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;

12.7 使用帶聚合函式的聯結

檢索所有客戶及客戶所下的訂單數

SELECT
	customers.cust_name,
	customers.cust_id,
	COUNT( orders.order_num ) AS num_ord 
FROM
	customers
	INNER JOIN orders ON customers.cust_id = orders.cust_id 
GROUP BY
	customers.cust_id;

檢索所有客戶及客戶所下的訂單數，使用左外聯結來包含所有客戶，甚至包含那些沒有下訂單的客戶

SELECT
	customers.cust_name,
	customers.cust_id,
	COUNT( orders.order_num ) AS num_ord 
FROM
	customers
	 LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id 
GROUP BY
	customers.cust_id;

13.0 組合查詢

13.1 建立組合查詢 union

SELECT
	vend_id,
	prod_id,
	prod_price 
FROM
	products 
WHERE
	prod_price > 5 UNION
SELECT
	vend_id,
	prod_id,
	prod_price 
FROM
	products 
WHERE
	vend_id IN ( 1001, 1002 );

union 規則

union 必須由兩條或兩條以上的select語句組成
union 中的每個查詢必須包含相同的列、表示式或聚集函式（順序可以不一致）
列資料型別必須相容，及可以隱含轉換的型別
union 可用於組合不同的表

13.2 包含或取消重複的行 union all

13.1 的例子中，如果兩條語句單獨查詢共計顯示9條資料，而使用union卻只顯示了8行，
這是因為union預設會去除掉重複的行，不然不祥去重，可以使用 union all

SELECT
	vend_id,
	prod_id,
	prod_price 
FROM
	products 
WHERE
	prod_price > 5 UNION ALL
SELECT
	vend_id,
	prod_id,
	prod_price 
FROM
	products 
WHERE
	vend_id IN ( 1001, 1002 );

13.3 對組合查詢結果排序

order by只能放在最後的select查詢上

SELECT
	vend_id,
	prod_id,
	prod_price 
FROM
	products 
WHERE
	prod_price > 5 UNION
SELECT
	vend_id,
	prod_id,
	prod_price 
FROM
	products 
WHERE
	vend_id IN ( 1001, 1002 ) 
ORDER BY
	vend_id,
	prod_price;

上面的查詢看似只對第二條select語句進行排序，實則是對所有的select語句進行排序，

14.0全文字搜尋

並不是所有的引擎都支援全文索引。兩個最常使用的引擎為MyISAM和InnoDB,前者支援全文字索引，而後者不支援

前面所提到的 like 和 regexp 都能實現這個功能，但是會有如下的的限制

效能 -- 萬用字元和正規表示式匹配通常要求mysql嘗試匹配表中所有行(而且這些搜尋極少使用表索引)。
因此，由於被搜尋行數不斷增加,這些搜尋可能非常耗時
明確控制
智慧化的結果 --

14.1啟用全文字搜尋

-- FULLTEXT() 可指定多個列
CREATE TABLE `productnotes` (
  `note_id` int(11) NOT NULL AUTO_INCREMENT,
  `prod_id` char(10) NOT NULL,
  `note_date` datetime NOT NULL,
  `note_text` text,
  PRIMARY KEY (`note_id`),
  FULLTEXT (`note_text`)
) ENGINE=MyISAM;

注意：不要在匯入資料時使用 fulltext，可以先匯入資料後在修改表

14.2進行全文字搜尋

Match() 指定被搜尋的列
傳遞給Match()的值必須與fulltext定義中的相同，如果指定多個列，則必須列出它們（且次序正確）
Against() 指定要使用的搜尋表示式 - 不區分大小寫

select note_text from productnotes where Match(note_text) Against('rabbit')\G;
*************************** 1. row ***************************
note_text: Customer complaint: rabbit has been able to detect trap, food apparently less effective now.
*************************** 2. row ***************************
note_text: Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.
2 rows in set (0.00 sec)

檢視全文搜尋的排序如何工作的

select note_text, Match(note_text) Against('rabbit') as rank from productnotes \G;
*************************** 1. row ***************************
note_text: Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.
     rank: 0
*************************** 2. row ***************************
note_text: Can shipped full, refills not available.
Need to order new can if refill needed.
     rank: 0
*************************** 3. row ***************************
note_text: Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers.
     rank: 0
*************************** 4. row ***************************
note_text: Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.
     rank: 1.5905543565750122
*************************** 5. row ***************************
note_text: Included fuses are short and have been known to detonate too quickly for some customers.
Longer fuses are available (item FU1) and should be recommended.
     rank: 0
*************************** 6. row ***************************
note_text: Matches not included, recommend purchase of matches or detonator (item DTNTR).
     rank: 0
*************************** 7. row ***************************
note_text: Please note that no returns will be accepted if safe opened using explosives.
     rank: 0
*************************** 8. row ***************************
note_text: Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.
     rank: 0
*************************** 9. row ***************************
note_text: Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.
     rank: 0
*************************** 10. row ***************************
note_text: Customer complaint: rabbit has been able to detect trap, food apparently less effective now.
     rank: 1.6408053636550903
*************************** 11. row ***************************
note_text: Shipped unassembled, requires common tools (including oversized hammer).
     rank: 0
*************************** 12. row ***************************
note_text: Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.
     rank: 0
*************************** 13. row ***************************
note_text: Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.
     rank: 0
*************************** 14. row ***************************
note_text: Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
Comment forwarded to vendor.
     rank: 0
14 rows in set (0.00 sec)

14.3使用查詢擴充套件 with query expansion

查詢擴充套件用來放寬所返回的文字搜尋結果的範圍

在使用查詢擴充套件的時候Mysql對資料和索引進行兩邊掃描完成搜尋

-- 在在上的例子中沒有使用查詢擴充套件的時候只能返回兩行
-- 現在使用返回了6行
select note_text from productnotes where Match(note_text) Against('rabbit' with query expansion)\G;
*************************** 1. row ***************************
note_text: Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.
*************************** 2. row ***************************
note_text: Customer complaint: rabbit has been able to detect trap, food apparently less effective now.
*************************** 3. row ***************************
note_text: Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.
*************************** 4. row ***************************
note_text: Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.
*************************** 5. row ***************************
note_text: Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.
*************************** 6. row ***************************
note_text: Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.
6 rows in set (0.00 sec)

14.4布林文字搜尋 in boolean mode

即使沒有定義fulltext索引，也可以使用它喲

select note_text from productnotes where Match(note_text) Against('rabbit' in boolean mode) \G;
*************************** 1. row ***************************
note_text: Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.
*************************** 2. row ***************************
note_text: Customer complaint: rabbit has been able to detect trap, food apparently less effective now.
2 rows in set (0.00 sec)

-- 匹配包含 heavy 但不包含任意一repo開始的詞
select note_text from productnotes where Match(note_text) Against('heavy -rope*' in boo
lean mode) \G;
*************************** 1. row ***************************
note_text: Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.
1 row in set (0.01 sec)

全文字布林操作符

操作符	描述
+	包括，這個詞必須存在
-	排除，這個詞不能存在
>	包括並增加排名值
<	包括並降低排名值
()	將詞分組成子表示式(允許將其包括，排除，排序等作為一個組)
~	取消一個詞的排名值
*	在結尾的萬用字元
""	定義一個短語(與單個單詞列表相反，整個短語匹配包含或排除)

下面是一些列子

-- 搜尋匹配包含詞 rabbit和bait的行
select note_text from productnotes where Match(note_text) Against('+rabbit +bait' in boolean mod
e) \G;

-- 沒有指定操作符，這個搜尋匹配包含詞 rabbit和bait 中的至少一個詞 
select note_text from productnotes where Match(note_text) Against('rabbit bait' in boolean mode) \G;

-- 匹配短語rabbit bait
select note_text from productnotes where Match(note_text) Against('"rabbit bait"' in boolean mode) \G;

-- 增加rabbit的等級，降低bait的等級
select note_text from productnotes where Match(note_text) Against('>rabbit <bait"' in boolean mode) \G;

-- 匹配詞safe和combination 降低combination的等級
select note_text from productnotes where Match(note_text) Against('+safe +(<combination)"' in boolean mode) \G;

15.0插入資料

15.1插入完整的行

使用關鍵字 low_priority 降低insert語句降低優先順序，提升查詢效能

-- 不指定具體欄位,預設把欄位全部插一遍
insert low_priority into 表名 values(值1，值2, ..., 值n);

-- 一次插入一條資料, 推薦使用
insert low_priority into 表名(列1,列2) values(值1, 值2);

-- 一次插入多條資料， 推薦使用
insert low_priority into 表名(列1,列2) values(值1, 值2),(值3, 值4),(值5, 值6);

-- 可以具體指定某個欄位進行插入
insert low_priority into 表名(列名) values(值);

15.2插入檢索出的資料

INSERT INTO customers ( cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country ) SELECT
cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country 
FROM
	custnew;

16.0更新和刪除資料

更新

-- 不加條件有風險,一改全改,一定加where
update 表名 set 列名=值 where 條件
update 表名 set 列名1=值, 列名2=值 where 條件

刪除

-- 刪除的時候,必須加上where
delete from 表名 where 列名 = 值;

-- 刪除所有資料,一刪全刪,一定加where
delete from 表名;

-- 刪除所有 (資料+重置id)
truncate table 表名;

17.0建立表和操作表

17.1建立表

建立表的約束

關鍵字	說明
unsigned	無符號數
not null	不為空
default	預設值
unique	唯一值,加入唯一索引(索引相當於字典目錄,索引的提出是為了加快速度,一味地亂加索引不會提高查詢效率)
primary key	主鍵
auto_increment	自增加一
zerofill	零填充
foreign key	外來鍵

常見mysql資料型別
整數型別

型別名稱	說明	儲存需求
TINYINT	-128〜127	0 〜255(1個位元組)
SMALLINT	-32768〜32767	0〜65535(2個位元組)
MEDIUMINT	-8388608〜8388607	0〜16777215（三個位元組）
INT (INTEGER)	-2147483648〜2147483647	0〜4294967295（四個位元組）
BIGINT	-9223372036854775808〜9223372036854775807	0〜18446744073709551615（八個位元組）

浮點數型別

型別名稱	說明	儲存需求
FLOAT	單精度浮點數	4 個位元組
DOUBLE	雙精度浮點數	8 個位元組
DECIMAL (M, D)，DEC	壓縮的“嚴格”定點數	M+2 個位元組

日期和時間型別

型別名稱	日期格式	日期範圍	儲存需求
YEAR	YYYY	1901 ~ 2155	1 個位元組
TIME	HH:MM:SS	-838:59:59 ~ 838:59:59	3 個位元組
DATE	YYYY-MM-DD	1000-01-01 ~ 9999-12-3	3 個位元組
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	8 個位元組
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC	4 個位元組

字串型別

型別名稱	說明	儲存需求
CHAR(M)	固定長度非二進位制字串	M 位元組，1<=M<=255
VARCHAR(M)	變長非二進位制字串	L+1位元組，在此，L< = M和 1<=M<=255
TINYTEXT	非常小的非二進位制字串	L+1位元組，在此，L<2^8
TEXT	小的非二進位制字串	L+2位元組，在此，L<2^16
MEDIUMTEXT	中等大小的非二進位制字串	L+3位元組，在此，L<2^24
LONGTEXT	大的非二進位制字串	L+4位元組，在此，L<2^32
ENUM	列舉型別，只能有一個列舉字串值	1或2個位元組，取決於列舉值的數目 (最大值為65535)
SET	一個設定，字串物件可以有零個或多個SET成員	1、2、3、4或8個位元組，取決於集合成員的數量（最多64個成員）

二進位制型別

型別名稱	說明	儲存需求
BIT(M)	位欄位型別	大約 (M+7)/8 位元組
BINARY(M)	固定長度二進位制字串	M 位元組
VARBINARY (M)	可變長度二進位制字串	M+1 位元組
TINYBLOB (M)	非常小的BLOB	L+1 位元組，在此，L<2^8
BLOB (M)	小 BLOB	L+2 位元組，在此，L<2^16
MEDIUMBLOB (M)	中等大小的BLOB	L+3 位元組，在此，L<2^24
LONGBLOB (M)	非常大的BLOB	L+4 位元組，在此，L<2^32

CREATE TABLE `customers` (
  `cust_id` int(11) NOT NULL AUTO_INCREMENT,
  `cust_name` char(50) NOT NULL,
  `cust_address` char(50) DEFAULT NULL,
  `cust_city` char(50) DEFAULT NULL,
  `cust_state` char(5) DEFAULT NULL,
  `cust_zip` char(10) DEFAULT NULL,
  `cust_country` char(50) DEFAULT NULL,
  `cust_contact` char(50) DEFAULT NULL,
  `cust_email` char(255) DEFAULT NULL,
  PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10011 DEFAULT CHARSET=utf8

17.2引擎型別

表級鎖 :  只要有一個執行緒執行修改表中的相關操作,就會上鎖,其他執行緒預設等待;
行級鎖 :  針對於當前表中的這條記錄,這一行進行上鎖,其他資料仍然可以被其他執行緒修改,實現高併發,高可用;
事務處理: 執行sql語句時,必須所有的操作全部成功,才最終提交資料,有一條失敗,直接回滾,恢復到先前狀態
begin     : 開啟事務
commit    : 提交資料
rollback  : 回滾資料

MyISAM: 表級鎖，全文索引
InnoDB: 事務處理,行級鎖,外來鍵
MEMORY: 同MyISAM，唯一不同的是把資料放在記憶體中,臨時快取;
BLACKHOLE: anything you write to it disappears
		   一般用於同步主從資料庫;(放在主資料庫和從資料庫之間的一臺伺服器;)

17.3操縱表

-- modify 只能改變資料型別
alter table 表名 modify 列名 新資料型別;

-- change 改變列名+資料型別
alter table 表名 change 列名 新列名 資料型別;

-- add 新增列
alter table 表名 add 列名 資料型別;

-- drop 刪除欄位
alter table 表名 drop 列名;

-- rename 更改表明
alter table 表名 rename 新表名;

-- 刪表
drop table 表名;

18.0使用檢視

檢視用create view語句來建立

CREATE VIEW productcustomers AS SELECT
cust_name,
cust_contact,
prod_id 
FROM
	customers,
	orders,
	orderitems 
WHERE
	customers.cust_id = orders.cust_id 
	AND orderitems.order_num = orders.order_num;


select cust_name, cust_contact from productcustomers where prod_id = 'TNT2';

使用 show create view 檢視名稱; 來檢視建立檢視的語句

show create view productcustomers\G;

用drop view 檢視名稱; 來刪除檢視

drop view productcustomers\G;

更新可以先刪除檢視再建立,也可以直接使用create or replace view 更新檢視

CREATE OR REPLACE VIEW productcustomers AS SELECT cust_name, prod_id FROM customers, orders, orderitems WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num;

18.1用檢視重新格式化檢索出的資料

CREATE VIEW vendorlocations AS SELECT CONCAT( RTRIM( vend_name ), '(', RTRIM( vend_country ), ')' ) AS vend_title FROM vendors ORDER BY vend_name; SELECT * from vendorlocations;

18.1用檢視過濾不需要的資料

CREATE VIEW customeremail AS SELECT cust_id, cust_name, cust_email FROM customers WHERE cust_email IS NOT NULL; SELECT * FROM customeremail;

18.1用檢視計算欄位

CREATE VIEW orderitemsexpanded AS SELECT order_num, prod_id, quantity, item_price, quantity * item_price AS expanded_pricce FROM orderitems; SELECT * FROM orderitemsexpanded;

19.0使用儲存過程

20.0使用遊標

21.0使用觸發器

22.0管理事物處理

23.0安全管理

24.0資料庫維護

25.0優化效能

入門入門入門 MySQL命名行
2020-12-15
MySql
MySQL學習筆記---入門使用
2020-10-24
MySql筆記
MySQL入門
2024-04-13
MySql
MySQL入門--MySQL安全
2019-06-11
MySql
MySQL入門--mysql命令
2019-05-27
MySql
Mysql入門【Mysql約束】
2020-11-20
MySql
MySQL入門系列：MySQL概述
2019-03-06
MySql
SQL快速入門 ( MySQL快速入門, MySQL參考, MySQL快速回顧 )
2018-09-16
MySql
MySQL 入門指南
2024-03-09
MySql
Mysql入門【JDBC】
2020-11-22
MySqlJDBC
如何入門 MySQL
2020-05-15
MySql
MySQL 快速入門
2020-04-05
MySql
mySql入門-（二）
2019-05-19
MySql
MySQL入門——排序
2019-07-03
MySql排序
MYSQL 入門全套
2021-01-31
MySql
入門MySQL——使用者與許可權
2020-07-03
MySql
MySQL入門--使用者與許可權
2019-05-29
MySql
MySQL 基礎入門
2020-05-05
MySql
MySQL入門--EVENT（事件）
2019-06-03
MySql事件
Mysql 簡單入門
2020-12-25
MySql
[MySQL光速入門]030 使用者和資料安全
2019-04-18
MySql
Android入門教程 | RecyclerView使用入門
2021-11-07
AndroidView
入門MySQL——架構篇
2020-05-29
MySql架構
MySQL 入門（5）：複製
2020-05-18
MySql
[MySQL光速入門]027 索引
2019-04-18
MySql索引
MySQL入門--儲存引擎
2019-06-28
MySql儲存引擎
MySQL入門--表維護
2019-05-29
MySql
MySQL入門系列：檢視
2019-03-18
MySql
Mysql系列一:SQL入門
2018-08-05
MySql
node+express+mysql入門
2018-04-24
ExpressMySql
mysql新手入門隨筆
2018-03-28
MySql
[MySQL光速入門]002 讓MySQL跑起來
2019-04-18
MySql
MySQL入門系列：MySQL資料型別
2019-03-07
MySql資料型別
Spring Boot入門(五)：使用JDBC訪問MySql資料庫
2019-04-25
Spring BootJDBCMySql資料庫
Redis 入門使用
2024-11-10
Redis
valgrind使用入門
2024-08-12
JNA使用入門
2024-08-09
appuploader 入門使用
2023-05-03
APP