文章目录
一、概述
-
数据库(Database,DB):保存在计算机存储设备上,按照一定规则组织起来,可以被用户和应用程序共享的数据的集合. 就是数据的仓库。数据库实际上是在硬盘上以文件的形式存在,即数据库类似于文件夹,而其内的文件,如表文件.frm即为有关系的数据表。
-
数据库管理系统(Database Management System, DBMS)是用来管理数据库的的软件,可以建立、使用和维护数据库,对数据库管理与控制以保证数据库的安全性与完整性.常用的数据管理系统有:MySQL,Oracle, DB2 , Sybase, SQL Server…
-
数据库系统(DatabaseSystem, DBS)包括安装DBMS的硬件, DBMS, 数据库和数据库管理员(Database Administrator,DBA)
-
数据管理技术的发展:人工管理阶段->文件系统阶段->数据库系统阶段)(如Java可以通过IO流中的序列化与反序列化进行数据存储,但不如专业的数据库系统好)
-
SQL语言(Structured Query Language,结构化查询语言):对数据库进行查询与操作的语言. SQL不是某个特定数据库专用商的语言,几乎所有的数据库管理系统都支持SQL。SQL语句在执行时,内部上也会进行编译(由DBMS完成),然后再执行。包括四个部分:
- 数据定义语言DDL, 包括CREATE创建表,ALTER修改表,DROP删除表等语句,对表结构的增删改
- 数据操作语言DML, 包括INSERT,UPDATE,DELETE等语句,对表当中的数据进行增删改
- 数据查询语言DQL,包括SELECT语句,对表当中的数据进行查询
- 数据控制语言DCL, GRANT授权,REVOKE撤销权限等
- 事务控制语言TCL,COMMIT提交事务,ROLLBACK回滚事务(T指Transaction)
- 关系:DBMS->(执行)->SQL->(操作)->DB
-
表:table,是数据库的基本组成单元,是一种结构化文件,所有数据都以表格形式组织,目的是可读性强。表具有特定的名称,不可重复,包括行(数据/记录,data)和列(字段,column)
-
字段的属性:字段名、数据类型、字段约束、字段长度
-
MySQL中的标识符可以使用``进行包裹(并非所有数据库通用)
-
数据库访问接口:不同的计算机语言有不同的数据库访问接口,程序通过接口执行SQL语句
- ODBC(Open Database Connectivity)开放数据库互连技术为访问不同的SQL数据库提供了一个共同的接口。ODBC使用SQL作为访问数据库的标准,通过ODBC可以使程序访问不同的数据库
- JDBC( Java Database Connectivity)Java数据库互连用于Java程序连接数据库
-
数据库分类:关系型数据库(SQL,以表为单位进行存储)与非关系型数据库(NoSQL=Not Only SQL,采取聚合结构存储数据,即Key—Value键值对(Redis采用此种)、列簇、图标模型)
-
Mysql:MySQL是瑞典MySQL AB公司开发的一款小型的免费的关系型数据库,于2008年被SUN公司收购, 2009年SUN被Oracle(甲骨文)收购,具有规模小,速度快,成本低等特点。
-
MySQL结构:MySQL分为服务端和客户端
- 服务端:服务端是指装在数据库服务器上的MySQL主服务,数据库的核心,用于数据库的管理,数据的与存储、查询、数据库资源的监控、监听等服务。
- 客户端:是指与服务端交互的工具,要登录使用MySQL数据库服务就需要安装客户端(如命令行,Navicat,Java程序等多种种类型),进而操作数据库服务端进行增删改查
-
Tips:sql语句以";"结尾、sql语句不区分大小写,但数据区分大小写
-
Oracle与MySQL区别:
- Oracle:大型数据库软件,收费,支撑体系完善,强大。单库可支撑数据量大,安全性高。适用于服务器比较强大的单节点或者集群环境。
Mysql:轻量级数据库,小巧,免费,使用方便。流行于单服务器,性能一般。
- Oracle:大型数据库软件,收费,支撑体系完善,强大。单库可支撑数据量大,安全性高。适用于服务器比较强大的单节点或者集群环境。
-
Oracle与MySQL的应用:
- Oracle的应用:主要在传统行业的数据化业务中,比如:银行、金融这样的对可用性、健壮性、安全性、实时性要求极高的业务;零售、物流这样对海量数据存储分析要求很高的业务。此外,高新制造业如芯片厂也基本都离不开Oracle;电商也有很多使用者,如京东(正在投奔Oracle)、阿里巴巴(计划去Oracle化)。
- MySQL的应用:大都集中于互联网方向,因为价格便宜,安装使用简便快捷,深受广大互联网公司的喜爱。
-
数据库可视化工具:Navicat
-
数据库建模工具:PowerDesigner
二、安装与卸载MySQL
-
修改mysql密码方法:Windows下mysql修改root密码的4种方法_Mysql_脚本之家 (jb51.net)
-
卸载Mysql(得看教程):mysql完全卸载教程(图文详细)_Of_the的博客-CSDN博客_卸载mysql
-
以上内容可以在需要的时候根据当前系统情况自行进行百度搜索
三、使用MySQL
-
查看mysql版本:mysql --version、mysql -Ver
-
启动MySQL服务:命令行窗口,net start mysql
-
关闭MySQL服务:命令行窗口,net stop mysql
-
登录MySQL,mysql -hlocalhost -uroot -proot 进入mysql数据库,其中-h表示服务器名(本地可以省略不写),localhost表示本地;-u为数据库用户名,root是mysql默认用户名;-p为密码,如果设置了密码,可直接在-p后直接输入(需配置环境变量)
-
查看数据库mysq命令(非SQL语句):show databases;
-
查询当前使用的数据库命令:select database();
-
登录数据库后查询数据库版本命令:select version();
-
创建数据库命令:create database 数据库名;
-
使用数据库命令:use 数据库名;;
-
查看数据表命令:show tables;
-
查看其他数据库中表的命令:show tables from 数据库名
-
导入SQL文件,执行sql脚本命令:source xxx.sql(文件以sql结尾,其内写了大量的sql文件,称为sql脚本;数据量过大,也可以使用这种方式打开)
-
查看表结构:desc 表名;
- 查看创建表语句:show create table 表名:展示创建表时所使用的SQL语句,也可以查询某表的当下在使用的存储引擎
-
查看SQL语句的执行情况:explain SQL语句;
-
结束一条语句:/c
-
删除数据库:drop database 数据库名;
-
退出数据库:exit;
-
创建表:
drop table if exists 表名;#建表前,一般会先尝试删除数据库中可能原有的同名表 create table 表名( 字段名1 数据类型, 字段名2 数据类型(指定字符数) default 1,#default表示指定字段默认值,不指定默认为null 字段名3 数据类型 约束, ... ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE t_student( no BIGINT, name VARCHAR(255), sex CHAR(1), classno VARCHAR(255), birth char(10) ); MySQL常见数据类型: int 整数型 (java中的int) bigint 长整型(java中的long) float 单精度浮点型 (java中的float) double 双精度浮点型 (java中的double) decimal 字符串形式的浮点数 金融计算的时候,一般使用decimal(java中的BigDecimal) char 定长字符串 (java中的String)效率较varchar更高,字段长度确定使用这种 varchar 可变长字符串 255个字符 (java中的StringBuffer/StringBuilder)字段长度不确定使用这种 text 文本串(大文本)(还是对应java中的String) date 日期类型 年月日(对应java.sql.Date类型) datetime 日期类型 年月日 时分秒 毫秒(最常用) timestamp 时间戳,1970.1.1到现在的毫秒数 time 日期型 时分秒 BLOB 二进制大对象(存储图片、视频等流媒体信息)Binary Large OBject (对应java中的Object)使用IO流插入到数据表中 CLOB(text) 字符大对象(存储较大文本,如可以4GB的字符串)Character Large OBject(对应java中的Object)使用IO流插入到数据表中 过大的图片、音乐、视频资源,存于硬盘中/专用文件服务器,然后将资源路径存储于表中 表名:建议以t_或者tbl_开始 约束(Constraint):在创建表的时候,给表的字段添加相应的约束,从而保证表中数据的完整性、合法性、有效性 非空约束(not null):约束的字段不能为null值 唯一约束(unique):唯一约束修饰的字段具有唯一性,不能重复,但可以为null 主键约束(primary key):约束的字段既不能为null也不能重复(PK) 外键约束(foreign key):...(FK) 检查约束(check):oracle数据库中有check约束,但是mysql没有,目前mysql不支持该约束 非空约束(not null): create table 表名( 字段名1 数据类型, 字段名2 数据类型 not null, 字段名3 数据类型 ... ); 唯一约束(unique): create table 表名( 字段名1 数据类型, 字段名2 数据类型 unique, 字段名3 数据类型 unique,#任何一个字段中有重复值均为重复(列级约束) ... ); create table 表名( 字段名1 数据类型, 字段名2 数据类型, 字段名3 数据类型 ... unique(字段名1,字段名2)# 此方式代表联合唯一性,要同时满足括号中的两个字段相同才是重复(表级约束 ) ); 主键约束(primary key):一张表的主键约束只能有1个 主键字段:带有主键约束的字段称为主键字段 主键值:主键当中插入的值称为主键值 主键作用:表的设计三大范式中要求,第一范式中要求任何一张表都应该有主键,主键值是一行记录的在表中的唯一标识。 主键自增:在主键约束后加上 auto_increment ,在主键列没有进行赋值的情况下也会以自然数的增长模式进行增长(让主键自动生成) Oracle中也提供了自增机制,序列对象(sequence) create table 表名( 字段名1 数据类型, 字段名2 数据类型 primary key auto_increment,#列级约束,主键自增 字段名3 数据类型 ... ); 主键的分类: 根据主键字段和字段数量来划分: 单一主键(推荐的、常用的):直接在定义字段名时,在其后添加primary key 复合主键(多个主键联合起来添加一个主键约束,较少使用,违背三范式):并非所有数据表均存在ID属性,例如学生表(姓名,生日,性别,班级),这里面 每一个值都可能重复,无法使用单一字段作为主键,这时我们可以将多个字段设置为复 合主键,由复合主键标识唯一性。只要不是复合主键每个值都重复,就不算重复。 例: create table 表名( 字段名1 数据类型, 字段名2 数据类型, 字段名3 数据类型 ... primary key (字段名1,字段名2,字段名3...)#表级约束 ); 根据主键性质划分: 自然主键:主键值是一个和业务没有任何关系的自然数(推荐使用) 业务主键:主键值和系统的业务挂钩,例如:拿着银行卡的卡号作为主键(脱开主键,加unique约束),拿着身份证号作为主键。(不推荐使用,主键最好与业务脱 钩,因为业务一旦发生改变,主键值可能也需要跟着改变,进而导致主键值重复) 外键约束(foreign key)(不推荐使用):为了避免将过多信息存储在同一张表上,造成信息冗余,则通过分表的方式进行数据存储。而外键约束用于在两个表之间建立关 系,需要指定引链表用主表的哪一列。 外键字段:添加有外键约束的字段: 外键值:外键字段中的每一个值: 例,有两张表: t_class 班级表 cno(pk) cname -------------------------------------------------------- 101 XX1班 102 XX2班 t_student 学生表 sno(pk) sname classno(该字段添加外键约束fk) ------------------------------------------------------------ 1 zs1 101 2 zs2 101 3 zs3 102 4 zs4 102 5 zs5 102 Tips1:t_student中的classno字段引用t_class表中的cno字段,此时t_student表叫做子表。t_class表叫做父表。 Tips2:子表中被进行外键约束的字段,只能从所引用的父表字段中取值 Tips3:添加外键约束的字段值可以为null Tips4:被引用的字段不一定是主键,但至少具有unique约束 顺序要求:1.删除数据的时候,先删除子表,再删除父表;2.添加数据的时候,先添加父表,在添加子表。3.创建表的时候,先创建父表,再创建子表。4.删除表的时候,先 删除子表,在删除父表。 drop table if exists t_student; drop table if exists t_class; create table t_class( cno int, cname varchar(255), primary key(cno) ); create table t_student( sno int, sname varchar(255), classno int, primary key(sno), foreign key(添加外键约束的字段名) references 父表名(被引用字段)#语法格式,添加外键的子表是数据较多的表,被引用的父表是相对数据较少的表 );
四、DQL查询语句(一)常规查询
- 查询多个字段:select 字段名1,字段名2,字段名3*12 as rename,…from 表名;
- Tips:
- 查询字段可以进行数学运算
- 查询字段也可以进行重命名字段名 as 别名(as可以省略)
- 字符串用单引号括起来
- Tips:
- 查询全部字段:select * from 表名;实际开发少用该方式,查询效率较低
- 条件查询:select 字段1,字段2,字段3…from 表名 where 条件(如a=b,a>10,a<>20即不等于20);(有执行顺序)
- 条件:
- = 等于
- <>或!= 不等于
- < 小于
- > 大于
- >= 大于等于
- between…and… 两个值之间,等同于>=and<=,闭区间左小右大;除了数字,还可以使用在字符串上,如between ‘A’ and ‘C’ 字符方面左闭右开
- is null 为null(is not null不为空)数据库中,null不是一个值,不能用"="衡量,必须使用is null或is not null
- and 并且 (同时满足两个条件)(优先级较or高,会先合并)(当运算符优先级不确定时加小括号)
- or 或者 (满足其中一个条件即可)
- in 包含,相当于多个or(not in不在这个范围中)例:字段名 in (‘字段名1’,‘字段名2’,…)
- not in 不在这几个值当中 例:字段名 not in (‘字段名1’,‘字段名2’,…)
- not 可以取非,主要用在is或in中
- like 称为模糊查询,支持**%或下划线**匹配,%代表任意多个字符,_代表任意1个字符 例:like ‘%a%’,若查询的是下划线,可使用转义字符"\_"
- 条件:
- 排序(升序(默认是升序)、降序):select 字段名1,字段名2 from 表名 order by 字段名;
- 升序asc: select 字段名1,字段名2 from 表名 order by 字段名 asc;
- 降序desc: select 字段名1,字段名2 from 表名 order by 字段名 desc;
- 多重条件排列: select 字段名1,字段名2 from 表名 order by 字段名1 asc/desc ,order by 字段名2 asc/desc ;(当字段1相等时,才使用字段2进行排序)
- 排序也可以是使用列数字进行排列:select 字段名1,字段名2 from 表名 order by 1/2/3/… asc;
- 与where一起使用:字段名2 from 表名 where 条件 order by 字段名 asc/desc;
- 分组函数/聚合函数/多行处理函数:所有的分组函数都是对某一组数据进行操作的,输入多行,最终输出的结果是1行,会自动忽略null。(分组函数不可以直接使用再where字句上,因为group by在where执行之后再执行,分组函数在group by执行后再执行)
- count 计数
- count(*)与count(具体字段)的区别:count(*)代表总记录条数,count(具体字段的区别)代表该字段中不为空(null)的记录条数
- sum 求和
- avg 平均值
- max 最大值
- min 最小值
- 例:select sum(字段名) from 表名;代表求这个字段的总和
- 可以进行组合使用:select sum(字段名), avg(字段名), max(字段名) from 表名;代表求这个字段的总和
- 找出某个字段中大于平均值的数字(使用子查询:select语句中嵌套select语句):
- 例:select 字段名 from 表名 where 字段名>(select avg(字段名) from 表名)
- count 计数
- 单行处理函数:输入一行,处理一行
- 例:select (字段名+数字)*数字 from 表名;
- Tips:数据库中,只要数学表达式中有null,整个式子的结果都为null
- 空值处理函数:ifnull(可能为null的数据,被当做什么处理):例:select 字段名1,ifnull(字段名2,0) from 表名
- group by 和 having (搭配使用)
- group by:按照某个字段或者某些字段进行分组
- 例:select max(字段名1) from 表名 group by 字段名2;表示以字段名2进行分组,然后找出每组中以字段名1的最大值
- Tips1:分组函数一般与group by联合使用,且分组函数是在group by函数执行结束之后才会执行的。若无group by,则以整张表为一组
- Tips2:一条sql语句若有group by,select后只能跟参加分组的字段以及分组函数
- Tips3:多个字段联合分组:select max(字段名1) from 表名 group by 字段名2,字段3; 先以字段名2分,再字段名3分
- having:对分组之后的数据进行再次过滤,针对经过分组函数处理过后的数据进行过滤。(能使用where过滤的先使用)
- 例:select 分组函数名(字段名1),字段名2 from 表名 where 字段名3>2900 group by 字段名 having 条件 order by 字段名 asc/desc;
- group by:按照某个字段或者某些字段进行分组
- 去除指定字段的重复记录:select distinct 字段名 from 表名;
- Tips1:distinct只能出现在所有字段的最前面
- Tips2:select distinct 字段名1,字段名2 from 表名; 代表字段1,字段2联合起来去重
- Tips3:统计去重后的数量:select count(distinct 字段名) from 表名;
五、DQL查询语句(二)连接查询
- 连接查询:实际开发中,大部分情况下,都不是从单表中查询数据,而是多张表联合查询取出最终结果(先连接完,再取出特定的字段);若将数据都存于一张表中会导致数据的冗余。(关联表之间需要设置关联字段,通常子表中设置一个字段对父表的主键Id进行关联)分类如下:
- 以语法出现的年代划分:
- SQL92(比较老的DBA还在使用)
- SQL99(比较新的语法)
- 以表的连接方式划分:
- 内连接:凡是表a和表b能够匹配上的记录查询出来,两张表没有主副之分,两张表是平等的。
- 等值连接
- 非等值连接
- 自连接
- (使用较多)外连接:表a与表b中有一张是主表,一张表是副表,主要查询主表中的数据,捎带查询副表,副表不匹配,则自动模拟null与之进行匹配。(匹配不到也能显示主表的数据)
- 左外连接(左连接):表示左表是主表
- 右外连接(右连接):表示右表示主表
- Tips:左连接有右连接的写法,右连接也有对应的左连接写法
- 全连接
- 内连接:凡是表a和表b能够匹配上的记录查询出来,两张表没有主副之分,两张表是平等的。
- 笛卡尔积现象(笛卡尔乘积现象):若两张表进行连接查询却没有加任何条件进行限制,最终查询记录条数是两表记录数的乘积(每一条记录都会和另一个表的所有记录进行匹配)
- 例:select 别名1.表1字段,别名2.表2字段 from 表1 别名1,表2 别名2; 最终记录条数为表1数据条数 乘 表2数据条数
- 使用加条件过滤进行笛卡尔现象消除,但不会减少匹配次数:
- 例:select a.name,b.name from table1 a,table2 b where a.no=b.no;
- 连接查询时使用表的别名,以及别名.字段的方式进行表示:执行效率高,可读性好
- 以语法出现的年代划分:
- 连接查询->内连接->等值查询:
- select a.name,b.name from table1 a,table2 b where a.no=b.no;//SQL92
- select a.name,b.name from table1 a inner join table2 b on a.no=b.no;//SQL99,常用,因为将过滤where与表连接条件on分离,inner可省略(增加可读性 )
- 连接查询->内连接->非等值查询:
- select a.name,b.name from table1 a inner join table2 b on a.no between b.no1 and b.no2;
- 连接查询->内连接->自连接:一张表看做两张表,自己连接自己(需要表中的项跟其中每一项作对比时使用)
- select a.name,b.name from table1 a inner join table1 b on a.no1=b.no2;
- 连接查询->内连接->外连接:
- 左连接:select a.name,b.name from table1 a left (outer) join table1 b on a.no1=b.no2;
- 右连接:select a.name,b.name from table1 a inner right (outer) join table1 b on a.no1=b.no2;
- 三表连接查询:表1和表2先进行表连接,连接之后的结果表再和表3进行连接:
- 例1:select a.name,b.name,c.name from table1 a join table2 b on a.no = b.no join table3 c on a.name2 between c.name1 and c.name2;
- 例2:select a.name,b.name,c.name from table1 a join table2 b on a.no = b.no left join table3 c on a.name2 between c.n