实习之sql常用语句

sql的功能

          

SELECT：从数据库中选取数据

select * from table_name;

##如果我们要去除结果表中的重复行，可用DISTINCT实现

select distinct column_name from table_name;

##确定范围（BETWEEN...AND）

 BETWEEN....AND...   包括临界值

NOT BETWEEN....AND...  不包括临界值

查询表中的id在100到200之间的记录

select id，name from table_name where id between 100 and 200;

查询id不在100到200之间的记录

select id，name from table_name where id not between 100 and 200;

##确定集合（IN）:用来查询属性值属于指定集合的元素
 

查询奥运村站、八达岭站、八里庄站的记录

select staid，staname from table_name where staname in ('奥运村站','八达岭站','八里庄站');

查询除了奥运村站、八达岭站、八里庄站的记录

select staid,staname from table_name where staname not in ('奥运村站','八达岭站','八里庄站');

##字符匹配（LIKE）

#一般形式为：列名 [NOT] LIKE 匹配串

#作用：查找指定列名与匹配串常量匹配的元组

#匹配串类型：匹配串可以是字符串常量，也可以含有通配符。

#通配符种类：

%（百分号）：匹配0个或者多个字符

_（下划线）：匹配一个字符

[]：匹配括号中的字符

[^]：不匹配括号中的字符

查询变电站名称第二个字是“运”的记录

select staid,staname from table_name where staname like '_运%'；


查询所有变电站名称中不是以站结尾的记录

select staid,staname from table_name where staname not like '%站';

##涉及空值的查询

# 空值是未确定的值或其值尚不知道

# 例如，刚生成的信号时还没有点号，这时点号一项的值就是空值

# 不能用=或<>，只能用 IS NULL 或 IS NOT NULL

查询变电站遥信信号中点号为空的信号 

select staid,signal,yaoxindianhao from dbStationSignal where yaoxindianhao is null;

##多重条件查询

#用逻辑运算符AND和OR组成多条件查询

查询变电站ID为272并且设备编号是2#主变的间隔信息

select spaceid,spacenum from dbspace where staid=272 and spaceNum='2#主变';

##对查询结果排序

#对查询结果进行排序的子句为：

ORDER BY<列名>[<列名>...] [ASC | DESC]

#说明：按<列名>进行升序（ASC）或者降序（DESC) 排序，如果不写，则默认为按升序排列

查询所有间隔信息，查询结果按变电站ID降序排列，同一变电站的间隔按间隔编号升序排列

select spaceid,spacenum staid from dbspace ORDER BY staid DESC,spacenum

注：最后的spacenum的后面没有写ASC，默认是升序

 

CREATE：

创建数据库

create database XX;

创建表：（前提已经 use database）

create table 表名 (
字段名1 datatype，
字段名2 datatyoe,
字段名3 datatype);

DROP：删除结构

类似于删除数据库：drop database XX，删除表：drop table XX.

删除字段也可以用drop，删除字段改变了表的结构，所以先alter方法。例如我们删除student表下的age字段，可以这样写：

alter table student drop age;

ALTER table 语句：用于在已有的表中添加、修改、删除列。

##在表中添加列

alter table table_name add column_name datatype;

##删除表中的列

alter table table_name drop column column_name;

##改变表中列的数据类型（modify）

alter table table_name modify column column_name datatype;

datatype表示要改变的数据类型

INSERT INTO：用于向数据库中的某个表中添加新的数据行。

insert into table_name [(column1,column2,column3,.....columnN)] values (value1,value2,value3,.....valueN);

这里的column代表的是要插入数据的表中的列的名称，value1指的是要插入的数据

如果要为表中的所有的列都添加值，可以不用制定列的名称，但要保证列的顺序。

insert into table_name values(value1,value2,value3,....valueN);

UPDATE：用于更新表中的已经存在的记录（修改）

update table_name set column1=value1,column2=value2,...valueN where some_column=some_value;

注意：where规定了哪条记录或者哪些记录需要更新，如果省略了where子句，所有的记录都会被更新。

DELETE:删除表中的行（数据）

delete from table_name where some_column=some_value;

当使用delete删除表的时候，只会删除表中里面的数据，表名还存在；

delete from 表名;

计算函数

COUNT（*）：统计表中元组的个数

查询变电站ID为272下间隔的个数

SELECT count(*) FROM dbspace where staid=272;

COUNT（<列名>）：统计本列非空列值个数

查询变电站id的个数

select count(id) from table_name;

SUM（<列名>）：计算列值总和（必须是数值型列）

查询id的总数

select sum(id) from table_name;

AVG（<列名>）：计算列值平均数（必须是数值型列）

查询id的平均数

select avg(id) from table_name;

MAX（<列名>）：求列值最大值

select max(id) from comment;

MIN（<列名>）：求列值最小值

select min(id) from comment;

 

对查询结果分组

作用：可以控制计算的级别（对全表还是对一组）

目的：细化计算函数的作用对象

分组语句的一般形式：

[where<条件>]

对查询结果进行统计

[group by <分组条件>]

对查询结果按id的值进行分组，所有具有相同id值的元组为一组，然后再对每一组使用count计算，求得每组的个数。

[having <组自身条件>]

HAVING用于对分组自身进行限制，它有点象WHERE子句，但它用于组而不是对单个记录。

统计 id 数量大于2个的信息

 

多表连接查询

若一个查询同时涉及两个或者两个以上的表，则称之为连接查询。

连接查询包括内连接、外连接和交叉连接等。

 

前提
建表语句：

CREATE TABLE `a_table` (
  `a_id` int(11) DEFAULT NULL,
  `a_name` varchar(10) DEFAULT NULL,
  `a_part` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8


CREATE TABLE `b_table` (
  `b_id` int(11) DEFAULT NULL,
  `b_name` varchar(10) DEFAULT NULL,
  `b_part` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

1、内连接

关键字：inner join on

定义：只显示多表之间与关联条件相匹配的列。

语句：select * from a_table a inner join b_table b on a.a_id=b.b_id;

说明：组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。

2、左连接（左外连接）

关键字：left join on 或 left outer join on 

运算结果：不管第二个表中是否有匹配的数据，结果将包含第一个表中的所有行，而第二个表中的数据只显示与关联条件相匹配的列，不匹配的列以NULL关键字填充

语句：select * from a_table a left join b_table b on a.a_id=b.b_id;

执行结果：

 

说明：

left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。

左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

3、右连接（右外连接）

关键字：right join on 或 right outer join on 

运算结果：不管第一个表是否有匹配的数据，结果将包含第二表的所有行的数据，而第一个表的数据只显示与关联条件相匹配的列。不匹配的列用NULL关键字填充

语句：select * from a_table a right outer join b_table b on a.a_id = b.b_id;

执行结果：

说明：

right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。

与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。

 

合并结果集

利用 UNION 关键字，可以给出多个select语句，并将他们的结果组合成一个结果集。合并时，两个表对应的列和数据类型都必须相同。SELECT语句之间使用 UNION 或者 UNION ALL 关键字分隔，当不使用关键字ALL时，执行的时候会删除重复的记录，结果中所有返回的行都是唯一的；使用关键字ALL  的作用是不删除重复行也不会对结果进行自动排序。

基本的语法格式如下:

 

SELECT   column，....   FROM table1
    UNION [ALL]
    SELECT  column ，...   FROM table2

前提，将数据准备好

   

例：查询 tp_admin的所有信息，查询tp_person的所有信息，使用UNION连接查询结果，SQL语句如下：

使用的是UNION关键字

结果显示，将重复的行自动删除了。

例：查询 tp_admin的所有信息，查询tp_person的所有信息，使用UNION ALL 连接查询结果，SQL语句如下：

结果显示，当使用UNION ALL 时，重复的行不会被删掉