Bjb Code

表操作

在 SQL Server 中，表是一种关键的数据库对象，用于存储数据。表操作包括创建、修改、查询和删除表等。

创建表

CREATE TABLE table_name
(
	--主键自增长，从 100 开始，每次增加 5
	id INT PRIMARY KEY IDENTITY(100,5),
	--UINQUE 的列，可以插入 NULL，但是不能有 2 个以上的 NULL，否则会被认为重复。（ORACLE 中的 UNIQUE 列可以插入多个 NULL，不会被认为重复）
	name NVARCHAR(100) UINQUE,
	age INT NOT NULL,
	email NVARCHAR(100) UINQUE NOT NULL,
	--()可以省略，但是单引号不能省略；设置了默认值的列，在 INSERT 语句中可以不给值，会自动采用默认值。
	sex NCHAR(1) DEFAULT('男'), 
	sal INT CHECK (sal>=1000 and sal<=8000),
);

CREATE TABLE table_name
(
	--设置外键（CONSTRAINT fk_banji_id 用于指定外键的名字，可以省略）
	banji_id INT CONSTRAINT fk_banji_id FOREIGN KEY REFERENCES banji(id),
	--设置外键
	classroom_id INT FOREIGN KEY REFERENCES classroom(id),
	course NVARCHAR(100),
	--设置这张表为联合主键
	CONSTRAINT pk_id PRIMARY KEY (banji_id,classroom_id)
);

删除表

DROP TABLE table_name;

增删改查

插入

INSER INTO table_name(id,sal,name,age,sex) VALUES(1,1000,'张三',26,'男');

删除

DELETE FROM table_name WEHRE condition;

更新

UPDATE te SET te.sal = 1000 FROM t_emp AS te WHERE [条件];

查询

/* 条件查询（WHERE 里面不允许使用聚合函数） */
--<>、!= 表示不等于，> 表示大于，>= 表示大于等于，< 表示小于，<= 表示小于等于
SELECT * FROM table_name WHERE sal != 1000;
SELECT * FROM table_name WHERE sal BETWEEN 1000 AND 3000;
SELECT * FROM table_name WHERE sal NOT BETWEEN 1000 AND 3000;
SELECT * FROM table_name WHERE sal IN (1000,3000);
SELECT * FROM table_name WHERE sal NOT IN (1000.3000);
--查询 sal 字段为 NULL 或不为 NULL 的记录，不能写成 “=NULL” 或 “!=NULL”，这个写法是错误的
SELECT * FROM table_name WHERE sal IS NULL;
SELECT * FROM table_name WHERE sal IS NOT NULL;
--模糊查询名字里含 A 的记录，% 表示任意 0 个或多个字符
SELECT * FROM table_name WHERE name LIKE '%A%';
--模糊查询名字以 A 开头的记录
SELECT * FROM table_name WHERE name LIKE 'A%';
--模糊查询名字以 A 结尾的记录
SELECT * FROM table_name WHERE name LIKE '%A';
--模糊查询名字的第 2 个字符是 A 的记录
SELECT * FROM table_name WHERE name LIKE '_A%';
--模糊查询名字的第 2 个字符是 A 到 F 中的任意字符的记录，‘[A-F]’ 是正则表达式，表示 A 到 F 中的任意一个字符
SELECT * FROM table_name WHERE name LIKE '_[A-F]%';
--模糊查询名字的第 2 个字符，不是 A 到 F 中的任意字符的记录，‘[^A-F]’ 是正则表达式，表示非 A 到 F 中的任意一个字符
SELECT * FROM table_name WHERE name LIKE '_[^A-F]%';
--模糊查询名字里含有 % 的记录，‘\’ 是转义字符，ESCAPE 表示跳过 ‘\’
SELECT * FROM table_name WHERE name LIKE '%\%%' ESCAPE '\';
--模糊查询名字里含有_的记录，'\' 是转义字符，ESCAPE 表示跳过 '\'
SELECT * FROM table_name WHERE name LIKE '%\_%' ESCAPE '\';
--模糊查询名字不以 A 开头的记录
SELECT * FROM table_name WHERE name NOT LIKE 'A%';



/* 排序 */
SELECT * FROM table_name ORDER BY sal DESC; --DESC 表示降序排列，默认升序排列 ASC
SELECT * FROM table_name ORDER BY name,sal DESC; --先按照 name 升序排列，再按照 sal 降序排列



/* 别名 */
--SqlServer 中别名允许用双引号括起来，也可以不用双引号；Oracle 中不允许用双引号括起来
SELECT sal*12 AS "年薪",sal AS "月薪" FROM table_name;



/* 一般函数 */
SELECT NEWID(); --自动产生 id：F410FC7F-95AE-4849-B3BB-D9AE13325874（类似于 mysql 中的 uuid）
SELECT DISTINCT deptno FROM table_name; --查询不一样的 deptno
SELECT DISTINCT deptno,name FROM table_name; --查询不一样的 deptno 和 name 的组合
SELECT ISNULL(sal,0)*12 FROM table_name; --ISNULL 判断字段为否为 NULL，为 NULL 则用 0 代替。
SELECT LOWER(name) FROM table_name; --将名字转成小写字母
SELECT UPPER(name) FROM table_name; --将名字转成大写字母
SELECT LEFT('abcdefg',3); --输出 abc，LEFT('源字符串','要截取最左边的字符个数')
SELECT RIGHT('abcdefg',3); --输出 efg，RIGHT('源字符串','要截取最右边的字符个数')
SELECT SUBSTRING('abcdefg',3 ,4); --输出 cdef，SUBSTRING('源字符串','截取起始位置(是位置不是索引，含该位置上的字符)','截取长度')
SELECT CHARINDEX('d','abcdef',0); --输出 4，CHARINDEX 查询字符串所在的位置，'d' 为子字符串，'abcdef' 为父字符串，0 表示开始查询的位置
SELECT LEN(area_code) FROM d_pick; --函数说明：获取字符串长度



/* 聚合函数 */
SELECT MAX(sal) FROM table_name; --查询薪水最高的记录
SELECT MIN(sal) FROM table_name; --查询薪水最低的记录
SELECT AVG(sal) FROM table_name; --查看平均薪水
SELECT SUM(sal) FROM table_name; --查看薪水总和
SELECT COUNT(*) FROM table_name; --查询表中的总记录数
SELECT COUNT(deptno) FROM table_name; --查询表中 deptno 字段不为 NULL 的记录数
SELECT COUNT(DISTINCT deptno) FROM table_name; --查询表中 deptno 字段不重复的记录，不包含为 NULL 的记录



/* 分组函数 */
--查询部门的平均工资等信息，并按部门升序排列
SELECT deptno,AVG(sal),MAX(sal),MIN(sal) FROM table_name GROUP BY deptno ORDER BY deptno;
--查询平均工资大于 2000 的部门。（HAVING 后不能用别名，只能原始的字段）
SELECT deptno,AVG(sal) FROM table_name GROUP BY deptno HAVING AVG(sal) > 2000;
--查询薪水大于 2000 的人的平均工资，且按照部门分组后部门平均工资大于 3000 的
SELECT deptno,AVG(sal) FROM table_name WHERE sal > 2000 GROUP BY deptno HAVING AVG(sal) > 3000;
--虽然查询内容中没有 AVG(sal)，但因为 COUNT 和 AVG 都是聚合函数，所以可以这样使用
SELECT COUNT(*) FROM table_name HAVING AVG(sal) > 2000;



/* 时间查询 */
SELECT GETDATE(); --获取当前时间：2021-01-14 16:20:51.803
SELECT CURRENT_TIMESTAMP; --获取当前时间：2021-01-14 16:20:51.803
SELECT CONVERT(varchar(7),GETDATE(),120); --转换为：2021-09
SELECT CONVERT(varchar(8),GETDATE(),112); --转换为：20210909
SELECT CONVERT(varchar(10),GETDATE(),120); --转换为：2021-09-09
SELECT CONVERT(varchar(12),GETDATE(),111); --转换为：2021/09/09
SELECT FORMAT(p.ship_plan_date,'yyyyMMdd') AS nickname FROM table_name; --格式化时间字段



/* 连接查询 */
SELECT * FROM emp INNER JOIN dept ON emp.deptno_id = dept.id; --内联查询



/* 分页查询 */
SELECT TOP 10 * FROM table_name; --查询前 10 条数据，如果总共有 50 条记录， 则输出前 10 条
SELECT TOP 10 PERCENT * FROM table_name; --查询前 10% 的数据，如果总共有 50 条数据，则输出前 5 条
SELECT TOP 5 * FROM table_name ORDER BY sal DESC; --按工资降序查询前 5 条记录
--按工资降序查询第 6~10 条数据
SELECT TOP 5 * FROM table_name WHERE empno NOT IN (SELECT TOP 5 empno FROM emp ORDER BY sal DESC) ORDER BY sal DESC;
--按工资降序查询 11~15 条数据
SELECT TOP 5 * FROM table_name WHERE empno NOT IN (SELECT TOP 10 empno FROM emp ORDER BY sal DESC) ORDER BY sal DESC;
--分页公式，每页显示 n 条记录，当前要显示第 m 页
SELECT TOP n * FROM table_name WHERE column_name NOT IN (SELECT TOP (m-1)*n column_name FROM emp);



/* 给查询结果进行编号 */
--详见：https://blog.csdn.net/weixin_38158541/article/details/91439802
SELECT ROW_NUMBER() OVER(ORDER BY totalPrice) AS rows FROM d_pick WHERE is_active = 1;



/* 查询锁表 */
--查询锁表
SELECT object_name(resource_associated_entity_id) AS tableName, request_session_id AS pid 
FROM sys.dm_tran_locks
WHERE resource_type = 'OBJECT';
--杀掉进程
KILL 52;