数据库基础

数据库基础

一、DataGrip软件的使用

1.基础配置

• 修改背景颜色：Appearance -> Theme
• 修改字体大小： 搜索font -> Font
• 修改SQL语句的大小写： Editor -> Code Style -> SQL -> MySQL

2.软件界面说明

DataGrip软件界面包含：菜单栏、数据文件、工作区、脚本文件、处理结果等区域。

3.常见的实用操作

DataGrip软件的使用操作包含：刷新信息、关闭快捷提示、显示近期需使用的数据库名、运行SQL语句、创建一个新的SQL语句。

4.常用的快捷键

快捷名称	含义
Ctrl + /	单行注释
Ctrl + D	快速复制一行。
Ctrl + V	快速删除一行。
Ctrl + Alt + L	快速格式化代码。

二、数据库操作【DDL】

1.创建数据库

语法：

create database [其他] 数据库名 [设置编码格式]
# 说明：使用[]的内容表示可选选项

2.查看数据库

语法：

-- 查看有哪些数据库
SHOW DATABASES;
-- 查看数据库的创建语句
SHOW CREATE DATABASE 数据库名；

3.删除数据库

DROP DATABASE 数据库名称；
-- 注意：当使用SQL命令删除数据库时，一定要慎重。

4.其他操作命令

需要切换使用另一个数据库时，命令：

USE 数据库名；

查看当前正在使用的数据库，命令：

SELECT DATABASE();
-- 注意：在MySQL中，不允许出现同名的数据库名。

三、数据类型

1.字段类型

在MySQL数据库中，常用的字段类型如下：[数据类型]

1. 整数：int
2. 小数：double, 注意：在编程开发中，一般把小数称为浮点数；
3. 字符串：varchar
4. 时间日期：date,datetime

2.整数

整数的类型有：

类型	类型名称	说明	字节大小
tinyint	很小的整数	-128~127	1
smallint	小的整数	-32768~32767	2
mediumint	中等大小的整数	-8388608~8388607	3
int	普通大小的整数	-2147483648~2147483647	4
bigint	大整形的整数	-19223372036854775808~9223372036854775807	8

3.浮点数

浮点数类型有：

类型	类型名称
float	单精度浮点数
double	双精度浮点数
decimal(m,d)	定点数，m表示整数的总长度，d表示小数长度

4.字符串

类型	说明	使用场景
char[(m)]	固定长度，小型数据，m为0~255之间的整数定长（固定长度）	身份证号、手机号、电话
varchar(m)	可变长度，小型数据，m为0~65535之间的整形变长（变化长度）	密码、姓名、地址、品牌、型号
text	可变长度，字符个数大于4000，允许长度0~65535字节。	小型文章、技术博客、新闻正文
mediumtext	可变长度，中型文本数据，允许长度0~167772150字节。	中型文本数据，例如论文内容
longtext	可变长度，极大型文本数据，允许长度0~4294967295字节。	极大型文本数据，例如一本小数内容

char与varchar的区别：

固定长度使用char(长度)，可变长度使用varchar(长度)。

5.时间日期

类型	类型名称	实例
year	YYY 1901~2155	2017
time	HH:MM:SS -838:59:59~838:59:59	12：29：59
date	YYYY-MM-DD 1000-01-01~9999-12-3	2020-01-01
datetime	YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~9999-12-31 23:59:59	2020-01-01 12:29:59
timestamp	YYYY-MM-DD HH:MM:SS 19700101 00:00:01 UTC~2028-01-19 03:14:07UTC	1970-01-01 00:00:01 UTC~2038-01-01 00:00:01 UTC

其他类型

图片、音频、视频

注意：对于图片、音频、视频等文件，由于文件本身容量大、占用存储空间。因此，一般是在表中，存储这个文件的保存路径。

五、数据表操作【DDL】

数据表操作命令有:

# 创建数据表：
CREATE TABLE 表名(
	字段名1 数据类型(长度) [约束]
    字段名2 数据类型(长度) [约束]
    字段名3 数据类型(长度) [约束]
);

# 查看有哪些数据表：
SHOW TABLES;
# 查看数据表的创建语句
SHOW CREATE TABLE 表名;
# 查看表结构
DESC 表名;

# 删除数据表语法：
DROP TABLE 表名;

六、修改数据表【DDL】

1.添加一列

ALTER TABLE 表名 ADD 字段名 类型(长度);
ALTER TABLE 表名 ADD 字段名 类型(长度) [FIRST|ALTER 其他字段名称];
# FIRST:把新添加的字段放在第一位
# AFTER 其他字段名称：把新添加的字段放在指定字段的后面

2.修改列类型

ALTER TABLE 表名 MODIFY 要修改的字段名 类型(长度);
ALTER TABLE 表名 MODIFY 要修改的字段名 类型(长度) [约束];  # 不为空

3.修改列名

• 修改列明，指的是：给数据表的列明进行修改处理。
• 语法：

alter table 表名 change 旧列名 新列名 类型(长度) [约束];

4.删除一列

• 删除一列，指的是：删除数据表的一列字段。
• 语法：

alter table 表名 drop 列名;

5.修改表名

• 修改表名，指的是修改数据表的名称。
• 语法：

rename table 表名 to 新表名;

七、 插入数据【增】

1. 数据操作命令含义

• 数据操作语言DML语法结构

  • 插入(insert)数据，常见语法：

  insert into 表名(字段名1,字段名2,字段名3,...) values(值1,值2,值3,...)
  

  • 修改(update)数据，常见语法：

  update 表名 set 字段名1=值,字段名2=值,字段名3=值,... where 条件;
  

  • 删除(delete)数据，常见语法：

  delete from 表名 where 条件;
  

2.插入一条数据

• 给数据表中插入一条数据，语法：

insert into 表名 values(值1,值2,...);
insert into 表名(字段名1,字段名2,字段名3,...) values(值1,值2,值3,...);

• 说明：
  • 给字符串类型的字段插入值时，要使用单引号把值引起来，否则添加数据会报错
  • 表名后字段个数与类型等，要与values后的值对应

3.插入多条数据

• 给数据表中插入多条数据，语法：

insert into 表名 [(字段名1,字段名2,字段名3,...)] values(值1,值2,值3,...),(值1,值2,值3,...);

• 说明：
  • 多条数据之间使用,分割。

4.主键约束与自动增长

• 给字段添加主键约束与自动增长的语法：

create table 表名(
	字段名 数据类型[(长度)] primary key auto_increment,
	...
);

• 说明：
  • 可以不给字段添加值，会默认有值。

八、修改数据【改】

1.修改所有数据

• 给数据表修改所有数据的语法：

update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3,...;

• 说明：
  • 当数据更新时，如果不指定条件，则会把这个数据表的所有数据记录全部跟新。

2.按条件修改数据

• 给数据表条件修改数据的语法：

update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3,... where 条件;

• 说明：
  • 当满足条件时，数据会被修改。

九、删除数据【删】

1.删除所有数据

• 删除所有数据的语法：

delete from 表名;

• 说明：
  • 不加条件时，会删除所有数据内容。

2.按条件删除数据

• 按条件删除数据的语法：

delete form 表名 where 条件;

• 说明：
  • 当满足条件时，数据会被删除。

3.清空数据的区别

• 清空数据有两种方式：

# 删除数据表数据
delete from 表名;
# 销毁及删除数据表数据
truncate 表名；

• 说明：

  • 两种方式都可以用于清空数据内容。

• delete和truncate删除表的区别：

  	delete from	truncate
  属于那种语言分类？	数据操作语言（DML）	数据定义语言（DDL）
  是否可设置条件删除？	可以带条件删除	不能带条件删除
  删除时具体操作如何？	删除大量数据速度慢，只能删除数据，主键自增序列不会清零。	删除大量数据速度快，主键自增序列会清零。

十、SQL约束

SQL约束简介

• SQL约束是用于指定字段数据的规则，可以保证数据更加精确、可靠。
• 常见的SQL约束：

约束名称	关键字	含义
主键	primary key	MySQL建议所有表的主键字段都叫id，类型为int.
非空	not null	此字段不允许填充空值。
唯一	unique	此字段的值不允许重复。
默认值	default	当不填写字段对应的值时，会使用默认值；如果填写时，则以填写为准。
外键	foreign key	涉及多张表时才会使用。对表与表的关系字段进行约束，当表与表关系字段填写值时，会到关联的表中查询此值是否从在。如果存在则填写成功；如果不存在，则填写失败。

主键约束

1.添加主键约束

• 在创建表示添加主键约束语法：

create table 表名(
	字段名 数据类型(长度) primary key [其他约束],
	...
)default charset utr8;

• 说明：
  • 给primary key主键约束的字段设定值时，不允许出现重复值；[唯一性]
  • 主键约束列不能包含null值；[非空]
  • 每个表都应该有一个主键约束，且每个表只能有一个主键。

2.单独添加主键约束

• 当数据表创建完成后，单独添加主键约束，语法：

# 创建表后，使用alter table关键字添加主键
alter table 表名 add primary key(字段名);

3.删除主键约束

• 给有主键约束的数据表删除主键约束，语法：

# 使用alter table关键字删除主键
alter table 表名 drop primary key;

• 说明：
  • 当删除主键约束后，字段依旧有非空约束，不能为空。

4.主键约束：自动增长

• 在创建表时让主键约束字段为自动增长[auto]，语法：

create table 表名(
	字段名 数据类型(长度) primary key auto_increment,
	....
)default charset utf8;

• 说明：
  • 自动增长列的字段必备是整型；
  • 自动增长列必须为X键（例如主键、外键）。

5.主键约束：验证主键约束的字段效果

• 当给字段设定了主键约束后，则：
  • 值是唯一的；
  • 值是非空的。

6.非空约束：添加非空约束

• 当在创建表时给某个字段添加非空约束，语法：

create table 表名(
	字段名 数据类型(长度) [其他约束] not null,
)default charset utf8;

• 说明：
  • 当设定主键约束后，默认值是非空的；
  • 当给某列设定为not null后，在添加数据时必须向该字段传递非空null值
• 当设定为非空约束后，则：
  • not null约束强制字段不接受null值；
  • not null约束强制字段传递数据时，必须有数值。

唯一约束：添加唯一约束

• 当在创建时给某字段添加唯一约束，语法：

create table 表名(
	字段名 数据类型(长度) [其他约束] unique,
	...
)default charset utf8;

• 说明：
  • 每个表可以有多个unique唯一约束，但是每个表只能有一个primary key主键约束。
• 当设定为唯一约束后，则：
  • unique约束用于唯一标识数据表中的每条数据；
  • primary key字段默认唯一；
  • unique和primary key约束均为列提供了唯一性保证。

默认值约束

• 当在创建表时给某个字段添加默认值约束，语法：

creat table 表名(
	字段名 数据类型(长度) [其他约束] default 值,
	...
)default charset utf8;

• 说明：
  • 每个表可以有多个默认值约束；
  • 设置了默认值后，若不传递数值，则直接使用默认值。
• 当设定为默认值约束后，则：
  • 有了默认值约束后，当不传递字段对应的值时，会使用默认值；
  • 有了默认值约束后，当传递字段对应值时，会以传递的具体值为准。

十一、数据查询语言

1.简单查询

• 简单查询有两种方式：
  • 查询所有数据
  • 按不同字段名来查询数据
• 语法：

# 查询表内所有数据
select * from 表名;
# 查询表内数据，以指定的列来显示结果
select [distinct] from 字段名1,字段名2,... from 表名;

2.比较查询

• 对数据进行条件处理，通用语法：

select [*|字段1,字段2,...] from 表名 where 条件;

• 在where条件中，使用比较运算符来查询结果，比较运算符有：

比较运算符	含义
=	等于
>	大于
>=	大于等于
<	小于
<=	小于等于
!=[或<>]	不等于，忽略<>

3.范围查询

• 范围查询是指在某个范围内进行查询，分别有in和between…and…。

  • in是非连续指的范围查询，语法：

  select * from 表名 where 字段名 in (范围值1,范围值2,...);
  

  • between and 是用于值在连续范围的查询，语法：

  select * from 表名 where 字段名 between 范围值1 and 范围值2;
  

4.逻辑查询

• 对数据进行条件筛选处理，通用语法：

select [*|字段名1,字段名2,...] from 表名 where 条件；

• 逻辑运算符有：

逻辑运算符	含义
and	与、和、且。 1.逻辑运算符and的前后一般是True或者False的结果 2.当两个条件都为True(真)，整个操作结果才是True真。
or	或、或者 1.逻辑运算符or的前后一般都是True或False的结果； 2.当两个条件都为False(假)，整个操作结果才是False假。
not	一般情况下，要设定not取反，对整个操作结果做not处理。

5.模糊查询

• 模糊查询的语法：

select * from 表名 where 字段名 like '%某个字段%';
select * from 表名 where 字段名 like '某个字_'

• 说明：
  • %表示任意多个字符，_表示一个任意字符。

6.非空查询

• 非空查询的语法：

select * from 表名 where 字段名 [条件];

• 说明：
  • [条件]部分就可以使用空或非空来表示。
• 非空运算符有：

非空查询	含义
is null	判断为空
is not null	判断为非空

7.排序查询

• SQL查询通用语法：

select [*|字段名1,字段名2,...|函数(...)] from 表名 where 条件 另外要求;

• 排序查询指的是对某字段进行升序或者降序的形式来查询结果，语法：

select * from 表名 where 条件 order by 字段名 [asc|desc];

• 说明：
  • asc从小到大排列，即升序;
  • desc从大到小排序，即降序;
  • 默认按照列值从小到大进行排序（即asc升序）。

8.聚合查询

• 聚合查询的语法：

select 函数(...) from 表名 [where 条件];

• 说明：
  • 函数(…)指的是聚合函数。
• 聚合函数有：

聚合函数	作用
count(col)	用于统计指定列不为null的总数据条数。
sum(col)	用于计算指定列的数值和。
max(col)	用于计算指定列的最大值。
min(col)	用于计算指定列的最小值。
avg(col)	用于计算指定列的平均值。