熟知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 字句操作符号
操作符 | 说明 |
---|---|
> | 大于 |
!=(<>) | 不等于 |
= | 等于 |
< | 小于 |
>= | 大于等于 |
<= | 小于等于 |
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 次且最多匹配 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 的使用说明
-
- group by 子句可以包含任意数目的列
-
- 如果在group by子句中嵌套了分组,数据将在最后规定的分组上进行分组
-
- group by 子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚合函数)
可以这样想即 select 列1 ... group by 列1 注意不能 group by 别名
- 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的所有客户
- 检索包含物品TNT2的所有订单的编号
- 检索具有前一步骤列出的订单编号的所有客户ID
- 检索前一步骤返回的所有客户ID的客户信息
-- 1)
select order_num from orderitems where prod_id = 'TNT2';
+-----------+
| order_num |
+-----------+
| 20005 |
| 20007 |
+-----------+
2 rows in set (0.00 sec)
-- 2)
select cust_id from orders where order_num in (20005, 20007);
+---------+
| cust_id |
+---------+
| 10001 |
| 10004 |
+---------+
2 rows in set (0.00 sec)
-- 3)
select cust_name, cust_contact from customers where cust_id in (10001, 10004);
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
+----------------+--------------+
2 rows in set (0.01 sec)
现在呢,我们就可以把这三个查询合并为一个查询
SELECT
cust_name,
cust_contact
FROM
customers
WHERE
cust_id IN (
SELECT
cust_id
FROM
orders
WHERE
order_num IN ( SELECT order_num FROM orderitems WHERE prod_id = 'TNT2' ));
像上面的语句可能不是最有效率的方式,可以在后面参考(联结表)
11.1 作为计算字段使用子查询
假如需要显示customers表中每个客户的总订单数。 订单与相应的客户ID存储在orders表中
- 从customers表中检索客户列表
- 对于检索出来的每个客户,统计其在orders表中的订单数目
SELECT
cust_name,
cust_state,
( SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id ) AS orders
FROM
customers
ORDER BY
cust_name;
12.0 联结表
Mysql联结的表越多会导致性能的下降
12.1 创建联结表
-- 等值联结
SELECT
vend_name,
prod_name,
prod_price
FROM
vendors,
products
WHERE
vendors.vend_id = products.vend_id
ORDER BY
vend_name,
prod_name;
如果上面的句子删除掉 where 就会出现 笛卡尔积,这并非是我们需要的数据
-- 笛卡尔积
SELECT
vend_name,
prod_name,
prod_price
FROM
vendors,
products
ORDER BY
vend_name,
prod_name;
12.2 内部联结
此联结与上面的等值接连一致,唯一不同的是这里使用关键子 inner join ... on 进行限定
SELECT
vend_name,
prod_name,
prod_price
FROM
vendors
INNER JOIN products ON vendors.vend_id = products.vend_id;
12.3 联结多个表
SELECT
vend_name,
prod_name,
quantity,
prod_price
FROM
vendors,
products,
orderitems
WHERE
vendors.vend_id = products.vend_id
AND orderitems.prod_id = products.prod_id
AND order_num = 20005;
11.0 中出现的问题 现在需要列出订购物品TNT2的所有客户 即可以通过下面的方式处理
SELECT
cust_name,
cust_contact
FROM
customers
WHERE
cust_id IN (
SELECT
cust_id
FROM
orders
WHERE
order_num IN ( SELECT order_num FROM orderitems WHERE prod_id = 'TNT2' ));
SELECT
cust_name,
cust_contact
FROM
orderitems,
orders,
customers
WHERE
customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num
AND prod_id = 'TNT2';
12.4 使用别名
这条语句和上面的查询结果一致,但使用了 别名
SELECT
cust_name,
cust_contact
FROM
orderitems AS oi,
orders AS o,
customers AS c
WHERE
c.cust_id = o.cust_id
AND oi.order_num = o.order_num
AND prod_id = 'TNT2';
12.5 自联结
自联结: 同一张表的联结
SELECT
p1.prod_id, p1.prod_name
FROM
products AS p1,
products AS p2
WHERE
p1.vend_id = p2.vend_id
AND p2.prod_id = 'DTNTR';
12.6 外联结
许多联结将一个表中的行与另外一个表中的行进行关联。但有时会需要包含没有关联的那些行,这就用到了外联结
LEFT OUTER JOIN : OUTER JOIN左边的表
RIGHT OUTER JOIN : OUTER JOIN右边的表
SELECT
customers.cust_id, orders.order_num
FROM
customers
LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;
12.7 使用带聚合函数的联结
检索所有客户及客户所下的订单数
SELECT
customers.cust_name,
customers.cust_id,
COUNT( orders.order_num ) AS num_ord
FROM
customers
INNER JOIN orders ON customers.cust_id = orders.cust_id
GROUP BY
customers.cust_id;
检索所有客户及客户所下的订单数,使用左外联结来包含所有客户,甚至包含那些没有下订单的客户
SELECT
customers.cust_name,
customers.cust_id,
COUNT( orders.order_num ) AS num_ord
FROM
customers
LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id
GROUP BY
customers.cust_id;
13.0 组合查询
13.1 创建组合查询 union
SELECT
vend_id,
prod_id,
prod_price
FROM
products
WHERE
prod_price > 5 UNION
SELECT
vend_id,
prod_id,
prod_price
FROM
products
WHERE
vend_id IN ( 1001, 1002 );
union 规则
- union 必须由两条或两条以上的select语句组成
- union 中的每个查询必须包含相同的列、表达式或聚集函数(顺序可以不一致)
- 列数据类型必须兼容,及可以隐含转换的类型
- union 可用于组合不同的表
13.2 包含或取消重复的行 union all
13.1 的例子中,如果两条语句单独查询共计显示9条数据,而使用union却只显示了8行,
这是因为union默认会去除掉重复的行,不然不祥去重,可以使用 union all
SELECT
vend_id,
prod_id,
prod_price
FROM
products
WHERE
prod_price > 5 UNION ALL
SELECT
vend_id,
prod_id,
prod_price
FROM
products
WHERE
vend_id IN ( 1001, 1002 );
13.3 对组合查询结果排序
order by只能放在最后的select查询上
SELECT
vend_id,
prod_id,
prod_price
FROM
products
WHERE
prod_price > 5 UNION
SELECT
vend_id,
prod_id,
prod_price
FROM
products
WHERE
vend_id IN ( 1001, 1002 )
ORDER BY
vend_id,
prod_price;
上面的查询看似只对第二条select语句进行排序,实则是对所有的select语句进行排序,
14.0全文本搜索
并不是所有的引擎都支持全文索引。两个最常使用的引擎为MyISAM和InnoDB,前者支持全文本索引,而后者不支持
前面所提到的 like 和 regexp 都能实现这个功能,但是会有如下的的限制
- 性能 -- 通配符和正则表达式匹配通常要求mysql尝试匹配表中所有行(而且这些搜索极少使用表索引)。
因此,由于被搜索行数不断增加,这些搜索可能非常耗时 - 明确控制
- 智能化的结果 --
14.1启用全文本搜索
-- FULLTEXT() 可指定多个列
CREATE TABLE `productnotes` (
`note_id` int(11) NOT NULL AUTO_INCREMENT,
`prod_id` char(10) NOT NULL,
`note_date` datetime NOT NULL,
`note_text` text,
PRIMARY KEY (`note_id`),
FULLTEXT (`note_text`)
) ENGINE=MyISAM;
注意:不要在导入数据时使用 fulltext,可以先导入数据后在修改表
14.2进行全文本搜索
-
Match() 指定被搜索的列
传递给Match()的值必须与fulltext定义中的相同,如果指定多个列,则必须列出它们(且次序正确) -
Against() 指定要使用的搜索表达式 - 不区分大小写
select note_text from productnotes where Match(note_text) Against('rabbit')\G;
*************************** 1. row ***************************
note_text: Customer complaint: rabbit has been able to detect trap, food apparently less effective now.
*************************** 2. row ***************************
note_text: Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.
2 rows in set (0.00 sec)
查看全文搜索的排序如何工作的
select note_text, Match(note_text) Against('rabbit') as rank from productnotes \G;
*************************** 1. row ***************************
note_text: Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.
rank: 0
*************************** 2. row ***************************
note_text: Can shipped full, refills not available.
Need to order new can if refill needed.
rank: 0
*************************** 3. row ***************************
note_text: Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers.
rank: 0
*************************** 4. row ***************************
note_text: Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.
rank: 1.5905543565750122
*************************** 5. row ***************************
note_text: Included fuses are short and have been known to detonate too quickly for some customers.
Longer fuses are available (item FU1) and should be recommended.
rank: 0
*************************** 6. row ***************************
note_text: Matches not included, recommend purchase of matches or detonator (item DTNTR).
rank: 0
*************************** 7. row ***************************
note_text: Please note that no returns will be accepted if safe opened using explosives.
rank: 0
*************************** 8. row ***************************
note_text: Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.
rank: 0
*************************** 9. row ***************************
note_text: Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.
rank: 0
*************************** 10. row ***************************
note_text: Customer complaint: rabbit has been able to detect trap, food apparently less effective now.
rank: 1.6408053636550903
*************************** 11. row ***************************
note_text: Shipped unassembled, requires common tools (including oversized hammer).
rank: 0
*************************** 12. row ***************************
note_text: Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.
rank: 0
*************************** 13. row ***************************
note_text: Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.
rank: 0
*************************** 14. row ***************************
note_text: Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
Comment forwarded to vendor.
rank: 0
14 rows in set (0.00 sec)
14.3使用查询扩展 with query expansion
查询扩展用来放宽所返回的文本搜索结果的范围
在使用查询扩展的时候Mysql对数据和索引进行两边扫描完成搜索
-- 在在上的例子中没有使用查询扩展的时候只能返回两行
-- 现在使用返回了6行
select note_text from productnotes where Match(note_text) Against('rabbit' with query expansion)\G;
*************************** 1. row ***************************
note_text: Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.
*************************** 2. row ***************************
note_text: Customer complaint: rabbit has been able to detect trap, food apparently less effective now.
*************************** 3. row ***************************
note_text: Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.
*************************** 4. row ***************************
note_text: Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.
*************************** 5. row ***************************
note_text: Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.
*************************** 6. row ***************************
note_text: Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.
6 rows in set (0.00 sec)
14.4布尔文本搜索 in boolean mode
即使没有定义fulltext索引,也可以使用它哟
select note_text from productnotes where Match(note_text) Against('rabbit' in boolean mode) \G;
*************************** 1. row ***************************
note_text: Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.
*************************** 2. row ***************************
note_text: Customer complaint: rabbit has been able to detect trap, food apparently less effective now.
2 rows in set (0.00 sec)
-- 匹配包含 heavy 但不包含任意一repo开始的词
select note_text from productnotes where Match(note_text) Against('heavy -rope*' in boo
lean mode) \G;
*************************** 1. row ***************************
note_text: Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.
1 row in set (0.01 sec)
全文本布尔操作符
操作符 | 描述 |
---|---|
+ | 包括,这个词必须存在 |
- | 排除,这个词不能存在 |
> | 包括并增加排名值 |
< | 包括并降低排名值 |
() | 将词分组成子表达式(允许将其包括,排除,排序等作为一个组) |
~ | 取消一个词的排名值 |
* | 在结尾的通配符 |
"" | 定义一个短语(与单个单词列表相反,整个短语匹配包含或排除) |
下面是一些列子
-- 搜索匹配包含词 rabbit和bait的行
select note_text from productnotes where Match(note_text) Against('+rabbit +bait' in boolean mod
e) \G;
-- 没有指定操作符,这个搜索匹配包含词 rabbit和bait 中的至少一个词
select note_text from productnotes where Match(note_text) Against('rabbit bait' in boolean mode) \G;
-- 匹配短语rabbit bait
select note_text from productnotes where Match(note_text) Against('"rabbit bait"' in boolean mode) \G;
-- 增加rabbit的等级,降低bait的等级
select note_text from productnotes where Match(note_text) Against('>rabbit <bait"' in boolean mode) \G;
-- 匹配词safe和combination 降低combination的等级
select note_text from productnotes where Match(note_text) Against('+safe +(<combination)"' in boolean mode) \G;
15.0插入数据
15.1插入完整的行
使用关键字 low_priority 降低insert语句降低优先级,提升查询性能
-- 不指定具体字段,默认把字段全部插一遍
insert low_priority into 表名 values(值1,值2, ..., 值n);
-- 一次插入一条数据, 推荐使用
insert low_priority into 表名(列1,列2) values(值1, 值2);
-- 一次插入多条数据, 推荐使用
insert low_priority into 表名(列1,列2) values(值1, 值2),(值3, 值4),(值5, 值6);
-- 可以具体指定某个字段进行插入
insert low_priority into 表名(列名) values(值);
15.2插入检索出的数据
INSERT INTO customers ( cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country ) SELECT
cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
FROM
custnew;
16.0更新和删除数据
更新
-- 不加条件有风险,一改全改,一定加where
update 表名 set 列名=值 where 条件
update 表名 set 列名1=值, 列名2=值 where 条件
删除
-- 删除的时候,必须加上where
delete from 表名 where 列名 = 值;
-- 删除所有数据,一删全删,一定加where
delete from 表名;
-- 删除所有 (数据+重置id)
truncate table 表名;
17.0创建表和操作表
17.1创建表
创建表的约束
关键字 | 说明 |
---|---|
unsigned | 无符号数 |
not null | 不为空 |
default | 默认值 |
unique | 唯一值,加入唯一索引(索引相当于字典目录,索引的提出是为了加快速度,一味地乱加索引不会提高查询效率) |
primary key | 主键 |
auto_increment | 自增加一 |
zerofill | 零填充 |
foreign key | 外键 |
常见mysql数据类型
整数类型
类型名称 | 说明 | 存储需求 |
---|---|---|
TINYINT | -128〜127 | 0 〜255(1个字节) |
SMALLINT | -32768〜32767 | 0〜65535(2个字节) |
MEDIUMINT | -8388608〜8388607 | 0〜16777215(三个字节) |
INT (INTEGER) | -2147483648〜2147483647 | 0〜4294967295(四个字节) |
BIGINT | -9223372036854775808〜9223372036854775807 | 0〜18446744073709551615(八个字节) |
浮点数类型
类型名称 | 说明 | 存储需求 |
---|---|---|
FLOAT | 单精度浮点数 | 4 个字节 |
DOUBLE | 双精度浮点数 | 8 个字节 |
DECIMAL (M, D),DEC | 压缩的“严格”定点数 | M+2 个字节 |
日期和时间类型
类型名称 | 日期格式 | 日期范围 | 存储需求 |
---|---|---|---|
YEAR | YYYY | 1901 ~ 2155 | 1 个字节 |
TIME | HH:MM:SS | -838:59:59 ~ 838:59:59 | 3 个字节 |
DATE | YYYY-MM-DD | 1000-01-01 ~ 9999-12-3 | 3 个字节 |
DATETIME | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 | 8 个字节 |
TIMESTAMP | YYYY-MM-DD HH:MM:SS | 1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC | 4 个字节 |
字符串类型
类型名称 | 说明 | 存储需求 |
---|---|---|
CHAR(M) | 固定长度非二进制字符串 | M 字节,1<=M<=255 |
VARCHAR(M) | 变长非二进制字符串 | L+1字节,在此,L< = M和 1<=M<=255 |
TINYTEXT | 非常小的非二进制字符串 | L+1字节,在此,L<2^8 |
TEXT | 小的非二进制字符串 | L+2字节,在此,L<2^16 |
MEDIUMTEXT | 中等大小的非二进制字符串 | L+3字节,在此,L<2^24 |
LONGTEXT | 大的非二进制字符串 | L+4字节,在此,L<2^32 |
ENUM | 枚举类型,只能有一个枚举字符串值 | 1或2个字节,取决于枚举值的数目 (最大值为65535) |
SET | 一个设置,字符串对象可以有零个或 多个SET成员 | 1、2、3、4或8个字节,取决于集合 成员的数量(最多64个成员) |
二进制类型
类型名称 | 说明 | 存储需求 |
---|---|---|
BIT(M) | 位字段类型 | 大约 (M+7)/8 字节 |
BINARY(M) | 固定长度二进制字符串 | M 字节 |
VARBINARY (M) | 可变长度二进制字符串 | M+1 字节 |
TINYBLOB (M) | 非常小的BLOB | L+1 字节,在此,L<2^8 |
BLOB (M) | 小 BLOB | L+2 字节,在此,L<2^16 |
MEDIUMBLOB (M) | 中等大小的BLOB | L+3 字节,在此,L<2^24 |
LONGBLOB (M) | 非常大的BLOB | L+4 字节,在此,L<2^32 |
CREATE TABLE `customers` (
`cust_id` int(11) NOT NULL AUTO_INCREMENT,
`cust_name` char(50) NOT NULL,
`cust_address` char(50) DEFAULT NULL,
`cust_city` char(50) DEFAULT NULL,
`cust_state` char(5) DEFAULT NULL,
`cust_zip` char(10) DEFAULT NULL,
`cust_country` char(50) DEFAULT NULL,
`cust_contact` char(50) DEFAULT NULL,
`cust_email` char(255) DEFAULT NULL,
PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10011 DEFAULT CHARSET=utf8
17.2引擎类型
表级锁 : 只要有一个线程执行修改表中的相关操作,就会上锁,其他线程默认等待;
行级锁 : 针对于当前表中的这条记录,这一行进行上锁,其他数据仍然可以被其他线程修改,实现高并发,高可用;
事务处理: 执行sql语句时,必须所有的操作全部成功,才最终提交数据,有一条失败,直接回滚,恢复到先前状态
begin : 开启事务
commit : 提交数据
rollback : 回滚数据
MyISAM: 表级锁,全文索引
InnoDB: 事务处理,行级锁,外键
MEMORY: 同MyISAM,唯一不同的是把数据放在内存中,临时缓存;
BLACKHOLE: anything you write to it disappears
一般用于同步主从数据库;(放在主数据库和从数据库之间的一台服务器;)
17.3操纵表
-- modify 只能改变数据类型
alter table 表名 modify 列名 新数据类型;
-- change 改变列名+数据类型
alter table 表名 change 列名 新列名 数据类型;
-- add 添加列
alter table 表名 add 列名 数据类型;
-- drop 删除字段
alter table 表名 drop 列名;
-- rename 更改表明
alter table 表名 rename 新表名;
-- 删表
drop table 表名;
18.0使用视图
视图用create view语句来创建
CREATE VIEW productcustomers AS SELECT
cust_name,
cust_contact,
prod_id
FROM
customers,
orders,
orderitems
WHERE
customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num;
select cust_name, cust_contact from productcustomers where prod_id = 'TNT2';
使用 show create view 视图名称; 来查看创建视图的语句
show create view productcustomers\G;
用drop view 视图名称; 来删除视图
drop view productcustomers\G;
更新可以先删除视图再创建,也可以直接使用create or replace view 更新视图
CREATE OR REPLACE VIEW productcustomers AS SELECT
cust_name,
prod_id
FROM
customers,
orders,
orderitems
WHERE
customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num;
18.1用视图重新格式化检索出的数据
CREATE VIEW vendorlocations AS SELECT
CONCAT( RTRIM( vend_name ), '(', RTRIM( vend_country ), ')' ) AS vend_title
FROM
vendors
ORDER BY
vend_name;
SELECT * from vendorlocations;
18.1用视图过滤不需要的数据
CREATE VIEW customeremail AS SELECT
cust_id,
cust_name,
cust_email
FROM
customers
WHERE
cust_email IS NOT NULL;
SELECT * FROM customeremail;
18.1用视图计算字段
CREATE VIEW orderitemsexpanded AS SELECT
order_num,
prod_id,
quantity,
item_price,
quantity * item_price AS expanded_pricce
FROM
orderitems;
SELECT * FROM orderitemsexpanded;
19.0使用存储过程
20.0使用游标
21.0使用触发器
22.0管理事物处理
23.0安全管理
24.0数据库维护
24.1 数据库的导入导出 不要加分号
导出数据库
shell 下执行
mysqldump -uroot -p 数据库名 > 数据库名.sql
导入数据库
登录到mysql之后
source 路径+文件
24.2 配置linux下的编码集
!includedir /etc/mysql/conf.d/ 客户端的修改
# 设置mysql客户端默认字符集
default-character-set=utf8
!includedir /etc/mysql/mysql.conf.d/ 服务端的修改
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
24.3 数据库只剩下文件的恢复
# MyISAM 直接创建数据库后,在数据库的数据目录下把要恢复的3个文件文件直接扔里面就OK了
.frm .myd .myi
# innodb 在只有frm和ibd文件的情况下,如何恢复数据;
安装 MySQL Utilities
https://downloads.mysql.com/archives/utilities/
cmd中找到frm那个文件,执行如下命令:
切换到对应目录,执行下面语句,不要加分号
mysqlfrm --diagnostic ./文件目录/表名.frm
查出建表语句,复制查询出来的建表语句在mysql中创建的新数据中使用
# 创建表后执行下面的操作
#对已创建的表进行表空间卸载 删除ibd文件
mysql> alter table innodb1 discard tablespace;
把要恢复的idb文件替换进去
#对已创建的表进行空间装载
mysql> alter table innodb1 import tablespace;
25.0优化性能
- 避免使用select *,
- 不确定表大小时候,先用count(*)查下数据.
- 创建表时尽量使用 char 代替 varchar
- 定长的字段放前面,变长的字段放后面.(尽可能小的改变树状结构高度)
- 组合索引代替多个单列索引
(由于mysql中每次只能使用一个索引,所以经常使用多个条件查询时更适 合使用组合索引) - 尽量使用短索引(小数据值)
- 重复少的字段值不适合做索引,例:性别不适合
- 使用连接(JOIN)来代替子查询(Sub-Queries)