Mysql - 使用入門

本文是學習 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', '[email protected]');
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', '[email protected]');
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', '[email protected]');
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 字句運算子號

基本使用方法

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需要使用 '\\' 為前導的匹配方式，如匹配 '.' 則運算式為 '\\.'

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

6.5 匹配字符類

字符類

6.5 匹配多個示例

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 定位符

定位元字符

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 常見文理處理函式

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

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 常見數值處理函式

9.0 聚合or集合函式

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

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 子句可以包含任意數目的列
• 2. 如果在group by子句中嵌套了分組,資料將在最后規定的分組上進行分組
• 3. group by 子句中列出的每個列都必須是檢索列或有效的運算式(但不能是聚合函式)
     可以這樣想即 select 列1 ... group by 列1 注意不能 group by 別名
• 4. 如果列中含有 NULL 值，則分組單獨顯示出來，有多個Null，它們將分為一組
• 5. 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的所有客戶

1. 檢索包含物品TNT2的所有訂單的編號
2. 檢索具有前一步驟列出的訂單編號的所有客戶ID
3. 檢索前一步驟回傳的所有客戶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表中

1. 從customers表中檢索客戶串列
2. 對于檢索出來的每個客戶，統計其在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 規則

1. union 必須由兩潭訓兩條以上的select陳述句組成
2. union 中的每個查詢必須包含相同的列、運算式或聚集函式（順序可以不一致）
3. 列資料型別必須兼容，及可以隱含轉換的型別
4. 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創建表

創建表的約束

常見mysql資料型別
整數型別

浮點數型別

日期和時間型別

字串型別

二進制型別

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 productcustomers\G;

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;

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

SELECT * from vendorlocations;

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


SELECT	* FROM	customeremail;

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

SELECT	* FROM	orderitemsexpanded;

標籤：其他

上一篇：樹形結構的選單表設計與查詢

下一篇：.NET 5/.NET Core使用EF Core 5連接MySQL資料庫寫入/讀取資料示例教程