• 表的CRUD
  • varchar，varchar2,char的区别：
  • 时间/时间戳使用
  • 创建表
  • 创建一张表，表结构与已经存在的表一致
  • 查看表的信息
  • 表中增加一列：
  • 修改表中的列：
  • 删除表中的列
  • 修改表名
  • 修改表的字符集
  • 修改表中的列名
  • 表中数据的插入 更新 删除
  • INSERT 语句
  • update语句：
  • delete语句
  • select 语句：
  • 常用的where
  • between and, in,like
  • order by
  • 常用的函数
  • 统计函数 count
  • 合计函数sum
  • avg 平均函数
  • max min 求最值函数
  • group by 和 having 分组
  • 字符串相关函数
  • 多表查询-自连接
  • 多表查询-子查询
  • 单行子查询
  • 多行子查询
  • 子查询当做临时表
  • 在多行子查询中使用all操作符
  • 在多行子查询中使用any操作符
  • 多列子查询 （子查询返回的 数据是多列的）
  • 子查询练习
  • 表中数据的复制和去重
  • 复制
  • 去重
  • 合并查询 union
  • 外连接
  • 视图view
  • 概念：
  • 视图的基本使用
  • 视图的说明
  • 视图最佳实践


表的CRUD

varchar，varchar2,char的区别：

char：存储的是固定长度的，定义长度为多少就占多少存储空间，要是存的字符没有占满空间，会自动用空格补充。

varchar：存储的空间是可变的，不是定义多大就占用多少空间，而是根据实际存储字符的大小决定占用多少存储空间。

varchar是标准sql里面的。 varchar2是oracle提供的独有的数据类型。

5.0版本以上，varchar(100)，指的是100字符，无论存放的是数字、字母还是UTF8汉字（每个汉字3字节），都可以存放100个

时间/时间戳使用

create table t14(
    birthday DATE,--记录年月日
    job_time DATETIME,--记录年月日 时分秒
    login_time TIMESTAMP --时间戳
    	NOT NULL DEFAULT CURRENT_TIMRSTAMP -- 定义时间戳不为空，默认为当前时间
    	ON UPDATE CURRENT_TIMESTAMP -- 定义时间戳在修改时自动更新为当前时间
);

-- 执行这条语句，会将login_time的值设置为当前时间 
INSERT INTO t14(birthday,job_time) values ('2022-11-11','2022-11-11 10:10:10');
-- 如果执行更新记录的SQL，则时间戳login_time的值会自动更新为当前时间
update t14 SET birthday='2022-11-12',job_time='2022-11-12 10:10:10' where ...
-- 这里不需要考虑是否login_time 时间戳字段有值 ，会自动赋值

创建表

create table `emp` (
    id int comment 'id',
    'name' VARCHAR(32) comment '名字',
    sex char(1) comment '性别',
    birthday DATE comment '生日',
    entry_date DATETIME comment '进入时间',
    job VARCHAR(32) comment '工作',
    salary DOUBLE comment '薪水',
);CHARSET UTF8 COLLATE utf8_bin ENGINE INNODE;

-- 插入一条记录
INSERT INTO `emp` values (100,'lsi','男','2022-03-20','2-22-03-20 11:11:11','doSomeThing',3000);

创建一张表，表结构与已经存在的表一致

  • 即创建一张属性列与已经存在的表一致的表
create table emp2 like emp;

查看表的信息

desc emp2;

表中增加一列：

-- 在员工表emp上增加一个image列，varchar类型，默认值空，要求在列字段salary的后面
alter table emp add image varchar(32) not null default '' after salary;

修改表中的列：

-- 修改job列，长度变为60,该列的值默认为空值
alter table emp modify job varchar(60) not null dafault '';

删除表中的列

-- 删除sex列
alter table emp drop sex;

-- oracle中要加column：
alter table emp drop column sex;

修改表名

--将表名改为employee
rename table emp TO employee

修改表的字符集

-- 修改表的字符集为utf8
alter table employee character set utf8;

修改表中的列名

-- 列名name修改为user_name 并且该列默认值不为空，为''
alter table employee change `name` `user_name` varchar(64) not null default '';

表中数据的插入 更新 删除

INSERT 语句

insert into emp (id,name,sex,birthday,entry_date,job,salary) values (10001,'lsi','男','2021-11-11','2021-11-11 11:11:11','tset',30000);

注意：

1、int double类型对应的可以加单引号，也可以不加，sql底层会自动对应

2、插入的数据要与字段的数据类型对应

3、字符和日期类型数据要包含在单引号中

4、默认值的使用，当不给某个字段值时，如果有默认值就会添加默认值，否则报错。

update语句：

-- UPDATE 语句如果没有where条件，会更新全部记录
update employee set salary =3000;
-- 更新时要加条件
update employee set salary=3000 where user_name='test';
-- 更新salary字段的值，在原有基础上加1000
update employee set salary=salary+1000 where user_name ='test';

delete语句

-- 不加where条件，全部删除
delete from employee;
-- 根据条件删除记录
delete from employee where user_name='test';

select 语句：

常用的where

定义一个studnet表，有id,name,chinese,math,english属性列

-- 基本列的加减
 -- 统计每个学生的总分并加10分
 select `name` ,(chinese + english + math +10) as total_score from student;
 
 -- 查询总分大于200分的学生
 select * from student where (chinese + english + math) >200;
 -- 查询math大于60并且id大于90的学生成绩
 select * from student where math > 60 and id > 90;
 -- 查询英语成绩大于语文成绩的同学
 select * from student where english > chinese;
 -- 查询总分大于200分并且数学成绩小于语文成绩的姓韩的学生
 select * from student where (chinese + english + math) >200 and math < chinese and name like '韩%';

between and, in,like

-- 查询英语分数在80-90之间的同学
 select * from student where english between 80 and 90;
 -- 查询数学分数为89,90,91的同学
 select * from student where math in (89,90,91);
 -- 查询所有姓李的学生成绩
 select `name` ,(chinese + english + math ) as total_score from student where `name` like '李%';
 -- 查询数学分 >80,语文分>80 的同学
 select * from student where math >80 and chinese >80;

order by

指定排序的列，排序的列既可以是表中的列名，也可以是select语句后列的别名。

Asc 升序(默认) ，Desc降序

-- 对数学成绩排序后输出(升序)
select * from student order by math;
-- 对总分按从高到低的顺序输出
select `name`,(chinese + english + math ) as total_score from student order by total_score desc;
--对姓李的学生成绩排序输出(升序)
select `name`,(chinese + english + math ) as total_score from student where name like '李%' order by total_score;
select * from student where name like '李%' order by (chinese + english + math);

常用的函数

统计函数 count

count(*)和count(列)的区别：

count (*) 返回满足条件的记录的行数。count(列)统计满足条件的某列有多少个，但是会排除为null的情况。

-- 统计一个班有多少学生
select count(*) from student;
-- 统计数学成绩大于90的学生的个数
select count(*) from student where math>90;
-- 统计总分大于250的人数有多少
select count(*) from student where (math+chinese +english) > 250;
合计函数sum

sum函数一般用在数值列

-- 统计一个班级数学总成绩
select sum(math) from student;
-- 统计一个班级语文，英语数学各科的总成绩
select sum(math) ,sum(english),sum(chinese) from student;
--统计一个班级语文，英语，数学的成绩总和
select sum(math+english+chinese) from student;
-- 统计一个班级语文成绩平均分
select avg(chinese) from student;
select sum(chinese)/count(*) from student;
avg 平均函数
-- 求一个班级数学平均分
select avg(math) from student; 
-- 求一个班级总分平均分
select avg(math+english+chinese) from student;
max min 求最值函数
-- 求班级数学最高分和最低分
select max(math) as max ,min(math) as min from student;

group by 和 having 分组

group by对查询结果分组。having用于限制分组显示结果

  • where 和group by不能一起用，对group by条件过滤用having
  • group by后，select后只能跟函数或者分组的那个属性，因此复杂查询时可以将此作为子查询或者临时表进行操作

创建部门表dept：

有三个属性列：deptno（部门编号） ,dname（部门名字） ,Loc（地址）

创建员工表emp:

有8个属性列：empno（编号），ename（名字），job（工作），mgr（上级编号），hiredate(入职时间)，sal（薪水），comm（红利），deptno（部门编号）

创建工资级别表sal_grade：

有三个属性列：grade（工资级别），losal（该级别的最低工资），hisal(该级别的最高工资)

-- 显示每个部门的最高工资和平均工资
select max(sal),avg(sal),deptno from emp group by deptno;
-- 显示每个部门的每种岗位的平均工资和最低工资
select  min(sal),avg(sal),deptno ,job from emp group by deptno , job;
-- 显示平均工资低于2000的部门号和它的平均工资
select deptno ,avg(sal) from emp where avg(sal)<2000 group by deptno; (这种写法是错误的，不能用where)
select deptno ,avg(sal) from emp   group by deptno having avg(sal)<2000; 这种是正确的
select deptno ,avg(sal) as avg_sal from emp   group by deptno having avg_sal <2000; 使用别名也可以，效率高一些

字符串相关函数

-- charset(str) 返回str的字符集
-- 查询emp表中ename的字符集
select charset(ename) from emp; -- 返回结果为utf8

-- concat (string2[,...])连接字串，将多个列拼接成一列
select concat(ename ,'工作是',job) from emp;

-- instr(string,substring) 返回substring在string中出现的位置，没有返回0
-- dual 亚元表 ，系统表 可以作为测试表使用
select inser('hanshunping','ping') from dual; -- 返回结果是8

-- ucase(str) 转换成大写  lcase(str) 转换成小写  将str转换为大写或小写
select ucase(ename) from emp;
select lcase(ename) from emp;

-- left (str,length); 从str中的左边起取length个字符
-- right(str,length);从str中的右边起取length个字符
select left(ename,2) from emp;

-- length (str) str的长度【按照字节计算】
select length(ename) from emp; -- 查询ename的字段长度 

-- replace(str ,search_str,replace_str) 在str中，用search_str 替换replace_str
select ename ,replace(job,'manager','经理') from emp;

-- strcmp (str1,str2) 逐字符比较两字符的大小
select strcmp('hsp','Hsp') from dual;

-- substring(str,position [,length]) 从str的position开始，[从1开始计算] 截取字符串
select substring(ename,1,2) from emp; -- 从ename列的第一个位置开始取2个字符。

-- ltrim(str) ,rtrim(str),trim(str) 去除前端空格或者后端空格
select trim(' hsp ') from dual; -- 去掉字符串' hsp '的空格

--练习 以首字母小写的方式显示所有员工emp表中姓名
select concat(lcase(substring(ename,1,1)),substring(ename,2) as new_name from dept_emp;
select concat(lcase(left(ename,1,)),substring(ename,2) as new_name from dept_emp;

多表查询-自连接

sqlite3 varchar 类型 sql varchar()_sqlite3 varchar 类型

-- 查询公司员工的名字和他的上级
select 
	worker.ename as '员工',boss.ename as '上级' 
	from emp worker,emp boss 
	where worker.mgr=boss.empno;

多表查询-子查询

单行子查询
-- 显示与smith同一部门的所有员工
	-- 先查所有的员工
	select * from emp;
	--再查smith的所在部门
	select deptno from emp where ename='smith';
	--二者组合起来
	select * 
        from emp 
        where deptno =(
            select deptno 
            from emp 
            where ename='smith'
        );
多行子查询
-- 查询和部门10的工作相同的雇员的名字，岗位，工资，部门号，但是不含10号部门自己的雇员
	-- 先查雇员的名字，岗位 工资 部门号
	select ename,job,sal,deptno from emp;
	-- 再查部门10 的工作(结果去重)
	select distinct job from emp where deptno='10'
	-- 查出和部门10的工作相同的雇员的名字，岗位，工资，部门号
	select ename,job,sal,deptno from emp where job in(
    	select distinct job from emp where deptno='10'
    );
    -- 排除10号部门
    select ename,job,sal,deptno from emp where job in(
    	select distinct job from emp where deptno='10'
    ) and deptno !=10;

sqlite3 varchar 类型 sql varchar()_sqlite3 varchar 类型_02

子查询当做临时表

sqlite3 varchar 类型 sql varchar()_子查询_03

-- 查询ecshop中各个类别中价格最高的商品的id，类别，名字，价格
	-- 查询各个类别中 价格最高的商品
	select max(shop_price) as max_price,cat_id from ecshop group by cat_id;
	-- 将上面的查询结果作为子查询
	select goods_id, ecshop.cat_id, goods_name, shop_price
	from (
    	select max(shop_price) as max_price, cat_id from ecshop group by cat_id
    ) temp ,ecshop 
    where temp.max_price = ecshop.shop_price 
    		and temp.cat_id=ecshop.cat_id;
  • 注意这里只用max和分组group by解决不了，因为使用group by后，前面的select只能用分组的字段和函数，不能用其他的字段。
在多行子查询中使用all操作符
-- 显示工资比30号部门的所有员工的工资高的员工的姓名，工资和部门号
select ename,sal,deptno from emp where sal> all (select sal from emp where deptno=30);
在多行子查询中使用any操作符
-- 显示工资比部门30的其中一个员工的工资高的员工的姓名，工资和部门号
select ename,sal,deptno from emp where sal> any (select sal from emp where deptno=30);
select ename ,sal,deptno from emp where sal>(select min(sal) from emp where deptno=30);
多列子查询 （子查询返回的 数据是多列的）

sqlite3 varchar 类型 sql varchar()_mysql_04

select * from student where (math,english,chinese)=(
	select math,english,chinese from student where name='宋江'
);
-- 查询与Smith的部门和岗位完全相同的所有雇员(并且不含smith本人)
select * from emp where (deptno ,job)= 
	(select deptno,job from emp where ename='smith')
and ename!='smith';
子查询练习
-- 查找每个部门里工资高于本部门平均工资的人的资料
select emp.* from emp,
	(select avg(sal) as avg_sal,deptno from emp group by deptno) emp1,
	where emp.sal>emp1.avg_sal 
	and emp.deptno=emp1.deptno
-- 查找每个部门工资最高的人的详细资料
select emp. * from emp ,(
    select max(sal) as max_sal,deptno from emp group by deptno
)emp1, 
where emp.sal=emp1.max_sal and emp1.deptno=emp.deptno;
-- 查询每个部门的信息(包括：部门名，编号，地址)和人员数量
	-- 查询每个部门的人数
	select count(*) as sum,deptno from emp group by deptno;
	-- 查询部门信息
	select * from dept;
	
select dept.deptname,dept.deptno,dept.address ,emp1.sum
	from dept,(
    select count(*) as sum,deptno from emp group by deptno)emp1
    where emp1.deptno=dept.deptno

表中数据的复制和去重

复制
-- 将表a的部分字段的数据批量复制到表b中的部分字段
insert into my_tab(id,`name`,sal,job,deptno)
	select id,ename,sal,job,deptno from
		emp;
-- 自我复制
insert into emp 
	select * from emp;
  • 如果id是自增的，在插入时就不要选择id属性列
去重

思路：

  • 先创建一个临时表b，表结构与当前的表a相同；
  • 把表a中的数据通过distinct去重后复制到表b中
  • 清除表a中的数据
  • 把表b中的数据复制到表a中
  • 删除表b
1.
create table b like a;
2.
insert into b select distinct * from a;
3.
delete from a;
4.
insert into a select * from b;
5. 
drop table b;

或者：

  • 先创建一个临时表b，表结构与当前的表a相同；
  • 把表a中的数据通过distinct去重后复制到表b中
  • 将表b名字改为表a
create table b like a;
insert into b select distinct * from a;
rename table b TO a;

合并查询 union

union: 合并

union all: 合并并去掉结果中重复的数据

select ename,sal,job from emp where sal>2000
union all 
select ename,sal,job from emp where job='manager';

select ename,sal,job from emp where sal>2000
union  
select ename,sal,job from emp where job='manager';

外连接

左外连接：左侧的表完全显示 left join … on

右外连接：右侧的表完全显示 right join … on

之前的查询where a.id=b.id 这种是 显示两张表的匹配记录，如果匹配 不上就不显示，如果要实现没有匹配上的记录就要使用外连接。

表stu

id

name

1

jack

2

tom

3

kity

4

nono

表 exam

id

grade

1

56

2

76

11

8

-- 使用左连接（显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id号，成绩显示为空）
select stu.id,stu.name,exam.grade
	from stu left join exam on stu.id=exam.id;
--右连接（显示所有成绩，如果没有名字匹配，显示空）
select stu.id,stu.name,exam.grade
	from stu right join exam where stu.id=exam.id;
-- 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
	-- 没有员工的部门，说明部门的信息是冗余的
-- 左连接
select emp.* ,dept.name
	from dept left join emp where dept.deptno=emp.deptno;
-- 右连接
select emp.* ,dept.name
	from emp right join dept where dept.deptno=emp.deptno;

视图view

概念：

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包括列，其数据来自对应的真实表(基表)

  • 视图是根据基表创建的，视图是虚拟的表
  • 视图也有列，数据来自基表
  • 通过视图可以修改基表的数据
  • 基表的改变，也会影响到视图的数据
视图的基本使用
create view 视图名 as select语句
alter view 视图名 as select语句
show create view 视图名 -- 查看创建视图的指令
drop view 视图名1，视图名2 --删除视图
-- 创建视图
-- 创建一个视图emp_view,只能查询emp表的(empno,ename,job和deptno)的信息
create view emp_view
	as 
	select empno,ename,job,deptno from emp;
-- 创建视图 select三张表
create view emp_view3
	as
	select empno,ename,dname,grade
	from emp,dept,salgrade
	where emp.deptno=dept.deptno and 
	(sal between losal and hisal)
	
-- 查看已创建的视图
desc emp_view;

-- 在视图中可以再创建视图
create view emp_view1
	as 
	select empno,ename from emp_view;
视图的说明
  • 表中存放的是数据，而视图存放的是SQL语句，每次使用视图，都会运行一次里面的SQL语句，并形成一个临时表，同时，视图内的数据随着原表数据的更新而更新。
  • 不要在视图的基础上再创建视图，会降低SQL的运行效率。
  • 创建视图后，在数据库对应的文件夹下发现只有一个视图结构文件(视图名.frm)。说明视图并不会存储数据，只是存储的sql语句。


视图最佳实践
  • 安全：一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。
  • 性能：关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时，数据库查询通常会用到连接join。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用join查询数据。
  • 灵活：如果系统中有一张旧的表，这张表由于设计问题，即将被废弃。然而，很多应用都是基于这张表，不易改变。这时就可以建立一张视图，视图中的数据直接映射到新表，这样，就可以少做很多改动，也达到了升级数据表的目的。