史上最全SQL基础知识总结（理论+举例+10条吐血建议）

CREATE DATABASE TestDB;
DROP DATABASE TestDB;
ALTER DATABASE TestDB COLLATE Chinese_PRC_CI_AS;

CREATE TABLE NumTable (
    id INT,
    smallNum TINYINT,
    money DECIMAL(10, 2),
    size FLOAT
);
CREATE TABLE DateTable (
    date_column DATE,
    time_column TIME
);
CREATE TABLE StringTable (
    fixed_length_name CHAR(50),
    variable_length_address VARCHAR(255)
);

CREATE TABLE EmployeeTable (
    ID int,
    Name varchar(255),
    Age int
);
DROP TABLE EmployeeTable;
ALTER TABLE EmployeeTable ADD Email varchar(255);
ALTER TABLE EmployeeTable MODIFY COLUMN Age smallint;
ALTER TABLE EmployeeTable DROP COLUMN Age;
RENAME TABLE EmployeeTable TO StaffTable;

INSERT INTO employees (id, name, department_id) VALUES (1, 'Li Ming', 101);    
(向employees表中插入一条新的员工记录，id为1, 名字是'Li Ming', 部门id是101)
INSERT INTO employees (name, department_id) VALUES ('Wang Gang', 102);      
(向employees表中插入一条新的员工记录, id将自动生成，名字是'Wang Gang', 部门id是102)
INSERT INTO employees VALUES (3, 'Zhang San', 103);            
(向employees表中插入一条新的员工记录，id为3, 名字是'Zhang San', 部门id是103)

UPDATE employees SET department_id = 201 WHERE name = 'Li Ming';             
(修改名为'Li Ming'的员工的部门id为201)
UPDATE employees SET name = 'Liu Yan' WHERE id = 2;               
(修改id为2的员工的名字为'Liu Yan')
UPDATE employees SET name = 'Zhu Rongji', department_id = 202 WHERE id = 3;                  
(修改id为3的员工的名字为'Zhu Rongji'和部门id为202)

DELETE FROM employees WHERE id = 1;                
(删除id为1的员工记录)
DELETE FROM employees WHERE name = 'Wang Gang';                   
(删除名字为'Wang Gang'的员工记录)
DELETE FROM employees WHERE department_id = 202;                 
(删除部门id为202的所有员工记录)

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
（在"localhost"上创建一个名为"newuser"的新用户，密码是"password"。）

GRANT SELECT, INSERT, DELETE ON database_name.table_name TO 'username'@'localhost';
（在localhost上给"username"用户赋予运database_name数据库中table_name表的SELECT，INSERT和DELETE权限。）

REVOKE INSERT ON database_name.table_name FROM 'username'@'localhost';
（在localhost上撤销"username"用户对database_name数据库中table_name表的INSERT权限。）

SHOW GRANTS FOR 'username'@'localhost';
（显示"localhost"上"username"用户的所有权限。）

DROP USER 'username'@'localhost';
（删除"localhost"上名为"username"的用户。）

ALTER USER 'username'@'localhost' IDENTIFIED BY 'newpassword';
（修改在"localhost"上username用户的密码为"newpassword"。）

SELECT * 
FROM Employee;  
（查询Employees表中的所有记录）
SELECT FirstName, LastName 
FROM Employee;  
（查询Employee表中的FirstName和LastName字段所有记录）

SELECT * 
FROM Employee 
WHERE Salary > 5000; 
（查询Employee表中Salary大于5000的所有记录）
SELECT FirstName, LastName 
FROM Employee 
WHERE Age <= 30; 
（查询Employee表中年龄小于等于30的员工的firstName和lastName字段记录）
SELECT * 
FROM Employee 
WHERE Salary > 5000 
AND Age <= 30; 
（查询Employee表中Salary大于5000且年龄小于等于30的所有记录）
SELECT * 
FROM Employee 
WHERE Salary > 5000 
OR Age <= 30; 
（查询Employee表中Salary大于5000或年龄小于等于30的所有记录）

SELECT * 
FROM Employee 
WHERE FirstName LIKE 'John%'; 
（查询Employee表中FirstName以John开头的所有记录）
SELECT * 
FROM Employee 
WHERE FirstName LIKE '%John%'; 
（查询Employee表中FirstName包含John的所有记录）
SELECT FirstName, LastName 
FROM Employee 
WHERE LastName LIKE '%son_'; 
（查询Employee表中LastName以son+一个任意字符结束的所有记录）

SELECT DISTINCT City 
FROM Employee; 
（查询Employee表中，City字段去重之后的所有城市记录）
SELECT DISTINCT Age, Salary 
FROM Employee 
WHERE Age<50; 
（查询Employee表中，Age小于50岁的员工的Age和Salary字段组合记录，并进行去重处理）

SELECT * 
FROM Employee 
ORDER BY Salary; 
（按'工资'列的升序返回Employee表中的所有行。）

SELECT * 
FROM Employee 
ORDER BY Salary, Age DESC;
（首先根据'工资'列的升序对Employee表中的行进行排序，然后在工资相同的情况下，根据'Age'列的降序进行排序。）

SELECT COUNT(*) 
FROM Employee;
（返回Employee表的总行数。）

SELECT SUM(Salary) 
FROM Employee;
（返回Employee表中所有员工的薪水总和。）

SELECT MAX(Age) 
FROM Employee;
（返回Employee表中员工的最大年龄。）

SELECT MIN(Age) 
FROM Employee;
（返回Employee表中员工的最小年龄。）

SELECT AVG(Salary) 
FROM Employee;
（返回Employee表中员工的平均薪水。）

SELECT Department, COUNT(*) 
FROM Employee 
GROUP BY Department;
（返回每个部门的员工数量。）

SELECT Department, JobTitle, AVG(Salary) 
FROM Employee 
GROUP BY Department, JobTitle;
（返回每个部门及职位下员工的平均薪水。）

SELECT Department, JobTitle, AVG(Salary) 
FROM Employee 
GROUP BY Department, JobTitle
HAVING AVG(Salary) > 5000;
（返回每个部门及职位下员工的平均薪水大于5000的部分。）

SELECT * 
FROM Employee 
ORDER BY Salary DESC 
LIMIT 10;
（返回前10个工资最高的员工。）

SELECT * 
FROM Employee 
ORDER BY Salary DESC 
LIMIT 10 OFFSET 5;
（返回工资排名第6到第15的10个员工。）

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
RIGHT JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

SELECT Products.ProductName, Suppliers.SupplierName
FROM Products
CROSS JOIN Suppliers;