mysql实践教程 教案_Mysql基础操作实践

介绍Mysql基本的增删查改语句的实践，没有难度，孰能生巧。 首先我们随便创建一张库和表 mysql create database test1;mysql create table disc_info ( id int(5) NOT NULL auto_increment, disc_name varchar(20) NOT NULL, disc_desc varchar(255), PRIMAR

介绍Mysql基本的增删查改语句的实践，没有难度，孰能生巧。

首先我们随便创建一张库和表

mysql> create database test1;

mysql> create table disc_info ( id int(5) NOT NULL auto_increment, disc_name varchar(20) NOT NULL, disc_desc varchar(255), PRIMARY KEY (`id`) );

Query OK, 0 rows affected (0.04 sec)

看看表结构

mysql> desc disc_info;

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

| Field | Type | Null | Key | Default | Extra |

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

| id | int(5) | NO | PRI | NULL | auto_increment |

| disc_name | varchar(20) | NO | | NULL | |

| disc_desc | varchar(255) | YES | | NULL | |

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

3 rows in set (0.00 sec)

新增数据

mysql> insert into disc_info (`disc_name`,`disc_desc`) VALUES ('codebabe','welcome to codebabes.com');

Query OK, 1 row affected (0.00 sec)

查看数据

查看所有数据

mysql> select * from disc_info;

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

| id | disc_name | disc_desc |

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

| 1 | codebabe | welcome to codebabes.com |

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

1 row in set (0.00 sec)

查看指定数据列

mysql> select disc_name,disc_desc from disc_info;

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

| disc_name | disc_desc |

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

| codebabe | welcome to codebabes.com |

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

1 row in set (0.00 sec)

多表查询

mysql> select discinfo2.disc_name,discinfo.disc_name from discinfo,discinfo2;

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

| disc_name | disc_name |

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

| letong | codeman |

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

1 row in set (0.00 sec)

join查询

mysql> select * from discinfo join discinfo2 on discinfo.id=0;

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

| id | disc_name | disc_desc | id | disc_name | disc_desc |

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

| 0 | codebabe | welcome to codebabes.com | 1 | letong | welcome to letong.me |

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

1 row in set (0.00 sec)

修改数据

修改指定数据

mysql> update disc_info SET disc_name='codeman' where id=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

更改表名

mysql> alter table disc_info rename discinfo;

Query OK, 0 rows affected (0.01 sec)

mysql> show tables;

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

| Tables_in_kaoshi |

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

| discinfo |

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

1 row in set (0.00 sec)

修改表字段

mysql> alter table discinfo change id id int(6) not null;

Query OK, 1 row affected (0.01 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> desc discinfo;

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

| Field | Type | Null | Key | Default | Extra |

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

| id | int(6) | NO | PRI | NULL | |

| disc_name | varchar(20) | NO | | NULL | |

| disc_desc | varchar(255) | YES | | NULL | |

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

3 rows in set (0.00 sec)

删除数据

mysql> delete from discinfo where disc_name='codeman';

Query OK, 1 row affected (0.00 sec)

mysql> select * from discinfo;

Empty set (0.00 sec)