Mysql-密码破解、配置文件、时间枚举、外键

密码破解

我们使用mysql时候，会发生如下错误

Warning: Using a password on the command line interface can be insecure.ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)

那么我们怎样来破解密码呢？

1、首先停止mysql服务

2、在cmd中输入mysqld –skip-grant-tables如下：

C:\Users\67334>mysqld --skip-grant-tables2017-12-15 16:06:23 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

3、然后另外启一个cmd窗口

4、然后启动mysql服务可以在cmd窗口，也可以利用图形化界面，参考步骤一

5、然后就可以登陆了mysql -uroot -proot

表拷贝

拷贝表结构+记录
create table day43.user select host,user,password from mysql.user;
只拷贝表结构
create table day43.user select host,user,password from mysql.user where 1=2;

时间

YEAR        YYYY（1901/2155）    DATE        YYYY-MM-DD（1000-01-01/9999-12-31）    TIME        HH:MM:SS（'-838:59:59'/'838:59:59'）    DATETIME        YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59    Y）    TIMESTAMP        YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）

示例

============date,time,datetime===========MariaDB [db1]> create table t11(d date,t time,dt datetime);MariaDB [db1]> desc t11;+-------+----------+------+-----+---------+-------+| Field | Type     | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| d     | date     | YES  |     | NULL    |       || t     | time     | YES  |     | NULL    |       || dt    | datetime | YES  |     | NULL    |       |+-------+----------+------+-----+---------+-------+MariaDB [db1]> insert into t11 values(now(),now(),now());MariaDB [db1]> select * from t11;+------------+----------+---------------------+| d          | t        | dt                  |+------------+----------+---------------------+| 2017-07-25 | 16:26:54 | 2017-07-25 16:26:54 |+------------+----------+---------------------+

枚举

MariaDB [db1]> create table consumer(     -> name varchar(50),    -> sex enum('male','female'),    -> level enum('vip1','vip2','vip3','vip4','vip5'), #在指定范围内，多选一    -> hobby set('play','music','read','study') #在指定范围内，多选多    -> );MariaDB [db1]> insert into consumer values      -> ('egon','male','vip5','read,study'),    -> ('alex','female','vip1','girl');MariaDB [db1]> select * from consumer;+------+--------+-------+------------+| name | sex    | level | hobby      |+------+--------+-------+------------+| egon | male   | vip5  | read,study || alex | female | vip1  |            |+------+--------+-------+------------+

char\varchar

char定长，简单粗暴，浪费空间，存取速度快
字符长度范围：0-255（一个中文是一个字符，是utf8编码的3个字节）

存储：
存储char类型的值时，会往右填充空格来满足长度
例如：指定长度为10，存>10个字符则报错，存<10个字符则用空格填充直到凑够10个字符存储

检索：
在检索或者说查询时，查出的结果会自动删除尾部的空格

varchar类型：变长，精准，节省空间，存取速度慢
字符长度范围：0-65535
varchar类型存储数据的真实内容，不会用空格填充，如果’ab ‘,尾部的空格也会被存起来
尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容

mysql> create table t1(x char(5),y varchar(5));Query OK, 0 rows affected (0.26 sec)#char存5个字符，而varchar存4个字符mysql> insert into t1 values('你瞅啥 ','你瞅啥 ');Query OK, 1 row affected (0.05 sec)mysql> SET sql_mode='';Query OK, 0 rows affected, 1 warning (0.00 sec)#在检索时char很不要脸地将自己浪费的2个字符给删掉了，装的好像自己没浪费过空间一样，而varchar很老实，存了多少，就显示多少mysql> select x,char_length(x),y,char_length(y) from t1; +-----------+----------------+------------+----------------+| x         | char_length(x) | y          | char_length(y) |+-----------+----------------+------------+----------------+| 你瞅啥    |              3 | 你瞅啥     |              4 |+-----------+----------------+------------+----------------+1 row in set (0.00 sec)#略施小计，让char现出原形mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';Query OK, 0 rows affected (0.00 sec)#这下子char原形毕露了......mysql> select x,char_length(x),y,char_length(y) from t1;+-------------+----------------+------------+----------------+| x           | char_length(x) | y          | char_length(y) |+-------------+----------------+------------+----------------+| 你瞅啥      |              5 | 你瞅啥     |              4 |+-------------+----------------+------------+----------------+1 row in set (0.00 sec)

外键

#表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一create table department(id int primary key,name varchar(20) not null)engine=innodb;#dpt_id外键，关联父表（department主键id），同步更新，同步删除create table employee(id int primary key,name varchar(20) not null,dpt_id int,constraint fk_name foreign key(dpt_id)references department(id)on delete cascadeon update cascade )engine=innodb;#先往父表department中插入记录insert into department values(1,'欧德博爱技术有限事业部'),(2,'艾利克斯人力资源部'),(3,'销售部');#再往子表employee中插入记录insert into employee values(1,'egon',1),(2,'alex1',2),(3,'alex2',2),(4,'alex3',2),(5,'李坦克',3),(6,'刘飞机',3),(7,'张火箭',3),(8,'林子弹',3),(9,'加特林',3);#删父表department，子表employee中对应的记录跟着删mysql> delete from department where id=3;mysql> select * from employee;+----+-------+--------+| id | name  | dpt_id |+----+-------+--------+|  1 | egon  |      1 ||  2 | alex1 |      2 ||  3 | alex2 |      2 ||  4 | alex3 |      2 |+----+-------+--------+#更新父表department，子表employee中对应的记录跟着改mysql> update department set id=22222 where id=2;mysql> select * from employee;+----+-------+--------+| id | name  | dpt_id |+----+-------+--------+|  1 | egon  |      1 ||  3 | alex2 |  22222 ||  4 | alex3 |  22222 ||  5 | alex1 |  22222 |+----+-------+--------+ 示范