Mysql - 使用入门

时间:2021-04-28 13:05:00 来源:互联网 作者: 神秘的大神 字体:

本文是学习 Mysql必知必会 后的笔记
学习之前需要创建一个数据库,然后导入下面两个mysql脚本

create database db1 charset utf8;
########################################
# MySQL Crash Course
# Example table creation scripts
########################################


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

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


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

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

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

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


#####################
# Define foreign keys
#####################
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);
########################################
# MySQL Crash Course
# Example table population scripts
########################################


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


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


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



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


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

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

1.0 初入Mysql

1.1 Mysql的连接

mysql -u 用户名 -p -h 主机名 -P 端口号

-u 指名是谁来连接数据库
-p 连接数据库用户的密码
-h ip地址或主机名(也就是dns能解析到ip地址的网址)
-P 端口号,默认Mysql为 3306

1.2 成功连接数据后

当然了,连接上了,会显示一大串的英语什么的,这里就说到几个会用上的把

  • 命令用 ';' or '\g' 结束,换句话说,仅按下Enter后不执行任何命令
  • 输入 'help' or '\h' 获取帮助, 例如: '\h select' or 'help select' 注意这里结尾不需要加 ';'
  • 输入 'quit' or 'exit' or '\q' 退出连接
1.2.1 Mysql了解数据库与表

查看当前用户或指定用户的权限

show grants; --当前用户
show grants for 用户名; --特定用户

mysql> show grants;
+-------------------------------------------------------------+
| Grants for root@%                                           |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+
ALL PRIVILEGES : 表示那些权限,这里是所有权限
*.* : 中第一个*是哪个数据库,如果为*指所有数据库,第二个*指选中库的哪个表,为*指所有表
root : 指用户名
@后面的 % : 指授权地址,%指所有地址,localhost指本地,也可以为127.0.0.1
WITH GRANT OPTION : 这个选项表示该用户可以将自己拥有的权限授权给别人

查看当前有那些数据库

show databases;

查看用于创建数据库的命令

show create database 数据库名字;

查看服务器状态信息

show status \G;
--\G: 垂直显示, 用于显示得更加清洗,可不加

查看服务器错误或警告

show errors\G;
show warnings \G;

使用数据库

use 数据库名字;

查看数据库内的表

show tables;

查看创建这个表的语句

show create table 表名\G;

查看表结构

show columns from 表名;
desc 表名;

2.0 简单的 select 语句

查看单个列的数据

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

查看多个列的数据

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

查看表内所有数据

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

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

2.1 去重 distinct

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

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

2.2 限制显示结果 limit

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

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

-- 这里还有另外一种写法
select * from user limit 2 offset 0 \G;

-- 即limit y offset x

2.3 完全限制表库

这样做的目的是限制唯一的列名

select 表名.列名 from 数据库名.表名;
select user.User from mysql.user;

3.0 数据的排序

排序的数据在很多场合可能都会用上,可以下面下面的方式进行检索排序

select 列名 from 表名 order by 列名;
select prod_name from products order by prod_name;
-- order by 同时也可以按照非检索的列名进行排序,通常不这样做

3.1 多个列排序

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

3.2 指定排序方向 desc(降序) asc(默认升序)

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

3.3 order by 于 limit的组合

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

4.0 过滤行数据(where子句)

where 字句操作符号

操作符 说明
> 大于
!=(<>) 不等于
= 等于
< 小于
>= 大于等于
<= 小于等于
between 在两者之间
and 两种条件都要满足
or 只要满足其中的一种条件
in 指定检索的范围
not 否定后面跟的条件

基本使用方法

select 列1, 列2 from 表名 where 列数据 = 2.5;
select prod_name, prod_price from products where prod_price = 2.5;

4.1 检索值范围 between x and y 即(x <= value <= y)

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

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

4.2 空值检索

-- 应为空值具有特殊含义, 所以这里用的是 is

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

4.3 or操作符

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

4.4 or与and的组合

-- 这里需要的注意的是在进行组合运算的时候添加个 () 不会有错的
-- 如果不添加 () ,下面的例子就会出现很奇怪的事
select prod_name, prod_price from products where  prod_price >=10 and (vend_id = 1002 or vend_id = 1003);

4.5 in操作符

上面的句子其实是可以这么化简的

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

4.6 not操作符

对上面的 vend_in 筛选的结果取反

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

5.0 通配符的使用

通配符

  • % 表示任何字符出现任何次
  • _ 表示任何字符出现一次

5.1 like操作符

select 列名 from 表名 where 列名 like 带有通配符的值;

-- 如果我只记得prod_name的值有je开头的,我应该咋匹配呢?
select prod_id, prod_name from products where prod_name like 'je%';

或者我想搜索一个文本中包含什么的

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

......

5.2 通配符的使用技巧

通配符确实很好用,但是这个开销会比前面的检索方式慢太多

  • 通过其他方式能检索到的,就完全没比较使用通配符了
  • 在确实需要使用的时候,最好不用把通配符置于搜索模式的开始处,这样搜索是最慢的

6.0 正则表达式进行检索(尽量少用)

select 列名 from 表名 where 列表 regexp 正则表达式;

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

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

由上面可以发现,like 是匹配整个列的,当列数据不一致,即不返回数据,
而 regexp 是在列值内进行匹配,如果被匹配上了,当然就返回数据了

6.1 正则表达式进行or匹配 ' | '

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

6.2 匹配几个字符之一 ' [] '

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

6.3 匹配范围 ' [a-g] ' ... '[0-4]'

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

6.4 匹配特殊字符

在mysql需要使用 '\\' 为前导的匹配方式,如匹配 '.' 则表达式为 '\\.'

元字符 说明
\\f 换页
\\n 换行
\\r 回车
\\t 制表
\\v 纵向制表
-- 匹配 vend_name 列中含有小数点记录
select vend_name from vendors where vend_name regexp '\\.';

6.5 匹配字符类

字符类

说明
[:alnum:] 匹配字符和数字(同 [a-zA-Z0-9])
[:alpha:] 任意字符(同[a-zA-Z])
[:blank:] 空格和制表(同[\\t])
[:cntrl:] ASCII控制字符(ASCII 0到31和127)
[:digit:] 任意数字(同[0-9])
[:graph:] 与[:print:]相同但不包括空格
[:lower:] 任意小写字母(同[a-z])
[:print:] 任意可打印字符
[:punct:] 既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:] 包括空格在内的任意空白字符(同[\\f\\n\\r\\t\v])
[:upper:] 任意大写字母(同[A-Z])
[:xdigit:] 任意十六进制数字(同[a-fA-F0-9])

6.5 匹配多个示例

元字符 说明
* 0个或多个匹配
+ 1个或多个匹配(等于 {1,})
? 0个或1个匹配(等于 {0,1})
{n} n 是一个非负整数。匹配确定的 n 次
{n,m} 最少匹配 n 次且最多匹配 m 次 (m<=255)
select prod_name from products where prod_name regexp '\\([0-9] sticks?\\)';

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

6.6 定位符

定位元字符

元字符 说明
^ 文本的开始( ^在[]内表示非 例如 [^a-z] 即非 a-z )
$ 文本的结尾
[[:<:]] 词的开始
[[:>:]] 词的结尾
select prod_name from products where prod_name regexp '^[0-9\\.]';

7.0 创建计算字段

7.1 concat() 拼接字段

使用 concat() 函数用于把多个列拼接起来

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

7.2 as 使用别名

上面输出的表抬头是不是感觉好丑,那么就可以使用 as

select concat(列1, 列2, ..., 列n) as 别名 from 表名;
select concat(vend_name, '(', vend_country, ')')  as vend_tittle from vendors;
7.2.1 trim() 删除左右两边的空格 rtrim(),ltrim()
select concat(trim(vend_name), '(', trim(vend_country), ')')  as vend_tittle from vendors;

7.3 算式计算

SELECT
	prod_id,
	quantity,
	item_price,
	quantity * item_price AS expanded_price 
FROM
	orderitems 
WHERE
	order_num = 20005;
操作符 说明
+
-
*
/

函数的测试

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

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

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

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

8.0 函数

8.1 常见文理处理函数

函数 说明
length(s) 返回串s的长度
trim(s) 去掉字符串 s 开始和结尾处的空格
upper(s) 将字符串转换为大写
left(s,n) 返回字符串 s 的前 n 个字符
lower(s) 将字符串 s 的所有字母变成小写字母
locate(s1,s) 从字符串 s 中获取 s1 的开始位置
right(s,n) 返回字符串 s 的后 n 个字符
ltrim(s) 去掉字符串 s 开始处的空格
rtrim(s) 去掉字符串 s 结尾处的空格
substring(s, start, length) 从字符串 s 的 start 位置截取长度为 length 的子字符串

8.1 常见日期和时间处理函数

函数名 描述
addDate(d,n) 计算起始日期 d 加上 n 天的日期
addTime(t,n) n 是一个时间表达式,时间 t 加上时间表达式 n
curDate() 返回当前日期
curTime() 返回当前时间
date() 从日期或日期时间表达式中提取日期值
CURRENT_TIME 返回当前时间
CURRENT_DATE() 返回当前日期
CURRENT_TIMESTAMP() 返回当前日期和时间
dateDiff(d1,d2) 计算日期 d1->d2 之间相隔的天数
date_add(d,INTERVAL expr type) 计算起始日期 d 加上一个时间段后的日期
date_format(d,f) 按表达式 f的要求显示日期 d
day(d) 返回日期值 d 的日期部分
dayOfMonth(d) 计算日期 d 是本月的第几天
dayOfWeek(d) 日期 d 今天是星期几,1 星期日,2 星期一,以此类推
dayOfYear(d) 计算日期 d 是本年的第几天
hour(t) 返回 t 中的小时值
minute(t) 返回 t 中的分钟值
month(d) 返回日期d中的月份值,1 到 12
now() 返回当前日期和时间
time(expression) 提取传入表达式的时间部分
year(d) 返回年份
select cust_id, order_num from orders where order_date = '2005-09-01';

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

select cust_id, order_num from orders where year(order_date) = 2005 and month(order_date)=9;
SELECT
	cust_id,
	order_num 
FROM
	orders 
WHERE
	date( order_date ) BETWEEN '2005-09-01' 
	AND '2005-09-30';

8.2 常见数值处理函数

函数名 描述
ABS(x) 返回 x 的绝对值
COS(x) 求余弦值(参数是弧度)
EXP(x) 返回 e 的 x 次方
MOD(x,y) 返回 x 除以 y 以后的余数
PI() 返回圆周率(3.141593)
ROUND(x) 返回离 x 最近的整数
SIN(x) 求正弦值(参数是弧度)
SQRT(x) 返回x的平方根
TAN(x) 求正切值(参数是弧度)

9.0 聚合or集合函数

聚集函数(aggregate function)运行在行组上,计算和返回单个值的函数

聚集函数 说明
AVG([distinct] expr) 求平均值
COUNT({* [distinct] } expr)
MAX([distinct] expr) 求最大值
MIN([distinct] expr) 求最小值
SUM([distinct] expr) 求累加和
select avg(列名) from 表名;
-- ......

-- avg() 仅仅用于单列,多列着多个 avg()函数
-- 忽略列值为 NULL 的行
select avg(prod_price) as avg_price from products;

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

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

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

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

9.1 聚合or集合不同的值,默认为all

嵌套的 distinc

select avg(distinct prod_price) as avg_price from products;

10.0 分组

10.1 创建分组 group by

select 列名 from 表名 group by 列名;

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

关于 group by 的使用说明

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

使用 with rollup 关键字,可以得到每个分组以及每个分组汇总级别的值

-- 返回单独的vend_id供货总数
select count(vend_id)  from products where vend_id = '1001';
+----------------+
| count(vend_id) |
+----------------+
|              3 |
+----------------+
1 row in set (0.00 sec)


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

10.1 过滤分组 having

  • 注意 where 为过滤行,但是 having 支持 where 的所有操作
select cust_id, count(*) as orders from orders group by cust_id having count(*) >= 2;
SELECT
	vend_id,
	COUNT(*) AS num_prods 
FROM
	products 
WHERE
	prod_price >= 10 
GROUP BY
	vend_id 
HAVING
	COUNT(*) >= 2;

10.2 分组和排序

检索总订单价格大于50的订单号和总计订单价格在按照总计订单价格排序输出

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

11.0 使用子查询

子查询:即嵌套在其它查询中的查询

现在需要列出订购物品TNT2的所有客户

  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创建表

创建表的约束

关键字 说明
unsigned 无符号数
not null 不为空
default 默认值
unique 唯一值,加入唯一索引(索引相当于字典目录,索引的提出是为了加快速度,一味地乱加索引不会提高查询效率)
primary key 主键
auto_increment 自增加一
zerofill 零填充
foreign key 外键

常见mysql数据类型
整数类型

类型名称 说明 存储需求
TINYINT -128〜127 0 〜255(1个字节)
SMALLINT -32768〜32767 0〜65535(2个字节)
MEDIUMINT -8388608〜8388607 0〜16777215(三个字节)
INT (INTEGER) -2147483648〜2147483647 0〜4294967295(四个字节)
BIGINT -9223372036854775808〜9223372036854775807 0〜18446744073709551615(八个字节)

浮点数类型

类型名称 说明 存储需求
FLOAT 单精度浮点数 4 个字节
DOUBLE 双精度浮点数 8 个字节
DECIMAL (M, D),DEC 压缩的“严格”定点数 M+2 个字节

日期和时间类型

类型名称 日期格式 日期范围 存储需求
YEAR YYYY 1901 ~ 2155 1 个字节
TIME HH:MM:SS -838:59:59 ~ 838:59:59 3 个字节
DATE YYYY-MM-DD 1000-01-01 ~ 9999-12-3 3 个字节
DATETIME YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 8 个字节
TIMESTAMP YYYY-MM-DD HH:MM:SS 1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC 4 个字节

字符串类型

类型名称 说明 存储需求
CHAR(M) 固定长度非二进制字符串 M 字节,1<=M<=255
VARCHAR(M) 变长非二进制字符串 L+1字节,在此,L< = M和 1<=M<=255
TINYTEXT 非常小的非二进制字符串 L+1字节,在此,L<2^8
TEXT 小的非二进制字符串 L+2字节,在此,L<2^16
MEDIUMTEXT 中等大小的非二进制字符串 L+3字节,在此,L<2^24
LONGTEXT 大的非二进制字符串 L+4字节,在此,L<2^32
ENUM 枚举类型,只能有一个枚举字符串值 1或2个字节,取决于枚举值的数目 (最大值为65535)
SET 一个设置,字符串对象可以有零个或 多个SET成员 1、2、3、4或8个字节,取决于集合 成员的数量(最多64个成员)

二进制类型

类型名称 说明 存储需求
BIT(M) 位字段类型 大约 (M+7)/8 字节
BINARY(M) 固定长度二进制字符串 M 字节
VARBINARY (M) 可变长度二进制字符串 M+1 字节
TINYBLOB (M) 非常小的BLOB L+1 字节,在此,L<2^8
BLOB (M) 小 BLOB L+2 字节,在此,L<2^16
MEDIUMBLOB (M) 中等大小的BLOB L+3 字节,在此,L<2^24
LONGBLOB (M) 非常大的BLOB L+4 字节,在此,L<2^32
CREATE TABLE `customers` (
  `cust_id` int(11) NOT NULL AUTO_INCREMENT,
  `cust_name` char(50) NOT NULL,
  `cust_address` char(50) DEFAULT NULL,
  `cust_city` char(50) DEFAULT NULL,
  `cust_state` char(5) DEFAULT NULL,
  `cust_zip` char(10) DEFAULT NULL,
  `cust_country` char(50) DEFAULT NULL,
  `cust_contact` char(50) DEFAULT NULL,
  `cust_email` char(255) DEFAULT NULL,
  PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10011 DEFAULT CHARSET=utf8

17.2引擎类型

表级锁 :  只要有一个线程执行修改表中的相关操作,就会上锁,其他线程默认等待;
行级锁 :  针对于当前表中的这条记录,这一行进行上锁,其他数据仍然可以被其他线程修改,实现高并发,高可用;
事务处理: 执行sql语句时,必须所有的操作全部成功,才最终提交数据,有一条失败,直接回滚,恢复到先前状态
begin     : 开启事务
commit    : 提交数据
rollback  : 回滚数据

MyISAM: 表级锁,全文索引
InnoDB: 事务处理,行级锁,外键
MEMORY: 同MyISAM,唯一不同的是把数据放在内存中,临时缓存;
BLACKHOLE: anything you write to it disappears
		   一般用于同步主从数据库;(放在主数据库和从数据库之间的一台服务器;)

17.3操纵表

-- modify 只能改变数据类型
alter table 表名 modify 列名 新数据类型;

-- change 改变列名+数据类型
alter table 表名 change 列名 新列名 数据类型;

-- add 添加列
alter table 表名 add 列名 数据类型;

-- drop 删除字段
alter table 表名 drop 列名;

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

-- 删表
drop table 表名;

18.0使用视图

视图用create view语句来创建

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


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

使用 show create view 视图名称; 来查看创建视图的语句

show create view productcustomers\G;

用drop view 视图名称; 来删除视图

drop view productcustomers\G;

更新可以先删除视图再创建,也可以直接使用create or replace view 更新视图

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

18.1用视图重新格式化检索出的数据

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

SELECT * from vendorlocations;

18.1用视图过滤不需要的数据

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


SELECT	* FROM	customeremail;

18.1用视图计算字段

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

SELECT	* FROM	orderitemsexpanded;

19.0使用存储过程

20.0使用游标

21.0使用触发器

22.0管理事物处理

23.0安全管理

24.0数据库维护

25.0优化性能