mysql增查删改

mysql> use test
Database changed
mysql> create table class (
    -> id int primary key auto_increment,
    -> sname varchar(10) not null default '',
    -> gender char(1) not null default '',
    -> company varchar(20) not null default '',
    -> salary decimal(6,2) not null default 0.00,
    -> fanbu smallint not null default 0
    -> )engine myisam charset utf8;
Query OK, 0 rows affected (0.11 sec)

mysql> desc class;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| sname   | varchar(10)  | NO   |     |         |                |
| gender  | char(1)      | NO   |     |         |                |
| company | varchar(20)  | NO   |     |         |                |
| salary  | decimal(6,2) | NO   |     | 0.00    |                |
| fanbu   | smallint(6)  | NO   |     | 0       |                |
+---------+--------------+------+-----+---------+----------------+
6 rows in set (0.02 sec)

mysql> insert into class
    -> (id,sname,gender,company,salary,fanbu)
    -> values
    -> (1,'张三','男','百度',8889.23,250);
Query OK, 1 row affected (0.05 sec)

mysql> insert into class
    -> (sname,gender,salary)
    -> values
    -> ('科比','男',9000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from class;
+----+--------+--------+---------+---------+-------+
| id | sname  | gender | company | salary  | fanbu |
+----+--------+--------+---------+---------+-------+
|  1 | 张三   | 男     | 百度    | 8889.23 |   250 |
|  2 | 科比   | 男     |         | 9000.00 |     0 |
+----+--------+--------+---------+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into class
    -> values
    -> (3,'李四','女','新浪',5888.90,125);
Query OK, 1 row affected (0.00 sec)

mysql> insert into class
    -> values
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2
mysql> insert into class
    -> (sname,company,salary)
    -> values
    -> ('刘备','皇室',1000.00),
    -> ('孙策','江东集团',8000.00),
    -> ('曹操','魏国',5000.00);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from class;
+----+--------+--------+--------------+---------+-------+
| id | sname  | gender | company      | salary  | fanbu |
+----+--------+--------+--------------+---------+-------+
|  1 | 张三   | 男     | 百度         | 8889.23 |   250 |
|  2 | 科比   | 男     |              | 9000.00 |     0 |
|  3 | 李四   | 女     | 新浪         | 5888.90 |   125 |
|  4 | 刘备   |        | 皇室         | 1000.00 |     0 |
|  5 | 孙策   |        | 江东集团     | 8000.00 |     0 |
|  6 | 曹操   |        | 魏国         | 5000.00 |     0 |
+----+--------+--------+--------------+---------+-------+
6 rows in set (0.00 sec)

mysql> update class set gender='男' where id=4;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from class;
+----+--------+--------+--------------+---------+-------+
| id | sname  | gender | company      | salary  | fanbu |
+----+--------+--------+--------------+---------+-------+
|  1 | 张三   | 男     | 百度         | 8889.23 |   250 |
|  2 | 科比   | 男     |              | 9000.00 |     0 |
|  3 | 李四   | 女     | 新浪         | 5888.90 |   125 |
|  4 | 刘备   | 男     | 皇室         | 1000.00 |     0 |
|  5 | 孙策   |        | 江东集团     | 8000.00 |     0 |
|  6 | 曹操   |        | 魏国         | 5000.00 |     0 |
+----+--------+--------+--------------+---------+-------+
6 rows in set (0.00 sec)

mysql> select * from class\G
*************************** 1. row ***************************
     id: 1
  sname: 张三
 gender: 男
company: 百度
 salary: 8889.23
  fanbu: 250
*************************** 2. row ***************************
     id: 2
  sname: 科比
 gender: 男
company:
 salary: 9000.00
  fanbu: 0
*************************** 3. row ***************************
     id: 3
  sname: 李四
 gender: 女
company: 新浪
 salary: 5888.90
  fanbu: 125
*************************** 4. row ***************************
     id: 4
  sname: 刘备
 gender: 男
company: 皇室
 salary: 1000.00
  fanbu: 0
*************************** 5. row ***************************
     id: 5
  sname: 孙策
 gender:
company: 江东集团
 salary: 8000.00
  fanbu: 0
*************************** 6. row ***************************
     id: 6
  sname: 曹操
 gender:
company: 魏国
 salary: 5000.00
  fanbu: 0
6 rows in set (0.00 sec)

#建立文件，并把操作mysql表的内容全部显示
tee F:\1230.sql
#学生表
create table class (
id int primary key auto_increment,
sname varchar(10) not null default '',
gender char(1) not null default '',
company varchar(20) not null default '',
salary decimal(6,2) not null default 0.00,
fanbu smallint not null default 0
)engine myisam charset utf8;

#查看表的结构
desc class;
#增加一条信息
insert into class
(id,sname,gender,company,salary,fanbu)
values
(1,'张三','男','百度',8889.23,250);

#特殊情况
insert into class
(sname,gender,salary)
values
('科比','男',9000);

#插入所有列
insert into class
values
(3,'李四','女','新浪',5888.90,125);

#插入多列
insert into class
(sname,company,salary)
values
('刘备','皇室',1000.00),
('孙策','江东集团',8000.00),
('曹操','魏国',5000.00);

#修改
update class set gender='男' where id=4;

#全部改
update class set fanbu=100 where 1;
#where 1 ,1 表示真，1恒为真

#删除
delete from class where id=1;

#查询
select * from class;