sudo systemctl start/stop mysql

sudo service mysql start/stop

sudo systemctl restart mysql

sudo service mysql restart

sudo systemctl status mysql

sudo service mysql status

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

CREATE USER 'john'@'localhost' IDENTIFIED BY 'password123';

DROP USER 'username'@'host';

ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

-- 删除旧用户
DROP USER 'john'@'localhost';

-- 重新创建用户并指定新的主机
CREATE USER 'john'@'%' IDENTIFIED BY 'password123';

GRANT privileges ON database_name.* TO 'username'@'host';

GRANT ALL PRIVILEGES ON test_db.* TO 'john'@'localhost';

FLUSH PRIVILEGES;

SHOW GRANTS FOR 'username'@'host';

REVOKE ALL PRIVILEGES ON test_db.* FROM 'john'@'localhost';

CREATE USER 'john'@'localhost' IDENTIFIED BY 'password123' WITH GRANT OPTION;  //加上这句
GRANT ALL PRIVILEGES ON test_db.* TO 'john'@'localhost';

SHOW TABLE STATUS FROM 数据库名;
SHOW TABLE STATUS from RUNOOB LIKE 'runoob%'; 
#显示以runoob%开头的表的信息
SHOW TABLE STATUS from RUNOOB LIKE 'runoob%'\G;
#\G，查询结果按列打印

SHOW DATABASES;

CREATE DATABASE [IF NOT EXISTS] 数据库名;
如果要指定选项，可以加上其他参数：
CREATE DATABASE mydatabase
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_general_ci;
指定字符集和排列顺序。
如果不加IF NOT EXISTS并且存在该数据库，会报错

mysqladmin -u your_username -p your_command
例如：
mysqladmin -u your_username -p status

mysqli_query(connection,query,resultmode);
也有返回值,也是用于判断是否成功

DROP DATABASE [IF EXISTS] <database_name>;
同样，如果不加IF EXISTS，删除不存在的，会报错

<?php
$dbhost = 'localhost';  // mysql服务器主机地址
$dbuser = 'root';            // mysql用户名
$dbpass = '123456';          // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('连接失败: ' . mysqli_error($conn));
}
echo '连接成功';
mysqli_select_db($conn, 'RUNOOB' );
mysqli_close($conn);
?>

SHOW TABLES; 
SHOW TABLE STATUS;  //这是看表类型（ENGING等）
SHOW COLUMNS FROM table_name;  //这是看表结构，不是数据

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);
//column是列名

CREATE TABLE mytable (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    birthdate DATE,
    is_active BOOLEAN DEFAULT TRUE
);
//AUTO_INCREMENT PRIMARY KEY,设定该列自增长(1,2,3...)，且是主键
（这两者一般同时出现）
注意：每个表只能有一个 AUTO_INCREMENT 列，必须为整数类型
	即使删除了表中最大自增值的记录，下一次插入新记录时，自增值仍然会继续
	递增，不会复用已删除的自增值。
	起始值默认为1，但可以用`ALTER TABLE users AUTO_INCREMENT = 100;`改
//NOT NULL，这项必填
//DEFAULT TRUE，设定默认值为TRUE
以上三行都是附加的属性

DROP TABLE [IF EXISTS] table_name; //这是直接删表
TRUNCATE TABLE table_name;         //这是删表数据（保留结构，数据全删）
DELETE FROM table_name WHERE condition; //这也是删表数据（保留结构，加条件可不全删）
注意：TURUNCATE是直接释放数据页的空间，没有详细的删除的日志，因此不能触发任何触发器，也不能事务回滚
但是更加高效，delete是逐行删除，会触发，能回滚，但更低效

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

INSERT INTO users (username, email, birthdate, is_active)
VALUES
    ('test1', 'test1@runoob.com', '1985-07-10', true),
    ('test2', 'test2@runoob.com', '1988-11-25', false),
    ('test3', 'test3@runoob.com', '1993-05-03', true);

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;   //where子句可选，没有就会更新所有行

ALTER TABLE table_name
ADD COLUMN new_column_name datatype;
以及
ALTER TABLE table_name
ADD PRIMARY KEY (column_name);
以及
ALTER TABLE testalter_tbl ENGINE = MYISAM;

ALTER TABLE child_table
ADD CONSTRAINT fk_name
FOREIGN KEY (column_name)           
REFERENCES parent_table (column_name);

ALTER TABLE TABLE_NAME
MODIFY COLUMN column_name new_datatype;

ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name datatype;
-- 可以顺手改数据类型
以及
ALTER TABLE old_table_name
RENAME TO new_table_name;

ALTER TABLE table_name
DROP COLUMN column_name;

ALTER TABLE testalter_tbl MODIFY c CHAR(10);
等同于
ALTER TABLE testalter_tbl CHANGE c c CHAR(10);
只不过MODIFY子句不能顺便改名

ALTER TABLE testalter_tbl MODIFY j BIGINT NOT NULL DEFAULT 100;

ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
以及
ALTER TABLE testalter_tbl ALTER i DROP DEFAULT/PRIMARY KEY;

select * from 数据表名;   //可以多表`,`分开

SELECT column1, column2, ...
FROM table_name                   //可以多表`,`分开
[WHERE condition]
[ORDER BY column_name [ASC | DESC]]
[LIMIT N][offset M];

...WHERE username = 'test';
...salary >= 50000;

...WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

...WHERE email IS [NOT] NULL;

...WHERE BINARY...

SELECT column1, column2, ... FROM table1 WHERE condition1
UNION           //加中间就行，可以叠叠乐
SELECT column1, column2, ... FROM table2 WHERE condition2
[ORDER BY column1, column2, ...];  //可选，排序合并后的数据

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

ORDER BY 3 DESC, 1 ASC;

SELECT column1, column2, aggregate_function(column3)
FROM TABLE_NAME
WHERE condition
GROUP BY column1, column2;

SELECT customer_id, SUM(order_amount) AS total_amount
FROM orders
GROUP BY customer_id;
//计算每个客户的订单总金额

SELECT column1, column2, ...
FROM table1
INNER JOIN table2 
ON table1.column_name = table2.column_name;
//只返回两表中column_name内容相同的行
//SELECT中的列也要写成类似于table.column的形式，不必包含ON使用的那列

SELECT orders.order_id, customers.customer_name, products.product_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
INNER JOIN order_items ON orders.order_id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.product_id;
//此时只有在四个表的所选列中都存在的才会返回对应的SELECT

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 
ON table1.column_name = table2.column_name;
[WHERE 条件]
//返回左表中所有行的情况下。当右表中没有匹配的行时，相关列将显示为 NULL。

SELECT * FROM employees WHERE commission <=> NULL;
`<=>`是比较表达式是否相等的特殊操作符，可以直接替换`=`
都是NULL就返回TRUE
注意：回值始终是NULL

SELECT column1, column2, ...
FROM table_name
WHERE column_name REGEXP [BINARY] 'pattern';
//'pattern'是一个正则表达式模式,例如：'^[aeiou]|ok$'，'item[0-9]+'等
//使用BINARY关键字，匹配区分大小写

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
-- 判断是否要提交还是回滚
IF (条件) THEN
    COMMIT; -- 提交事务
ELSE
    ROLLBACK; -- 回滚事务
END IF;

SELECT price FROM products WHERE id = 1 LOCK IN SHARE MODE;   -- 加共享锁
SELECT price FROM products WHERE id = 1 FOR UPDATE; -- 加排他锁

-- 事务 A 开始
START TRANSACTION;
-- 事务 A 读取数据
SELECT price FROM products WHERE id = 1; -- 假设读取到价格为 100
-- 如果改用共享锁，事务B会等待事务A释放锁

-- 事务 B 开始
START TRANSACTION;
-- 事务 B 修改数据
UPDATE products SET price = 200 WHERE id = 1;
-- 事务 B 提交
COMMIT;

-- 事务 A 再次读取数据
SELECT price FROM products WHERE id = 1; 
-- 仍然会读取到价格为 100，因为事务 A 读取的是快照中的数据
-- 事务 A 提交
COMMIT;

-- 事务 A
START TRANSACTION;
SELECT price FROM products WHERE id = 1 LOCK IN SHARE MODE;
SELECT price FROM products WHERE id = 2 FOR UPDATE; -- 尝试获取 id = 2 的排他锁

-- 事务 B
START TRANSACTION;
SELECT price FROM products WHERE id = 2 LOCK IN SHARE MODE;
SELECT price FROM products WHERE id = 1 FOR UPDATE; -- 尝试获取 id = 1 的排他锁

<?php
$dbhost = 'localhost';  // mysql服务器主机地址
$dbuser = 'root';            // mysql用户名
$dbpass = '123456';          // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('连接失败: ' . mysqli_error($conn));
}
// 设置编码，防止中文乱码
mysqli_query($conn, "set names utf8");
mysqli_select_db( $conn, 'RUNOOB' );
mysqli_query($conn, "SET AUTOCOMMIT=0"); // 设置为不自动提交，因为MYSQL默认立即执行
mysqli_begin_transaction($conn);            // 开始事务定义
 
if(!mysqli_query($conn, "insert into runoob_transaction_test (id) values(8)"))
{
    mysqli_query($conn, "ROLLBACK");     // 判断当执行失败时回滚
}
 
if(!mysqli_query($conn, "insert into runoob_transaction_test (id) values(9)"))
{
    mysqli_query($conn, "ROLLBACK");      // 判断执行失败时回滚
}
mysqli_commit($conn);            //执行事务
mysqli_close($conn);
?>