常用sql文--mysql

よく使われるsql文をまとめ、テストバージョン:5.1
1.テーブルの作成

CREATE TABLE t_test
(
	id_ VARCHAR(32),
	title_ VARCHAR(255),
	content_ VARCHAR(2000),
	create_time_ VARCHAR(25),
	state_ VARCHAR(10)
)

 
2.文の挿入

INSERT INTO t_test  VALUES ('1', '  ','  ','2013-01-30','1')
INSERT INTO t_test (id_,title_,state_) VALUES ('2', '  2','0')

 
3.削除文

DELETE FROM t_test WHERE id_='2'

 
4.文の変更

UPDATE t_test SET title_ = '   ',content_='   ' WHERE id_='1'

 
5.クエリ文

SELECT * FROM t_test WHERE id_='1'
SELECT title_ AS   ,content_ AS    FROM t_test WHERE id_='1'
SELECT * FROM t_test LIMIT 2,3 //   2  ， 3   ，      

 
6.表増加フィールド

ALTER TABLE t_test ADD click_num_ VARCHAR(10)

 
7.タイプ変換+加算

SELECT SUM(CAST(id_ AS DECIMAL)) FROM t_test 

 
8.IFNULL(column,value)

SELECT IFNULL(SUM(CAST(click_num_ AS DECIMAL)),0) FROM t_test 

 
9.IF NOT EXISTS+NOT NULL(空ではない)+UNIQUE(一意)+PRIMARY KEY(プライマリキー)

CREATE TABLE IF NOT EXISTS t_test  
(  
    id_ VARCHAR(32) NOT NULL UNIQUE PRIMARY KEY,  
    title_ VARCHAR(255),  
    content_ VARCHAR(2000),  
    create_time_ VARCHAR(25),  
    state_ VARCHAR(10)  
)

 
10.重複除外DISTINCT

SELECT DISTINCT title_,content_ FROM t_test1

 
11.テーブル情報照会

SELECT * FROM information_schema.columns WHERE table_schema='cxy' AND table_name='t_test' AND column_name='title_';

 
12.テーブルの削除

DROP TABLE t_test

 
13.文字列接続

SELECT CONCAT(title_,':',content_) AS      FROM t_test

 
14.文字列LEFT(str,len)、RIGHT(str,len)、SUBSTRING(str,pos,len)を切り取る

SELECT LEFT(content_,2) FROM t_test
SELECT RIGHT(content_,2) FROM t_test
SELECT SUBSTRING(content_,2,1) FROM t_test

 
15.データベース情報文

SELECT VERSION(),USER(),DATABASE();

 
16.フィールド式

SELECT	id_ AS id,'  ' AS   ,(id_*1.1) AS      FROM t_test; 
#       ，  ：3.3000000000000003
SELECT	id_ AS id,'  ' AS   ,CONVERT((id_*1.1),DECIMAL(10,2)) AS      FROM t_test;

 
17.タイプ変換

#CAST(xxx  AS     ) , CONVERT(xxx,  )	SIGNED:  
SELECT CONVERT('23.1',SIGNED);	#  ：23
SELECT CONVERT('23.9',SIGNED);	#  ：23
SELECT CONVERT('23.1',DECIMAL(4,2));	#DECIMAL(2+2,2)，                  ，            
SELECT LEFT(CONVERT(100.1,CHAR),2);

SELECT CAST('23.911' AS DECIMAL(4,2));	#  ：23.91

 
18.注意:distinctは先にソートされ、大きな結果セットにはかなり時間がかかります.
 
19.テーブルの広範な定義は、永続テーブル(create table)、一時テーブル(サブクエリが返すテーブル)、仮想テーブル(create view)である.

SELECT t_temp.  ,t_temp.   FROM
(SELECT id_, title_ AS   ,content_ AS    FROM t_test) AS t_temp;	#t_temp      

表示:

#           
CREATE VIEW t_simple_test AS 
SELECT SUM(id_) FROM t_test;

SELECT * FROM t_simple_test;

DROP VIEW t_simple_test;

 
20.簡略表名

#                      ，      as
SELECT t.id_,t.title_ FROM t_test t;
SELECT t.id_,t.title_ FROM t_test AS t;

 
21.and、or、not、between and/is null

# where    and、or、not、between and/is null
SELECT * FROM t_test WHERE NOT(id_='1');
SELECT * FROM t_test WHERE title_ IS NOT NULL AND id_='2' OR id_='3';
SELECT * FROM t_test WHERE create_time_ BETWEEN '2012-12-01' AND '2013-01-31'

 
22.group byとhaving

#having             ，            
SELECT create_time_,SUM(click_num_) AS num FROM t_test 
WHERE SUM(click_num_) > 2
GROUP BY create_time_ ;	#         

SELECT create_time_,SUM(click_num_) AS num FROM t_test 
GROUP BY create_time_ HAVING num > 2;

 
23.ソート

#ORDER BY
SELECT * FROM t_test ORDER BY click_num_;	#    
SELECT * FROM t_test ORDER BY click_num_ DESC;	#    
SELECT * FROM t_test ORDER BY click_num_ ,id_;	#   

 
24.等しくない

SELECT * FROM t_test WHERE  id_ != '1';
SELECT * FROM t_test WHERE  id_ <> '1';

 
25.inとnot in

SELECT * FROM t_test WHERE title_ IN('  1','  2');
SELECT * FROM t_test WHERE id_ IN(SELECT id_ FROM t_test WHERE click_num_ >3);
SELECT * FROM t_test WHERE id_ NOT IN(SELECT id_ FROM t_test WHERE click_num_ >3);

 
26.likeとワイルドカード

SELECT * FROM t_test WHERE title_ LIKE '  _';
SELECT * FROM t_test WHERE title_ LIKE '% %';

 
27.一時取引

BEGIN;  #    
INSERT INTO t_test  VALUES ('11', '  11','  11','2013-01-30','1',1);  
SELECT * FROM t_test;  #             ，             
ROLLBACK;  #