MySQL学习(一)

  • Post author:
  • Post last modified:2022年5月12日
  • Post category:web
  • Post comments:0评论
  • Reading time:10 mins read

MySQL学习(一)

什么是SQL

Structured Query Language:结构化查询语言——定义了操作所有关系型数据库的规则。

以下是当前数据库 TOP10 所对应的数据库类型。

RankDBMSDatabase ModelScore
1OracleRelational1254.82
2MySQLRelational1204.16
3Microsoft SQL ServerRelational938.46
4PostgreSQLRelational614.46
5MongoDBDocument483.38
6RedisKey-value177.61
7ElasticsearchSearch engine160.83
8IBM Db2Relational160.46
9Microsoft AccessRelational142.78
10SQLiteRelational132.80

以上数据来自来自于 DB-Engines 发布的 DB-Engines Ranking of database management systems, April 2022

我们发现其中大部分都是关系型数据库,也就是说,我们既可以用 SQL 操作 MySQL,也可以用其操作 Oracle。当然,对于不同的数据库,SQL 语句有细微差异,我们称其为方言

什么是MySQL

MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL 是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件。MySQL 软件采用了双授权政策,分为社区版和商业版,由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,一般中小型网站的开发都选择 MySQL 作为网站数据库。

MySQL 所使用的 SQL 语言是用于访问数据库的最常用标准化语言。

MySQL 将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。

MySQL安装

我这里采用最为便捷的方法——docker 容器启动。

依然是给出 shell 和 docker-compose 两种启动,注意修改密码等内容:

  1. shell

    docker run -itd --name mysql -h mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD= mysql
  2. docker-compose.yml

    version: "3"
    services:
    
     db:
       image: mysql
       container_name: mysql
       stdin_open: true
       tty: true
       environment:
         MYSQL_ROOT_PASSWORD: "root"
       ports:
         - "3306:3306"
       restart: always
       hostname: mysql

这就安装好了,输入命令docker exec -it mysql bash即可进入容器,按下ctrl}+{p}+{q即可退出容器但保持容器活跃,输入命令mysql -u root -p即可进入 mysql 命令行。

MySQL使用

SQL通用语法

  • SQL 语句以;\g结尾。

  • SQL 语句应用空格或缩进以代码格式化,便于阅读。

  • SQL 语句中关键词尽量大写,但 SQL 语句对大小写不敏感。

  • SQL 语句中注释有三种:

    # 单行注释,MySQL特有
    -- 单行注释
    /*
    多行注释
    */
  • SQL 分类:

    1. DDL(Data Definition Language)数据定义语言:
      用来定义数据库对象︰数据库,表,列等。关键字: createdropalter等。

    2. DML(Data Manipulation Language)数据操作语言:
      用来对数据库中表的数据进行增删改。关键字:insertdeleteupdate等。

    3. DQL(Data Query Language)数据查询语言:
      用来查询数据库中表的记录(数据)。关键字:selectwhere等。

    4. DCL(Data Control Language)数据控制语言(了解即可):

      用来定义数据库的访问权限和安全级别,及创建用户。关键字:GRANTREVOKE等。

DDL:操作数据库、表
操作数据库
  1. C(Create):创建

    创建数据库CREATE DATABASE 数据库名称;

    判断不存在才创建CREATE DATABASE IF NOT EXISTS test;

    指定字符集创建CREATE DATABASE test CHARACTER SET gbk;

    综合:CREATE DATABASE IF NOT EXISTS test CHARACTER SET gbk——如果该数据库不存在则创建之,并指定字符集为 gbk。

  2. R(Retrieve):查询

    查询数据库名称SHOW DATABASES;

    我们会看到有四个数据库information_schemamysqlperformance_schemasys,其用途如下:

    • information_schema:信息数据库,在其中的是视图而非基本表,这个库提供访问数据库元数据的方式。

    • mysql:核心数据库,存储数据库的用户、权限设置、关键字等控制管理信息。比如说,我们可以在user表中修改用户密码。

    • performance_schema:收集数据库服务器性能参数

    • sys:其中数据来自:performance_schema。目的是将performance_schema复杂度降低,让 DBA 能更好的阅读这个库里的内容,让 DBA 更快的了解 DB 的运行情况。

    • 查询数据库创建语句SHOW CREATE DATABASE 数据库名称;

      image-20220510173606574

  3. U(Update):修改

    修改数据库字符集ALTER DATABASE 数据库名称 CHARACTER SET 字符集;

    值得注意的是,该操作时修改库,不会对其中已有的表的字符集做出修改!

  4. D(Delete):删除

    删除数据库DROP DATABASE 数据库名称;

    判断存在才删除DROP DATABASE IF EXISTS 数据库名称;

  5. 使用数据库

    查询当前正在使用的数据库名称SELECT DATABASE();

操作表
  1. C(Create):创建

    • 语法

      CREATE TABLE 表名(
       列名1 数据类型1,
       列名2 数据类型2
      );
    • 数据类型:详细见MySQL 数据类型 | 菜鸟教程 (runoob.com)

      1. int整数类型——age INT
      2. double小数类型——score DOUBLE(5,2),小数最多五位,小数点后保留两位
      3. date日期类型,yyyy-MM-dd格式
      4. datetime日期类型,yyyy-MM-dd HH:mm:ss格式
      5. timestamp时间戳,yyyy-MM-dd HH:mm:ss格式,若不赋值则获取当前系统时间
      6. varchar字符串类型——name VARCHAR(20),最大 20 个字符
    • 示例:

      CREATE TABLE student(
       id INT,
       name VARCHAR(20),
       age INT,
       score DOUBLE(5,2),
       birthday DATE,
       insert_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      );
    • 复制表CREATE TABLE 表名 LIKE 源表名;

  2. R(Retrieve):查询

    • 查询库中所有表名称SHOW TABLES;
    • 查询表结构DESC 表名;
    • 查询表创建语句SHOW CREATE TABLE 表名
  3. U(Update):修改

    • 修改表名ALTER TABLE 原表名 RENAME TO 新表名;
    • 修改表字符集ALTER TABLE 表名 CHARACTER SET 字符集;
    • 添加列ALTER TABLE 表名 ADD 列名 数据类型;
    • 修改列ALTER TABLE 表名 CHANGE 原列名 新列名 新数据类型;ALTER TABLE 表名 MODIFY 原列名 新数据类型
    • 删除列
  4. D(Delete):删除

    • DROP TABLE 表名

      DROP TABLE IF EXISTS 表名

DML:增删改表中数据
添加数据

语法:INSERT INTO 表名(列名1,列名2,...,列名n) VALUES(值1,值2,...,值n);

注意事项:

  1. 列名与值一一对应。
  2. 如果表名后不定义列名,则默认给所有列添加值
  3. 除了数字类型,其他数据类型均需要用单引号(或双引号)括起来
删除数据

语法:DELETE FROM 表名 [WHERE 条件];

注意事项:

如果不添加条件,则删除所有记录!

不推荐使用DELETE FROM 表名;来删除表中所有记录,推荐使用TRUNCATE TABLE 表名;

TRUNCATE TABLE 表名;DROP TABLE 表名;以及DELETE FROM 表名;区别:

TRUNCATE TABLE 表名直接删除所有记录再创建相同表

DROP TABLE 表名直接删除表

DELETE FROM 表名重复执行DELETE以删除表中每一条记录,但不删除表结构。

修改数据

语法:UPDATE 表名 SET 列1=值1,列2=值2,...,列n=值n [WHERE 条件]

注意事项:如果不添加条件,则修改所有记录!

DQL:查询表中记录
语法
SELECT 
    字段列表 
FROM 
    表名列表 
WHERE 
    条件列表 
GROUP BY 
    分组字段 
HAVING 
    分组之后的条件 
ORDER 
    排序 
LIMIT 
    分页限定;
基础查询
  1. 多字段查询SELECT 列名1,列名2,...,列名n FROM 表名;

    如果是查询所有字段,可以用SELECT * FROM 表名;,但这样写的可读性不高。

  2. 去除重复DISTINCT

  3. 计算列SELECT 四则运算式 FROM 表名;

    SELECT math+english FROM stu;

    值得注意的是,如果有NULL参与,则计算结果也为NULL,解决方法:使用函数IFNULL(字段,替换值)

    SELECT math+IFNULL(english,0) FROM stu;
  4. 起别名AS(该关键字可省略)

    SELECT math 数学,english 英语,math+IFNULL(english,0) AS 总分 FROM stu;
条件查询

WHERE子句后跟条件

运算符:

  • >, <, >=, <=, =, <>

    <>在 SQL 中表示不等于,在 MySQL 中也可以用!=表示不等于,但请注意,没有==

  • BETWEEN...AND

  • IN(集合)

  • LIKE模糊查询

    • 占位符:_单个字符,%多个任意字符

    eg,查询name字段包含’马’的人:SELECT name FROM stu WHERE name LIKE '%马%'

  • IS NULL

    NULL 值不可以做大小比较,只能用ISIS NOT来判断

  • AND 或 &&

    在 SQL 中建议使用前者,后者不通用

  • OR 或 ||

  • NOT 或 !

排序查询

语法:ORDER BY 排序字段1 排序方式1, 排序字段2 排序方式2...;

默认排序方式为ASC即升序,而DESC为降序,当且仅当第一条件无法完全判断时才执行后续条件

SELECT id,name,score FROM stu ORDER BY score desc,id; -- 按照 score 降序排列,相同成绩则按 id 升序排列
聚合函数

将—列数据作为一个整体,进行纵向的计算。

  • COUNT:计算个数
  • MAX:计算最大值
  • MIN:计算最小值
  • SUM:求和
  • AVG:求平均值
SELECT COUNT(id) FROM stu; 

注意,所有聚合函数的计算会排除 NULL 值,解决方案:

  • 选择不包含 NULL 值的列进行计算
  • 使用IFNULL函数
分组查询

语法:GROUP BY 分组字段 HAVING 分组后查询条件

注意事项:

  1. 分组之后查询的字段:分组字段、聚合函数
  2. WHEREHAVING的区别:
    1. WHERE在分组之前进行限定,如果不满足条件,则不参与分组。HAVING在分组之后进行限定,如果不满足结果,则不会被查询出来
    2. WHERE后不可跟聚合函数,HAVING可以进行聚合函数的判断。
SELECT gender, AVG(score) FROM stu WHERE score >= 70 GROUP BY gender HAVING COUNT(id) > 2; 
-- 按 gender 分组,查询 score 平均数且 score 低于 70 的参与,分组之后,人数要大于二:
分页查询

语法:LIMIT 开始的索引,每页显示的条数

公式:开始的索引 = (当前的页码 - 1) * 每页显示的条数

eg,从0开始,显示3条 SELECT * FROM stu LIMIT 0,3

注意,LIMIT是 MySQL 的方言,对于其他 SQL 可能不适用!

约束

概念:对表中的数据进行限定,保证数据的正确性、有效性和完整性。

分类:

  • 主键约束:primary key
  • 非空约束:not null
  • 唯一约束:unique
  • 外键约束:foreign key
非空约束:not null
  1. 创建表时添加非空约束:

    CREATE TABLE test(
       id INT,
       name VARCHAR(20) NOT NULL -- name非空
    );
  2. 创建表后添加非空约束:

    ALTER TABLE test MODIFY name VARCHAR(20) NOT NULL;
  3. 删除非空约束:

    ALTER TABLE test MODIFY name VARCHAR(20);
唯一约束:unique
  1. 创建表时添加唯一约束:

    CREATE TABLE test(
       id INT,
       phone_number VARCHAR(20) UNIQUE -- phone_number唯一
    );
  2. 创建表后添加唯一约束:

    ALTER TABLE test MODIFY name VARCHAR(20) UNIQUE;
  3. 删除唯一约束:

    ALTER TABLE test DROP INDEX phone_number;
主键约束:primary key
  • 非空且唯一

  • 主键是表中记录的唯一标识

    比如身份证号

  1. 创建表时添加主键:

    CREATE TABLE test(
       id INT PRIMARY KEY, -- id主键
       name VARCHAR(20)
    );
  2. 创建表后添加主键:

    ALTER TABLE test MODIFY id INT PRIMARY KEY;
  3. 删除主键:

    ALTER TABLE test DROP PRIMARY KEY; -- 删除主键
    ALTER TABLE test MODIFY id INT; -- 删除非空约束
  4. 自动增长

    概念:如果某一列是数值类型的,使用 auto_increment 可以来完成值的自动增长

    1. 创建表时添加主键约束,并完成主键自增长:

      CREATE TABLE test(
        id INT PRIMARY KEY AUTO_INCREMENT, -- id主键
        name VARCHAR(20)
      );

      之后再添加记录时可以省略主键,其值为上一条记录的主键值加一

      insert into test(name) values('a');
    2. 添加自动增长

      ALTER TABLE test MODIFY id INT AUTO_INCREMENT;
    3. 删除自动增长

      ALTER TABLE test MODIFY id INT;
外键约束:foreign key

如果公共关键字在一个关系中是主关键字,那么这个公共关键字被称为另一个关系的外键。由此可见,外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表,具有此外键的表被称为主表的从表。

  1. 语法:

    CREATE TABLE 表名(
       ...
       外键列
       CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 主表名称(主表列名称)
    );

    eg,

    CREATE TABLE department(
       id INT PRIMARY KEY AUTO_INCREMENT,
       dep_name VARCHAR(20),
       dep_location VARCHAR(20)
    ); -- 主表
    CREATE TABLE employee(
       id INT PRIMARY KEY AUTO_INCREMENT,
       name VARCHAR(20),
       age INT,
       dep_id INT, -- 外键对应主表的主键
       CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)
    ); -- 从表
  2. 创建表后添加外键

    ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id);
  3. 删除外键

    ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;
  4. 级联操作

    级联,指当主动方对象执行操作时,被关联对象(被动方)是否同步执行同一操作。

    分类:

    • 级联更新
    • 级联删除

    添加外键,设置级联更新、级联删除

    ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE;

发表评论