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 classG
*************************** 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;