MySql常用sql语句

2018-02-13 本文已影响0人 101之歌

Hive中的mysql取前一个月的日期

SELECT
    a.xh AS student_number,
    concat( substring( a.DAYS, 1, 11 ), a.STARTTIME ) AS starttime,
    a.S_NAME AS coursename,
    '1' AS STATUS 
FROM
    XJ_WATERLIST_TO_DC a 
WHERE
    a.STATUS = '3' 
    AND substring( a.days, 1, 7 ) BETWEEN from_unixtime( unix_timestamp( ) - 2678400, 'yyyy-MM-dd' ) 
    AND from_unixtime( unix_timestamp( ), 'yyyy-MM-dd' ) 
ORDER BY
    substring( starttime, 1, 10 )

mysql字符串向前匹配12个月

SELECT * FROM student_all_closeclasswarning a
where substring( a.starttime, 1, 10 ) between DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and substring(now(),1,10);

查看mysql的版本

select version();

导出数据库中的某个表的数据

mysqldump -u userName -p  dabaseName tableName > fileName.sql

举例：

mysqldump -u root -p bigdata_web_statis studygrade > /home/studygrade.sql

如果想在一个已经建好的表中添加一列

alter table t1 add column addr varchar(20) not null;

举例：

alter table averager_grade add xf varchar(255) not null;

MySQL将查询结果插入到数据表中

INSERT INTO student(id,xuesheng,yuwen,shuxue,yingyu)
SELECT id,xuesheng,yuwen,shuxue,yingyu FROM stu;

截取前12个月的日期

substring( a.starttime, 1, 10 ) between DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and substring(now(),1,10);

查询一个结果放到另一张表中去

insert into student_closeclasswarning_recent SELECT * FROM student_all_closeclasswarning a

建表时指定索引和编码格式

CREATE TABLE `student_closeclasswarning_recent` (
  `student_number` varchar(250) DEFAULT NULL COMMENT '学号',
  `starttime` varchar(250) DEFAULT NULL COMMENT '旷课时间',
  `coursename` varchar(250) DEFAULT NULL COMMENT '课程名称',
  `STATUS` varchar(20) NOT NULL,
  KEY `index1` (`student_number`) USING BTREE,
  KEY `index2` (`starttime`) USING BTREE,
  KEY `index3` (`coursename`) USING BTREE,
  KEY `status` (`STATUS`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

给建好的表加一个自动增长的主键

alter table `表格名` add column `列名` int not null auto_increment primary key comment '主键' first;

错误的查询结果和错误的用法

SELECT
    a.student_number AS student_number,
    a.student_grade AS student_grade,
    a.student_academy,
    a.student_professional,
    a.student_predictionclass AS student_predictionclass,
    a.student_predictiongrade AS student_predictiongrade,
    a.student_model_correlation_coefficient,
    a.student_accuracy_model_points,
    a.student_rate_return_model_exam,
    a.student_false_alarm_rate_model_fail_exam,
    a.student_model_training_sample,
    b.average_grade AS average_grade,
    e.xf 
FROM
    (student_gradeprediction a,
    averager_grade b)
    RIGHT OUTER JOIN (
SELECT
    c.student_predictionclass,
    c.xf 
FROM
    averager_grade d
    RIGHT OUTER JOIN ( SELECT student_predictionclass, xf FROM studygrade, averager_grade WHERE student_predictionclass = kcmc ) c ON c.student_predictionclass = d.student_predictionclass 
WHERE
    c.xf IS NOT NULL 
GROUP BY
    c.student_predictionclass

) e ON e.student_predictionclass = a.student_predictionclass 
WHERE
    a.student_predictiongrade > 0 
    AND a.student_predictionclass = b.student_predictionclass
    AND a.student_number = '2160400224'