常见的数据库

• 数据库分为：关系型数据库（Relational Database）、非关系型数据库（NoSQL Database）

下载

• https://downloads.mysql.com/archives/installer/

  官方文档

• 中文文档

  • http://www.searchdoc.cn/rdbms/mysql/dev.mysql.com/doc/refman/5.7/en/index.com.coder114.cn.html

• 英文文档

  • https://dev.mysql.com/doc/refman/5.7/en/

    MySQL的使用步骤

• 登录、连接MySQL服务器 （管理员账户名root）

  • mysql -uroot -p密码
  • mysql -uroot -p

• 在Windows中，可以通过以下命令查看占用3306端口号的进程ID

  • netstat -aon| findstr 3306

    数据库的内部存储细节

    

• 一个数据库（Database）中可以存放多张表（Table）

• 每个表（Table）中包含一些列（Column，也叫作字段）

• 每个表（Table）中存放的数据，一般称为记录（Record）

  SQL语句

• SQL是Structured Query Language的简称，译为“结构化查询语言”，用于操作关系型数据库

• 4大类
  • DDL（Data Definition Language）
    • 数据定义语言
    • 创建（CREATE)、修改（ALTER）、删除（DROP）数据库\表
  • DML（Data Manipulation Language）
    • 数据操纵语言
    • 增加（INSERT）、删除（DELETE）、修改（UPDATE）记录
  • DQL（Data Query Language）
    • 数据查询语言
    • 查询记录（SELECT）
  • DCL（Data Control Language）
    • 数据控制语言
    • 控制访问权限（GRANT、REVOKE）

DDL语句 - 数据库

• 创建
  • CREATE DATABASE 数据库名
  • CREATE DATABASE 数据库名 CHARACTER SET 字符编码
  • CREATE DATABASE IF NOT EXISTS 数据库名
  • CREATE DATABASE IF NOT EXISTS 数据库名 CHARACTER SET 字符编码
• 查询
  • SHOW DATABASE
  • SHOW CREATE DATABASE 数据库名 # 查询数据库的创建语句
  • USE 数据库名
  • SELECT DATABASE( ) # 查询正在使用的数据库
• 修改
  • ALTER DATABASE 数据库名 CHARACTER SET 字符编码 # 修改数据库的字符编码

• 删除

  • DROP DATABASE 数据库名
  • DROP DATABASE IF EXISTS 数据库名
    

    DDL语句 - 表

• 创建 （基本语法）

  • CREATE TABLE 表名 （

列名1 数据类型1
列名2 数据类型2
…
）

• 查询
  • SHOW TABLES
  • DESC 表名 # 查看表结构
• 删除
  • DROP TABLE 表名
  • DROP TABLE IF EXISTS 表名
• 修改
  • ALTER TABLE 表名 RENAME TO 新表名
  • ALTER TABLE 表名 CHARACRER SET 字符编码 # 修改表格的字符编码
  • ALTER TABLE 表名 ADD 列名 数据类型 #增加新的一列
  • ALTER TABLE 表名 MODIFY 列名 新数据类型 # 修改某一列的数据类型
  • ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型 # 修改某一列的列名、数据类型
  • ALTER TABLE 表名 DROP 列名 # 删除某一列TRUNCATE

TRUNCATE

• TRUNCATE [TABLE] 表名 # 先删除后重新创建表（效率高）

  • TRUNCATE归类为DDL语句，而不是DML语句
  • 为了实现高性能，它绕过了删除数据的DML方法，因此，它不能被回滚，不会导致ON DELETE触发器触发，并且不能对InnoDB具有父子外键关系的表执行

    DML语句

• 增加

  • INSERT INTO 表名(列名1，…) VALUES (值1，….)
  • INSERT INTO 表名 VALUES (值1，值2，…) # 从左至右按顺序给所有列添加值
• 修改
  • UPDATE 表名 SET 列名1 = 值1， 列名2 = 值2，… [WHERE 条件]

• 删除

  • DELETE FROM 表名 [WHERE 条件]

    DQL语句 - SELECT语句

    SELECT [DISTINCT] 列名1，…
    FROM 表名
    [WHERE …]
    [GROUP BY …]
    [HAVING …]
    [ORDER BY …]
    [LIMIT ….]

• SELECT * FROM customer #查询表中的所有记录

• SELECT DISTINCT * FROM customer # 查询表中的所有记录（去除了重复的记录）

  聚合函数（Aggregate Function）

• SELECT COUNT(*) FROM customer # 查询表中的记录总数

• SELECT COUNT(phone) FROM customer # 查询表中phone的总数

• SELECT COUNT(DISTINCT phone) FROM customer

• SELECT SUM(salary) FROM customer

• SELECT MIN(age) FROM customer
• SELECT MAX(age) FROM customer

• SELECT AVG(salary) FROM customer
  

  常见的WHERE子句

• 比较运算

  • WHERE age != 25
  • WHERE age <> 25 # age不等于25
• NULL值判断 （不能用=、!=、<>）
  • WHERE phone IS NULL
  • WHERE phone IS NOT NULL
• 逻辑运算
  • WHERE age BETWEEN 20 AND 30
  • WHERE age IN (18, 20, 22)
  • WHERE NOT (age < 18) # age大于等于18
  • WHERE ! (age < 18) # age大于等于18

• 模糊查询 （ _ 代表单个任意字符，%代表任意个任意字符）

  • WHERE name LIKE ‘码‘ # 3个字符并且中间是‘码’字
  • WHERE name LIKE ‘ _‘ # 3个字符
  • WHERE name LIKE ‘李%’ # 以李字开头
  • WHERE name LIKE ‘_码%’ # 第2个字符是‘码’字
  • WHERE name LIKE ‘%码%’ # 包含‘码’字

    常用数据类型 - 数字类型

    
    

    常用数据类型 - 字符串类型

    

    常用数据类型 - 日期和时间类型

    

    DATETIME\TIMESTAMP的自动设置

• DEFAULT CURRENT_TIMESTAMP

  • 当插入记录时，如果没有指定时间值，就设置时间为当前的系统时间
• ON UPDATE CURRENT_TIMESTAMP
  • 当修改记录时，如果没有指定时间值，就设置时间为当前的系统时间

表的复制

• 创建一张拥有相同表结构的空表（只复制表结构， 不复制记录）
  • CREATE TABLE new_table LIKE old_table

• 创建一张拥有相同表结构的空表（复制表结构， 复制记录）

  • CREATE TABLE new_table AS (SELECT * FROM old_table)
  • 可以省略AS

    列的常用属性

• NOT NULL: 不能设置为NULL值

• COMMENT : 注释
• DEFAULT: 默认值 （BLOB、TEXT、GEOMETRY、JSON类型不能有默认值）

• AUTO_INCREMENT: 自动增长

  • 适用于INT、 FLOAT、DOUBLE类型
  • 在插入记录时，如果不指定此列的值或设置为NULL, 会在此前的基础上自动增长1
  • 不能有默认值（不能使用DEFAULT）
  • 在一个表格中，最多只能有一列被设置为AUTO_INCREMENT
  • 这一列必须被索引（UNIQUE、 PRIMARY、KEY、FOREIGN KEY等）

    UNIQUE索引

• 一旦某一列被设置了UNIQUE索引

  • 该列的所有值必须是唯一的
  • 允许存在多个NULL值
• 2种常见写法
  • 列名 数据类型 UNIQUE [ KEY ]
  • UNIQUE [KEY] (列名)

CREATE TABLE student（
id INT UNIQUE,
name VARCHAR(20),
UNIQUE (name)
）;

主键（PRIMARY KEY）

• 主键的作用：可以保证在一张表中的每一条记录都是唯一的
  • 如果将某一列设置为主键，那么这一列相当于加上了NOT NULL UNIQUE
  • 建议每一张表都有主键
  • 主键最好跟业务无关，常设置为INT AUTO_INCREMENT
• 2种常见写法
  • 列名 数据类型 PRIMARY KEY
  • PRIMARY KEY (列名)

CREATE TABLE company（
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL UNIQUE
);

CREATE TABLE company(
id INT AUTO_INCREMENT,
name VARCHAR(20) NOT NULL UNIQUE,
PRIMARY KEY (id)
);

外键 （FOREIGN KEY）

• 一般用外键来引用其他表的主键
• 常见写法
  • FOREIGN KEY (列名) REFERENCE 表名（列名）

CREATE TABLE company（
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL UNIQUE
);

CREATE TABLE customer(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
company_id INT NOT NULL,
FOREIGN KEY (company_id) REFERENCES company(id)
);

级联（CASCADE）

• 定义外键时，可以设置级联

  • ON DELETE CASCADE
    • 当删除被引用的记录时，引用了此记录的其他所有记录都会被自动删除
  • ON UPDATE CASCADE
    • 当修改被引用的记录时， 引用了此记录的其他所有记录都会被自动更新

      多表查询

• 内连接

  • INNER JOIN、 CROSS JOIN、 JOIN
    • 在MySQL中，它们是等价的，但是在标准SQL中，它们并不是等价的
• 外连接
  • LEFT [OUTER] JOIN、 RIGHT [OUTER] JOIN
• 并集
  • UNION
  • MySQL并不支持标准SQL中的“FULL [OUTER] JOIN”, 但可以用UNION来代替实现
• ON和WHERE后面都可以跟着条件，它们的区别是
  • ON: 配合JOIN语句使用，用以指定如何连接表的条件
  • WHERE: 限制哪些记录出现在结果集中

• INNER JOIN 和逗号（,）在没有连接条件的情况下， 语义上是等价的

  • 都在指定的表之间产生笛卡尔积（Cartesian Product）
  • 也就是说，第一个表中的每一行都连接到第二个表中的每一行

    子查询

• 当一个查询是另一个查询的条件时，称之为子查询

• 举例
  • SELECT * FROM customer WHERE company_id = (SELECT id FROM company WHERE name = ‘腾讯’)