SQL基础
来源:互联网 发布:泰拉瑞亚数据统计器 编辑:程序博客网 时间:2024/05/16 15:28
SQL基础
select food from diet where species='orange';
一句话就能将表diet中的’orange’类的food全部选取出来,也是很神奇的!
查询操作的实现
那么SQL的查询(query)操作是如何实现的呢?
下图给出了很好的解释:
自己写的SQL语句通过网络访问数据库服务器,服务器根据这一指令进行直接查询,结果形成一个表作为返回值。也就是说,SQL语句也只不过是间接查询的途径罢了。
唯一性(uniqueness)
表中的每一个条目都是唯一的,那么如何区分呢?数据库中可以使用id进行区分不同的条目。如果每个条目是一个人的话,那么id就是人的身份证,它是区分人是否不同的权威标准。
Join Table
表animals
表diet
select animals.name,animals.species,diet.food from animals join diet on animals.species=diet.species where food ='fish';
通常由on引导join的条件,也就是将两个表animals和diet关联起来的语句,这里要求的是animals.species 与diet.species相同。这条语句的意思就是查询吃鱼的动物的名字,种类,还有习惯的食物。
SQL中的数据类型
比较运算
select .... from table where statements;
statements表示的是过滤条件(row restriction)
这时写这些条件语句的时候边涉及到了比较运算。
很简单跟编程语言一样。
常用的keywords
max 函数
select max(name) from animals;
max函数返回最大值,对字符串而言,大小的比较是首先比较头字母的大小。‘a’->’z’从大到小。如果首字母相同,依次比较下一位字母,直到比较出大小,”空字符最小。
select * from animals limit 10;
limit value表示显示前value个条目。
select name from animals where species='orangutan' order by birthdate;
order by col 表示依据col列的值进行升序排序。
select name from animals where species='orangutan' order by birthdate desc;
order by col desc 表示按col列的值降序排列。
select name, birthdate from animals order by name limit 10 offset 20;
offset value 表示偏移值,这个sql语句的含义就是:按name排序得到第位20-30个含有name,birthdate的条目。
select species,min(birthdate) from animals gruop by species;
group by col 表示按col列的值进行聚类,所有col列的值相同的条目算一类。
本sql语句的含义是:得到animals的种类以及每个种类中最小的生日值(年龄最大的)。
select name,count(*) as num from animals gruop by name order by num desc limit 5;
count()函数统计数量
统计animals中的重名动物,并按重名的数量降序排列,得到重名数最多的前五个名字,及其数量。
插入新的行到表中
insert into tablename values(42,’staff’);
if the new values aren’t in the same order as the table’s columns:
insert into tablename(col2,col1) values(‘staff’,42);
比如在animals中插入一行2014-11-11日出生的叫wibble的oppssum类动物:
insert into animals values(‘wibble’,’oppssum’,’2014-11-11’);
Join the Table (simple join)
join 操作往往能被where替代:
比如:
假设T,S是两个表
1. select T.thing , S.stuff from T join S on T.target = S.match;
T.target 与 S.match 就像是桥梁将表T与表S连接起来了。
2. select T.thing , S.stuff from T, S where T.target = S.match;
语句1与语句2可以达到同样的效果
再举个例子:
select name from animals,diet where animals.species = diet.species and diet.food=’fish’;
select animals.name from animals join diet on animals.species = diet.species where diet.food=’fish’;
表示得到吃鱼的动物的名字。
练习时间
question1:which species does the zoo have only one of?
select species,count(*) as num from animals group by species where num=1;
好吧,上面的语句是显而易见的答案,但是如果你这么认为的话,你就错了!
因为 where引导的过滤条件在count开始之前就进行过滤了,但是过滤的时候还没有num产生,这时找不到num必然报错。
用英文说一遍就是:the value of num comes from count and group by.
But where always runs before aggregations.
通过对上面错误答案的微小改动就能得到question1的正确答案。
答案是:
select species,count(*) as num from animals group by species having num=1;
那么having与where的区别是?
where is a restriction on the source tables.
having is a restriction on the result after aggregation.
也就是where引导的条件表达式中的变量必须是源表(source table)中的。
question2:Find the one food the is eaten by only one animal.
answer1:
select food,count(*) as num from animals join diet on animals.species = diet group by food having num=1;
answer2:
select food,count(animals.name) as num from diet,animals where nimals.species = diet group by food having num=1;
Normalized Design
in a normalized database, the relationships among the tables match the relationships that are really there among the data.
Greate Table and Types
创建 table 的方法是:
create table tablename(
column1 type [constrains],
column2 type [constains],
…,
…,
[row constrains]);
some systems support abbreviations for long type names.
for example,
timestamptz(PostgreSQL only) = timestamp with time zone (SQL standard type name)
In generally, user facing code doesn’t usually create new tables.
日常生活中,我们一般只是在已有数据库的情况下,对数据库的表进行增删改查操作,并不会直接接触到创建数据库中的表的操作。
Creating and Dropping
Create database name[options];
drop database name[options];
drop table name[options];
在psql中具体进行的就是如下操作:
1.打开terminal:$psql
2.psql=> create database fishies;
3.psql=> \c fishies
4.psql=> create table fish(name text,id serial);
5.psql=> insert into fish values(‘papapa’,1);
Declaring Primary Keys
primary key: a column or columns that uniquely identify what each row in a table is about.
for example,
psql=> create table students(
id serial primary key,
name text,
birthdate date);
有时,也有两个key同时组成primary key.
比如,
psql=> create table post_places(
postal_code text,
country text,
name text,
primary key (post_code,country));
一旦primarykey确定了,当我们输入数据时,如果我们输入的数据中有两个item的primary key 相同,数据库会throws error。
Declaring Relationships
psql=> create table sales(
sku text references products,
sale_date date,
count integer);
products 是一个表
psql=> create table sales(
sku text references products(sku),
sale_date date,
count integer );
products(sku)是一个列
references provides referential integrity-columns that are supporsed to refer to each other are guaranteed to do so.
Foreign Keys
A foreign key is a column or set of columns in one table that uniquely identifies rows in another table.
用法举例
create table students(id serial primary key, name text);
create table courses(id text primary key,name text);
create table grades(student integer references student (id),course text references courses (id), garde text);
下面三个表中哪些列是primary key,哪些列是foreign key?
表users
表posts:
表votes:
表1中,username是作为primary key的。
表2中,id 作为primary key,author作为foreign key 并参考表1的primary key: username
表3中,post_id 作为foreign key并参考表2的primary key:id;voter作为foreign key参考表1的primary key: username.
self joins
table residences
create table residences(id integer references students, building text references buildings(naem), room text);
question:find roommate.
select a.id,b.id from residences as a, residences as b where a.building=b.building and a.room = b.room and a.id<b.id order by a.building, a.room;
left join
**A regular(inner) join returns only those rows where the two tables have entries matching the join condition.
A left join returns all those rows plus the row where the left table has an entry but the right table does not **
create table programs(name text,filename text);create table bugs(filename text,description text,id serial primary key);
question: count the number of bugs of each program.
select programs.name,count(bugs.id) as num from programs left join bugs on programs.name = bugs.filename group by programs.name order by num;
Subqueries
select avg(bigscore) from (select max(score) as bigscore from mooseball group by team ) as maxer;
the principle is one query, not two!
select name from players,(select avg(weight) as av from players) where weight < av;
or
select name,weight from players,(select avg(weight) as av from players) as subq where weight < av;
Views
A view is a select query stored in the database in a way that lets you use it like a table.
create view viewname as select ......;
create view course_size as select course_id,count(*) as num from enrollment group by course_id;
use view
select * from viewname where ....;
delte view
drop view view_name;
- SQL基础
- SQL基础
- SQL基础
- SQL 基础
- sql:基础
- SQL基础
- SQL基础
- SQL基础
- SQL基础
- SQL基础
- SQL 基础
- sql基础
- SQL基础
- sql基础
- SQL基础
- SQL基础
- sql 基础
- sql基础
- 虚拟机中centos安装JDK
- 取出System的Properties内容的方法
- LaTex学习(一)
- 电子商务平台支付接口开发原理及流程
- Hadoop YARN
- SQL基础
- flash读取外部xml配置文件基本方法
- Linux下TCP/IP通信
- 易中天经典语录
- iOS开发之打包上传到App Store——(三)申请证书并且发布应用程序到App Store
- 归档文件任务计划
- 红杉资本和IDG
- 动态规划_最大m子段和
- bash: ./a.sh: /bin/bash^M: bad interpreter: No such file or directory的解决方法------dos--->unix