MySQL 进阶学习笔记

查看操作说明

? <操作>

例如:

? select

SQL的动词

SQL功能 动词
数据查询 SELECT
数据定义 CREATE,DROP,ALTER
数据操纵 INSERT,UPDATE,DELETE
数据控制 GRANT,REVOKE

主键和外键

主键:某一个属性组能唯一标示一条记录,该属性组可成为一个主键

外键:用于和另一张表关联,能确定另一张表记录的字段,用于保持数据的一致性

编码问题

windows cmd采用gbk编码,mysql采用utf8时会冲突,解决方法:

set names gbk;

https://blog.csdn.net/zxy13826134783/article/details/82799019

数据库(模式)的定义与删除

书上在创建表前面有个创建模式的操作,去查了一下模式和数据库的关系:

从MySQL 5.0.2开始,CREATE SCHEMA是CREATE DATABASE的同义词

因此可以理解为模式就是数据库

可以创建模式:

create schema <模式名>

或者创建数据库:

create database <数据库名>

删除模式:

drop schema <模式名> <cascade|restrict>

cascade表示级联操作,表示在删除模式的同时将模式中的所有数据库对象也全部删除,restrict表示限制操作,表示如果该模式中已经定义了下属的数据库对象,则拒绝该删除语句的执行

上面<cascade|restrict>这两种操作在MySQL中不起作用

删除数据库:

drop database <数据库名>

删除模式和删除数据库也是一样的

进入模式/数据库:

use <数据库名>

学生-课程数据库

create语句

首先进入到数据库:

use stu_course;

以学生-课程类型的数据库为例,各表和字段如下,关系的主码加下划线表示:

1.学生表:Student(Sno, Sname, Ssex, Sage, Sdept)

学号(Sno) 姓名(Sname) 性别(Ssex) 年龄(Sage) 所在系(Sdept)
201215121 李勇 20 CS

建表语句:

create table Student(
    Sno char(9) primary key,
    Sname char(20) unique,
    Ssex char(2),
    Sage smallint,
    Sdept char(20)
);

2.课程表:Course(Cno, Cname, Cpno, Ccredit)

课程号(Cno 课程名(Cname) 先行课(Cpno) 学分(Ccredit)
1 数据库 5 4

先行课就是上这门课之前需要上的课,因此先行课的号需要存在于课程号中,因此在定义表时可以用外键关联。参照表和被参照表可以是同一个表

建表语句:

create table Course(
    Cno char(4) primary key,
    Cname char(40) not null,
    Cpno char(4),
    Ccredit smallint,
    foreign key(Cpno) references Course(Cno)
);

在插入数据的时候,可以先将Cpno设为null,之后再update

3.学生选课表:SC(Sno, Cno, Grade)

学号(Sno) 课程号(Cno) 成绩(Grade)
201215121 1 92

建表语句:

create table SC(
    Sno char(9),
    Cno char(4),
    Grade smallint,
    primary key(Sno, Cno),
    foreign key(Sno) references Student(Sno),
    foreign key(Cno) references Course(Cno)
);

上述在stu_course中创建了三个数据表,如果开始没有指定数据库,那么可以在建表的时候指定:

create table stu_course.test(
    ...
)

alter语句

句式为:

alter table <表名> <add|modify|change|drop>

1.在Student表中新增一个Test字段,类型为smallint:

alter table Student add Test smallint;

2.将Student表中的Test字段的类型改为char(10):

alter table Student modify column Test char(10);

3.将Student表中的Test字段增加unique约束:

alter table Student add unique(Test);

4.将Student表中的Test字段改为Test1,类型为int:

alter table Student change Test Test1 int;

5.将Student表中的Test字段删除:

alter table Student drop Test;

6.将Student表中的Test字段unique约束取消掉:

alter table Student drop index Test;

drop语句

1.将Student表删除:

drop table Student;

2.将test数据库删除:

drop database test;

select语句

聚集函数

特点:不允许嵌套使用,例如sum(max(xx));默认忽略null

  • count():返回结果总数量(行数)
  • avg():对某列求平均值
  • sum():对某列进行求和
  • max():对某列求最大值
  • min():对某列求最小值
  • group_concat():将某列的多行数据合并为一行

查询分组

group by用于将结果集中的数据行根据列的值进行分组,有相同的列的值为一组;之后可以对分组使用聚合函数;默认按asc升序排序

分组后数据显示不全,可以使用group_concat查看分组后的所有数据

可以使用having在分组后再次过滤,可以在having子句中使用聚集函数;注意和where的区别:

where是在执行group by操作之前进行的过滤,where子句不能使用聚集函数;

连接查询

通过和其他表关联的字段将两张表或多张表连接起来,例如:

select Student.*,SC.Cno,SC.Grade from Student,SC where Student.Sno=SC.Sno;

上面在查询时使用了表名前缀,是为了避免混淆,如果属性名在各连接的字段中是唯一的,那么表名前缀可省略

嵌套查询

将一个查询块select-from-where嵌套在另一个查询块的wherehaving子句中的查询称为嵌套查询

1.带in关键字的子查询

内层查询返回一个数据集合,提供给外层查询进行数据筛选

视图

视图是一个虚表,数据库只存放视图的定义而不存放视图对应的数据;视图相当于select操作的快捷方式;视图只操作它可以查询出来的数据,对于它查询不出的数据,即使基表有,也不可以通过视图来操作。

1.创建视图:

create view <视图名> as <语句> [with check option]

with check option表示对视图的update、insert的操作数据进行检测,以保证能被视图查询出来

2.查看所有表(包括视图表):

show table status\G; 

3.删除视图

drop view <视图名>

其他对视图的增删改查操作和基本表相同

索引

作用:加快查询速度

缺点:占用一定的存储空间,当基本表更新时,索引需要进行相应的维护,会增加数据库负担

待更。

数据库安全性控制

用户

1.查询所有用户和host:

select user,host from mysql.user;

2.创建用户:

create user <用户名> identified by '123';

3.删除用户:

drop user gtfly@localhost;

4.查询所有用户、角色和host:

select user,host from mysql.user;

5.查询当前用户

select user();

角色

MySQL8.0可直接对角色的进行管理,MySQL5.7还需要对配置文件进行修改。Ubuntu安装MySQL8.0可参考:https://blog.csdn.net/wm609972715/article/details/83759266

MySQL角色是指定的权限集合。像用户帐户一样,角色可以拥有授予和撤消的权限

1.创建角色

create role <角色名>

2.删除角色

drop role <角色名>

3.查询当前角色

select current_role();

授权

1.将用户gtfly@localhost授权给角色R1:

grant gtfly@localhost to R1;

2.把查询user表的权限授予用户gtfly@localhost

grant select on table user to gtfly@localhost;

3.查询root用户所赋予的所有权限:

show grants for root@localhost;

4.查询所有用户及其权限、权限赋予者等信息:

select * from mysql.tables_priv;

5.为用户设置默认角色:

set default role <角色名> to <用户名>

综合查询语句

1.查询一个数据库的所有表的字段:

select group_concat(column_name) form information_schema.columns where table_schema=database();

2.搜索含有某关键字的字段:

select group_concat(column_name) from information_schema.columns where table_schema=database() and column_name like '%pass%';

3.根据字段查询所在表:

select table_name from information_schema.columns where table_schema=database() and column_name='user_pass';

4.根据sql数据库中表的字段数量降序排序:

select table_name, table_rows from information_schema.tables where table_schema='xxx' order by table_rows desc;

或者sqlmap:

... -D xxx --count

5.查询用户密码:

select * from mysql.user where User='root';
select User,authentication_string from mysql.user;

查到后去cmd5等网站解密,类型为mysql5或其他的

6.根据表查所在数据库:

select distinct(table_schema) from information_schema.columns where table_name='xxx' and table_schema<>'mysql';

导出MySQL数据

1.命令行

导出Test数据库中的customer表到log.sql文件:

mysqldump -uroot -p123456 Test customer > log.sql

导出Test数据库全部内容到log.sql文件:

mysqldump -uroot -p123456 Test > log.sql

2.MySQL

select * from customer into outfile '/var/www/html/log.sql';