11.22
select * from student; 查询student表里所有字段的数据select name from student; 查询student表里所有的姓名select name,class from student; 查询student表里的姓名和班级select * from student where class=1833; 查询表里班级为1833班的所有信息select * from student where class=1833 or class=1834; 查询班级为1833和1834的信息。同一字段等于多个值，用or连接select * from student where class!=1833; 查询班级不为1833班的信息select * from student where class<>1833; 查询班级不为1833班的信息 select * from student where name=’zhangsan’ and class=1833; 查询姓名为zhangsan,班级为1833班的信息。and两边可以接两个不同字段select * from student where age 25 between 31; 查询年龄在25到31岁之间的信息，包含25和31select * from student where age>=25 and age<=31; 查询年龄在25到31岁之间的信息，包含25和31select * fromstudent where class in(1833,1834); 查询班级在1833和1834里面的信息。和class=1833 or class=1834 一样select * from student where class not in(1833,1834); 查询班级不为1833和1834的信息 select * from student where class is null; 查询班级为空的信息，用isselect * from student where class is not null; 查询班级不为空的信息 select * from student where name like ‘xiao%’; 查询姓名以 xiao 开头的信息select * from student where name like ‘%ao%; 查询姓名中包含ao的信息 select * from student where id limit 5; 查询前5行select * from student where id limit 1,4; 查询2到5行的数据。limit m,n m是下标，n是行数，m取值：第2行即2-1作为下标m值，n值即5-下标值m使用limit 时，limit前面的字段一般接 int 或bigint类型，并且字段对应的数据有多个值 select count(*) from student; 统计student 表数据的行数。count后可接字段名统计单个字段名，但尽量不接字段名 select sum(math) from student; 查询student表数学成绩总分select avg(math) from student; 查询student表平均数学成绩select max(math) from student; 查询student表最大数学成绩select min(math) from student; 查询student表最小数学成绩select distinct(sex) from student; 对student表里的sex字段进行去重操作 select * from student order by age asc; order by 用于排序的关键字，对年龄进行从小到大进行排序select * from student order by age desc; 对年龄从大到小进行排序 select class,sum(math) from student group by class;查询每个班级的数学总分。把class字段加在select后面，方便识别查询出来的结果，即哪个总分属于哪个班（group by class,根据班级进行分组） select class,sum(math) from student group by class having sum(math)>100; 查询每个班级数学总分大于100的班级和总分数 取别名:select class,sum(math)as s from student group by class having s>100; select class,sum(math) s from student group by class having s>100;select class as ‘班级’,sum(math)s from student group by class having s>100 select class,sum(math) from student where sex=1 group by class having sum(math)>100; 查询每个班级中性别为1的数学总成绩大于100的班级和总分数。where 不能放在group by 后面，where是在分组之前过滤数据，条件中不能包含聚合函数。having 放在group by后面，此时作用相当于where，在分组之后过滤数据，常包含聚合函数。 insert into test values(11,’xiaoliu’,2021,66.66,’ ‘,’2021-11-22’); 给test表所有字段都插入数据，values前面不写字段名的时候，values后面要按照字段的顺序并且每个字段都要写入插入数据insert into test values(11,’xiaoliu’,2021,66.66,’ ‘,’2021-11-22’)，(12,’xiaoyi’,2022,69.99,’ ‘,’2021-11-22’)....;插入多条数据 delete from test where sid=11; 删除sid=11的一条数据delete from test where sid in(7,8,9); 删除sid为7,8,9的数据truncate test; 删除表中所有的数据，不删除表drop table test; 删除test整张表。若表里有数据也会连同数据一起删除 备份表：先创建表，再插入数据。create table student1 like student; 创建一个student1表，表结构和student表一样且字段也一样insert into student1 select * from studnet; 备份数据，往student1表里插入数据，插入的数据来源是从student表查询出来的所有数据create table student2 like student; 创建一个student2表，结构和student一样insert into studnent2(id,name) select id,name from student; 往student2表里只插入id和name字段数据，数据来源是从student表查询出来的id和name的数据 备份数据库：回到Linux操作界面mysqldump -uroot -p123456 dcs10>/dcs10.sql 在Linux操作界面进行对dcs10数据库的备份，备份之后形成一个名字为dcs10.sql脚本，并放在根目录下还原数据库：还原之前先进入到MySQL交互界面创建一个dcs10_back空库mysql -uroot -p123456 dcs10_back</dcs10.sql 在Linux操作界面将dcs10.sql脚本的数据还原至dcs10_back的空库里。还原到数据库之后进入到数据库进行查看 update student set math=100 where id=1; 把id=1的数学分数改为100
页:
[1]