MySQL_数据库基本操作&增删改查操作

小咪咪 2023-06-18 08:54 17阅读 0赞

### 文章目录 ###

*   *  数据库操作
     *  数据库引擎
     *  表的创建及约束
     *  表结构
     *  删除表
     *  数据插入
     *  数据更新
     *  数据删除
     *  数据查询（重要）

## 数据库操作 ##

-- 查看数据库
    show databases;
    -- 创建数据库
    create database test;
    -- 删除数据库
    drop database test;

## 数据库引擎 ##

show engines;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NoaWxsX0x5bg_size_16_color_FFFFFF_t_70]

*  InnoDB是事务性数据库的首选 引擎，支持事物安全表（ACID）
 *  MyISAM具有较高的插入和查询速度，不支持事务处理
 *  Memory将表的数据存储在内存中  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NoaWxsX0x5bg_size_16_color_FFFFFF_t_70 1]

-- 创建表时指定引擎,默认InnoDB
    create table person(
    	...
    )engine=InnoDB default charset=utf8;
    -- 修改表的引擎
    alter table emp engine=MyISAM;

## 表的创建及约束 ##

-- 表的创建
    create table person(
    	[约束条件]
    );
    -- 约束
    -- 主键约束:　
    数据库主键，指的是一个列或多列的组合，其值能唯一地标识表中的每一行，
    通过它可强制表的实体完整性。主键主要是用于其他表的外键关联，以及本记录的修改与删除。
    -- 单列主键
    create table person(
    	id int primary key;
    )
    -- 多列主键
    create table person(
    	id int,
    	name varchar,
    	primary key(id,name)
    )
    -- 外键约束
    -- 外键约束对应的必须是其他表的主键约束或者唯一约束
    create table dept(
    	deptno int primary key,
    	dname varchar,
    	dloc varchar
    )
    
    create table emp(
    	empno int primary key,
    	ename varchar,
    	deptno int,
    	constraint fk_dept_emp foreign key(deptno) references dept(deptno);
    )
    -- 不为空约束
    create table person(
    	id int primary key,
    	name varchar not null;
    )
    -- 唯一约束
    -- unique修饰的字段不可重复，可为null，整列仅有一个null
    -- primary key 该字段不能重复，且不能为null
    -- unique 可设置多字段为unique，不可重复，可为null
    create table person(
    	id int primary key,
    	name varchar unique
    )
    -- 默认值
    -- 没有赋值时为默认值
    create table person(
    	id int primary key,
    	sex int default 1
    )
    -- 主键自动递增
    create table person(
    	id int primary key auto_increment
    )
    -- check约束
    create table person(
    	id int primary key,
    	check(id>0)
    )

## 表结构 ##

-- 查看表结构
    desc dept;
    -- 查看创建表的SQL语句
    show create table dept;
    -- 修改表名
    alter table oldname rename newname;
    -- 修改字段数据类型
    alter table emp modify name varchar;
    -- 修改字段名
    alter table emp change empno empid int;
    -- 添加字段
    alter table emp add age int;
    -- 删除字段
    alter table emp drop age;
    -- 增加外键约束
    alter table emp add constraint fk_dept_emp foreign key(deptno) references dept(deptno);
    -- 删除外键约束
    alter table emp drop foreign key fk_dept_emp;

## 删除表 ##

-- 删除无关联表
    drop table test;
    -- 删除关联表
    -- 如果表之间存在外键关联，不允许直接删除父表，会破坏表的完整性
    -- 如果必要删除，先删除子表或者解除外键连接

## 数据插入 ##

-- 为所有字段插入数据
    insert into emp values(...);
    -- 指定字段插入
    insert into emp(age,name) values(26,"zhs");
    -- 同时插入多条数据，效率没有多行分别插入高
    insert into emp values(...),(...)...;
    -- 将查询结果插入
    insert into emp select * from emp2 where id=1;

## 数据更新 ##

update emp set name="lisi",age=10 where empno=1001;

## 数据删除 ##

delete from emp where empno=1001;

## 数据查询（重要） ##

-- 基本格式
    select *|结果列1，结果列2...
    	from 表名
    	where 条件	-- 条件过滤
    	group by 字段1，字段2..	-- 分组
    	having 条件	-- 分组后条件过滤
    	order by 字段	-- 排序
    	limit 	-- 限制查询条数
    	
    -- 给表或列取别名
    select round(pi()*2*2) area from dual;
    select empno from emp e;
    
    -- 关于null查询
    -- 背景：COMM列某些数据为null
    select sal+comm from emp;	-- 错误，null不能直接参与运算
    select sal+ifnull(comm,0) from emp;
    select sal+comm from emp where comm is not null;
    
    -- 条件组合查询 and or not between...and
    select * from emp where sal>=1000 and sal<=2000;
    select * from emp where sal between 1000 and 2000;
    select * from emp where sal<1000 or sal>2000;
    select * from emp where sal not between 1000 and 2000;
    
    -- 排序
    select * from emp order by sal desc,comm asc;
    
    -- 消除重复记录
    select distinct deptno from emp;
    
    -- 限制查询条数，实现分页
    -- 起始索引=（页数-1）*每页显示记录条数
    select * from emp limit 起始索引,记录条数;
    -- 假设每页显示n条记录，显示m页
    select * from emp limit (m-1)*n,n;
    
    -- 分组查询
    -- 通常和集合函数一起使用
    -- 如果不分组，所有记录默认一组
    select count(*),sum(sal),max(sal),min(sal),round(avg(sal),2) from emp;
    select count(*),sum(sal),max(sal),min(sal),round(avg(sal),2) from emp group by deptno;
    -- 组函数不能作为where的查询条件，必要时使用子查询
    select * from emp where sal>avg(sal); -- 错误
    select * from emp where sal>(select avg(sal) from emp);
    -- 分组后select的查询列只能为分组列或者组函数，不能查询其他任意列
    select deptno,count(*) from emp group by deptno;
    -- where在分组前过滤，having在分组后过滤
    select deptno,avg(sal) avgsal from emp group by deptno having avgsal>2000;
    -- 可以多字段分组
    select count(*) from emp group by deptno,job;
    
    -- 联合查询,多张表查询
    select ename,emp.deptno,dname
    	from emp,dept
    	where emp.deptno=dept.deptno;
    -- 内连接
    -- 两张表中都只有符合关联条件的记录才能被显示
    select ename,emp.deptno,dname
    	from emp inner join dept
    	on emp.deptno=dept.deptno;
    -- 外连接
    -- 某张表中的所有记录和另一张表中符合关联条件的记录
    -- 左连接,左边表所有记录全显示
    select empno,empname,emp,deptno,dname
    	from emp left join dept
    	on emp.deptno=dept.deptno;
    -- 右连接，右边表所有记录显示
    select empno,empname,emp,deptno,dname
    	from emp right join dept
    	on emp.deptno=dept.deptno;
    -- 自连接，物理上一张表，逻辑上两张表
    select e1.empno,e1.ename,e2.empno,e2.ename
    	from emp e1 left join emp e2
    	on emp1.mgr=emp2.empno;
    -- 不等连接 关联条件为不等关系
    select empno,ename,sal,grade
    	from emp,salgrade
    	where sal>losal and sal<hisal;
    
    -- 子查询，将一条查询语句的结果作为另一条查询语句的条件
    -- any some 比其中某一个怎么样即可
    select * from emp where sal>any(select sal from emp where deptno=20);
    -- all 比所有
    select * from emp where sal>all(select sal from emp where deptno=20);
    -- exists 当子查询有结果时执行外层查询，否则不执行
    select * from emp where exists(select ename from emp where deptno=10);
    -- in 类似逻辑判断or
    select * from emp where deptno=10 or deptno=20;
    select * from emp where deptno in(10,20);
    select * from emp where deptno in (select deptno from emp where ename in("SCOTT","BLAKE"));
    -- 合并查询
    -- union all 合并两条语句查询结果
    -- union 合并查询结果并去重
    select empno,ename,emp.deptno,dname from emp left join dept on emp.deptno=dept.deptno
    union
    select empno,ename,emp.deptno,dname from emp right join dept on emp.deptno=dept.deptno;
    
    -- 正则表达式
    select ename from emp where ename regexp "^m";	-- 不区分大小写

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NoaWxsX0x5bg_size_16_color_FFFFFF_t_70 2]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NoaWxsX0x5bg_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191201160320264.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NoaWxsX0x5bg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NoaWxsX0x5bg_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191201160653867.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NoaWxsX0x5bg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NoaWxsX0x5bg_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191201205812303.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NoaWxsX0x5bg==,size_16,color_FFFFFF,t_70