数据库基础知识

飞天小胖子
461 阅读11分钟

SQL常用增删改查语句

增 有2中方法

  1. 使用insert插入单行数据
insert into <表名> [列名] values <列值>

例子：

insert into Student (name,sex,birthday) values("张三",'男','1995/10/10');
  1. 使用insert,select 语句将现有表中的数据添加到已有的新表中
  • 语法：
  • insert into <已有的新表> <列名> select <原表列明> from <原表名>
  • 例子：
  • insert into adressList('姓名','地址','电子邮件')select name,address,email from Strdents

注意：查询到的数据个数、顺序、数据类型等，必须与插入的项保持一致

删

  1. 使用delete删除某些数据
  • 语法：

delete from <表名> [where <删除条件>]

  • 例子：

delete from Student where name='李四'

注意：删除整行不是删除单个字段，所有delete后面不能出现字段名

  1. 使用truncate table删除整个表的数据
  • 语法：truncate table<表名>
  • 例子：

truncate table Student

注意：删除表的所有行，但表的结构、列、约束、索引等不会被删除，不能用于有外键约束引用的表

  1. 删除表中的某个字段
  • 语法：

alter table <表名> drop column <列名>

  • 例子：

alter table student column sex;

  1. drop 删除表
  • 语法：
  • drop table <表名>

注意：drop table 语句用于删除表（表的结构、属性以及索引也会被删除） *例子： drop table Student

  1. drop 删除数据库
  • 语法：

drop database <数据库名>

  • 例子：

drop database Test

  1. 删除表格中的索引(MySql) 语法： drop index on mytable 例子：

改(update)

  1. 语法

update table_name set column_name=new_values where column_name=某值

  1. 例子
  • 更新某一行中的列

update table_name set name="李四" where name="张三";

  • 更新某一行的若干列

update table_name set name="李四",address="南京鼓楼" where id='202004200001';

  • 多表关联update单字段

update stu t set t.name=(select t1.name from stu1 t1 where t1.id=t.id) where exists(select 1 from stu1 t2 where t2.id=t.id)

  • 说明：将stu中与stu1具有相同id的name替换成stu1中的name

  • 多表关联update多字段

update stu t set (t.name,t.sex)=(select t1.name,t1.sex from stu t1 where t1.id=t.id)where exists(selec 1 from stu1 t2 where t2.id=t.id)

查询(select)

  1. 普通查询 语法：select <列名> from <表名> [where <查询条件表达式>][order by <排序的列名> [asc 或 desc]]
  • 查询Student表中所有数据行和列

select * from Student

  • 查询Student表中部分行列--条件查询r

select id from Stundent where name = '张三';

  • 在查询中使用as更改列名

select name as 姓名 from Student where id='91819901015689';

  • 查询空行

select name from Student where email is null;

说明：sql中用is null 或者 is not null 来判断是否为空行

  • 在查询中使用常量

select name '北京' as 地址 from Student

  • 说明： 查询表Student，显示name列，并添加地址列，其列值都为北京

  • 查询返回限制行数（关键字top） select top 6 name from Student 说明：查询表Sudent，显示列name的前6行，top为关键字（oracle 中没有top 关键字用rownum代替） select * from Sudent where rownum <6

  • 查询排序（关键字：order by，asc，desc）

select name from achievement where grade >=60 order by grade desc 说明： 查询表中成绩大于60的所有行，并按降序显示name列；默认为asc升序

  1. 模糊查询
  • 使用like 进行模糊查询

注意：like 运算符只能用于字符串

select * from Student where name like '赵%'

  • 使用between在某个范围内进行查询 select * from Student where age between 16 and 18

  • 使用in在列举值内进行查询

select name from Student where address in ('北京'、'上海'、'唐门')

SQl多表查询

连接(join):将一张表中的行按某个条件和另一张表中的行连接起来形成一个新行的过程。

  1. 根据连接查询返回的结果，分3类：
  • 内连接
  • 外连接
  • 交叉连接
  1. 根据连接条件所使用的操作符，分2类：
  • 不相等连接
  • 相等连接
  1. 内联接 inner join
  • 只返回两张表中所有满足连接条件的行
  • 例子:

select * from a,b where a.id=b.id

外连接outer join

  • 使用外连接，不但返回符合条件连接和查询条件的数据行，还返回不符合条件的一些行。
  • 在Mysql数据库中外连接分两类： 左外连接、右外连接
  1. 左外连接包含left join
  • 左表所有行，如果左表某行在右表没有匹配，则结果中应对右表部分全部为空

select S.Sname,X.xuefen from stu S left join xuanke X on S.sid=X.sid

  1. 右边连接包含right join
  • 右表所有行，如果右表中某行在左表中没有匹配，则结果中对应左表的部分全部为空

select S.sname, X.xuefen from xuanke X right join stu S on X.sid=S.Sid

  1. 交叉连接 cross join

没有where字句的交叉连接将产生连接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。

select * from stu cross join xuanke;

drop、delete和truncate的区别？

相同点： truncate和不带where子句的delete、以及drop都会删除表内的数据。 drop、truncate都是DDL语句（数据定义语句），执行后会自动提交 不同点： truncate 和 delete 只删除数据不删除表的数据结构，drop语句将删除表的结构被依赖的约束、触发器、索引；依赖与该表的存储过程/函数将保留，但是变为invalid状态 delete 语句时数据库操作语言，这个操作会放到rollback segement中，事务提交之后才生效；如果有相应的trigger，执行的时候将被触发 truncate、drop 是数据库定义语言ddl，操作立即生效，原数据不放到rollback segment中，不能回滚，操作不触发trigger

delete 语句不影响表所占用extent，高水线保持原位置不动。drop 语句将表占用的控件全部释放。truncate 语句缺省情况下见空间释放到minextents个extent，除非使用reuse storage；truncate 会将高水线复位

速度，一般来说：drop>truncate>delete 安全性：小心使用drop 和 truncate，尤其是没有备份的时候。使用上，想删除部分数据行用delete，注意带上where字句，回滚段要足够大；想删除表，用drop；想保留表而将所有数据删除，如果和事务无关，用truncate即可。

delete是dml语句，不会自动提交。drop/truncate是ddl语句，执行后会自动提交

truncate table 在功能上与不带where字句的delete语句相同：二者均删除表中的全部行。但truncate table 比delete速度快，且使用的系统和事务日志资源少。delete 语句每删除一行，并在事务日志中为所删除的每行记录一项。truncate table 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

truncate table 删除表中的所有行，但表结果及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用delete。如果要删除表定义及其数据，请使用drop table语句

对于由 foreign key 约束引用的表，不能使用 truncate table，而应该用不带where 字句的delete语句。由于truncate table 不记录在日志中，所以它不能激活触发器

truncate table 不能用于参与了索引视图的表

在SQL语句中，in，exists，join那个效率更高一点

Exists、in、join，都可以用来实现形如“查询A表中在或不在B表中的记录”的查询逻辑。

查询的两个表大小相当的情况下，3种查询方式的执行时间通常是： Exists <= in <= join not exists <= not in <= left join 只有当表中的字段允许null时，not in 的方式最慢 not exists <= left join <= not in

但是如果两个表中一个较小，一个较大，则子查询表大的用exists，子查询表小的用in，因为in是把外表和内表做hansh连接，而exists是对外表做loop循环，每次loop循环在对内表进行查询。无论哪个表大，用not exists都比not in要快。这是因为如果查询语句使用ont in 那么内外表都要进行全表扫描，没有用到索引；而not exists 的子查询依然用到表上的索引。

in 的好处是逻辑直观简单（通常是对独子查询）；缺点是只能判断单字段，并且not in 时效率较低，而且null 会导致不想要的结果。

exists 的好处是效率高，可以判断单字段和组合字段，并不是受null 影响；缺点是逻辑稍微复杂（通常是相关子查询）

join 的用途是连接两个表，而不是判断一个表的记录是否在另一表。

sql order by的用法

使用order by，一般是用来，依照查询结果的某一列或多列属性，进行排序（ASC，DESC，默认为：ASC）

当排序列有空值时： ASC ： 排序列为空值的元组最后显示 desc ： 排序列为空值的元组最先显示

  1. 实例
  • 单一列属性排序

select * from s order by id desc

  • 多个列属性排序(选择多个列属性进行排序，然后排序的顺序是，从左到右，依次排序)

select * from s order by sname desc,sage desc

SQL group by、having的用法

group by

  1. 实例：
  • 显示每个地区总人口数和总面积

select region,sum(population),sum(area) from bbc group by region

先以region 把返回记录分成多个组。分组后，然后用聚合函数对每组中的不同字段做运算。

having

  • having 子句可以让我们筛选成组后的各种数据，where子句在聚合前筛选记录，也就是说作用在group by和having子句前。而having子句在聚合后对组记录进行筛选。
  1. 例子
  • 显示每个地区的总人数和总面积，仅显示哪些面积超过10000的地区

selct region,sum(population),sum(area) from bbc group by region having sum(area) > 10000

一般group by 是和聚合函数配合使用，group by有一个原则，就是select 后面的所有列中，没有使用聚合函数的列，必须出现在group by 后面

SQL 中Where与Having的区别

Where 是一个约束声明，使用Where来约束来之数据库的数据，Where是在结果返回之前起作用的，且Where中不能使用聚合函数 Having 是过滤声明，是在查询返回结果集以后对查询结果进行的过滤，在Having中可以使用聚合函数

sqL between and 用法和边界问题

操作符between...and 会选取介于两个值之间的数据范围。这些值可以是数值、文本或日期

  1. 例子
  • 查询成绩为100~120之间的学生

select name from ss where grade bewteen 100 and 120

不同的数据库对between...and操作符的处理方式是有差异的。某些数据库会列出介于"adams"和"carter"之间的人，但不包括"adams"和"carter";某些数据库会列出介于"adams"和"carter"之间"adams"和"carter"的人；而另一些数据库介于"adams"和"carter"之间的人，包含"adams"但不包括"carter"

关于SQL中between语句查询日期的问题

某个表中，日期以“YYYY-MM-DD 00：00：00”存放 如： A 2020-04-10 21:22:00 B 2020-04-10 21:00:00

现在想查询日期为2020-04-22的记录

要实现功能，就使用连接字符串的形式，在短日期后面吧时间补全。

select * from tb CtateDate bewteen '"+dateTimePicker1.value.ToshorDateString()+"00:00:00' + dateTimePicker2.Value.ToShortDateString() + " 23:59:59'

SQL查询今天、昨天、本周、上周、本月、上月数据

blog.csdn.net/huyunsheng2…

  1. 今天 select * from table_name where to_days(时间段名称) = to_days(now ());

  2. 昨天

select * from table_name where to_days(now()) - to_days(时间段名) <= 1

  1. 近7天

select * from table_name where date_sub(curdate(),interval 7 day ) <= date(时间段名);

  1. 近30天

select * from table_name where date_sub(curdate(), interval 30 day)<=date(时间段名);

  1. 本月 select * from table_name where date_format(时间段名称,'%Y%m') = date_format(curdate(),'%Y%m')

  2. 上月

select * from table_name where period_diff(date_format(now,'%Y%m'),date_format(时间段名称,'%Y%m'))=1

  1. 查询本季度数据

select * from table_name where quarter(create_date) = quarter(now());

  1. 查询上季度数据

select * from table_name where quarter(create_date) = quarter(date_sub(now(),interval 1 quarter));

  1. 查询本年度数据

select * from table_name where Year(create_date)=Year(now());

  1. 查询上年度数据

select * from table _name where Year(create_date) = year(date_sub(now(),interval 1 year);

  1. 查询本周的数据 select * from table_name where yearweek(date_format(submittime,'%Y-%m-%d')) = yearweek((now());

  2. 查询上周的数据 select * from table_name where YEARWEEK(date_format(submittime,%Y-%m-%d)) = YEARWEEK(now())-1;

在多表查询中in 和 join那个效率高

非相关（无索引）的多表查询中，使用in与join的查询都是先将外部表的查询结果加入连接缓冲区，再从内部表拿取数据进入缓冲区比价。但in存在优先级的关系，比join多了一次subquery的查询

当两张表先关（外键相连时）使用in方式进行查询时，不再像非相关那样显示子查询subquery了，而是有个参照过程！ 先借助索引多外部表test2进行扫描，在借助索引对test1进行扫描，其中参照了test2的id列 使用join 方式也是一样有一个参照过程！ 这使两种方式的查询也没有用上面所说的连接缓冲区与阻塞嵌套循环。 总结，当两张表相关（外键相连）时，无论是in还是join，联合查找都是一个参照的过程。

avatar
文章
阅读
粉丝