ssh mysql 命令_mysql8常用命令

连接

-- 不要在命令行中输入密码

mysql -h localhost -P 3306 -u -p

Enter Password :

-- 指定数据库

mysql u root -p company

CREATE DATABASE company;

CREATE DATABASE `my.contacts`;

USE company;

-- 要查找连接到了哪个数据库

SELECT DATABASE();

SHOW DATABASES;

SHOW TABLES;

-- 表结构

SHOW CREATE TABLE customers;

DESC customers;

-- 当前的数据目录

SHOW VARIABLES LIKE 'datadir';

导入数据

-- 另存为文件

SELECT first_name, last_name INTO OUTFILE 'result.csv';

-- 将数据加载到表中

LOAD DATA INFILE 'result.csv' INTO TABLE

employee_names

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"'

LINES TERMINATED BY '\n';

-- 忽略1行

LOAD DATA INFILE 'result.csv' INTO TABLE

employee_names

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"'

LINES TERMINATED BY '\n'

IGNORE 1 LINES;

LOAD DATA INFILE 'result.csv' REPLACE

INTO TABLE employee_names FIELDS TERMINATED BY ','OPTIONALLY ENCLOSED BY '"' LINES TERMINATED

BY '\n';

LOAD DATA INFILE 'result.csv' IGNORE INTO

TABLE employee_names FIELDS TERMINATED BY

','OPTIONALLY ENCLOSED BY '"' LINES TERMINATED

BY '\n';

查询

-- 查询

SELECT * FROM departments;

SELECT emp_no, dept_no FROM dept_manager;

SELECT COUNT(*) FROM employees;

SELECT emp_no FROM employees WHERE first_name='Georgi' AND last_name='Facello';

-- in

SELECT COUNT(*) FROM employees WHERE last_name IN ('Christ', 'Lamba', 'Baba');

-- BETWEEN

SELECT COUNT(*) FROM employees WHERE hire_date BETWEEN '1986-12-01' AND '1986-12-31';

SELECT COUNT(*) FROM employees WHERE hire_date NOT BETWEEN '1986-12-01' AND '1986-12-31';

-- 模式匹配

SELECT COUNT(*) FROM employees WHERE first_name LIKE 'christ%';

-- 匹配单个字符

SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil';

-- 正则

SELECT COUNT(*) FROM employees WHERE first_name RLIKE '^christ';

SELECT COUNT(*) FROM employees WHERE last_name REGEXP 'ba$';

-- 别名 AS

SELECT COUNT(*) AS count FROM employees WHERE hire_date BETWEEN '1986-12-01' AND '1986-12-31';

-- 排序 限制结果数量

SELECT emp_no,salary FROM salaries ORDER BY salary DESC LIMIT 5;

-- 按多个列排序

SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name;

-- 排序方向

SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC;

-- 分组

SELECT gender, COUNT(*) AS count FROM employees GROUP BY gender;

-- 去重

SELECT DISTINCT title FROM titles;

-- HAVING 子句来过滤GROUP BY 子句的结果

SELECT emp_no, AVG(salary) AS avg FROM salaries GROUP BY emp_no HAVING avg > 140000 ORDER BY avg DESC;

-- join

SELECT vend_name, prod_name, prod_price

FROM vendors, products

WHERE vendors.vend_id = products.vend_id

ORDER BY vend_name, prod_name;

-- join 和上面一样，为等值联结，不过明确指出join，推荐

SELECT vend_name, prod_name, prod_price

FROM vendors INNER JOIN products

ON vendors.vend_id = products.vend_id;

插入

-- 插入完整的行

INSERT INTO Customers VALUES(NULL, 'Pep E. LaPew', '100 Main Street');

-- 指明列，可以插入部分，不依赖顺序

INSERT INTO customers(cust_name, cust_address) VALUES('Pep E. LaPew', '100 Main Street');

-- 插入多行

INSERT INTO customers(cust_name, cust_address) VALUES('Pep E. LaPew', '100 Main Street'),('Pep E. LaPew', '100 Main Street');

-- 插入检索出的数据

INSERT INTO customers(cust_id, cust_contact) SELECT cust_id, cust_contact FROM custnew;

INSERT IGNORE INTO `company`.`customers`(first_name, last_name,country) VALUES ('Mike', 'Christensen', 'USA');

更新

UPDATE customers SET cust_email = 'elmer@fudd.com' WHERE cust_id = 10005;

-- 多个列

UPDATE customers SET cust_name = 'The Fudds', cust_email = 'elmer@fudd.com' WHERE cust_id = 10005;

UPDATE customers SET first_name='Rajiv',country='UK' WHERE id=4;

删除

DELETE FROM customers WHERE cust_id = 10006;

-- 删除所有内容，高危

DELETE FROM customers;

-- 快速清空表内容

TRUNCATE TABLE customers;

创建和操纵表

CREATE TABLE customers

(

cust_id int NOT NULL AUTO_INCREMENT,

cust_name char(50) NOT NULL ,

cust_address char(50) NULL ,

cust_city char(50) NULL ,

cust_state char(5) NULL ,

cust_zip char(10) NULL DEFAULT '123',

cust_country char(50) NULL ,

cust_contact char(50) NULL ,

cust_email char(255) NULL ,

PRIMARY KEY (cust_id)

) ENGINE=InnoDB;

-- 修改表结构

ALTER TABLE vendors ADD vend_phone CHAR(20);

ALTER TABLE Vendors DROP COLUMN vend_phone;

-- 定义外键

ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);

-- 删除表

DROP TABLE customers2;

-- 重命名表

RENAME TABLE customers2 TO customers;

-- 克隆表结构

CREATE TABLE new_customers LIKE customers;

视图

-- 创建视图

CREATE VIEW productcustomers AS

SELECT cust_name, cust_contact, prod_id

FROM customers, orders, orderitems

WHERE customers.cust_id = orders.cust_id

AND orderitems.order_num = orders.order_num;

-- 使用视图

SELECT cust_name, cust_contact

FROM productcustomers

WHERE prod_id = 'TNT2';

存储过程

-- 执行存储过程

CALL productpricing(@pricelow, @pricehigh, @priceaverage);

-- 创建存储过程

CREATE PROCEDURE productpricing()

BEGIN

SELECT Avg(prod_price) AS priceaverage

FROM products;

END;

-- 删除存储过程

CALL productpricing();

-- 使用参数

CREATE PROCEDURE productpricing(

OUT pl DECIMAL(8,2),

OUT ph DECIMAL(8,2),

OUT pa DECIMAL(8,2)

) BEGIN

SELECT Min(prod_price)

INTO pl

FROM products;

SELECT Max(prod_price)

INTO ph

FROM products;

SELECT Avg(prod_price)

INTO pa

FROM products;

END;

-- 调用

CALL productpricing(@pricelow,

@pricehigh,

@priceaverage);

--

SELECT @priceaverage;

-- 检查存储过程

SHOW CREATE PROCEDURE ordertotal;

游标

-- 创建游标 DECLAR 定义了名为ordernumbers的游标

CREATE PROCEDURE processorders()

BEGIN

DECLARE ordernumbers CURSOR

FOR

SELECT ordernum FROM orders;

END;

OPEN ordernumbers;

CLOSE ordernumbers;

CREATE PROCEDURE processorders()

BEGIN

-- Declare the cursor

DECLARE ordernumbers CURSOR

FOR

SELECT order_num FROM orders;

-- Open the cursor

OPEN ordernumbers;

-- Close the cursor

CLOSE ordernumbers;

END;

触发器

-- 创建触发器

CREATE TRIGGER newproduct AFTER INSERT ON products

FOR EACH ROW SELECT 'Product added';

-- 删除触发器

DROP TRIGGER newproduct;

事务

-- 事务的开始

START TRANSACTION

-- ROLLBACK

SELECT * FROM ordertotals;

START TRANSACTION;

DELETE FROM ordertotals;

SELECT * FROM ordertotals;

ROLLBACK;

SELECT * FROM ordertotals;

-- 使用COMMIT

START TRANSACTION;

DELETE FROM orderitems WHERE order_num = 20010;

DELETE FROM orders WHERE order_num = 20010;

COMMIT;

-- 保留点

SAVEPOINT delete1;

ROLLBACK TO delete1;

字符集

-- 查看所支持的字符集完整列表

SHOW CHARACTER SET;

-- 查看所支持校对的完整列表

SHOW COLLATION;

-- 确定所用的字符集和校对

SHOW VARIABLES LIKE 'character%';

SHOW VARIABLES LIKE 'collation%';

-- 给表指定字符集和校对

CREATE TABLE mytable

(

columnn1 INT,

columnn2 VARCHAR(10)

) DEFAULT CHARACTER SET hebrew

COLLATE hebrew_general_ci;

-- 对每个列设置

CREATE TABLE mytable

(

columnn1 INT,

columnn2 VARCHAR(10),

column3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci

) DEFAULT CHARACTER SET hebrew

COLLATE hebrew_general_ci;

-- 在SELECT语句自身中进行

SELECT * FROM customers

ORDER BY lastname, firstname COLLATE latin1_general_cs;

安全

-- 创建用户

CREATE USER IF NOT EXISTS 'company_read_only'@'localhost' IDENTIFIED WITH mysql_native_password

BY 'company_pass'

WITH MAX_QUERIES_PER_HOUR 500

MAX_UPDATES_PER_HOUR 100;

CREATE USER ben IDENTIFIED BY 'p@$$w0rd';

-- 使用hash值

CREATE USER IF NOT EXISTS

'company_read_only'@'localhost'

IDENTIFIED WITH mysql_native_password

AS '*EBD9E3BFD1489CA1EB0D2B4F29F6665F321E8C18'

WITH MAX_QUERIES_PER_HOUR 500

MAX_UPDATES_PER_HOUR 100;

-- 重新命名

RENAME USER ben TO bforta;

-- 删除用户账号

DROP USER bforta;

-- 授予和撤销用户的访问权限

GRANT SELECT ON company.* TO 'company_read_only'@'localhost';

-- 限制查询列

GRANT SELECT(first_name,last_name) ON employees.employees TO 'employees_ro'@'%'

GRANT ALL ON *.* TO 'dbadmin'@'%';

-- 检查权限

SHOW GRANTS FOR 'employees_ro'@'%';

-- 撤销 FROM

REVOKE DELETE ON company.* FROM 'company_write'@'%';

-- user表 没事别改它

SELECT * FROM mysql.user WHERE user='dbadmin'\G

UPDATE mysql.user SET host='localhost' WHERE user='dbadmin';

FLUSH PRIVILEGES;

-- 密码有效期

-- 创建一个具有过期密码的用户

CREATE USER 'developer'@'%' IDENTIFIED WITH mysql_native_password AS

'*EBD9E3BFD1489CA1EB0D2B4F29F6665F321E8C18' PASSWORD EXPIRE;

-- 改密

ALTER USER 'developer'@'%' IDENTIFIED WITH

mysql_native_password BY 'new_company_pass';

-- 手动设置过期用户

ALTER USER 'developer'@'%' PASSWORD EXPIRE;

-- 要求用户每隔90 天更改一次密码

ALTER USER 'developer'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 锁定账户

ALTER USER 'developer'@'%' ACCOUNT LOCK;

ALTER USER 'developer'@'%' ACCOUNT UNLOCK;

-- 角色

CREATE ROLE 'app_read_only', 'app_writes', 'app_developer';

GRANT SELECT ON employees.* TO 'app_read_only';

GRANT INSERT, UPDATE, DELETE ON employees.* TO 'app_writes';

GRANT ALL ON employees.* TO 'app_developer';

GRANT 'app_read_only', 'app_writes' TO 'emp_read_write'@'%';