# DataBase: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 = 25where Sno = ‘2016213‘ # 删除内容 delete from student where Sno = ‘2016218‘ # 选择并映射指定的列 select Sname, Sage from student # 去掉重复的行 distinctselect 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 或:orselect * from student where Sno like‘2016%‘ and Sage < 21select * 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,5select * from student limit 5; select * from student # exists 返回true or false where exists ( select * from teacher where Tno = ‘1111‘ ) # 建立视图 create view student_view asselect 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 INTbegin # 声明一个变量以及对应类型 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语句; # endcreate 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 # 一个或多个语句列表,列表里面的每条语句必须用分号隔开 # endcreate trigger insert_stu after inserton 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;