插入数据

单行插入：

INSERT  [INTO]  <表名>  [列名]  VALUES  <值列表>

例：INSERT INTO Authors (AuthorName,Sex,Age,Email,TelPhone,City)

     VALUES (‘吴玉鹏‘,1,47,‘wyp@sohu.com‘,‘01090876529‘,‘北京‘)

 

多行插入：

（1） INSERT INTO  <表2>    [列名]    SELECT  <列名>  FROM  <表1>

         <表2>必须事先存在

      例：INSERT INTO AuthorsInfo (作者姓名,年龄,电子邮件,所在城市)

             SELECT AuthorName,Age,Email,City

                FROM Authors

（2）SELECT  <列名> 

     INTO  <新表>

     FROM  <原始表>

       <新表>不能事先存在，它是在执行该语句时系统自动创建的。

     例：SELECT AuthorName,Age,Email,City

         INTO  NewAuthorsName

         FROM  Authors

（3）Union关键字插入多行数据

     INSERT  [INTO]  <表名>  [列名]

     SELECT  <值列表> UNION

     SELECT  <值列表> UNION

     SELECT  <值列表>

     例：INSERT INTO Authors (AuthorName, Sex, Age, Email, TelPhone)

     SELECT ‘张笑林‘,1,30,‘zxl@163.com‘,‘02067839876‘ UNION

     SELECT ‘李辉‘,0,52,‘lh@126.com‘,‘02167345987‘ UNION

     SELECT ‘洪海波‘,1,40,‘hhb@163.com‘,‘031189654329‘

 

插入标识列数据：

SELECT  IDENTITY(数据类型, 标识种子, 标识增量)  as 列名 

INTO  <新表>

FROM  <原始表>

例：要将Authors表中“作者姓名、年龄、电子邮件”数据存储到一个新表中，并且

    要求插入新表时自动生成标识列字段。

    SELECT IDENTITY(int, 1, 1) as Id, AuthorName,Age,Email,City

    INTO NewAuthorsName

    FROM Authors

 

 

        查询数据

查询有条件限制的数据：

SELECT    <列名>

FROM      <表名>

[WHERE    <查询条件表达式>]

[ORDER BY <排序的列名>[ASC或DESC]]

例：SELECT AuthorName, Email, Age

    FROM Authors

    WHERE City=‘北京‘

    ORDER BY  AuthorID DESC

 

查询数据表中部分列：

SELECT AuthorName, Age, Sex, City

FROM Authors

 

使用“*”查找所有列：

SELECT  *   FROM  Authors

 

使用WHERE过滤部分行数据：

SELECT AuthorName, Age, Sex, City

FROM    Authors

WHERE City=‘北京’

 

使用As子句将列转别名：

SELECT AuthorName as ‘作者姓名‘, Email as 电子邮件, Age as ‘年龄‘

FROM  Authors

转别名还可以使用“=”

SELECT ‘作者姓名‘=AuthorName, 电子邮件=Email, 年龄=Age

FROM Authors

 

使用“+”连接多个字段，合并成一列：

SELECT AuthorName +‘   ‘+ City as 作者居住城市

FROM  Authors

 

使用Top关键字查询表中限定行数：

SELECT TOP 5 *  FROM  Authors

使用PERCENT按百分比取数据：

SELECT Top 30 PERCENT * FROM  Authors

 

在查询结果中使用常量列：

SELECT AuthorName as 作者姓名, 80 as 满意度

FROM Authors

 

使用DISTINCT关键字屏蔽重复数据：

SELECT DISTINCT City FROM Authors

 

使用IS  NULL查询空数据：

SELECT AuthorName, Birthday, City

FROM    Authors

WHERE Birthday IS NULL

如果填写了生日信息，然后又删了，如何查询出来?：

SELECT AuthorName, Birthday, City

FROM Authors

WHERE Birthday IS NULL OR Birthday=‘‘

 

UNION联合查询:

SELECT CustomerName as 姓名, City as 居住城市

FROM Customers UNION

SELECT AuthorName as 姓名, City as 居住城市

FROM Authors

 

LIKE进行模糊查询：

WHERE  <列名>  [NOT]  LIKE  <字符表达式>

 

IN进行模糊查询：

WHERE  <列名>  IN  <[常量列表]>

例：查询居住地是“北京”、“上海”、“西安“、“成都”的作者

    SELECT AuthorName, City

    FROM Authors

    WHERE City IN (‘北京‘,‘上海‘,‘西安‘,‘成都‘)

 

BETWEEN...And...进行模糊查询(在指定范围内)：

WHERE  <列名>  [NOT]  BETWEEN  <起始表达式> And  <结束表达式>

例：查找单价在20到99元的图书信息

    SELECT BookCode, BookName, UnitPrice

    FROM Books

    WHERE UnitPrice Between 20 and 99

 

 

         修改数据

修改数据：

UPDATE  <表名>  SET  <列名=值>  [WHERE <更新条件>]

例：（1）将作者表中所有作者的居住城市都更改成“北京”

         UPDATE Authors SET City=‘北京’

    （2）将作者表中AuthorID为2的作者年龄更改成36岁

         UPDATE Authors SET Age=36 WHERE AuthorID=2

    （3）更新语句中还可以使用表达式

      UPDATE Books SET price=price-2

          WHERE CatagoryID=1 OR CatagoryID=2

 

 

           删除数据

删除数据：

（1）DELETE  FROM  <表名>  [WHERE <更新条件>]

     例：DELETE FROM Books WHERE BookCode=‘981267‘

（2）Truncate Table <表名>

      Truncate Table只删除表中的数据行

 

 

         排序

查询所有图书信息并按照价格字段升序显示：

SELECT BookName, Description, UnitPrice

FROM Books

ORDER By UnitPrice

例：查询单价在50元以上的图书信息，要求价格以8折显示，并按价格降序排列：

    SELECT BookName as 图书名, Description as 描述, UnitPrice * 0.8 as 单价

    From Books

    WHERE UnitPrice > 50

    ORDER By UnitPrice DESC

 

查询结果还可以按多列进行排序：

SELECT BookName as 图书名, CategoryID as 类别编号, UnitPrice as 单价

From Books

ORDER By CategoryID, UnitPrice DESC

例：（1）查询电话号码是13920298734的会员信息，要求显示会员名称、居住城市、会员等级。

         SELECT CustomerName as 会员名称, City as 居住城市, Grade as 会员等级

     FROM Customers

     WHERE TelPhone=‘13920298734‘

    （2）编写代码查询积分在500分以上的会员信息，并按积分降序排列。

     SELECT * FROM Customers

     Where Upoint>=500

     ORDER By Upoint DESC

 

 

         聚合函数

总积分 SUM：

SELECT SUM(Upoint) as 总积分 FROM Customers

 

平均分 AVG：

SELECT AVG(upoint) as 平均积分 FROM Customers

 

最高分和最低分 MAX和MIN ：

SELECT MAX(upoint) as 最高积分, MAX(upoint) as 最低积分 FROM Customers

MAX和MIN除了计算数值列，还可以用于计算字符型以及日期时间类型数据列：

SELECT MAX(Birthday) as 最大日期 FROM Customers

 

COUNT(表达式)    返回结果集的非空行数。

COUNT(*)：返回表中所有数据行的记录数：

例：SELECT COUNT(*) as 人数 FROM Customers WHERE upoint >300

COUNT(列名)：返回指定列非空值个数。

例：SELECT COUNT(Address) as 个数 FROM Customers

 

 

        分组统计

SELECT   <字段列表>   [聚合函数]

FROM  表

[WHERE]  条件

GROUP BY  <字段列表>

 

HAVING子句对分组结果再次过滤：

SELECT  City as 居住城市, Sex as 性别, Count(*)  as 会员人数

FROM Customers

Group By City, Sex

HAVING Count(*) >= 2

 

 

          多表关联

内连接：

SELECT <列名>

FROM 表1  INNER  JOIN  表2

ON 表1.列名  条件运算符  表2.列名

[WHERE 条件]

[ORDER BY  排序列]

例：SELECT b.BookCode as 图书编号, b.BookName as 图书名称, a.AuthorName as 作者姓名

    FROM Books as b INNER JOIN Authors as a

    ON b.AuthorID = a.AuthorID

    或：

    SELECT b.BookCode as 图书编号, b.BookName as 图书名称, a.AuthorName as 作者姓名

    FROM Books b,  Authors a

    WHERE b.AuthorID = a.AuthorID

 

多表内连接：

SELECT <列名>

FROM （表1  INNER  JOIN  表2

ON 表1.列名  条件运算符  表2.列名)

INNER JOIN  表3

ON 表1.列名  条件运算符  表3.列名

……

[WHERE 条件]

[ORDER BY  排序列]

例：SELECT b.BookCode as 图书编号, c.PublisherName as 出版商名,b.BookName as 图书名称,a.AuthorName as 作者姓名

    FROM ( Books b INNER JOIN  Authors a

    ON b.AuthorID=a.AuthorID)  INNER JOIN Publisher c

    ON b.PublisherID=c.PublisherID

 

左外连接：

SELECT <列名>

FROM 左表  LEFT  [OUTER] JOIN  右表

ON 左表.列名  条件运算符  右表.列名

[WHERE 条件]

[ORDER BY  排序列]

例：SELECT b.BookCode as 图书编号, b.BookName as 图书名称, a.AuthorName as 作者姓名

    FROM Books b LEFT JOIN  Authors a

    ON b.AuthorID=a.AuthorID

 

右外连接：

SELECT <列名>

FROM 左表  RIGHT  [OUTER] JOIN  右表

ON 左表.列名  条件运算符  右表.列名

[WHERE 条件]

[ORDER BY  排序列]

例：SELECT b.BookCode as 图书编号, b.BookName as 图书名称, a.AuthorName as 作者姓名

    FROM Books b RIGHT JOIN  Authors a

    ON b.AuthorID=a.AuthorID

 

完全连接：

SELECT <列名>

FROM 左表  FULL  [OUTER] JOIN  右表

ON 左表.列名  条件运算符  右表.列名

[WHERE 条件]

[ORDER BY  排序列]

例：SELECT b.BookCode as 图书编号, b.BookName as 图书名称, a.AuthorName as 作者姓名

    FROM Books b FULL JOIN  Authors a

    ON b.AuthorID=a.AuthorID

 

 

常用的条件运算符:

运算符    含义       示例

  =     等于       AuthorID＝5

  >    大于       Price>50

  <    小于       Quantity<20

  >=    大于等于   Age>=50

  <=    小于等于   Price<=100

  <>    不等于       Birthday<>‘1970-10-10’

 

 

常与LIKE关键字配合使用，表示一个模糊的范围：

运算符    含义                                  示例

  %    任意长度的字符串            Email Like ‘%@%.com’

‘_’    任意一个字符                    AuthorName Like ‘张_’

 []    在指定范围内的一个字符            A Like ‘A6C8[1-5]’

 [^]    不在指定范围内的任意一个字符    A Like ‘A6C8[^1-6]’

 

 

逻辑运算符：

运算符    含义                                      示例

 And    连接的两个条件表达式都为True，           A > 90 And B >100

        表达式结果才为True，否则为False   

 Or    连接的两个条件表达式中有一个为True，     A >90 Or B >100

        则表达式结果就为True   

 Not    否定条件                             Not (A > 90)

 

 

         函数

字符串函数：

函数名                功能描述                        举例

 LEN         返回指定字符串的字符个数(而不是字节)，        SELECT LEN(‘李丽然作者‘) 

             其中不包含尾随空格                                 返回:5

UPPER         将小写字符转换成大写字符                            SELECT UPPER(‘book图书表‘) 

                                返回:BOOK图书表

LTRIM         返回去掉左侧空格的字符串                            SELECT LTRIM(‘  Authors‘)   

                                返回: Authors

CHARINDEX    查找一个指定的字符串在另一个字符串中的起始位置    SELECT CHARINDEX(‘L‘, ‘HELLO‘, 1)   

                                返回:3

 LEFT         返回字符串中从左边开指定个数的字符                    SELECT LEFT(‘zhangsan‘, 2)   

                                返回:zh

DATALENGTH   返回指定字符串的字节数                            SELECT DATALENGTH(‘中国人‘)   

                                返回：6

REPLACE         替换一个字符串中的字符                            SELECT REPLACE(‘我爱我的家乡家乡‘, ‘家乡‘, ‘学校‘)   

                                返回: 我爱我的学校学校

SBUSTRING    从字符串串的起始位置连续取指定个数的子串            SELECT SUBSTRING(‘我爱我的家乡‘,3, 2)

                                                                返回：我的

 

数学函数：

函数名         功能描述                                      举例

 ABS      返回表达式绝对值                         SELECT ABS(-90)

                                 返回：90

ROUND      按指定的精度进行四舍五入             SELECT ROUND(56.629, 2)

                             返回：56.630

SQRT      返回指定表达式的平方根             SELECT SQRT(9)

                             返回：3

FLOOR      返回小于或等于指定数值表达式的最大整数    SELECT FLOOR(23.9)

                            返回: 23

CEILING      返回大于或等于指定数值表达式的最小整数    SELECT CEILING(23.9)

                            返回：24

 

日期函数：

函数名            功能描述                                 举例

GETDATE        得到当前系统日期                SELECT GETDATE()

                            返回：系统当前日期

DATEPART    返回指定日期部分的整数            SELECTDATEPART(dd, ‘01/09/2003‘)

                            返回：9

DATEADD        在指定的日期上累加数值得到新的日期        SELECT DATEADD(yyyy, 4, ‘01/09/2003‘)

                            返回：2007-01-09

DATEDIFF    返回两个日期的差值                SELECT DATEDIFF(dd, ‘02/05/2003‘, ‘02/09/2005‘)

                            返回：735

DATENAME    返回指定日期部分的字符串            SELECT DATENAME(dw, ‘02/02/2009‘)

                            返回: 星期一

YEAR        返回指定日期“年”部分整数            SELECT YEAR(GETDATE())

                            返回：当前年份整数

MONTH        返回指定日期“月”部分整数            SELECT MONTH(GETDATE())

                            返回：当前月份整数

DAY        返回指定日期“日”部分整数            SELECT DAY(GETDATE())

                            返回：当前日期整数

 

转换函数：

函数名    功能描述          举例

CONVERT    数据类型转换：      SELECT CONVERT(DateTime, ‘2020-09-09‘)                           

        将第2个参数转       返回: 2020-09-09 00:00:00.000

        换为第一个参数

        所指定的类型。        SELECT CONVERT(varchar(11), GETDATE(), 121)

                返回：2010-03-24

 

                 常用日期格式：  121：日期格式为 yyyy-mm-dd hh:mi:ss.mmm  111：日期格式为 yy/mm/dd

                SELECT CONVERT(varchar(5), 92.89)

                返回：92.89

 

 

创建表student：

create table tbl_student

(

    //列名、数据类型

    id int identity(1,1),

    stu_no char(8),

    stu_sex char(1),

    stu_age int,

    stu_address varchar(20),

)

 

主键约束：

alter table tbl_student

   add constraint PK_id primary key(id)

 

默认约束：

alter table tbl_student

    add constraint CK_sex check(stu_sex in(‘0‘,‘1‘))

 

唯一约束：

alter table tbl_student

    add constraint DF_address default(‘山东‘)for stu_address

 

检查约束：

alter table tbl_student

    add constraint UK_stuno unique(stu_no)

 

 

CHECK约束命名规范为CK_字段

年龄：Age>0 AND Age<=120

      年龄在0—120之间的

邮箱：Email Like ‘%@%‘

姓名：AuthorName Like‘张_‘

      例如：张三

A Like ‘A6C8[1-5]’ 表示以A6C8开头并且最后一位是1到5之间的任意字符的字符串

      例：A6C83

A Like ‘A6C8[^1-6]’ 表示以A6C8开头并且最后一位不是1到6之间的任意字符结尾的字符串

      例：A6C89

Fax：([Fax] like‘[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]‘ or [Fax] like ‘[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]‘)

 

将城市设置成默认北京

修改表中点到City下面有列属性，常规中默认值或绑定一栏写（‘北京’）