主要内容：
1）常用快捷键和关键字
2）创建库和表 create
3）插入数据：insert into
4）更改数据：update set
5）删除数据：delete，truncate，delete from 表名
6）数据约束：6种常用的约束，两种添加约束的方法。主键、外检、check、default、unique、非空， alter
7）数据检索：数据查询 as设置别名
8）聚合函数：avg count max min sum len(列名)获取列中值的长度
9）带条件查询：where 子句
10）模糊查询：通配符 _ % [] ^
11）空值处理：is null,is not null,null不知道
12）数据排序：order by, 升序降序,多列排序,Order by子句的位置
重点图片：
常见的约束设置
代码如下：
/*
快捷键：
1)打开和关闭"查询结果窗口":Ctrl+R
2)打开表下的键，就能看到添加的约束
3)数据库设置：显示行号
4)添加约束： add constraint FK_Employees_Deaprtment foreign key(EmpDepartID) 约束名 约束类型(为哪一列添加约束)
5)格式化 ctrl+k
*/
--创建数据库
CREATE DATABASE MyFirstData;
--删除数据库
DROP DATABASE MyFirstData;
--将代码环境切换到你想要使用的表下面
USE MyFirstData;
--如何在数据库中创建数据表
/*
1)最后一句不需要添加逗号
2)非空not null
3)主键primary key
4)自动增长identity(1.1)
5)可以为空null,如果不写是否可以为空,默认是可以为空的。
6)日期类型为datetime。
7)性别类型 bit或者nchar(1)
8)出生年月 datetime
9)工资类型 money
*/
CREATE TABLE Departments
(
AutoID INT IDENTITY(1, 1)
PRIMARY KEY ,
DepartmentName NVARCHAR(10) NOT NULL
);
--删除数据表
USE MyFirstData;
DROP TABLE Departments;
--使用sql语句创建表:员工表
CREATE TABLE Employees
(
EmpID INT IDENTITY(1, 1)
PRIMARY KEY ,
EmpIdCard VARCHAR(18) NOT NULL ,
EmpName NVARCHAR(10) NULL ,
EmpGender BIT NULL ,
EmpJoinData DATETIME ,
EmpAge INT NULL ,
EmpAddress NVARCHAR(50) NULL ,
EmpPhone VARCHAR(100) NOT NULL ,
--所属部门id
EmpDepartID INT NOT NULL ,
EmpEmail VARCHAR(100) NULL
);
-------------------------------------------------------------------------
--创建学生表
CREATE TABLE Student
(
StuId INT IDENTITY(1, 1)
PRIMARY KEY ,
StuName NVARCHAR(10) NULL ,
StuGender NCHAR(1) NOT NULL ,
--不写null 表示可以为空
StuAddress NVARCHAR(100) ,
StuAge INT ,
StuBirthday DATETIME ,
StuCardId VARCHAR(18) ,
StuClassId INT NOT NULL
);
--创建班级表：班级id和班级名称
CREATE TABLE Class
(
ClassID INT IDENTITY(1, 1)
PRIMARY KEY ,
ClassName NVARCHAR(10) NOT NULL
);
--创建成绩表
CREATE TABLE Score
(
--分数主键
ScoreID INT IDENTITY(1, 1)
PRIMARY KEY ,
--学生id，关联学生表的主键
ScoreStuId INT NOT NULL ,
--英语成绩
ScoreEnglish FLOAT ,
--语文成绩
ScoreChinese FLOAT
);
--创建老师表
CREATE TABLE Teacher
(
--主键id 名字 性别 年龄 工资 出生年月 工资是money类型
TeaID INT IDENTITY(1, 1)
PRIMARY KEY ,
TeaName NVARCHAR(10) ,
TeaGender BIT NOT NULL ,
TeaAge INT NULL ,
TeaSalary MONEY ,
--日期类型
TeaBirthdat DATETIME
);
/*
insert into注意事项：
1)插入的语法格式
2)自动编号列默认增长,不需要插入数据
3)如果sql语句比较长，可以在关键字处折行
4)插入int类型不需要引号
5)插入除了自动编号的所有列的值时，可以省略列名,去掉(),但是必须保证列名和列的值是一致对应的。
6)sql语句以分号结尾
*/
--向班级表中插入一行记录
INSERT INTO Class
( ClassName )
VALUES ( '.net黑马' );
SELECT *
FROM Class;
--向学生表中插入一行记录
INSERT INTO Student
( StuName ,
StuGender ,
StuAddress ,
StuAge ,
StuBirthday ,
StuCardId ,
StuClassId
)
VALUES ( '陈如水' ,
'男' ,
'河南省项城市范集乡' ,
25 ,
'2016-10-23' ,
'412702199102184123' ,
1
);
SELECT *
FROM Student;
--插入数据省略列名
INSERT INTO Student
VALUES ( '陈诗音', '女', '河南省项城市范集乡', 12, '2010-10-23', '41270219910218112x', 1 );
SELECT *
FROM Student;
--直插入某几列的值(不一定全部都插入)
INSERT INTO Student
( StuName ,
StuAddress ,
StuGender ,
StuClassId
)
VALUES ( '陈诗乐' ,
'上海市闵行区沪闵路7876号' ,
'女' ,
1
);
SELECT *
FROM Student;
--如何向自动编号列插入值？
--启动某个表的"自动编号列"的插入值功能
SET IDENTITY_INSERT Student ON;
INSERT INTO Student
( StuId ,
StuName ,
StuGender ,
StuClassId
)
VALUES ( 10 ,
'君应怜' ,
'女' ,
1
);
SET IDENTITY_INSERT Student OFF;
SELECT *
FROM Student;
--继续自动编号
INSERT INTO Student
( StuName, StuGender, StuClassId )
VALUES ( '君应怜', '女', 1 );
SELECT *
FROM Student;
--自动编号为11
--如果字符串的值为汉语，建议在字符串的前面加上N,防止中文乱码
--是否出现乱码取决于当前数据库的排序规则.创建数据库的时候，设置排序规则。
INSERT INTO Class
( ClassName )
VALUES ( N'.net黑马二期' );
SELECT *
FROM Class;
/*
如何对数据库中的数据进行更新或者说修改？(数据更新和数据修改)
更新语句语法：update 表名 set 列名称=值, 列名称=值 where 在什么条件下进行更新操作;
update语句不加条件,表示对表中所有数据都进行修改;所以一定要加判断条件。
多少行受到影响，表示你修改了多少行？
use 数据库名；后面不能跟数据表的名称
where语句还可以使用复杂的逻辑判断：or and not或者、并且、非;>、<，用()可以改变逻辑判断的优先级
SQL语句中使用=表示等于判断，而不是==
*/
SELECT *
FROM Student;
USE MyFirstData;
--年龄减1，名字后面加上‘爱’
UPDATE Student
SET StuAge = StuAge - 1 ,
StuName = StuName + '爱'
WHERE StuGender = '女';
SELECT *
FROM Student;
UPDATE Student
SET StuAge = StuAge + 1 ,
StuName = StuName - '爱'
WHERE StuGender = '女';
SELECT *
FROM Student;
/*
数据删除：
delete from Student;如果不加where子句，表示删除表中的所有数据；但是重新插入数据时，自动编号并没有恢复到默认，仍然继续。
经常忘记from关键字
delete只是删除数据，表还在。但是drop是删除表结构，表就不存在了。
delete也可以携带where子句来删除一部分数据
删除表中的全部数据：delete from Student; truncate table Studenet;
如果要删除表中所有数据，建议使用truncate语句:trancate table 表名
1)truncate语句不能携带where子句，只能删除表中所有数据；同时自动编号恢复到初始位置(delete删除不会)
2)truncate的删除效率要比delete高很多
3)核心原因是truncate删除不记录行日志，只记录页数据，而delete删除每行都要记录日志。
substring关键字的使用，截取字符串。
根据日志文件可以进行数据恢复，数据库日志文件特别重要
LEN(列名) 函数：用于获取列的长度
*/
SELECT *
FROM Student;
DELETE Student
WHERE StuAge = 11;
SELECT *
FROM Student;
--截取字符串
UPDATE Student
SET StuName = SUBSTRING(StuName, 0, 4);
SELECT *
FROM Student;
--获取列的长度
SELECT LEN(StuName)
FROM Student;
--向教师表中插入两条数据
INSERT INTO Teacher
VALUES ( '陈如水', 1, 25, 22000, '2016-07-15' );
INSERT INTO Teacher
( TeaName ,
TeaGender ,
TeaAge ,
TeaSalary ,
TeaBirthdat
)
VALUES ( '陈诗音' ,
0 ,
18 ,
1000 ,
'2010-07-09'
);
SELECT *
FROM Teacher;
--向分数表中插入两条数据
SELECT *
FROM Score;
INSERT INTO Score
VALUES ( 1, 96, 90 );
INSERT INTO Score
VALUES ( 2, 91, 80 );
SELECT *
FROM Score;
--给StuId为1的 英语成绩+5分,结果已经变化了
UPDATE Score
SET ScoreEnglish = 100
WHERE ScoreEnglish + 5 > 100;
UPDATE Score
SET ScoreEnglish = ScoreEnglish + 5
WHERE ScoreStuId = 1;
--给所有学生的英语成绩+5分,如果超过100，设置为100分
UPDATE Score
SET ScoreEnglish = ScoreEnglish + 5;
--删除工资大于15000的老师
SELECT *
FROM Teacher;
DELETE FROM Teacher
WHERE TeaSalary > 15000;
SELECT *
FROM Teacher;
--删除整张表中的数据，重新插入自动编号重新开始
TRUNCATE TABLE Teacher;
SELECT *
FROM Teacher;
INSERT INTO Teacher
( TeaName ,
TeaGender ,
TeaAge ,
TeaSalary ,
TeaBirthdat
)
VALUES ( '陈诗音' ,
0 ,
18 ,
1000 ,
'2010-07-09'
);
/*
数据约束
约束是为了保证数据的完整性(正确性)，确保写入到数据库中的数据是合法的、有效的、正确的。
1)非空约束:设置是否为空，其实是一种约束,not null,null
2)唯一约束(可以为空，但是只能出现一次)，如何实现唯一约束:右键单击表----->索引/键----->修改类型和名称----->保存(蓝色钥匙表示唯一约束)
3)主键约束(唯一不为空)
4)默认约束:比如性别这一列，如果用户不输入值，默认为男；设置默认约束的地方：列属性----->默认/绑定值
5)外键约束：如何添加外键约束，在外键表上建。单击----->选择关系;主键是金钥匙，外键是灰钥匙。如果在外键表中，有引用，那么这条记录就不能被删掉。
6)检查约束 设计年龄只能在20到40岁之间，每次你插入数据时，都会进行检查，如果不满足条件，就不让你插入。
注意：创建约束时，如果表中已经存在“非法数据”（与约束不一致的数据），那么此时创建约束是无法成功的，需要先将表中的数据进行修改，然后才能创建约束。
1)通过设计器创建约束：表----->设计----->设置主键(钥匙符号)
2)通过代码(t-sql)创建约束
*/
--教师表中，把TeaGender列的值设置成只能是男或者女
USE MyFirstData;
SELECT *
FROM Teacher;
--手动删除一列，如何实现？删除表中的一列是修改表结构的，使用alter关键字
/*
修改表结构使用alter关键字
删除列使用drop
列的英语单词column
添加一列使用关键字add
alter table Teacher drop column TeaSalary;
约束 英语单词：constraint
*/
SELECT *
FROM Teacher;
ALTER TABLE Teacher DROP COLUMN TeaSalary;
SELECT *
FROM Teacher;
--如何添加一列数据，指定的数据类型
ALTER TABLE Teacher ADD TeaAddress NVARCHAR(10);
SELECT *
FROM Teacher;
--修改TeaEmail这一列的数据类型为varchar(20)
ALTER TABLE Teacher ADD TeaEmail NVARCHAR(20);
SELECT *
FROM Teacher;
ALTER TABLE Teacher ALTER COLUMN TeaEmail VARCHAR(10);
--为表增加一个主键列（添加 约束 约束名 约束类型(类名) 相当于在某列上添加约束）
ALTER TABLE Teacher ADD CONSTRAINT pk_Teahcher_TeaID PRIMARY KEY(TeaID);
--为TeaName添加一个非空约束(其实就是修改列的属性)
ALTER TABLE Teacher ALTER COLUMN TeaName NVARCHAR(20) NOT NULL;
--为TeaName添加一个唯一约束(姓名不能是一样的) 约束关键字 约束名 约束类型 给哪一个字段添加约束
ALTER TABLE Teacher ADD CONSTRAINT uq_Teacher_TeaName UNIQUE(TeaName);
--为性别字段添加默认约束，如果用户不输入数据，就默认为男
ALTER TABLE Teacher ADD CONSTRAINT DF_teacher_TeaGender DEFAULT('男') FOR TeaGender;
--为性别添加检查约束
ALTER TABLE Teacher ADD CONSTRAINT CK_teacher_TeaGender CHECK(TeaGender=1 OR TeaGender=0);
SELECT *
FROM Teacher;
--为年龄添加检查约束 年龄必须在0-120岁之间
ALTER TABLE Teacher ADD CONSTRAINT CK_Teacher_TeaAge CHECK(TeaAge>=0 AND TeaAge<=120);
--为老师表添加主键约束
ALTER TABLE Teacher ADD CONSTRAINT PK_Teacher_TeaID PRIMARY KEY(TeaID);
--为员工表添加主键约束
SELECT *
FROM Employees;
ALTER TABLE Employees ADD CONSTRAINT PK_Employees_EmpID PRIMARY KEY(EmpID);
SELECT *
FROM Departments;
--为员工表添加外键约束
--删除员工表中的EmpDepartID列
ALTER TABLE Employees DROP COLUMN EmpDepartID;
--如何通过代码添加外检约束
ALTER TABLE Employees ADD CONSTRAINT FK_Employees_Deaprtment FOREIGN KEY(EmpDepartID) REFERENCES Departments(AutoID);
--如何删除约束（各种类型的约束都能删除）
ALTER TABLE Employees DROP CONSTRAINT FK_Employees_Departments;
--通过一行代码来增加多个约束
ALTER TABLE Employees ADD
CONSTRAINT FK_Employees_Deaprtment FOREIGN KEY(EmpDepartID) REFERENCES Departments(AutoID),
CONSTRAINT CK_Teacher_TeaAge CHECK(TeaAge>=0 AND TeaAge<=120);
--创建表的时候，就给表增加约束。添加检查约束
CREATE TABLE MyLove
(
--添加主键和非空约束,主键自增
MyLoveID INT NOT NULL
IDENTITY(1, 1)
PRIMARY KEY ,
--添加唯一约束
MyLoveName NVARCHAR(20) NULL
UNIQUE ,
--添加check约束
MyLoveAge INT NOT NULL
CHECK ( MyLoveAge >= 0
AND MyLoveAge <= 100 ) ,
--添加默认约束
MyLoveGender NCHAR(1) DEFAULT ( '男' )
);
/*
数据检索：其实就是数据查询
只查询需要的列： select sName,sAge from 表名
列别名：select sName as 姓名,sAge as 年龄,sBirthday as 出生日期 from Student;
使用where子句检索符合条件的数据：select sName from 表名 where sAge>10; 根据条件，只查询部分行的记录.
检索不与任何表关联的数据：select 1+1; select getdate();select是可以单独使用的，不一定要配合from使用
* 是通配符，表示所有的列。 select * from Employees; 表示查询所有行的数据
Top关键字：获取前几行数字，top一般与order by连用，比如获取年级最小的5个学生.top 几行记录 列名；查询结果中的前多少行记录。
Distinct关键字:去除重复的数据。select distinct sName from Student;
Distinct关键字对查询出的整个结果集进行数据重复处理，而不是针对某一个列。
order by 列名：对某一列进行排序，默认是升序排列。
desc 降序排列，大的数据在上面，小的数据在下面。
*/
SELECT *
FROM Student;
SELECT StuName ,
StuGender
FROM Student;
SELECT StuName
FROM Student
WHERE StuAge > 20;
--为查询到的结果集中的列起别名，查询得到的结果集的列名已经改变
--其实省略as也是可以执行的，给结果集的列起别名有很多种方式的
SELECT StuName AS 姓名
FROM Student
WHERE StuAge > 20;
SELECT StuName 姓名
FROM Student
WHERE StuAge > 20;
SELECT 姓名 = StuName
FROM Student
WHERE StuAge > 20;
--为查询到的结果集添加一列
SELECT 姓名 = StuName ,
婚否 = '否'
FROM Student
WHERE StuAge > 20;
--如何打印数据库所在的服务器的时间,其实是调用的SQL中的函数
SELECT 当前系统时间 = GETDATE();
SELECT 值 = 1 + 1;
SELECT 班长 = '陈如水' ,
班花 = '张幻幻';
--去除查询到的结果集中的重复数据
SELECT *
FROM Student;
SELECT DISTINCT
StuName ,
StuGender
FROM Student;
--按照年龄，降序排列
SELECT *
FROM Student
ORDER BY StuAge DESC;
--按照年龄，升序排列
SELECT *
FROM Student
ORDER BY StuAge;
--选取前几行
SELECT TOP 1
StuAge
FROM Student
ORDER BY StuAge DESC;
--如果top后面接的是表达式，不是数字，一定要用()扩起来，否则会报错的。
SELECT TOP ( 2 * 2 )
StuAge
FROM Student
ORDER BY StuAge DESC;
--top后面跟百分比(都会进1的)
SELECT TOP 50 PERCENT
*
FROM Student
ORDER BY StuAge DESC;
/*
聚合函数
聚合函数聚合的是行中的数据。用于统计一组的信息。
五个最常用的聚合函数：MAX(最大值) MIN(最小值) AVG(平均数) SUM(求和) COUNT(数量，记录的条数)
聚合函数对null值不进行计算
如果一行的数据都是null，count(*)包含对空值行、重复行的统计
平均成绩：select AVG(列名) from 表名
男学生出生日期的最大值与最小值：select max(sBirthday),min(sBirthday) from Student where sex='男';
聚合函数默认把表中的数据当做"一组"来进行处理;如果没有group by的话
*/
--统计年龄的总和
SELECT 年龄总和 = SUM(StuAge)
FROM Student;
--统计当前表中一共有多少条记录
SELECT COUNT(*) AS 记录总和
FROM Student;
--统计所有人的平均年龄
SELECT AVG(StuAge) AS 平均年龄
FROM Student;
--统计平均年龄，使用子查询来实现(里面包含两个子查询)
SELECT 平均年龄 = ( SELECT SUM(StuAge) AS 年龄总和
FROM Student
) / ( SELECT COUNT(*)
FROM Student
);
--查询年龄最大的和年龄最小的人
SELECT MAX(StuAge)
FROM Student;
SELECT MIN(StuAge)
FROM Student;
--聚合函数不统计空值 count() avg() 也不统计空值；sum对于null值，认为0，
SELECT COUNT(StuAddress)
FROM Student;
SELECT *
FROM Student;
/*
带条件查询
1)select(列)...from(表)...where(条件)... 查询年龄在20—30之间的学生：列表示把某一列当做结果集进行显示
2)between...and... 在...之间 闭区间,包含两个端点,between前面跟列名
3)查询班级id为1、2、3的所有学生sClasdID in(1、2、3) 或者 sClasdID >= 1 and sClassID <= 3;后面这种写法效率高
*/
--查询班级id为1、2、3的所有学生sClasdID in(1、2、3)
--注意：如果查询的条件为连续的几个数字，使用 >= 效率更高
SELECT *
FROM Student;
SELECT *
FROM Student
WHERE StuClassId = 1
OR StuClassId = 2
OR StuClassId = 3;
SELECT *
FROM Student
WHERE StuClassId IN ( 1, 2, 3 );
SELECT *
FROM Student
WHERE StuClassId >= 1
AND StuClassId <= 3;
--查询年龄在20—30之间的所有学生
SELECT *
FROM Student
WHERE StuAge BETWEEN 20 AND 30;
/*
模糊查询
1)针对字符串列的操作,
2)其实就是通过通配符进行匹配
3)_ % [] ^ 这四个常用的通配符 _ 表示任意的单个字符(一个汉字) % 匹配任意多个任意字符 [] 表示筛选范围
4)查询出的是某一行记录，在某列数据中进行查询。
5)len()函数的使用：查询某一列字符的长度
6)replace:用一个字符串替换所有出现的字符串值
7)通配符要配合like关键字使用
8)通配符方法[]中就转义了，不在具备通配符的含义，只是普通的符号
*/
--查询出所有姓陈的同学，并且名字只有两个字
USE MyFirstData;
SELECT *
FROM Student;
SELECT *
FROM Student
WHERE StuName LIKE '陈_';
--姓张，名字只有三个字
SELECT *
FROM Student
WHERE StuName LIKE '张__';
--查询出以张开头的所有记录
SELECT *
FROM Student
WHERE StuName LIKE '陈%';
SELECT *
FROM Student
WHERE StuName LIKE '陈%'
AND LEN(StuName) = 2;
--替换操作的使用(对某一列进行的操作，把某个字符串替换成另一个字符串)
UPDATE Student
SET StuName = REPLACE(StuName, '陈', '张');
SELECT *
FROM Student;
--只查询名字中间是数字的(名字只有三个字符，中间的第二个字符是数字的)
SELECT *
FROM Student
WHERE StuName LIKE '张[0-9]妹';
--只查询名字只有三个字符，中间的第二个字符是任意的
SELECT *
FROM Student
WHERE StuName LIKE '张_妹';
--只查询名字只有三个字符，中间的第二个字符是字母的所有记录
SELECT *
FROM dbo.Student
WHERE StuName LIKE '张[a-z]妹';
--只查询名字只有三个字符，中间的第二个字符是字母或者数字的所有记录
SELECT *
FROM dbo.Student
WHERE StuName LIKE '张[a-z0-9]妹';
--只查询名字只有三个字符，中间的第二个字符是不是数字的所有记录
SELECT *
FROM dbo.Student
WHERE StuName LIKE '张[^0-9]妹';
--如何查询出名字中间包含%的所有记录
SELECT * FROM Student WHERE StuName LIKE '%[%]%';
SELECT *
FROM Student
WHERE StuName LIKE '陈%'
AND LEN(StuName) = 2;
/*
这行代码的意思：
1)* 表示显示所有的列信息/所有的记录;如果有列名做参数,结果集就显示这一列信息,
2)FROM 表示从哪张表中查询
3)where表示针对某一列的值进行查询或者检索
*/
/*
空值处理:不表示空，而表示不知道
1)数据库中如果一个列没有指定值，那么值就是null，数据库中的null表示“不知道”，而不是表示没。因此select null+1的
结果还是null，不知道。
2)select * from score where english=null;
select * from score where english!=null;
这两条语句执行都没有结果，因为数据库页不知道
3)Sql中使用 is null和is not null进行空值判断
select * from score where english is null;
select * from score where english is not null;
4)任何值与null进行计算，结果还是null(不知道)
*/
SELECT * FROM dbo.Student;
SELECT * FROM dbo.Student WHERE StuBirthday=NULL;
SELECT * FROM dbo.Student WHERE StuBirthday!=NULL;
--查询出值是空的，或者值是非空的所有记录
SELECT * FROM dbo.Student WHERE StuBirthday IS NULL;
SELECT * FROM dbo.Student WHERE StuBirthday IS NOT NULL;
/*
数据排序
1)order by子句位于select的末尾，它允许按照一个列或者多个列进行排序，还可以指定排序方式，升序还是降序desc
对某个指定的列进行排序：order by 列名 排序规则;
2)如果英语成绩相同，就按照数据成绩排序（降序拍列）order by English desc,Math desc; 根据多列进行排序，如何实现？
3)order by 子句要放到where子句的后面，一般放到所有语句的后面，就是让其他语句先进性筛选，全部筛选完成后，最后排序一下。
4)表中的数据是集合，集合是没有顺序的。order by返回的数据是有顺序的，因此我们把order by以后返回的数据集合叫做"游标"。
*/
SELECT * FROM Student;
SELECT * FROM dbo.Student ORDER BY StuAge DESC;
--根据多列进行排序
SELECT * FROM Student ORDER BY StuAge,StuClassId;
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
