--创建数据库
CREATE DATABASE Nuctech
--删除数据库
DROP DATABASE Nuctech
--创建表
CREATE TABLE Zhaocl(name VARCHAR(50),gender VARCHAR(50),age INT)
--删除表
DELETE dbo.Zhaocl --删除表数据,不删除表结构,不释放空间
TRUNCATE TABLE dbo.Zhaocl --删除表数据,不删除表结构,释放空间
DROP TABLE dbo.Zhaocl --删除表所有,删除表结构,释放空间
--增加字段(列)
ALTER TABLE dbo.Zhaocl ADD addr VARCHAR(50)
--删除字段(列)
ALTER TABLE dbo.Zhaocl DROP COLUMN age
--插入数据
INSERT dbo.Zhaocl
( NAME, gender, addr )
VALUES ( 'zhaocl', -- NAME - varchar(50)
'men', -- gender - varchar(50)
'beijing' -- addr - varchar(50)
)
--删除数据
DELETE FROM dbo.Zhaocl WHERE NAME = 'zhaocl' --(注意where条件,否则就删除所有数据了)
--更新数据
UPDATE dbo.Zhaocl SET addr = 'zhengzhou' WHERE NAME = 'zhaocl' --(注意where条件,否则更新所有字段值)
--查询表所有数据
SELECT * FROM dbo.Websites
--查询某列数据
SELECT name FROM dbo.Websites
--返回唯一不同的值
SELECT DISTINCT name FROM dbo.Websites
--升序排列(默认ASC)
SELECT * FROM dbo.Websites ORDER BY alexa
--降序排列
SELECT * FROM dbo.Websites ORDER BY alexa DESC
--获取第一条数据
SELECT TOP 1 NAME,url FROM dbo.Websites
--获取最后一条数据
SELECT TOP 1 name FROM dbo.Websites ORDER BY name DESC
--获取某列最大值
SELECT MAX(alexa) AS maxAlexa FROM dbo.Websites
--获取某列最小值
SELECT MIN(alexa) AS minAlexa FROM dbo.Websites
--某列求和(varchar类型的不可)
SELECT SUM(name) AS sumAlexa FROM dbo.Websites
--字母转大写
SELECT UPPER(country) FROM dbo.Websites
--字母转小写
UPDATE dbo.Websites SET country = LOWER(country)
--字符串截取
SELECT SUBSTRING(country,1,3) FROM dbo.Websites
--获取字符串长度
SELECT LEN(country) AS lenCountry FROM dbo.Websites WHERE alexa=13
--四舍五入
SELECT ROUND(1.56,1)
--第三个参数非0时,表示舍弃位数,不再进行四舍五入
SELECT ROUND(2.56,1,1)
--获取当前时间
SELECT GETDATE()
--获取当前年月日
SELECT DATENAME(YEAR,GETDATE())
SELECT DATENAME(MONTH,GETDATE())
SELECT DATENAME(DAY,GETDATE())
SELECT YEAR(GETDATE())
--FORMAT格式化
SELECT FORMAT(GETDATE(),'yyyy-MM-dd')
--对结果集分组,并对组进行操作(SUM)
SELECT country,SUM(alexa) AS sumAlexa FROM dbo.Websites GROUP BY country
--where无法和聚合函数一起使用,所以使用HAVING跟条件
SELECT country,AVG(alexa) AS avgAlexa FROM dbo.Websites GROUP BY country HAVING country = 'china'
--LIKE
SELECT country FROM dbo.Websites WHERE country LIKE '%a%' --包含a(SQL不区分大小写)
SELECT country FROM dbo.Websites WHERE country LIKE '%a' --a结尾
SELECT country FROM dbo.Websites WHERE country LIKE 'a%' --a开头
SELECT country FROM dbo.Websites WHERE country LIKE '_a_' --三位并且中间是a
SELECT country FROM dbo.Websites WHERE country LIKE '_a' --两位并且末尾是a
SELECT country FROM dbo.Websites WHERE country LIKE 'a_' --两位并且a开头
--返回多个条件中符合的条目
SELECT * FROM dbo.Websites WHERE alexa IN ('1','0','100')
--返回范围内的数据
SELECT * FROM dbo.Websites WHERE alexa BETWEEN 1 AND 26 --[]两边都包括
--列的别名(单个以及多个)
SELECT name AS wName,CONCAT(url,'-',alexa,'-',country) AS allName FROM dbo.Websites
--表的别名
SELECT * FROM dbo.Websites a,dbo.Zhaocl b WHERE a.alexa = b.gender