MySQL基础

最新推荐文章于 2024-09-12 12:42:46 发布

任腾飞QAQ

最新推荐文章于 2024-09-12 12:42:46 发布

阅读量388

点赞数 1

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_51501165/article/details/121350269

版权

DDL

数据定义语言 (Data Definition Language, DDL) 是SQL语言集中，负责数据结构定义与数据库对象定义的语言，以CREATE、ALTER与DROP三种指令为核心组成。

创建

创建数据库

创建数据库:
create database 数据库名称;
创建数据库时,判断不存在，再创建
create database if not exists 数据库名称
创建数据库并指定字符集
create database 数据库名称 character set 字符集名;
创建名字为name的数据库，并判断是否存在，并指定字符集为gbk
create database if not exists name character set gbk;

创建表

create table 表名(
  列名1 数据类型1,
  列名2 数据类型2,
  ...
);
(注意最后一行不加逗号)

复制表: create table 表名 like 被复制的表名;

数据类型

varchar：最长为255位，可变长度的字符串。
char：最长为255位，定长字符串
int: 最长为11位，整数型
bigint：长整数
float：单精度浮点类型
double：双精度浮点类型
date：短日期类型
datetime：长日期类型
clob：字符大对象，最多可以存储4G的字符串。超过255个字节的字符串都采用clob进行存储。例如存储一篇文章。
blob：二进制大对象。专门存储图片、声音、视频等流媒体数据。往blob类型中插入数据时需要使用IO流。

修改

改变数据库的字符集
alter database name character set 字符集名称;
修改表名
alter table 表名 rename to 新表名;
修改表的字符集
alter table 表名 character set 字符集名称;
添加一列
alter table 表名 add 列名数据类型;
修改列的名称类型
alter table 表名 change 列名新列名新数据类型;
alter table 表名 modify 列名新数据类型;
删除列
alter table 表名 drop 列名;

删除

删除数据库
drop database name;
判断数据库是否存在,存在再删除
drop database if exists name;
删除表
drop table 表名;
判断表是否存在,存在再删除
drop table if exists 表名;

DQL

数据查询语言（Data Query Language, DQL）是SQL语言中，负责进行数据查询而不会对数据本身进行修改的语句，这是最基本的SQL语句。关键字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的关键字有FROM，WHERE，GROUP BY，HAVING和ORDER BY。这些DQL保留字常与其他类型的SQL语句一起使用。

简单查询

查询语法：
select 字段1, 字段2, … from 表名;
当查询的字段是表中的所有字段时可以将字段1, 字段2, … 整体替换成为*
可以给查询出来的字段其别名
select 字段1 as 别名1, 字段2 as 别名2, … from 表名; （as可以省略）
查询出来的字段可以参与四则运算

条件查询

条件查询是以在简单查询的基础上在关键字where后面添加需要查询的条件
格式为：select 字段1, 字段2, … from 表名 where 条件;
条件支持的符号
运算符>,<,<=,>=,=,<>
BETWEEN…AND
IN(集合)
LIKE (模糊查询)
_:单个任意字符
%:多个任意字符
IS NULL(当条件是判断字段为null不能直接使用=号而需要使用IS NULL)
AND 或 &&
OR 或 ||
当AND和OR同时使用时AND的优先级高于OR
NOT 或 !=

排序

语法: order by 排序字段1 排序方式1, 排序字段二排序方式,…
排序方式
ASC 升序,默认
DESC 降序
注意：如果有多个字段需要排序,字段一相同才回去排序字段二以此类推

各关键字的执行顺序

from：从某张表中查询数据
where：先经过where条件筛选出有价值的数据
group by：对有价值的数据进行分组
having：对分组完的数据继续筛选
select：查询出对应的字段
order by：最后进行排序

单行处理函数

一个输入(一行)对应一个输出。

函数名	作用
lower	转换小写
upeer	转换大写
substr	取子串
length	取长度
trim	去空格
str_to_date	将字符串转换成日期
date_format	格式化日期
format	设置千分位
round	四舍五入
rand()	设置随机数
ifnull	将null值转换为具体值

多行处理函数

多个输入(多行)对应一个输出。需要注意的是多行处理函数使用前必须分组，默认情况下一张表就是一个分组。由于where语句执行前没有分组所以where语句后面不能使用多行处理函数。并且多行函数的计算是排除null值的。

函数名	作用
max	计算最大值
min	计算最小值
sun	计算和
ave	计算平均值
count	统计个数

分组查询

语法: select 字段1，字段2… from 表名 where 条件 group by 分组字段1, 分组字段2…;
需要注意的是select后面跟的字段必须是分组字段，或者是使用了多行处理函数的字段。
使用having可以对分组完的数据进行进一步的过滤，having不能单独使用必须和group by联合使用。having也不能代替where语句。由于where在分组前执行所以不能使用多行处理函数而having在分组后执行所以可以使用多行处理函数。另外distinct可以去除重复字段，但必须加在所有字段的前面。

连接查询

内连接

SQL92标准：select 字段1, 字段2… from 表1，表2…where 连接条件 + 筛选条件
SQL99标准：select 字段1, 字段2… from 表1 join 表2 on 连接条件 where 筛选条件
99标准相比于92标准，SQL语句的连接条件和筛选条件分离使得SQL语句变得更加清楚

外连接

SQL99标准：select 字段1, 字段2… from 表1 left join 表2 on 连接条件 where 筛选条件
左外连接即左表是主表，主要是查出左表的全部信息顺带着查询右表的表。

union

union关键字用于合并两个select语句的查询结果。可以优化某一些连接查询的效率。
因为对于连接查询，查询次数满足笛卡尔积而union可以减少查询次数从而达到提高效率的目的。

子查询

子查询就是在select语句中嵌套一个select语句。
当嵌套的select语句出现在where后面查询结果可以看作是一个数值，出现在from后面相当于一张临时表，select后面也可以跟子查询，但该子查询的结果必须只能是一条否则将报错。

分页查询

limit可以将查询结果集中的一部分数据取出来进行展示，通常用于分页查询中。分页查询可以提高用户的体验。
用法: limit startIndex length startIndex为起始下标，length为长度；limit跟在order by后面，即limit在order by执行后执行。

DML

数据操纵语言（Data Manipulation Language, DML）是SQL语言中，负责对数据库对象运行数据访问工作的指令集，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除。

insert

添加数据
语法:insert into 表名(列名1, 列名2, …) values(数值1, 数值2, …);
注意
1.列名与数值需要一一对应
2.如果没有列名,则默认给所有的列添加值
3.除了数字类型，其他数据类型都要加双引号或者单引号

update

语法:update 表名 set 列名1 = 值1, 列名2 = 值2, … where 条件
注意:
1.如果不注意添加条件，则表中所有的数据都会被修改

delete

语法:delete form 表名 where 条件;
注意:
1.如果不加条件，则删除表中所有记录
2.如果要删除所有记录
1.delete form 表名; 不推荐使用,表中有多少条记录就要执行多少次操作，效率低
2.TRUNCATE TABLE 表名; 推荐使用,效率高,先直接删除该表,然后创建一张相同的空表

DCL

数据控制语言 (Data Control Language) 在SQL语言中，是一种可对数据访问权进行控制的指令，它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。由 GRANT 和 REVOKE 两个指令组成。DCL以控制用户的访问权限为主，GRANT为授权语句，对应的REVOKE是撤销授权语句。
1.管理用户:
添加用户: create user ‘用户名’’@’‘主机名’ / %’ IDENTIFIED BY ‘密码’;
2.删除用户: drop user ‘用户名’’@’‘主机名’
3.修改用户密码:
4.查询用户:切换到mysql数据库下,查询user表
5.权限管理
查询:show grants for ‘用户名’’@’‘主机名’
授予:grant select, update on 数据库名.表名 from ‘用户名’’@’‘主机名’
grant all on . from ‘用户名’’@’‘主机名’
撤销:remove update on 数据库名.表名 from ‘用户名’’@’‘主机名’
revoke * on 数据库名.表名 from ‘用户名’’@’'主机名’

约束

约束就是在创建表的时候，我们可以在字段上加上一些约束条件，来保证表中数据的完整性与有效性。

非空约束

not null，使得字段不能为空。

唯一约束

unique，使得字段唯一，但是可以为NULL。

主键约束

primary key，使得字段不为空且唯一，是表中每一行数据的ID。一张表中必须有且只有一个主键。
主键建议使用int、bigint等数据类型。尽量使用自然主键，不要使用业务主键。
在myssql中被not null、unique联合约束的字段称为主键约束。

外键约束

foreign key，A(子表)表中引用B(父表)表的字段称为A表的外键。
这时，删除表先删子再删父，创建表先创父再创子，删除数据先删子再删父，插入数据先插父再插子。
子表中的外键值可以为NULL，并且外键不一定是父表的主键，但至少具有唯一性。

事务

事务是一个完整的业务逻辑。该业务逻辑是一个最小的工作单元不可再分，要么同时成功要么同时失败。本质上，事务说到底就是多条DML语句同时成功或者失败。
需要注意的是只有DML语句才和事务有关，其他语句和事务无关。只要涉及增删改那么一定要考虑安全问题，数据安全是第一位。

事务的实现

在事务的执行过程中，每一条DML语句操作都会记录在事务活动日志文件中去。在执行事务中可以进行提交事务，也可以进行回滚事务。
事务的提交与回滚都代表了事务的结束且会清空事务日志文件。但是提交代表了DML操作的成功，会将数据持久化到数据库中去。而会滚则代表DML操作的失败，会撤销所有的DML操作。

提交与回滚

默认请求下，MySQL支持自动提交事务。每执行一条DML语句提交一次事务。
执行 start transaction关闭自动提交事务。
执行commit进行提交事务
执行rollback进行回滚事务

事务特性

A(原子性)：说明事务是最小的工作单元不可再分。
C(一致性)：一个事务中的操作必须同时成功或者同时失败
I(隔离性)：A事务和B事务具有一定的隔离性。A与B事务同时操作一张表会怎么样？
D(持久性)：事务的提交，将没有保存在硬盘上的数据保存在硬盘上。

事务的隔离级别

读未提交(最低)：事务A可以读取事务B未提交的数据。但是会出现脏读现象。
读已提交：事务A可以读取事务B已提交的数据，数据够真实。解决了脏读现象，但是会出现不可重复读问题。
可重复读：当事务B开始之后，不管多久，每一次事务A读取事务B的数据都是一致的(事务B开始前的数据)，即使对事务B将数据进行修改。该隔离级别解决了不可重复读问题，但是会出现幻读问题，数据不够真实。
序列化/串行化(最高)：解决了所有问题，但是效率最低。这种隔离级别表示事务排队，不能并发。

索引

索引是一种提高查询效率的机制。索引可以添加在字段上，也可以多个字段字段联合添加索引。其本质就是减少扫描的范围来提高效率。
主键约束和唯一约束的字段，在mysql数据库中会自动创建索引。
使用索引的条件:
1.数据量庞大
2.该字段经常出现在where的后面，即该字段经常被扫描。
3.该字段很少使用DML语句
创建索引:
create index emp_ename_index on emp(ename);
给emp表的ename字段添加索引，并给索引起名为emp_ename_index
删除索引:
drop index emp_ename_index on emp;
将emp表上的emp_ename_index索引对象删除
索引失效:
1.模糊查询中，匹配字段以’%'开头
2.如果使用or，那么or两边的字段都要有索引才会走索引否则索引失效。所以不建议使用or。
3. 使用复合索引的时候，左侧列查找索引会失效。
4. 在where中索引字段参与数学运算，索引失效。