[開発][SQL] summary

ddl
data definition language
create
drop
alter

dml
data manipulation language
select
insert
update
delete

dcl
data control language
grant アクセス権を設定する
revoke アクセス権を取り消す

※ 厳密にはSELECT文DML文と区別されQuery文という位置づけ


create table:
create table table_name (col1_name int, col2_name varchar(32), col3_name date);

not null:
create table table_name (col1_name int not null primary key, col2_name varchar(32), col3_name date);

primary key:
create table table_name (col1_name int not null primary key, col2_name varchar(32), col3_name date);
create table table_name (col1_name int not null, col2_name varchar(32), col3_name date, primary key(col1_name));

create table from a query result:
create table table2 as select col1, col2 from table1;

drop table:
drop table table_name;

alter table definitions -add column:
alter table table_name add new_col_name varchar(32) after existed_col_name;

alter table definitions - drop column:
alter table table_name drop deleting_col_name;

alter table definitions -change column def:
alter table table_name change target_col_name new_col_name text not null;

alter table definitions -add primary key:
alter table table_name add primary key(col1_name, col2_name);

alter table definitions -rename table:
alter table old_table_name rename to new_table_name;

select -where:
select col1_name, col2_name from table_name where col1_name = 'hoge';

select -where -not:
select * from table_name where not (col1_name = 'hoge' or col2_name = 1);

select -where -between:
select * from table_name where col1_name between 8 and 10;

select -where -in:
select * from table_name where col1_name in (8, 10, 12);

select -where -like:
select * from table_name where col1_name like 'word%';
select * from table_name where col1_name like '%word';
select * from table_name where col1_name like '%word%';

select -order by:
select * from table_name order by col1_name, col2_name;

select -count:
select count(col1_name) from table_name;
select count(*) from table_name where col1_name = 'piyo';
※ the null cell wont be counted.

select -limit:
select * from table_name order by col1_name limit [offset] limit_count;
※ オフセット=取得済み件数
. オフセットが10であれば 10件が取得済みと見なし 11件目から検索する

select -is null:
select * from table_name order by col1_name is null;

join

function -sum:
select sum(col1_name) from table_name;

function -max/min:
select max(col1_name), min(col2_name) from table_name;

function -avg:
select avg(col1_name) from table_name;

distinct:
select distinct(col_name) from table_name;
※ it returns null if there are no matched records.

insert:
insert into table_name (col1_name, col2_name) values (val1, val2);
insert into table_name (col1_name, col2_name) values (val1, val2), (val3, val4);

update:
update table_name set col1_name = val1, col2_name = val2 where id = 1;

delete:
delete from table_name where id = 1


transaction:
begin;
commit;
rollback;


useful commands:
to use the created databese; >use database_name
to know what databases exist; >show databases
to know all tables in the current database; >show tables
to know the columns exist in the table; >show fields from table_name
to read from a sql file: >\. fileName.sql
to refer the user names, database names and connection; >\s
to disconnect from the database; >exit
to show all commands; >?


data types:
文字型
char (バイト数) 固定長文字列 255バイト
varchar (バイト数) 可変長文字列 255バイト
text 可変長文字列 65535バイト
mediumtext 可変長文字列 16777215バイト
longtext 可変長文字列 4294967295バイト
数字型
int 整数 -2^31 to 2~31-1
float 実数 32ビットの浮動小数点数
double 実数 64ビットの浮動小数点数
日付型
time 時刻 -838:59:59 to 838:59:59
date 日付 1000-01-01 to 9999-12-31
datetime 日時 1000-01-01 00:00:00 to 9999-12-31 23:59:59
※RDBMS次第で異なる

日付と時刻関数
現在日時の取得:select now();
現在時刻の取得:select curtime();
現在日付の取得:select curdate();

集合関数
行数の取得:select count(*) from table_name;
最小値の取得:select min(col_name) from table_name;
最大の取得:select max(col_name) from table_name;

tag : ddl dml dcl SQL

2010-05-26 01:15 : 開発 : コメント : 0 : トラックバック : 0 :
コメントの投稿
非公開コメント

« next  ホーム  prev »

search

ad



counter


tag cloud

category cloud