步步为营-35-SQL语言基础
SQL 结构化查询语言(Structured Query Language)
DDL DML DCL 在此不再对其进行详细解释
1 DDL 数据库定义语言
1.1 创建数据库脚本
--DDL create drop alter --创建数据库 create database TestDB; --删除数据库 drop database TestDB; --创建数据库 create database DemoDB2 on ( name = 'DemoDB2',--逻辑名称于创建名字一致 size = 5MB, --最小为5MB filegrowth = 2MB,--超过5MB后以步长为2MB增长 fileName = 'F:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DemoDB2.mdf' -- 文件存放路径 ) log on ( name = 'DemoDB2_log', size = 2MB, --最小为2MB filegrowth = 2MB,--超过5MB后以步长为2MB增长 fileName = 'F:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DemoDB2_log.ldf' )
1.2 操作表脚本
use DemoDB go --1创建表 Create table Employee ( --设置自增长主键,不为空 EmpId int identity(1,1) primary key not null, EmpName nvarchar(32) null,--定义大小为2的幂 32,64 EmpAge int default(18) not null, -- 设置默认值18 Delflag smallint default(0) not null ) go --2修改表 --2.1 修改表名称 --ALTER TABLE DemoDB.Employee rename to DemoDB.Student;--不好使 --通过存储过程重新命名 exec sp_rename 'Employee','Student' --2.2 修改列 --2.2.1 修改列名 exec sp_rename 'Student.EmpAge','StuAge' --2.2.2 新增列 Alter table Student add StuClassNo int --2.2.3 修改列 Alter table Student alter column StuClassNo nvarchar(32) --2.2.4 删除列 Alter table Student drop column StuClassNo --3删除表 drop table UserInfo
2 DML 数据库操作语言(很重要)
2.1 Select 语句都被写烂了,这里总结一下执行顺序
From=>where=>group by =>having=>select=>order by
--Insert into 表名(列名称...) values (值..); Insert into Student (EmpId, StuName, StuAge, Delflag) values (1,'张三',12,0); --删除 delete from Student where 1=1 --查询 select * from Student; --修改 Update 表名 set 列名 = 值 where 条件表达式 Update Student set StuName = '李四' where EmpId = 1;
2.2 约束:保证数据的完整性
alter table 表名 add constraint 约束的名字.....
--00 删除约束 --alter table 表名 drop constraint 约束的名字 alter table UserInfo drop constraint DF_UserInfo_DelFlag -- 01 添加默认约束 --alter table 表名 add constraint 约束的名字 default(0) for ... alter table UserInfo add constraint DF_UserInfo_DelFlag default(0) for DelFlag; --02 添加主键约束 alter table UserInfo add constraint PK_UserInfo_EmpId primary key(EmpId); --03 添加唯一约束 alter table UserInfo add constraint UN_UserInfo_StuName unique (StuName) --04 添加检查约束 alter table UserInfo add constraint CK_UserInfo_StuAge check(StuAge>0 and StuAge<180) --05 非空约束 --以便建表的时候就创建了 --06 外键约束 --0601 先建立外键表Class create table ClassInfo ( ClassId int identity(1,1) primary key, ClassName nvarchar(32) null ) --0602 添加外键列 alter table UserInfo add ClassNo int null --0603 添加外键约束 alter table UserInfo add constraint FK_UserInfo_ClassInfo foreign key(ClassNO) references ClassInfo(ClassId);
2.3 常用的函数
use DemoDB go select * from UserInfo -- 给列和表起别名 select EmpId as 编号 from UserInfo as U; --count(1)优于count(*);因为count(*) 找出表中最短的数列 --常见的聚合函数 --count(),sum(),max(),min(),avg(),max(),min() --常见的转换函数 --Convert(目标类型,转换的表达式,格式规范) --Cast(表达式 as 类型) select StuName+'年龄是'+CONVERT(nvarchar(32),StuAge) as 学生年龄信息 from UserInfo; select StuName+'年龄是'+Cast(StuAge as nvarchar(32)) as 学生年龄信息 from UserInfo; --日期函数 --01获取当前日期 --select getdate(); --dateadd(要增加的计量单位,数量,原有日期)添加日期 select DATEADD(day,2,'2012-12-12') -- DATEDIFF(要比较的计量单位,日期,日期)时间差 select DATEDIFF(DAY,'2012-12-12','2012-12-19'); --DatePart select DATEPART(MONTH,'2014-1-15'); select MONTH('2014-1-15'); --字符串函数 --转换大(upper)小(LOWER)写 select Upper('abc') ; --字符串左(left)右(right)截取 select left('1234',2); --字符串长度 select DATALENGTH(N'12'); --去掉左(LTRIM)右(RTRIM)空格 select LTRIM(' 1231 ');
2.4 Case When的两种使用
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; namespace 完整的增删查改 { public partial class MainForm : Form { public MainForm() { InitializeComponent(); } private void MainForm_Load(object sender, EventArgs e) { string sqlStr = "select EmpId, Pwd, StuName, StuAge, Delflag, ClassNo from UserInfo where Delflag = 0 "; LoadUserInfoToGridView(sqlStr); } //获取连接字符串 public string ConnStr = SqlHelper.GetConnStr(); #region 02右击删除-多项删除 private void contextMenuDelete_Opening(object sender, CancelEventArgs e) { //02-01给出提示,判断是否确定删除 if (MessageBox.Show("确认删除选中信息吗?","提示信息",MessageBoxButtons.YesNo,MessageBoxIcon.Information) != DialogResult.Yes) { return; } //02-02 拿到选中行的主键,并把主键拼接起来 var rows = this.dataGridView1.SelectedRows; //02-03 编写执行SQL脚本 #region 方法二- StringBuilder sbSql = new StringBuilder(); List<SqlParameter> parameters = new List<SqlParameter>( ); for (int i = 0; i < rows.Count; i++) { sbSql.Append("Update UserInfo set DelFlag = 1 where EmpId=@EmpId"+i+";"); SqlParameter para = new SqlParameter("@EmpId"+i,SqlDbType.Int); para.Value = int.Parse(rows[i].Cells["EmpId"].Value.ToString()); parameters.Add(para); } string sqlStr2 = sbSql.ToString(); #endregion int resultNum = SqlHelper.ExcuteNonQuery(sqlStr2, parameters.ToArray()); if (resultNum > 0) { MessageBox.Show("删除成功!一共删除" + resultNum + "条"); } MainForm_Load(this,null); } #endregion #region 03双击事件-弹出修改窗体 private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e) { //03-01 拿到Id int id = int.Parse(dataGridView1.SelectedRows[0].Cells["EmpId"].Value.ToString()); //03-02打开修改窗体 EditForm frmEdit = new EditForm(new UserInfo (){EmpId = id}); //03-04让主窗体关注"编辑窗体"的关闭事件. frmEdit.FormClosing += EditUserInfoFrm_FormClosing; frmEdit.Show(); //03-03 通过构造函数传递数据 } //03-04 当修改窗体关闭时候执行下面的事件响应方法 private void EditUserInfoFrm_FormClosing(object sender, FormClosingEventArgs e) { btnSearch_Click(this, null); } #endregion #region 04-多条件查询 private void btnSearch_Click(object sender, EventArgs e) { //string connStr = SqlHelper.GetConnStr(); #region 04-02拼接SQl语句 string sqlText = "select EmpId, Pwd, StuName, StuAge, Delflag, ClassNo from UserInfo"; List<string> whereList = new List<string>(); List<SqlParameter> parameters = new List<SqlParameter>(); if (!string.IsNullOrEmpty(this.txtName.Text.Trim())) { //把Where条件添加到List集合中 whereList.Add(" StuName like @StuName "); //把参数进行赋值 SqlParameter parameter = new SqlParameter(); parameter.ParameterName = "@StuName"; parameter.Value = "%" + txtName.Text + "%"; parameters.Add(parameter); } if (!string.IsNullOrEmpty(this.txtAge.Text.Trim())) { //把Where条件添加到List集合中 whereList.Add(" StuAge like @StuAge "); //把参数进行赋值 SqlParameter parameter = new SqlParameter(); parameter.ParameterName = "@StuAge"; parameter.Value = "%" + txtAge.Text + "%"; parameters.Add(parameter); } if (whereList.Count > 0) { sqlText += " where " + string.Join(" and ", whereList); } #endregion LoadUserInfoToGridView(sqlText, parameters.ToArray()); } #endregion #region 05-将01和04进行优化可得-----加载数据到GridView private void LoadUserInfoToGridView(string sqlStr,params SqlParameter[] parameters) { //01-00 设置强类型数据源 List<UserInfo> userInfoList = new List<UserInfo>(); DataTable dt = SqlHelper.ExcuteDataTable(sqlStr, parameters); //01-05 应该使用强类型数据 foreach (DataRow dr in dt.Rows) { //数据封装 UserInfo userInfo = new UserInfo(); userInfo.EmpId = int.Parse(dr["EmpId"].ToString()); userInfo.Pwd = dr["Pwd"].ToString(); userInfo.StuName = dr["StuName"].ToString(); userInfo.StuAge = int.Parse(dr["StuAge"].ToString()); userInfo.Delflag = Char.Parse(dr["Delflag"].ToString()); userInfo.ClassNo = int.Parse(dr["ClassNo"] == DBNull.Value ? "-1" : dr["ClassNo"].ToString()); //添加到列表中 userInfoList.Add(userInfo); } //01-06 配置数据源 this.dataGridView1.DataSource = userInfoList; } #endregion } }
2.5 IsNull(表达式,替换内容)函数
select CreateDate,IsNull(CreateDate,getDate()) from UserInfo
2.6 with 语句的使用
select distinct(VendorName),ve.*,ba.* from [dbo].[AS_Batch] as ba inner join AS_Vendor ve on ba.VendorID = ve.VendorID inner join AS_Equipment ae on ae.EquipmentID=ba.EquipmentID where 1=1 order by ve.VendorID desc --新建了一台设备 "联想电脑" select * from AS_Equipment where EquipmentID = '1AC91813-F636-43AD-82E1-0384051D0DB2'; --新建了3个厂商 select * from AS_Vendor where VendorID in ('065A1954-88A8-415E-BA19-549C17F3354E','EA7B0530-2D5C-4ADB-B2D7-5854C00787A7','2D8F6E94-7243-42C1-B6AD-1008F6D50CE8') --创建一个场景: --批次 厂商 数量 价格 --批次1 A 10 100 --批次2 B 20 200 --批次3 C 30 300 --批次4 A 40 400 select * from [AS_Batch] where BatchID in ('027D504A-9EC2-43F3-BCC9-8A962831F603','FF8BA2CC-2662-469E-BB16-07DB7599FD06','187334AB-892D-4553-A928-7638067E1D77','F3768B5A-9AB2-497B-B8BB-06E81473996F') --根据设备ID查询供应商 select ve.* from [dbo].[AS_Batch] as ba inner join AS_Vendor ve on ba.VendorID = ve.VendorID inner join AS_Equipment ae on ae.EquipmentID=ba.EquipmentID where 1=1 and ae.EquipmentName='联想电脑' order by ve.VendorID desc --获取同一设备在不同厂商的均价(a)，计算方法：(批次1数量*批次1价格+批次2数量*批次2价格…)/(批次1数量+批次2数量…)； select sum(ba.EquipmentPrice*ba.EquipmentNum)/sum(ba.EquipmentNum) as EquipmentAvgPrice,sum(ba.EquipmentNum) as EquipmentAmount,ba.VendorID from [dbo].[AS_Batch] as ba inner join AS_Vendor ve on ba.VendorID = ve.VendorID inner join AS_Equipment ae on ae.EquipmentID=ba.EquipmentID where 1=1 and ae.EquipmentName='联想电脑' group by ba.VendorID order by ba.VendorID desc --创建一个场景: --批次 厂商 数量 价格 损坏个数/次数 --批次1 A 10 100 1/2 --批次2 B 20 200 2/(1+2) --批次3 C 30 300 4/(1+1+2+1) --批次4 A 40 400 2/(2+3) --获取同一设备在不同厂商的设备正常率(p)=(总设备个数-维修设备个数)/总设备个数 select count(distinct(af.QRCode)),ba.VendorID from [dbo].[AS_Batch] as ba inner join AS_Vendor ve on ba.VendorID = ve.VendorID inner join AS_Equipment ae on ae.EquipmentID=ba.EquipmentID inner join AS_AfterSale af on substring(af.QRCode,1,36)=ba.batchid where 1=1 and ae.EquipmentName='联想电脑' group by ba.VendorID order by ba.VendorID desc -------------计算性厂商价比 with pri as ( select sum(ba.EquipmentPrice*ba.EquipmentNum) as EquipmentAmountPrice, sum(ba.EquipmentPrice*ba.EquipmentNum)/sum(ba.EquipmentNum) as EquipmentAvgPrice,sum(ba.EquipmentNum) as EquipmentAmount,ba.VendorID as VendorID from [dbo].[AS_Batch] as ba inner join AS_Vendor ve on ba.VendorID = ve.VendorID inner join AS_Equipment ae on ae.EquipmentID=ba.EquipmentID where 1=1 and ae.EquipmentName='联想电脑' group by ba.VendorID ), repairs as ( select count(distinct(af.QRCode)) as RepairsNum,ba.VendorID as VendorID from [dbo].[AS_Batch] as ba inner join AS_Vendor ve on ba.VendorID = ve.VendorID inner join AS_Equipment ae on ae.EquipmentID=ba.EquipmentID inner join AS_AfterSale af on substring(af.QRCode,1,36)=ba.batchid where 1=1 and ae.EquipmentName='联想电脑' group by ba.VendorID ) --设备正常率/均价 select (EquipmentAmount-RepairsNum)/EquipmentAmountPrice from pri,repairs where pri.VendorID=repairs.VendorID;
3 DCL语言
--03-01分配权限
grant select on UserInfo To zw
4 大的备份脚本还原
5：DBA管理，
查看表字段和表字段属性
SELECT 表名=d.name, 表说明=isnull(f.value,''), 字段序号=a.colorder, 字段名=a.name, 字段标题=isnull(g.[value],''), 标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end, 主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in ( SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid ))) then '√' else '' end, 类型=b.name, 占用字节数=a.length, 长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'), 小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0), 允许空=case when a.isnullable=1 then '√'else '' end, 默认值=isnull(e.text,'') --into ##tx FROM syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=g.class and a.colid=g.minor_id --sql2000此处替换为：left join sysproperties g on a.id=g.id and a.colid=g.smallid left join sys.extended_properties f on d.id=f.class and f.minor_id=0 --sql2000此处替换为：left join sysproperties f on d.id=f.id and f.smallid=0 where d.name='ProcessPublish' --如果只查询指定表,加上此条件 order by object_name(a.id),a.colorder
查看数据表及对应的记录数
SELECT SUM(记录条数) AS 总记录数 FROM (SELECT TOP (10000) a.name AS 表名, MAX(b.rows) AS 记录条数 FROM sys.sysobjects AS a INNER JOIN sys.sysindexes AS b ON a.id = b.id WHERE (a.xtype = 'u') GROUP BY a.name ORDER BY 记录条数 DESC) AS t1 SELECT a.name AS 表名, MAX(b.rows) AS 记录条数 FROM sys.sysobjects AS a INNER JOIN sys.sysindexes AS b ON a.id = b.id WHERE (a.xtype = 'u') GROUP BY a.name ORDER BY 记录条数 DESC
