Mysql数据库基本知识三:表的增删查改

===========================【MySQL数据库表结构的修改】========================

1,表名的修改:ALTER TABLE 表名 RENAME TO 新表名;  或  RENAME TABLE 表名 TO 新表名;

2,字段的修改:
(1)增加字段(列):ALTER TABLE 表名 ADD 字段名 数据类型 [列级约束]
(2)删除字段:ALTER TABLE 表名 DROP 字段名 
(3)修改字段名和数据类型:

字段名和字段类型同时修改:
ALTER TABLE 表名 CHANGE 原字段名 新字段名 新数据类型 

单独修改字段名时,字段类型不变,:
ALTER TABLE 表名 CHANGE 原字段名 新字段名 原数据类型

单独修改数据类型时,字段名不变:
ALTER TABLE 表名 CHANGE 原字段名 原字段名 新数据类型

修改字段的排列位置:ALTER TABLE 表名 MODIFY [COLUMN] 字段名1 数据类型 FIRST / (AFTER 字段名2)

注:FIRST表示将字段名1对应的字段调整到第一列。
AFTER字段名2表示将字段名1对应的字段移动到字段名2对应字段之后。

***MODIFY只修改数据类型:ALTER TABLE 表名 MODIFY 字段名 新数据类型
默认值约束、非空值约束和改变字段排列位置既可以用modify子句也可以用chang子句实现。

3,约束的修改:
(1)删除约束:
删除主码:ALTER TABLE 表名 DROP PRIMARY KEY;
删除外码:ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名 ;
删除唯一约束:ALTER TABLE 表名 DROP INDEX 唯一约束名;
删除默认值约束:ALTER TABLE表名 CHANGE 原字段名 原数据类型 DEFAULT NULL;
删除非空约束:ALTER TABLE 表名 CHANGE  原字段名 原数据类型 NULL;

(2)增加约束
主码约束:ALTER TABLE 表名 ADD PRIMARY KEY(列名);
外码约束:ALTER TABLE 表名 ADD  FOREIGN KEY 字段名 REFERENCES 父表 主码字段名
唯一性约束:ALTER TABLE 表名 ADD UNIQUE 列名
默认值约束:ALTER TABLE 表名 CHANGE 原字段名 原字段名 原数据类型 DEFAULT 默认值;
非空约束:ALTER TABLE 表名 CHANGE 原字段名 原字段名 原数据类型 NOT NULL;

4,选项的修改:
修改存储引擎:ALTER TABLE 表名 ENGINE=新的存储引擎类型;
修改默认字符集:ALTER TABLE 表名 DEFAULT CHARSET=新的字符集;
修改索引关键字压缩方式:ALTER TABLE 表名 PACK_KEYS=新的压缩类型;
设置自增字段初始值:ALTER TABLE 表名 AUTO_INCREMENT=新的初始值;


=================================【插入数据】=================================
1,【插入完整的数据记录】:INSERT INTO 表名 (字段名1 ,…字段名n)  VALUES (值1,…值n );

插入数据和建表一样,先向父表插入数据再向子表插入数据。
SET FOREIGN_KEY_CHECKS=0; 取消外键约束。
SET FOREIGN_KEY_CHECKS=1; 将外键约束还原。
自增约束字段取NULL值。

2,【插入非完整的数据记录】:
INSERT INTO 表名 (字段名1 ,…字段名n)  VALUES (值1,…值n );

方法一:指定字段名列表
INSERT INTO Department(DId, DName)  VALUES ('D04', '信息与软件工程系');
注:在没有指定的DHeader字段,系统会给它一个NULL。

方法二:构造完整数据记录
INSERT INTO Department  VALUES ('D04', '信息与软件工程系',NULL);

3,【插入多条数据记录】:

【例】向registration 数据库中Major(专业表)批量插入下表中的记录。
代码如下:

INSERT INTO Major VALUES
('M01', '信息管理与信息系统', 'D01'),
('M02', '物流管理', 'D01'),
('M03', '电子商务', 'D01');


4,【插入查询结果】:

【例】从registration 数据库中Major(专业表)中查询所有的记录,并将其插入到新表Profession中。
该案例可以采用两种方法实现。

方法一:先复制表结构,然后插入查询结果
CREATE TABLE Profession LIKE Major;
INSERT INTO Profession
SELECT *
FROM Major;

方法二:复制表结构的同时还复制表中的记录
CREATE TABLE Profession
SELECT *
FROM Major;

5,【采用REPLACE语句插入数据记录】:
replace 插入前进行判断,如果插入的记录在【主码或者非空字段】的取值与表中已有记录相同,
则先删除表中的记录在插入新纪录。

=============================【修改数据】===========================
update 表名            改哪张表
set name='新数据',price='100'    改哪些字段
where id=2;            改哪行记录
=============================【删除数据】=============================
delete from 表名        删哪张表
where  id=2;        删哪行记录 (无条件时则删除整张表的记录)

ps:TRUNCATE 表名;可以删除整张表的记录,不能删除父表,数据不可回复,不能激活触发器。

=============================【数据查询】==============================
查询总顺序:
select 
from 
where         过滤表中数据的条件
group by         分组
having        筛选出满足指定条件的组
order by        排序
limit

1,查询指定的列/全部列:SELECT 字段名/* FROM 表名
ps:年龄=当前年份-出生年份:YEAR(CURDATE())-YEAR(SBirth)

2,为查询字段指定别名:SELECT 字段名 AS 别名 FROM 表名
例:SELECT SId AS 学号,SName AS 姓名,YEAR(CURDATE())-YEAR(SBirth) AS 年龄

3,获取不重复的行:SELECT distinct 字段名 FROM 表名
distinct:去掉重复的行。

4,限制显示记录数:LIMIT [起始记录位置偏移量],记录数
ps:如果不指定“起始记录位置偏移量”,将会从表中的第一条记录开始显示。

第一条记录的位置偏移量是 0,第二条记录的位置偏移量是 1,以此类推。
第一个参数“起始记录位置偏移量”,指定从哪一条记录开始,第二个参数“记录数”指定返回的记录条数。

【例】查询课程表中从第3条记录开始的3条记录信息。
SELECT * 
FROM Course 
LIMIT 2,3; 

=============================WHERE条件查询===========================
1,【比较条件查询】
查询成绩大于等于60分:WHERE Score>=60  /  WHERE NOT Score<60

2,【范围查询】
一),BETWEEN x AND y---and:包含边界值(闭区间)
二),NOT BETWEEN … AND--or:不包含边界值

查询出生日期在1998-12-20至1999-04-9之间:
WHERE SBirth BETWEEN '1998-12-20' AND '1999-04-09'
或者
WHERE SBirth >='1998-12-20' AND SBirth<= '1999-04-09';

3,【集合查询】:in---or   /  not in---and
查询职称为讲师、副教授和教授:
SELECT *
FROM Teacher
WHERE TRank IN('讲师', '副教授', '教授'); 

4,【模糊查询】:like
ps:使用转义字符 ' \ ' 对通配符进行转义(把通配符转义为普通字符)。

1) 百分号(%):长度不能确定。
2) 下划线(_):一个'_'代表一个字符,长度可以确定。

查询姓刘的教师信息:
SELECT *
FROM Teacher
WHERE Tname LIKE'刘%';

5,【空值查询】:IS NULL (判断字段的取值是否为空)
WHERE 字段名 [NOT] IS NULL

查询系主任不为空的系部信息。:
SELECT *
FROM Department
WHERE DHeader NOT IS NULL;

6,【多重条件查询】
同时满足: AND(AND的优先级高于OR)
只需满足其中一个: OR

7,【对查询结果排序】:ORDER BY 2 DESC,1 ASC;
关键字ASC表示按升序排序,关键字DESC表示按降序排序,按多个字段排序时,字段之间用逗号分隔,ASC可以省略。


===================================聚合函数==================================
count(*)  :        返回行数      (不忽略空值)
count(列名):    返回指定列中非NULL值的个数
SUM():        返回指定列中非NULL值之和
MAX():        返回指定列中非NULL值的最大值
MIN():        返回指定列中非NULL值的最小值采用
AVG():        返回指定列中非NULL值的平均值

GROUP BY:    在数据查询中,进行分类汇总。

聚合函数的特性:
①:除count(*)函数外,其他聚合函数忽略空值。
②:聚合函数在SELECT中使用,不能在where子句中使用。
③:在没有分组的情况下,聚合函数作用于整张表中满足where条件的所有记录,
在查询结果中,一个聚合函数只返回单一的值。

分组查询:细化聚合函数

======================分组查询======================
【例】查询平均分大于等于80的学生学号和对应的平均成绩
select sid,avg(score)
from register
group by sid
having avg(score)>=80;

where用于限定表中已有的字段(列)。
having用于限制分组计算出来的值。

【例】查询平均成绩大于80分的女生的姓名

select sname
from student
where sgender='女'
having avg(score)>80;

【例】查询每个专业的男生人数和女生人数
select smajor,sgender,count(sid)
from student
group by smajor,sgender;

【例】姓名长度大于10的学生信息(使用char_length,length查的是字节长度)

【例】查询每门课程选修的人数,并在分组后加上一条汇总记录。
代码如下:
SELECT CId,COUNT(SId)
FROM Register
GROUP BY CId WITH ROLLUP;
注:WITH ROLLUP选项放在GROUP BY子句的末尾是用来对所有的分组进行汇总运算。

======================CASE子句====================
要查询的数据表里没有现成的,但是该数据由表中的数据转换得到的,这时用case子句。

1,【简单CAS】E函数只做数据匹配,不进行比较运算和逻辑运算。
CASE 列名
WHEN 值1 THEN 转换值1
WHEN 值2 THEN 转换值2
ELSE 转换值n
END

【例】查询教师姓名,职称,以及将职称以高级,中级,初级的形式显示
select tname,trank,case trank 
when '教授' then '高级'
when '副教授' then '高级'
when '讲师' then '中级'
when '助教' then '初级'
else '不清楚'
end as DJ
from teacher
-------------------------------------------------------------------------------------------
2,【搜索CASE】可以完成更为复杂的数据转换。区别:CASE后无列名,WHEN 后是布尔表达式。
CASE 
WHEN 布尔表达式1  THEN 转换值1
WHEN 布尔表达式2 THEN 转换值2
ELSE 转换值n
END

【例】查询所有学生选修课程的情况,包括学号,课程号,成绩以及成绩对应的等级。
select sid,cid,score,case
when score >=90 then '优'
when score >=80 then '良'
when score >=60 then '及格'
when score <60 then '不及格'
else '成绩为空'
end as dj
from register;

=========================【连接查询】===========================
连接查询:多表查询(连接条件)
1,内连接:inner join
内连接又分为:等值连接、非等值连接、自然连接和自连接

2,外连接:outer join(连接条件)
    --左外连接left  join 
    --右外连接right join
    --全外连接full join,但MySQL不支持

3,交叉连接:cross join (老师说几乎不用,无连接条件)
测试有多少种连接情况的,没什么意义。

==========================内连接=========================
内连接查询的结果集中仅包含满足连接条件的元组!!!

1)等值连接:在连接条件中使用等于(=)运算符比较连接字段上的取值,其查询结果包括其中的重复列。
2)非等值连接:在连接条件使用除等于运算符以外的其它比较运算符比较连接字段上的取值。
     这些运算符包括>、>=、<=、<和<>。
3)自然连接***:"from 字段1 natural join 字段2"
是一种特殊的等值连接,在【查询结果中去掉重复的列】。

4)自连接:自己和自己进行连接,即一个表与其自身进行连接。


【例】查询每个学生及其选修课程的情况。
格式一对应代码如下:
SELECT *
FROM Student JOIN Register 
ON Student.SId= Register.SId;

格式二对应代码如下:
SELECT *
FROM Student, Register 
WHERE Student.SId= Register .SId;

注意:取了别名,只能用别名。


【例】查询所有学生对应的专业信息,包括学生的学号、姓名、专业号和专业名。

select sid,sname,mid,mname
from student  join major
on smajor=mid;
ps: 22条信息,取决于子表的数据条目。

【例】查询网络工程专业选修数据库原理与应用课程的信息,要求列出学生的学号、姓名和选课时间。

四张表:major,course,student,register

select s.sid,sname,rdate
from major join student s on mid=smajor
join register r on r.sid=s.sid
join course c on c.cid=r.cid
where mname='网络工程'
and cname='数据库原理与应用';

自连接:
(自连接时,必须给表起别名以示区别。查询结果用一个表,条件用另一个表。)
select m2.mid,m2.mname,m2.mdept
from major m1 join major m2
on m1.mdept=m2.mdept
where m1.mname='电子商务' and m2.mname!='电子商务';

【例】选修c01 和选修c02的人
select r2.sid
from register r1 join register r2
on r1.sid=r2.sid
where r1.cid='c01' and r2.cid='c02'; 
【例】查询比数据库原理与应用 学分高的课程信息
select c2.*
from course c1 join course c2
on c1.ccredit=c2.ccredit
where c1.cname='数据库原理与应用' and c2.ccredit>c1.ccredit;

分组查询***:select后面只能加分组字段和聚合函数

=======================外连接=====================
1)左外连接(LEFT OUTER JOIN):查询的结果集中除了包括满足连接条件的元组外,
还包括左表不满足连接条件的元组。对右表有限定
2)右外连接(RIGHT OUTER JOIN):查询的结果集中除了包括满足连接条件的元组外,
还包括右表不满足连接条件的元组。
3)全外连接(FULL OUTER JOIN):查询的结果集中除了包括满足连接条件的元组外,
还包括两个表不满足连接条件的元组(MySQL不支持全外连接)。

出现"包括没有"四个字,就用外连接!!!

【例】查询所有学生的选课情况,包括没有选修课程的学生。
select register.* 
from student left join register
on student.sid=register.sid; 
没有选课的在学生表里,所以显示左表不满足连接条件的元组用‘左外连接’。

注意:
1)外连接查询时,同名属性(连接字段)用父表中的。
2)查询时,遇到否定词不要轻易用<>,全部课程-女生选修的课程。 

否定:全部-肯定
【例】查询没有教授所授的课程
select c.*,sc.courseid
from course c left join
(select courseid from teacher t join schedule sc on t.TeacherID=sc.teacherid
and profession='教授') sc
on c.courseid=sc.courseid
and sc.CourseID is null
=========================交叉连接====================
交叉连接:cross join (老师说几乎不用,无连接条件,用来查询所有的连接情况)
select *
from student [cross] join register;/from student,register;

8*22=176行记录

=========================联合查询 Union =====================
条件:相同的列
【例】将M02专业学生的查询结果和M03专业学生的查询结果合并为一个结果集。

一:
SELECT * FROM Student WHERE SMajor='M02'
union
SELECT * FROM Student WHERE SMajor='M03';

二:
SELECT * FROM Student WHERE SMajor='M02' or SMajor='M03';

注意:
union的操作对象时集合
or的操作对象时表达式

三:
SELECT * FROM Student where smajor in('M02','M03');


==========================子查询============================
子查询,就是指在一个查询里嵌套了其他的若干查询,
即在一个SELECT语句的WHERE或HAVING或FROM子句中包含另一个SELECT查询语句。

【例】    SELECT Sname   外层查询(父查询)
    FROM Student
    WHERE Sid  IN
    (SELECT Sid            内层查询(子查询)
                 FROM Register
                 WHERE Cid= c01);

这里"in"是集合运算符,引出子查询。
内层查询的结果作为外层查询的条件,内层查询带"()"。

引出子查询的运算符:
1,比较运算符    子查询返回一行
2,in        子查询返回多行
3,all        子查询返回多行,配合比较运算符使用 >all >max() >min()
4,any/some    内层返回多行+某个值       配合标准运算符使用
5,exists         很特别,逻辑值(真和假)

>all        大于最大值
<All       小于最小值
>ANY/SOME    大于最小值  (子查询时,ANY和some是等价的。)

子查询不返回任何数据,只产生逻辑真值“TRUE”或逻辑假值“FALSE”,
NOT EXISTS与EXISTS刚好相反。

语法为:
EXISTS
(子查询)

(子查询):用于指定子查询,由EXISTS引出的子查询,其目标列表达式通常都用* 。
当子查询返回逻辑值时,用EXISTS/NOT EXISTS引出子查询。
EXISTS引出的子查询主要用于判断子查询的结果集是否为空。


【例1】查询与“电子商务”属于同一个系的其他专业信息,包括专业号、专业名和所属系部。
select mid,mname,mdept 
from major 
where mdept=
(select mdept from major where mname='电子商务');

内层查什么,外层取什么。(由内向外)

【例2】查询成绩大于该课程的平均成绩

select sid,cid,score
from register r1
where score>
(select avg(score) from register r2 where r1.cid=r2.cid);

相关子查询:内层查询的条件依赖于外层查询
处理过程:外-内-外

第一步:取出外层查询中的第一行,根据相关属性cid上的取值C01取处理内层查询;
第二步:开始处理内层查询,根据传递进来的c01处理内层查询,查询到该课程的平均成绩
第三步:处理外层查询

【例】查询"计算机系"的班级信息
select * from class where departmentid in
(select departmentid from department where departmentname='计算机系');


【例】查询没有选修课程名为‘JAVA程序设计’的学生学号和姓名
思路:全部-肯定

select sid,sname
from student
where sid not in
(SELECT sid from register where cid in 
(select cid from course where cname='JAVA程序设计'))

当子查询返回多行时,不能使用比较运算符引出子查询。

  • 4
    点赞
  • 16
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值