Mysql相关操作与命令

引用

Mysql必知必会-下载
Mysql必知必会-简书

APT

mysql 5.51 API

初始化与启动

开机设置

cp support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql
#把mysql注册为开机启动的服务
chkconfig --add mysql 
 
#查看是否添加成功
chkconfig --list mysql

启动与停止

Mysql 服务
根据安装方式使用

yum安装mysql使用：
service mysqld start 或者/etc/init.d/mysqld start
service mysqld restart
service mysqld stop

或者
systemctl start  mysqld.service
systemctl stop  mysqld.service
systemctl restart  mysqld.service
systemctl status mysqld.service

设置mysql的开机启动

chkconfig --add mysqld
chkconfig mysqld on

systemctl enable mysqld
systemctl daemon-reload

库操作

用户相关

远程用户

GRANT 【权限内容】 ON 【库名】.【表名】 TO 【用户名】@'【IP地址】' IDENTIFIED BY '【密码】' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.*  TO ghost@'%' IDENTIFIED BY 'admin' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.*  TO qulv@'%' IDENTIFIED BY 'Qulv789.' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.*  TO php@'%' IDENTIFIED BY 'Qulv789.' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.*  TO php@'%' IDENTIFIED BY 'Qulv789.' WITH GRANT OPTION;

name	权限
all privileges	增删改查所有权限
insert	增
delete	删
update	改
select	查

使命令立刻执行

FLUSH PRIVILEGES;

查询数据库用户：

SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user; 

环境相关：

防火墙-linux

1:查看防火状态
systemctl status firewalld
service  iptables status

2:暂时关闭防火墙
systemctl stop firewalld
service  iptables stop

3:永久关闭防火墙
systemctl disable firewalld
chkconfig iptables off

4:重启防火墙
systemctl enable firewalld
service iptables restart  

5:永久关闭后重启//暂时还没有试过chkconfig iptables on

#开启8011端口 
/sbin/iptables -I INPUT -p tcp --dport 8011 -j ACCEPT 
#保存配置 
/etc/rc.d/init.d/iptables save 
#重启服务 
/etc/rc.d/init.d/iptables restart 
#查看端口是否已经开放
/etc/init.d/iptables status 

1) 重启后生效 
开启： chkconfig iptables on 
关闭： chkconfig iptables off 
2) 即时生效，重启后失效 
开启： service iptables start 
关闭： service iptables stop 

创建数据库

CREATE DATABASE `ultrax` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8_general_ci

登录同时进入数据库

> \>mysql -h host -u user -p databasesname 
> Enter password: *******

查看版本和时间

mysql > select version(),current_date; select now();

退出和取消命令

mysql> \c
mysql> quit

查看表结构

mysql> describe table;
mysql> show columns from table;
mysql> desc table;

切换结束符 DELIMITER

（主要创建储存过程使用）

DELIMITER $$
或
DELIMITER //

mysql> delimiter $$
mysql> delimiter ;

通配与正则

通配

# 通配符 % 在like之后： '%anvil%'
mysql> SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '%anvil%';

mysql> SELECT prod_name FROM products WHERE prod_name LIKE 's%e';

正则匹配

# 在子句中使用正则匹配 REGEXP
mysql> SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;

正则大小写

# 区分大小写
mysql> SELECT prod_name FROM products WHERE prod_name REGEXP BINARY 'Jet' ORDER BY prod_name;

正则匹配多个

# 匹配两个串(类似OR的功能)
mysql> SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name;

# 匹配一组字符
mysql> SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;

增删改以及常用语法

Insert

mysql> insert into table ([column],[column],[column],[column]) values ([value],[value],[value],[value],);
或者：
mysql> insert into table set col_name = value ,

insert...select 用于插入从其它表中选出的行

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]    [INTO] tbl_name [(col_name,...)]    VALUES ({expr | DEFAULT},...),(...),...    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
或：
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]    [INTO] tbl_name    SET col_name={expr | DEFAULT}, ...    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
或：
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]    [INTO] tbl_name [(col_name,...)]    SELECT ...    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]


# 一次插入多行数据  (多行数用括号包裹，逗号分隔)
# 此技术可以提高数据库处理的性能,因为MySQL用单条 INSERT 语句处理多个插入比使用多条 INSERT语句快

mysql> INSERT INTO customers ( cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country )
VALUES
    ( 'Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA' ),
    ( 'M. Martian', '42 Galaxy Way', 'New York', 'NY', '11213', 'USA' );

Delete

mysql> insert into table values ( [column],[column],[column],[colume]);

mysql> delete 

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [WHERE where_definition]    [ORDER BY ...]    [LIMIT row_count]

多表语法：
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]  tbl_name[.*] [, tbl_name[.*] ...]    
FROM table_references   [WHERE where_definition]
或：
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]  FROM tbl_name[.*] [, tbl_name[.*] ...]    USING table_references    [WHERE where_definition]

注释：当引用表名称时，您必须使用别名（如果已给定）：
DELETE t1 FROM test AS t1, test2 WHERE ...
进行多表删除时支持跨数据库删除，但是在此情况下，您在引用表时不能使用别名。
举例说明：DELETE test1.tmp1, test2.tmp2 FROM test1.tmp1, test2.tmp2 WHERE ...
目前，您不能从一个表中删除，同时又在子查询中从同一个表中选择

Update

– 更新和删除的指导原则
– 前一节中使用的 UPDATE 和 DELETE 语句全都具有 WHERE 子句，这样做的
– 理由很充分。如果省略了 WHERE 子句，则 UPDATE 或 DELETE 将被应用到表中
– 所有的行。换句话说，如果执行 UPDATE 而不带 WHERE 子句，则表中每个行
– 都将用新值更新。类似地，如果执行 DELETE 语句而不带 WHERE 子句，表的
– 所有数据都将被删除。
– 下面是许多SQL程序员使用 UPDATE 或 DELETE 时所遵循的习惯。
–  除非确实打算更新和删除每一行，否则绝对不要使用不带 WHERE
– 子句的 UPDATE 或 DELETE 语句。
–  保证每个表都有主键（如果忘记这个内容，请参阅第15章），尽可能
– 像 WHERE 子句那样使用它（可以指定各主键、多个值或值的范围）。
–  在对 UPDATE 或 DELETE 语句使用 WHERE 子句前，应该先用 SELECT 进
– 行测试，保证它过滤的是正确的记录，以防编写的 WHERE 子句不
– 正确。
–  使用强制实施引用完整性的数据库（关于这个内容，请参阅第15
– 章），这样MySQL将不允许删除具有与其他表相关联的数据的行
– 不要省略 WHERE 子句 在使用 UPDATE 时一定要注意细心, 因为稍不注意，就会更新表中所有行.

# 更新符合筛选条件的行中的指定列
mysql> UPDATE customers SET cust_email = 'elemer@fudd.com' WHERE cust_id = 10005;

# 更新多列
mysql> UPDATE customers SET cust_name = 'The Fudds', cust_email = 'elmer@fudd.com' WHERE cust_id = 10005;

# 更新多行时, 使用IGNORE忽略错误, 继续更新下去
mysql> UPDATE IGNORE customers SET cust_email = 'elemer1@fudd.com' WHERE cust_id = '10005';


# 可以用来删除指定列(可以为NULL的情况)
mysql> UPDATE customers SET cust_email = NULL WHERE cust_id = 10005;

Alert Table 更新表

Create Index 创建索引

Create procedure 创建储存过程

create table 创建表

create user 添加用户

create view 创建视图

drop 永久删除数据库对象：表、视图、索引等

insert select 插入select的结果到一个表

distinct 去重

select distinct job from emp;

事务

commit 提交事务

rollback 撤销事务处理块

savepoint 为rollback语句设立保留点

start transaction 设置新事务处理快的开始点

mysql> 

函数与查询

查看当前连接

mysql> show status like ‘Threads%’;

Variable_name	Value	info
Threads_cached	58
Threads_connected	57	###这个数值指的是打开的连接数
Threads_created	3676
Threads_running	4	###这个数值指的是激活的连接数，这个数值一般远低于connected数值

Threads_connected 跟show processlist结果相同，表示当前连接数。准确的来说，Threads_running是代表当前并发数

最大连接数

查询数据库当前设置的最大连接数

mysql> show variables like ‘%max_connections%’;

显示当前用户的连接信息

mysql>  show processlist;
+------+------+------------------+----------------+---------+------+----------+------------------+
| Id   | User | Host             | db             | Command | Time | State    | Info             |
+------+------+------------------+----------------+---------+------+----------+------------------+
| 3007 | qulv | 1.198.19.8:26778 | b3log_symphony | Sleep   | 4618 |          | NULL             |
| 3008 | qulv | 1.198.19.8:25855 | b3log_symphony | Sleep   | 4618 |          | NULL             |
| 3013 | root | localhost:44234  | b3log_symphony | Sleep   |    1 |          | NULL             |
| 3014 | root | localhost:44238  | b3log_symphony | Sleep   |  196 |          | NULL             |
| 3015 | root | localhost:44272  | b3log_symphony | Sleep   |  196 |          | NULL             |
| 3016 | root | localhost:44274  | b3log_symphony | Sleep   |  196 |          | NULL             |
| 3017 | root | localhost:44276  | b3log_symphony | Sleep   |  197 |          | NULL             |
| 3018 | root | localhost:44278  | b3log_symphony | Sleep   |  197 |          | NULL             |
| 3022 | qulv | 1.198.19.8:27370 | b3log_symphony | Sleep   |  899 |          | NULL             |
| 3023 | qulv | 1.198.19.8:27451 | b3log_symphony | Sleep   |  899 |          | NULL             |
| 3024 | qulv | 1.198.19.8:27463 | b3log_symphony | Sleep   |  899 |          | NULL             |
| 3025 | qulv | 1.198.19.8:27054 | b3log_symphony | Sleep   |  880 |          | NULL             |
| 3026 | qulv | 1.198.19.8:27545 | b3log_symphony | Sleep   |  444 |          | NULL             |
| 3028 | root | localhost        | b3log_symphony | Query   |    0 | starting | show processlist |
| 3029 | qulv | 1.198.19.8:26436 | NULL           | Sleep   |    2 |          | NULL             |
| 3030 | qulv | 1.198.19.8:26760 | b3log_symphony | Sleep   |    2 |          | NULL             |
+------+------+------------------+----------------+---------+------+----------+------------------+
16 rows in set (0.00 sec)

显示连接状态

mysql> SHOW STATUS LIKE ‘%connect%’;

mysql>  show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

常用文本处理函数

拼接 Concant()

mysql> select concat(vend_name, '(',vend_country, ')') from vendors order by vend_name;

+------------------------------------------+
| concat(vend_name, '(',vend_country, ')') |
+------------------------------------------+
| ACME(USA)                                |
| Anvils R Us(USA)                         |
| Furball Inc.(USA)                        |
| Jet Set(England)                         |
| Jouets Et Ours(France)                   |
| LT Supplies(USA)                         |
+------------------------------------------+
6 rows in set (0.00 sec)

去空格 Trim()

Rtrim() 右边空格
LTrim() 左边空格
Trim() 两端空格

mysql> select concat(RTrim(vend_name),'(',RTrim(vend_country),')') from vendors order by vend_name;
+------------------------------------------------------+
| concat(RTrim(vend_name),'(',RTrim(vend_country),')') |
+------------------------------------------------------+
| ACME(USA)                                            |
| Anvils R Us(USA)                                     |
| Furball Inc.(USA)                                    |
| Jet Set(England)                                     |
| Jouets Et Ours(France)                               |
| LT Supplies(USA)                                     |
+------------------------------------------------------+
6 rows in set (0.00 sec)

mysql> select concat(RTrim(vend_name),'(',RTrim(vend_country),')') as title from vendors order by vend_name;
+------------------------+
| title                  |
+------------------------+
| ACME(USA)              |
| Anvils R Us(USA)       |
| Furball Inc.(USA)      |
| Jet Set(England)       |
| Jouets Et Ours(France) |
| LT Supplies(USA)       |
+------------------------+
6 rows in set (0.00 sec)

别名 As

其它查询可以直接使用别名代替计算、查询等操作

mysql> select concat(sno, ' - ',sname) as title from student order by sno desc;

mysql> select prod_id, quantity, item_price, quantity*item_price AS expanded_price from orderitems where order_num = 20005;

常用文本处理函数

Upper() Lower() 转换大小写函数

SELECT vend_name, UPPER(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;
SELECT vend_name, LOWER(vend_name) AS vend_name_lower FROM vendors ORDER BY vend_name;

Left() Right()返回串左边和返回串右边指定位数的字符

SELECT vend_name, LEFT(vend_name, 5) AS vend_name_left FROM vendors ORDER BY vend_name;
SELECT vend_name, RIGHT(vend_name, 5) AS vend_name_right FROM vendors ORDER BY vend_name;

Length() 返回串长度

SELECT vend_name, LENGTH(vend_name) AS vend_name_length FROM vendors ORDER BY vend_name;

Locate() 返回串的子串第一次出现的位置

SELECT vend_name, LOCATE('A', vend_name) AS vend_name_locate FROM vendors ORDER BY vend_name;

SubString() 返回子串的字符(截取子串)

SELECT vend_name, SUBSTRING(vend_name, 1, 5) AS vend_name_locate FROM vendors ORDER BY vend_name;

SOUNDEX() 返回串的SOUNDEX值

SELECT vend_name, SOUNDEX(vend_name) AS vend_name_locate FROM vendors ORDER BY vend_name;

SOUNDEX() 发音相近匹配

SELECT cust_name, cust_contact FROM customers WHERE SOUNDEX(cust_contact) = SOUNDEX('Y. Lie');

日期处理函数

当前日期

SELECT CURDATE();
CURDATE() CURRENT_DATE()
CURRENT_DATE和CURRENT_DATE()是CURDATE()的同义词。

mysql> SELECT CURRENT_DATE(), CURRENT_DATE, CURDATE();
+----------------+--------------+------------+
| CURRENT_DATE() | CURRENT_DATE | CURDATE() |
+----------------+--------------+------------+
| 2017-08-10   | 2017-08-10  | 2017-08-10 |
+----------------+--------------+------------+
1 row in set

NOW() 返回当前日期时间

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-09-10 17:26:26 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE(NOW());
+-------------+
| DATE(NOW()) |
+-------------+
| 2017-08-10 |
+-------------+
1 row in set

获取日期偏移

date_sub()

mysql> select date_sub(DATE(now()),interval 1 day) ;
+--------------------------------------+
| date_sub(DATE(now()),interval 1 day) |
+--------------------------------------+
| 2019-09-09                           |
+--------------------------------------+
1 row in set (0.00 sec)

当前时2019-09-17
表示90天前：

mysql> select date_sub(DATE(now()), interval 90 day);
2019-06-19
date_sub(curdate(),interval 1 day) 表示 2019-06-19
date_sub(curdate(),interval -1 day) 表示 2019-09-18
date_sub(curdate(),interval 1 month) 表示 2019-10-17
date_sub(curdate(),interval -1 month) 表示 2019-8-17
date_sub(curdate(),interval 1 year) 表示 2018-09-17
date_sub(curdate(),interval -1 year) 表示 2020-09-17
或
date_sub('2019-06-19',interval 1 day) 表示 2019-06-19

TO_DAYS()

1、利用to_days函数查询今天的数据：
select * from 表名 where to_days(时间字段名) = to_days(now());
to_days函数：返回从0000年（公元1年）至当前日期的总天数。
2、昨天
SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) – TO_DAYS( 时间字段名) <= 1
3.7天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)
4.近30天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)
5.本月
SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, ‘%Y%m' ) = DATE_FORMAT( CURDATE( ) , ‘%Y%m' )
6.上一月
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , ‘%Y%m' ) , date_format( 时间字段名, ‘%Y%m' ) ) =1
#查询本季度数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());
#查询上季度数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
#查询本年数据
select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());
#查询上年数据
select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));

当前时间

SELECT CURTIME();

比较日期(计算日期差值)

SELECT DATEDIFF(‘2018-10-10’, “2018-10-11”);

给指定的日期增加指定的天数

SELECT ADDDATE(NOW(), 10);

向OrderDate增加2天

SELECT OrderId,DATEADD(day,2,OrderDate) AS OrderPayDate FROM Orders

高精度增加时间

SELECT DATE_ADD(NOW(), INTERVAL 60 SECOND)

使用指定格式格式化日期

SELECT DATE_FORMAT(NOW(),’%Y-%m-%d %H:%i:%S’);
SELECT DATE(‘2018-10-10 12:12’); # 截取日期部分
SELECT TIME(‘2018-10-10 12:12:12’); #返回日期的时间部分
SELECT YEAR(‘2018-10-10 12:12:12’); #返回日期的年份部分
SELECT MONTH(‘2018-10-10 12:12:12’); #返回日期的月份部分
SELECT DAY(‘2018-10-10 12:12:12’); #返回日期的天数部分
SELECT HOUR(‘2018-10-10 12:12:12’); #返回日期的时针部分
SELECT MINUTE(‘2018-10-10 12:12:12’); #返回日期的分针部分
SELECT SECOND(‘2018-10-10 12:12:12’); #返回日期的秒部分

根据日期筛选

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 DATE(order_date) BETWEEN ‘2005-09-01’ AND ‘2005-09-30’;

<!--查询临期5天-过期15天公司-->
<select id="queryEndDateLtByCompanyId" resultMap="BaseResultMap">
select <include refid="Base_Column_List"/>    
from company where stop_date &lt;= date_sub(DATE(now()), interval -5 day) and stop_date &gt;= date_sub(DATE(now()), interval 15 day) and company_id = #{companyId,jdbcType=INTEGER}
</select>

更可靠的日期范围筛选

SELECT cust_id, order_num FROM orders WHERE Year(order_date) = 2005 AND Month(order_date) = 9;

返回0-1之间不包括1的随机数

SELECT RAND();

数学函数

Abs()       返回一个数绝对值
Exp()       指数值
Mod()     除操作的余数
Pi()          圆周率
Rand()    随机数
Sqrt()      平方根
Sin()       角度的正弦
Cos()      角度的余弦
Tan()      角度的正切

聚集函数 汇总数据

回指定列的平均值

SELECT AVG(prod_price) AS avg_price FROM products;

AVG() 返回指定列符合条件的行的平均值

SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003;

count(*) 计算指定表的行数(包括null值行)

SELECT COUNT(*) AS num_cust FROM customers;

count(col) 计算指定列的行数(不包括null值行)

SELECT COUNT(cust_email) AS num_cust FROM customers;

MAX() 返回指定列的最大值（忽略null值）

SELECT MAX(prod_price) AS max_price FROM products;

MIN() 返回指定列的最小值（忽略null值）

SELECT MIN(prod_price) AS min_price FROM products;

SUM() 对指定列求合

SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;

SUM(A*B) 在求全函数中计算

SELECT SUM(quantity\*item_price) AS total_price FROM orderitems WHERE order_num = 20005;

去重（列出不同的值）- 在函数中使用DISTINCT

SELECT DISTINCT 列名称 FROM 表名称

SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 1003;

使用多个函数

SELECT
    COUNT( * ) AS num_items,
    MIN( prod_price ) AS price_min,
    MAX( prod_price ) AS price_max,
    AVG( prod_price ) AS price_avg 
FROM
    products;

分组数据： Group by 和 Having

Group By 分组

– 在具体使用 GROUP BY 子句前，需要知道一些重要的规定。
– * GROUP BY 子句可以包含任意数目的列。这使得能对分组进行嵌套，
– 为数据分组提供更细致的控制。
– * 如果在 GROUP BY 子句中嵌套了分组，数据将在最后规定的分组上
– 进行汇总。换句话说，在建立分组时，指定的所有列都一起计算
– （所以不能从个别的列取回数据）。
– * GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式
– （但不能是聚集函数）。如果在 SELECT 中使用表达式，则必须在
– GROUP BY 子句中指定相同的表达式。不能使用别名。
– * 除聚集计算语句外， SELECT 语句中的每个列都必须在 GROUP BY 子
– 句中给出。
– * 如果分组列中具有 NULL 值，则 NULL 将作为一个分组返回。如果列
– 中有多行 NULL 值，它们将分为一组。
– * GROUP BY 子句必须出现在 WHERE 子句之后， ORDER BY 子句之前

mysql> select vend_id,count(*) as num_prods from products;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |        14 |
+---------+-----------+
1 row in set (0.00 sec)

# 分组显示每个id下有多少产品
mysql> select vend_id,count(*) as num_prods from products group by vend_id;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+
4 rows in set (0.00 sec)

# 对分组汇总
SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id WITH ROLLUP;

过滤分组Having 分组的where

• where过滤行，Having过滤分组。Where在分组前过滤，Having在分组后过滤

# 使用HAVING过滤分组
mysql> SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;
+---------+--------+
| cust_id | orders |
+---------+--------+
|   10001 |      2 |
+---------+--------+
1 row in set (0.00 sec)


# 查询出具有2个或以上，价格为10或以上产品的供应商
mysql> SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >= 2;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1003 |         4 |
|    1005 |         2 |
+---------+-----------+
2 rows in set (0.00 sec)

mysql> SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*) >= 2;

分组和排序 GROUP BY 和 ORDER BY

Order By	Group By
排序产生的输出	分组行。但输出不一定是分组的排序
任意列可使用	只能使用选择列或者表达式列，而且必须使用每个选择列表达式
非必须	如果与聚集函数（比如统计conut、计算等）则必须使用

# GROUP BY 和 ORDER BY 
mysql> SELECT order_num, SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >= 50 ORDER BY ordertotal;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20006 |      55.00 |
|     20008 |     125.00 |
|     20005 |     149.87 |
|     20007 |    1000.00 |
+-----------+------------+
4 rows in set (0.00 sec)

SELECT子句的顺序

SELECT > FROM > WHERE > GROUP BY > HAVING > ORDER BY > LIMIT;

子查询

查询使用关键字 IN

# 列出订购物品 TNT2 的所有客户
-- 方式一：分条查询

mysql> SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';
+-----------+
| order_num |
+-----------+
|     20005 |
|     20007 |
+-----------+
2 rows in set (0.00 sec)

mysql> SELECT cust_id FROM orders WHERE order_num IN (20005, 20007);
+---------+
| cust_id |
+---------+
|   10001 |
|   10004 |
+---------+
2 rows in set (0.00 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'));

mysql> 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'));
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
2 rows in set (0.00 sec)

计算字段中使用子查询

# 计算字段中使用子查询
SELECT
    cust_name,
    cust_state,
    ( SELECT COUNT( * ) FROM orders WHERE orders.cust_id = customers.cust_id ) AS orders 
FROM
    customers 
ORDER BY
    cust_name;

联结Join

# 联结查询（等值联结果或者内部连接）
SELECT
    vend_name,
    prod_name,
    prod_price 
FROM
    vendors,
    products 
WHERE
    vendors.vend_id = products.vend_id 
ORDER BY
    vend_name,
    prod_name;
    
# 内部连接的另一种更明确的写法
SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;


# 同时连接多个表
SELECT
    prod_name,
    vend_name,
    prod_price,
    quantity 
FROM
    orderitems,
    products,
    vendors 
WHERE
    products.vend_id = vendors.vend_id 
    AND orderitems.prod_id = products.prod_id 
    AND order_num = 20005;

笛卡尔乘积

笛卡尔积即是查询结果的相乘，即两个集合X和Y的乘积，表示为X*Y。
第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员 。
通俗点讲就是检索出的数目行数可能是第一个表的行数乘以第二个表中的行数。

高级联结

左联右联 内联外联
inner join
left join
right join

# 在连接查询中使用别名
SELECT
    cust_name,
    cust_contact 
FROM
    customers AS c,
    orders AS o,
    orderitems AS oi 
WHERE
    c.cust_id = o.cust_id 
    AND oi.order_num = o.order_num 
    AND prod_id = 'TNT2';

# 自连接
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';

使用自联结而不是子查询，有时候处理紫联结比使用子查询快的多，应该尝试两种方法那种性能更好

外联 （左联右联)

LEFT OUTER JOIN 简写 left join
如果是左联，则保留左表所有的行，即使在右表中没有匹配的行。
右联同理

# 外部连接(左外和右外)

SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10002 |      NULL |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+
6 rows in set (0.00 sec)

SELECT customers.cust_id, orders.order_num FROM customers RIGHT OUTER JOIN orders ON orders.cust_id = customers.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+
5 rows in set (0.00 sec)

带函数的联结

# 带聚集函数的联结
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;

+----------------+---------+---------+
| cust_name      | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc.    |   10001 |       2 |
| Wascals        |   10003 |       1 |
| Yosemite Place |   10004 |       1 |
| E Fudd         |   10005 |       1 |
+----------------+---------+---------+
4 rows in set (0.00 sec)


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;

+----------------+---------+---------+
| cust_name      | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc.    |   10001 |       2 |
| Mouse House    |   10002 |       0 |
| Wascals        |   10003 |       1 |
| Yosemite Place |   10004 |       1 |
| E Fudd         |   10005 |       1 |
+----------------+---------+---------+
5 rows in set (0.00 sec)

组合查询 并 union

# 使用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);

+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1003 | FC      |       2.50 |
|    1002 | FU1     |       3.42 |
|    1003 | SLING   |       4.49 |
|    1003 | TNT1    |       2.50 |
|    1001 | ANV01   |       5.99 |
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1002 | OL1     |       8.99 |
+---------+---------+------------+
8 rows in set (0.00 sec)

# 不自动取消重复行
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);

+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1003 | FC      |       2.50 |
|    1002 | FU1     |       3.42 |
|    1003 | SLING   |       4.49 |
|    1003 | TNT1    |       2.50 |
|    1001 | ANV01   |       5.99 |
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1002 | OL1     |       8.99 |
+---------+---------+------------+
8 rows in set (0.00 sec)

# 只能在最后一条SQL后写排序
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);
ORDER BY vend_id, prod_price;

全文本搜索

视图

• 数据库中的虚拟表，修改等同于修改实际表。同时修改实际表的时候视图也会自动修改
• 简单化，安全控制权限在视图内，逻辑数据独立性
• 可单表或者多表建立视图

创建：

create vuew vuew_name (column_list, colume_list, …)
as select select_column_list, select_column_list …
from table_name, table_name
where …

p:

CREATE VIEW user_company_view ( user_id, company_id, user_name, real_name, position, start_date, stop_date, version ) AS SELECT USER
.user_id,
USER.company_id,
USER.user_name,
USER.real_name,
USER.position,
company.start_date,
company.stop_date,
company.version 
FROM
	USER,
	company 
WHERE
	USER.company_id = company.company_id

查询修改删除

同正常使用
select update delete

视图结构查看修改与删除视图等

查看基本信息 describe view_name
等同 desc view_name
或 show table status like ‘view_name’
mysql> show table status like ''user_company_view
或在视图表中查看：
select * from information_schema.views

修改：
修改和创建时同一条语句，如果当前视图已存在则修改，不存在则创建
create or update view view_name as sleect * from name;

CREATE  [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW  view_name  [(column_list)]
AS SELECT_statement
[WITH [CASCADED \ LOCAL] CHECK OPTION]

或者 使用alert
alert view view_name as select column_name from table_name [or view_name]

删除
drop view if exists view_name

储存过程

单独编写每条语句，并根据结果有条件的执行另外的语句。即类似脚本、批处理文件
存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

简单、安全、高性能

Sql中的存储过程及相关介绍：
CREATE PROCEDURE [拥有者.]存储过程名[;程序编号]
[(参数#1,…参数#1024)]
[WITH
{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}
]
[FOR REPLICATION]
AS 程序行
其中存储过程名不能超过128个字。每个存储过程中最多设定1024个参数

创建储存过程

• 由于储存过程中sql语句需要使用分号作为结束符，所以需要使用delimiter设置自定义结束符。创建完储存过程后后再使用delimiter ;重设结束符为分号；

创建

CREATE 
    [DEFINER = { user | CURRENT_USER }] 　
PROCEDURE sp_name ([proc_parameter[,...]]) 
    [characteristic ...] routine_body 
proc_parameter: 
    [ IN | OUT | INOUT ] param_name type 
characteristic: 
    COMMENT 'string' 
    | LANGUAGE SQL 
    | [NOT] DETERMINISTIC 
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } 
    | SQL SECURITY { DEFINER | INVOKER } 
routine_body: 　　
    Valid SQL routine statement 
    
[begin_label:] BEGIN 　　
    [statement_list] 　　　　
    …… 
END [end_label]

# P:
CREATE PROCEDURE demo_in_parameter(IN p_in int)     

MYSQL 存储过程中的关键语法
声明语句结束符，可以自定义:

DELIMITER $$
或
DELIMITER //

声明存储过程:

CREATE PROCEDURE demo_in_parameter(IN p_in int)

存储过程开始和结束符号:

BEGIN … END

变量赋值:

SET @p_in=1

变量定义:

DECLARE l_int int unsigned default 4000000;

创建mysql存储过程、存储函数:

create procedure name()

存储过程名(参数)存储过程体:

create function 存储函数名(参数)

检查

show cretate procedure name()

调用

call productpricing()

删除

变量

mysql> delimiter //
mysql> create procedure productpricing()
    -> begin
    -> select avg(prod_price) as priceaverage
    -> from products;
    -> end //
    
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call productpricing()
    -> ;
+--------------+
| priceaverage |
+--------------+
|    16.133571 |
+--------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.02 sec)

CALL productpricing(@pricelow, @pricehigh, @priceaverage);

游标

触发器

事务处理

数据库维护

优化、改善性能

配置

[client]
port = 3306
socket = /tmp/mysql.sock
 
[mysqld]
 
###############################基础设置#####################################
 
#Mysql服务的唯一编号 每个mysql服务Id需唯一
server-id = 1
 
#服务端口号 默认3306
port = 3306
 
#mysql安装根目录
basedir = /usr/local/mysql
 
#mysql数据文件所在位置
datadir = /usr/local/mysql/data
 
#临时目录 比如load data infile会用到
tmpdir  = /tmp
 
#设置socke文件所在目录
socket  = /tmp/mysql.sock
 
#主要用于MyISAM存储引擎,如果多台服务器连接一个数据库则建议注释下面内容
skip-external-locking
 
#只能用IP地址检查客户端的登录，不用主机名
skip_name_resolve = 1
 
#事务隔离级别，默认为可重复读，mysql默认可重复读级别（此级别下可能参数很多间隙锁，影响性能）
transaction_isolation = READ-COMMITTED
 
#数据库默认字符集,主流字符集支持一些特殊表情符号（特殊表情符占用4个字节）
character-set-server = utf8mb4
 
#数据库字符集对应一些排序等规则，注意要和character-set-server对应
collation-server = utf8mb4_general_ci
 
#设置client连接mysql时的字符集,防止乱码
init_connect=‘SET NAMES utf8mb4‘
 
#是否对sql语句大小写敏感，1表示不敏感
lower_case_table_names = 1
 
#最大连接数
max_connections = 400
 
#最大错误连接数
max_connect_errors = 1000
 
#TIMESTAMP如果没有显示声明NOT NULL，允许NULL值
explicit_defaults_for_timestamp = true
 
#SQL数据包发送的大小，如果有BLOB对象建议修改成1G
max_allowed_packet = 128M
 
#MySQL连接闲置超过一定时间后(单位：秒)将会被强行关闭
#MySQL默认的wait_timeout  值为8个小时, interactive_timeout参数需要同时配置才能生效
interactive_timeout = 1800
wait_timeout = 1800
 
#内部内存临时表的最大值 ，设置成128M。
#比如大数据量的group by ,order by时可能用到临时表，
#超过了这个值将写入磁盘，系统IO压力增大
tmp_table_size = 134217728
max_heap_table_size = 134217728
 
#禁用mysql的缓存查询结果集功能
#后期根据业务情况测试决定是否开启
#大部分情况下关闭下面两项
query_cache_size = 0
query_cache_type = 0
 
#####################用户进程分配到的内存设置BEGIN#############################
 
##每个session将会分配参数设置的内存大小
#用于表的顺序扫描，读出的数据暂存于read_buffer_size中，当buff满时或读完，将数据返回上层调用者
#一般在128kb ~ 256kb,用于MyISAM
#read_buffer_size = 131072
#用于表的随机读取，当按照一个非索引字段排序读取时会用到，
#一般在128kb ~ 256kb,用于MyISAM
#read_rnd_buffer_size = 262144
#order by或group by时用到
 
#建议先调整为2M，后期观察调整
sort_buffer_size = 2097152
 
#一般数据库中没什么大的事务，设成1~2M，默认32kb
binlog_cache_size = 524288
 
########################用户进程分配到的内存设置END############################
 
#在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中
#官方建议back_log = 50 + (max_connections / 5),封顶数为900
back_log = 130
 
############################日志设置##########################################
 
#数据库错误日志文件
log_error = error.log
 
#慢查询sql日志设置
slow_query_log = 1
slow_query_log_file = slow.log
 
#检查未使用到索引的sql
log_queries_not_using_indexes = 1
 
#针对log_queries_not_using_indexes开启后，记录慢sql的频次、每分钟记录的条数
log_throttle_queries_not_using_indexes = 5
 
#作为从库时生效,从库复制中如何有慢sql也将被记录
log_slow_slave_statements = 1
 
#慢查询执行的秒数，必须达到此值可被记录
long_query_time = 8
 
#检索的行数必须达到此值才可被记为慢查询
min_examined_row_limit = 100
 
#mysql binlog日志文件保存的过期时间，过期后自动删除
expire_logs_days = 5
 
############################主从复制设置#####################################
 
#开启mysql binlog功能
log-bin=mysql-bin
 
#binlog记录内容的方式，记录被操作的每一行
binlog_format = ROW
 
#对于binlog_format = ROW模式时，减少记录日志的内容，只记录受影响的列
binlog_row_image = minimal
 
#master status and connection information输出到表mysql.slave_master_info中
master_info_repository = TABLE
 
#the slave‘s position in the relay logs输出到表mysql.slave_relay_log_info中
relay_log_info_repository = TABLE
 
#作为从库时生效,想进行级联复制，则需要此参数
log_slave_updates
 
#作为从库时生效,中继日志relay-log可以自我修复
relay_log_recovery = 1
 
#作为从库时生效,主从复制时忽略的错误
slave_skip_errors = ddl_exist_errors
 
#####################redo log和binlog的关系设置BEGIN#########################
 
#(步骤1) prepare dml相关的SQL操作，然后将redo log buff中的缓存持久化到磁盘
#(步骤2)如果前面prepare成功，那么再继续将事务日志持久化到binlog
#(步骤3)如果前面成功，那么在redo log里面写上一个commit记录
#当innodb_flush_log_at_trx_commit和sync_binlog都为1时是最安全的，
#在mysqld服务崩溃或者服务器主机crash的情况下，binary log只有可能丢失最多一个语句或者一个事务。
#但是都设置为1时会导致频繁的io操作，因此该模式也是最慢的一种方式。
#当innodb_flush_log_at_trx_commit设置为0，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
#当innodb_flush_log_at_trx_commit设置为2，只有在操作系统崩溃或者系统掉电的情况下，上一秒钟所有事务数据才可能丢失。
 
#commit事务时,控制redo log buff持久化磁盘的模式 默认为1
innodb_flush_log_at_trx_commit = 2
 
#commit事务时,控制写入mysql binlog日志的模式 默认为0
#innodb_flush_log_at_trx_commit和sync_binlog都为1时，mysql最为安全但性能上压力也是最大
sync_binlog = 1
 
####################redo log和binlog的关系设置END############################
 
############################Innodb设置#####################################
 
#数据块的单位8k，默认是16k，16kCPU压力稍小，8k对select的吞吐量大
#innodb_page_size的参数值也影响最大索引长度，8k比16k的最大索引长度小
#innodb_page_size = 8192
 
#一般设置物理存储的60% ~ 70%
innodb_buffer_pool_size = 1G
 
#5.7.6之后默认16M
#innodb_log_buffer_size = 16777216
 
#该参数针对unix、linux，window上直接注释该参数.默认值为NULL
#O_DIRECT减少操作系统级别VFS的缓存和Innodb本身的buffer缓存之间的冲突
innodb_flush_method = O_DIRECT
 
#此格式支持压缩, 5.7.7之后为默认值
innodb_file_format = Barracuda
 
#CPU多核处理能力设置，假设CPU是2颗4核的，设置如下
#读多，写少可以设成2:6的比例
innodb_write_io_threads = 4
innodb_read_io_threads = 4
 
#提高刷新脏页数量和合并插入数量，改善磁盘I/O处理能力
#默认值200（单位：页）
#可根据磁盘近期的IOPS确定该值
innodb_io_capacity = 500
 
#为了获取被锁定的资源最大等待时间，默认50秒，超过该时间会报如下错误:
# ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
innodb_lock_wait_timeout = 30
 
#调整buffer pool中最近使用的页读取并dump的百分比,通过设置该参数可以减少转储的page数
innodb_buffer_pool_dump_pct = 40
 
#设置redoLog文件所在目录, redoLog记录事务具体操作内容
innodb_log_group_home_dir = /usr/local/mysql/redolog/
 
#设置undoLog文件所在目录, undoLog用于事务回滚操作
innodb_undo_directory = /usr/local/mysql/undolog/
 
#在innodb_log_group_home_dir中的redoLog文件数, redoLog文件内容是循环覆盖写入。
innodb_log_files_in_group = 3
 
#MySql5.7官方建议尽量设置的大些，可以接近innodb_buffer_pool_size的大小
#之前设置该值较大时可能导致mysql宕机恢复时间过长，现在恢复已经加快很多了
#该值减少脏数据刷新到磁盘的频次
#最大值innodb_log_file_size * innodb_log_files_in_group <= 512GB,单文件<=256GB
innodb_log_file_size = 1024M
 
#设置undoLog文件所占空间可以回收
#5.7之前的MySql的undoLog文件一直增大无法回收
innodb_undo_log_truncate = 1
innodb_undo_tablespaces = 3
innodb_undo_logs = 128
 
#5.7.7默认开启该参数 控制单列索引长度最大达到3072
#innodb_large_prefix = 1
 
#5.7.8默认为4个, Inodb后台清理工作的线程数
#innodb_purge_threads = 4
 
#通过设置配置参数innodb_thread_concurrency来限制并发线程的数量，
#一旦执行线程的数量达到这个限制，额外的线程在被放置到对队列中之前，会睡眠数微秒，
#可以通过设定参数innodb_thread_sleep_delay来配置睡眠时间
#该值默认为0,在官方doc上，对于innodb_thread_concurrency的使用，也给出了一些建议:
#(1)如果一个工作负载中，并发用户线程的数量小于64，建议设置innodb_thread_concurrency=0；
#(2)如果工作负载一直较为严重甚至偶尔达到顶峰，建议先设置innodb_thread_concurrency=128,
###并通过不断的降低这个参数，96, 80, 64等等，直到发现能够提供最佳性能的线程数
#innodb_thread_concurrency = 0
 
#强所有发生的死锁错误信息记录到error.log中，之前通过命令行只能查看最近一次死锁信息
innodb_print_all_deadlocks = 1
 
############################其他设置########################################
 
[mysqldump]
quick
max_allowed_packet = 128M
 
[mysql]
no-auto-rehash
 
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 256k
read_buffer = 2M
write_buffer = 2M
 
[mysqlhotcopy]
interactive-timeout
 
[mysqld_safe]
#增加每个进程的可打开文件数量
open-files-limit = 28192