USE master
GO
--判断当前数据库是否存在
IF EXISTS(SELECT*FROM sys.databases WHERE name='RUNOOB')
--存在则删除
DROP DATABASE RUNOOB
GO
--创建数据库
CREATE DATABASE RUNOOB
GO
--引用数据库
USE RUNOOB
GO
--判断将要创建的表书否存在
IF EXISTS(SELECT*FROM sys.tables WHERE name='Websites')
--存在则删除
DROP TABLE Websites
GO
--创建数据库表
CREATE TABLE Websites
(
id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
name VARCHAR(32) NOT NULL,
url VARCHAR(32) NOT NULL,
alexa INT NOT NULL,
country VARCHAR(32)NOT NULL
)
--IF EXISTS(SELECT*FROM sys.tables WHERE name='access_log')
--DROP TABLE access_log
--GO
--CREATE TABLE access_log
--(
--aid INT NOT NULL PRIMARY KEY IDENTITY(1,1),
--site_id INT NOT NULL REFERENCES(id),
--count
--date
--)
--插入数据
INSERT INTO dbo.Websites
( name, url, alexa, country )
VALUES ( 'Google', -- name - varchar(32)
'https://www.google.cm/', -- url - varchar(32)
1, -- alexa - int
'USA' -- country - varchar(32)
)
INSERT INTO dbo.Websites
( name, url, alexa, country )
VALUES ( '淘宝', -- name - varchar(32)
'https://www.taobao.com/', -- url - varchar(32)
13, -- alexa - int
'CN' -- country - varchar(32)
)
INSERT INTO dbo.Websites
( name, url, alexa, country )
VALUES ( '菜鸟教程', -- name - varchar(32)
'http://www.runoob.com/', -- url - varchar(32)
4689, -- alexa - int
'CN' -- country - varchar(32)
)
INSERT INTO dbo.Websites
( name, url, alexa, country )
VALUES ( '微博', -- name - varchar(32)
'http://weibo.com/', -- url - varchar(32)
20, -- alexa - int
'CN' -- country - varchar(32)
)
INSERT INTO dbo.Websites
( name, url, alexa, country )
VALUES ( 'Facebook', -- name - varchar(32)
'https://www.facebook.com/', -- url - varchar(32)
3, -- alexa - int
'USA' -- country - varchar(32)
)
--查询数据表
SELECT*FROM dbo.Websites
SELECT*FROM dbo.Websites WHERE alexa=1 --alexa这一列的值等于1
SELECT DISTINCT country FROM dbo.Websites --查询country这一列不重复的值
SELECT*FROM dbo.Websites WHERE alexa>1 --查询表内alexa列小于1的值
SELECT*FROM dbo.Websites WHERE alexa>1 AND alexa<5 --查询表内alexa列大于1,小于5的值
Select * from dbo.Websites where alexa between 1000 and 5000;--查询表内alexa1000到5000的值
Select * from dbo.Websites where alexa in (5000,3000,4689);--查询表内alexa在(5000,3000,4689)值内的数据
Select * from dbo.Websites where name like '菜鸟%';--模糊查询
SELECT TOP 2 * FROM dbo.Websites
SELECT TOP 2 * FROM dbo.Websites ORDER BY id DESC --后2行--desc 表示降序排列 asc 表示升序
SELECT * FROM Websites WHERE name LIKE '_oogle'; --——"_"占位符搜索
SELECT*FROM dbo.Websites WHERE alexa IN (1,2) --alexa在值1和2的数据
SELECT*FROM dbo.Websites WHERE alexa =1 --alexa等于1的数据
SELECT*FROM dbo.Websites WHERE alexa BETWEEN 1 AND 20; --alexa值在1-20的数据
SELECT*FROM dbo.Websites WHERE alexa NOT IN (1,20); --alexa不是1和20的数据
SELECT alexa AS a FROM dbo.Websites ---查询alexa列,添加别名
SELECT name, CONCAT(url, ', ', alexa, ', ', country) AS site_info FROM Websites;--利用别名添加合并列数据