• 简介
  • 数据库和SQL介绍
  • MySQL 介绍
  • 一些基本命令
• 创建一个数据库并插入语句
  • 数据库和表
  • MySQL 中的数据类型
  • 创建数据库和表
  • 向表中插入数据
• 约束
  • 主键
  • 默认值约束
  • 唯一约束
  • 外键约束
  • 非空约束
• SELECT语句详解
  • 基本 SELECT 语句
  • 限制条件
  • 对结果排序
  • 使用内置函数进行计算
  • 子查询
  • 连接查询
• 修改与删除
  • 基本命令
  • 对列进行修改
• 其他
  • 索引
  • 视图
  • 导入
  • 导出
  • 备份
  • 恢复

简介

数据库和SQL介绍

• 数据库（Database）是按照数据结构来组织、存储和管理数据的仓库
• 结构化查询语言(Structured Query Language)简称SQL，是上世纪70年代由IBM公司开发，用于对数据库进行操作的语言。

MySQL 介绍

• MySQL是一个DBMS（数据库管理系统），由瑞典MySQLAB公司开发，目前属于Oracle公司
• MySQL是关系型数据库管理系统（关联数据库：将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。）

一些基本命令

• 打开服务

sudo service mysql start #打开 MySQL
mysql -u root #使用 root 登陆

• 查看数据库

mysql> show databases; # 注意分号

• 连接数据库

mysql> use database_name

• 查看表

mysql> show tables; # 注意分号

• 退出

mysql> exit

创建一个数据库并插入语句

数据库和表

• 数据表（table）简称表，它是数据库最重要的组成部分之一
• 数据库只是一个框架，表才是实质内容。
• 一个数据库中一般会有多张表，这些各自独立的表通过建立关系被联接起来，才成为可以交叉查阅、一目了然的数据库
• 下面是一个表的例子

MySQL 中的数据类型

创建数据库和表

CREATE DATABASE db_name; # 一般将保留字大写，变量小写
USE db_name;
CREATE TABLE table_name(
col_1_name INT(10),
col_2_name CHAR(20),
col_3_name CHAR(10)
);

向表中插入数据

INSERT INTO table_name(col_1_name, col_2_name, ...) VALUES (c_1, c_2, ...);
SELECT * FROM table_name; # 查看表

注意：

1. col_1_name 必须是 table_name 中有的列，否则报错
2. INSERT 时可以缺省某一列，此时这一列会被 NULL 填充

约束

主键

• 主键 (PRIMARY KEY) 是用于约束表中的一行，作为这一行的标识符
• 在一张表中通过主键就能准确定位到一行
• 行中的主键不能有重复且不能为空
• 定义主键

  • 方法 1

      CREATE TABLE table_name(
      col_1 INT(10) PRIMARY KEY,
      col_2 ...,
      );
    

  • 方法 2

      CREATE TABLE table_name(
      col_1 INT(10),
      col_2 ...,
      CONSTRAINT Prim_key_name PRIMARY KEY(col_1) #Prim_key_name 为自定义名
      );
    

• 定义复合主键

    CONSTRAINT Prim_key_name PRIMARY KEY(col_1, col_2)
  

默认值约束

col INT(10) DEFAULT 'default_value',

唯一约束

UNIQUE(col),

外键约束

• 每个外键必须 REFERENCES (参考) 另一个表的主键，被外键约束的列，取值必须在它参考的列中有对应值。

• 在 INSERT 时，如果被外键约束的值没有在参考列中有对应，则INSERT失败

• 创建外键约束

    CONSTRAINT key_name FOREIGN KEY(col) REFERENCES ref_tb(ref_col),
    # key_name - 外键名
    # col - 被约束的列
    # ref_db 参考列所在的表
    # ref_col 参考列
  

非空约束

• 在MySQL中违反非空约束，不会报错，只会有警告
• 输入为空时，用 0 填充

• 例子

    col INT(10) NOT NULL,
  

SELECT语句详解

基本 SELECT 语句

格式：

SELECT 要查询的列名 FROM 表名字 WHERE 限制条件;

限制条件

• WHERE 后面的条件可以通过 OR 或 AND 连接

• 可通过 IN 和 NOT IN 来筛选 “在” 或 “不在” 某个范围内的结果
  • 例子：查询在部门＃3 或部门＃4 的员工

    SELECT name,age FROM employee WHERE in_dpt IN ('dpt3','dpt4');
  

• Like 与通配符
  • 关键字 LIKE 在 SQL 语句中和通配符一起使用，通配符代表未知字符。
  • SQL中的通配符是 _ 和 %
    • _ 代表一个未指定字符
    • % 代表不定个未指定字符
  • 语法 WHERE 限制条件 LIKE 通配符
    • 例子：查询电话号码以 10 开头的员工

      SELECT name,phone FROM employee WHERE phone LIKE '10%';
    

对结果排序

格式

SELECT 要查询的列名 FROM 表名字 WHERE 限制条件 ORDER BY 排序列 ASC/DESC;

其中，ASC 为升序，DESC 为降序；默认为升序

使用内置函数进行计算

语法：

SELECT FUNC(col) AS new_name FROM table_name;
# As new_name 可省略，作用是给 FUNC(col) 重命名

例子：

SELECT MAX(salary) AS richest, MIN(salary) AS easiest FROM employee;

子查询

查询结果可以作为更高一级查询的范围

例子：

SELECT col_1, col_2 WHERE col_1 IN (SELECT col_3 FROM table_name);

这段语句

• 先求出 SELECT col_3 FROM table_name 的结果
• 再判断 col_1 是否在该结果中
• 最终输出符合条件的 col_1, col_2

连接查询

例子：

SELECT id,name,people_num
FROM employee,department
WHERE employee.in_dpt = department.dpt_name
ORDER BY id;

等价于

SELECT id,name,people_num
FROM employee JOIN department
ON employee.in_dpt = department.dpt_name
ORDER BY id;

作用是输出工号，姓名以及所在部门的人数

修改与删除

基本命令

• 删除数据库

    DROP DATABASE db_name;
  

• 删除表

    DROP TABLE table_name;
  

• 重命名表

    RENAME TABLE 原名 TO 新名字;
      
    ALTER TABLE 原名 RENAME 新名;
      
    ALTER TABLE 原名 RENAME TO 新名;
  

对列进行修改

• 增加一列

    ALTER TABLE 表名字 ADD COLUMN 列名字 数据类型 约束; # 方法一
    ALTER TABLE 表名字 ADD 列名字 数据类型 约束; # 方法二
  

  • 在指定位置增加一列: 语句最后加上 AFTER col_1
  • 指定添加到第一列: 使用 FIRST 关键词

• 删除一列

    ALTER TABLE 表名字 DROP COLUMN 列名字 数据类型 约束; # 方法一
    ALTER TABLE 表名字 DROP 列名字 数据类型 约束; # 方法二
  

• 重命名一列

    ALTER TABLE 表名字 CHANGE 旧列名 新列名 数据类型 约束;
  

• 改变一列的数据类型

    ALTER TABLE 表名字 MODIFY 列名 新数据类型;
  

• 改变一列的内容

    UPDATE 表名字 SET 列1=值1,列2=值2 WHERE 条件;
  

  • 如果这里没有 WHERE 的话，会把 列1 的所有值设置为 值1，把 列2 的所有值设置为 值2

其他

索引

索引是一种与表有关的结构，它的作用相当于书的目录，可以根据目录中的页码快速找到所需的内容。 当表中有大量记录时，若要对表进行查询，没有索引的情况是全表搜索：将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录。这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作。 而如果在表中已建立索引，在索引中找到符合查询条件的索引值，通过索引值就可以快速找到表中的数据，可以大大加快查询速度。

索引的效果是加快查询速度，当表中数据不够多的时候是感受不出它的效果的。

在使用 SELECT 语句查询的时候，语句中 WHERE 里面的条件，会自动判断有没有可用的索引。

• 建立索引

ALTER TABLE 表名字 ADD INDEX 索引名 (列名);
CREATE INDEX 索引名 ON 表名字 (列名);

例子：在 employee 中建立两个索引,

ALTER TABLE employee ADD INDEX idx_id (id); 
# 在 employee 表的 id 列上建立名为 idx_id 的索引

CREATE INDEX idx_name ON employee (name);
# 在 employee 表的 name 列上建立名为 idx_name 的索引

SHOW INDEX FROM 表名字  # 查看索引

视图

视图是从一个或多个表中导出来的表，是一种虚拟存在的表。它就像一个窗口，通过这个窗口可以看到系统专门提供的数据，这样，用户可以不用看到整个数据库中的数据，而只关心对自己有用的数据。

• 数据库中只存放了视图的定义，而没有存放视图中的数据，这些数据存放在原来的表中；
• 使用视图查询数据时，数据库系统会从原来的表中取出对应的数据；
• 视图中的数据依赖于原来表中的数据，一旦表中数据发生改变，显示在视图中的数据也会发生改变；
• 在使用视图的时候，可以把它当作一张表。

视图也可以建立在多张表上，只需在 SELECT 语句中使用子查询或连接查询。

• 创建视图

CREATE VIEW 视图名(列a,列b,列c) AS SELECT 列1,列2,列3 FROM 表名字;

• 例子: 从 employee 表中创建视图

CREATE VIEW v_emp(v_name, v_age, v_phone) AS SELECT name, age, phone FROM employee;

SELECT * FROM v_emp;

结果：

导入

导入操作，可以把一个文件里的数据保存进一张表。

• 语句

LOAD DATA INFILE '文件路径' INTO TABLE 表名字;

• 例子

在 /tmp/SQL6 目录下有一个名为 in.txt 的文件，我们尝试把这个文件中的数据导入数据库 mysql_shiyan 的 employee 表中。

文件内容

导入前的数据库

命令：

LOAT DATA INFILE '/tmp/SQL6/in.txt' INTO TABLE employee;

导入之后的数据库

导出

导出与导入是相反的过程，是把数据库某个表中的数据保存到一个文件之中。

SELECT 列1，列2 ... INTO OUTFILE '文件路径和文件名' FROM 表名字;

备份

备份与导出的区别：

• 导出: 只保存数据库中的数据
• 备份: 把数据库的结构，包括数据、约束、索引、视图等全部另存为一个文件

mysqldump 是 MySQL 用于备份数据库的实用程序。它主要产生一个 SQL 脚本文件，其中包含从头重新创建数据库所必需的命令 CREATE TABLE INSERT 等。

语句：

mysqldump -u root 数据库名>备份文件名;   #备份整个数据库

mysqldump -u root 数据库名 表名字>备份文件名;  #备份整个表

恢复

source sql文件＋路径 # 方法一，恢复以前的数据库

mysql -u root 新数据库名 < sql文件＋路径 # 方法二，将以前的数据库恢复到特定的数据库名里