Mysql基础

01Mysql基础

正则表达式：由一堆的字母、数字、符号组成的一系列规则的集合体

数据库：存储数据的仓库

​ 关系型数据库：数据是以表的形式存储，表与表之间可以存在关联关系，数据可以持久化 如：mysql oracle …
​ 非关系型数据库：数据多以json字符串的格式存储，没有表的概念，数据之间没有关联关系 如：redis …

mysql

​ 端口 3306
​ 字符集：utf8mb4

oracle

​ 端口 1521

使用mysql:

1. 开启/关闭服务

   1). 电脑搜索“服务”，找到对应数据库，右键-启动/关闭
   2). cmd - 管理员运行，输入命令：net start xxx / net stop xxx xxx为服务名，忽略大小写

2. 连接mysql

   1). 从开始菜单中找到mysql - MySQL 8.0 Command Line Client - 输入密码 - 回车
   2). cmd - 管理员运行，进入mysql安装目录到bin为止，运行命令：mysql -uxxxx -pxxxx 如：mysql -uroot -proot
   3). 可视化工具连接

3. 使用

   1). 查看已存在的数据库：show databases; 注意：分号;不能丢
   2). 创建数据库：create database xxx;
   3). 删除数据库：drop database xxx;
   4). 使用数据库：use xxx;

   5). 查看表：show tables;
   6). 创建表：
   create table xxx(
   字段名 类型(长度),
   字段名 类型,
   …
   );
   7). 删除表：drop table xxx;
   8). 查看表结构：desc xxxx;

DML: 数据操作语言，用来操作数据库表中所包含的数据 INSERT / UPDATE / DELETE
DDL：数据定义语言，用于创建和删除数据库对象等操作 CREATE / DROP / ALTER
DQL: 数据查询语言，用来对数据库表中的数据进行查询 SELECT
DCL：数据控制语言，用来控制数据库组件的存取许可、存取权限等 GRANT / COMMIT / ROLLBACK

数据库中的类型：

数值：

​ TINYINT：非常小的数据，1字节 常用
​ SMALLINT：较小的数据，2字节
​ MEDIUMINT：中等大小的数据，3字节
​ INT：标准整数，4字节 常用
​ BIGINT：较大的整数，8字节 常用
​ FLOAT：单精度浮点数，4字节
​ DOUBLE：双精度浮点数，8字节 常用
​ DECIMAL：字符串形式的浮点数，M+2个字节
​ UNSIGNED：标识为无符号数
​ ZEROFILL：位数不足以0填充

字符串：

​ CHAR(): 定长字符串
​ VARCHAR()：可变字符串 常用
​ TINYTEXT：微型文本串
​ TEXT：文本串

日期：

​ DATE：YYYY-MM-DD 常用
​ DATETIME：YY-MM-DD hh:mm:ss 常用
​ TIME：hh:mm:ss:
​ TIMESTAMP：YYYYMMDDHHMMSS 1970年某时刻~2038年某时刻，精度为1秒 常用
​ YEAR：YYYY格式的年份

二进制：

​ blob

表字段的属性和约束：

非空约束：NOT NULL 字段不允许为空
默认值：DEFAULT xxx 赋予某字段默认值
唯一约束: UNIQUE KEY(UK) 设置字段的值是唯一的，允许为空，但只能有一个空值
主键约束：PRIMARY KEY(PK) 设置该字段为表的主键，可唯一标识该表记录
外键约束：FOREIGN KEY(FK) 用于在两表之间建立关系，需要指定引用主表的哪一字段
自动增长：AUTO_INCREMENT 设置该列为自增字段，默认每条自增1，通常用于设置主键

COMMENT 字段或表注释

eg: create table student(
studentNo int(4) not null primary key COMMENT “学号”,
loginPwd varchar(20) not null comment “密码”,
studentName varchar(50) not null comment “姓名”,
sex char(2) not null default “男” comment “性别”,
gradeId int(4) UNSIGNED comment “年级编号”
) comment “学生表”

修改表结构：

​ 修改表名：alter table 表名 rename 新表名;
​ 添加字段：alter table 表名 add 字段名 字段类型 [属性/约束];
​ 修改字段：alter table 表名 change 原字段 新字段 字段类型 [属性/约束];
​ 删除字段：alter table 表名 drop 字段名;
​

​ 创建主键：ALTER TABLE 表名 ADD CONSTRAINT 主键名 PRIMARY KEY 表名 主键字段;
​ alter table student add constraint pk_studentNo primary key studnet studentNo;

​ 创建外键：ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY 外键字段 REFERENCES 关联表名 关联字段;
​ alter table student add constraint fk_gradeid foreing key gradeId references grade id;

存储引擎

​ MyISAM、InnoDB(默认)
名称 InnoDB MyISAM
事务处理 支持 不支持
数据行锁定 支持 不支持
外键约束 支持 不支持
全文索引 不支持 支持
表空间大小 较大,约2倍 较小

事务：

​ 当一个操作中涉及多张表数据操作时，其中一部分成功了，另一部分没有成功，就会出问题，为了确保这些操作统一成功或失败，会把这些操作放入一个事务中

数据库操作

数据行锁定：

​ 当多个操作对同一条数据生效时，A操作把这条数据先锁定，锁定后就不允许B再操作

添加表数据：

​ insert into 表名(字段1, 字段2, …) values (值1, 值2, …)
​ insert into 表名(字段1, 字段2, …) values (值1, 值2, …), (值1, 值2, …), …
​ 注意：表名后的字段可以省略，省略代表这张表中的所有字段都要添加数据，不建议省略

修改表数据：

​ update 表名 set 字段1=值1, 字段2=值2, … where 筛选条件
​ 注意：一定要加筛选条件

删除表数据：

​ delete from 表名 [where 筛选条件]
​ 注意：一定要确认要不要加筛选条件

​ truncate 表名; 删除表中所有的数据
​ truncate 删除数据时，会先把整个表删除，再重新建一个一模一样的表
​ delete 删除数据，只是把表中的所有数据删除

​ drop table xxx;

查询表数据：

​ select 字段1, 字段2, … from 表名 [where 筛选条件]
​ 中间的字段可以是*，代表要查询所有字段的值，不建议用*

as 起别名: as 可以省略不写

​ 1). 给列起别名
​ select studentNo as ‘学号’, studentName as ‘姓名’ from student;
​ select studentNo ‘学号’, studentName ‘姓名’ from student;
​ 2). 给表起别名
​ select a.id, b.studentNo, stuName from stu as a, student as b;
​ select a.id, b.studentNo, stuName from stu a, student b;
​ 3). 给结果集起别名
​ select t.sno, t.sn from (select s.studentno sno, s.studentname sn, s.borndate b from student s) t

null 和 ""字符串查询

​ select * from stu where stuName is [not] null;
​ select * from stu where stuName=“”;

聚合函数：

​ AVG() 返回某字段的平均值
​ COUNT() 返回某字段的行数
​ MAX() 返回某字段的最大值
​ MIN() 返回某字段的最小值
​ SUM() 返回某字段的和

面试题：count(0)、count(1)、count(*)区别

MySQL函数

字符串函数：

​ CONCAT(str1, str1…strn)：字符串连接 SELECT CONCAT(‘My’,‘S’,‘QL’);返回：MySQL
​ INSERT(str,pos,len,newstr)：字符串替换 SELECT INSERT(‘这是SQL Server数据库’,3,10,‘MySQL’);返回：这是MySQL数据库
​ LOWER(str)：将字符串转为小写 SELECT LOWER(‘MySQL’);返回：mysql
​ UPPER(str)：将字符串转为大写 SELECT UPPER(‘MySQL’);返回：MYSQL
​ SUBSTRING(str,num,len)：字符串截取 SELECT SUBSTRING(‘JavaMySQLOracle’,5,5);返回：MySQL
​ substr(str,num,len):字符串截取 SELECT SUBSTR(‘JavaMySQLOracle’,5,5);返回：MySQL

时间日期函数:

​ CURDATE() 获取当前日期，年月日
​ CURTIME() 获取当前时间，时分秒
​ NOW() 获取当前日期和时间，年月日时分秒
​ WEEK(date) 返回日期date为一年中的第几周
​ YEAR(date) 返回日期date的年份
​ HOUR(time) 返回时间time的小时值
​ MINUTE(time) 返回时间time的分钟值
​ DATEDIFF(date1,date2) 返回日期参数date1和date2之间相隔的天数
​ ADDDATE(date,n) 计算日期参数date加上n天后的日期

数学函数：

​ CEIL(x)：返回大于或等于数值x的最小整数
​ FLOOR(x)：返回小于或等于数值x的最大整数
​ RAND()：返回0~1间的随机数

其他查询

order by xxx: 通过指定列排序，asc 升序，默认可不写， desc 降序
select studentNo, subjectNo, studentResult from result where subjectNo=1 and examDate=“2016-02-17 00:00:00” order by studentResult desc

limit x： 从前往后，取x条
limit a, b： 从指定下标a开始，往后取b条，下标从0开始
select studentNo, subjectNo, studentResult from result where subjectNo=1 and examDate=“2016-02-17 00:00:00” order by studentResult desc limit 0, 3

like “”: 模糊查询，使用通配符 %：可以匹配任意数量 _：只能匹配一个
select * from student where studentName like ‘李%’

子查询：查询里面套着查询

in：

exists:

group by: 分组

having: 分组之后的筛选

​ – 统计每门课程平均分，筛选出>65分的
​ – select subjectNo, avg(studentResult) from result where examDate=“2016-02-17 00:00:00” group by subjectNo having avg(studentResult) > 65
​

– 统计每个年级的男生女生数量
select gradeId, sex, count(1) from student GROUP BY gradeId, sex

where 和 having 的区别：

​ where是从原始数据中筛选出我们想要的数据进行处理
​ having是从分组处理后的数据中再筛选

多表连接查询：

​ 左连：left join … on … 把左表的数据全部显示，右表只显示和左表有关联的
​ 右连：right join … on … 把右表的数据全部显示，左表只显示和右表有关联的
​ 内连：inner join … on … 查询左表右表都有的数据，即两张表的交集
​ 外连：union / union all
​ (select s.studentNo, s.studentName, s.gradeid, g.gradeName from student s left JOIN grade g on s.gradeId=g.gradeID)
​ union
​ (select s.studentNo, s.studentName, s.gradeid, g.gradeName from student s right JOIN grade g on s.gradeId=g.gradeID)

表之间的关联关系：

一对一
一对多
多对一
多对多

事务的四个特性（属性）（ACID）：

​ 原子性（Atomicity）：事务是一个完整的操作，事务的各步操作是不可分的(原子的)，要么都执行，要么都不执行
​ 一致性（Consistency）：当事务完成时，数据必须处于一致状态
​ 隔离性（Isolation）：并发事务之间彼此隔离、独立，它不应以任何方式依赖于或影响其他事务
​ 持久性（Durability）：事务完成后,它对数据库的修改被永久保持

视图：

​ 一张虚拟表，是从一个或多个表中，提取出来的关注字段，临时从内存中凑成的一张虚拟表

索引:

就是查询目录
主键索引
唯一索引
普通索引
复合索引
全文索引
空间索引

全量备份
增量备份

数据库设计三大范式：

​ 第一范式：原子性，表中的每一个字段要拆分到不能再拆
​ 第二范式：单一性，每张表中描述一件事
​ 第三范式：关联性，满足第二范式的前提下，表与表之间除主键外没有其他的传递关联关系