数据库学习与练习笔记

数据库学习与练习笔记（MySql ）

SQL标准语法

SQL练习

---

创建

标记简介：

[]: 可选项

<>:标识符

表 Create Table <表名>( <列名><数据类型>[列级完整性约束条件] [,<列名><数据类型>[列级完整性约束条件]] ... [,<表级完整性约束条件>] ); --列级完整性 --not null <非空约束> --unique <唯一约束> --表级完整性约束条件 --主键 Primary Key(<列名>[,<列名>]) --外键 Foreign key(<列名>)References <表名>(<列名>) 触发器 Create Trigger <触发器名> {Before|After}<触发事件> on<表名> Referencing new|old row as<变量> for each{row|statement} [when <触发条件>]<触发动作> 索引 Create [Unique][Cluster] Index <索引名> On <表名>( <列名>[<次序>] [,<列名>[<次序>]] ... );

显示

模式/数据库 --Mysql语法 show databases --所有数据库/模式 表 show tables --所有表

删除

表 Drop Table <表名> [Restrict|Cascade] --默认Restrict 索引 Drop Index <索引名> --Mysql 语法 Drop Index <索引名> On <表名>

修改

Alter Table <表名> [Add [Column]<新列名><数据类型>[完整约束条件]] --添加列 [Add <表完整性约束条件>] --添加表完整性约束条件 [Drop [Column]<列名>[Cascade|Restrict]] --删除列 --Cascade:自动删除引用了该列的其他对象，如视图; Restrict:若被其他对象应用，则拒绝删除该列属性； [Drop Constraint<完整性约束名>[Cascade|Restrict]] --删除完整性约束 [Alter|Modify Column <类名><数据类型>] --Mysql语法 Alter Table <表明> Modify Colum <属性名> <数据类型> --Mysql只支持Modify

查询

Select [All|Distinct] <目标表达式>[,<目标表达式>]··· From <表名或视图>[别名][,<表名或视图名>[别名]···]| (<Select 语句>)[As]<别名> [Where <条件表达式>] [Group By<列名1>[Having <条件表达式>]] [Order By<列名2>[Asc|Desc]] --1.目标列表单式的可选格式 --(1)* --(2)<表名>.* --(3)COUNT([Distinct|All]*) --(4)[<表名>.](属性列名表达式)[,[<表名>.](属性列名表达式)]... --属性列名表达式:属性列，作用于属性列的聚集函数和常量的任意算术运算（+，-，*，/）组成的运算公式 --2.聚集函数一般形式 --COUNT([DISTINCT|ALL]<列名>) --SUM([DISTINCT|ALL]<列名>) --AVG([DISTINCT|ALL]<列名>) --MAX([DISTINCT|ALL]<列名>) --MIN([DISTINCT|ALL]<列名>) --3.Where字句表达式的可选格式 --(1) / <属性列名> -- <属性列名> <算术运算符> | <常量> -- \ [ANY|ALL](Select语句) --(2) / <属性列名> / <属性列> -- <属性列名>[NOT]Between | <常量> And | <常量> -- \ (Select语句) \ (Select语句) --(3) / (<值1>[,<值2>]...) -- <属性列名>[Not] In | (Select语句) --(4) <属性列名>[Not] Like <匹配串> --(5) <属性列名>Is [Not] Null --(6) [Not] Exists (Select语句) --(7) / And [/And ] -- <条件表达式>| OR <条件表达式> [|OR <表达式> ]

插入数据

Insert Into <表名>[(<属性列1>[,<属性列2>]...)]Values(<常量1>[,<常量2>]...)

插入子查询数据

Insert Into <表名> [<属性列1>[,<属性列2>]] 子查询

修改数据

Update <表名> Set <列名>=<表达式>[,<列名>=<表达式>]... [Where <表达式>]

删除数据

Delete From <表名> [Where <条件>]

创建视图

Create View <视图名>[(<列名>[,列名]...)] As<子查询> [With Check Option]

删除视图

Drop View <视图名>[Cascade]

创建用户

Create User <用户名>@<主机IP>　Identified by<密码> --本地IP：localhost --任意主机IP：%

显示用户权限

show grants for <用户名>@<主机IP>

授权

Grant <权限>[,<权限>]... On <对象类型><对象名>[,<对象类型><对象名>]... To<用户>[,<用户>]... [With Grant Option] --Mysql Grant <权限>[,<权限>] On <数据库名>.<表名>[,<数据库名>.<表名>] To <用户名>@<主机名>,[<用户名>@<主机IP>]

收回

Revoke <权限>[,<权限>]... On <对象类型><对象名>[,<对象类型><对象名>]... From <用户>[,<用户>]... [Cascade|Restrict] --Mysql Revoke <权限>[,<权限>]... On <数据库名>.<表名>[,<数据库名>.<表名>] From <用户名>@<主机IP＞[,<用户名>@<主机IP>]

创建数据库模式的权限

Create User <用户名>[with][DBA|RESOURCE|CONNECT]

角色创建

create Role<角色名>

角色授权

Grant <权限>[,<权限>]... On <对象类型>对象名 TO <对象>[,<对象>]...

角色授予其他角色和用户

Grant <角色1>[,<角色2>]... TO <角色3>[,<用户>]... [With Admin Option]

角色权限的收回

Revoke <权限>[,<权限>]... On <对象类型><对象名> From <角色>[,<角色>]...

---

---

create schema DBTEST /*或*/create database DBTEST --创建数据库 show databases --显示所有数据库 use DBTEST --使用DBTest数据库 Create Table Student ( --创建Student表 Sno Char(9) Primary Key, /*学号 char 主键*/ Sname Char(20) Unique, /*姓名 char 唯一*/ Ssex Char(2), /*性别 char */ Sage Smallint, /*年龄 Smallint*/ Sdept char(20) /*系别 char*/ )Default Charset=utf8; Create Table Course( --创建Course表 Cno Char(4) Primary key,/*课程号 char 主键*/ Cname Char(40) Not Null,/*课程名称 char 非空*/ Cpno Char(4), /*先行课号 char*/ Ccredit Smallint, /*学分 Samllint*/ Foreign key (Cpno) References Course(Cno) /*外码（Cpno） 参考 Course（Cno）*/ )Default Charset=utf8; Create Table SC( --创建SC表 Sno Char(9),/*学号*/ Cno Char(4),/*课程号*/ Grade Smallint,/*成绩*/ Primary key(Sno,Cno),/*主键（Sno Cno）*/ Foreign key(Cno) References Course(Cno), /*外码（Cno）参考 Course(Cno)*/ Foreign key(Sno) References Student(Sno) /*外码 （Sno）参考 Student(Sno)*/ )Default Charset=utf8; Alter Table Student Add S_entrance Date --Student表添加S_entrance属性 Alter Table Student Modify Colum Sage Int --Student表修改Sage字段数据类型Int Alter Table Course Add Unique(Cname) --Course表修改Cname属性唯一 Create Unique Index Stusno On Student(Sno) --Student表上依据Sno升序创建索引Stusno Create Unique Index Coucno On Course(Cno) --Course表上依据Cno升序创建索引Coucno Create Unique Index SCno On SC(Sno ASC,Cno DESC) --SC表上依据Sno升序，Cno降序创建索引SCno Drop Index Stusno on Student --删除Student表上的索引Stusno Drop Table Student Restrict --限制性删除Student表 Drop Table Student Cascade --级联删除Student表 drop schema DBTEST --删除数据库DBTest drop database DBTEST --删除数据库DBTest --单表查询 Select Sno,Sname From Student Select Sname,Sno,Sdept From Student Select Sno,Sname,Ssex,Sage,Sdept From Student Select * From Student Select Sname,2020-Sage From Student Select Sname,'Year of BirthDay',2020-Sage,Lower(Sdept) From Student Select Sname Name,'Year of BirthDay:' Birth,2020-Sage Birthday,Lower(Sdept) Department From Student Select Sno From SC Select All Sno From SC --查询所有元组 Select Distinct Sno From SC --消除重复元组 Select Sname From Student where Sdept = 'CS' Select Sname,Sage From Student Where Sage < 20 Select Distinct Sno From SC Where Grade < 60 Select Sname,Sdept,Sage From Student Where Sage Between 20 And 23 Select Sname,Sdept,Sage From Student Where Sage Not Between 20 And 24 Select Sname,Ssex From Student Where Sdept NOT In ('CS','MA','IS') Select * From Student Where Sno Like '20121515121' Select * From Student Where Sname Like '张%' Select * From Student Where Sname Like '张_' Select Sname,Sno,Ssex From Student Where Sname Not Like '刘%' Select Cno,Ccredit From Course Where Cname Like 'DB/_Design' Escape '/' --转义 Select * From Course Where Cname Like 'DB/_%i__' Escape '/' Select Sno,Cno From SC Where Grade IS Null Select Sno,Cno From SC Where Grade IS Not Null Select Sname From Student Where Sdept='CS' And Sage<20 Select Sname,Ssex From Student Where Sdept='CS' Or Sdept='MA' Or Sdept= 'IS' Select Sno,Grade From SC Where Cno = '3' Order By Grade Desc Select * From Student Order By Sdept,Sage Desc Select Count(*) From Student Select AVG(Grade) From SC where Cno = '1' Select Max(Grade) From SC where Cno = '1' Select Sum(Ccredit) From Sc,Course Where Sno =' 201813137031' And SC.Cno =Course.Cno Select Cno,Count(Sno) From Sc Group By Cno Select Sno From SC Group By Sno Having Count(*)>3 Select Sno,Avg(Grade) From Sc Group By Sno Having Avg(Grade)>90 --连接查询 Select Student.*,SC.* From Student,SC Where Student.Sno = Sc.Sno Select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade From Student,SC where Student.Sno = SC.Sno Select Student.Sno,Sname From Student,SC Where Student.Sno = SC.Sno And SC.Cno = '2' And SC.Grade > '90' Select First.Cno,Second.Cpno From Course First,Course Second where First.Cpno = Second.Cno Select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade From Student Left Outer Join SC On (Student.Sno=SC.Sno) Select Student.Sno,Sname,Cname,Grade From Student,SC,Course Where Student.Sno = SC.Sno And SC.Cno = Course.Cno --嵌套查询 Select Sname From Student Where Sno IN (Select Sno From SC Where Cno = '2') Select Sno,Sname,Sdept From Student Where Sdept In (Select Sdept From Student Where Sname ='刘晨') Select Sno,Sname,Sdept From Student Where Sdept = (Select Sdept From Student Where Sname ='刘晨') Select Sno,Sname,Sdept From Student Where Sdept In ('CS') Select S1.Sno,S1.Sname,S1.Sdept From Student S1,Student S2 Where S1.Sdept=S2.Sdept And S2.Sname ='刘晨' Select Sno,Sname From Student Where Sno In (Select Sno From SC Where Cno In (Select Cno From Course Where Cname ='信息系统')) Select Student.Sno,Sname From Student,SC,Course Where Student.Sno=SC.Sno And SC.Cno = Course.Cno And Course.Cname ='信息系统' Select Sno,Cno From SC O Where Grade >= (Select Avg(Grade) From SC T Where T.Sno =O.Sno) Select Sname,Sage From Student where Sage <Any (Select Sage From Student Where Sdept = 'CS ') And Sdept <> 'CS ' Select Sname,Sage From Student Where Sage < (Select Max(Sage) From Student Where Sdept ='CS ') And Sdept <> 'CS' Select Sname,Sage From Student Where Sage <All (Select Sage From Student Where Sdept = 'CS') And Sdept <> 'CS' Select Sname,Sage From Student Where Sage < (Select Min(Sage) From Student Where Sdept = 'CS ') And Sdept <> 'CS' Select Sname From Student Where Exists (Select * From SC Where Sno = Student.Sno And Cno = '1') Select Sname From Student ,SC Where Student.Sno =SC.Sno And Cno = '1' Select Sname From Student Where Not Exists (Select * From SC Wheres Sno=Student.Sno And Cno = '1') Select Sno,Sname,Sdept From Student S1 Where Exists (Select * From Student S2 Where S1.Sdept = S2.Sdept And S2.Sname = '刘晨') Select Sname From Student Where Not Exists(Select * From Course where Not Exists (Select * From SC Where SC.Sno =Student.Sno And Cno = Course.Cno)) Select Distinct Sno From SC SCX Where Not Exists (Select * From SC SCY Where SCY.Sno ='201515122' And Not Exists (Select * From SC SCZ Where SCZ.Sno = SCX.Sno And SCZ.Cno = SCY.Cno)) --集合查询 Select * From Student Where Sdept = 'CS' Union Select * From Student Where Sage <= 19 Select Snon From SC where Cno = '1' Union Select Sno From SC Where Cno = '2' Select Sno From SC Where Cno ='1' And Sno In (Select Sno From SC Where Cno = '2') --Select * From Student Where Sdept = 'CS' Intersect Select * From Student Where Sage <= 19 //Mysql不支持 Select * From Student Where Sdept = 'CS' And Sage <=19 --Select * From Student Where Sdept = 'CS' Except Select * From Student Where Sage <=19 //Mysql不支持 Select * From Student Where Sdept = 'CS' And Sage >19 Select SC.Sno,Cno From SC ,(Select Sno ,Avg(Grade) avg_grade From SC Group By Sno ) As Avg_sc Where SC.Sno = Avg_sc.Sno And Avg_sc.avg_grade Select Sname From Student,(Select Sno From SC Where Cno = '1')As SC1 Where Student.Sno = SC1.Sno --数据更新 Insert Into Student(Sno,Sname, Ssex,Sdept,Sage)Values ('20121528','陈东','男','IS',18) Insert Into Student Values('20202','张成名','M',18,'CS') Insert Into SC(Sno,Cno)Values('20202','1') Insert Into SC Values ('20121528', '1',NULL) Create Table Dept_age( --系平均年龄 Sdept Char(15). Avg_avg Smallint ); Insert Into Dept_age(Sdept,Avg_age) Select Sdept,AVG(Sage) From Student Group By Sdept Update Student Set Sage = 22 Where Sno = '20121515' Update Student Set Sage =Sage+1 Update SC Set Grade=0 Where Sno In (Select Sno From Student Where Sdept ='CS') Delete From Student Where Sno = '20202' Delete From SC Delete From SC Where Sno In (Select Sno From Student Where Sdept = 'CS') Insert Into Sc(Sno,Cno,Grade) Values ('2020','1',NULL ) Insert Into SC(Sno,Cno) Values ('2020','1') Update Student Set Sdept = NULL Where Sno = '2020' Select * From Student Where Sname IS Null OR Ssex Is Null OR Sage Is Null OR Sdept Is Null Select Sno From SC Where Grade < 60 And Cno = '1' Select Sno From SC Where Grade <60 And Cno = '1' Union Select Sno From SC Where Grade Is Null ANd Cno = '1' Select Sno From SC Where Cno = '1' And (Grade < 60 OR Grade IS Null) --视图 Create View IS_Student As Select Sno,Sname,Sage From Student Where Sdept = 'IS' Create View Is_Student AS Select Sno,Sname,Sage From Student Where Sdept = 'IS' With Check Option Create view IS_SI(Sno,Sname,Grade) As Select Student.Sno,Sname,Grade From Student ,SC Where Student.Sno = Sc.Sno And SC.Cno ='1' Create View IS_S2 AS Select Sno,Sname,Grade From IS_SI Where Grade >= 90 Create View BT_S(SNo,Sname,Sbirth) As Select Sno,Sname,2014-Sage From Student Create View S_G(Sno,Gavg) As Select Sno,Avg(Grade) From SC Group By Sno Create View F_Student(F_Sno,name,sex,age,dept) As Select * From Student Where Ssec = 'W' Drop View IS_SI Cascade Select Sno,Sage From IS_Student Where Sage <20 Select IS_Student.Sno,Sage,Sname From IS_Student ,SC Where IS_Student.Sno = SC.Sno And SC.Sno ='1' Select * From S_G Where Gavg >=90 Select Sno,Avg(Grade) From SC Group By Sno Having Avg(Grade)>90 Select * From (Select Sno ,Avg(Grade) Gavg From SC Group By Sno)As S_G Where Gavg >=90 Update Is_student Set Sname='HHH' Where Sno='1' Insert Is_Student Values ('2022','sdfsdfasdfasdf',20) Delete From IS_Student Where Sno = '2022' --数据库安全问题 Create User 'U1'@'localhost' Identified by '123' Grant Select On dbtest.student to 'U1'@'localhost' Grant All Privileges On dbtest.student to 'U2'@'localhost','U3'@'localhost' Grant All Privileges On dbtest.course to 'U2''localhost','U3'@'localhost' Grant Update(Sno),select on dbtest.student to 'U4'@'localhost' Grant Insert on dbtest.sc to U3@'localhost' with Grant option Revoke Update(Sno) On dbtest.Student from 'U4'@'localhost' Revoke Insert On dbtest.Sc from 'U5'@'localhost' Revoke Insert On dbtest.Sc from 'U6'@'localhost' --完整性约束条件设置 create Table Student ( Sno Char(9)Primary Key, /**列级完整性*/ Sname Char(20) Not Null, Ssex Char(2), Sage SmallInt, Sdept Char(20) ); Create Table Student ( Sno Char(9), Sname Char(20)Not null, Ssex Char(2), Sage SmallInt, Sdept Char(20) Primary key(Sno) /**列级完整性*/ ); Create Table Sc ( Sno Char(9) not Null, Cno Char(4) not null, Grade Smallint, Primary key(Sno,Cno)/**只能在表级定义主码*/ ); Create Table Sc ( Sno Char(9) Not null, Cno Char(4)Not Null , Grade Smallint, primary key(sno,cno), /**表级定义实体完整性*/ Foreign key(Sno) References Student(sno), /**表级定义参照完整性*/ Foreign key(Cno) References Course(Cno) /**表级定义参照完整性*/ ); --显示说明数据库违反参照完整性约束条件时应对策略 Create Table SC ( Sno Char(9), Cno Char(4), Grade SmallInt, primary Key(Sno,Cno), Foreign key(sno) references Student(sno)on delete cascade on update cascade, Foreign Key(Cno) references Course(Cno) on delete no action on update cascade ); create table sc ( sno char(9) not null,/**sno属性不允许空值*/ cno char(4) not null,/**Cno属性不允许空值*/ Grade smallint not null,/**Grade属性不允许空值*/ primary key(sno,cno)/** 表级定义实体完整性时，暗含sno,cno 属性不允许为空值， 在列级不允许取空值的定义可不写*/ ... ); create table Dept ( Deptno numeric(2), Dname char(9)unique not null, Location char(10), primary key(Deptno) ); /**Mysql 不支持check用法 create table student( sno char(9) primary key, sname char(9) not null, ssex char(2) check (ssex in '男','女'), sage smallint, sdept char(20) ); create table sc( sno char(9), cno char(4), Grade smallint check(Grade >=0 and Grade <=100), primary key(sno,cno), foreign key (sno) references student(sno), foreign key(cno) references course(cno) ); create table student ( sno char(9), sname char(8)not null, ssex char(2), sage smallint, sdept char(20), primary key(sno), check(ssex='女' OR sname not like 'Ms.%') ) create table student( sno numeric(6), constraint C1 check (sno between 9000000 and 99999999), sname char(20), constraint c2 not null, sage numeric(3), constraint c3 check(sage<20), ssex char(2), constraint c4 check(ssex in ('男','女')), constraint sudentkey primary key(sno) ); create table teacher( Eno Numeric(4)primary key, Ename char(10), Job char(8), Sal numeric(7,2), Deptno numeric(2), Constraint Teacherkey foreign key(Deptno) References Dept(deptno), constraint c1 check(sal+deduct>=3000) ); Alter table student drop constraint c4; Alter table student drop constraint c1 check(sno between 900000 and 999999); Alter table student student drop constraint c3; Alter table stident add constraint c3 check(sage <40); */