mysql使用入门教程_cad自学入门

本文是学习 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 errorsG;
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 userG;

注意：除非你确实是需要表中的所有数据，否则最好不要使用 * 通配符, 使用 * 会检索出所有数据,这会降低检索的性能。当然了，使用 * 通配符也可以检索出来未知的列

2.1 去重 distinct

当然了去掉重复的数据可以使用下面的命令

-- distinct不能多列使用，除非多列检测出来的一行或者多行的组合有重复，不然就都会显示出来
select distinct 列名 from 表名;
select distinct Host from user;

2.2 限制显示结果 limit

select 列名 from 表名 limit x,yG;
x : 开始的位置
y : 开始位置后的行数

-- 这里x默认为0，即开始的 第0行 到即到它后面的 2行
select * from user limit 2G;

-- 这里还有另外一种写法
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 productcustomersG;

drop view productcustomersG;

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;