MySQL学习之旅

第一节

1. 连接数据库
2. 创建数据库

Create database 

数据库名称（以字母数字下划线组成的字符串，但不要以数字开头）;

    3.查看数据库

 show databases;

     4.查看字符集

Show variables like 'character%';

     5.查看端口号

   Show variables like 'port';

   6.查看数据存储路径

   Show variables like ‘datadir’;

第二节

1.使用数据库

use demol;

2.新建test表

create table test(id int(11),name varchar(50));

desc test;

3.新建student表

create table student(sno char(11) primary key,sname varchar(20) not null);

desc student;

4.新建course表

create table course

    -> (

    -> cno varchar(20) primary key,

    -> course_name varchar(50) not null,

    -> cpno varchar(20),

    -> course_credit decimal(4,1)

-> );

desc course;

5.查看表

show tables;

6.在student表添加ssex列

alter table student

    -> add ssex char(2) not null;

desc student;

7.修改默认值

alter table student

    -> alter ssex set default '男';

desc student;

8.给表重命名test改成test2

方法1：

alter table test2

       ->rename to test;

//更改表名

方法2：

rename table test to test

第三节

创建数据表：时间戳（此刻到计算机元年之间相差的（毫秒) 秒数 ）

9.更改student表中的ssex的数据类型为枚举类型（'male','female'）,默认值为'male'     

 alter table student
    -> modify ssex enum('male','female') not null;

      alter table student
    -> alter ssex set default 'male';

10.向test表添加主键约束

alter table test
    -> add primary key(id)；

11.添加外键约束

     alter table sc
    -> add foreign key(cno) references course(cno);

12.设置组合主键

  alter table sc
     add primary key(sno,cno);

DDL(数据定义语言) 表结构 :create alter drop

DML(数据操纵语言) 表内容 : insert update delete

DQL:select * from 表名  SELECT <字段名表>FROM <表或视图名>WHERE <查询条件>

13.

修改列名

alter table goods_order change column receive_name user_name varchar(100);

第四节

1.为表添加数据

insert into 表名(字段1，字段2，…)   values(值1，值2，…)

简写方法：可以省略字段部分，但需要和字段一一对应       

insert into test values(4,'王五');

省略字段且只给部分值： 

insert into test values(5,NULL);

一次添加多值：

insert into test(id) values(6),(7),(8),(9),(10);

mysql独有的添加记录的语句：

insert into test
  -> set id=11

  -> name='王美丽';

把表1的内容添加到表2里面：

insert into 表2 select * from 表1;

2.为表修改数据

update 表名 set name='jerry' where id=2;

update test set age=20 where id=5 or id=7;

在同一条件下修改多个属性值：

update test set name='tom',age=23 where id=6;

修改多个表：

update 表名1，表名2…

      

set demo.name='qin',test.name='qin'
where test.id=8 and test.id=demo.id;

replace语句可以在插入数据之前将于新记录冲突的旧记录删除

replace into test(id,name,age) values(1,'王五',24);

3.删除表的数据

delete from test where id=10;

truncate table 表名

第五节

1.普通索引（属性值可以重复）

create index idx_name on test(name);

2.创建唯(惟)一性索引（属性值不能重复，比较适合候选码）

3.表不存在时可以创建为：

create table test4(id int,name varchar(10),
primary key(id),
unique(name)
);     

 //建表的同时创建唯一索引

比较几种索引

（1）.一个表只能创建一个索引是：主键（索引）

（2）.一个表可以创建多个普通或者唯一索引 

（3）.创建为索引的属性列值必须唯一的是：主索引和唯一索引，值可以重复的是普通索引

创建表时为索引起名：

create table test5(id int,name varchar(10),constraint mypri primary key(id),constraint myuiq unique(name));

可以根据索引名称，进行删除操作

删除主键（索引），不需要使用名称

alter table test5 drop primary key;

删除其他索引，需要使用名称

alter table test 5 drop index myuiq;

drop index myuiq on test5;

3.主键（主索引）

对列：

 create table test2(id int primary key);

对表：

create table test3(id int,primary key(id));

第六节

/*从test表中查询一个属性列（字段）的值，name是一个关键字，
因此不建议使用name作为字段名
*/

select name from test;

#distinct关键字可以去除字段值中的重复值

select distinct name from test;
select name,age from test;

update test set name='张浩浩' where id=5

第七节

#查询某个表的全部数据

select * from test;

#查询test表一共有多少记录

select count(id) from test;
select count(id) as nums from test;

#年龄最大的同学的姓名及其年龄

select name,max(age) from test;
select name,max(age) as 最大年龄 from test;

#where  条件表达式
#表达式的表示方法：字段名 运算符 值
#字段名 运算符 值and|or 字段名 运算符 值 同一列只能用or

#查询年龄等于20岁的同学

select * from test where age=20;

#查询年龄大于20岁的同学

select * from test where age>20;

#查询年龄大于等于22岁的同学

select * from test where age>=22;

#查询年龄为23岁或24岁的同学

select * from test where age=23 or age=24;

#查询年龄比较多使用in运算符 in运算符相当于同一个字段的or运算

select * from test where age in (20,22,24,26,28);

#查询年龄在20——22岁之间的同学

select * from test where age>=20 and age<=22;

#BETWEEN 值1 AND 值2 可以确定范围

select * from test where age BETWEEN 20 and 22;

#查询name为空值的

select * from test where name is null;

#查询name不为空值的

select * from test where name is not null;

#用like运算符实现模糊查询

select * from test where name like '王%';

#'_'代表单个任意字符

select * from test where name like '王__'or name like'王_';

#查询含i的姓名

select * from test where name like '%i%';

#按照年龄从高到低排序显示某个表的全部记录

select * from test order by age; #默认为升序asc
select * from test order by age desc;  #降序desc

#先按照年龄(第一个)降序，再按照id(第二个)降序

select * from test order by age desc,id desc;

#年龄最大的三位同学的信息

select * from test order by age desc limit 3;

#从第三个开始取年龄最大的三位同学的信息  

select * from test order by age desc limit 2,3;  #(start,length) start从0开始，length表示取几个，若从0开始取则可以省略start

第八节

#查询student表中出生年份是2002的学生信息

select * from student
where year(stu_birth)='2002';

select stu_no,stu_name,stu_birth from student
where year(stu_birth)='2002';

select stu_no,stu_name,year(stu_birth) as birthyear from student
where year(stu_birth)='2002';

#查询成绩最高的信息//查询score表的最高分，平均分
#出现在select里只能和其他聚集函数一起用，不能出现任何其他属性

select max(score),avg(score) from score

#分组进行统计，每门课程的最高分

select course_num,max(score) from score group by course_no; 

#查询平均分低于60分的学生的学号及平均分

select stu_no,avg(score) as 平均分 from score 
#where avg(score)<=60 整个查询的条件
group by stu_no 
having avg(score)<=60 ;

#查询选修了002和003两门课且平均分低于60的学号和平均分

select stu_no,avg(score) as 平均分 from score where course_no in('010002','010003')
group by stu_no having avg(score)<=60;

#查询秦建兴是哪个班的？

select stu_name,class_name
from student,class
where stu_name='秦建兴' and
student.class_no=class.Class_no;

#查询所有男同学来自哪些系

select stu_name,stu_sex,dep_name
from student,class
where stu_sex='男' and
student.class_no=class.Class_no;

第九节

#两张表的等值连接，有同名列存在

select * from student,class
where student.class_no=class.Class_no;

#改成自然连接，选取其中一张表的同名列，用表名.字段名进行选取，其他列任选

select student.class_no,
stu_name,class_name,dep_name from student,class
where student.class_no=class.Class_no;

select student.*,class_name,dep_name from student,class
where student.class_no=class.Class_no;

#查询所有学生的姓名，课程名及其成绩

select stu_name,course_name,score from student,course,score where student.stu_no=score.stu_no and course.course_no=score.course_no;

删除课程号为010002的课程

delete from score where
course_no='010002';

#inner join(默认连接),left join(额外留下左表中不与右表等值的信息),right join,full join

select course_name,score from course  left join score on course.course_no=score.course_no;

第十节

#查询哪些课是先修课

select * from course where Course_no in(select cpno from course);
select * from course as ca join course as cb on ca.cpno=cb.course_no;

#自连接

select DISTINCT(cb.
course_name) from course as
ca join course as cb on ca.
cpno=cb.course_no
where EXISTS(select * from
course as cb where ca.
course_no=cb.cpno)

#查询没有选课的学生学号及其姓名(相关子查询,外层查询给内层查询提供数据，如果根据外层查询提供的数据能查到结果，则内层查询返回为真，否则返回为假。外层查询的值根据内层查询结果为真或假来决定留下哪些记录)
 

select stu_no,stu_name from
 student where not exists
 (select * from score where
student.stu_no=score.Stu_no);

第十一节

#删除视图

SELECT * from view_score;

drop view view_class

#创建一个可以查看计算机系班级信息的视图

create view view_class as
select class_no as 班级号,
as 系部名称 from class where
Dep_name='计算机系' with check option;

select * from view_class;
insert into view_class(班级号,班级名,系部名称)
values ('223','大数据1班','计算机工程学院')

#视图

create view view_score as
select student.stu_no,Stu_name
,course.Course_no,course.Course_name,score 
from student join score on student.Stu_no=score.Stu_no
join course on course.Course_no=score.Course_no

第十二节

#编程基础
#@@系统变量
#@会话变量
#1.根据输入的课程号，输出其课程名

create function courename(cno char(6))returns varchar(50)reads sql data
begin
declare cname varchar(50);
case cno
when '010001' then set cname='大学计算机基础';
when '010002 ' then set cname='数据结构';
when '010003' then set cname='数据库原理';
else set cname='非计算机类课程';
end case;
return cname;
end;
select courename('010023');

#2、根据学号，返回其年龄

drop function get_age;
delimiter $$
create function get_age(sno char(10)) returns int
reads sql data
begin
declare myage int;
select year(now())-year(stu_birth) into myage from student where stu_no=sno;
return myage;
end$$;
delimiter;

#通过查询获得人名对应的学号，将其传给函数

select stu_no into @sno from student
where stu_name='沈柯辛';
select get_age(@sno);

#存储过程
#1、基础（与函数的不同）
#1）过程可以没有返回值，但是函数必须有;
#2)参数区别：函数只有传入的参数；过程有in，out，inout

#2.根据姓名和课程名，查询其学号，姓名，课程名及成绩。

delimiter$$
create procedure 
proc_stu_score(in sname char(
10),in cname char(20))
reads sql data
begin
select student.stu_no,
stu_name,course_name,score
from student join score on
student.stu_no=score.stu_no
join course on score.
course_no=course.course_no
where stu_name=sname and
course_name=cname;
end$$
delimiter;

#调用过程

call proc_stu_score(
'秦建兴','数据库原理');

#3、根据课程号，统计其平均分

drop procedure proc_stu_avg;
delimiter$$
create procedure proc_stu_avg
(in sname char(10),out 
avg_score float)
reads sql data
begin
select avg(score) into 
avg_score from student join
score on student.stu_no=score.stu_no
where stu_name=sname group by score.stu_no; 
end$$
delimiter;

#调用

call proc_stu_avg('秦建兴',@avg_score);
select round(@avg_score,2);

CREATE PROCEDURE proc_update_score(IN kch  char(6))
Modifies sql data 
BEGIN 
DECLARE xh char(10);
DECLARE cj float;
DECLARE state char(10);
DECLARE update_cj_cursor 
CURSOR FOR
  SELECT stu_no,score FROM score WHERE course_no=kch;
DECLARE continue HANDLER FOR 1329 SET state='遍历完成';
   OPEN update_cj_cursor;
REPEAT
    FETCH update_cj_cursor INTO xh,cj;
    IF (CJ>=55 AND CJ<60) THEN SET cj=60;
    END IF;
  UPDATE score SET score=cj WHERE stu_no=xh AND course_no=kch;
    UNTIL state='遍历完成'
  END REPEAT;
  CLOSE update_cj_cursor;
END $$
DELIMITER ;