mysql单表查询实例_mysql——单表查询——其它整理示例00

create table student( sid varchar(50),

snamevarchar(50),

sagevarchar(50),

ssexvarchar(50)

);insert into student( sid,sname,sage,ssex ) values('1','zhaolei','1990-01-01','nan');insert into student values('2','qiandian','1990-12-21','nan');insert into student values('3','sunfeng','1990-05-20','nan');insert into student values('4','liyun','1990-08-06','nan');insert into student values('5','zhoumei','1991-12-01','nv'),

('6','wulan','1992-03-01','nv'),

('7','zhenzu','1989-07-01','nv'),

('8','wangju','1990-01-20','nv');select * fromstudent;select sid,sname,sage,ssex fromstudent;select sid,sname,ssex fromstudent;=============================================================================================

1、修改表名

语法格式:alter table 旧表名 rename [to]新表名;

注释:修改后example1表就不存在了,只存在名为user的新表,但是其内容是一致的,只是换了个名称.alter table student rename tosww;select * fromstudent;select * fromsww;alter table sww rename tostudent;=====================================================================================================

2、修改字段名

语法格式:alter table表名 change 旧属性名 新属性名 新数据类型;

注释:新数据类型指修改后的数据类型,如不需要修改,则将新数据类型设置成与原来一样alter table student change sname sww varchar(50);select * fromstudent;alter table student change sww sname varchar(50);==============================================================================================

3、修改字段的数据类型

语法格式:alter table表名 modify 属性名 数据类型;

注释:表名指所要修改数据类型的字段的表的名称;

属性名指:所要修改数据类型字段的名称;

数据类型指:修改后的新的数据类型=========================================================================================================

4、修改字段的排列位置

语法格式:alter table 表名 modify 属性名1 数据类型 first|after 属性名2;alter table student modify sage varchar(50) first;select * fromstudent;alter table student modify sage varchar(50) after sname;======================================================================================

5、增加字段

语法格式:alter table 表名 add 属性名1 数据类型 [完整性约束条件] [first | after 属性名2];

完整性约束条件:是可选参数,用来设置新增字段的完整性约束条件

first:是可选参数,其作用是将新增字段设置为表的第一个字的

after:是可选参数,其作用是将新增字段添加到“属性名2”所指的字段后

如果执行的SQL语句中没有“first”或者“after 属性名2”参数指定新增字段的位置,则新增字段默认为表的最后一个字段alter table student add saddress varchar(50) after ssex;select * fromstudent;==============================================================================================================

6、删除字段

删除字段是删除表中已经定义好的表中的某个字段,删除后其字段所属的数据都会被删除

语法格式:alter table 表名 drop属性名;alter table student dropsaddress;select * fromstudent;==========================================================================================================

select * fromstudent;select * from student where sid in('1','3','5','7');select * from student where sid not in('1','3','5','7');select * from student where sid > 4;select * from student where sid != 4;select * from student where sid between 4 and 6;select * from student where sid not between 4 and 6;select * from student where sname like '%n%';select * from student where sname not like '%n%';=====================================================================================================

select * fromstudent;select * from student order by sid desc;select count(*) fromstudent;select sum(sid) fromstudent;select avg(sid) fromstudent;select max(sid) fromstudent;select min(sid) fromstudent;select count(*) from student group byssex;select sid as a, sname as b,sage as c, ssex as d fromstudent;select * from student limit 3;select * from student limit 1,2;====================================================================================增加一个字段,插入数据,并更改数据alter table student add saddress varchar(50) after ssex;select * fromstudent;update student set saddress = 'jiangsu' where sid = '1';update student set saddress = 'jiangsu' where sid = '2';update student set saddress = 'shanghai' where sid = '3';update student set saddress = 'shanghai' where sid = '4';update student set saddress = 'shanghai' where sid = '5';update student set saddress = 'beijing' where sid = '6';update student set saddress = 'beijing' where sid = '7';update student set saddress = 'anhui' where sid = '8';insert into student ( sid,sname,sage,ssex ) values ('9','shenweiwei','1989-10-18','nan');update student set saddress = 'jiangsu' where sid = '9';

一、将查询结果插入到表中

insert语句可以将一个表中查询到的数据插入到另外一个表中

语法格式:insert into 表名1 (属性列表1) select 属性列表2 from 表名2 where条件表达式;

表名1说明记录插入到哪个表中;

表名2表示记录是从哪个表中查询出来的;

属性列表1参数表示为哪些字段赋值;

属性列表2表示从表中查询出哪些字段的数据;

条件表达式参数设置了select语句的查询条件;

注意:使用这种方法时,必须保证属性列表1和属性列表2中的字段个数是一样的,而且每个对应字段的数据类型必须是一样的。create table student2( sid varchar(50),

snamevarchar(50),

sagevarchar(50),

ssexvarchar(50),

saddressvarchar(50)

);select * fromstudent2;delete fromstudent2;insert into student2 ( sid,sname,sage,ssex,saddress ) values ('11','fenglili2','1988-09-18','nv','jiangsu');insert into student2 ( sid,sname,sage,ssex,saddress ) values ('12','fenglili3','1988-08-18','nv','jiangsu');insert into student2 ( sid,sname,sage,ssex,saddress ) values ('13','fenglili4','1988-07-18','nv','shanghai');insert into student ( sid,sname,sage,ssex,saddress ) values ('11','fenglili2','1988-09-18','nv','jiangsu');insert into student ( sid,sname,sage,ssex,saddress ) values ('12','fenglili3','1988-08-18','nv','jiangsu');insert into student ( sid,sname,sage,ssex,saddress ) values ('13','fenglili4','1988-07-18','nv','shanghai');delete from student where sid in('11','12','13');select * fromstudent;insert into student(sid,sname,sage,ssex) select sid,sname,sage,ssex from student2 where sid = '11';select * fromstudent;insert into student(sid,sname,sage,ssex,saddress) select sid,sname,sage,ssex,saddress from student2 where sid in ('12','13');select * fromstudent;update student set saddress = 'jiangsu' where saddress is null;select * fromstudent;================================================================================================================================一、基本查询语句

select的基本语法格式如下:select 属性列表 from表名和视图列表[where 条件表达式1]

[group by 属性名1 [ having 条件表达式2]][order by 属性名2 [ asc | desc]]

属性列表参数表示需要查询的字段名;

表名和视图列表参数表示从此处指定的表或者视图中查询数据,表和视图可以有多个;

条件表达式1参数指定查询条件;

属性名1参数指按照该字段的数据进行分组;

条件表达式2参数满足该表达式的数据才能输出;

属性名2参数指按照该字段中的数据进行排序;排序方式由asc和desc这两个参数指出;

asc参数表示升序,这是默认参数,desc表示降序;(升序表示从小到大)

对记录没有指定是asc或者desc,默认情况下是asc;

如果有where子句,就按照“条件表达式1”指定的条件进行查询;如果没有where子句,就查询所有记录;

如果有group by子句,就按照“属性名1”指定的字段进行分组,如果group by后面带having关键字,那么只有

满足“条件表达式2”中知道的条件才能输出。group by子句通常和count()、sum()等聚合函数一起使用;

如果有order by子句,就按照“属性名2”指定的字段进行排序,排序方式由asc和desc两个参数指出;默认情况下是asc;

查询结果不重复:distinct关键字select * fromstudent;select distinct ssex fromstudent;select distinct saddress fromstudent;

分组查询:select * from student group byssex;select * from student group bysaddress;group by关键字与group_concat()函数一起使用,每个分组中指定字段值都显示出来:select ssex,GROUP_CONCAT(ssex) from student group byssex;select ssex,GROUP_CONCAT(sname) from student group byssex;select saddress,GROUP_CONCAT(sname) from student group bysaddress;group by关键字与集合函数一起使用:select ssex,count(ssex) from student group byssex;select saddress,count(saddress) from student group bysaddress;group by 关键字与"having条件表达式"一起使用,可以限制输出结果,只有满足条件表达式的结果才会显示:select ssex,count(ssex) from student group byssex;select ssex,count(ssex) from student group by ssex having count(ssex) >6;select saddress,count(saddress) from student group bysaddress;select saddress,count(saddress) from student group by saddress having count(saddress) >3;

注意:“having 表达式” 与 "where表达式"都是用来限制显示的,但是两者起作用的地方不一样;

"where表达式"用于表或者视图,是表和视图的查询条件;

“having表达式”作用于分组后的记录,用于选择满足条件的分组。group by 关键字与 withrollup 一起使用,会在所有记录的最后加上一条记录,这条记录是上面所有记录的总和。select ssex,count(ssex) from student group byssex;select ssex,count(ssex) from student group by ssex withrollup;select saddress,count(saddress) from student group bysaddress;select saddress,count(saddress) from student group by saddress withrollup;select * fromstudent;select * from student limit 4;select * from student limit 1,5;=======================================================================================================

create table score ( xh int(50),

kmvarchar(50),

cjint(50)

);select * fromscore;insert into score values(1,'shuxue',80);insert into score values(1,'yuwen',70);insert into score values(1,'yingyu',40);insert into score values(2,'shuxue',40);insert into score values(2,'yuwen',60);insert into score values(2,'yingyu',50);insert into score values(3,'shuxue',60);insert into score values(3,'yuwen',20);insert into score values(3,'yingyu',90);insert into score values(4,'shuxue',50);insert into score values(4,'yuwen',60);insert into score values(4,'yingyu',70);select xh,sum(cj) from score where xh = 1; 查询此同学的总成绩;select xh,sum(cj) from score where xh = 4;select xh,sum(cj) from score group byxh; 查询每一个同学的各科总和成绩;select km,max(cj) from score group bykm; 查询各个科目的最高成绩;select km,avg(cj) from score group bykm; 查询每一科目的平均成绩;select km,max(cj) from score group bykm; 查询每一科目的最高成绩;select km,min(cj) from score group bykm; 查询每一科目的最低成绩;=======================================================================================================================

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值