Mysql - 使用入门 | V2AS

Mysql - 使用入门

阅读原文时间：2021年04月29日阅读：1

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

两种条件都要满足

只要满足其中的一种条件

指定检索的范围

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

使用 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数据库维护

25.0优化性能

手机扫一扫

移动阅读更方便

你可能感兴趣的文章