数据库 - MySQL基础知识

这篇博客详细介绍了MySQL的基础知识,包括在CentOS上安装MySQL,单一表格和多表检索,如何更新和汇总数据,以及视图、存储过程、触发器和事件的使用。还深入探讨了事务处理、数据类型、数据库设计、索引优化和数据库保护等关键概念。
摘要由CSDN通过智能技术生成

数据库 - 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
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值