数据库
数据库概述
数据库(Database,简称DB)是按照数据结构来组织、存储和管理数据的仓库
数据库可以视为电子化的文件柜
数据库管理系统
数据库管理系统(DBMS:DB management system)是一种操作和管理数据库的大型软件,一般具有存储、截取、安全保障、备份等基础功能。
大部分DBMS提供数据定义语言DDL(DateDefinition language)和数据操作语言DML
(Data manipulation language),供用户定义数据库的模式结构与权限约束,实现对数据的追加、删除等操作。
数据库范式(三大范式)
第一范式:
原子性,字段不可再分
1、每一列属性都是不可再分的属性值,确保每一列的原子性
2、两列的属性相近或相似或一样,尽量合并属性一样的列,确保不产生冗余的数据
第二范式:
唯一性,一张表只能说明一个事件
1、每行数据只能与其中一列相关,即一行数据只能做一件事,只要数据列中出现数据重复,就要把表拆分开来
第三范式:
每列都与主键有直接关系,不存在传递依赖
数据不能存再传递关系,即每个属性都跟主键有直接关系而不是间接关系。像:a→b→c属相之间含有这样的关系是不符合第三范式的
Eg:
1、Student表(学号、姓名、年龄、性别、所在院校、院校地址、院校电话)
2、拆分后的表是:
三大范式只是一般设计数据的基本概念,可以建立冗余较小、结构合理的数据库。如果有特殊情况,当然要特殊对待,数据库设计最重要的是看需求和性能,需求>性能>表结构。多以不能一味的去追求范式建立数据库。
数据模型
1、数据库的数据结构形式,叫数据模型,是对数据库如何组织的一种模式化
2、矩形代表实体,菱形代表一种关系,椭圆代表实体属性
3、实体:指客观存在并可相互区别的物体(实体之间的练习:一对一;一对多;多对多)
4、属性:实体具有的某一种特性
5、练习:也称关系,反映实体内部或实体之间的联系
6、实体性:属性的集合
7、实体数据模型:层次型、网络型、关系型
SQL
SQL(StructuredQuery language)语言,全称是结构化查询语言;数据库管理系统通过SQL语言来管理数据库中的数据。
作用:让你访问和处理数据库
数据库访问技术
JDBC:是一种使用SQL语句的Java API
做什么:
面对数据库增、删、改、查
关系型数据库
RDBMS(关系型数据库管理语言)
关系:可以理解为一种二维表,每个关系都具有一个关系名,就是通常说的表名。
元组:可以理解为二维表中的一行,在数据库中经常被称为记录
属性:可以理解为二维表中的一列,在数据库中经常被称为字段
域:属性的取值范围,也就是数据库中某一列的取值限制。
关键字:一组可以唯一标示的元组的属性,数据库中经常称为主键,有一个或多个列组成,用的最多的是一列
关系模式:指对关系的描述。其格式为:关系名(属性1,属性2),在数据库中称为表结构
关系型数据库的瓶颈:
1、高并发读写需求
2、海量数据的高效率读写
3、高扩展性和可用性
菲关系型数据库
NoSQL:知道是开发的一个没有SQL功能,轻量级的,开元的关系型数据库。但是NoSQL的发展慢慢偏离的初衷
使用场景:
1、数据模型比较简单
2、需要灵活性更强的IT系统
非关系型数据库的分类:
1、(Key-Value)存储数据库:
a) 优势:简单,一部书
b) Redis
c) Memcached
2、列存储数据库
a) 通常是用来应对分布式存储的海量数据
b) Riak
c) Cassandra
d) HBase
3、文档性数据库
4、图形数据库
MySQL
发展阶段:
1、初始开源数据库阶段ABMySQL阶段
2、Sun MySQL阶段
3、Oracle MySQL阶段
优势:
1、开源代码
2、跨平台的
3、功能强大
4、价格优势
MySQL数据引擎
1、ISAM:执行读取操作的速度很快,而且不占用大量的内存和存储资源,不支持事物处理,也不能够容错
2、MyISAM:提供lSAM里没有的索引和字段管理的大量功能,还提供一种表格锁定机制,来优化多个并发的读写操作。强调快速读取操作,大多数虚拟主机提供商和INTERNET平台提供商只允许使用MyiSAM格式。有一个重要缺陷就是不能在表损坏后恢复数据
3、HEAP:允许只驻留的内存里的临时表。比ISAM和MyiSAM都快,但是管理的数据不稳定,如果关机之前没有进行保存,那么所有数据都会丢失
4、InnoDB:支持对实物处理和外来键的支持,比ISAM和MyiSAM引擎慢得多
mysql –uroot –p;
net start mysql56
重置密码:
1. 打开my.ini,右键编辑,添加skip-grant-tables(位置:D:\ProgramData\MySQL\MySQLServer 5.6) ------------用root权限进去,不输入密码就可以进入mysql
例:
# server_type=3
[mysqld]
skip-grant-tables
1. 修改密码(1.关闭服务。2.不输入直接进入。3.show。4.进入mysql。5.修改密码。6.生效。重启)
update user set Password=PASSWORD(‘123456’)where USER=’root’;
2. show databases; 查看数据库
3. use + 数据库名 (例:use information_schema) 进入这里面
4.flush privileges; 修改后生效
5.select user,host,password from user; 查询用户名、地址和密码有哪些表
6.create user ‘username’@’host’ identifiedby ‘123456’; 新建用户
7.show grants; 查看权限
show grants for ‘pig’@’%’;
select * from user;
select * from user\g
结尾可以用 ;号(表格形式)或 \g(每页每页的形式) 结尾
8. select * from user where user=’root’\G 查看
9. .create user test identified by‘123456’; 创建用户(用户连接时,必须指定密码)可以用test进入mysql(创建时没有给地址,地址就会是%,%代表可以远程连接)
10. grant all privileges on *.* to ‘test’@’localhost’; 赋所有权限给test (allprivileges可以换成其他权限,权限间用逗号隔开,localhost是你自己的地址)
11. revoke all privileges on *.* from‘test’@’localhost’; 收回权限
12. mysqladmin –uroot –p password 新密码 (改密码,旧密码可以省略)
删除:
取消一个账户和其权限
drop USER user(仅删除用户)
drop user username@’%’(删除用户以及他的东西)
drop user username@localhost
删除用户:
Delete from user where user = ‘user_name’and host = ‘test_ps’; (test_ps自己用户名)
字符序:
show collation 查看字符序
_ci 对大小写不敏感
_cs 对大小写敏感
_bin 用编码值进行比较
查看:
Show global variables like ‘%chara%’; 查看数据库字符序
修改:
Set global 类别 = 字符集 修改字符序
Set global character_set_server = utf-8
实例:
1.创建一个数据库create database db_test;
2.进入数据库 use db_test
3.创建一张表 create table t(user_id int(10) not null, user_name varchar(20) notnull, study_num int(5) not null, primary key (user_id)) engine=innodb defaultcharset=latin1;
4.修改表的字符集 alter table t convert to character set utf8;
5.查看 show create table t;
6.修改表的字符集 alter
7.添加中文数据 insert into t values(1,‘好’,5);
Select * from t;
乱码解决:
修改过字符集,输入中文乱码依然乱码解决方法:
1.cmd进入mysql 。 2.status命令。 3.set name gbk
导入导出:
导出数据:(必须拥有file权限才能使用此语法)
select * from 数据表 into outfile ‘存放位置’;
select * from t into outfile ‘t.txt’;
导出sql脚本:(在外面,不在sql里,最好cd mysql的bin目录下再使用)
mysqldump –u 用户名–p 数据库名 > 存放位置
mysqldump –u root –p db_test >db_test.sql
导入sql脚本:(在外面,不在sql里,)
mysql –u root –p db_test < db_test.sql
导入数据:(进到数据库中)
load data local infile ‘c:/t.txt’ intotable t;
备份所有数据库:
1、mysqldump -u 用户名 -p --all-databases > 存放位置
2、mysqldump -u root -p --all-databases > all_databases.txt;
show:
show columns from 数据表名(简单写法:desc 数据表) 显示数据表的属性、类型等
show create table 数据表名 显示数据表创建的代码
了解:(show indexfrom 表名 显示数据表的详细索引信息,包括主键
showtable status like 表名 输出sql数据库管理系统性能及统计信息
)
创建一个表:
create table t_person (
idint not null auto_increment comment ‘id’,
last_namevarchar(45) null comment ‘姓’,
first_namevarchar(45) null comment ‘名’,
addressvarchar(45) null comment ‘地址’,
cityvarchar(45) null comment ‘城市’,
primarykey(id)
)
Insert into t_person values(1,’adams’,’DSF’,’SD’,’DTEI’);
查询:
Select last_name from t_person;
Sql分类:
DQL(数据查询语句):create、from、where
DML(数据操作语句):insert、update、delete
DDL(数据定义语句):create、alter、drop、truncate删除表内容
DCL(数据控制语句):grant、rollback回滚(数据状态回到上一次提交状态)、commint、revoke撤销授权
查询:
1.select* from 表名
2.select last, first from t_person 查询表中两个列的信息
3.select distinct 列名称 from 表名 查询表中不同的值(有相同的会忽略)
4.select 列名称 from 表名称 where 列 运算符 值;
5.select * from t_prson where city=’beijing’ 查询北京的人有哪些
6.select * from t_person where first_name=’th’and last_name=’car’; 查询姓为x和名x的人
7. select * from t_person where (first_name=’th’and last_name=’car’) and last_name=’car’;
Order by 排序(默认升序)
升序:asc 降序:desc
8.select company, order_number fromt_orders order by company; 以字母顺序显示公司名称
9. select company, order_number fromt_orders order by company, order_name; 以字母顺序显示公司名称,并以数字顺序显示顺序号(红字,谁在前面就按照谁的顺序优先)
10.select company, order_number fromt_orders order by company desc; 以逆字母顺序显示公司名称
11. select * from t_person wherefirst_name=’th’ and first_name =’car’;
select* from t_person where first_name in (‘ad’, ‘cc’); 与上一句作用相同
插入:
1.insert into t_person values(‘1’, ‘fd’, ‘bub’,‘xuanwumen 10’, ‘beijing’, ‘1990’); 在t_person插入一条数据
2.insert into t_person(year)values (‘1332’); 在某列插一个数据
3.insert into t_person(last_name, address,year) values (‘tom’, ‘ch’, 1954); 在t_person表中姓和地址插入一条数据
修改:
1.update t_person set first_name=’fred’where last_name=’tom’; 为last_name是‘tom’的人添加first_name
2. update t_person set address=’zhongshan’,city=’nanjing’ where last_name=’tom’; 为last_name是‘tom’的人修改地址,添加城市名
删除:
1.delete from t_person where last_name=’tom’; 删除last_name 为tom的数据
2.delete from t_person; 删除所有数据(删除后再添加数据,会接着之前的id,自增)
3.truncate table 表名 快速清空所有资料,且针对自增也会技术归零
Sqlserver Top:
规定要返回的记录的数目(不是所有数据库都支持top)
select top 偏移量列表名 from 表名称
1.select top 10 * from t_test; 查询前1-10条
2.select top 10 * from t_test where id notin (select top 100 * from t_test); 查询的钱10条数据不在100条内,表示从101条开始
Mysql适用的top:
1.Select * from t_person limit 5; 查询5条
2.Select * from t_person limit 2, 5; 查询下标从2开始的到5的数据(3-6)
3.select * from t_persons limit 3; 查询最前面的3条数据
4.select * from t_person limit 1, 2; 查询最前面的2-3条数据
Oracle适用:
Select * from t_person where rownum <=5;
Select * from (select * from t_person whererownum <= 5) where rownum > 2;
Like搜索:
(%:通配符)
Select 列名称 from 表名称 where 列名称 like 表达式
1.select * from t_person where city like ‘n%’; 从表中选取居住在以n开头的城市里的人
2.select * from t_person where city like ‘%g’; 选取居住在以g结尾的城市里的人
3. select * from t_person where city like ‘%g%’; 包含g的城市人
4. select * from t_person where city not like‘%g%’; 不包含g的城市人
5. select * from t_person where city like ‘_gg’; 选取城市名字第一个字符后是gg的人
6. select * from t_person where city like ‘g_g_hh’; 以g开头,然后任意字符,然后g…….
Between:
1. select * from t_person where id between1 and 4; id1-4的数据
select* from t_person where id in(1,2,3,4);
2.select * from t_person where last_namebetween ‘aa’ and ‘cc’; 以字母顺序显示介于aa和cc之间的人
3. select * from t_person where last_name notbetween ‘aa’ and ‘cc’; 范围之外的人
Exists:
检查子查询是否至少会返回一行数据,不反回任何数据,只有true,false
exists subquery
Subquery:是受限的select语句(不允许有compute子句和into关键字)
Alias(别名):
为列名称和表名称指定别名
表语法:
Select 列名称 from 表名称 as 别名;
列语法:
Select 列名称as 别名 from 表名称;
1.Select p.last_name, o.company fromt_person as p, t_orders as o where p.last_name=’adams’ or p.first_name=’carter’;
使用列名称别名:
表t_person中列last_name和first_name ,分别指定family和name
2.select last_name as family, first_name asname from t_person;
Join:
根据2个或多个表中的列间的关系,从这些表中查询数据
Inner join(内连接)
(inner join和join是相同的)
表中至少存在一个匹配时,返回行
连接分为:
inner join(join):如果表中有至少一个匹配,则返回行
left join:即使右表中没有匹配,也从左表返回所有的行
right join:即使左表中没有匹配,也从右表返回所有的行
full join:只要其中一个表中存在匹配,就返回行
inner join:
列出所有人的定购:
select t_persons.last_name,t_persons.first_name, t_orders.order_number from t_persons inner join t_orders on t_persons.id=t_orders.id order byt_persons.last_name;
INNER JOIN 关键字在表中存在至少一个匹配时返回行。如果 "Persons" 中的行在 "Orders" 中没有匹配,就不会列出这些行
Left join(左连接):
Left join 关键字会从左表 (表1) 那里返回所有的行,即使在右表 (表2) 中没有匹配的行
语法:
SELECT 列名 FROM 表1 LEFT JOIN 表2 ON 表1.列名=表2.列名
注意:⚠️在某些数据库中, LEFT JOIN 称为 LEFT OUTER JOIN
实例:
列出所有的人,以及他们的定购- 如果有的话
select t_persons.last_name,t_persons.first_name, t_orders.order_number from t_persons left join t_orders on t_persons.id=t_orders.id order byt_persons.last_name;
left join 关键字会从左表 (t_persons) 那里返回所有的行,即使在右表 (t_orders) 中没有匹配的行
Right join(右连接):
语法:
SELECT 列名 FROM 表1 RIGHT JOIN 表2 ON 表1.列名=表2.列名
注意:⚠️在某些数据库中, RIGHT JOIN 称为 RIGHT OUTER JOIN
实例:
列出所有的人,以及他们的定购- 如果有的话
SELECT t_persons.last_name,t_persons.first_name, t_orders.order_number FROM t_persons RIGHT JOIN t_orders ON t_persons.id=t_orders.id ORDERBY t_persons.last_name;
RIGHT JOIN 关键字会从右表 (t_orders) 那里返回所有的行,即使在左表 (t_persons) 中没有匹配的行
full join(全连接):
只要其中某个表存在匹配,FULLJOIN 关键字就会返回行。
语法:
SELECT 列名 FROM 表1 FULL JOIN 表2 ON 表1.列名=表2.列名
注意:⚠️在某些数据库中, FULL JOIN 称为 FULL OUTER JOIN
MySQL 不支持全连接
实例:
列出所有的人,以及他们的定购- 如果有的话
SELECT t_persons.last_name,t_persons.first_name, t_orders.order_number FROM t_persons FULL JOIN t_orders ON t_persons.id=t_orders.id ORDERBY t_persons.last_name;
FULL JOIN 关键字会从左表 (t_persons) 和右表 (t_orders) 那里返回所有的行。如果 "t_persons" 中的行在表 "t_orders" 中没有匹配,或者如果 "t_orders" 中的行在表 "t_persons" 中没有匹配,这些行同样会列出
Union:
UNION 操作符用于合并两个或多个 SELECT 语句的结果集
注意:⚠️UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条SELECT 语句中的列的顺序必须相同
UNION 语法:
SELECT 列名 FROM 表1 UNIONSELECT 列名 FROM 表2
(不同的值。如果允许重复的值用 UNION ALL)
Select e_name from t_kk union select e_name from t_jj; (两个表中不同值)
Select e_name from t_kk union all select e_namefrom t_jj; (两个表中列出全部值,包括有重复)