Mysql数据库

最新推荐文章于 2023-09-02 18:12:02 发布

墨痕留绝

最新推荐文章于 2023-09-02 18:12:02 发布

阅读量463

点赞数

文章标签： mysql 数据库后端

本文链接：https://blog.csdn.net/qq_31539817/article/details/106402825

版权

一：通用sql以及数据类型

1. 数据库
* 查看所有数据库：SHOW DATABASES
* 切换（选择要操作的）数据库：USE 数据库名
* 创建数据库：CREATE DATABASE [IF NOT EXISTS] mydb1 [CHARSET=utf8]
* 删除数据库：DROP DATABASE [IF EXISTS] mydb1
* 修改数据库编码：ALTER DATABASE mydb1 CHARACTER SET utf8

2. 数据类型(列类型)

int：整型
double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；
decimal：浮点型，在表单钱方面使用该类型，因为不会出现精度缺失问题；
char：固定长度字符串类型； char(255)，数据的长度不足指定长度，补足到指定长度！
varchar：可变长度字符串类型； varchar(65535), zhangSan
text(clob)：字符串类型；
  > 很小
  > 小
  > 中
  > 大
blob：字节类型；
  > 很小
  > 小
  > 中
  > 大

二：DDL（对数据库、表操作）

1，DDL操作操作数据库：CRUD

1)C(create):创建

a，创建数据库

create database 数据库名；

b，创建数据库，判断不存在，再创建

create database if not exists 数据库名称

c，创建数据库，如果判断是否存在，存在就制定相应的字符集

create database if not exists 数据库名称 character set 字符集名；

2）R（retrieve）：查询

a，查询全部数据库的名称：show databases；

b，查询数据库的字符集：show create database 数据库名；

3）U（update）：修改

a，修改数据库的字符集

alter database 数据库名 character set 字符集名称；

4）D（delete）：删除

a，删除数据库：

drop database 数据库名称；

b，判断数据库是否存在，存在就删除

drop database if exists 数据库名称；

5）使用数据库：

a，查询当前正在使用的数据库名称

select database（）；

b，使用数据库

use 数据库名称；

2，DDL操作表

1)C(create):创建

a，语法

createtable [if not exists] 表名(
列名1 列类型1 default 默认数据,
列名2 列类型2,
...
列名n 列类型n
);

2）R（retrieve）：查询

a，查询数据库中全部的表

show tables；

b，查询表结构

desc 表名；

3）U（update）：修改

a，修改表名称

alter table 表名 rename to 新表名；

b，修改表的字符集

alter table 表名 character set 字符集名称；

c，添加一列

alter table 表名 add 列名列类型；

d，修改表的列名

alter table 表名 change 列名新列名新数据类型

alter table 表名 modify 列名新数据类型

e，设置列的默认值

alter table 表名 alter column 字段名 set default 默认值;

f，在指定列前插入列

Alter table 表名 add column 插入的列名 int NOT NULL DEFAULT '0' after 列名;

g，删除列

alter table 表名 drop 列名

4）D（delete）：删除

a，删除表

drop table if exists 表名称；

b，复制表：

create table 表名 like 被复制的表名；

c，删除列队默认值

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

三：DML(表数据操作)

1，添加数据：insert into 表名(列名1,列名2, ...) values (列值1, 列值2, ...);或者insert into 表名 values (列值1, 列值2, ...);

2,修改数据

update 表名 set 列名1=列值1, 列名2=列值2, ... [where条件]

3，删除数据

delete from 表名 [where 条件];效率低，有多少跳记录就执行多少次删除语句；

truncate table 表名：TRUNCATE是DDL语句，它是先删除drop该表，再create该表。而且无法回滚

四：DQL（数据查询语言）

1，基本查询

a，查询所有列
select * from 表名;

b，查询指定列

select 列1 [, 列2, ... 列N] from 表名;

c，完全重复的记录只一次（去重复）

select distinct [ 列1 列2, ... 列N] from 表名;

d,列运算

1)数量类型的列可以做加、减、乘、除运算

select sal*1.5 from emp;
select sal+comm from emp;

2)字符串类型可以做连续运算
select concat ('$', sal) from emp;

3)转换NULL值

有时需要把NULL转换成其它值，例如com+1000时，如果com列存在NULL值，那么NULL+1000还是NULL，而我们这时希望把NULL当前0来运算。
select ifnull(comm, 0)+1000 from emp;

4)给列起别名，as可以省略

select comm as 奖金 from emp;

e，全部语法

select 字段列表

from 表名

where 条件列表

Groud by 分组列表

having 分组之后的条件

order by 排序

limit 分页限定

2. 条件控制
1) 条件查询
与前面介绍的UPDATE和DELETE语句一样，SELECT语句也可以使用WHERE子句来控制记录。
* SELECT empno,ename,sal,comm FROM emp WHERE sal > 10000 AND comm IS NOT NULL;
* SELECT empno,ename,sal FROM emp WHERE sal BETWEEN 20000 AND 30000;
* SELECT empno,ename,job FROM emp WHERE job IN ('经理', '董事长');
2) 模糊查询

占位符：_:表示单个字符；%：表示多个任意字符
当你想查询姓张，并且姓名一共两个字的员工时，这时就可以使用模糊查询
SELECT * FROM emp WHERE ename LIKE '张_';
--> 模糊查询需要使用运算符：LIKE，其中_匹配一个任意字符，注意，只匹配一个字符而不是多个。
--> 上面语句查询的是姓张，名字由两个字组成的员工。
SELECT * FROM emp WHERE ename LIKE '___'; /*姓名由3个字组成的员工*/

如果我们想查询姓张，名字几个字可以的员工时就要使用“%”了。
SELECT * FROM emp WHERE ename LIKE '张%';
--> 其中%匹配0~N个任意字符，所以上面语句查询的是姓张的所有员工。
SELECT * FROM emp WHERE ename LIKE '%阿%';
--> 千万不要认为上面语句是在查询姓名中间带有阿字的员工，因为%匹配0~N个字符，所以姓名以阿开头和结尾的员工也都会查询到。
SELECT * FROM emp WHERE ename LIKE '%';
--> 这个条件等同与不存在，但如果姓名为NULL的查询不出来！
3）查询条件运算符

between....and
in（集合）
like
is null
and 或&&
or 或 ||
not 或者！

3，排列查询

语法：order by 字句

order by 排序字段1 排序方式1，排序字段2 排序方式2 .....排序字段n 排序方式n;

排序方式：ASC，默认排序；DESC,降序排序

注意：只有当一个排序值一样是，才会判断后面的排序条件；，

4，聚合查询

聚合函数用来做某列的纵向运算。
1) count
SELECT COUNT(*) FROM emp;
--> 计算emp表中所有列都不为NULL的记录的行数
SELECT COUNT(comm) FROM emp;
--> 计算emp表中comm列不为NULL的记录的行数
2) MAX
SELECT MAX(sal) FROM emp;
--> 查询最高工资
3) MIN
SELECT MIN(sal) FROM emp;
--> 查询最低工资
4) SUM
SELECT SUM(sal) FROM emp;
--> 查询工资合
5) AVG
SELECT AVG(sal) FROM emp;
--> 查询平均工资

5，分组查询

语法：Groud by 分组字段

select deptno, count(*) from emp groud by deptno having count (*) > 3;

Groud by 和having的区别：

1，where在分组之前进行限定，如果不满足条件，则不参与分组，having在分组之后进行限定，如果不满足结果，就不会被查询出来。

2，where之后不可以跟聚合函数，having可以进行聚合函数的判断

6，分页查询

LIMIT用来限定查询结果的起始行，以及总行数。

如查询起始行为第5行，一共查询3行记录
select * from emp limit 4, 3;

五：约束

1，概念,

对表中的数据进行限定，保证数据的正确性，有效性和完整性。

2，分类

a，主键约束：primary key

b，非空约束：not null

c，唯一约束：unique

d，外键约束：foreign key

3，主键约束

a；注意：主键约束表示非空且唯一，一个表只能有一个主键，为一张表记录的唯一标识；

b，创建表时加约束；id int（20） primary key；

c，删除主键：alter table 表名 drop primary key ；

d，列添加主键：alter table 表名 modify id int primary key；

e：主键自增长： auto_increment

4，非空约束：

a，创建表的时候：name varchar（20） not null；

添加默认值：name varchar（20） not null default 0；

d，删除非空约束：alter table 表名 modify 列名新数据类型 not null；

c，给列添加非空约束：

5，唯一约束：unique

a，创建表的时候：name varchar（20） unique；

b，删除唯一约束：alter table 表名 modify 列名新数据类型；

c；给列添加唯一约束：alter table 表名 modify 列名新数据类型 unique；

注意：唯一约束允许多个null值存在

6,外键约束：

a, 在创建表的时候，可以添加外键列

create table 表名（

......

外键列列类型

constraint 外键名称(一般是本表名_关联表名_fk) foreign key （外键列名称） references 需要关联表名称（需要关联列名称）

或者

FOREIGN KEY (sid) REFERENCES stu(sid)

）；

b,删除外键：alter table 表名 drop foreign key 外键名称；

d，给某一列加外链：ALTER TABLE 表名称 ADD FOREIGN KEY (外链名称) REFERENCES 需要关联表名称（需要关联列名称）;

e，级联操作

有时我们需要修改被关联表的主键，这时，由于被关联表有外键约束，不可以修改，这时就需要使用级联操作进行修改。使用级联之后，只要修改一个表的的关联数据，就会使得另一个表跟着被修改。

语法：ALTER TABLE 表名称 ADD FOREIGN KEY (外链名称) REFERENCES 需要关联表名称（需要关联列名称） on delete cascade on update cascade；

六：数据库设计

1，多表之间的关系

a，分类：一对一；一对多（多对一）；多对多

2，一对一关系实现

可以通过任意一方通过外键指向另一方

3，一对多关系实现

通过外键实现，关系为多的一方增加一个外键列，然后指向关系为一的表的主键；

4，多对多关系实现

通过建立一个中间表，列名分别为两个表的主键名（也可以不是），然后分别让着两个表的主键当做外键指向这个中间表对应的列；

七：数据库的范式

设计数据库时需要遵循的一些规范

数据来源：https://www.cnblogs.com/xietianjiao/p/10972285.html

第一范式（1NF）：所有的域都应该是原子性的

例如下图一：

图一

在图一中，系可以再分，则这个表不符合第一范式。如需要符合第一范式，则需要将其拆分如下图二所示：

图二

第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）

如上图二所示，表存在问题，学号，名字，系名，系主任存在重复，使得数据冗余（同一数据存储在不同数据文件中的现象）

在解决之前，需要知道几个概念：

a：函数依赖：A-->B,如果函数通过A属性（组）的值，可以确定唯一B属性的值，则B依赖于A；

如图二中的学号可以确定姓名，就说明姓名依赖于学号（学号-->姓名）；学号和课程名称可以确定分数，就说明分数依赖于学号和课程；【（学号，课程）-->分数】

b：完全依赖函数：A-->B，如果A是一个属性组，则B属性值的确定需要依赖于A属性组中所有的属性值

例如学号和课程名称可以确定分数，就说明分数依赖于学号和课程；

c：部分依赖函数：A-->B，如果A是一个属性组，则B属性值的确定只需要依赖A属性组的某些值就可以；

例如：（学号，课程）-->姓名，在这个属性组中只要学号就可以确定姓名

d：传递依赖函数：A-->B；B-->C。,如果函数通过A属性（组）的值，可以确定唯一B属性的值，，再通过B属性（组）的值可以确定唯一C属性的值，则称C专递依赖于A。

如学号-->系名；系名-->系主任

e：码：如果在一张表中，一个属性或者属性组，被其他属性所完全依赖，则这个属性被称为码；

例如图二的表的码为（学号，课程名称）

码的概念，我们建表拆分为如下图三所示：

图三

第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

在图三中，虽然我们去掉了数据的冗余，但是当我们删除学号为10010的数据，会发现系名，系主任也会被删除，这不符合我们所需要的；通过第三范式的概念我们可以知道，在学生表中存在专递依赖，学号-->系名；系名--》系主任；也就是说系主任专递依赖学号；这是我们还需要进行拆分学生表；如下图四所示：

图四

如图四所示，我们删除学号为10010的数据，会发现系名和系主任不会被删除，则就符合第三范式；

当然了，这个表还有许多要改进的地方，比如可以再分为成绩表，只是为了理解就不再分了。

八：多表查询

给出两个表为dept和

emp表

dept表

完全查询为：

图五

我们发现图五有了很多的有很多的重复数据，这时候需要清除这些重复数据，（去笛卡尔积），下面将会介绍如何去掉重复无用数据；

1，内连接查询：分为隐式内连接和显式内连接，结果一样的。主要查询交集部分；

a：隐式内连接：使用where条件来消除无用的数据

查询：

select * from emp，dept

where emp.dept_id=dept.id;

查询结果

b：显式内连接：

语法：select 字段列表 from 表名 inner join 表名2 on 条件

如：select * from emp inner join dept on emp.dept_id=dept.id

2，外连接查询：分为左外连接和右外链接，查询某表全部记录和交集部分；

a，左外连接：

语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；

例如：select t1.*,t2.name from emp t1 left join dept t2 on t1.dept_id=t2.id ;

向左补空，查询左表的全部记录和交集部分；

b，右外连接：

语法：select 字段列表 from 表1 right [outer] join 表2 on 条件；

例如：select t1.*,t2.name from emp t1 right join dept t2 on t1.dept_id=t2.id ;

向右补空，查询右表全部记录和交集部分，查询结果如果需要和左外连接一样，表换位置就可以了。

3，子查询：查询中嵌套查询

例如（使用上表数据操作）：

查询工资最高的员工的信息

select * from emp where emp.salary=（(select max(salary) from emp);

a，子查询的不同情况：

单行单列：子查询作为条件，使用运算符去判断。

查询员工工资小于平均值的人

select * from emp where emp.salary<（(select avg(salary) from emp);

多行单列：子查询作为条件使用in来判断

查询财务部和市场部所有的员工信息

select * from emp where dept_id in (select id from dept where name="财务部" or name=“市场部”);

多行多列： 子查询作为一张虚表

查询员入职日期为2011-11-11之后的员工信息和部门信息

select * from dept t1 ，(select * from emp where emp.join_data>'2011-11-11') t2

where t1.id=t2.dept_id;

九，事务管理

1，事务

a，概念：访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。

b，操作

开启事务：start transaction

回滚：rollback

提交：commit

2，事务的四大特征：

原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败；

持久性：当事务提交或者回滚后，数据库会持久化保存数据

隔离性：多个事物之间，相互独立。

一致性：事务操作前后，数据总量不变。

3，事务的并发问题

　　脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

　　不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。

　　幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

4，隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（read uncommitted）	是	是	是
不可重复读（read committed）mysql默认	否	是	是
可重复读（repeatable read）oracle默认	否	否	是
串行化（serializable）	否	否	否

5，设置隔离级别：

查看当前会话隔离级别

select @@tx_isolation;

查看系统当前隔离级别

select @@global.tx_isolation;

设置当前会话隔离级别

set session transaction isolatin level repeatable read（隔离字符集）;

设置系统当前隔离级别

set global transaction isolation level repeatable read（隔离字符集）;

十：DCL：管理用户，授权

1，创建用户：

create user ‘用户名’@‘主机名’ identified by ‘密码’

2，删除用户：

drop user ‘用户名’@‘主机名’；

3，修改密码：

普通用户密码：

a,update user set password =password（‘新密码’） where user=‘用户名’；

b,set password for ‘用户名’@‘主机名’ =password（‘新密码’）；

root用户密码：

地址：https://www.jb51.net/article/100925.htm

4，授权

a，查询权限：show grants for ‘用户名’@‘主机名’

b，授权权限：grant 授权列表 on 数据库名.表名 to ‘用户名’@‘主机名’；

c，撤销权限：revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’；

墨痕留绝

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Mysql数据库

一：通用sql以及数据类型1. 数据库* 查看所有数据库：SHOW DATABASES* 切换（选择要操作的）数据库：USE 数据库名* 创建数据库：CREATE DATABASE [IF NOT EXISTS] mydb1 [CHARSET=utf8]* 删除数据库：DROP DATABASE [IF EXISTS] mydb1* 修改数据库编码：ALTER DATABASE mydb1 CHARACTER SET utf82. 数据类型(列类型)int：整型double：浮点型，
复制链接

扫一扫