mysql练习.docx
- 文档编号:11585550
- 上传时间:2023-03-19
- 格式:DOCX
- 页数:12
- 大小:21.18KB
mysql练习.docx
《mysql练习.docx》由会员分享,可在线阅读,更多相关《mysql练习.docx(12页珍藏版)》请在冰豆网上搜索。
mysql练习
我是在MySQL数据库中做的测试,不同的数据库有一定的差别。
MySQL中模式就是数据库
SHOWDATABASES;
showdatabases;
罗列所有数据库名称
CREATEDATABASE<数据库名>
createdatabaseTEST;
创建名为TEST的数据库
DROPDATABASE<数据库名>
dropdatabaseTEST;
删除名为TEST的数据库
USE<数据库名>
useTEST;
使用名为TEST的数据库
SHOWTABLES
showtables;
显示当前数据库所有表格
SHOW[FULL]COLUMNSFROM<表名>
show[full]columnsfrom<表名>
在MySQL数据库中显示表的结构,如果加上full则更加详细
DESC<表名>
descTEST;
查看表TEST的结构,同showcolumnsfromTEST
SHOWCREATETABLE<表名>
showcreatetableTEST;
查看表TEST的所有信息,包括建表语句
创建基本表:
CREATETABLE<表名>
(<列名><数据类型>[列级完整性约束条件],
<列名><数据类型>[列级完整性约束条件],
………………
);
createtableSTUDENT
(
studentIdint(30)primarykey,
namevarchar(255),
addressvarchar(255)
);
创建名为STUDENT的表格,有属性studentId,name,address,其中studentId为主键
createtableTEACHER
(
teacherIdint(30),
namevarchar(255),
ageint(20),
studentIdint(30),
primarykey(teacherId),
foreignkey(studentId)referencesSTUDENT(studentId)
);
创建名为TEACHER的表格,其中teacherId为主键,studentId为外键,引用STUDENT表的主键studentId
修改基本表:
ALTERTABLE<表名>RENAME<修改后表名>
altertableTEACHERrenameS;
将TEACHER表的表名改为S
ALTERTABLE<表名>ADDCOLUMN<列名><属性类型>
altertableTEACHERaddcolumnADDRESSvarchar(255);
在表名为TEACHER的表中加入名为ADDRESS的列
ALTERTABLE<表名>CHANGECOLUMN<列名><修改后列名><属性类型>
altertableTEACHERchangecolumnADDRESSaddressvarchar(230);
修改TEACHER表中的列,将ADDRESS的列名改为address
ALTERTABLE<表名>DROP[COLUMN]<列名>
altertableTEACHERdrop[column]address;
删除列名为address的列,column可有可无
删除基本表:
DROPTABLE<表名>[RESTRICT|CASCADE]
删除表格
droptableSTUDENTrestrict;
删除STUDENT表。
受限制的删除,欲删除的基本表不能被其他表的约束所引用(如check,foreignkey等约束),
不能有视图,不能有触发器,不能有存储过程或函数等。
droptableSTUDENTcascade;
若选择cascade,则该表删除没有限制。
在删除基本表的同时,相关的依赖对象,例如视图,都将被一起删除。
关于完整约束性:
参考文章:
完整性约束的SQL定义
ALTERTABLE<表名>ADDCONSTRAINT<约束名><约束条件>
altertableteacheraddconstraintpk_teacher_idprimarykeyteacher(id);
在teacher表中增加名为pk_teacher_id的主键约束。
ALTERTABLE<表名>DROP<约束条件>
altertableteacherdropprimarykey;
删除teacher表的主键约束。
altertablestudentaddconstraintfk_student_teacherforeignkeystudent(teacherId)referencesteacher(id);
在student表中增加名为fk_student_teacher的约束条件,约束条件为外键约束。
索引的建立与删除:
索引的建立:
CREATE[UNIQUE]|[CLUSTER]INDEX<索引名>ON<表名>(<列名>[次序][,<列名>[次序]]……);
UNIQUE表明此索引的每一个索引值只对应唯一的数据记录。
CLUSTER表示要建立的索引是聚簇索引。
createuniqueindexid_indexonteacher(idasc);
对teacher表的id列建立unique索引,索引名为id_index
索引的删除:
DROPINDEX<索引名>ON<表名>
dropindexid_indexonteacher;
在teacher表中删除索引,索引名为id_index
另外的方法:
新建索引:
ALTERTABLE<表名>ADD[UNIQUE]|[CLUSTER]INDEX[<索引名>](<列名>[<次序>],[<列名>[<次序>]]……)
altertableteacheradduniqueindexid_index(idasc);
在teacher表中对id列升序建立unique索引,索引的名字为id_index
删除索引:
ALTERTABLE<表名>DROPINDEX<索引名>
altertableteacherdropindexid_index;
删除teacher表名为id_index的索引
数据库索引的建立有利也有弊,参考文章:
数据库索引的作用和优点缺点
(一)
数据库索引的作用和优点缺点
(二)
数据库建立索引的原则
数据查询:
SELECT[ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]……
FROM<表名或视图名>[<表名或视图名>]……
[WHERE<条件表达式>]
[GROUPBY<列名1>[HAVING<条件表达式>]]
[ORDERBY<列名2>[ASC|DESC][,<列名3>[ASC|DESC]]……];
查询经过计算的值:
selectteacherIdasid,salary-100asSfromteacher;
查询经过计算的值,从teacher表中查询出teacherId字段,别名为id,并且查询出salary字段减去100后的字段,别名为S
使用函数和字符串:
selectteacheridasid,'birth',salary-20asSA,lower(name)fromteacher;
<目标表达式>可以是字符串常量和函数等,'birth'为字符串常量,lower(name)为函数,将name字段以小写字母形式输出
消除取值重复的行:
selectdistinctnamefromteacher;
如果没有指定DISTINCT关键词,则缺省为ALL.
查询满足条件的元组:
WHERE子句常用的查询条件:
查询条件
谓词
比较
=,>,<,>=,<=,!
=,<>,!
>,!
<
确定范围
BETWEENAND,NOTBETWEENAND
确定集合
IN,NOTIN
字符匹配
LIKE,NOTLIKE
空值
ISNULL,ISNOTNULL
多重条件(逻辑运算)
AND,OR,NOT
(1)比较大小:
select*fromteacherwherename='test';
select*fromteacherwheresalary>500;
select*fromteacherwheresalary<>500;
(2)确定范围:
select*fromteacherwheresalarybetween300and1000;
select*fromteacherwheresalarynotbetween500and1000
(3)确定集合
select*fromteacherwherenamein('test','test2');
select*fromteacherwherenamenotin('test','test2');
(4)字符匹配:
[NOT]LIKE'<匹配串>'[ESCAPE'<换码字符>']
<匹配串>可以是一个完整的字符串,也可以含有通配符%和_
%代表任意长度(长度可以是0)的字符。
例如a%b表示以a开头,以b结尾的任意长度的字符串。
如acb,addgb,ab
_代表任意单个字符。
例如a_b表示以a开头,以b结尾的长度为3的任意字符串。
如acb,afb等都满足该匹配串。
select*fromteacherwherenamelike'%2%‘;
select*fromteacherwherenamelike'_e%d’;
注意一个汉字要占两个字符的位置。
(5)涉及空值查询:
select*fromteacherwherenameisnull;
select*fromteacherwherenameisnotnull;
注意这里的"is"不能用符号(=)代替。
(6)多重条件查询:
select*fromteacherwherename='test'andsalarybetween400and800;
select*fromteacherwherenamelike'%s%'orsalary=500;
ORDERBY子句:
ORDERBY子句对查询结果按照一个或多个属性列的升序(ASC)或降序(DESC)排列,缺省值为(ASC)
selectsalaryfromteacherorderbysalaryasc;
select*fromteacherorderbynamedesc,salaryasc;
聚集函数(aggregatefunctions):
COUNT([DISTINCT|ALL]*)统计元组个数
COUNT([DISTINCT|ALL]<列名>)统计一列值的个数
SUM([DISTINCT|ALL]<列名>)计算一列值的总和
AVG([DISTINCT|ALL]<列名>)计算一列值的平均值
MAX([DISTINCT|ALL]<列名>)求一列值中的最大值
MIN([DISTINCT|ALL]<列名>) 求一列值中的最小值
缺省值为ALL
selectcount(distinctname)fromteacher;
查询没有重复的名字的个数
selectcount(*)fromteacher;
查询teacher表格总记录数
selectsum(salary)fromteacher;
查询teacher表的salary字段的总和
selectavg(salary)fromteacher;
查询teacher表的salary字段的平均值
selectmax(salary)fromteacher;
查询teacher表的salary字段的最大值
selectmin(salary)fromteacher;
查询teacher表的salary字段的最小值
GROUPBY子句:
GROUPBY子句将查询结果按某一列或多列的值分组,值相等的为一组。
对查询结果分组的目的是为了细化聚集函数的作用对象。
分组后聚集函数将作用于每一个组,即每一组都有一个函数值。
selectcno,count(*)fromteachergroupbycno;
对teacher表格按照cno分组,并算出每组里面有多少个元素
如果分组后还要按照一定的条件对这些组进行筛选,最终只输出满足指定条件的组,则可以使用HAVING语句指定筛选条件。
selectcno,count(*)fromteachergroupbycnohavingcount(*)>=4;
对teacher表格按照cno分组,并算出每组里面有多少个元素,得到元素个数大于等于4的值
连接查询:
连接查询是关系数据库中最主要的的查询,包括等值连接查询,自然连接查询,非等值连接查询,自身连接查询,外连接查询和复合条件连接查询等。
等值与非等值连接查询:
连接查询的WHERE子句中用来连接两个表的条件称为连接条件或连接谓词,格式为:
[<表名1>.]<列名><比较运算符>[<表名2>.]<列名2>
其中比较运算符主要有:
=,>,<,>=,<=,!
=(或<>)等
selects.*,t.*fromstudentass,teacherastwheres.teacherid=t.teacherid;
等值连接查询,将student表和teacher的信息连接查询出来,连接条件是s.teacherid=t.teacherid
自身连接:
一个表与自身进行连接,称为自身连接
selectteacher.name,student.namefrompeopleasteacher,peopleasstudentwhereteacher.name=student.teacher;
自身连接查询,在people表里有两种角色,一种是教师,一种是学生,利用自身连接查询,得到name字段和teacher字段相等的元组
外连接:
左外连接列出左边关系中所有元组,右外连接列出右边关系中所有元组。
左外连接:
SELECT<目标列表达式>[,<目标列表达式>]……FROM<表名1>LEFT[OUTER]JOIN<表名2>ON<连接条件>
右外连接:
SELECT<目标列表达式>[,<目标列表达式>]……FROM<表名1>RIGHT[OUTER]JOIN<表名2>ON<连接条件>
selects.sno,s.name,o,c.namefromstudentassleftouterjoinclassascon(o=o);
student表和class表进行左外连接,连接条件是o=o
selecto,c.name,s.sno,s.namefromstudentassrightouterjoinclassascon(o=o);
student表和class表进行右外连接,连接条件为o=o
student表数据:
+-----+-----+------+
|sno|cno|name|
+-----+-----+------+
|1|1|地心|
|2|2|华雄|
|3|1|孝慈|
|4|3|必须|
+-----+-----+------+
class表数据:
+-----+-----+------+
|cid|cno|name|
+-----+-----+------+
|1|1|化学|
|2|2|物理|
|3|3|政治|
+-----+-----+------+
左外连接效果:
+-----+------+-----+------+
|sno|name|cno|name|
+-----+------+-----+------+
|1|地心|1|化学|
|2|华雄|2|物理|
|3|孝慈|1|化学|
|4|必须|3|政治|
+-----+------+-----+------+
右外连接效果:
+-----+------+-----+------+
|cno|name|sno|name|
+-----+------+-----+------+
|1|化学|1|地心|
|1|化学|3|孝慈|
|2|物理|2|华雄|
|3|政治|4|必须|
+-----+------+-----+------+
MySQL不支持全外连接!
复合条件连接:
WHERE子句中可以有多个连接条件,称为复合条件连接
selects.sno,s.name,c.name,s.scorefromstudents,classcwhereo=oands.score<60;
复合条件连接查询,查询学生信息和课程信息,并且成绩小于60的记录
嵌套查询:
一个SELECT-FROM-WHERE语句称为一个查询块。
将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。
子查询的SELECT语句中不能使用ORDERBY子句,ORDERBY子句只能对最终查询结果排序
带有IN谓词的子查询:
selectsno,namefromstudent
wherecnoin
(
selectcnofromstudent
wherename='华雄'
);
查询和"华雄"选同一课程的所有学生的学号和姓名。
子查询的查询条件不依赖于父查询,称为不相关子查询。
如果子查询条件依赖于父查询,这类子查询称为相关子查询,整个查询语句称为相关嵌套查询语句。
带有比较运算符的子查询:
selectname,cnofromstudents1
wherescore>
(
selectavg(score)fromstudents2
wheres2.name=s1.name
);
查询学生的大于各科平均成绩的科目
以上是相关子查询。
带有ANY(SOME)或ALL谓词的子查询
子查询返回单值时可以用比较运算符,但返回多值时要用ANY(有的系统用SOME)或ALL谓词修饰。
使用ALL或ALL谓词时必须使用比较运算符。
>ANY大于子查询结果的某个值
>ALL大于子查询结果的所有值
>=ANY大于等于子查询结果的某个值 >=ALL大于等于子查询结果的所有值 <=ANY小于等于子查询结果的某个值 <=ALL小于等于子查询结果的所有值 =ANY等于子查询结果的某个值 =ALL等于子查询结果的所有值(通常没有实际意义) ! =(或<>)ANY不等于子查询结果的某个值 ! =(或<>)ALL不等于子查询结果的任何一个值 selectname,scorefromstudentwherescore<=all(selectscorefromstudent); 查询成绩最小的学生姓名和成绩 集合查询: SELECT语句的查询结果是元组的集合,所以多个SELECT语句的结果可进行集合操作。 集合操作主要包括并操作(UNION),交操作(INTERSECT),差操作(EXCEPT)。 参加集合操作的各查询结果的列数必须相同;对应项的数据类型也必须相同。 MySQL数据库不支持INTERSECT和EXCEPT操作! select*fromstudentwherecno=1 union select*fromstudentwherecno=2; 查询班级1和班级2所有学生信息 数据更新: 插入数据: 插入元组: INSERT INTO<表名>[(<属性列1>)[,<属性列2>]……] VALUES(<常量1>[,<常量2>]……); 例子: insertintostudent(cno,name,score)values(2,'横切',85); 插入子查询结果: INSERT INTO<表名>[(<属性1>[,<属性2>]……)] 子查询; 例子: insertintostudentcopyselect*fromstudent; 将student表的信息全部复制到studentcopy表中 修改数据: UPDATE<表名> SET<列名>=<表达式>[,<列名>=<表达式>]…… [WHERE<条件>] 修改某一元组的值: updatestudentcopysetscore=80wheresno=1; 修改多个元组的值: updatestudentcopysetscore=score-20; 删除数据: DELETE FROM<表名> [WHERE<条件>]; 删除某一元组: deletefromstudentcopywheresno=1; 删除多个元组: deletefromstudentcopy; 带子查询的删除语句: deletefromstudentcopywherecnoin(selectcnofromstudentasswhereo=2); 视图: 关于视图,它的作用和优缺点可以参考文章: 数据库视图介绍 建立视图: CREATEVIEW<视图名>[(<列名>[,<列名>]……)] AS<子查询> [WITHCHECKOPTION] 子查询可以是任意复杂的SELECT语句,但通常不允许含有ORDERBY子句和DISTINCT语句。 WITHCHECKOPTION表示对视图进行UPDATA,INSERT和DELETE操作时要保证更新,插入或删除的行满足视图定义中的谓词条件 组成视图的属性列名或者全部省略或者全部指定,没有第三种选择。 如果省略了视图的各个属性列名,则隐含该视图由子查询中SELECT子句目标列中的诸字段组成。 但在下面三种情况下必须明确指定组成视图的所有列名: (1)某个目标列不是单纯的属性名,而是聚集函数或列表达式。 (2)多表连接时选出了几
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- mysql 练习