第三章结构化查询语言

3.1 DDL 数据定义语言

Data Definition Language (DDL 数据定义语言) 用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等

3.1.1操作数据库

创建:

创建数据库：create database 数据库名称;

创建数据库，判断不存在，再创建：create database if not exists 数据库名称;

创建数据库，并指定字符集：create database 数据库名称 character set 字符集名;

创建db数据库，判断是否存在，并制定字符集为gbk：create database if not exists db character set gbk

查询:

查询所有数据库的名称:show databases;

查询某个数据库的定义信息:show create database 数据库名称;

修改:

修改数据库的字符集:alter database 数据库名称 character set 字符集名称;

删除:

删除数据库:drop database 数据库名称;

判断数据库是否存在，存在再删除:drop database if exists 数据库名称;

使用数据库:

查询当前正在使用的数据库名称:select database();

使用数据库:use 数据库名称;

3.1.2 操作表

创建表

create table 表名(

　　列名1 数据类型1,

　　列名2 数据类型2,

　　....

　　列名n 数据类型n);

常见数据库类型：

1. 字符型

　　char 固定长度存储数据无论使用几个字符都占满全部

　　varcahr 变长存储数据使用几个字符就占用几个

　　　　varchar(20) 20个字符 sql:3个字符数据库:3个字符

　　text 当存储大量的字符串时使用

2.日期时间型

　　time:表示时间类型

　　date:表示日期类型，yyyy-MM-dd

　　datetime:同时可以表示日期和时间类型 yyyy-MM-dd HH:mm:ss

3.浮点型

　　float(m,n) 单精度浮点数，占 4 个字节

　　double(m,n) 双精度浮点数，占 8 个字节

　　decimal(m,n) 高精度占m+2个字节,常用于money相关方面

　　m是有效数字长度，n是小数点后的位数

　　　　例：decimal(5,2) 是一个小数点前有 3 位数，小数点后有 2 位数的数字。

4.整数型

　　bigint 大整型:超大整数型(占64位二进制) 从-2^63到2^63-1的整型数据

　　int 整型：整数类型:(占32位二进制) 从-2^31到 2^31的整型数据

　　mediumint 中整型：中等长度的整数(占24位二进制)从-2^23到2^24-1的整型数据

　　smallint 小整型：小的整数(占16位二进制) 从-2^15到 2^15 - 1的整数数据

　　tinyint 微整型：很小的整数(占8位二进制) 从0 到255的整数数据

5.大文本

　　tinytext 长度 0~255 字节

　　text 长度 0~65535 字节

　　mediumtext 长度 0~167772150 字节

　　longtext 长度 0~4294967295 字节

例： create table student(

　　id int, name varchar(32),

　　age int ,

　　score double(4,1),

　　birthday date,

　　insert_time timestamp);

复制表

复制创建表（只会复制表结构，不会复制表中数据）：create table 表名 like 被复制的表名;

查询

查询某个数据库中所有的表名称：show tables;

查询表结构：desc 表名;

查询表的创建语句：show create table 表名;

修改

修改表名：alter table 表名 rename to 新的表名;

修改表的字符集：alter table 表名 character set 字符集名称;

添加一列：alter table 表名 add 列名数据类型;

修改列名称/类型：alter table 表名 change 列名新列名新数据类型;

alter table 表名 modify 列名新数据类型;

删除列：alter table 表名 drop 列名;

删除

drop table 表名;

drop table if exists 表名 ;

3.2 DML 数据操纵语言

Data Manipulation Language(DML 数据操纵语言)用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等

3.2.1.添加数据：

语法：insert into 表名(列名1,列名2,...列名n) values(值1,值2,值n);

INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')

蠕虫复制:将一张已经存在的表中的数据复制到另一张表中（复制表数据，成倍复制的）。

语法格式：

1.INSERT INTO 表名 1 SELECT * FROM 表名 2;

2.INSERT INTO 表名 1(列 1, 列 2) SELECT 列1, 列2 FROM 表名 2;

3.2.2.删除数据

语法：delete from 表名 [where 条件]

注意：

1. 如果不加条件，则删除表中所有记录。

2. 如果要删除所有记录

　　　1. delete from 表名; -- 不推荐使用。有多少条记录就会执行多少次删除操作

　　　2. TRUNCATE TABLE 表名; -- 推荐使用，效率更高先删除表，然后再创建一张一样的表。

　　　3. 没有添加数据的字段会使用 NULL

3.2.3.修改数据：

语法：update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];

3.3 DQL 数据查询语言

Data Query Language(DQL 数据查询语言)用来查询数据库中表的记录(数据)。关键字：select, where 等

3.3.1基础查询

多个字段的查询：select 字段名1，字段名2... from 表名；

注意：如果查询所有字段，则可以使用*来替代字段列表。

去除重复：select distinct 字段名1，字段名2... from 表名；

注意：过滤掉跟在distinct后所有字段都重复的记录，distinct必须放在要查询字段的开头。

计算列：

1. 使用四则运算计算一些列的值。（一般只会进行数值型的计算）

2. ifnull(表达式1,表达式2)：null参与的运算，计算结果都为null

表达式1：哪个字段需要判断是否为null

如果该字段为null则使用表达式2的值进行替换值。

起别名：SELECT name as uname FROM student

as：as可以省略

3.3.2条件查询

1. where子句后跟条件

2. 运算符：> 、< 、<= 、>= 、= 、<>

　　-- 查询年龄大于等于20 小于等于30

　　SELECT * FROM student WHERE age >= 20 && age <=30;

　　-- 查询年龄不等于22岁

　　SELECT * FROM student WHERE age != 22;

　　SELECT * FROM student WHERE age <> 22;

2. BETWEEN...AND

　　-- 查询年龄大于等于20 小于等于30

　　SELECT * FROM student WHERE age BETWEEN 20 AND 30;

3. IN( 集合)

mysql中in常用于where表达式中，其作用是查询某个范围内的数据。

可以将查询结果作为 IN 的列表项以实现最终的查询结果，但在子查询中返回的结果必须是一个字段列表项。

　　-- 查询年龄是10岁，20岁，20岁的学生信息

　　SELECT * FROM student WHERE age IN (10,20,30)

4. LIKE：模糊查询

　　-- 查询姓陈的有哪些？

　　SELECT * FROM student WHERE NAME LIKE '陈%';

占位符：

　　1. _:单个任意字符

　　-- 查询姓名第二个字是陈的人

　　SELECT * FROM student WHERE NAME LIKE "_陈%";

　　-- 查询姓名是3个字的人

　　SELECT * FROM student WHERE NAME LIKE '___';

　　2. %：多个任意字符

　　-- 查询姓名中包含陈的人

　　SELECT * FROM student WHERE NAME LIKE '%陈%';

5. IS NULL

　　-- 查询学生地址为NULL

　　SELECT * FROM student WHERE address IS NULL;

6. and 或 &&

　　-- 查询年龄大于等于20 小于等于30

　　SELECT * FROM student WHERE age >= 20 AND age <=30;

7. or 或 ||

　　-- 查询年龄是10岁，20岁，20岁的学生信息

　　SELECT * FROM student WHERE age = 10 OR age = 20 OR age = 30

8. not 或 !

　　-- 查询学生地址不为NULL

　　SELECT * FROM student WHERE address IS NOT NULL;

3.3.3排序查询

1. 语法：order by 子句

　　order by 排序字段1 排序方式1,排序字段2 排序方式2...

　　-- 查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩升序排序

　　select * from student order by age desc, score asc;

2. 排序方式：

　　1. ASC：升序，默认的。

　　2. DESC：降序。

注意：如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。

3.3.4聚合函数

将一列数据作为一个整体，进行纵向的计算。

1. count：计算个数

　　1. 一般选择非空的列：主键

　　2. count(*)

2. max：计算最大值

3. min：计算最小值

4. sum：计算和

5. avg：计算平均值

6. 注意：聚合函数的计算，排除null值。

　　解决方案：

　　1. 选择不包含非空的列进行计算

　　2. IFNULL函数：select count (ifnull(score,0)) from student;

3.3.5 分组查询

1. 语法：group by 分组字段；

注意：

　　1. 分组之后查询的字段：分组字段、聚合函数

　　2. where 和 having 的区别？

　　　　1. where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来

　　　　2. where 后不可以跟聚合函数，having可以进行聚合函数的判断。

　　-- 以性别分组。分别查询男、女同学的平均分,人数

　　SELECT sex , AVG(score),COUNT(id) FROM student GROUP BY sex;

　　-- 以性别分组。分别查询男、女同学的平均分,人数要求：分数低于70分的人，不参与分组,分组之后。人数要大于2个人

　　SELECT sex , AVG(score),COUNT(id) FROM student WHERE score> 70 GROUP BY sex HAVING 人数 > 2;

3.3.6嵌套查询

含in关键词的嵌套查询

　　in关键词有两种用法：

　　　　1.将可枚举的离散值写在值列表中

　　　　2.语法中的嵌套，即把另一个查询语句块写在in关键词后面的括号内

含exists关键词的嵌套查询

　　exists关键词的作用与in几乎一样，区别在于，通过exists查询返回的不是具体的值的集合，而是产生逻辑的“true”和“false”；

　　通常情况下，exists的查询速度比in关键词查询速度快一些。

查询所有选修了1号课程的学生姓名。

SELECT Sname FROM Student

WHERE EXISTS

(SELECT * FROM SC

WHERE Sno = Student.Sno AND Cno = '1');

查询与“刘晨”在同一个系学习的学生。

SELECT sno,sname,sdept FROM student

WHERE sdept = (

SELECT sdept FROM student

WHERE sname = '刘晨')；

SELECT Sno,Sname,Sdept FROM Student S1

WHERE EXISTS

(SELECT * FROM Student S2

WHERE S2.Sdept = S1.Sdept AND S2.Sname = '刘晨');

查询选修了全部课程的学生姓名。

SELECT Sname FROM Student

WHERE NOT EXISTS /*不存在课程 (在Course中循环取所有课程信息)*/

(SELECT * FROM Course

WHERE NOT EXISTS /*没选修 (在SC表中循环比对) */

(SELECT * FROM SC

WHERE Sno = Student.Sno AND Cno = Course.Cno));

查询至少选修了学生201215122选修的全部课程的学生号码。

转换为存在量词：不存在这样的课程y，学生201215122选修了y，而学生x没有选。

SELECT DISTINCT Sno

FROM SC SCX

WHERE NOT EXISTS

(SELECT *

FROM SC SCY

WHERE SCY.Sno = '201215122' AND

NOT EXISTS

(SELECT *

FROM SC SCZ

WHERE SCZ.Sno = SCX.Sno AND

SCZ.Cno = SCY.Cno));

3.3.7 集合查询

(1) 并—UNION

　　UNION：将多个查询结果合并起来时，系统自动去掉重复元组

　　UNION ALL：将多个查询结果合并起来时，保留重复元组

(2) 交—INTERSECT

(3) 差—EXCEPT

参加集合操作的各查询结果的列数必须相同，对应项的数据类型必须相同。

查询计算机科学系的学生及年龄不大于19岁的学生。

SELECT * FROM Student

WHERE Sdept = 'CS'

UNION SELECT * FROM Student

WHERE Sage <= 19;

查询选修了课程1或者选修了课程2的学生。

SELECT Sno FROM SC

WHERE Cno = '1'

UNION SELECT Sno

FROM SC WHERE Cno = '2';

查询计算机科学系的学生与年龄不大于19岁的学生的交集。

SELECT * FROM Student

WHERE Sdept = 'CS'

INTERSECT SELECT FROM Student

WHERE Sage <= 19;

查询既选修了课程1又选修了课程2的学生。

SELECT Sno FROM SC

WHERE Cno = '1'

INTERSECT SELECT Sno

FROM SC WHERE Cno = '2';

查询计算机科学系的学生与年龄不大于19岁的学生的差集。

SELECT * FROM Student

WHERE Sdept = 'CS'

EXCEPT SELECT * FROM Student

WHERE Sage <= 19;

3.3.6基于派生表的查询

1.派生表的概念：由子查询语句的查询结果所生成的表叫做派生表。

2.派生表的位置：位于from之后。由select查询语句确定。

子查询出现在FROM子句中，这时子查询生成的临时派生表成为主查询的查询对象。

查询每个学生超过自己选修课平均成绩的课程号。

select sno cno from sc,

(select sno avg(grade) from sc group by sno)

as avg_sc(avg_sno ,avg_grade ) /*派生表 as后面的语句是给派生表起别名*/

Where sc.sno= avg_sc.vg_sno and sc.grade>=avg_sc.avg_grade;

3.3.6分页查询

LIMIT 的作用就是限制查询记录的条数

1. 语法：limit 开始的索引,每页查询的条数;

2. 公式：开始的索引 = （当前的页码 - 1） * 每页显示的条数

-- 每页显示5条记录

SELECT * FROM student LIMIT 0,5; -- 第1页

SELECT * FROM student LIMIT 5,5; -- 第2页

SELECT * FROM student LIMIT 10,5; -- 第3页

-- 如果第一个参数是 0 可以省略写：

select * from student limit 5;

3. limit 是MySQL的"方言"

3.4 DCL 数据控制语言

Data Control Language(DCL 数据控制语言)用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

3.4.1用户管理

1.创建用户：CREATE USER '用户名'@'ip' IDENTIFIED BY '密码'

本地登录:create user 'test'@'localhost' identified by 'test';

远程登录:create user 'test'@'%' identified by 'test';

2.修改密码USE mysql;

用SET PASSWORD命令

set password for 用户名@localhost = password(‘新密码’);

用UPDATE直接编辑user表

UPDATE USER SET PASSWORD=PASSWORD(‘新密码’) WHERE User=’用户名’ and Host=’IP’;

-- 刷新系统权限表

FLUSH PRIVILEGES;

-- 普通方式:

update user set password=password('test') where user='test' and host=’localhost’;

flush privileges;

-- 简化方式:

mysql> set password for root@localhost = password(‘123’);

3.删除用户：DROP USER 用户名;

drop user test@localhost;

4.查询用户

-- 切换到mysql数据库

USE myql;

-- 查询user表

SELECT * FROM USER;

通配符： % 表示可以在任意主机使用用户登录数据库

3.4.2.用户权限管理

1.授权

GRANT 权限1, … , 权限n ON 数据库.* TO '用户名'@'主机名';

grant create,alter,drop,insert,update,delete,select on db.* to test@localhost;

-- 刷新系统权限表：flush privileges;

该权限如果发现没有该用户，则会直接新建一个用户。

2.撤权

REVOKE 权限1, … , 权限n ON 数据库.* FORM '用户名'@'主机名';

revoke create,select on db.* to test@localhost;

3.查权

SHOW GRANTS FOR '用户名'@'主机名';

show grants for test@localhost;

3.4.3 角色

数据库角色：被命名的一组与数据库操作相关的权限

角色的创建：create role <角色名>

角色授权:role是权限的集合

grant <权限> [，<权限>]...

[on <对象类型> <对象名>]

to <角色> [，<角色>]...

将一个角色授予其他的角色或用户:

grant <角色1> [，<角色2>]...

to <角色3> [，<用户1>]...

[with admin option]

角色权限的回收:

revoke <权限> [，<权限>]...

[on <对象类型> <对象名>]

from <角色> [，<角色> ]...

posted @ 2022-07-24 21:08 石元阅读(91) 评论(0) 编辑收藏举报

刷新页面返回顶部

张一诺

第三章结构化查询语言

3.1 DDL 数据定义语言

3.1.1操作数据库

3.1.2 操作表

3.2 DML 数据操纵语言

3.2.1.添加数据：

3.2.2.删除数据

3.2.3.修改数据：

3.3 DQL 数据查询语言

3.3.1基础查询

3.3.2条件查询

3.3.3排序查询

3.3.4聚合函数

3.3.5 分组查询

3.3.6嵌套查询

3.3.7 集合查询

3.3.6基于派生表的查询

3.3.6分页查询

3.4 DCL 数据控制语言

3.4.1用户管理

3.4.2.用户权限管理

3.4.3 角色

公告

张一诺

第三章 结构化查询语言

3.1 DDL 数据定义语言

3.1.1操作数据库

3.1.2 操作表

3.2 DML 数据操纵语言

3.2.1.添加数据：

3.2.2.删除数据

3.2.3.修改数据：

3.3 DQL 数据查询语言

3.3.1基础查询

3.3.2条件查询

3.3.3排序查询

3.3.4聚合函数

3.3.5 分组查询

3.3.6嵌套查询

3.3.7 集合查询

3.3.6基于派生表的查询

3.3.6分页查询

3.4 DCL 数据控制语言

3.4.1用户管理

3.4.2.用户权限管理

3.4.3 角色

公告

第三章结构化查询语言