数据库学习笔记

数据库学习笔记图形化界（NavicatePremium）面操作数据库1、工具----命令行操作2、选中某个表对象---右击----逆向表到模型---生成各表的关系模型3、选中某个表对象--右击--对象信息---DDL查看创建表的信息Sql语言集数据定义语言，数据操纵语言，数据查询语言，数据控制语言于一体，可以完成数据库生命周期中的全部工作。数据定义语言：完成创建，修改或删除数据库中的各种对象有create，drop，alter的命令。数据查询语言：按各种条件查询数据库中的数据有select及其相关命令。数据操纵语言：对已经从在的数据库对其进行数据的插入，删除和修改有insert，update，delete命令。数据库控制语言：用于授权或收回访问数据库的某种特权，控制数据操纵事物的发生时间及效果，对数据库进行监视。有grant，revoke，commit，rollback等命令。一、数据库操作1、启动数据库：netstartmysql2、关闭数据库：netstopmysql3、打开数据库：mysql-uroot-p1234、显示所有数据库：showdatabases;5、创建数据库：createdatabasemydb1;6、删除数据库：dropdatabasemydb1;二、单表操作：1、选择并使用数据库：usemydb12、创建表：（宠物表）宠物表：id名字、主人、种类，性别、出生和死亡日期。createtablepet(idintprimarykey,namevarchar(20),ownervarchar(20),speciesvarchar(20),sexchar(1),birthdate,deathdate);3、插入数据：insertintopetvalues(1,’ergou’,’zx’,’dog’,’f’,’2013-09-06’null);4、查询所有内容select*frompet;5、删除一条数据：deletefrompetwhereid=1;6、修改数据中的内容：updatepetsetbirth=’2015-09-09’wherename=’huahua’;7、选择特殊行：select*frompetwherespecies=’dog’;select*frompetwherespecies=’dog’andsex=’f’;select*frompetwherespecies=’dog’or‘cat’;select*frompetwherebirth’2016-09-01’andbirth’2014-09-09’;select*frompetwhere(species=’dog’andsex=’f’)or(species=’cat’andsex=’m’);select*frompetwheresex’f’;select*frompetwheresex!=’f’;8、选择特殊列：selectname,species,birthfrompetwherespecies=’dog’orspecies=’cat’;9、排序查询：orderbyselectname,birthfrompetorderbybirth;selectname,birthfrompetorderbybirthdesc;10、分组查询groupbyselectspeices,sexfrompetgroupbyspecies,sex;11、模糊查询：select*frompetwhereownerlike’z%’;select*frompetwherenamelike’%b’;select*frompetwherenamelike’%b%’;12、计数行selectcount(*)frompet;selectspecies,sex,count(*)frompetgroupbyspecies,sex;13、像表中添加一列：altertableeventaddsexvarchar(20);14、删除表中一列：altertableeventdropsex;15、修改表名：renametableAtoB;学生表student学号（sno）、姓名（sname）、性别（ssex）、年龄（sage）、所在系（sdept）student(sno,sname,ssex,sage,sdept)主键：sno课程表course课程号（cno）课程名（cname）任课老师姓名（teacher）学分（credit）course(cno,cname,teacher,credit)主键cno学生选课表sc学生号（sno）课程号（cno）成绩（geade）sc(sno,cno,grade)主键（sno，cno）外键（sno，cno）教师表teacher教师号（tno）姓名（tname）性别（tsex）年龄（tage）职称（title）工资（tsalary）奖金（tcomm）所在系（tdept）teacher(tno,tname,tsex,tage,title,tsalary,tcomm,tdept)主键：tno教师授课表tc教师号（tno）课程号（cno）tc(tno,cno)主键（tno，cno）外键（tno，cno）部门信息表dept部门号（deptno）、部门名称（dname）、部门地点（loc）dept(deptno,dname,loc)主键（deptno）雇员信息表empt雇员编号（empno）雇员姓名（ename）工作（job）经理编号（mgr）工资（sal）奖金（comm）雇佣日期（hiredate）部门号（deptno）emp（empno,ename,job,mgr,sal,comm,hiredate,dephno）主键empno外键deptno工资等级表salgrade等级号（grade）最低工资（lowsal）最高工资（highsal）salgrade(grade,lowsal,highsal)部门信息表dept部门号（deptno）、部门名称（dname）部门地点（loc）主键deptno创建表：例：创建学生选课表scCREATETABLE`sc`(`sno`char(8)NOTNULL,`cno`char(8)NOTNULL,`grade`int(11)DEFAULTNULL,PRIMARYKEY(`sno`,`cno`),KEY`cno_fk`(`cno`),CONSTRAINT`cno_fk`FOREIGNKEY(`cno`)REFERENCES`course`(`cno`),CONSTRAINT`sno_fk`FOREIGNKEY(`sno`)REFERENCES`student`(`sno`))ENGINE=InnoDBDEFAULTCHARSET=gbk;添加主键：PRIMARYKEY(`sno`,`cno`),添加外键：KEY`cno_fk`(`cno`),关联主外建：CONSTRAINT`cno_fk`FOREIGNKEY(`cno`)REFERENCES`course`(`cno`),CONSTRAINT`sno_fk`FOREIGNKEY(`sno`)REFERENCES`student`(`sno`)单表查询：1、查询全体学生的学号、姓名、性别selectsno,sname,ssexfromstudent;2、查询学校所开全部课程的课程名和学分selectcname,creditfromcourse;3、查询全部课程的所有信息select*fromcourse;4、查询全体学生的姓名及出生年月（查询经过计算的值）selectsname,2016-sagefromstudent;5、查询所有选修过课程的学生（加distinct去掉重复）的学号；selectsnofromsc;selectdistinctsnofromsc;6、查询信息系所有学生名单selectsnamefromstudentwheresdept='XI';7、查询所有考试成绩大于80分的学生的学号和成绩。selectsno,gradefromscwheregrade80;8、查询所有年龄在18-25之间的学生的姓名和年龄selectsname,sagefromstudentwheresagebetween18and20;selectsname,sagefromstudentwheresage=18andsage=20;9、查询选修了‘1’号课程的学生的学号，成绩查询结果按降序排序selectsno,gradefromscwherecno=1orderbygradedesc;10、聚组函数（1）查询学生总人数selectcount(*)fromstudent;（2）查询选修了课程的所有学生selectcount（distinctsno）fromsc;(3)查询学习‘1’号课程的学生的最高成绩、最低成绩、平均分selectavg(grade),max(grade),min(grade)fromscwherecno=1;（4）查询学号为1的同学的选修课程的总学分selectsum(credit)fromsc,coursewheresno=1andsc.cno=course.cno;（5）查询选修1门以上课程的学号selectsno,count(*)asS_NUMfromscgroupbysnohavingcount(*)1;(6)查询每个学生的平均成绩selectstudent.sno,sname,avg(grade)fromsc,studentwheresc.sno=student.snogroupbystudent.sno;（7）输出平均成绩大于80的男生的学号、姓名、平均成绩并按学号的升序排序selectstudent.sno,sname,avg(grade)fromsc,studentwheressex='男'andsc.sno=student.snogroupbystudent.snohavingavg(grade)80orderbystudent.sno;(8)查询每个部门的每种职位的雇员数selectdeptno,job,count(*)fromemptgroupbydeptno,job;（9）查询奖金高于其工资5%的雇员信息，并按奖金与工资比例的降序排列selectename,sal,comm,comm/salfromemptwherecomm0.05*salorderbycomm/saldesc;联接查询（多表查询）1、查询每个同学及其选修课的情况（等值连接）selectstudent.*,sc.*fromsc,studentwheresc.sno=student.sno;2、查询每个学生及其选修课的课程名和成绩的情况（自然连接）selectstudent.sno,sname,course.cname,gradefromstudent,course,scwherestudent.sno=sc.snoandcourse.cno=sc.cno;3、查询选修2号课程且成绩在90分以上的所有学生的学号，姓名，和成绩（复合连接）selectstudent.sno,sname,course.cname,gradefromstudent,scwherestudent.sno=sc.sno（连接条件）andsc.cno=2andsc.grade90groupbystudent.sno;（限制条件）4、查询每门课程的课程名、任课教师名，以及选课人数selectcname,teacher,count(sno)fromcourse,scwherecourse.cno=sc.cnogroupbysc.cno;5、查询工资等级为3的雇员姓名和工资（非等值连接）selectename,sal,gradefromempt,salgradewheresalbetweenlowsalandhighsalandgrade=3;6、找出empt