13.mysql数据类型

从系主任表中找出系主任的姓名。
select mname from t_man;

找出系部管理表中系的名字
select dname from t_dept;

找出学生的姓名和年龄。
select sname,sage from t_student;

从学生表中找出学生的学号、姓名、英文名。
select sid,sname,sename from t_student;

SeLeCt Sname from t_student;

从学生表中查找前5行学生的姓名和年龄。

select sname,sage from t_student limit 5;

从学生表中查找第4行到第10行学生的姓名和年龄。
select sname,sage from t_student limit 3,7;

从学生表中查询第5行到第11行学生的学号和姓名及年龄。
select sname,sid,sage from lianxi.t_student limit 4,7;

use a;

select * from b.x;

select distinct sclass from t_student;

在学生表中找出 学生姓名和班级编号，并去掉里面的重复行。
select distinct sclass,sname from t_student;

 

select sage + 10 from t_student;

从学生表中找出学生的姓名、年龄、10年以后的年龄，并取前5行。
select sname+10,sage,sage+10 from t_student limit 5;

select sname, sage + ifnull(sclass,0) from t_student;

 

 

select sname as 姓名 from t_student limit 5;

select sname,sage+10 as '10年以后年龄' from t_student;

 

String sql = " select sname as '姓名' from t_student ";

 

select sname as '姓 名' from t_student;

在学生表中找出学生的学号、姓名(取别名叫name)、高考分数+100(取别名最终分数).结果取第3行到第7行。
select sid,sname as name,sscore+100 as 最终分数 from t_student limit 2,5;

 

select concat(sid,'的姓名是：',sname,sscore) as 详细信息 from t_student;

 

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2016-02-29 15:57:04 |
+---------------------+

select now() from t_student;

select 3+4;

mysql> select 3+4;
+-----+
| 3+4 |
+-----+
| 7 |
+-----+
学生表中找出所有学生的姓名、班级、系编号。如果班级为空则给1班，如果系编号为空则2号系。取
前6行记录。
select sname ,ifnull(sclass,1) as 班级, ifnull(did,2) as 系编号 from t_student limit 6;

学生表中找出所有学生的学号、姓名、高考分数+50、年龄+10的结果（如果高考分数为空则给1分，
如果年龄为空则给20）。最终结果取3到10行。

select sid,sname,ifnull(sscore,1) + 50 as 最终分数 ,ifnull(sage,20) + 10 from t_student limit 2,8;

 

 

select concat(did,'的系主任编号是：',mid) from t_dept;

 

找出所有学生的姓名和高考分数，并按照分数从低到高排序。
select sname,sscore from t_student order by sscore;

找出最高分学生的所有信息。

select * from t_student order by sscore desc limit 10;

找出学生的姓名、年龄(如果为空则10)、班级(如果为空则1)、分数，结果按照
年龄和班级升序。

select sname,ifnull(sage,10),ifnull(sclass,1) as a,sscore from t_student order by a,sage asc;

 

select sname,sage as 年龄 ,sclass as 班级 from t_student order by sscore;

找出最小年龄的学生的姓名、年龄、学号、分数（如果没有年龄，则默认20岁）。
select sname,ifnull(sage,20) as age ,sid,sscore from t_student order by age limit 1;

找出陈卓的所有信息。
select * from t_student where sname='陈卓';

找出年龄为20岁的学生的所有信息，并按照分数降序排序。
select * from t_student where sage=20 order by sscore desc;

 

找出500以上的学生的所有信息。
select * from t_student where sscore > 500;

找出分数600分以下的学生的姓名、学号、分数，（如果没有分数则默认给1分，），
结果按照年龄降序，最终取前3结果。
select sname,sid,ifnull(sscore,700) as a from t_student where sscore < 600 order by sage desc ;

 

找出年龄18到20岁之间的学生的所有信息。

select * from t_student where sage between 20 and 18;

找出年龄是20岁以下，分数是500分以上的学生的所有信息。

select * from t_student where sage < 20 and sscore > 500;

找出分数500分以上，年龄18到20之间且是1班的学生。

select * from t_student where ( sscore > 500 )and (sage between 18 and 20) and (sclass=1);

 

where sage >= 18 and sage <= 20;

找出学生是1班 或者年龄22以上，或者分数500分以上的所有男生信息。
select * from t_student where( sclass =1 or sage>22 or sscore > 500) and ssex='男';

找出年龄为空的学生的所有信息
select * from t_student where sage is null;

 

找出学号是1201、1205、1209、1211的学生的信息
select * from t_student where not sid=1201 or sid = 1205 or sid = 1209 or sid= 1211;

select * from t_student not where sid in (1201,1205,1209,1211);

查找英文名是 apple 、oracle、tea、ice学生的姓名、学号、电话、英文名，并按照年龄升序。

select sname,sid,sphone,sename from t_student where sename in ('apple' ,'orange','tea','ice') order by sage;

where not ******

select * from t_student where (not (sage > 18 or sscore > 500)) and (not sclass != 1) ;

select * from t_student where sage <=18 and sscore<=500 and sclass=1;

 

 

找出姓李的(且名字只有两个字)学生的信息

select * from t_student where sname like '_李_';

找出英文名字中含有字符ee的学生姓名和英文名，按照班级排序。
select sname,sename from t_student where sename like '%ee%' order by sclass;

找出英文名字中含有字符a 和字符e的学生姓名和英文名，按照班级排序。
select sname,sename from t_student where sename like '%a%e%' or sename like '%e%a%' order by sclass;

 

 

select * from t_student where sename regexp '[abc]';

 

select * from t_student where sename regexp 'ee|ea';

查找英文名中包含ta或Ta 或tA 或TA。 where sename regexp '[Tt][Aa]';

 

select * from t_student where sename regexp '[a-z]';
Mate

 

英文名字中不包含a或b或c
select * from t_student where not sename regexp '[abc]';

英文名字中含有.

select * from t_student where sename regexp '\-';

电话号码含有3个6
select * from t_student where sphone regexp '6{2,3}';

select * from t_student where sname regexp '[[:lower:]]';

'[abc]*'

'[abc]?'

 

 

select * from t_student where sename regexp '^A'; like '%A%'

regexp '^1[3578][0-9]{9}$';

regexp '^[0-9]{1}$'

'^a$'

 

select right(left(sename ,3),1) as a,sename,sname from t_student;

查询每个学生的姓名和英文名及英文名的长度。length（str）
select sname,sename,length(sename) from t_student;

select * from t_student where sename regexp 'a';

select sename ,lower(sename) from t_student;

select sename ,ltrim(sename) from t_student;

 

 

 

输出每个学生姓名和出生的月份
select sname,month(sbir),sbir as 月份 from t_student;

select sname,monthname(sbir),sbir as 月份 from t_student;

输出今天的月份。

select month(now());

select dayname(now());

输出学生的姓名、生日、出生当天星期几。
select sname,sbir,dayname(sbir) from t_student;

select WEEKOFYEAR(now());

select date_format(now(),'%Y年abc-%m月def-%d天aaa');

输出学生的姓名、生日及生日格式 （**月**号****年）
select sname,sbir,date_format(sbir,'%m月----%d---号%Y年----') from t_student;

输出平均成绩
select avg(sscore) as 平均成绩 from t_student;

select avg(sage) from t_student;

输出表中有多少学生。 count(字段)
select count(sid),avg(sage) from t_student;

select count(*) from t_student;

求最大年龄

select max(sage),sname from t_student;

求最大年龄的学生姓名

select sname from t_student where sage = (select max(sage) from t_student);
Invalid use of group function

 

整个表分数的总和
select sum(sscore) from t_student;

表中男生年龄的总和 及 平均年龄
select sum(sage) sex='男';
select avg(sage), sum(sage) from t_student where ssex='男';

select count(*) from t_student where ssex='男';

分组查询 子查询 数据类型
增删改
索引 视图 存储过程 触发器

 

select count( distinct sage) from t_student;

 

 

查询500分以上的女生有多少个
select count(*) from t_student where sscore>500 and ssex='女';

select max(sscore) from t_student where ssex='男';
select max(sscore) from t_student where ssex='女';

select max(sscore) from t_student where ssex='女';

按照班级分组，输出个组的编号和总人数。
select sclass,count(sid) from t_student group by sclass;

 

 

按照性别分组，求每个组的名称和最大年龄

select ssex,max(sage) from t_student group by ssex;

按照系编号分组，输出每个组的系编号和500分以上学生的个数。
select did,count(*) from t_student where sscore>500 group by did ;

 

按照系编号分组，输出每个组的系编号和500分以上学生的个数，将结果按照系编号降序排列。

select did,count(*) from a_student where sscore>500 group by did order by did desc limit 2;

 

按照性别和班级分组，求每个组的人数,将结果按照组的人数降序排序。
select ssex,sclass,count(*) as a from t_student group by ssex,sclass order by a desc;

 

select sname ,max(sage) from t_student group by sname;

按照性别、系编号、班级编号分组，求每个组的人数。
select ssex,did,sclass,count(*) from t_student group by ssex,did,sclass;

select ssex,sclass ,count(*) from t_student group by ssex,sclass with rollup ;

 

按照性别、系编号分组，输出人数大于5的组的编号和人数。

select ssex,did,count(*) from t_student
group by ssex,did
having count(*)>3 and count(*)<6;

select count(*) from t_student where count(*)>5;

按照性别、系编号将500分以上的学生分组，输出人数大于4小于8的组的编号和人数。

select ssex,did,count(*) from t_student
where sscore>300
group by ssex,did
having count(*)>4 and count(*)<8
order by did desc
limit 1;

 

在学生信息表中将系编号和班级编号不为空的学生按照班级分组，
统计每个组有多少个不同的年龄(即去掉年龄的重复)并取别名“不同年龄”，
输出每个组的班级编号和不同年龄的个数，并按照年龄个数升序排列，取前两个结果

select sclass,count(distinct sage) as 不同年龄 from t_student
where not( sclass is null or did is null)
group by sclass
order by 不同年龄 asc
limit 2;

 

select **** from **** where***(select **** from **** where***)

找出跟王涛在一个班的学生信息。
select * from t_student where sclass = (select sclass from t_student where sname='王涛');

 

找出跟学号为1203或1205一样大的学生姓名和年龄。
select sname,sage from t_student where
sage in (select sage from t_student where sid = 1203 or sid = 1205);

 

找出学号为1210的学生的系名。

select dname from t_dept where did = (select did from t_student where sid = 1210);

找出刘某某的系主任名字。
select mname from t_man where mid = (select mid from t_dept where did= (select did from t_student where sname='刘某某') )
//select did from t_student where sname='刘某某';
//select mid from t_dept where did = (select did from t_student where sname='刘某某');

select mname from t_man
where mid=(select mid from t_dept
where did = (select did from t_student
where sname='刘某某'));

 

找出比刘某某年龄大且分数比1203高的学生信息。

select * from t_student where sage>(select sage from t_student where sname='刘某某')
and sscore>(select sscore from t_student where sid = 1203) ;

select sname from (select sname,sid,sename from t_student where ssex='女') as a;

select sname from t_student where ssex='女';

 

找出最高分学生的姓名、学号、分数。
select sname,sid,sscore from t_student
where sscore=(select max(sscore) from t_student );

 

统计平均分以上有多少人。
select count(*) from t_student where sscore>(select avg(sscore) from t_student);

找出比全校平均年龄小的每个班的平均年龄

select avg(sage) from t_student gorup by sclass having avg(sage)< (select avg(sage) from t_student);

 

select sname from t_student where sage = (select * from t_student where sid = 1201);

找出比3班每个都大的学生信息。

 

select * from t_student where sage > any (select sage from t_student where sclass =2);

查询年龄与Tea或Ice或Eat年龄相同的学生的姓名。

select sname from t_student
where sage in (select sage from t_student where sename in ('Tea' ,'Ice','Eat') );

 

找出比考试分数500以上每个学生年龄都大的学生的姓名。
select sname from t_student
where sage > all (select sage from t_student where sscore > 500);

 

找出每个学生的姓名系名。
select sname,dname from t_student,t_dept; 笛卡尔积

select sname,dname from t_student,t_dept where t_student.did = t_dept.did;

找出每个学生的姓名系编号、系名。
select sname,did,dname from t_student,t_dept where t_student.did = t_dept.did;

ERROR 1052 : Column 'did' in field list is ambiguous

 

select sname,t_student.did,dname from t_student,t_dept where t_student.did = t_dept.did;

 

找出500分以上的学生的学号、姓名、系编号、系名、系主任编号、系主任姓名。

select sid,sname,t_student.did,dname,t_dept.mid,mname from t_student,t_dept,t_man
where t_student.did = t_dept.did and t_dept.mid = t_man.mid and sscore > 500;

 

select a.sid,a.sname,a.did,b.dname,b.mid,c.mname
from t_student as a,t_dept as b,t_man as c
where a.did = b.did and b.mid = c.mid and a.sscore > 500
order by a.sage asc
limit 5
;

输出最低分学生的姓名和系的名字

select sname ,b.dname from t_student as a,t_dept as b
where a.did = b.did and sscore = (select min(sscore) from t_student);

 

在学生信息表中将1班和3班的学生且年龄是18岁以上的学生按照系编号分组，
如果系编号为空则改为0。统计每个组的人数个数、最大年龄、最高分数（分别取别名），
输出结果中人数大于1的组的信息，并按照系编号降序排列取第一个结果。

select ifnull(did,0),count(*) as 人数,max(sage) as 最大年龄,max(sscore) as 最高分数
from t_student where sclass in(1,3) and sage > 18 group by did having
人数>1 order by did desc limit 1;

在学生表和系部表中找出学生的姓名和系部名字（使用内部联结）
select sname,dname from t_student inner join t_dept on t_student.did = t_dept.did;

select sname,dname from t_student,t_dept where t_student.did = t_dept.did;

外部联结： 左联结 left outer join on 、 右联结 rigth outer join on

select sname,dname from t_student left outer join t_dept on t_student.did = t_dept.did;

select sname,dname from t_student right outer join t_dept on t_student.did = t_dept.did;

 

DML DCL DDL DQL

 

create table 表名(
字段名 类型,
字段名 类型,
....
primary key(字段)

 

);

 

create table user(
id int auto_increment,
username varchar(10) ,
password varchar(10) not null,
age int,
primary key(id),
foreign key(age) references m(age)
);

 

drop table user;

 

insert into 表名 values(值1，值2，值3...);

insert into user values(3,'zhangsan','123456',19);

insert into 表名(字段名1,字段名2,...) values(值1,值2,...)

insert into user(username,password) values('aaa1','444'),('ccc2','555');

 

a b

//表的结构必须相同。
insert into a select * from b;

insert into copystudent(id,name,ename) select sid,sname,sename from t_student where sscore > 500 and ssex='女';

 

update 表名 set **** = ****；

 

update user set username = 'ooo',password='999' where id = 2;

 

 

删除学生表中所有的女生
delete from t_student where ssex = '女' or ...;

 

 

insert into 表名(属性1,属性2,...) values(值1,值2...)

inesrt into a(column1,column2) select column1,coumun2 from b;

 

update 表名 set 属性=新值,属性=新值.... where 属性=值 ;

 

delete from 表名 where 属性=值;

 

创建数据库： create databases 数据库名;
创建表：
create table tableName(
column1 类型 not null auto_increment,
column2 类型,
...

primary key(column1),

foreign key (column2) references tableName2(column2)

)

 

 

 

 

 

 

 

 

select * from t_student where sname='刘某某';

select * from t_student where sid=1203;

 

select * from t_student where did = 2;

 

 

 

create table a(

id int,
name varchar(10),
index in_id(id),
primary key(name)

)

 

 

create UNIQUE index in_a on t_student(sname);

explain select * from (select sname,sename from t_student) as a;

 

 

explain select sid,sname from t_student union select did,dname from t_dept;

 

 

查询所有学生的学号、姓名、系编号、系名、系主任编号、系主任名。

select sid,sname,t_dept.did,dname,t_dept.mid,mname from t_student,t_dept,t_man
where t_student.did=t_dept.did and t_dept.mid=t_man.mid;

 

 

create view s_d_m as select sid,sname,t_dept.did,dname,t_dept.mid,mname from t_student,t_dept,t_man
where t_student.did=t_dept.did and t_dept.mid=t_man.mid;

 

查找学号是1210以上的学生姓名和系部名。
select sname,dname from s_d_m where sid > 1210;

 

把500分以上的学生信息单独定义为一个视图。
create view s_score_bigger_500 as select * from t_student where sscore > 500;

 

把500分到600分之间的女生学号、姓名、英文名、分数、电话找出定义为一个视图。
create view s_girls as select sid as 学号,sname as 姓名,sename as 英文名,sscore as 分数,sphone as 电话 from t_student
where ssex='女' and sscore between 500 and 600;

找出上面视图中学号为1228的学生所有信息。

select * from s_girls where 学号 = 1228;

create or replace view aaa as select ....

 

 

select * from t_student,t_dept,t_man where t_student.did = t_dept.did and t_dept.mid =t_man.mid;

 

解耦 降低耦合度

降低冗余

void a(){
......
}

a();
a();

create procedure p_a()
begin
...
end

 

 

 

create procedure p_a()
begin
select * from t_student where sscore > 500;
select * from t_man123;
select * from t_dept;
end

 

delimiter

 

if(n1 == null){
n1 = 10;
}

create procedure tat(n1 int, n2 int)
begin
declare n3 int;
if n1 is null then
set n1 = 10;
end if;
set n3 = n1 + n2;
select n3;
end

 

 

create procedure p_b()
begin
select sname as 姓名 from t_student ;
select * from t_dept;
end

call p_b();

drop procedure if exists p_b;

 

 

 

create procedure p_c(n1 int)
begin
select * from t_student where sid = n1;
end

 

create procedure p_d( out n2 int)
begin
select max(sscore) into n2 from t_student;
end

 

create procedure p_m(in n1 int, out n2 int)
begin
select sscore into n2 from t_student where sid=n1;
end

 

 

create procedure p_n(inout n1 int)
begin
select sscore into n1 from t_student where sid=n1;
end

int i = 10;

void a(int a){
a = 11;
}

a(i);

一般在大型的数据库系统中，一组为了完成特定功能的sql语句集，
存储在数据库中，经过第一次编译后，再次调用不需要再次编译，
用户只需要指定存储过程名字并给定参数就可以执行完成任务。

create procedure 存储过程名( in|out|inout 参数名 参数类型)
begin
sql语句;
sql语句;
end

 

一个存储过程，所带参数1 --》 系编号 ， 第二个参数 ----》最大年龄

create procedure p_get(in n1 int, out n2 int)
begin
select max(sage) into n2 from t_student where did=n1;
end

 

create procedure p_get2(n1 int ,n2 int)
begin
select * from t_student limit n1,n2;
end

当在学生表中添加一个学生时，
就在系部表中将当前学生所在的系
的人数增加一个。
create trigger t_a after insert
on t_student for each row
update t_dept set dnum=dnum+1 where did=new.did;

//把刘某某所在的系中dnum增加一
update t_dept set dnum = dnum+1 where did=(select did from t_student where sname='刘某某')

 

 

insert into t_student(sid,sname,did) values(111,'zhangsan',1);

create trigger tr_student_delete_after after delete on t_student
for each row
update t_dept set dnum = dnum-1 where did=old.did;