一，增
语法：createtable表名; createtableuser(idint,id_namevarchar(30),sexvarchar(20));//创建一个表insertintouservalues(1,'张三','男');//全列插入数据insertintouser (id,id_name) values(1,'张三');//制定列插入数据
二，查
1.全列查询
语法：select*from表名;//全列查询mysql>droptableuser; QueryOK, 0rowsaffected (0.02sec) mysql>createtablestudent(namevarchar(20),chineseint,mathint,englishint); QueryOK, 0rowsaffected (0.03sec) mysql>insertintostudentvalues('张三',82,90,75); QueryOK, 1rowaffected (0.01sec) mysql>insertintostudentvalues('李四',85,82,90); QueryOK, 1rowaffected (0.01sec) mysql>insertintostudentvalues('王五',84,90,90); QueryOK, 1rowaffected (0.01sec) mysql>select*fromstudent; +------+---------+------+---------+|name|chinese|math|english|+------+---------+------+---------+|张三|82|90|75||李四|85|82|90||王五|84|90|90|+------+---------+------+---------+
2.指定列查询
//查询姓名和语文成绩字段mysql>selectname,chinesefromstudent; +------+---------+|name|chinese|+------+---------+|张三|82||李四|85||王五|84|+------+---------+3rowsinset (0.00sec)
3.查询字段为表达式
//查询姓名，以及三科成绩的总和mysql>selectname ,chinese+math+englishfromstudent; +------+----------------------+|name|chinese+math+english|+------+----------------------+|张三|247||李四|257||王五|264|+------+----------------------+3rowsinset (0.00sec)
4.别名查询
mysql>selectname ,chinese+math+englishfromstudent; +------+----------------------+|name|chinese+math+english|+------+----------------------+|张三|247||李四|257||王五|264|+------+----------------------+3rowsinset (0.00sec) //这里我们把三科成绩起了个别名，在视觉上，可以更直观的看出mysql>selectname ,chinese+math+englishastetolfromstudent; +------+-------+|name|tetol|+------+-------+|张三|247||李四|257||王五|264|+------+-------+3rowsinset (0.00sec)
5.去重查询
mysql>selectenglishfromstudent; +---------+|english|+---------+|75||90||90|+---------+3rowsinset (0.00sec) //去重查询使用关键字distinctmysql>selectdistinctenglishfromstudent; +---------+|english|+---------+|75||90|+---------+2rowsinset (0.00sec)
6.排序查询
1）升序
//使用order by 关键字进行升序mysql>selectchinesefromstudentorderbychinese; +---------+|chinese|+---------+|82||84||85|+---------+3rowsinset (0.00sec)
2）降序
//在order by的基础上，增加desc关键字进行降序;mysql>selectchinesefromstudentorderbychinesedesc; +---------+|chinese|+---------+|85||84||82|+---------+3rowsinset (0.00sec)
7.条件查询
1）基本查询
//查询语文成绩大于82分以上（不包括82）的同学mysql>selectnamefromstudentwherechinese>82; +------+|name|+------+|李四||王五|+------+2rowsinset (0.00sec)
2）and与or查询
//查询语文成绩大于82，并且数学成绩大于82的同学mysql>selectnamefromstudentwherechinese>82andmath>82; +------+|name|+------+|王五|+------+1rowinset (0.00sec) //查询语文成绩大于82，或者数学成绩大于82的同学mysql>selectnamefromstudentwherechinese>82ormath>82; +------+|name|+------+|张三||李四||王五|+------+3rowsinset (0.00sec)
3）范围查询
//查询语文成绩在84到85之间的同学mysql>selectnamefromstudentwherechinesebetween84and85; +------+|name|+------+|李四||王五|+------+2rowsinset (0.00sec)
4）模糊查询
mysql>select*fromstudent; +--------+---------+------+---------+|name|chinese|math|english|+--------+---------+------+---------+|张三|82|90|75||李四|85|82|90||王五|84|90|90||张伟|99|89|78||张大大|100|59|78|+--------+---------+------+---------+5rowsinset (0.00sec) // %可以匹配任意多个字符mysql>selectnamefromstudentwherenamelike'张%'; +--------+|name|+--------+|张三||张伟||张大大|+--------+3rowsinset (0.00sec) // _只能匹配一个任意字符mysql>selectnamefromstudentwherenamelike'张_'; +------+|name|+------+|张三||张伟|+------+2rowsinset (0.00sec)
8.分页查询
//使用limit关键字进行分页查询mysql>select*fromstudent; +--------+---------+------+---------+|name|chinese|math|english|+--------+---------+------+---------+|张三|82|90|75||李四|85|82|90||王五|84|90|90||张伟|99|89|78||张大大|100|59|78|+--------+---------+------+---------+5rowsinset (0.00sec) mysql>selectchinesefromstudentlimit3; +---------+|chinese|+---------+|82||85||84|+---------+3rowsinset (0.00sec) mysql>selectchinesefromstudentlimit3,5; +---------+|chinese|+---------+|99||100|+---------+2rowsinset (0.00sec) mysql>selectchinesefromstudentlimit3offset0; +---------+|chinese|+---------+|82||85||84|+---------+3rowsinset (0.00sec) mysql>selectchinesefromstudentlimit3offset3; +---------+|chinese|+---------+|99||100|+---------+2rowsinset (0.00sec)
三，改
mysql>select*fromstudent; +--------+---------+------+---------+|name|chinese|math|english|+--------+---------+------+---------+|张三|82|90|75||李四|85|82|90||王五|84|90|90||张伟|99|89|78||张大大|100|59|78|+--------+---------+------+---------+5rowsinset (0.00sec) //将张三的数学成绩改为100mysql>updatestudentsetmath=100wherename='张三'; QueryOK, 1rowaffected (0.01sec) Rowsmatched: 1Changed: 1Warnings: 0mysql>select*fromstudent; +--------+---------+------+---------+|name|chinese|math|english|+--------+---------+------+---------+|张三|82|100|75||李四|85|82|90||王五|84|90|90||张伟|99|89|78||张大大|100|59|78|+--------+---------+------+---------+5rowsinset (0.00sec) //将张三的语文成绩改为90，数学成绩改成100；mysql>updatestudentsetchinese=90, math=100wherename='张三'; QueryOK, 1rowaffected (0.01sec) Rowsmatched: 1Changed: 1Warnings: 0//将所有同学的语文成绩都加10分；mysql>updatestudentsetchinese=chinese+10; QueryOK, 5rowsaffected (0.00sec) Rowsmatched: 5Changed: 5Warnings: 0mysql>select*fromstudent; +--------+---------+------+---------+|name|chinese|math|english|+--------+---------+------+---------+|张三|100|100|75||李四|95|82|90||王五|94|90|90||张伟|109|89|78||张大大|110|59|78|+--------+---------+------+---------+5rowsinset (0.00sec)
四，删
mysql>select*fromstudent; +--------+---------+------+---------+|name|chinese|math|english|+--------+---------+------+---------+|张三|100|100|75||李四|95|82|90||王五|94|90|90||张伟|109|89|78||张大大|110|59|78|+--------+---------+------+---------+5rowsinset (0.00sec) //删除张三同学的所有成绩mysql>deletefromstudentwherename='张三'; QueryOK, 1rowaffected (0.01sec) mysql>select*fromstudent; +--------+---------+------+---------+|name|chinese|math|english|+--------+---------+------+---------+|李四|95|82|90||王五|94|90|90||张伟|109|89|78||张大大|110|59|78|+--------+---------+------+---------+4rowsinset (0.00sec)
