数据库概述、数据定义、数据操作
一、数据库概述
数据库基础概念：按照一定的数据结构来组织、存储和管理数据的仓库。
企业数据存储面临的问题：
- 存储大量数据；
- 大量数据的检索和访问；
- 保证数据信息的一致性和完整性；
- 数据共享和安全；
数据库分类：
- 关系型数据库（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 |
主键约束 |
非空不重复 |
|
NOT NULL |
非空约束 |
不能为空 |
|
UNIQUE |
唯一约束 |
不能重复 |
|
AUTO_INCREMENT |
自增字段 |
自动增长 |
|
DEFAULT |
默认约束 |
默认值 |
|
FOREIGN KEY |
外键约束 |
与主键相对应 |
主键约束（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;
相关文章
