mysql创建表设置数据类型datetime mysql建表时间字段

20191121

一、登录方式

mysql -uroot -pmypassword 可登录，命令行不能带有密码 否则history查看泄露，不推荐
mysql -uroot -p 回车输入密码
mysql -u root -pmypassword u和用户之间有空格，可登录，不推荐
mysql -u root -p ruozedata 不能登录，-p不能有空格

二、杀进程

show processlist 查看进程:看时间，找到消耗时间长的，有可能导致mysql服务夯住或锁死的
kill id 杀进程:执行的sql确认清楚，谨慎kill

三、字段类型

数值型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

时间类型

类型	大小(字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038	结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS

文本型

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

四、数据库语言

整理三句话 创建db 创建用户 赋权 刷新权限

create database ruozedata;
grant all privileges on *.* to jepson@'%' identified by 'ruozedata';
flush privileges;

1、DDL 数据定义语言

MySQL中的DDL代表着数据库定义语句，用来创建数据库中的表、索引、视图、存储过程、触发器等。
常用的语句关键字有：CREATE,ALTER,DROP,TRUNCATE,COMMENT,RENAME

create database；
drop database；
create table mytable (id)

2、DML

数据操纵语言，数据的增删改查

insert into mytable (name,age) values ('zhangsan',18);
update mytable set name='zhangsan' where id=1;
delete from mytale where id=1;

注意:update和delete之前一定确认是否有where条件筛选

3、DCL 数据控制语言

数据控制语言
通过GRANT和REVOKE，确定单个用户或用户组对数据库对象的访问权限。

4、DQL

数据查询语言

5、DPL 事务处理语言

事务处理语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION、COMMIT和ROLLBACK。

6、CCL 指针控制语言

它的语句，像DECLARE CURSOR、FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。

五、建表规范

create table mytable(
id int(11) not null auto_increment,
···
name varchar(200) COMMENT '用户名称',
age  int(3) COMMENT '用户年龄',
···
createuser varchar(200) ,
createtime timestamp not null default current_timestamp,
updateuser varchar(200) ,
updatetime timestamp not null default current_timestamp on update current_timestamp,
primary key (id)
);

• 表名、字段名等不能是中文，避免使用汉语拼音；
• 统一风格：如createtime、create_time、cretime、cre_time、ctime，具体安装现有业务上的风格来建表
• 第一个字段一定是ID，自增长，主键，not null，unique。自增ID能使得数据写入时按照顺序写入，写入和后续数据读取都更快。
• 一张表只有一个主键,primary key，unique，not null:
  ALTER TABLE mytable ADD CONSTRAINT mytable_un UNIQUE KEY (id) ;
• 后四个字段建议都加：创建用户、创建时间、修改用户、修改时间.
• 业务字段一定要有注释：
• 业务数据有修改人、修改时间等信息
• 建表前查看database的默认字符集是否是自己需要的。了解下database 字符集、table字符集 、column 字符集、服务端 字符集、客户端字符集

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| testdb          |
| test               |
+--------------------+
5 rows in set (0.00 sec)
mysql> show create database testdb;
+-----------+-----------------------------+
| Database  | Create Database                                                      |
+-----------+-----------------------------+
| testdb | CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+-----------+-----------------------------+
1 row in set (0.00 sec)
mysql> show variables like '%char%';
+--------------------------+--------------------------+
| Variable_name            | Value             |
+--------------------------+--------------------------+
| character_set_client     | utf8              |
| character_set_connection | utf8              |
| character_set_database   | latin1            |
| character_set_filesystem | binary            |
| character_set_results    | utf8              |
| character_set_server     | latin1            |
| character_set_system     | utf8              |
| character_sets_dir       | /usr/local/mysql-5.6.23-linux-glibc2.5-x86_64/share/charsets/ |
+--------------------------+--------------------------+
8 rows in set (0.00 sec)
mysql>

六、思考:

1.datetime和timestamp区别？

序号	区别点	datetime	timestamp
1	表示的日期类型	YYYY-MM-DD HH:MM:SS[.fraction]	YYYY-MM-DD HH:MM:SS[.fraction]
2	存储方式	不做任何改变,基本上是原样输入和输出	把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储,查询时，将其又转化为客户端当前时区进行返回
3	存储的时间范围	‘1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’	‘1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’

2. 假如id 超了，怎么办？

以无符号整型为例，存储范围为0～4294967295，约43亿！我们先说一下，一旦自增id达到最大值，此时数据继续插入是会报一个主键冲突异常如下所示：//Duplicate entry ‘4294967295’ for key ‘PRIMARY’
解决方法:
1、项目初期表设计时，使用 BIGINT 而不是 INT
2、若已经达到最大值，考虑更改自增ID字段的类型为BIGINT
mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type; 在5.6+开始，mysql支持在线修改数据库表。但是，对于修改数据类型这种操作，是不支持并发的DML操作！也就是说，如果你直接使用ALTER这样的语句在线修改表数据结构，会导致这张表无法进行更新类操作(DELETE、UPDATE、DELETE)。

注意：由于存在rollback等操作存在，会导致id不连续。因此自增id的最大值和表记录条数不一定相等

3. ENGINE=InnoDB 是什么？MyISAM区别是什么

1.MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。
2.MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持已经外部键等高级数据库功能。
3.InnoDB不支持FULLTEXT类型的索引。
4.InnoDB中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含where条件时，两种表的操作是一样的。
3.对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。
4.DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。
5.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用。
6.MyISAM类型的二进制数据文件可以在不同操作系统中迁移。也就是可以直接从Windows系统拷贝到linux系统中使用。
7.InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如update table set num=1 where name like “%a%”

4. binlog文件恢复数据怎么做

前提：数据库需要开启log_bin功能并设置开启binlog行级模式(ROW):

vim /etc/my.cnf , 在mysqld选项中添加内容如下:

>log-bin=mysql-bin

默认如果不给值的话，log-bin 的会以mysqld-bin 为索引，创建mysqld-bin.00001等。重启mysqld即可。

操作语法：
格式：mysqlbinlog 日志文件 参数 | mysql -u用户名 -p密码
全部恢复
mysqlbinlog liangck.000001 | mysql -uroot –p123456 从417至773位置
mysqlbinlog liangck.000002 --start-pos=417 --stop-pos=773 | mysql -uroot -p123456 从头至773位置
mysqlbinlog liangck.000002 --stop-pos=773 | mysql -uroot -p123456 从417至尾 位置
mysqlbinlog liangck.000002 --start-pos=417 | mysql -uroot -p123456 日期参数和pos同理
mysqlbinlog liangck.000002 --start-datetime="2004-12-25 11:25:56" | mysql -uroot -p123456

5. 了解MySQL 主从复制，读写分离

https://www.jianshu.com/p/84d8f40c07aa