Mysql基本操作记录
时间：2021-07-01 10:21:17
帮助过：24人阅读
：Mysql Tool：Navicat
# 创建学生信息表
create table Student (
# 学号作为主键，varchar是可变长字符串
Sno VARCHAR(
20)
primary key,
# 使用default定义默认值
Sage int default 18,
Sname VARCHAR(
20)
)
# 创建老师表
create table Teacher (
Tno VARCHAR(
20)
primary key,
Tname VARCHAR(
20),
Tage INT,
AcademyNo VARCHAR(
20),
# 创建外键约束
constraint Fk_Teacher_Academy
foreign key(AcademyNo)
references Academy(AcademyNo)
)
# 创建课程表
create table Course (
# 列级定义主键
Cno VARCHAR(
20)
primary key,
Tno VARCHAR(
20),
Cname VARCHAR(
50)
)
# 添加约束
alter table Course
add constraint Fk_Course_Teacher
foreign key(Tno)
references Teacher(Tno);
# 创建成绩表
create table Sc (
Sno VARCHAR(
20),
Cno VARCHAR(
20),
Score FLOAT,
# 表级定义多个主键
primary key (Sno, Cno),
# 添加外键并级联操作
constraint Fk_Sc_Student
foreign key(Sno)
references Student(Sno)
on delete cascade on update cascade,
# 默认删除跟修改都是restrict
constraint Fk_Sc_Course
foreign key(Cno)
references Course(Cno)
)
# 查看创建的表
select * from Student
# 修改表操作，#增加一列
alter table Teacher
add column Tage
VARCHAR(
3);
# 删除指定列
alter table Teacher
drop column Tname;
# 修改某一列
alter table Teacher
change column Tage Tage
int;
# 删除表
drop table Teacher
# Mysql常用五类约束类型：
# not null：非空约束，指定某列不为空
# unique： 唯一约束，指定某列和几列组合的数据不能重复
# primary key：主键约束，指定某列的数据不能重复、唯一
# foreign key：外键，指定该列记录属于主表中的一条记录，参照另一条数据
# check：检查，指定一个表达式，用于检验指定数据
# 插入内容
insert into student
VALUES(
‘2016211‘,
21,
‘tom‘);
# 插入指定内容，注意，主键不为空
insert into student(Sno, Sname)
VALUES(
‘2016205‘,
‘jerry‘);
# 更新内容
update student
set Sage
= 25
where Sno
= ‘2016213‘
# 删除内容
delete from student
where Sno
= ‘2016218‘
# 选择并映射指定的列
select Sname, Sage
from student
# 去掉重复的行 distinct
select distinct Sage
from student
# 使用where语句筛选数据
select * from student
where Sage
> 21
# 指定范围 (not)between .. and .. 包含等号
select * from student
where Sage
between 21 and 25
# 使用 in 查找属性值属于指定集合的元组
select * from student
where Sname
in(
‘tom‘,
‘LiMing‘)
# 字符匹配 like, _代表任意单个字符，%代表任意长度
select * from student
where Sno
like ‘2016211‘
select * from student
where Sname
like ‘j%‘
select * from student
where Sname
like ‘t_m‘
# 转义字符 \, 如 \_hell 表示 _hell
select * from student
where Sname
like ‘m\_ary‘
# 涉及空值的查询
select * from student
where Sage
is NULL
# 与:and 或:or
select * from student
where Sno
like‘2016%‘ and Sage
< 21
select * from student
where Sno
=‘2016215‘ or Sage
>= 24
# order by , DESC降序，ASC升序
select * from student
where sno
like‘2016%‘
order by Sage
desc
# 常用聚集函数
# COUNT()：统计个数
# sum()：计算总和
# avg()：平均值
# max()：最大值
# min()：最小值
# 统计不同的年龄个数
select count(
distinct Sage)
from student
# 计算平均年龄
select avg(Sage) 平均年龄
from student
# having的使用作用，类似于where，但可以对聚集函数使用，结合group by使用
select AcademyName 学院,
count(s.AcademyNo) 人数
from student s, Academy a
# 等值连接
where s.AcademyNo
= a.AcademyNo
# 通过学院代号分组
group by s.AcademyNo
# 筛选分组
having count(s.AcademyNo)
>= 2
# 嵌套查询，例：查询选修了数据库原理的所有学生信息
select * from student
# 如果学生学号在子查询的集合里面则判断为 true
where Sno
in (
# 子查询获取的是一个包含所有学生学号的集合
select s.Sno
from sc s, course c
where s.Cno
= c.Cno
and Cname
= ‘数据库原理‘
)
select * from student
# 如果把集合直接给出，也就类似于上一个嵌套查询的效果
where Sno
in(
‘2016211‘,
‘2016212‘)
# 使用limit筛选数据 limit 开始下标(0开始) 条数
# 筛选第 1~2条数据
select * from student limit
0,
2;
# 筛选前三条数据，相当于limit 0,
5
select * from student limit
5;
select * from student
# exists 返回true
or false
where exists (
select * from teacher
where Tno
= ‘1111‘
)
# 建立视图
create view student_view
as
select Sno 学号, Sname 姓名
from student
# 像基本表一样对视图查询
select * from student_view
# 删除视图
drop view student_view
# check短语使用，但Mysql中check不强制执行
create table people (
Pid varchar(
20)
primary key,
Pname varchar(
30),
# 当然也可以跟PK等一样表级定义
Psex varchar(
2)
check (Psex
in (
‘男‘,
‘女‘))
)
# 授予权限：grant 权限1,权限2.. on 对象类型 对象名 to 用户1,用户2...
grant select,
insert on table student
to user1
# 收回权限：revoke 权限1,权限2.. on 对象类型 对象名 from 用户1,用户2...
revoke delete on table student
from user1
# 函数以及变量的使用
# create function 函数名(
[参数])
returns 返回值类型
# begin
# declare 变量名 变量类型...
# sql语句;
# return 值;
# end;
create function getStudentAge(Id
varchar(
20))
returns INT
begin
# 声明一个变量以及对应类型
declare age
int;
# 赋值或者set age = (
select Sage
from student
where Sno
= Id);
select Sage
from student
where Sno
= Id
into age;
# 返回值
return age;
end
# 调用函数获取内容
select getStudentAge(
‘2016211‘);
# drop function 函数名
drop function getStudentAge1;
# 存储过程的使用,类似函数
# create procedure proc_getStudentAge(
in 变量名 类型,out(返回变量) 变量名 类型)
# begin
# Sql语句;
# end
create procedure proc_getStudentAge(Id
varchar(
20))
begin
select * from student
where Sno
= Id;
end
# in表示传入变量，out表示传出变量
create procedure proc_getStudentAge2(
in Id
varchar(
20), out age
int)
begin
select Sage
from student
where Sno
= Id
into age;
end
# 执行存储过程
call proc_getStudentAge(‘2016211‘);
# 执行存储过程获取数据并使用数据
call proc_getStudentAge2(‘2016211‘,
@age);
select @age;
# 删除存储过程
drop procedure proc_getStudentAge;
# 触发器的使用，new表示新数据，old表示原数据
# create trigger 触发器名称
[before | after] [insert | update | delete]
# on 表名
for each row
# begin
# 一个或多个语句列表，列表里面的每条语句必须用分号隔开
# end
create trigger insert_stu after
insert
on student
for each row
begin
# 自变量
declare age
int;
# new.Sno 是指新添元组的Sno
set age
= (
select Sage
from student
where Sno
= new.Sno);
insert into sc
values(new.Sno,
‘51‘, (
100-age));
end
# 测试上述触发器
insert into student
values(
‘2016221‘,
20,
‘oppo‘,
‘17‘);
# 删除触发器
drop trigger insert_stu;
Mysql基本操作记录
标签：strong group by 删除 对象 oppo gets 不同 rev opp
