Mysql复习

Mysql复习

切换数据库: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 排序显示。