SQL 基础

markdown ### 名词解释 ```text 1. DB Database 存储数据的仓库,本质是一个文件系统 2. DBMS Database Management System 是一种操纵和管理数据库的大型软件,用于建立,使用和维护数据库,对数据苦进 行统一管理和控制,用户通过数据库管理系统访问数据库中表内的数据 3. SQL Structured Query Language 专门用来与数据库通信的语言 ``` ```text 关系型数据库 字段 以row和column(又称为field) 组成了table,单个或多个table组成了database 复杂查询 可以在一个表或多个表之间进行复杂事务查询 事务支持 使事务安全性能很高的数据访问要求得以实现 非关系性数据库 基于键值对存储数据,以获取更高的性能 ``` ### 书写规范 ```text 数据库名,表名,表别名,字段名,字段别名等都小写 SQL关键字,函数名,绑定变量等都大写 SQL语句以 ; 结尾 表别名使用 "双引号" (只能在WHERE使用,不能在ORDER BY上使用) 数据使用 '单引号' 当字段,表名 与关键字重复时可以使用 `字段/表名` 查看表的详细信息 DESCRIBE 表名; 只要 NULL 参与运算 都为 NULL ``` ### 数据导入 ```text #导入 .sql 文件 source 文件位置 #此为命令行版 ``` ### SQL操作分类 #### 1. DDL 数据定义语言 ```text CREATE DROP ALTER RENAME TRUNCATE ``` #### 2. DML 数据操纵语言 ```text INSERT DELETE UPDATE SELECT #查询表 1. SELECT * FROM 表(table); #查询单个字段 并设置别名 2. SELECT 字段(field),字段(field) AS "别名" FROM 表(table); #查询表设置字段的查询条件 3. SELECT * FROM 表(table) WHERE 查询条件; #查询字段并去重 4. SELECT DISTINCT 字段 FROM 表(table); #查询字段并降序输出 5. SELECT 字段(field) FROM 表(table) ORDER BY 字段(field) DESC; #查询某个字段为110,100 6. SELECT 字段(field) FROM 表(table) WHERE 字段(field) IN (110,100); #分页查询: 偏移量 = 返回数 * (页数 - 1) 7. SELECT * FROM 表(table) LIMIT 偏移量,返回数; #查询某个范围的字段 8. SELECT * FROM 表(table) WHERE 字段(field) NOT BETWEEN 范围1 AND 范围2; ``` ```text #多表查询 #等值连接 SELECT DISTINCT emp.department_id,dep.department_name, loc.city FROM employees AS emp, departments AS dep, locations AS loc WHERE emp.department_id = dep.department_id AND dep.location_id = loc.location_id; #非等值连接 SELECT emp.employee_id,emp.salary,job.grade_level FROM employees AS emp,job_grades AS job where emp.salary BETWEEN job.lowest_sal AND job.highest_sal ORDER BY job.grade_level; #自连接 SELECT emp.employee_id,emp.first_name,man.employee_id,man.first_name FROM employees emp, employees man WHERE emp.manager_id = man.employee_id; #非自连接 #外连接 SELECT emp.employee_id,dep.department_name FROM employees AS emp LEFT OUTER JOIN departments AS dep ON emp.department_id = dep.department_id; #满内链接 SELECT emp.employee_id,dep.department_name FROM employees AS emp RIGHT JOIN departments AS dep ON emp.department_id = dep.department_id WHERE emp.department_id IS NULL UNION ALL SELECT emp.employee_id,dep.department_name FROM employees AS emp LEFT JOIN departments AS dep ON emp.department_id = dep.department_id; #内连接 SELECT emp.employee_id,dep.department_name FROM employees AS emp,departments AS dep WHERE emp.department_id = dep.department_id; #等同于 SELECT emp.employee_id,dep.department_name FROM employees AS emp INNER JOIN departments AS dep ON emp.department_id = dep.department_id; ```
#### 3. DCL 数据控制语言 ```text GRANT REVOKE COMMIT ROLLBACK SAVEPOINT ``` *

*[返回教程主页](https://www.monody.net/p/blog-page_3.html)*

*

评论