数据库

数据库

数据库概述

数据库（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_personsinner 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_personsleft 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_personsRIGHT 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_personsFULL 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;     （两个表中列出全部值，包括有重复）