MYSQL单表CRUD操作.docx
《MYSQL单表CRUD操作.docx》由会员分享，可在线阅读，更多相关《MYSQL单表CRUD操作.docx（15页珍藏版）》请在冰豆网上搜索。
MYSQL单表CRUD操作
单表的CRUD操作
（
Createinsert
Readselect
Updateupdate
Deletedelete
）
=======================================================================
MicrosoftWindowsXP[版本5.1.2600]
（C）版权所有1985-2001MicrosoftCorp.
//启动mysql服务
C:
\DocumentsandSettings\ctd>netstartmysql
MySQL服务已经启动成功。
//停止mysql服务
C:
\DocumentsandSettings\ctd>netstopmysql
MySQL服务正在停止.
MySQL服务已成功停止。
C:
\DocumentsandSettings\ctd>netstartmysql
MySQL服务已经启动成功。
//使用root用户登录系统
C:
\DocumentsandSettings\ctd>mysql-uroot-proot
WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis2
Serverversion:
5.0.45-community-ntMySQLCommunityEdition（GPL）
Type'help;'or'\h'forhelp.Type'\c'toclearthebuffer.
//创建数据库,mydb为数据库表，一张数据表必须存放在某个数据库中。
mysql>createdatabasemydb;
QueryOK,1rowaffected（0.02sec）
//使用且打开数据库，才能使后头的建表操作能把表建立在这个数据库中。
mysql>usemydb;
Databasechanged
//查看数据库中有哪些表
mysql>showtables;
Emptyset（0.00sec）//没有任何表
//建表操作
mysql>createtablestudent（
->stu_nointprimarykey,
->stu_namevarchar（20）notnull,
->stu_ageintcheck（stu_age>=16andstu_age<=20）,
->stu_markdecimal（5,2）default0.0
->）;
QueryOK,0rowsaffected（0.08sec）
//查看表结构（desc:
describe）
mysql>descstudent;
+----------+--------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+----------+--------------+------+-----+---------+-------+
|stu_no|int（11）|NO|PRI|||
|stu_name|varchar（20）|NO||||
|stu_age|int（11）|YES||NULL||
|stu_mark|decimal（5,2）|YES||0.00||
+----------+--------------+------+-----+---------+-------+
4rowsinset（0.02sec）
//插入表数据，全字段天然顺序插入，则无需指定字段名
mysql>insertintostudentvalues（1,'mary',16,95.5）;
QueryOK,1rowaffected（0.05sec）
mysql>insertintostudentvalues（2,'david',16,85.5）;
QueryOK,1rowaffected（0.02sec）
mysql>select*fromstudent;
+--------+----------+---------+----------+
|stu_no|stu_name|stu_age|stu_mark|
+--------+----------+---------+----------+
|1|mary|16|95.50|
|2|david|16|85.50|
+--------+----------+---------+----------+
2rowsinset（0.00sec）
//主键不能雷同
mysql>insertintostudentvalues（1,'mary',16,95.5）;
ERROR1062（23000）:
Duplicateentry'1'forkey1
mysql>insertintostudentvalues（2,123456,16,95.5）;
ERROR1062（23000）:
Duplicateentry'2'forkey1
mysql>select*fromstudent;
+--------+---------------------+---------+----------+
|stu_no|stu_name|stu_age|stu_mark|
+--------+---------------------+---------+----------+
|1|mary|16|95.50|
|2|david|16|85.50|
+--------+---------------------+---------+----------+
4rowsinset（0.00sec）
//自定义字段顺序插入，必须指定字段名
mysql>insertintostudent（stu_name,stu_age,stu_no,stu_mark）
->values（'mike',14,6,60.5）;
QueryOK,1rowaffected（0.01sec）
//部分字段插入，那么拥有默认值约束的字段将启动默认值
mysql>insertintostudent（stu_name,stu_age,stu_no）
->values（'kate',15,7）;
QueryOK,1rowaffected（0.01sec）
mysql>select*fromstudent;
+--------+---------------------+---------+----------+
|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|0.00|
+--------+---------------------+---------+----------+
7rowsinset（0.00sec）
============================================QUERYOPERATION========================================
//全行列查询，没有任何限制，各字段按照天然顺序输出
mysql>select*fromstudent;
+--------+----------+---------+----------+
|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|0.00|
+--------+----------+---------+----------+
5rowsinset（0.00sec）
//列约束输出，仅仅2列别允许输出
mysql>selectstu_no,stu_namefromstudent;
+--------+----------+
|stu_no|stu_name|
+--------+----------+
|1|mary|
|2|david|
|5|david|
|6|mike|
|7|kate|
+--------+----------+
5rowsinset（0.00sec）
//自定义列输出顺序，必须明确指定字段名
mysql>selectstu_no,stu_age,stu_name,stu_markfromstudent;
+--------+---------+----------+----------+
|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|kate|0.00|
+--------+---------+----------+----------+
5rowsinset（0.00sec）
//列不限制，行约束输出
mysql>select*fromstudentwherestu_name='kate';
+--------+----------+---------+----------+
|stu_no|stu_name|stu_age|stu_mark|
+--------+----------+---------+----------+
|7|kate|15|0.00|
+--------+----------+---------+----------+
1rowinset（0.00sec）
mysql>select*fromstudentwherestu_mark>60;
+--------+----------+---------+----------+
|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|
+--------+----------+---------+----------+
4rowsinset（0.00sec）
//复合条件行约束输出
mysql>select*fromstudentwherestu_mark>80andstu_age=16;
+--------+----------+---------+----------+
|stu_no|stu_name|stu_age|stu_mark|
+--------+----------+---------+----------+
|1|mary|16|95.50|
|2|david|16|85.50|
+--------+----------+---------+----------+
2rowsinset（0.00sec）
mysql>select*fromstudentwherestu_mark>80orstu_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|
+--------+----------+---------+----------+
4rowsinset（0.00sec）
mysql>select*fromstudentwherenot（stu_mark>80orstu_age=14）;
+--------+----------+---------+----------+
|stu_no|stu_name|stu_age|stu_mark|
+--------+----------+---------+----------+
|7|kate|15|0.00|
+--------+----------+---------+----------+
1rowinset（0.00sec）
//复合条件行列均约束输出
mysql>selectstu_name,stu_agefromstudentwherestu_mark>80orstu_age=14;
+----------+---------+
|stu_name|stu_age|
+----------+---------+
|mary|16|
|david|16|
|david|11|
|mike|14|
+----------+---------+
4rowsinset（0.00sec）
mysql>select*fromstudent;
+--------+----------+---------+----------+
|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|0.00|
+--------+----------+---------+----------+
5rowsinset（0.00sec）
//单字段修改（使用where限制为单行修改）
mysql>updatestudentsetstu_name='henry'wherestu_no=5;
QueryOK,1rowaffected（0.02sec）
Rowsmatched:
1Changed:
1Warnings:
0
mysql>select*fromstudent;
+--------+----------+---------+----------+
|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|15|0.00|
+--------+----------+---------+----------+
5rowsinset（0.00sec）
//多字段修改
mysql>updatestudentsetstu_age=14,stu_mark=81.5wherestu_no=7;
QueryOK,1rowaffected（0.03sec）
Rowsmatched:
1Changed:
1Warnings:
0
mysql>select*fromstudent;
+--------+----------+---------+----------+
|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|
+--------+----------+---------+----------+
5rowsinset（0.00sec）
//增量修改（使用多行书写格式，更加容易进行维护）
mysql>updatestudent
->setstu_mark=stu_mark+2
->wherestu_mark<90;
QueryOK,4rowsaffected（0.01sec）
Rowsmatched:
4Changed:
4Warnings:
0
mysql>select*fromstudent;
+--------+----------+---------+----------+
|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|
+--------+----------+---------+----------+
5rowsinset（0.00sec）
mysql>selectstu_no,stu_name
->fromstudent
->wherestu_no>3;
+--------+----------+
|stu_no|stu_name|
+--------+----------+
|5|henry|
|6|mike|
|7|kate|
+--------+----------+
3rowsinset（0.00sec）
================================================DELETEOPERATION=======================
//条件约束删除
mysql>deletefromstudentwherestu_no=7;
QueryOK,1rowaffected（0.03sec）
mysql>select*fromstudent;
+--------+----------+---------+----------+
|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|
+--------+----------+---------+----------+
4rowsinset（0.00sec）
mysql>deletefromstudent
->wherestu_mark>=80;
QueryOK,3rowsaffected（0.02sec）
mysql>select*fromstudent;
+--------+----------+---------+----------+
|stu_no|stu_name|stu_age|stu_mark|
+--------+----------+---------+----------+
|6|mike|14|62.50|
+--------+----------+---------+----------+
1rowinset（0.00sec）
============================================五大聚合函数============================================
mysql>insertintostudentvalues（1,'mary',12,67.5）;
QueryOK,1rowaffected（0.03sec）
mysql>insertintostudentvalues（2,'henry',13,73.5）;
QueryOK,1rowaffected（0.01sec）
mysql>insertintostudentvalues（3,'tom',11,78.5）;
Qu
升级会员 