Mysql的数据库操作

MySQL是一种关系型数据库管理系统，它支持SQL语言进行数据的增删改查操作。下面列举出MySQL的常见操作及相关案例。

数据库操作

创建数据库

CREATE DATABASE database_name;

例如：

CREATE DATABASE mydb;

删除数据库

DROP DATABASE database_name;
例如：

DROP DATABASE mydb;

选择数据库

USE database_name;

例如：

USE mydb;

数据表操作

创建数据表

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  column3 datatype,
  ....
);

例如：

CREATE TABLE students (
  id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  age INT(11) NOT NULL,
  gender VARCHAR(10) NOT NULL
);

删除数据表

DROP TABLE table_name;

例如：

DROP TABLE students;

修改数据表

添加新的列

ALTER TABLE table_name
ADD column_name datatype;

例如：

ALTER TABLE students
ADD email VARCHAR(255) NOT NULL;

修改列属性

ALTER TABLE table_name
MODIFY column_name datatype;

例如：

ALTER TABLE students
MODIFY email VARCHAR(100) NOT NULL;

删除列

ALTER TABLE table_name
DROP COLUMN column_name;

例如：

ALTER TABLE students
DROP COLUMN email;

数据增删改查操作

插入数据

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

例如：

INSERT INTO students (name, age, gender)
VALUES ('Tom', 18, 'Male');

更新数据

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

例如：

UPDATE students
SET age = 19
WHERE name = 'Tom';

删除数据

DELETE FROM table_name WHERE condition;

例如：

DELETE FROM students WHERE name = 'Tom';

查询数据

查询所有数据

SELECT * FROM table_name;

例如：

SELECT * FROM students;

查询某些列的数据

SELECT column1, column2, ... FROM table_name;

例如：

SELECT name, age FROM students;

查询具有特定条件的数据

SELECT * FROM table_name WHERE condition;

例如：

SELECT * FROM students WHERE gender = 'Male';

对查询结果进行排序

SELECT * FROM table_name ORDER BY column_name ASC|DESC;

例如：

SELECT * FROM students ORDER BY age DESC;

限制查询结果

SELECT * FROM table_name LIMIT number;

例如：

SELECT * FROM students LIMIT 10;

以上是MySQL的基本增删改查操作。使用这些操作可以完成各种数据操作任务，例如，创建新数据库，创建新数据表，向数据表中添加和修改数据以及查询和删除数据等等。

子查询

在SQL中，子查询是一个嵌套的SELECT语句，用来为主查询提供一组过滤器。以下是一个基本的例子：

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT column_name FROM table_name WHERE condition);

例如：

SELECT customers.customerName, customers.city
FROM customers
WHERE customers.customerNumber IN (SELECT orders.customerNumber FROM orders WHERE orders.orderDate='2003-01-31');

此查询将返回在2003年1月31日下订单的客户的名称和城市。

自连表

自连表是一种特殊的表，其中一列连接到表本身的另一行。自连表用于存储具有父/子关系的数据，例如一棵树。以下是一个基本的例子：

SELECT employee.employeeName, manager.employeeName AS manager
FROM employee
LEFT JOIN employee manager ON employee.reportsTo = manager.employeeNumber;

此查询将返回员工的名称和他们的经理名称。

连表查询

连接查询是将两个或多个表中的行组合成一个结果集的查询。以下是一些常见的连接类型：

• INNER JOIN：只返回两个表中都有匹配项的行
• LEFT JOIN：返回左侧表中所有行，以及在右侧表中有匹配项的行
• RIGHT JOIN：返回右侧表中所有行，以及在左侧表中有匹配项的行
• FULL JOIN：返回两个表中所有行

以下是一个基本的INNER JOIN例子：

SELECT orders.orderID, customers.customerName
FROM orders
INNER JOIN customers ON orders.customerID = customers.customerID;

此查询将返回订单号和客户名称。

视图

视图是一种虚拟的表，它以查询的方式定义。视图不包含数据本身，而是从其定义查询的结果生成数据。以下是一个基本的例子：

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

例如：

CREATE VIEW customer_details AS
SELECT customers.customerName, orders.orderDate, orderdetails.quantity
FROM customers
INNER JOIN orders ON customers.customerID = orders.customerID
INNER JOIN orderdetails ON orders.orderID = orderdetails.orderID;

此查询将创建一个名为customer_details的视图，其中包含客户名称、订单日期和订单数量。

索引

索引是在MySQL表中的一个或多个列上创建的数据结构，用于加快数据检索速度。以下是一个基本的例子：

CREATE INDEX index_name
ON table_name (column_name);

例如：

CREATE INDEX idx_customerName
ON customers (customerName);

此查询将在customers表中创建一个名为idx_customerName的索引，它将在customerName列上加速数据检索速度。

去重

在MySQL中，可以使用DISTINCT关键字去重。以下是一个基本的例子：

SELECT DISTINCT column_name(s)
FROM table_name;

例如：

SELECT DISTINCT customerName
FROM customers;

此查询将返回唯一的客户名称列表

查看是否有重复数据

可以使用GROUP BY和HAVING语句来查看是否有重复数据。以下是一个基本的例子：

SELECT column_name(s), COUNT(*)
FROM table_name
GROUP BY column_name(s)
HAVING COUNT(*) > 1;

例如：

SELECT customerName, COUNT(*)
FROM orders
INNER JOIN customers ON orders.customerID = customers.customerID
GROUP BY customerName
HAVING COUNT(*) > 1;

此查询将返回客户名称和订单数量，仅包括具有多个订单的客户。

慢查询

可以使用MySQL的慢查询日志来记录查询执行时间超过某个阈值的查询。以下是一个基本的例子：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 5;

此设置将启用慢查询日志，并将查询执行时间阈值设置为5秒。

可以使用以下查询来查看慢查询日志：

SELECT *
FROM mysql.slow_log;

此查询将返回慢查询日志中的所有条目。

SHOW VARIABLES LIKE '%slow_query_log%';

如果慢查询日志已启用，则将出现以下结果：

+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| slow_query_log      | ON    |
| slow_query_log_file | path  |
+---------------------+-------+

在这里，路径将是慢查询日志应写入的文件名和位置。

如果慢查询日志尚未启用，则变量值将为OFF。要启用慢查询日志，请使用以下命令：

SET GLOBAL slow_query_log = 'ON';

上面的命令会立即启用慢查询日志。要更改MySQL慢查询日志的默认行为，可以更改以下变量：

slow_query_log_file：慢查询日志文件的名称。默认情况下，它是主机名后面加上-slow.log扩展名。

long_query_time：执行时间超过long_query_time秒的查询将被记录在慢查询日志中。

log_queries_not_using_indexes：如果启用，则MySQL将记录所有执行查询时未使用索引的查询。

log_output：指定日志记录输出的格式。

为了解决慢查询问题，可以采取以下步骤：

1.使用EXPLAIN命令分析查询，并确定是否存在性能问题。

2.优化查询的表结构和索引。

3.根据具体情况修改MySQL配置文件（如增加缓存或者优化缓存）。

4.在MySQL中使用速度更快的查询语句。

5.分析查询日志以发现性能问题，并采取相应措施。

总之，性能问题往往需要综合考虑数据库设计、索引和查询优化等多个方面，才能找到最佳解决方案。

条件语句查询  

在MySQL中，可以使用条件语句进行查询，其中包括AND和OR逻辑运算符。

AND运算符表示必须同时满足两个条件，例如：

SELECT * FROM table_name
WHERE condition1 AND condition2;

OR运算符表示至少满足其中一个条件，例如：

SELECT * FROM table_name
WHERE condition1 OR condition2;

SELECT * FROM table_name
WHERE (condition1 AND condition2) OR (condition3 AND condition4);

上述语句中，首先执行condition1和condition2的AND运算，然后执行condition3和condition4的AND运算，最后将两个组的结果用OR运算符合并。

 mysql where与having 的区别:

WHERE用于筛选记录，只能在基于列上做筛选（如：WHERE age > 18）；

 HAVING用于筛选分组，可以在计算聚合函数后，使用HAVING筛选分组（如：HAVING COUNT(*) > 10）。

mysql区间查询的案例：

 查询score在80到90之间的学生信息：

SELECT * FROM student WHERE score BETWEEN 80 AND 90;

 查询注册时间在一周内的用户信息：

SELECT * FROM user WHERE register_time >= DATE_SUB(NOW(), INTERVAL 7 DAY);

mysql算法查询的案例：

查询所有工资大于平均工资的员工信息：

SELECT * FROM employee WHERE salary > (SELECT AVG(salary) FROM employee);

 查询出现次数超过10次的IP地址及其访问次数：

SELECT ip, COUNT(*) AS count FROM log GROUP BY ip HAVING count > 10;

mysql定时查询、修改：

 使用EVENT命令创建定时任务：

CREATE EVENT event_name ON SCHEDULE every 1 hour DO SELECT * FROM table;

 使用TRIGGER命令实现定时修改：

CREATE TRIGGER trigger_name BEFORE DELETE ON table FOR EACH ROW BEGIN SET NEW.insert_time = NOW();

END;

mysql查询储存过程日志的案例：

开启MySQL日志功能：

SET global log_output = 'file';

SET global general_log_file = 'mysql.log';

SET global general_log = 1;

 查询储存过程相关日志信息：

SELECT * FROM mysql.general_log WHERE command_type = 'Query' AND argument LIKE '%PROCEDURE%';