赞
踩
SQL,全称Structured Query Language,即结构化查询语言,是用于管理关系数据库管理系统(RDBMS)的标准编程语言。SQL语言的主要功能包括数据查询(Data Query)、数据操纵(Data Manipulation)、数据定义(Data Definition)和数据控制(Data Control)。
CREATE语句用于创建数据库对象,如表、索引、视图等。以下是一些常见的用法。
CREATE DATABASE 数据库名;
CREATE DATABASE my_database;
CREATE TABLE 表名 (
列名1 数据类型,
列名2 数据类型,
...
);
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
department_id INT
);
CREATE [UNIQUE] INDEX 索引名 ON 表名 (列名);
CREATE INDEX idx_employees_name ON employees (name);
CREATE VIEW 视图名 AS
SELECT 列名1, 列名2, ...
FROM 表名
WHERE 条件;
CREATE VIEW employees_by_department AS
SELECT id, name, department_id
FROM employees
WHERE department_id = 1;
ALTER语句用于修改数据库对象的定义,如表的结构、索引等。以下是一些常见的用法。
ALTER TABLE 表名 ADD 列名 数据类型;
ALTER TABLE employees ADD salary DECIMAL(10, 2);
ALTER TABLE 表名 MODIFY 列名 新数据类型;
ALTER TABLE employees MODIFY age INT;
ALTER TABLE 表名 DROP COLUMN 列名;
ALTER TABLE employees DROP COLUMN department_id;
DROP语句用于删除数据库对象,如表、索引、视图等。以下是一些常见的用法。
DROP TABLE 表名;
DROP TABLE employees;
DROP INDEX 索引名 ON 表名;
DROP INDEX idx_employees_name ON employees;
DROP VIEW 视图名;
DROP VIEW employees_by_department;
SELECT * FROM 表名;
SELECT * FROM employees;
SELECT 列名1, 列名2, ... FROM 表名;
SELECT name, age FROM employees;
SELECT 列名1, 列名2, ... FROM 表名 WHERE 条件;
SELECT name, age FROM employees WHERE age > 30;
在SQL中,模糊查询可以使用LIKE关键字来实现。LIKE关键字用于匹配模式,可以与通配符一起使用来执行模糊查询。
SELECT 列名1, 列名2, ... FROM 表名 WHERE 列名 LIKE '模式';
SELECT name FROM employees WHERE name LIKE 'A%';
SELECT name FROM employees WHERE name LIKE '_A_';
SELECT name FROM employees WHERE name LIKE '%A%';
SQL中的聚合函数用于对一组值执行计算,并返回单个值。
SELECT COUNT(列名), MAX(列名), MIN(列名), AVG(列名), SUM(列名) FROM 表名;
SELECT COUNT(column_name) FROM table_name;
SELECT COUNT(id) FROM employees;
SELECT SUM(column_name) FROM table_name;
SELECT SUM(salary) FROM employees;
SELECT AVG(column_name) FROM table_name;
SELECT AVG(age) FROM employees;
SELECT MIN(column_name) FROM table_name;
SELECT MIN(salary) FROM employees;
SELECT MAX(column_name) FROM table_name;
SELECT MAX(age) FROM employees;
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;
SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;
SELECT department, MAX(salary) AS max_salary
FROM employees
GROUP BY department;
SELECT 列名1, 列名2, ... FROM 表名 ORDER BY 列名 [ASC|DESC];
SELECT name, age FROM employees ORDER BY age DESC;
SELECT 列名1, 列名2, ... FROM 表名 GROUP BY 列名;
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;
SELECT 列名1, 列名2, ... FROM 表名 GROUP BY 列名 HAVING 条件;
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) > 5;
SELECT 列名1, 列名2, ... FROM 表名 LIMIT 数量 OFFSET 偏移量;
SELECT name, age FROM employees
LIMIT 5, 5; -- 或者 LIMIT 5 OFFSET 5;
DISTINCT关键字用于去除查询结果中的重复行。
SELECT DISTINCT 列名1, 列名2, ... FROM 表名;
SELECT DISTINCT department_id FROM employees;
JOIN操作用于将多个表中的数据进行关联。
返回两个表中满足连接条件的记录。
SELECT 表名1.列名1, 表名2.列名2, ... FROM 表名1 INNER JOIN 表名2 ON 表名1.列名 = 表名2.列名;
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;
返回左表中的所有记录,以及右表中满足连接条件的记录。如果右表中没有满足条件的记录,则用NULL填充。
SELECT 表名1.列名1, 表名2.列名2, ... FROM 表名1 LEFT JOIN 表名2 ON 表名1.列名 = 表名2.列名;
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;
返回右表中的所有记录,以及左表中满足连接条件的记录。如果左表中没有满足条件的记录,则用NULL填充。
SELECT 表名1.列名1, 表名2.列名2, ... FROM 表名1 RIGHT JOIN 表名2 ON 表名1.列名 = 表名2.列名;
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;
INSERT语句用于向表中插入新的数据行
INSERT INTO 表名 (列名1, 列名2, ...) VALUES (值1, 值2, ...);
INSERT INTO employees (name, age, department_id) VALUES ('John Doe', 30, 1);
INSERT INTO 表名 (列名1, 列名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), ...;
INSERT INTO employees (name, age, department_id) VALUES
('John Doe', 30, 1),
('Jane Smith', 28, 2),
('Bob Johnson', 35, 1);
UPDATE语句用于修改表中的数据
UPDATE 表名 SET 列名 = 新值 WHERE 条件;
UPDATE employees SET salary = 5000 WHERE name = 'John Doe';
UPDATE 表名 SET 列名1 = 新值1, 列名2 = 新值2, ... WHERE 条件;
UPDATE employees SET salary = 5000, department_id = 3 WHERE name = 'John Doe';
DELETE语句用于从表中删除数据
DELETE FROM 表名 WHERE 条件;
DELETE FROM employees WHERE name = 'John Doe';
DELETE FROM 表名;
DELETE FROM employees;
TRUNCATE TABLE employees;
GRANT语句用于授予用户或角色对数据库对象的操作权限
GRANT 权限 ON 对象 TO 用户;
GRANT SELECT ON employees TO john;
GRANT 权限 ON 对象 TO PUBLIC;
GRANT SELECT ON employees TO PUBLIC;
GRANT 权限1, 权限2 ON 对象 TO 用户;
GRANT SELECT, UPDATE ON employees TO john;
REVOKE语句用于移除之前授予的权限
REVOKE 权限 ON 对象 FROM 用户;
REVOKE SELECT ON employees FROM john;
REVOKE 权限 ON 对象 FROM PUBLIC;
REVOKE SELECT ON employees FROM PUBLIC;
REVOKE 权限1, 权限2 ON 对象 FROM 用户;
REVOKE SELECT, UPDATE ON employees FROM john;
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。