MySQL Hand Book

Word count: 10.8kReading time: 49 min

 1994/10/09   Share

数据库SQL指令笔记

本文档为本人学习sql的笔记（虽然我能保证在我的电脑上每部都成功，但若您将本文作为参考也可能遇到我没有遇到的问题）

本文标题级别并不是按照知识系统来划分，而是按照我所学习的资料的章节所划分的

安装

搜索mysql 5.5.27 win64 msi

配置

配置程序在server/bin的文件夹下（如果你安装最后一步没有点开始配置的话来这里启动）
端口3306
字符集utf8

服务器操作

启动mysql服务器

方法一

根据配置时候设置的服务名，在我的电脑右键，管理，服务和应用程序，服务，里找到mysql5.5(配置的时候设置的服务名为mysql5.5)，右键属性，启动（停止的话就点停止）

方法二

管理员打开cmd命令提示符

> net start mysql5.5

> net stop mysql5.5

后面的操作都要保证服务器是开着的

客户端登录和退出

因为环境变量已经配置好了所以在cmd命令提示符内是可以直接使用mysql命令的（这一步不需要管理员模式启动）

启动指令1 在-p后面直接输入自己的密码，一步启动

1	>mysql -uroot -p12345

指令2 输入后会提示输入密码，这样输入的时候密码都是*，旁边的人看不见

1	>mysql -uroot -p

u means user, p means password（记得自己安装时候设置的root密码，后面也可以添加其他用户)

输入下面的代码来退出

1	mysql>exit

上面是连接本机3306端口（根据配置）的指令，想要连接别的主机，使用下面的命令:

1	>mysql -h localhost -P3306 -u root -p12345

-h means host 除了写localhost，还可以写局域网地址，比如192.168.***.***

-P means port（端口）注意这里是大写，和password 分开

另外 -h -u -P这些后面的空格可加可不加，但是-p(密码)后面一定不能加空格

注意提示： Commands end with ; or \g.

SQL 命令注意事项

不区分大小写
库名，表名，字段名建议大写，其他建议小写
加#单行注释
加— 单行注释（注意连杠后又空格
\/*多行注释*\/多行注释间不能嵌套注释
可以换行写，和R规则差不多，不能劈开关键字

常用命令

更改服务器字符集为gbk（注意客户端字符集和服务器字符集不一样）

1	mysql> set names gbk;

显示存在的数据库

1	mysql> show databaeses;

使用数据库 (mysql是数据库名)

1	mysql> use mysql;

显示数据库中的表

1	mysql> show tables;

显示其他数据库中的表（其实就是用了下from语句跳过了use test;命令，这个命令也不会改变当前使用的数据库）（test也是数据库名）

1	mysql> show tables from test;

查看当前使用的数据库

1	mysql> select database(); #注意不是databases

建立表 (请不要在mysql这样储存系统信息的数据库中建表，在test里建就行了) （stuinfo是表名）

mysql> create table stuinfo(
    -> stuid int, #stuid是变量名
    -> stuname varchar(20), # 20是控制字符类变量(varchar)的长度
    -> gender char, #char 代表单个字符
    -> borndate datetime);

查看表结构(stuinfo是表名)

1	mysql> desc stuinfo;

查看表数据

1	mysql> select * from stuinfo;

插入数据

1	mysql> insert into stuinfo values(1, '张无忌', '男','1998-3-3');

更新数据

1	mysql> update stuinfo set borndate='1980-1-1' where stuname = '张三忌';

删除数据

1	mysql> delete from stuinfo where stuid = 1;

删除表

1	mysql> drop table stuinfo;

SQL语言介绍

命令（语言）分类

DDL (Data Defination Language):定义数据库对象如库，表，列等(create/ drop/ alter)
DML (Data Manipulation Language):数据的增删改 (insert/update/delete)
DCL (Data Control Language): 控制数据库各种权限，事务(TCL Transaction Control Language)等
DQL (Data Query Language): 数据查询语言(select)

基础查询

以下内容均在SQLyog上进行，数据库文件为myemployees，请自行读取

语法：
select 查询列表 from 表名;

查询的结果是一个虚拟的表（不会存储
select 后面的查询列表可以由多个部分组成，用’，’隔开
执行顺序：

查看from后的表是否存在

查看select后面的变量名是否存在

查询列表可以是：字段，表达式，常量，函数等

查询常量

1	SELECT 1000;

查询表达式

1	SELECT 100%3;

由于常量和表达式不需要存在于任何的表（你加不加from它都是去算100%3）所以不写from也行

查询单个字段

1
2
3

SELECT `last_name` FROM `employees`;

SELECT `first_name`,`last_name`,`first_name` FROM `employees`;

注意反引号`和单引号’的区别
SQLyog 小知识：快捷键F12自动换行并对齐

查询所有字段

1	SELECT * FROM `employees`;

查询函数（调用函数，获取返回值）

1
2
3

SELECT DATABASE();
SELECT VERSION();
SELECT USER();

起别名（因为查询函数返回的表的列名是函数名，不好看）

方法一： AS （推荐）

1
2
3

SELECT USER() AS "用户名";
SELECT USER() AS `用户名`;
SELECT `last_name` AS `姓 名`, salary * 12 AS "年收入" FROM `employees`;

方法二：空格

1
2
3

SELECT USER() "用户名";
SELECT USER() `用户名`;
SELECT `last_name` `姓 名` FROM `employees`;

拼接字符串（或字符串查询结果）的函数concat (类似R语言中paste)

如果null参与拼接则输出为null，可以使用IFNULL函数来回避(ifnull函数的第二个字符串参数不能有中文)
IFNULL(`var`, “string”), 如果var列中由值是null，则显示string

SELECT CONCAT(`first_name`, `last_name`) AS "姓名" FROM `employees`;

SELECT CONCAT(`first_name`, `last_name`,',',`commission_pct`) AS "姓名与奖金率" FROM `employees`;

SELECT CONCAT(`first_name`, `last_name`,',',IFNULL(`commission_pct`,"null")) AS "姓名与奖金率" FROM `employees`;

去重函数distinct(类似R语言中的unique)

比较一下去重和不去重的结果

1
2
3

SELECT `department_id` from employees;

SELECT DISTINCT `department_id` from employees;

查看表的结构

1
2
3

DESC employees;

SHOW COLUMNS FROM employees;

条件查询

语法：

SELECT 查询列表

FROM 表名

WHERE 筛选条件

执行顺序：

from

where

select

按关系表达式筛选

关系运算符包括 >, <, >=, <=, =, <>（不等于,也兼容!=但不建议）

案例：

SELECT *
FROM employees
WHERE department_id <> 100;

SELECT last_name, salary
FROM employees
WHERE salary < 15000;

按逻辑运算表达式筛选

包括： and, or, not

案例：

SELECT last_name, department_id, email
FROM employees
WHERE NOT(department_id >= 50 AND department_id<= 100)

SELECT *
FROM `employees`
WHERE `commission_pct` > 0.03 OR (`employee_id` >= 60 AND `employee_id` <= 110)

模糊查询

包括like, in, between and

like语句

一般和通配符搭配使用，对字符型数据进行部分匹配查询

#通配符

_ # 任意单个字符 （1个字符）

% # 任意多个字符 （0-n个字符）

案例

SELECT *
FROM `employees`
WHERE `last_name` LIKE '%a%'; # 包含a，不区分大小写

SELECT *
FROM `employees`
WHERE `last_name` LIKE '%e'; # 最后一个字符为e

SELECT *
FROM `employees`
WHERE `last_name` LIKE 'e%'; # 最后一个字符为e

SELECT *
FROM `employees`
WHERE `last_name` LIKE '__x%'; # 第三个字符为x

SELECT *
FROM `employees`
WHERE `last_name` LIKE '_\_%'; # 第二个字符是下划线的

SELECT *
FROM `employees`
WHERE `last_name` LIKE '_$_%' ESCAPE '$'; # 第二个字符是下划线的

小技巧： ESCAPE 是指定某字符为转义字符的命令

in 语句

用于查询某字段的值是否属于指定的列表之内
a in(value1, value2, value3)
a not in(value1, value2, value3)

案例

SELECT last_name, department_id
FROM employees
WHERE department_id in (30, 50, 90);

SELECT last_name, department_id
FROM employees
WHERE department_id = 30
OR department_id = 50
OR department_id = 90;

SELECT *
FROM employees
WHERE job_id NOT IN ('SH_CLERK', 'IT_PROG')

SELECT *
FROM employees
WHERE NOT(job_id = 'SH_CLERK'
OR job_id = 'IT_PROG');

在SQL中数值类的值直接写，非数值类的值用单引号引起来

between and 语句

判断某个字段的值是否在某个区间
between and / not between and都可以

案例

SELECT department_id, last_name
FROM employees
WHERE department_id BETWEEN 30 AND 90;

SELECT department_id, last_name
FROM employees
WHERE department_id >= 30 AND department_id <= 90;

SELECT last_name, salary, salary * 12 * (1 + IFNULL(commission_pct, 0)) AS Annual
FROM employees
WHERE salary * 12 * (1 + IFNULL(commission_pct, 0)) NOT BETWEEN 100000 AND 200000;

SELECT last_name, salary, salary * 12 * (1 + IFNULL(commission_pct, 0)) AS Annual
FROM employees
WHERE salary * 12 * (1 + IFNULL(commission_pct, 0)) < 100000 OR salary * 12 * (1 + IFNULL(commission_pct, 0)) > 200000;

区间要按照数字大小来写不能90 and 30

is null 语句

案例

SELECT *
FROM employees
WHERE commission_pct IS NULL;

SELECT *
FROM employees
WHERE commission_pct IS NOT NULL;

SELECT *
FROM employees
WHERE commission_pct = NULL; # 这种写法是错误的，因为=只能判断数值

SELECT *
FROM employees
WHERE salary = 10000; 

SELECT *
FROM employees
WHERE salary IS 10000; # 这种写法是错误的，因为IS 只能判断NULL

# 安全等于 <=> 既可以判断具体数值，也可以判断NULL

SELECT *
FROM employees
WHERE salary <=> 10000; 

SELECT *
FROM employees
WHERE commission_pct <=> NULL;

排序查询

select 查询列表 from 表名 (where 筛选条件) order by 排序列表
执行顺序:

from

where

select

order by

排序列表可以是字段，多个字段，表达式，函数，列数以及以上的组合
升序 (asc) (默认)
降序desc

按字段案例

SELECT * 
FROM employees
WHERE employee_id > 120
ORDER BY salary ASC;

SELECT * 
FROM employees
WHERE employee_id > 120
ORDER BY salary DESC;

SELECT * 
FROM employees
WHERE employee_id > 120
ORDER BY salary;

按表达式和函数案例

SELECT *, salary * 12 * (1 + IFNULL(commission_pct,0)) AS Annual
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary * 12 * (1 + IFNULL(commission_pct, 0)) DESC;

如果你认为order by后面太长了，可以按别名排序

按别名排序

where 不能按别名筛选的原因是执行顺序在select语句之前

案例

SELECT *, salary * 12 * (1 + IFNULL(commission_pct,0)) AS Annual
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY Annual DESC;

按字节（字符串）长度排序 (LENGTH 函数)

案例

SELECT LENGTH(last_name), last_name
FROM employees;

SELECT last_name
FROM employees
ORDER BY LENGTH(last_name);

组合排序

案例 (注意ORDER BY 语句后成分的顺序)

1
2
3

SELECT last_name, salary, department_id
FROM employees
ORDER BY salary ASC, department_id DESC;

按某一列（列数排序）（不常用）

案例：按第二列排序

SELECT *
FROM employees
ORDER BY first_name; # 字母会自动按abcd顺序排序

SELECT *
FROM employees
ORDER BY 2; # 字母会自动按abcd顺序排序

常见函数（单行函数：输入一个参数，返回一个结果）所有函数后最好紧贴括号

CONCAT 拼接字符

1 2	SELECT CONCAT('hello ', last_name) FROM employees;

LENGTH 获取字节长度
CHAR_LENGTH 获取字符长度（中文字符一个字符有三个字节，注意区别）
SUBSTRING(str, 起始位数, 截取长度) 截取子串，不写截取长度的话会把后面的字符串都显出来

1	SELECT SUBSTR('阿珍爱上了阿强', 1, 3); # 注意字符从1开始而不是从0开始

INSTR 获取字符第一次出现的索引

1	SELECT INSTR('阿珍爱上了阿强1阿强2阿强3','阿强');

TRIM去前后指定字符，默认是去空格

1
2
3

SELECT TRIM('   赵日  天             ') AS name_;

SELECT TRIM('6' FROM '6666赵日66天66666666666666') AS name_;

LPAD/RPAD 左填充/右填充 (强制设定列的字符长度，多删少补,删的话都是把右边多的删掉)

SELECT LPAD('赵日天', 10, 'a');
SELECT RPAD('赵日天', 10, 'a');

SELECT LPAD('赵日天', 2, 'a');
SELECT RPAD('赵日天', 2, 'a');

UPPER/LOWER 变大写变小写

SELECT 
  CONCAT(
    UPPER(SUBSTR(first_name, 1, 1)),
    LOWER(SUBSTR(first_name, 2)),
    '_',
    UPPER(last_name)
  ) AS names_ 
FROM
  employees 
ORDER BY names_ ;

STRCMP 比较两个字符大小(先比第一个字符，一样再往后比)

SELECT STRCMP('abc', 'abc')

SELECT STRCMP('abc', 'acc')

SELECT STRCMP('acc', 'abc')

SELECT STRCMP('acc', 'azc')

LEFT/RIGHT 截取字串

1
2
3

SELECT LEFT('阿珍爱上了阿强', 2);

SELECT RIGHT('阿珍爱上了阿强', 2);

分组函数/ 聚合函数 / 统计函数（将一组值经过运算返回一个field）

sum 求和
avg 求均值
min 求最小值
max 求最大值
count 计算非空字段值的个数（类似于R中的length）

SELECT sum(salary), avg(salary), min(salary), max(salary), count(salary) AS people_who_have_salary
FROM employees;

SELECT count(*)
FROM employees; #返回表的总行数（除非有一行全是空值）

SELECT count(*)
FROM employees
WHERE department_id = 30;

SELECT count(1) #相当于往表里面加了一列1，然后查查有几行1
FROM employees; #返回表的总行数（除非有一行全是空值）

count 和 distinct 一起使用可以达到R语言中length(unique(变量))的效果

1 2	SELECT COUNT(DISTINCT(department_id)) FROM employees; #求部门个数

分组查询

引入：如何查询每个部门的工资和(field = c(department_id, sum_salary))
解决方案：将总表按department_id分成若干个小表（小组），然后对每个小表进行查询

1
2
3

SELECT department_id, SUM(salary) # department_id 是null的说明他没有部门
FROM employees
GROUP BY department_id;

完整语法：

SELECT 查询列表(一般是分组函数)

FROM 表名

WHERE 筛选条件

GROUP BY 分组列表

特点1：查询列表往往是分组函数和被分组的字段

SELECT job_id, AVG(salary)
FROM employees
GROUP BY job_id;

SELECT manager_id, COUNT(1) # 每个领导手下有多少人
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id;

SELECT department_id, MAX(salary) AS "zuigaogongzi"
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

SELECT manager_id, AVG(salary) # 每个领导手下有奖金的员工平均工资
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

特点2：由于select函数执行再where之后，所以where后不能加分组函数
HAVING 相当于对分组后的结果表进行SELECT
其实放在where后面的判断放在having后面也可以，但是效率会低，能放where后面就放where后面，先筛选掉一部分，效率会高一点

# 查询员工数大于5的部门编号：

SELECT COUNT(1), department_id
FROM employees
GROUP BY department_id
HAVING COUNT(1) > 5;

# 每个工种有奖金的员工的最高工资大于12000的工种编号和最高工资

SELECT job_id, MAX(salary) AS 'zui_gao_gong_zi'
FROM employees
WHERE commission_pct > 0
GROUP BY job_id
HAVING MAX(salary) > 12000;

# 领导编号大于102的每个领导手下的最低工资大于5000的领导编号和最低工资

SELECT manager_id, MIN(salary)
FROM employees
WHERE manager_id > 102
GROUP BY manager_id
HAVING MIN(salary) > 5000;

SELECT MAX(salary), job_id
FROM employees
WHERE commission_pct IS NULL
GROUP BY job_id
HAVING MAX(salary) > 6000
ORDER BY MAX(salary) ASC;

按多个字段分组

SELECT job_id, department_id, MIN(salary)
FROM employees
GROUP BY job_id, department_id
ORDER BY MIN(salary) DESC;

基础查询总结

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

顺序：

from
where
group
having
select
order by

连接查询

等值连接

1
2
3

SELECT 查询列表
FROM 表名1, 表名2
WHERE 等值连接的连接条件

特点：为了解决表中字段重名的问题，往往为表起别名，提高语义性
表的顺序无要求
连接后一定把反引号加好

查询员工名和部门名

起完别名后 select一定用别名. 因为执行顺序

SELECT e.last_name, d.`department_name`
FROM employees AS `e`, departments AS `d`
WHERE e.`department_id` = d.`department_id`;

SELECT `department_name`, `city`
FROM departments d, locations l
WHERE d.`location_id` = l.`location_id` AND d.`department_id` > 100;

SELECT `last_name`, `department_name`
FROM departments d, employees e
WHERE d.`department_id` = e.`department_id` AND e.`commission_pct` IS NOT NULL;

SELECT department_name, city
FROM departments d, locations l
WHERE d.`location_id` = l.`location_id`
AND city LIKE '_o%';

再加上分组连接后一定把反引号加好

SELECT COUNT(1), l.city
FROM departments d, locations l
WHERE d.`location_id` = l.`location_id`
GROUP BY city;

SELECT e.`manager_id`, d.`department_name`, MIN(e.`salary`)# MIN(salary)
FROM employees e, departments d
WHERE e.`commission_pct` IS NOT NULL AND e.`department_id` = d.`department_id`
GROUP BY e.`department_id`, e.`manager_id`;
ORDER BY MIN(e.`salary`) DESC;

自己尝试一下三表连接

非等值连接

连接条件不是’=’

案例：员工的工资和工资级别

下面是生成工资级别表的代码，先不要管什么意思

CREATE TABLE job_grades
(grade_level VARCHAR(3),
lowest_sal INT,
highest_sal INT);

INSERT INTO job_grades
VALUES ('A', 1000, 2999);

INSERT INTO job_grades
VALUES ('B', 3000, 5999);

INSERT INTO job_grades
VALUES ('C', 6000, 9999);

INSERT INTO job_grades
VALUES ('D', 10000, 14999);

INSERT INTO job_grades
VALUES ('E', 15000, 24999);

INSERT INTO job_grades
VALUES ('F', 25000, 40000);

SELECT * FROM job_grades;

1
2
3

SELECT employee_id, salary, grade_level
FROM employees e, job_grades j
WHERE salary BETWEEN j.`lowest_sal` AND j.`highest_sal`;

自连接

自己等值连接自己（适合像静态链表一样的表）

案例：查询员工名和上级的名称

SELECT e1.`last_name`, e2.`last_name` AS "BOSS_NAME"
FROM employees AS e1, employees AS e2
WHERE e1.`manager_id` = e2.`employee_id`;

/*等效于*/

SELECT e1.`last_name`, e2.`last_name` AS "BOSS_NAME"
FROM employees AS e1 inner join employees AS e2
WHERE e1.`manager_id` = e2.`employee_id`;

外连接 join (左连接右连接)

与内连接的辨析：

本身内连接是不分主表和从表的，但我们就这样说了。比如说我们按照员工部门id匹配部门名称，内连接返回的表格中只会有员工存在部门id的员工信息。而外连接会返回所有员工信息，对于那些没有部门的，他们的部门名称会是一个空值。某种程度上来说，外连接比内连接适用性更好。一般外连接用于主表中有，但从表中没有的记录。

特点：存在主从表，所以表顺序不能任意调换
左连接：左边为主表；右连接：右边为主表
当主表中的一行对应从表中的多行时，会自动复制所需数量的主表的某一行，详情可以看下面的案例的方法2

SELECT 查询列表

FROM 表1 别名

LEFT//RIGHT//FILL (OUTER) JOIN 表2 别名 # 选LEFT表1是主表，选RIGHT表2是主表 # FILL 的意思是，有些从表中的行匹配不到主表上，但是也显示出来（即全外连接）

ON 连接条件

WHERE 筛选条件;

SELECT e.`last_name`, d.`department_name`
FROM employees e
LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

/*等效于*/

SELECT e.`last_name`, d.`department_name`
FROM employees e
JOIN departments d
ON e.`department_id` = d.`department_id`;

SELECT e.`last_name`, d.`department_name`
FROM employees e
LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL;

SELECT e.`last_name`, d.`department_name`
FROM departments d
RIGHT JOIN employees e
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL;

案例：显示哪个部门没有员工

# 这是我自己想的用嵌套查询的解决方案
SELECT v.`num`, d.`department_name`
FROM departments d
LEFT JOIN( 
SELECT COUNT(1) AS `num`, department_id
FROM employees
GROUP BY department_id )
 v
ON v.`department_id` = d.`department_id`;

# 教程的方法
SELECT d.*, e.*
FROM departments d
LEFT JOIN employees e ON d.`department_id` = e.`department_id`;

SELECT d.`department_id`
FROM departments d
LEFT JOIN employees e ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;

子查询嵌套查询

主查询 = 外查询，子查询 = 内查询
需要表的地方用()括起来一个SELECT查询
不一定只有SELECT语句才会用嵌套查询，INCERT， CREATE 等命令中也会出现
嵌套查询可以出现在：

SELECT后面，要求：子查询的结果为单行单列（标量子查询）

FROM后面，要求：子查询的结果可以为多行多列

WHERE后面，要求：子查询结果必须为单列（单行子查询，多行子查询）

EXIST后面，要求子查询结果必须为单列（相关子查询）

特点：

子查询放在条件中必须放在条件的右侧

1
2
3

department_id > (SELECT...) # 正确

(SELECT ...) < department_id # 错误

子查询一般放在小括号中
子查询的执行有限执行括号内的
单行子查询对应使用单行操作符（关系运算符）: > < >= <= = <>
多行子查询对应了多行操作符： any / in / some / all

单行子查询

案例：查询和员工zoltkey相同部门的员工姓名和工资

SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey';

SELECT last_name, salary
FROM employees
WHERE department_id = (
  SELECT department_id
  FROM employees
  WHERE last_name = 'Zlotkey'
);

案例：查询工资比平均工资高的员工号，姓名和工资

SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
);

案例：删选最低工资比部门id=50的最低工资高的各部门的最低工资

SELECT MIN(salary), department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
  SELECT MIN(salary)
  FROM employees
  WHERE department_id = 50
);

多行子查询

in 查询某字段是否再制定列表内 in(30, 50, 70) / in(select…)
any/some 判断某字段的值是否满足其中任意一个 40 > any(30, 50, 70) -> TRUE
all 判断某字段的值是否满足其中所有的 40 > all(30, 50 , 70) -> FALSE

案例：查询location_id是1400或1700的部门种所有的员工姓名

SELECT last_name
FROM employees
WHERE department_id IN(
  SELECT department_id
  FROM departments
  WHERE location_id IN (1400, 1700)
);

案例返回其他部门重比job_id 为 ‘IT_PROG’部门任一工资低的员工的员工号姓名 jobid 和salary

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE job_id <> 'IT_PROG' AND salary < (

SELECT MAX(salary)
FROM employees
WHERE job_id = 'IT_PROG'

)
ORDER BY salary DESC;

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE job_id <> 'IT_PROG' AND salary < ANY(

SELECT DISTINCT(salary)
FROM employees
WHERE job_id = 'IT_PROG'

)
ORDER BY salary DESC;

分页查询

比如我们要让结果每页显示size条结果，然后显示第page页

1
2
3

SELECT last_name
FROM employees
LIMIT (page-1)*size, size; # 先算好数字

联合查询

SELECT last_name
FROM employees
WHERE salary > 5000
UNION
SELECT last_name
FROM employees
WHERE salary < 2000;

# 比较下面两段代码的区别

SELECT 1, 'a'
UNION
SELECT 1, 'a';

/*上面的自动去重，下面的带着重复*/

SELECT 1, 'a'
UNION ALL
SELECT 1, 'a';

# 比较下面两段代码的区别 (提醒我们一定要对好顺序，还有column的数量必须一样)

SELECT 1, 'a'
UNION
SELECT 2, 'a';

SELECT 1, 'a'
UNION
SELECT 'a', '2';

DDL 语言 data define language

数据定义语言: 对数据库和表的管理操作

库的管理

创建数据库

1
2
3

CREATE DATABASE stuDB;

CREATE DATABASE IF NOT EXISTS stuDB; # 提高代码容错性

删除数据库

1
2
3

DROP DATABASE stuDB;

DROP DATABASE IF EXISTS stuDB;

表的管理

创建表

语法： create table t_name(
field_name field_type field_property, # field_property可写可不写
field_name field_type field_property,
field_name field_type field_property
)

CREATE TABLE IF NOT EXISTS stuinfo(
  stuid INT,
  stuname VARCHAR(20),
  stugender CHAR,
  email VARCHAR(20),
  studob DATETIME
);

DESC stuinfo;

SQL 常用变量类型

int 整型（tinyint smallint int bigint）
double/float 浮点型 double(5, 2)表示最多5为必须有两位小数即 999.99 到 -999.99 中的数字 float 为单精度，double为双精度
decimal 浮点型，表示钱方面的使用这个类型，应为不会出现精度缺失的问题（精度最高）
char 固定长度字符串类型 char(4) 不管输入什么，最终储存就是4个字符长度括号可以省略，默认是1
varchar 可变长度字符类型 varchar(20) 括号不能省略
text 字符串类型，可储存较长文本
blob 字节类型（二进制型） tinyblob mediumblob longblob 用于存储图片数据
date 日期 yyyy-mm-dd
time 时间 hh:mm:ss
timestamp / datetime: yyyymmdd hhmmss (datetime> 1900-1-1, 8字节) (19700101 < timestamp <20381231, 4字节)

以下内容是在备考计算机三级数据库时学习的

高级查询

SELECT [DISTINCT] [TOP n] select_list
[INTO new_table]
[FROM table_source]
[WHERE search_condition]
[GROUP BY group_by_expression] # 分组查询
[HAVING search_condition] # 聚合查询
[ORDER BY order_expresion [ASC|DSC]]
[COMPUTE expression] # 尾行汇总

TOP 只查看前多少行

TOP n [percent][WITH TIES]

Top n # 前n行

Top n [percent] #前n%行

[WITH TIES] # 允许并列

CASE 分情况显示不同类型的数据

分为简单CASE函数和搜索CASE函数

CASE

WHEN 布尔表达式1 then 结果表达式1 # 布尔(boolean)表达式指返回值为ture或false的表达式

WHEN boolen2 then result2

...

WHEN boolenn then resultn

[ELSE resultn+1]

END

SELECT a.GoodsID, 商品销售类别 = CASE
WHEN COUNT(b.GoodsID)>10 THEN '热门商品'
WHEN COUNT(b.GoodsID)BETWEEN 1 AND 10 THEN '普通商品'
ELSE '滞销商品'
END
FROM Table_Goods a LEFT JOIN Table_Sales b 
ON a.GoodsID=b.GoodsID GROUP BY a.GoodsID

INTO 将查询结果保存在新表中

表前面加一个#号代表局部临时表 (当前查询1次有效)

表前面加两个#号代表全局临时表

只有表名为永久表

1
2
3

SELECT * INTO #HD_Customer 
FROM Table_CUstomer
WHERE ...

并交差运算 UNION INTERSECT EXCEPT

UNION 将多个查询结果合并为一个结果集

SELECT 语句1
  UNION [ALL]

SELECT 语句2
  UNION [ALL]

注意要进行合并的查询中的字段必须语义相同，数量也必须相同，数据类型隐式兼容（如char(20) 和 varchar(40)）

合并后的结果集采用第一个SELECT语句的列标题

所以GROUP BY 和ORDER By之类的写在最后一个SELECT后面但要用第一个SELECT中的列名

INTERSECT 交运算返回同时在两个集合中出现的记录

SELECT order1

INTERSECT

SELECT order2

INTERSECT ...

SELECT ordern

EXCEPT 差运算返回第一个查询中有第二个查询中没有的记录

SELECT order1

EXCEPT

SELECT order2

EXCEPT ...

SELECT ordern

子查询 subquery

参考前面

WHERE expression [NOT] IN (subquery)

WHERE expression compression_operator [ANY|ALL]

WHERE [NOT] EXISTS (subquery) # 存在性查询

插入

1	INTERT 表名 Vales (值1, 值2) # 值的顺序必须和表里的一样

更新

1 2	UPDATE 表名 Set 字段 = 值 WHERE ... # 不带where整个表都改了, where中要带主键

删除

1 2	DELETE 表名 WHERE ...

开窗函数 OVER

指把count, sum, avg等聚合计算赋值到每一行后面而不是单单给出结果

SELECT Cno, CName, Semester, Credit,
  SUM(Credit) OVER(PARTITION BY Semester) AS 'Total',
  AVG(Credit) OVER(PARTITION BY Semester) AS 'Avg',
  MIN(Credit) OVER(PARTITION BY Semester) AS 'Min',
  MAX(Credit) OVER(PARTITION BY Semester) AS 'Max'
FROM Course

排名函数 RANK()

SELECT OrderID, ProductID, OrderQty,
  RANK() OVER
  (PARTITION BY OrderID ORDER BY OrderQty DESC) AS RANK
FROM Order Detail
GROUP BY OrderID

DENSE_RANK()函数也用于排序，与RANK()的区别是前者没有并列，后者有并列

公用表表达式 (生成临时表) WITH

# 定义：

WITH BuyCount(CardID, Count)
AS( 
  SELECT CardID, COUNT(*)
  FROM Table_SaleBill
  GROUP BY CardID
)

# 使用

AS(
  SELECT CardID, Counts
  FROM BuyCount
  ORDER BY Counts
)

案例（以下操作均再SQL server 2008 express 环境基础上使用SSMS 2008 express 操作完成）

创建数据库、辅助文件、日志文件、设置初始大小和增长方式、设置物理文件保存位置

create database StudentDB /* 大小写不敏感建在这里纯粹为了可读性 */
/*事先在D盘上创建好目录文件夹再执行这个操作*/
on primary(
 name = 'StudentDB', /*主数据文件不做要求应与数据库同名*/
 filename = 'D:\DATABASE\DATA1\StudentDB.mdf', /*物理路径*/
 size = 3MB, /*初始文件大小3mb*/
 maxsize = unlimitaed, /*不设置文件大小上线*/
 filegrowth = 1mb /*按照1mb增长*/
),
(
 name = 'StuDB1', /*辅助数据文件*/
 filename = 'D:\DATABASE\DATA2\StuDB1.ndf', /*物理路径*/
 size = 3MB, /*初始文件大小3mb*/
 maxsize = unlimitaed, /*不设置文件大小上线*/
 filegrowth = 1mb /*按照1mb增长*/
),
(
 name = 'StuDB2', /*辅助数据文件*/
 filename = 'D:\DATABASE\DATA2\StuDB2.ndf', /*物理路径*/
 size = 3MB, /*初始文件大小3mb*/
 maxsize = unlimitaed, /*不设置文件大小上线*/
 filegrowth = 1mb /*按照1mb增长*/
)

/*创建日志文件*/
log on(
 name = 'StudentDB_log',
 filename = 'D:\DATA1\StudentDB_log.ldf',
 size = 1mb, /*1mb是最低要求，也是一般选择*/
 maxsize = 1gb,
 filegrowth = 10% /*按10%速率增长*/
)

修改主数据文件初始大小为5mb

/*修改主数据文件初始大小为5mb（修改数据库）*/

--单行注释

alter database StudentDB
 MODIFY file(
  name = StudentDB, /*创建时主数据文件和数据库文件的名字一样，这里不要搞混了*/
  size = 5mb
 )

添加文件组

/*添加文件组，添加一个辅助数据文件到新的文件组*/
alter database StudentDB
add filegroup GData
go 
alter database StudentDB
add file(
name = 'StuDB3',
filename = 'D:\DATABASE\DATA2\StuDB3.ndf'
)
to filegroup GData

/*go: 批处理：同时执行上下两个命令. 注意go的那行不能加注释。。啥毛病都是，吐了*/

删除数据库辅助文件

1
2
3

--删除数据库的辅助数据文件StuDB3
alter database StudentDB
remove file StuDB3

删除数据库

1 2	--删除数据库 drop database StudentDB

分离与附加数据库

分离数据库可以作为删除数据库的一个备选方案，因为可以保留备份以供恢复

--分离数据库(停止使用数据库从而可以拷贝出来以便今后附加数据库)
exec sp_detach_db StudentDB

--附加数据库
create database StudentDB
on primary(
 name = 'StudentDB',
 filename = 'C:\StudentDB.mdf'
)
for attach

创建与删除架构

架构可以理解为一个数据库的下级文件夹和一个表的上级文件夹

-- 创建架构
use StudentDB /*声明使用哪个数据库*/
go
create schema my_schema

-- 删除架构
drop schema my_schema

分区相关

-- 创建分区表：

--- （1） 创建分区函数

create partition function YearOrderPartFunc(int) /*针对int类型的列进行分区*/
as range left for values ('2007', '2009') /*左分区*/

-- （2）创建分区方案 /*创建分区方案之前一定要把文件组建好。并且，这个数据库的辅助储存文件一定要有保存在那些文件组里的，要不然表里的数据存不进去*/

create partition scheme YearOrderPartScheme
as partition YearOrderPartFunc /*根据这个分区函数来创建*/
to(
 [primary], /*主文件组*/
 GData1, /*文件组1*/
 GData2 /*文件组2, 因为上面左分区会形成3个区域，所以这里分三个*/
)

--- （3）根据分区方案创建表

create table Tb_StudentInfo(
 StdInfoID int identity(1, 1) primary key not null, /*identity(m, n)从m开始以n为步长自动增长*/
 StdName varchar(20) not null,
 StdYear int not null
)
on YearOrderPartScheme(StdYear) /*根据stdyear使用YearOrderPartScheme分区方案来进行分区*/

-- 查看所在分区

select *, $partition.YearOrderPartFunc(StdYear) as '所属分区'
from Tb_StudentInfo

视图相关

-- 创建视图，查看每门课程的授课老师

create view view_teachercourse(
 课程名称,
 教师姓名
) as
select courseinfoname, teacherinfoname
from dbo.courseinfo as a join dbo.teachercourse as b join dbo.teacherinfo as c
on b.teachercourseID = c.teachInfoID
on a.CourseInfoID = b.CourseInfoID

-- 查看视图

select * from dbo.view_teachercourse

-- 查看视图的定义

sp_helptext view_teachercourse

-- 修改视图

alter view view_teachercourse(
 课程名称,
 教师姓名,
 老师学历
) as 
select courseinfoname, teacherinfoname, teacherknowl
from dbo.courseinfo as a join dbo.teachercourse as b join dbo.teacherinfo as c
on b.teachercourseID = c.teachInfoID
on a.CourseInfoID = b.CourseInfoID

-- 删除视图

drop view view_teachercourse

字段的约束

/*创建表的约束*/

-- 空约束 not null 约束

create table StudentDB.my_schema.temp_table(
 stu_id int identity(1,1) primary key not null, /*不能为int指定列宽*/
 stu_name varchar(20) not null,
 stu_age int
)

drop table StudentDB.my_schema.temp_table

-- 缺省约束 default constrain (定义默认值)

--- （1）创建表的时候 (其他的约束也都可以在这两种时刻进行)

create table StudentDB.my_schema.temp_table(
 classID int not null,
 classdepart varchar(20) not null default('软件学院'),
 studentnumber int not null default(0) /*注意缺省值设定应与列数据类型一致*/
)

--- （2）修改表的时候

alter table StudentDB.my_schema.temp_table
add constraint df_classID default(1001) for classID

drop table StudentDB.my_schema.temp_table

-- 唯一约束 unique constrain （字段值取值唯一但允许null）

create table StudentDB.my_schema.temp_table(
 stdID int not null,
 stdInfoNum varchar(20) not null unique,
 stdIDcardnum varchar(18)
)

alter table StudentDB.my_schema.temp_table
add constraint ix_stdIDcardnum unique(stdIDcardnum)

drop table StudentDB.my_schema.temp_table

-- 检查约束 check constrain （限制范围）

create table StudentDB.my_schema.temp_table(
 stuID int not null,
 courseID int not null,
 grade numeric check(grade >= 0 and grade <= 100),
 grade2 numeric
)

alter table StudentDB.my_schema.temp_table
add constraint ck_grade2 check(grade2 between 0 and 100) /*另一种约定上下限的方式*/

drop table StudentDB.my_schema.temp_table

-- 主键约束 primary key constrain

create table StudentDB.my_schema.temp_table(
 stuID int not null primary key,
 courseID int not null,
 grade numeric check(grade >= 0 and grade <= 100),
 grade2 numeric
)

alter table StudentDB.my_schema.temp_table
add constraint pk_courseID primary key(courseID)

/*创建复合主键的表：*/

create table studentCourse(
 stdID int not null,
 courseID int not null,
 score numeric not null,
 constraint pk_stdid_courseid primary key(stdID, courseID)
)

drop table studentCourse

drop table StudentDB.my_schema.temp_table

-- 外键约束 foreign key constrain

create table StudentDB.my_schema.temp_table(
 stuID int not null primary key,
 courseID varchar(20) not null foreign key references my_schema.Tb_Stu_Info(Stu_No), /*注意外键的数据类型要与其引用的*/
 courseID2 varchar(20) not null
)

alter table StudentDB.my_schema.temp_table
add constraint fk_courseID2 foreign key(courseID2) references my_schema.Tb_Stu_Info(Stu_No)

-- 删除约束
alter table StudentDB.my_schema.temp_table
drop constraint FK__temp_tabl__cours__286302EC /*从对象资源管理器里拖拽进来的外键约束*/

索引

/*索引*/

-- 创建索引
create clustered index pk_courseID /*聚集索引（创建之前确保没有其他的聚集索引如主键之类的）（散列是CREATE INDEX xxx using hash on table(column)）*/
on StudentDB.my_schema.temp_table(courseID desc) /*降序*/

-- 重新生成索引
alter index pk_courseID
on StudentDB.my_schema.temp_table rebuild

-- 删除索引
drop index pk_courseID
on StudentDB.my_schema.temp_table

插入

insert dbo.studentcourse
values (1,50,0,0,0,null,null)
/*注意插入时不要违反各种约束和变量类型，如这个例子的第一列要自动增长*/

/*插入多行*/
insert dbo.studentcourse
values (2,50,0,0,0,null,null),
(3,50,0,0,0,null,null),
(4,50,0,0,0,null,null),
(5,50,0,0,0,null,null),
(6,50,0,0,0,null,null)

/*只插入某几个字段*/
insert dbo.teachercourse(teacherinfoID, courseinfoID)
values(18, 17)

/*插入来自其他表的数据（from select）
注意自己的表要兼容插入进来的数据*/
insert into dbo.studentInfo1
select stdinfonum, stdinfoname, stdinfosex, stdinfocard, stdinfonatns, stdinfotel
from dbo.studentinfo
where stdinfosex = '女'

更新

update dbo,studentinfo
set stdinfoname - '李婵林',
    stdinfotel = '15874584299',
    stdinfoemail = 'lichan8299@163.com'
where stdinfoname = '李婵'
/*如果没有where则会修改整个表格*/

一些查询

/*消除重复*/
select distinct teacherinfospec from dbo.teachinfo

/*前n条（top）*/
select top 8 * from dbo.classinfo /*超过就是查全部*/

/*前20%*/
select top 20 percent * from dbo.classinfo

/*查询计算结果*/
select play_time, study_time, play_time + study_time as 'all_time'
from time_table
/*数值型相加是求和，字符串型相加是连接字符串，混合型默认把字符串强制转换为数字，转换不了就报错*/


/*另一种别名方式*/
select 'pt' = play_time, 'st'= study_time, 'all_time' = play_time + study_time
from time_table

/*排序 group by*/
-- 排序默认升序asc；降序是desc
select stdinfonum, stdinfoname,stdinfobirth
from dbo.studentinfo
where stdinfobirth >= '1990-1-1'
order by stdinfobirth desc

/*查询包含字符串*/
select * 
from dbo.studentinfo
where stdinfoname like '张%' /*张+任意字符*/

/*between and*/
select *
from dbo.studentinfo
where stdinfoyear between 2007 and 2009
-- 等效于
select *
from dbo.studentinfo
where stdinfoyear >= 2007 and stdinfoyear <=2009
-- 等效于
select *
from dbo.studentinfo

/*in*/
select *
from dbo.studentinfo
where stdinfoyear not in (2007, 2008, 2009)

/*is null*/

/*把聚合函数的结果汇总在每一行*/

select stdinfoname, stdinfosex, stdinfonatns
from dbo.studentinfo
compute count(stdinfoname)

select stdinfoname, stdinfosex, stdinfonatns
from dbo.studentinfo
order by stdinfonatns
compute count(stdinfonatns) by stdinfonatns

判断if语句

案例：判断学生(ID = 6)是否已经选了3门以上的课程

declare @sname varchar(50), @num int

select @sname = StdInfoName /*赋值的一种方法*/
from dbo.StudentInfo
where stdInfoID = 6

set @num = (select COUNT(*) 
             from dbo.StudentCourse
             group by StdInfoID
             having  StdInfoID = 6) /*赋值的另一种方法*/

if @num >= 3
  begin
    print @sname + '，你已经完成了选课！'
  end
else
  print @sname + ',你还要继续选课' /*只有一个命令的时候begin end 可写可不写*/

判断case语句

案例：把性别中的‘男’显示为male，女生同样

select SrdInfoName, 性别 = CASE
                           WHEN StdInfo Sex = '男' then 'male'
                           WHEN StdInfoSex = '女' then 'female'
                          End
FROM dbo.StudentInfo

循环结构

案例计算1 - 100 以内能被3整除的最大数字

decalre @x int  = 100

while(@x >= 1)
 begin
  if(@x % 3 = 0)
   break
  set @x- = 1
 end

print 'the largest number between 1 and 100 which can be devide by 3 is'

print @x

存储过程（函数）

/*定义存储过程*/
create procedure p_showteachercourse /*procedure 可以简写为 proc*/
AS
SELECT TeachInfoName, TeachInfoSex, TeachInfoKnowl, TeachInfoTitle, CourseInfoName
From dbo.TeachInfo a join dbo.TeachCourse b
on a.TeachInfoID = b.TeachInfoID
join dbo.CourseInfo c
on b.CourseInfoID = c.CourseInfoID

/*调用存储过程s*/
p_showteachercourse /*方法一，单执行这一行，不能在批处理之中*/

execure p_showteachercourse /*方法二*/

exec dbo.p_showteachercourse /*方法三，架构可加可不加*/

/*修改存储过程*/
alter procedure p_showteachercourse
AS
SELECT TeachInfoName, TeachInfoSex, TeachInfoKnowl, TeachInfoTitle, CourseInfoName
From dbo.TeachInfo a join dbo.TeachCourse b
on a.TeachInfoID = b.TeachInfoID
join dbo.CourseInfo c
on b.CourseInfoID = c.CourseInfoID
where TeachInfoTitle = '副教授'

/*删除存储过程*/
drop procedure p_showteachercourse
drop proc p_showteachercourse

/*带参数的存储过程*/
create proc p_showteachercourse
@teachername varchar(30)
as
SELECT TeachInfoName, TeachInfoSex, TeachInfoKnowl, TeachInfoTitle, CourseInfoName
From dbo.TeachInfo a join dbo.TeachCourse b
on a.TeachInfoID = b.TeachInfoID
join dbo.CourseInfo c
on b.CourseInfoID = c.CourseInfoID
where TeachInfoName = @teachername

-- 执行（执行时需要输入参数）
dbo.p_showteachercourse '李静'

/*带输出的存储过程*/
create proc AddTeaCourse
@TeaInfoID int, @CourseInfoID int,
TeaCourseID int output /*输出*/
as
insert dbo.TeachCourse
values(@TeaInfoID, @TeaCourseID)
--设置输出参数，用来储存自动增长的编号
set @TeacourseID = scope_identity()

-- 执行
declare @TeaCId int
exec dbo.AddTeaCourse 16, 26, @TeaCId output

触发器

案例1

create trigger trig_insertTeachinfo
on dbo.TeachInfo
after insert /*后触发器*/
as
begin transaction /*事务的开始*/
if exists (
select * 
from insertted a
where a.DepInfoID not in(
  select DepInfoID from dbo.DepInfo
)
begin /*if的判断体*/
 raiserror('数据一致性验证！', 16, 1) /*报错消息：如果出错，会报：数据一致性验证！ 级别16， 状态1*/
 rollback transaction /*回滚事务*/
end
else
 commit transaction /*提交事务*/
) /*inserted 是一个逻辑表，存放插入之后记录*/

-- 测试
insert dbo.TeachInfo(
  DepInfoID,
  TeachTyoeID,
  TeachInfoNum,
  TeachInfoName
)
values(9, 2, '123123', '张三') /*这个出错是因为没有ID是9的部门*/

insert dbo.TeachInfo(
  DepInfoID,
  TeachTyoeID,
  TeachInfoNum,
  TeachInfoName
)
values(7, 2, '123123', '张三')

案例2

-- 更新分为两个步骤：删除和插入
-- 牵扯到两个逻辑表：deleted（被删除的记录），inserted（存放插入之后记录）
create trigger trig_update class
on dbo.ClassInfo
after update
as
 if update(ClassInfoID) /*如果更新的是classinfoid的话更新其他classname啥的则不会触发*/
 begin
  update dbo.StudentInfo
  set ClassInfoID = (
    select ClassInfoID 
    from inserted
    where classinfoid = (
      select classinfoid from deleted
    )
  )
 end

-- 测试
update dbo.classinfo
set classinfoid = 444 where classinfoid = 4

案例delete后触发器

create trigger trig_deleteStudent
on dbo.StudentInfo
after delete
as
begin
 declare @stdinfoID int
 select @stdInfoID  = deleted.stdInfoID from deleted

 delete dbo.StudentXourse
 where StdInfoID = @stdInfoID
end

-- 测试
delete dbo.StudentInfo
where StdInfoID = 2

案例（前触发器）不允许删除已经存在的记录

create trigger trig_deleteDep
on dbo.DepInfo
instead of delete
as
begin
 declare @delCount int
 select @delcount = count(*) from deleted
 if @delCuunt > 0
  begin
   raiserror ('不能删除院系表中的任何记录！', 10, 1)
   -- rollback transaction /*推荐回滚一下，不回滚也可以*/
  end
end

-- 测试
delete dbo.DepInfo

案例

/*删除触发器*/
drop trigger trig_deleteDep

/*修改触发器*/
alter trigger trig_deleteDep
/*后面和create的一样*/
on dbo.DepInfo
instead of delete
as
begin
 declare @delCount int
 select @delcount = count(*) from deleted
 if @delCuunt > 0
  begin
   raiserror ('不能删除院系表中的任何记录！', 10, 1)
   -- rollback transaction /*推荐回滚一下，不回滚也可以*/
  end
end

案例删除学生时对学生总数进行更新

create trigger trig_stdinfoclassnum
on dbo.StudentInfo
after insert, delete
as
if(exists (select * from inserted))
begin
 update dbo.classinfo
 set classinfosum + = 1
 where classinfoid  = (selected classinfoid from inserted)
end

if(exists (select * from deleted))
begin
 update dbo.classinfo
 set classinfosum - = 1
 where classinfoid  = (selected classinfoid from deleted)
end

-- 测试
insert dbo.StudentInfo(ClassInfoID, StdInfoNum, StdInfoname, StdInfosex)
values(10, '123123', '李四', '男')

DDL 触发器

DDL触发器只能作为后触发器而不能定义为instead of

案例

create trigger trig_stuselDBsafe
on database
for drop_table, alter_table
as
print '你不能删除或修改StuselDB中任何对象'
rollback

-- 测试
drop table abo.AdminUser

/*删除ddl触发器*/
drop trigger trig_stuselDBsafe
on database

用户定义函数

系统的内置函数

/*转换函数（强制转换）*/
select cast('11.92' as float), /*默认返回int，这里返回float*/
  CONVERT(varchar(20), GETDATE(), 111) /*将当前系统日期转换成字符串。111指日期风格的一种：yyyy/mm/dd，112就是yyyymmdd*/


/*数学函数*/
select celling(5.6), floor(5.6) /*两种取整*/

select round(2, 19) /*上下范围*/

select abs(-2) /*绝对值*/

/*字符串函数*/
-- 查找姓名第二位是晓的同学
select StdInfoNum, StdInfoName, StdInfoSex
from dbo.StudentInfo
where substring(StdInfoName, 2, 1) = '晓' /*从哪个字段取，从第几位开始取，取多长*/

/*日期函数*/
-- 日期计算
select dateadd("month", 1, GETDATE()) /*比现在的日期增加一月*/

-- 从DOB计算年龄
select * from(

select StdInfoNum, StdInfoName, StdInfoSexs, DATEDIFF(YEAR, StdInfoBirthD, GETDATE()) as age /*计算StdInfoBirthD和GETDATE()之间年的差别*/
from dbo.StudentInfo

) as temp
where age between 20 and 25

自定义用户函数

自定义函数分为两种：标量函数和表值函数（根据返回值的不同）

create function FTitleSum(@depName varchar(20))
returns int /*返回老师总人数*/
as
begin
 declare @num int
 select @num = count(*)
 from dbo.Teachinfo
 where DeptInfoID = (
   select DeptInfoID
   from dbo.DepInfo
   where DeptInfoName = @depName
 )
 return @num
end

-- 调用
declar @name varchar(20) = '信息工程系'
select dbo.FTitleSum(@name) as 教师人数

/*表值函数*/
create function FSelTeach(@depName varchar(20))
returns table
as 
return (select TeacName
 from dbo.Teachinfo
 where DeptInfoID = (
   select DeptInfoID
   from dbo.DepInfo
   where DeptInfoName = @depName
 ))

 --调用
 declar @name varchar(20) = '信息工程系'
 select * from FSelTeach(@name)

/*多语句表值函数*/

select CourseInfoName, CourseInfoRestPer
from dbo.CourseInfo c join dbo.TeachInfo a join dbo.TeachCourse b
on a.TeachInfoID = b.TeachInfoID
on c.CourseInfoID = b.CourseInfoID
where TeachInfoName = @teachername

create function FSelCourse(@teachName varchar(20))
returns @teach_Course table(
  课程名称 varchar(80),
  理论学时 int,
) /*定义返回的表的结构*/
as
begin
 insert @teach_Course
  select CourseInfoName, CourseInfoRestPer
  from dbo.CourseInfo c join dbo.TeachInfo a join dbo.TeachCourse b
  on a.TeachInfoID = b.TeachInfoID
  on c.CourseInfoID = b.CourseInfoID
  where TeachInfoName = @teachername
return @teach_Course
end

-- 调用
declare @tname varchar(20)
set @tname = '王钢蛋'
select * from FSelCourse(@tname)

游标

游标是针对结果集的操作

declare @sname varchar(20), @ssex varchar(5), @snatns varchar(5)

/*声明游标*/
declare student_cursor cursor for
select StdInfoName, StdInfoSex, StdInfoNatns
from dbo.StudentInfo
where StdInfoNatns = '汉' and StdInfoSex = '男'

/*打开游标*/
open student_cursor

/*提取数据*/
fetch next from student_cursor into @sname, @ssex, @snatns
/*展示数据*/
while @@FETCH_STATUS = 0
begin
 print '学生姓名：' + @sname + '性别' + @ssex + '民族' + @snatns
 fetch next from student_cursor into @sname, @ssex, @snatns
end

/*关闭游标*/
close student_cursor

/*释放游标*/
deallocate student_cursor

安全管理（权限）

创建登录名

1
2
3

create login [pcname\username]
from windows
with default_database= [master]

create login test_user
with password = '123456',
default_database= [master],
/*默认设置成master，否则会报错 with 有一个就可以，后面不用加with*/
check_expiration = on, /*强制密码策略*/
check_policy = on

修改密码

1
2
3

alter login test_user
with password = '6666'
old_password = '123456`'

禁用登陆

1	alter login test_user disable /启用时enable/

删除登录名

1	drop login test_user

创建数据库用户并绑定到服务器登录名

create login teachUser
with password = '123456'
default_database = [master]
go
use StudentDB
go
create user teach1
for login teachUser
/*一个数据库用户只能的关联到一个服务器登录名*/

-- 更换数据库用户
alter user teach1
with name = teach2

-- 删除数据库用户
drop user teach2
/*拥有架构的用户无法删除*/

数据库用户权限管理

/*授权*/
grant select on TeachInfo to teach1 /*授权要给user而不是login*/

/*拒绝权限*/
deny select on TeachInfo to teach1

/*撤销权限*/
revoke select on TeachInfo to teach1

角色管理（创建服务器角色）

/*服务器角色：*/
exec sp_addsrvrolemember 'teachers', 'sysadmin'
/*teachers是登录名*/

/*把登录名从服务器固定角色中删除*/
exec sp_dropsrvrolemember 'teachers', 'sysadmin'

/*把数据库用户设置为数据库角色*/
use StudentDB
go
exec sp_addrolemember 'db_datareader', 'test' 
/*test是数据库用户名*/

/*取消设置为数据库角色*/
exec sp_droprolemember 'db_datareader', 'test'

/*创建数据库角色*/
create role Myteacher

grant select, insert, update, delete on TeachInfo to Myteacher
/*TeachInfo是表名*/
deny create table to Myteacher

备份与回复

对数据库右键 -> 任务 -> 备份

对服务器上的数据库文件夹右键 -> 还原

Author：Ruoyan Han

Link of This Article：https://ry2an.github.io/1994/10/09/MySQL-Hand-Book/

Publish Date：October 9th 1994, 8:31:59 pm

Update Date：August 10th 2021, 4:12:22 pm

Next Post

Markdown Cheat Sheet
Previous Post

test

CATALOG

1. 数据库SQL指令笔记
2. 以下内容是在备考计算机三级数据库时学习的
3. 案例（以下操作均再SQL server 2008 express 环境基础上使用SSMS 2008 express 操作完成）



缺失模块。
1、请确保node版本大于6.2
2、在博客根目录（注意不是archer根目录）执行以下命令：
npm i hexo-generator-json-content --save
3、在根目录_config.yml里添加配置：

jsonContent:
  meta: false
  pages: false
  posts:
    title: true
    date: true
    path: true
    text: false
    raw: false
    content: false
    slug: false
    updated: false
    comments: false
    link: false
    permalink: false
    excerpt: false
    categories: true
    tags: true