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

r囧r小猫 2023-01-11 04:57 104阅读 0赞

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)

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

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关最全的mysql常用语句大全

相关 MySQL[练习]SQL 语句

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

相关 MySQL语句练习

相关史上最全的MYSQL备份方法

相关《史上最全最详细MySQL初级系列教程》

相关 Mysql语句练习

相关 MySQL:sql语句强化练习

相关史上最全 MySQL JOIN 查询

相关史上最全mysql面试大全