mysql基础

mysql语法基础：

rand()*数字;//获取随机数

floor//只取整数    select floor(2.35) from dual;----2

round（数字，保留位数）;//四舍五入  

select round(2.5) from dual;---3

select round(2.537,1) from dual; ---2.5     保留一位小数

select round(2.537,2) from dual; ---2.54   保留两位小数

SELECT floor(RAND() * (SELECT MAX(id) FROM `table`))

 

有关delete truncate

1.truncate无论大小都非常快，用rollback命令delete可以撤销，而truncate则不会被撤销

2。truncate是ddl语句【被隐式提交】，不能对truncate使用rollback

3.truncate将重新设置所有的索引【成初始大小】，对整个表和索引进行浏览时，经过truncate操作后的表比delete快很多

4.truncate不触发任何delete触发器

视图：

一，什么是视图：

查看数据表中数据的一种方式

提供了存储预定义的查询语句作为数据库中对象，以备以后使用的准备

视图只是一种逻辑对象，不是物理对象，因此不占物理存储空间

视图中被查询的表叫基表

二，视图的优点：

1.集中用户使用的数据

2.简化用户权限管理

3.为向其他应用程序输出而重新组织数据

三，创建视图：

实例1。

create view dbo.vw_cjd(name,cid,result)

as select name ,report.cid,report.result 

from student join report on student.sid = report.sid

实例2.

create view dbo.vw_avg(sepciality,avage)

as select speciality,avg(age)

from student

group by speciality

四。修改删除视图

修改视图

ALTER VIEW vw_cjd

AS   Select name, report.cid, report.result, address 

From student join report  ON  student.sid=report.sid

删除视图：

drop view vw_cjd

==========================================================================================================

业务需求，

更新一张表的某个字段，赋值为一个字符串和另外一个字段的某些字符串的拼接

update xy_works a, 
  (select b.works_id,concat('http://xxxxxxxx/downloadRemote? workPath=',substring_index(b.works_url,'/',-3) ) as downloadPath  from xy_works b)  x
set a.dowloadPath = x.downloadPath
where a.works_id = x.works_id

折腾半天根本原因在于：

1，首先对update认识不足，update后面写的就是多个表【有关联关系的】

好友数量修正脚本：

update xy_user ,(select initiative_user_id, count(*) friendnumber from xy_user_friend where status=1  group by  initiative_user_id) friendCount 
set friend_count = friendCount.friendnumber
where xy_user.user_id = friendCount.initiative_user_id

新的业务需求：把一张表的某个字段，复制到另外一张表的某个字段，

技术点注意：没有value关键字

insert  into users(username,password,created_at ) (select user_id ,'123456','2015-03-16 15:15:15'  from xy_user);

今天清理垃圾数据用到的group by 字句：

说白了就清理数据吧，真实的值写到主表的字段里面：

用到sql语句

update   xy_user a ,
     (select user_id,count(works_id) as worksrealCount from xy_works  group by user_id  ) b
     set a.works_count = b.worksrealCount
where a.user_id = b.user_id and a.works_count!= b.worksrealCount

核心语句在这里：groupby 和count()函数配合使用

还注意有个having的用法

一般这样用：having  count(*) > 1   

今天遇到一个mysql报错：too many connection

解决办法：

show variables like '%max_connections%'----查看目前连接数有多少

set GLOBAL max_connections=500-----重新设置最大连接数【前提是admin用户】

=============union 和union all 和or    和in=================

作用：将两个或更多查询的结果组合为单个结果集，该结果集包含联合查询中的所有查询的全部行

使用前提：所选记录的两个表的字段要一致【所有查询中的列数和列的顺序必须相同。】

union       系统会自动将重复的元素去掉【隐藏着对结果的排序】，因为去重，所以明显效率低，

union all  则只是简单合并，保留重重复元组，效果高多了\

Or             实现union同样的功能

区别：

or用不上索引，而用union all可以用上索引

推荐一篇好文章：union uion all or in效率比较

下面是示例：

如下SQL语句：
SELECT irdno FROM birdird
WHERE irdid='RD' AND (irdserial='833006' OR irdmodel='833020')【or造成全表扫描，用不上索引，所以效率不高】

1.分别搜索irdserial='833006'与irdmodel='833020'(两次扫描)，这时在非聚集索引上进行处理结果存入临时数据库的工作表中

2.排序去重，得到irdserial='833006'与irdmodel='833020'的结果集

3.再在这个结果集上搜索irdno='RD'

SELECT irdno FROM birdird WHERE irdid='RD' AND irdserial='833006'
UNION ALL
SELECT irdno FROM birdird WHERE irdid ='RD' AND irdmodel='833020'
1.分别搜索 irdid='RD' AND irdserial='833006'与irdid ='RD' AND irdmodel='833020'（）
2.合并得到结果

SELECT irdno FROM birdird WHERE irdid='RD' AND irdserial='833006'
UNION
SELECT irdno FROM birdird WHERE irdid ='RD' AND irdmodel='833020'
1.分别搜索 irdid='RD' AND irdserial='833006'与irdid ='RD' AND irdmodel='833020'（）
2.合并得到的结果

3.对结果排序，然后去重

该表建立了聚集索引irdno，并且在irdserial和irdmodel上分别建立了非聚集索引
为何UNION ALL的性能最高，而OR的性能最差了？？？？？【or造成全表扫描，用不上索引，所以效率不高】

========================1.5版本数据库的清洗和新增逻辑脚本==============================

升级新版本步骤：必须严格执行

1.必须先停服务

2.执行下面的脚本

3.部署新的服务

4.重新启动服务器

因为user 表和userproperty表，数据有的表添加了字段，所以视图也需要重新建立

DROP VIEW IF EXISTS `v_xy_user_all`;
CREATE VIEW `v_xy_user_all` AS 
select 
`xiaoyang`.`xy_user`.`password` AS `password`,
`xiaoyang`.`xy_user`.`friend_count` AS `friend_count`,
`xiaoyang`.`xy_user`.`user_id` AS `user_id`,
`xiaoyang`.`xy_user`.`background_url` AS `background_url`,
`xiaoyang`.`xy_user`.`login_name` AS `login_name`,
`xiaoyang`.`xy_user`.`logo_url` AS `logo_url`,
`xiaoyang`.`xy_user`.`nick_name` AS `nick_name`,

`xiaoyang`.`xy_user`.`nick_name_text` AS `nick_name_text`,
`xiaoyang`.`xy_user`.`status` AS `status`,
`xiaoyang`.`xy_user`.`works_count` AS `works_count`,
`xiaoyang`.`xy_user`.`profile` AS `profile`,
`xiaoyang`.`xy_user_property`.`birthday` AS `birthday`,
`xiaoyang`.`xy_user_property`.`city_id` AS `city_id`,
`xiaoyang`.`xy_user_property`.`gendar` AS `gendar`,
`xiaoyang`.`xy_user_property`.`last_imei` AS `last_imei`,
`xiaoyang`.`xy_user_property`.`last_login_datetime` AS `last_login_datetime`,
`xiaoyang`.`xy_user_property`.`last_login_ip` AS `last_login_ip`,
`xiaoyang`.`xy_user_property`.`location` AS `location`,
`xiaoyang`.`xy_user_property`.`login_account_type` AS `login_account_type`,
`xiaoyang`.`xy_user_property`.`mobile` AS `mobile`,
`xiaoyang`.`xy_user_property`.`province_id` AS `province_id`,
`xiaoyang`.`xy_user_property`.`recommend_friend_on` AS `recommend_friend_on`,
`xiaoyang`.`xy_user_property`.`register_datetime` AS `register_datetime`,
`xiaoyang`.`xy_user_property`.`tencent_account` AS `tencent_account`,
`xiaoyang`.`xy_user_property`.`user_property_id` AS `user_property_id`,
`xiaoyang`.`xy_user_property`.`voice_on` AS `voice_on`,
`xiaoyang`.`xy_user_property`.`region` AS `region`,
`xiaoyang`.`xy_user_property`.`tag_id` AS `tag_id`,
`xiaoyang`.`xy_user_property`.`weibo_account` AS `weibo_account`
from (`xiaoyang`.`xy_user` 
left join `xiaoyang`.`xy_user_property` on((`xiaoyang`.`xy_user`.`user_id` = `xiaoyang`.`xy_user_property`.`user_id`)));

====================================================================================

下面是梳理有关评论数量的脚本：

###步骤1.先清洗下当前作品的评论数

update xy_works a ,
(select works_id ,count(works_comment_id) commentCount from xy_works_comment group by works_id) b
 set a.comment_count = b.commentCount 
 where a.works_id = b.works_id

###步骤2。把之前的作品评论先保存起来
 create table temp_works_commentcount as
select works_id,comment_count from xy_works

 #步骤3：计算评论对应的回复数量
create table temp_comment_replycount as
select works_comment_id,count(works_comment_id) replycount 
from xy_works_comment_reply 
group by works_comment_id;
 
 ##步骤4.保存作品，和对应的评论id
 create table temp_works_comment as (
select works_id,works_comment_id from xy_works_comment where works_comment_id in(select distinct(works_comment_id) from xy_works_comment_reply)
)

##步骤5.计算出作品和对应的回复总数
select a.works_id ,sum(b.replycount) replycount from temp_works_comment a,temp_comment_replycount b where a.works_comment_id = b.works_comment_id
group by a.works_id

##步骤6，由于在xy_works表中新增了字段comment_count_all
##先在全表范围内把所有的comment_count_all的值置为之前字段comment_count的值
update xy_works set comment_count_all = comment_count;
select * from xy_works where comment_count_all != comment_count;

##步骤7.把步骤2表中的把有子评论的作品的comment_count_all属性重新赋值
update xy_works  m,
       (select a.works_id,a.comment_count,b.replycount ,a.comment_count+b.replycount commentall
        from 
        (select * from temp_works_commentcount) a,
        (select a.works_id ,sum(b.replycount) replycount from temp_works_comment a,temp_comment_replycount b where a.works_comment_id = b.works_comment_id
                           group by a.works_id) b
         where a.works_id = b.works_id 
         ) n 
 set m.comment_count_all = n.commentall
 where m.works_id = n.works_id
 
 数据清洗完后把，中间表删掉temp_

===================================================================================

下面是梳理现网用户昵称二级制昵称到varchar字段中，用于搜索时不分大小写

调用下面的服务：http://localhost:8080/xiaoyangNew/user/test_cleanNickNameText/0.do

搜索的时候，不区分大小写，sql写法：关键词upper 

============把用户默认company为“独立音乐人”============================

update xy_user_property set company = '独立音乐人'

==========================下面是有关：可能认识的人的逻辑===================================


#####1.新注册用户时，登陆进来就调用接口，让他上传通讯录


2.现网所在用户数据，通过脚本来更新数据，添加是否是小样用户标识      
    update   xy_user_contact a, (select  CONCAT('+86',user.login_name)  as mobile1,property.user_property_id property_id from  xy_user user ,xy_user_property property
                              where user.user_id = property.user_id)b
   set a.xyuserproperty_id = b.property_id
   where a.mobile1 = b.mobile1 and a.xyuserproperty_id is  null;

3.调用新的上传通讯录接口时把2中逻辑，写入程式

这次上线还要把3月28号的修改bug时候改动的数据再改回去

select * from xy_works where  title like '%\'%';

把之前的转译字符去掉，因为新版本的程序中做了处理 ！

以上步骤一个都不能少！！！！！！！！！！

mysql获取系统时间:

select now();

2015-04-09 12:13:37

mysql获取当前的和当天的数据【注意有个mysql函数TO_DAYS()】,思路就是和当前时间做减法

select count(*) 

from xy_user a,xy_user_property b 

where a.user_id = b.user_id and TO_DAYS(NOW())-TO_DAYS(b.register_datetime) =0

mysql获取当前的和前一天的数据【注意有个mysql函数TO_DAYS()】,思路就是和当前时间做减法

select count(*) 

from xy_user a,xy_user_property b 

where a.user_id= b.user_id and   TO_DAYS(NOW())-TO_DAYS(b.register_datetime) = 1

mysql获取当前的和前一个小时的数据【注意有个mysql函数data_sub()】,思路就是和当前时间做减法

works.create_date>date_sub(now(), interval 1 hour)

=============================================================

实现中文和英文排序用convert 函数

select * from xy_user where user_id in(
   select a.passive_user_id
    from xy_user_friend a 
    where a.initiative_user_id = 1821 and a.status=1)
order by convert(nick_name USING gbk) COLLATE  gbk_chinese_ci

这样的顺序是：数字，英文，汉字，都是按这样a-z的顺序排列！

---------------这次2.0。1升级改造，把两个标签库合并为一个，

用下面的脚本，把用户之前打过的标签，同步过来----------------------------------

UPDATE xy_works set xy_tag_ids = tag_id

replace函数的使用

修改表中某个字段--把双空格，变为单空格

UPDATE `xy_user_property` 

SET `last_login_ip_address` = replace (`last_login_ip_address`,'  ',' ') WHERE `last_login_ip_address` LIKE '%  %'  

2.0.1版本上线工作

1，执行脚本，把所有用户的位置信息都加上

http://localhost:8080/xiaoyangNew/user/setRealAddress.do

2.数据库表结构更新

3.标签库这里的改动

步骤1：上线后把，首先把用户之前的标签都清掉。

脚本:update xy_user_property set tag_id = null

步骤2：修改接口

public List<UserTagVO> getAllUserTags() throws Exception{

public List<WorkTag> getAllWorkTags()

上线后把这个接口的调用改为和getAllUserTags()_v2_0_1一样的逻辑

目的是保证现在网用户也用新的标签库，打标签

今天遇到下面个这个异常：

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'O' union  

select user.* from xy_user_property user where mobile like '%RE'O%' or' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

解决办法：在程序里对查询条件进行处理，如果把当引号，都替换为两个单引号，这样就ok了，下面是脚本测试结果：

同样的道理，所以如果sql语句中查询条件是用双引号，则如果查询条件中包含双引号也报错

解决办法就是把一个双引号换成两个双引号

总结：查询条件中的转义字符，应该是根据你所写的sql来判断java中如何转换：

如果是sql条件用“"引起来，sql.replaceAll(""","""")用两个双引号代替一个

如果是sql条件用’‘引起来，sql.replaceAll("‘","’‘")用两个单引号代替一个

=====================mysql随机获取几条记录================================

【主要用到一个特殊的order by】 order by rand();

同事添加条数限制 limit xxx

示例：

select * from xy_user order by rand() limit 8;

这样的写法效率低下

下面改进用join方法 连接一张自动生成id的表

但是遇到的问题是：查询的结果都是连续的

解决办法：

1.一次只随机一条

2.程序循环调用去取多条

===================================查询某字段中是否包含有某些字符============================

用到sql的instr函数instr(字段，‘包含的字符串’)>1则证明存在

select xy_works.*

from xy_works  

where instr(xy_tag_ids,'13')>1 

order by recommend desc,create_date desc

有关去重的专辑：http://www.jb51.net/article/23964.htm

mysql给表添加联合唯一约束
ALTER TABLE jw_resource  
ADD UNIQUE  KEY(resource_name, resource_type);  

查询使用

case 

when **** then **

when **** then **

end as xxx

SELECT cp.question,
CASE 
WHEN uq.answer=1 THEN '能'
WHEN uq.answer=2 THEN '不能'
WHEN uq.answer=3 THEN '不清楚'
END  AS answer
  from cp_user_question uq 
LEFT JOIN cp_question cp on uq.question_id = cp.id 
where uq.user_id = 669169