基础sql语句

基础sql语句

CREATE DATABASE databasetest;/*创建数据库*/
DROP DATABASE databasetest;/*删除数据库*/
USE databasetest;/*使用数据库*/`test`
CREATE TABLE t_test (id INT(11) PRIMARY KEY AUTO_INCREMENT, testname VARCHAR(50) NOT NULL, testvalue VARCHAR(50));/*创建表*/
CREATE TABLE t_new LIKE test1/*用旧表创建新表，只创建表结构，不创建表数据*/
CREATE TABLE t_new1 AS SELECT id,NAME FROM test1 /*使用旧表创建新表，可以在旧表中选取字段，创建表结构与表数据*/
DROP TABLE t_new1/*该表在没有被其他表或视图引用下，直接删除，如果引用，不可以删除*/
ALTER TABLE t_test ADD COLUMN testno VARCHAR(20)/*添加列，列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。*/
ALTER TABLE t_test ADD PRIMARY KEY(id)/*添加主键*/
ALTER TABLE t_test DROP PRIMARY KEY(id)/*删除主键*/
CREATE UNIQUE INDEX unique_no ON t_test (testno)/*创建索引*/
ALTER TABLE t_test ADD KEY key_testvalue (testvalue)/*为字段创建key索引*/
DROP INDEX key_testvalue ON t_test/*删除索引*/
CREATE VIEW view_test AS (SELECT * FROM test1) /*创建视图*/
DROP VIEW view_test/*删除视图*/
SELECT SUM(id) AS sumvalue FROM test1;/*使用sum函数*/
SELECT AVG(id) AS sumvalue FROM test1;/*使用avg函数*/
SELECT COUNT(*) FROM test1;/*使用count函数*/
SELECT MAX(id) FROM test1;/*使用max函数*/
SELECT MIN(id) FROM test1;/*使用min函数*/
SELECT E_Name FROM employees_china 
UNION 
(SELECT E_Name FROM employees_usa)/*使用union，自动去重*/
SELECT E_Name FROM employees_china 
UNION ALL
(SELECT E_Name FROM employees_usa)/*使用union all，不会自动去重*/

(SELECT E_Name FROM employees_china)
except
(SELECT E_Name FROM employees_usa)/*使用exceptd，根据左查询，返回不重复的字段 mysql中不具备这个字段*/

(SELECT E_Name FROM employees_china)
intersect
(SELECT E_Name FROM employees_usa)/*使用intersect，根据左查询，返回重复的字段 mysql中不具备这个字段*/

SELECT  E_Name,  E_ID, COUNT(*)
    FROM (SELECT E_Name, E_ID
        FROM employees_china
        
        UNION ALL
        
        SELECT E_Name, E_ID
        FROM employees_usa
        ) a
    GROUP BY E_ID, E_Name
    HAVING COUNT(*) > 1 /*mysql 查询交集*/
    
SELECT china.*,usa.* FROM employees_china china LEFT JOIN employees_usa usa ON china.E_ID = usa.E_ID /*左连接*/
SELECT china.*,usa.* FROM employees_china china LEFT JOIN 
employees_usa usa ON china.E_ID = usa.E_ID WHERE usa.E_ID IS NULL/*左连接不存在的*/
SELECT china.*,usa.* FROM employees_china china RIGHT JOIN employees_usa usa ON china.E_ID = usa.E_ID WHERE china.E_ID IS NULL/*右连接*/

SELECT china.*,usa.* FROM employees_china china FULL JOIN employees_usa usa ON china.E_ID = usa.E_ID /*全连接 mysql不支持，oracle支持*/

sp_renamedb 'databasetest', 'databasetest1' /*更改数据库的名称 sqlserver支持，mysql不支持*/

SELECT * FROM employees_china WHERE E_ID BETWEEN 1 AND 3 /*beween and*/
SELECT * FROM employees_china WHERE E_ID NOT BETWEEN 1 AND 3 /*not beween and*/

SELECT * FROM employees_china WHERE E_ID IN (1,5,4)/* in*/
SELECT * FROM employees_china WHERE E_ID NOT IN (1,5,4)/* not in*/

DELETE FROM t_test test WHERE EXISTS (SELECT E_ID FROM employees_china china WHERE test.id = china.`E_ID`)/*删除存在的数据*/
DELETE FROM t_test test WHERE NOT EXISTS (SELECT E_ID FROM employees_china china WHERE test.id = china.`E_ID`)/*删除不存在的数据*/

SELECT top 10 * FROM employees_china /*top针对sqlserver*/

SELECT * FROM employees_china LIMIT 0, 10 /*limit针对mysql*/
select rownum r,e.* from so_delivery e where rownum <=5;
select * from (select rownum r,e.* from so_delivery e where rownum <=5) t where r>0;/*rownum针对oracle分页*/
show tables;/*显示所有的数据表-mysql*/
 select name from sysobjects where type='U';/*显示所有的数据表-sqlserver*/
 show columns from employees_china/*查看表结构-mysql*/
 select name from syscolumns where id=object_id('TableName')/*sqlserver查看表结构*/
 /*已知数据按照另外一种方式进行分组*/
 SELECT CASE country  
	WHEN '中国' THEN '亚洲' 
	WHEN '美国' THEN '北美'  
	WHEN '日本' THEN '亚洲' 
	WHEN '英国' THEN '欧洲' 
	WHEN '法国' THEN '欧洲' 
	WHEN '加拿大' THEN '北美' ELSE '' END AS a, SUM(population)
 FROM test_a GROUP BY
 CASE country  WHEN '中国' THEN '亚洲'
	WHEN '美国' THEN '北美'
	WHEN '日本' THEN '亚洲'
	WHEN '英国' THEN '欧洲'
	WHEN '法国' THEN '欧洲'
	WHEN '加拿大' THEN '北美'
	ELSE '' END 
select
  case
    when salary >= 1000 and salary < 2000 then '1000-2000'
    when salary >= 2000 and salary < 3000 then '2000-3000'
    when salary >= 3000 and salary < 4000 then '3000-4000'
    else '其他' end as '薪资等级',
    count(*) as '数量'
 from test_b group by 
 CASE
    WHEN salary >= 1000 AND salary < 2000 THEN '1000-2000'
    WHEN salary >= 2000 AND salary < 3000 THEN '2000-3000'
    WHEN salary >= 3000 AND salary < 4000 THEN '3000-4000'
    ELSE '其他' END
/*竖表转横表*/
SELECT country,
  CASE sex WHEN 1 THEN population ELSE 0 END AS '男性',
  CASE sex WHEN 2 THEN population ELSE 0 END AS '女性',
  CASE sex WHEN 3 THEN population ELSE 0 END AS '无性',
  CASE sex WHEN 4 THEN population ELSE 0 END AS '双性'
FROM test_c 
GROUP BY country

SELECT country,
  MAX(CASE sex WHEN 1 THEN population ELSE 0 END) AS '男性',
  MAX(CASE sex WHEN 2 THEN population ELSE 0 END) AS '女性',
  MAX(CASE sex WHEN 3 THEN population ELSE 0 END) AS '无性',
  MAX(CASE sex WHEN 4 THEN population ELSE 0 END) AS '双性'
FROM test_c 
GROUP BY country
/* case when then else end 结构*/
truncate table t_test/*初始化数据表，只删除数据，不删除表结构，原理是现将表删除，然后再新建*/
SELECT @@global.sql_mode