Mariadb
Date: . Last updated: .
SQL
常用命令
systemctl start mariadb;
systemctl status mariadb;
systemctl stop mariadb;
systemctl restart mariadb;
mysqladmin -u root -p password '123456';
mariadb -h localhost -P 3306 -u root -p
SHOW DATABASES; -- 查看库
USE sql_hr; --使用库
SHOW TABLES; -- 查看表
SHOW CREATE TABLE offices; -- 查看表的创建过程
DESC offices; --查看表的结构
CREATE DATABASE dbtest1; --建库
DROP DATABASE dbtest1; -- 删库
CREATE TABLE emplyees(id int, name varchar(15)); --建表
DROP TABLE emplyees ; --删表
INSERT INTO emplyees values(1001, "TOM"); --往表添加数据
DDL：数据定义语言 CREATE / ALTER / DROP / RENAME / TRUNCATE
DML: 数据操作语言 INSERT / DELETE / UPDATE / SELECT
DCL: 数据控制语言 COMMIT / ROLLBACK / SAVEPOINT / GRANT / REVOKE
SELECT department_id, MAX(salary)
FROM employee
WHERE department_id IN (10, 20, 30, 40) -- 过滤条件中没有聚合函数时，建议声明在WHERE子句中
GROUP BY department_id
HAVING MAX(salary) > 1000 -- 过滤条件中有聚合函数时, 过滤条件必须声明在HAVING子句中
-- 2
SELECT ...............(存在聚合函数) -- 要查询的列
-- 1
FROM .......（LEFT / RIGHT)JOIN....... ON ........ -- 要查询的表(多表连接)
WHERE -- 查询条件
GROUP BY ......
HAVING .......包含聚合函数的过滤条件
-- 3
ORDER BY ..........(ASC/ DESC) -- 排序方式
LIMIT ........... 分页
单行子查询/多行子查询
INSERT INTO employee(id, name, salary)
VALUES
(1, 'jack', 8000)
(3, 'tom', 9000)
UPDATE employee(id, name, salary) -- 更新/删除可能因为约束失败
SET salary = 1200
WHERE id = 1
h