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

数据库操作

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

数据库引擎

show engines;

• InnoDB是事务性数据库的首选 引擎，支持事物安全表（ACID）
• MyISAM具有较高的插入和查询速度，不支持事务处理
• Memory将表的数据存储在内存中
  

-- 创建表时指定引擎,默认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";	-- 不区分大小写