学习笔记

2017年5月25日星期四

Mysql中Case when 的用法

Case具有两种格式，简单case函数和case搜索函数

1、简单case 函数

Case sex

When “1” then “男”

When “2” then “女”

Else “其他” end                

2、Case搜索函数

Case when sex=”1” then “男”

Case when sex=”1” then “女”

Else “其他” end

 

简单case函数的写法相对比较简洁，但是和case搜索函数相比，功能方面会有限制，比如写判断式。

还有一个需要注意的问题是，case函数只返回第一个符合条件的值，剩下的case部分将会被自动忽略

比如说下面sql永远无法得到“第二类”这个结果

Case when col_1 in (“a”,”b”’)  then “第一类”

When col_1 in(“a” then ) “第二类”

Else “其他” end

 

 

空值(Null)与空字符(’’)相同吗?答案是否定的

判断NULL用is null  或者 is not null。 sql语句里可以用ifnull函数来处理
判断空字符串‘’，要用 =''或者 <>''。sql语句里可以用if(col,col,0)处理，即：当col为true时(非null，及非'')显示，否则打印0

 

Mysql中不能写  1<Id<5这种两边都有的情况，可以写成id>1 andid<5

 

 

 

 

 

自定义排序用fidld函数，如下

某列如，安全生产工作年限，有数字，还有数字加年的值  1、1年，2,3年这种，如果要统一把年去掉  可以用replace函数，如下：

update yw_ajj_saqscjdglj_aqscp setajj_csaqgznx=replace(ajj_csaqgznx,"年","")

 

2017年5月26日星期五

Mysql中修改表列名

alter table 表名 CHANGE column 旧列名 新列名 varchar(4)

alter table yw_ajj_7hy_all CHANGE column 行业 hylxvarchar(4)

 

表连接

 left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID). B表记录不足的地方均为NULL.

和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.

说明inner join并不以谁为基础,它只显示符合条件的记录.

语法：FROM table1 LEFT JOIN table2 ON table1.field1 compopr table2.field2 
说明：table1, table2参数用于指定要将记录组合的表的名称。

 

使用 create database 语句可完成对数据库的创建, 创建命令的格式如下:

create database 数据库名 [其他选项];

例如我们需要创建一个名为 samp_db 的数据库, 在命令行下执行以下命令:

create database samp_db character 

两种方式对数据库进行使用的选择:

 

一: 在登录数据库时指定, 命令: mysql -D 所选择的数据库名 -h 主机名 -u 用户名 -p

 

例如登录时选择刚刚创建的数据库: mysql -D samp_db -u root -p

 

二: 在登录后使用 use 语句指定, 命令: use 数据库名;

 

use 语句可以不加分号, 执行 usesamp_db 来选择刚刚创建的数据库, 选择成功后会提示: Database changed

 

1.      使用 show tables; 命令可查看已创建了表的名称; 2. 使用 describe 表名; 命令可查看已创建的表的详细信息。

 

向表中插入数据

insert 语句可以用来将一行或多行数据插到数据库表中,使用的一般形式如下:

insert [into] 表名 [(列名1, 列名2, 列名3, ...)]values (值1, 值2, 值3, ...);

其中 [] 内的内容是可选的, 例如, 要给 samp_db 数据库中的 students 表插入一条记录, 执行语句:

insert into students values(NULL, "王刚","男", 20, "13811371377");

 

查询表中的数据

select 语句常用来根据一定的查询规则到数据库中获取数据, 其基本的用法为:

select 列名称 from 表名称 [查询条件];

 

按特定条件查询:

where 关键词用于指定查询条件, 用法形式为: select列名称 from 表名称 where 条件;

以查询所有性别为女的信息为例, 输入查询语句: select * from students where sex="女";

 

更新表中的数据

update 语句可用来修改表中的数据, 基本的使用形式为:

update 表名称 set 列名称=新值 where 更新条件;

 

删除表中的数据

delete 语句用于删除表中的数据, 基本用法为:

delete from 表名称 where 删除条件;

 

alter table 语句用于创建后对表的修改, 基础用法如下:

添加列

基本形式: alter table 表名 add 列名列数据类型 [after 插入位置];

示例:

在表的最后追加列 address: alter table students add address char(60);

在名为 age 的列后插入列 birthday: alter table students add birthday date after age;

 

修改列

基本形式: alter table 表名 change 列名称列新名称 新数据类型;

示例:

将表 tel 列改名为 telphone: alter table students change tel telphone char(13) default"-";

将 name 列的数据类型改为 char(16): alter table students change name name char(16) not null;

删除列

基本形式: alter table 表名 drop 列名称;

示例:

删除 birthday 列: alter table students drop birthday;

 

重命名表

基本形式: alter table 表名 rename 新表名;

示例:

重命名 students 表为 workmates: alter table students rename workmates;

 

删除整张表

基本形式: drop table 表名;

示例: 删除 workmates 表: drop table workmates;

 

删除整个数据库

基本形式: drop database 数据库名;

示例: 删除 samp_db 数据库: drop database samp_db;

 

Mysql中查询重复的数据，以及部分字段去重和完全去重

1、查找表中多余的重复记录（多个字段）

select * from vitae a

where (a.peopleId,a.seq) in

(select peopleId,seq from vitae group by peopleId,seqhaving count(*)>1)

 

2、删除表中多余的重复记录（多个字段），只留有rowid最小的记录

delete from vitae a

where

(a.peopleId,a.seq) in (select peopleId,seqfrom vitae group by peopleId,seq

having count(*) > 1)

and rowid not in (select min(rowid) fromvitae group

by peopleId,seq having count(*)>1)

 

上面是部分字段去重

而完全相同的去重相对简单些，方法如下

select distinct * into Tmp from tableName

drop table tableName

select * into tableName from Tmp

drop table Tmp

 

2017年5月27日星期六

行转列，列转行

CREATE TABLE StudentScores

(

  UserName         VARCHAR(20),        -- 学生姓名

   Subject          VARCHAR(30),        -- 科目

   Score            FLOAT             -- 成绩

)

 

INSERT INTO StudentScores VALUES

('Nick', '语文', 80),

 ('Nick', '数学', 90),

 ('Nick', '英语', 70),

 ('Nick', '生物', 85),

 ('Kent', '语文', 80),

 ('Kent', '数学', 90),

 ('Kent', '英语', 70),

 ('Kent', '生物', 85)

如果我想知道每位学生的每科成绩，而且每个学生的全部成绩排成一行，这样方便我查看、统计，导出数据

Select username,

max(case whensubject="语文" then score else 0 end) as"语文",

max(case whensubject="数学" then score else 0 end) as"数学",

max(case whensubject="英语" then score else 0 end) as"英语",

max(case whensubject="生物" then score else 0 end) as"生物"

fromstudentscores

group byusername

或者

Select username,

max(case subjectwhen"语文" then score else 0 end) as"语文",

max(case subjectwhen"数学" then score else 0 end) as"数学",

max(case subjectwhen"英语" then score else 0 end) as"英语",

max(case subjectwhen"生物" then score else 0 end) as"生物"

fromstudentscores

group byusername

结果为：

 

另一个例子

CREATE TABLEInpours

(

   ID               INT not null AUTO_INCREMENT,

   UserName          VARCHAR(20) not null,  --游戏玩家

   CreateTime        date not null,      --充值时间   

   PayType           VARCHAR(20) not null,  --充值类型   

   Money            double not null,       --充值金额

   IsSuccess         int not null,           --是否成功 1表示成功， 0表示失败

   PRIMARY KEY(ID)

 

INSERT INTOInpours (UserName,CREATEtime,PayType,Money,IsSuccess)

values

('张三','20100501', "支付宝", 50, 1),

 ('张三', '2010-06-14', '支付宝', 50, 1),

 ('张三', '2010-06-14', '手机短信', 100, 1),

 ('李四', '2010-06-14', '手机短信', 100, 1),

 ('李四', '2010-07-14', '支付宝', 100, 1),

('王五', '2010-07-14', '工商银行卡', 100, 1),

('赵六', '2010-07-14', '建设银行卡', 100, 1)

 

要求按日期、支付方式来统计充值金额信息。这也是一个典型的行转列的例子。我们可以通过下面的脚本来达到目的

Selectcreatetime,

sum(case whenpaytype="支付宝" then money else 0 end )as"支付宝",

sum(case whenpaytype="手机短信" then money else 0 end )as"手机短信",

sum(case whenpaytype="工商银行卡" then money else 0 end )as"工商银行卡",

sum(case whenpaytype="建设银行卡" then money else 0 end )as"建设银行卡"

from inpours

group bycreatetime

结果如下：

2017年5月31日星期三

http://jingyan.baidu.com/article/fb48e8be3db3776e622e14c0.html

R中如何载入包

 

R语言中：清屏 CTRL+L

mean(x, trim = 0.1)
就是先把x的最大的10%的数和最小的10%的数去掉，然后剩下的数算平均。

 

2017年6月5日星期一

茎叶图：

2017年6月7日星期三

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

在使用left jion时，on和where条件的区别如下：

1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

假设有两张表：

 create table tab1 (id int,size varchar(10));

 create table tab2 (size varchar(10),namevarchar(10));

insert into tab1 values(1,10),(2,20),(3,30)

insert into tab2values(10,"AAA"),(20,"BBB"),(20,"CCC")

select * from tab1 left join tab2 on (tab1.size = tab2.size) wheretab2.name="AAA"

 

select * from tab1 left join tab2 on(tab1.size = tab2.size and tab2.name="AAA")

 

其实以上结果的关键原因就是left join,right join,full join的特殊性，不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的特性的并集。 而inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。

 

select * from tab1  join tab2 on (tab1.size = tab2.size) wheretab2.name="AAA"

 

 

select * from tab1 join tab2 on (tab1.size= tab2.size and tab2.name="AAA")

 

Mysql修改列名：

alter table studentscores_1  change columnusername  username_1 varchar(10)

alter table  studentscores_1  change score  score_1 varchar(10)

alter table studentscores_1 change  subject subject_1 varchar(10)

这些都可以

 

 

数据框（data frame）

R语言中，一个矩阵内的数据类型要求都要相同，这对生物类数据不大适用，因为我们的数据经常是既有数字又有字符类标记。R语言提供了另外一种更灵活的数据类型：数据框。可以将几个不同类型但长度相同的向量用data.frame( )函数合并到一个数据框，它的模样就像二维数组。但要注意：合并的几个向量长度必需一致。

 

> name <- c("赵匡胤","钱学森", "孙思邈")

> height <- c(172, 175, 168)

> info <- data.frame(name,height)

> info

   name height

 

1 赵匡胤    172

2 钱学森    175

3 孙思邈    168