MySQL总结笔记1-必知必会

最新推荐文章于 2022-04-25 17:23:47 发布

Kangvcar Blogs

最新推荐文章于 2022-04-25 17:23:47 发布

阅读量654

点赞数

分类专栏： Linux Mysql 文章标签： mysql 数据库数据

本文链接：https://blog.csdn.net/kangvcar/article/details/72774109

版权

Linux 同时被 2 个专栏收录

46 篇文章 0 订阅

订阅专栏

Mysql

7 篇文章 0 订阅

订阅专栏

此笔记总结《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数据库

Kangvcar Blogs

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL总结笔记1-必知必会

此笔记总结《MySQL必知必会》基本环境说明：此笔记是学习《MySQL必知必会》后的总结，用于个人日后回顾和参考。此笔记的数据库数据由本书作者提供，使用的MySQL版本为5.5.35-MariaDB在CentOS 7 系统下。此笔记数据库数据脚本环境部署[root@kangvcar ~]# yum -y install maridb mariadb-server //安装mysql客户端和
复制链接

扫一扫