MySQL 基本用法

MySQL

本系列为本人学习情况记录，部分资料来自老师课件或书籍摘录。

---

一、基础概念

1.1 实体

实体是客观存在的，可以被描述的事物

1.2 数据

数据是对事物的描述信息

1.3 数据库

数据库是按照数据结构来组织、存储和管理数据的仓库，它是存储在一起的相关数据的集合

1.4 数据库系统

数据库系统是用于描述数据库存储数据的规则

1.5 数据库管理系统

按照数据库系统描述的规则，对数据库中的数据执行管理的程序

补充： 数据结构

数据结构是互相之间存在一种或多种特定关系的数据元素的集合

---

二、数据库管理系统

2.1 数据库管理系统需要提供的功能

数据定义功能，提供数据定义语言(DDL)；定义数据库对象
数据操作功能，提供数据操作语言；实现数据的CRUD
运行管理数据库
建立维护数据库

2.2 常用的数据库

MySQL、Oracle、MS SQL、Redis

三、MySQL基本命令

命令	描述
mysqld --install	安装MySQL服务
mysqld --remove	删除当前MySQL服务
mysql -h 服务器地址 -P 端口 -u mysql账户 -p 账户密码	链接数据库(-p 之后的密码可以不写，直接回车之后根据mysql的提示输入密码，可以保证密码安全不被泄漏)
show databases;	查看数据库列表
use 数据库名;	使用数据库
show tables;	查看当前数据库表格列表
desc 表名;	查看表格结构
exit;	退出
help;	查看帮助
\c	放弃正在输入的命令
\h	显示命令清单
\q	退出MySQL
\s	查看MySQL服务器状态信息

---

四、数据库操作流程

4.1 基本步骤

1. 创建数据库
2. 使用数据库
3. 向数据库中添加表
4. 给表格命名、编码、注释等
5. 为表格的列设置名称、数据类型、长度、备注等
6. 操作表格中的数据

4.2 基本操作(数据库中的关键字不区分大小写)：

# 如果由指定的数据库存在，就删除
drop database if EXISTS `HouseSystemDB`;
# 创建新数据库
create database `HouseSystemDB`;
# 使用刚创建的数据库
use `HouseSystemDB`;
# 修改数据库的编码
alter database `HouseSystemDB` default character SET utf8 collate utf8_general_ci;

# 创建一个房东表 - 定义字段时，先书写字段名称，再书写数据类型
create table `Lorder`(
	`id` int,								# 房东编号
	`name` varchar(20),			# 房东名称
	`age` int								# 房东年龄
);

# 向表中插入数据
insert into Lorder (id, `name`, age) values(1, '包租婆', 42);
insert into Lorder (id, `name`, age) values(2, '包租公', 42);

# 查询数据
select * from Lorder;													# 查询Lorder表中的所有列
select * from Lorder where name = '包租公';   # 根据条件查询

# 修改数据
update Lorder set `name` = '星仔', age = 35 where id = 2;

# 删除数据
delete from Lorder where id = 1;

# 排序
select * from Lorder order by id DESC;			# 以id的降序排列
select * from Lorder order by id ASC;				# 以id的升序排序

select * from Lorder;													# 查询Lorder表中的所有列

4.3 MySQL中的数据类型

数值类型

类型	字节数	取值范围	说明
tinyint	1字节	有符号(-2的7次方 ~ 2的7次方-1)	非常小的数据
smallint	2字节	15	较小的数据
mediumint	3字节	23	中等大小数据
int	4字节	31	标准整数
bigint	8字节	63	较大的整数
float	4字节		单精度浮点数
double	8字节		双精度浮点数
decimal			字符串形式的浮点数

字符串类型

数据类型	说明
char[(M)]	定长字符串，M为0~255之间的整数(读取该数据时要进行去两侧空格操作)
varchar[(M)]	变长字符串，M为0~65535之间的整数，值的长度+1字节
tinyblob	允许长度0~255字节，值的长度+1个字节
bolb	允许长度0~65535字节，值的长度+2个字节
tinytext	允许长度0~255字节，值的长度+2个字节
text	允许长度0~65535字节，值的长度+2个字节
varbinary[(M)]	允许长度0~M个字节的变长字符串，值的长度+1个字节
binary[(M)]	允许长度0~M个字节的变长字符串

• 在检索的时候，char 列删除了尾部的空格，而 varchar 则保留这些空格
• binary 和 varbinary 类似于 char 和 varchar，不同的是它们包含二进制字符串而不包含非二进制字符串。当保存 binary 值时，在值的最后通过填充"ox00"(零字节)以达到指定的字段定义长度

日期和时间类型

数据类型	字节数	格式和取值范围
date	3字节	1000-01-01~9999-12-31
time	3字节	-838:59:59~838:59:59
datetime	8字节	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
year	1字节	1901~2155

4.4 MySQL结构中的部分概念

表的列

主要对表格中某一个列信息进行描述
主要由名称、类型、长度、选项、注释等组成

列的属性

属性	说明
unsigned	无符号的
zerofill	用0填充
auto_increment	自动增长
null	默认为空，不填写
not null	不允许为空
default	某一列的默认值

常用的存储引擎

（只介绍两种引擎）

功能	InnoDB	M有ISAM
支持事务	支持	不支持
支持全文索引	不支持	支持
外键约束	支持	不支持
数据行锁定	支持	不支持
表空间大小	较大	较小

创建表格并规定属性

drop database if exists `schooldb`;
create database if not exists `schooldb`;

use `schooldb`			# 一定记得先选择数据库再进行操作

drop table if exists `Student`;
create table if not exists `Student`(
`stuId`		int primary key auto_increment		comment '学号',
`loginPwd`	varchar(20) not null				comment '密码',
`studentName`	varchar(50) not null			comment '姓名',
`sex`		char(2)		not null default '男'	comment '性别',
`gradeId`	int		unsigned					comment '年级',
`phone`		varchar(50)							comment '电话',
`address`	varchar(255) default '地址不详'		comment '地址',
`bornDate`	datetime							comment '出生日期',
`identityCard`	varchar(18)	unique				comment '身份证号码'
)engine=innodb charset=utf8mb4 					comment='学生信息表';

drop table if exists `Score`;
create table if not exists `Score`(
`id`		int primary key auto_increment		comment '编号',
`stuId`		int not null						comment '学生学号',
`subjectId`	int not null						comment '学科编号',
`examDate`	datetime default now()				comment '考试日期',
`score`		double 								comment '考试成绩'
)engine=innodb charset=utf8mb4 					comment='学生成绩表';

drop table if exists `Grade`;
create table if not exists `Grade`(
`GradeId`	int(4) primary key auto_increment 	comment '年级编号',
`GradeName`	varchar(50) not null 				comment '年级名称'
)engine=MyISAM;

drop table if exists `Subject`;
create table if not exists `Subject`(
`SubjectID`	int(4) primary key auto_increment	comment '科目编号',
`SubjectName` varchar(50)	not NULL			comment '科目名称',
`GradeID`	int(4)								comment '所属年级编号',
`Hours`		tinyint(4)							comment '学时'
)engine=MyISAM;

4.5 使用数据库操作语句执行数据库操作

数据库操作语句

简称DDL，Data Definational Language
主要负责对数据结构的处理，数据库、数据表、以及表中的列

4.5.1 数据库创建

create database if not exists `t15_stu`;

4.5.2 删除数据库

drop database if exists `t15_stu`;

4.5.3 使用数据库

use `t15_stu`;

4.5.4 修改数据库

alter database `t15_stu` default charset utf8 collate utf8_general_ci;

4.5.5 数据库导出

mysqldump -uroot -p -d t15_student > t15_student_structure.sql

4.5.6 数据库导入

mysql -uroot -p t15_student < t15_student.sql

4.5.7 删除表结构

drop table if exists `student`;

4.5.8 表结构修改

# 修改表的名称
alter table `Grade` rename `NewGrade`;	
# 为表添加字段
alter table `NewGrade` add `StuCount` int not null;
# 修改字段
alter table `Subject` modify `hours` int;
# 修改字段（修改字段的名称和数据类型）
alter table `Subject` change `GradeId` `GradeNo` bigint;
# 删除字段
alter table `NewGrade` drop `StuCount`;
select * from `NewGrade`;

---

五、数据的完整性

5.1 概念

数据的完整性包括数据的准确性和可靠性
数据库完整性是指数据库中数据在逻辑上的一致性、正确性、有效性和相容性。数据库完整性由各种各样的完整性约束来保证，因此可以说数据库完整性设计就是数据库完整性约束的设计。数据库完整性包括以下几种形式：

• 实体完整性：主键约束(PRIMARY KEY) 、唯一约束(UNIQUE KEY) 、标识列(AUTO_INCREAMENT)
• 域完整性：检查约束(CHECK)、数据类型、默认值(DEFAULT)、非空约束(NOT NULL)、外键约束(FOREIGN KEY)
• 引用完整性：外键约束
• 自定义完整性：存储过程、触发器
  添加约束：

# 一般情况下，建议创建表时，不要添加主键及外键约束
# 如果要修改表中原本的主键，需要四步 - 了解即可
# 1. 删除自增
alter table `Student` change `stuId` `stuId` int;
# 2. 删除主键约束
alter table `Student` drop primary key;
# 3. 重新设置主键约束
alter table `Student` add constraint PK_stuNo primary key (stuId);
# 4、重新添加自增
alter table `Student` auto_increment=0;

# 为字段添加唯一约束
alter table `Student` add constraint uq_mobile unique (mobile);

# 添加默认值约束
alter table `Student` alter column `email` set default '默认电子邮件';

# 添加检查约束
alter table `Student` add constraint ck_name check (len(name) < 15);

# 添加外键约束 主表-学生信息表；子表-成绩表
select * from score;
insert into score values(default, 10, 1, now(), 98);
truncate table score;

alter table `Score` add constraint fk_stuid 
	foreign key (stuId) references `Student`(`stuId`);

复合主键

一个表可以不只有一个主键，可以有多个主键，通过这几个主键一起来确定一条记录，那么这几个主键组成的就是复合主键

primary key(id, name)

---

六、DML命令操作数据

插入数据
插入数据时，单行插入为一个事务，插入失败会进行回滚
多行插入也只作为一个事务，如果有其中一条失败，就会全部回滚

# 插入单行数据
insert into 表名 (字段) values(值);
# 插入多行数据

删除数据
删除数据时，一定要注意条件，大多数情况下不加条件直接删除都是面向监狱编程！！！！！
删除数据时，不会影响自增列的当前值
清空表时，会将自增列置默认值

# 删除单条数据
delete from 表名 [where <删除条件>];
# 清空表
truncate table 表名;

更新数据

update 表名 set 列名 = 更新值 [where 更新条件];

---

七、DQL命令查询命令

常用函数

字符串函数

# 返回字符串字节数；一个中文占3个字节		UTF8mb4中占4个字节
length('老九学堂');
# 返回字符串字符数
char_length('老九学堂');
# 返回下标(从1开始)
position('老' in '老九学堂');
# 大小写转换
lower('WE r FAMILY');
upper('WE r FAMILY');
# 去掉两侧空格
ltrim(rtrim('      abc      '));
trim('      abc      ');
# 返回起始与结束
left('小荷才露尖尖角', 2);
right('小荷才露尖尖角', 3);
# 字符串替换
replace('小荷才露尖尖角尖尖', '尖尖', '顿顿');
# 字符串插入、替换或删除
insert('小荷才露尖尖角', 3, 0, '方便面');
insert('小荷才露尖尖角', 4, 7, '方便面');
insert('小荷才露尖尖角', 3, 4, '');
# 字符串截取
substring('小荷才露尖尖角', 4, 3);
substring('小荷才露尖尖角' from 4 for 3);
substring('小荷才露尖尖角', 4);
# 字符串反转
reverse('小荷才露尖尖角');
# 字符串拼接
concat(11, 'asd', 'asdf23');

时间函数

# 当前日期
now();
# 日期加减
date_add(now(), interval 30 year);
date_add(now(), interval -30 year);
# 返回某日期是该月的第几天
dayofmonth(now());
# 计算日期之差
datediff(now(), '1949-10-1');
# 日期加减，返回对应的年/月/日
timestampdiff(year, now(), '1949-10-1');
timestampadd(year, 10, '1949-10-1');
# 日期格式
date_format(now(), "%y-%m-%d");

数学函数

# 取绝对值
abs(-888);
# 取整
ceiling(2.1);
floor(-4.9);
# 次方
pow(2, 3);
# 随机数
rand();
# 四舍五入
round(1.3)

类型转换

cast('88.3' as decimal);
convert('88.38', decimal);

聚合函数

# 返回最大值
max();
# 返回最小值
min();
# 返回平均数
avg();
# 返回总和
sum();
# 返回数量
count();

基本查询

全部语法

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [into_option]
    [FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]
    [into_option]

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

select <列名|表达式|函数|常量> from <表名>
[where <查找条件>]
[order by <排序的列名>[ASC 或 DESC]];

# 查询整张表
select * from 表名;
# 查询某列
select <列名> from 表名;
# 按条件查询
select <列名> from 表名 where <条件>;
# 别名
select <列名> as <别名> from 表名;

模糊查询

分组查询

select field... from TableName
[where ...]
[order by ...]
[group by field1, field2...]
[having ...]

注意：

• select 后面出现聚合函数和普通列名的情况下，一定是使用分组查询，将普通列明分组
• having 通常和聚合函数搭配使用
  

八、联表查询

内联接

select field... from `tablename`
inner join `tablename`
[on...]
[where...]

外联接

select field... from `tablename`
left join `tablename` on ...
[where...]

区别：

• 内联接可以没有on条件；外联接必须有on条件
• 内联接中子表没有的数据行会直接忽略；外联接中子表没有的数据行也会强制显示，并输出null

子查询

合并

• 使用union 时，完全相等的行，将会被合并
• 而使用union all时，不会合并

---