Mysql概念和查询使用

● 数据库概念和Mysql使用

一、数据库相关概念

Data、Database（DB）、DBMS、DBS、DBA

二、SQL: 结构化查询语言

SQL分类：

数据定义语言 ：DDL（针对数据库对象）:create-创建、alter-修改、drop-删除

数据操纵语言 ：DML（针对数据信息）:insert-插入、update-更新、delete-删除

数据查询语言 ：DQL（针对数据信息）：select-查询

数据控制语言 ：DCL（针对用户）：grant-授权、revoke-回收

三、服务及连接操作

1、 启动/关闭服务

启动指定服务：net start 服务名

关闭指定服务：net stop 服务名

2、 连接服务

mysql -u 用户名 -p -h -P

user password host port

默认用户名为：root，用户名为mysql数据库user表中user、host字段的组合，如果host字段值为%，则不需要书写，其他则如test@172.168.0.4

3、 查看所有数据库

show databases;

4、 使用数据库（表示后期操作都针对该数据库进行）

use 数据库名;

5、 查看指定数据库下边所有数据表

show tables;

四、用户管理

1、 创建用户

法一：创建用户（同时设置密码）

语法：create user 用户名称 [identified by ‘密码’];

法二：创建用户同时分配权限

语法：grant 权限 on 范围 to 用户 identified by ‘密码’;

2、 删除用户

语法：drop user 用户名;

说明：

A、ceshi@192.168.1.2表示“ceshi”用户可通过IP为“192.168.1.2”的地址访问指定数据库服务器

B、ceshi@192.168.1.%表示“ceshi”用户可通过IP为“192.168.1.0”至“192.168.1.255”之间的地址访问指定数据库服务器

C、create user c identified by ‘111’;

上边语句创建用户c对应的host地址为“%”

3、 更改密码

A、更改指定用户密码

语法：set password for 用户名=password(‘密码’);

B、更改自己的登录密码

语法：set password=password(‘密码’)；

五、权限管理

1、 授权

语法：grant 权限 on 范围 to 用户 [with grant option];

说明：如果在授权语句中出现with grant option，代表被授权的用户可将自己的权限授予给别人

2、 回收

语法：revoke 权限 on 范围 from 用户;

3、 刷新权限机制

flush privileges;

A、权限作用范围及分类

① 全局权限Global Privileges

它是管理权限，作用到服务器上的所有数据库。要授予全局权限，使用 ON *.的语法，.*表示所有数据库的所有表（对象，视图等）中。MySQL 把全局权限保存在 mysql.user 表中

② 数据库权限Database Privileges

作用到某个特定数据库的所有对象上。要授予数据库权限，使用 ON db_name.* 的语法，db_name.*表示db_name数据库下的所有表（对象，视图等）。 MySQL 把数据库权限保存在 mysql.db 表中

③ 表权限Table Privileges

应用到某个特定表的所有列上。要授予表权限，使用 ON db_name.tbl_name 的语法。 MySQL 把表权限保存在 mysql.tables_priv 表中

④ 列权限

权限操作分类： 使用逗号分隔

◆ 列权限

作用在特定表的特定列上，在权限操作分类中体现列权限 select（col_name），记录到mysql.columns_prive表中 field

◆ 存储过程权限

作用在存储过程和函数上，记录到mysql.procs_prive表中 procedure

◆ 代理用户权限

作用是使一个用户成为另一个用户的代理，记录到mysql.proxies_priv表中 proxy

B、查看用户当前权限

语法：show grants for 用户名;

六、建库建表

注意：在SQL语法中，[]表示可有可无

1、 创建数据库语法：create database 数据库名;

2、 创建表语法：create table 表名(

​ 字段名 字段类型(长度) …

);

3、 MySQL数据类型

​ ①数值型

​ ◆整型（tinyint，smallint，mediumint， int，bigint）

​ ◆ 浮点型（float，double）

​ ◆ 定点型（decimal）

​ ② 字符型（char，varcharl blob，textl enum（多选一），set（多选多））

​ ③ 日期和时间类型（date，time，datetime，timestamp，year）

4、 字段属性unsigned、zerofill的用法！！！

​ ① unsigned 为非负数，用此类型可以增加存储数值范围!

​ ② zerofill属性，在数字长度不够的数据前面填充0，以达到设定的长度

六、插入语句

语法：inset into 表名[(字段1[,字段2,…])] values(值1,值2,…);

注意：

1、 如果针对表中所有字段添加数据，可省略字段列表

2、 字段列表不是表中所有字段时，非空、无默认值的字段必须出现

3、 值列表与字段列表必须一一对应（数量、数据类型）

4、 对于字段列表中有允许为空字段，其值可使用default或null替代

5、 对于字段列表中有默认值字段，其值可使用default替代

强调：针对字符、日期和时间类型对应的数据值，在SQL语句中须使用英文单引号括起来！

七、更新语句

语法：update 表名 set字段1=值1[,字段2=值2,…] [where 条件表达式];

注意：在update语句中，如果没有出现where子句，则表示针对表中所有数据进行更新操作；如果表中出现where子句，则必须先根据条件表达式筛选数据，再行更新操作

八、注释

1、SQL标准注释方式：

​ ◆ 单行注释：-- 注释内容（提醒：在第二个中横线后打一空格，再添加注释内容方有效）

​ ◆ 多行注释：/注释内容/

2、MySQL注释方式：

​ ◆ #注释内容

●数据库增删改操作

一、自动递增（auto_increment）

◆ auto_increment是数据列的一种属性，只适用于数字类型

◆ auto_increment对应数据列必须具备not null

◆ 一个表只能有一个字段使用auto_increment

◆ 将字段设置成auto_increment之后，是需要将其设置成主键/或者主键的一部分

◆ 用户添加数据时，该字段的值可不用添加

◆ 可在建表时可用auto_increment =n选项来指定一个自增的初始值

◆ 可用alter table 表名 auto_increment =n命令来重设自增的起始值，默认的起始值、步长均是1

二、enun、set的应用

1、 enum：枚举字符串（多选一）

◆ 将可能出现的数据列举出来，实际存储的数据只能是列举出来的数据

◆ 使用枚举有利于统一数据，以及节省存储空间（枚举只是存储数值，系统转换成字符串）

◆ 枚举元素实际上按定义的顺序标号，从1开始，所以可以直接插入枚举元素对应的数值

◆ enum的选项对应一个数字，依次是1，2，3，4，5…，最多有65535个选项

如：enum(‘a’,’b’,’c’)，如果某行的对应值为2，则选中了“b”

2、 set：集合字符串（多选多）

◆ 定义了集合之后，不能插入集合元素之外的字符串，（可以插入多个符合条件的字符串）

◆ 集合字符串实际上也是仅仅存储数值，系统自动转换成对应的字符串

◆ 集合中每一个元素对应一个二进制位，被选中的为1，没选中的为0，左边为二进制中的低位，右边为高位，从右到左得到二进制结果

◆ set的每个选项值对应一个数字，依次是1，2，4，8，16…，最多有64个选项

如：set(‘a’,’b’,’c’)，如果某行的对应值为5，二进制表示为：101（22+0+20=5），则选中了“a,c”

三、另类的insert

1、 一条语句插入多条数据值

语法：insert into 表名(字段列表) values(值列表1),(值列表2),…

2、 insert…select…（须先创建目标表）

语法：insert into 表名[(字段列表)] select 字段列表 from 表名;

注意：两处字段列表数量须一致，且数据类型能兼容

3、 create…select…（插入数据前自动根据源表所选字段构建目标表）

语法：create table 表名 select 字段列表 from 表名;

四、数据表的更改

删除外键约束：alter table b drop foreign key fk_no_id;

删除主键：alter table drop primary key;

删除unique：alter table drop index username；

添加字段: alter table add userpassword varchar(20) not null;

删除字段：alter table a drop userpassword;

更改字段属性：alter table a modify userpassword varchar(30) not null;

五、约束

1、数据完整性：是指存储在数据库中的数据的一致性和正确性。

2、约束（constraint）是通过限制列中的数据、行中的数据和表之间数据来保证数据完整性的非常有效的方法。

完整性类型	约束类型	描 述
域完整性 （针对表中字段进行约束）	not null(非空)	指定某列不为空
default(缺省)	指定某一个列中的默认数值
实体完整性 （针对表中记录/数据进行约束）	primary key(主键)	每一行的惟一标识符，确保用户不能输入 冗余值和确保创建索引，提高性能，不允 许空值
unique(唯一)	防止出现冗余值，并且确保创建索引，提 高性能；允许空值，但字段中的数据值不能出现重复
引用完整性 （针对表与表之间的约束）	foreign key(外键)	定义一列或者几列，其值与本表或者另外 一个表的主键/唯一键值匹配

3、约束示例

◆ 创建表时创建约束

◆ 表存在后创建约束——实质是对表结构进行修改

六、删除语句

使用delete语句删除表中的记录（数据），可以删除指定的行或同时删除所有的行。

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

逻辑运算符：与(and)、或(or)、非（not）

not（非）	如果原来条件返回true，在其之前使用not之后则返回false。如果原来条件返回false，在其之前使用not之后则返回true。
and（与）	and运算符可以连接两个或两个以上的条件，只有当and连接的条件都为true（真）时，and返回的结果才是true（真）。如果其中有一个条件为false（假），and返回的值就是false（假）。
or（或）	or运算符连接的条件中只要有一个条件为true时，or返回的结果就是true。当然两个条件均为true时，or返回的结果当然是true。

语法：truncate table 表名;

补充：delete与truncate的区别

◆ delete会记录日志，意味着删除后的数据还可以恢复，但是效率低。

◆ truncate不会记录日志，删除后的数据不能恢复，但是效率高；truncate不能用于有外键约束引用的表。

truncate应用场景：如果是开发一个应用程序，开始可能使用的是测试数据，当应用开发成功后，需要将真正的数据导入到数据表中，为了使自增主键从1开始，使用truncate操作数据表比较方便。

**注意：**在表中的操作绝大部分都是记录在案的（日志），数据库实质是通过文件来存储（数据文件、日志文件）

如果删除数据涉及外键引用则须注意：当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据， 然后才可以删除主表的数据。（设置级联操作除外

补充：

◆ 语法：desc 表名;– 查看指定表结构

desc A；#语句执行结果如下所示

◆ show create table 表名;– 查看指定表定义语句

​

● 数据查询操作

1 、检索表中所有数据信息

select * from 表名;# *表示表中所有字段

2、检索表中部分字段信息

select 字段1,字段2,… from 表名;

3、列重命名

select 字段 [as] ‘别名’ from 表名;-- 建议保留as关键字

select cName as ‘课程名称’ from Courses;

select cName ‘课程名称’ from Courses;

4、排序（order by）

select 字段列表 from 表名

order by 字段1 [asc]|desc[,字段2 [asc]|desc,…];

说明：

A、排序方式包含asc（升序，由小到大，默认值）、desc（降序，由大到小）

B、可根据多个字段排序，但是每个字段有其自身排序方式，如果出现多个字

段排序，后者是在前者出现重复值的情况下有意义）

C、order by子句出现在where子句的后边（如果有where子句的话）

D、聚合函数可以作为排序依据，如order by count(字段) desc

select * from elogs order by elScore desc;

分析下边语句的作用

select * from Teachers order by tAge desc,tNo desc;

select * from Courses order by cName;#因为编码问题可能达不到想要的结果

#因为当前数据库语言设置为utf8，使用中文字段排序则须进行编码更改

select * from Courses order by convert(cName using gbk);

5、取多少条数据（limit）

#select 字段列表 from 表名 limit [m,]n;

注意：

1、limit关键字后只有一个数字n时，表示取前n条数据（此时m=0）

2、limit关键字后有两个数m,n时,表示从m+1行开始读取n条数据（常用于分页数据读取）

查询学生信息表中前五条数据

select * from Students limit 5;

select * from Students limit 0,5;

select * from Students limit 5,5;#6~10

任务：检索年龄最小的五位男老师信息

select * from Teachers where tGender=‘男’ order by tAge limit 5;

查找年龄最大的老师？—年龄最大的不止一位老师的时候！！！

思路：

第一步：找到教师信息表中最大的年龄值

第二步：在教师信息表中找到与年龄最大值相同年龄的老师信息

法一：

select * from Teachers where tAge=(

select tAge from Teachers order by tAge desc limit 1

);

法二：

select * from Teachers where tAge=(

select max(tage) from Teachers – max(字段名)：获取该字段数据中的最大值

);

重点提醒：聚合函数不能直接出现在where子句中！！！

select * from Teachers where tAge=max(tage);#错误的

6、聚合函数

max(字段名):返回指定字段的最大值

min(字段名):返回指定字段的最小值

avg(字段名):返回指定字段的平均值，只能针对数值型

sum(字段名):返回指定字段的和值，只能针对数值型

count():返回结果集的信息条数

count(*)

count(1)

count(主键字段)

count(非主键字段)

注意：

1、max(字段)、min(字段)、avg(字段)、sum(字段)、count(非主键字段)在统计时

自动排除字段值中的null值

2、count(*)、count(1)、count(主键字段)返回表中所有符合条件的信息数目

3、avg(字段)、sum(字段)在统计字符字段时，返回值为0

4、聚合函数不能直接嵌套使用

5、聚合函数不能直接出现在where子句中

统计选修记录信息表中，记录信息的数量及考试成绩的平均分

select count(*),count(1),count(elno),count(elscore),avg(elscore) from elogs;

select avg(cName),sum(cName),max(cName),min(cName) from Courses;

#select max(count(cid)) from elogs;-- 错误的

7、取消重复值（distinct）

#结果集中重复的数据值只会出现一次

select distinct tage from teachers;

练习：

查询选修考试成绩中，课程编号为2的前十名同学学号及成绩信息

select sid,elscore from elogs where cid=‘2’

order by elscore desc limit 10;

统计课程编号为00002的考试有多少学生未参考（考试成绩为空表示未参考）

select count(*) from elogs where elscore is null and cid=‘00002’;

select count(*)-count(elscore) from elogs where cid=‘00002’;

select * from elogs;

查看课程编号为00002，考试成绩为60、70、80、90、100的学生学号

select sid from elogs where elscore in (60,70,80,90,100)

and cid=‘00002’;

查询参与了选修的同学学号有哪些？

select distinct sid from elogs;

查询参与了选修的同学姓名

explain select sName from Students where sno in (

select sid from elogs

);

desc select sName from Students as S where exists(

select sid from elogs where sid=S.sno

);

8、子查询

子查询是指一个查询语句嵌套在另一个查询语句内部的查询

子查询（内层）结果作为外层SQL操作的过滤条件

子查询可以添加到select、update、delete语句中

子查询可进行多次嵌套

子查询类型

◆ 带比较运算符的子查询

◆ 带in关键字的子查询

◆ 带exists关键字的子查询

◆ 带any/some关键字的子查询

◆ 带all关键字的子查询

外部数据大于内部数据，使用in

内部数据大于外部数据，使用exists

in子查询：

select 字段列表 from 表A where 表A.字段 in(

select 表B.字段 from 表B

);

exits子查询：

select 字段列表 from 表A where exists(

select 主键字段 from 表B where 表B.字段=表A.字段

);

注意：

1、表A.字段与表B.字段有主外关联为佳

2、exists子查询中内部语句的where条件表达式是两表对应字段

#exists根据子句返回是否有结果（有数据返回，则exists返回真；否则返回假），以确定其前面的select语句是否需要执行

select * from Students where exists(select * from elogs);

select * from Students where exists(select * from elogs where cid=‘1’);

9、合并结果集union

默认将结果集中的重复值取消，如果不想取消重复值，则在union的后边增加all

select tName from Teachers

union all

select cName from Courses;

10、连接技术

连接技术：通过方法将两个及以上的表关联在一起（逻辑上看成一个表来操作）

内连接：inner join

语法：select 字段列表 from 表1 inner join 表2 on 表1.主键字段=表2.外键字段 [where 条件表达式];-- 建议

或

select 字段列表 from 表1,表2[,…] where 表1.主键字段=表2.外键字段 [and 条件表达式]

说明：呈现多表中能匹配的数据（有条件表达式存在则再筛选）

外连接：outer join

​ 左外连接：left outer join/left join

​ 语法：select 字段列表 from 表1 left join 表2 on 表1.主键字段=表2.外键字段 [where 条件表达式];

​ 说明：以左表（left join左边的表）为基表呈现数据，右表中无对应匹配的数据则以null呈现（有条件表达式存在则再筛选）

​ 右外连接：right outer join/right join

​ 语法：select 字段列表 from 表1 right join 表2 on 表1.主键字段=表2.外键字段 [where 条件表达式];

​ 说明：以右表（right join右边的表）为基表呈现数据，左表中无对应匹配的数据则以null呈现（有条件表达式存在则再筛选）

问题：统计《计算机网络基础》这门课程缺考的同学有多少

select count(*) from ELogs where elScore is null and cID=?

select cNo from Courses where cName=‘计算机网络基础’;

内连接（两表连接）

select count(*) from ELogs as E inner join Courses as C on C.cNo=E.cID where cName=‘计算机网络基础’;

子查询

select count(sID) from eLogs where elScore is null and cID =(select cNo from Courses where cName=‘计算机网络基础’);

问题：老张老师上了哪些课

分析：牵涉到两个表，分别是教师信息表（Teachers）、课程信息表（Courses）

Teachers、Courses存在主外关联（Courses.tID------>Teachers.tNo）

本次查询的是课程名称(cName)

本次涉及的条件是：老张老师参与授课(tName=‘老张’)

select cName from Courses inner join Teachers on tNo=tID where TName=‘老张’;

问题：查看选修了《C语言》这门课程的学生姓名

分析：牵涉到三个表，分别是学生信息表（Students）、课程信息表（Courses）、选修记录信息表（ELogs）

Students、ELogs存在主外关联（ELogs.sID------>Students.sNo）

Courses、ELogs存在主外关联（ELogs.cID------>Courses.cNo）

本次要查询的是：学生姓名（sName）

本次涉及的条件是：选修过《C语言》这门课程（cName=‘C语言’）

内连接（三表连接）

select sName from Students inner join ELogs inner join Courses where sNO=sID and cNo=cID and cName=‘C语言’;

问题：查看未进行选修的学生姓名

分析：未选修则表示：选修记录信息表中无这些学生学号的存在

学生姓名在学生信息表中，而选修涉及选修记录信息表

但此题并不是多表之间匹配数据的涉及，故不使用内连接解决

可考虑使用子查询 或 外连接技术实现

子查询

select sName from Students where sNo not in(

select distinct sID from ELogs

);

– 左外连接

select sName from Students left join ELogs on sNo=sID where elNO is null;

– 右外连接

select sName from ELogs right join Students on sNo=sID where elNO is null;

11、分组技术

#语法：select 字段列表 from 表名 [where 条件表达式] group by 字段1[,字段2,…] [having 条件表达式] [order by 字段列表]

统计在选修记录信息表中，已登记同学每人选修课程门数

select * from ELogs

select count(*) from ELogs group by sID;

查询已登记的每位学生姓名及其选修课程数目

select sID,count(*) from ELogs group by sID;

连接技术实现

– select * from ELogs inner join Students on sNo=sID;

select sName,count(*) from ELogs inner join Students on sNo=sID group by sID;

子查询技术实现

select (select sName from Students where sNo=sID),count(*) from ELogs group by sID;

应用group_concat()函数将指定字段（课程编号）以列表形式呈现在结果集内（就是所选课程的编号）

select sName,count(*),group_concat(cID) from ELogs inner join Students on sNo=sID group by sID;

select sName,count(*),group_concat(cName) from ELogs inner join Students on sNo=sID

inner join Courses on cNO=cID group by sID;

查询已登记的张姓同学的姓名及其选修课程数目

– 建议使用where进行数据的先行筛选再分组

select sName,count(*),group_concat(cName) from ELogs inner join Students on sNo=sID

inner join Courses on cNO=cID where sName like ‘张%’ group by sID;

select sName,count(*),group_concat(cName) from ELogs inner join Students on sNo=sID

inner join Courses on cNO=cID group by sID having sName like ‘张%’;

查询已登记的张姓同学的姓名及其选修课程数目，且选修课程门数必须大于三门才能呈现

select sName,count(*),group_concat(cName) from ELogs inner join Students on sNo=sID

inner join Courses on cNO=cID where sName like ‘张%’ group by sID having count(*)>3;