Mysql语句实例+练习分享
mysql> select 10;
+----+
| 10 |
+----+
| 10 |
+----+
1 row in set (0.00 sec)
mysql> select 10+20+30;
+----------+
| 10+20+30 |
+----------+
| 60 |
+----------+
1 row in set (0.02 sec)
mysql> select "aa"+"bb";
+-----------+
| "aa"+"bb" |
+-----------+
| 0 |
+-----------+
1 row in set, 2 warnings (0.00 sec)
mysql> select "10a"+"bb";
+------------+
| "10a"+"bb" |
+------------+
| 10 |
+------------+
1 row in set, 2 warnings (0.00 sec)
mysql> select "10a"+"bb20";
+--------------+
| "10a"+"bb20" |
+--------------+
| 10 |
+--------------+
1 row in set, 2 warnings (0.00 sec)
mysql>
mysql> select 10*"2a";
+---------+
| 10*"2a" |
+---------+
| 20 |
+---------+
1 row in set, 1 warning (0.00 sec)
mysql> select "3a"+"4b";
+-----------+
| "3a"+"4b" |
+-----------+
| 7 |
+-----------+
1 row in set, 2 warnings (0.00 sec)
mysql> select "3a"+"4e2b";
+-------------+
| "3a"+"4e2b" |
+-------------+
| 403 |
+-------------+
1 row in set, 2 warnings (0.00 sec)
mysql> select "3a"+"4.23b";
+--------------+
| "3a"+"4.23b" |
+--------------+
| 7.23 |
+--------------+
1 row in set, 2 warnings (0.00 sec)
mysql> select 12=20;
+-------+
| 12=20 |
+-------+
| 0 |
+-------+
1 row in set (0.00 sec)
mysql> select 0="abc";
+---------+
| 0="abc" |
+---------+
| 1 |
+---------+
1 row in set, 1 warning (0.00 sec)
mysql> select "10"="10a";
+------------+
| "10"="10a" |
+------------+
| 0 |
+------------+
1 row in set (0.00 sec)
mysql> select 10>=1 and 10<=20;
+------------------+
| 10>=1 and 10<=20 |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
mysql> select 10 between 1 and 20;
+---------------------+
| 10 between 1 and 20 |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.00 sec)
mysql> select 1<<3;
+------+
| 1<<3 |
+------+
| 8 |
+------+
1 row in set (0.02 sec)
mysql> select 1<<10;
+-------+
| 1<<10 |
+-------+
|1024 |
+-------+
1 row in set (0.00 sec)
mysql> select 1<<20;
+---------+
| 1<<20 |
+---------+
| 1048576 |
+---------+
1 row in set (0.00 sec)
mysql> select 1|2;
+-----+
| 1|2 |
+-----+
| 3 |
+-----+
1 row in set (0.00 sec)
mysql> select 1&2;
+-----+
| 1&2 |
+-----+
| 0 |
+-----+
1 row in set (0.00 sec)
mysql> select 5&6;
+-----+
| 5&6 |
+-----+
| 4 |
+-----+
1 row in set (0.00 sec)
--创建数据库
mysql> create database fooknet;
Query OK, 1 row affected (0.00 sec)
--查看当前用户可以看到所有数据库
mysql> show databases;
+----------------------+
| Database |
+----------------------+
| information_schema |
| test |
| mysql |
| fooknet |
+----------------------+
83 rows in set (0.75 sec)
--删除数据库
mysql> drop database fooknet;
Query OK, 0 rows affected (0.03 sec)
mysql> create database fooknet;
Query OK, 1 row affected (0.00 sec)
mysql>
-- 选择进入fooknet数据库
mysql> use fooknet;
Database changed
--查看当前你在哪个数据库下
mysql> select database();
+------------+
| database() |
+------------+
| fooknet |
+------------+
1 row in set (0.00 sec)
--查看当前数据库中的所有表格。
mysql> show tables;
Empty set (0.02 sec)
mysql>
--创建留言表格
mysql> create table liuyan(
-> id int unsigned not null auto_increment primary key,
-> title varchar(32) not null,
-> author varchar(32) not null default 'wu',
-> addtime datetime,
-> content text not null);
Query OK, 0 rows affected (0.08 sec)
--id 整型 无负号 非空 自增 主键（非空，唯一性、索引列）
--title 字串（长度32位） 非空
--author 字串（长度32位） 非空 默认值 “wu”
--查看留言表结构
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> show create table liuyan \G;
*************************** 1. row ***************************
Table: liuyan
Create Table: CREATE TABLE `liuyan` (
`id` int(10) unsigned NOT NULL auto_increment,
`title` varchar(32) NOT NULL,
`author` varchar(32) NOT NULL default 'wu',
`addtime` datetime default NULL,
`content` text NOT NULL,
PRIMARY KEY(`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
mysql>
--查看当前数据库中表格
mysql> show tables;
+------------------+
| Tables_in_fooknet |
+------------------+
| liuyan |
+------------------+
1 row in set (0.00 sec)
mysql>
--删除表格liuyan
mysql> drop table liuyan;
Query OK, 0 rows affected (0.00 sec)
mysql>CREATE TABLE `liuyan` (
-> `id` int(10) unsigned NOT NULL auto_increment,
-> `title` varchar(32) NOT NULL,
-> `author` varchar(32) NOT NULL default 'wu',
-> `addtime` datetime default NULL,
-> `content` text NOT NULL,
-> PRIMARY KEY(`id`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.05 sec)
mysql>
--添加一个测试表格
mysql> create table aa(id int not null);
Query OK, 0 rows affected (0.05 sec)
--查看当前数据库下的表格
mysql> show tables;
+------------------+
| Tables_in_fooknet |
+------------------+
| aa |
| liuyan |
+------------------+
2 rows in set (0.00 sec)
--删除一个不存在的表格
mysql> drop table bb;
ERROR 1051 (42S02): Unknown table 'bb'
--删除不存在的bb表，不报错
mysql> drop table if exists bb;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> drop table if exists aa;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table if exists aa;
Query OK, 0 rows affected, 1 warning (0.00 sec)
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: 3Duplicates: 0Warnings: 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> create table news(
-> title varchar(32) not null);
Query OK, 0 rows affected (0.08 sec)
--显示表结构
mysql> desc news;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| title | varchar(32) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
--为表添加一个主键列
mysql> alter table news add id int unsigned not null auto_increment primary key
first;
Query OK, 0 rows affected (0.09 sec)
Records: 0Duplicates: 0Warnings: 0
--显示表结构
mysql> desc news;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(32) | NO | | NULL | |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> alter table news add content text not null;
Query OK, 0 rows affected (0.06 sec)
Records: 0Duplicates: 0Warnings: 0
--显示表结构
mysql> desc news;
+---------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(32) | NO | | NULL | |
| content | text | NO | | NULL | |
+---------+------------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)
--为表添加一个作者字段 varchar32位，在标题后面
mysql> alter table news add author varchar(32) not null after title;
Query OK, 0 rows affected (0.08 sec)
Records: 0Duplicates: 0Warnings: 0
--显示表结构
mysql> desc news;
+---------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(32) | NO | | NULL | |
| author| varchar(32) | NO | | NULL | |
| content | text | NO | | NULL | |
+---------+------------------+------+-----+---------+----------------+
4 rows in set (0.02 sec)
--将字段author修改为username类型varchar(16)
mysql> alter table news change author username varchar(16) not null;
Query OK, 0 rows affected (0.08 sec)
Records: 0Duplicates: 0Warnings: 0
--显示表结构
mysql> desc news;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(32) | NO | | NULL | |
| username | varchar(16) | NO | | NULL | |
| content| text | NO | | NULL | |
+----------+------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
--删除一个字段
mysql> alter table news drop content;
Query OK, 0 rows affected (0.08 sec)
Records: 0Duplicates: 0Warnings: 0
--显示表结构
mysql> desc news;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(32) | NO | | NULL | |
| username | varchar(16) | NO | | NULL | |
+----------+------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql>
================================================================
修改表数据
================================================================
mysql>
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)
--修改留言表中author字段值为qq（所有数据）
mysql> update liuyan set author='qq';
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7Changed: 7Warnings: 0
mysql> select * from liuyan;
+----+-------+--------+---------------------+-------------+
| id | title | author | addtime | content |
+----+-------+--------+---------------------+-------------+
|1 | news| qq | 2012-05-22 12:32:50 | hello mysql |
|2 | info| qq | 2012-12-24 12:12:12 | aaaaa |
|3 | test| qq | 2012-12-22 00:00:00 | hello |
|4 | demo| qq | 2012-09-10 12:34:56 | ddddd |
|5 | tt1 | qq | 2012-02-03 12:21:34 | dddd |
|6 | tt2 | qq | 2012-02-03 12:21:34 | eeee |
|7 | tt3 | qq | 2012-02-03 12:21:34 | ffff |
+----+-------+--------+---------------------+-------------+
7 rows in set (0.00 sec)
--修改留言表中某列的某几条数据。
mysql> update liuyan set author='zhangsan' whereid in(1,3,5);
Query OK, 3 rows affected (0.03 sec)
Rows matched: 3Changed: 3Warnings: 0
mysql> select * from liuyan;
+----+-------+----------+---------------------+-------------+
| id | title | author | addtime | content |
+----+-------+----------+---------------------+-------------+
|1 | news| zhangsan | 2012-05-22 12:32:50 | hello mysql |
|2 | info| qq | 2012-12-24 12:12:12 | aaaaa |
|3 | test| zhangsan | 2012-12-22 00:00:00 | hello |
|4 | demo| qq | 2012-09-10 12:34:56 | ddddd |
|5 | tt1 | zhangsan | 2012-02-03 12:21:34 | dddd |
|6 | tt2 | qq | 2012-02-03 12:21:34 | eeee |
|7 | tt3 | qq | 2012-02-03 12:21:34 | ffff |
+----+-------+----------+---------------------+-------------+
7 rows in set (0.00 sec)
--修改某条数据的某几列。
mysql> update liuyan set title='llllll',author="lisi",content="qwertyuio"
-> where id=7;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1Changed: 1Warnings: 0
mysql> select * from liuyan;
+----+--------+----------+---------------------+-------------+
| id | title| author | addtime | content |
+----+--------+----------+---------------------+-------------+
|1 | news | zhangsan | 2012-05-22 12:32:50 | hello mysql |
|2 | info | qq | 2012-12-24 12:12:12 | aaaaa |
|3 | test | zhangsan | 2012-12-22 00:00:00 | hello |
|4 | demo | qq | 2012-09-10 12:34:56 | ddddd |
|5 | tt1 | zhangsan | 2012-02-03 12:21:34 | dddd |
|6 | tt2 | qq | 2012-02-03 12:21:34 | eeee |
|7 | llllll | lisi | 2012-02-03 12:21:34 | qwertyuio |
+----+--------+----------+---------------------+-------------+
7 rows in set (0.00 sec)
--将6号信息中的作者改为王五
mysql>update liuyan set author="wangwu" where id=6;
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)
--将demo表中d1字段的所有值都减一。
mysql> update demo set d1=d1-1;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3Changed: 3Warnings: 0
mysql> select * from demo;
+-----+-------+-------+------+
| d1| d2 | d3 | d4 |
+-----+-------+-------+------+
|10 |0022 | 99.99 | abcd |
|10 | 12345 | 12.35 | abc|
| 126 |0022 | 99.99 | abcd |
+-----+-------+-------+------+
3 rows in set (0.00 sec)
mysql> use fooknet
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| fooknet |
+------------+
1 row in set (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_fooknet |
+------------------+
| demo |
| liuyan |
| news |
+------------------+
3 rows in set (0.06 sec)
mysql> create table stu(
-> id int unsigned not null auto_increment primary key,
-> name varchar(16) unique not null,
-> sex enum('m','w') not null default 'm',
-> age tinyint unsigned ,
-> classid char(6) not null);
Query OK, 0 rows affected (0.13 sec)
mysql> create table grade(
-> id int unsigned not null auto_increment primary key,
-> sid int unsigned not null,
-> php tinyint unsigned not null,
-> mysql tinyint unsigned not null);
Query OK, 0 rows affected (0.06 sec)
mysql> desc stu;
+---------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(16) | NO | UNI | NULL | |
| sex | enum('m','w') | NO | | m | |
| age | tinyint(3) unsigned | YES| | NULL | |
| classid | char(6) | NO | | NULL | |
+---------+---------------------+------+-----+---------+----------------+
5 rows in set (0.08 sec)
mysql> desc grade;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| sid | int(10) unsigned | NO | | NULL | |
| php | tinyint(3) unsigned | NO | | NULL | |
| mysql | tinyint(3) unsigned | NO | | NULL | |
+-------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> insert into stu values(null,'zhangsan','m',20,'fooknet');
Query OK, 1 row affected (0.03 sec)
mysql> insert into stu values(null,'lisi','w',21,'fooknet'),
-> (null,'wangwu','m',20,'lamp46'),
-> (null,'zhaoliu','w',19,'lamp47'),
-> (null,'tianqi','m',22,'lamp47'),
-> (null,'xiaowang','w',24,'lamp46'),
-> (null,'aa','w',25,'fooknet'),
-> (null,'qq','w',27,'fooknet'),
-> (null,'dd','m',20,'lamp46');
Query OK, 8 rows affected (0.00 sec)
Records: 8Duplicates: 0Warnings: 0
mysql> insert into stu values(null,'pp','w',25,'fooknet');
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu;
+----+----------+-----+------+---------+
| id | name | sex | age| classid |
+----+----------+-----+------+---------+
|1 | zhangsan | m | 20 | fooknet|
|2 | lisi | w | 21 | fooknet|
|3 | wangwu | m | 20 | lamp46|
|4 | zhaoliu| w | 19 | lamp47|
|5 | tianqi | m | 22 | lamp47|
|6 | xiaowang | w | 24 | lamp46|
|7 | aa | w | 25 | fooknet|
|8 | qq | w | 27 | fooknet|
|9 | dd | m | 20 | lamp46|
| 10 | pp | w | 25 | fooknet|
+----+----------+-----+------+---------+
10 rows in set (0.00 sec)
mysql> desc grade;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| sid | int(10) unsigned | NO | | NULL | |
| php | tinyint(3) unsigned | NO | | NULL | |
| mysql | tinyint(3) unsigned | NO | | NULL | |
+-------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> insert into grade values(null,5,80,86),
-> (null,6,58,62),(null,4,90,98),
-> (null,1,87,90),(null,2,90,69),
-> (null,7,52,58),(null,3,67,86);
Query OK, 7 rows affected (0.00 sec)
Records: 7Duplicates: 0Warnings: 0
mysql> select * from stu;
+----+----------+-----+------+---------+
| id | name | sex | age| classid |
+----+----------+-----+------+---------+
|1 | zhangsan | m | 20 | fooknet|
|2 | lisi | w | 21 | fooknet|
|3 | wangwu | m | 20 | lamp46|
|4 | zhaoliu| w | 19 | lamp47|
|5 | tianqi | m | 22 | lamp47|
|6 | xiaowang | w | 24 | lamp46|
|7 | aa | w | 25 | fooknet|
|8 | qq | w | 27 | fooknet|
|9 | dd | m | 20 | lamp46|
| 10 | pp | w | 25 | fooknet|
+----+----------+-----+------+---------+
10 rows in set (0.00 sec)
mysql> select * from grade;
+----+-----+-----+-------+
| id | sid | php | mysql |
+----+-----+-----+-------+
|1 | 5 |80 | 86 |
|2 | 6 |58 | 62 |
|3 | 4 |90 | 98 |
|4 | 1 |87 | 90 |
|5 | 2 |90 | 69 |
|6 | 7 |52 | 58 |
|7 | 3 |67 | 86 |
+----+-----+-----+-------+
7 rows in set (0.00 sec)
mysql> update grade set mysql=mysql+1;
Query OK, 7 rows affected (0.05 sec)
Rows matched: 7Changed: 7Warnings: 0
mysql> select * from grade;
+----+-----+-----+-------+
| id | sid | php | mysql |
+----+-----+-----+-------+
|1 | 5 |80 | 87 |
|2 | 6 |58 | 63 |
|3 | 4 |90 | 99 |
|4 | 1 |87 | 91 |
|5 | 2 |90 | 70 |
|6 | 7 |52 | 59 |
|7 | 3 |67 | 87 |
+----+-----+-----+-------+
7 rows in set (0.00 sec)
mysql> quit
Bye
C:\Documents and Settings\admin>mysqldump
Usage: mysqldump database
OR mysqldump --databases DB1
OR mysqldump --all-databases
For more options, use mysqldump --help
C:\Documents and Settings\admin>d:
D:\>cd D:\AppServ\MySQL\bin
D:\AppServ\MySQL\bin>dir
驱动器 D 中的卷没有标签。
卷的序列号是 000E-9EA3
D:\AppServ\MySQL\bin 的目录
2012-04-1011:13 <DIR> .
2012-04-1011:13 <DIR> ..
2008-04-1720:13 2,052,736 mysql.exe
2005-10-2518:23 894 mysql.ico
2008-04-1720:13 2,003,584 mysqladmin.exe
2008-04-1720:13 5,750,784 mysqld-nt.exe
2008-04-1720:13 2,048,640 mysqldump.exe
5 个文件 11,856,638 字节
2 个目录7,268,278,272 可用字节
数据的备份与恢复
=====================================================
切记是在DOS命令行下执行：
--备份(导出)数据库fooknet
D:\AppServ\MySQL\bin>mysqldump -u root -p fooknet>fooknet_20120523.sql
Enter password: ****
--备份(导出)数据库fooknet中的一个stu表。
D:\AppServ\MySQL\bin>mysqldump -u root -p fooknet stu>fooknet_stu_20120523.sql
Enter password: ****
--数据的恢复（导入）
D:\AppServ\www\fooknet\mysql>mysql -u root -p fooknet <fooknet_20120523.sql
Enter password: ****
页:
[1]