SQL基础知识

最新推荐文章于 2021-12-08 17:11:59 发布

Ayn慢慢

最新推荐文章于 2021-12-08 17:11:59 发布

阅读量461

点赞数

分类专栏：数据库文章标签： SQL 数据库

本文链接：https://blog.csdn.net/qq_34400736/article/details/81297249

版权

数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

1创建表

除了creat,select，insert into,也可以创建表，在表备份里面

1.1语法

Use MyDBTest—声明在哪个数据库中生成，一定要写或者在上方选择

create table Tstudent—创建一个Tstudent表

(

stuId int ,

stuName nvarchar(10),--列名类型可为空

stuGender bit not null—列名类型非空

)

1.2创建表的数据类型

Char（若为10，则只能存10个，不够的话空格补齐）

Nchar

Varchar（可以0-10个英文，不会空格补齐，不能10个汉字）

Nvarchar（可以10个汉字可以10个英文）

Varchar(max)

Nvarchar(max)

Text

Ntext

int(将该列设定为主键后，可以在属性中设置自动增长)

带var表示可变

不带var表四固定长度

带N为unicode字符

不带N为非unicode字符，

如果列数据项的大小一致，则使用 char。

如果列数据项的大小差异相当大，则使用 varchar。

如果列数据项大小相差很大，而且大小可能超过 8,000 字节，请使用 varchar(max)

ps:

1. Char(10),10表示10个字符。能存储10个ASCII字符和5个Unicode字符。长度为1-8000

存储10个ASCII字符，占10个字节

存储5个Unicode字符占10个字节

2.Nchar(10),能存储10个ASCII字符或10个Unicode字符。长度为1-4000

存储10个ASCII字符占20个字节

存储10个Unicode字符也占20个字节。

2 向表中插入数据

2.1语法

1. Insert into 表名(列名1,列名2)values(值1,值2)
2. Insert into 表名values(值1,值2)-- 若向所有的列插入数据，则可以省略列名

3. Insert into 表(列1) values(值1)—向指定列中添加数据，但须保证其余列可以为NULL才可。

4.插入多行数据

insert into Score1(studentId,english,math)

select 1,80,100 UNION all

select 1,80,100 union all

select 3,50,59 union ALL

select 4,66,89 UNION all

select 5,59,100

----全部为union all连接的语句进行添加，才不会删除重复项，若有一个不是union all ,则就会删除重复项

Insert into 表（列）----当多行添加时只写一行改行代码即可

select 值1，值2 union—---写列值

select 值1，值2—最后一--行不必加union

-----union连接的语句进行添加数据会将重复添加的数据删除

3更新表中数据

3.1语法

update 表名 set 列1=值1,列2=值2 where 列名=值

如果不加where条件，则整张表都会被更新

4删除表中数据

4.1 语法

1、delete FROM tableName ——删除表中所有数据

2、drop TABLE tableName ——删除该表

3、delete FROM tableName where colName=1 ——根据条件删除某一条数据

4、truncate TABLE tableName ——删除数据表，不能加where

5 SQL语句之代码对列（非数据）的操作

5.1 手动删除一列

ALTER TABLE taleName DROP COLUMN colName

5.2 手动增加列

ALTER TABLE tableName ADD colName type,colName type

——不加column关键词，增加多列用逗号隔开

5.3手动修改列的数据类型

ALTER TABLE tableName ALTER COLUMN colName type

——需要column和两个alter关键词

6 SQL语句之准确查询语句的补充

6.1查询一个表中的所有数据

SELECT * FROM tableName

--*代表所有的列，为了简化而已。

6.2查询特定列的数据

SELECT colName1,colName2 from tableName

--将*替换下来即可，中间用逗号隔开

6.3添加别名查询某一列（为了让别人看的更方便）

1、SELECT EmpName AS 姓名,EmpAgeAS 年龄 FROM employees

--关键词as 后面的为别名，但不会修改表自身的列名

2、SELECT姓名=EmpName,年龄=EmpAge FROMemployees

3、SELECT EmpName+EmpAdr AS 信息 FROM employees

--将几列数据合为一列，以别名的方式显示出来，加号注意同类型才可加

6.4查询具有某种条件的方法

SELECT姓名=EmpName,年龄=EmpAge FROMemployees WHERE deptid=1

--关键词where 查询depid为一的人

6.5 between and语句的使用，在什么之间

SELECT * FROM tblstudent WHERE tsage BETWEEN20 AND 30

6.6 in的作用 or的意思

SELECT * FROM tblstudent WHERE tsid IN (10,11,12)

--查询tsid为10或12或11的数据

6.7 SQL语句之模糊查询

1. 模糊查询关键字 like

2.通配符 _ 、 % 、 [] 、 ^

select * from TblStudent where tSName like '张%^'

select * from TblStudent where tSName like '张%' andLEN(tSName)=3

--^只有MSSQL Server支持，其他DBMS用not like。

select * from TblStudent where tSName like '张%'

--通配符 %多字符匹配的通配符，它匹配任意次数（零或多个）出现的任意字符

select * from TblStudent where tSName like '张__'

--通配符_ 单字符匹配，它匹配单个出现的字符，查询三个字的张姓人物

select * from TblStudent where tSName like '张[a-z]_'

select * from TblStudent where tSName like '张[0-9]_'

select * from TblStudent where tSName like '张[^0-9]_'

--[] 只匹配一个字符并且这个字符必须是[]范围内的 [0-9] [a-z]

not与like一起使用：not like ….

3. 要通配_、%、[、^这些字符怎么办？[_]、[%]、[ [ ]、^(不需要放到中括号里，因为^只有放到中括号中才认为是通配符)

6.8 select补充

1.可直接计算

select 45*3 --执行后会显示结果

2.显示当前时间

SELECT getdate ()

7 order by,Distinct,limit关键词的作用(查询)

7.1 order by

排序，默认为从小到大，默认ASC为从小到大，DESC为从大到小，位于select末尾

SELECT * FROM employees ORDER BY deptid ASC–一般和TOP配合使用

7.2 distinct

去除重复的数据（可以是指定列的数据）

SELECT DISTINCT EmpName FROM employees

--去除EmpName列的重复数据

SELECT DISTINCT *FROM employees

DISTINCT是对查询出的整个结果集进行数据重复处理的，而不是针对某一个列。

7.3 limit

限制查询个数

Select * from tableName limit 3 ——表中前三条数据

8 SQL聚合函数（需要有后缀括号）

1.注意：聚合使用后一定要弄清楚是否有其他列，如果有其他列，那么就要将其他列group by，不然会产生不伦不类的表

2.MAX（最大值）、MIN（最小值）、AVG （平均值）、SUM （和）、COUNT（数量:记录的条数。）

聚合函数对null值不计算。

如果一行的数据都是null，count(*)包含对空值行、重复行的统计

3.代码：

SELECT COUNT(empName) FROM employees—名字的数目

SELECT COUNT(empName),empage FROMemployees—会报错，因为会产生畸形表

SELECT MAX(empage),MIN(empage)FROMemployees—最大最小值

9 SQL之数据分组(group by，where，having)

9.1 group by

1. 在使用select查询的时候，有时需要对数据进行分组汇总（即：将现有的数据按照某列来汇总统计），这时就需要用到group by语句。select 语句中可以使用group by 子句将行划分成较小的组，然后，使用聚组函数返回每一个组的汇总信息。//分组一般都和聚合函数连用。

2.group by 语句作用的一列就相当于聚合函数的作用。

SELECT 班级ID=tsclassid,总人数=COUNT(*)FROM tblstudent GROUPBY tsclassid

--count(*)因为前面执行的语句，显示的为符合前面语句的数量，tsclassid相当于被聚合

SELECT 班级ID=tsclassid,总人数=COUNT(*)

FROM tblstudent

WHERE tsgender='男'

GROUP BY tsclassid

--上述为一个语句，添加了一个条件为男

3. GROUP BY子句必须放到WHERE语句的之后 ,Group By与Order By都是对筛选后的数据进行处理，而Where是用来筛选数据的

没有出现在GROUP BY子句中的列是不能放到SELECT语句后的列名列表中的（聚合函数中除外）

错误： select sClassId,count(sName),sAge from student group by sClassId

正确： select sClassId,count(sName),avg(sAge) from student group by sClassId

9.2分组之后再进行筛选（having）

对表中的数据分组后，会得到一个分组后的结果集，对该结果集进行删选用having

SELECT 班级ID=tsclassid,总人数=COUNT(*)

FROM tblstudent

GROUP BY tsclassid

HAVING COUNT(*)>3—having后需写聚合表达式

1.where和having的区别

注意：Having中不能使用未参与分组的列，Having不能替代where。作用不一样，Having是对组进行过滤。

Having 是Group By的条件对分组后的数据进行筛选（与Where类似，都是筛选，只不过having是用来筛选分组后的组的。）

在Where中不能使用聚合函数，必须使用Having，Having要位于GroupBy之后。

Having的使用几乎是与where一样的，也可以用in。

Having count(*) in (5,8,10)

10 SQL之类型转换函数

1.CAST ( expressionAS data_type)//顺序和convert相反

CONVERT ( data_type, expression,[style])

Select ‘您的班级编号’+1 错误这里+是数学运算符

2.代码：

SELECT '英语成绩为：'+CAST(tenglishAS VARCHAR(20)) FROM tblscore

SELECT '英语成绩为：'+CONVERT(VARCHAR(10),tenglish) FROM tblscore

3.对日期的转换

SELECT CONVERT(VARCHAR(50),GETDATE())—显示当前时间

SELECT CONVERT(VARCHAR(50),GETDATE(),120)

--第三个参数可以填特定的数字，将显示的时间格式转换

11 联合结果集

11.1上下联合之union（效率低，因为要去除重复）

1.简介：

集合运算符是对两个集合操作的，两个集合必须具有相同的列数，列具有相同的数据类型（至少能隐式转换的），最终输出的集合的列名由第一个集合的列名来确定。（可以用来连接多个结果）

2.基本原则：

每个结果集必须有相同的列数；每个结果集的列必须类型相容。

3. SELECT tsage,tsphone FROM tblstudent

UNION

SELECT tsid,CAST(tmath AS VARCHAR(20)) FROMtblscore

4. 将多个结果集合并成一个结果集。

union(默认去除重复，相当于默认应用了distinct)

10.2上下联合之union all（效率高，不去出重复）

1.和uniom几乎相同

2.不同点

select tName,tSex from teacher union

select sName,sSex from student

--UNION合并两个查询结果集，并且将其中完全重复的数据行合并为一条

select tName,tSex from teacher union all

select sName,sSex from student

--Union因为要进行重复值扫描，所以效率低，因此如果不是确定要合并重复行，那么就用UNION ALL

11 备份表格

1.无条件复制并且表没事先建好

SELECT *INTO newTblscore FROM tblscore

--将tblscore全部复制到通过该语句新创建的表newTblscore里面

--通过这种方式复制，只能复制表中的数据，以及列的名字和数据类型。对于约束，不会复制过来。下同

2.有条件复制并且表没事先建好

SELECT*INTO newTblscore FROM tblscore WHERE 4>3

--若where后面的表达式成立，则复制全部的表

--若where后面的表不成立，则只复制表结构

--这样做可以只复制表结构，但效率并不高

SELECT TOP 0 *INTO new1tblscore FROMtblscore

--只复制表的结构的话，这个方法效率高，建议使用

3.表要事先建好

insertinto backupStudent select * from students

--backupstudent要事先建好

12 字符串函数（不改变表的内容，只改变显示的内容）

1、 SELECT LEN('你好哈，我不好')

--len计算字符串的长度

--计算时不包含字符右边的（再往右无字符）空格

2、SELECT DATALENGTH('你好a')

--datalength计算字符串的字节

--计算时包含右面的空格

3、SELECT LOWER('AFASdf')

--lower大写转小写，小写不变

SELECT UPPER('asfWE')

--upper小写转大写，大写不变

4、PRINT LTRIM(' 哈哈 ')

SELECT LTRIM(' 哈哈 ')

--ltrim字符串左侧的空格去掉

--print以消息的形式显示

5、SELECT RTRIM(' 哈哈 ')

--rtrim字符串右侧的空格去掉

--配合使用和ltrim

6、PRINT LEFT ('你好哈',2)

--left从左侧开始留下两个字符，其余的切掉

7、SELECT RIGHT ('你好哈',2)

--right从右侧开始留下两个字符，其余的切掉

8、SELECT SUBSTRING(umsg,2,4) FROM t6

--substring显示从第二个字符开始到第三个字符

13 Case函数

SELECT tsid AS 学号,

tEnglish AS 英语,--所要显示的列名，逗号不要忘了

等级=--新增临时列的名称

( --括号可以不加

CASE

WHEN tenglish>90 THEN 'A'--中间部分为条件，后面部分为满足条件后显示的内容

WHEN tenglish>80 THEN 'B'—then后面的数据类型要一致

WHEN tenglish>70 THEN 'C'—没有逗号分隔

WHEN tenglish IS NULL THEN '缺考'

ELSE 'D'

END --以此结束，case语句

)

FROM TblScore

select studentId,成绩=

(

case

when english between 90 and 100 then 'A'—and表示区间

when english between 80 and 89 then 'B'

when english between 70 and 79 then 'C'

when english between 60 and 69 then 'D'

when english < 60 then 'E'

else '缺考'

end

)

from score

SELECT

A=CASE

WHEN A>B THEN A ELSE B-----A的值大于B的值显示A的值，否则显示B的值

END ,--配套的case开头，end结束

B=CASE

WHEN B>C THEN B ELSE C-----case在一个Select语句中可以多次使用

END

FROM t8

SELECT 商品名称,销售总额=sum(销售数量*销售价格),

等级=

CASE

WHEN sum(销售数量*销售价格)>10000 THEN '金牌'

WHEN sum(销售数量*销售价格) BETWEEN7000 AND 10000 THEN '银牌'

ELSE '铜牌'

END

FROM Myorders

GROUP BY 商品名称

ORDER BY 销售总额 DESC

14 子查询

1.简介：把一个查询的结果在另一个查询中使用就叫子查询。(将一个查询语句做为一个结果集供其他SQL语句使用) 就像使用普通的表一样，被当作结果集的查询语句被称为子查询。所有可以使用表的地方几乎都可以使用子查询来代替。

2.分类：

独立子查询(子查询可以独立运行)

SELECT

总人数=(SELECTCOUNT(*)FROM tblstudent),

男同学=(SELECTCOUNT(*) FROM tblstudent WHERE tsgender='男'),

平均成绩=(SELECT AVG(tmath)FROM tblscore)—这个没有使用from

相关子查询(子查询中引用了父查询中的结果)

SELECT tsname FROM (SELECT tsname,tsid FROMtblstudent) AS tbl

SELECT * FROM (SELECT * FROM tblstudent WHEREtsgender='男') AS tbl WHERE tbl.tsage=29;

SELECT * FROM tblstudent

WHERE tsclassid

IN (SELECT tclassid FROM tblclass

WHERE tclassname='高一一班' OR tclassName='高二一班')

3.注意点：

所使用的结果集需要起别名（as xxx）

可以多个表一起使用

如果子查询是多行单列的子查询，这样的子查询的结果集其实是一个集合。可以使用in关键字代替=号

15 over与rank语句和partition语句（）

15.1 over与rank

1.over简介：

Over()就是传说中的”开窗函数”，本身聚合函数只会计算一次，开窗以后就可以为每条记录都计算一次聚合了。

Over子句可以为每一行计算表达式而不是只为一行，并且over可以单独定义窗口中的排序方式，而不影响最终结果集。例如：select*,row_number() over(order by id asc) as hanghao from callrecords order by iddesc

在应用具体的聚合函数、排名函数前对行集的分区和排序。over子句，用于支持窗口的计算,所以一般与排名开窗函数、聚合开窗函数一起使用。

窗口是用户指定的一组行。开窗函数计算从窗口派生的结果集中各行的值。

以前使用聚合函数必须分组，即便没有group by 子句，也是默认将表中所有的数据分成了1组，来聚合。通过使用over子句可以在不分组的情况下实现聚合运算，在查询结果集中既包含基础行的数据也包含聚合函数的值。（可以把over()子句理解成是“后台运行的数据”，只是为了让聚合函数或者是排名函数“用一下”，并不影响实际显示的数据。在后台提供数据。）

2.over两种使用方法

（1）.over子句与排名开窗函数（row_number()）一起用，语法：over([partition by 列1] order by 列2)。必须有order by 子句

SELECT *FROM

(SELECT *,编号=ROW_NUMBER()OVER(ORDERBY customerid)

FROM customers )AS newcus----添加编号的方法

WHERE newcus.编号 BETWEEN(3-1)*5+1 AND 3*5

（2）.over子句与聚合开窗函数（rank()）一起用，语法：over([partition by 列1])不能使用order by子句了。

不能用order语句是说在整个语句的最后面，不是在括号里，因为在最后面使用时会将原本因为聚合开窗函数局部聚合的数据打乱。

SELECT *,排名=RANK()OVER(ORDERBY tmath)

FROM tblscore

//排名形式1，2，2，4，4，4，7，7，9

------------------------求百分比