MySQL基础篇：常用的SQL语句

你的名字 2022-06-18 07:26 199阅读 0赞

### **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][http_blog.csdn.net_wenteryan]

[http_blog.csdn.net_wenteryan]: http://blog.csdn.net/wenteryan