数据库 - MySQL
1. CentOS 下 MySQL 的安装
-
在官网下载相应的 rpm 软件包,并安装
wget ... yum -y install ...
-
更新安装的软件
yum update
-
安装 MySQL server
yum install mysql-server
-
运行 mysqld 并查看运行状态
systemctl start mysqld systemctl status mysqld
-
查找默认密码
grep "password" /var/log/mysqld.log
-
以管理员身份进入mysql
mysql -u root -p
-
修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new password';
-
查看MySQL的所有用户及可连接的IP
SELECT host, user FROM mysql.user; -- 结果显示类似于: +-----------+------------------+ | host | user | +-----------+------------------+ | % | root | | localhost | mysql.infoschema | | localhost | mysql.session | | localhost | mysql.sys | | localhost | root | +-----------+------------------+ 5 rows in set (0.00 sec) -- 其中,% 表示所有IP均可访问,loocalhost 表示本地用户才可以访问
-
开通外网访问,% 表示所有 ip 均可访问,可改为指定 ip
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '你的密码' WITH GRANT OPTION; --已失效 -- 改为 create user 'root'@'%' identified by 'password'; -- 创建用户 root grant all privileges on *.* to 'root'@'%'; -- 赋予 root 用户权限
-
更新规则
flush privileges;
2. 单一表格检索
2.1 常用
USE sql_store;
SELECT *
FROM customers
WHERE customer_id = 1
ORDER BY first_name
2.2 SELECT
SELECT
points,
points * 10 as 'discount factor'
FROM customers
SELECT DISTINCT state
FROM customers
2.3 WHERE
USE sql_store;
SELECT *
FROM customers
WHERE birth_date > '1973-11-03' AND points > 2000
SELECT *
FROM customers
WHERE points <> 947
SELECT *
FROM customers
WHERE state IN ('MA', 'TX', 'VA')
SELECT *
FROM customers
WHERE points BETWEEN 1000 AND 3000
SELECT *
FROM customers
WHERE last_name LIKE '%b%'
SELECT *
FROM customers
WHERE last_name LIKE 'b_____'
SELECT *
FROM customers
WHERE last_name REGEXP 'b' -- 查找带有 b
WHERE last_name REGEXP 'b$' -- 以 b 结尾
WHERE last_name REGEXP '^b' -- 以 b 开头
WHERE last_name REGEXP '^b|b$' -- 以 b 开头或结尾
WHERE last_name REGEXP '[abc]b' -- b 前面至少有 abc 中之一
WHERE last_name REGEXP '[a-c]b' -- 同上
SELECT *
FROM customers
WHERE phone IS NULL
2.4 ORDER BY
USE sql_store;
SELECT *, quantity * unit_price AS total_price
FROM order_items
ORDER BY total_price DESC -- DESC 表示倒序
2.5 LIMIT
USE sql_store;
SELECT *, quantity * unit_price AS total_price
FROM order_items
ORDER BY total_price DESC
LIMIT 6, 3 -- 忽略前 6 项后取 3 项
3. 多张表结合检索
3.1 内连接 INNER JOIN
USE sql_store;
SELECT c.customer_id, first_name, last_name, comments -- 合并处需注明
FROM orders o -- 简称
JOIN customers c ON o.customer_id = c.customer_id -- 两张表的合并方式
3.2 跨数据库连接 Joining Across Databases
USE sql_store;
SELECT *
FROM sql_inventory.products p -- 跨数据库需要用前缀指名
JOIN order_items oi ON p.product_id = oi.product_id
3.3 自连接 Self Joins
USE sql_hr;
SELECT e.employee_id, e.first_name, m.employee_id, m.first_name AS manager
FROM employees e
JOIN employees m ON e.reports_to = m.employee_id
3.4 多表连接 Joining Multiple Tables
use sql_store;
SELECT c.customer_id, c.address, c.last_name, o.order_date, os.name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_statuses os ON o.status = os.order_status_id
3.5 复合连接条件(有多个列作为 key)
use sql_store;
SELECT *
FROM order_items oi
JOIN order_item_notes oin
ON oi.order_id = oin.order_id
AND oi.product_id = oin.product_id
3.6 隐式连接 Implicit Join Syntax
USE sql_store;
SELECT c.customer_id, first_name, last_name, comments
FROM orders o, customers c
WHERE o.customer_id = c.customer_id
-- 尽量不要使用,因为忘记 WHERE 条件,两个表笛卡尔积
3.7 外连接 OUTER JOIN
use sql_store;
-- 1. 两张表的外连接
SELECT *
FROM orders o
LEFT JOIN customers c -- 保留 orders 中的所有记录
ON c.customer_id = o.customer_id
ORDER BY c.customer_id
SELECT *
FROM orders o
RIGHT JOIN customers c -- 保留 customers 中的所有记录
ON c.customer_id = o.customer_id
ORDER BY c.customer_id
-- 2. 多表的外连接
use sql_store;
SELECT *
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
LEFT JOIN shippers sh
ON o.shipper_id = sh.shipper_id
ORDER BY c.customer_id
-- 尽量使用 LEFT JOIN,以便于阅读和理解
-- 3. 自外连接
use sql_hr;
SELECT
e.employee_id,
e.first_name,
m.first_name as manager
FROM employees e
LEFT JOIN employees m
ON e.reports_to = m.employee_id
3.8 USING
USE sql_store;
SELECT *
FROM order_items oi
JOIN order_item_notes oin
USING (order_id, product_id)
3.9 自然连接 NATURAL JOIN
USE sql_store;
SELECT *
FROM customers c
NATURAL JOIN orders o
-- 由系统判断合并那些列,不建议使用
3.10 交叉连接 CROSS JOIN
USE sql_store;
SELECT
c.customer_id AS customer,
p.name AS product
FROM customers c
CROSS JOIN products p
-- 笛卡尔积
3.11 UNION
USE sql_store;
SELECT
order_id,
order_date,
'ACTIVE' AS status
FROM orders
WHERE order_date >= '2019-01-30'
UNION
SELECT
order_id,
order_date,
'ARCHIVED' AS status
FROM orders
WHERE order_date < '2019-01-30'
-- 结合多个查询结果
4. 更新表
4.1 列属性 Collomn Attributes
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1lQBjQCG-1647936792806)(C:\Users\15729\AppData\Roaming\Typora\typora-user-images\image-20220302193434689.png)]
名称 | 含义 |
---|---|
Datatype |
数据类型, VARCHAR 代表可变长度字符, 相比于CHAR(50) 可以节省空间 |
PK |
primary key 主键 |
NN |
Not NULL 不允许为空 |
AI |
Auto Increment 自动递增 |
Default/Expression |
默认值, 一般给非 NN 的列设置 |
4.2 插入行 Inserting a row
USE sql_store;
INSERT INTO customers (first_name, last_name, birth_date, address, city, state)
VALUE ('Fu', 'Yu', '1998-03-01', 'LiChuan', 'China', 'JX')
4.3 插入多行 Inserting multi-rows
USE sql_store;
INSERT INTO shippers (name)
VALUES ('shipper1'),
('shipper2'),
('shipper3'),
('shipper4')
4.4 插入分层行 Inserting hierarchicals rows
INSERT INTO orders (customer_id, order_date, status)
VALUE (1, '2020-3-2', 1);
INSERT INTO order_items
VALUES (LAST_INSERT_ID(), 1, 2, 2.95),
(LAST_INSERT_ID(), 2, 1, 3.95)
4.5 复制创建表 Create a Copy a Table
USE sql_invoicing;
C