Mysql 5.7 系列命令 timestamp类型的字段不能设默认值为“0000-00-00 00:00:00” 要设为`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新',

时间:2021-05-24 09:55:47

一.show相关命令语句

1.查看表的索引
 show index from tbl_name;
  • 1
  • table:表名
  • non_unique:索引是非唯一的?。0否,唯一是索引的。1是,是非唯一索引。(ps:这让我想起了令我自挂东南枝的英语)
  • key_name:索引名称
  • seq_in_index: 索引中的起始序列号
  • column_name:创建索引的名称
  • collation:列以什么形式存储在索引中,A升序,null不排序
2.显示系统变量
show session variables [like ''] //查询会话变量, show global variables [like ''] //查询全局变量 
  • 1
3.查询数据库|表|表字段
show databases , show tables [from db_name], show columns from tab_name
  • 1
4 查看数据库引擎相关
show engines  //查看msql支持的所有数据库存储引擎
  • 1
  • engine:存储引擎的名字
  • support: mysql是否支持该引擎。default 默认使用,yes 支持, no不支持。
  • transactions:该引擎是否支持事务,yes 支持 no不支持
  • savepoints: 是否支持回滚点,yes 支持 no不支持
5查看状态信息
show table status \G;//查看某个数据库下所有表的状态信息,所以第一步需要use dbName
  • 1
  • name:表名
  • engine:该表使用的存储引擎。默认innodb
  • rows:行数。对于使用支持事务的引擎,该值不准确。所以一般该值不准确
  • update_time:修改时间
  • create_time:创建时间
  • data-length:表的数据长度(bytes) 。
  • avg_row_length:平均每行数据长度(bytes)同上不准确
  • Auto_increment::下一个序列的值
  • Collation:字符集

show status [like ’ ‘]查看mysql服务的状态信息。mysql优化经常用到该命令。信息比较多,请参考

6显示触发器
show triggers [{from|in} db_name] [like 'pattern' |where expr]//显示某个数据库或者当前数据库下的所有的触发器,可以使用like 或where进行过滤
  • 1
  • 2
7 显示数据库或对象的创建
show create {table tal_name | function fun_name | procedure proc_name | trigger name | user user_name@host | view view_name | db_name}
  • 1
mysql> show create table student\G; *************************** 1. row ***************************  Table: student  Create Table: CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `age` int(11) DEFAULT NULL, `sex` varchar(2) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

二.用户管理

1.修改密码

  • set password for '用户名'@'主机名' =password('新密码');
  • alter user 'userName'@'host' identified by 'newPassword';
  • update mysql.user set authentication_string=password('新密码') where user='用户名';
mysql> set password for 'root'@'localhost'=password('111111'); Query OK, 0 rows affected, 1 warning (0.22 sec)
  • 1
  • 2

2.添加用户

CREATE USER [IF NOT EXISTS] user identified by 'password'
  • 1

3.删除用户

drop user 'user_name'@'localhost' //drop user 'test'@'localhost'
  • 1

4.查询用户

select * from mysql.user\G; //查询所有用户
  • 1

三.实用

1.查询表结构

{describe| desc} tbl_name [col_name]
  • 1

2.语句分析

explain  statemnt_expr  
  • 1

mysql数据语句优化经常用到该命令。一般优化的原则便是尽可能用到索引经可能避免表的全部扫描。详细内容可以参考

四.内置函数

1.日期类
1)查询当前时间日期
  • now() 获取 当前日期和时间 //2018-04-12 18:18:57
  • curdate() 当前日期,///2018-04-12
  • curtime() 当前时间 //18:18:57
  • current_time() ; //同curtime(),current_time
  • current_date() ; // 同curdate(),current_date
  • current_timestamp() //同now()
    Mysql 5.7 系列命令 timestamp类型的字段不能设默认值为“0000-00-00 00:00:00” 要设为`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新',
2)时间戳
  • unix_timestamp([date]) 将日期转化为时间戳,参数可以不写默认now()
    • date 可以是日期时间类型 也可以是时间字符串 ‘2018-2-12’
    mysql> select from_unixtime(unix_timestamp('2018:4:8')); +------------------------------------------------+ | from_unixtime(unix_timestamp('2018:4:8')) | +------------------------------------------------+ | 2018-04-08 00:00:00 |
  • 1
  • 2
  • 3
  • 4
  • 5
  • from_unixtime(timestamp [,format]) 根据format格式化时间戳
    • timestamp : 时间戳即 UNIX_TIMESTAMP()
    • format 格式化表达式。使用format参数后返回的结果是字符串。常用的时间格式化占位符如下更多参数请参考,
      • %Y 年,四位数字
      • %y 年,后两位数字
      • %m 月 ,数字[1-12]
      • %d 日,月份里的第几天,两位数字[1-31]
      • %H 小时,24进制 [0-23]
      • %h 小时,12进制[0-11]
      • %i 分钟 [0-59]
      • %s | %S 秒 0-59

Mysql 5.7 系列命令 timestamp类型的字段不能设默认值为“0000-00-00 00:00:00” 要设为`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新',

3)时间截取(返回对应的日期,时间或者数字)
  • date(expr) // 如果解析失败返回Null,返回日期类型date
  • time(expr) //返回时间类型 time
  • timestamp(exper,format) //返回混合日期和时间,datetime 类型
  • year(expr) //返回数字
  • month(expr)//返回数字【1-12】
  • day(expr) //返回数字【1-31】
  • hour(expr) //返回数字【0-23】
  • minute(expr) //返回数字【0-59】
  • last_day(date) //返回最后一天 ,返回date类型 //2018-04-30
  • to_days(expr) //返回到公元0年一月一日的总天数
    expr :时间字符串,日期字符串或者日期时间类型。
    对于截取年,月,日,时,分,秒返回的都是对应的字符串,因为时间日期类型mysql 本身就只提供了date,time ,datetime,timestamp,year这五种类型

    select date(now()),time('2018-04-19 11:10:11'),year(now()),to_days(now()); 2018-04-19 11:10:11 2018 737168
    • 1
    • 2
日期操作
  • ADDDATE(date,INTERVAL expr unit)
    • date 要操作的日期,可以是日期类型也可以是日期字符串
    • INTERVAL: MySQL关键字 ,意思是间隔,间隙
    • unit 操作的单元,年,月,日,时,分,秒对应YEAR,MONTH,DAY,HOUR,MINUTE,SECOND
    • expr 想要增加或减少的时间单位。expr>0,增加,expr<0减少
      -DATE_ADD(date,INTERVAL expr unit)类似ADDDATE()
  • ADDTIME(expr1,expr2) 时间相加 expr1+expr2
    Mysql 5.7 系列命令 timestamp类型的字段不能设默认值为“0000-00-00 00:00:00” 要设为`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新',
  • SUBDATE(date,INTERVAL expr unit)DATE_SUB(date,INTERVAL expr unit)
  • DATEDIFF(expr1, expr2) 计算两个表达式之间的时间差(expr1-expr2 )天,
SELECT DATEDIFF( '2018-4-23', '2018-4-12 12:12:23'), DATEDIFF( now(), DATE_ADD(now(), INTERVAL 12 DAY) ) 11 -12
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
日期时间类型和其他类型转换
  • DATE_FORMAT(date,format) //转为字符串 跟 from_unixtime(timestamp,format)类似
  • TIME_FORMAT(date,format) //只能转化时间,不能转化日期
select DATE_FORMAT(now(),'%Y-%m-%d %H:%i-%s'),TIME_FORMAT(now(),'%Y-%m-%d %H:%i-%s'); 2018-04-23 15:15-26 0000-00-00 15:15-26
  • 1
  • 2

五数据库备份与恢复

msqldump备份

msqldump可以用数据的备份,其有两种方式的输出,取决于是否使用 –tab。

不使用 –tab,会保存数据和表的创建语句保存到一个文件中
使用–tab,会为每个备份的表生成两份文件,一份是数据(tal_name.text),一份是表的创建语句(tal_name.sql)

ps:查看表的创建可以使用show create tab_name

语法: mysqldump [arguments]
  • 1
-h ,--host=hostName 连接的主机,默认127.0.0.1 -p ,--port=port 服务的端口号,默认3306 -u ,--user=username 用户名 -p ,--password[=password] 密码,可以直接写在命令行 -B ,--databases db1[db2] 备份的数据库,单个数据库的话可以省略 --add-drop-database 在执行create database 之前执行drop database --add-drop-table 执行create table之前先执行drop table,默认使用 --skip-add-drop-table 每次create table之前不执行drop table
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
1)不使用–tab

备份指定的数据库

mysqldump -u用户名 -p[密码] [--databases db1,db2] > 输出文件路径 msqldump -uroot -p [--databases]test >E:\test.text //将test备份到本地E:\test.text文件下 ,因为至备份一个数据库,所以--databases可以省略
  • 1
  • 2
  • 3

备份所有数据库

mysqldump -u用户名 -p[密码] --all-databases >输出文件路径 
  • 1

备份某个数据库下的表

mysqldump  -uroot -p test --tables user address> E:\tables_backup.sql //备份test数据库下的user表,address表到本地
  • 1
  • 2
2)使用–tab
mysqldump -uroot -p --tab=文件保存目录
  • 1

使用上面的命令可能会出现以下错误提示:

mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'
  • 1

使用 mysqld --verbose --help可以查看具体用法
–secure-file-priv=name
Limit LOAD DATA, SELECT … OUTFILE, and LOAD_FILE() to
files within specified directory
同时会看到secure-file-priv NULL,所以启动在mysql配置文件my.ini下添加
secure-file-priv =再起启动mysqld就可以了。详情请参考

恢复

1.

mysql -u用户名 -p < 文件路径 mysql -uroot -p < E:\text.text
  • 1
  • 2
  • 3

2.

msql> source 文件路径 //该方式需要先登陆,在导数据