MYSQL 数据库基本操作

数据库

服务器连接

注意：本文所使用的bjpowernode.sql文件可以在这里下载
打开Windows PowerShell，输入下面命令，以root身份连接数据库服务器。当然，命令可以运行成功需要两个前提，一是MySQL服务正在运行，二是将mysql.exe所在目录写入环境变量。

数据库服务器启动
net start MySQL80
数据库服务器关闭
net stop MySQL80
连接数据库服务器
mysql -uroot -p
在cmd环境下查看数据库版本
mysql -V
在mysql环境下查看数据库版本
select version();

数据库创建

查看数据库
show databases;
创建数据库
create database bjpowernode;
使用数据库
use bjpowernode;
查看当前数据库
select database();
往数据库中导入数据
source C:\Users\HoloLens\Desktop\bjpowernode.sql
查看数据库中的表格
show tables;
查看其它数据库中的表格
show tables from bjpowernode;
查看表格结构
desc dept;

查询

简单查询

单字段查询
select mgr from emp;
多字段查询，','隔开
select mgr,sal from emp;
条件查询
select mgr,sal from emp where sal=5000;
注意查询NULL时不能用=，需要用is
select empno,ename,comm from emp where comm is null
模糊查询
% 表示任意1-n个字符
_ 表示任意1个字符
select ename from emp where ename like '%O%';

排序

升序
select ename,sal from emp order by sal asc;
降序
select ename,sal from emp order by sal desc;
双排序
select ename,sal from emp order by sal desc,ename asc;

单行处理函数

一般函数
lower();upper();substr();length();trim();round();ifnull();等等
日期处理函数
str_to_date()

分组函数/多行处理函数

分组函数自动忽略NULL，但是不能直接使用在where语句中
count();sum();avg();max();min()

去重

select distinct job from emp;

分组

找出每个岗位的最高薪水
select job,max(sal) from emp group by job;
找出每个岗位的平均薪水，且只显示平均薪水大于1500
错误语句，分组函数不能再where中使用，必须是在group by之后才能使用
select job,avg(sal) from emp where avg(sal)>1500 group by job;
正确语句
select job,avg(sal) from emp group by job having avg(sal)>1500;

连接查询

内连接 等值连接
找出员工名字和对应的部门名字
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;
内连接 非等值连接
找出有员工名字和工资及工资对应的等级
select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
内连接 自连接
找出员工名字和对应的上级领导名字
select a.ename,b.ename from emp a join emp b on a.mgr=b.empno;
右外连接
找出每一个员工对应的部门名称，要求部门名称全部显示
select e.ename,d.dname from emp e right join dept d on e.deptno=d.deptno;
左外连接
找出每一个员工对应的部门名称，要求部门名称全部显示
select e.ename,d.dname from dept d left join emp e on e.deptno=d.deptno;
任意一个左外连接都可以改写为右外连接，反之也成立
外连接
找出员工名字和对应的上级领导，要求员工名字全部显示
select a.ename,b.ename from emp a left join emp b on a.mgr=b.empno;

子查询

找出薪水比公司平均薪水高的员工名字和薪水
select ename,sal from emp where sal > (select avg(sal) from emp);
找出每个部门的平均薪水并且找出平均薪水的等级
select p.deptno,p.avgsal,s.grade from (select deptno,avg(sal) as avgsal from emp group by deptno) p join salgrade s on p.avgsal between s.losal and s.hisal;
找出每一个员工对应的部门名称
select e.ename,(select d.dname from dept d where d.deptno=e.deptno) as dname from emp e;

分页查询

找出员工表中前5条记录
select ename from emp limit 5;
等同于
select ename from emp limit 0,5;
limit 语句下标从0开始

表格操作

表格创建删除

字段属性：字段名称 字段类型（字段长度） 约束条件
字段类型如下表所示

数据类型	含义
VARCHAR	可变长度字符串
CHAR	定长字符串
INT	INT(3) 表示最大999
BIGINT	长整型
FLOAT	浮点型单精度
DOUBLE	浮点型双精度
DATE	日期
BLOB	Binary Large Object 存储图片声音视频
CLOB	Character Large Object 可以存储4G字符串

创建一个学生表，包含学号、姓名、性别、生日、邮件
CREATE TABLE t_student(
	sno INT(10),
	sname VARCHAR(32),
	sex CHAR(1) default 'm',
	birth DATE,
	email VARCHAR(128)
);
删除学生表
drop table if exists t_student;
向表格中插入数据
insert into t_student(sno,sname,sex,birth,email) values(1,Bob,'m','1994-11-10','1234@163.com');
表格复制
create table emp1 as select * from emp;

增删改表格字段

给t_student增加一个tel字段
alter table t_student add tel varchar(10);
给tel字段长度扩展为20
alter table t_student modify tel varchar(20);
删除tel字段
alter table t_student drop tel;

增删改表格数据

insert into tablename(column1,column2,...) values(data1,data2,...);
update tablename set column1=data1,column2=data2 where;
delete from tablename where;

约束

非空约束
drop table if exists t_student;
CREATE TABLE t_student(
	sno INT(10),
	sname VARCHAR(32) not null,
	sex CHAR(1) default 'm',
	birth DATE,
	email VARCHAR(128)
);
唯一性约束
drop table if exists t_student;
CREATE TABLE t_student(
	sno INT(10),
	sname VARCHAR(32) not null,
	sex CHAR(1) default 'm',
	birth DATE,
	email VARCHAR(128) unique
);
主键约束,非空唯一，并且提供索引
drop table if exists t_student;
CREATE TABLE t_student(
	sno INT(10),
	sname VARCHAR(32) not null,
	sex CHAR(1) default 'm',
	birth DATE,
	email VARCHAR(128),
	primary key(sno)
);
外键约束
foreign key(keyname) references tablename(columnname)