mysql与其他数据库_Mysql与其他数据库一些不同地方

1.Insert Set语句

mysql> create table wison_test1(id int,name varchar(10));

Query OK, 0 rows affected (0.10 sec)

mysql> insert into wison_test1 set id=1,name='wison';-------相对于后一条insert语句,返回的提示信息少了一行

Query OK, 1 row affected (0.08 sec)

mysql> select * from wison_test1;

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

| id | name |

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

| 1 | wison |

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

1 row in set (0.00 sec)

mysql> insert into wison_test1 select 2,'Wison';

Query OK, 1 row affected (0.10 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from wison_test1;

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

| id | name |

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

| 1 | wison |

| 2 | Wison |

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

2 rows in set (0.00 sec)

2.数据类型带长度

mysql> create table wison_test2(id int(5));

Query OK, 0 rows affected (0.12 sec)

mysql> insert into wison_test2 select 1;

Query OK, 1 row affected (0.10 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from wison_test2;

+------+

| id |

+------+

| 1 |

+------+

1 row in set (0.00 sec)

mysql> alter table wison_test2 modify id int(5) zerofill;

Query OK, 1 row affected (0.58 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from wison_test2;

+-------+

| id |

+-------+

| 00001 |

+-------+

1 row in set (0.00 sec)

mysql> insert into wison_test2 select 1111111;

Query OK, 1 row affected (0.10 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from wison_test2;

+---------+

| id |

+---------+

| 00001 |

| 1111111 |

+---------+

2 rows in set (0.00 sec)

3.BIT类型---直接读取会查询不出结果,需要使用hex()或者bin()

mysql> create table wison_test3(id bit(2));

Query OK, 0 rows affected (0.11 sec)

mysql> insert into wison_test3 select 1;

Query OK, 1 row affected (0.07 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from wison_test3;

+------+

| id |

+------+

| |

+------+

1 row in set (0.00 sec)

mysql> select hex(id) from wison_test3;

+---------+

| hex(id) |

+---------+

| 1 |

+---------+

1 row in set (0.00 sec)

mysql> select bin(id) from wison_test3;

+---------+

| bin(id) |

+---------+

| 1 |

+---------+

1 row in set (0.00 sec)

4.TIMESTAMP类型--在Mysql中会根据当前时区不同返回不同的值

mysql> create table wison_test4(id1 timestamp,id2 datetime);

Query OK, 0 rows affected (0.09 sec)

mysql> show variables like 'time_zone';

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

| Variable_name | Value |

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

| time_zone | SYSTEM |

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

1 row in set (0.00 sec)

mysql> insert into wison_test4 select now(),now();

Query OK, 1 row affected (0.07 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from wison_test4;

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

| id1 | id2 |

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

| 2014-04-29 23:05:03 | 2014-04-29 23:05:03 |

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

1 row in set (0.00 sec)

mysql> set time_zone='+9:00';

Query OK, 0 rows affected (0.00 sec)

mysql> select * from wison_test4;

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

| id1 | id2 |

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

| 2014-04-30 15:05:03 | 2014-04-29 23:05:03 |

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

1 row in set (0.00 sec)

mysql> set time_zone='-7:00';

Query OK, 0 rows affected (0.00 sec)

mysql> select * from wison_test4;

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

| id1 | id2 |

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

| 2014-04-29 23:05:03 | 2014-04-29 23:05:03 |

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

1 row in set (0.00 sec)

5.CHAR数据类型---CHAR会自动将后面的空格截断,VARCHAR会保留空格

mysql> create table wison_test5(c1 char(5),c2 varchar(5));

Query OK, 0 rows affected (0.11 sec)

mysql> insert into wison_test5 select 'a ','a ';

Query OK, 1 row affected (0.08 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from wison_test5;

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

| c1 | c2 |

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

| a | a |

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

1 row in set (0.00 sec)

mysql> select length(c1),length(c2) from wison_test5;

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

| length(c1) | length(c2) |

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

| 1 | 3 |

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

1 row in set (0.00 sec)

mysql> select concat(c1,'+'),concat(c2,'+') from wison_test5;

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

| concat(c1,'+') | concat(c2,'+') |

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

| a+ | a + |

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

1 row in set (0.00 sec)

6.ENUM类型---插入的值可以是在枚举的列表中,可以写明值,或者对应的顺序编号(比如3的意思就是第三个值),NULL(插入之后仍为NULL),传入不存在的值插入的是空格

mysql> create table wison_test6(country ENUM('CN','US','TW','RU','BR'));

Query OK, 0 rows affected (0.09 sec)

mysql> insert into wison_test6 values ('CN'),('US'),('1'),('3'),('4'),(2),(NULL);

Query OK, 7 rows affected (0.06 sec)

Records: 7 Duplicates: 0 Warnings: 0

mysql> select * from wison_test6;

+---------+

| country |

+---------+

| CN |

| US |

| CN |

| TW |

| RU |

| US |

| NULL |

+---------+

7 rows in set (0.00 sec)

mysql> insert into wison_test6 select 'CA';

Query OK, 1 row affected, 1 warning (0.06 sec)

Records: 1 Duplicates: 0 Warnings: 1

mysql> show warnings;

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

| Level | Code | Message |

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

| Warning | 1265 | Data truncated for column 'country' at row 1 |

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

1 row in set (0.00 sec)

mysql> select * from wison_test6;

+---------+

| country |

+---------+

| CN |

| US |

| CN |

| TW |

| RU |

| US |

| NULL |

| |

+---------+

8 rows in set (0.00 sec)

另一个例子----可以看到当枚举值背身为数字时,当insert的value没包含'',则表示是第几个枚举值,否则插入的值为insert的对应值.

mysql> create table wison_test7(id ENUM('1','2','4','3','5'));

Query OK, 0 rows affected (0.07 sec)

mysql> insert into wison_test7 values('1'),('3'),(3),('4'),(4);

Query OK, 5 rows affected (0.07 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from wison_test7;

+------+

| id |

+------+

| 1 |

| 3 |

| 4 |

| 4 |

| 3 |

+------+

5 rows in set (0.00 sec)

7.在Mysql中，如果列上有索引，无需先Drop Index，可直接修改列。这点与MSSQL不一致。如下：

mysql> create table test1(id int,name char(5));

Query OK, 0 rows affected (0.30 sec)

mysql> create index ix_test1_name on test1(name);

Query OK, 0 rows affected (0.21 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table test1;

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

| Table | Create Table |

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

| test1 | CREATE TABLE `test1` (

`id` int(11) DEFAULT NULL,

`name` char(5) DEFAULT NULL,

KEY `ix_test1_name` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

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

1 row in set (0.00 sec)

mysql> alter table test1 modify name char(10);

Query OK, 0 rows affected (0.54 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table test1;

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

| Table | Create Table |

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

| test1 | CREATE TABLE `test1` (

`id` int(11) DEFAULT NULL,

`name` char(10) DEFAULT NULL,

KEY `ix_test1_name` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

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

1 row in set (0.00 sec)