投稿
  1. 云图网首页
  2. 技术专区
  3. 数据库

Mysql学习总结

• 数据库 • 阅读 42


Mysql小结

数据库概念

定义

数据库(Database)是按照数据结构来组织、存储和管理数据的建立在计算机存储设备上的仓库。

数据库是长期储存在计算机内、有组织的、可共享的数据集合。

分类：

非结构化数据，半结构化数据， 结构化数据

SQL(Structured Query Language)结构化查询语言

分类
​
•   DDL：数据库定义语言（定义数据库的一些组件 表 索引 视图 自增序列...）
​
•   DML：数据库操作语言（添加 删除 修改） CRUD
​
•   DQL：数据库查询语言 查询
​
•   DCL：数据库控制语言（权限 用户管理...）  DBA工程师
​
•   SQL语言属于第四代语言，而java c++ 才属于第三代
​
​

 

基础的sql语句

登录数据库

[[email protected] ~]# mysql -uroot -p123456
[[email protected] ~]# mysql -uroot -p
(密码)
​

 

库表的一些基本操作

create database 库名 default charset utf8 collate; utf8_general_ci;
drop database 库名;
use 库名;
show databases;
-- exit; (退出数据库)
​
​
create table 表名(...)default charset=utf8;
​
alter table 表名 add 列名 类型 not null defalut 默认值;
alter table 表名 add 列名 类型 not null primary key auto_increment;
alter table 表名 drop column 列名;
alter table 表名 change column 列名;
alter table 表名 change 原列名 新列名 新类型 ...;
alter table 表名 alter 列名 drop/set 
​
​
​
​

常见类型表

  • int[(m)][unsigned][zerofill]

    int             表示有符号，取值范围：-2147483648 ～ 2147483647
    int unsigned    表示无符号，取值范围：0 ～ 4294967295
    int(5)zerofill  仅用于显示，当不满足5位时，按照左边补0，例如：00002；满足时，正常显示。

  • tinyint[(m)] [unsigned] [zerofill]

    有符号，取值范围：-128 ～ 127.
    无符号，取值范围：0 ～ 255

  • bigint[(m)][unsigned][zerofill]

    有符号，取值范围：-9223372036854775808 ～ 9223372036854775807
    无符号，取值范围：0  ～  18446744073709551615

  • decimal[(m[,d])] [unsigned] [zerofill]

    准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。

  • FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

    单精度浮点数，非准确小数值，m是数字总个数，d是小数点后个数。

  • DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

    双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。

  • char(m)

    定长字符串，m代表字符串的长度，最多可容纳255个字符。
    ​
    定长的体现：即使内容长度小于m，也会占用m长度。例如：char(5)，数据是：yes，底层也会占用5个字符；如果超出m长度限制（默认MySQL是严格模式，所以会报错）。
        如果在配置文件中加入如下配置，
            sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
        保存并重启，此时MySQL则是非严格模式，此时超过长度则自动截断（不报错）。。
    ​
    注意：默认底层存储是固定的长度（不够则用空格补齐），但是查询数据时，会自动将空白去除。 如果想要保留空白，在sql-mode中加入 PAD_CHAR_TO_FULL_LENGTH 即可。
    查看模式sql-mode，执行命令：show variables  like 'sql_mode';
    ​
    一般适用于：固定长度的内容。

  • varchar(m)

    变长字符串，m代表字符串的长度，最多可容纳65535个字节。
    ​
    变长的体现：内容小于m时，会按照真实数据长度存储；如果超出m长度限制（（默认MySQL是严格模式，所以会报错）。
        如果在配置文件中加入如下配置，
            sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
        保存并重启mysql服务，此时MySQL则是非严格模式，此时超过长度则自动截断（不报错）。

  • datetime

    YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59）

  • timestamp

    YYYY-MM-DD HH:MM:SS（1970-01-01 00:00:00/2037年）
    对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储，查询时，将其又转化为客户端当前时区进行返回。
    ​
    对于DATETIME，不做任何改变，原样输入和输出。

  • date

    YYYY-MM-DD（1000-01-01/9999-12-31）

  • time

    HH:MM:SS（'-838:59:59'/'838:59:59'）

DQL语法

格式

select 列名*N from 表名 where 查询条件1 and/or 查询条件2 group by 列 Having 分组条件 Order by 排序

规则

sql在书写的时候除了查询条件之外，大小写都可以

select * from user where uname =’zs’;

SELECT * FROM USER WHERE UNAME = ‘zs’;

保持大小写风格

去公司之后需要看其他员工SQL大小写

— 属于SQL语句的注释

所有的查询条件为字符串时，需要用’xxx’进行修饰，否则就会当做列名去处理

常用方法

  • in 在某个范围中查找

    --查询 员工编号为 7369 7788 7881的员工信息
    select * from emp where empno in (7369,7788,7881);

  • 普通条件查询 <> 效果类似！=

    --查询薪资不等于2000的员工
    select * from emp where sal != 2000;
    select * from emp where sal <> 2000;

  • null值查询

    --查询不发放津贴的员工信息
    select * from emp where comm is null;
    ​
    --查询发放津贴的员工信息
    select * from emp where comm is not null;

  • 范围比较

    --查询薪资范围在1000-4000之间的员工信息 [1000.4000]
    select * from emp where sal between 1000 and 4000;

  • 模糊查询 % _

    --查询名字中有S的员工
    select * from emp where ename like '%S%';
    ​
    --查询名字最后一个字符是S
    select * from emp where ename like '%S';
    ​
    --查询名字第一个字符是S
    select * from emp where ename like 'S%';
    ​
    --查询名字第二个字符是A
    select * from emp where ename like '_A%';
    ​
    --查询名字没有字符是S
    select * from emp where ename not like '%S%'
    ​
    --查询名字中有%的员工
    select * from emp where ename like '%/%%';
    --查询名字第8 188个字符是A，这是需要一些特殊的手段-》函数
    -- % 代表任意字符的任意次数 _任意字符的一次

  • 多条件联合查询 and or

    --and 必须前后同时满足条件才能返回结果
    --or前后有一个满足条件就能返回结果
    ​
    --查询在20部门并且薪资大于2000的员工
    select * from emp where deptno =20 and sal >2000;
    ​
    --查询在20部门或者薪资大于2000的员工
    select * from emp where deptno = 20 or sal >2000;
    ​
    --查询不在20部门并且薪资小于2000的员工
    select * from emp where deptno <> 20 and sal <2000;

  • select结果排序 order by

    使用asc是升序排列（默认），使用desc可以降序排序

    --按照薪资进行排序（默认升序）
    select * from emp order by sal;
    ​
    --按照薪资进行排序（降序）
    select * from emp order by sal desc;
    ​
    --按照薪资进行排序（升序）
    select * from emp order by sal asc;
    ​
    --按照津贴进行排序(null排在最前面)
    select * from emp order by comm;

  • select结果分页

    --每次查询前N行
    SELECT
     * 
    FROM
     emp 
     LIMIT 4;
     
    --查询第N页,每页显示M个
    select * from emp limit 0,3;
    select * from emp limit 3,3;
    select * from emp limit 6,3;
    select * from emp limit (n-1)*M,M;
    ​
    --查询薪资大于1000的逆序排列，然后显示前5条记录
    select * from emp where sal >1000 order by sal desc limit 0,5 ;

     

函数

函数都是数据库提前给我们准备好的，所以我们可以直接调用，使用函数可以让指定的列计算出我们需要的数据

单行函数 : 指的是操作一行数据返回一行数据，操作10行数据返回10行数据

单行函数

字符串函数

image-20220502143914047

日期函数

image-20220502144036473

数字函数

image-20220502144452475

转换函数

-- 日期--》字符串
 - date_format(date,expr)
 - select DATE_FORMAT(sysdate(),'%Y-%m-%d %H:%i:%s');
 
-- 字符串--》日期
 - 要注意字符串和格式的匹配
 - select STR_TO_DATE('2020-4-16 17:15:24','%Y-%c-%d %H:%i:%s');
 - select STR_TO_DATE('5月2022年4日','%m月%Y年%d日');
 
-- 数字--》字符串
 - 直接拼接一个字符串即可，可以自动转换 lpad,concat
 
-- 字符串--》数字
 - 依靠函数提供的参数

其他函数

-- 空值的处理
    if null(exp1,exp2) exp1!=null?exp1:exp2
    select IFNULL(comm,888) from emp;
    
-- 加密算法
    select MD5('123456');
    select AES_ENCRYPT('123456','abcd'),AES_DECRYPT(AES_ENCRYPT('123456','abcd'),'abcd');
多行函数

不管函数处理多少条，只返回一条记录

如果你的数据可以分为多个组，那么返回的数据条数和组数相同

数据分组

按照某一个条件进行分组，每一组返回对应的结果

group by 可以对指定的列进行分组，列尽量有相同的

having可以对分组之后的数据进行过滤，所以能出现在having中的比较项一定是被分组的列或者是组函数

底层(注意！！！)

where称之为行级过滤，处理的是表中每一行数据的过滤

having称之为组级过滤，处理的是分组之后的每一组数据

能使用where的，尽量不要使用having

DQL单表关键字执行顺序

select: 我们要显示那些列的数据

from: 从那张表中获取数据

where: 从表中获取数据的时候进行行级的数据过滤

group by: 对数据进行分组处理，一组获取对应的结果

having: 组级过滤，组级过滤的数据必须是分组条件或者是组函数

order by: 排序 asc desc

执行的顺序(面试题)

from –> where –>group by –>select –>having–>order by

多表查询

a. 查询的两张表如果出现同名的列，我们需要将表名标注到列名前面

b. 如果是非同名的列，表名可加可不加，推荐加上

为了书写方便，可以给表添加别名

一般情况下取首字母，特殊情况下取它所代表的含义

表的别名只在本次查询中生效

c. 如果表与表进行关联查询的时候，如果不添加关联条件，查询的总记录数就是a*b = 笛卡尔积

select * from emp,dept;

a 15 b 10 c 10 –>1500条

d. 多表查询的时候必须要加条件

等值

非等值

表与表关联的方式

因为表的关联条件和业务查询条件放在了一起，为了防止混淆于是提供了下面三种方式

自然连接

-- 会自动选择列名相同并且类型相同的列
--查询薪资大于2000的员工姓名和部门名称
select e.ename,d.dname from emp e natural join dept d ;

using

-- 不需要mysql帮我们选择等值连接的列，现在我们指定等值连接的列
----查询薪资大于2000的员工姓名和部门名称 using
select e.ename,d.dname from emp e join dept d using(deptno);
select e.ename,d.dname from emp e join dept d using(deptno) where e.sal > 2000;

on（重要）

-- 我们可以指定两张表关联的条件，可以是非等值的操作
----查询薪资大于2000的员工姓名和部门名称 using
select e.ename,d.dname from emp e join dept d on(e.deptno = d.deptno);
select e.ename,d.dname from emp e join dept d on(e.deptno = d.deptno) where e.sal > 2000;
​
--查询每个员工所对应的薪资等级
select e.ename,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;
select e.ename,s.grade from emp e join salgrade s on(e.sal between s.losal and s.hisal);
表与表的外连接

当我们对两张表进行关联查询的时候，基于数据的原因导致其中一张表中的数据没办法被完全查询出来

外连接可以让没查询出来的数据也显示出来

因为我们写SQL的时候表总有左右之分 ,外连接也分为

左外连接：显示左面表所有的数据：left join

右外连接：显示右面表所有的数据: right join

表与表的自连接

我们要查询的两个字段同时处于一张表上，我们只能将一张表当做含有不同意义的两张表去处理

给相同的表取不同的简称（按照所代表的含义去取）

--查询每个员工与其直属领导的名字
select e.ename,m.ename from emp e,emp m where e.mgr = m.empno;
select e.ename,m.ename from emp e join emp m on(e.mgr = m.empno)

表与表的子连接(常用！！)

-- 把一个SQL语句的查询结果当成另外一个SQL语句的查询条件
--查询公司中薪资最低的员工姓名
select ename,sal from emp where sal = (select min(sal) from emp);
​
--查询公司中谁的薪资高于平均薪资
select ename,sal from emp where sal > (select avg(sal) from emp);
​
--谁的薪资高于20部门员工的薪资
select ename,sal from emp where sal > all(select sal from emp where deptno = 20 );
select ename,sal from emp where sal > some(select sal from emp where deptno = 20 );
select ename,sal from emp where sal in (select sal from emp where deptno = 20 );

表与表的伪表查询 （常用！！）

如果我们所需要的查询条件 需要别的SQL语句提供

如果只需要一个条件，那么可以使用子查询来完成

如果需要多个查询条件，这是就要将所有的查询结果当做伪表进行管理

我们需要把一些含有特殊符号的列名设置别名，然后给伪表设置一个别名（见名知意）

--查询高于自己部门平均薪资的员工信息
select deptno,avg(sal) avgsal from emp group by deptno;
​
SELECT
 e.ename,
 e.sal,
 e.deptno
FROM
 emp e,
 ( SELECT deptno, avg( sal ) avgsal FROM emp GROUP BY deptno ) d 
WHERE
 e.deptno = d.deptno
 AND e.sal > d.avgsal;

SQL-DML

常用语句
insert into 表名 values();
insert into 表名(列名) values(值...);
insert into 表名(列名) select ....
​
delete from 表名
delete from 表名 where 条件
-- truncate table emp;
-- 截断表--不要使用--不要使用
​
update 表名 set 列名=value ,列名=value
update 表名 set 列名=value ,列名=value where 条件
​
数据库事务

数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成

事务指的是数据库一种保护数据的方式

事务一般由增删改操作自动调用，事务根据数据库不同提交的时机也是不同的

mysql数据库默认执行增删改就会提交事务

我们可以设置为 手动提交 begin 或者 start transaction;

事务的特征

ACID原则：

原子性

事务是操作数据的最小单元，不可以再分

一致性

事务提交之后，整个数据库所看到数据都是最新的数据

所有人看到的数据都是一致的

隔离性

别人无法访问到我们未提交的数据，而且一旦这个数据被我修改，别人也无法进行操作

持久性

事务一旦被提交，数据库就进入到一个全新的状态

数据再也不能返回到上一个状态

事务如何开启和提交？

开启

当我们执行增删改操作的时候就会默认开启一个事务

这个事务和当前操作的窗口有关，别人是无法共享这个事务的

提交

手动

显示

commit; 提交

rollback; 回滚

隐式

执行DDL操作，会默认提交当前的事务

用户退出，事务统一进行回滚（Mysql）

自动

mysql数据库执行DML操作之后会自动的提交事务

好处：

方便

坏处：

不能将多个SQL纳入到一个事务，不便于管理

当我们大批量插入数据的时候，数据库会频繁的开启关闭事务影响插入效率

事务的隔离级别

根据数据库的不同用途，我们可以对数据库的事务进行级别的设置

级别设置的越高，数据越安全，但是效率越低

读未提交

我们可以读取到别人未提交的数据

有可能产生脏读的问题

读已提交

只能读取别人提交后的数据

不能达到可重复读，但是可以避免脏读

有可能产生虚读或者幻读的情况

可重复读

当数据被我查询之后，别人就不能修改这个数据了

说明在我查询的时候已经有事务操作到这些数据，查询都会开启事务

但是不能防止别人查询别的数据

序列化

当前数据库只能存在一个事务，但我操作数据库的时候，别人是不能访问数据库的

这时对于用户来讲数据相当安全，一般在倒库的时候才会开启这种级别

脏读

读取别人未提交的数据，这个数据是不安全的

虚读

第一次读取的数据，第二次在读取的时候有可能被被人修改了

幻读

第一次读取的数据，第二次多了一条或者少了一条

SQL-DDL

常见组成

库的操作

表

视图

存储过程

事件

索引

触发器

数据库的操作

# 数据库创建
create database 数据库名 charset utf8;
​
# 查看数据库
show databases;
show create database db;
desc db;
select database();
​
# 选择数据库
use 数据库名;
​
# 删除数据库
drop database 数据库名;
​
# 修改数据库
alter database db1 charset utf8;

数据类型

image-20220505191209237

image-20220505191226792

表 table的约束

约束指的是我们创建的表 别人在插入数据的时候，对数据的约束,而不是对创建人的约束

  1. 主键约束 PRIMARY KEY

    a: 主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不可能存在有相同主键值的两行数据。

    b: 主键分为单字段主键和多字段联合主键

    c: 联合主键不能包含不必要的多余字段。当把联合主键的某一字段删除后，如果剩下的字段构成的主键仍然满足唯一性原则，那么这个联合主键是不正确的。这是最小化原则。

  2. 唯一性约束 unique

    列中的值可以为空但是不能相同

  3. 非空约束 not null

    列中的值不能为null

  4. 外键约束

    定义外键时，需要遵守下列规则：

    主表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。

    必须为主表定义主键。

    主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主表中，这个外键的内容就是正确的。

    在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或唯一性键。

    外键中列的数目必须和主表的主键中列的数目相同。

    外键中列的数据类型必须和主表主键中对应列的数据类型相同。

    外键的删除

    级联删除

    设置为NULL

    阻止删除

    删除主表数据的时候，要保证这个ID没有被字表所使用

  5. 自动增长

    添加:alter table table_name modify 列名 int auto_increment

  6. 默认值

    添加:alter table table_name alter 列名 set default ‘值’

索引

简介

在MySQL中，索引（index）也叫做“键（key）”，它是存储引擎用于快速找到记录的一种数据结构。

索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对性能的影响就愈发重要。

索引优化应该是对查询性能优化最有效的手段。

-- CREATE INDEX indexName ON mytable(username); 
Create Index index_dept_dname on dept(dname);
Drop Index index_dept_dname on dept;

image-20220505191957778

分类

常规索引

常规索引，也叫普通索引（index或key），它可以常规地提高查询效率。一张数据表中可以有多个常规索引。常规索引是使用最普遍的索引类型，如果没有明确指明索引的类型，我们所说的索引都是指常规索引。

主键索引

主键索引（Primary Key），也简称主键。它可以提高查询效率，并提供唯一性约束。一张表中只能有一个主键。被标志为自动增长的字段一定是主键，但主键不一定是自动增长。一般把主键定义在无意义的字段上（如：编号），主键的数据类型最好是数值。

唯一索引

唯一索引（Unique Key），可以提高查询效率，并提供唯一性约束。一张表中可以有多个唯一索引。

外键索引

外键索引（Foreign Key），简称外键，它可以提高查询效率，外键会自动和对应的其他表的主键关联。外键的主要作用是保证记录的一致性和完整性。

 

索引是数据库中一块独立的空间，专门存储索引值的一棵B-树

我们可以通过B-树快速的定位到要查找的数据

尽量不要对重复数据的列添加索引

性别

优点

索引可以让MySQL快速地查找到我们所需要的数据，但这并不是索引的唯一作用。

索引大大减少了MySQL服务器需要扫描的数据量。

索引可以帮助服务器避免排序和临时表。

索引可以将随机I/O变为顺序I/O。

缺点

影响数据库的增删改速度

视图

简介

MySQL 视图（View）是一种虚拟存在的表，同真实表一样，视图也由列和行构成，但视图并不实际存在于数据库中。

行和列的数据来自于定义视图的查询中所使用的表，并且还是在使用视图是动态生成的。

优点

1) 定制用户数据，聚焦特定的数据

2) 简化数据操作

3) 提高数据的安全性

4) 共享所需数据

5) 更改数据格式

6) 重用 SQL 语句

创建 查看

-- CREATE VIEW <视图名> AS <SELECT语句>
-- <视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
-- <SELECT语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。

三范式

我们创建表需要遵循的规范

 

第一范式

数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组

第二范式

数据库中每一行数据必须依赖于主键，每一个行数据都要有主键

主键是一行数据的唯一性标识

第三范式

表中非主键的列要完全依赖于主键，不能出现部分属性依赖于其他属性

当出现传递依赖的时候要将非依赖于主键的列专门创建一张表进行管理

原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/tech/database/279708.html

赞 (0)
0 0
上一篇 2022年8月10日 15:04
下一篇 2022年8月10日 15:04

相关推荐

发表回复

登录后才能评论