SQL数据系统2

一、数据操作DML:增删改

1.添加数据: INSERT

1)添加全部数据
insert into 表名 values(全部数据列表)

数据的写法：
数值型的不用引号
字符型和日期型的使用单引号
每个数据以逗号分割
数据的顺序必须和字段一一对应

示例:
insert into stud values(1, ‘zhangsan’,‘1990-10-16’,96.5);

2)添加部分数据
insert into 表名（部分字段名列表）values(部分数据列表)

数据的写法：
数值型的不用引号
字符型和日期型的使用单引号
每个数据以逗号分割
数据的顺序和部分字段名列表一一对应

示例:
insert into stud(sname,score,sid) values(‘ada’,80.5’5);

3)添加多条数据
insert into 表名[(部分字段)] values(第一条数据),(第二条数据)…(第N条数据);

示例: .
insert into stud values
(3,‘aaa’,‘1997-9-9’,80),
(4,李四’，’ 1997-9-9’,80),
(6,‘王五,1997-9-9’,80)，
(8,陈麻子,‘1997-9-9’,80),
(9,‘ddd’,‘1997-9-9’,80);

示例:
insert into stud(sname,score,sid) values
(‘力王’,97,12),
(‘王丽’,78,13);

2.查看数据表

select * from 表名；

3.添加约束后insert语句的使用特点

1)主键约束primary key
添加重复主键会报错
主键不能添加null数据
添加部分数据是不能缺少主键

示例: .
create table stud（
sid int primary key,
sname varchar(20)
);

insert into stud values(1,‘ada’);
insert into stud values(1 ,'bush);##报错， 因为主键重复
insert into stud values(null,'bush");##报错，主键不能为空
insert into stud(sname) values('smith");##出错，主键不能缺少

2）not null约束不能添加null数据
部分数据不能缺少
示例：
create table stud （
sid int ,
sname varchar(20) not null
);

insert into stud values(1,nulI);##出错,不能添加NULL数据
insert into stud(sid) values(1);##出错,部分数据不能缺少

3）默认值default的使用
示例:
create table stud
(
sid int,
sname varchar(20),
score float default 60
);
insert into stud values(1,‘ada’,90); 指定数据就不会使用默认值
insert into stud values(2,‘linda’,default); 使用默认值
insert into stud(sid,sname) values(3,‘smith’);部分字段，不指定默认值的字段

4）约束的修改
约束的修改,前提条件是数据表中没有违反约束规则的数据存在，否则会报错
示例：
CREATE DATABASE IF NOT EXISTS mydb;
USE mydb;
DROP TABLE IF EXISTS students;
CREATE TABLE students
(
sid int,
sname varchar(20),
birthday date,
score decimal(4,1)
);

//添加主键约束
alter table students(表名) add constraint pk_1(约束名) primary key(sid);
alter table students modify sid int primary key;//删除主键约束
alter table students drop primary key;//添加唯一约束
alter table students add unique(sname);
alter table students modify sname varchar(20) unique;
//显示唯一约束
show indexes from students\G;
//删除唯一约束
alter table students drop index sname;
//添加默认约束
alter table students alter score set default 60;
//删除默认约束
alter table students alter score drop default;
//修改字段非空约束
alter table students modify sname varchar(20) not null;//删除字段非空约束
alter table students modify sname varchar(20) null;

4.自增字段添加数据

示例：
create table stud
(
sid int primary key auto_increment,
sname varchar(20)
);
//可以指定自增数据
insert into stud values(18,‘linda’);
//自动增加：从指定的数据开始自增
insert into stud(sname) values(‘AAA’),(‘BBB’),(‘CCC’);

5.修改数据

updateupdate 表名 set 字段1=值1…[where…]
示例：
create table student
(
id int primary key,
name varchar(20) not null,
gender char(2) default ‘男’,
grade float
);
insert into student(id,name,grade) values
(1,‘宋江’,82),
(2,‘刘海’,60),
(3,‘吴文江’,72),
(4,‘海涛’,98),
(5,‘万海’,54),
(6,‘林冲’,28),
(7,‘马四海’,78),
(8,‘张婷发’,85),
(9,‘维素华’,null),
(10,‘吴恩江’,null),
(11,‘冯斯文’,67),
(12,‘刘晨’,81),
(13,‘袁林涛’,92),
(14,‘吴忠海’,100);

示例1：修改一个字段的全部数据
update student set gender=‘女’;
示例2：修改多个字段的全部数据
update student set gender=‘男’,grade=88;
示例3：修改部分数据
update student set grade=90 where id<=7;

6.删除数据

deletedelete from 表名 [where] 删除条件

示例1：全部删除
create table stud (
sid int primary key auto_increment,
sname varchar(20)
);
insert into stud(sname) values(‘aaa’),(‘bbb’),(‘ccc’);delete from stud; 自增数据仍然从前面最大数开始
truncate table stud；自增会重新开始
示例2： 部分删除
delete from stud where sid=7;

delete，drop，truncate 都有删除表的作用，区别在于：
1、delete 和 truncate 仅仅删除表数据，drop 连表数据和表结构一起删除
2、delete 是 DML 语句，操作完以后如果没有不想提交事务还可以回滚，truncate 和 drop 是 DDL 语句，操作完马上生效，不能回滚
3、执行的速度上，drop>truncate>delete

二、查询语句DQL

1、select:

1）查询全部数据
select * from 表名;
2）指定部分字段查询
select 字段列表 from 表名;

示例:
select id,name from student;
混合使用
select *,id,name from student;

3）where条件进行筛选
select 字段列表 from 表名 where 条件;

练习：使用各种关系运算符来筛选查询;
CREATE TABLE student
(
id INT(3) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
grade FLOAT,
gender CHAR(2)
);
#插入数据
INSERT INTO student(name,grade,gender)
VALUES(‘songjiang’,40,‘男’),
(‘wuyong’,100,‘男’),
(‘qinming’,90,‘男’),
(‘husanniang’,88,‘女’),
(‘sunerniang’,66,‘女’),
(‘wusong’,86,‘男’),
(‘linchong’,92,‘男’),
(‘yanqing’,90,NULL);

A）关系运算符：
示例:
select * from student where id=4;
=相等
<>/!=不等

<>=
<=

B）in/not in： 集合筛选
示例：查询3，8，5，2号学生信息
select * from student where id in (3,8,5,2);
反过来，查询不是:3，8，5，2号学生信息
select * from student where id not in (3,8,5,2);

C）between…and / not between…and ：范围筛选
示例：查询成绩在60到90之间的学生信息(包括60和90)
select * from student where grade between 60 and 90;
反过来，查询成绩不在60到90之间的学生信息
select * from student where grade not between 60 and 90;

D）null空值
查询null不能使用关系运算符号，应该使用 is null/is not null;
select * from student where gender is null;
select * from student where gender is not null;

4）distinct去掉重复
单字段去重
select distinct grade from student;
select distinct gender from student;

多字段要求每个字段都一样才能去掉重复
select distinct grade,name from student;

5）like
关键字，模糊查询
匹配查询%：任意多个字符
_：任意单个字符

示例1：查询姓名以s开头的学生
select * from student where name like ‘s%’;

示例2：查询姓名中间有s的
select * from student where name like ‘%s%’;

示例3：查询姓名第三个字符是n的学生
select * from student where name like ‘__n%’;

6)and/or 关键字
用于多个条件的连接and:
多个条件必须同时成立or: 多个条件中至少成立一个

示例1：查询成绩80分以上的男生
select * from student where grade>80 and gender=‘男’;

示例2：查找学号为1或者为5或者为8的学生
select * from student where id=1 or id=5 or id=8;
select * from student where id in(1,5,8);

7）高级查询 聚合函数：

对数据进行统计的函数
sum:总和
avg:平均
max:最大
min:最小
count:计算总记录数

示例1：计算学生的总分，平均分，最高分，最低分，总人数
select
sum(grade) as 总分,
avg(grade) as 平均分,
max(grade) as 最高分,
min(grade) as 最低分,
count(*) as 总人数
from student;

8）排序
order by 字段名 [asc|desc]
asc:默认，升序排序
desc：降序

示例：按成绩排序
select * from student order by grade; //默认升序
select * from student order by grade asc;
select * from student order by grade desc;//降序

9）limit设置查询条数(筛选)

说明：limit字句应该放在最后
示例1：
select * from student limit 1,3; //1开始的行数（0开始计数） 3提取的行数

练习：列出班上成绩的前三名同学信息
示例：select * from student order by grade desc limit 0,3;

10）分组统计
销售表:
create table xsb
(
sp varchar(20), #商品
xsy varchar(20), #销售员
sl int, #数量
money float #单价
);
insert into xsb values
(‘电视’,‘张三’,10,5000),
(‘手机’,‘李四’,3,4500),
(‘冰箱’,‘张三’,6,2500),
(‘电视’,‘李四’,2,5000),
(‘手机’,‘王五’,7,4500),
(‘洗衣机’,‘张三’,8,2500),
(‘汽车’,‘李四’,5,45000),
(‘电视’,‘王五’,2,4500),
(‘汽车’,‘张三’,1,45000),
(‘手机’,‘王五’,6,4500);

任务1：统计销售有哪几种商品？
去重
select distinct sp from xsb;
分组
select sp from xsb group by sp;

任务2：统计每种商品销售总数量,总金额
select sp, sum(sl) as 总数量, sum(sl*money) as 总金额 from xsb group by sp;

任务3：统计每个销售员每种商品销售数量
select xsy,sp,sum(sl) as 销售总数量 from xsb group by xsy,sp;

任务4：统计销售额不达标的销售员
统计性条件不能使用where ,要使用having 关键字，having条件只能再group by 子句之后
select xsy,sum(slmoney) as zxs from xsb group by xsy having zxs<100000;
select xsy,sum(sl * money) as zxs from xsb group by xsy having sum(slmoney)<100000;

任务5：找出销冠
select xsy,sum(sl*money) as zxs from xsb group by xsy order by zxs desc limit 0,1;

执行顺序select –>where –> group by–> having–>order by

11)函数
数学函数
sign //正负数
SQRT //平方根
ABS //绝对值
Round //四舍五入

字符串函数
length:长度 length(‘abcd’);

concat:字符串的连接:
select concat(id,’-’,name,’-’,gender) from student;

replace:字符串的替换
select replace(name,‘n’,‘x’) from student; //临时替换
update student set name=replace(name,‘n’,‘x’); //直接替换某个字段中的某个字符

substring :截取字串
select substring(name,1,3) from student;trim:去掉前后空格
select concat(‘a’,trim(’ xxx '),‘b’);

reverse:反转
select reverse(‘abcd’);

locate:查找位置
select locate(‘world’,‘hello world’);

日期函数:
select curdate();
select curtime();
select sysdate();//日期的运算函数

加天数
adddate();
select adddate(‘2020-7-14’,10);

减天数
subdate();
select subdate(‘2020-7-14’,10);

判断函数:
if(条件，值1，值2);
示例：
select name,if(grade>=60,‘及格’,‘不合格’) from student;ifnull();

如果为null 显示某个值
示例：
select name,ifnull(gender,‘未填写’) from student;

多条件判断
case
when … then
…
end
示例：
select id,name,
case
when grade>=90 and grade<=100 then ‘A’
when grade>=80 and grade<90 then ‘B’
when grade>=70 and grade<80 then ‘C’
when grade>=60 and grade<70 then ‘D’
when grade<60 then ‘E’
end as 等级
from student;