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, 感谢原作者分享。