代码拉取完成,页面将自动刷新
常用mysql命令:
source sql脚本; //执行sql脚本
desc 表名; //查看表结构
create database 数据库名称; //创建数据库
show databases; //查看所有数据库
use 数据库名称; //进入某个数据库
show tables; //查看数据库中所有的表
select database(); //查看正在使用的数据库名称
select version(); //查看使用的mysql的版本
exit; //退出命令
show create table emp; //查看创建表emp的sql语句
sql命令:
查询语句(DQL):
简单查询:
\c //停止语句
select ename,sal*12 as yearsal from emp;//取别名 as可省略 mysql的字符串用''括起来虽然""也可以,但是放在其他数据库中就运行不起来
条件查询:
where 条件;
between and //是闭区间
between and //用在字符中 是左闭右开
is null/is not null //数据库中null不是一个值所以不能用=衡量,只能用is/is not null来衡量
in 等同于 or //select ename from emp where job in('salesman','manager');
模糊查询:like %表示任意多个字符 _表示任意一个字符 mysql \有转义作用
排序:
order by 默认是升序排 asc 升序 desc降序
select ename,sal from emp order by sal desc,ename asc; //,分割排序
order by 1 //按照查询的第一个字段排序
***null 只要mysql语句中关于null的表达式,那么最终结果都是null
单行处理函数:
ifnull(可能为null的数据,被当作什么处理) 空处理函数
分组函数(多行处理函数)自动忽略null:
count 计数
sum 求和
avg 平均值
max 最大值
min 最小值
select ename,sal from emp where sal>avg(sal); //error 1111
以上sql语句错误的原因是:sql语句当中有一个语法规则,分组函数不能直接使用在where子句中。
count(*) 这个语句已经和null没有了关系,是直接记录总记录条数
group by 和 having :(没有group by的时候不能用having)
group by:按照某个字段或者某些字段进行分组 (分组函数通常和group by联合使用 ,这也是为什么叫做分组函数)
having: having是对分组之后的数据进行再次过滤
当一条sql语句有group by的话,那么select只能由参与分组的字段和分组函数,其他的一律没有意义
找出每个部门不同工作岗位的最高薪资:
select deptno,job,max(sal) from emp group by deptno,job order by 1;
语句对比:
select max(sal),deptno from emp where sal>2900 group by deptno; 先划定范围,然后再分组,这样效率高
select max(sal),deptno from emp group by deptno having sal>2900;
查询结果怎么去重:
select distinct job from emp; distinct关键字用来去重
如果distinct后边有多个字段,那么distinct就是对多个字段进行组合去重
连接查询:
内连接:
等值连接:
SQL92:
select e.name,d.dname from emp e,dept d where e.deptno=d.deptno;
SQL99:(SQL99语法结构更清晰一些:表的连接条件和后来的where条件分离了)
select e.name,d.dname from emp e (inner) 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 e1.ename,e2.ename from emp e1 join emp e2 on e1.mgr=e2.empno;
外连接
左外: select e1.ename,e2.ename from emp e1 left (outer) join emp e2 on e1.mgr=e2.empno;
右外: select b.* from emp a right join dept b on a.deptno=b.deptno where a.deptno is null;
内连接和外连接的区别?
内:两张表是平等的。 外:AB两张表有一个是主表,一个是副表,查询主表的数据,捎带查询副表,如果
副表中的数据没有和主表的数据匹配上,副表自动模拟出NULL与之匹配。
全连接(这个不讲,很少用)
三张表怎么连接
Q:找出每一个员工的部门名称以及工资等级 ?
select e.ename,s.grade,d.dname from emp e join salgrade s join dept d on (e.sal between s.losal and s.hisal ) and e.deptno=d.deptno;
Q:找出每一个员工的部门名称以及工资等级以及上级领导?
select e.ename,d.dname,s.grade,e1.ename
-> from emp e
-> join
-> salgrade s
-> on
-> e.sal between s.losal and s.hisal
-> join
-> dept d
-> on
-> e.deptno=d.deptno
-> left join
-> emp e1
-> on
-> e.mgr=e1.empno;
子查询:
select 中嵌套select语句
select
.(select)..
from
.(select)..
where
.(select)..
以上三个地方可嵌套select语句
Q1:找出高于平均薪资的员工信息(where中嵌套子查询)
-> select e.*
-> from
-> emp e
-> where
-> e.sal
->(select avg(sal) from emp);
Q2:找出每个部门平均薪水的薪资等级(from中嵌套子查询)
> select t.avgsal,s.grade
-> from
-> (select deptno,avg(sal) as avgsal from emp group by deptno) t
-> join
-> salgrade s
-> on
-> t.avgsal between s.losal and s.hisal;
Q3:找出每个部门薪资等级的平均值
select t.deptno,avg(s.grade)
from
(select e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal) t
group by
t.deptno;
或者
select e.deptno,avg(s.grade)
from
emp e
join
salgrade s
on e.sal between s.losal and s.hisal
group by
e.deptno
+--------+------------+
| deptno | avg(grade) |
+--------+------------+
| 10 | 3.6667 |
| 20 | 2.8000 |
| 30 | 2.5000 |
+--------+------------+
Q4:找出每个员工所在的部门名称,要求显示员工名和部门名
select e.ename,(select d.dname from dept d where e.deptno=d.deptno) dname from emp e;
4.union(可以将查询结果相加)
(1)select ename,job from emp where job ='manager' or job='salesman';
(2)select ename,job from emp where jon in ('manager','salesman');
(3)select ename,job from emp where job='manager'
union
select ename,job from emp where job='salesman';
5.limit(重点中的重点,其他数据库中没有,相同于Orocal中的rownum)
limit startIndex ,length
Q:找出薪资前五名的员工
select ename,sal from emp order by sal desc limit 5;
select ename,sal from emp order by sal desc limit 0,5;
Q:找出薪资第四到第九名的员工
select ename,sal from emp order by sal desc limit 3,6;
通用的标准分页sql?
每页显示n条数据
第m页:(m-1)*n,n
sql语句执行顺序
select
* 5
from
tablename 1
where
条件 2
group by
... 3
having
... 4
order by
... 6
limit
...; 7
1.创建表
create table 表名(
字段名1 数据类型 约束,
字段名1 数据类型 约束,
...
);
Mysql中常见字段的数据类型?
int 整数型(java中的int)
bigint 长整型(java中的long)
float 浮点型(java中的float)
char 定长字符串(java中的String)对于一些定长的字符串 生日和性别
varchar 可变长字符串 255(java中的StringBuffer/StringBulider)
date 日期类型
BLOB 二进制大对象(存储图片,视频等流媒体信息)
CLOB 字符串大对象(存储较大文本,可以存储4g的字符串)
create table t_student(
no bigint,
name varchar(255),
sex char(1) default 1,
classno varchar(255),
birth char(10)
);
2.插入数据
insert into 表名(字段名1,字段名2,....) values (值1,值2,...)
字段名和值的位置可以改变,但是要确保前后对应,前后也可以只插入一个字段的数据,其他字段自动插入null
插入数据的时候,可以不写字段名,但是要确保后边values的值顺序和类型都要正确
insert into 表明 () values (),() 可以插入多行数据
3.表的复制
create table emp as select *(这里可以选字段) from emp;
create table 表明 as select语句
4.将查询结果插入到一张表中
insert into dept1 select * from dept;
5.修改数据
update 表名 set 字段名1=值1,字段名2=值2 ...where 条件
注意 没有where条件整张表全部更新
6.删除数据
delete from 表名 where 条件;
没有条件全部删除
delete 删除之后表的格式还在 方便回滚
怎么删除大表
truncate table 表名;//表被截断,不可回滚,永久丢失
7.约束(constraint)
作用:为了保证表中数据的合法性、有效性、完整性
常见的约束有哪些:
非空约束(not null)
唯一性约束(unique)唯一但是可以为null
主键约束(primary key) 约束的字段既不能为null 也不能重复(简称为PK)
外键约束(foreign key) 不能为null也不能重复
检查约束(check) 注意:oracle有check约束 但是目前mysql没有
非空约束:
drop table if exists t_user;
create table t_user(
id int,
username varchar(255) not null,
password varchar(255)
);
insert into t_user (id,password) values(1,139923);
唯一性约束:
drop table if exists t_user;
create table t_user(
id int,
username varchar(255), 这里加unique是列级约束
password varchar(255),
unique(username,password) 表级约束
);
主键约束:
主键的作用:第一范式,表示每一行在表中的唯一标识
主键的分类:
按照数量分类:
单一主键:推荐 常用
复合主键:不常用 因为违背了三范式
按照主键性质来分:
自然主键:主键值最好是一个不和业务挂钩的自然数
业务主键: 主键值和业务挂钩,比如将银行卡号或者身份证号作为主键(不推荐用)
drop table if exists t_user;
create table t_user(
id int primary key auto_increment,
username varchar(255)
);
insert into t_user(username) values ('a');
insert into t_user(username) values ('b');
insert into t_user(username) values ('c');
insert into t_user(username) values ('d');
select * from t_user;
外键约束:(外键可以为null)
业务背景 设计数据库表,用来维护学生和班级的信息
子表和父表:
添加外键的表叫做子表,被关联外键的表叫做父表,(创建表)删除数据的时候先删除子表,再删除父表
(删除表)添加数据的时候先添加父表,再添加子表
create table t_class(
cno int,
cname varchar(255),
primary key(cno)
);
create table t_student(
sno int,
sname varchar(255),
classno int,
foreign key(classno) references t_class(cno)
);
设置外键的时候必须是父表的主键吗?
不是必须,但是被作为外键的字段必须有唯一性(至少有unique约束)
存储引擎(Oracle中把存储引擎叫做存储方式):
mysql的默认存储引擎是InnoDB 默认采用的字符集是utf-8
查看当前版本mysql所支持的存储引擎:show engines \G
常见的存储引擎:
MyISAM:不支持事务
它管理的表有以下特征:
格式文件:存储表结构的定义(mytable.frm)
数据文件:存储表行的内容(mytable.MYD)
索引文件:存储表上索引(mytable.MYI)
优点:可被压缩,节省存储空间。并且可以转换为只读表,提高检索效率。
缺点:不支持事务(不安全)
InnoDB:支持事务,行级锁,外键等,有安全保证
数据存储在tablespace这样的表空间内(逻辑概念),无法被压缩,无法转换为只读
在mysql服务器崩溃之后可自动恢复数据
支持级联删除和级联更新(对于外键来说)
MEMORY:不支持事务,数据容易丢失,以为所有数据和索引都存储在内存中
优点:查询速度极快 不能使用CLOB和BLOB
事务(transaction):
比如:银行转账,从A账户向B账户转账,需要执行两个update语句,为了保证两个DML语句同时成功
或者同时成功,那么就需要使用事务机制。
事务机制的一般流程:
开启事务
一系列DML语句,DML执行成功之后,十八执行记录到数据库的操作历史中,并不会改变硬盘上的数据
提交事务或回滚事务(操作历史清空,提交事务就是改变数据库的数据,如果是回滚则仅仅清空操作历史)
TCL(commit;roolback) savepoint
事务的四大特性:ACID
A:原子性 事务是最小的工作单元,不可再分
C:一致性 事务必须保证DML语句同时成功或失败
I:隔离性 事务A和事务B必须具有隔离
隔离级别:
第一级别:读未提交(read uncommitted) 对方事务未提交就可以读取
存在脏读现象
第二级别:读已提交(read committed) 对方事务提交之后可以读取
解决了脏读现象,但是不可重复读
第三级别:可重复读(repeatable read) 提交不提交 都可以读
解决了不可重复读问题,但是读到的数据可能是幻想
第四级别:序列化读/串行化读(serializable)
解决了所有问题,但是效率低,需要事务排队
Oracle 数据库默认的隔离级别是:读已提交
Mysql数据库默认的隔离级别是:可重复读
D:持久性 必须将数据持久化在硬盘文件中,事务才算成功
mysql事务默认情况下是自动提交,只要使用DML语句一次就提交一次,怎么取消自动提交呢 ?
start transaction;
设置事务隔离级别:
set global transaction isolation level read uncommitted;
查看事务隔离级别:
select @@global.tx_ioslation;
3.删除表
drop table if exists 表名;
索引(底层数据结构是B tree)
索引即目录,可以快速找到对应的资源
什么时候需要给字段加索引?(满足什么条件)
1、数据量庞大的时候
2、该字段很少有DML操作
3、该字段经常出现在where子句中
注意:主键和具有unique约束的字段自动会添加索引
根据主键查询效率较高,尽量根据主键检索
看一个语句的执行情况 在要执行的语句前边加 explain即可
如何添加索引?
给薪资sal字段添加索引
create index emp_sal_index on emp(sal);
如何删除索引?
drop index emp_sal_index on emp;
索引的分类:
单一索引:
复合索引:
主键索引:
唯一索引:
...
索引什么时候失效:模糊查询的时候,第一个通配符是%,这个时候索引是失效的。
视图(view):
怎么创建视图?
create view myview as select empno,ename from emp;
创建视图只能通过DQL来创建
怎么删除视图?
drop view myview;
对视图进行增删改查,会影响到原表数据(通过视图影响原表数据的,不是直接操作的原表)
视图的作用:
视图可以隐藏表的实现细节。保密级别较高的系统,数据库支队外提供相关的视图,java程序员只对象进行CRUD。
视图不可以提高检索效率。
DBA命令:
新建用户:
create user username identified by 'password';
授权:
grant all peibileges on dbname.tbname to 'username'@'login ip' identified by 'password' with grant option;
dbname=* 表示所有数据库
tbname=* 表示所有表
login ip=%表示任何ip
password 为空,表示不需要密码即可登录
with grant option 表示改用户还可以授权给其他用户
撤销权限:
revoke privileges on dbname[.tbname] from username;
将数据库当中的数据导出:
在windows的dos命令窗口中执行(指定库):
mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p139923
指定表:
mysqldump bjpowernode emp>~~~~~~
导入数据
create database bjpowernode;
use bjpowernode;
source 文件路径
******数据库设计三范式*******
什么是设计范式?
设计表的依据,按照这三个范式设计的表不会出现数据冗余。
三范式有哪些?
第一范式:任何一张表都要有主键,并且每一个字段原子性不可再分。
第二范式:建立在第一范式的基础上,所有非主键字段完全依赖主键,不能产生部份依赖。(比如:复合主键)
多对多?三张表,关系表两外键。
第三范式:建立在第二范式基础上,所有非主键字段直接依赖主键,不能产生传递依赖。
一对多?两张表,多的表加外键
在实际开发中,以满足客户的需求为主,有的时候会拿冗余来换取执行速度
一对一设计有两种方案:
1、主键共享
t_user_login 用户登陆表
id(pk) username password
----------------------------
1 zs 123
2 ls 456
t_user_detail 用户详细信息表
id(pk+fk) realname tel ...
----------------------------------
1
2
2、外键唯一
t_user_login 用户登陆表
id(pk) username password
----------------------------
1 zs 123
2 ls 456
t_user_detail 用户详细信息表
id(pk) realname tel userid(fk+uniquer)...
----------------------------------
1 1
2 2
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。