linuxsir首页 LinuxSir.Org | Linux、BSD、Solaris、Unix | 开源传万世,因有我参与欢迎您!
网站首页 | 设为首页 | 加入收藏
您所在的位置:主页 > Linux数据库 >

MySQL常用SQL语句:插入更新删除查询

时间:2018-06-04  来源:未知  作者:admin666

整理一些MySQL常用SQL语句:插入、更新、删除、查询、根据指定的列对结果集进行排序等。

数据库表:

   

           student表                course表               sc表

student表中sno为主键,course表中cno为主键,sc表中sno和cno为联合主键,并且分别对应于student表的sno和course表的cno。

关于联合主键:联合主键就是用2个或2个以上的字段组成主键。用这个主键组合包含的字段作为主键,这个组合在数据表中是唯一,且加了主键索引。

1. 插入
(1) 不指定列名

insert into student values (218004, '龙猫', '男', '23')

注意:如果不指定插入的列名,则value中的值要对应表中的每一列,若少列,会报错。
(2) 指定列名

insert into student (sno, sname) values (218005, '王王')

2. 更新

update student set sname = '刘一', sex = '女' where sno = 218004

3. 删除
(1) 删除表中的某行记录

delete from student where sno = '218005'

(2) 删除表中的所有记录

delete from student

delete * from student

4. 单表查询
(1) 查询所有

select * from student

(2) 查询指定列

select sno, sname from student

(3) distinct去重
单列

select distinct snamefrom student

多列(只有所有指定的列信息都相同,才会被认为是重复的信息)

select distinct sname, sno, sex, age from student

note:
a. 在多列去重时,只有所有指定的列信息都相同(即sname, sno, sex, age都相同),才会被认定为重复的信息
b. distinct必须放在第一列前,如果放在后面会报错,例:select sno, distinct snamefrom student
(4) 关于where
  a. where结合and或or,AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来

select * from student where sno = 218001 and sname = '张雨'

  b. where结合between and,选取介于两个值之间的数据范围

select * from student where sno between 218003 and 218005

(5) 根据指定的列对结果集进行排序

select * from student order by sname

逆序

select * from student order by sname desc

(6) 根据id排序,返回头两条记录

select * from student order by sno limit 2

(7) 模糊查询

select * from student where sname like '刘%'

查询不包含“刘”的记录

select * from student where sname not like '%刘%'

(8) 在where子句中规定多个值(使用in)

select * from student where sno in (218001, 218002, 218004)

5. 多表查询

多表查询与单标查询对应,涉及多个表的查询为多表查询,其中又分为连接查询、嵌套查询、 集合查询。
(1) 连接查询
连接查询是数据库查询中最常用的一种查询语句,是指通过连接字段和连接条件连接多个表从而进行查询,连接查询又分为小类:等值连接、非等值连接 、内连接、外连接、自然连接、自身连接。

 a. 等值连接与非等值连接(其实就是根据两个表之间的关系,直接查询)
当连接条件是等于号(=)时的连接称之为等值连接,相反,当连接条件不是等于号就是非等值连接。

select * from student, sc where sc.sno = student.sno
select student.sno, student.sname, course.cname, sc.grade from student, course, sc where sc.sno = student.sno and sc.cno = course.cno

 b. 内连接
内连接就是等值连接或者非等值连接的另一种写法,写法有INNER JOIN ON或者CORSS JOIN USING两种。
从数学的角度讲就是求两个表的交集,从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录。

select * from student inner join sc on sc.sno = student.sno
select * from student join sc on sc.sno = student.sno
select * from student cross join sc using(sno)

 c. 外连接:分为左外连接,右外连接 ,全外连接
左外连接
以JOIN关键字左边的表为基准,没有匹配的记录则置NULL。从笛卡尔积的角度讲,就是先从笛卡尔积中挑出ON子句条件成立的记录,然后加上左表中剩余的记录

#两个表
select * from student left join sc on student.sno = sc.sno

#三个表
select student.sno, student.sname, course.cname, sc.grade from sc left join student on sc.sno = student.sno left join course on sc.cno = course.cno

使用join关联三个表的语法格式:

表A-------------------------------关联第一张表B------------------------关联第二张表c

select * form 表A  left join 表B on 表A字段 = 表B的id    left join 表c on 表A字段 = 表c的id

右外连接
以JOIN关键字右边的表为基准,没有匹配的记录则置NULL。从笛卡尔积的角度描述,右连接就是从笛卡尔积中挑出ON子句条件成立的记录,然后加上右表中剩余的记录

select * from student right join sc on student.sno = sc.sno

全外连接
以JOIN关键字左右两边的表为基准,没有匹配的记录则置NULL。从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录,然后加上左表中剩余的记录,最后加上右表中剩余的记录。
mysql不支持full join,但可以通过左外连接 + union + 右外连接来实现(left join + union(可去除重复数据)+ right join)

#select * from student full join sc on student.sno = sc.sno
#报错:[Err] 1054 - Unknown column 'student.sno' in 'on clause'
select * from student left join sc on student.sno = sc.sno union select * from student right join sc on student.sno = sc.sno

语法格式:

select * from A left join B on A.id = B.id (where 条件)
union
select * from A right join B on A.id = B.id (where 条件);

c. 自然连接(使用nature join)
自然连接就是USING子句的简化版,它找出两个表中相同的列作为连接条件进行连接。有左自然连接,右自然连接和普通自然连接之分。

select * from student nature join sc

d. 自身连接
顾名思义,自身连接就是一张表,自己和自己连接。(需要使用别名加以区分)

select * from student as student1, student as student2 where student1.sno = student2.sno and student1.sno = 218001

(2) 嵌套查询
首先引入一个查询块的概念,一个 SELECT...FROM...WHERE... 形式的SQL语句称为查询块。当一个查询块的SELECT子句或者WHERE子句中嵌套了另一个查询块的查询语句就称为嵌套查询。最外层的查询称为外层查询或父查询,最内层的查询称为内层查询或子查询。子查询用到了父查询的数据(表、字段)的情况称为相关子查询,相反,如果没用到就称为不相关子查询。 通常嵌套查询与IN、ALL、ANY、EXISTS配合使用。

select sc.sno, sc.cno, sc.grade from sc where sc.sno in (select student.sno from student where student.sno = 218003)

(3) 集合查询
涉及UNION、UNION ALL、INTERSECT、EXCEPT的查询操作就称为集合查询。其中,UNION和UNION ALL都会做并集,但UNION会去除重复的记录。最后,MySQL不支持INTERSECT和EXCEPT。

#student表中性别为女 与 student表中年龄大于19 这两个查询结果作并集
select * from student where sex= '女' union select * from student where age >19

 

6. SQL函数
(1) COUNT() 函数返回匹配指定条件的行数。

select count(*) from student
select count(sno) from student

7. 关于limit

#根据姓名排序,取第一条记录
select * from student order by sname limit 1
#根据id倒序排序,取前2条记录
select * from student order by sno desc limit 2
#从第2条记录往后,返回1条记录
select * from student limit 2, 1

友情链接