mysql -uroot -proot
show databases;
create database
zjq
CHARACTER SET ‘utf8mb4’ COLLATE utf8_chinese_ci;
use zjq;
show tables;
drop database zjq;
desc dept;
select * from emp;
select version();
show create table emp;
select ename from emp where ename between ‘A’ and ‘C’; 会查出来A和B开头的不会查出来C开头的
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; 注意:越靠前的字段越能起到主导作用。只有当前面的字段无法完成排序的时候,才会启用后面的字段。
count 计数 sum 求和 avg 平均值 max 最大值 min 最小值 PS:所有的分组函数都是对“某一组”数据进行操作的。 分组函数也能组合起来使用: select count(*),sum(sal),avg(sal),max(sal) from emp;
select ename,ifnull(comm,0) as comm from emp;
具体见这篇文章:group by和having区别
具体见这篇文章:SQL语句执行顺序是什么样的呢
select distinct job from emp; 注意:distinct 只能出现在所有字段的最前面
在表的连接查询方面有一种现象被称为:笛卡尔积现象。 笛卡尔积现象:当两张表进行连接查询的时候,没有任何条件进行限制,最终的查询结果条数是两张表记录条数的乘积。 怎么避免笛卡尔积现象?当然是加条件进行过滤。 思考:避免了笛卡尔积现象,会减少记录的匹配次数吗? 不会。只不过显示的是有效记录。
查询每个员工的部门名称,要求显示员工名和部门名。
SQL92: select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and xxx and xxx; //结构不清晰 SQL99:(常用的) select e.ename,d.dname from emp e inner join //inner可以省略,带着inner目的是可读性好一些。 dept d on 连接条件 where … SQL99语法机构更清晰一些:表的连接条件和后来的where条件分离了。
找出每个员工的工资等级,要求显示员工名、工资、工资等级。
select e.ename,e.sal,s.grade from emp e inner join salgrade s on e.sal between s.losal and s.hisal;
找出每个员工的上级领导,要求显示员工名和对应的领导名
员工的领导编号=领导的员工编号 select a.ename,b.ename from emp a inner join emp b on a.mgr=b.empno;
什么是外连接,和内连接有什么区别? 内连接: 假设A和B表进行连接,使用内连接的话,凡是A表和B表能够匹配上的记录查询出来,这就是内连接。 外连接: 假设A和B表进行连接,使用外连接的话,AB两张表中有一张表是主表,一张表是副表,主要查询主表中的数据,捎带着查询副表,当附表中的数据没有和主表中的数据匹配上,副表自动模拟出NULL与之匹配。
外连接的分类 左外连接(左连接):表示左边的这张表是主表 右外连接(右连接):表示右边的这张表是主表 左连接有右连接的写法,右连接也会有相应的左连接的写法。
(错误–内连接) select a.ename ‘员工’ ,b.bname ‘领导’ from emp a (inner)join //inner 可以省略 emp b on a.mgr=b.empno; (正确–外连接) select a.ename ‘员工’,b.ename ‘领导’ from emp a left (outer) join / /out可以省略 emp b on a.mgr=b.empno;
外连接最重要的特点是:主表的数据无条件的全部查询出来
内连接: select e.,d. from emp e right join dept d on e.deptno=d.deptno where e.empno is null;
A join B join C on表示:A表和B表先进行表连接,连接之后A表继续和C表进行连接。
select e.ename,d.dname,s.grade from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and s.hisal;
select e.ename,d.dname,s.grade from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and s.hisal left join emp e1 on e.mgr=e1.empno;
select语句当中嵌套select语句,被嵌套的select语句是子查询。
select *from emp where sal>(select avg(sal) from emp);
第一步:找出每个部门的平均薪水(按照部门编号分组,求sal的平均值) select deptno ,avg(sal) as avgsal from emp group by deptno; 第二步:将以上的查询结果当做临时表t,让t表和salgrade s 表连接,条件是:t.avgral between s.losal and s.histol; 汇总:
select t.*,s.grade from (select deptno ,avg(sal) as avgral from emp group by deptno ) t join salgrade s on t.avgral between s.losal and s.hisal;
select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno; select e.ename,e.deptno, (select d.dname from dept d where e.deptno=d.deptno; ) as dname from emp e;
第一种:
select ename,job from emp where job=‘MANAGER’ or job=‘SALSMAN’;
第二种:
select ename,job from emp where job in (‘MANAGER’,‘SALSMAN’);
第三种:UNION
select ename,job from emp where job =‘MANAGER’ UNION select ename,job from emp where job =‘SALSMAN’;
两张不相干的表中的数据拼接在一起显示。
select ename from emp union select dname from dept;
注意:查询的列数要相等。
limit是mysql特有的,其他数据库中没有,不通用。(Oracle中有一个相同的机制rownum)
limit startIndex,length
startIndex表示起始位置,从0开始,0表示第一条数据,length表示取几个
select ename ,sal from emp order by sal desc limit 0,5;
select ename ,sal from emp order by sal desc limit 3,6;
– pageNo为当前页,pageSize为每页条数。 limit (pageNo-1)*pageSize,pageSize
语法格式: create table 表名( 字段名1 数据类型 字段名2 数据类型 字段名3 数据类型 … );
char和varchar怎么选择? 在实际的开发中,当某个字段中的数据长度不发生改变的时候,是定长的,例如:性别、生日等都是采用char。 当一个字段的数据长度不确定,例如:简介、姓名等都是采用varchar。
语法格式: insert into 表名(字段名1,字段名2,字段名3…)values(值1,值2,值3)
要求:字段的数量和值的数量相同 ,并且数据类型要对应相同 ,只要能对应上,顺序无所谓。
INSERT INTO
roles
(uid
,rid
) VALUES (534,14),(535,14),(536,14),(537,14),(539,14);
语法: Insert into<新的表名>(列名) select<列名> from<旧的表名>
create table 表名 as select 语句; 将查询结果当做表创建出来。
insert into dept select* from dept;
update 表名 set 字段名1=值1,字段名2=值2 where 条件; 注意:没有条件整张表数据全部更新。
delete from 表名 where 条件; 注意:没有条件全部删除。
具体见这篇文章:SQL中的DQL DML DDL和DCL是怎么区分和定义的
在创建表的时候,可以给表的字段添加相应的约束,添加约束的目的是为了保证表中数据的合法性、有效性、完整性。 常见的约束有哪些呢?
create table t_user( id int , username varchar(255) not null, password varchar(255) );
唯一约束修饰的字段具有唯一性,不能重复。但可以为NULL。
– 案例: create table t_user( id int , username varchar(255) unique ); insert into t_user values(1,‘zhangsan’); insert into t_user values(2,‘zhagnsan’); – 案例:给两个列或者多个列添加unique drop table if exists t_user; create table t_user( id int , username varchar(255), email varchar(255), unique(usercode,username) //多个字段联合起来添加一个约束 [表级约束] ); drop table if exists t_user; create table t_user( id int , username varchar(255) unique, //列级约束 email varchar(255) unique ); 注意:not null 约束只有列级约束,没有表级约束
create table t_user( id int primary key, username varchar(255), email varchar(255) ) ;
根据以上的测试得出:id是主键,因为添加了主键约束,主键约束中的数据不能为NULL,也不能重复。
根据主键字段的字段数量来划分: 单一主键 (推荐的,常用的) 复合主键(多个字段联合起来添加一个主键约束)(复合主键不建议使用,因为复合主键违背三范式) 根据主键性质来划分: 自然主键:主键值最好就是一个和业务没有任何关系的自然数。(这种方式是推荐的) 业务主键:主键值和系统的业务挂钩,例如:拿着银行卡的卡号做主键,拿着身份证号码作为主键(不推荐用)**最好不要拿着和业务挂钩的字段作为主键。**因为以后的业务一但发生改变的时候,主键值可能也需要随着发生变化,但有的时候没有办法变化,因为变化可能会导致主键值重复。一张表的主键约束只能有一个。
使用表级约束方式定义主键:
drop table if exists t_user; create table t_user( id int, username varchar(255), primary key(id) );
mysql 提供主键值 自增:
drop table if exists t_user; create table t_user ( id int primary key auto-increment, //id字段自动维护一个自增的数字,从1开始,以1递增。 username varchar(255) );
提示:oracle当中也提供了一个自增机制,叫做:序列(sequence)对象。
现在一般不建议使用,每次做DELETE 或者UPDATE都必须考虑外键约束,会导致开发的时候很痛苦,测试数据极为不方便。 阿里巴巴开发规范也不建议使用:
【强制】不得使用外键与级联,一切外键概念必须在应用层解决。
CREATE TABLE
dept
(DEPTNO
int(2) NOT NULL,DNAME
varchar(14) DEFAULT NULL,LOC
varchar(13) DEFAULT NULL, PRIMARY KEY (DEPTNO
) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
建表的时候可以指定存储引擎,也可以指定字符集。 MySql默认使用的存储引擎是InnoDB方式。默认采用的字符集是UTF8。
存储引擎这名字只有在mysql中存在。(oracle中有相应的机制,但是不叫做存储引擎。Oracle中没有特殊的名字,就是”表的存储方式”) Mysql支持很多存储引擎,每一个存储引擎都对应了一种不同的存储方式。 每一个存储引擎都有自己的优缺点,需要在合适的时机选择合适的存储引擎。
show engines
Mysql 5.7.27版本支持的存储引擎有9个。
MyISAM存储引擎是mysql最常用的引擎。 它管理的表具有以下特征:
InnoDB存储引擎是mysql的缺省引擎。 它管理的表具有下列主要特征:
使用MEMORY存储引擎的表,其数据存储在内存中,且行的长度固定,这两个特点使得MEMORY存储引擎查询速度最快。 MEMORY存储引擎管理的表具有下列特征:
一个事务是一个完整的业务逻辑单元,不可再分。 比如:银行账户转账,从A账户向B账户转账10000.需要执行两条update语句。
update t_act set balance=balance-10000 where actno=‘act-001’; update t_act set balance=balance+10000 where actno=‘act-002’;
以上两条DML语句必须同时成功,或者同时失败,不允许出现一条成功,一条失败。 要想保证以上的两条DML语句同时成功或者同时失败,那么就需要使用数据库的“事务机制”。 事务可以保证多个操作原子性,要么全成功,要么全失败。对于数据库来说事务保证批量的DML要么全成功,要么全失败。
整个事务中的所有操作,必须作为一个单元全部完成(或全部取消)。
在事务开始之前与结束之后,数据库都保持一致状态。
一个事务不会影响其他事务的运行。
持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响。
为什么?因为它们这三个语句都适合数据库表当中的“数据”相关的。事务的存在是为了保证数据的完整性,安全性。
不需要事务。但实际情况不是这样的,通常一个“事儿(事务【业务】)”需要多条DML语句共同联合完成。
假设一个事儿,需要先执行一条insert,再执行一条update,最后执行一条delete。这个事儿才算完成。 开启事务机制: 执行insert语句–>insert…(这个执行成功之后,把这个执行记录到数据库的操作历史当中,并不会向文件中保存一条数据,不会真正的修改硬盘上的数据。) 执行update语句—>update…(这个执行也是记录一下历史操作,不会真正的修改硬盘上的数据) 执行delete语句—>delete…(这个执行也是记录一下历史操作【记录到缓存】,不会真正的修改硬盘上的数据) 提交事务或者回滚事务(结束)
事务隔离性存在隔离级别,理论上隔离级别包括四个:
对方事务还没有提交,我们当前事务可以读取到对方未提交的数据。读未提交存在脏读(dirty read现象):表示读到了脏的数据。
对方事务提交之后的数据我方可以读取到。这种隔离级别解决了:脏读现象没有了。读已提交存在的问题是:不可重复读。
这种隔离级别解决了:不可重复读问题。
解决了所有问题。效率低。需要事务排队。 Oracle数据库默认的隔离级别是二挡起步:读已提交。(read committed) Mysql 数据库默认的隔离级别是三档起步:可重复读(repeatable read)。
索引就相当于一本书的目录,通过目录可以快速地找到对应的资源。在数据库方面,查询一张表的时候有两种检索方式:
索引为什么可以提高检索效率呢? 其实最根本的原理是缩小了扫描的范围。 索引虽然可以提高检索效率,但是不能随意的添加索引,因为索引也是数据库当中的对象,也需要数据库不断地维护。是有维护成本的。比如,表中的数据也经常被修改这样就不适合添加索引,因为数据一旦修改,索引需要重新排序,进行维护。 添加索引是给某一个字段,或者说某些字段添加索引。
Select ename ,sal from emp where ename=‘smith’;
当ename字段上没有添加索引的时候,以上sql语句会进行全表扫描,扫描enamel字段中的所有的值。当enamel字段上添加索引的时候,以上sql语句会根据索引扫描,快速定位。
创建索引对象:
create index 索引名称 on 表名(字段名);
删除索引对象:
drop index 索引名称 on 表名;
注意:主键和具有unique约束的字段自动会添加索引。
explain select ename,sal from emp where sal=5000;
给薪资sal字段添加索引:
create index emp_sal_index on emp(sal);
explain select ename,sal from emp where sal=5000;
索引底层采用的数据结构是:B+Tree 通过B tree 缩小扫描范围,底层索引进行了排序,分析,索引会携带数据在表中的“物理地址”,最终通过索引检索到数据之后,获取到关联的物理地址,通过物理地址定位表中的数据,效率是最高的。
select ename from emp where ename=‘smith’;
通过索引转换为:
select ename from emp where 物理地址=0x33;
单一索引:给单个字段添加索引 复合索引:给多个字段联合起来添加一个索引 主键索引:主键上会自动添加索引 唯一索引:有unique约束的字段上会自动添加索引
select ename from emp where ename like ‘%A%’;
模糊查询的时候,第一个通配符使用的是%,这个时候索引是失效的。
站在不同的角度去看到数据。(同一张表的数据,通过不同的角度去看待)
create view myview as select empno,ename from emp; drop view myview;
注意:只有DQL语句才能以视图对象的方式创建出来。
select * from myview; create table emp_bak as select * from emp; create view myview1 as select empno,ename,sal from emp_bak; update myview1 set ename=’hehe’,sal=1 where empno=7369;//通过视图修改原表数据。 delete from myview1 where empno=7369;//通过视图删除原表数据。 视图示例: create view myview2 as select empno a,ename b,sal c from emp_bak; select * from myview2; insert into myview2(a,b,c) values(…);
语法: mysqldump -u 用户名 -p 数据库名称>存放位置+导出的文件名称 案例: mysqldump -uroot -proot zjq>D:\zjq.sql
mysqldump -u 用户名 -p密码 数据库名称 表名>存放位置+导出的文件名称 mysqldump -uroot -proot zjq emp>D:\emp.sql
mysqldump -h 127.0.0.1 -uroot -proot test>D:\zjq.sql mysqldump -h ip -u 用户名 -p 数据库名称>存放位置+导出的文件名称
create database zjq; use zjq; source D:\zjq.sql
设计表的依据。按照这个三范式设计的表不会出现数据冗余。
第一范式:任何一张表都应该有主键,并且每一个字段原子性不可再分。 第二范式:建立在第一范式的基础之上,所有非主键字段完全依赖主键,不能产生部分依赖。
多对多?三张表,关系表两个外键。 t_student学生表 sno(pk) sname t_teacher讲师表 Tno(pk) tname t_student_teacher_relation 学生讲师关系表 Id(pk) sno(fk) tno(fk)
第三范式:建立在第二范式的基础之上,所有非主键字段直接依赖主键,不能产生传递依赖。
一对多? 两张表,多的表加外键。 班级t_class cno(pk) cname 学生t_student Sno(pk) sname classno(fk)
提醒:在实际的开发中,以满足客户的需求为主,有的时候会拿冗余换执行速度。
一对一怎么设计? 一对一设计方案一:主键共享 t_user_login 用户登录表 Id(pk) username password t_user_detail 用户详细信息表 Id(pk+fk) realname tel userid(fk+unique) 一对一设计方案二:外键唯一。 t_user_login 用户登录表 Id(pk) username password t_user_detail 用户详细信息表 Id(pk) realname tel userid(fk+unique)