mysql数据库操作

mysql数据库操作

@千年老妖
1.Mysql简介
关系型数据库：Mysql Oracle SqlServer…一对一，一对多等关系
非关系型数据库：MongoDB (NOSql数据库)
SQL（Structured Query Language）
Mysql将所有的数据以表格的形式保存
Mysql中的常见数据类型：
数字：int
字符串：varchar
日期：date

2.数据库简单命令
1.进入数据库：
Mysql [-h主机名 -P端口号] -u 用户名 -p 回车 输入密码
2.查询所有数据库
show databases;
3.使用某一个数据库
use 数据库名称; use test;
4.创建一个数据库
create database SJT1903;
5.查看数据库编码
show create database sjt1903;
6.删除数据库
drop database sjt1903;
7.查看所有表格
show tables;
8.创建新的表格 id username password age
create table t_student (id int(5),username varchar(10),password varchar(15),age int(3));
9删除表格
drop table t_user;
10查询表格中的数据
select * from t_student;
设置编码：set names utf8; /gbk
\c gbk

11.添加数据
insert into t_student (id,username,password,age) values (1,“zs”,“123456”,20);
insert into t_student values (1,“ls”,“123456”,20);
12.删除数据
delete from t_student 清空表格
delete from t_student where id = 2; 删除指定条件的数据
Truncate table +表名 直接截断表
13.修改数据
update t_student set username = “admin”,password = “admin” where id = 2;

表虽然创建了，但是没有约束
3.约束条件
3.1.非空约束(NK)
not null
3.2.唯一约束(UK)
Unique
create table t_user(
id int(3) not null,
username varchar(5) unique,
password varchar(10)
);
或
create table t_user(
id int(3) not null,
username varchar(5),
password varchar(10),
constraint UK_username unique(username)
);
3.3.主键约束(PK)
任意一张表中都应该存在主键 ID作为主键
ID一般不参与业务逻辑运算
主键约束 == 非空约束+唯一约束
create table t_user(
id int(3),
username varchar(5),
password varchar(10),
constraint PK_id primary key(id)
);
3.4.检查约束(CK)
create table t_user(
id int(3),
username varchar(5),
password varchar(10),
age int(3),
constraint PK_id primary key(id),
constraint CK_age check(age between 1 and 120)
);
Mysql可以设置检查约束，但是不检查
3.5.外键约束(FK)
一对多
t_user
id username password age
1 张三 123456 20
2 李四 123456 21

t_book
bid bname author uid
1 飞狐外传 金庸 1
2 雪山飞狐 金庸 2
3 连城诀 金庸 1
create table t_user(
id int(3),
username varchar(5),
password varchar(10),
age int(3),
constraint PK_id primary key(id),
);
create table t_book(
bid int(3)
bname varchar(5),
author varchar(10),
uid int(3),
constraint PK_bid primary key(bid),
constraint FK_uid foreign key(uid) references t_user(id)
);
默认情况：
删除一的一方，需要检测在多的一方是否有被引用，如果有外键引用，则无法删除

删除一的一方的时候，想要直接删除，比如删除某一个用户，
第一种：级联删除
create table t_book(
bid int(3),
bname varchar(5),
author varchar(10),
uid int(3),
constraint PK_bid primary key(bid),
constraint FK_uid foreign key(uid) references t_user(id) on delete cascade
);
第二种：解除关系
create table t_book(
bid int(3),
bname varchar(5),
author varchar(10),
uid int(3),
constraint PK_bid primary key(bid),
constraint FK_uid foreign key(uid) references t_user(id) on delete set null
);

4.SQL分类
一：数据查询语言（DQL:Data Query Language）：
其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。这些DQL保留字常与其他类型的SQL语句一起使用。
二：数据操作语言（DML：Data Manipulation Language）：
其语句包括动词INSERT，UPDATE和DELETE。它们分别用于添加，修改和删除表中的行。也称为动作查询语言。
三：事务处理语言（TPL）：
它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION，COMMIT和ROLLBACK。
四：数据定义语言（DDL）：
其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。

数据库的设计：
购物商城数据库表结构V1.0
用户表t_user
字段 类型（大小） 描述
id int 用户表的唯一标识
username varchar(14) 用户名，唯一的，用于用户登录
password varchar(100) 用户密码，用户用户登录,存储到数据库中的密码，需要进行加密（MD5）
realname varchar(30) 用户的真实姓名
cardid varchar(18) 身份证号
phone varchar(11) 个人电话
userInfo varchar(140) 个人简介

商品表t_goods
字段 类型（大小） 描述
id int 商品表的唯一标识
goodsname varchar(14) 商品名称
goodsprice double(10,2) 商品的价格
goodsInfo varchar(140) 商品的简介
goodstype_id Int 商品类型

商品类别表t_goodstype
字段 类型（大小） 描述
id int 商品类别表的唯一标识
goodstype varchar(10) 商品类型名称
mark varchar(140) 商品类型简介

购物车表t_cart
字段 类型（大小） 描述
id int 购物车表的唯一标识
user_id int 用户id，当前购物车数据属于哪个用户
goods_id int 购物车中存放的商品信息
goods_count Int(3) 购物车中商品的数量

地址表t_address
字段 类型（大小） 描述
id int(9) 地址表的唯一标识
user_id int(9) 用户id，当前购地址数据属于哪个用户
address Varchar(255) 收货详细地址
postalcode int(6) 邮政编码
conname varchar(30) 收货人姓名
conphone varchar(20) 收货人电话

5.数据查询语言DQL
Emp 员工表
字段 类型 描述
empno mediumint(9) 员工编号
ename varchar(20) 员工姓名
job varchar(9) 员工职位
mgr mediumint(9) 领导编号
hiredate date 入职日期
sal decimal(7,2) 员工工资
comm decimal(7,2) 员工奖金
deptno mediumint(9) 部门编号

Dept 部门表
字段 类型 描述
deptno mediumint(8) 部门编号
dname varchar(20) 部门名称
loc varchar(13) 部门位置
Salgrade 工资等级表
字段 类型 描述
grade mediumint(8) 工资等级
losal decimal(7,2) 最低工资
hisal decimal(7,2) 最高工资

5.1.简单查询
查询所有信息
Select * from emp;
查询指定字段的信息
SELECT empno,ename,job FROM emp;
查询结果后指定别名
SELECT e.empno 员工编号,e.ename 员工姓名,e.job 员工职位 FROM emp e;
查询职位
SELECT e.job FROM emp e;
发现有重复职位，就应该去重 distinct
SELECT DISTINCT e.job FROM emp e;
查询每一个员工的年薪
5.2.限定查询
SELECT * FROM 表名称
[WHERE 条件]
< > >= <= != <> between…and… or and … is null
like in

查询工资高于1500的员工信息
select * from emp WHERE sal > 1500;
查询工资介于1500到3000的员工信息
select * from emp WHERE sal > 1500 and sal < 3000;
select * from emp WHERE sal BETWEEN 1500 and 3000;
查询每个月没有奖金的员工信息
select * from emp where comm is null or comm = 0
查询每个月有奖金的员工信息
select * from emp where comm > 0
查询每个月有奖金并且工资高于1500的员工信息
select * from emp where comm > 0 and sal > 1500
查询在1981年入职的员工
select * from emp where hiredate BETWEEN “1981-01-01” and “1981-12-31”
select * from emp where hiredate like “%1981%”
查询员工姓名以S开头的信息
select * from emp where ename like “S%”
查询第二个字母为O的员工信息
select * from emp where ename like “_O%”
查询员工编号在7499,7369,7521的员工信息
select * from emp where empno = 7369 or empno = 7499 or empno = 7521
select * from emp where empno in (7369,7499,7521)
5.3.查询排序
SELECT * FROM 表名称
[WHERE 条件]
[排序]
排序关键字：order by 字段 升序/降序
升序：asc
降序：desc
按照工资由低到高排序
select * from emp order by sal asc
如果工资相同，按照入职日期排序
select * from emp order by sal asc,hiredate desc

5.4.多表查询
SELECT * FROM 表名称1,表名称2…
[WHERE 条件]
[排序]
排序关键字：order by 字段 升序/降序
升序：asc
降序：desc

如果一条sql语句想要查询多张表的记录，需要用到多表查询
一旦有多表查询，必须要有关联条件
查询员工与部门表的详细信息
select * from emp,dept
查到的是14*4=56条记录，两张表的乘积
这种现象叫做笛卡尔积效应
找到两张表中的关联字段
select * from emp,dept where emp.deptno = dept.deptno
追加关联条件，只是显示上消除了没用记录，只是显示上消除了笛卡尔积，但是底层数据库还是会运算56次

1.查询每个员工的编号，姓名，职位，部门名称，部门位置
逐步分析：
需要用到的表：
emp e,dept d
需要用到的字段：
e.empno 编号,e.ename 姓名,e.job 职位,d.dname 部门名称,d.loc 部门位置
需要的关联条件
e.deptno = d.deptno
组装sql:
Select e.empno 编号,e.ename 姓名,e.job 职位,d.dname 部门名称,d.loc 部门位置 from emp e,dept d where e.deptno = d.deptno

2.查询每一个员工的员工姓名，职位和领导姓名
逐步分析：
需要用到的表：
emp e1,emp e2
需要用到的字段：
e1.ename 员工姓名,e1.job 员工职位,e2.ename 领导姓名
需要的关联条件
e1.mgr = e2.empno
组装sql:
Select e1.ename 员工姓名,e1.job 员工职位,e2.ename 领导姓名
From emp e1,emp e2
Where e1.mgr = e2.empno

3.查询员工编号，姓名，工资，职位，所在部门名称，部门位置，领导姓名（员工详细信息）
逐步分析：
需要用到的表：
emp e1,emp e2,dept d
需要用到的字段：
e1.empno 编号,e1.ename 员工姓名,e1.sal 工资,e1.job 员工职位,e2.ename 领导姓名,d.dname 部门名称,d.loc 部门位置
需要的关联条件
e1.mgr = e2.empno and e1.deptno = d.deptno
组装sql:
Select e1.empno 编号,e1.ename 员工姓名,e1.sal 工资,e1.job 员工职位,e2.ename 领导姓名,d.dname 部门名称,d.loc 部门位置
From emp e1,emp e2,dept d
Where e1.mgr = e2.empno and e1.deptno = d.deptno

4.查询每一个员工的编号，姓名，职位，部门名称，工资等级
逐步分析：
需要用到的表：
emp e,dept d,salgrade s
需要用到的字段：
e.empno,e.ename,e.job,d.dname,s.grade
需要的关联条件
e.deptno = d.deptno and e.sal between s.losal and s.hisal
组装sql:
Select e.empno,e.ename,e.job,d.dname,s.grade
From emp e,dept d,salgrade s
Where e.deptno = d.deptno and e.sal between s.losal and s.hisal
5.5.左右连接查询
左(外)连接：left (outer) join…on…
右(外)连接：right (outer) join…on…
SELECT * FROM 表名称1 left join表名称2 on 连接条件
[WHERE 条件]
[排序]
查询每一个员工的员工姓名，职位和领导姓名
Select e1.ename 员工姓名,e1.job 员工职位,e2.ename 领导姓名
From emp e1 left join emp e2 on e1.mgr = e2.empno
查询员工编号，姓名，工资，职位，所在部门名称，部门位置，领导姓名（员工详细信息）
Select e1.empno 编号,e1.ename 员工姓名,e1.sal 工资,e1.job 员工职位,e2.ename 领导姓名,d.dname 部门名称,d.loc 部门位置
From emp e1 left join emp e2 on e1.mgr = e2.empno ,dept d
Where e1.deptno = d.deptno
查询员工信息以及部门，要求显示所有部门
Select d.deptno 部门编号,d.dname 部门名称,d.loc 部门位置,e.empno 员工编号,e.ename 姓名,e.job 职位
from emp e right join dept d on e.deptno = d.deptno
SQL1999语法：
交叉连接 产生笛卡尔积效果
select * from emp cross join dept;
自然连接 自动关联条件
select * from emp natural join dept
Join…using…
select * from emp join dept using (deptno);
Join…on…
select * from emp join dept on emp.deptno = dept.deptno;
5.6.统计函数
求和：count(id)
平均：avg(字段)
最大值：max(字段)
最小值：min(字段)
5.7.分组查询
SELECT * FROM 表名称1,表名称2…
[WHERE 条件]
[group by 字段]
[having 条件]
[order by 字段 asc/desc]

某些情况下，是需要对某一列字段进行分组查询的
前提：分组的这一列必须有重复数据

按照部门分组，统计每个部门的部门人数和平均工资
逐步分析：
需要用到的表：
emp e
需要用到的字段：
Count(empno) 部门人数,avg(sal) 平均工资
分组字段：
deptno
组装sql:
Select deptno 部门编号, Count(empno) 部门人数,avg(sal) 平均工资
From emp e
Group by deptno
查询每个职位的最高工资与最低工资
Select job, min(sal),max(sal) from emp group by job;
查询每个部门的编号，名称，位置，部门人数，平均工资
逐步分析：
需要用到的表：
emp e,dept d
需要用到的字段：
d.deptno 部门编号,d.dname 部门名称,d.loc 部门位置,Count(empno) 部门人数,avg(sal) 平均工资
关联条件：
e.deptno = d.deptno
分组字段：
e.deptno
组装sql:
Select d.deptno 部门编号,d.dname 部门名称,d.loc 部门位置,Count(empno) 部门人数,avg(sal) 平均工资
From emp e,dept d
Where e.deptno = d.deptno
Group by d.deptno,d.dname,d.loc

左右连接显示边界数据：
Select d.deptno 部门编号,d.dname 部门名称,d.loc 部门位置,Count(empno) 部门人数,avg(sal) 平均工资
From emp e RIGHT join dept d
on e.deptno = d.deptno
Group by d.deptno,d.dname,d.loc
处理null值
Select d.deptno 部门编号,d.dname 部门名称,d.loc 部门位置,Count(empno) 部门人数,IFNULL(avg(sal),0) 平均工资
From emp e RIGHT join dept d
on e.deptno = d.deptno
Group by d.deptno,d.dname,d.loc

总结：
1.统计函数可以单独使用，但是不允许出现其它字段
2.统计函数可以和分组一块使用，但是分组之后只允许出现统计函数和分组字段
3.统计函数可以嵌套，但是嵌套之后不允许出现任何字段
对分组之后的数据进行再过滤的时候，那么没有办法直接使用where
Where后面不能使用分组函数
Select d.deptno 部门编号,d.dname 部门名称,d.loc 部门位置,Count(empno) 部门人数,avg(sal) 平均工资
From emp e,dept d
Where e.deptno = d.deptno
Group by d.deptno,d.dname,d.loc
having avg(sal) > 2000
Where和having的区别：

5.8.单行函数
对每一条记录输入值进行计算，得到相应的计算结果，返回给用户，也就是说，每条记录作为一个输入参数，经过函数计算得到每条记录的计算结果。

每一个函数中都有一些常用的函数（方法）
在学习函数的时候，Mysql数据库为咱们提供了一张虚拟表 dual
1、字符函数：对字符串操作 （相当于JAVA中的String）
① Upper(字符串)—将字符串转换为大写
例子：select upper(‘zhangsan’) from dual;
LOWER(字符串)—将字符串转换为小写
例子：select ename,lower(ename) from emp;

②LENGTH(字符串)-----获取字符串的长度
例子：select length(‘abcde’) from dual;

③REPALCE(字符串,旧的字符串，新的字符串) 替换
例子：找到emp表中的7369人员的名字，进行替换
select ename,REPLACE(ename,‘TH’,‘AA’)
from emp
where empno=7369;

④SUBSTR(字符串,开始位置,长度) 截取
例子：
select ename,substr(ename,2,2)
from emp
where empno=7369;
例子：截取后三位
select ename,substr(ename,-3)
from emp
where empno=7369;

⑤LPAD(字符串，字符串长度len，添加的字符串strs) 动态拼接字符串，从左边
例子：select LPAD(‘he’,10,’’) from dual;
RPAD(字符串，字符串长度len，添加的字符串strs) 动态拼接字符串，从右边
例子：select RPAD(‘he’,10,’’) from dual;

⑥trim 消除空格
LTRIM(字符串)消除前面的空格
例子：select LTRIM(’ HELLO’) from dual;
RTRIM(字符串)消除后面的空格
例子：select RTRIM(’ HELLO ') from dual;

2、数值函数：对数字进行操作
①四舍五入 ROUND(数字,[保留的小数位数])
例子：select ROUND(129.56) from dual;
select ROUND(129.56,1) from dual;
②求余数 MOD(数字1，数字2)
例子：select MOD(11,3) FROM DUAL;
③绝对值 ABS(数字)
例子：select ABS(-11) from dual;
④返回不大于数字的最大整数 FLOOR(数字)
例子：select floor(5.5) from dual;
select floor(-5.5) from dual;
⑤返回大于数字的最小整数 ceiling(数字) --正负数都可以
例子：select ceiling(5.5) from dual;
select ceiling(-5.5) from dual;

3、日期函数：操作数据库中的日期
①获取当前时间
now()
sysdate()
–区别：now获取最开始函数的值
–select now(),sleep(3),now() from dual;
sysdate获取函数执行时的值
–select sysdate(),sleep(3),sysdate() from dual;
②获取日期 curdate()
例子：select curdate() from dual;
③获取时间 curtime()
例子：select curtime() from dual;
④获取星期 dayofweek(日期) 返回索引
例子：select dayofweek(‘2018-08-30’) from dual;
日期的工作日索引 1=星期日，2-星期一…7=星期六
⑤获取一月中的第几天 dayofmonth(日期)
例子：select DAYOFMONTH(‘2018-08-23’) from dual;
⑥获取一年中的第几天 dayofyear(日期)
例子：select dayofyear(‘2018-08-30’) from dual;
⑦获取日期中的月份 month(日期)
例子：select month(‘2018-08-30’) from dual;
⑧获取季度 quarter(日期)
例子：select quarter(‘2018-08-30’) from dual;

4、其它函数
①获取当前数据库 database()
例子：select database() from dual;
②获取当前数据库用户 system_user()
例子：select system_user() from dual;
③获取数据库版本 version()
例子：select version() from dual;
④加密 md5(内容)
例子：select md5(‘123456’) from dual;
⑤格式化 format(内容,格式化)
例子：select format(123456789,3) from dual;

5.9.子查询
在一个查询内部嵌套另外一个查询
Where 子查询
From子查询 多行多列
5.查询比smith工资高的员工信息
select * from emp where sal > (select sal from emp where ename = ‘SMITH’);
6.查询工资高于平均工资的员工信息
select * from emp where sal > (select avg(sal) from emp);
7.查询和经理的工资相同的所有员工信息
select * from emp where sal in (select sal from emp where job = “MANAGER”);

=any 等价于 in
<any 比最大值小

any 比最小值大

all 比最大值大
<all 比最小值小
Select *from emp where sal>(select sal from emp where name=’zhansna”=’);
Select *from emp where sal>(select avg(sal)from emp)
Select *from emp where sal=any(select sal from emp where job=‘manger’ )
Select *from emp where sal in (select sal from emp where job=‘manger’)
String sql=”delect *from emp where sal>(select sal from epm where cname=’+name’);”

8.查询每个部门的编号，名称，位置，部门人数，平均工资
1.先查询部门编号，名称，位置
Select d.deptno,d.dname,d.loc from dept d
2.再查询部门人数，平均工资
Select e.deptno,count(e.empno),avg(e.sal) from emp e group by e.deptno
3.整合以上两张表
Select d.deptno,d.dname,d.loc,ee.con,ee.sal from dept d,(Select e.deptno num,count(e.empno) con,avg(e.sal) sal from emp e group by e.deptno) ee where d.deptno = ee.num
4.左右连接显示边界值
Select d.deptno,d.dname,d.loc,ee.con,ee.sal from dept d left join (Select e.deptno num,count(e.empno) con,avg(e.sal) sal from emp e group by e.deptno) ee on d.deptno = ee.num
5.处理null
Select d.deptno,d.dname,d.loc,ifnull(ee.con,0),ifnull(ee.sal,0) from dept d left join (Select e.deptno num,count(e.empno) con,avg(e.sal) sal from emp e group by e.deptno) ee on d.deptno = ee.num
6.分页
select * from emp limit n,m;
n:第n条记录，从0开始
m:每页显示多少条

7.事务处理
事务的自动提交和手动提交

A-------->B 100
try{
判断A是否有100
A-100
//系统异常 停电…
B+100
事务提交
}catch(){
A+100
事务回滚
}
如果要自己控制事务，那么需要设置手动提交事务的方式
set autocommit = 0; 设置手动提交
set autocommit = 1; 设置自动提交
commit 提交
rollback 回滚

8.触发器

9.视图
如果对一个复杂的查询频繁使用，可以将这个查询封装为一个视图
create view aaa as (Select e.deptno,count(e.empno) number,avg(e.sal) avgsal from emp e group by e.deptno)
10.JDBC
Java DataBase Connection(Java数据库连接)