显示表中的列元素:desc 表名;
mysql> desc ysx;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | char(10) | YES | | NULL | |
| adress | varchar(10) | YES | | NULL | |
| pay | decimal(5,2) | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+
4 rows in set (0.13 sec)
这是我在数据库中新建的一个表,叫ysx,这次将会在这个表中进行操作
增(*补):
mysql> insert into ysx
-> values
-> (6,'TOM','Beijing',625.26),
-> (7,'BOB','Shanghai',908.03);
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
是可以直接插入两行数据的
改的操作:update 表名
set 选择所要改变的列的值
where (表达式,代表着表达式成立时运行set)选择哪一行
mysql> select * from ysx;
+------+-----------+----------+--------+
| id | name | adress | pay |
+------+-----------+----------+--------+
| 1 | 杨绍兴 | 吉林 | 888.88 |
| 2 | 小黄 | 江苏 | 777.77 |
| 3 | 文轩 | 青海 | 345.45 |
| 4 | 振轩 | 武汉 | 356.27 |
| 5 | 肖肖 | 武汉 | 362.37 |
| 6 | TOM | Beijing | 625.26 |
| 7 | BOB | Shanghai | 908.03 |
+------+-----------+----------+--------+
7 rows in set (0.00 sec)
mysql> update ysx
-> set id=8
-> where adress='武汉' ;
Query OK, 2 rows affected (0.07 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from ysx;
+------+-----------+----------+--------+
| id | name | adress | pay |
+------+-----------+----------+--------+
| 1 | 杨绍兴 | 吉林 | 888.88 |
| 2 | 小黄 | 江苏 | 777.77 |
| 3 | 文轩 | 青海 | 345.45 |
| 8 | 振轩 | 武汉 | 356.27 |
| 8 | 肖肖 | 武汉 | 362.37 |
| 6 | TOM | Beijing | 625.26 |
| 7 | BOB | Shanghai | 908.03 |
+------+-----------+----------+--------+
7 rows in set (0.00 sec)
注意:where可以把它理解成一个表达式,set只是改变列的值,只有满足where的条件才可以进行set
举一个例子
mysql> select * from ysx;
+------+-----------+----------+--------+
| id | name | adress | pay |
+------+-----------+----------+--------+
| 1 | 杨绍兴 | 吉林 | 888.88 |
| 2 | 小黄 | 江苏 | 777.77 |
| 3 | 文轩 | 青海 | 345.45 |
| 8 | 振轩 | 武汉 | 356.27 |
| 8 | 肖肖 | 武汉 | 362.37 |
| 6 | TOM | Beijing | 625.26 |
| 7 | BOB | Shanghai | 908.03 |
+------+-----------+----------+--------+
7 rows in set (0.00 sec)
mysql> update ysx
-> set pay=100.00
-> where adress='武汉' and name='振轩';
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select* from ysx;
+------+-----------+----------+--------+
| id | name | adress | pay |
+------+-----------+----------+--------+
| 1 | 杨绍兴 | 吉林 | 888.88 |
| 2 | 小黄 | 江苏 | 777.77 |
| 3 | 文轩 | 青海 | 345.45 |
| 8 | 振轩 | 武汉 | 100.00 |
| 8 | 肖肖 | 武汉 | 362.37 |
| 6 | TOM | Beijing | 625.26 |
| 7 | BOB | Shanghai | 908.03 |
+------+-----------+----------+--------+
7 rows in set (0.00 sec)
从这个例子中,就可以看出,where的筛选作用
删除操作:delete from 表名
where 条件;【一定要小心】
大伙一定要小心,别手抖,
mysql> delete from ysx;
Query OK, 7 rows affected (0.13 sec)
我心都凉了,一定要小心,呜呜呜~~
删除其实和改差不多,注意理解好where
查找操作;select 列名1,列名2,…from 表名
mysql> select * from ysx;
+------+--------+---------+--------+
| id | name | adress | pay |
+------+--------+---------+--------+
| 6 | TOM | Beijing | 625.26 |
| 3 | 文轩 | 青海 | 345.45 |
+------+--------+---------+--------+
2 rows in set (0.00 sec)
mysql> select name,pay from ysx
-> where id=3;
+--------+--------+
| name | pay |
+--------+--------+
| 文轩 | 345.45 |
+--------+--------+
1 row in set (0.00 sec)
说一下前面常用的: select * from ysx; *代表所有的列 没有where就是输出所有行
mysql> select * from ysx;
+------+--------+---------+--------+
| id | name | adress | pay |
+------+--------+---------+--------+
| 6 | TOM | Beijing | 625.26 |
| 3 | 文轩 | 青海 | 345.45 |
+------+--------+---------+--------+
2 rows in set (0.00 sec)