mysql

 

Mysql整型存储字节与最大长度设置
bigint 带符号的范围是-9223372036854775808到9223372036854775807。无符号的范围是0到18446744073709551615。
int普通大小的整数。带符号的范围是-2147483648到2147483647。无符号的范围是0到4294967295。
就是值得范围的问题，如果需要大于int的范围，就用bigint. 整数类型与大整数类型.
 
Mysql  int与tinyint 有什么区别?
TINYINT  1字节  (-128，127)  ；无符号 tinyint（0,255）别忘了勾选。   小整数值
 
Mysql字段长度：int；类型长度20：表示可以存20位数字。Varchar，类型长度20表示可以存20个汉字或者字母。
 
datetime 日期类型（日期+时间）
 
在Navicat里面进行选择当前操作哪个数据库？
对当前哪个数据库进行操作？（对应的使用use关键字） 别忘了切换数据库。
 
导出远程数据库为.sql文件，然后保存到本地。
 
定时任务，其实是在执行存储过程（特定函数）。
MySql中的存储构成。
 
Mysql 中的事件/定时任务
使用Navicat工具编写定时任务。
什么是事件？
一组SQL集，用来执行定时任务，跟触发器很像，都是被动执行的，事件是因为时间到了触发执行，而触发器是因为某件事件（增删改）触发执行；
开启事件
新建事件----->计划，定义----->事件名----->保存

 

有两种事件类型，一个是间隔触发事件，另一个是特定时间触发事件。

 

Date是SQL Server 2008新引进的数据类型。它表示一个日子，不包含时间部分，可以表示的日期范围从公元元年1月1日到9999年12月31日。只需要3个字节的存储空间。

 

如何合并记录，直接查询的时候就进行处理即可。一个标的由两笔投资记录,通过uid分组进行实现.

select * from dr_member where mobilePhone=13024112588
-- 所有记录
select * from dr_product_invest where uid=25307 
-- 查询记录条数
select count(*) from dr_product_invest where uid=25307 
-- 对金额列求和
select sum(amount) as '总投资额' from dr_product_invest as dpi where uid=25307 and Date(dpi.investTime)>Date('2018-05-08') order by uid
-- 时间对比
select * from dr_product_invest AS dpi where Date(dpi.investTime)>Date('2018-05-08') order by uid
-- 如何分组之后求和，按照uid进行分组，分组以后一个用户只保存一条记录
select  * , sum(amount) as '总金额' from dr_product_invest AS dpi where Date(dpi.investTime)>Date('2018-05-08') GROUP BY uid order by uid

-- 用户信息表
select * from dr_member as dm where dm.uid=4
select * from dr_member where mobilePhone=13024112588


-- 回款信息表（查询出所有的回款信息或者某一款产品的）
select *  from dr_product_invest_repayinfo


-- 用户投资表(查询表的定义语言，每个字段的备注信息)
-- isFirst 表示用户是否是首次投资
-- 判断是否相等，使用一个=
select * from dr_product_invest where uid=31
select IF(dpi.isFirst=0,'否','是') as 是否首投 from dr_product_invest as dpi where id=28


-- 产品信息表(查询产品周期，产品总金额)
select * from  dr_product_info where id=50


-- 渠道信息表
select * from dr_channel_info     -- 按照名称进行分组
select IFNULL(dcinfo.name,'嗷呜宝') AS 渠道列表 from  dr_channel_info as dcinfo group by dcinfo.name


-- ★需求描述：(数据申请表) 5.29至6.4号平台所有回款数据用户名单，需求字段为UID,手机号码，回款时间，回款金额，项目周期
-- 渠道来源，是否首投(排除体验金的数据，按照UID进行升序排列)
-- 用户的每一笔投资都有记录（投资id进行关联）
-- sql语句查询不到数据(可能根据查询条件就是没有数据，不是语法的错误)
SELECT
	dm.uid AS UID,
	dm.mobilePhone AS 手机号码,
	dpir.shouldTime AS 回款时间,
	dpir.shouldPrincipal AS 回款金额,
	dpinfo.deadline AS 项目周期,
	IFNULL( dci.NAME, '嗷呜宝' ) AS 渠道来源,
IF
	( dpi.isFirst = 0, '否', '是' ) AS 是否首投 
FROM
	dr_product_invest_repayinfo AS dpir
	LEFT JOIN dr_member AS dm ON dpir.uid = dm.uid
	LEFT JOIN dr_product_info AS dpinfo ON dpir.pid = dpinfo.id
	LEFT JOIN dr_channel_info AS dci ON LOCATE( dci.CODE, dm.toFrom ) = 1
	LEFT JOIN dr_product_invest AS dpi ON dpi.id = dpir.investid 
WHERE
	dm.uid IS NOT NULL 
	AND dpi.amount > 0 
	AND DATE( dpir.shouldTime ) >= DATE( '2018-1-29' ) 
	AND DATE( dpir.shouldTime ) <= DATE( '2018-6-04' ) 
ORDER BY
	dm.uid;
-- sql语句中的if判断条件，判断用户是否首投
-- 日期Date()函数，参数为字符串类型
-- 排除投资体验标的数据
-- 对查询结构进行排序
-- 导出结果：导出当前的结果，包含列的标题
-- 想要查询的结果字段在哪个表格里面，如何进行表关联
-- 判断当前列是否为空IFNULL()函数
-- 左连接查询，当前需求结果在哪个表中的字段比较多，就当做主表。left join...on...
-- 查询结果列之间，用,分隔
-- MySQL使用内置函数来进行模糊搜索(locate()等) 后面的字符串是否包含前面的字符
-- 判断某个字段的值不能为空dm.uid IS NOT NULL，添加新的判断条件
-- 五重连接查询


-- 邀请关系建立：询我邀请了哪些人（需要邀请人id）   
-- 查询某个用户邀请了哪些好友 
select * from dr_member where mobilePhone=13024112588
-- 查询推荐人id为2的人邀请的所有好友
select * from dr_member_recommended where referrerId=31


-- 红包失效（修改红包状态） 
-- 设置红包过期，未使用红包设置成已过期（过期时间 比当前时间早）
-- CURDATE() 函数，返回当前的日期
select * from dr_member_favourable
update dr_member_favourable set status=2 where status=0 and expireDate<= CURDATE()


-- ★ 需求描述：用户账户余额>100元的用户
select * from dr_member
-- 资金信息表
select * from dr_member_funds
select * from dr_channel_info 
select * from dr_product_invest
select * from dr_member_white_list


SELECT
	dm.uid AS UID,
	dm.mobilePhone,
	dmr.balance AS 账户余额,
	IFNULL(dci.NAME, '嗷呜宝' ) AS 渠道来源,
IF
	( dpi.amount > 0, '是', '否' ) AS 是否投资过 
FROM
	dr_member AS dm
	LEFT JOIN dr_member_funds AS dmr ON dmr.uid = dm.uid
	LEFT JOIN dr_channel_info AS dci ON locate(dci.code, dm.toFrom) = 1
	LEFT JOIN dr_product_invest AS dpi ON dpi.uid = dmr.uid 
WHERE
	dmr.balance > 100 
	AND ( dpi.amount > 0 OR dpi.amount IS NOT NULL ) 
	AND dm.uid NOT IN ( SELECT uid FROM dr_member_white_list ) 
GROUP BY
	dm.uid 
ORDER BY
	dmr.balance
-- 联合查询，uid 不在白名单列表中的
-- 先进行数据分组去重，在进行数据排序
-- 逻辑判断条件的使用  and or
-- 别名的使用
-- 工具--- >选项--->编辑器（修改注释颜色，重新启动Navicat）


-- ★ 需求描述：五月活动数据获取
SELECT * FROM dr_product_invest 
SELECT * FROM	dr_product_info 


SELECT
	dm.uid AS UID,
	dm.mobilePhone AS 手机号码,
	Date( dpir.shouldTime ) AS 待回款时间,
	dpi.amount AS 投资金额,
	dpinfo.deadline AS 投资期限,
	IFNULL( dci.NAME, '嗷呜宝' ) AS 投资渠道,
IF
	( dpi.isFirst = 1, '是', '否' ) AS 是否首投 
FROM
	dr_member AS dm
	LEFT JOIN dr_product_invest_repayinfo AS dpir ON dpir.uid = dm.uid
	LEFT JOIN dr_product_invest AS dpi ON dpi.uid = dpir.uid
	LEFT JOIN dr_product_info AS dpinfo ON dpinfo.id = dpir.pid
	LEFT JOIN dr_channel_info AS dci ON locate( dci.CODE, dm.toFrom ) > 0 
WHERE
	dpi.amount > 0 
	AND date( dpir.shouldTime ) >= date( '2018-5-1' ) 
	AND date( dpir.shouldTime ) <= date( '2018-5-14' ) 
ORDER BY
	dpir.shouldTime;
-- 对查询结果，按照投资时间进行排序
-- 查询条件，投资金额和投资时间（时间格式化后可以进行比较）
-- DATE() 函数返回日期或日期/时间表达式的日期部分。
-- LOCATE() 是否包含
select * from dr_member where mobilePhone=13024112588
select uid from dr_member where mobilePhone=13024112588
-- 查询渠道来源
select tofrom from dr_member where mobilePhone=13024112588
-- 渠道信息列表
select * from dr_channel_info  
-- 系统文章表()
select *  from sys_article


-- 1)美化SQL,2)数据库下面的函数和事件,3）数据库中别名的使用，特别常见。
-- 2)工具菜单----->命令列界面----->输入sql命令的
-- 常用的sql语句，在项目映射文件中。
-- Sql中进行when...then的条件判断
-- 常用的sql语句我自己写的或者说我见过的sql关键字
-- 把后台接口的代码,发送到服务器端,姓名截取,电话号码截取,多表连接查询.


-- 用户绑定的银行卡信息
select * from  dr_member_bank
-- 用户的身份证信息(常见用户信息表的数据)
select * from  dr_member_base_info


-- MySql中字符串截取与拼接操作  concat()函数，substring()函数,参数以逗号分隔
-- 隐藏电话号码中间四位的操作
select * from dr_member as dm where uid=31
select concat(substring(dm.mobilePhone,1,3) ,'****',substring(dm.mobilePhone,8)) as 电话号码 from dr_member as dm where uid=31


-- 时间格式化函数(按要求处理时间格式)  datetime类型，两个参数类型
select regdate as 注册时间 from dr_member where uid=31
select DATE_FORMAT(dm.regdate,'%Y-%m-%d') as 注册时间 from dr_member as dm where uid=31


-- 非空判断 is not null(这个字段的值不为空)
select * from dr_product_invest
select * from dr_product_invest where experience is not null and experience>1000


-- 软件更新信息表
select *  from  sys_app_renewal
-- 查询安卓的更新信息
select * from sys_app_renewal WHERE status=1 and containers=2


-- sql语句中的分支判断  case...when...then...end 
SELECT * FROM dr_product_invest as pi where id < 100
SELECT ( CASE WHEN pi.amount >= 50000 
AND pi.isFirst = 1 THEN
55 
WHEN pi.amount >= 30000 
AND pi.isFirst = 1 THEN
54 
WHEN pi.amount >= 3000 
AND pi.isFirst = 1 THEN
51 
END 
	) AS activityId,
	pi.* 
FROM
	dr_product_invest pi;
	
-- 把sql数据库保存到本地
-- 转存sql数据库(结构和数据)，保存为sql文件。 
-- 运行sql文件，导入保存在本地的sql文件(运行sql文件----->选择sql文件即可)。
-- 右键单击表----->转存Sql文件----->数据和结构（仅结构），文件类型为*.sql文件；
-- 项目上线时，将新建的表结构.sql文件进行上传，然后在linux平台运行即可；


-- 保存修改的数据（表中的数据修改以后，记得保存） Ctrl+S。
-- DDL:数据库定义语言，可以查看每个字段的备注,每个字段的含义。
-- 数据库建表时，别忘了写备注(每个字段的含义)。数据表和字段的名字使用下划线命名法。
-- Tab键的使用，创建数据表的时候。
-- 右键单击表名，设计表，用于修改表结构。


SELECT * FROM dr_member_bank where uid=25117
select * from  dr_member where mobilePhone=13024112588
SELECT * FROM dr_member_base_info where uid=25117
-- 四要素认证
SELECT * FROM dr_member_fourelements_log where uid=25117


-- 查看事件(定时任务)是否开启
-- 新建事件，有两种事件类型，一个是间隔触发，另一个是特定事件触发。
-- 如何新建定时任务？ 如何启动定时任务？如何关闭定时任务？
-- INSERT INTO user(name, address,addtime) VALUES('陈如水','上海市',now())
-- 定时任务的计划与定义(这两个选项卡好好设置)

数据仓库
就与我们之前学过的纯文本,properties,XML这些技术一样.用来保存数据.并提供对数据进行增删改查的操作. 我们以后做项目时,项目中的数据都是保存在数据库中的.
和文本存数据的区别是数据库中存放的是格式化、有结构的数据。
 
关系数据库
是建立在关系模型基础上的数据库；现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示(现实世界中各种实体所具备的属性以及实体之间的关系)。
 
标准数据查询语言
SQL就是一种基于关系数据库的语言，这种语言执行对关系数据库中数据的检索和操作。
 
为什么要用数据库, 数据库的特点？
1、实现数据共享.2、减少数据的冗余度.3、数据实现集中控制.4、数据一致性,完整性和可维护性，以确保数据的安全性和可靠性(数据库的事务).5、故障恢复
 
常用数据库
MySQL：MySQL是最受欢迎的开源SQL数据库管理系统，MySQL是一个关系型数据库管理系统。MySQL是开源的。
 
MySql数据库的安装，安装位置有两个.
1、数据库服务位置: MySQL Server;启动运行在内存中的应用程序，由服务帮我们操作数据库。2、数据库数据文件位置: Server data files; 存储数据。(首先选择的)。3、安装完成后，mysql.exe的路径需要配置到环境变量里面。
数据库服务配置注意事项:1、数据库对连接人数有限制。2、记住数据库应用程序的端口号3306、tomcat的端口号是8080。3、选择MySql默认的字符集：默认的编码方式；自定义选择utf8。4、mysql数据库，管理员名称为root。5、是否允许远程计算机以root方式连接到你的数据库？
 
数据库是由两部分构成的：数据库服务和库文件。通过数据库服务操作库文件。
DDL 数据库定义语言Data Definition Language  || create alter drop (操作数据结构的语句)(每个字段的含义)
DCL 数据库控制语言Data Control Language || grant rollback commit.
DML 数据库操纵语言Data Manipulation Language || insert update delete select；对数据库的增删改查。
DQL 数据库查询语言 Data Query Language || select  (输入DML)
 
Mysql中的语法：
1、sql中的注释：--表示注释一行
2、sql不区分大小写吗?  语句不区分大小写(关键字).  数据区分大小写.
3、sql语句的结束.使用";"表示sql语句的结束.  分号
4、数据表命名法: 下划线命名法，字段驼峰命名法。
 
数据文件目录:
1、可以建立多个数据库(通常情况下一定会建立多个数据库)；
2、每一个数据库中可能包含多张数据表；数据以表的形式进行保存。
3、表是存储数据的载体，所有的数据都是存储在表中的。
 
新建连接，点击测试连接，测试连接是否成功。
所有的操作都会转化成sql语句传递给mysql服务执行。语句的显示在“历史记录”面板里面。
刷新数据表。
 
数据库中的数据类型：varchar最好指定长度；整型一般不指定。
知道在什么情况下应该使用什么类型的数据？(这是要靠经验积累的)
 
问题：char和varchar有什么区别?
char定长字符串. varchar表示变长字符串.
同时指定长度为10。当存储abc
char => 'abc       '；varchar => 'abc'   开发中varchar用的最多。char只在表示固定长度的枚举中使用。例如 ：性别（用01,02表示）
 
问题：Mysql数据库中数据类型的长度是什么
长度：可以理解为多少个汉字？如果是int类型，则表示数字的位数。字符串类型：由于varchar是变长存储的，所以实际开发中我们一般都把varchar的宽度设为最长255，反正你没用完它也不会浪费空间。浮点和日期等数据类型对数据的宽度没有要求，一般也不设置，默认是0。
 
问题：时间类型Date与DateTime的区别
1、Date:代表xxxx年xx月xx日，只表示前面的日期
2、 DateTime:代表xxxx年xx月xx日xx时xx分xx秒，精确到时分秒,用于做时间戳。
3、若mysql中指定字段类型是DateTime,则后台TimeStamp timeStamp=new TimeStamp(new Date.getTime)或Date date=new Date();插入数据时，直接new Date()即可；并且mybatis中:mapper.xml里JDBCType=TIMESTAMP。
 
问题: datatime 和timestamp区别?
这两种类型记录的数据是一模一样.区别在于插入的时候,如果插入datatime类型时,没有传值,那么该类型默认值就是null;如果插入timestamp类型时,没有传值,那么该类型默认值就是当前时间;默认值不同。
 
问题：日期和时间类型
date   只记录日期    2015-01-14
time   只记录时间    11:36:25
year   只记录年      2015
datetime  又记录日期又记录时间2015-01-14 11:36:25
timestamp 同上 2015-01-14 11:36:25
 
列的约束（约束主要分三种，规范用户操作数据的）
保证数据的完整性和规范性.
1.非空约束(not null)  指定非空约束的列,在插入记录时 必须包含值.
2.唯一约束(unique)  该列的内容在表中.值是唯一的，不能重复.
3.主键约束(primary key)  当想要把某一列的值,作为该列的唯一标示符时,可以指定主键约束(包含 非空约束和唯一约束). 一个表中只能指定一个主键约束列。主键约束 , 可以理解为 非空+唯一（不能重复）.注意： 并且一张表中只能有一个主键约束.
 
数据库建表
1)主键自增列, tab键的使用。
 
建立数据表：主键列和关联列，如何与其他表进行关联起来？
设计表：修改表结构。
 
新建数据表设置“注释”和“选项”，对表的字符集和排序规则进行设置，引擎以及行格式。注释写数据库表的名称。
新建数据表时，每个字段必须写注释，表明当前字段的含义。
数据类型：如果可以为null时，不输入，默认为空字符串。
 
TINYINT，1字节，有符号(-128，127)，无符号 (0，255)小整数值，数据库建表时可以设置小整数型，不需要负数进行表示。
 
常用的数据类型：
一般的数据表字段都会有createTime，updateTime和预留字段。
如果确切知道一个数据的长度，使用char类型，比如推荐码，必须是电话号码的话，长度为11.