Sql语句面试大全总结

1建表

CREATE TABLE IF NOT EXISTS Test(
	id INT AUTO_INCREMENT,                         //自增长
	username VARCHAR(20) NOT NULL,                 //非空
	age TINYINT UNSIGNED NOT NULL DEFAULT 18,      //默认值
	sex ENUM (‘男’，‘女’，‘保密’) NOT NULL DEFAULT ‘男’,
	card CHAR(18) UNIQUE,                            //唯一
    primary key(id)				   //主键
) engine=INNODB AUTO_INCREMENT =316001;            //引擎

2 对表结构修改

ALTER TABLE Test1 RENAME TO Test2;                  //更改名字
ALTER TABLE Test1 ADD card CHAR(18);                 //添加字段
ALTER TABLE Test1 DROP card;                         //删除字段
ALTER TABLE Test1 MODIFY card CHAR(18);               //修改字段
ALTER TABLE Test1 ADD PRIMARY KEY(id,card);           //添加主键和删除主键

3 对表数据修改

INSERT Test1(username,password) VALUES(‘A’,’AAA’);	                           //插入记录
UPDATE Test1 SET age =5;                                                      //更新记录
SELECT Test1. username AS ‘用户名’ FROM TEST1 AS test1;                        //起名字
SELECT * FROM Test1 WHERE age <=> NULL;                                        //检测NULL
SELECT * FROM Test1 WHERE age BETWEEN 3 AND 10;                                //范围查询
SELECT * FROM Test1 GROUP BY username;                                         //分组查询
SELECT *  FROM Test1 WHERE username NOT LIKE  '刘%';                           //模糊查询
SELECT * FROM Test1 LIMIT 3;                                                    //显示前三条
SELECT * FROM Test1 LIMIT 0，1;                                                 //显示第一条
SELECT * FROM Test1 ORDER BY username DESC, password ASC;                       //升序降序
SELECT * FROM Test1 INNER JOIN Test2 ON Test1.id = Test2.id                     //内外连接 
SELECT col, COUNT(*) AS num FROM Test1 WHERE col > 2GROUP BY col HAVING COUNT(*) >= 2;  //HAVING

Group By 和 Having, Where ,Order by这些关键字是按照如下顺序进行执行的：Where, Group By, Having, Order by。

SELECT username FROM Test1 UNION SELECT username FROM Test2        //联合查询
SELECT * FROM Test1 WHERE EXISTS (SELECT m FROM Test2 WHERE id =100) // EXISTS
SELECT * FROM Test1 WHERE username in (SELECT username FROM Test1 WHERE username ='刘' );

4存储过程

delimiter //
create procedure myprocedure( )
    begin
        DELETE FROM Test1
        WHERE Test1.id = 2;
end //
delimiter ;

5 truncate delete drop 区别