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)

本文出自：http://letong.me, 原文地址：http://letong.me/mysql_basics.html, 感谢原作者分享。