3-数据库和表的基本操作_创建一个数据库,名称为 a school ,在其下创建表格,其结构如下-CSDN博客

本文链接：https://blog.csdn.net/WWXDwrn/article/details/128547109

1.SQL命令分类

1.1.DDL[Data Definition Language]：数据定义语言。

1.2.DML[Data Manipulation Language]：数据操纵语言。

1.3.DCL[Data Control Language]：数据控制语言。

PS：使用windows命令行在数据库连接时直接切换到某个数据库：

--->PS：查询数据库、表、表数据的存储目录：

------>a.根据某一列升序（默认，asc可省略）

------>b.根据某一列降序（desc）

--->⑦条件查询：where（超级重要）

------>PS：为什么别名不能使用在where中，可以使用在order by中？

------>a.英语成绩为NULL的人

------>b.数学成绩及格的人

------>c.语文成绩大于数学成绩的人

------>d.总成绩大于150分的人

------>e.语文成绩和数学成绩都>80分的人

------>f.语文成绩或数学成绩>80分的人

------>g.between x and y（包含x和y值）

------>h.in查询

------>i.模糊查询：like/not like

--->⑧分页查询：limit（查询某一个页面上的部分信息，截取了部分行信息）

--->PS：delete VS drop VS truncate

数据库安装成功之后就可以使用客户端来操作数据库了，操作数据库分为两步：

先连接上MySQL数据库（服务器端）；
使用命令来管理或查询数据库。

下面将讲述如何使用SQL命令来管理和查询数据。

1.SQL命令分类

1.1.DDL[Data Definition Language]：数据定义语言。

用来维护存储数据的结构，操作库或表的结构。代表指令：create，drop，alter（修改）。

1.2.DML[Data Manipulation Language]：数据操纵语言。

用来对数据进行操作。代表指令：insert，delete，update；DML中又单独分了一个DQL：数据查询语言，代表指令：select。

1.3.DCL[Data Control Language]：数据控制语言。

主要负责权限管理和事务（运维工程师）。代表指令：grant，revoke，commit。

2.数据库操作

2.1.查询所有数据库（DML）

show databases;

2.2.创建数据库（DDL）

create database [if not exists] 数据库名 [[default] character set 字符集 [[default] collate 效验规则]...]

说明：

MySQL命名规则：所有的数据库名称、表名称、字段名称，不要使用中文，一律使用英文，且要求英文单词必须全部小写，若有多个单词，用下划线“_”进行分隔。数据库名、表名、字段名可以使用" ` ` "（电脑键盘数字1按键左边的按键，相当于转义字符）避免与关键字重名。
MySQL语句默认采用全部小写，防止编写的sql不通用。因为MySQL默认在Windows系统以及其他非Linux系统中大小写不敏感，忽略大小写匹配。而在Linux系统中大小写敏感。
[ ]中的内容为可选项，可写可不写。
character set：指定数据库采用的字符集：utf8/utf8mb4。
collate：指定数据库字符集的效验规则，可以设置大小写是否敏感等信息。

character set（charset）字符集
charset encoding（encoding）字符集编码，简称编码。

编码是依赖于字符集的，就像代码中的接口实现依赖于接口一样。

一个字符集可以有多个编码实现，就像一个接口可以有多个实现类一样。

collate

数据库中有哪些校验规则，可以通过 show collation; 命令来查看。部分校验规则：

utf8_bin将字符串中的每一个字符用二进制数据存储，区分大小写。
utf8_general_ci 不区分大小写，ci为case insensitive的缩写，即大小写不敏感。
utf8_general_cs 区分大小写，cs为case sensitive的缩写，即大小写敏感。

示例：

创建名为student的数据库。

create database student;

注：当创建数据库没有指定字符集和效验规则时，系统使用默认字符集：utf8，效验规则：utf8_general_ci。（general表示一般的，ci表示大小写不敏感）

如果系统没有teacher的数据库，则创建一个名叫teacher的数据库，若有则不创建。

create database if not exists teacher;

如果系统没有school的数据库，则创建一个使用utf8mb4字符集的school数据库，若有则不创建。

create database if not exists school character set utf8mb4;

注：MySQL的utf8编码不是真正的utf8，没有包含某些复杂的中文字符。MySQL真正的utf8是使用utf8mb4，建议都使用utf8mb4。

2.3.查询当前所在数据库（DML）

select database();

2.4.使用/切换数据库（DML）

use 数据库名;

PS：使用windows命令行在数据库连接时直接切换到某个数据库：

2.5.删除数据库（DDL）

drop database [if exists] 数据库名;

注：数据库删除以后，内部看不到对应的数据库，里边的表和数据全部被删除。

通常情况下，数据库很少进行修改操作。多会去修改表。

3.MySQL数据类型

MySQL和Java编程一样，创建表时要考虑数据类型。

MySQL表组成：列名/列数据类型；数据。

3.1.数值类型（分为整型和浮点型）

数据类型	大小	说明	对应java类型	对应C类型
bit[(M)]	M指定位数，默认为1	二进制数，M范围从1~64，存储数据范围从0到2^M-1。	常用boolean对应bit，此时默认是1位，即只能存在0和1	char[]
tinyint	1字节		Byte	signed char
smallint	2字节		Short	short int
int	4字节		Integer	int
bigint	8字节		Long	long long int
float(M,D)	4字节	单精度，M指定总长度（包含D），D指定小数位数。会发生精度丢失。（故在MySQL中不建议使用）	Float	float
double(M,D)	8字节	双精度，M指定总长度（包含D），D指定小数位数。会发生精度丢失。（故在MySQL中不建议使用）	Double	double
decimal(M,D)	M/D最大值+2	双精度，M指定总长度（包含D），D表示小数点后的位数。精确数值。	BigDecimal	char[]
numeric(M,D)	M/D最大值+2	和decimal一样。	BigDecimal	char[]

注：

数值类型可以指定为无符号（unsigned），表示不取负数。
1字节(byte) = 8位(bit)。
对于整型类型的范围：

有符号范围：-2^（类型字节数*8-1）到2^（类型字节数*8-1）-1，如int是4字节，就是-2^31到2^31-1。
⽆符号范围：0到2^（类型字节数*8）-1，如int就是2^32-1。

尽量不使⽤unsigned，对于int类型可能存放不下的数据，int unsigned同样可能存放不下，与其如此，还不如设计时，将int类型提升为bigint类型。

3.2.字符串类型

数据类型	大小	说明	对应java类型	对应C类型
varchar(size)（size不可省略，表示字符串长度是多少，基本都会设置为250）	0~65535字节	可变长度字符串	String	chat[]
text	0~65535字节	长文本数据	String	char[]
mediumtext	0~16777215字节	中等长度文本数据	String	char[]
longtext	0~4294967295字节	极大文本数据	String	char[]
blob	0~65535字节	二进制形式的长文本数据。存储的都是二进制形式的文件流，如：音频文件，图片文件，视频文件。不建议在数据库中直接存储它，因为它会非常占用空间，通常做法是将其存储到专门的文件系统服务器（如阿里云的OOS）。	byte[]	char[]

3.3.日期类型

数据类型

大小

说明

对应java类型

对应C类型

datetime

（时间）

8字节

范围从1000到9999年，不会进行时区的检索及转换。

java.util.Date、

java.sql.Timestamp

MYSQL_TIME

timestamp

（时间戳）

4字节

范围从1970到2038年，自动检索当前时区并进行转换。

java.util.Date、

java.sql.Timestamp

MYSQL_TIME

时间戳是10位(s)和13位(ms，更精确)的数字。

时间和时间戳可以相互转换。

注：

身份证号（18位）不能使用int类型（最大存储大概是27亿，是10位），要使用varchar(18)。
性别一般用bit类型，0女，1男。若有保密、未知项，则使用varchar类型。
姓名用varchar(250)，字符串长度设置大些，避免特殊情况放不下。
身高、体重、价格可能会有小数，用decimal类型。
生日用datetime类型。
描述信息一般不建议使用varchar类型，可能会存不下，要使用text类型。

4.表操作

4.1.表结构操作

4.1.1.创建表

create table 表名 (
    字段名 字段类型,
    字段名 字段类型 comment'注释说明', //使用comment增加字段的说明/注释（可以是中文）
    字段名 字段类型
) character set 字符集 collate 校验规则 engine 存储引擎; //创建表的时候单独设置这张表的字符集、校验规则、存储引擎

engine数据库引擎：

InnoDB（后来作为的默认引擎）：优点是支持事务，稳定性很好；缺点是性能一般。
MyISAM（最早作为的默认引擎）：优点是执行效率高，性能好于InnoDB；缺点是不支持事务，稳定性不如InnoDB。

关系型数据库在创建表时需要先至少将表中的字段名和字段的数据类型都规定好。

好处：稳定性。
坏处：不灵活。

牺牲灵活性保证稳定性。

4.1.2.查看所有表

show tables;

4.1.3.查看表结构

①查询表的主要信息，但查询不到注释说明信息：

desc 表名;

②查询表的所有信息-写法1：

show create table 表名;

③查询表的所有信息-写法2(推荐使用)：

show full columns from 表名;

4.1.4.删除表（一张/多张）

drop [temporary] table [if exists] 表名 [,表名]...

临时表：

程序执行时只用一次，用完就要把它删除掉。业务里几乎不用。

若创建的是临时表，删除表时需要加上temporary。

4.1.5.修改表结构

--->①添加列

MySQL中，列名就是字段名。

alter table 表名 add column 列名 列类型 [comment 备注信息];

alter使用()添加多个字段，且column可省略。

多种写法：

-- 创建测试表
create table test_alter (id int);
 
-- 只写add,没有 column 关键字, 增加的多个字段内容在大括号中
alter table test_alter add (user_name char(5), address varchar(30));
 
-- 带 column 关键字, 增加的多个字段内容在大括号中
alter table test_alter add column (user_name char(5), address varchar(30));
 
-- 分开写多个add，没有 column 关键字, 没有大括号
alter table test_alter add user_name char(5), add address varchar(30);
 
-- 分开写多个add，带有 column 关键字, 没有大括号
alter table test_alter add column user_name char(5), add column  address varchar(30);

 
-- 小扩展，分开写多个除了写add，也可以写modify等其他修改
alter table test_alter add user_name char(5), modify address varchar(15);

--->②删除列

alter table 表名 drop column 列名;

删除列时，这一列对应的数据也会被删除。

--->③修改列

列已经有数据了，不删除，只修改，可以使用change语句，修改字段不会对此字段的原有数据造成任何影响：

alter table 表名 change 原字段名 新字段名 类型 [约束];

--->④修改表名

alter table 旧表名 rename 新表名;

--->⑤修改字符集

alter table 表名 convert to character set 字符集;

设置字符集在后，创建表在前，就会出现虽然设置了编码但还是插入中文失败的情况，此时可以通过修改某个表的字符集来将原来的表支持中文，且保留原有数据：alter table 表名 convert to character set 'utf8mb4';

一个数据库中不能出现2个相同的表名。
一张表中不能出现2个相同的字段名。

在MySQL中：

按1下电脑键盘的"↑"，就能直接使用自己已经写过的上一个命令了，不用再写。
按2下"↑"，就能直接使用已经写过的上上一个命令了。
按n下"↑"，就能直接使用已经写过的前n个命令。

SQL执行报错原因：

标点符号可能使用的是中文->改成英文。
数据库名、表名、字段名可能使用的是关键字（create、desc(describe)等）->名称上加上转义符：`名称`。

4.2.表数据操作——增删改查

注释：在SQL中可以使用"--空格+描述"来表示注释说明。
CRUD ：增加(Create)、查询(Retrieve)、修改(Update)、删除(Delete)四个单词的首字母缩写。

若在MySQL中命令输入错了：

将上面的错误命令复制粘贴到记事本上。

去掉所有"->"，修改错误命令。

上述问题在于逗号使用了中文的，将其改为英文的即可。

后将正确命令复制粘贴会控制台。

4.2.1.添加数据

--->①全列添加

insert [into] 表名 --T-SQL标准中要求加上into，那么在实际开发中我们也要加上into，这样My SQL语句在SQL Server中也可用
   [(字段名 [, 字段名] ...)] --指定某些列，因为是全列添加，可省略
   values (值, [, 值] ...) [, (值, [, 值] ...)] ... --添加时如果不指定列，那么插入值的顺序要和建表时列的顺序保持一致。可添加多行值，一个()是一行

--->②指定列添加

insert [into] 表名 --T-SQL标准中要求加上into，那么在实际开发中我们也要加上into，这样My SQL语句在SQL Server中也可用
   (字段名 [, 字段名] ...) --指定某些列，不可省略
   values (值, [, 值] ...) [, (值, [, 值] ...)] ... --添加时如果指定列，那么插入值的顺序要和指定列的顺序保持一致。可添加多行值，一个()是一行

--->PS：查询数据库、表、表数据的存储目录：
show variables like '%dir%'; --模糊查询系统变量
也可以使用：
select @@datadir; --@@表示系统变量
Win + R 搜索路径：

按回车键Enter：

*.frm：表结构⽂件。
*.ibd：表数据和索引的⽂件。

还可查看其他的系统变量：

4.2.2.查询数据（重点）

select
   [distinct] {* | {字段名 [,字段名] ...}
   [from 表名]
   [where ...]
   [order by 字段名 [ASC | DESC], ...]
   limit ...

--->①全列查询

-- 通常情况下不建议使用 * 进行全列查询
-- 1.查询的列越多，意味着需要传输的数据量越大，会占用很大的带宽，传输速度很慢。
-- 2.可能会影响到索引的使用（索引待后面课程讲解）
select * from 表名;

数据库分为客户端和服务器端：

客户端组装SQL，向服务器端发送SQL。
服务器端执行响应，在.ibd文件中检索对应的内容，将数据返回给客户端。
开发环境，客户端和服务器端在1台服务器上。
生产环境，客户端和服务器端在2台服务器上，这样当一个出现问题，不会影响另一个。

二者间存在数据传输。

故全列查询在开发环境使用较多，因为开发环境的数据一般不大；但在生产环境一定要慎用。

--->②指定列查询

select 列名 [,列名]... from 表名;

注：列名顺序可以随意修改，不影响查询结果，只影响展示数据。

--->③表达式查询

select [列名, [列名,]...] 表达式 from 表名;

将所有数学成绩+10分：

计算每个人的总成绩：

虚拟了一列：

注：使用select进行的表达式计算不会修改原表数据。

--->④别名查询

select 列名 | 表达式 [as] 别名 from 表名;

注：使用select进行的别名查询不会对原始数据造成任何影响。

--->⑤去重distinct查询

select distinct 列名 [,列名]... from 表名;  -- distinct只能放在查询的最前面

数学成绩去重：

多行去重，语文&数学成绩都重复的才会合并：

注：distinct可以使用多个字段联合去重，但是去重的规则是：所有的联合字段全部都相同才会去重，否则不去重。

distinct错误用法：

若有类似业务需求，可用分组查询的group by解决。

--->⑥排序查询：order by

select * | 列名 [,列名]... | 表达式 as 别名 from 表名 order by 列名 [,列名]... | 表达式 | 别名 asc | desc;

------>a.根据某一列升序（默认，asc可省略）

如果不指定排序规则asc/desc，默认使用asc。

《阿里巴巴开发手册》约定：创建的表里要有这3个字段：

id（主键编号，唯一的身份标识）
createtime（创建时间）
updatetime（修改时间）

这样写可以保证数学成绩升序排序，并且在数学成绩相同时，会按照id升序排序。

而不会出现当数学成绩相同时，其顺序每次查询都是随机的、混乱的。

------>b.根据某一列降序（desc）

------>c.根据多列之和升序

------>d.根据多列之和降序

------>e.多列之和使用别名

在order by中能使用别名。
在where中不能使用别名。

------>f.NULL列为最小值

------>g.多列排序

根据语文和数学成绩进行组合排序：

组合排序优先级：先根据最左边字段（列）的值进行排序（升序），如果左边字段的值相同的情况下，再根据右边字段的值进行排序（升序）。

组合排序优先级：先根据最左边字段（列）的值进行排序（升序），如果左边字段的值相同的情况下，再根据右边字段的值进行排序（降序）。

组合排序优先级：如果所有字段的值都相同时（这里使用一个字段表示同样的意思)，按照自然插入值的顺序（id升序）进行排序。

使用多列、多排序规则：

所有查询都不会改变数据库表的物理结构、原始数据。
以上查询查的是所有行数据，所有列或部分列。
下面将学习查询部分行数据。

--->⑦条件查询：where（超级重要）

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

比较运算符：

逻辑运算符：

注：

WHERE条件可以使⽤表达式，但不能使⽤别名。

AND的优先级⾼于OR，在同时使⽤时，需要使⽤⼩括号()包裹优先执⾏的部分。

优秀生要同时满足以下2个条件：

数学成绩>=60或英语成绩>=90。
语文成绩>=90。

------>PS：为什么别名不能使用在where中，可以使用在order by中？

MySQL执行顺序：

from 阶段
where 阶段
group by 阶段（分组查询）
having 阶段（分组查询条件）
select 阶段
order by 阶段
limit 阶段（分页查询）（只有MySQL有，SQL Server和Oricle没有，Oricle的前6个执行顺序和MySQL一样）
select math as m from score where math>=60 order by math desc;
别名产生于select阶段，别名产生之后才能被后面的执行阶段使用。

------>a.英语成绩为NULL的人

建议使用is null/is not null方式。

------>b.数学成绩及格的人

------>c.语文成绩大于数学成绩的人

------>d.总成绩大于150分的人

写法一：

写法二：

使用临时表：它是并不存在的虚拟表，它可以作为另一张表的结果集来用。

select * from 表名; 
--此处的表名既可以是实际存在的物理表，也可以是不存在的虚拟表（任意一个select出来的表都可以作为虚拟表来使用），只要是一个二维表结构就行。

前置工作：

查询：

------>e.语文成绩和数学成绩都>80分的人

------>f.语文成绩或数学成绩>80分的人

------>g.between x and y（包含x和y值）

------>h.in查询

------>i.模糊查询：like/not like

%：匹配任意多个（包含0个）字符。
_：匹配任意一个字符。
二者可以用在任意位置，且二者可以一起使用。

注：MySQL 所有查询应考虑最左匹配原则，将能过滤最多条数的条件放到最左边。(这与索引的底层结构有关)

上面的查询是把一张表里的所有行都查询出来了，把一些列过滤了。where页可以过滤部分行。

--->⑧分页查询：limit（查询某一个页面上的部分信息，截取了部分行信息）

-- 起始下标为 0

-- 从 0 开始，筛选 n 条结果。limit就是限制的意思，限制最多查300条，如果数据量不够，也不会报错
select ... from 表名 [where] [order by] limit n;
-- 从 s 开始，筛选 n 条结果
select ... from 表名 [where] [order by] limit s, n;
-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用。offset就是偏移的意思，offset s就是从第一条结果开始，跳过前s条结果
select ... from 表名 [where] [order by] limit n offset s;

总分数排名前3：

总分数第2页（每页3条数据，4-6条数据）

limit x offset y（x，y 值超出不报错）

PS：