数据库基础

安装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