在公司和开发过程中最常用的SQL语句大全

       

         在公司和开发过程中，SQL（结构化查询语言）是用于与数据库交互的关键工具。无论你是开发人员、数据分析师还是数据库管理员，掌握常用的SQL语句都是必不可少的。本文将介绍一些在日常工作和开发中最常用的SQL语句，包括查询、插入、更新和删除数据等操作。

1. 数据库基本操作

创建数据库

CREATE DATABASE my_database;

删除数据库

DROP DATABASE my_database;

使用数据库

USE my_database;

2. 表操作

创建表

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

删除表

DROP TABLE employees;

修改表结构

添加列

ALTER TABLE employees ADD COLUMN hire_date DATE;

删除列

ALTER TABLE employees DROP COLUMN hire_date;

修改列的数据类型

ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12, 2);

3. 数据操作

插入数据

INSERT INTO employees (name, age, department, salary) 
VALUES ('John Doe', 30, 'Engineering', 60000.00);

查询数据

查询所有列

SELECT * FROM employees;

查询指定列

SELECT name, department FROM employees;

条件查询

SELECT * FROM employees WHERE age > 25;

排序查询

SELECT * FROM employees ORDER BY salary DESC;

分组查询

SELECT department, COUNT(*) AS employee_count 
FROM employees 
GROUP BY department;

聚合查询

SELECT AVG(salary) AS average_salary 
FROM employees;

更新数据

UPDATE employees 
SET salary = 65000.00 
WHERE name = 'John Doe';

批量更新数据

UPDATE employees
SET department = 'Sales'
WHERE department = 'Marketing';

删除数据

DELETE FROM employees 
WHERE name = 'John Doe';

4. 高级查询

子查询

SELECT name 
FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

联合查询

SELECT name, age FROM employees
UNION
SELECT name, age FROM managers;

连接查询

内连接

SELECT e.name, d.department_name 
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

左连接

SELECT e.name, d.department_name 
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

右连接

SELECT e.name, d.department_name 
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

交叉连接

SELECT e.name, d.department_name 
FROM employees e
CROSS JOIN departments d;

使用别名

SELECT e.name AS employee_name, d.name AS department_name 
FROM employees AS e
INNER JOIN departments AS d ON e.department_id = d.id;

5. 数据库管理

创建用户

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';

删除用户

DROP USER 'new_user'@'localhost';

授权用户

GRANT ALL PRIVILEGES ON my_database.* TO 'new_user'@'localhost';

撤销权限

REVOKE ALL PRIVILEGES ON my_database.* FROM 'new_user'@'localhost';

刷新权限

FLUSH PRIVILEGES;

        本文介绍了在公司和开发过程中最常用的SQL语句。这些SQL语句涵盖了从数据库和表的基本操作到数据的查询、插入、更新和删除等多个方面。在实际工作中，根据具体的业务需求和场景，灵活运用这些SQL语句，可以提高数据操作的效率和准确性。

希望这篇文章对你有所帮助，并能在实际工作中为你提供参考。如果你有任何问题或建议，欢迎在评论区留言。请记得一键三连（点赞、收藏、分享）哦！