【一周入门MySQL—1】数据库概述、数据定义、数据操作

数据库概述、数据定义、数据操作

一、数据库概述

数据库基础概念：按照一定的数据结构来组织、存储和管理数据的仓库。

企业数据存储面临的问题：

• 存储大量数据；
• 大量数据的检索和访问；
• 保证数据信息的一致性和完整性；
• 数据共享和安全；

数据库分类：

• 关系型数据库（RDBMS）：结构化的数据集；
• 非关系型数据库（NOSQL）：非结构化的数据集；

表结构数据：

由固定列和任意行构成的表结构的数据集；

表中的列称为字段，表中的行称为记录；

以字段为基本的存储单元和计量单位；

每一个字段必须有字段名，且同一个表中的字段名不能重复；

每个字段的数据类型必须一致；

除去Excel、WPS之外的其他分析工具，都是使用二维表结构的存储格式。

表格与表的区别：（Excel中表格数据选择套用表格格式转成表数据）

• 表格数据的最小单位是一个单元格，表数据的最小单位是一个字段；
• 表格数据中可以没有列名，表数据中每一个字段必须有字段名，且同一个表中的字段名不能重复；
• 表格数据中每个单元格的数据类型可以不一致，表数据中每一列的数据类型必须一致；

在Excel中可以通过“套用表格格式”将表格转换为表。

主流的关系型数据库：（表结构数据形式，都是使用SQL语法）

• Oracle：运行稳定，可移植性高，功能齐全，性能超群，适用于大型企业；
• DB2：速度快，可靠性好，适用于海量数据，恢复性极强，适用于大中型企业；
• MySQL：开源，体积小，速度快，适用于中小型企业；
• SQL Server：全面高效，界面友好易操作，但不是跨平台（Windows系统），适用于中小型企业；

现状：

多台MySQL服务器建立集群 – 性价比更高；

阿里 - 去IOE（IBM、Oracle、EMC）化口号 – 选择MySQL替换Oracle，自研开发阿里Ocean Base；

国企、央企也开始使用自研数据库，以及华为、腾讯、阿里等企业；

2009年MySQL所属的Sun公司被Oracle收购，加强了企业级的特性；

MySQL 8.0支持开窗函数；

数据库、数据库管理系统和SQL之间的关系：

数据库（DB）是长期存储在计算机内，有组织的，统一管理的相关数据的集合；

数据库管理系统（DBMS）是用于管理数据库的软件，它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性；

SQl是一种结构化的查询语言（Structure Query Language），它是国际标准化组织（ISO）采纳的标准数据库语言；

我们以数据库管理系统为载体来学习SQL。

SQL就好比驾照，数据库管理系统就是什么牌子的车子，有了驾照，什么牌子的车都可以开。

SQL语言的分类：

• 数据定义语言DDL：用于创建、修改、删除数据库中的各种对象，比如数据库、表、视图、索引等，常用的命令有Create、Alter、Drop等；
• 数据操作语言DML：用于操作数据库表中的记录，常用的命令有Insert、Update、Delete等；
• 数据查询语言DQL：用户查询数据库表中的记录，基本结构是SELECT <字段名> FROM <表或者视图> WHERE <查询条件>；
• 数据控制语言DCL：用于定于数据库访问权限和安全级别，常用命令有Grant、Revoke等；

SQL书写要求：

• SQL语言可以单行或者多行书写，用分号结尾；
• SQL关键字用空格分隔，也可以用缩进来增强语句的可读性；
• SQL对大小写不敏感；
• 用#或者—单行注释，用/* */多行注释，注释的语句不可以执行；

安装MySQL Workbench进行数据库连接

使用MySQL Workbench客户端操作MySQL数据库，它是官方提供的免费工作台。

SQL语句执行方式：（如上图所示）

• 全部执行，或者选中行执行；
• 光标所在行执行（Ctrl + Enter）；

进入MySQL Workbench前需理解的基本概念：

数据库：组织、存储和管理相关数据的集合，同一个数据库管理系统中的数据库名必须唯一；

表：由固定的列数和任意的行数构成的二维表结构的数据集，同一个数据库中表名必须唯一；

字段：一列即为一个字段，同一个表中的字段名必须唯一；

记录：一行即为一条记录；

以字段为基本存储和计算单位，每个字段的数据类型必须一致。

MySQL常用数据类型：

• int：大整数型；
• float：单精度浮点型，默认float(10,2),表示最多10个数字，其中2位小数；
• decimal：十进制小数型；
• char：固定长度字符串型，1-255；
• varchar：可变长度字符串型，1-255；
• text：长文本字符串型，最大65535，不能指定长度；
• data：日期型，yyyy-MM-dd；
• time：时间型，hh:mm:ss；
• datetime：日期时间型，yyyy-MM-dd hh:mm:ss；
• Timestamp：时间戳；

二、数据定义

以下操作需在MySQL Workbench客户端执行操作

-- 查看系统中有哪些数据库

show databases;



-- 创建test数据库（不能使用关键字作为数据库名）

create database test;



-- 选择进入数据库

use test;



-- 删除数据库test(慎用)

drop database test;



-- 创建数据表（建表之前先选择进入数据库）

use test;

create table department(deptno int,dname varchar(10),num int);



-- 查看当前数据库中有哪些数据库表

show tables;



-- 查看表结构

desc department;

describe department;



-- 删除数据库表(删除表结构和表数据 慎用)

drop table department;

约束条件：

约束条件是在表上强制执行的数据检验规则；

用来保证创建的表的数据的完整性和准确性；

主要在两方面对数据进行约束：空值和重复值；

主键约束（primary key）

每个表中只能有一个主键；

主键值须非空且不重复；

可设置单字段主键，也可以设置多字段联合主键；

联合主键中多个字段的取值完全相同时，才违反主键约束；

添加主键约束：

列级添加主键约束：create table <表名>(<字段名称1><字段类型1>primary key, ……<字段名称n><字段类型n>);

表级添加主键约束：create table <表名>(<字段名称1><字段类型1>, ……<字段名称n><字段类型n>,primary key(<字段名称1>));

联合主键必须使用第二种方法。

比如员工表中的工号就可以设置为主键。

唯一约束（unique）

指定字段的取值不能重复，可以为空，但只能出现一个空值；

也可以在列级和表级添加唯一约束。

自动增长列（auto_increment）

指定字段的取值自动生成，默认从1开始，每增加一条记录，该字段的取值会加1；

只适用于整数型，配合主键一起使用；

创建自动增长约束 ： create table <表名>(<字段名称1><字段类型1>primary key auto_increment, ……<字段名称n><字段类型n>);

比如员工表中的工号就可以设置为主键，并设置为自动增长列，每次新添加一个员工就加1。

一个字段可以设置多个约束条件，用空格分隔，且没有先后顺序。

非空约束（not null）

字段的值不能为空；

创建非空约束 ： create table <表名>(<字段名称1><字段类型1>not null, ……<字段名称n><字段类型n>);

默认约束（default）

如果新插入一条记录时没有为该字段赋值，系统会自动为这个字段赋值为默认约束设定的值；

创建默认约束 ：create table <表名>(<字段名称1><字段类型1> default value, ……<字段名称n><字段类型n>);

外键约束（foreign key）

在一张表中执行数据插入、更新、删除等操作的时候，DBMS都会跟另一张表进行对照，避免不规范的操作，以确保数据存储的完整性。

某一表中的某字段的值依赖于另一张表中某字段的值；

主键所在的表为主表，外键所在的表为从表；

每一个外键值必须与另一个表中的主键值相对应；

创建外键约束 ： create table <表名>(<字段名称1><字段类型1>, ……<字段名称n><字段类型n> foreign key(字段名) references <主表>(主键字段));

比如上图中的部门表和员工表，因为有外键约束条件，所以需要先创建主表<部门表>，再创建从表<员工表>。

-- 创建带有约束条件的表

create table dept(

       deptno int primary key,

    dname varchar(10),

    loc varchar(15)

    );



create table employee(

       empid int primary key auto_increment,

    ename varchar(15) unique,

    job varchar(10) not null,

    mgr int,

    hiredate date,

    sal float default 0,

    comm float,

    deptno int,

    foreign key(deptno) references dept(deptno)

);

三、数据操作

修改数据表：修改数据库中已经存在的数据表的结构

-- 修改表名

alter table employee rename emp;

show tables;



-- 修改字段名

alter table emp change empid empno int;

desc emp;

# 删除原有主键

alter table emp drop primary key;

# 添加新的联合主键

alter table emp add primary key(ename,deptno);



-- 修改字段类型

alter table emp modify sal decimal default 0;

desc emp;



-- 添加字段(默认添加到最后一列,first第一列)

alter table emp add city varchar(10);

desc emp;



-- 修改字段的排列位置

alter table emp modify city varchar(10) after job;

desc emp;

alter table emp modify city varchar(10) first;

desc emp;



-- 删除字段

alter table emp drop city;

desc emp;

插入数据

字段名称和字段值的数据类型、个数、顺序必须一一对应。

可以指定字段名插入，也可以不指定字段名插入（为表中每一个字段指定值，且值得顺序必须和数据表中得顺序一致）。

批量导入数据：路径中不能有中文，并且要将“\”改为“\\”或者“/”

load data infile ‘文件路径.csv’ into table tablename fields terminated by ',' ignore 1 lines;

MySQL 默认得数据格式是UTF-8 既支持中文也支持英文

指定外部路径需要管理员进行相应的设置赋予权限（MySQL 8.0），这里为了简便可以直接从以下路径进行导入：

C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/loaddata.csv

-- 插入数据:指定字段名

desc dept;

insert into dept(deptno,dname,loc) value (10,'财务部','北京总部'),

                                         (20,'行政部','北京总部'),

                                         (30,'销售部','上海分部');

select * from dept;



-- 插入数据:不指定字段名

insert into dept value (40,'总裁办','null'),

                       (50,'采购部','null'),

                       (60,'电商部','上海分部');

select * from dept;



-- 批量导入数据

load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/loaddata.csv" 

into table dept

fields terminated by ','

# ignore 1 lines # 忽略第一条记录

;

select * from dept;

更新数据

update 表名 set 字段名1 = 字段值1 [，字段名2 = 字段值2 ……] [where 更新条件];

删除数据

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

truncate 表名; 与delete from 表名是一样的，都是删除表中的全部数据，保留表的结构。

delete 和 truncate 的区别：

• delete可以添加where子句删除表中的部分数据，但是truncate只可以删除表中的全部数据。
• （执行原理）delete删除表中的数据保留结构（扫描全部的数据，花费的时间更长），truncate直接把表删除（drop table）然后再创建一张新表（create table），执行速度比delete更快。

-- 对表中已经存在的数据进行更新

update dept set dname = '总裁办公室' where deptno = 40;

set sql_safe_updates=0;# 设置数据库安全权限（放开，默认是等于1）

update dept set deptno = deptno+1;# 没有where条件的话就是全部更新，全部更新需要打开上面的权限

select * from dept;



-- 删除表数据

delete from dept where dname = 'IT部';

delete from dept; # 删除表中全部记录

select * from dept;



-- 清空数据

truncate dept;

【课后练习】

创建salgrade表并添加数据

通过create和insert命令的语法练习，理解数据库的基本结构，熟悉掌握数据定义语言和数据操作语言的使用。

-- 作业：创建salgrade表并添加数据

create table salgrade(grade int primary key,losal int,hisal int);

desc salgrade;

insert into salgrade value(1,700,1200),

                                            (2,1201,1400),

                                            (3,1401,2000),

                                            (4,2001,3000),

                                            (5,3001,9999);

select * from  salgrade;