MYSQL单表CRUD操作.docx

1、MYSQL单表CRUD操作单表的CRUD操作 ( Create insert Read select Update update Delete delete )=Microsoft Windows XP 版本 5.1.2600(C) 版权所有 1985-2001 Microsoft Corp./启动mysql服务C:Documents and Settingsctdnet start mysqlMySQL 服务已经启动成功。/停止mysql服务C:Documents and Settingsctdnet stop mysqlMySQL 服务正在停止.MySQL 服务已成功停止。C:Docume

2、nts and Settingsctdnet start mysqlMySQL 服务已经启动成功。/使用root用户登录系统C:Documents and Settingsctdmysql -uroot -prootWelcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 2Server version: 5.0.45-community-nt MySQL Community Edition (GPL)Type help; or h for help. Type c to clear t

3、he buffer./创建数据库,mydb为数据库表，一张数据表必须存放在某个数据库中。mysql create database mydb;Query OK, 1 row affected (0.02 sec)/使用且打开数据库，才能使后头的建表操作能把表建立在这个数据库中。mysql use mydb;Database changed/查看数据库中有哪些表mysql show tables;Empty set (0.00 sec) /没有任何表/建表操作mysql create table student( - stu_no int primary key, - stu_name varc

4、har(20) not null, - stu_age int check(stu_age=16 and stu_age stu_mark decimal(5,2) default 0.0 - );Query OK, 0 rows affected (0.08 sec)/查看表结构 (desc:describe)mysql desc student;+-+-+-+-+-+-+| Field | Type | Null | Key | Default | Extra |+-+-+-+-+-+-+| stu_no | int(11) | NO | PRI | | | stu_name | varc

5、har(20) | NO | | | | stu_age | int(11) | YES | | NULL | | stu_mark | decimal(5,2) | YES | | 0.00 | |+-+-+-+-+-+-+4 rows in set (0.02 sec)/插入表数据，全字段天然顺序插入，则无需指定字段名mysql insert into student values(1,mary,16,95.5);Query OK, 1 row affected (0.05 sec)mysql insert into student values(2,david,16,85.5);Quer

6、y OK, 1 row affected (0.02 sec)mysql select * from student;+-+-+-+-+| stu_no | stu_name | stu_age | stu_mark |+-+-+-+-+| 1 | mary | 16 | 95.50 | 2 | david | 16 | 85.50 |+-+-+-+-+2 rows in set (0.00 sec)/主键不能雷同mysql insert into student values(1,mary,16,95.5);ERROR 1062 (23000): Duplicate entry 1 for

7、key 1mysql insert into student values(2,123456,16,95.5);ERROR 1062 (23000): Duplicate entry 2 for key 1mysql select * from student;+-+-+-+-+| stu_no | stu_name | stu_age | stu_mark |+-+-+-+-+| 1 | mary | 16 | 95.50 | 2 | david | 16 | 85.50 |+-+-+-+-+4 rows in set (0.00 sec)/自定义字段顺序插入，必须指定字段名mysql in

8、sert into student(stu_name,stu_age,stu_no,stu_mark) - values(mike,14,6,60.5);Query OK, 1 row affected (0.01 sec)/部分字段插入，那么拥有默认值约束的字段将启动默认值mysql insert into student(stu_name,stu_age,stu_no) - values(kate,15,7);Query OK, 1 row affected (0.01 sec)mysql select * from student;+-+-+-+-+| stu_no | stu_name

9、 | stu_age | stu_mark |+-+-+-+-+| 1 | mary | 16 | 95.50 | 2 | david | 16 | 85.50 | 5 | david | 11 | 85.50 | 6 | mike | 14 | 60.50 | 7 | kate | 15 | 0.00 |+-+-+-+-+7 rows in set (0.00 sec)= QUERY OPERATION =/全行列查询，没有任何限制，各字段按照天然顺序输出mysql select * from student;+-+-+-+-+| stu_no | stu_name | stu_age |

10、stu_mark |+-+-+-+-+| 1 | mary | 16 | 95.50 | 2 | david | 16 | 85.50 | 5 | david | 11 | 85.50 | 6 | mike | 14 | 60.50 | 7 | kate | 15 | 0.00 |+-+-+-+-+5 rows in set (0.00 sec)/列约束输出，仅仅2列别允许输出mysql select stu_no,stu_name from student;+-+-+| stu_no | stu_name |+-+-+| 1 | mary | 2 | david | 5 | david |

11、6 | mike | 7 | kate |+-+-+5 rows in set (0.00 sec)/自定义列输出顺序，必须明确指定字段名mysql select stu_no,stu_age,stu_name,stu_mark from student;+-+-+-+-+| stu_no | stu_age | stu_name | stu_mark |+-+-+-+-+| 1 | 16 | mary | 95.50 | 2 | 16 | david | 85.50 | 5 | 11 | david | 85.50 | 6 | 14 | mike | 60.50 | 7 | 15 | kat

12、e | 0.00 |+-+-+-+-+5 rows in set (0.00 sec)/列不限制，行约束输出mysql select * from student where stu_name=kate;+-+-+-+-+| stu_no | stu_name | stu_age | stu_mark |+-+-+-+-+| 7 | kate | 15 | 0.00 |+-+-+-+-+1 row in set (0.00 sec)mysql select * from student where stu_mark60;+-+-+-+-+| stu_no | stu_name | stu_ag

13、e | stu_mark |+-+-+-+-+| 1 | mary | 16 | 95.50 | 2 | david | 16 | 85.50 | 5 | david | 11 | 85.50 | 6 | mike | 14 | 60.50 |+-+-+-+-+4 rows in set (0.00 sec)/复合条件行约束输出mysql select * from student where stu_mark80 and stu_age=16;+-+-+-+-+| stu_no | stu_name | stu_age | stu_mark |+-+-+-+-+| 1 | mary | 16

14、 | 95.50 | 2 | david | 16 | 85.50 |+-+-+-+-+2 rows in set (0.00 sec)mysql select * from student where stu_mark80 or stu_age=14;+-+-+-+-+| stu_no | stu_name | stu_age | stu_mark |+-+-+-+-+| 1 | mary | 16 | 95.50 | 2 | david | 16 | 85.50 | 5 | david | 11 | 85.50 | 6 | mike | 14 | 60.50 |+-+-+-+-+4 row

15、s in set (0.00 sec)mysql select * from student where not(stu_mark80 or stu_age=14);+-+-+-+-+| stu_no | stu_name | stu_age | stu_mark |+-+-+-+-+| 7 | kate | 15 | 0.00 |+-+-+-+-+1 row in set (0.00 sec)/复合条件行列均约束输出mysql select stu_name,stu_age from student where stu_mark80 or stu_age=14;+-+-+| stu_name

16、 | stu_age |+-+-+| mary | 16 | david | 16 | david | 11 | mike | 14 |+-+-+4 rows in set (0.00 sec)mysql select * from student;+-+-+-+-+| stu_no | stu_name | stu_age | stu_mark |+-+-+-+-+| 1 | mary | 16 | 95.50 | 2 | david | 16 | 85.50 | 5 | david | 11 | 85.50 | 6 | mike | 14 | 60.50 | 7 | kate | 15 |

17、 0.00 |+-+-+-+-+5 rows in set (0.00 sec)/单字段修改 （使用where限制为单行修改）mysql update student set stu_name=henry where stu_no=5;Query OK, 1 row affected (0.02 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql select * from student;+-+-+-+-+| stu_no | stu_name | stu_age | stu_mark |+-+-+-+-+| 1 | mary | 16 | 95.

18、50 | 2 | david | 16 | 85.50 | 5 | henry | 11 | 85.50 | 6 | mike | 14 | 60.50 | 7 | kate | 15 | 0.00 |+-+-+-+-+5 rows in set (0.00 sec)/多字段修改mysql update student set stu_age=14,stu_mark=81.5 where stu_no=7;Query OK, 1 row affected (0.03 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql select * from st

19、udent;+-+-+-+-+| stu_no | stu_name | stu_age | stu_mark |+-+-+-+-+| 1 | mary | 16 | 95.50 | 2 | david | 16 | 85.50 | 5 | henry | 11 | 85.50 | 6 | mike | 14 | 60.50 | 7 | kate | 14 | 81.50 |+-+-+-+-+5 rows in set (0.00 sec)/增量修改 (使用多行书写格式，更加容易进行维护)mysql update student - set stu_mark=stu_mark+2 - wher

20、e stu_mark select * from student;+-+-+-+-+| stu_no | stu_name | stu_age | stu_mark |+-+-+-+-+| 1 | mary | 16 | 95.50 | 2 | david | 16 | 87.50 | 5 | henry | 11 | 87.50 | 6 | mike | 14 | 62.50 | 7 | kate | 14 | 83.50 |+-+-+-+-+5 rows in set (0.00 sec)mysql select stu_no,stu_name - from student - where

21、 stu_no3;+-+-+| stu_no | stu_name |+-+-+| 5 | henry | 6 | mike | 7 | kate |+-+-+3 rows in set (0.00 sec)= DELETE OPERATION =/条件约束删除mysql delete from student where stu_no=7;Query OK, 1 row affected (0.03 sec)mysql select * from student;+-+-+-+-+| stu_no | stu_name | stu_age | stu_mark |+-+-+-+-+| 1 |

22、 mary | 16 | 95.50 | 2 | david | 16 | 87.50 | 5 | henry | 11 | 87.50 | 6 | mike | 14 | 62.50 |+-+-+-+-+4 rows in set (0.00 sec)mysql delete from student - where stu_mark=80;Query OK, 3 rows affected (0.02 sec)mysql select * from student;+-+-+-+-+| stu_no | stu_name | stu_age | stu_mark |+-+-+-+-+| 6 | mike | 14 | 62.50 |+-+-+-+-+1 row in set (0.00 sec)= 五大聚合函数 =mysql insert into student values(1,mary,12,67.5);Query OK, 1 row affected (0.03 sec)mysql insert into student values(2,henry,13,73.5);Query OK, 1 row affected (0.01 sec)mysql insert into student values(3,tom,11,78.5);Qu