mysql语句联系_史上最全Mysql语句实例+练习

mysql>

mysql>

--查看表结构

mysql> desc liuyan;

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

| Field |

Type | Null |

Key | Default | Extra |

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

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| title |

varchar(32) | NO | |

NULL | |

| author |

varchar(32) | NO | |

wu | |

| addtime | datetime |

YES | |

NULL | |

| content | text |

NO | |

NULL | |

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

5 rows in set (0.02 sec)

--采用标准方式添加一条数据

mysql> insert into

liuyan(id,title,author,addtime,content)

->

values(1,'news','zhangsan','2012-05-22 12:32:50','hello

mysql');

Query OK, 1 row affected (0.01 sec)

--查看留言表中的所有数据。(*表示所有列)

mysql> select * from liuyan;

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

| id | title | author |

addtime |

content |

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

| 1 |

news | zhangsan | 2012-05-22

12:32:50 | hello mysql |

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

1 row in set (0.00 sec)

mysql>

--添加数据(字段名的顺序可以颠倒)

mysql> insert into

liuyan(id,content,addtime,title,author)

->

values(2,'aaaaa','2012-12-24

12:12:12','info','lisi');

Query OK, 1 row affected (0.00 sec)

--只指定部分字段名来添加数据

mysql> insert into

liuyan(title,addtime,content)

->

values('test','2012-12-22','hello');

Query OK, 1 row affected (0.00 sec)

mysql> desc liuyan;

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

| Field |

Type | Null |

Key | Default | Extra |

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

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| title |

varchar(32) | NO | |

NULL | |

| author |

varchar(32) | NO | |

wu | |

| addtime | datetime |

YES | |

NULL | |

| content | text |

NO | |

NULL | |

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

5 rows in set (0.00 sec)

--不指定字段名添加数据(values中要给所有字段赋值，并顺序一致)

mysql> insert into liuyan

->

values(null,'demo','wangwu','2012-09-10

12:34:56','ddddd');

Query OK, 1 row affected (0.00 sec)

mysql> select * from liuyan;

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

| id | title | author |

addtime |

content |

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

| 1 |

news | zhangsan | 2012-05-22

12:32:50 | hello mysql |

| 2 |

info | lisi | 2012-12-24

12:12:12 | aaaaa |

| 3 |

test | wu |

2012-12-22 00:00:00 | hello |

| 4 |

demo | wangwu | 2012-09-10 12:34:56 | ddddd |

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

4 rows in set (0.00 sec)

mysql>

---批量添加3条数据

mysql> insert into liuyan

->

values(null,'tt1','lisi1','2012-02-03

12:21:34','dddd'),

->

(null,'tt2','lisi2','2012-02-03 12:21:34','eeee'),

->

(null,'tt3','lisi3','2012-02-03 12:21:34','ffff');

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates:

0 Warnings: 0

mysql> select * from liuyan;

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

| id | title | author |

addtime |

content |

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

| 1 |

news | zhangsan | 2012-05-22

12:32:50 | hello mysql |

| 2 |

info | lisi | 2012-12-24

12:12:12 | aaaaa |

| 3 |

test | wu |

2012-12-22 00:00:00 | hello |

| 4 |

demo | wangwu | 2012-09-10 12:34:56 | ddddd |

| 5 | tt1 | lisi1 |

2012-02-03 12:21:34 | dddd |

| 6 | tt2 | lisi2 |

2012-02-03 12:21:34 | eeee |

| 7 | tt3 | lisi3 |

2012-02-03 12:21:34 | ffff |

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

7 rows in set (0.00 sec)

mysql>

mysql> create table demo(

-> d1 tinyint not

null,

-> d2 int(4) zerofill not

null,

-> d3 double(4,2) not

null,

-> d4 varchar(4) not

null);

Query OK, 0 rows affected (0.05 sec)

mysql> desc demo;

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

| Field | Type | Null | Key

| Default | Extra |

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

| d1 |

tinyint(4) |

NO | |

NULL | |

| d2 | int(4) unsigned

zerofill | NO | |

NULL | |

| d3 |

double(4,2) |

NO | |

NULL | |

| d4 |

varchar(4) |

NO | |

NULL | |

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

4 rows in set (0.00 sec)

mysql> show create table demo \G;

*************************** 1. row

***************************

Table: demo

Create Table: CREATE TABLE `demo` (

`d1` tinyint(4) NOT

NULL,

`d2` int(4) unsigned zerofill

NOT NULL,

`d3` double(4,2) NOT

NULL,

`d4` varchar(4) NOT

NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

ERROR:

No query specified

mysql>mysql> desc demo;

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

| Field | Type | Null | Key

| Default | Extra |

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

| d1 |

tinyint(4) |

NO | |

NULL | |

| d2 | int(4) unsigned

zerofill | NO | |

NULL | |

| d3 |

double(4,2) |

NO | |

NULL | |

| d4 |

varchar(4) |

NO | |

NULL | |

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

4 rows in set (0.00 sec)

mysql> insert into demo

values(11,22,123.456,"abcdef");

Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> select * from demo;

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

| d1 | d2 |

d3 | d4 |

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

| 11 | 0022 | 99.99 | abcd |

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

1 row in set (0.00 sec)

mysql> insert into demo

values(11,12345,12.3456,"abc");

Query OK, 1 row affected (0.00 sec)

mysql> select * from demo;

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

| d1 | d2 |

d3 | d4 |

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

| 11 | 0022 | 99.99 | abcd

|

| 11 | 12345 | 12.35 |

abc |

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

2 rows in set (0.00 sec)

mysql> insert into demo

values(128,22,123.456,"abcdef");

Query OK, 1 row affected, 3 warnings (0.00 sec)

mysql> select * from demo;

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

| d1 | d2 | d3 |

d4 |

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

| 11

| 0022 | 99.99 | abcd

|

| 11 | 12345 | 12.35 |

abc |

| 127 | 0022 | 99.99 | abcd

|

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

3 rows in set (0.00 sec)