mysql操作
把student表的数据插入到student1表insert into student1 select * from student;
创建具有student表结构的student1表
create table student1 like student;
删除test表
truncate test;
查找名字中含ng字段的信息
select * from student where name like '%ng%';
查询名字是xiao开头的信息
select * from student where name like 'xiao%';
查询班级不为空的信息
select * from student where class is not null;
查询class不是1825和1835的信息
select * from student where class not in(1835,1825);
查询class是1825和1835的信息
select * from student where class in(1835,1825);
查询class不是1833信息
select * from student where class!=1833;
查询class是1833和1834的信息
select * from student whereclass= 1833 or class=1834;
删除ID=1的数据
delete from air where id=1;
+-------+---------+---------+------+----------+------+-----+-------+------+
| class | chinese | english | math | name | age| sid | phone | sex|
+-------+---------+---------+------+----------+------+-----+-------+------+
|1833 | 86 | 90 | 40 | zhangsan | 21 | 1 |NULL | NULL |
|1832 | 60 | 86 | 66 | lisi | 22 | 2 |NULL | NULL |
|1833 | 93 | 57 | 98 | zhaoliu | 23 | 3 |NULL | NULL |
|1832 | 84 | 90 | 88 | wangwu | 24 | 4 |NULL | NULL |
|1833 | 93 | 57 | 22 | lijiu | 25 | 5 |NULL | NULL |
|1832 | 84 | 98 | 77 | liuqi | 26 | 6 |NULL | NULL |
|1832 | 60 | 57 | 77 | liuli | 27 | 7 |NULL | NULL |
|1833 | 60 | 58 | 88 | wangbo | 28 | 8 |NULL | NULL |
|1832 | 78 | 57 | 88 | wangsan| 29 | 9 |NULL | NULL |
|1833 | 87 | 60 | 65 | wangan | 30 |10 |NULL | NULL |
|1832 | 80 | 76 | 88 | wangping | 31 |11 |NULL | NULL |
|1833 | NULL | 79 | 88 | wanghui | 32 |12 |NULL | NULL |
+-------+---------+---------+------+----------+------+-----+-------+------+
增加phone和add两个字段
alter table grade add (phone bigint(20),sex char(5));
查询每个班的最高语文成绩
select sum(chinese),class from grade group by class;
查询语文成绩小于60的学生名字
select name from grade where chinese<60;
查询语文成绩为空的学生名字和班级
select class,name from grade where chinese is null;
查询语文成绩和数学成绩小于80的学生名字
select name from grade where chinese<80 and math<80;
按语文成绩降序查询1832班的学生信息
select * from grade where class=1832 order by chinese desc;
查询5到10行的信息
select * from grade limit 4,6;
把学号位12的学生语文成绩改为空
update grade set chinese=NULL where sid=12;
页:
[1]