MySQL 数据库基础知识（系统化一篇入门）

1、DDL(Data Definition Language) 数据定义语言，用来操作数据库、表、列等；常用语句：CREATE、 ALTER、DROP
2、DML(Data Manipulation Language) 数据操作语言，用来操作数据库中表里的数据；常用语句：INSERT、 UPDATE、 DELETE
3、DCL(Data Control Language) 数据控制语言，用来操作访问权限和安全级别；常用语句：GRANT、DENY
4、DQL(Data Query Language) 数据查询语言，用来查询数据常用语句：SELECT

三、数据库的数据类型

3.1、数据库的列类型

数值类型

数据类型	描述	大小
tinyint	十分小的数据	1个字节
smallint	较小的数据	2个字节
mediumint	中等大小的数据	3个字节
int	标准的整数	4个字节
bigint	较大的数据	8个字节
float	浮点数	4个字节
double	浮点数	8个字节
decimal	字符串形式的浮点数，一般用于金融计算

字符串

数据类型	描述	大小
char	字符串固定大小	0~255
varchar	可变字符串	0~65535
tinytext	微型文本	2^8-1
text	文本串	2^16-1

时间日期

数据类型	描述	格式
date	日期格式	YYYY-MM-DD
time	时间格式	HH：mm：ss
datetime	最常用的时间格式	YYYY-MM-DD HH：mm：ss
timestamp	时间戳，1970.1.1到现在的毫秒数
year	年份表示

3.2、数据库的字段属性

UnSigned

无符号的
声明了该列不能为负数

ZEROFILL

0填充的
不足位数的用0来填充 , 如int(3),5则为005

Auto_InCrement

通常理解为自增，自动在上一条记录的基础上默认+1
通常用来设计唯一的主键，必须是整数类型
可定义起始值和步长

NULL 和 NOT NULL

默认为NULL , 即没有插入该列的数值
如果设置为NOT NULL , 则该列必须有值

DEFAULT

默认的
用于设置默认值
例如,性别字段,默认为"男" , 否则为 “女” ; 若无指定该列的值 , 则默认值为"男"的值

四、数据库、数据表的基本操作

4.1、数据库的基本操作

MySQL要想将数据存储到数据库的表中，首先要创建一个数据库。创建数据库就是在数据库系统中划分一块空间存储数据，语法如下：

创建数据库MySQL命令：

create database 数据库名称;

删除数据库MySQL命令：

drop database 数据库名;

查询出MySQL中所有的数据库MySQL命令：

show databases;

将数据库的字符集修改为gbk MySQL命令：

alter database 数据库名 character set gbk;

切换数据库 MySQL命令：

use 数据库名;

查看当前使用的数据库 MySQL命令：

select database();

4. 2、数据表的基本操作

1、创建、查看数据表

数据库创建成功后可在该数据库中创建数据表(简称为表)存储数据。请注意：在操作数据表之前应使用“USE 数据库名;”指定操作是在哪个数据库中进行先关操作，否则会抛出“No database selected”错误。
创建数据表语法如下：

 create table 表名(
         字段1 字段类型,
         字段2 字段类型,
         …
         字段n 字段类型
);

查看当前数据库中所有表 MySQL命令：

show tables;

查表的基本信息 MySQL命令：

show create table 数据表名;

查看表的字段信息 MySQL命令：

desc 数据表名;

2、修改数据表

有时，希望对表中的某些信息进行修改，例如：修改表名、修改字段名、修改字段数据类型…等等。在MySQL中使用alter table修改数据表.

修改表名 MySQL命令：

alter table 旧数据表名 rename to 新数据表名;

修改字段名 MySQL命令：

alter table 数据表名 change 旧字段名 新字段名 varchar(10);

修改字段数据类型 MySQL命令：

alter table 数据表名 modify 字段名 数据类型;

增加字段 MySQL命令：

alter table 数据表名 add 新增字段名 varchar(50);

删除字段 MySQL命令：

alter table 数据表名 drop 字段名;

删除数据表 MySQL命令：

drop table 表名;

五、数据表字段的约束

为了防止往数据表中插入错误的数据，在MySQL中，定义了一些维护数据库完整性的规则，即表的约束。

常见约束：

约束条件	说明
PRIMARY KEY	主键约束用于唯一标识对应的记录
FOREIGN KEY	外键约束
NOT NULL	非空约束
UNIQUE	唯一性约束
AUTO_INCREMENT	主键自增加约束
DEFAULT	默认值约束，用于设置字段的默认值

5.1、主键约束

在MySQL中，为了快速查找表中的某条信息，可以通过设置主键来实现。主键约束是通过PRIMARY KEY

定义的，它可以唯一标识表中的记录，这就好比身份证可以用来标识人的身份一样。

主键约束基本语法：

字段名 数据类型 primary key;

设置主键约束(primary key)的第一种方式
示例：MySQL命令：

CREATE TABLE StuDent(
id int primary key, 
name varchar(20)
);

设置主键约束(primary key)的第二·种方式
示例：MySQL命令：

CREATE TABLE StuDent(
id int unsigned，
name varchar(20),
primary key(id)
);

5.2、外键约束

如果表A的主键是表B中的字段，则该字段称为表B的外键；另外表A称为主表，表B称为从表。外键是用来

实现参照完整性的，不同的外键约束方式将可以使两张表紧密的结合起来，特别是修改或删除的级联操作将使

得日常维护更轻松。外键主要用来保证数据的完整性和一致性。其基本语法如下：

-- 在创建数据表时语法如下：
CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段)
-- 将创建数据表创号后语法如下：
ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段);

示例：创建一个学生表 MySQL命令：

create table student(
id int primary key,
name varchar(20)
);

示例：创建一个班级表 MySQL命令：

create table class(
classid int primary key,
studentid int
);

示例：学生表作为主表，班级表作为副表设置外键， MySQL命令：

alter table class add constraint fk_class_studentid foreign key(studentid) references student(id);

【补充】一个表允许有多个外键，且外键只适用于InnoDB表，MyISAM表不支持外键。InnoDB表和MyISAM表的主要区别是：InnoDB表支持事务、行锁和外键，MyISAM不支持；InnoDB表索引式修改效率较MyISAM高，MyISAM表查询、插入和删除效率较InnoDB高，而且MyISAM索引和数据分离，索引压缩存储，而InnoDB索引和数据紧密结合，不支持压缩，所以MyISAM内存利用率比InnoDB高；另外MyISAM的移植性也更强。

删除外键语法如下：

alter table 从表名 drop foreign key 外键名；

示例：删除外键 MySQL命令：

alter table class drop foreign key fk_class_studentid;

5.3、唯一性约束

唯一性约束即UNIQUE用于保证数据表中字段的唯一性，即表中字段的值不能重复出现，其基本的语法格式如下所示：

字段名 数据类型 UNIQUE;

示例：MySQL命令：

create table student(
id int unique,
name varchar(20)，
);

5.4、默认值约束

默认值约束即DEFAULT用于给数据表中的字段指定默认值，即当在表中插入一条新记录时若未给该字段赋值，那么，数据库系统会自动为这个字段插人默认值；其基本的语法格式如下所示：

字段名 数据类型 DEFAULT 默认值；

示例：MySQL命令：

create table student(
id int,
name varchar(20),
gender varchar(10) default 'male'
);

5.5、非空约束

非空约束即 NOT NULL指的是字段的值不能为空，基本的语法格式如下所示：

字段名 数据类型 NOT NULL;

示例：MySQL命令：

create table student(
id int，
name varchar(20) not null，
);

5.6、自增约束

在数据表中，若想为表中插入新记录自动生成唯一的ID，可以使用AUTO_INCREMENT约束来实现。AUTO_INCREMENT约束的字段可以是任何整数类型。默认情况下，改字段的值是从1开始自增的。其基本语法如下：

id lNT PRIMARY KEY AUTO_INCREMENT；

示例：MySQL命令：

create table student (
id lNT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)，grade FLOAT
);

六、数据表添加修改数据

6.1、在表中插入数据

向数据表中全部字段添加数据 MySQL命令：

insert into 表名 values(值1，值2，值3);

向数据表中部分字段添加数据 MySQL命令：

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

6.2、修改数据表中数据

修改表中的某一字段全部的记录 MySQL命令：

update  表名 set 字段 = 值;

修改表中符合条件的记录 MySQL命令：

update  表名 set 字段 = 值 where 条件;

6.3、删除数据表中数据

删除表中全部的记录 MySQL命令：

delete from 数据表名;

删除表中符合条件的记录 MySQL命令：

delete from 数据表名 where 条件;

七、MySQL 数据表查询

查询数据指从数据库中获取所需要的数据。查询数据是数据库操作中最常用，也是最重要的操作。用户可以根据自己对数据的需求，使用不同的查询方式。通过不同的查询方式，可以获得不同的数据。MySQL中是使用SELECT语句来查询数据的。

7.1、MySQL数据表简单查询

简单查询即不含where的select语句。在此，我们讲解简单查询中最常用的两种查询：查询所有字段和查询指定字段。

7.1.1、MySQL查询所有字段

查询所有字段 MySQL命令：

select * from students;

7.1.2、MySQL查询指定字段

查询指定字段 MySQL命令：

select stuid from students;

7.1.3 、常数的查询

在SELECT中除了书写列名，还可以书写常数。可以用于标记
常数的查询日期标记 MySQL命令：

select stuid,name,'2021-03-02' from students;

7.1.4、从查询结果中过滤重复数据

在使用DISTINCT 时需要注意：
在SELECT查询语句中DISTINCT关键字只能用在第一个所查列名之前。
MySQL命令格式：

select distinct，name from students；

7.1.5、算术运算符（举例加运算符）

在SELECT查询语句中还可以使用加减乘除运算符。
查询学生10年后的年龄 MySQL命令：

select name,age+10 from students;

7.2 聚合函数

在开发中，我们常常有类似的需求：统计某个字段的最大值、最小值、平均值等等。为此，MySQL中提供了聚合函数来实现这些功能。所谓聚合，就是将多行汇总成一行；其实，所有的聚合函数均如此——输入多行，输出一行。聚合函数具有自动滤空的功能，若某一个值为NULL，那么会自动将其过滤使其不参与运算。
聚合函数使用规则：
只有SELECT子句和HAVING子句、ORDER BY子句中能够使用聚合函数。例如，在WHERE子句中使用聚合函数是错误的。
接下来，我们学习常用聚合函数。

函数	作用
sum（）	计算指定列的数值和，如果指定列类型不是数值类型则计算结果为0
avg（）	计算指定列的平均值，如果指定列类型不是数值类型则计算结果为0
max（）	计算指定列的最大值，如果指定列是字符串类型则使用字符串排序运算
min（）	计算指定列的最小值，如果指定列是字符串类型则使用字符串排序运算
count（）	统计表中数据的行数或者统计指定列其值不为NULL的数据个数

7.2.1、sum（）语句格式

MySQL语法格式：

select sum(字段名） from 数据表名;

查询该学生表中年纪的总和 MySQL命令：

select sum(age) from students;

7.2.2、avg（）语句格式

MySQL语法格式：

select avg(字段名） from 数据表名;

查询该学生表中年纪的平均数 MySQL命令：

select avg（age） from students；

7.2.3、max（）语句格式

MySQL语法格式：

select max(字段名） from 数据表名;

查询学生表中年龄最大的学生 MySQL命令：

select max（age） from students；

7.2.4、min（）语句格式

MySQL语法格式：

select min(字段名) from 数据表名;

查询该学生表中年纪最小的学生 MySQL命令：

select min(age) from students;

7.2.5、count（）语句格式

MySQL语法格式：

select count(字段名）from 数据表名;

统计学生表中有多少人 MySQL命令：

select count(*) from students;

7.3、条件查询

数据库中存有大量数据，我们可根据需求获取指定的数据。此时，我们可在查询语句中通过WHERE子句指定查询条件对查询结果进行过滤。

7.3.1、条件语句格式

select  *  from  表名 where 条件；

7.3.2、条件的写法

条件	作用
>	大于
<	小于
<>	不等于
!=	不等于
=	等于
<=	小于等于
>=	大于等于
or	或
and	而且
is null	内容为空
is not null	内容不为空
like	模糊查询
in	判断某个字段是否存在给定的元组中
between...and...	判断范围

7.3.3、使用关系运算符查询

查询年龄小于25的学生的信息 MySQL命令

select * from students where age<25;

7.3.4、使用in关键字查询

N关键字用于判断某个字段的值是否在指定集合中。如果字段的值恰好在指定的集合中，则将字段所在的记录将査询出来。

查询stuid为18和20的学生信息 MySQL命令：

select * from students where stuid in (18,20);

7.3.5、使用BETWEEN AND关键字查询

BETWEEN AND用于判断某个字段的值是否在指定的范围之内。如果字段的值在指定范围内，则将所在的记录将查询出来
查询10到18岁的学生信息 MySQL命令：

select * from students where age between 10 and 18

7.3.6、使用空值查询

在MySQL中，使用 IS NULL关键字判断字段的值是否为空值。请注意：空值NULL不同于0，也不同于空字符串
查询name不为空值的学生信息 MySQL命令：

select * from students where name is not null;

7.3.7、使用LIKE关键字查询

%用于匹配任意长度的字符串。例如，字符串“a%”匹配以字符a开始任意长度的字符串

_用于匹配一个任意的字符串。列入，字符串"ab_"匹配"ab"开始长度为3的字符串，如abc、abp等等..

查询学生姓名以r为开头的记录 MySQL命令：

select * from student where name like "r%";

7.3.8、使用LIMIT限制查询结果的数量

查询学生表中年纪最大的3位同学 MySQL命令：

select * from students age order by desc limit 3;

7.3.9、使用GROUP BY进行分组查询

统计学生男女人数 MySQL命令：

select count(gender),gender from students group by gender;

7.4.0、使用 order by 对查询结果进行排序。

从表中査询出来的数据可能是无序的或者其排列顺序不是我们期望的。为此，我们可以使用ORDER BY对查询结果进行排序
升序排列 MySQL命令格式：

select 字段名 from 数据表名 order by stuid;

降序排列 MySQL命令格式：

select 字段名 from 数据表名 order by stuid desc;

7.4.1、使用distinct去除重复的字段

去除重复的年龄[每个年龄只显示一条记录] MySQL命令格式：

select distinct age from students order by age ;

7.4、多表连接查询

将两张表或多张表联合起来查询，这就是连接查询。

7.4.1、交叉连接

交叉连接返回的结果是被连接的两个表中所有数据行的笛卡儿积。

MySQL:语法格式:

select * from 表1，表2；

由于这个交叉连接查询在实际运用中没有任何意义，所以只做为了解即可

7.4.2、内链接查询(自然连接，等值链接）

内连接使用比较运算符对两个表中的数据进行比较并列出与连接条件匹配的数据行，组合成新的记录。也就是说在内连接查询中只有满足条件的记录才能出现在查询结果中。

MySQL:语法格式:

select * from 表1,表2 where 表1.字段 = 表2.字段；

查询学生所在班级 MySQL命令格式：

select * from students,classes where students.classID = classes.classID;

7.4.3、外链接查询

在使用内连接查询时我们发现：返回的结果只包含符合查询条件和连接条件的数据。但是，有时还需要在返回查询结果中不仅包含符合条件的数据，而且还包括左表、右表或两个表中的所有数据，此时我们就需要使用外连接查询。外连接又分为左(外)连接和右(外)连接。

MySQL 语法格式:

select * from 表1 left join 表2 on 条件；

LEFT JOIN 左(外)连接：返回包括左表中的所有记录和右表中符合连接条件的记录。
RIGHT JOIN 右(外)连接：返回包括右表中的所有记录和左表中符合连接条件的记录。

关键字左边的表被称为左表，关键字右边的表被称为右表.

7.4.3.1、左外链接查询

左外连接的结果包括LEFT JOIN子句中指定的左表的所有记录，以及所有满足连接条件的记录。如果左表的某条记录在右表中不存在则在右表中显示为null。

查看班级下所有学生的老师姓名 MySQL命令：

select * from students left join teachers on students.teacherid = teachers.tid;

7.4.3.2、右外链接查询

右外连接的结果包括RIGHT JOIN子句中指定的右表的所有记录，以及所有满足连接条件的记录。如果右表的某条记录在左表中没有匹配，则左表将返回null。

查看每个学生的姓名和班级名 MySQL命令：

select * from students right join classes on students.classid = classes.classid;

7.5、子查询

子查询是指一个查询语句嵌套在另一个查询语句内部的查询；该查询语句可以嵌套在一个 SELECT、SELECT…INTO、INSERT…INTO等语句中。在执行查询时，首先会执行子查询中的语句，再将返回的结果作为外层查询的过滤条件。

MySQL语法格式：

select 字段 from 表名 where 字段=（select classid from students where name = Sun dasheng）

查询所有年龄小于平均年龄的学生 MySQL:

select * from students where age<(select avg(age) from students);

对于MySQL，不推荐使用子查询，执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响，这里多了一个创建和销毁临时表的过程。

八、MySQL创建用户

MySQL创建用户格式：

授权用户可以管理所有的库和表
GRANT ALL ON *.* TO "用户名"@"主机" IDENTIFIED BY "密码";

授权用仅仅可以管理指定的库中的全部的表
GRANT ALL ON 库名.* TO "用户名"@"主机" IDENTIFIED BY "密码";

授权用仅仅可以管理指定的库中的指定的表
GRANT ALL ON 库名.表名 TO "用户名"@"主机" IDENTIFIED BY "密码";

用户名叫jerry，只可以在192.168.0.112主机登录，可以管理所db03库的所有表，密码为123 ：

GRANT ALL ON "db03".* TO "jerry"@"192.168.0.112" IDENTIFIED BY "123";

以上就是数据库的基础知识和操作了，觉得有用的小伙伴点个关注。还会带来更多有用的知识。

love9599

关注

100
点赞
踩
615

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录