MySql学习
1. 连接与退出
登录 MySQL
mysql -u 用户名 -p密码 数据库[可选] -P 端口 -h IP
输入上述命令后，系统会提示输入密码。
示例：
mysql -u root -p123456 mysql -P 3306 -h 192.168.1.1
指定数据库登录
mysql -u 用户名 -p 数据库名
示例：
mysql -u root -p test_db
退出 MySQL
在 MySQL 提示符下输入：
exit;
或
quit;
2. 数据库操作
显示所有数据库
SHOW DATABASES;
创建数据库
CREATE DATABASE 数据库名;
示例：
CREATE DATABASE my_database;
删除数据库
DROP DATABASE 数据库名;
示例：
DROP DATABASE my_database;
使用指定数据库
USE 数据库名;
示例：
USE my_database;
3. 表操作
显示当前数据库中的所有表
SHOW TABLES;
显示表的列
SHOW COLUMNS FROM customers;
或者
DESC customers;
创建表
CREATE TABLE 表名 (
列名1 数据类型 约束,
列名2 数据类型 约束,
...
);
示例：
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
删除表
DROP TABLE 表名;
示例：
DROP TABLE users;
查看表结构
DESCRIBE 表名;
或
DESC 表名;
示例：
DESCRIBE users;
4. 数据操作
插入数据
INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...);
示例：
INSERT INTO users (username, email) VALUES ('john_doe', '[email protected]');
查询数据
SELECT 列1, 列2, ... FROM 表名 [WHERE 条件] [ORDER BY 列 ASC|DESC] [LIMIT 数量];
示例：
SELECT * FROM users;
SELECT username, email FROM users WHERE id > 10 ORDER BY created_at DESC LIMIT 5;
更新数据
UPDATE 表名 SET 列1 = 新值1, 列2 = 新值2 WHERE 条件;
示例：
UPDATE users SET email = '[email protected]' WHERE username = 'john_doe';
删除数据
DELETE FROM 表名 WHERE 条件;
示例：
DELETE FROM users WHERE id = 5;
5. 用户与权限管理
创建新用户
CREATE USER '用户名'@'主机' IDENTIFIED BY '密码';
示例：
CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password123';
授权
GRANT 权限 ON 数据库.表 TO '用户名'@'主机';
授予数据库my_database的权限：
GRANT ALL PRIVILEGES ON my_database.* TO 'new_user'@'localhost';
授予所有数据库的权限：
GRANT ALL PRIVILEGES ON *.* TO 'new_user'@'localhost';
刷新权限
FLUSH PRIVILEGES;
撤销权限
REVOKE 权限 ON 数据库.表 FROM '用户名'@'主机';
示例：
REVOKE ALL PRIVILEGES ON my_database.* FROM 'new_user'@'localhost';
删除用户
DROP USER '用户名'@'主机';
示例：
DROP USER 'new_user'@'localhost';
查询用户
SELECT User, Host FROM mysql.user;
6. 备份与恢复
导出数据库（备份）
使用 mysqldump 工具：
mysqldump -u 用户名 -p 数据库名 > 备份文件.sql
示例：
mysqldump -u root -p my_database > my_database_backup.sql
导入数据库（恢复）
mysql -u 用户名 -p 数据库名 < 备份文件.sql
示例：
mysql -u root -p my_database < my_database_backup.sql
导入数据库文件
source 文件位置
示例：
source /Users/kai/Desktop/atguigudb.sql;
7. 其他常用命令
显示当前使用的数据库
SELECT DATABASE();
查看当前用户
SELECT USER();
显示当前活动的进程
SHOW PROCESSLIST;
杀死指定进程
KILL 进程ID;
示例：
KILL 12345;
显示服务器状态
SHOW STATUS;
显示服务器变量
SHOW VARIABLES;
更改数据库字符集
ALTER DATABASE 数据库名 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
示例：
ALTER DATABASE my_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
8. 高级查询
使用 JOIN 进行表连接
SELECT 列1, 列2, ...
FROM 表1
JOIN 表2 ON 表1.列 = 表2.列
WHERE 条件;
示例：
SELECT users.username, orders.order_date
FROM users
JOIN orders ON users.id = orders.user_id
WHERE users.id = 1;
分组与聚合函数
SELECT 列, COUNT(*) FROM 表名 GROUP BY 列;
示例：
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;
子查询
SELECT 列 FROM 表 WHERE 列 IN (SELECT 列 FROM 表 WHERE 条件);
示例：
SELECT username FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);
这些命令涵盖了 MySQL 日常使用中的大部分场景。根据具体需求，您可能还需要深入学习更高级的功能和优化技巧。
附录1：命令合集
连接数据库
-- mysql -u 用户名 -p密码 数据库[可选] -P 端口 -h IP
mysql -u root -p -P 3307 -h 127.0.0.1
数据库
显示所有数据库
-- SHOW DATABASES;
show databases;
使用数据库
-- USE 数据库名;
use practice;
查看创建数据库的命令（如何创建的）
-- SHOW CREATE DATABASE 数据库名;
show create database practice;
查看当前使用的数据库
-- SELECT DATABASE();
select database();
数据表
查看当前数据库下的所有表
-- SHOW TABLES;
show tables;
查看某个表下的所有列
-- DESCRIBE 表名;
-- 或者
-- SHOW COLUMNS FROM customers;
show create table customers;
-- 或者
desc table customers;
创建一个表
/*
CREATE TABLE 表名 (
列名1 数据类型 约束,
列名2 数据类型 约束,
...
);
*/
create table users(
id int auto_increment primary key,
username varchar(50) not null,
email varchar(50) not null,
create_at timestamp default current_timestamp
);
查看创建某个表的命令（如何创建的）
-- SHOW CREATE TABLE 表名;
show create table customers;
删除某个表
-- DROP TABLE 表名;
drop table users;
数据操作
新增数据
-- INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...);
insert into users (username, email) values ('EmccK', '[email protected]');
删除数据
-- DELETE FROM 表名 [WHERE 条件]
-- 不加条件则是删除所有
delete from users where id = 2
修改数据
-- UPDATE 表名 SET 列1 = 新值1, 列2 = 新值2 WHERE 条件;
update users set username = 'John' where id = 6;
查询数据
-- SELECT 列1, 列2, ... FROM 表名 [WHERE 条件] [ORDER BY 列 ASC|DESC] [LIMIT 数量];
-- 所有列表：SELECT *
-- 排序：ORDER BY，ASC升序（默认从小到大），DESC降序
-- 限制数量：LIMIT
select username, email from users order by email asc limit 1;
条件查询数据
查询所有数据
-- SELECT * FROM 数据表;
select * from products;
查找之后去重
-- DISTINCT关键字
select distinct vend_id from products;
-- 同时两列不同
select distinct vend_id, prod_price from products;
查找限制几行
-- LIMIT 数量，限制只有固定数量行
select * from products limit 5;
-- LIMIT 起点索引（包含）, **数量**
select * from products limit 5, 10;
排序数据
-- ORDER BY [列1] ASC(升序，默认值，可以不写)/DESC(降序), [列表2] ASC(升序，默认值，可以不写)/DESC(降序)
select * from products order by prod_price;
select * from products order by prod_price desc;
select * from products order by prod_price asc, prod_name desc;
-- 在大多数数据库中，A和a是相同的
过滤数据
-- WHERE关键字
/*
= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
BETWEEN AND 在指定的两个值之间，前后都包括
*/
查询某一列是NULL
-- WHERE 列表 IS NULL
select * from users where email is null;
组合查找
-- 使用 AND 和 OR 可以组合处理多个条件
select prod_id, prod_price, prod_name from products where vend_id = 1003 and prod_price <= 10 order by prod_price;
select * from products where vend_id = 1002 or vend_id = 1003;
-- AND和OR放一起的时候，会优先处理AND语句，再处理OR语句
-- 如果是：列出价格为10美元（含）以上且由1002或1003制造的所有产品，则下面的就是错误的
select * from products where vend_id = 1002 or vend_id = 1003 and prod_price >= 10;
-- 正确的是需要添加括号
select * from products where (vend_id = 1002 or vend_id = 1003) and prod_price >= 10 order by prod_price;
-- IN (1002, 1003) 等价于 vend_id = 1002 OR vend_id = 1003,
-- IN里面是一个列表，IN比OR更快，更容易管理
select * from products where vend_id in (1002, 1003) order by prod_price;
否定查找
-- NOT WHERE子句中用来否定后跟条件的关键字
select * from products where vend_id not in (1002, 1003) order by vend_id;
字符串查找，通配符&正则
查找`prod_name`以`jet`开头的产品-匹配多字符
-- 可以使用 LIKE 关键字，后面跟通配符，不区分大小写
-- 可以使用%查询，%表示任何字符出现任意次数**(0,1,以及任意)**
SELECT * FROM products WHERE prod_name LIKE 'jet%';
-- %可以同时位于两端
SELECT * FROM products WHERE prod_name LIKE '%anvil%';
通配符查找，匹配单字符
-- 可以使用 _ 匹配单字符，使用起来跟%差不多
SELECT * FROM products WHERE prod_name LIKE '_ ton anvil';
最好和其他条件配合查找，因为通配符的查找会比较慢一点
正则表达式查找
-- 使用关键字<u>**REGEXP**</u>，代替LIKE，使用起来是一样的，后面跟正则表达式，这个也是不区分大小写。
-- 正则表达式中的<u>**.**</u>可以匹配任意<u>**一个**</u>字符
SELECT * FROM products WHERE prod_name REGEXP '.000';
-- 如果需要匹配特殊字符，则需要添加<u>\\</u>为前导。<u>**\\-**</u>为查找<u>**-**</u>字符，<u>**\\.**</u>为查找<u>**.**</u>字符
SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.';
拼接字段
查找供应商和地区，并拼接在一块name(location)的样式
-- MySQL中使用Concat()拼接字符串
SELECT Concat(vend_name, '(', vend_country, ')') FROM vendors ORDER BY vend_name;
-- 使用函数Trim()来去除数据左右两侧多余的空格，RTrim()则是去掉右侧多余的空格，LTrim()则是去掉左侧多余的空格
SELECT Concat(Rtrim(vend_name), '(', Rtrim(vend_country), ')') FROM vendors ORDER BY vend_name;
拼接字段之后设置别名
-- 上述拼接完之后，如果不设置别名的话，其他人是没办法使用的，可以使用AS关键字设置别名
SELECT Concat(Trim(vend_name), '(', Trim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name;
计算`orderitems`表里面`order_num`是20005的产品的每一项的总价，命名为`expanded_price`
-- 计算的话，可以直接使用+-*/
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM orderitems WHERE order_num = 20005;
常用函数
字母转换成大写
-- Upper()方法，将所有小写字母转换成大写字母
SELECT vend_name, Upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;
常用的一些函数
-- Left() 返回字符串左边的字符
-- Length() 返回字符串的长度
-- Locate() 找出串的一个子串
-- Lower() 将串转换成小写
-- LTrim() 去掉左边的空格
-- Right() 返回串右边的字符
-- RTrim() 去掉右边的空格
-- Soundex() 返回串的SOUNDEX值
-- SubString() 返回字串的字符
-- Upper() 将串转换成大写
常用的时间函数
💡 时间格式默认最好是：yyyy-mm-dd
SELECT * FROM orders WHERE Year(order_date) = 2005 AND Month(order_date) = 9;
数值处理函数
汇总数据
常用的汇总函数
AVG函数
查询某一列的平均值，后面可以跟条件
-- 查询所有产品的平均值
SELECT AVG(prod_price) AS avg_price FROM products;
-- 查询vend_id为1003的产品的平均值
SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003;
COUNT函数
-- 查询某个表下数据总数
SELECT COUNT(*) AS num_cust FROM customers;
-- 只对有值的行计数，如果某一个行没有值，则不计数
SELECT COUNT(cust_email) AS num_cust FROM customers;
MAX函数和MIN函数
-- 查询最大值
SELECT MAX(prod_price) as max_price FROM products;
-- 查询最小值
SELECT MIN(prod_price) as min_price FROM products;
SUM函数
-- 计算某列下的所有值的和
SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;
-- 计算值
SELECT SUM(quantity*item_price) AS total_price FROM orderitems WHERE order_num = 20005;
-- 去除重复的值
SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 1003;
组合聚集函数
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;
分组数据
返回每个供应商提供的产品数目
SELECT vend_id COUNT(*) AS num_prods FROM products GROUP BY vend_id;
设置分组过滤条件
SELECT vend_id COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*) >= 3;
列出具有2个（含）以上、价格为10（含）以上的产品的供应商
SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING num_prods >= 2 ORDER BY num_prods;
查找总计订单价格大于等于50的订单的订单号和总计订单价格，`orderitems`
SELECT order_num, SUM(item_price * quantity) AS ordertotal FROM orderitems GROUP BY order_num HAVING ordertotal >= 50 ORDER BY ordertotal;
子查询
要列出订购物品TNT2的所有客户
1. 检索包含物品TNT2的所有订单的编号`orderitems` 2. 检索具有前一步骤列出的订单编号的所有客户的ID`orders` 3. 检索前一步骤返回的所有客户ID的客户信息-- 1. 检索包含物品TNT2的所有订单的编号
SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';
-- 得到结果：20005, 20007
-- 2. 检索具有前一步骤列出的订单编号的所有客户的ID
SELECT cust_id FROM orders WHERE order_num IN (20005, 20007);
-- 得到结果：10001, 10004
-- 3. 检索前一步骤返回的所有客户ID的客户信息
SELECT * FROM customers WHERE cust_id IN (10001, 10004);
-- 1和2可以合并成一个语句
SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');
-- 1、2、3三个合并成一个子句
SELECT * FROM customers WHERE cust_id IN (SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'));
显示customers表中每个客户的订单总数
附录2：关键字
- SHOW：显示
- DATABASES：数据库
- TABLE：表
- COLUMN：列
- ROW：行
- PRIMARY KEY：主键
- CREATE：新建
- WHERE：哪个
- IS：是
- FROM：从哪里
- DELETE：删除数据
- LIKE：通配符匹配
- REGEXP：正则表达式匹配
- AS：设置别名
本文是原创文章，采用 CC BY-NC-ND 4.0 协议，完整转载请注明来自 EmccK
评论
匿名评论
隐私政策
你无需删除空行，直接评论以获取最佳展示效果