MySQL数据库的SQL语句

大孜然的搬运工
494 阅读11分钟

一、MySQL的常用数据类型

1.1 MySQL数据类型及含义

数据类型含义
int整数，范围为4个字节16位，即{-2147483648-2147483648}
float单精度浮点，长度为4字节32位
double双精度浮点，长度为8字节64位
char固定长度的字符类型，最多255个字符
varchar可变长度的字符类型，最多65535个字符
text文本，最多65535个字符
image图片
decimal{5,2}代表5个有效长度数字，小数点后面有2位

1.2 char与varchar的区别

值CHAR(4)存储需求VARCHAR(4)存储需求
'''    '4个字节''1个字节
'ab''ab  '4个字节'ab '3个字节
'abcd''abcd'4个字节'abcd'5个字节
'abcdefgh''abcd'4个字节'abcd'5个字节
  • char(n) 若存入字符数小于n，会使用空格补于其后，查询的时候会再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。
  • char(n) 为固定长度，char(n)不管是存入几个字符，都将占用n个字节，varchar是存入的实际字符数n+1个字节（n<=255）或2个字节(n>255)，
  • 由于长度占用字节的区别，varchar实际存入字符会比正常存入字符数+1，所以占用空间高，因此char查找效率会很高，varchar查找效率会更低。
  • char插入时可省略，vaechar插入时不会省略，查找时省略。

二、MySQL的基本命令

2.1 登录数据库

mysql -u root -p  # -u 指定用户 -p 该选项后面可以直接跟明文密码进行登录

image.png

image.png

2.2 查看MySQL数据库版本

mysql -V    # 查看MySQL数据库的版本

image.png

2.2 查看当前服务器中的数据库

show databases;   #注意：所有操作都要加上“;”

image.png

2.3 查看MySQL库中的表

方法一：

use <库名>；  # 切换到指定库
show tables； # 显示指定库中的所有表

image-20221024085118551.png

方法二：

show tables from <库名>;   #查看sys数据库中的表
# 该操作不会改变当前所处的数据库

image-20221024085309009.png

2.4 查看表的结构（字段）

方法一：

use <库名>;  # 切换到库
desc <表>;   # 查看库中表字段（数据）

desc <表>\G;  
#页面显示比较混乱时，使用\G可以将每一行表格的字段竖向分段显示

image.png

方法二：

desc <库>.<表> ; 
#查看某库中的某表（同上，不改变当前所在库）

image.png

三、SQL语句

3.1 SQL简介及分类

SQL语句用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理邓功能。

  • SQL语言分类
    • DDL：数据定义语言；用于创建数据库对象，如库、表、索引等
    • DML：数据操作语言：用于对表中的数据进行管理
    • DQL：数据查询语言：用于从数据表中查找符合条件的数据记录
    • DCL：数据控制语言：用于设置或者更改数据库用户或角色权限

3.2 创建新的数据库

create database <新库名>; #创建新的数据库

image.png

3.3 创建新的表

方法一：

use <库>;               #切换到指定库
create table <新表名>（字段1 数据类型,字段2 数据类型,...字段n 数据类型primary key (主键名)）;
# 创建表在当前库中 
# primary key (主键名) 可以给某一列设置主键
# 主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键

image.png

方法二：

create table <库名>.<表名>（字段1 数据类型,字段2 数据类型,...字段n 数据类型primary key (主键名)）; 
# 该操作一样不需要进入数据库再进行创建，可以直接使用

3.4 添加表中各字段内容

方法一：

insert into <库名>.<表名>(字段1,字段2,字段3,字段4,字段5) values (字段值1,字段值2,字段值3,字段值4,字段值5);
# 如果字段的数据类型中使用了字符类型（如char、varchar等字符类型），输入字段值时两边需要加上单引号
# 该操作也可以指定某些字段的值，进行添加，未添加的值均按照之前的设定的默认值进行添加

image.png

方法二：

insert into school.class1 values(字段值1,.....,字段值n);
#如果字段值是按照字段的顺序，依次填入的，且没有未填的值，可直接忽略填写前面的字段内容

image.png

方法三：

insert into <表名>(id,name) values(3,'wangwu');
# 指定某个字段加入字段值

image.png image.png

3.5 查看表中内容

select * from <表名>; #当前库中指定表中内容

select * from <库名>.<表名> #查看指定库中表内容


select * from <表名>\G #将表中每一行记录的数据纵向查看

image.png

select * from <表名> limit 2; #查看初始两行

image.png

select * from <表名> limit 2，3; #查看第二行后面的3行，即3-5行

image.png

select * from <表名> where 条件表达式 
#指定查看符合条件的行，and为需要满足左右两个条件的记录行才会显示

image.png

select <字段1>,<字段2> from <表名>  
# 查看表中指定字段

image.png

3.6 删除库/表

drop database <库名>;      #删除库
drop table <表名>;         #删除当前库中的指定表

drop table <库名>.<表名>;  #直接删除某库中的某表

3.7 在数据表中删除指定的数据记录

delete from <表名> where 条件表达式
#删除指定表中的数据

image.png

四、SQL语句用法实例（一）

4.1 修改表名

alter table <旧表名> rename <新表名>;     #更改表名

image.png

4.2 扩展表结构（即增加表的纵列，增加字段）

alter table <表名> add <字段> <数据类型>      #在已有的表中添加相关字段和制约属性
alter table class1 add cardid char(18) default '暂不提供';
#在class1表中添加cardid字段，设置数据类型为字符，最多填写18个字节，设置default默认值为'暂不提供'

4.3 修改指定字段的值

update <表名> set <字段>=<字段值> where 条件表达式

image.png

image.png

4.4 已有表中添加字段

alter table <表名> add <字段> <数据类型>;

image.png

4.5 修改字段名及数据类型

alter table <表名> change <旧字段名> <新字段名> 数据类型;
# 该命令可以更改表结构中的字段名称，并修改字段的数据类型

#如果只想要更改字段的数据类型也可以执行以下命令格式
alter table <表名> change <字段名> <字段名> 数据类型;
#以上两个字段内容一致

image.png

image.png

4.6 删除指定字段

alter table <表名> drop <字段>;
#指定表删除指定字段

image.png

五、SQL语句用法实例(二)

5.1 克隆表

方法一：

create table <新表> like <旧表>;
#通过like方法复制旧表的表结构
insert into <新复制表> select * from <旧表>;
使用insert into 进行插入（导入）数据，数据选取使用select * from 提取旧表内容

image.png

image.png

image.png 方法二：

create table <新表> (select * from <旧表>);
# 此方法只能正常导入旧表的数据，但是无法完整复制旧表的结构

image.png

5.2 清空表，删除表内的所有数据

方法一：

delete from <表名>；
  • delete 清空表后，返回结果内有删除的记录条目;delete工作时是一行一行的删除记录数据的;
  • 如果表中有自增长字段，使用delete from删除所有记录后，再次新添加的记录会从原来最大的记录ID后面继续自增长

方法二：

truncate table <表名>;
  • truncate 清空表后，没有返回被删除的条目；truncate 工作时是将表结构按原样重新建立，因此在速度上 truncate 会比delete清空表快;使用truncate table清空表内数据后，ID会从1开始重新记录。

5.3 创建临时表

  • 临时表创建成功之后，使用show tables命令是看不到创建的临时表的，临时表会在连接退出后被销毁。如果在退出连接之前，也可以执行增删改查等操作，比如使用drop table 语句将会直接删除临时表。

      create temporary table <表名> (字段1 数据类型,字段2 数据类型,...)

image.png

image.png

临时表主要运用于测试场景

六、补充

6.1 数据类型的属性

MySQL关键字含义
NULL数据列可包含NULL值
NOT NULL数据列不允许包含NULL值
DEFAULT默认值
PRIMARY KEY主键
AUTO_INCREMENT自动递增，适用于整数类型
UNSIGNED无符号
CHARACTER SET name指定一个字符集

6.2 MySQL常见约束

1、非空约束(not null)

非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。

Null类型特征：所有的类型的值都可以是null，包括int、float 等数据类型

2、唯一性约束(unique)

唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。

唯一约束不允许出现重复的值，但是可以为多个null。

同一个表可以有多个唯一约束，多个列组合的约束。

在创建唯一约束时，如果不给唯一约束名称，就默认和列名相同。

唯一约束不仅可以在一个表内创建，而且可以同时多表创建组合唯一约束。

3、主键约束(primary key) PK

主键约束相当于 唯一约束 + 非空约束 的组合，主键约束列不允许重复，也不允许出现空值。

每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别创建。

当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

4、外键约束(foreign key) FK

外键约束是用来加强两个表（主表和从表）的一列或多列数据之间的连接的，可以保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。

创建外键约束的顺序是先定义主表的主键，然后定义从表的外键。也就是说只有主表的主键才能被从表用来作为外键使用，被约束的从表中的列可以不是主键，主表限制了从表更新和插入的操作。

5、默认值约束 (Default)

若在表中定义了默认值约束，用户在插入新的数据行时，如果该行没有指定数据，那么系统将默认值赋给该列，如果我们不设置默认值，系统默认为NULL。

6、自增约束(AUTO_INCREMENT)

自增约束(AUTO_INCREMENT)可以约束任何一个字段，该字段不一定是PRIMARY KEY字段，也就是说自增的字段并不等于主键字段。

但是PRIMARY_KEY约束的主键字段，一定是自增字段，即PRIMARY_KEY 要与AUTO_INCREMENT一起作用于同一个字段。

当插入第一条记录时，自增字段没有给定一个具体值，可以写成DEFAULT/NULL，那么以后插入字段的时候，该自增字段就是从1开始，没插入一条记录，该自增字段的值增加1。当插入第一条记录时，给自增字段一个具体值，那么以后插入的记录在此自增字段上的值，就在第一条记录该自增字段的值的基础上每次增加1。

也可以在插入记录的时候，不指定自增字段，而是指定其余字段进行插入记录的操作。

6.3 如何创建外键约束？

① 外键的定义

如果一个属性字段X在表一中是主键，而在表二不是主键，则字段X称为表二的外键。

  • 主键表和外键表的理解
    • 以公共关键字作为主键的表为主键表（父表、主表）
    • 以公共关键字作为外键的表为外键表（从表、外表）

注意：与外键关联的主表的字段必须设置为主键。要求从表不能是临时表，主从表的字段具备相同的数据类型，字符长度和约束。

② 主外键表的应用实例

创建2个表，一个专业名称的表，一个学生的表，学生所学的专业编号与，专业表中的专业对应编号进行一一对应

create table profession(proid int,proname char(10));
# 创建一个名为profession（专业）的表

create table student (id int primary key,name char(10),age int,proid int not null);
# 创建一个名为student(学生)的表

alter table profession add primary key(proid);
# 给profession表增加主键，设置主键为proid

alter table student add constraint FK_pro foreign key(proid) references profession(proid);
# 给student表增加从键proid，并且绑定profession的主键proid作为主从键绑定
# constraint FK_pro 指定外键的名称为FK_pro

image.png

image.png

image.png

image.png

PS：插入数据时需要先在主表中插入数据，删除数据如果需要删除主表数据，需要先把外键表中关联的相关数据删除，才可以进行删除操作。

  • 验证测试：

外键表的数据增加 image.png

主键表的数据增加

image.png

外键表的数据删除

image.png

主键表的数据删除

image.png

avatar
文章
阅读
粉丝