连接:mysql -h 主机地址 -u 用户名 -p / mysql -u root -p root
DB database 数据库
DBMS 数据库管理系统 (Oracle mysql DB2 sybase sqlserver)
DBMS->(执行)->SQL->(操作)->DB
sql 语句分类:
DQL(数据查询语言):select 查询语句
DML(数据库操作语言): inset delect update
DDL(数据定义语言/修改表结构):create drop alter
TCL(事务控制语言):commit 提交事务 rollback 回滚事务
DCL(数据控制语言):grant授权 revoke 撤销权限
source 路径(路径中不能有中文); //初始化表格(去公司用的到)
注意:直接使用source 可以执行SQL脚本 SQL脚本数量太大时,无法打开,请使用source命令完成初始化
\c 终止一条语句
exit退出
优先级加()
show tables from ....;查看别的数据库中的表格
show create table...;查看创建表的语句
DQL
1、简单的查询语句
select * from 表名; <!--查询表名中的所有字段,缺点:效率低,可读性差,尽量不要写在程序中-->
desc 表名;<!--查看表结构-->
select database (); <!--当前使用的数据库-->
select version (); <!--查看数据库版本号-->
select 字段名 from 表名;<!--查询一个字段-->
select 字段名 ,字段名as 字段名 。。。。from 表名;<!--给字段名重命名(as关键字可以省略,或者用单引号或双引号括起来,一般使用单引号)-->
2、条件查询
<>或!= 不等于
between ...and ...两个值之间等同于 >= and <=
select 字段名... from 表名 where 条件 ;
is null 为空(is not null 不为空 )
and 并且 or 或者
and 和or 同时出现 优先级问题 <!--and 比or的优先级高,如果想先执行 or 用括号分割-->
in(包含 )等同于or not in 可以取反
select 字段名 from 表名 where 字段名=or
select 字段名 from 表名 where 字段名 in ( , );<!-- 括号里不是区间而是或的关系 -->
模糊查询 like % 表示任意多个字符 下划线_ 表示任意一个字符
eg select 字段名 from 表名 where 字段名like ‘ %o_ ’;
查询含有下划线的数据的时候,需要使用转义字符 \转义
3、排序
排序(指定升序asc、降序desc)默认升序
select 字段名 from 表名 order by 字段名 asc;升序
select 字段名 from 表名 order by 字段名 desc;降序
select 字段名 from 表名 order by 字段名 asc,字段名 desc;<!--升序起的主导作用大 相同时启动后面-->
查询加排序
select 字段名 from 表名 where 条件 order by 字段名 asc;
3 1 2 4
4、数据处理函数
单行处理函数:一行输入对应一行输出
常见的单行处理函数:
lower() 转换小写
upper () 转换大写
substr() 取子串{substr(被截取的字段,起始下标(从1开始),截取长度)}
concat() 字符串拼接
length(字段名) 取长度
trim() 去空格
str_to_data 将字符串varchar类型转换为日期data类型
date_format 将日期data类型转换为字符串varchar类型 设置千分位
case ... when ...then ...when...then...else...end
round() 四舍五入
rand()生成随机数
ifnull(数据,被当作那个值) 可以将null转换成一个具体值
常见的多行处理函数(分组函数)
多行处理函数:多行输入对应一行输出
count 计数
avg 平均值
sum 求和
max 求最大值
min 求最小值
分组函数使用时的注意事项:
-
分组函数:自动忽略null ,不需要外加过滤条件,不需要提前处理
-
count(*)和count(具体字段)有什么区别? count():不是统计某个字段中数据的个数,而是统计总记录条数。 count ():表示统计某个字段中 不为null 的数据总数量
-
分组函数:不可直接使用在where子句当中
-
所有分组函数可以组合起来一起用
5.分组查询⭐⭐⭐⭐
分组查询:先进行分组,然后再对每一组的数据进行查询
group by: 按照某个字段或者某些字段进行分组,group by后不能有where having : having 是对分组之后的数据进行再次过滤,不能单独使用,必须与group by组合使用
where与 having 优先使用where,where后面不能有分组函数
select .. from..where..group by...having...orderby... 5 1 2 3 4 6排序
为什么分组函数不能在where后面执行?
因为select 在group by 之后执行 而where在group by 之前执行
distinct去除重复记录
-
关键字 distinct
-
distinct 只能出现在所有字段的最前方
-
distinct出现在两个字段之前,表示两个字段联合起来去除重复数据
6.连接查询⭐⭐⭐⭐⭐
笛卡尔积现象:两张表进行连接查询,没有任何条件限制的时候最终查询条数,是两张表条数的乘积。
怎样避免笛卡尔积现象?
连接时加条件,满足这个条件的记录被筛选出来
根据语法出现的年代来划分的话,
包括: SQL92(比较老,几乎不用)
语法:select...fromA , B on where ...
SQL99(比较新的语法) 根据表的连接方式来划分
SQL99语法更清晰,表连接条件和后来的where条件分离了。(更常用)
语法:select...from A (inner ) join B on 连接条件 where ... <!--inner 可以省略-->
内连接:假设A和B进行连接,使用内连接的话,凡是A表和B表能够匹配上的记录查询出来,这就是内连接。AB两张表没有主副之分,两张表是平等的.
-
等值连接
-
非等值连接(连接条件中是非等量关系)
-
自连接 (一个表看成两个表)
外连接:假设A和B进行连接,使用外链接的话AB两张表中有一张表是主表,一张表是附表,主要查询主表中的数据,捎带查询附表,当附表中的数据没有和主表的数据匹配上,附表自动模拟出null与之匹配。
-
左外连接(左连接):左边是主表
语法:select...from A left join B on 连接条件
-
右外连接(右连接):右边是主表
语法:select...from A right join B on 连接条件
-
全连接(这个不讲,很少用!)
外连接的查询结果条数一定是>= 内连接的查询结果条数
三张表,四张表怎么连接?
select ... from a join b on ...(a和b的连接条件) join c on ... (a 和 c 的连接条件) join on ... (a和d的连接条件)
总结:一条SQL中内连接和外连接可以混合,都可以出现
子查询 子查询先执行(select嵌套select语句,被嵌套的select语句称为子查询)
语法:select ....(select) from ....(select) where ......(select)
union(可以将查询结果相加合并)
使用union效率更高(union把乘法变成了加法,减少次数)
union注意事项?
-
union在进行结果集合并的时候,要求两个结果集的列数相同。
-
MySQL可以,oracle语法严格,不可以,报错。
要求结果集合并时列与列的数据类型也一样。
7、limit(分页查询全靠它)
limit是mysql中有特有的
完整用法 :limit startIndex, length
startIndex 表示起始位置,从0开始,0表示第一条数据。
length 表示取几个
缺省用法:limit 5 ;这是取前五
语法:select ... from ... order by limit ....;
分页公式:
第pageNo页 :limit(pageNo -1)*pageSize,pageSize pageSize每页显示多少条记录 pageNo 显示第几页
DQL语句总结
select .. from..where..group by...having...orderby... limit
5 1 2 3 4 6排序 7
char和varchar怎么选择?
varchar (最长255):可变长度的字符串、比较智能节省空间,会根据数据长度自动分配空间,节省空间。 缺点:动态分配空间,速度慢
char(最长255): 定长字符串。优点:速度快 缺点:使用不当造成资源空间浪费
在实际的开发中,当某个字段中的数据长度不发生改变的时候,是定长的,例如:性别、生日等都是采用char。 当一个字段的数据长度不确定,例如:简介、姓名等都是采用varchar。
关于MySQL当中字段的数据类型?以下只说常见的
int 整数型(java中的int)
bigint 长整型(java中的long)
float 浮点型(java中的float double)
char 定长字符串(String) varchar 可变长字符串(StringBuffer/StringBuilder)
date 短日期类型 (包含年月日信息)%Y-%m-%d
datatime 长日期类型 (包含年月日时分秒信息)%Y-%m-%d %h:%i:%s
BLOB ( Binary Large OBject ) 二进制大对象(存储图片、视频等流媒体信息) 在使用时必须使用IO流
CLOB (Character Large OBject) 字符大对象(存储较大文本,比如,可以存储4G的字符串。)(超过255 就用它)
8、表的创建(建表DDL)
表名:建议t_ 或 tbl_开始
-
创建一张表 create table 表名(
列名 列类型,
列名 列类型,
列名 列类型 default 1,(默认值为1) );
-
删除数据表(if exists 是指如果存在则删除,不存在则跳过) drop table [if exists] 表名;
-
插入数据(DML)(一次可以查找多条数据): insert into 表名(字段名1,字段名2.。。。。。)values (值1,值2。。。);
-
insert 插入日期
数字格式化:format(数字,‘ 格式 ’ )
mysql的日期格式:
%Y 年
%m 月
%d 日
%h 时
%i 分
%s 秒
-
修改update(DML)
update 表名 set 字段名1 = 值1,字段名2 = 值2,字段名3 = 值3 ....where 条件;
-
删除数据delect (DML)
delect from 表名 where 条件;
-
快速创建表
create table emp2 as select * from emp;
原理:将一个查询结果当作一张表新建
可以完成表的快速复制
表创建出来,同时表中的数据也存在了
-
快速删除表中数据
用法:delect from 表名;
delect 删除的原理?(DML)
表中的数据被删除了,但数据在硬盘上的真实存储空间没有被释放,缺点是:删除效率比较低。优点是:支持回滚(rollback),后悔了可以再恢复数据。
truncate语句删除数据的原理?(DDL)
这种删除效率比较高,表被一次截断,物理删除。
缺点:不支持回滚,不能恢复
优点:快速
用法:truncate from 表名;
-
表结构的增删改(在实际开发中很少使用) a.修改列类型 alter table 表名 modify 列名 列类型; b.增加列 alter table 表名 ADD 列名 列类型; c.删除列 alter table 表名 drop 列名; d.列改名 alter table 表名 change 旧列名 新列名 列类型; e.更改表名 alter table 表名 rename 新表名; 或 rename table 表名 TO 新表名;
-
修改字段 alter table employees modify age int(3);
-
增加字段 alter table employees ADD column gender CHAR(1);
-
删除字段 alter table employees drop column sex;
-
修改字段名称 alter table 表名 change 旧列名 新列名 旧列类型; alter table 表名 change 旧列名 新列名 新列类型;
-
更改表名 rename table ... to...
9、约束
-
约束有哪些? not null 非空 unique key 唯一键 primary key 主键 ⭐⭐⭐⭐⭐ (id int(4) not null primary key auto_increment 主键自增) foreign key 外键 (foreign key (classno) refernces t _class (cno)) check 检查约束(mysql 不支持,Oracle支持)
-
添加约束
添加非空约束 ALTER TABLE student2 MODIFY name varchar(255) NOT NULL; 添加唯一约束 ALTER table student2 ADD unique key student2_name_uk (name);//student2_name_uk为约束名 添加主键约束⭐⭐⭐⭐⭐ ALTER TABLE student2 ADD PRIMARY KEY student2(id); 添加外键约束(先增加外键字段,在增加约束) ALTER TABLE student2 ADD COLUMN ttid INT; ALTER TABLE student2 ADD CONSTRAINT student2_tid_fk FOREIGN KEY (tid) REFERENCES teacher (ttid)ON DELETE CASCADE ON UPDATE CASCADE;
-
唯一约束总结:name字段虽然被unique约束了,但是可以为Null。
给某一列添加unique , 称为列级约束。
给两个列或者多个列添加unique, 多个字段联合起来添加1个约束unique , 称为表级约束。
not null约束只有列级约束,没有表级约束。
什么时候使用表级约束?
需要给多个字段联合起来添加某一个约束得时候,需要使用表级约束。
在MySQL 中 not null 和unique 可以联合,被他俩约束之后字段变成主键字段。(oracle不是这样)
-
primary key 主键约束⭐⭐⭐⭐⭐
表的设计三范式中有要求,第一范式就要求任何一张表都应该有主键。
主键值是每行记录的唯一标识
一个字段做主键叫单一主键
多个字段联合起来叫复合主键
-
主键的分类?
根据主键字段的字段数量来划分:
单一主键(推荐的,常用的。)
复合主键(多个字段联合起来添加一个主键约束)(复合主键不建议使用,因为复合主键违背三范式。)
根据主键性质来划分:
自然主键:主键值最好就是一个和业务没有任何关系的自然数。(这种方式是推荐的)
业务主键:主键值和系统的业务挂钩,例如:拿着银行卡的卡号做主键,拿着身份证号码作为主键。(不推荐用)
最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候,主键值可能也需要
随着发生变化,但有的时候没有办法变化,因为变化可能会导致主键值重复。
-
外键约束 foreign key ⭐⭐⭐⭐⭐
外键约束: foreign key
外键字段:添加有外键约束的字段
外键值:外键字段中的每一个值。
为了防止出现字段没有任何约束可能导致的数据无效,需要给字段添加外键约束。
-
存储引擎
存储引擎这个名字只有在mysql中存在。(Oracle中有对应的机制,但是不叫做存储引擎。Oracle中没有特殊的名字, 就是“表的存储方式”)
完整的建表语句:
CREATE TABLE ‘t_x ’(
‘id’ int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
注意:在MySQL当中,凡是标识符是可以使用飘号括起来的。最好别用,不通用。
建表的时候可以指定存储引擎,也可以指定字符集。 mysql默认使用的存储引擎是InnoDB方式。 默认采用的字符集是UTF8
查看当前mysql支持的存储引擎?
show engines \G
MySQL支持九大存储引擎,版本不同支持情况不同。
-
常见的存储引擎
MyISAM这种存储引擎不支持事务。
MyISAM是mysql最常用的存储引擎,但是这种引擎不是默认的。 MyISAM采用三个文件组织一张表: xxx.frm(存储格式的文件) xxx.MYD(存储表中数据的文件) xxx.MYI(存储表中索引的文件)
优点:可被压缩,节省存储空间。并且可以转换为只读表,提高检索效率。 缺点:不支持事务。
InnoDB存储引擎(mysql默认的存储引擎,重量级存储引擎,安全性高,效率低)
优点:支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。 表的结构存储在xxx.frm文件中 数据存储在tablespace这样的表空间中(逻辑概念),无法被压缩,无法转换成只读,不能节省空间。
这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制。 InnoDB支持级联删除和级联更新。
MEMORY存储引擎
缺点:不支持事务。数据容易丢失,不安全,关机后数据消失,不需要和硬盘交互。因为所有数据和索引都是存储在内存当中的。
优点:查询速度最快,效率高。 以前叫做HEPA引擎。
10、事务
-
事务(Transaction):一个事务是一个完整的业务逻辑,是一个最小的工作单元,不可再分。(一个事务就是要完成一件事)
-
和事务相关的语句只有:DML语句(insert delete update)。 只要涉及数据的增删改问题就熬考虑安全问题。
事务本质上就是多条DML语句同时成功,或者同时失败。
事务存在是为了保证数据的完整性安全性。
假设所有业务都能用一条DML语句搞定,就不需要事务机制了在事务的。
执行过程中,每一条DML的操作都会记录到”事务性活动的日志文件“中。
在事务的执行过程中,我们可以提交事务,也可以回滚事务。 提交事务?commit 清空事务性活动的日志文件,将数据全部彻底持久化到数据库表中。提交事务标志着,事务的结束。并且是一种全部成功的结束。 回滚事务?rollback 将之前所有的DML操作全部撤销,并且清空事务性活动的日志文件回滚事务标志着,事务的结束。并且是一种全部失败的结束。
Mysql 默认支持自动提交事务,每执行完一条DML语句,则提交一次。回滚只能滚回到上一次的提交点。
关闭MySQL的自动提交机制? 执行 start transaction;
3.事务的特性?⭐⭐⭐⭐ 事务包含四大特征:ACID A:原子性:事务是最小的工作单元,不可再分 C:一致性:事务必须保证多条DML语句同时成功或者同时失败 I: 隔离性:事务A与事务B之间具有隔离 D:持久性:持久性说的是最终数据必须持久化到硬盘当中,事务才算完成
-
关于事务之间的隔离性 事务隔离性存在隔离级别,理论上隔离级别包括4个: 第一级别:读未提交(read uncommitted) 对方事务还没有提交,我们当前事务可以读取到对方未提交的数据。 读未提交存在脏读(DirtyRead)现象:表示读到了脏的数据。 隔离级别最低。 第二级别:读已提交(read committed) 对方事务提交之后的数据我方可以读取到。 这种隔离级别解决了:脏读现象没有了。
oracle 默认读已提交
读已提交存在的问题是:不可重复读。 第三级别:可重复读(repeatable read) 这种隔离级别解决了:不可重复读问题。 这种隔离级别存在的问题是:读取到的数据是幻象
mysql 默认可重复读
第四级别:串行化/序列化读 解决了所有问题 效率低。需要事务排队。
oracle数据库默认的隔离级别是:读已提交。 mysql数据库默认的隔离级别是:可重复读。
11、索引
Mysql在查询方面主要有两种方式?
-
全表扫描
-
根据索引检索
索引之前必须先排序,这个索引的排序和TreeSet数据结构相同。TreeSet底层是一个自平衡的二叉树,在MySQL当中索引是一个B—Tree数据结构。
遵循左小右大的原则存放,采用中序遍历的方式遍历取数据。
mysql当中主键上自动创建索引,unique约束的字段也自动创建索引。
索引的实现原理?
每一行记录在硬盘上都有物理的存储标编号
其实最根本的原理是缩小了扫描的范围。
什么条件下给字段添加索引?
-
数据量庞大
-
该字段总是存在where后面,总是被扫描
-
字段很少有DML的操作。
建议不要随意添加索引,建议通过主键查询或者unique约束查询
索引怎么创建
语法:创建索引 create index 字段别名 on 表名(字段名);
删除索引 drop index 索引字段 on 表名 ;
MySQL 中索引的底层是 B-Tree ; B-Tree 底层: 二叉树
查看sql语句是否用了索引?
explain 查询语句。。。
索引有失效的时候,什么时候索引失效?
失效的第1种情况:
select ename from emp where ename like '%A';
模糊查询的时候,第一个通配符使用的是%,这个时候索引是失效的。
失效的第2种情况: 使用or的时候会失效,如果使用or那么要求or两边的条件字段都要有索引,才会走索引,如果其中一边有一个字段没有索引,那么另一个字段上的索引也会失效。所以这就是为什么不建议使用or的原因。
失效的第3种情况:
使用复合索引的时候,没有使用左侧的列查找,索引失效什么是复合索引? 两个字段,或者更多的字段联合起来添加一个索引,叫做复合索引。
失效的第4种情况:
在where当中索引列参加了运算,索引失效。
失效的第5种情况:
在where当中索引列使用了函数,索引失效。
索引是各种数据库进行优化的重要手段。优化的时候优先考虑的因素就是索引。
索引在数据库当中分了很多类 单一索引: 一个字段上添加素引。 复合索引: 两个字段或者更多的字段上添加索引。 主键索引: 主键上添加素引。 唯一性索引:具有unique约束的字段上添加索引。
注意:唯一性比较弱的字段上添加索引用处不大。
12、视图(view)
视图:站在不同的角度去看到数据。(同一张表的数据,通过不同的角度去看待)。
-
创建视图(注意:只有DQL语句才能以视图对象的方式创建出来。)
create view myview as select empno,ename from emp;
删除视图
drop view 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; // 通过视图删除原表数据
视图的作用? 方便、简化开发、利于维护 视图可以隐藏表的实现细节。保密级别较高的系统,数据库只对外提供相关的视图,java程序员只对视图对象进行CRUD。
13、DBA命令
-
将数据库当中的数据导出 在windows的dos命令窗口中执行:(导出整个库) mysqldump bipowernode>D:\bipowernode.sq1-uroot-p333
在windows的dos命令窗口中执行:(导出指定数据库当中的指定表) mysqldump bjpowernode emp>D:\bjpowernode.sq1-uroot -p123
-
导入数据 create database bipowernode; use bjpowernode; source D:\bjpowernode.sql I
14.数据库设计三范式
第一范式:任何一张表都应该有主键,并且每一个字段原子性不可再分。
主键共享,外键唯一(一对一,外键唯一)。
第二范式:建立在第一范式的基础之上,所有非主键字段完全依赖主键,不能产生部分依赖。
多对多,三张表,关系表两个外键
第三范式:建立在第二范式的基础之上,所有非主键字段直接依赖主键,不能产生传递依赖.
一对多,两张表,多的表加外键
设计表的时候,根据三范式进行,可以避免表中的数据冗余。
提醒: 在实际的开发中,以满足客户的需求为主,有的时候会拿冗余换执行速度。