MySQL基础篇：常用的SQL语句

MySQL基础篇：常用的SQL语句

#1.创建表
CREATE TABLE t_test (
    id VARCHAR(11),
    title VARCHAR(100),
    content VARCHAR(255),
    state VARCHAR(11)

)

#2.插入语句
INSERT INTO t_test VALUES('1','标题1','内容1','1')
INSERT INTO  t_test (id,title,state) VALUES('2','标题2','2')

#3.删除语句
DELETE FROM t_test WHERE id='1'

#4修改语句
UPDATE t_test SET title='标题2改', content='内容2' WHERE id='2'

#5查询语句
SELECT * FROM t_test WHERE id='2' 
SELECT id AS ID, title AS 标题, content AS 内容, state AS 状态 FROM t_test WHERE id='2'
SELECT * FROM t_test LIMIT 2,10

#6表增加字段
ALTER TABLE t_test ADD click VARCHAR(11)

#7类型转换+求和
SELECT SUM(CAST(id AS DECIMAL)) FROM t_test
SELECT SUM(click) FROM t_test

SELECT CAST('12' AS INT)

#8判空 IFNULL(expr1,expr2)          
#如果expr1不是NULL，IFNULL()返回expr1，否则它返回expr2。
SELECT IFNULL(SUM(CAST(click AS DECIMAL)),0) FROM t_test

#9IF NOT EXISTS+NOT NULL（不为空）+UNIQUE（唯一）+PRIMARY KEY（主键）
CREATE TABLE IF NOT EXISTS t_test1(
    id VARCHAR(11) NOT NULL UNIQUE PRIMARY KEY,
    title VARCHAR(100),
    content VARCHAR(255),
    state VARCHAR(11)
)

#10去掉重复的数据 distinct

SELECT DISTINCT title,content FROM t_test

#11表数据查询
SELECT * FROM information_schema.columns WHERE table_schema='db_test' AND table_name='t_test' AND column_name='title';

#12删除表
DROP TABLE t_test1

#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  

#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;  

#21and、or、not、between and/is null
SELECT * FROM t_test WHERE NOT(id=2)
SELECT * FROM t_test WHERE title IS NOT NULL AND id='2' OR id='3'
SELECT * FROM t_test WHERE id BETWEEN 3 AND 5

#22group by 和having
#having 能对分组后的数据进行筛选，尤其在使用聚集函数的时候  
SELECT * FROM t_test WHERE click>0 GROUP BY state
SELECT state,SUM(click) AS num FROM t_test GROUP BY state HAVING num > 2 

#23排序
SELECT * FROM t_test ORDER BY click
SELECT * FROM t_test ORDER BY click DESC
SELECT * FROM t_test ORDER BY click,id

#24不等于
SELECT * FROM t_test WHERE id !=1
SELECT * FROM t_test WHERE id <>'3'

#25in 和not in
SELECT * FROM t_test WHERE title IN('标题1','标题2','标题3') 
SELECT * FROM t_test WHERE id IN(SELECT id FROM t_test WHERE click >3)  
SELECT * FROM t_test WHERE id NOT IN(SELECT id FROM t_test WHERE click >3)

#26lick和通配符
SELECT * FROM t_test WHERE title LIKE '标题'  
SELECT * FROM t_test WHERE title LIKE '%题%'

#27临时事务
BEGIN;  #开始事务  
INSERT INTO t_test  VALUES ('11', '标题11','内容11','1','11');    
SELECT * FROM t_test;  #这里的查询是有上面的记录的，但是实际库中并没有这个记录  
ROLLBACK;  #回滚 

本篇为wenteryan原创，转载请注明出处：http://blog.csdn.net/wenteryan