如何编辑网站源代码,深圳龙岗新楼盘开盘信息,去掉网站侧边栏,做家装施工的网站前言
SQL#xff08;结构化查询语言#xff09;是数据库管理和操作的核心工具#xff0c;无论是初学者还是经验丰富的数据库管理员#xff0c;掌握常用的 SQL 语句对于高效管理和查询数据都至关重要。本文将系统性地介绍最常用的 SQL 语句#xff0c;并为每个语句提供详细…前言
SQL结构化查询语言是数据库管理和操作的核心工具无论是初学者还是经验丰富的数据库管理员掌握常用的 SQL 语句对于高效管理和查询数据都至关重要。本文将系统性地介绍最常用的 SQL 语句并为每个语句提供详细注释和实际案例帮助大家在实际项目中得心应手地使用它们。
对于影刀RPA中级开发者我们需要掌握基本SQL语句增删改查将数据插入到数据库存放比传统的Excel文本存放要好的多我们可以把数据集中管理同时便于查询不需要繁琐的合并数据
数据定义语言DDL
数据库操作
创建数据库CREATE DATABASE my_database;
CREATE DATABASE test_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;删除数据库DROP DATABASE my_database;
DROP DATABASE test_db;选择数据库USE my_database;
USE test_db;表操作
创建表CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(100) NOT NULL,email VARCHAR(100) UNIQUE NOT NULL
);
CREATE TABLE orders (order_id INT PRIMARY KEY AUTO_INCREMENT,user_id INT,amount DECIMAL(10, 2),FOREIGN KEY (user_id) REFERENCES users(id)
);删除表DROP TABLE users;
DROP TABLE orders;修改表ALTER TABLE users ADD COLUMN age INT;
ALTER TABLE users MODIFY COLUMN email VARCHAR(150);
ALTER TABLE users DROP COLUMN age;数据操作语言DML
插入数据
插入单条数据INSERT INTO users (name, email, age) VALUES (John Doe, john.doeexample.com, 30);插入多条数据INSERT INTO users (name, email, age) VALUES
(Jane Smith, jane.smithexample.com, 25),
(Robert Brown, robert.brownexample.com, 40);
INSERT INTO orders (user_id, amount) VALUES (1, 99.99);删除数据
删除特定数据DELETE FROM users WHERE name John Doe;
DELETE FROM users WHERE age 30;
DELETE FROM orders WHERE amount 50;更新数据
更新特定数据UPDATE users SET age 31 WHERE name John Doe;
UPDATE users SET age age 1;
UPDATE orders SET amount amount * 1.1 WHERE user_id 1;数据查询语言DQL
基础查询
查询所有记录SELECT * FROM users;
SELECT * FROM orders;查询特定列SELECT name, email FROM users;
SELECT user_id, amount FROM orders;排序查询
按列排序SELECT * FROM users ORDER BY age ASC;
SELECT * FROM users ORDER BY age DESC;
SELECT * FROM orders ORDER BY amount ASC;聚合函数
计算总数SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM orders;计算平均值SELECT AVG(age) FROM users;
SELECT AVG(amount) FROM orders;计算总和SELECT SUM(amount) FROM orders;计算最大值和最小值SELECT MAX(age) FROM users;
SELECT MIN(amount) FROM orders;分组查询
按列分组SELECT age, COUNT(*) FROM users GROUP BY age;
SELECT user_id, SUM(amount) FROM orders GROUP BY user_id;分页查询
分页查询SELECT * FROM users LIMIT 10 OFFSET 10;
SELECT * FROM orders LIMIT 5 OFFSET 10;连接查询
内连接查询SELECT users.name, orders.amount
FROM users, orders
WHERE users.id orders.user_id;
SELECT users.name, orders.amount
FROM users
INNER JOIN orders ON users.id orders.user_id;外连接查询SELECT users.name, orders.amount
FROM users
LEFT JOIN orders ON users.id orders.user_id;
SELECT users.name, orders.amount
FROM users
RIGHT JOIN orders ON users.id orders.user_id;子查询
子查询结果为单行单列SELECT *
FROM users
WHERE age (SELECT MAX(age) FROM users);
SELECT *
FROM users
WHERE id (SELECT user_id FROM orders WHERE amount (SELECT MAX(amount) FROM orders));子查询结果为多行单列SELECT *
FROM users
WHERE age (SELECT age FROM users WHERE age 30);
SELECT *
FROM users
WHERE id IN (SELECT user_id FROM orders);子查询结果为多行多列SELECT *
FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount 100);
SELECT *
FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount (SELECT AVG(amount) FROM orders));数据控制语言DCL
管理用户
添加用户CREATE USER usernamelocalhost IDENTIFIED BY password;
CREATE USER adminlocalhost IDENTIFIED BY securepass;删除用户DROP USER usernamelocalhost;
DROP USER adminlocalhost;权限管理
查询权限SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR usernamelocalhost;授予权限GRANT ALL PRIVILEGES ON test_db.* TO usernamelocalhost;
GRANT SELECT, INSERT ON *.* TO adminlocalhost;撤销权限REVOKE ALL PRIVILEGES ON test_db.* FROM usernamelocalhost;
REVOKE SELECT, INSERT ON *.* FROM adminlocalhost;最后
感谢大家请大家多多支持 欢迎大家交流扣949574316
