此笔记总结《MySQL必知必会》
基本环境说明:此笔记是学习《MySQL必知必会》后的总结,用于个人日后回顾和参考。此笔记的数据库数据由本书作者提供,使用的MySQL版本为5.5.35-MariaDB在CentOS 7 系统下。
环境部署
[root@kangvcar ~]# yum -y install maridb mariadb-server //安装mysql客户端和服务端
[root@kangvcar ~]# systemctl restart mariadb //启动mysql服务
[root@kangvcar ~]# systemctl enable mariadb //设置开机自启动
[root@kangvcar ~]# mysql_secure_installation //进行初始化安装设置
[root@kangvcar ~]# mysql -uroot -p000000 //登录数据库
MariaDB [(none)]> create database kangvcar; //创建数据库用于导入数据
MariaDB [(none)]> use kangvcar; //设置当前使用的数据库
MariaDB [kangvcar]> source create.sql; //导入create.sql脚本用于创建表
MariaDB [kangvcar]> source populate.sql //导入populate.sql脚本用于填充表
MariaDB [kangvcar]> show tables; //查看导入的表
+--------------------+
| Tables_in_kangvcar |
+--------------------+
| customers |
| orderitems |
| orders |
| productnotes |
| products |
| vendors |
+--------------------+
表关系结构说明
表.vendors的列
列 | 含义 | 说明 |
---|---|---|
vend_id | 唯一的供应商ID | 主键,自增 |
vend_name | 供应商名 | |
vend_address | 供应商的地址 | |
vend_city | 供应商的城市 | |
vend_state | 供应商的州 | |
vend_zip | 供应商的邮政编码 | |
vend_country | 供应商的国家 |
表.products的列
列 | 含义 | 说明 |
---|---|---|
prod_id | 唯一的产品ID | 主键 |
vend_id | 产品供应商ID | 外键 |
prod_name | 产品名 | |
prod_price | 产品价格 | |
prod_desc | 产品描述 |
表.customers的列
列 | 含义 | 说明 |
---|---|---|
cust_id | 唯一顾客ID | 主键,自增 |
cust_name | 顾客名 | |
cust_address | 顾客的地址 | |
cust_city | 顾客的城市 | |
cust_state | 顾客的州 | |
cust_zip | 顾客的邮编 | |
cust_country | 顾客的国家 | |
cust_contact | 顾客的联系名 | |
cust_email | 顾客的email |
表.orders的列
列 | 含义 | 说明 |
---|---|---|
order_num | 唯一订单号 | 主键,自增 |
order_date | 订单日期 | |
cust_id | 顾客ID | 外键 |
表.orderitems的列
列 | 含义 | 说明 |
---|---|---|
order_num | 订单号 | 联合主键,外键 |
order_item | 订单物品号 | 联合主键 |
prod_id | 产品ID | 外键 |
quantity | 物品数量 | |
item_price | 物品价格 |
表.productnotes的列
列 | 含义 | 说明 |
---|---|---|
note_id | 唯一注释ID | 主键 |
prod_id | 产品ID | 外键 |
note_data | 添加注释的日期 | |
note_text | 注释文本 |
USE
MariaDB [(none)]> USE kangvcar; //选择kangvcar数据库为当前数据库
SHOW
MariaDB [kangvcar]> SHOW DATABASES; //查看数据库
MariaDB [kangvcar]> SHOW TABLES; //查看表
MariaDB [kangvcar]> SHOW STATUS; //查看服务器状态信息
MariaDB [kangvcar]> SHOW COLUMNS FROM customers; //查看表结构,列信息
MariaDB [kangvcar]> DESC customers; //查看表结构,列信息
SELECT,DISTINCT,LIMIT
MariaDB [kangvcar]> SELECT prod_name
-> FROM products; //检索单个列
MariaDB [kangvcar]> SELECT prod_id, prod_name, prod_price
-> FROM products; //检索多个列
MariaDB [kangvcar]> SELECT *
-> FROM products; //检索所有列
MariaDB [kangvcar]> SELECT DISTINCT vend_id
-> FROM products; //检索不包含相同的行
MariaDB [kangvcar]> SELECT prod_name
-> FROM products
-> LIMIT 5; //限制返回前5行
MariaDB [kangvcar]> SELECT prod_name
-> FROM products
-> LIMIT 5,5; //限制返回第5行开始的后5行
SELECT,ORDER BY,DESC
MariaDB [kangvcar]> SELECT prod_name
-> FROM products
-> ORDER BY prod_name; //按单个列排序所返回的结果集
MariaDB [kangvcar]> SELECT prod_id, prod_price, prod_name
-> FROM products
-> ORDER BY prod_price, prod_name; //按多个列排序所返回的结果集
MariaDB [kangvcar]> SELECT prod_id, prod_price, prod_name
-> FROM products
-> ORDER BY prod_price DESC; //以降序按prod_price排列结果集
SELECT,WHERE,BETWEEN
MariaDB [kangvcar]> SELECT prod_name, prod_price
-> FROM products
-> WHERE prod_price = 2.50; //过滤数据(可用=,<>,!=,<,<=,>,>=,BETWEEN进行过滤)
MariaDB [kangvcar]> SELECT prod_name, prod_price
-> FROM products
-> WHERE prod_price BETWEEN 5 AND 10; //BETWEEN指定范围过滤数据
MariaDB [kangvcar]> SELECT cust_id
-> FROM customers
-> WHERE cust_email IS NULL; //空值检查过滤数据
SELECT,WHERE,AND,OR,IN,NOT
MariaDB [kangvcar]> SELECT prod_id, prod_price, prod_name
-> FROM products
-> WHERE vend_id = 1003 AND prod_price <= 10; //AND操作符连接过滤条件
MariaDB [kangvcar]> SELECT prod_name, prod_price
-> FROM products
-> WHERE vend_id = 1002 OR vend_id = 1003; //OR操作符连接过滤条件
MariaDB [kangvcar]> SELECT prod_name, prod_price
-> FROM products
-> WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10; //()改变优先级过滤条件
MariaDB [kangvcar]> SELECT prod_name, prod_price
-> FROM products
-> WHERE vend_id IN (1002,1003) //IN 指定过滤条件
-> ORDER BY prod_name; //ORDER BY 排序结果集
MariaDB [kangvcar]> SELECT prod_name, prod_price
-> FROM products
-> WHERE vend_id NOT IN (1002,1003)
-> ORDER BY prod_name;
SELECT,LIKE,%,_
MariaDB [kangvcar]> SELECT prod_id, prod_name
-> FROM products
-> WHERE prod_name LIKE 'jet%'; //like配合通配符过滤结果集
MariaDB [kangvcar]> SELECT prod_id, prod_name
-> FROM products
-> WHERE prod_name LIKE '%anvil%'; //like配合通配符过滤结果集
MariaDB [kangvcar]> SELECT prod_id, prod_name
-> FROM products
-> WHERE prod_name LIKE 's%e'; //like配合通配符过滤结果集
MariaDB [kangvcar]> SELECT prod_id, prod_name
-> FROM products
-> WHERE prod_name LIKE '_ ton anvil'; //like配合通配符过滤结果集
SELECT,REGEXP
MariaDB [kangvcar]> SELECT prod_name
-> FROM products
-> WHERE prod_name REGEXP '1000'
-> ORDER BY prod_name; //正则表达式过滤结果集
MariaDB [kangvcar]> SELECT prod_name
-> FROM products
-> WHERE prod_name REGEXP '.000'
-> ORDER BY prod_name; //正则表达式过滤结果集
MariaDB [kangvcar]> SELECT prod_name
-> FROM products
-> WHERE prod_name REGEXP '1000|2000'
-> ORDER BY prod_name; //正则表达式过滤结果集
MariaDB [kangvcar]> SELECT prod_name
-> FROM products
-> WHERE prod_name REGEXP '[123] Ton'
-> ORDER BY prod_name; //正则表达式过滤结果集
MariaDB [kangvcar]> SELECT prod_name
-> FROM products
-> WHERE prod_name REGEXP '1|2|3 Ton'
-> ORDER BY prod_name; //正则表达式过滤结果集
MariaDB [kangvcar]> SELECT prod_name
-> FROM products
-> WHERE prod_name REGEXP '[1-5] Ton'
-> ORDER BY prod_name; //正则表达式过滤结果集
MariaDB [kangvcar]> SELECT vend_name
-> FROM vendors
-> WHERE vend_name REGEXP '\\.'
-> ORDER BY vend_name; //正则表达式过滤结果集
SELECT,Concat(),计算列
MariaDB [kangvcar]> SELECT Concat(vend_name,'(',vend_country,')')
-> FROM vendors
-> ORDER BY vend_name; //Concat()函数连接字符
MariaDB [kangvcar]> SELECT prod_id,
-> quantity,
-> item_price,
-> quantity*item_price AS expanded_price
-> FROM orderitems
-> WHERE order_num = 20005; //计算列
文本函数
Left(),Right(),Lower(),Upper(),LTrim(),RTrim(),Trim(),Length()
MariaDB [kangvcar]> SELECT vend_name, Upper(vend_name) AS vend_name_upcase
-> FROM vendors
-> ORDER BY vend_name; //Upper()函数
日期时间函数
AddDate(), AddTime(), CurDate(), CurTime(), Date(), Day(), Month(), Year(), Hour(), Minute(), Second(), Now()
MariaDB [kangvcar]> SELECT cust_id, order_num
-> FROM orders
-> WHERE Year(order_date) = 2005 AND Month(order_date) = 9; // Year()函数
数值处理函数
Abs(), Cos(), Exp(), Mod(), Pi(), Rand(), Sin(), Sqrt(), Tan()
汇聚函数AVG(), COUNT(), MAX(), MIN(), SUM()
MariaDB [kangvcar]> SELECT AVG(prod_price) AS avg_price
-> FROM products
-> WHERE vend_id = 1003; //AVG()函数
MariaDB [kangvcar]> SELECT AVG(DISTINCT prod_price) AS avg_price
-> FROM products
-> WHERE vend_id = 1003; //AVG()函数
MariaDB [kangvcar]> 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; //AVG(), COUNT(), MAX(), MIN(), SUM()函数
过滤分组,GROUP BY, HAVING
MariaDB [kangvcar]> SELECT vend_id, COUNT(*) AS num_prods
-> FROM products
-> GROUP BY vend_id; //GROUP BY分组结果集
MariaDB [kangvcar]> SELECT vend_id, COUNT(*) AS num_prods
-> FROM products
-> WHERE prod_price >= 10
-> GROUP BY vend_id
-> HAVING COUNT(*) >= 2; //GROUP BY分组过滤结果集
MariaDB [kangvcar]> SELECT order_num, SUM(quantity*item_price) AS ordertotal
-> FROM orderitems
-> GROUP BY order_num
-> HAVING SUM(quantity*item_price) >= 50
-> ORDER BY ordertotal; //GROUP BY分组过滤排序结果集
联结查询,INNER JOIN ,LEFT OUTER JOIN,RIGHT OUTER JOIN
MariaDB [kangvcar]> SELECT vend_name, prod_name, prod_price
-> FROM vendors, products
-> WHERE vendors.vend_id = products.vend_id
-> ORDER BY vend_name, prod_name; //联结两个表进行查询
MariaDB [kangvcar]> SELECT vend_name, prod_name, prod_price
-> FROM vendors INNER JOIN products
-> ON vendors.vend_id = products.vend_id; //内部联结
MariaDB [kangvcar]> 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; //多表联结
MariaDB [kangvcar]> 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'; //使用表别名
MariaDB [kangvcar]> SELECT customers.cust_id, orders.order_num
-> FROM customers LEFT OUTER JOIN orders
-> ON customers.cust_id = orders.cust_id; //外部联结
组合查询,UNION
MariaDB [kangvcar]> 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); //组合查询
MariaDB [kangvcar]> 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; //对组合查询结果排序
全文搜索,Match(), Against()
MariaDB [kangvcar]> SELECT note_text
-> FROM productnotes
-> WHERE Match(note_text) Against('rabbit'); //Match指定搜索的列,Against指定搜索关键字
MariaDB [kangvcar]> SELECT note_text
-> FROM productnotes
-> WHERE Match(note_text) Against('rabbit' WITH QUERY EXPANSION); //查询扩展
MariaDB [kangvcar]> SELECT note_text
-> FROM productnotes
-> WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE); //布尔文本搜索
插入数据,INSERT
MariaDB [kangvcar]> INSERT INTO customers
-> VALUES(NULL,
-> 'Pep E. LaPeW',
-> '100 Main Street',
-> 'Los Angeles',
-> 'CA',
-> '90046',
-> 'USA',
-> NULL,
-> NULL); //所有列都要指定值
MariaDB [kangvcar]> 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'); //指定列插入值
MariaDB [kangvcar]> 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'
); //插入多行值
更新数据, UPDATE
MariaDB [kangvcar]> UPDATE customers
-> SET cust_name = 'The Fudds',
-> cust_email = 'elmer@fudd.com'
-> WHERE cust_id = 10005; //更新多行数据
删除数据,DELETE
MariaDB [kangvcar]> DELECT FROM customers
-> WHERE cust_id = 10006; //删除指定行数据
MariaDB [kangvcar]> TRUNCATE TABLE; //删除所有行
创建表,CREATE TABLE
MariaDB [(none)]> CREATE TABLE orderitems
-> (
-> order_num int NOT NULL,
-> order_item int NOT NULL,
-> prod_id char(10) NOT NULL,
-> quantity int NOT NULL DEFAULT 1, //设置默认值
-> item_price decimal(8,2) NOT NULL,
-> PRIMARY KEY (order_num, order_item) //设置主键
-> )ENGINE=InnoDB; //设置数据库引擎
更新表,ALTER TABLE
MariaDB [(none)]> ALTER TABLE vendors
-> ADD vend_phone CHAR(20); //添加列
MariaDB [(none)]> ALTER TABLE vendors
-> DROP COLUMN vend_phone; //删除列
MariaDB [(none)]> ALTER TABLE orderitems
-> ADD CONSTRAINT fk_orderitems_orders
-> FOREIGN KEY (order_num) REFERENCES orders (order_num); //设置外键
删除表,DROP TABLE
MariaDB [(none)]> DROP TABLE customers; //删除表
重命名表,RENAME TABLE
MariaDB [(none)]> RENAME TABLE customers2 TO customers; //重命名表
创建视图,CREATE VIEW
MariaDB [(none)]> 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; //创建视图
MariaDB [kangvcar]> SELECT cust_name, cust_contact
-> FROM productcustomers
-> WHERE prod_id = 'TNT2'; //使用视图
创建触发器,CREATE TRIGGER
说明:触发器就是定义一段语句用于在某个条件成立触发执行。
种类:
INSERT触发器,DELETE触发器,UPDATE触发器
事务处理
说明:事务处理用来维护数据库的完整性,他保证成批的MySQL语句要么完全执行,要么完全不执行。
安全管理
MariaDB [(none)]> USE mysql;
MariaDB [(none)]> SELECT user FROM user; //查询用户列表
MariaDB [(none)]> CREATE USER ben IDENTIFIED BY 'PASSWORD'; //创建用户并设定密码
MariaDB [(none)]> RENAME USER ben TO beforta; //重命名用户
MariaDB [(none)]> DROP USER bforta; //删除用户
MariaDB [(none)]> SHOW GRANTS FOR beforta; //查看用户的权限
MariaDB [(none)]> GRANT SELECT ON kangvcar.* TO beforta; //授权某数据库的指定权限给用户
MariaDB [(none)]> REVOKE SELECT ON kangvcar.* FROM beforta; //撤销用户的某数据库的指定权限
MariaDB [(none)]> SET PASSWORD FOR beforta = Password('123123'); //修改密码
备份,还原数据库mysqldump
[root@kangvcar ~]# mysqldump -uroot -p000000 kangvcar > /root/kangvcarDB.dump //备份kangvcar数据库
[root@kangvcar ~]# mysqldump -uroot -p000000 kangvcar < /root/kangvcarDB.dump //还原kangvcar数据库