【MySQL】入门 Day1

MySQL数据库操作

---

前言

对于数据分析而言，SQL语言是必不可少的，而大多数行业数据分析师都会选择MySQL，
其特点表现为：开源、体积小、速度快，本篇文章主要讲述MySQL的一些基本知识。

---

一、数据库概述

数据库是用来存储数据的地方
数据库管理系统是管理数据库的软件（与数据库是包含与被包含的关系），可包含多个数据库
SQL是一种结构化查询语言

数据库分为关系型数据库（表结构）和非关系型数据库
表结构：以字段（表格中的列）为基本的存储单位和计算单位

表格数据与表数据区别：

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

二、SQL语言

2.1 书写要求

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

2.2 分类

• 数据定义语言DDL：用于创建（CREATE）、修改（ALTER）、删除（DROP）数据库中的各种对象
• 数据操作语言DML：用于操作数据库表中的记录，常用命令有添加（INSERT）、修改（UPDATE）、删除（DELETE）
• 数据查询语言DQL：用于查询数据库表中的记录，基本结构：SELECT<字段名>FROM<表或视图名>WHERE<查询条件>
• 数据控制语言：用于定义数据库访问权限和安全级别，常用命令：GRANT，REVOKE

2.2.1 数据定义语言

将鼠标定位到所要执行语句任意位置，按下Ctrl+回车即可执行。

2.2.1.1 数据库的增删选查

-- 查看系统中有哪些数据库
show databases;
-- 创建一个新数据库test
create database test;
-- 选择进入数据库
use test;
-- 删除数据库（一定要慎用，一般只有数据库管理员有此权限）
drop database test;

注：创建的数据库名称不能与SQL关键字相同，也不能重复，否则会报错（MySQL报错时会出现错误代码，只需要查一下代码意思即可找出错误点。）

数据库中包含表结构，当我们想对表数据进行操作时首先需要进入数据库

2.2.1.2 数据表的增删改查

-- 创建数据表（可以不指定约束条件，但必须指定表名、字段名及每个字段数据类型）
create table department(deptno int,dname varchar(10),num int);
-- 查看当前数据库中有哪些表
show tables;
-- 查看表结构
desc department;
-- 删除数据表(慎用)
drop table department;

注：表名不能与SQL关键字相同，同一个数据库下的表名不能重复

2.2.1.3 MySQL常用数据类型

• int：大整数型，默认长度最多为11个数字，如int(11)
• float：单精度浮点型，默认float(10,2)，表示最多10个数字，其中有2位小数
• decimal：十进制小数型，适合金额、价格等对精度要求较高的数据存储。默认decimal(10,0)，表示最多10位数字，其中0位小数
• char：固定长度字符串型，长度为1-255。如果长度小于指定长度，右边填充空格。如果不指定长度，默认为1。如char(10)，‘abc ’
• varchar：可变长度字符串型，长度为1-255。必须指定长度，如varchar(10)，‘abc’ 如果长度小于指定长度，不会填充
• text：长文本字符串型，最大长度65535，不能指定长度
• date：日期型，‘yyyy-MM-dd’
• time：时间型，‘hh:mm:ss’
• datetime：日期时间型，‘yyyy-MM-dd hh:mm:ss’
• Timestamp：时间戳，在1970-01-01 00:00:00和2037-12-31 23:59:59之间，如1973-12-30 15:30，时间戳为:19731230153000

2.2.1.4 约束条件

• 约束条件是在表上强制执行的数据检验规则
• 用来保证创建的表的数据完整性和准确性
• 主要在两方面对数据进行约束：空值和重复值

1、主键约束（primary key）

• 表中对于非空不能重复的字段设置成主键约束（如身份证号，每个人都有，且不重复）
• 每个表中只能有一个主键，可设置单字段主键，也可设置多字段联合主键
  列级添加主键约束：create table <表名> (<字段名1> <字段类型1> primary key，……<字段名n> <字段类型n>);
  表级添加主键约束：create table <表名>(<字段名1> <字段类型1>,…<字段名n> <字段类型n>,[constraint 主键约束名] primary key(字段名1[,字段名2,…字段名n]));

主键约束名是可以自己指定的，但不能重复，也不能与关键字相同。只有主键约束有一个，其他约束可以有多个。

2、唯一约束（unique）

• 指定字段的取值不能重复
• 唯一约束与添加主键约束类似。

3、自动增长列(auto_increment)

• 指定字段的取值自动生成，默认从1开始，每增加一条记录，该字段的取值会在前一个基础上加1
• 只适用于整数型，配合主键一起使用（如产品编号，员工编号）
• 创建自动增长约束：create table <表名> (<字段名1> <字段类型1> primary key auto_increment,……<字段名n> <字段类型n>);

4、非空约束(not null)

• 指定字段的取值不能为空
• 创建非空约束：create table <表名> (<字段名1> <字段类型1> not null,……<字段名n> <字段类型n>);

5、默认约束(default)

• 如果新插入一条记录时没有为该字段赋值，系统会自动为这个字段赋值为默认约束设定的值
• 创建默认约束：create table <表名> (<字段名1> <字段类型1> default value,……<字段名n> <字段类型n>);

6、外键约束(foreign key)
在一张表中执行数据插入、更新、删除等操作时，数据库管理系统（DBMS）都会跟另一张表进行对照，避免不规范的操作，以确保数据存储的完整性
• 某一表中某字段的值依赖于另一张表中某字段的值
• 主键所在的表为主表，外键所在的表为从表
• 每一个外键值必须与另一个表中的主键值相对应
创建外键约束：create table <表名> (<字段名1> <字段类型1>,……<字段名n> <字段类型n>,[constraint 外键约束名] foreign key(字段名) references <主表>(主键字段));

例：创建下面两个表

代码展示：

-- 创建带有约束条件的表(因为两张表中有主外键约束,所以需要先创建主键所在的dept,再创建外键所在的emp)
create table dept(
deptno int primary key,
dname varchar(10),
loc varchar(15)
);
create table emp(
empid int primary key auto_increment,
ename varchar(10) unique,
job varchar(10) not null default'未知',
mgr int,
hiredate date,
sal float default 0,
comm float,
deptno int,
foreign key(deptno) references dept(deptno)
);

2.2.1.5 修改数据表

修改数据库中已经存在的数据表的结构
• 修改表名：alter table 原表名 rename 新表名;
• 修改字段名：alter table 表名 change 原字段名 新字段名 数据类型;
• 修改字段类型：alter table 表名 modify 字段名 新数据类型; （也可以修改表中字段排列位置）
• 添加字段：alter table 表名 add 新字段名 数据类型;（不添加排序位置会默认添加到最后一列）
• 修改字段的排列位置：
alter table 表名 modify 字段名 数据类型 first;
alter table 表名 modify 要排序的字段名 数据类型 after 参照字段;
• 删除字段：alter table 表名 drop 字段名;

-- 修改表名
alter table emp rename employee;
-- 修改字段名
alter table employee change empid empno int; 
-- 修改字段类型
alter table employee modify sal decimal default 0;
-- 添加字段
alter table employee add city varchar(20) first; #添加到表中第一列
-- 修改字段的排列位置：
alter table employee modify city varchar(20) after job; #在job列后面
-- 删除字段
alter table employee drop city;

2.2.2 数据操作语言

用于插入数据，前提是数据库中表结构已经创建好。

2.2.2.1 插入数据

字段名与字段值的数据类型、个数、顺序必须一 一对应
• 指定字段名插入：
insert into 表名(字段名1[,字段名2,…]) values(字段值 1[,字段值 2,…]);
• 不指定字段名插入：
insert into 表名 values(字段值 1[,字段值 2,…]);需要为表中每一个字段指定值，且值的顺序须和数据表中字段顺序相同
• 批量导入数据：（路径中不能有中文，并且要将‘\’改为‘\’或‘/’）load data infile ‘文件路径.csv’into table 表名 fields terminated by ‘,’ ignore 1 lines;

-- 指定字段名插入
insert into dept(deptno,dname,loc) values (10,'accounting','new york'),(20,'research','dallas');
-- 不指定字段名插入
insert into dept values (30,'sales','chicago'),(40,'operations','boston');
-- 批量导入数据
load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/archemployee.csv"
into table emp 
fields terminated by ',' 
ignore 1 lines; 

按照mysql8.0版本，需要先将文件放入指定路径才能进行导入

2.2.2.2 更新数据（对表中数据进行修改）

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

update emp set sal=sal+1000 where ename = 'smith'; -- 将smith工资加1000
--若没有where 则对每个员工都加1000，但是这样批量修改需要先加一条语句，否则会报错
set sql_safe_updates=0; -- 设置数据库安全权限

2.2.2.3 删除数据(均保留表结构)

• delete from 表名[ where 删除条件];（删除行）
• truncate 表名;（清空表，与delete from 表名不加 where一样）

• delete和truncate的区别：
delete可以添加where子句删除表中部分数据，truncate只能删除表中全部数据
delete删除表中数据保留表结构，truncate直接把表删除(drop table)然后再创建一张新表(create table)，执行速度比delete快。

delete from emp where deptno=20;--删除表中部门为20的数据