2018-1-30 数据操作语言(DML)操作表的增删改查
数据插入(添加数据):
1、insert into 表名(字段名1,字段名2,....)values (值a1,值a2, .....), (值b1,值b2, .....),..... ;
在表1中插入数据
2、insert into 表名1(字段名1,字段名2,....)select 字段名1,字段名2,.... from 表名2;
将表2中的字段提取出来插入表1中
3、insert into 表名 set 字段名1=值1,字段名2=值2, ..... ;
外键好处:两张表关联紧密,数据严谨
外键坏处:不方便操作
写创建表的sql语句,先写字段,后加约束
注意:1、在“字段”和“值”之间,都有“一一对应”关系
2、前两种形式,可以一次插入多条数据。
3、值的形式,通常是这样:数字直接写,字符串和时间加单引号,但如果是函数值,则不能加引号。
4、观念问题:不管那种形式,都要理解为:插入数据的单位是“行”;
5、有的字段通常无需插入数据,此时不应该出现该字段名:auto_increment(自增长), timestamp(时间戳)。
例:
insert into teacher values
("804","李诚","男","1958-12-02","副教授","计算机系"),
("856","张旭","男","1969-03-12","讲师","电子工程系"),
("825","王萍","女","1972-05-05","助教","计算机系 "),
("831","刘冰","女","1977-08-14","助教","电子工程系");
载入外部“形式整齐”的数据:
load data infile ‘文件完整名(含路径)’ into table 表名;
复制一个表的结构和数据:
create table 表名1 select * from 表名2;
删除数据:delete from 表名 [where条件] [order排序] [limit限定];
中括号的意思代表可以省略。
1、观念问题:删除也是应该以理解为“以行为单位”进行的。
2、删除语句中,where条件通常都要写上,因为如果不写,则就删除了所有数据,应用中极少如此。
3、order排序子句和limit限定子句,应用中通常不需要。
①order排序子句用于设定删除数据的先后顺序。
② limit限定子句用于限定在设定的顺序情况下删除指定的某些行。
truncate [table] 表名;用于直接删除整个表(结构)并重新创建该表。
跟delete 语句不带where条件,有什么区别?
主要影响的是类似:auto_increment这种类型的字段值:
truncate结果会重新计算,delete还能继续增长。
修改数据:update 表名 set 字段名1=值表达式1,字段名2=值表达式2,....[where条件] [order排序] [limit限定];
1、以“行”为单位进行,可以指定只更新其中的部分字段。
2、where条件子句,在应用中,几乎必须有。
3、order排序子句通常不需要,用于将要更新的数据指定更新的顺序。
4、limit限定子句通常不需要,用于将要更新的数据的指定顺序去更新部分(局部)数据,比如:前500行。
5、字段的值可以是表达式,或直接值,或函数,如果是直接值,同样遵循insert语句中的值的写法。
例:update student set sname="曾华" where ssex="女";
意思就是所有性别为“女”的都改名叫“曾华”。
复制表:
方法1:
create table tab2 like tab1; //复制结构
insert into tab2 select * from tab1; //复制数据
这种方法可以比较完整。
方法2:
create table tab2 select * from tab1; //同时复制结构和数据
这种方法可能会丢一些结构信息,比如:索引,约束,自增长属性
表查询:
select [all | distinct] 字段或表达式列表 [from子句] [where子句] [group by子句] [having子句] [order by子句] [limit子句];
select语句,作用是从“数据源”中,找出(取出)一定的数据,并作为该语句的返回结果(数据集)
数据源:通常,数据源就是“表”。
但:也可以没有数据源,而是使用“直接数据”(或函数执行结果)。
给字段加别名:select sname as “学生姓名” from student;必须有from子句。
all、distinct
用于设定所select出来的数据是否允许出现重复行(完全相同的数据行)
all:允许出现——默认不写就是All(允许的)。
distinct:不允许出现——就是所谓的“消除重复行”。写在字段前面
from子句:就是指定数据的来源,其实就是“表”,可以是一个表名,也可以是多个表——多表查询了。
例:select a.sno,b.cname,c.degree from student as a,course as b,score as c where a.sno=c.sno and b.cno=c.cno;
where子句:where子句,相当于php或js中的if条件语句:其最终结果就是布尔值(true/false)
where true, where 1; where 1=1; 都表示true
where false, where 0; where 1<>1; 都表示false
where中可用的运算符:算术运算符: + - * / %
比较运算符: > >= < <= =(等于) <>(不等于)
==(等于,mysql扩展),!=(不等于,mysql扩展)
逻辑运算符: and(与) or(或) not(非)
布尔值的判断方式:
布尔值:本质上,布尔值只是一位整数的“别名”,0表示false,非0表示true。
判断为true: XX is true
判断为fale: XX is false
XX应该是一个字段名,且其类型应该是一个整数。
实际应用中,布尔值判断很少用,因为可以直接使用数学大小。
空值的判断方式:
判断为null: XX is null
判断为非空: XX is not null
XX应该是一个字段名
例:select * from tab5 where f4 is null;
between语法:
XX between 值1 and 值2;
含义:字段XX的值在值1和值2之间(含),相当于:XX >=值1 and XX<=值2;
例:select * from score where degree between 60 and 80;
like语法:XX like "要查找字符";
① % 其含义是:代表任意个数的任意字符
② _ 其含义是:代表1个的任意字符
实际应用中的模糊查找,通常都是这样:like "%关键字%";
例:select avg(degree) from score group by cno having cno like"3%" and count(*)>=5 ;
有from可以没有where,而有where必须有from。
group by 分组子句:group by 字段1 排序方式1,字段2 排序方式2, .....
通常都只进行一个字段的分组。
1,数据结果只能是“组”——没有数据本身的个体
2,数据结果就可能“丢失”很多特性,比如没有性别,身高,姓名,等等。
3,实际上,结果中通常只剩下“组”作为整体的信息:
首先是该组的本身依据值,
另外,这几个可能的值:组内成员的个数,组内某些字段的最大值,最小值,平均值,总和值。
其他字段,通常就不能用了。
4,如果是2个字段或以上分组,则其实是相当于对前一分组的组内,再进行后一依据的分组。
例:select avg(degree) from score group by cno having cno like"3%" and count(*)>=5 ;
意思是查询Score表中至少有5名学生选修的并以3开头的课程的平均分数
在分组查询中,基本都依赖于一下几个函数(聚合函数,统计函数):
count(*): 统计一组中的数量,通常用“*”做参数
max(字段名):获取该字段中在该组中的最大值。
min(字段名):获取该字段中在该组中的最小值。
sum(字段名):获取该字段中在该组中的总和。
avg(字段名):获取该字段中在该组中的平均值。
group_concat 获取该字段中某字段的所有值。
having子句:相当于group by中的where。
where是针对表的字段的值进行“条件判断”
having是只针对groupby之后的“组”数据进行条件判断
例:select count(*) as f1 , max(f1) as f2 from tab1 group by f3 having f1 > 5 and f2 < 1000;
order by子句:order by 排序字段1 [排序方式], 排序字段2 [排序方式], .....
正序: ASC(默认值),可以省略
倒序: DESC
例:select* from student order by sno; student表格中的sno列正序排列
select * from student order by sno desc; student表格中的sno列倒序排列
select * from student order by sno,cno desc; student表格中的sno列正序排列,cno倒叙排列
select * from student order by sno,cno desc; student表格中的sno和cno都倒叙排列