基础SQL总结篇

最新推荐文章于 2025-12-03 13:04:04 发布

原创最新推荐文章于 2025-12-03 13:04:04 发布 · 256 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

SQL 专栏收录该内容

2 篇文章

订阅专栏

作者：http://mrrobot.iteye.com/

/*
* SQL SERVER 
*/
use master
--go：应用于批处理  分块处理语句  执行完一块SQL再执行另一块SQL
go
--判断数据库（user_db）是否存在，如果存在则删除（user_db）
/*
 *语法：
 *    
 *if exists (select * from sysdatabases where name = '数据库名')
 *drop database 数据库名
 *
 * 注释：sysdatabases 系统视图
 */
if exists (select * from sysdatabases where name = 'user_db')
drop database user_db
go
--数据库
/*
 * 创建语法：
 *     create database  数据库名
 * 删除语法：
 *     drop database  数据库名
 */
create database user_db
go
--切换数据库
/*
* 语法：use 数据库名
*/
use user_db
go
--表
/*
 * 创建语法：
 *         CREATE TABLE 表名称
 *        (
 *
 *          列名称1 数据类型,
 *
 *          列名称2 数据类型,
 *
 *          列名称3 数据类型,
 *
 *           ....
 *         )
 *
 *  删除语法：
 *        删除表和数据： drop table 表名称   
 *        删除表中数据不删除表结构：truncate  table 表名称
 */
 --用户表
create table sys_User(
      u_Id  int PRIMARY KEY IDENTITY, -- PK
      FK_sys_User_Status int,         -- FK > sys_User_Status
      FK_sys_User_Role int,           -- FK > sys_User_Role
      u_Account nvarchar(20) not null UNIQUE,-- 账号
      u_Name  nvarchar(20),           -- 昵称
      u_CreateDate  datetime,         -- 创建时间
      u_Asssets money,                -- 资产
      u_Desc  ntext default '暂时没有描述' -- 描述
)
go
--用户权限表
create table sys_User_Role(
      r_Id  int PRIMARY KEY IDENTITY, -- PK
      r_Name  nvarchar(20),           -- 权限名
      r_Desc  ntext   default '暂时没有描述' -- 描述
)
go
--用户状态表
create table sys_User_Status(
      s_Id  int PRIMARY KEY IDENTITY, -- PK
      s_Name  nvarchar(20),           -- 状态名
      s_Desc  ntext  default '暂时没有描述'  -- 描述
)
go
--添加约束
/*
 *  PK:
 *         创建：主键生成策略采用SQL SERVER自身的 NOT NULL AUTO_INCREMENT(1,1)
 *         删除：ALTER TABLE 表名 DROP CONSTRAINT 键名
 *  NOT NULL:非空约束
 *         创建：列名称3 数据类型  NOT NULL
 *  UNIQUE:唯一约束
 *         创建：列名称3 数据类型  UNIQUE
 *  CHECK:条件约束
 *         添加：ALTER TABLE 表名 ADD CHECK (条件)  条件：各种运算符的搭配条件
 *         删除：ALTER TABLE 表名 DROP CONSTRAINT 约束名称
 *  DEFAULT:默认值
 *         创建：列名称3 数据类型  DEFAULT '默认值'
 *         删除：ALTER TABLE 表名 ALTER COLUMN 列名 DROP DEFAULT
 *  FOREIGN KEY:外键约束
 *         创建：alter table 外键表 
 *               add constraint 约束名称 
 *               foreign key (外键表外键列) 
 *               references 主键表(主键列);
 *         删除：DROP CONSTRAINT 约束名
 */
--条件约束
ALTER TABLE sys_User
ADD CONSTRAINT chk_Person_u_Asssets  
CHECK (u_Asssets<=1000000000)
go
--ALTER TABLE sys_User DROP CHECK chk_Person_u_Asssets
--外键约束
--用户表（sys_user）&权限表（sys_User_Role ）
alter table  sys_user 
add constraint fk_sys_user_sys_User_Role 
foreign key (FK_sys_User_Role)
references sys_User_Role(r_Id)
--DROP CONSTRAINT fk_sys_user_sys_User_Role
go
--用户表（sys_user）&状态表（sys_User_Status ）
alter table  sys_user 
add constraint fk_sys_user_sys_User_Status
foreign key (FK_sys_User_Status)
references sys_User_Status(s_Id)
--DROP CONSTRAINT fk_sys_user_sys_User_Status
go
--创建索引
/*
 *CREATE UNIQUE INDEX 索引名称 ON 表名 (列名)
 */
CREATE UNIQUE INDEX index_u_Account
ON sys_User (u_Account)
--DROP INDEX sys_User.index_u_Account
go
--列模式
/*
 *  添加列：ALTER TABLE 表名 ADD 列名 类型
 *  删除列：ALTER TABLE 表名 DROP COLUMN 列名
 *  改变表中列的数据类型:
 *  ALTER TABLE 表名
 *  ALTER COLUMN 列名 类型
 */
ALTER TABLE sys_User ADD u_images image
go
ALTER TABLE sys_User DROP COLUMN u_images
go
--视图
/*
 *CREATE VIEW 视图名称 AS(查询的结果集)
 */
CREATE VIEW view_sys_User_info AS(select * from sys_User as u,sys_User_Role as r,sys_User_Status as s )
--DROP VIEW view_sys_User_info
go

USE user_db
GO
INSERT INTO [user_db].[dbo].[sys_User_Status]
           ([s_Name]
           ,[s_Desc])
     VALUES
           ('冻结状态'
           ,'不能登陆')
GO
INSERT INTO [user_db].[dbo].[sys_User_Status]
           ([s_Name]
           ,[s_Desc])
     VALUES
           ('冻结状态'
           ,'不能登陆')
GO
INSERT INTO [user_db].[dbo].[sys_User_Status]
           ([s_Name]
           ,[s_Desc])
     VALUES
           ('正常状态'
           ,'正常进行操作')
GO
INSERT INTO [user_db].[dbo].[sys_User_Status]
           ([s_Name]
           ,[s_Desc])
     VALUES
           ('黑名单状态'
           ,'拒绝任何操作')
GO
INSERT INTO [user_db].[dbo].[sys_User_Role]
           ([r_Name]
           ,[r_Desc])
     VALUES
           ('管理员'
           ,'可以管理本系统')
GO

INSERT INTO [user_db].[dbo].[sys_User_Role]
           ([r_Name]
           ,[r_Desc])
     VALUES
           ('普通用户'
           ,'可以访问本系统')
GO


INSERT INTO [user_db].[dbo].[sys_User_Role]
           ([r_Name]
           ,[r_Desc])
     VALUES
           ('超级管理员'
           ,'超级权限')
GO
INSERT INTO [user_db].[dbo].[sys_User_Role]
           ([r_Name]
           ,[r_Desc])
     VALUES
           ('访客'
           ,'可阅读相关信息')
GO

INSERT INTO [user_db].[dbo].[sys_User]
           ([FK_sys_User_Status]
           ,[FK_sys_User_Role]
           ,[u_Account]
           ,[u_Name]
           ,[u_CreateDate]
           ,[u_Asssets]
           ,[u_Desc])
     VALUES
           (3
           ,1
           ,'chenxuan_s@163.com'
           ,'chenxuan'
           ,'2015-03-20 13:55:50.000'
           ,1000.00
           ,'这就是描述')
GO

INSERT INTO [user_db].[dbo].[sys_User]
           ([FK_sys_User_Status]
           ,[FK_sys_User_Role]
           ,[u_Account]
           ,[u_Name]
           ,[u_CreateDate]
           ,[u_Asssets]
           ,[u_Desc])
     VALUES
           (2
           ,1
           ,'chenxuan_s1@163.com'
           ,'chenxuan1'
           ,'2015-03-20 13:55:50.000'
           ,2000.00
           ,'这就是描述')
GO
INSERT INTO [user_db].[dbo].[sys_User]
           ([FK_sys_User_Status]
           ,[FK_sys_User_Role]
           ,[u_Account]
           ,[u_Name]
           ,[u_CreateDate]
           ,[u_Asssets]
           ,[u_Desc])
     VALUES
           (1
           ,1
           ,'chenxuan_s2@163.com'
           ,'chenxuan2'
           ,'2015-03-20 13:55:50.000'
           ,3000.00
           ,'这就是描述')
GO

USE user_db
GO
/*
 * SQL select
 */
--查询所有
select * from sys_user
go
--查询单列
select u_account from sys_user
go
--查询多列
select u_account,u_name from sys_user
/*
 *SQL distinct 
 *注意：ntext 数据类型不能选为 DISTINCT，因为它不可比。
 */
select s_name from sys_user_status
select distinct s_name from sys_user_status
/*
 * SQL where
 * 
 */
select * from  sys_user as u where  u.u_asssets = 1000
select * from  sys_user as u where  u.u_asssets > 1000
select * from  sys_user as u where  u.u_asssets < 1000
select * from  sys_user as u where  u.u_asssets <= 1000
select * from  sys_user as u where  u.u_asssets >= 1000
select * from  sys_user as u where  u.u_asssets != 1000
 /*
  * SQL AND & OR
  *
  */
 select * from  sys_user as u where  u.u_asssets != 1000 and u.u_asssets = 2000
 select * from  sys_user as u where  u.u_asssets = 1000 or u.u_asssets = 2000
 /*
 * SQL Order By
 *
 */
 select * from  sys_user as u where  u.u_asssets < 10000 order by u.u_id asc
 select * from  sys_user as u where  u.u_asssets < 10000 order by u.u_id desc
 /*
 * SQL insert
 *
 */
 INSERT INTO [user_db].[dbo].[sys_User_Status]
           ([s_Name]
           ,[s_Desc])
     VALUES
           ('冻结状态'
           ,'不能登陆')
GO
 /*
 * SQL update
 *
 */
 select * from sys_user
 update sys_user  set  sys_user.u_name='rrr' where sys_user.u_name ='xxx'
 update sys_user  set  sys_user.u_name='xxx' 
 select * from sys_user
 go
 /*
 * SQL delete
 */
 select * from sys_user
 --TRUNCATE TABLE sys_user
 --DROP TABLE sys_user 
 /*
 * SQL Top
 */
select * from sys_user
select top 2 * from sys_user 
 /*
 * SQL Like
 *
 */
select * from sys_user where u_Account like 'chen%'
select * from sys_user where u_Account like '%com'
select * from sys_user where u_Account like '_hen'
select * from sys_user where u_Account like '[c]%'
select * from sys_user where u_Account like '[^c]%'

 /*
 * SQL In
 *
 */
select * from sys_user
select * from sys_user  where FK_sys_User_Status in (1,2)
/*
 *  SQL Between
 */
select * from sys_user 
select * from sys_user  where u_asssets between 0 and 2100
  /*
 *SQL Aliases
 *
 */
 select * from sys_User  As u
  /*
  * SQL Join
  *
  */
  --笛卡尔积
  select *from sys_User,sys_User_Role,sys_User_Status 
  --普通方式两表联查
  select *from sys_User,sys_User_Role where sys_User.FK_sys_User_Role = sys_User_Role.r_Id
  --普通方式三表联查
  select *from sys_User,sys_User_Role,sys_User_Status 
  where sys_User.FK_sys_User_Role = sys_User_Role.r_Id and sys_User.FK_sys_User_Status = sys_User_Status.s_Id
  --SQL Inner Join
  --两表联查
  select * from sys_User as u 
  inner join sys_User_Role r  
  on  u.FK_sys_User_Role=r.r_Id 
  --三表联查
  select * from sys_User as u 
  inner join sys_User_Role r  
  on  u.FK_sys_User_Role=r.r_Id 
  inner join sys_User_Status s 
  on u.FK_sys_User_Status =s.s_Id
  --SQL Left Join
    --两表联查
  select * from sys_User as u 
  left join sys_User_Role r  
  on  u.FK_sys_User_Role=r.r_Id 
  --SQL Right Join
  --两表联查
  select * from sys_User as u 
  Right join sys_User_Role r  
  on  u.FK_sys_User_Role=r.r_Id 
  --SQL Full Join
  --两表联查
  select * from sys_User as u 
  full join sys_User_Role r  
  on  u.FK_sys_User_Role=r.r_Id 
  /*
  * SQL Union
  * 注意：使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。
  */
  --select * from sys_User
  --union
  --select *from sys_User_Role
  select COUNT(*) from sys_User
  union
  select count(*) from sys_User_Role
  /*
 * SQL Select Into
 *
 */
select  * into mycopeTable from sys_User
select  sys_User.u_Name,sys_User.u_Account into mycopeTable2 from sys_User
  /*
  * SQL  Nulls
  *
  */
 select  * from sys_User where sys_User.u_Account = null
 select  * from sys_User where sys_User.u_Account  is not null
 select  * from sys_User where sys_User.u_Account is null
 
 -------------------------------------函数应用---------------------------------------------------------------
 -- 语法:SELECT function(列) FROM 表
  /*
  * SQL isnull()
  * 如果值为null 返回指定的参数
  */
 select sys_User.*, ISNULL(sys_User.u_Desc,'') from sys_User 
   /*
  * SQL AVG(column)
  * 平均数
  */
  select AVG(sys_User.u_Asssets) from sys_User
    /*
  * COUNT(column)	返回某列的行数（不包括NULL值）
  */
  select COUNT(sys_User.u_Asssets) from sys_User
    /*
  * COUNT(*)	返回被选行数
  */
    select COUNT(*) from sys_User
    /*
  * MAX(column)	返回某列的最高值
  */
      select MAX(sys_User.u_Asssets) from sys_User
    /*
  * MIN(column)	返回某列的最低值
  */
     select MIN(sys_User.u_Asssets) from sys_User
     /*
  * SUM(column)	返回某列的总和
  */
       select sum(sys_User.u_Asssets) from sys_User
     /*
  * COUNT(DISTINCT column)	返回相异结果的数目
  */
  select COUNT(distinct sys_User.u_Asssets ) from sys_User