快捷搜索:

比较简单实用的PHP无限分类源码分享,风马一族_mysql

2019-11-17 16:03栏目:计算机网络
TAG:

 mysql> insert into tb_kind values(null,"NBA",8,"0-1-5-8");
 Query OK, 1 row affected

mysql> insert into tb_kind values(null,"NBA",8,"0-1-5-8");
Query OK, 1 row affected

  1. 创建数据库
    1. mysql>create database 数据库名 ;
  2. 显示数据库
    1. mysql>show databases;
    2. +--------------------+
      | Database                |
      +--------------------+
      | information_schema   |
      | mysql                       |
      | performance_schema |
      | test                         |
      | twwq                       |
      | xhkdb                      |
      +--------------------+
      6 rows in set (0.02 sec) 
  3. 删除数据库
    1. drop database 数据库名;
    2. mysql> drop database twwq;
      Query OK, 0 rows affected (0.05 sec)
  4. 连接数据库
    1. use 数据库名;
    2. mysql>use twwq;
      Database changed
  5. 查看 当前连接的数据库
    1. mysql>select database();
      +------------+
      | database()  |
      +------------+
      | twwq          |
      +------------+
      1 row in set (0.00 sec)

4. TIMESTAMP

格式:'YYYY-MM-DD HH:MM:SS'。

范围:'0000-01-01 00:00:00'到'9999-12-31 23:59:59'。

 

mysql> create table d5 (id timestamp primary key);

Query OK, 0 rows affected (0.03 sec)

 

mysql> show create table d5;

+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                                                     |

+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| d5    | CREATE TABLE `d5` (

  `id` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

 

mysql> insert into d5 values ();

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from d5;

+---------------------+

| id                  |

+---------------------+

| 2017-09-01 12:15:16 |

+---------------------+

1 row in set (0.00 sec)

 

TIMESTAMP与DATETIME类型的区别:

(1)TIMESTAMP的默认值为CURRENT_TIMESTAMP,即当前时间。

在定义列时,默认情况下具备以下属性:

 DEFAULT CURRENT_TIMESTAMP

而DATETIME类型则默认值为NULL,但是可以显式的指定为DEFAULT CURRENT_TIMESTAMP。

 

(2)TIMESTAMP类型的列在行数据更新时,默认情况下自动更新为当前时间。

在定义列时,默认情况下具备以下属性:

ON UPDATE CURRENT_TIMESTAMP,

而DATETIME类型则默认情况下并不自动更新,但是可以显式的指定ON UPDATE CURRENT_TIMESTAMP属性。

 

(3)TIMESTAMP类型的列在默认情况下为NOT NULL,如果需要赋值为NULL则必须显式定义为NULL属性。 DATETIME类型在默认情况下为NULL。

 

 

 

上述自动赋予的默认值和默认更新值仅仅对于数据表中第1个timestamp字段有效。

如果存在多个timestamp字段,则会报错:

mysql> create table d6 ( id int primary key,v1 datetime, v2 timestamp, v3 timestamp);

ERROR 1067 (42000): Invalid default value for 'v3'

 

此时需要显式的定义其它timestamp字段的default属性和on update属性。

mysql> create table d6 ( id int primary key,v1 datetime, v2 timestamp, v3 timestamp default current_timestamp on update current_timestamp);

Query OK, 0 rows affected (0.03 sec)

 

mysql> show create table d6;

+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                                                                                                                                                                                             |

+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| d6    | CREATE TABLE `d6` (

  `id` int(11) NOT NULL,

  `v1` datetime DEFAULT NULL,

  `v2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  `v3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

 

 

TIMESTAMP类型在默认情况下的DEFAULT值是否定义为CURRENT_TIMESTAMP,以及ON UPDATE属性是否定义为CURRENT_TIMESTAMP,可以通过全局变量来控制:

mysql> show variables like '%timestamp%';

+---------------------------------+-------------------+

| Variable_name                   | Value             |

+---------------------------------+-------------------+

| explicit_defaults_for_timestamp | OFF               |

| log_timestamps                  | UTC               |

| timestamp                       | 1504241030.617255 |

+---------------------------------+-------------------+

3 rows in set (0.16 sec)

 

explicit_defaults_for_timestamp 这个全局变量默认情况下为OFF,即不需要显式定义TIMESTAMP的默认值;如果修改为ON,则不会在定义数据表时自动加上DEFAULT和ON UPDATE属性。

 

mysql> set explicit_defaults_for_timestamp=on;

Query OK, 0 rows affected (0.05 sec)

 

mysql> create table d11 ( id timestamp primary key);

Query OK, 0 rows affected (0.05 sec)

 

mysql> show create table d11;

+-------+-------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                |

+-------+-------------------------------------------------------------------------------------------------------------+

| d11   | CREATE TABLE `d11` (

  `id` timestamp NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+-------+-------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

 

 

 +------------+
 | abs        |
 +------------+
 | 0-1        |
 | 0-1-5      |
 | 0-1-5-8    |
 | 0-1-5-8-10 |
 | 0-1-5-8-11 |
 | 0-1-5-9    |
 | 0-1-6      |
 | 0-1-7      |
 | 0-2        |
 | 0-3        |
 | 0-4        |
 +------------+
 11 rows in set
 mysql> select concat(path,"-",id) as,id,name,path abs from tb_kind order by abs;
 ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'id,name,path abs from tb_kind order by abs' at line 1
 mysql> select concat(path,"-",id) as abs,
 id,pname,path abs from tb_kind order by abs;
 +------------+----+----------+---------+
 | abs        | id | pname    | abs     |
 +------------+----+----------+---------+
 | 0-1        |  1 | 新闻     | 0       |
 | 0-1-5      |  5 | 体育新闻 | 0-1     |
 | 0-1-5-8    |  8 | 篮球新闻 | 0-1-5   |
 | 0-1-5-8-10 | 10 | NBA      | 0-1-5-8 |
 | 0-1-5-8-11 | 11 | CBA      | 0-1-5-8 |
 | 0-1-5-9    |  9 | 足球新闻 | 0-1-5   |
 | 0-1-6      |  6 | 娱乐新闻 | 0-1     |
 | 0-1-7      |  7 | 财经新闻 | 0-1     |
 | 0-2        |  2 | 视频     | 0       |
 | 0-3        |  3 | 图片     | 0       |
 | 0-4        |  4 | 博客     | 0       |
 +------------+----+----------+---------+
 11 rows in set
 mysql>

mysql> insert into tb_kind values(null,"博客",0,0);
Query OK, 1 row affected

 

3. DATETIME

格式:'YYYY-MM-DD HH:MM:SS'。

范围:'0000-01-01 00:00:00'到'9999-12-31 23:59:59'。

 

mysql> create table d3 ( id datetime primary key);

Query OK, 0 rows affected (0.03 sec)

 

mysql> insert into d3 values ('0000-01-01 00:00:00');

Query OK, 1 row affected (0.01 sec)

 

 

DATETIME中的年月日时分秒只能是合法的取值。其中小时不能超过23。

mysql> insert into d3 values ('0000-01-01 24:00:00');

ERROR 1292 (22007): Incorrect datetime value: '0000-01-01 24:00:00' for column 'id' at row 1

 

DATETIME类型的列可以显式指定默认值和更新时自动更新为当前时间。

mysql> create table d9 ( id int primary key,v1 datetime default current_timestamp on update current_timestamp);

Query OK, 0 rows affected (0.06 sec)

 

mysql> show create table d9;

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                                                                    |

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| d9    | CREATE TABLE `d9` (

  `id` int(11) NOT NULL,

  `v1` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

 

mysql> insert into d9 values (1);

ERROR 1136 (21S01): Column count doesn't match value count at row 1

mysql> insert into d9 (id) values (1);

Query OK, 1 row affected (0.02 sec)

 

mysql> select * from d9;

+----+---------------------+

| id | v1                  |

+----+---------------------+

|  1 | 2017-09-01 12:37:12 |

+----+---------------------+

1 row in set (0.00 sec)

 

mysql> update d9 set id=2;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

 

mysql> select * from d9;

+----+---------------------+

| id | v1                  |

+----+---------------------+

|  2 | 2017-09-01 12:37:28 |

+----+---------------------+

1 row in set (0.01 sec)

 

 mysql> select concat(path,"-",id) from tb_kind;
 +---------------------+
 | concat(path,"-",id) |
 +---------------------+
 | 0-1                 |
 | 0-2                 |
 | 0-3                 |
 | 0-4                 |
 | 0-1-5               |
 | 0-1-6               |
 | 0-1-7               |
 | 0-1-5-8             |
 | 0-1-5-9             |
 | 0-1-5-8-10          |
 | 0-1-5-8-11          |
 +---------------------+
 11 rows in set

mysql> insert into tb_kind values(null,"图片",0,0);
Query OK, 1 row affected

2.  mysql>select * from 数据表 order by id limit
    从哪个位置开始,一共几条;
    1.  mysql> select * from registered order by id limit 1,2;  
        +----+----------+----------+------------+  
        | id | username | password | repassword  |  
        +----+----------+----------+------------+  
        |  2 | ceo      | aswe1322 | aswe1322       |  
        |  3 | jk       | qazxsw12 | qazxsw12        |  
        +----+----------+----------+------------+  
        2 rows in set (0.00 sec)     //获取两条数据

7. 微秒

对于时间相关类型TIME,TIMESTAMP,DATETIME,MySQL提供了微秒的表达方式。

mysql> select cast( '2017-09-01 13:51:25.123456'  as datetime(6));

+-----------------------------------------------------+

| cast( '2017-09-01 13:51:25.123456'  as datetime(6)) |

+-----------------------------------------------------+

| 2017-09-01 13:51:25.123456                          |

+-----------------------------------------------------+

1 row in set (0.00 sec)

 

 mysql> select * from tb_kind;
 +----+----------+-----+---------+
 | id | pname    | pid | path    |
 +----+----------+-----+---------+
 |  1 | 新闻     |   0 | 0       |
 |  2 | 视频     |   0 | 0       |
 |  3 | 图片     |   0 | 0       |
 |  4 | 博客     |   0 | 0       |
 |  5 | 体育新闻 |   1 | 0-1     |
 |  6 | 娱乐新闻 |   1 | 0-1     |
 |  7 | 财经新闻 |   1 | 0-1     |
 |  8 | 篮球新闻 |   5 | 0-1-5   |
 |  9 | 足球新闻 |   5 | 0-1-5   |
 | 10 | NBA      |   8 | 0-1-5-8 |
 | 11 | CBA      |   8 | 0-1-5-8 |
 +----+----------+-----+---------+
 11 rows in set

mysql> insert into tb_kind values(null,"财经新闻",1,"0-1");
Query OK, 1 row affected

  1. 创建表
    1. create table 表名(<字段名1> <类型1> [,..<字段名n> <类型n>]);        //警告:没有给表填加字段,会出错
    2. mysql> create table registered(id int(8) not null primary key auto_increment,
          -> username char(20) not nul
          -> password char(20) not null,
          -> repassword char(20) not null);
      Query OK, 0 rows affected (0.19 sec)
  2. 查看表
    1. mysql>desc 表名 ; (方式一)      mysql> show columns from 表名; (方式二)
    2. mysql>desc registered; (方式一)      mysql> show columns from registered;(方式二 )
      +------------+----------+------+-----+---------+----------------+
      | Field        | Type       | Null   | Key  | Default   | Extra               |
      +------------+----------+------+-----+---------+----------------+
      | id               | int(8)       | NO   | PRI   | NULL      | auto_increment |
      | username    | char(20)  | NO    |        | NULL      |                        |
      | password    | char(20)  | NO    |        | NULL      |                        |
      | repassword | char(20)  | NO    |        | NULL      |                        |
      +------------+----------+------+-----+---------+----------------+
      4 rows in set (0.01 sec)
  3. 修改表
    1. rename table (原先的)表名  to (修改后的)表;
    2. mysql> rename table registered to sows;
      Query OK, 0 rows affected (0.06 sec)
    3. 查看效果
      1. mysql> desc sows;
        +------------+----------+------+-----+---------+----------------+
        | Field        | Type       | Null   | Key  | Default   | Extra              |
        +------------+----------+------+-----+---------+----------------+
        | id               | int(8)      | NO    | PRI   | NULL     | auto_increment |
        | name          | char(20)  | YES  |         | NULL     |                        |
        | password    | char(20)  | NO    |        | NULL     |                        |
        | repassword | char(20)  | NO    |        | NULL     |                        |
        +------------+----------+------+-----+---------+----------------+
        4 rows in set (0.01 sec)      
  4. 删除表
    1. mysql>drop table 表名;            //警告:永久性删除数据表,慎用
    2. mysql>drop table registered;
      Query OK, 0 rows affected (0.05 sec)  
  5. 增加    alter add命令用来增加表的字段。
    1. 增添字段
      1. alter table 数据表 add 字段 参数 其他;
      2. mysql> alter table registered add sex char(2);
        Query OK, 0 rows affected (0.33 sec)
        Records: 0  Duplicates: 0  Warnings: 0
      3. 查看效果
        1. mysql> desc registered;
          +------------+----------+------+-----+---------+----------------+
          | Field           | Type        | Null   | Key | Default | Extra                 |
          +------------+----------+------+-----+---------+----------------+
          | id               | int(8)       | NO    | PRI | NULL    | auto_increment   |
          | username    | char(20)   | NO    |      | NULL    |                          |
          | password    | char(20)   | NO    |      | NULL    |                          |
          | repassword | char(20)   | NO    |      | NULL    |                          |
          | sex             | char(2)    | YES   |      | NULL    |                          |        //增加sex 字段
          +------------+----------+------+-----+---------+----------------+
          5 rows in set (0.01 sec) 
        2. mysql> desc registered;   
          ERROR 1146 (42S02): Table 'twwq.registered' doesn't exist   //原因表名被修改,因此原先的表名,无法使用了
    2. 修改字段
      1. alter table 数据表 change (要被修改的)字段  (修改后的)字段  参数;
      2. mysql> alter table registered change username name char(20);
        Query OK, 0 rows affected (0.33 sec)
        Records: 0  Duplicates: 0  Warnings: 0  
      3. 查看效果
        1. mysql> desc registered;
          +------------+----------+------+-----+---------+----------------+
          | Field           | Type       | Null    | Key  | Default  | Extra               |
          +------------+----------+------+-----+---------+----------------+
          | id               | int(8)      | NO     | PRI  | NULL     | auto_increment |
          | name          | char(20)  | YES   |        | NULL     |                        |        // username   修改成  name
          | password    | char(20)  | NO     |        | NULL     |                       |
          | repassword | char(20)  | NO     |         | NULL     |                      |
          | sex            | char(2)    | YES    |         | NULL     |                      |
          +------------+----------+------+-----+---------+----------------+
          5 rows in set (0.01 sec)   
    3. 删除字段
      1. alter table 数据表 drop 字段;
      2. mysql> alter table registered drop sex;
        Query OK, 0 rows affected (0.31 sec)
        Records: 0  Duplicates: 0  Warnings: 0
      3. 查看效果
        1. mysql> desc registered;
          +------------+----------+------+-----+---------+----------------+
          | Field           | Type       | Null    | Key  | Default  | Extra               |
          +------------+----------+------+-----+---------+----------------+
          | id               | int(8)      | NO    | PRI   | NULL     | auto_increment |
          | name          | char(20)  | YES  |        | NULL      |                        |
          | password    | char(20)  | NO    |        | NULL     |                        |
          | repassword | char(20)  | NO    |        | NULL      |                       |
          +------------+----------+------+-----+---------+----------------+   //sex 字段 已经被删除
          4 rows in set (0.01 sec)             
    4. 加索引  
      1. alter table 数据表 add index 索引名(【已经存在于表的】字段名);
      2. mysql> alter table registered add index sows_name(username);
        Query OK, 0 rows affected (0.17 sec)
        Records: 0  Duplicates: 0  Warnings: 0 
      3. 查看效果
        1. mysql> desc registered;
          +------------+----------+------+-----+---------+----------------+
          | Field           | Type       | Null    | Key | Default | Extra                 |
          +------------+----------+------+-----+---------+----------------+
          | id               | int(8)      | NO    | PRI   | NULL    | auto_increment  |
          | username    | char(20)  | NO    | MUL | NULL     |                        |   //Key 字段出现 MUL
          | password    | char(20)  | NO    |        | NULL     |                        |
          | repassword | char(20)  | NO    |        | NULL     |                        |
          | sex            | char(2)    | YES   |        | NULL     |                        |
          +------------+----------+------+-----+---------+----------------+
          5 rows in set (0.01 sec)  
    5. 删除索引
      1. alter table 数据表 drop index 索引名;
      2. mysql> alter table registered drop index sows_name;
        Query OK, 0 rows affected (0.15 sec)
        Records: 0  Duplicates: 0  Warnings: 0
      3. 查看效果
        1. mysql> desc registered;
          +------------+----------+------+-----+---------+----------------+
          | Field           | Type       | Null    | Key | Default   | Extra               |
          +------------+----------+------+-----+---------+----------------+
          | id               | int(8)      | NO    | PRI   | NULL     | auto_increment |
          | username    | char(20)  | NO    |        | NULL     |                        |  //key 字段的内容消失
          | password    | char(20)  | NO    |        | NULL     |                        |
          | repassword | char(20)  | NO    |        | NULL     |                        |
          | sex            | char(2)    | YES   |        | NULL     |                        |
          +------------+----------+------+-----+---------+----------------+
          5 rows in set (0.01 sec)                
  6. 插入数据
    1. mysql>insert into 数据表(字段  //如果已经实现步骤1,则不用再写字段) values (根据字段的数量,变量类型、变量长度要求、是否允许为空,来进行填写内容)
    2. mysql> insert into registered values (1,'sows','asqw1234','asqw1234'),(2,'ceo','
      aswe1322','aswe1322'),(3,'jk','qazxsw12','qazxsw12');
      Query OK, 3 rows affected (0.01 sec)
      Records: 3  Duplicates: 0  Warnings: 0      // 成功插入 3条数据
  7. 查看表中的数据    //准确性的查询所需数据
    1. mysql>select * from 数据表;                         //获取表中所有数据
      1. mysql>select * from  registered;
        +----+----------+----------+--------------+
        | id    | username | password | repassword |
        +----+----------+----------+--------------+
        |  1   | sows        | asqw1234 | asqw1234   |
        |  2   | ceo          | aswe1322  | aswe1322   |
        |  3   | jk            | qazxsw12  | qazxsw12   |
        +----+----------+----------+--------------+
        3 rows in set (0.00 sec)      //获取到3条数据

5. 日期时间相关函数

 

(1)取得当前日期和时间。

now()取得当前日期和时间。

current_timestamp()取得当前日期和时间。

 

mysql> select now(), current_timestamp() ;

+---------------------+---------------------+

| now()               | current_timestamp() |

+---------------------+---------------------+

| 2017-09-01 12:39:50 | 2017-09-01 12:39:50 |

+---------------------+---------------------+

1 row in set (0.00 sec)

 

mysql> select current_timestamp ;

+---------------------+

| current_timestamp   |

+---------------------+

| 2017-09-01 12:40:01 |

+---------------------+

1 row in set (0.00 sec)

 

now()函数必须是函数调用的形式。

mysql> select now ;

ERROR 1054 (42S22): Unknown column 'now' in 'field list'

 

 

(2)独立取得当前日期和时间。

curdate()取得当前日期,curtime()取得当前时间。

mysql> select curdate(),curtime();

+------------+-----------+

| curdate()  | curtime() |

+------------+-----------+

| 2017-09-01 | 13:34:14  |

+------------+-----------+

1 row in set (0.00 sec)

 

(3)取得指定日期时间的年月日时分秒的值。

mysql> select now(),year(now()), month(now()),day(now()), hour(now()),minute(now()),second(now());

+---------------------+-------------+--------------+------------+-------------+---------------+---------------+

| now()               | year(now()) | month(now()) | day(now()) | hour(now()) | minute(now()) | second(now()) |

+---------------------+-------------+--------------+------------+-------------+---------------+---------------+

| 2017-09-01 13:48:26 |        2017 |            9 |          1 |          13 |            48 |            26 |

+---------------------+-------------+--------------+------------+-------------+---------------+---------------+

1 row in set (0.00 sec)

 

 

 

(4)等待一段时间。

sleep()函数提供了等待指定秒数的功能。

mysql> select now(); select  sleep(11); select now();

+---------------------+

| now()               |

+---------------------+

| 2017-09-01 13:54:07 |

+---------------------+

1 row in set (0.00 sec)

 

+-----------+

| sleep(11) |

+-----------+

|         0 |

+-----------+

1 row in set (11.00 sec)

 

+---------------------+

| now()               |

+---------------------+

| 2017-09-01 13:54:18 |

+---------------------+

1 row in set (0.00 sec)

 

(5)sysdate。

sysdate()取得当前日期和时间。

now()函数返回的是语句开始执行时系统当前时间;而sysdate()函数返回的是函数执行时系统当前时间。

mysql> select now(), sleep(3), now();

+---------------------+----------+---------------------+

| now()               | sleep(3) | now()               |

+---------------------+----------+---------------------+

| 2017-09-01 14:01:34 |        0 | 2017-09-01 14:01:34 |

+---------------------+----------+---------------------+

1 row in set (3.00 sec)

可以看到两个now()返回的值相同,因为都是SELECT语句开始执行时的时间。

 

mysql> select sysdate(),sleep(3),sysdate();

+---------------------+----------+---------------------+

| sysdate()           | sleep(3) | sysdate()           |

+---------------------+----------+---------------------+

| 2017-09-01 14:01:49 |        0 | 2017-09-01 14:01:52 |

+---------------------+----------+---------------------+

1 row in set (3.01 sec)

两个sysdate()返回的时间并不相同,相隔了3秒钟。

 

 

 mysql> insert into tb_kind values(null,"体育新闻",1,"0-1");
 Query OK, 1 row affected

mysql> select * from tb_kind;
+----+----------+-----+---------+
| id | pname | pid | path |
+----+----------+-----+---------+
| 1 | 新闻 | 0 | 0 |
| 2 | 视频 | 0 | 0 |
| 3 | 图片 | 0 | 0 |
| 4 | 博客 | 0 | 0 |
| 5 | 体育新闻 | 1 | 0-1 |
| 6 | 娱乐新闻 | 1 | 0-1 |
| 7 | 财经新闻 | 1 | 0-1 |
| 8 | 篮球新闻 | 5 | 0-1-5 |
| 9 | 足球新闻 | 5 | 0-1-5 |
| 10 | NBA | 8 | 0-1-5-8 |
| 11 | CBA | 8 | 0-1-5-8 |
+----+----------+-----+---------+
11 rows in set

数据表的常用指令

1. DATE

DATE类型只包含日期,格式:'YYYY-MM-DD'。范围从'0000-01-01'到'9999-12-31'。其中年月日只能输入有效的年月日。

 

mysql> create table d1 ( id  date primary key);

Query OK, 0 rows affected (0.02 sec)

 

mysql> show create table d1;

+-------+-------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                          |

+-------+-------------------------------------------------------------------------------------------------------+

| d1    | CREATE TABLE `d1` (

  `id` date NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+-------+-------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

 

mysql> insert into d1 values ('0001-01-01');

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from d1;

+------------+

| id         |

+------------+

| 0001-01-01 |

+------------+

1 row in set (0.00 sec)

 

 

MySQL将对日期的年月日进行合法性校验。

mysql> insert into d1 values ('2017-02-29');

ERROR 1292 (22007): Incorrect date value: '2017-02-29' for column 'id' at row 1

mysql> insert into d1 values ('2017-02-28');

Query OK, 1 row affected (0.01 sec)

 

年的范围为0000到9999。

ERROR 1292 (22007): Incorrect date value: '20170-02-28' for column 'id' at row 1

mysql> insert into d1 values ('10000-02-28');

ERROR 1292 (22007): Incorrect date value: '10000-02-28' for column 'id' at row 1

mysql> insert into d1 values ('9999-02-28');

Query OK, 1 row affected (0.00 sec)

 

 

 

 

 mysql> select * from tb_kind;
 +----+----------+-----+-------+
 | id | pname    | pid | path  |
 +----+----------+-----+-------+
 |  1 | 新闻     |   0 | 0     |
 |  2 | 视频     |   0 | 0     |
 |  3 | 图片     |   0 | 0     |
 |  4 | 博客     |   0 | 0     |
 |  5 | 体育新闻 |   1 | 0-1   |
 |  6 | 娱乐新闻 |   1 | 0-1   |
 |  7 | 财经新闻 |   1 | 0-1   |
 |  8 | 篮球新闻 |   5 | 0-1-5 |
 |  9 | 足球新闻 |   5 | 0-1-5 |
 +----+----------+-----+-------+
 9 rows in set

mysql> insert into tb_kind values(null,"体育新闻",1,"0-1");
Query OK, 1 row affected

 

6. 两位数年份

对于日期相关类型,如果输入两位数年份,则根据年份所在的范围识别为20XX或19XX。

(1)00到69:结果为20XX。

(2)70到99:结果为19XX。

 

mysql> select cast( '17-09-01' as date) as "17-09-01", cast('69-09-01' as date) as "69-09-01" ,  cast('70-09-01' as date ) as "70-09-01";

+------------+------------+------------+

| 17-09-01   | 69-09-01   | 70-09-01   |

+------------+------------+------------+

| 2017-09-01 | 2069-09-01 | 1970-09-01 |

+------------+------------+------------+

1 row in set (0.00 sec)

 

 

您可能感兴趣的文章:

  • thinkphp实现无限分类(使用递归)
  • 解析thinkphp的左右值无限分类
  • PHP+Mysql树型结构(无限分类)数据库设计的2种方式实例
  • PHP无限分类的类
  • PHP 无限分类三种方式 非函数的递归调用!
  • php递归方法实现无限分类实例代码
  • php递归实现无限分类生成下拉列表的函数
  • PHP递归遍历多维数组实现无限分类的方法
  • PHP实现的无限分类类库定义与用法示例【基于thinkPHP】

mysql> select * from db_kind;
ERROR 1146 : Table 'db_kind.db_kind' doesnot exist
mysql> select * from tb
_kind;
+----+----------+-----+------+
| id | pname | pid | path |
+----+----------+-----+------+
| 1 | 新闻 | 0 | 0 |
| 2 | 视频 | 0 | 0 |
| 3 | 图片 | 0 | 0 |
| 4 | 博客 | 0 | 0 |
| 5 | 体育新闻 | 1 | 0-1 |
| 6 | 娱乐新闻 | 1 | 0-1 |
| 7 | 财经新闻 | 1 | 0-1 |
+----+----------+-----+------+
7 rows in set
mysql> insert into tb_kind values(null,"篮球新闻",5,"0-1-5");
Query OK, 1 row affected

数据库常用的指令:

2. TIME

时间类型使用TIME表示。格式'HH:MM:SS'。

 

mysql> create table d2 ( id time primary key);

Query OK, 0 rows affected (0.04 sec)

 

mysql> show create table d2;

+-------+-------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                          |

+-------+-------------------------------------------------------------------------------------------------------+

| d2    | CREATE TABLE `d2` (

  `id` time NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+-------+-------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

 

 

mysql> insert into d2 values ('00:00:00');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into d2 values ('23:59:59');

Query OK, 1 row affected (0.01 sec)

 

mysql> insert into d2 values ('24:00:00');

Query OK, 1 row affected (0.01 sec)

 

mysql> insert into d2 values ('25:00:00');

Query OK, 1 row affected (0.02 sec)

 

mysql> insert into d2 values ('00:60:60');

ERROR 1292 (22007): Incorrect time value: '00:60:60' for column 'id' at row 1

mysql> insert into d2 values ('00:00:60');

ERROR 1292 (22007): Incorrect time value: '00:00:60' for column 'id' at row 1

mysql> select * from d2;

+----------+

| id       |

+----------+

| 00:00:00 |

| 23:59:59 |

| 24:00:00 |

| 25:00:00 |

+----------+

4 rows in set (0.00 sec)

 

 

TIME类型的取值范围为:'00:00:00'到'838:59:59'。

mysql> insert into d2 values ('838:59:59');

Query OK, 1 row affected (0.01 sec)

 

mysql> insert into d2 values ('839:00:00');

ERROR 1292 (22007): Incorrect time value: '839:00:00' for column 'id' at row 1

 

 mysql> select * from db_kind;
 ERROR 1146 : Table 'db_kind.db_kind' doesnot exist
 mysql> select * from tb
 _kind;
 +----+----------+-----+------+
 | id | pname    | pid | path |
 +----+----------+-----+------+
 |  1 | 新闻     |   0 | 0    |
 |  2 | 视频     |   0 | 0    |
 |  3 | 图片     |   0 | 0    |
 |  4 | 博客     |   0 | 0    |
 |  5 | 体育新闻 |   1 | 0-1  |
 |  6 | 娱乐新闻 |   1 | 0-1  |
 |  7 | 财经新闻 |   1 | 0-1  |
 +----+----------+-----+------+
 7 rows in set
 mysql> insert into tb_kind values(null,"篮球新闻",5,"0-1-5");
 Query OK, 1 row affected

 代码如下

  1. 创建用户
    1. mysql>grant 权限(select,insert,update,delete) on  数据库.数据表  to  用户名@电脑的地址  identified by "用户的密码" 
      1. 权限  【有四种权限】
        1. select      查询
        2. insert      插入
        3. update    修改
        4. delete  删除 
      2. 数据库.数据表 【有四种写法】  
        1. *.*           表示所有数据库与所有数据表          所有
        2. *.user          表示所有数据库的user表               某个表的数据库
        3. mysql.*        表示数据库mysql的所有数据表       某个数据库
        4. mysql.user  表示数据库mysql的数据库user表  某个数据库的某个数据表
      3. 用户名@电脑的地址
        1. 用户名          要创建的用户名称,不能跟原先的用户名相同
        2. 电脑的地址   mysql软件所在的电脑连接
          1. 在自己电脑使用时,用 127.0.0.1
          2. 与服务器的mysql交互时,用服务器的IP  
    2. mysql> grant select,insert,update,delete on mysql.* to ceosows@127.0.0.1 identif ied by "ceosows";
      Query OK, 0 rows affected (0.01 sec)
    3. mysql> grant select,insert,update,delete on *.* to ceosows@127.0.0.1 identif ied by "ceosows";
      Query OK, 0 rows affected (0.01 sec)
  2. 查看用户
    1. select * from 数据库.数据表
    2. select * from mysql.user            

 //////////////
   //////无限分类的数据库设计及样例
   //////////////
   mysql> create database db_kind;
   Query OK, 1 row affected

php处理分类源码

用户

 mysql> insert into tb_kind values(null,"足球新闻",5,"0-1-5");
 Query OK, 1 row affected

mysql> select * from tb_kind;
+----+----------+-----+-------+
| id | pname | pid | path |
+----+----------+-----+-------+
| 1 | 新闻 | 0 | 0 |
| 2 | 视频 | 0 | 0 |
| 3 | 图片 | 0 | 0 |
| 4 | 博客 | 0 | 0 |
| 5 | 体育新闻 | 1 | 0-1 |
| 6 | 娱乐新闻 | 1 | 0-1 |
| 7 | 财经新闻 | 1 | 0-1 |
| 8 | 篮球新闻 | 5 | 0-1-5 |
| 9 | 足球新闻 | 5 | 0-1-5 |
+----+----------+-----+-------+
9 rows in set

  1. 删除表中的数据
    1. mysql>delect from 数据表  where 条件限制 ;  
    2. mysql> delete from registered where id=1;
      Query OK, 1 row affected (0.01 sec)  //删除成功
    3. 使用5,查看删除的效果
      1. mysql> select * from registered;
        +----+----------+----------+------------+
        | id    | username | password | repassword |
        +----+----------+----------+------------+
        |  2    | ceo         | aswe1322 | aswe1322   |
        |  3    | jk           | qazxsw12 | qazxsw12   |
        +----+----------+----------+------------+
        2 rows in set (0.00 sec)   //id为1的数据被成功删除了  
  2. 修改表中的数据
    1. mysql>update 数据表 set (要修改数据的)字段=修改后的数据   where  条件限制
      1. mysql> update registered set username='sowsceo' where id=2;
        Query OK, 1 row affected (0.01 sec)                          //修改成功
        Rows matched: 1  Changed: 1  Warnings: 0     //修改的数量
      2. 查看修改后的效果
        1. mysql> select * from registered;
          +----+----------+----------+------------+
          | id | username | password | repassword |
          +----+----------+----------+------------+
          |  2 | sowsceo  | aswe1322 | aswe1322   |      //username 从ceo 变成 sowsceo
          |  3 | jk       | qazxsw12 | qazxsw12   |
          +----+----------+----------+------------+
          2 rows in set (0.00 sec)   

 mysql> insert into tb_kind values(null,"视频",0,0);
 Query OK, 1 row affected

mysql> insert into tb_kind values(null,"娱乐新闻",1,"0-1");
Query OK, 1 row affected

船停在港湾是很安全的,但那不是造船的目的!

 mysql> insert into tb_kind values(null,"CBA",8,"0-1-5-8");
 Query OK, 1 row affected

//////////////
//////无限分类的数据库设计及样例
//////////////
mysql> create database db_kind;
Query OK, 1 row affected

 mysql> insert into tb_kind values(null,"财经新闻",1,"0-1");
 Query OK, 1 row affected

mysql> use db_kind;
Database changed
mysql> create table tb_kind(
-> id int not null auto_increment primary key,
-> pid int,
-> path varchar(200)
-> );
Query OK, 0 rows affected

上面的代码中其实中间有空格的输入效果还是非常不错的,请大家本地测试。因编辑器问题导致排版混乱。

mysql> select concat(path,"-",id) as abs from tb_kind order by abs.path;
ERROR 1054 : Unknown column 'abs.path' in 'order clause'
mysql> select concat(path,"-",id) as abs from tb_kind order by abs

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" ";
<html xmlns=";
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>无标题文档</title>
</head>
<body>
<!--显示结果
新闻
体育新闻
篮球新闻
NBA
CBA
足球新闻
娱乐新闻
财经新闻
视频
图片
博客
-->
<?
$conn=mysql_connect("localhost","root","root");
mysql_select_db("db_kind");
mysql_query("set names utf8");
$sql="select concat(path,'-',id) as abspath,id,pname,path from tb_kind order by abspath";
$rs=mysql_query($sql);
while($result=mysql_fetch_assoc($rs)){
$num=count(explode("-",$result[path]))-1;
$new_str=str_repeat("---",$num);
echo $new_str.$result[pname];
echo "<br>";
}
$str=str_repeat("=",10);
echo $str;
$num=count(explode("-","0-1-5-8"))-1;
echo $num;
?>
</body>
</html>

mysql> insert into tb_kind values(null,"新闻",0,0);
Query OK, 1 row affected

 mysql> insert into tb_kind values(null,"图片",0,0);
 Query OK, 1 row affected

+------------+
| abs |
+------------+
| 0-1 |
| 0-1-5 |
| 0-1-5-8 |
| 0-1-5-8-10 |
| 0-1-5-8-11 |
| 0-1-5-9 |
| 0-1-6 |
| 0-1-7 |
| 0-2 |
| 0-3 |
| 0-4 |
+------------+
11 rows in set
mysql> select concat(path,"-",id) as,id,name,path abs from tb_kind order by abs;
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'id,name,path abs from tb_kind order by abs' at line 1
mysql> select concat(path,"-",id) as abs,
id,pname,path abs from tb_kind order by abs;
+------------+----+----------+---------+
| abs | id | pname | abs |
+------------+----+----------+---------+
| 0-1 | 1 | 新闻 | 0 |
| 0-1-5 | 5 | 体育新闻 | 0-1 |
| 0-1-5-8 | 8 | 篮球新闻 | 0-1-5 |
| 0-1-5-8-10 | 10 | NBA | 0-1-5-8 |
| 0-1-5-8-11 | 11 | CBA | 0-1-5-8 |
| 0-1-5-9 | 9 | 足球新闻 | 0-1-5 |
| 0-1-6 | 6 | 娱乐新闻 | 0-1 |
| 0-1-7 | 7 | 财经新闻 | 0-1 |
| 0-2 | 2 | 视频 | 0 |
| 0-3 | 3 | 图片 | 0 |
| 0-4 | 4 | 博客 | 0 |
+------------+----+----------+---------+
11 rows in set
mysql>

下面一段代码是创建相应数据库的sql代码:

 

 mysql> select concat(path,"-",id) as abs from tb_kind order by abs.path;
 ERROR 1054 : Unknown column 'abs.path' in 'order clause'
 mysql> select concat(path,"-",id) as abs from tb_kind order by abs

mysql> insert into tb_kind values(null,"CBA",8,"0-1-5-8");
Query OK, 1 row affected

 mysql> insert into tb_kind values(null,"娱乐新闻",1,"0-1");
 Query OK, 1 row affected

mysql> select concat(path,"-",id) from tb_kind;
+---------------------+
| concat(path,"-",id) |
+---------------------+
| 0-1 |
| 0-2 |
| 0-3 |
| 0-4 |
| 0-1-5 |
| 0-1-6 |
| 0-1-7 |
| 0-1-5-8 |
| 0-1-5-9 |
| 0-1-5-8-10 |
| 0-1-5-8-11 |
+---------------------+
11 rows in set

 mysql> insert into tb_kind values(null,"新闻",0,0);
 Query OK, 1 row affected

mysql> insert into tb_kind values(null,"足球新闻",5,"0-1-5");
Query OK, 1 row affected

下面是php源文件:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">
<html xmlns="">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>无标题文档</title>
</head>
<body>
<!--显示结果
新闻
体育新闻
篮球新闻
NBA
CBA
足球新闻
娱乐新闻
财经新闻
视频
图片
博客
-->
<?
$conn=mysql_connect("localhost","root","root");
mysql_select_db("db_kind");
mysql_query("set names utf8");
$sql="select concat(path,'-',id) as abspath,id,pname,path from tb_kind order by abspath";
$rs=mysql_query($sql);
while($result=mysql_fetch_assoc($rs)){
$num=count(explode("-",$result[path]))-1;
$new_str=str_repeat("---",$num);
echo $new_str.$result[pname];
echo "<br>";
}
$str=str_repeat("=",10);
echo $str;
$num=count(explode("-","0-1-5-8"))-1;
echo $num;
?>
</body>
</html>

 mysql> insert into tb_kind values(null,"博客",0,0);
 Query OK, 1 row affected

mysql> select concat(path,"-",id) from tb_kind;
+---------------------+
| concat(path,"-",id) |
+---------------------+
| 0-1 |
| 0-2 |
| 0-3 |
| 0-4 |
| 0-1-5 |
| 0-1-6 |
| 0-1-7 |
| 0-1-5-8 |
| 0-1-5-9 |
| 0-1-5-8-10 |
| 0-1-5-8-11 |
+---------------------+
11 rows in set

复制代码 代码如下:

mysql> insert into tb_kind values(null,"视频",0,0);
Query OK, 1 row affected

   mysql> use db_kind;
   Database changed
   mysql> create table tb_kind(
     -> id int not null auto_increment primary key,
     -> pid int,
     -> path varchar(200)
     -> );
 Query OK, 0 rows affected

复制代码 代码如下:

 mysql> select concat(path,"-",id) from tb_kind;
 +---------------------+
 | concat(path,"-",id) |
 +---------------------+
 | 0-1                 |
 | 0-2                 |
 | 0-3                 |
 | 0-4                 |
 | 0-1-5               |
 | 0-1-6               |
 | 0-1-7               |
 | 0-1-5-8             |
 | 0-1-5-9             |
 | 0-1-5-8-10          |
 | 0-1-5-8-11          |
 +---------------------+
 11 rows in set

版权声明:本文由澳门金莎娱乐发布于计算机网络,转载请注明出处:比较简单实用的PHP无限分类源码分享,风马一族_mysql