简单数据库mysql的增删改查（新手）

一，创建表department

mysql> use csdn;
Database changed
mysql> create table department(`主键` varchar(36),`名称` varchar(200),`父部门` v
archar(36),`排序` int,`电话` varchar(200),`传真` varchar(200),`地址` varchar(200
),`备注` varchar(4000),`创建时间` datetime,`创建人` varchar(200),`编辑时间` date
time,`编辑人` varchar(200),`时间戳` timestamp);
Query OK, 0 rows affected (0.76 sec)

二，查看表department
 
mysql> desc department;
+--------------+---------------+------+-----+-------------------+---------------
--------------+
| Field        | Type          | Null | Key | Default           | Extra
              |
+--------------+---------------+------+-----+-------------------+---------------
--------------+
| 主键         | varchar(36)   | YES  |     | NULL              |
              |
| 名称         | varchar(200)  | YES  |     | NULL              |
              |
| 父部门       | varchar(36)   | YES  |     | NULL              |
              |
| 排序         | int(11)       | YES  |     | NULL              |
              |
| 电话         | varchar(200)  | YES  |     | NULL              |
              |
| 传真         | varchar(200)  | YES  |     | NULL              |
              |
| 地址         | varchar(200)  | YES  |     | NULL              |
              |
| 备注         | varchar(4000) | YES  |     | NULL              |
              |
| 创建时间     | datetime      | YES  |     | NULL              |
              |
| 创建人       | varchar(200)  | YES  |     | NULL              |
              |
| 编辑时间     | datetime      | YES  |     | NULL              |
              |
| 编辑人       | varchar(200)  | YES  |     | NULL              |
              |
| 时间戳       | timestamp     | NO   |     | CURRENT_TIMESTAMP | on update CURR
ENT_TIMESTAMP |
+--------------+---------------+------+-----+-------------------+---------------
--------------+

三 插入数据

13 rows in set (0.02 sec)
mysql> insert into department values('','`商务部 ','','1','','','','',20130904,'
',20130904,'',20130904);
Query OK, 1 row affected (0.08 sec)

mysql> insert into department values('','人事行政部 ','','1','','','','',2013090
4,'',20130904,'',20130904);
Query OK, 1 row affected (0.27 sec)

mysql> insert into department values('','财务部 ','','1','','','','',20130904,''
,20130904,'',20130904);
Query OK, 1 row affected (0.06 sec)

mysql> insert into department values('','教育事业部 ','','2','','','','',2013090
4,'',20130904,'',20130904);
Query OK, 1 row affected (0.07 sec)

mysql> insert into department values('','总经办 ','','1','','','','',20130904,''
,20130904,'',20130904);
Query OK, 1 row affected (0.11 sec)

四，查看插入数据以后的表

mysql> select * from department;
+--------+------------------+-----------+--------+--------+--------+--------+---
-----+---------------------+-----------+---------------------+-----------+------
---------------+
| 主键   | 名称             | 父部门    | 排序   | 电话   | 传真   | 地址   | 备
注   | 创建时间            | 创建人    | 编辑时间            | 编辑人    | 时间
戳              |
+--------+------------------+-----------+--------+--------+--------+--------+---
-----+---------------------+-----------+---------------------+-----------+------
---------------+
|        | `商务部          |           |      1 |        |        |        |
     | 2013-09-04 00:00:00 |           | 2013-09-04 00:00:00 |           | 2013-
09-04 00:00:00 |
|        | 人事行政部       |           |      1 |        |        |        |
     | 2013-09-04 00:00:00 |           | 2013-09-04 00:00:00 |           | 2013-
09-04 00:00:00 |
|        | 财务部           |           |      1 |        |        |        |
     | 2013-09-04 00:00:00 |           | 2013-09-04 00:00:00 |           | 2013-
09-04 00:00:00 |
|        | 教育事业部       |           |      2 |        |        |        |
     | 2013-09-04 00:00:00 |           | 2013-09-04 00:00:00 |           | 2013-
09-04 00:00:00 |
|        | 总经办           |           |      1 |        |        |        |
     | 2013-09-04 00:00:00 |           | 2013-09-04 00:00:00 |           | 2013-
09-04 00:00:00 |
+--------+------------------+-----------+--------+--------+--------+--------+---
-----+---------------------+-----------+---------------------+-----------+------
---------------+
5 rows in set (0.00 sec)

 

五，创建表preson

mysql> create table preson(`主键` varchar(36),`用户名` varchar(200),`姓名` varch
ar(36),`密码` varchar(200),`确认密码` varchar(200),`手机号码` varchar(200),`办公
电话` varchar(200),`联系地址` varchar(200),`邮箱` varchar(200),`备注` varchar(20
0),`状态` varchar(200),`创建时间` datetime,`创建人` varchar(200),`编辑时间` date
time,`编辑人` varchar(200),`时间戳` timestamp);
Query OK, 0 rows affected (0.72 sec)

六插入数据

mysql> insert into preson values('','张三','','','','','','','','','',20130904,'
',20130904,'',20130904);
Query OK, 1 row affected (0.06 sec)

mysql> insert into preson values('','王五','','','','','','','','','',20130904,'
',20130904,'',20130904);
Query OK, 1 row affected (0.06 sec)

mysql> insert into preson values('','李四','','','','','','','','','',20130904,'
',20130904,'',20130904);
Query OK, 1 row affected (0.13 sec)

mysql> insert into preson values('','麻子','','','','','','','','','',20130904,'
',20130904,'',20130904);
Query OK, 1 row affected (0.10 sec)

mysql> insert into preson values('','凤姐','','','','','','','','','',20130904,'
',20130904,'',20130904);
Query OK, 1 row affected (0.08 sec)

七，查看插入数据的表格

mysql> select * from preson;
+--------+-----------+--------+--------+--------------+--------------+----------
----+--------------+--------+--------+--------+---------------------+-----------
+---------------------+-----------+---------------------+
| 主键   | 用户名    | 姓名   | 密码   | 确认密码     | 手机号码     | 办公电话
    | 联系地址     | 邮箱   | 备注   | 状态   | 创建时间            | 创建人
| 编辑时间            | 编辑人    | 时间戳              |
+--------+-----------+--------+--------+--------------+--------------+----------
----+--------------+--------+--------+--------+---------------------+-----------
+---------------------+-----------+---------------------+
|        | 张三      |        |        |              |              |
    |              |        |        |        | 2013-09-04 00:00:00 |
| 2013-09-04 00:00:00 |           | 2013-09-04 00:00:00 |
|        | 王五      |        |        |              |              |
    |              |        |        |        | 2013-09-04 00:00:00 |
| 2013-09-04 00:00:00 |           | 2013-09-04 00:00:00 |
|        | 李四      |        |        |              |              |
    |              |        |        |        | 2013-09-04 00:00:00 |
| 2013-09-04 00:00:00 |           | 2013-09-04 00:00:00 |
|        | 麻子      |        |        |              |              |
    |              |        |        |        | 2013-09-04 00:00:00 |
| 2013-09-04 00:00:00 |           | 2013-09-04 00:00:00 |
|        | 凤姐      |        |        |              |              |
    |              |        |        |        | 2013-09-04 00:00:00 |
| 2013-09-04 00:00:00 |           | 2013-09-04 00:00:00 |
+--------+-----------+--------+--------+--------------+--------------+----------
----+--------------+--------+--------+--------+---------------------+-----------
+---------------------+-----------+---------------------+
5 rows in set (0.00 sec)

八，修改姓名

mysql> update preson set 用户名='涛哥' where 用户名='凤姐';
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

九查看修改以后的表格

mysql> select * from preson;
+--------+-----------+--------+--------+--------------+--------------+----------
----+--------------+--------+--------+--------+---------------------+-----------
+---------------------+-----------+---------------------+
| 主键   | 用户名    | 姓名   | 密码   | 确认密码     | 手机号码     | 办公电话
    | 联系地址     | 邮箱   | 备注   | 状态   | 创建时间            | 创建人
| 编辑时间            | 编辑人    | 时间戳              |
+--------+-----------+--------+--------+--------------+--------------+----------
----+--------------+--------+--------+--------+---------------------+-----------
+---------------------+-----------+---------------------+
|        | 张三      |        |        |              |              |
    |              |        |        |        | 2013-09-04 00:00:00 |
| 2013-09-04 00:00:00 |           | 2013-09-04 00:00:00 |
|        | 王五      |        |        |              |              |
    |              |        |        |        | 2013-09-04 00:00:00 |
| 2013-09-04 00:00:00 |           | 2013-09-04 00:00:00 |
|        | 李四      |        |        |              |              |
    |              |        |        |        | 2013-09-04 00:00:00 |
| 2013-09-04 00:00:00 |           | 2013-09-04 00:00:00 |
|        | 麻子      |        |        |              |              |
    |              |        |        |        | 2013-09-04 00:00:00 |
| 2013-09-04 00:00:00 |           | 2013-09-04 00:00:00 |
|        | 涛哥      |        |        |              |              |
    |              |        |        |        | 2013-09-04 00:00:00 |
| 2013-09-04 00:00:00 |           | 2013-09-04 19:23:07 |
+--------+-----------+--------+--------+--------------+--------------+----------
----+--------------+--------+--------+--------+---------------------+-----------
+---------------------+-----------+---------------------+
5 rows in set (0.00 sec)

                    

mysql> use csdn;
Database changed
mysql> create datdbase student;

 

mysql> create database student;
Query OK, 1 row affected (0.01 sec)

一，创建数据表格student

mysql> use csdn;
Database changed

mysql> create table student(`姓名` varchar(200),`年龄` int(11));
Query OK, 0 rows affected (0.95 sec)

二，查看表格

mysql> desc student;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| 姓名   | varchar(200) | YES  |     | NULL    |       |
| 年龄   | int(11)      | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

三，插入数据

mysql> insert into student values('任务','20');
Query OK, 1 row affected (0.08 sec)

mysql> insert into student values('LOVE','40');
Query OK, 1 row affected (0.31 sec)

mysql> insert into student values('凤姐','40');
Query OK, 1 row affected (0.08 sec)

mysql> select * from student;
+-----------+--------+
| 姓名      | 年龄   |
+-----------+--------+
| 任务      |     20 |
| LOVE      |     40 |
| 凤姐      |     40 |
+-----------+--------+
3 rows in set (0.00 sec)

四，修改资料

mysql> update student set 年龄=60 where 姓名='凤姐';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

五，查看修改以后的表格

mysql> select * from student;
+--------+--------+
| 姓名   | 年龄   |
+--------+--------+
| 任务   |     20 |
| LOVE   |     40 |
| 凤姐   |     60 |
+--------+--------+
3 rows in set (0.00 sec