安装MySQL+Navicat Premium
Authentication plugin 'caching_sha2_password' cannot be loaded问题的处理方式
登陆MySQL
mysql -u root -p-
修改账户密码加密规则并更新用户密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; #修改加密规则ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; #更新一下用户的密码
刷新权限并重置密码
FLUSH PRIVILEGES; #刷新权限
数据库常见语法
增删改查
SELECT * FROM product
# 带条件查询
SELECT productName,salePrice FROM product WHERE salePrice <= 119
# like模糊查询
SELECT id,productName FROM product WHERE productName LIKE '%罗技M9__%'
# 排序
SELECT id,productName,salePrice * cutoff pf FROM product
WHERE productName LIKE '%M%'
ORDER BY pf ASC
# 多表查询
SELECT * FROM product p,productdir pd
WHERE p.dir_id = pd.id AND p.salePrice >= 200 AND pd.dirName = '无线鼠标'
# 增
INSERT INTO productdir (id,dirName,parent_id) VALUES(10,'美女鼠标',1)
INSERT INTO productdir (id,dirName,parent_id) VALUES(NULL,'美女鼠标',1)
INSERT INTO productdir (id,dirName,parent_id) VALUES(NULL,'办公鼠标1',1),(NULL,'办公鼠标2',1),(NULL,'办公鼠标3',1)
# 更新操作
SELECT * FROM product WHERE salePrice >= 300
UPDATE product SET salePrice = salePrice * 1.2
WHERE salePrice >= 300
# 删除
DELETE FROM productdir
WHERE id BETWEEN 20 AND 25
