共计 41897 个字符,预计需要花费 105 分钟才能阅读完成。
一、理解 MySQL 基本概念
1、MySQL 软件 :MySQL 实际上 就是一软件,是一工具,是关系型数据库管理系统软件
2、MySQL 数据库:就是按照数据结构来组织、存储和管理数据的仓库
3、MySQL 数据库实例:
①MySQL 是 单进程多线程(而 Oracle 是多进程),也就是说 MySQL 实例在系统上表现就是一个服务进程,即进程;
②MySQL 实例是 线程和内存组成,实例才是真正用于操作数据库文件的;
一般情况下 一个实例操作一个或多个数据库;集群情况下 多个实例操作一个或多个数据库。
二、MySQL 数据库启动以及启动的判断
1、启动 MySQL 数据实例:
shell> service mysqld start #rpm 包安装的 mysql
如果是源码安装的话,推荐使用 mysqld_safe 命令的安全启动(可以看到启动信息)。
2、判断 MySQL 数据库是否启动:
shell> netstat -tulnp|grep 3306 #如果可以过滤出来 (有输出) 证明已启动
shell> mysqladmin -uroot -p123 ping #出现 mysqld is alive 证明是活跃的
三、如何使用官方文档和 help
1、基本技能:DBA 所有的操作必须来自于官方文档
2、mysql> help contents; #寻求 help 帮助的入口
四、官方文档概览
1、Tutorial:将 MySQL 常用的一些操作使用一个场景串联起来
只是关注里面的灰色部分就可以,按照里面的灰色操作部分顺一遍
2、server Administrator:MySQL 管理需要的一些命令、工具、参数等
3、SQL Syntax
SQL 语法,使用最多,特别是 DDL 语句一定要使用 SQL 语法进行参考
4、Server Option / Variable Reference:MySQL 的参数和状态值,使用较多
5、Functions and Operators
MySQL 常用函数和操作符,使用较多
6、Views and Stored Programs
视图、存储过程、函数、触发器、event 语法参考
7、Optimization:优化
非常值得细致的看一遍,此篇文档不仅仅用来参考,更多的是用来学习优化知识,算是 DBA 进阶宝典
8、Partitioning
如果是要进行表分区,此文档是必须参考的资料,也是唯一参考的资料
9、Information Schema、Performance Schema
中级 DBA 常用的两个参考资料
10、Spatial Extensions
地理位置信息
11、Replication
MySQL 使用复制功能,常用的参考资料
12、Semisynchronous Replication
半同步复制,个别场合会用到
五、如何使用官方文档
1、参考官方文档修改密码强度(降低密码强度)、修改密码
①改密码强度:
mysql> show variables like ‘validate_password%’;
mysql> SET GLOBAL validate_password_policy=0;
②修改密码:set、alter
2、参考官方文档查询当前数据库连接的数量(查询状态值 Threads_connected)
mysql> show status like ‘%Threads_connected%’;
注意:查看状态值是 show status
查看变量值是 show variables
3、建立一个数据库指定字符集
mysql> create database test_db character set utf8;
4、给一个表增加一个列,要求这个列的数据类型是字符串、非空(alter)
ALTER TABLE tbl_name ADD COLUMN col_name varchar(20) not null;
5、用函数将两个字符串串联起来(concat:合并多个字符串)
CONCAT():returns NULL if any argument is NULL.
CONCAT_WS(separator,str1,str2,…)
6、mysqladmin 的使用:类同于 ping 数据库是否活跃、关闭数据库
shell> mysqladmin -uroot -p123 ping
mysqld is alive
shell> mysqladmin -uroot -p123 shutdown
7、如何启动数据库:mysqld_safe 命令 ( 切记挂后台 &,否则占领当前会话无法退出)
shell> mysqld_safe –defaults-file=/etc/my.cnf &
官方文档对于具有一定基础知识的人来说,是一个最合适的工具,可以使 DBA 的操作变得没有障碍
六、登录 MySQL 查看当前会话的状态
mysql> status
七、描述 MySQL 在 Linux 平台下的大小写、同时演示大小写的区别
1、数据库名、表名、表别名严格区别大小写
2、列名、列别名忽略大小写
3、变量名严格区别大小写
4、MySQL 在 windows 下各个对象都不区别大小写
②lower_case_table_names表示表名或数据库存储是否区别大小写,为只读变量,可以在配置文件 my.cnf 里面修改:
0 表示区分大小写,按照新建数据库的大小写形式存储显示;
1 表示无论新建数据库大小写都以小写的形式存储显示。
八、MySQL 的几种帮助
1、shell> mysql –help
2、mysql> help show
mysql> show create table tel_name
mysql> help set
九、MySQL 的变量如何查看,如何修改
1、查看变量用 select
局部变量select var_name;
用户变量select @var_name;
全局变量select @@var_name;
2、修改变量用 set
②set session 表示修改后对本次会话生效
③如果变量是只读变量 可以通过修改 MySQL 的配置文件 my.cnf 来修改变量,在 [mysqld] 下添加一行数据:user_var_name=expr,然后 重启数据库再登录即可。
十、MySQL 的状态参数如何查看、如何参考阅读其内容
在官方文档的 Server Option / Variable Reference 部分,进行参考查看 MySQL 的参数变量以及状态值
1、cmd-line 表示能否在 mysql 安全启动 (mysqld_safe) 中进行参数设置 –var_name=……
2、option file 表示能否在 mysql 的参数文件中进行参数设置
3、system var 表示是否是系统变量
4、status var 表示是否是状态变量
5、var scope 表示变量的范围:全局 global、会话 session
6、dynamic 表示是否是动态参数,yes 是动态,no 是静态
十一、如何查看某个数据库里面有多少表、每一个表的列的信息
1、show tables; desc tbl_name;
2、mysql> select * from information_schema.TABLES
-> where TABLE_NAME=’tbl_name’\G;
①information_schema 数据库:也称为数据字典,记录了各数据库的表、视图、索引、存储过程、函数等信息……
②information_schema.TABLES:记录了 MySQL 中每一个数据库中表所在的数据库、表的名字、表的行数等信息。
十二、如何查看一个表的建表语句、一个数据库的建库语句
1、show create table tbl_name;
2、show create database db_name;
十三、如何查看 MySQL 支持的数据类型以及数据类型如何使用
mysql> help contents;
mysql> help data types;
mysql> help ……
十四、列举 show 命令常用的语法
1、show status like …… 查看状态值
2、show variables like …… 查看变量参数值
3、show create …… 查看建表、库……的语句信息
4、show procedure status where db=’db_name’\G; #查看存储过程信息
5、show warnings\G; #查看警告信息
十五、help kill 如何使用
mysql> help kill
KILL [CONNECTION | QUERY] processlist_id
注:Thread processlist identifiers can be determined from the ID column of the INFORMATION_SCHEMA.PROCESSLIST table。
mysql> select * from INFORMATION_SCHEMA.PROCESSLIST\G;
十六、描述 MySQL 用户名组成以及特点
1、MySQL 用户身份识别认证:用户名 user、密码 password、登录 mysqld 主机 host
shell> mysql -uroot -p123 -h172.16.11.99
-u:登录的用户名
-p:登录用户对应的密码
-h:MySQL 服务器主机 IP,默认是 localhost 的 IP
2、MySQL 的用户管理模块的 特点 : 客户端请求连接,提供 host、username、password,用户管理模块进行验证请求连接,通过 mysql.user 表进行校验信息。
十七、如何查看 MySQL 有多少用户以及对应的权限
1、mysql> select count(*) from mysql.user; #查看 MySQL 有多少用户
2、mysql> select * from mysql.user\G; #用户信息查询(权限)
十八、建立一个用户
1、本地登录
mysql> create user ‘u1’@’localhost’ identified by ‘123’;
2、任意都可以登录
mysql> create user ‘u2’@’%’ identified by ‘123’;
3、某一个网段可以登录
mysql> create user ‘u3’@’172.16%’ identified by ‘123’;
4、具体主机可以登录
mysql> create user ‘u4’@’172.16.12.24’ identified by ‘123’;
十九、使用 help grant,给用户赋权
二十、建立一个 db1 数据库的只读用户
建用户然后授权
mysql> GRANT SELECT ON db1.* TO ‘olr_user’@’%’;
二十一、建立一个只能进行系统状态信息查询的管理用户
mysql> grant select on information_schema.* to ‘admin_user’@’%’;
二十二、建立一个 db1 的生产用户,只能进行 dml、select,不能进行 ddl
mysql> grant select,insert,update,delete on *.* to ‘pro_user’@’%’;
二十三、建立一个可以进行 DDL 的管理用户
mysql> grant create,drop,alter on *.* to ‘admin_user’@’%’;
二十四、建立一个工资表,只有指定的用户可以访问工资列,其他用户都不能访问工资列
实现步骤:
先在 mysql.user 里将所有用户检索出来,进行跑批处理(脚本或存储过程)revoke 对该表列的权限;
然后 grant 创建用户,并对该表列赋访问权限。
二十五、查询上述用户以及所赋权限是否正确,同时进行验证
mysql> select * from mysql.user\G; #查看 MySQL 用户信息
进行用户登录验证
二十六、解释 with grant option,并且演示其功能
mysql> grant all on *.* to ‘zhang’@’%’ identified by ‘123’ with grant option;
with grant option 子句 : 通过在 grant 语句的最后使用该子句,就允许被授权的用户把得到的权限继续授给其他用户。也就是说,客户端用 zhang 用户登录 MySQL,可以将 zhang 用户有的权限使用 grant 进行授权给其他用户。
二十七、查询某一个表上的权限、查看某一个列上的权限、查看某一个数据库上面的权限
1、查询所有数据库的权限
mysql> select * from mysql.user;
2、查询某个数据库的权限
mysql> select * from mysql.db;
3、查询某个数据库中某个表的权限
mysql> select * from mysql.tables_priv;
4、查询某个数据库某个表中某个列的权限
mysql> select * from mysql.columns_priv;
二十八、修改参数运行使用 grant 建立用户,修改参数禁止 grant 建立用户
sql_mode 参数中的 NO_AUTO_CREATE_USER 值:不自动创建用户
mysql>set @@session.sql_mode=……; #设置 sql_mode 参数
二十九、修改 mysql 的用户密码,分别使用 grant、alter、set 修改
①mysql> grant all on *.* to ‘ 用户名 ’@’ 登录主机 ’ identified by ‘ 密码 ’;
②mysql> alter user ‘ 用户名 ’@’ 登录主机 ’ identified by ‘ 密码(自定义)’;
③mysql> SET PASSWORD FOR ‘ 用户名 ’@’ 登录主机 ’ = PASSWORD(‘ 密码 ’);
三十、破解密码步骤:
①到 /etc/my.cnf 里将 validate_password=off 行注释 // 关闭密码策略
②shell> mysqld_safe –skip-grant-tables & // 重启数据库
③shell> mysql -uroot // 无密码登录
④mysql> flush privileges; // 刷新权限使密码生效
⑤修改密码,退出,重启数据库,进入
三十一、使用 revoke 进行权限的收回,将上面用户的授权分别收回,同时查看收回后的结果
①REVOKE INSERT ON *.* FROM ‘jeffrey’@’localhost’;
②REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] …
三十二、select 最简单常用语法
1、全表查询
select * from tbl_name;
2、某些行查询
select * from tbl_name where ……;
3、某些列查询
select clm_name from tbl_name;
4、某些行的某些列查询
select clm_name from tbl_name where ……;
5、列别名
select clm_name as new_name from tbl_name;
6、列运算
select clm_name+123 from tbl_name;
三十三、concat 函数的使用
1、concat 函数:将多个字符串参数首尾相连后返回
2、concat_ws 函数:将多个字符串参数以给定的分隔符,首尾相连后返回
3、group_concat:函数的值等于属于一个组的指定列的所有值,以逗号隔开,并且以字符串表示
三十四、演示打开和关闭管道符号“|”的连接功能
PIPES_AS_CONCAT:将“||”视为字符串的连接操作符而非或运算符
|| 管道连接符:
mysql> select 列名 1 || 列名 2 || 列名 3 from 表名;
在 mysql 中,进行上式连接查询之后,会将查询结果集在一列中显示,列名是‘列名 1 || 列名 2 || 列名 3’
如果不给 sql_mode 参数加入 PIPES_AS_CONCAT 的话,|| 默认是 or 的意思,查询结果是一列显示是 1。
三十五、使用 mysql> help functions; 学习 MySQL 各类函数
三十六、常见功能函数
1、upper(……)、lower(……)大小写变换
2、user()查看登录用户、current_user()查看当前用户
3、database()查看使用的数据库
更多详情见请继续阅读下一页的精彩内容:http://www.linuxidc.com/Linux/2017-05/144012p2.htm
三十七、使用 help 来学习下面的数据类型(建立对应类型的列、插入数据、显示数据)
1、整数:int
2、非负数:unsigned 无符号即非负数 —e.g:int unsigned
3、小数:dec
4、浮点数以及科学计数法:float、double
如果 FLOAT 数据在插入的时候,要使用 NeM(科学计数法)的方式插入时:
比如
5e2 就是 5 *10 的 2 次方
5e- 2 就是 5 *10 的 - 2 次方
4e-1+5.1e2 就是 510.4
5、字符串:varchar
6、布尔:bool、boolean—synonyms(同义词):TINYINT(1)
7、位:bit
如何使用 16 进制常量:hex()
如何使用 2 进制常量:bin()
date 类型以及 STR_TO_DATE 函数
time 类型以及 STR_TO_DATE 函数
dateime 数据类型以及标准写法、STR_TO_DATE 函数
date 和 time 显示方式以及 date_format 函数
三十八、时区
1、查看操作系统时区、数据库时区
查看操作系统时区:
shell> cat /etc/sysconfig/clock
ZONE=”Asia/Shanghai”
shell> ls /usr/share/zoneinfo
……
MySQL> show variables like ‘system_time%’; #查看 MySQL 系统时区
mysql> show variables like ‘time_zone%’; #查看数据库时区
2、修改数据库时区为东八区,去掉数据库时区对 os 时区的依赖(查看官方文档)
加载系统时区:将 Linux 时区导入到数据库中
shell> mysql_tzinfo_to_sql /usr/share/zoneinfo |mysql -uroot -p123 mysql
mysql> set @@global.time_zone=’Asia/Shanghai’;
修改数据库时区为东八区,同时在 参数文件中进行修改,永久保存
3、时区在什么时候有用:
如果数据库里面没有 timestamp 这个数据类型,那么时区参数没有意义!
你如何确认你的数据库里面是否有 timestamp 类型的列?
mysql> select table_name,column_name,data_type
-> from information_schema.columns
-> where data_type='timestamp';
……
时区原理描述:insert 过程和 select 过程的描述:相对应的 0 时区的转换
4、时区的正确实践(timestamp)
insert 以前:你的 values 对应的时间到底是哪个时区,然后设置 set @@session.time_zone 为对应的时区
select 获取以前:你想得到什么时区的时间,就设置 set @@session.time_zone 为对应的时区
三十九、字符集
1、查看服务器的字符集
mysql> show variables like ‘character_set_server’;
2、查看数据库字符集
mysql> show variables like ‘character_set_database’;
一般在数据库实现字符集即可,表和列都默认采用数据库的字符集
gbk
utf8
3、查看表的字符集、查看列的字符集
mysql> show create table tbl_name;
4、字符集原理描述、字符集正确实践
对于 insert 过程描述、对于 select 过程描述
①对于 insert 来说,character_set_client、character_set_connection 相同,而且正确反映客户端使用的字符集
②对于 select 来说,character_set_results 正确反映客户端字符集
③数据库字符集取决于我们要存储的字符类型
④字符集转换最多发生一次,这就要求 character_set_client、character_set_connection 相同
⑤所有的字符集转换都发生在数据库端
总述:
1)建立数据库的时候注意字符集(gbk、utf8)
2)连接数据库以后,无论是执行 dml 还是 select,只要涉及到 varchar、char 列,就需要设置正确的字符集参数:
character_set_client、character_set_connection、character_set_results
5、客户端字符集如何来理解?
取决于客户端工具
shell> mysql -uroot -p123456 -hserver_host -P3306
mysql 工具本身没有字符集,因此客户端字符集取决于工具所在的 os 的字符集(windows:gbk、linux:utf8)
sqlyog 工具本身带字符集,此时客户端 os 字符集就没有意义
6、如何判断字符集出现了问题?
所有设置都正确,但是查询到的还是乱码,这就是出现问题了
四十、如何识别变量参数、状态参数 status var
show variables……
show status……
识别判断都是查看官方文档 System Var、Status Var
四十一、如何识别动态参数、静态参数
动态参数 dynamic:Yes
静态参数 dynamic:No
四十二、对于动态参数如何设置,如何判断动态参数是否可以在全局级别或者会话级别修改
1、set
2、修改参数文件 /etc/my.cnf:弊端是需要重启才能生效(很少用)
判断:参考官方文档 Option/Variable Summary,通过 Var scope 来进行判断动态参数的全局 global、both
四十三、对于静态参数如何修改
静态参数,在整个实例声明周期内都不得进行更改,就好似是只读的;
一般静态参数都是在配置文件中修改 /etc/my.cnf,当然静态参数能否写入配置文件还要看官方文档对该参数的 Option File 的描述 Yes 与否。
四十四、掌握 @@、@的区别
1、@@var_name 表示的系统变量
根据系统变量的作用域可分:全局变量、会话变量
2、@var_name 表示的用户变量
①用户变量和数据库连接有关,连接后声明变量,连接断开后,自动消失;
②select 一个没有赋值的用户变量,返回 NULL,也就是没有值;
Mysql 的变量类似于动态语言,变量的值随所要赋的值的类型而改变。
四十五、set @@session. 和 set @@global. 的生效时间
对于一个新建立的连接,只有全局变量,会话变量还不存在,这个时候会从全局变量拷贝过来。
1、set @@session.:只对当前连接起作用
2、set @@global.:对全局变量的修改会影响到整个服务器
注意:set 系统变量时,不带作用域修饰,默认是指 会话作用域;
(特别注意,有些系统变量不带作用域修饰,无法设置,因此 最好都带上作用域设置系统变量)。
四十六、动态参数最佳实践
1、尽量先进行会话级别的设置 set @@session,确认生效而且效果不错以后,再进行全局设置,如果需要马上生效,杀掉所有的会话:
mysql> select concat(‘kill ‘,conn_id,’;’) from sys.session;
2、确认没有问题以后,修改参数文件,下次系统启动一直生效。
四十七、select 书写技巧
1、确认需要访问数据来自于哪几张表
from 来自某张表或者某几张表
join 添加某张表
on 表连接条件
记住一点:每关联一个表就需要加上对应的 on 条件(on 条件就是主外键条件)
2、通过 where 条件来过滤数据
3、确认需求里面是否有分组聚合的含义
分组:group by
聚合:聚合函数
聚合条件过滤:having
4、是否需要排序
order by
四十八、MySQL 内置函数(将列出的常见的一些函数熟悉过一遍)
1、内置函数的多少是一个数据库是否成熟的标志
2、学会使用 help Functions 学习和使用函数 ( 重点!!!!!!!!!!!)
3、常用函数要过一遍
①日期时间相关的函数
CURDATE、DATEDIFF、DATE_FORMAT、DAYOFWEEK、LAST_DAY、EXTRACT、STR_TO_DATE
②比较操作符要求都过一遍,help Comparison operators;
③流程控制行数 help Control flow functions;
④加密函数 help Encryption Functions;
只需要看看 decode、password 两个函数即可
⑤信息获取函数 help Information Functions;
通过这些函数可以知道一些信息,过一遍即可
⑥逻辑操作符 help Logical operators;
!、and、or,这些常用的要过一遍
⑦杂项函数 help Miscellaneous Functions;
简单浏览一下里面的函数,对于名字有个印象即可
⑧数值函数 help Numeric Functions;
使用数据库来进行数学运算的情况不多,常用的加减乘除、TRUNCATE、ROUND
⑨字符串函数 help String Functions;
CONCAT、CONCAT_WS、CAST、FORMAT、LIKE、REGEXP、STRCMP、TRIM、SUBSTRING、UPPER,其它函数名字过一遍
4、聚合分组函数的使用了解
①select 后面得列或者出现在 group by 中,或者加上聚合函数
select c1,c2,sum(c3),count(c4)
from t1
group by c1,c2;
②help contents;
查看聚合函数help Functions and Modifiers for Use with GROUP BY;
AVG、MAX、MIN、SUM、COUNT、COUNT DISTINCT、GROUP_CONCAT、BIT_AND、BIT_OR、BIT_XOR
四十九、隐式类型转换,要避免隐式类型转换
1、最常用的几个数据类型:数字、字符串、日期时间
2、字符串里面可以存放数字和日期,但是在设计表的时候,要注意不要将日期和数字列设计成字符串列
3、对于字符串列的比较,一定要加上引号:
mysql> select * from t where name_phone=’1301110001′;
五十、limit 使用很频繁,注意其使用方法
1、limit 使用的场合
从结果集中选取最前面或最后面的几行
2、limit 配合 order by 使用
3、MySQL5.7 doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’
五十一、in、not in、exists、not exists、left join、distinct join 互相转换
1、in 和 exists 可以互相转换
select * from players a where a.teamno in (select teamno from team where teamname='骑士队');
select * from players a where exists (select 1 from team b where a.teamno=b.teamno and b.teamname='骑士队');
2、not in 和 not exists 可以互相转换
3、not in、not exists 可以转换成 left join
1、理解为什么会出现表连接:查询的列来自于多个表
2、理解表连接的书写方式
join 一个表、on 一个条件
3、理解表连接的注意条件
①两个表要连接一定要存在主外键关系(有可能需要第三张表协助关联)
实际上存在外键约束
存在外键列,但是没有外键约束
②防止扇形陷阱 ( 两个表需要关联,但是没有直接主外键,借助第三个表进行关联,但是存在扇形问题,此时不能借助第三个表进行关联)
示例:学院表、专业表、学生表
学院实体和专业实体之间是一对多的联系;
学院实体和学生实体之间也是一对多的联系;
而学生和专业之间没有联系;
如果学生和专业通过学院表进行关联,就会出现扇形问题。
4、外连接:左外连接、右外连接
外连接是 为了防止出现某一个表的数据被遗漏
开发人员非常喜欢使用外连接.
五十三、子查询
1、子查询可能出现的位置
①select from 之间可能会出现子查询
②from 后面
③join 后面可能会出现子查询
④where 后面可能会出现子查询
⑤having 后面可能会出现子查询
2、尽最大程度的不要使用子查询
3、相关子查询、无关子查询
相关子查询特别容易出现在 select from 之间、where 后面
相关子查询不能独立执行,子查询执行次数取决于父查询返回的行数
无关子查询可以独立执行,子查询执行一次
五十四、子查询出现的场合
1、where 中出现的子查询,一般可使用表连接进行改写
①select 列(涉及到 A 表,没有涉及到 B 表)
②where 条件(涉及到 B 表)
2、from 后面的子查询
①对于取出来的数据再次进行复杂的处理
例如 分组聚合、having 条件、where 条件 等
②对一个结果集再次进行复杂的查询
意味着我们取数据的这个过程中,对数据进行处理的力度很复杂
3、select from 之间的子查询
对于返回的每一行数据,select 和 from 之间的子查询都要执行一次
select 后面的列要进行复杂的处理,如果这个处理涉及到另外一个表,若这个表很可能没有出现在 from 和 join 里面,则进行子查询:
示例:将每一个同学的成绩列出来,同时计算他的成绩和本组平均成绩的差距
select 学生成绩,
学生成绩-(select avg(成绩) from 选课表 a where a. 组 ID=b. 组 ID)
from 选课表 b;
一、理解 MySQL 基本概念
1、MySQL 软件 :MySQL 实际上 就是一软件,是一工具,是关系型数据库管理系统软件
2、MySQL 数据库:就是按照数据结构来组织、存储和管理数据的仓库
3、MySQL 数据库实例:
①MySQL 是 单进程多线程(而 Oracle 是多进程),也就是说 MySQL 实例在系统上表现就是一个服务进程,即进程;
②MySQL 实例是 线程和内存组成,实例才是真正用于操作数据库文件的;
一般情况下 一个实例操作一个或多个数据库;集群情况下 多个实例操作一个或多个数据库。
二、MySQL 数据库启动以及启动的判断
1、启动 MySQL 数据实例:
shell> service mysqld start #rpm 包安装的 mysql
如果是源码安装的话,推荐使用 mysqld_safe 命令的安全启动(可以看到启动信息)。
2、判断 MySQL 数据库是否启动:
shell> netstat -tulnp|grep 3306 #如果可以过滤出来 (有输出) 证明已启动
shell> mysqladmin -uroot -p123 ping #出现 mysqld is alive 证明是活跃的
三、如何使用官方文档和 help
1、基本技能:DBA 所有的操作必须来自于官方文档
2、mysql> help contents; #寻求 help 帮助的入口
四、官方文档概览
1、Tutorial:将 MySQL 常用的一些操作使用一个场景串联起来
只是关注里面的灰色部分就可以,按照里面的灰色操作部分顺一遍
2、server Administrator:MySQL 管理需要的一些命令、工具、参数等
3、SQL Syntax
SQL 语法,使用最多,特别是 DDL 语句一定要使用 SQL 语法进行参考
4、Server Option / Variable Reference:MySQL 的参数和状态值,使用较多
5、Functions and Operators
MySQL 常用函数和操作符,使用较多
6、Views and Stored Programs
视图、存储过程、函数、触发器、event 语法参考
7、Optimization:优化
非常值得细致的看一遍,此篇文档不仅仅用来参考,更多的是用来学习优化知识,算是 DBA 进阶宝典
8、Partitioning
如果是要进行表分区,此文档是必须参考的资料,也是唯一参考的资料
9、Information Schema、Performance Schema
中级 DBA 常用的两个参考资料
10、Spatial Extensions
地理位置信息
11、Replication
MySQL 使用复制功能,常用的参考资料
12、Semisynchronous Replication
半同步复制,个别场合会用到
五、如何使用官方文档
1、参考官方文档修改密码强度(降低密码强度)、修改密码
①改密码强度:
mysql> show variables like ‘validate_password%’;
mysql> SET GLOBAL validate_password_policy=0;
②修改密码:set、alter
2、参考官方文档查询当前数据库连接的数量(查询状态值 Threads_connected)
mysql> show status like ‘%Threads_connected%’;
注意:查看状态值是 show status
查看变量值是 show variables
3、建立一个数据库指定字符集
mysql> create database test_db character set utf8;
4、给一个表增加一个列,要求这个列的数据类型是字符串、非空(alter)
ALTER TABLE tbl_name ADD COLUMN col_name varchar(20) not null;
5、用函数将两个字符串串联起来(concat:合并多个字符串)
CONCAT():returns NULL if any argument is NULL.
CONCAT_WS(separator,str1,str2,…)
6、mysqladmin 的使用:类同于 ping 数据库是否活跃、关闭数据库
shell> mysqladmin -uroot -p123 ping
mysqld is alive
shell> mysqladmin -uroot -p123 shutdown
7、如何启动数据库:mysqld_safe 命令 ( 切记挂后台 &,否则占领当前会话无法退出)
shell> mysqld_safe –defaults-file=/etc/my.cnf &
官方文档对于具有一定基础知识的人来说,是一个最合适的工具,可以使 DBA 的操作变得没有障碍
六、登录 MySQL 查看当前会话的状态
mysql> status
七、描述 MySQL 在 Linux 平台下的大小写、同时演示大小写的区别
1、数据库名、表名、表别名严格区别大小写
2、列名、列别名忽略大小写
3、变量名严格区别大小写
4、MySQL 在 windows 下各个对象都不区别大小写
②lower_case_table_names表示表名或数据库存储是否区别大小写,为只读变量,可以在配置文件 my.cnf 里面修改:
0 表示区分大小写,按照新建数据库的大小写形式存储显示;
1 表示无论新建数据库大小写都以小写的形式存储显示。
八、MySQL 的几种帮助
1、shell> mysql –help
2、mysql> help show
mysql> show create table tel_name
mysql> help set
九、MySQL 的变量如何查看,如何修改
1、查看变量用 select
局部变量select var_name;
用户变量select @var_name;
全局变量select @@var_name;
2、修改变量用 set
②set session 表示修改后对本次会话生效
③如果变量是只读变量 可以通过修改 MySQL 的配置文件 my.cnf 来修改变量,在 [mysqld] 下添加一行数据:user_var_name=expr,然后 重启数据库再登录即可。
十、MySQL 的状态参数如何查看、如何参考阅读其内容
在官方文档的 Server Option / Variable Reference 部分,进行参考查看 MySQL 的参数变量以及状态值
1、cmd-line 表示能否在 mysql 安全启动 (mysqld_safe) 中进行参数设置 –var_name=……
2、option file 表示能否在 mysql 的参数文件中进行参数设置
3、system var 表示是否是系统变量
4、status var 表示是否是状态变量
5、var scope 表示变量的范围:全局 global、会话 session
6、dynamic 表示是否是动态参数,yes 是动态,no 是静态
十一、如何查看某个数据库里面有多少表、每一个表的列的信息
1、show tables; desc tbl_name;
2、mysql> select * from information_schema.TABLES
-> where TABLE_NAME=’tbl_name’\G;
①information_schema 数据库:也称为数据字典,记录了各数据库的表、视图、索引、存储过程、函数等信息……
②information_schema.TABLES:记录了 MySQL 中每一个数据库中表所在的数据库、表的名字、表的行数等信息。
十二、如何查看一个表的建表语句、一个数据库的建库语句
1、show create table tbl_name;
2、show create database db_name;
十三、如何查看 MySQL 支持的数据类型以及数据类型如何使用
mysql> help contents;
mysql> help data types;
mysql> help ……
十四、列举 show 命令常用的语法
1、show status like …… 查看状态值
2、show variables like …… 查看变量参数值
3、show create …… 查看建表、库……的语句信息
4、show procedure status where db=’db_name’\G; #查看存储过程信息
5、show warnings\G; #查看警告信息
十五、help kill 如何使用
mysql> help kill
KILL [CONNECTION | QUERY] processlist_id
注:Thread processlist identifiers can be determined from the ID column of the INFORMATION_SCHEMA.PROCESSLIST table。
mysql> select * from INFORMATION_SCHEMA.PROCESSLIST\G;
十六、描述 MySQL 用户名组成以及特点
1、MySQL 用户身份识别认证:用户名 user、密码 password、登录 mysqld 主机 host
shell> mysql -uroot -p123 -h172.16.11.99
-u:登录的用户名
-p:登录用户对应的密码
-h:MySQL 服务器主机 IP,默认是 localhost 的 IP
2、MySQL 的用户管理模块的 特点 : 客户端请求连接,提供 host、username、password,用户管理模块进行验证请求连接,通过 mysql.user 表进行校验信息。
十七、如何查看 MySQL 有多少用户以及对应的权限
1、mysql> select count(*) from mysql.user; #查看 MySQL 有多少用户
2、mysql> select * from mysql.user\G; #用户信息查询(权限)
十八、建立一个用户
1、本地登录
mysql> create user ‘u1’@’localhost’ identified by ‘123’;
2、任意都可以登录
mysql> create user ‘u2’@’%’ identified by ‘123’;
3、某一个网段可以登录
mysql> create user ‘u3’@’172.16%’ identified by ‘123’;
4、具体主机可以登录
mysql> create user ‘u4’@’172.16.12.24’ identified by ‘123’;
十九、使用 help grant,给用户赋权
二十、建立一个 db1 数据库的只读用户
建用户然后授权
mysql> GRANT SELECT ON db1.* TO ‘olr_user’@’%’;
二十一、建立一个只能进行系统状态信息查询的管理用户
mysql> grant select on information_schema.* to ‘admin_user’@’%’;
二十二、建立一个 db1 的生产用户,只能进行 dml、select,不能进行 ddl
mysql> grant select,insert,update,delete on *.* to ‘pro_user’@’%’;
二十三、建立一个可以进行 DDL 的管理用户
mysql> grant create,drop,alter on *.* to ‘admin_user’@’%’;
二十四、建立一个工资表,只有指定的用户可以访问工资列,其他用户都不能访问工资列
实现步骤:
先在 mysql.user 里将所有用户检索出来,进行跑批处理(脚本或存储过程)revoke 对该表列的权限;
然后 grant 创建用户,并对该表列赋访问权限。
二十五、查询上述用户以及所赋权限是否正确,同时进行验证
mysql> select * from mysql.user\G; #查看 MySQL 用户信息
进行用户登录验证
二十六、解释 with grant option,并且演示其功能
mysql> grant all on *.* to ‘zhang’@’%’ identified by ‘123’ with grant option;
with grant option 子句 : 通过在 grant 语句的最后使用该子句,就允许被授权的用户把得到的权限继续授给其他用户。也就是说,客户端用 zhang 用户登录 MySQL,可以将 zhang 用户有的权限使用 grant 进行授权给其他用户。
二十七、查询某一个表上的权限、查看某一个列上的权限、查看某一个数据库上面的权限
1、查询所有数据库的权限
mysql> select * from mysql.user;
2、查询某个数据库的权限
mysql> select * from mysql.db;
3、查询某个数据库中某个表的权限
mysql> select * from mysql.tables_priv;
4、查询某个数据库某个表中某个列的权限
mysql> select * from mysql.columns_priv;
二十八、修改参数运行使用 grant 建立用户,修改参数禁止 grant 建立用户
sql_mode 参数中的 NO_AUTO_CREATE_USER 值:不自动创建用户
mysql>set @@session.sql_mode=……; #设置 sql_mode 参数
二十九、修改 mysql 的用户密码,分别使用 grant、alter、set 修改
①mysql> grant all on *.* to ‘ 用户名 ’@’ 登录主机 ’ identified by ‘ 密码 ’;
②mysql> alter user ‘ 用户名 ’@’ 登录主机 ’ identified by ‘ 密码(自定义)’;
③mysql> SET PASSWORD FOR ‘ 用户名 ’@’ 登录主机 ’ = PASSWORD(‘ 密码 ’);
三十、破解密码步骤:
①到 /etc/my.cnf 里将 validate_password=off 行注释 // 关闭密码策略
②shell> mysqld_safe –skip-grant-tables & // 重启数据库
③shell> mysql -uroot // 无密码登录
④mysql> flush privileges; // 刷新权限使密码生效
⑤修改密码,退出,重启数据库,进入
三十一、使用 revoke 进行权限的收回,将上面用户的授权分别收回,同时查看收回后的结果
①REVOKE INSERT ON *.* FROM ‘jeffrey’@’localhost’;
②REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] …
三十二、select 最简单常用语法
1、全表查询
select * from tbl_name;
2、某些行查询
select * from tbl_name where ……;
3、某些列查询
select clm_name from tbl_name;
4、某些行的某些列查询
select clm_name from tbl_name where ……;
5、列别名
select clm_name as new_name from tbl_name;
6、列运算
select clm_name+123 from tbl_name;
三十三、concat 函数的使用
1、concat 函数:将多个字符串参数首尾相连后返回
2、concat_ws 函数:将多个字符串参数以给定的分隔符,首尾相连后返回
3、group_concat:函数的值等于属于一个组的指定列的所有值,以逗号隔开,并且以字符串表示
三十四、演示打开和关闭管道符号“|”的连接功能
PIPES_AS_CONCAT:将“||”视为字符串的连接操作符而非或运算符
|| 管道连接符:
mysql> select 列名 1 || 列名 2 || 列名 3 from 表名;
在 mysql 中,进行上式连接查询之后,会将查询结果集在一列中显示,列名是‘列名 1 || 列名 2 || 列名 3’
如果不给 sql_mode 参数加入 PIPES_AS_CONCAT 的话,|| 默认是 or 的意思,查询结果是一列显示是 1。
三十五、使用 mysql> help functions; 学习 MySQL 各类函数
三十六、常见功能函数
1、upper(……)、lower(……)大小写变换
2、user()查看登录用户、current_user()查看当前用户
3、database()查看使用的数据库
更多详情见请继续阅读下一页的精彩内容:http://www.linuxidc.com/Linux/2017-05/144012p2.htm
五十五、select 执行的顺序
1、先从表中取数据,访问 innodb buffer pool
from …
join …
on …
where
2、分组、聚合,数据已经进入用户工作空间
group by …
having …
3、select ….:取列数据
4、order by:排序输出
五十六、集合操作
union:结果集去重
union all:结果集不去重
五十七、insert 增
1、insert values 一条数据
表的名字后面最好加上列的名字
2、insert values 多条数据
3、insert into select
select 可以非常复杂,语法完全就是 select
五十八、update 改
基本格式:update 一个表 set 列 where 列条件;
1、一定要带上 where 条件
2、update 分为下面的几个步骤操作
①找到需要 update 的数据,此操作取决于 where 条件
where 条件可以是一个复杂的 where 条件,比如是一个子查询
示例:将平均成绩 75 分以上的学生的级别设置为优等生
update 学生信息表 a
set grade=‘优等生’where a.stuno in (select b.stuno from 成绩表 b group by b.stuno having avg(成绩)>=75);
②set 后面的列,也可以很复杂,比如是一个相对子查询
把 1 和 2 改写成一个 select 语句,不要对一个 update 在生产里面直接进行优化
4、update 可以使用 order by,数据按照顺序进行更新
5、update 可以使用 limit,限制每次更新的行数
五十九、replace 替代已有的行
使用场合 insert+update,两个表数据合并到一起
六十、delete 删
1、绝大多数情况下需要加上 where 条件
2、where 条件可以很复杂,例如是一个子查询
3、理解 delete 和 truncate 的区别
truncate:清空全部数据、速度快、释放空间(不删表)
delete:全部或者部分删除数据、速度慢、不释放空间
六十一、临时表
1、只是针对当前会话有效,临时表和数据都 存储在用户工作空间
2、临时表的使用 很消耗资源
①create、insert、drop,因此在非常频繁的查询环境下,不宜使用临时表;
②临时表需要使用用户工作空间,临时表中存在的数据不易过多,否则容易出现磁盘临时表;
3、临时表的使用场合
需要暂存结果集数据,后面的操作需要访问这些暂存结果集,主要是为了可读性。
4、有一种误区一定要注意,一定不要将普通表作为临时表来使用
原因:普通表当做临时表来使用,下面的操作需要手工去做
①create、insert、truncate 或者 drop
②对于普通表的所有操作都会产生 redo(事务),非常消耗资源
六十二、关于约束
1、非空
2、default 约束
3、主键约束
4、外键约束
5、SET、ENUM 约束
约束注意点:
①尽量选择列都为非空
②对于 bool、时间列经常会出现 default 约束
③每一个表尽最大程度要有主键
④唯一键可以有多个,唯一键可以有空值
⑤外键列一般会有,但是外键约束不建议使用,在应用层面保证主表和外表的一致性
⑥合理使用 set 和 enum 约束,提升数据的质量
⑦外键约束中 on delete、update,尽量不要设置级联删除操作 ( 很危险!!!)
六十三、表的 DDL
1、极其严肃的一个动作
2、使用 help 书写 DDL 语句
3、ddl 动作的后遗症和危险性
①影响 I、D、U、S
②长时间锁表、产生海量 IO
4、测试 DDL 的影响范围 — 优化对象
①锁表时间
②IO 情况
③具体测试要求
示例:产生一个 500 万行的表(写一个存储过程实现),对表进行增加列、删除列、修改列的名字、将列的长度变长、将列的长度变短
看一下上面的这些操作,哪些操作时间长、哪些操作时间短,并对其进行初步的原理分析
六十四、视图的最佳实践
1、视图就是 select 的一个名字
2、不建议使用复杂视图
select 语句里面 不要带有 distinct、group by、聚合函数、union 等操作
3、不建议在视图中嵌套视图
4、视图的主要使用场合
统一访问接口(select)— 主要的好处
规范访问
隐藏底层表结构、ddl 不影响应用访问
5、视图在安全方面的意义
六十五、存储过程(脚本)
1、存储过程使用的场合
①重复性很高的复合操作(dml)
②统一访问接口(dml、事务)
③批量业务(跑批)
2、存储过程结构分析
①存储过程中嵌入了 dml、select
②存储过程有参数,参数的不同会产生不同的事务
in、out、inout
③存储过程里面有结构化语句,即流程控制语句:
循环
条件判断
使得在执行 dml、select 的时候,变得方便
④存储过程可以定义变量
select 取出来的结果可以存储到变量中
dml 需要的输入值可以通过变量来实现
⑤存储过程里面可以有游标 ,游标的核心就是 可以对一个结果集进行处理
1)定义游标(游标和一个 select 关联)
2)打开游标(将 select 的结果赋给游标,可以是 N 行列)
3)遍历游标(一行行数据获取,每一行数据赋给 N 个变量)
4)关闭游标
⑥存储过程有异常处理部分
1)异常处理是一个存储过程是否可以产品化、商业化很重要的一个标志
2)异常处理只关心 SQL 语句的异常
每一个存储过程都要对着三类 SQLWARNING、NOT FOUND、SQLEXCEPTION 进行处理;
存储过程异常处理通常只是进行错误的记录,或者空处理。
⑦存储过程书写过程
1)定义一个结构
存储过程基本结构
参数
异常处理
2)书写涉及到 SQL 语句
3)考虑使用变量、游标、条件判断、循环将 SQL 语句组合起来
4)经常使用 begin end 来将一组 SQL 语句或者语句组合起来,作为一个语句来出现
3、存储过程安全方面的意义:防止对底层表直接进行 dml
六十六、自定义函数
1、自定义函数和存储过程的区别
①有一个返回值
CREATE FUNCTION SimpleCompare(n INT, m INT)
RETURNS VARCHAR(20)
……
②调用的时候必须放在 = 的右边
set @ax = SimpleCompare(1,2);
2、整理笔记,将函数定义和函数调用整理一个例子出来
六十七、触发器
1、尽量少使用触发器,不建议使用
2、触发器是一个 begin end 结构体
3、触发器和存储过程的唯一区别就是在于被执行方式上的区别
存储过程需要手工去执行
触发器被 DML 自动触发
4、触发器被触发的条件
①for each row(每一行都被触发一次,这就决定了 频繁 dml 的表上面不要有触发器)
②增删改都可以定义触发器
③before、after 可以定义触发的时机
5、触发器中经常 使用 new、old
insert 里面可以有 new
delete 里面可以有 old
update 里面可以有 new、old
6、使用触发器的场合
一般用来进行审计使用:产品价格表里面的价格这个列,只要是有人对这个表的这个列进行更新,就要保存修改前和修改后的值,将这个信息记录到一个单独的表中(审计表)
7、要求你将触发器的例子保存到笔记中
①insert 触发器(new)
②delete 触发器(old)
③update 触发器(new、old)
④before、after
六十八、event
1、周期性执行
①linux 里面的 at、crontab
②MySQL 里面的 event
2、event 的核心知识点
①执行一次
SQL 语法考核
SQL 语法多变,不敢保证唯一,也不敢保证全对,如果错误欢迎指出,即刻修改。
一、现有表结构如下图
TABLENAME:afinfo
Id |
name |
age |
birth |
sex |
memo |
1 |
徐洪国 |
37 |
1979-03-23 |
男 |
高中 |
2 |
王芳 |
26 |
1988-02-06 |
女 |
本科 |
3 |
李达康 |
24 |
1990-04-02 |
男 |
硕士 |
4 |
侯亮平 |
30 |
1984-09-12 |
女 |
博士 |
5 |
徐夫子 |
27 |
1987-12-30 |
男 |
大专 |
6 |
…… |
…… |
…… |
…… |
…… |
1)请编写 sql 语句对年龄进行升序排列
MySQL> select * from afinfo
-> order by birth;
2)请编写 sql 语句查询对“徐”姓开头的人员名单
mysql> select * from afinfo
-> where name like '徐 %';
3)请编写 sql 语句修改“李达康”的年龄为“45”
mysql> update afinfo
-> set age=45
-> where name='李达康';
4)请编写 sql 删除王芳这表数据记录。
mysql> delete from afinfo
-> where name='王芳';
二、现有以下学生表和考试信息表
学生信息表(student)
姓名 name |
学号 code |
张三 |
001 |
李四 |
002 |
马五 |
003 |
甲六 |
004 |
考试信息表(exam)
学号 code |
学科 subject |
成绩 score |
001 |
数学 |
80 |
002 |
数学 |
75 |
001 |
语文 |
90 |
002 |
语文 |
80 |
001 |
英语 |
90 |
002 |
英语 |
85 |
003 |
英语 |
80 |
004 |
英语 |
70 |
1)查询出所有学生信息,SQL 怎么编写?
mysql> select * from student;
2)新学生小明,学号为 005,需要将信息写入学生信息表,SQL 语句怎么编写?
mysql> insert into student values('小明','005');
3)李四语文成绩被登记错误,成绩实际为 85 分,更新到考试信息表中,SQL 语句怎么编写?
mysql> update exam,student
-> set exam.score=85
-> where student.code=exam.code
-> and student.name='李四'
-> and exam.subject='语文';
4)查询出各科成绩的平均成绩,显示字段为:学科、平均分,SQL 怎么编写?
mysql> select subject 学科,avg(score) 平均分
-> from exam
-> group by subject;
5)查询出所有学生各科成绩,显示字段为:姓名、学号、学科、成绩,并以学号与学科排序,没有成绩的学生也需要列出,SQL 怎么编写?
mysql> select s.name 姓名,s.code 学号,e.subject 学科,e.score 成绩
-> from student s
-> left join exam e
-> on s.code=e.code
-> order by 学号, 学科;
6)查询出单科成绩最高的,显示字段为:姓名、学号、学科、成绩,SQL 怎么编写?
mysql> select s.name 姓名,s.code 学号,
-> sum(if(e.subject='语文',e.score,0)) 语文成绩,
-> sum(if(e.subject='数学',e.score,0)) 数学成绩,
-> sum(if(e.subject='英语',e.score,0)) 英语成绩
-> from student s
-> left join exam e
-> on s.code=e.code
-> group by s.name,s.code;
表结构:
student(s_no,s_name,s_age,sex) 学生表
teacher(t_no,t_name) 教师表
course(c_no,c_name,t_no) 课程表
sc(s_no,c_no,score) 成绩表
基础表数据 (个人铺的): 根据题目需要自行再铺入数据
mysql> select s_no,avg(score)
-> from sc
-> group by s_no
-> having avg(score)>60;
3、查询所有同学的学号、姓名、选课数、总成绩。
mysql> select student.s_no,student.s_name,count(sc.c_no),sum(sc.score)
-> from student
-> left join sc
-> on student.s_no=sc.s_no
-> group by student.s_no,student.s_name;
4、查询姓李的老师的个数。
mysql> select count(*)
-> from teacher
-> where t_name like '李 %';
5、查询没学过“叶平”老师课的同学的学号、姓名
mysql> select student.s_no,student.s_name
-> from student
-> join sc
-> on sc.s_no=student.s_no
-> where sc.score<60;
10、查询没有学全所有课的同学的学号、姓名。
mysql> set @ye_c_no=(select c_no from course,teacher where course.t_no=teacher.t_no and t_name=’叶平’);
mysql> delete from sc
-> where c_no=@ye_c_no;
16、向 sc 表中插入一些记录,这些记录要求符合一下条件:没有上过编号“003”课程的同学学号
mysql> select distinct s_no from sc
-> where c_no not in (select c_no from sc where c_no='003')
-> and s_no not in (select s_no from sc where c_no='003');
17、查询各科成绩最高和最低的分:以如下形式显示:课程 ID,最高分,最低分。
mysql> select c_no 课程 ID,max(score) 最高分,min(score) 最低分
-> from sc
-> group by c_no;
18、按照平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“马克思”三门的课程成绩,按如下形式显示:学生 ID, 数据库,企业管理,马克思,有效课程数,有效平均分。
mysql> select c_no,avg(score)
-> from sc
-> group by c_no
-> order by 2 desc;
20、查询如下课程成绩第 3 名到第 6 名的学生成绩单:企业管理(001)、马克思(002),UML(003),数据库(004)
mysql> select c_no 课程 ID,count(s_no) 学生人数
-> from sc
-> group by c_no;
23、查询出只选修了一门课程的全部学生的学号和姓名
mysql> select s_name 姓名,count(*)
-> from student
-> group by s_name
-> having count(*)>1;
25、查询 1994 年出生的学生名单(注:student 表中 sage 列的类型是 datatime)
mysql> select * from student
-> where year(curdate())-s_age='1994';
26、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列。
mysql> select c_no 课程 ID,avg(score)
-> from sc
-> group by c_no
-> order by avg(score) asc,c_no desc;
27、查询平均成绩都大于 85 的所有学生的学号,姓名和平均成绩
mysql> select student.s_no 学号,s_name 姓名,avg(score) 平均成绩
-> from student,sc
-> where student.s_no=sc.s_no
-> group by student.s_no,s_name
-> having avg(score)>85;
28、查询课程名称为“数据库”且分数低于 60 的学生姓名和分数
mysql> select s_name 学生姓名,score 分数
-> from student,sc,course
-> where student.s_no=sc.s_no and sc.c_no=course.c_no
-> and c_name='数据库'
-> and score<60;
29、查询所有学生的选课情况
mysql> select student.s_no 学号,student.s_name 姓名,group_concat(c_no) 所选课程 ID
-> from student,sc
-> where student.s_no=sc.s_no
-> group by student.s_no,student.s_name;
30、查询任何一门课程成绩在 90 分以上的姓名、课程名称和分数。
mysql> select s_name 姓名,c_name 课程名称,score 分数
-> from student,sc,course
-> where student.s_no=sc.s_no and sc.c_no=course.c_no
-> and score > 90
-> order by s_name;
31、查询不及格的课程,并按课程号从大到小排序。
mysql> select s_no 学生 ID,c_no 不及格课程 ID
-> from sc
-> where score<60
-> order by c_no desc;
32、求选修了课程的学生人数。
mysql> select course.c_no 课程 ID,course.c_name 课程名,count(s_no) 选修人数
-> from course
-> join sc
-> on course.c_no=sc.c_no
-> group by course.c_no,course.c_name;
35、查询不同课程成绩相同的学生的学号、课程号、学生成绩。
mysql> select a.s_no 学号,group_concat(a.c_no) 课程号,a.score 学生成绩
-> from sc a,sc b
-> where a.score=b.score and a.c_no<>b.c_no
-> group by a.s_no,a.score;
36、查询每门课程最好的前两名。
mysql> select a.s_no,a.c_no,a.score
-> from sc a
-> where
-> (select count(distinct score) from sc b where b.c_no=a.c_no and b.score>=a.score)<=2
-> order by a.c_no,a.score desc;
37、检索至少选修两门课程的学生学号。
mysql> select s_no 学生学号
-> from sc
-> group by s_no
-> having count(*)>=2;
38、查询全部学生都选修的课程的课程号和课程名。
mysql> select course.c_no 课程号,c_name 课程名
-> from course
-> join sc on course.c_no=sc.c_no
-> join (-> select c_no,count(s_no) from sc group by c_no
-> having count(s_no)=(select count(*) from student)) as a
-> on course.c_no=a.c_no;
39、查询没有学过“叶平”老师讲授的任一门课程的学号姓名。
mysql> select student.s_no 学号,student.s_name 姓名
-> from student
-> join sc
-> on sc.s_no=student.s_no
-> where sc.s_no not in
-> (-> select s_no
-> from course,teacher,sc
-> where course.t_no=teacher.t_no and sc.c_no=course.c_no
-> and teacher.t_name='叶平'
-> );
40、查询两门以上不及格课程的同学的学号及其平均成绩。
mysql> select s_no 学号,avg(score) 平均成绩
-> from sc
-> where s_no in (-> select s_no from sc
-> where score<60
-> group by s_no
-> having count(*)>2)
-> group by s_no;
四、根据表 1 和表 2 的信息写出 SQL
表 1:books 书表 b
主码 |
列标题 |
列名 |
数据类型 |
宽度 |
小数位数 |
是否空值 |
P |
书号 |
TNO |
char |
15 |
no |
|
书名 |
TNAME |
varchar |
50 |
no |
||
作者姓名 |
TAUTHOR |
varchar |
8 |
no |
||
出版社编号 |
CNO |
char |
5 |
yes |
||
书类 |
TCATEGORY |
varchar |
20 |
yes |
||
价格 |
TPRICE |
numeric |
8 |
2 |
yes |
表 2:book_concern 出版社表 C
主码 |
列标题 |
列名 |
数据类型 |
宽度 |
小数位数 |
是否空值 |
p |
出版社编号 |
CNO |
char |
5 |
NO |
|
出版社名称 |
CNAME |
varchar |
20 |
NO |
||
出版社电话 |
CPHONE |
varchar |
15 |
YES |
||
出版社城市 |
CCITY |
varchar |
20 |
YES |
1、查询出版过“计算机”类图书的出版社编号(若一个出版社出版过多部“计算机”类图书,则在查询结果中该出版社编号只显示一次)
mysql> select distinct cno 出版社编号
-> from books
-> where tcategory='计算机';
2、查询南开大学出版社的“经济”类或“数学”类图书的信息。
mysql> select *
-> from books,book_concern
-> where books.cno=book_concern.cno
-> and cname='南开大学出版社'
-> and tcategory in ('数学 ',' 经济');
3、查询编号为“20001”的出版社出版图书的平均价格。
mysql> select cno 出版社编号,avg(tprice) 图书均价
-> from books
-> where cno='20001';
4、查询至少出版过 20 套图书的出版社,在查询结果中按出版社编号的升序顺序显示满足条件的出版社编号、出版社名称和每个出版社出版的图书套数。
mysql> select b.cno 出版社编号,cname 出版社名称,count(*) 图书套数
-> from books b,book_concern c
-> where b.cno=c.cno
-> group by b.cno,cname
-> having count(*)>20
-> order by b.cno;
5、查询比编号为“20001”的出版社出版图书套数多的出版社编号。
mysql> select cno 出版社编号
-> from books
-> group by cno
-> having count(*)>(select count(*) from books where cno='20001');
五、一道关于 group by 的经典面试题:
有一张 shop 表如下,有三个字段 article、author、price,选出每个 author 的 price 最高的记录(要包含所有字段)。
mysql> select article,s1.author,s1.price
-> from shop s1
-> join ( -> select author,max(price) price
-> from shop
-> group by author) s2
-> on s1.author=s2.author and s1.price=s2.price;
3、使用 left join 语句(毕竟子查询在有些时候,效率会很低)
mysql> select s1.article,s1.author,s1.price
-> from shop s1
-> left join shop s2
-> on s1.author=s2.author and s1.price<s2.price
-> where s2.article is null;
原理分析:当 s1.price 是当前 author 的最大值时,就没有 s2.price 比它还要大,所以此时 s2 的 rows 的值都会是 null。
六、用一条 SQL 语句查询出每门课都大于 80 分的学生
name |
kecheng |
fenshu |
张三 |
语文 |
81 |
张三 |
数学 |
75 |
李四 |
语文 |
76 |
李四 |
数学 |
90 |
王五 |
语文 |
81 |
王五 |
数学 |
100 |
王五 |
英语 |
90 |
mysql> select a.name 姓名
-> from
-> (select name,count(*) anum from NO_6 where fenshu>80 group by name) a,
-> (select name,count(*) bnum from NO_6 group by name) b
-> where a.name=b.name
-> and a.anum=b.bnum;
Year |
month |
amount |
1991 |
1 |
1.1 |
1991 |
2 |
1.2 |
1991 |
3 |
1.3 |
1991 |
4 |
1.4 |
1992 |
1 |
2.1 |
1992 |
2 |
2.2 |
1992 |
3 |
2.3 |
1992 |
4 |
2.4 |
查成这样一个结果
year |
M1 |
M2 |
M3 |
M4 |
1991 |
1.1 |
1.2 |
1.3 |
1.4 |
1992 |
2.1 |
2.2 |
2.3 |
2.4 |
mysql> select year,
-> sum(if(month=1,amount,0)) M1,
-> sum(if(month=2,amount,0)) M2,
-> sum(if(month=3,amount,0)) M3,
-> sum(if(month=4,amount,0)) M4
-> from NO_7
-> group by year;
请写出 SQL 查询表 A 中存在 ID 重复三次以上的记录。
mysql> select IP from login_ftp
-> group by IP
-> having count(*)>3;
九、创建存储过程,要求具有游标 ( 遍历表 ) 示例
本文永久更新链接地址:http://www.linuxidc.com/Linux/2017-05/144012.htm