Mysql复习

DDL(DATA Definition language)

数据定义语言,用来定义数据库对象:库、表、列、等

数据库或表的结构操作

操作数据库

查看所有数据库:SHOW DATABASE;

切换数据库:USE mydb1; #切换到mydb1数据库

创建数据库:CREATE DATABASE \[IF NOT EXISTS\]mydb1;

删除数据库:DROP DATABASE\[IF EXISTS\]mydb1;

修改数据库编码:ALTER DATABASE mydb1 CHARACTER SET utf8;

操作表

#创建表
CREATE TABLE 表名(
列名 列类型，
列名 列类型，
......
);

#查看当前数据库中所有表名称:
SHOW TABLES;

#查看指定表的创建语句:
SHOW CREATE TALBE emp; #查看emp表的创建语句

#查看表结构:
DESC emp; #查看emp表结构

#删除表:
DROP TABLE emp; #删除emp表

#查询表中所有数据
SELECT * FROM students

#修改表:
#添加列:给stu表添加classname列:
ALTER TABLE stu ADD(classname varchar(100));

#修改列类型:修改stu表的gender列类型为CHAR(2);
ALTER TABLE  stu MODIFY gender CHAR(2);

#修改列名：修改stu表的gender列名为sex：
ALTER TALBE stu CHANGE gender sex CAHR(2);

#删除列: 删除stu表的classname列：
ALTER TABLE stu DROP classname;

#修改表名称：修改stu表名称为student：
ALTER TABLE stu RENAME TO student;

---

DML(DATA Manipulation Language)

数据操作语言,用来定义数据库记录(数据);

对表的记录进行更新(增、删、改)

#插入数据
INSERT INTO 表名(列名1,列名2...) VALUES(值1,值2)；

#因为没有指定要插入的列，表示按创建表时列的顺序插入所有的列
INSERT TNTO 表名 VALUES(值1，值2...);

`注意:所有字符串数据必须使用单引号`

#修改数据
UPDATA 表名  SET 列名1=值1,...  列名n=值n WHERE 条件

#删除数据
DELETE FROM 表名 WHERE 条件

TRUNCATE TALBE 表名

#DLETE 效率没有TRUNCATE 高
#TRUNCATE先DROP TABLE ,再CREATE TABLE 。而且TRUNCATE删除的记录是无法回滚的,但DELETE删除的记录是可以回滚的

---

DCL(Data Control Language)

数据控制语言,用来定义访问权限和安全级别;

对用户的创建,及授权

#创建用户
CREATE USER 用户名@地址 IDENTIFIED BY '密码';

#给用户授权
GRANT 权限1,...,权限n ON 数据库.*TO 用户名

#撤销授权
REVOKE 权限1,...,权限n ON 数据库*.FORM 用户名

#查看用户权限
SHOW GRANTS FOR 用户名

#删除用户
DROP USER 用户名

#修改用户密码
USE mysql;  
UPDATE USER SET PASSWORD=PASSWORD('密码')WHERE User='用户名' and Host='IP';  
FLUSH PRIVILGES;

---

DQL(Data Query Language)

数据查询语言,用来查询记录(数据)。

对表的记录的查询(难)

#要查询的列名称
SELECT selection_list

#要查询的表名称
FROM table_list

#行条件
WHERE condition

#对结果分组
GROUP BY grouping_colums

#分组后的行条件
HAVING condition

#对结果分组
ORDER BY sorting_colums 

#结果限定
LIMIT offset\_start,row\_count

字段控制查询

#去除重复记录
SELECT DISTINCT sal FROM emp;

#查看雇员的月薪与佣金之和
SELECT *,sal+IFNULL(comm,0)FROM emp

#更换列名(换成total)
SELECT *,sal+IFNULL(comm,0) total FROM emp;

聚合函数

用来做纵向运算的函数

COUNT()：统计指定列不为NULL的记录函数

#查询emp表中记录数
SELECT COUNT(*) AS cnt FROM emp;

#查询emp表中有佣金的人数;
SELECT COUNT(comm) cnt FROM emp;

#注意，因为count()函数中给出的是comm列，那么只统计count列非NULL的行数

分组查询

#查询每个部门的编号以及每个部门的人数
SELECT DEPTNO,COUNT(*) FROM EMP GROUP BY DEPTNO;

#查询每个部门的部门编号以及每个部门工资大于1500的人数
SELECT DEPTNO,COUNT(*) FROM EMP WHERE sal>1500  GROUP BY DEPTNO;

HAVING子句

#查询工资和大于9000的部门编号以及工资和
SELECT DEPTNO,SUM(sal) FROM emp GROUP BY DEPTNO HAVING SUM(sal) >9000;

LIMIT

#LIMIT用来限定查询结果的起始行，以及总行数

#查询5行记录,起始行从0开始
SELECT * FROM emp LIMIT 0,5;

#注意，起始行从0开始，即第一行开始
#查询10行记录,起始行从3开始
SELECT * FROM emp LIMIT 3,10;

#分页查询
#如果一页记录为10条,希望查看第3页记录应该怎么查呢?
#第一页记录起始行为0，一共查询10行;
#第二页记录起始行为10，一共查询10行;
#第三页记录起始行为20，一共查询10行

#第一步分析：
#要查哪些
#列
#表
#条件

---查询组合字段
SELECT
---连表
FROM
---查询条件
WHERE
---分组
GROUP BY
---分组条件
HAVING
---排序
ORDER BY
select top(4)  status , max(m.id) as maxMemberID  
from \[dbo\].\[Member\] as m right outer join \[dbo\].\[Order\] as o   
on m.id=o.member_id   
where m.id>0  
group by status   
having status>=0  
order by maxMemberID asc

第一步，从from开始
1.1、加载左表
from \[dbo\].\[Member\] as m 
-   查询结果：rember表中的所有数据
    

1.2、在sql中被定义分解为2个步骤，即join ，right outer join 。表达式关键字从左到右,依次执行。
join \[dbo\].\[Order\] as o 
查询结果，存入伪表，为两个表的笛卡尔集合
    
1.3、on筛选器
on m.id=o.member_id 
从上一步的笛卡尔集合35条数据中删除掉不匹配的行，就得到啦5条数据，存入虚拟表Vt2
    

1.4、添加外部行(outer row)
right outer join \[dbo\].\[Order\] as o 
右表（order）作为保留表，把剩余的数据重新添加到上一步的虚拟表中vt2，生成虚拟表vt3.
    

第二部，进入where 阶段
where m.id>0
查询结果：存入虚拟表vt4，为筛选的条件为true的结果集，这里加入一个记忆点，就是，where的筛选删除为永久的，
而on的筛选删除为暂时的，因为on筛选过后，有可能会经过outer添加外部行，重新把数据加载回来，而where则不能。
    

第三部，group by分组
group by status
查询结果：存入vt5，以status列的数值开始分组，即status列，值一样的分为一组，这里的两个null在三值逻辑中被视为true。
三值逻辑：true，false，null。此三值，null为未知，是数据的逻辑特色，有的地方两个null相等为ture，在有些地方则为false。

第四部,having筛选器
having status>=0 
查询结果：筛选分好组的组数据，把不满足条件的删除掉

第五步,select 查询挑拣计算列

5.1、计算表达式
select status , max(m.id)
查询结果：从分过组的数据中计算各个组中的最大m.id,列出要筛选显示的列。

5.2、distinct过滤重复

5.3、top 结合order by 筛选 多少行，但这里的数据没有排序只是把多少行数据列出来而已。

**第六部，order by 排序显示。**

数据库调优

1 避免在where子句中进行null判断
2 避免在where子句中使用!=
3 避免在where子句中进行or条件连接
4 避免使用select*，而应该使用具体的字段名
5 避免使用like关键字