随笔分类 -  SQL

有关T-SQL的10个好习惯【转】
摘要：原文：http://www.cnblogs.com/CareySon/archive/2012/10/11/2719598.html1.在生产环境中不要出现Select * 这一点我想大家已经是比较熟知了，这样的错误相信会犯的人不会太多。但我这里还是要说一下。 不使用Select *的原因主要不是坊间所流传的将*解析成具体的列需要产生消耗，这点消耗在我看来完全可以忽略不计。更主要的原因来自以下两点: 扩展方面的问题 造成额外的书签查找或是由查找变为扫描 扩展方面的问题是当表中添加一个列时，Select *会把这一列也囊括进去，从而造成上面的第二种问题。 而额外的IO这点显而易见，当查找不需要的 阅读全文
posted @ 2012-10-11 12:22 Leo Forest 阅读(205) 评论(0) 推荐(0)
PostgreSQL基础命令【转】
摘要：原文：http://www.chnlanker.com/postgresql/556.html（1）用户实用程序：createdb 创建一个新的PostgreSQL的数据库（和SQL语句：CREATE DATABASE 相同）createuser 创建一个新的PostgreSQL的用户（和SQL语句：CREATE USER 相同）dropdb 删除数据库dropuser 删除用户pg_dump 将PostgreSQL数据库导出到一个脚本文件pg_dumpall 将所有的PostgreSQL数据库导出到一个脚本文件pg_restore 从一个由pg_dump或pg_dumpall程序导出的脚本文 阅读全文
posted @ 2012-07-08 20:10 Leo Forest 阅读(535) 评论(0) 推荐(0)
Arch 安装和简单使用Postgresql
摘要：$ sudo pacman -S postgresql$ sudo /etc/rc.d/postgresql start$ groups postgressu rootsu - postgres详细解释看：https://wiki.archlinux.org/index.php/PostgreSQL_%28%E7%AE%80%E4%BD%93%E4%B8%AD%E6%96%87%29给postgresql射密码$ su root# passwd postgres开始使用# su postgres建立数据库createdb test;进入数据库psql test;建一个叫cats的表：test. 阅读全文
posted @ 2012-07-02 23:25 Leo Forest 阅读(706) 评论(0) 推荐(0)
c连接mysql编译报错 /usr/bin/ld: cannot find -lmysqlclient
摘要：CentOS 64位用c连接mysql 编译参数：-lmysqlclient 出错出错信息/usr/bin/ld: cannot find -lmysqlclient原因：libmysqlclient.so不在/usr/bin目录下，而是在/usr/lib64/mysql目录下解决：建一个软连接或者复制到/usr/bin目录下#cp /usr/lib64/mysql/* /usr/lib/ 阅读全文
posted @ 2012-07-02 10:30 Leo Forest 阅读(1167) 评论(0) 推荐(0)
mysql c api
摘要：http://zetcode.com/tutorials/mysqlcapitutorial/ 阅读全文
posted @ 2012-07-02 10:22 Leo Forest 阅读(148) 评论(0) 推荐(0)
mysql常用命令【转】
摘要：http://www.cnblogs.com/hateislove214/archive/2010/11/05/1869889.html1.导出整个数据库mysqldump -u 用户名 -p --default-character-set=latin1 数据库名 > 导出的文件名(数据库默认编码是latin1)mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql2.导出一个表mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名mysqldump -u wcnc -p smgp_apps_wcnc users> 阅读全文
posted @ 2012-07-01 21:22 Leo Forest 阅读(212) 评论(0) 推荐(0)
C连接mysql【转】
摘要：原文：http://www.cnblogs.com/wangkangluo1/archive/2012/07/01/2571800.htmlmysql 使用的是xampp 需要指定sock源码：main.c#if defined(_WIN32) || defined(_WIN64) //为了支持windows平台上的编译#include <windows.h>#endif#include <stdio.h>#include <stdlib.h>#include <mysql/mysql.h> //我的机器上该文件在/usr/local/inclu 阅读全文
posted @ 2012-07-01 21:19 Leo Forest 阅读(274) 评论(0) 推荐(0)
SqlZoo习题答案：Dealing with NULL.
摘要：习题地址：http://sqlzoo.net/4.htm表结构： msp(name, party, constituency) party(code, name, leader) msp.party = party.code1a.One MSP was kicked out of the Labour party and has no party. Find him.select name from msp where party is null1b.Obtain a list of all parties and leaders.select name,leader f... 阅读全文
posted @ 2012-06-03 15:47 Leo Forest 阅读(628) 评论(0) 推荐(0)
SqlZoo.net习题答案：NULL, INNER JOIN, LEFT JOIN, RIGHT JOIN
摘要：习题地址：http://sqlzoo.net/4a.htm表结构： teacher(id, dept, name, phone, mobile) dept(id, name) teacher.dept = dept.id1a.List the teachers who have NULL for their department.select name from teacher where dept is null1b.Note the INNER JOIN misses the teacher with no department and the department ... 阅读全文
posted @ 2012-06-02 13:10 Leo Forest 阅读(2182) 评论(0) 推荐(0)
SqlZoo.net习题答案：The Join 【Movie】
摘要：习题地址：http://sqlzoo.net/3.htm表结构： movie(id, title, yr,director) actor(id, name) casting(movieid,actorid, ord)1b.Give year of 'Citizen Kane'.select yr from moviewhere title = 'Citizen Kane'1c.List all of the Star Trek movies, include theidtitleandyr. (All of these movies include the wo 阅读全文
posted @ 2012-06-02 11:46 Leo Forest 阅读(2402) 评论(0) 推荐(0)
SqlZoo.net习题答案：How to do joins.【Table Tennis】
摘要：习题地址：http://sqlzoo.net/3b.htm表结构： ttms(games,color, who,country) country(id, name) ttms.county = county.id1b.Show the who and the color of the medal for the medal winners from 'Sweden'.select who, colorfrom ttms join countryon (ttms.country = country.id)where country.name = 'Sweden'1 阅读全文
posted @ 2012-06-02 00:40 Leo Forest 阅读(488) 评论(0) 推荐(0)
SqlZoo.net习题答案：Using nested SELECT.
摘要：习题地址：http://sqlzoo.net/1a.htm表结构： bbc(name, region, area, population, gdp)1a.List each country name where the population is larger than 'Russia'.select name from bbc where population > (select population from bbc where name = 'Russia')1b.List thenameandregionof countries in the re 阅读全文
posted @ 2012-06-01 23:54 Leo Forest 阅读(762) 评论(0) 推荐(0)
SqlZoo.net习题答案：Using the SELECT statement.【nobel】
摘要：习题地址：http://sqlzoo.net/1b.htm表结构： nobel(yr, subject, winner)1a.Change the query shown so that it displays Nobel prizes for 1950.select *from nobel where yr = 19501b.Show who won the 1962 prize for Literature.select winner from nobel where yr = 1962 and subject = 'Literature'2a.Show the year 阅读全文
posted @ 2012-06-01 21:33 Leo Forest 阅读(825) 评论(0) 推荐(0)
SqlZoo.net习题答案：How to do joins.【Album】
摘要：习题地址：http://sqlzoo.net/3a.htm表结构： album(asin, title, artist, price, release, label, rank) track(album, dsk, posn, song)1b.Whichartistrecorded thesong'Exodus'?select album.artist from album join trackon (album.asin = track.album)where track.song = 'Exodus'1c.Show thesongfor eachtracko 阅读全文
posted @ 2012-06-01 18:22 Leo Forest 阅读(1495) 评论(0) 推荐(0)
SqlZoo.net习题答案：Using SUM, COUNT, MAX, AVG, DISTINCT and ORDER BY.
摘要：习题地址：http://sqlzoo.net/2.htm表结构：bbc(name, region, area, population, gdp)1b.List all the regions - just once each.select distinct region from bbc1c.Give the total GDP of Africaselect sum(GDP) from bbc where region = 'Africa'1d.How many countries have an area of at least 1000000select count(na 阅读全文
posted @ 2012-06-01 16:59 Leo Forest 阅读(594) 评论(0) 推荐(0)
SqlZoo.net习题答案：Using the SELECT statement.【bbc】
摘要：习题地址：http://sqlzoo.net/1.htm表结构：bbc(name, region, area, population, gdp)2a.Show the name for the countries that have a population of at least 200 million. (200 million is 200000000, there are eight zeros)select name from bbc where population >= 2000000002b.Give the name and the per capitaGDPfor t 阅读全文
posted @ 2012-06-01 16:45 Leo Forest 阅读(406) 评论(0) 推荐(0)