创建数据库

指令规范

注意:从现在开始将会频繁使用mysql指令,需要遵守一些指令规范

• 除use database指令外,其他指令都需要在结尾加上分号’;’
• 使用英文的标点符号
• 数据库命名规则一般以小写英语字母和_组成
• 数据表命名是小写字母和下划线 _ 组成，用来分割不同单词之间的含义

各功能指令

• 查看数据库

>show databases;

系统会显示当前已经存在四个MySQL系统数据库，这些数据库用来存储和管理 MySQL 服务相关的一些配置。

• 新建数据库

>create database item_name;

• 也可以利用客户端工具新建数据库

此处略.

删除数据库

在登录状态下,输入指令:

>drop database item_name;

选中数据库

• 输入选择数据库指令:

>use item_name;

选中数据后才能对数据库进行操作,当想选中另一个数据库时,直接输入use database_name即可

设计数据表

• 一个数据表主要包含信息有 : 表名、主键、字段、数据类型、索引

• 数据表是在数据中进行操作的,所以进行数据表操作时,需要先进入相对应的数据库

字段

以数据表student为例:

字段名称	数据类型	含义
id	无符号整型 (unsigned int)	自增子健
name	varchar(50)	学生姓名
age	unsigned int	学生年龄
id_number	varchar(18)	身份证号

主键

每一张表都必须有一个主键，一般建议选定为无符号整型 id 作为主键，并且 id 一般作为主键一般设置为自增的（特殊情况可使用其他非自增 id 作为主键）

新建数据表

• 学生数据表

create table `student`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '无名',
  `age` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `id_number` varchar(18) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
);

注:其中 “student” 为表名称，“id”、“name”、“age”、“id_number” 为字段名称，跟在字段名称后面的是字段的数据类型，“UNSIGNED” 表示无符号，“AUTO_INCREMENT” 表示自增，"PRIMARY KEY (id)"表示设置 “id” 为业务主键,，"NOT NULL DEFAULT ‘无名’ " 表示默认不为空，且默认值为 “无名” 。

同理:

• 教师数据表

create table `teacher`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '教师名',
  `age` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `id_number` varchar(18) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
);

• 课程表

create table `course`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `course_name` varchar(50) NOT NULL DEFAULT '',
  `teacher_id` int(10) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
);

• 学生选课关联表

create table `student_course`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `student_id` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `course_id` int(10) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
);

查看数据表

输入查看指令:

show tables;

可查看到新建了四个数据表.

数据表设计规范

第一设计范式

第一设计范式要求表中字段都是不可再分的，如果实体中的某个属性有多个值时，必须拆分为不同的属性 。通俗理解即一个字段只存储一项信息.

第二设计范式

第二设计范式要求表中必须存在业务主键，并且全部非主键依赖于业务主键。

第三设计范式

一个数据库表中不包含已在其它表中已包含的非主键字段。就是说表的信息如果能够被推导出来，就不应该单独的设计一个字段来存放（能尽量外键 join 就用外键 join）。

反范式设计

没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，提高读性能，就必须降低范式标准，适当保留冗余数据。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，减少了查询时的关联，提高查询效率，因为在数据库的操作中查询的比例要远远大于 DML 的比例。

alter 命令修改表

修改数据表名称

alter table 旧的表名 rename to 新的表名;

查看建表sql语句

show create table 需要查看的表名;

修改建表sql语句

alter table `表名`
modify column `字段1` 数据类型
新字段类型的字符集和编排方式
not NULL default '无名' after `字段2`;

新增表字段

alter table `表名`
add column `新字段` 数据类型 
not NULL default 1 comment '字段名 : 1:数据一 2:数据二' alter `字段2`;

其中comment '字段名 : 1:数据一 2:数据二’表示该字段的注释说明

删除表字段

alter table `表名`
drop column `字段`;

修改表字段名称

alter table `表名`
change column `旧字段名` `新字段名` 数据类型 编码方式

删除数据表

先选中表所在的数据库,然后输入删除指令:

use 数据库;
drop table 表名;

insert 插入数据

insert into 表名
(字段_1,字段_2,字段_3...)
values
(数据,数据,数据...),
(数据,数据,数据...),
...
(数据,数据,数据...);

通过使用真实数据替换上面指令,可进行一条至多条数据的插入.

delete删除数据

删除部分数据

delete from 表名 where 字段=1(假设为1);

选中数据库,然后输入上面指令表明:删除所选表中,所选字段对应数据为1的数据.

删除表全部数据

• delete

delete from 表名;

• truncate

truncate table 表名;

注:

TRUNCATE 清空表数据的实际过程是先删除数据表，然后新建一张和原来表结构一模一样的表来替代清空。

DELETE 删除表数据不会改变自增主键的增长值

select查询数据

查询表中所有的数据

select * from 表名; 

查询指定条数的结果集

select * from 表名 limit 10(假设为10);

输入上述指令将呈现10条结果的结果集.

查询指定其实条数的结果集

select * from 表名 limit 10,10;(假设为10,10);

将输出11-20的十条结果的结果集.

查询指定字段列的结果集

输入以下指令:

select 字段1,字段2 from 表名 limit 6,5;

给指定字段重命名

select 字段1 as 字段3,字段2 from 表名 limlt 6,5;

注:该重命名仅仅使用呈现结果,而不会保存到原数据表

update更新数据

更新某一列字段的值

输入下列指令:

update 表名 set 字段1=数据1 limit 3;

即可将前三条数据中字段1的数据修改为 数据1.

更新多段字段的值

update 表名 set 字段1=数据1,字段2=数据2 where 某字段=某一数据;

即可进行修改.

like模糊查询

模糊查询表达式

% 表示指代任意内容,例如 '%小%' 表示包含 小 的表达式,且 小 前后都有内容, '%小' 表示以 小 结尾的表达式,王 前面有内容,后面没有内容,'小%' 表示以 小 开头的表达式,小 前面没有内容,后面有内容。

使用like模糊查询

输入下面指令:

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

即可查看该字段对应的字符串最右边的字符为’小’的结果.

where条件查询

where条件

符号	说明
<	小于
=	等于
>	大于
<>或!=	不等于
<=	小于等于
>=	大于等于
like	模糊条件
not like	不符合模糊条件
between and	在两个值之间(包含两端值)
not between and	不在在两个值之间(不包含两端值)
is null	空
is not null	不为空

单条件查询

select * from 表名 where 条件1;

and多条件查询

select * from 表名 where 条件1 and 条件2;

and表示同时满足两个条件才为真.

or多条件查询

select * from 表名 where 条件1 or 条件2;

只需要满足至少一个条件即为真

union联合查询

union all查询

输入以下指令即可把满足两种查询条件的结果集并到一起：

select * from 表名 where 条件1
union all
select * from 表名 where 条件2;

注: UNION ALL 联合查询的结果集没有去掉重复的数据.

union查询

select * from 表名 where 条件1
union
select * from 表名 where 条件2;

注:UNION 将两种查询结果并到一起，可以看到结果集中已经去掉重复的数据

order by排序

ASC 从小到大排序

输入以下指令:

select * from 表名 order by 某字段 ASC;

即可根据该字段数据实现从小到大排序

DESC 从大到小排序

select * from 表名 order by 某字段 DESC;

多字段混合排序

select * from 表名 order by 字段1 DESC,字段2 ASC;

根据优先级是:先进行根据字段1的降序,在此基础上再进行根据字段2的升序.

对字符串排序

对字符串排序和对数值进行排序的区别在于:英文字符排序在前，中文在后，其中排序规则是按照字符 ASCII码 对应值的大小排序.

join表连接

left左连接

输入以下指令:

select a.原字段名 as 新字段名,a.*,b.* 
from 表一  a 
left join 表二 b
on a.字段1=b.字段2;

• a.* 表示 表一所有字段数据；

• t.* 表示 表二 表字段所有数据；

• ON 后面跟着的条件是连接表的条件；

• 表一 a 表示将 表一 简写为 a， 表二 b 表示将 表二 简写为 b；

• left join 为左连接，是以左边的表为’基准’，若右表没有对应的值，用 NULL 来填补。

同理:

• right join 为右连接，是以右边的表为’基准’，若左表没有对应的值，用 NULL 来填补。

当然,内连接有些许不同:

• INNER JOIN 为内连接，展示的是左右两表都有对应的数据。

多表混合连接

即一或多种连接方式的多次使用:

select a.原字段名 as 新字段名,a.*,b.* 
from 表一  a 
left join 表二 b
on a.字段1=b.字段2
right join 表三 c
on b.字段2=c.字段3
inner join 表四 d
on b.字段3=d.字段4;

该多表连接以表二的字段2为基准.

distinct去重

输入下列指令:

select
distinct 字段1,字段2,...字段n
from 
表一 a
inner join
表二 b
on a.字段1=b.字段2;

group by分组

单字段分组

输入以下指令:

select a.原字段名 as 新字段名,a.*,b.* 
from 表一  a 
left join 表二 b
on a.字段1=b.字段2
right join 表三 c
on b.字段2=c.字段3
inner join 表四 d
on b.字段3=d.字段4
group by 某字段;

即可完成单字段分组.

多字段分组

输入以下指令:

select a.原字段名 as 新字段名,a.*,b.* 
from 表一  a 
left join 表二 b
on a.字段1=b.字段2
right join 表三 c
on b.字段2=c.字段3
inner join 表四 d
on b.字段3=d.字段4
group by a.字段1,b.字段2;

即可完成所需分组.

单字段分组和多字段分组的区别在于，单字段是以一个字段来判断数据是否重复分组出来的结果，多字段分组是以多个字段同时来判断是否重复分组出来的结果。

聚合函数

函数	作用
avg()	计算平均值
sum()	计算总和
count()	计算总条数
min()	取最小值
max()	取最大值

数据类型

整数类型

类型	大小	说明
tinyint	1字节	小整型
smallint	2字节	小整型
mediumint	3字节	中整型
int	4字节	整型
bigint	8字节	大整型

浮点类型

类型	大小	说明
float	4字节	单精度浮点型
double	8字节	双精度浮点型
decimal	每4个字节存储9个数字,小数点占1字节	精确类型,常用来表示金额

日期与时间类型

类型	大小	格式	范围
date	3字节	YYYY-MM-DD	1000-01-01~9999-12-31
time	3-6字节	HH::MM::SS[.微秒]	-838:59:59~838:59:59
year	1字节	YYYY	1901~2155
datetime	5-8字节	YYYY-MM-DD HH::MM::SS[.微秒]	1000-01-01 00:00:00~2038-01-19 03:14:07 UTC
timestamp	4-7字节	YYYY-MM-DD HH::MM::SS[.微秒]	1974-01-01 00:00:00~2038-01-19 03:14:07 UTC

字符串类型

类型	范围	说明
char	1~255个字节	固定长度
varchar	字段存储所占字节数不能超过65535字节	可变长度
tinytest	最大长度255字节	可变长度
text	最大长度65535字节	可变字节
mediumtext	最大字节16777215字节	可变字节
longtext	最大长度4294967295字节	可变字节

char 类型是一种固定长度的类型，varchar 则是一种可变长度的类型，它们的区别是：char(M) 类型的数据列里，每个值都占用 M 个字节，如果某个长度小于 M，MySQL 就会在它的右边用空格字符补足.。

枚举值类型

类型	范围	说明
enum	几何数最大65535	枚举值类型,只能插入列表中指定的值

二进制类型

类型	范围	说明
tinyblob	最大长度255字节	可变长度
blob	最大长度65535字节	可变长度
mediumblob	最大长度16777215字节	可变长度
longblob	最大长度4294967295字节	可变字节

如果觉得对你们有帮助，可以给文章点个赞哦~