DEV Community
·
PSQL 第二天
内容提要
本文介绍了基本的SQL命令及示例，包括创建数据库、选择数据库、创建表、插入数据、查询、更新和删除记录，帮助用户有效管理数据库。
关键要点
-
创建数据库命令：CREATE DATABASE mydatabase;
-
选择数据库命令：USE mydatabase;
-
创建表命令：CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(50), age INT, department VARCHAR(50));
-
插入数据命令：INSERT INTO employees (id, name, age, department) VALUES (1, 'Alice', 30, 'HR');
-
查询数据命令：SELECT * FROM employees; SELECT name, age FROM employees WHERE department = 'HR';
-
更新记录命令：UPDATE employees SET age = 31 WHERE id = 1;
-
删除记录命令：DELETE FROM employees WHERE id = 1;
-
删除表命令：DROP TABLE employees;
-
修改表结构命令：ALTER TABLE employees ADD salary DECIMAL(10,2);
-
修改列命令：ALTER TABLE employees MODIFY COLUMN age SMALLINT;
-
删除列命令：ALTER TABLE employees DROP COLUMN salary;
-
连接表命令：SELECT employees.name, departments.department_name FROM employees INNER JOIN departments ON employees.department = departments.id;
-
分组与过滤命令：SELECT department, COUNT() as total_employees FROM employees GROUP BY department HAVING COUNT() > 5;
-
排序结果命令：SELECT * FROM employees ORDER BY age DESC;
-
限制结果数量命令：SELECT * FROM employees LIMIT 5;
-
合并查询结果命令：SELECT name FROM employees UNION SELECT name FROM managers;
-
检查数据存在性命令：SELECT name FROM employees WHERE EXISTS (SELECT 1 FROM departments WHERE department_name = 'IT');
延伸问答
如何创建一个新的数据库？
使用命令 CREATE DATABASE mydatabase; 来创建一个新的数据库。
如何插入数据到表中？
使用命令 INSERT INTO employees (id, name, age, department) VALUES (1, 'Alice', 30, 'HR'); 来插入数据。
如何查询特定部门的员工信息？
使用命令 SELECT name, age FROM employees WHERE department = 'HR'; 来查询特定部门的员工信息。
如何更新表中某个记录的年龄？
使用命令 UPDATE employees SET age = 31 WHERE id = 1; 来更新记录的年龄。
如何删除表中的某条记录？
使用命令 DELETE FROM employees WHERE id = 1; 来删除表中的某条记录。
如何连接两个表的数据？
使用命令 SELECT employees.name, departments.department_name FROM employees INNER JOIN departments ON employees.department = departments.id; 来连接两个表的数据。
