mysql常用笔记

原文出处 https://www.cnblogs.com/jhy-ocean/p/5560857.html

MYSQL日期 字符串 时间戳互转

平时比较常用的时间、字符串、时间戳之间的互相转换，虽然常用但是几乎每次使用时候都喜欢去搜索一下用法；本文将作为一个笔记，整理一下三者之间的 转换（即：date转字符串、date转时间戳、字符串转date、字符串转时间戳、时间戳转date，时间戳转字符串）用法，方便日后查看；

涉及的函数

date_format(date, format) 函数，MySQL日期格式化函数date_format()

unix_timestamp() 函数

str_to_date(str, format) 函数

from_unixtime(unix_timestamp, format) 函数，MySQL时间戳格式化函数from_unixtime

时间转字符串

[sql] 预览复制
  1. select date_format(now(), '%Y-%m-%d');  
  2.   
  3. #结果：2016-01-05  

时间转时间戳

[sql] 预览复制
  1. select unix_timestamp(now());  
  2.   
  3. #结果：1452001082  

字符串转时间

[sql] 预览复制
  1. select str_to_date('2016-01-02', '%Y-%m-%d %H');  
  2.   
  3. #结果：2016-01-02 00:00:00  

字符串转时间戳

[sql] 预览复制
  1. select unix_timestamp('2016-01-02');  
  2.   
  3. #结果：1451664000  

时间戳转时间

[sql] 预览复制
  1. select from_unixtime(1451997924);  
  2.   
  3. #结果：2016-01-05 20:45:24  

时间戳转字符串

[sql] 预览复制
  1. select from_unixtime(1451997924,'%Y-%d');  
  2.   
  3. //结果：2016-01-05 20:45:24  

附表

MySQL日期格式化（format）取值范围。

 值含义
秒 %S、%s 两位数字形式的秒（ 00,01, ..., 59）
分 %I、%i 两位数字形式的分（ 00,01, ..., 59）
小时  %H 24小时制，两位数形式小时（00,01, ...,23）
%h 12小时制，两位数形式小时（00,01, ...,12）
%k 24小时制，数形式小时（0,1, ...,23）
%l 12小时制，数形式小时（0,1, ...,12）
%T 24小时制，时间形式（HH:mm:ss）
%r  12小时制，时间形式（hh:mm:ss AM 或 PM）
%p  AM上午或PM下午 
  周   %W 一周中每一天的名称（Sunday,Monday, ...,Saturday）
 %a 一周中每一天名称的缩写（Sun,Mon, ...,Sat） 
%w  以数字形式标识周（0=Sunday,1=Monday, ...,6=Saturday） 
%U 数字表示周数，星期天为周中第一天
%u 数字表示周数，星期一为周中第一天
天 %d  两位数字表示月中天数（01,02, ...,31）
%e   数字表示月中天数（1,2, ...,31）
 %D 英文后缀表示月中天数（1st,2nd,3rd ...） 
 %j 以三位数字表示年中天数（001,002, ...,366） 
月 %M  英文月名（January,February, ...,December） 
%b  英文缩写月名（Jan,Feb, ...,Dec） 
%m  两位数字表示月份（01,02, ...,12）
%c  数字表示月份（1,2, ...,12） 
年 %Y  四位数字表示的年份（2015,2016...）
%y   两位数字表示的年份（15,16...）
文字输出  %文字  直接输出文字内容

 

 

SQL注入

1.数字注入方式 一般会会在查询的时候注入

请求地址：localhost/test.php/id=-1 or 1=1

控制器接收：

<?php 

$id = $_GET['id']?$_GET['id']:'';

$sql = "select * from user where id =".$id

数据库执行语句 ：select * from user where id=-1 or 1=1

预防方式：

验证类型是否正确 is_numeric(); //判断是否为数值型

 

2.字符串注入方式 一般在表单提交得时候注入

<?php 

# $user_name = 'json'#' 存在特殊字符，后面的and条件不起作用

$user_name = $_POSTT['user_name']?$_GET['user_name']:'';

$pass_word= $_POSTT['pass_word']?$_GET['pass_word']:'';

$sql = "select * from user where user_name = ". $user_name.' and pass_word = '.$pass_word

数据库执行语句 ：select * from user where user_name = 'json'#' and padd_word=123

预防方式：

转义特殊字符 addslashes()

转义后 ：

$sql = "select * from user where user_name = ". addslashes($user_name).' and pass_word = '.$pass_word

select * from user where user_name = 'json\'#' and padd_word=123

 

创建新用户

  CREATE USER 'username'@'host' IDENTIFIED BY 'password';

　  说明：

  • username：你将创建的用户名
  • host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
  • password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器

     例子：

 CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
 CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456';
 CREATE USER 'pig'@'%' IDENTIFIED BY '123456';
 CREATE USER 'pig'@'%' IDENTIFIED BY '';
 CREATE USER 'pig'@'%'

授权

      GRANT privileges ON databasename.tablename TO 'username'@'host'

      说明：
  • privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL
  • databasename：数据库名
  • tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*

     例子:

  GRANT SELECT, INSERT ON test.user TO 'pig'@'%';
  GRANT ALL ON *.* TO 'pig'@'%';
  GRANT ALL ON maindataplus.* TO 'pig'@'%';

     注意:

     用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，用以下命令:

     GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

　 授权以后要刷新下权限：flush privileges;

　 查看权限：show grants;

　 查看某个用户权限：show grants for 'yangxin'@'localhost';

　 删除权限：revoke delete on test.* from 'jack'@'localhost';

 



三.设置与更改用户密码


SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');


   如果是当前登陆用户用:


SET PASSWORD = PASSWORD("newpassword");


   例子:


SET PASSWORD FOR 'pig'@'%' = PASSWORD("123456");





创建数据库·表


　　1.创建数据库指定字符集


　　CREATE DATABASE IF NOT EXISTS my_db default charset utf8 COLLATE utf8_general_ci;


　　2.创建数据表


　　CREATE TABLE IF NOT EXISTS `test`(
 　　`A1` varchar(20) NOT NULL COMMENT '代码',
 　　`A2` varchar(20) NOT NULL COMMENT '名称',
 　　`A3` varchar(20) DEFAULT NULL COMMENT 'Letter abbreviation首字母缩略字',
 　　`A4` tinyint(4) NOT NULL COMMENT '分类',
 　　`A5` tinyint(4) DEFAULT '0' COMMENT '类型',
 　　`A6` float(20,6) DEFAULT '0.000000' COMMENT '',
 　　`A7` float(20,6) DEFAULT '0.000000' COMMENT '',
 　　`A8` bigint(20) DEFAULT '0' COMMENT '',
 　　`A9` bigint(20) DEFAULT '0' COMMENT '',
 　　`A10` bigint(20) DEFAULT '0' COMMENT '',
 　　`A11` float(20,6) DEFAULT '1.000000' COMMENT '',
　　 `A12` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '记录时间',
 　　`A13` tinyint(4) DEFAULT '0' COMMENT '0:已计算过,1:待计算',
 　　PRIMARY KEY (`A1`)
　　) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='基础信息';


　　3.查看表结构


　　desc table_name;


　　4.查看建表语句


　　show create table table_name;


 




删除表


DROP TABLE IF EXISTS video_image_record;


清空表


TRUNCATE TABLE video_channel_app_sort


导出库-表


mysqldump -uroot -p yaoquyule article article_channel article_channel_app article_hot> /opt/article.sql
mysqldump -uroot -p practice tg6_info tg6_info_channel tg6_info_related  tg6_sync_pond > /opt/tg6.sql
mysqldump -uroot -p practice video video_channel_app video_channel  video_related > /opt/video.sql
mysqldump -uroot -p practice tuiguang_channel tuiguang_data > /opt/tuiguang.sql


导出指定条件


mysqldump -uroot -p practice video --where="from_source='huoshan'" > /opt/www/video.sql
mysqldump -uroot -p practice video --where="from_source='fengwei'" > /opt/www/video.sql


#指定时间时注意：
1.先查看下数据库默认时区：
show variables like '%time_zone%';
2.看数据库默认时区是不是系统时区，然后再查看当前系统时区是不是跟现在时区一样
date -R
3.避免导出数据存在误差，切记先检查一下时区是否一致
mysqldump -uroot -p practice tg6_info -w"create_time >= '2020-04-15 00:00:00' " > /home/test.sql 


//存在时区误差解决方案：导出前设置 set time_zone='+00:00';导出后恢复设置 set time_zone='SYSTEM';


导入库


source /home/abc/abc.sql
source /opt/yule.sql


#添加列
ALTER TABLE `video` ADD (
	is_compress TINYINT (1) NOT NULL DEFAULT 0 COMMENT '图片是否压缩 0未压缩 1压缩',
	is_transcoding TINYINT (1) NOT NULL DEFAULT 0 COMMENT '是否转码 0未转码 1转码',
	is_draft TINYINT (1) NOT NULL DEFAULT 1 COMMENT '是否草稿 1草稿 2发布',
	process_status TINYINT (1) NOT NULL DEFAULT 0 COMMENT '处理状态 0未处理 1处理中 2处理完成',
	is_pgc TINYINT (1) NOT NULL DEFAULT 0 COMMENT '是否pgc用户上传 0不是 1是'
);



#创建联合索引
ALTER TABLE `video` ADD INDEX compress_search ( `is_pgc`, `is_draft`, `process_status` );


#添加列
ALTER TABLE video ADD img_process_status TINYINT(1) NOT NULL DEFAULT 0 COMMENT '图片处理状态 0未处理 1处理中 2处理完成';


#复制表记录
insert INTO video_channel_app(channel_id,video_id,`day`,`update`,is_focus) SELECT channel_id,video_id,`day`,`update`,is_focus from video_channel WHERE channel_id = 4


#检车MySQL是否以文件方式导入导出数据(该方法在18分钟内导出1.6亿条记录，46min内导入6472W条记录，平均速度：8442W条/h。)
show global variables like '%secure%';


ure_file_priv的值为null ，表示限制mysqld 不允许导入|导出
当secure_file_priv的值为/tmp/ ，表示限制mysqld 的导入|导出只能发生在/tmp/目录下
当secure_file_priv的值没有具体值时，表示不对mysqld 的导入|导出做限制


select * into outfile 'ddd.txt' fields terminated by ',' from dn_location limit 10;  
load data infile 'ddd.txt' into table dn_location2  FIELDS TERMINATED BY ','; 


默认导出地址为mysql>data>数据库名称+文件名


　　




 




 


 

























 










		

		
posted @ 
2017-11-09 14:58 
Jenkin一H 
阅读(357) 
评论(0) 
 
收藏 
举报