MySQL SQL语句

三范式

1NF 原子性，属性不可分（电话包含固定和移动，不合适）
2NF 1NF基础上，表必须有主键(集合)，其余属性必须完全依赖主键（集），不能只依赖于一部分（学号+课程为主键集， 学生姓名仅依赖于学号，拆表； 主键为学生成绩，学生成绩不能完全确定一个学生，不合适）
3NF 2NF基础上，非主属性直接依赖于主键，不能传递依赖（学号，姓名，学院，院长名，院长名依赖于学院，传递依赖于学号，不合适，拆 学院+院长名为新表）

常用数据类型

整数： int bit
小数： decimal（5，2） //总共5位，小数点后2位
字符串： char（3）固定长度字符串， 如果填充为"ab"补为“ab ”  varchar(3): 可变长度字符串， ab为ab  长度均不允许超过3
        长度超过4000 用 text
日期： date time datetime year timestemp
枚举类型： enum  性别（男、女）中一个

约束
主键： primary key  外键(不建议使用) foreign key  唯一： unique 默认： default

数据库操作

登录
mysql -uroot -p  ; mysql -uroot -p123456
退出
exit/quit

显示所有数据库
show databases;
显示时间 select now();  显示版本 select version();

创建并使用数据库
CREATE DATABASE DATABASE1 CHARSET=UTF8;
显示创建： SHOW CREATE DATABASE DATABASE1;
删除数据库： DROP DATABASE DATABASE1;
使用数据库： USE DATABASE1; --DATABASE NAME
显示当前数据库： SELECT DATABASE();

数据表操作

显示所有数据表： 
SHOW TABLES;
创建数据表
CREATE TABLE TABLE1(ID INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, 
                    NAME VARCHAR(30) NOT NULL, 
                    AGE TINYINT UNSIGNED DEFAULT 18, 
                    HEIGHT DECIMAL(5,2),
                    GENDER ENUM("MALE","FEMALE") DEFAULT "MALE"
                    );
数据表重命名： RENAME TABLE TABLE1 TO NEWTABLENAME;
显示表结构： DESC TABLE1;
删除表： DROP TABLE TABLE1;
修改表结构（列）增删改查(ALTER TABLE TABLENAME)
增： ALTER TABLE TABLE1 ADD BIRTHDAY DATETIME DEFAULT "2020-2-20 10:18:56";
修改数据类型：ALTER TABLE TABLE1 MODIFY BIRTHDAY DATE DEFAULT "2020-2-20";
修改列名和数据类型： ALTER TABLE TABLE1 CHANGE BIRTHDAY BIRTH DATE DEFAULT "2020-2-20";
删除某列： ALTER TABLE TABLE1 DROP BIRTHDAY

表内操作

增  --INSERT
增加整条数据： INSERT INTO TABLE1 VALUES("0","NAME",20,"HEIGHT",DEFAULT);
增加指定列一条数据： INSERT INTO TABLE1(NAME, AGE, GENDER) VALUES("NAME", 28,"1");
增加指定列多条数据，逗号隔开： INSERT INTO TABLE1(NAME,AGE) VALUES("NAEME1",20),("NAME2",30);
  -- REPLACE INTO /INSERT增强版
REPLACE INTO TABLE1(NAME, AGE, GENDER) VALUES("NAME", 28,"1");
尝试插入，如果发现已有此行数据（根据主键或者唯一索引），先删再插

删 --DELETE 从表内删除某条数据 where指定：DELETE FROM TABLE1 WHERE NAME="NAME1" 
通常使用伪删除 增加一列 bit ALTER TABLE TABLE1 ADD IS_DELETE BIT DEFAULT 0; UPDATE TABLE1 SET IS_DELETE=1 WHERE NAME="NAME1"; 
SELECT * FROM TABLE1 WHERE IS_DELETE=1; 
改 --UPDATE SET UPDATE TABLE1 SET AGE=20,GENDER=2 WHERE NAME=NAME1;--WHERE ID>3;  
查 --SELECT 显示全部数据： SELECT * FROM TABLE1; 
条件查询 显示指定数据where限定： SELECT * FROM TABLE1 WHERE ID>2;--WHERE NAME = "NAME1" -- WHERE ID BETWEEN 12 AND 20 -- WHERE ID>12 AND ID<20; (WHERE ID>12 OR AGE>18) -- WHERE NOT (ID>12 OR AGE>18) 括号指定优先级 
显示指定列数据，并且给列命名： SELECT NAME AS 姓名,AGE AS 性别 FROM TABLE1 WHERE ID>2; 
指代表名方便查询： SELECT S.NAME, S.AGE FROM STUDENTS AS S; 

模糊查询 --LIKE 效率低于范围查询 
姓名以小开头： SELECT AGE FROM TABLE1 WHERE NAME LIKE '小%'; -- % 类似正则的 ^小.* 
有两个字的名字： SELECT AGE FROM TABLE1 WHERE NAME LIKE '__'; 
名字含有小： SELECT AGE FROM TABLE1 WHERE NAME LIKE '%小%'; 
范围查询 --IN ; BETWEEN AND; NOT BETWEEN AND SELECT AGE FROM TABLE1 WHERE NAME IN ('A', 'B') WHERE NAME BETWEEN 12 AND 23 
正则表达式模糊查询 rlike SELECT AGE FROM TABLE1 WHERE NAME RLIKE '^周.*伦$'

排序  --ORDER BY 某列
SELECT * FROM TABLE1 WHERE AGE BETWEEN 18 AND 28 ORDER BY AGE ASC;  -- DESC从大到小；
SELECT * FROM TABLE1 WHERE AGE BETWEEN 18 AND 28 ORDER BY AGE ASC， ID DESC;  --多键排序

分组、聚合

聚合函数
-- 计算个数
SELECT COUNT(*) AS 数量 FROM TABLE1 WHERE AGE BETWEEN 18 AND 28;
-- 计算最大值，最小值
SELECT MAX(HEIGHT) AS 最大值 FROM TABLE1 WHERE AGE BETWEEN 18 AND 28;
SELECT MIN(HEIGHT) AS 最小值 FROM TABLE1 WHERE AGE BETWEEN 18 AND 28;
-- 求和, 均值
SELECT SUM(AGE) AS 总年龄 FROM TABLE1 WHERE AGE BETWEEN 18 AND 28;
SELECT AVG(AGE) AS 平均年龄 FROM TABLE1 WHERE AGE BETWEEN 18 AND 28;
-- 四舍五入并保留两位小数
SELECT round(SUM(AGE)/COUNT(*),3) AS 平均年龄 FROM TABLE1 WHERE AGE BETWEEN 18 AND 28;

分组  -- GROUP BY  分组要和聚合共同使用
-- 对分组结果计算数量
SELECT GENDER, COUNT(*) FROM TABLE1 GROUP BY GENDER;
-- 计算分组后男性数量  WHERE  需在GROUP 前 相当于先筛选在分组
SELECT GENDER, COUNT(*) FROM TABLE1 WHERE GENDER="男" GROUP BY GENDER;

-- 查询分组后，组内人员信息 GROUP_CONCAT  将内部成员连接成字符串
SELECT GENDER, GROUP_CONCAT(NAME) FROM TABLE1 GROUP BY GENDER;
SELECT GENDER, GROUP_CONCAT(NAME，"_", AGE,"_", ID) FROM TABLE1 WHERE GENDER=1 GROUP BY GENDER;

-- 分组条件查询 HAVING  对查出结果进行筛选
-- 查询平均年龄超过30岁的性别，姓名
SELECT GENDER, GROUP_CONCAT(NAME), AVG(AGE) FROM TABLE1 GROUP BY GENDER HAVING AVG(AGE)>30;
-- 查询每种性别中人数多于2个的信息
SELECT GENDER, GROUP_CONCAT(NAME), COUNT(*) FROM TABLE1 GROUP BY GENDER HAVING COUNT(*)>2;

分页

分页 --LIMIT START COUNT  LIMIT在语句最后
SELECT * FROM TABLE1 LIMIT 1, 5;  -- 起始下标， 数据条数（最大条数，不足显示现有）
SELECT * FROM TABLE1 WHERE AGE>18 LIMIT 1, 5;
SELECT * FROM TABLE1 WHERE AGE>18 ORDER BY AGE DESC LIMIT 1, 5;

 连接查询

-- 内连接 INNER JOIN 取交集 TABLE1 INNER JOIN TABLE2 ON 条件
SELECT * FROM STUDENTS AS S INNER JOIN CLASS AS C ON S.CLS_ID = C.ID;
SELECT S.*, C.ID FROM STUDENTS AS S INNER JOIN CLASS AS C ON S.CLS_ID=C.ID ORDER BY C.ID;
-- 外连接 左连接 LEFT JOIN 右连接 RIGHT JOIN
SELECT * FROM STUDENTS AS S LEFT JOIN CLASS AS C ON S.CLS_ID = C.ID;  -- 左表扩展
-- 新表条件用HAVING
SELECT * FROM STUDENTS AS S LEFT JOIN CLASS AS C ON S.CLS_ID = C.ID HAVING S.AGE=18;
SELECT * FROM STUDENTS AS S LEFT JOIN CLASS AS C ON S.CLS_ID = C.ID WHERE S.AGE=18;

-- 自关联
SELECT * FROM AREAS AS PROVIENCE INNER JOIN AREAS AS CITY ON CITY.PID = PROVIENCE.AID HAVING PROVIENCE.ATITLE="山东省";

-- 子查询  SELECT 嵌入 SELECT 效率低
SELECT * FROM TABLE1 WHERE AGE (SELECT MAX(AGE) FROM TABLE1);

 视图

视图 VIEW --执行查询语句后产生的虚拟表 原表改视图变
-- 创建视图
CREATE VIEW VIEWNAME AS SELECT语句;
-- 查看视图
SHOW TABLES; --会显示出视图
-- 查询，视图的作用就是查询
SELECT * FROM VIEWNAME;
-- 删除视图
DROP VIEW VIEWNAME;

事务

事务 ACID 原子性，一致性，隔离性，持久性
-- 开启事务
START TRANSACTION;  --BEGIN;
-- 提交
COMMIT;
-- 回滚
ROLLBACK;

索引

-- 提高查询效率
-- 开启运行时间监测
SET PROFILING=1;
SELECT语句;
SHOW PROFILES;  --显示运行时间
--建立索引 CREATE INDEX... ON...
CREATE INDEX TITLE_INDEX ON TABLE1(NAME(10));  --如果索引属性为字符串，需要括号写上长度
CREATE INDEX TITLE_INDEX ON TABLE1(AGE); 

-- 查看索引
SHOW INDEX FROM TABLENAME;
-- 删除索引
DROP INDEX INDEX_NAME ON TABLENAME;

 账户管理

-- 创建账户&授权
GRANT 权限列表 ON 数据库 TO 'USERNAME'@'HOST' IDENTIFIED BY 'PASSWORD';
-- 老王，本地，密码123456，对jd数据库内所有表具有查询权限；
GRANT SELECT ON JING_DONG.* TO 'LAOWANG'@'LOCALHOST' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON JING_DONG.* TO 'LAOWANG'@'LOCALHOST' IDENTIFIED BY '123456';
-- 修改密码
UPDATE USER SET AUTHENTICATION_STRING=PASSWORD('NEWPASSWORD') WHERE USER='USERNAME';
-- 刷新权限
FLUSH PRIVILEGES

-- MySQL8 需要先创建用户再赋予权限
-- 创建账户
create user 'LAOWANG'@'localhost' identified by '123456';
-- 赋予权限
GRANT SELECT ON JING_DONG.* TO 'LAOWANG'@'LOCALHOST';

-- 删除用户
DROP USER 'USERNAME'@'HOST';
DELETE FROM USER WHERE USER='USERNAME';

-- 所有操作后都需要刷新权限  FLUSH PRIVILEGES;

MySQL主从

-- 1.手动备份主服务器数据 
-- 数据备份
MYSQLDUMP -UROOT -P DATABASE_NAME > TEST.SQL;  --输入密码 >重定向
MYSQLDUMP -UROOT -P --ALL-DATABASES --LOCK-ALL-TABLES > ~/MASTER_DB.SQL;  -- 导出所有数据并锁定
-- 数据恢复
MYSQL -UROOT -P NEW_DATABASE_NAME < TEST.SQL;
MYSQL -UROOT -Pmysql  < TEST.SQL;
-- 2.编辑配置文件
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
修改 83，84 行无注释， server-id=1, log_bin = /var/log/mysql/mysql-bin.log 
-- 重启服务器
sudo service mysql restart
-- 3. 配置从服务器
-- 编辑配置文件
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
修改83 行 id不为1， 84 行保持注释
--4. 主服务器配置从属用户，新建用户并赋予权限
GRANT REPLICATION SLAVE ON *.* TO 'SLAVE'@'LOCALHOST' IDENTIFIED BY '123456'; -- 注意权限
FLUSH PRIVILEGES;
--5. 从服务器执行
CHANGE MASTER TO MASTER_HOST='MASTER_HOST(EG:10.211.55.5)', MASTER_USER='SLAVE', MASTER_PASSWORD='123456', MASTER_LOG_FILE='MYSQL-BIN.0000006', MASTER_LOG_POS=590;

-- MASTER_LOG_FILE/POS 查看
SHOW MASTER STATUS;  -- FILE AND POSITION
-- 查看从服务器状态
SHOW SLAVE STATUS;  -- <Space>\G排版
-- SLAVE_IO_RUNNING & SLAVE_SQL_RUNNING YES则正常

 

 

 数据库八股文：https://www.cnblogs.com/niuyeji648/p/14822043.html