您的位置:首页 > 博客中心 > 数据库 >

SQL数据库-基本操作

时间:2022-03-07 01:00

整理自:廖雪峰的官方网站-SQL教程

目录

增改删查

SQL就是访问和处理关系数据库的计算机标准语言。
SQL是结构化查询语言(Structured Query Language)的缩写,用来访问和操作数据库系统。
也就是说,无论用什么编程语言(Java、Python、C++……)编写程序,只要涉及到操作关系数据库,比如,一个电商网站需要把用户和商品信息存入数据库,或者一个手机游戏需要把用户的道具、通关信息存入数据库,都必须通过SQL来完成。

SQL快捷键

SQL Workbench

1、执行整篇sql脚本:【Ctrl】+【Shift】+【Enter】

2、执行当前行:【Ctrl】+【Enter】

3、注释/取消注释:【Ctrl】+【/】

4、格式化sql语句(美化sql语句):【Ctrl】+【B】

1.概述

SQL是结构化查询语言的缩写,用来访问和操作数据库系统。SQL语句既可以查询数据库中的数据,也可以添加、更新和删除数据库中的数据,还可以对数据库进行管理和维护操作。

数据类型

对于一个关系表,除了定义每一列的名称外,还需要定义每一列的数据类型。关系数据库支持的标准数据类型包括数值、字符串、时间等:


聚合查询的列中,只能放入分组的列。

  • 使用多个列进行分组

    例如,我们想统计各班的男生和女生人数:

    -- 按class_id, gender分组:
    SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;
    

    技术图片

  • 查询练习:

    -- 查出每个班级的平均分,结果集应当有3条记录:
    SELECT class_id, AVG(score) avg_score FROM students GROUP BY class_id;
    
    
    -- 查出每个班级的平均分,结果集应当有6条记录:
    SELECT class_id, gender, AVG(score) avg_sco FROM students GROUP BY class_id, gender;
    
    
    -- 查出每个班级的平均分,并按照从高到低排序,结果集应当有6条记录,:
    SELECT class_id, gender, AVG(score) avg_sco FROM students GROUP BY class_id, gender ORDER BY avg_sco DESC;
    
    -- 配合WHERE语句
    SELECT class_id, AVG(score) avg_sco_boys FROM students WHERE gender=‘M‘ GROUP BY class_id;
    

    技术图片

    技术图片

    技术图片

    技术图片

    4.7 多表查询

    查询多张表的语法是:SELECT * FROM <表1> <表2>

    -- FROM students, classes:
    SELECT * FROM students, classes;
    

    一次查询两个表的数据,查询的结果也是一个二维表,它是students表和classes表的“乘积”,即students表的每一行与classes表的每一行都两两拼在一起返回。结果集的列数是students表和classes表的列数之和,行数是students表和classes表的行数之积。

    这种多表查询又称笛卡尔查询,

    • 列名设置别名
    • 表设置别名
    • 配合WHERE语句
    SELECT
        s.id sid,
        s.name,
        s.gender,
        s.score,
        c.id cid,
        c.name cname
    FROM students s, classes c
    WHERE s.gender = ‘M‘ AND c.id = 1;
    

    例子:

    查询每个班男女生分别有多少人,并且列名班级是从 classes中取出的

    SELECT
     c.name ‘班级‘,
     s.gender ‘性别‘,
     COUNT(s.gender) ‘人数‘
    FROM students s, classes c
    WHERE c.id = s.class_id
    GROUP BY s.class_id, s.gender;
    

    技术图片

    4.8 连接查询

    连接查询对多个表进行JOIN运算,简单地说,就是先确定一个主表作为结果集,然后,把其他表的行有选择性地“连接”在主表结果集上。

    四种链接方式

    • INNER JOIN 只返回同时存在于两张表的行数据

    • RIGHT OUTER JOIN 返回右表都存在的行。
      如果某一行仅在右表存在,那么结果集就会以NULL填充剩下的字段。

    • LEFT OUTER JOIN则返回左表都存在的行。

    • FULL OUTER JOIN,它会把两张表的所有记录全部选择出来,并且,自动把对方不存在的列填充为NULL

    • 内连接——INNER JOIN

      INNER JOIN只返回同时存在于两张表的行数据

    例子:

    从students表中列出所有学生
    根据classes表中的班级 name列,连接到students主表中去
    students表中的班级和classes中的name列的对应关系是根据
    s.class_id = c.id
    来保证的

    -- 选出所有学生,同时返回班级名称
    SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
    FROM students s
    INNER JOIN classes c
    ON s.class_id = c.id;
    

    注意INNER JOIN查询的写法是:

    1. 先确定主表,仍然使用FROM <表1>的语法;
    2. 再确定需要连接的表,使用INNER JOIN <表2>的语法;
    3. 然后确定连接条件,使用ON <条件...>,这里的条件是s.class_id = c.id,表示students表的class_id列与classes表的id列相同的行需要连接;
    4. 可选:加上WHERE子句、ORDER BY等子句。

    使用别名不是必须的,但可以更好地简化查询语句。

    如何选用

    假设查询语句是:

    SELECT ... FROM tableA ??? JOIN tableB ON tableA.column1 = tableB.column2;
    

    技术图片

    小结

    • JOIN查询需要先确定主表,然后把另一个表的数据“附加”到结果集上;
    • INNER JOIN是最常用的一种JOIN查询,
      它的语法是SELECT ... FROM <表1> INNER JOIN <表2> ON <条件...>
    • JOIN查询仍然可以使用WHERE条件和ORDER BY排序。

    5. 修改数据

    关系数据库的基本操作就是增删改查

    对于增、删、改,对应的SQL语句分别是:

    • INSERT:插入新记录;
    • UPDATE:更新已有记录;
    • DELETE:删除已有记录。

    5.1 INSERT

    INSERT语句的基本语法是:

    INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);
    

    例如,我们向students表插入一条新记录,先列举出需要插入的字段名称,然后在VALUES子句中依次写出对应字段的值:

    • 一次添加一条
    -- 添加一条新记录 
    INSERT INTO students (class_id, name, gender, score) VALUES (2, ‘大牛‘, ‘M‘, 80);
    -- 查询并观察结果:
    SELECT * FROM students;
    
    • 一次添加多条记录
      只需要在VALUES子句中指定多个记录值,每个记录是由(...)包含的一组值
    INSERT INTO students (class_id, name, gender, score) VALUES
      (1, ‘大宝‘, ‘M‘, 87),
      (2, ‘二宝‘, ‘M‘, 81);
    
    SELECT * FROM students;
    

    5.2 UPDATE

    UPDATE语句的基本语法是:

    UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;
    

    UPDATE语句会返回更新的行数以及WHERE条件匹配的行数。

    例如,我们想更新studentsid=1的记录的namescore这两个字段,
    先写出UPDATE students SET name=‘大牛‘, score=66
    然后在WHERE子句中写出需要更新的行的筛选条件id=1

    • 指定值,单条更新
    -- 更新id=1的记录
    
    UPDATE students SET name=‘大牛‘, score=66 WHERE id=1;
    -- 查询并观察结果:
    SELECT * FROM students WHERE id=1;
    
    • 指定值,多条更新
    UPDATE students SET name=‘小牛‘, score=77 WHERE id>=5 AND id<=7;
    -- 查询并观察结果:
    SELECT * FROM students;
    
    • 使用表达式更新
    UPDATE students SET score=score+10 WHERE score<80;
    -- 查询并观察结果:
    SELECT * FROM students;
    

    如果WHERE条件没有匹配到任何记录,UPDATE语句不会报错,也不会有任何记录被更新。

    特别注意

    UPDATE语句可以没有WHERE条件,例如:

    UPDATE students SET score=60;
    

    这时,整个表的所有记录都会被更新。所以,在执行UPDATE语句时要非常小心,最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集,然后再用UPDATE更新。

    5.3 DELET

    DELETE语句也会返回删除的行数以及WHERE条件匹配的行数

    DELETE语句的基本语法是:

    DELETE FROM <表名> WHERE ...;
    
    • 删除students表中id=1的记录
    DELETE FROM students WHERE id=1;
    
    • 删除多条(WHERE条件)
    DELETE FROM students WHERE id>=5 AND id<=7;
    -- 查询并观察结果:
    SELECT * FROM students;
    

    如果WHERE条件没有匹配到任何记录,DELETE语句不会报错,也不会有任何记录被删除。

    特别注意

    别小心的是,和UPDATE类似,不带WHERE条件的DELETE语句会删除整个表的数据:

    DELETE FROM students;
    

    6.MySQL

    MySQL Server 真正的MySQL服务器 可执行程序是mysqld
    MySQL Client程序 一个命令行客户端,可以通过MySQL Client登录MySQL 可执行程序是mysql

    MySQL Client和MySQL Server的关系如下:

    技术图片

    小结

    命令行程序mysql实际上是MySQL客户端,真正的MySQL服务器程序是mysqld,在后台运行。

    6.1 管理MySQL

    很多时候,通过SSH远程连接时,只能使用SQL命令,所以,了解并掌握常用的SQL管理操作是必须的。

    数据库:

    • 列出所有数据库
      SHOW DATABASES;

    • 创建一个新数据库
      CREATE DATABASE test;

    • 删除一个数据库

      注意:删除一个数据库将导致该数据库的所有表全部被删除。
      DROP DATABASE test;

    • 切换为当前数据库
      USE test;

    表:

    • 列出当前数据库的所有表
      SHOW TABLES;

    • 查看一个表的结构
      DESC students;
      技术图片

    • 查看创建表的SQL语句
      SHOW CREATE TABLE students;
      技术图片

    • 创建表 CREATE TABLE

      即上述语句

    • 删除表 DROP TABLE students;

    • 修改表就比较复杂。

      • 要给students表新增一列birth

        ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;
        
      • 要修改birth列,例如把列名改为birthday,类型改为VARCHAR(20)

        ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;
        
      • 要删除列,使用:

        ALTER TABLE students DROP COLUMN birthday;
        
    • 退出MySQL

      使用EXIT命令退出MySQL:
      注意EXIT仅仅断开了客户端和服务器的连接,MySQL服务器仍然继续运行。

    6.2 实用SQL语句

    插入或替换

    如果我们希望插入一条新记录(INSERT),但如果记录已经存在,就先删除原记录,再插入新记录。此时,可以使用REPLACE语句,这样就不必先查询,再决定是否先删除再插入:

    REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, ‘小明‘, ‘F‘, 99);
    

    id=1的记录不存在,REPLACE语句将插入新记录,否则,当前id=1的记录将被删除,然后再插入新记录。

    插入或更新

    如果我们希望插入一条新记录(INSERT),但如果记录已经存在,就更新该记录,此时,可以使用INSERT INTO ... ON DUPLICATE KEY UPDATE ...语句:

    INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, ‘小明‘, ‘F‘, 99) ON DUPLICATE KEY UPDATE name=‘小明‘, gender=‘F‘, score=99;
    

    id=1的记录不存在,INSERT语句将插入新记录,否则,当前id=1的记录将被更新,更新的字段由UPDATE指定。

    插入或忽略

    如果我们希望插入一条新记录(INSERT),但如果记录已经存在,就啥事也不干直接忽略,此时,可以使用INSERT IGNORE INTO ...语句:

    INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, ‘小明‘, ‘F‘, 99);
    

    id=1的记录不存在,INSERT语句将插入新记录,否则,不执行任何操作。

    快照

    如果想要对一个表进行快照,即复制一份当前表的数据到一个新表,可以结合CREATE TABLESELECT

    -- 对class_id=1的记录进行快照,并存储为新表students_of_class1:
    CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;
    

    新创建的表结构和SELECT使用的表结构完全一致。

    写入查询结果集

    如果查询结果集需要写入到表中,可以结合INSERTSELECT,将SELECT语句的结果集直接插入到指定表中。

    例如,创建一个统计成绩的表statistics,记录各班的平均成绩:

    CREATE TABLE statistics (
        id BIGINT NOT NULL AUTO_INCREMENT,
        class_id BIGINT NOT NULL,
        average DOUBLE NOT NULL,
        PRIMARY KEY (id)
    );
    
    INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;
    

    确保INSERT语句的列和SELECT语句的列能一一对应,就可以在statistics表中直接保存查询的结果:

    技术图片

    强制使用指定索引

    在查询的时候,数据库系统会自动分析查询语句,并选择一个最合适的索引。但是很多时候,数据库系统的查询优化器并不一定总是能使用最优索引。如果我们知道如何选择索引,可以使用FORCE INDEX强制查询使用指定的索引。例如:

    > SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;
    

    指定索引的前提是索引idx_class_id必须存在。

    7. 事务

    把多条语句作为一个整体进行操作的功能,被称为数据库事务
    数据库事务可以确保该事务范围内的所有操作都可以全部成功或者全部失败。如果事务失败,那么效果就和没有执行这些SQL一样,不会对数据库数据有任何改动。

    数据库事务具有ACID这4个特性:

    • A:Atomic,原子性,将所有SQL作为原子工作单元执行,要么全部执行,要么全部不执行;
    • C:Consistent,一致性,事务完成后,所有数据的状态都是一致的,即A账户只要减去了100,B账户则必定加上了100;
    • I:Isolation,隔离性,如果有多个事务并发执行,每个事务作出的修改必须与其他事务隔离;
    • D:Duration,持久性,即事务完成后,对数据库数据的修改被持久化存储。

    对于单条SQL语句,数据库系统自动将其作为一个事务执行,这种事务被称为隐式事务

    手动把多条SQL语句作为一个事务执行,
    使用BEGIN开启一个事务,使用COMMIT提交一个事务,这种事务被称为显式事务
    例如,把上述的转账操作作为一个显式事务:

    BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
    COMMIT;
    

    多条SQL语句要想作为一个事务执行,就必须使用显式事务。
    COMMIT是指提交事务,即试图把事务内的所有SQL所做的修改永久保存。如果COMMIT语句执行失败了,整个事务也会失败。

    7.1 ROLLBACK

    有些时候,我们希望主动让事务失败,这时,可以用ROLLBACK回滚事务,整个事务会失败:

    BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
    ROLLBACK;
    

    数据库事务是由数据库系统保证的,我们只需要根据业务逻辑使用它就可以。

    提交事务,即确认DML的改动,使用commit;
    回滚事务,即要回退掉之前的操作,使用rollback;
    另外:如果不提交也不回滚
    执行的DML只是在当前会话有效,在其他会话是不生效的(不信你再重新打开一个连接窗口看看),
    所以begin一开始,要么以commit结束,要么以rollback结束。

    意思是,
    在执行较大量的update或delete时,一定要用事务事务
    可以在执行出错后,回滚到BEGIN语句还未开始的状态

    例子:

    SELECT * FROM statistics;
    BEGIN;
    UPDATE statistics SET average=average-60 WHERE id=1;
    SELECT * FROM statistics;
    UPDATE statistics SET ave=average-60 WHERE id=2;
    ROLLBACK;
    SELECT * FROM statistics;
    

    执行结果:

    技术图片

    2、3、4
    技术图片

    5.SQL语句报错(或者自己进行了误操作想进行回滚)

    6、7.执行回滚操作并显示回滚后的结果
    技术图片

    隔离级别

    对于两个并发执行的事务,如果涉及到操作同一条记录的时候,可能会发生问题。
    因为并发操作会带来数据的不一致性,包括脏读、不可重复读、幻读等。数据库系统提供了隔离级别来让我们有针对性地选择事务的隔离级别,避免数据不一致的问题。

    SQL标准定义了4种隔离级别,分别对应可能出现的数据不一致的情况:

    Isolation Level 脏读(Dirty Read) 不可重复读
    (Non Repeatable Read)
    幻读(Phantom Read)
    Read Uncommitted Yes Yes Yes
    Read Committed - Yes Yes
    Repeatable Read - - Yes
    Serializable - - -

    我们会依次介绍4种隔离级别的数据一致性问题。

    默认隔离级别

    如果没有指定隔离级别,数据库就会使用默认的隔离级别。在MySQL中,如果使用InnoDB,默认的隔离级别是Repeatable Read。

    7.2 Read Uncommitted

    在Read Uncommitted隔离级别下,一个事务可能读取到另一个事务更新但未提交的数据,这个数据有可能是脏数据。

    准备好students表的数据,该表仅一行记录:

    mysql> select * from students;
    +----+-------+
    | id | name  |
    +----+-------+
    |  1 | Alice |
    +----+-------+
    1 row in set (0.00 sec)
    

    然后,分别开启两个MySQL客户端连接,按顺序依次执行事务A和事务B:

    时刻 事务A 事务B
    1 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    2 BEGIN; BEGIN;
    3 UPDATE students SET name = ‘Bob‘ WHERE id = 1;
    4 SELECT * FROM students WHERE id = 1;
    5 ROLLBACK;
    6 SELECT * FROM students WHERE id = 1;
    7 COMMIT;

    当事务A执行完第3步时,它更新了id=1的记录,但并未提交,
    而事务B在第4步读取到的数据就是未提交的数据。

    随后,事务A在第5步进行了回滚,事务B再次读取id=1的记录,发现和上一次读取到的数据不一致,这就是脏读

    7.3 Read Committed

    在Read Committed隔离级别下,事务不可重复读同一条记录,因为很可能读到的结果不一致。

    不可重复读(Non Repeatable Read)的问题是指,在一个事务内,多次读同一数据,在这个事务还没有结束时,如果另一个事务恰好修改了这个数据,两次读取的数据可能不一致。

    时刻 事务A 事务B
    1 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    2 BEGIN; BEGIN;
    3 SELECT * FROM students WHERE id = 1;
    4 UPDATE students SET name = ‘Bob‘ WHERE id = 1;
    5 COMMIT;
    6 SELECT * FROM students WHERE id = 1;
    7 COMMIT;

    7.4 Repeatable Read

    在Repeatable Read隔离级别下,一个事务可能会遇到幻读(Phantom Read)的问题。

    幻读就是没有读到的记录,以为不存在,但其实是可以更新成功的,并且,更新成功后,再次读取,就出现了。

    students表的数据:

    mysql> select * from students;
    +----+-------+
    | id | name  |
    +----+-------+
    |  1 | Alice |
    +----+-------+
    1 row in set (0.00 sec)
    

    然后,分别开启两个MySQL客户端连接,按顺序依次执行事务A和事务B:

    时刻 事务A 事务B
    1 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    2 BEGIN; BEGIN;
    3 SELECT * FROM students WHERE id = 99;
    4 INSERT INTO students (id, name) VALUES (99, ‘Bob‘);
    5 COMMIT;
    6 SELECT * FROM students WHERE id = 99;
    7 UPDATE students SET name = ‘Alice‘ WHERE id = 99;
    8 SELECT * FROM students WHERE id = 99;
    9 COMMIT;

    事务B在第3步第一次读取id=99的记录时,读到的记录为空,说明不存在id=99的记录。
    随后,事务A在第4步插入了一条id=99的记录并提交。
    事务B在第6步再次读取id=99的记录时,读到的记录仍然为空,
    但是,事务B在第7步试图更新这条不存在的记录时,竟然成功了,
    并且,事务B在第8步再次读取id=99的记录时,记录出现了。

    7.5 Serializable

    Serializable是最严格的隔离级别。在Serializable隔离级别下,所有事务按照次序依次执行,因此,脏读、不可重复读、幻读都不会出现。

    虽然Serializable隔离级别下的事务具有最高的安全性,但是,由于事务是串行执行,所以效率会大大下降,应用程序的性能会急剧降低。如果没有特别重要的情景,一般都不会使用Serializable隔离级别。

    本类排行

    今日推荐

    热门手游