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\', \'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 字句操作符号

基本使用方法

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 的使用说明

• group by 子句可以包含任意数目的列
• 如果在group by子句中嵌套了分组,数据将在最后规定的分组上进行分组
• group by 子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚合函数)
  可以这样想即 select 列1 ... group by 列1 注意不能 group by 别名
• 如果列中含有 NULL 值，则分组单独显示出来，有多个Null，它们将分为一组
• 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）
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表中

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 规则

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;

来源：https://www.cnblogs.com/EdenWu/p/14567600.html
图文来源于网络，如有侵权请联系删除。