概要

mysql SQL必知语法

SQL必知语法包括SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY等关键字，用于查询和操作数据库中的数据。SELECT用于选择需要查询的列，FROM用于指定数据表，WHERE用于设置查询条件，GROUP BY用于对结果进行分组，HAVING用于对分组结果进行过滤，ORDER BY用于对结果进行排序。熟练掌握这些关键字和语法规则，可以实现高效、准确的数据库查询和操作。

技术细节

创建数据库
CREATE DATABASE 数据库名;
CREATE DATABASE chapter1 – 创建一个新数据库

删除数据库
DROP DATABASE <数据库名>;
DROP DATABASE chapter1

USE chapter1

数据类型
/*
字符串类型：
CHAR(size)定长、VARCHAR(size)变长 size为字符长度
TEXT 长文本
TINYTEXT 短文本
BLOB 二进制长文本···

数字类型：
严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)
近似数值数据类型(REAL、FLOAT(size,d）DOUBLE(size,d) d小数点右侧的最大位数…)。
整数类型 INT、TINYINT、SMALLINT、MEDIUMINT 和 BIGINT。(存储和范围不同)

日期/时间类型:
DATE()yyyymmdd、DATETIME()yyyymmddhhmmss、TIME()hhmmss、YEAR()
TIMESTAMP()1970-01-01 00:00:01至今多少秒、
···
*/

---

– 这是单行注释
这是单行注释

/* 这是多行注释
第一行
第二行 */

#创建表
CREATE TABLE table_name (column_name column_type);
#三要素：表名 字段名 字段描述

CREATE TABLE offices (
	office_id INT NOT NULL PRIMARY KEY,
	address VARCHAR ( 50 ) NOT NULL,
	city VARCHAR ( 50 ) NOT NULL,
	province VARCHAR ( 50 ) NOT NULL 
);-- 创建一个名为offices的表


CREATE TABLE employees (
	employee_id INT NOT NULL,
	en_name VARCHAR ( 50 ) NOT NULL,
	ch_name VARCHAR ( 50 ) NOT NULL,
	job_title VARCHAR ( 50 ) NOT NULL,
	salary INT NOT NULL,
	reports_to INT,
	office_id INT NOT NULL,
	PRIMARY KEY ( employee_id ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8;
-- 继续创建一个名为employees的表
-- 使用innodb存储引擎
-- 编码utf8

#删除数据表
DROP TABLE table_name ;
DROP TABLE employees;-- 删除表

#插入数据
INSERT INTO table_name ( field1, field2,…fieldN ) VALUES ( value1, value2,…valueN );

INSERT INTO employees 
VALUES
	( '33391', 'jackson', '谭鸿飞', '销售经理', '62871', '37270', '1' );-- 插入完整行
	
INSERT INTO employees ( employee_id, en_name, ch_name, job_title, salary, office_id )
VALUES
	( '37270', 'hall', '边天磊', '总经理', '163996', '10' );-- 提供列名，插入部分行

#查询数据
SELECT column（列名）, another_column, …FROM table（表名）

use chapter1

SELECT
	employee_id,
	ch_name 
FROM
	employees
	

SELECT
	* 
FROM
	employees
	

SELECT DISTINCT
	reports_to 
FROM
	employees -- 显式指定返回不同的列 
-- -- -- -- -- -- --

#过滤数据
– WHERE 子句
SELECT
office_id,
ch_name,
en_name
FROM
employees
WHERE
office_id= 1
or ch_name =‘边天磊’

– where满足的操作符 = != <> BETWEEN IS NULL··· 数值不需要带’'，但是字符需要

– AND操作符——通过不止一列过滤，可以使用AND操作符给WHERE子句添加条件。

– OR操作符——匹配任意条件则会返回并输出。

SELECT
	office_id,
	ch_name,
	en_name,
	salary
FROM
	employees 
WHERE
	(office_id= 1 OR office_id =2)  //office_id IN (1,2)
AND 
	salary>95000
-- AND的优先级更高，必要时需要通过括号进行明确分组。
	
SELECT
	office_id,
	ch_name,
	en_name 
FROM
	employees 
WHERE
	office_id NOT IN (1,2)
AND 
	salary>95000
	
	
	-- like子句 构造通配符
SELECT
	office_id,
	ch_name,
	en_name 
FROM
	employees 
WHERE
	en_name LIKE'b%' -- % 任意字符 _ 单个字符 (包括空格）注意通配符的位置和个数
	
	
	
SELECT
	office_id,
	ch_name,
	en_name 
FROM
	employees 
WHERE
	en_name like '%n'

SELECT
	office_id,
	ch_name,
	en_name 
FROM
	employees 
WHERE
  en_name like 'ha___' -- 不要过度使用
	

#排序
SELECT
	office_id,
	ch_name,
	en_name 
FROM
	employees 
where 1=1
ORDER BY 1,3

#LIMIT
SELECT 
	reports_to 
FROM
	employees 
	LIMIT 5 
	
SELECT 
	reports_to 
	,salary
FROM
	employees 
order by salary desc
LIMIT 5 
	
	
SELECT 
	salary 
FROM
	employees 
	LIMIT 5 OFFSET 5;-- 限制结果

#算数计算
/* MySQL运算符及运算符的优先级。
算术运算符:+、-、*、/、%
比较运算符:=、<>、!=、>、<、>=、<=、BETWEEN、NOT BETWEEN、IN、NOT IN 、ISNULL 、 IS NOT NULL
逻辑运算符:NOT、AND、OR
位运算符:&、|、^、！
*/
SELECT
salary+2000,salary
FROM
employees

#函数处理数据

– 文本处理
SELECT
offices.*, LEFT(address, 3)
FROM
offices

– 其他文本函数
LENGTH(str)/LOWER(str)转换为小写/LTRIM(str)去掉字符串左边的空格/REPLACE(s,s1,s2)···

– 数值处理函数
ABS(X)/COS(X)/PI()/MOD(x,y)

#汇总数据
– 聚集函数
AVG()-- 只能用于单个列
COUNT()–

SELECT
COUNT(*),
MAX(salary),
AVG(salary)+500 AS averge_salary
FROM
employees
WHERE
office_id=3

#分组数据
– 创建分组

SELECT
COUNT(*),
MAX(salary),
AVG(salary)+500 AS averge_salary,
office_id
FROM
employees
GROUP BY
office_id

– 过滤分组
SELECT
COUNT(),
MAX(salary),
AVG(salary)+500, – AS averge_salary
office_id
FROM
employees
GROUP BY
office_id
HAVING COUNT()>3 – 在这里where不可以在进行过滤分组，因为他过滤的是行而不是分组。

– 分组和排序
– ORDER BY 对产生的输出排序 任意列都可以（甚至没有被选中的列也可以使用）
– GROUP BY 对行分组，但是输出可能不是分组的顺序，只可以选择列或者表达式列，而且必须使用每个选择列表达式
SELECT
COUNT(*)AS items,
MAX(salary),
AVG(salary)+500 AS averge_salary,
office_id
FROM
employees
GROUP BY
office_id
HAVING items>3
ORDER BY averge_salary DESC

子查询
SELECT
address
FROM
offices
WHERE
office_id IN ( SELECT office_id FROM employees WHERE job_title = ‘销售经理’ );
– 一般不这么写因为难以阅读和调试。同时子语句的查询语句只能查询单个列。
– 两个表通过一个where联结，三个表用至少次过滤 WHERE AND

#连接表
/JOIN 按照功能大致分为如下三类：
INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
outer join/

use chapter1

SELECT
	address,en_name,ch_name
FROM
	offices,employees
WHERE
	offices.office_id=employees.office_id
AND job_title like "售前%"
-- 为了匹配，必须完全限定列名，谨防笛卡尔积的联结（叉联结）

SELECT E.*,O.*
FROM employees as E,offices O

自然连接
SELECT E.*,O.*
FROM employees E,offices O
WHERE E.office_id=O.office_id

内连接 – inner join on

外连接 – outer join on
左外连接 右外连接

SELECT offices.,employees.
FROM offices LEFT JOIN employees
ON employees.office_id=offices.office_id

全链接 – 左外连接+ union+右外连接

自连接 – 不是连接类型，但是是一种比较常用的手段

SELECT
	E1.en_name,E1.salary,E1.office_id,E2.en_name,E2.salary,E2.office_id
FROM
	employees AS E1,employees E2
WHERE
	E1.salary<E2.salary
AND
	E1.office_id=3
AND
	E2.office_id=4

组合查询
UNION 查询必须包括相同的列，表达式和聚集函数 和 UNION ALL 包含所有行，不去重

小结

如果你们学习完成SQL必知语法后，对于数据库的查询和操作有了更深入的理解和掌握。掌握了SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY等关键字和语法规则，能够更加灵活地编写SQL查询语句，实现对数据库中数据的精确提取和操作。通过不断练习和实践，我相信我能够运用这些语法知识处理更加复杂的数据库任务，提高工作效率和质量。