课堂练习：

CREATE TABLE `student` (	`id` INT ( 4 ) PRIMARY KEY auto_increment COMMENT '学号',	`name` VARCHAR ( 50 ) DEFAULT NULL COMMENT '学员姓名',	`sex` INT ( 2 ) DEFAULT '1' COMMENT '性别男为1，女为2，默认为1',	`phone` VARCHAR ( 255 ) COMMENT '手机号码',	`role` INT ( 4 ), 	`score` INT ( 4 ) COMMENT '分数' )

#添加数据insert into student values(DEFAULT,'zhangsan',2,'13837689458',1,54),(DEFAULT,'lisi',2,'18537689458',2,90),(DEFAULT,'wangwu',1,'15937689458',3,94),(DEFAULT,'zhaoliu',2,'13037689458',3,82);

　　

#更改名字是lisi的手机号码update student set phone=13865193802 where name='lisi';

　　

#查询分数小于60的学员select * from student where score<60;

　　

#查询姓名带z的学员信息select * from student where name like 'z%';

　　

#查询role为1或3的学员信息select * from student where role=1 or role=3;

　　

#删除role为1的学员delete from student where role=1;

　　

#清空表truncate student;

　　

笔记：

CREATE TABLE IF NOT EXISTS category(     categoryId int(10) auto_increment primary key,     categoryName varchar(32) not null,     pid  int(10));　　/*子查询：*/select c1.categoryName '父级栏目',c2.categoryName '子级栏目'from category c1,category c2where c1.categoryId=c2.pid

 

#降序查询 select * from result ORDER BY subjectno,studentresult descselect * from smbms_provider p#1、总条数；2、总页数；3、当前页；4、每页起始位置#1、总条数select count(1) from smbms_provider p#2、总页数#总条数%页面大小==0?总条数/页面大小:总条数/页面大小+1#4、每页起始位置#（当前页-1）*页面大小select * from smbms_provider p LIMIT 4,4#select * from smbms_provider p LIMIT ?,4#?=(当前页-1)*4