SQL数据库教案（入门必备）

文章目录：

一：前言

1.SQL定义

2.语法规范

3.表的概念

4.其他概念

4.1 数据类型

4.2 正则表达式regexp

二：语法执行顺序*

三：基础语法

1.数据库操作

2.表操作

2.1 create创建表 

constraint约束 

comment注释 

2.2 drop删除表

2.3 表结构

3.增

3.1 add添加新字段 

3.2 insert插入数据 

4.删

4.1 alter删除字段

4.2 delete删除数据

5.改

5.1 alter修改字段

5.2 update更新数据

6.查select

6.1 简单查询

6.2 条件查询where

运算符

关键字 

6.3 排序查询order by

6.4 分组查询group by having

聚合函数

6.5 多表查询

6.6 子查询

6.7 关联查询 

6.7.1 内关联（交集）

6.7.2 外关联（左外_左边和交集 右外_右边和交集）

6.7.3 全连接

四：语法进阶 

1.sql笛卡儿积

2.索引index

3.视图view

一：前言

1.SQL定义

SQL是什么
    a.SQL 指结构化查询语言，全称是 Structured Query Language

    b.用于数据库操作 和 数据管理:存取数据以及查询、更新和管理关系数据库系统

    c.SQL是一种用于管理和操作关系数据库的标准语言
      包括数据查询、数据插入、数据更新、数据删除、数据库结构创建和修改等功能


特点
    a.SQL本身不实现具体的数据库系统，而是作为一种语言被嵌入到各种数据库系统中

    b.采用客户端-服务器架构时，SQL语句在服务器端执行


功能与用途
    a.主要用于数据查询（SELECT）、数据定义（DDL如CREATE、ALTER、DROP）、
      数据操纵（DML如INSERT、UPDATE、DELETE）和数据控制（DCL如GRANT、REVOKE）

    b.SQL的功能是标准化的，不同数据库系统间的SQL语法虽然略有差异，但基本保持一致


SQL分类
    DML数据操作语言，操作数据库中存储的数据(insert、update、delete)
    DDL数据定义语言，创建、删除、修改数据库、数据表(create、drop、alter)
    DOL数据查询语言，查询数据库(select)
    DCL数据控制语言，用来控制数据库组件的存取(事务commit、回滚rolback)

2.语法规范

1.SQL 对大小写不敏感：SELECT 与 select 是相同的
                     关键字大写；表名 列名和其他名称小写

2.每条SQL语句的末端使用分号“；”
  也可以不加，但是建议加上分号

3.多个列 和 多个值 之间使用逗号“，”隔开

4.注释
  -- 这是一个单行注释
  #这是一个单行注释
  /*这是多行注释*/

5.占位符："？"

6.字符型相关的必须加引号：单引号 双引号都可以

3.表的概念

记录(横向的)
    行：记录/元组/具体的某个事物    


字段(纵向的)
    列标题：字段/事物属性 
      

值：交叉的小格子/每一个方格数据    
域：值的限定范围
 

主键外键 
    主键（主关键字）：可以定位到某一条具体的数据、非空、不能重复
                    方便和其他的表进行关联：因为不会重复、可以定位到一条具体的数据
    外键（外部关键字）：在另外一张表中与”主键“相互产生关联


表关系
    单表：单独一张表就可以将信息保持

    一对多：需要两张表来存储信息，且两张表存在一对多 或 多对一关系

    多对多：需要三张表来存储信息，两张单表+关系表，创造出两个单表之间多对多的关系

4.其他概念

4.1 数据类型

注意:文本用引号包裹、日期用#包裹 

4.2 正则表达式regexp

        *                任意多个字符                （王*        姓王、*王*    包含王）

        ？               任意单个字符                （王？       姓王，名字两个字）

        []               括号内任意单个字符          （[王李]？    姓王或李，名字两个字）    

        ！               不在括号内的字符            （![王李]？   不姓王或李，名字两个字）

        -                范围内的任意一个字符        （[a-j]bd     abd,cbd,jbd）
 
        #                单个数字                   （5#1         501,502,503）

        ^                开头

        $                结尾

        a|b              或                                                   

二：语法执行顺序*

SQL执行顺序:select...from...where...group by...order by...limit...
    ——>1.获取表，没有表啥也做不了
            from——>on——>join

    ——>2.获取结果
            ——>where——>group by——>having——>select

    ——>3.结果处理
            ——>distinct——>order by——>limit

    4.关键字
        distinct：去重
        as取别名：可以省略，在from之前搭配使用
        like模糊查询：在where之后搭配使用
        comment：注释     

        not和and和or结合运算符：在where之后搭配使用
        between...and...在某个范围内：在where之后搭配使用

        all所有的
        any任意的

        in指定多个值：在where之后搭配使用
        on:通常用于指定连接（JOIN）操作的条件


参考结构：
    select      查询目标

    from        表名称
    join        加入
    on          连接条件

    where       筛选记录的条件
    group  by   分组（每字开头）-分组字段，把选定的记录分成特定的组
    having      分组条件（和上面搭配使用）-筛选分组的条件，说明每个组需要满足的条件；替换where
    select      查询

    distinct    去重
    order  by   排序字段名
    limit  m,n  限制查询结果集输出的行数,m从那开始,n获取几条数据



语法
	select [distinct|top n]  *    |    字段1 [as] 别名1,字段2 [as] 别名2.字段n [as] 别名n  
    from 表名1,表名2... 
	[where 条件] 
	[group by 字段] 
    [having 条件]
	[order by 字段... [asc默升|desc降]]
	[limit n[,m]]

三：基础语法

数据库中数据的组织结构：数据库(database)、数据表(table)、数据行(row)、字段(field)

1.数据库操作

创建数据库：create databases 数据库名称 [default charset set utf8 collate utf8_general_ci];
    collate设置数据库的校验字符集：对某个字符串类型的数据进行排序查询的时候，数据排序的方式
    utf8_general_ci：不区分大小写
    utf8_bin：区分大小写


查看所有数据库：show databases;
查看当前使用的数据库：select database();
显示部分数据库：show databases like '匹配模式'
显示数据库创建语句：show create database 数据库名字;


使用数据库：use 数据库名称;


删除数据库：drop datebase 数据库名称;


查看有那些表：show tables;


退出命令行环境：exit;

导入数据
    mysql -u root -p 数据库名称 < 导入文件名.sql


导出数据
    mysqldump -u root -p 数据库名 > 导出文件名.sql

2.表操作

2.1 create创建表 

创建表create
    create table 表名(
        列1 数据类型1 [约束1]  [是否为主键/是否可以为空/默认值],
        列2 数据类型2 [约束2]  [是否为主键/是否可以为空/默认值],
        …
        列n 数据类型n [约束n]  [是否为主键/是否可以为空/默认值]
    );
    --------------------------------------------------------------------------------
 
    创建学生表:
        CREATE TABLE 学生(
            学号 INTEGER Primary Key,
            姓名 VARCHAR(4) Not Nul,
            性别 VARCHAR(1),出生日期 DATE,
            家庭住址 VARCHAR(30),学分 NUMBER
        );

constraint约束 

 constraint约束
    主键约束PK：primary key
    外键约束FK：foreign key
    唯一约束UQ：unique
    空值约束NN：not null、is null
    值自动增长Al：auto_increment
    check：保证列中的值符合指定的条件
    默认值default：规定没有给列赋值时的默认值

说明举例 

PRIMARY KEY 约束：是一种特殊的唯一约束，表中每行都必须有一个唯一的标识符
                 主键约束自动具有 NOT NULL 约束
    CREATE TABLE Persons (  
        ID int NOT NULL,  
        LastName varchar(255) NOT NULL,  
        FirstName varchar(255),  
        PRIMARY KEY (ID)  
    );


FOREIGN KEY 约束：用于在两个表之间创建链接。外键是一个表中的字段，是另一个表的主键
    CREATE TABLE Orders (  
        OrderID int NOT NULL,  
        OrderNumber int NOT NULL,  
        PersonID int,  
        PRIMARY KEY (OrderID),  
        FOREIGN KEY (PersonID) REFERENCES Persons(ID)  
    );


UNIQUE 约束：确保所有值在表中是唯一的
    CREATE TABLE Persons (  
        ID int NOT NULL,  
        LastName varchar(255) NOT NULL,  
        FirstName varchar(255),  
        UNIQUE (ID)  
    );


NOT NULL 约束：确保列不能有 NULL 值
    CREATE TABLE Persons (  
        ID int NOT NULL,  
        LastName varchar(255) NOT NULL,  
        FirstName varchar(255)  
    );


AUTO_INCREMENT 约束：用于生成一个唯一的数字，每当向表中插入新行时，该数字会自动增加
                    这通常用于主键列，以确保每条记录都有一个唯一的标识符
    CREATE TABLE Users (  
        UserID int NOT NULL AUTO_INCREMENT,  
        Username varchar(255) NOT NULL,  
        Email varchar(255),  
        PRIMARY KEY (UserID)  
    );


CHECK 约束：确保列中的值满足特定条件
    CREATE TABLE Persons (  
        ID int NOT NULL,  
        LastName varchar(255) NOT NULL,  
        FirstName varchar(255),  
        Age int,  
        CHECK (Age>=18)  
    );


DEFAULT 约束：为列指定默认值
    CREATE TABLE Persons (  
        ID int NOT NULL,  
        LastName varchar(255) NOT NULL,  
        FirstName varchar(255),  
        Country varchar(255) DEFAULT 'China'  
    );

comment注释 

给表添加注释
    CREATE TABLE employees (  
        id INT PRIMARY KEY,  
        name VARCHAR(100),  
        salary DECIMAL(10, 2)  
    ) COMMENT='员工信息表';


给列添加注释
    CREATE TABLE employees (  
        id INT PRIMARY KEY COMMENT '员工ID',  
        name VARCHAR(100) COMMENT '员工姓名',  
        salary DECIMAL(10, 2) COMMENT '员工薪水'  
    );


修改表或列的注释
    ALTER TABLE employees COMMENT = '更新后的员工信息表';

    ALTER TABLE employees MODIFY COLUMN name VARCHAR(100) COMMENT '员工的姓名';

2.2 drop删除表

删除表drop
    drop table 表名;
    --------------------------------------------------------------------------------
 
    DROP TABLE 学生;

2.3 表结构

查看表结构：desc 表名;
           describe描述

修改表结构：alter table 表名 modify column 列名 数据类型和大小;

重命名字段：alter table 表名 rename column name to 新的名字;

3.增

3.1 add添加新字段 

添加新字段alter add
    alter table 表名 add 新列名 数据类型 [约束];
    --------------------------------------------------------------------------------
 
    ALTER TABLE 学生 ADD 手机号 VARCHAR(11) Unique;

3.2 insert插入数据 

插入数据insert values
    insert into 表名(字段1,字段2..字段n) values(值1,值2...值n);
    --------------------------------------------------------------------------------
 
    INSERT INTO 学生 VALUES(2,"李四"“女",#2019-9-1#,"上海市",99.5);
    
    INSERT INTO 学生(学号,姓名,性别,出生日期,家庭住址,学分) VALUES(1,"张三""男,#2020-1-1#,“北京市",98);

4.删

4.1 alter删除字段

删除字段alter drop
    alter table 表名 drop 新列名;
    --------------------------------------------------------------------------------
 
    ALTER TABLE 学生 DROP 家庭住址;

4.2 delete删除数据

删除数据delete where
    delete from 表名 [where 条件];

    delete from 表名 limit 限制的行数量;
    --------------------------------------------------------------------------------
 
    DELETE FROM 学生 WHERE 姓名="张三";

5.改

5.1 alter修改字段

修改字段alter alter
    alter table 表名 alter 新列名 数据类型;
    --------------------------------------------------------------------------------
 
    ALTER TABLE 学生 ALTER 姓名 VARCHAR(10):

5.2 update更新数据

更新数据update set where
    update 表名 set 字段1=值1, 字段2 = 值2.... [where 条件];
    --------------------------------------------------------------------------------
 
    UPDATE 学生 SET 学分=学分+1 WHERE 姓名="张三";

6.查select

语法
	select [distinct|top n]  *    |    字段1 as 别名1,字段2 as 别名2.字段n as 别名n  
    from 表名1,表名2... 
	[where 条件] 
	[group by 字段] 
    [having 条件]
	[order by 字段... [asc默升|desc降]]
	[limit n[,m]]


exists判断查询是否有结果
    select ecists(查询语句);

6.1 简单查询

    select * from 表名称;

	select 列名称 from 表名称;
    --------------------------------------------------------------------------------

    SELECT*FROM 员工:

    SELECT 姓名,性别,手机号,工资 AS 薪资 FROM 员工;                       

    SELECT DISTINCT 性别 FROM 员工;                                    

6.2 条件查询where

运算符

关键字 

与where相关 

    SELECT 姓名,性别,手机号,工资,部门 
    FROM 员工 
    WHERE 部门="咨询部";

----------------------------------------------------------------------------------------

not关键字
    SELECT *  
    FROM employees  
    WHERE NOT department_id = 4;


and关键字
    SELECT *  
    FROM employees  
    WHERE salary > 50000 AND department_id = 2;

    SELECT 姓名,性别,手机号,工资,部门 
    FROM 员工 
    WHERE 工资>5000 AND 工资<20000;
 

    SELECT 姓名,性别,手机号,工资,部门 
    FROM 员工 
    WHERE 工资 Between 5000 AND 20000;


or关键字
    SELECT *  
    FROM employees  
    WHERE position = 'Manager' OR department_id = 1;

    SELECT *  
    FROM employees  
    WHERE (salary > 50000 AND department_id = 2) OR (position = 'Manager' AND NOT department_id = 4);

---------------------------------------------------------------------------------------- 

in关键字（指定多个值）
    SELECT 姓名,性别,手机号,工资,部门 
    FROM 员工 
    WHERE 部门 IN("咨询部""销售部");


    SELECT *  
    FROM employees  
    WHERE department_id IN (1, 2, 3);


    SELECT name, position  
    FROM employees  
    WHERE position IN ('Manager', 'Director', 'VP');


    SELECT name, department_id  
    FROM employees  
    WHERE department_id NOT IN (4, 5, 6);


on关键字：指定连接条件，它经常与 JOIN 语句一起使用
    SELECT employees.name, departments.department_name  
    FROM employees  
    JOIN departments 
    ON employees.department_id = departments.id;

----------------------------------------------------------------------------------------

like关键字（模糊查询）：通配符——%0个或多个字符、_1个字符
    SELECT 姓名,性别,手机号,工资,部门 
    FROM 员工 
    WHERE 姓名 LIKE"王*";

----------------------------------------------------------------------------------------

between...and..关键字
    SELECT *  
    FROM employees  
    WHERE salary BETWEEN 50000 AND 100000;

----------------------------------------------------------------------------------------

is null关键字、is not null关键字：null和任何东西比较都是假的，包括它本身
    SELECT *  
    FROM employees  
    WHERE email IS NULL;

    SELECT *  
    FROM employees  
    WHERE email IS NOT NULL;

----------------------------------------------------------------------------------------

all关键字
    SELECT *  
    FROM employees  
    WHERE salary > ALL (SELECT salary FROM interns);


any关键字
    SELECT *  
    FROM employees  
    WHERE department_id = ANY (SELECT department_id FROM departments WHERE location = 'New York');
    
----------------------------------------------------------------------------------------

与where不相关  

as关键字：取别名
    为列指定别名
        SELECT employee_id AS id, first_name AS name, salary * 12 AS annual_salary  
        FROM employees;

    为表指定别名
        SELECT e.id, e.name, d.department_name  
        FROM employees AS e  
        JOIN departments AS d 
        ON e.department_id = d.id;

----------------------------------------------------------------------------------------

distinct关键字：去重
    SELECT DISTINCT department_id  
    FROM employees;

6.3 排序查询order by

    SELECT TOP 3 姓名,性别,手机号,工资,部门 FROM 员工 WHERE 部门="咨询部" ORDER BY 工资 DESC;
    
    SELECT 姓名,工资 FROM 员工 ORDER BY 工资 DESC:

6.4 分组查询group by having

    SELECT 部门,COUNT(员工编号) AS 人数 FROM 员工 GROUP BY 部门:
 
    SELECT 部门,AVG(工资) AS 平均工资 FROM 员工 GROUP BY 部门 HAVING AVG(工资)>15000;

聚合函数

聚合函数：在from之前搭配使用;括号里面填写对象;where不能使用聚合函数_使用having   
    count(*)函数：用于返回查询结果的总数据条数 
    avg()函数：平均值
    sum()函数：求和
    max()函数：最大值
    min()函数：最小值
    group_concat()函数：指定的字段进行合并/字符串连接

说明举例 

COUNT()：计算行数
    计算employees表中的行数
        SELECT COUNT(*) FROM employees;



AVG()：计算数值列中值的平均值
    计算employees表中所有员工的平均薪水
        SELECT AVG(salary) FROM employees;

    平均薪水大于50000的所有部门及其平均薪水
        SELECT department, AVG(salary) AS average_salary  
        FROM employees  
        GROUP BY department  
        HAVING AVG(salary) > 50000;



SUM()：计算数值列中值的总和
    计算employees表中所有员工的薪水总和
        SELECT SUM(salary) FROM employees;



MAX()：找出列中的最大值
    找出employees表中薪水最高的金额
        SELECT MAX(salary) FROM employees;



MIN()：找出列中的最小值
    找出employees表中薪水最低的金额
        SELECT MIN(salary) FROM employees;



GROUP_CONCAT()：将列值连接成一个字符串
    将employees表中所有员工的名字连接成一个字符串
        SELECT GROUP_CONCAT(name) FROM employees;

6.5 多表查询

    select 列名称 from 表1 as 别名1,
                      表2 as 别名2，
					  ...表n
	[where 连接条件进行关联比对筛选]
    --------------------------------------------------------------------------------

    SELECT 客户.姓名,咨询信息.咨询事务,咨询信息.咨询方式 
    FROM 客户,咨询信息 
    WHERE 客户.客户编号=咨询信息.客户编号;
 
    SELECT 客户.姓名,员工.姓名,咨询信息.咨询事务,咨询信息.咨询方式 
    FROM 客户,咨询信息,员工 
    WHERE 客户.客户编号 = 咨询信息.客户编号 AND 咨询信息.员工编号=员工.员工编号;

6.6 子查询

    SELECT 姓名,工资 FROM 员工 WHERE 工资>(SELECT AVG(工资) FROM 员工);
 
    SELECT 姓名,工资 FROM 员工 WHERE 部门=“产品部" AND (工资 > ALL(SELECT 工资 FROM 员工 Where 部门=“销售部"));
 
    SELECT 姓名,工资 FROM 员工 WHERE 部门 ="产品部"AND (工资 > ANY(SELECT 工资 FROM 员工 Where 部门=“销售部”));

6.7 关联查询 

6.7.1 内关联（交集）

内关联（交集 双向奔赴 可能都没有不能相互关联） ：两个表都有的数据

    第一种：标准的SQL JOIN 语法
        select 列名称 
        from 表1 [as 别名1] 
        [inner]  join 表2 [as 别名2]
	    on 别名1.连接字段 = 别名2.连接字段;

    第二种：隐式连接（逗号分隔的连接）
        select 列名称 
        from 表1 [as 别名1] ,表2 [as 别名2]
	    where 别名1.连接字段 = 别名2.连接字段;
    --------------------------------------------------------------------------------

    select * from player
    inner join equip
    on player.id=equip.player_id

    select * from player,equip
    where player.id=equip.player_id

    加 WHERE 子句来进一步过滤结果
        SELECT 列名称  
        FROM 表1 AS 别名1  
        INNER JOIN 表2 AS 别名2  
        ON 别名1.连接字段 = 别名2.连接字段  
        WHERE 别名1.某个字段 = '某个值' OR 别名2.另一个字段 > 100;

6.7.2 外关联（左外_左边和交集 右外_右边和交集）

外关联（单向奔赴 以什么为主：主表有多少数据，最终显示多少条数据）

    第一种：标准的SQL JOIN 语法  
        select 列名称 
        from 表1 [as 别名1] 
        (left|right) [outer] join 表2 [as 别名2]
	    on 别名1.连接字段 = 别名2.连接字段;

    第二种：隐式连接（逗号分隔的连接）
        select 列名称 
        from 表1 [as 别名1] ,表2 [as 别名2]
	    where 别名1.连接字段 = 别名2.连接字段;
    --------------------------------------------------------------------------------

    select * from player
    left outer join equip
    on player.id=equip.player_id

    select * from player,equip
    where player.id=equip.player_id

解释 

左连接（LEFT JOIN/LEFT OUTER JOIN）：左表中所有的数据 与 右表匹配的数据；右表没有用NULL填充
    左连接用于从“左表”中获取所有行，并尝试与“右表”中的行进行匹配
    如果右表中存在与左表连接条件相匹配的行，则这些行会一起出现在结果中
    如果右表中没有匹配的行，则结果中右表的部分将包含NULL
    这通常用于确保结果中包含左表中的所有记录，即使它们在右表中没有对应的匹配项


右连接（RIGHT JOIN/RIGHT OUTER JOIN）：右表中所有的数据 与 左表匹配的数据；左表没有用NULL填充
    右连接与左连接相反，它从“右表”中获取所有行，并尝试与“左表”中的行进行匹配
    如果左表中存在与右表连接条件相匹配的行，则这些行会一起出现在结果中
    如果左表中没有匹配的行，则结果中左表的部分将包含NULL
    然而，由于可以通过交换表的顺序和使用左连接来达到相同的效果，右连接在实际应用中相对较少见


外连接（OUTER JOIN）
    外连接是一个更广泛的概念，它包括了左外连接和右外连接
    然而，在SQL查询中，你通常会明确指定是LEFT OUTER JOIN还是RIGHT OUTER JOIN，因为单独的OUTER JOIN可能在不同的数据库系统中具有不同的解释或不被直接支持
    外连接的主要目的是确保结果中包含至少一个表中的所有行，即使它们在另一个表中没有对应的匹配项。这些未匹配的行在结果中将以NULL填充另一个表的部分

6.7.3 全连接

union并集：合并两个表中的数据，生成新表，默认去重

union all并集：不会去除重复的记录

intersect交集-查询结果：相当于内关联-关联关系

except差集

    select 姓名,性别,手机号 from 员工 
    union 
    select 姓名,性别,手机号 from 客户;
    --------------------------------------------------------------------------------

四：语法进阶 

1.sql笛卡儿积

定义
    SQL中的笛卡尔积（Cartesian product）
    是指两个或更多表在没有指定连接条件的情况下进行连接时所产生的结果集


产生原因：当执行没有连接条件
    如使用JOIN关键字而没有ON子句
    使用逗号分隔表名而没有WHERE子句）的查询时


导致的结果
    结果集中的每一行都是第一个表中的一行与第二个表中的每一行组合而成的
    如果涉及到更多表，则结果集将是所有表行的组合


如何避免
    会通过添加适当的连接条件（如使用ON子句）来避免这种情况，以获取更有意义的结果


有什么用处
    笛卡尔积通常不是期望的结果，因为它包含了大量无用的数据组合
    然而，有时它们可以用于特定的分析或操作，比如生成测试数据

举例

employees（员工）表
    1	Alice
    2	Bob


departments（部门）表
    101	Engineering
    102	Marketing


执行以下查询，将产生笛卡尔积：
    SELECT *  
    FROM employees, departments;


结果将是：
    1	Alice	101	Engineering
    1	Alice	102	Marketing
    2	Bob	    101	Engineering
    2	Bob	    102	Marketing

2.索引index

索引是用来提高查询效率的数据结构，它可以帮助我们快速定位到我们想要的数据 

    创建索引：
        第一种：create index添加索引
             create [unique唯一|fulltext全文|special空间] index 索引名称
             on 表名(一个或多个字段名)

        第二种：修改表结构添加索引
             alter table 表名 add index
             on 索引名称(一个或多个字段名)


    查看索引：show index from 表名


    删除索引：drop index 索引名称 on 表名
    --------------------------------------------------------------------------------

3.视图view

是一种虚拟存在的表，本身并不包含数据，而是作为一个查询语句保持在数据字典中

当我们查询视图的时候，它会根据查询语句的定义，来动态的生成数据

    创建视图：create view 视图名称 as         
             select 字段名1, 字段名2, ... 
             from 表名称
             where 条件;


    查看视图：select * from 视图名称


    修改视图：alter view 视图名称 as
             select 字段名1, 字段名2, ...
             from 表名称
             where 条件;

    删除视图：drop view 视图名称
    --------------------------------------------------------------------------------