psql -U user -d db_name -h 127.0.0.1 -p 5432

show database; //在当前目录下查询现有数据库
CREATE DATABASE database_name; //创建数据库
drop db_name  //删除数据库

mysql -u root -p game < game.sql

CREATE TABLE [IF NOT EXISTS] 表名 (
    -- 1. 定义列（Column Definition）
    列名1 数据类型 [列级约束] [默认值] [注释],
    列名2 数据类型 [列级约束] [默认值] [注释],
    
    -- 2. 定义约束（Constraint Definition）—— 写在最后
    [表级约束]
) [表选项（如存储引擎、字符集）];

//创建一个表 括号中时字段名称和数据类型，注意逗号的使用
create table tb_name (
    id INT,
    name VARVHAR(100),  //字符串 大小100个字符
    level INT,
    exp INT, 
    god DECIMAL(10,2)  //长度为10，保留两位小数的十进制数值 DECIMAL 用于存储准确精度防止精度丢失
)

    CONSTRAINT LLZ_pk_emp PRIMARY KEY (empno),         
    FOREIGN KEY (mgr) REFERENCES llz_emp(empno) ON DELETE SET NULL,
    FOREIGN KEY (deptno) REFERENCES llz_dept(deptno) ON DELETE SET NULL

 -- 修改数据类型
Alter table tb_name MODIFY COLUMN name VARCHAR(200); 
//表示修改表(tb_name)中的"name"列，被操作的对象是 表和列 操作方式是修改数据类型

 -- 重命名
Alter table tb_name RENAME COLUMN name to nick_name;

-- 添加数据类型
ALTER TABLE tb_name ADD COLUMN last DATETIME  ;
-- 此时last是新添加的字段名，DATETIME为数据类型

 -- 删除某个字段(列)
ALTER TABLE tb_name DROP COLUMN last ;

-- 删除整个表格
DROP TABLE tb_name ;

-- 删除特定情况的数据(行)
DELETE FROM player where gold = 0; 

-- 想要修改某个数据具体值
UPDATE tb_name set level = 1 where name = "114"	

-- 想要某个数据类型在创建时写入默认值:
ALTER TABLE player MODIFY LEVEL INT DEFAULT 1 -- 默认LEVEL认值为1
-- 除DEFAULT还可以有FULL、NOT FULL、COMMENT 'infor'，UNIQUE不能写在MODIFY COLUMN

-- 修改约束
ADD CONSTRAINT unique_ename UNIQUE (ename);

//若是把后面的where语句去掉，则会把整个表的数据修改,使用","连接可以修改多个值
//关键字table 、 xxx COLUMN  
//ON DELETE SET NULL; 父表删除时子表相应部分设为 NULL ，同时子表数据不能定义为NOT NULL 
//而 ON DELETE CASCADE;当父表（主表）的某一行被删除时，数据库会去子表找到引用了该行的所有记录，物理删除那些数据行。
//而当 CASCADE 在 DROP CONSTRAINT 后面则只删约束，不删数据，因为作用对象是数据库结构

-- 假设表 employees 已有，为 employee_id 列添加主键
ALTER TABLE employees ADD PRIMARY KEY (employee_id);

-- 为多个列添加复合主键
ALTER TABLE order_items ADD PRIMARY KEY (order_id, product_id);

-- 指定约束名添加主键
ALTER TABLE employees ADD CONSTRAINT pk_emp_id PRIMARY KEY (employee_id);

ALTER TABLE employees DROP PRIMARY KEY;

-- 如果你当初没起名，系统会自动生成一个（如 employees_pkey），需先查询
ALTER TABLE employees DROP CONSTRAINT pk_emp_id;
-- 查询

-- 在 orders 表中添加 customer_id 外键，引用 customers 表的 id
ALTER TABLE orders 
ADD CONSTRAINT fk_customer 
FOREIGN KEY (customer_id) REFERENCES customers (id);

ALTER TABLE orders DROP FOREIGN KEY fk_customer;

ALTER TABLE orders DROP CONSTRAINT fk_customer;

-- 查看有哪些外键引用了该表的主键，有的话就接触约束：CASCADE
ALTER TABLE orders DROP CONSTRAINT fk_customer CASCADE;

-- 基本语法
INSERT INTO db_name(id,name,level,exp,gold)VALUES(1,"sc",1,1,1);
-- 多条插入
INSERT INTO db_name(id,name)VALUES(2,"114")(3,"514")
-- 此时没有默认值的数据类型是NULL，需要set默认值见上

-- select选择要查询的列 *则表示查询所有的列 from后面根要查询表的名称
select * from tb_name;

-- SELECT后列出要查询的列 FROM后列出有要select的表，然后再在后面加要求
SELECT ENAME, JOB, HIREDATE, SAL FROM EMP WHERE DEPTNO = 10;

UPDATE player set level = 1 where name = "";  -- 修改特定值，name可换成其他主键
UPDATE player set level = 1,gold = 0; -- 去掉where 修改全部

-- 删除整个表格
DROP TABLE tb_name ;

-- 删除特定情况的数据(行)
DELETE FROM player where gold = 0; 

WITH 新表名 AS(
    SELECT 选择查询返回的新表
)
SELECT (DISTINCT) 属性，属性 FROM 表 （缩写名）(AS 别名) (多表查询 xxx JOIN) WHERE/ LIKE 满足查询的要求的记录 GROUP BY 根据什么分组
HAVING 筛选分组后的数据 ORDER BY 属性名 排序顺序 LIMIT 选择展现数量
EXCEPT -- EXCEPT返回第一个查询中存在但第二个查询中不存在的行。

-- 1.
r.name AS 零售商名称,
-- 2.
    (
        SELECT COUNT(*)
        FROM "Order" o
        WHERE o.rID = r.rID 
          AND o.type = '普通订单'
    ) AS 普通订单数量,
-- 3.
AVG (price)
-- 聚合函数也可以用DISTINCT 如：
SELECT COUNT(DISTINCT p.pID) FROM

xxx JOIN 表1 （缩写名1） ON 连接条件
xxx JOIN 表2 （缩写名2） ON 连接条件

-- 判断某个字段的值，是否等于子查询返回结果中的任意一个值。
WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname = '张三');
-- 比较运算符 返沪一个聚合查询的结构
WHERE Grade > (SELECT AVG(Grade) FROM SC);
-- 带 EXISTS 谓词的子查询 返回是否存在1/0
WHERE EXISTS (SELECT * FROM SC WHERE SC.Sno = Student.Sno AND Cno = '001');
-- ANY/ALL  返回多个行
WHERE Grade >= ALL (SELECT Grade FROM SC WHERE Cno = '002');

LIKE '%王_'

HAVING AVG(salary) > 5000;

ORDER BY 5/price DESC/ASC 

LIMIT 3  -- 只取前三条
LIMIT 3,3 -- 返回第四到第六名 第一个三表示偏移量 第二个表示往后取三个

SELECT DISTINCT  -- 这里DISTINCT在分组后其实冗余（因为GROUP BY已确保唯一），但为了演示语法我保留它
    n.nID AS 管理员编号,
    n.nName AS 管理员姓名,
    COUNT(bs.bsID) AS 管理实体书总数
FROM BookSKU bs
INNER JOIN BookPub bp ON bs.bpID = bp.bpID
INNER JOIN Shelf s ON bs.sID = s.sID
INNER JOIN Librarian n ON s.nID = n.nID
INNER JOIN Room r ON s.rID = r.rID
WHERE r.rName = '信息技术图书室'     -- 位置条件
  AND bp.bpTitle LIKE '%数据%'      -- LIKE条件
  AND bs.bsRegTime >= '2026-01-01'
GROUP BY n.nID, n.nName
HAVING COUNT(bs.bsID) >= 2          -- 分组后筛选
ORDER BY COUNT(bs.bsID) DESC
LIMIT 2;

# 导出整个数据库
mysqldump -u username -p database_name > backup.sql;
# 导入数据库
mysql -u username -p database_name < backup.sql;
# 仅导出表结构，不含数据（-d 参数）
mysqldump -u username -p -d database_name > structure.sql;

# 导出数据
bcp database_name.dbo.your_table out data.csv -c -t, -S server_name -U username -P password

# 导出查询结果
bcp "SELECT * FROM your_table" queryout data.csv -c -t, -S server_name -U username -P password

//where
-- 查找经验在特定区域的player
SELECT * FROM player WHERE level > 1
SELECT * FROM player WHERE level > 1 AND level < 5

-- 优先级NOT > AND > OR，可以用括号改变优先级，可以用IN查找特定值的数据
SELECT * FROM player WHERE (LEVEL > 1 OR exp > 1)
SELECT * FROM player WHERE level IN (1,3,5) //查找为1,3,5的玩家
SELECT * FROM player WHERE NOT BETWEEN 1 AND 10 //等级不在1 ~ 10的玩家

-- 注意 % 和 _是like中才有的，正则表达式中没有

-- 则查找姓王且为两个字的玩家
SELECT * FROM player name REGEXP '^王.$' 
-- 查找名字带王的人
SELECT * FROM player name REGEXP '王' 

//LIKE模糊查询

-- 则模糊查询可以查找到名字是“王”前有任意个字，“王”后有两个字的玩家
SELECT * FROM player NAME LIKE '%王__' 
-- 查找姓“王”的玩家
SELECT * FROM player NAME LIKE '王%' 
-- 查找名字里有“王”的玩家
SELECT * FROM player NAME LIKE '%王%' 

-- 查找信息为空的值 注意不能用name = NULL NULL和任何值都不相等
SELECT * FROM plaer where email = NULL; -- 会得到一个空结果集
SELECT * FROM plaer where email is NULL; -- √
SELECT * FROM plaer where email <=> NULL //its all OK

-- 有时候是空字符串，其与NULL是不一样的，空字符串使用" = "判断
SELECT * FROM player where email = '' or email is NULL //its OK

-- 默认升序排列
SELECT * FROM player ORDER BY level; 
-- DESC 降序排列
SELECT * FROM player ORDER BY level DESC; //降序

-- 在优先按照level升降排列的情况下按照exp升序排列 
SELECT * FROM player ORDER BY level DESC,exp ASC;  
-- 且可以使用列数进行排列,假设level为第五列 则有
SELECT * FROM player ORDER BY 5 DESC,exp ASC;  

-- 查找玩家数量
SELECT COUNT(*) FROM  player; 
-- 查找等级平均值
SELECT AVG(LEVEL) FROM  player 

-- 按性别分组
SELECT sex,count(*) from player group by sex; 
-- 此时SELECT返回两列:s、c，from表示从player表，sex表示按照列的值进行分组
-- 得到的结果是所有 sex 相同的行会被归入同一个组，然后对每个组执行 count(*) 聚合计算。
SELECT level,count(level) from player group by level;//看每个等级各有多少个玩家

-- 1
SELECT 
    department_id,
    job_id,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id, job_id;

-- 2  同时使用where和having 两者具体使用在下面
SELECT 
    department_id,
    job_id,
    AVG(salary) AS avg_salary
FROM employees
WHERE job_id > 2           -- 分组前先筛选出 job_id > 2 的行
GROUP BY department_id, job_id
HAVING AVG(salary) > 5000; -- 分组后筛选平均工资 > 5000 的分组

-- 查询数量大于4的各个等级的数量（不是等级比4大的玩家）
SELECT level,count(level) from player group by level having count(level)> 4 

-- 降序排列
SELECT level,count(level) from player group by level having count(level) > 4 ORDER by count(level) DESC; 

-- 单独使用对聚合函数过滤
-- 检查所有员工的平均工资是否大于 5000
SELECT AVG(salary) AS avg_sal
FROM employees
HAVING AVG(salary) > 5000;

SELECT level,count(level) from player group by level having count(level) > 4 ORDER by count(level) DESC
/代码如having处，省略，注意必须要配合 ORDER BY 使用
LIMIT 3  -- 只取前三条
LIMIT 3,3 -- 返回第四到第六名 第一个三表示偏移量 第二个表示往后取三个

-- 查询所有玩家的性别
SELECT DISTINCT sex from player;

SELECT * FROM player WHERE level between 1 AND 3
UNION  -- UNION合并
SELECT * FROM player WHERE exp between 1 AND 3

SELECT AVG(level) from player;
select * from player where level > (SELECT AVG(level) from player) 
-- 使用 as 和 round
SELECT level,ROUND((SELECT AVG(level) from player)) as average;

-- 创建新表
create table new_player select * from player where level < 5;
-- 插入数据
insert into new_player select * from player where level BETWEEN 6 and 10;

WITH SAL_GRADE AS (
    SELECT GRADE, LOSAL, HISAL FROM SALGRADE
)
SELECT e.ENAME, e.SAL, (
    SELECT s.GRADE FROM SAL_GRADE s WHERE e.SAL BETWEEN s.LOSAL AND s.HISAL
) AS GRADE
FROM EMP e;

SELECT Sno, Sname
FROM tb_name
-- IN 谓词 子查询返回多个行
WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname = '张三');
-- 比较运算符 返沪一个聚合查询的结构
WHERE Grade > (SELECT AVG(Grade) FROM SC);
-- 带 EXISTS 谓词的子查询 返回是否存在1/0
WHERE EXISTS (SELECT * FROM SC WHERE SC.Sno = Student.Sno AND Cno = '001');
-- ANY/ALL  返回多个行
WHERE Grade >= ALL (SELECT Grade FROM SC WHERE Cno = '002');

SELECT 
    r.name AS 零售商名称,
    (
        SELECT COUNT(*)
        FROM "Order" o
        WHERE o.rID = r.rID 
          AND o.type = '普通订单'
    ) AS 普通订单数量,
    (
        SELECT COUNT(*)
        FROM "Order" o
        WHERE o.rID = r.rID 
          AND o.type = '加急订单'
    ) AS 加急订单数量
FROM Retailer r;

-- 此种会给出约束的完整定义，结果包含名字 + 具体是哪个字段做主键
SELECT conname, pg_get_constraintdef(oid) AS constraint_def
FROM pg_constraint
WHERE conrelid = 'table_name'::regclass
  AND contype = 'p';
  
 -- 只查询 conname（约束名称），只有名字，没有变量名（或者说字段）
 SELECT conname 
FROM pg_constraint 
WHERE conrelid = 'llz_dept'::regclass AND contype = 'p';

-- 修改主键
-- 1. 删除现有主键约束（假设约束名为 old_key）
ALTER TABLE emp DROP CONSTRAINT old_key;

-- 2. 添加新主键约束到目标列（deptno） 此时CONSTRAINT new_key是该改变主键约束名
ALTER TABLE emp ADD CONSTRAINT new_key PRIMARY KEY (deptno);
 
 

-- 父表
CREATE TABLE classes (
    class_id INT PRIMARY KEY,
    class_name VARCHAR(50)
);

-- 创建时
-- 子表，定义外键
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    class_id INT,
    CONSTRAINT fk_student_class   -- 可选，为约束命名
    FOREIGN KEY (class_id) REFERENCES classes(class_id)
);

-- 修改表时：
ALTER TABLE students
ADD CONSTRAINT fk_student_class
FOREIGN KEY (class_id) REFERENCES classes(class_id);

-- delete
ALTER TABLE students DROP FOREIGN KEY fk_student_class;  -- MySQL
ALTER TABLE students DROP CONSTRAINT fk_student_class; -- (PostgreSQL/SQL Server)

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    class_id INT,
    FOREIGN KEY (class_id) REFERENCES classes(class_id)
    ON DELETE SET NULL      -- 班级删除后，学生 class_id 变为 NULL
    ON UPDATE CASCADE       -- 班级 ID 更新时，学生 class_id 同步更新 
    -- 除此之外 SET DEFAULT 变为默认值 RESTRICT / NO ACTION阻止父表的删除/更新操作保护重要参照数据不被误删
);

SELECT 列名 AS 别名 FROM 表名;

SELECT 别名.列名 FROM 表名 AS 别名;

FROM orders o -- o作为order的别名

WITH 别名 AS (
    -- 子查询
    SELECT ...
)
-- 此时这个表已经经历了一轮筛选，并且被起了别名
-- 主查询使用这个 
SELECT * FROM 别名;

-- 查询每个员工及其部门名称
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;
-- 主表：e 被组合的表：e，此时我们可以在select后e.name AS ENAME来起别名，这样当两个表名称一样时则可以右更好的可读性
-- ON 后面跟条件，表示只有当id相同时才会被并入e表里

-- 左
SELECT s.student_id, s.name, sc.score
FROM students s
LEFT JOIN scores sc ON s.student_id = sc.student_id;
-- 保留了左表 students 的所有行
-- 右
SELECT s.student_id, s.name, sc.score
FROM students s
RIGHT JOIN scores sc ON s.student_id = sc.student_id;
-- 保留了右表 scores 的所有行
-- 一般主要使用左连接

SELECT 
    r.rName AS 图书室名称,
    COUNT(CASE WHEN bp.bpPrice > 50 THEN 1 END) AS 单价超50元图书数量
FROM Room r
LEFT JOIN Shelf s ON r.rID = s.rID
LEFT JOIN BookSKU bs ON s.sID = bs.sID
LEFT JOIN BookPub bp ON bs.bpID = bp.bpID
GROUP BY r.rID, r.rName
ORDER BY r.rName;

-- 查询所有学生的姓名和老师的姓名（合并为一列，且不重复）
SELECT column1, column2 FROM tableA
UNION
SELECT column1, column2 FROM tableB;

SELECT DISTINCT s.name -- 直接使用DISTINCT去除重复记录 
FROM students s  -- 虽然INNER JOIN逻辑是取两个表都有的部分，但是最终指向的还是s表，所以IDSTINCT s 
INNER JOIN teachers t ON s.name = t.name;

SELECT column1, column2 FROM tableA
EXCEPT
SELECT column1, column2 FROM tableB;

SELECT DISTINCT s.name
FROM students s
LEFT JOIN teachers t ON s.name = t.name
WHERE t.name IS NULL;