952
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;
最後更新：2017-04-04 07:03:18