MySQL数据库

发表于 更新于

数据库连接

1
mysql –u用户名 [–h主机名或者IP地址,-P端口号] –p密码

命令输入在mysql> 之后:

  • 用q\、quit、exit三种命令可以退出命令行实用程序；

开启MySQL的远程帐号

用GUI连接数据库如果报下面错误，是你的 MySQL远程连接账号没有开启。

1
2
3
Unable to connect to host 192.168.188.114, or the request timed out.
Be sure that the address is correct and that you have the necessary privileges, or try increasing the connection timeout (currently 10 seconds).
MySQL said: Host '192.168.188.106' is not allowed to connect to this MariaDB server

通过下面的命令，解决不能连接的错误，进入 MySQL 执行下面语句。

1
2
3
4
# 你想root使用123456从'192.168.188.106'主机连接到mysql服务器 wabg库下面所有表的话。
MySQL> grant all PRIVILEGES on wabg.* to  root@'192.168.188.106'  identified by '123456' WITH GRANT OPTION;
# 你想myuser使用mypassword从任何主机连接到mysql服务器的话
MySQL> grant all PRIVILEGES on *.* to  'myuser'@'%'  identified by 'mypassword' WITH GRANT OPTION;

上面的语句表示将 wabg 数据库的所有权限授权给 root 这个用户，允许 root 用户在 192.168.1.106 这个 IP 进行远程登陆，并设置 root 用户的密码为 123456 。

  • all PRIVILEGES 表示赋予所有的权限给指定用户，这里也可以替换为赋予某一具体的权限

  • wabg.* 表示上面的权限是针对于哪个表的，wabg指的是数据库，后面的 * 表示对于所有的表，由此可以推理出：

    • 对于全部数据库的全部表授权为“ . ”
    • 对于某一数据库的全部表授权为“ 数据库名.* ”
    • 对于某一数据库的某一表授权为“数据库名.表名”

  • root 表示你要给哪个用户授权，这个用户可以是存在的用户，也可以是不存在的用户。

  • 192.168.188.106 表示允许远程连接的 IP 地址，如果想不限制链接的 IP 则设置为“%”即可。

  • 123456 为用户的密码。

    如何开启MySQL的远程帐号？执行了上面的语句后，再执行下面的语句，方可立即生效。

    1
    MySQL> flush privileges;

当你报下面错误，提示您的密码不满足当前的策略要求。错误如下：

1
2
3
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
# 或者
mysqladmin: unable to change password; error: 'Your password does not satisfy the current policy requirements'

解决方法：可以按照现有策略设置密码，也可以更改密码策略。

1
2
3
4
# 更改密码策略为LOW
MySQL> set global validate_password_policy=0;
# 更改密码长度 密码最小长度为4
MySQL> set global validate_password_length=4;

进入 MySQL 查看你的密码验证策略

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password_check_user_name    | OFF   | 
| validate_password_dictionary_file    |       | 
| validate_password_length             | 4     | 
| validate_password_mixed_case_count   | 1     | 
| validate_password_number_count       | 1     | 
| validate_password_policy             | LOW   | 
| validate_password_special_char_count | 1     | 
+--------------------------------------+-------+
7 rows in set (0.00 sec)
  • validate_password_check_user_name 不得使用当前会话用户名作为密码的一部分
  • validate_password_dictionary_file 验证密码强度的字典文件路径
  • validate_password_length 密码最小长度
  • validate_password_mixed_case_count 密码至少要包含的小写字母个数和大写字母个数
  • validate_password_number_count 密码至少要包含的数字个数
  • validate_password_policy 密码强度检查等级，0/LOW、1/MEDIUM、2/STRONG
    • 0/LOW：只检查长度。
    • 1/MEDIUM：检查长度、数字、大小写、特殊字符。
    • 2/STRONG：检查长度、数字、大小写、特殊字符字典文件。
  • validate_password_special_char_count 密码至少要包含的特殊字符数

MySQL修改密码

1
mysqladmin -uroot -p password

创建数据库

对于表的操作需要先进入库use 库名;

1
2
3
4
5
6
7
8
-- 创建一个名为 samp_db 的数据库，数据库字符编码指定为 gbk
create database samp_db character set gbk;
drop database samp_db; -- 删除 库名为samp_db的库
show databases;        -- 显示数据库列表。
use samp_db;     -- 选择创建的数据库samp_db
show tables;     -- 显示samp_db下面所有的表名字
describe 表名;    -- 显示数据表的结构
delete from 表名; -- 清空表中记录

创建数据库表

使用 create table 语句可完成对表的创建, create table 的常见形式: 语法：create table 表名称(列声明);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 如果数据库中存在user_accounts表，就把它从数据库中drop掉
DROP TABLE IF EXISTS `user_accounts`;
CREATE TABLE `user_accounts` (
  `id`             int(100) unsigned NOT NULL AUTO_INCREMENT primary key,
  `password`       varchar(32)       NOT NULL DEFAULT '' COMMENT '用户密码',
  `reset_password` tinyint(32)       NOT NULL DEFAULT 0 COMMENT '用户类型：0－不需要重置密码；1-需要重置密码',
  `mobile`         varchar(20)       NOT NULL DEFAULT '' COMMENT '手机',
  `create_at`      timestamp(6)      NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `update_at`      timestamp(6)      NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  -- 创建唯一索引，不允许重复
  UNIQUE INDEX idx_user_mobile(`mobile`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8
COMMENT='用户表信息';

数据类型的属性解释

  • NULL：数据列可包含NULL值；

  • NOT NULL：数据列不允许包含NULL值；

  • DEFAULT：默认值；

  • PRIMARY KEY：主键；

  • AUTO_INCREMENT：自动递增，适用于整数类型；

  • UNSIGNED：是指数值类型只能为正数；

  • CHARACTER SET name：指定一个字符集；

  • COMMENT：对表或者字段说明；

检索数据

  • 检索多列
1
SELECT prod_id,prod_name FROM products;
  • 检索所有列
1
SELECT * FROM products;
  • 检索不同行 (DISTINCT返回不重复的值)
1
SELECT DISTINCT vend_id FROM products;

注：

不能部分使用DISTINCT, DISTINCT关键字应用于所有列，而不是前置列。

SELECT DISTINCT vend_id,prod_price 除非两个列都不同，否则所有行都被检索出来。

  • 限制结果(LIMIT)
1
2
3
SELECT prod_name FROM products LIMIT 5;
SELECT prod_name FROM products LIMIT 5,5; # 从第5行开始的5行
SELECT prod_name FROM products LIMIT 5 OFFSET 5; # 从第5行开始的5行

行0 LIMIT 1，1 检索出来的事第二行而不是第一行

  • 使用完全限定的表名
1
SELECT products.prod_name FROM products;

排序检索数据

1
SELECT prod_name FROM products ORDER BY prod_name;
  • 按多个列排序
1
2
# 首先按照价格，再按照名称
SELECT prod_id,prod_name FROM products ORDER BY prod_price, prod_name;
  • 指定方向排序
1
2
# 按照价格降序排序
SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price DESC;
1
2
#按照价格降序，再按照名称默认升序排列，如果需要名称也降序，需要prod_name后面也指定DESC
SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price DESC,prod_name;

ORDER BY子句的位置应该在FROM之后，如果使用LIMIT 他必须在ORDER BY之后

过滤数据

使用WHERE子句

1
2
3
SELECT prod_name 
FROM products 
WHERE prod_price = 2.50;
  • 范围值检查
1
2
3
SELECT prod_name,prod_price 
FROM products 
WHERE prod_price BETWEEN 5 AND 10;
  • 空值检查
1
2
3
SELECT cust_id 
FROM customers 
WHERE cust_email IS NULL;
  • 组合WHERE 子句 AND/OR
计算次序
1
2
3
4
SELECT prod_name, prod_price 
FROM products 
WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;
# AND优先级比OR高，所以vend_id = 1002 OR (vend_id = 1003 AND prod_price >= 10);

IN操作

1
2
3
SELECT prod_name, prod_price 
FROM products 
WHERE vend_id IN (1002, 1003);

NOT 操作符

1
2
3
SELECT prod_name, prod_price 
FROM products 
WHERE vend_id NOT IN (1002, 1003);

用通配符过滤

LIKE操作符
  • 百分号通配符 % 任意字符任意次数（包括0次）,区分大小写
1
2
3
SELECT prod_name
FROM products
WHERE prod_name LIKE 'aa%';

虽然可以匹配任意字符，但是不能个匹配NULL

  • 下划线(_)通配符 只匹配单个字符

技巧

不要过度使用通配符，在确实需要的时候使用

不要放在搜索模式的开始处

用正则表达式进行搜索

1
2
3
4
5
6
7
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000';  # 匹配包含内容 aaa1000可以被匹配到

SELECT prod_name
FROM products
WHERE prod_name LIKE '1000';  # 匹配整列数据内容 aaa1000无法匹配到

创建计算字段

拼接字段

1
2
3
4
5
# Concat 拼接字符
# RTrim 去除空格 
# AS 起别名
SELECT Concat(RTrim(vend_name), ' (', vend_country,')') AS  vend_title
FROM vendors;

执行算数计算

1
2
SELECT quantity*item_price AS expanded_price
FROM orderitems;

使用数据处理函数

不同的DBMS上，函数差异较大，所以可移植性差异较大。

文本处理函数

函数 说明
Left 返回左边字符
Length 返回串的长度
Locate 找出串的一个子串
Lower 转换为小写
LTrim 去掉串左边的小写
Right 返回串右边的字符
RTrim 去掉串右边的空格
Soundex 返回串的SOUBNDEX的值
SubString 返回子串的字符
Upper 将串转换为大写

返回日期和时间处理函数

应该总是用4位年份

1
2
3
4
#日期范围内的数据
SELECT  cust_id
FROM orders
WHERE Date(order_date) BETWEEN '2015-09-01' AND '2019-09-30'

数值处理函数

常用的 Abs() Mod() Rand()

汇总数据

函数 说明
AVG 返回平均值
COUNT 返回行数
MAX 返回最大值
MIN 返回最小值
SUM 返回和
COUNT函数
1
2
3
4
5
6
7
# 返回某一列的个数，包括NULL
SELECT COUNT(*) AS num_cust
FROM customers;

# 返回某一列的非空的个数
SELECT COUNT(column) AS num_cust
FROM customers;
MAX函数

返回一列最大值，或者日期，如果是文本，则数据按相应的列排序，返回最后一行。

MAX是忽略NULL的

SUM函数

多列函数的求和

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

去重求平均

1
2
3
SELECT AVG(DISTINCT item_price) AS avg_price
FROM products
WHERE vend_id = 1003;

分组数据 GROUP BY

1
2
3
SELECT vend_id, COUNT(*) AS num_prods
FROM products
GrOUP BY vend_id;
  • GROUP BY子句可以包含惹你数目的列
  • GROUP BY 子句中嵌套分组，数据将在最后规定的分组上进行汇总。
  • GROUP BY子句中的每一个列，必须是检索列或者有效的表达式。不能是聚集函数
  • 除聚集外，SELECT语句中的每一个列都必须在GROUP BY子句给出
  • 如果分组中具有NULL，NULL作为单独分组返回。
  • GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

过滤分组 HAVING

WHERE 用于过滤行，HAVING用于过滤组

1
2
3
4
5
# 所有客户订单数量大于2的信息
SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

同时使用WHERE 和HAVING

1
2
3
4
5
6
# 列出2个以上，价格10以上的产品的供应商
SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) > 2;

分组和排序

1
2
3
4
5
6
# 按照总计订单价格排序输出
SELECT order_num ,SUM(quantity*item_price) AS ordertotal
FROM orderitems
GrOUP BY order_num
HAVING SUM(quantity*item_price) >= 50
ORDER BY ordertotal;

SELECT子句顺序

1
SELECT FROM WHERE GROUP BY HAVING ORDER BY LIMIT

使用子查询

1
2
3
4
5
SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
										FROM orderitems
										WHERE prod_id = 'TNT2');

子查询并不是最有效的方法

联结表

关系型数据库

外键

关系型数据库中，某表中的一列是另外一个表的主键，则这列是外键。

1
2
3
4
SELECT vend_name,prod_name,prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;

笛卡尔积

没有联结条件的表关系返回的结果为笛卡尔积。

检索出来行的数目将是第一个表的行乘以第二个表的行数。

内部联结 INNER JOIN … ON

1
2
3
SELECT vend_name,prod_name,prod_price
FROM vendors INNER JOIN products
ON vendors,vend_id = products.vend_id;

自联结

1
2
3
4
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 = 'DINTR';

自然联结

1
2
3
4
#排除多次出现，使每一列治返回一次
SELECT b.*, a.order_name,a.order_date
FROM customers AS b, orders AS a
WHERE b.cust_id = a.cust_id;

外部联结 LEFT/RIGHT OUTER JOIN…ON

包含两表不关联的行的联结方式，叫做外部联结。

1
2
3
SELECT a.cust_id,b.order_num
FROM customers AS a LEFT OUTER JOIN orders AS b
ON a.cust_id = b.cust_id; # a的某些cust_id 在b中没有，a在左边，a显示全部，b没有的部分显示NULL

组合查询 UNION

将多条SELECT语句的结果作为一次查询结果输出

1
2
3
4
5
6
7
8
9
10
11
12
SELECT a.cust_id,a.cust_name
FROM customers AS a
WHERE a.cust_id < 10
UNION
SELECT a.cust_id,a.cust_name
FROM customers AS a
WHERE a.cust_id >100;

#等价于
SELECT a.cust_id,a.cust_name
FROM customers AS a
WHERE a.cust_id < 10 OR  a.cust_id > 100;

自动去重

UNION 可以自动把重复的行数去除

如果不需要去掉重复使用UNION ALL

UNION只能使用一次ORDER BY，且放在最后一个SELECT后面。

全文本搜索（因为不是所有引擎支持，所以忽略）

插入数据

插入完整的行

1
2
3
4
INSERT INTO Customers
VALUE (NULL,
      'XuQi',
      '123');

更安全的插入方法

1
2
3
INSERT INTO Customers (cust_name, /* 不需要指定cust_id */
                      cust_code)
                      VALUE('XuQi','123');

插入行的一部分

某些列可以被省略，但是这些列必须满足以下条件:

  • 该列定义为允许NULL值
  • 在表定义中给出默认值。和表示如果不给出值，将使用默认值。

更新和删除数据

1
2
3
UPDATE customers
SET cust_email = 'xuqi.jx@gmail.com'
WHERE cust_id = 10005;
1
2
DELETE FROM customers
WHERE cust_id = 10005;

创建和操纵表

创建表

1
2
3
4
5
6
7
CREATE TABLES customers
(
  cust_id int NOT NULL AUTO_INCREEMENT,
  cust_name char(50) NOT NULL DEFAULT 'custA',
  cust_address char(50) NULL,
  PRIMARY KEY(cust_id)
) ENGINE=InnoDB;

数据库引擎

InnoDB 可靠的事务处理引擎，他不支持全文本搜索。

MEMORY在与功能上等同于MyISAM，但由于数据存储在内存，所以速度快。

MyISAM是一个性能极高的引擎，他支持全文本搜索，但不支持事务。

修改表

常用于定义外键

1
2
3
ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders
FOREIGN KEY (order_num) REFERENCES orders (order_num);

删除表

1
DROP TABLES customers2;

重命名表

1
RENAME TABLE customers2 TO customers;

使用视图

视图是虚拟的表

为什么使用视图

  • 重用SQL语句

  • 简化复杂的SQL操作

  • 使用表的组成部分而不是整个表

  • 保护数据

  • 更改数据格式和表示

视图的规则和限制

  • 与表一样，视图必须唯一命名
  • 对于可以穿件的视图数目咩有限制
  • 为了创建视图，必须有足够的权限
  • 视图可以嵌套
  • ORDER BY可以被用于视图
  • 视图不能索引，不能由关联的触发器和默认值
  • 视图可以和表一起使用

使用视图

  • 视图用CREATE VIEW 语句创建

  • 使用SHOW CREATE VIEW viewname来查看创建视图的语句

  • 使用DROP删除视图

  • 更新视图时，先用DROP再用CREATE，也可以直接使用CREATE OR REPLACE VIEW

用视图简化复杂的联结
用视图重新格式化检索出的数据
用视图过滤不想要的数据

使用存储过程

批处理保存一条或多条MYSQL语句的集合

使用游标

使用触发器

管理事务处理

全球化和本地化

安全管理

数据库维护

改善性能