mysql语法的应用

第一阶段：

-- 使用命令创建课程表: create TABLE
create table t_course(
c_no char(6) not null primary key,
c_name varchar(20),
c_type varchar(20),
c_credit DECIMAL(2,1),
c_time int(2)
);

desc t_course; -- 显示t_course表的结构

-- 使用命令向t_course表添加数据: insert
insert into 表名(列列表) values(值列表);
insert into t_course(c_no,c_name,c_type,c_credit,c_time) values('340101','javaSE','专业必修课',4.0,90);
insert into t_course(c_no,c_name,c_type,c_credit,c_time) values('340102','jdbc','专业必修课',4.5,20);
insert into t_course(c_no,c_name,c_type,c_credit,c_time) values('340103','jquery','公共基础课',3.0,30);
insert into t_course(c_no,c_name,c_type,c_credit,c_time) values('340104','Spring','专业必修课',4.0,50);
insert into t_course(c_no,c_name,c_type,c_credit,c_time) values('340105','Mybatis','专业必修课',3.5,40);

-- -- 使用命令创建选课表: create TABLE
drop table if EXISTS t_result;
create table t_result(
r_s_no char(11) not null ,
r_c_no char(6) not null ,
r_result decimal(4,1) comment '学生选课成绩' ,
primary key(r_s_no,r_c_no)
);

-- 使用命令向t_result表添加数据: insert
desc t_result;
insert into t_result(r_s_no,r_c_no,r_result) VALUES('18302012301','340101',80);
insert into t_result(r_s_no,r_c_no,r_result) VALUES('18302012301','340102',97);
insert into t_result(r_s_no,r_c_no,r_result) VALUES('18302012302','340101',76);
insert into t_result(r_s_no,r_c_no,r_result) VALUES('18302012302','340103',88);
insert into t_result(r_s_no,r_c_no,r_result) VALUES('18302012302','340103',88);

alter table t_result add primary key(r_s_no,r_c_no);

 

第二阶段：

-- 使用sql命令新增学生记录：insert into ... values(....)
desc t_student;
-- insert into t_student(s_no,s_name,s_gender,s_birth,s_profession,s_class,s_phone,s_addr,s_photo) values(值列表);
-- 等价于：
insert into t_student values('18302012302','吴凡','M','1990/7/2','java软工','202001','18298089098','乌市沙依巴克区',LOAD_FILE('d:/img/pic0.jpg'));

-- 删除学生记录：delete from 表名|视图 where 条件
delete from t_student where s_no='18302012303';

-- 创建t_teacher表(教师编号t_no、教师姓名t_name、性别t_gender、出生年月t_birth、职称t_title、
-- 政治面貌t_politic、电话号码t_phone、家庭信址t_addr)
create table t_teacher(t_no char(7) primary key not null
,t_name varchar(20)
,t_gender char(1) default 'M'
,t_birth datetime
,t_title varchar(20) default '教授'
,t_politic varchar(10) default'党员'
,t_phone char(11)
,t_addr varchar(30)
);
desc t_teacher;

-- 查看t_teacher表的记录：
select * from t_teacher ;
select * from t_student;
select * from t_course;

-- 创建t_work表:
create table t_work(
w_t_no char(7) not null
,w_c_no char(6) not null
,w_class char(6) not null
,w_avg DECIMAL(5,2)
,primary key (w_t_no,w_c_no,w_class)
);

-- 完整性控制：
-- 1、实体完整性控制：唯一性、非空---->通过主键(primary key)实现|唯一键 (Unique)---->解决一个表中的数据无重复
-- 2、参照完整性控制: 表间数据的一致性 ---->通过外键(foreign key)来实现:
-- 3、自定义完整性控制: 约束实现---默认约束

-- 使用命令创建外键：
1、建表时添加外键：
drop table if EXISTS t_work;
create table t_work(
w_t_no char(7) not null
,w_c_no char(6) not null
,w_class char(6) not null
,w_avg DECIMAL(5,2)
,primary key(w_t_no,w_c_no,w_class)
,constraint FK_T_NO foreign key (w_t_no) REFERENCES t_teacher(t_no) on delete CASCADE on update CASCADE
,constraint FK_C_NO foreign key (w_c_no) REFERENCES t_course(c_no) on delete CASCADE on update CASCADE
);

drop table if EXISTS t_work;
create table t_work(
w_t_no char(7) not null
,w_c_no char(6) not null
,w_class char(6) not null
,w_avg DECIMAL(5,2)
,primary key(w_t_no,w_c_no,w_class)

);
2、修改表时添加： CONSTRAINT -- 约束
alter table t_work add constraint FK_T_NO
foreign key (w_t_no) REFERENCES t_teacher(t_no) on delete CASCADE on update CASCADE ;
alter table t_work add constraint FK_C_NO
foreign key (w_c_no) REFERENCES t_course(c_no) on delete CASCADE on update CASCADE;

-- 3、自定义完整性约束：默认约束
alter table t_course modify c_type varchar(20) default '公共基础课';
alter table t_course alter column c_type varchar(20) default '公共基础课'; -- SQL Server

-- 4、mysql的自动标识列： id列----自动增长的特性，常用作主键列
-- 向一个表中添加一个标识列:
alter table t_student add s_id int auto_increment primary key not null ;
-- 删除主键：
alter table t_student drop primary key;
-- 删除外键：
alter table t_result drop Foreign key fK_sno ;

-- 向含有自动增长列的表中添加数据:
insert into t_student(s_no,s_name,s_gender,s_birth,s_profession,s_class,s_phone,s_addr,s_photo) values('18302012304','喻雅楠','F','1996/7/2','java软工','202001','15098089098','乌市沙依巴克区',LOAD_FILE('d:/img/pic0.jpg'));
insert into t_student(s_id,s_no,s_name,s_gender,s_birth,s_profession,s_class,s_phone,s_addr,s_photo) values(4,'18302012304','喻雅楠','F','1996/7/2','java软工','202001','15098089098','乌市沙依巴克区',LOAD_FILE('d:/img/pic0.jpg'));

delete from t_student where s_id=5;

identity(1,1)
序列

 

 第三阶段：

-- 在指定字段后插入新的字段--->修改表结构
alter table t_work add t_passnums int(3) after w_class;
alter table t_work CHANGE t_passnums w_passnum int(3);
alter table t_work drop column w_passnum;

alter table w_work RENAME to t_work;

-- 更新表中的数据----update
update 表名 set 列名=列值，列名=列值 。。。 where 条件
update t_result set r_result=82 where r_s_no='18302012303' and r_c_no='340101'; -- not | and |or
-- 将所有01课程的学生的成绩都加10分
update t_result set r_result=r_result+10 where r_c_no='340101';
update t_course set c_no=concat('4501',SUBSTR(c_no FROM 5 FOR 2));
update t_course set c_no=concat('4501',SUBSTR(c_no,5)),c_time=c_time+5;

select concat('1','2','3') from dual; -- dual伪表
select concat(concat('1','2'),'3');
|| ---- oracle字符串连接运算符

select SUBSTR("340101",2,3);

select 1+1 from dual;
select 1+1;

-- 为t_result表添加外键r_s_no
alter table t_result add constraint FK_sNO
FOREIGN key (r_s_no) REFERENCES t_student(s_no) on DELETE CASCADE on update cascade;

-- 将t_student表的s_no列设置为唯一键：unique;
alter table t_student add constraint UK_sNO UNIQUE (s_no);

-- 向t_teacher表中添加数据
desc t_teacher;
select * from t_teacher;
delete from t_teacher;
insert into t_teacher(t_no,t_name,t_birth,t_phone,t_addr)
values('2000001','zhang','1965-3-7','13908080876','乌市沙依巴克区')
,('2000002','wang','1975-6-7','15208080123','乌市沙依巴克区')
,('2000003','li','1980-6-7','13808080456','乌市沙依巴克区')
,('2000004','cheng','1976-9-23','13208080123','乌市沙依巴克区')
,('2000005','wu','1983-7-8','18908080156','乌市沙依巴克区');

update t_teacher set t_gender='F' where t_no='2000002' or t_no='2000005' ;
-- 等价于：
update t_teacher set t_gender='F' where t_no in('2000002','2000005') ;

-- 向t_work表中添加数据
desc t_work;
insert into t_work values
('2000001','450101','202001',72.5)
,('2000002','450101','202002',54.65)
,('2000003','450105','202001',64.5)
,('2000003','450103','202002',82.5)
,('2000004','450101','202003',60.5)
,('2000003','450104','202001',54)

select * from t_work;

-- 查询命令：select
-- 单表查询：
select 查询项 from 表|视图 where 条件

select s_no,s_name,s_gender,s_class from t_student; -- 投影操作

select * from t_student;

select * from t_student where s_gender='M'; -- 选择操作

select s_no,s_phone from t_student where s_gender='M';

select * from t_student where year(s_birth)>1990;

select * from t_student where month(s_birth)=8 and day(s_birth)=12; -- 查询生日是今天(8.12)的学生信息
update t_student set s_birth='1994-8-12' where s_name='王鑫';

select * from t_student where month(s_birth)=month(CURDATE()) and day(s_birth)=day(CURDATE()); -- 查询生日是当天的学生信息

select CURDATE();
select now();
select CURRENT_DATE();

select * from t_result where r_result>=80 and r_result<=90;
select * from t_result where r_result BETWEEN 80 and 90;

-- 138打头 ----模糊查询---like ----标准SQL语言
select * from t_student where SUBSTR(s_phone,1,3)='138';
select * from t_student where s_phone like '138%'; -- % 通配符|模式符, 表示任意零个或多个字符
select * from t_student where s_phone like '138________'; -- _ ：表示任意一个字符

select * from t_student where s_name like '王%';
select * from t_student where s_name like '王__';

-- mysql中使用正则表达式: 不是标准sql语言
select * from t_student where s_phone REGEXP '^138';
select * from t_student where s_name REGEXP '^王';

select * from t_student where s_name REGEXP '斌$';
select * from t_student where s_name REGEXP '鑫$';

select * from t_student where s_name REGEXP '凡';

select * from t_student where s_phone REGEXP '139*';
select * from t_student where s_phone REGEXP '139+';
select * from t_student where s_phone REGEXP '13[9|8]'; -- 只有一个字符是9或8，[89] [8|9]
select * from t_student where s_phone REGEXP '138|139';
select * from t_student where s_phone REGEXP '1[358]' ; -- []表示[]中的任一个字符
select * from t_student where s_phone REGEXP '13|15|18' ;

select * from t_student where s_phone REGEXP '139{4}[0-9]{5}'; -- \d表示数字字符，但mysql不支持\d

select * from t_student where s_phone REGEXP '[0-9]{11}' ;

select * from t_student where s_class REGEXP '2020.' ;

select * from t_student where s_phone REGEXP '1[^3]'; -- 这个命令不能表示第一位是1第二位不是3是phone
-- 正确的：
select * from t_student where s_phone REGEXP '1[^3][0-9]{9}';

 第四阶段：

-- 单表查询：
-- 空值的查询： is | is not
select * from t_course where c_time is null;
select * from t_course where c_time is not null;

select s_no,s_name,s_gender,s_phone from t_student where s_addr in('乌市沙依巴克区','乌市新市区','乌市天山区') ;
select s_no,s_name,s_gender,s_phone from t_student where s_addr like '%沙依巴克%' or s_addr like '%新市区%' or s_addr like '%天山区%' ;
select s_no,s_name,s_gender,s_phone from t_student where s_addr REGEXP '沙依巴克' or s_addr REGEXP '新市区' or s_addr REGEXP '天山区' ;

-- 查询项中出现的计算列或表达式:
-- 查询学生的出生年份：
select s_no,s_name,s_gender,year(s_birth) from t_student ;
select s_no,s_name,s_gender,year(s_birth) yearOfBirth from t_student ;

select right(s_no,2) from t_student; -- 注意：如果是char(20),而实际数据不足20个
select substr(s_no,10,2) from t_student;

select right(s_phone,5) from t_student; -- mysql数据库中char型数据的宽度决定其存贮，但读取时，mysql会自动截取其尾部空格

create table test(col1 char(5) ,col2 varchar(60)) ;
delete from test;
insert into test values('夺圧圧 ','你好啊'); -- utf-8：一个汉字是一个字符，但在内存中占3个字节
select concat(col1,'@') from test;

select length(col1) from test; -- 测读取出来的col1在内存中的字节数
select concat(col2,'@') from test;

select length(col2) from test;

-- 查询中使用统计函数(聚合函数)
-- count()---统计记录数(行数)
-- sum()---统计总和
-- avg()---统计平均值
-- max()---统计最大值
-- min()---统计最小值

-- 统计男生的人数:
--select count(s_no) males from t_student where s_gender='M';
select count(*) males from t_student where s_gender='M';
-- 统计'450101'课程的选课人数
select count(r_s_no) rs from t_result where r_c_no='450101';

-- 统计专业必修课的数目
select count(c_no) nums from t_course where c_type='专业必修课';

select count(*) counts, sum(r_result) sumResult,avg(r_result) avgResult,max(r_result) maxResult,min(r_result) minResult from t_result where r_s_no='18302012303';

-- 查询学生来自于几个区
select count(DISTINCT s_addr) from t_student ; -- 3

select DISTINCT s_addr from t_student;

-- 查询中使用排序: order by 子句 ----order by 排序关键 ASC|DESC
select * from t_student order by s_birth desc; -- 仅能位于当前的select的最后

-- 查询各个区的学生的年龄大小信息:
select * from t_student order by s_addr asc ,s_birth desc ; -- 双重排序

select '乌市沙依巴克区'>'乌市新市区';

-- 分组统计汇总: group by 分组子句 ******
select count(r_s_no) as rs from t_result where r_c_no='450101';
select count(r_s_no) as rs from t_result where r_c_no='450103';
select count(r_s_no) as rs from t_result where r_c_no='450104';
select count(r_s_no) as rs from t_result where r_c_no='450105';

select r_c_no,count(r_s_no) as rs from t_result group by r_c_no;

select s_gender,count(s_no) rs from t_student group by s_gender;

-- 统计每个学生的所选课程的总分
select r_s_no,sum(r_result) zf from t_result group by r_s_no;

-- 对分组统计结果进行筛选: having 条件 ----必须与group by 连用
-- 查询总分高于150的学生的学号
select r_s_no,sum(r_result) zf from t_result group by r_s_no having zf>=120 ;
select r_s_no from t_result group by r_s_no having sum(r_result)>=150 ;
-- where 与 having ：
-- 查询男生中总分高于120的学生的学号
select r_s_no,sum(r_result) zf from t_result where r_s_no!='18302012304' group by r_s_no having zf>=120 ;

 第五阶段：

-- 多表查询：
1、嵌套查询：要查询的数据在一个表中，但条件涉及到多个表

select * from t_result where r_s_no = (select s_no from t_student where s_name='王鑫');

select * from t_result where r_s_no in(select s_no from t_student where s_gender='M');
-- 查询1号课程中与王鑫同学的成绩相同的学生的姓名
select s_name from t_student
where s_no in(select r_s_no from t_result
where r_result=(select r_result from t_result
where r_s_no=(select s_no from t_student where s_name='王鑫'
) and r_c_no='450101'
) and r_c_no='450101'
) and s_name!='王鑫';

(1)查询1号课程中与王鑫同学的成绩
select r_result from t_result where r_s_no=(select s_no from t_student where s_name='王鑫') and r_c_no='450101'
(2)查相同成绩的学生的学号
select r_s_no from t_result where r_result=(select r_result from t_result where r_s_no=(select s_no from t_student where s_name='王鑫') and r_c_no='450101'
) and r_c_no='450101' ;
(3)查学生的姓名

-- 查询选课人数超过2人的课程的课程信息
select * from t_course where c_no in
(select r_c_no from t_result group by r_c_no having count(r_s_no)>=2);

(1)每个课的选课人数
select r_c_no,count(r_s_no) from t_result group by r_c_no
(2)查询选课人数超过2人的课程号
select r_c_no from t_result group by r_c_no having count(r_s_no)>=2;
(3)查课程信息

-- 查询与王鑫同学的javaSE课程成绩相同的学生的姓名
select s_name from t_student
where s_no in(select r_s_no from t_result
where r_result=(select r_result from t_result
where r_s_no=(select s_no from t_student where s_name='王鑫'
) and r_c_no=(select c_no from t_course where c_name='javaSE')
) and r_c_no=(select c_no from t_course where c_name='javaSE')
) and s_name!='王鑫';

2、连接查询：要查询的数据在两个或两个以上的表中
-- 查询已选课的男生的选课信息(sno,sname,sclass,cno,result)
select s_no,s_name,s_class,r_result from t_student s,t_result r where s.s_no=r.r_s_no and s_gender='M'; -- 以笛卡积为基础的等值连接
select s_no,s_name,s_class,r_result from t_student s inner join t_result r on s.s_no=r.r_s_no where s_gender='M';

-- 查询男生的选课信息(sno,sname,sclass,cno,cname,ccredit,result)
select s_no,s_name,s_class,c_no,c_name,c_credit,r_result from t_student s,t_result r,t_course c where s.s_no=r.r_s_no and r_c_no=c_no and s_gender='M'; -- 以笛卡积为基础的等值连接
select s_no,s_name,s_class,r_result
from t_student s inner join t_result r on s.s_no=r.r_s_no
inner join t_course c on r_c_no=c_no
where s_gender='M';

 

第六阶段：

-- 外连接的查询：
左外：left outer join
右外：right outer join
全外：full outer join -- mysql不支持全外连接
-- 查询所有男生的选课信息：
select s.*,r.* from t_student s left join t_result r on s.s_no=r.r_s_no where s_gender='M';
select s.*,r.* from t_result r right join t_student s on s.s_no=r.r_s_no where s_gender='M';

select * from t_result where r_s_no = (select s_no from t_student where s_name='王鑫');

select r.* from t_student s,t_result r where s.s_no=r.r_s_no and s_name='王鑫';

-- 查询每个学生的平均成绩: sname avggrade
select r_s_no sno,avg(r_result) avggrade from t_result group by r_s_no;

SELECT
s_name,
avg(r_result) AS avggrade
FROM
t_student s,
t_result r
WHERE
s.s_no = r.r_s_no
GROUP BY
s_name;

-- 查询与"王鑫"同学所在区域相同的其他学生(sno,sname,sgender)

SELECT
*
FROM
t_student
WHERE
s_addr IN (
SELECT DISTINCT
(s_addr)
FROM
t_student
WHERE
s_name = '王鑫'
)
AND s_name != '王鑫';

-- 讨论：要将上述嵌套查询转换为连接查询： 自身连接查询
select s2.s_name from t_student s1,t_student s2 where s1.s_addr=s2.s_addr and s1.s_name='王鑫';
SELECT s1.s_name from t_student s1,t_student s2 WHERE s1.s_addr=s2.s_addr AND s2.s_name='王鑫' and s1.s_name!='王鑫';

-- 查询每个学生的选课成绩高于其平均成绩的课程的课程号
select cno from t_result where grade>=(查询当前记录对应的学生的平均成绩)

select * from t_result a where r_result>=(select avg(r_result) from t_result b where b.r_s_no=a.r_s_no ); -- 内外层互相关

select * from t_result;

select avg(r_result) from t_result b where b.r_s_no='18302012302'

-- 一个查询的结果做另一个查询的数据来源: From (子查询 )
-- 查询平均成绩最高的课程的课程名及其平均成绩 （注意:聚集函数不可以嵌套）
SELECT
c.c_name,
b.maxavg
FROM
t_course c,
(
SELECT
r_c_no,
avg(r_result) maxavg
FROM
t_result
GROUP BY
r_c_no
HAVING
avg(r_result) = (
SELECT
max(avggrade)
FROM
(
SELECT
r_c_no,
avg(r_result) avggrade
FROM
t_result
GROUP BY
r_c_no
) a
)
) b
WHERE
c.c_no = b.r_c_no;

-- 查询指定范围的记录
select * from t_result order by r_result desc LIMIT 0,3;
select * from t_result order by r_result desc LIMIT 3,3;
分页查询：
pageNo=1
pagecount=3
select * from t_result order by r_result desc LIMIT 3*(pageNo-1),pageCount;
1 0
2 3
3 6
4 9