阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

MySQL开发心得笔记超详细及SQL语法考核

262次阅读
没有评论

共计 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 下各个对象都不区别大小写

mysql> show variables like lower%;
+————————+——-+
| Variable_name          | Value |
+————————+——-+
| lower_case_file_system | OFF  |
| lower_case_table_names | 0    |
+————————+——-+
lower_case_file_system是对实际的文件系统的反应,为只读变量,不能修改。Off 表示 MySQL 所在的文件系统大小写敏感,也就是说进入 MySQL 所在的文件系统查看里面的内容,发现有 mysql 文件夹,此时新建一个名为 MYSQL 的文件夹是可以的,说明大小写敏感。

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 variable_assignment [, variable_assignment] …

variable_assignment:
user_var_name = expr #变量名字 =一个值
|[GLOBAL | SESSION] system_var_name = expr
|[@@global. | @@session. | @@]system_var_name = expr

 
①set global 表示修改后对全部会话生效,为全局修改变量

②set session 表示修改后对本次会话生效

如果变量是只读变量 可以通过修改 MySQL 的配置文件 my.cnf 来修改变量,在 [mysqld] 下添加一行数据:user_var_name=expr,然后 重启数据库再登录即可。

十、MySQL 的状态参数如何查看、如何参考阅读其内容

在官方文档的 Server Option / Variable Reference 部分,进行参考查看 MySQL 的参数变量以及状态值

MySQL 开发心得笔记超详细及 SQL 语法考核

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 建立用户

mysql> show variables like sql_mode%;
+—————+——————————————————————————————————————————————-+
| Variable_name | Value                                                                                                                                    |
+—————+——————————————————————————————————————————————-+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+—————+——————————————————————————————————————————————-+
1 row in set (0.37 sec)
 

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’

mysql> select s_no || s_name || s_age
   
-> from student;
+————————-+
| s_no || s_name || s_age |
+————————-+
| 1001 张三 23              |
| 1002 李四 19              |
+————————-+
如果不显示结果,是因为 sql_mode 参数中没有PIPES_AS_CONCAT,只要给 sql_mode 参数加入 PIPES_AS_CONCAT,就可以实现像 CONCAT 一样的功能;

如果不给 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

select * from 学生信息 a where a.stuno not in (select stuno from 选课信息表);

select * from 学生信息 a
left join 选课信息 b
on  a.stuno
=b.stuno
where b. 成绩 is null;

4、in、exists 可以转换成 distinct join
select * from 学生信息 a where a.stuno in (select stuno from 选课信息表 b);

select * from 学生信息 a where exists (select 1 from 选课信息 b where a.stuno=b.stuno);

select distinct a.*
from 学生信息
join 选课信息 b
on a.stuno
=b.stuno;

五十二、连接的具体使用含义

1、理解为什么会出现表连接:查询的列来自于多个表

select
from ..
where
group by 列
having 列
order by 列
limit x

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 下各个对象都不区别大小写

mysql> show variables like lower%;
+————————+——-+
| Variable_name          | Value |
+————————+——-+
| lower_case_file_system | OFF  |
| lower_case_table_names | 0    |
+————————+——-+
lower_case_file_system是对实际的文件系统的反应,为只读变量,不能修改。Off 表示 MySQL 所在的文件系统大小写敏感,也就是说进入 MySQL 所在的文件系统查看里面的内容,发现有 mysql 文件夹,此时新建一个名为 MYSQL 的文件夹是可以的,说明大小写敏感。

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 variable_assignment [, variable_assignment] …

variable_assignment:
user_var_name = expr #变量名字 =一个值
|[GLOBAL | SESSION] system_var_name = expr
|[@@global. | @@session. | @@]system_var_name = expr

 
①set global 表示修改后对全部会话生效,为全局修改变量

②set session 表示修改后对本次会话生效

如果变量是只读变量 可以通过修改 MySQL 的配置文件 my.cnf 来修改变量,在 [mysqld] 下添加一行数据:user_var_name=expr,然后 重启数据库再登录即可。

十、MySQL 的状态参数如何查看、如何参考阅读其内容

在官方文档的 Server Option / Variable Reference 部分,进行参考查看 MySQL 的参数变量以及状态值

MySQL 开发心得笔记超详细及 SQL 语法考核

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 建立用户

mysql> show variables like sql_mode%;
+—————+——————————————————————————————————————————————-+
| Variable_name | Value                                                                                                                                    |
+—————+——————————————————————————————————————————————-+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+—————+——————————————————————————————————————————————-+
1 row in set (0.37 sec)
 

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’

mysql> select s_no || s_name || s_age
   
-> from student;
+————————-+
| s_no || s_name || s_age |
+————————-+
| 1001 张三 23              |
| 1002 李四 19              |
+————————-+
如果不显示结果,是因为 sql_mode 参数中没有PIPES_AS_CONCAT,只要给 sql_mode 参数加入 PIPES_AS_CONCAT,就可以实现像 CONCAT 一样的功能;

如果不给 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 执行的顺序

select
from
join …
on …
where
group by ..
having …
order by …

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 后面的列,也可以很复杂,比如是一个相对子查询

UPDATE players_data pd
SET number_mat
= (
SELECT count(
*)
FROM matches m
WHERE m.playerno
= pd.playerno),
sum_penalties
= (
SELECT sum(amount)
FROM penalties pen
WHERE pen.playerno
= pd.playerno);
3、update 可以改写成一个 select 语句

把 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 万行的表(写一个存储过程实现),对表进行增加列、删除列、修改列的名字、将列的长度变长、将列的长度变短

MySQL> delimiter $$
mysql
> create procedure do_big(x int)
   
-> begin
   
-> declare v int;
   
-> set v=x;
   
-> create table test(test_num int auto_increment not null primary key);
   
-> while v>0 do
    -> insert into test values(null);
   
-> set v=v-1;
   
-> end while;
   
-> end $$
mysql
> delimiter ;
mysql
> call do_big(5000000);
……
mysql
> select count(*) from test;
+———-+
| count(*) |
+———-+
| 5000000 |
+———-+
 

看一下上面的这些操作,哪些操作时间长、哪些操作时间短,并对其进行初步的原理分析

mysql> insert into test values(123456789);

mysql> delete from test where test_num=123;

mysql> alter table test CHANGE COLUMN                                     
   
-> test_num
   
-> test_id  int(10) not null auto_increment;

mysql> alter table test modify test_id int(100);

mysql> alter table test modify test_id int(20);

总结:对于一个大表而言,将列的长度变长时间是最长的,其他的操作处理时间都还挺短。

六十四、视图的最佳实践

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 的核心知识点

①执行一次

CREATE EVENT myevent
ON SCHEDULE AT CURRENT_TIMESTAMP
+ INTERVAL 1 MINUTE
DO
begin
UPDATE t1 SET mycol
= mycol + 1;
end
②周期性执行
CREATE EVENT myevent
ON SCHEDULE EVERY
1 DAY STARTS STR_TO_DATE(‘20170501 20:00:00’,yyyy-mm-dd hh24:mi:ss)
DO
begin
UPDATE t1 SET mycol
= mycol + 1;
end

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 学号,e.subject 学科,e.score 成绩
   
-> from student s
   
-> join exam e
   
->    on s.code=e.code
   
-> where (e.subject,e.score) in
    -> (
   
->  select subject,max(score)
   
->  from exam
   
->  group by subject
   
-> );
7)列出每位学生的各科成绩,要求输出格式:姓名、学号、语文成绩、数学成绩、英语成绩,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;
三、根据要求写出 SQL 语句

表结构:

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 * from student;
+——+——–+——-+——+
| s_no | s_name | s_age | sex  |
+——+——–+——-+——+
| 1001 | 张三    |    23 | 男  |
| 1002 | 李四    |    19 | 女  |
| 1003 | 马五    |    20 | 男  |
| 1004 | 甲六    |    17 | 女  |
| 1005 | 乙七    |    22 | 男  |
+——+——–+——-+——+
5 rows in set (0.00 sec)

mysql> select * from teacher;
+——+——–+
| t_no | t_name |
+——+——–+
| 2001 | 叶平  |
| 2002 | 赵安  |
| 2003 | 孙顺  |
| 2004 | 刘六  |
+——+——–+
4 rows in set (0.00 sec)

mysql> select * from course;
+——+————–+——+
| c_no | c_name      | t_no |
+——+————–+——+
001 | 企业管理      | 2001 |
002 | 马克思        | 2002 |
003 | UML          | 2003 |
004 | 数据库        | 2004 |
+——+————–+——+
4 rows in set (0.05 sec)

mysql> select * from sc;
+——+——+——-+
| s_no | c_no | score |
+——+——+——-+
| 1001 | 001  |    93 |
| 1001 | 002  |    86 |
| 1001 | 004  |    92 |
| 1002 | 003  |  100 |
| 1003 | 001  |    93 |
| 1003 | 004  |    99 |
| 1004 | 002  |    75 |
| 1004 | 003  |    59 |
| 1002 | 002  |    50 |
| 1005 | 003  |    60 |
| 1005 | 002  |    60 |
+——+——+——-+
11 rows in set (0.00 sec)

1、查询“001”课程比“002”课程成绩高的所有学生的学号。
mysql> select a.s_no
   
-> from
    -> (select s_no,score from sc where c_no=001) a,
   
-> (select s_no,score from sc where c_no=002) b
   
-> where a.score>b.score
   
-> and a.s_no=b.s_no;
2、查询平均成绩大于 60 分的同学的学号和平均成绩。
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
   
-> where student.s_no not in
    -> (
   
-> select distinct(sc.s_no)
   
-> from sc
   
-> join course
   
-> on course.c_no=sc.c_no
   
-> join teacher
   
-> on teacher.t_no=course.t_no
   
-> where t_name=叶平
    -> );
6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名。
mysql> select student.s_no,student.s_name
   
-> from student
   
-> join sc
   
-> on sc.s_no=student.s_no
   
-> where c_no=001
    -> and exists
   
-> (select * from sc where sc.s_no=student.s_no and c_no=002);
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名。
mysql> select student.s_no,student.s_name
   
-> from student
   
-> join sc
   
->    on sc.s_no=student.s_no
   
-> join course
   
->    on course.c_no=sc.c_no
   
-> join teacher
   
->    on teacher.t_no=course.t_no
   
-> where teacher.t_name=叶平;
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名。
mysql> select student.s_no,student.s_name
   
-> from student
   
-> join (select s_no,score from sc where c_no=001) a
   
->  on a.s_no=student.s_no
   
-> join (select s_no,score from sc where c_no=002) b
   
->  on b.s_no=student.s_no
   
-> where a.s_no=b.s_no
   
-> and a.score>b.score;
9、查询所有课程成绩小于 60 分的同学的学号、姓名。
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> select student.s_no 学号,student.s_name 姓名
   
-> from student
   
-> left join sc
   
->  on sc.s_no=student.s_no
   
-> group by student.s_no,student.s_name
   
-> having count(*) < (
   
-> select count(*) from course);
11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名。
mysql> select student.s_no,student.s_name
   
-> from student
   
-> join sc
   
-> on sc.s_no=student.s_no
   
-> where sc.c_no in
    -> (
   
-> select c_no
   
-> from sc
   
-> where s_no=1001
    -> )
   
-> and student.s_no != 1001;
12、查询至少学过学号为“1001”同学所有一门课的其他同学学号和姓名。
mysql> select distinct sc.s_no,s_name
   
-> from student,sc
   
-> where student.s_no=sc.s_no
   
-> and c_no in
    -> (select c_no from sc where s_no=1001)
   
-> and student.s_no != 1001;
13、把“sc”表中“叶平”老师叫的课的成绩都更改为此课程的平均成绩。
mysql> set @ye_avg_score=
    -> (
   
-> select avg(score)
   
-> from
    ->    (
   
-> select sc.score
   
-> from sc
   
-> join course
   
-> on course.c_no=sc.c_no
   
-> join teacher
   
-> on teacher.t_no=course.t_no
   
-> where teacher.t_name=叶平
    -> ) azi
    -> );

mysql> update sc
   
-> set score=@ye_avg_score
   
-> where c_no in
    -> (
   
-> select c_no
   
-> from course
   
->    join teacher
   
-> on teacher.t_no=course.t_no
   
->    where teacher.t_name=叶平
    -> );

14、查询和“1002”号同学学习的课程完全相同的其他同学学号和姓名。
mysql> select s_no,s_name
    -> from student
    -> where s_no in (
    -> select distinct s_no from sc where c_no in
    -> (select c_no from sc where s_no=1002)
    -> group by s_no
    -> having count(*)=(select count(*) from sc where s_no=1002)
    -> and s_no<>1002
    -> );
15、删除学习“叶平”老师课的 sc 表记录。
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 sc.s_no 学号,
   
-> max(case c_name when 数据库 then score end) 数据库,
   
-> max(case c_name when 企业管理 then score end) 企业管理, -> max(case c_name when 马克思 then score end) 马克思,
   
-> count(sc.s_no) 有效课程数,
   
-> avg(ifnull(score,0)) 有效平均分
   
-> from sc,course
   
-> where sc.c_no=course.c_no
   
-> group by sc.s_no
   
-> order by 6 desc;
19、查询不同老师所教不同课程平均分从高到低显示。
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 student.s_no,s_name,c_no,score from student,sc where student.s_no=sc.s_no and c_no=001 order by score desc limit 2,4)
   
-> union
   
-> (select student.s_no,s_name,c_no,score from student,sc where student.s_no=sc.s_no and c_no=002 order by score desc limit 2,4)
   
-> union
   
-> (select student.s_no,s_name,c_no,score from student,sc where student.s_no=sc.s_no and c_no=003 order by score desc limit 2,4)
   
-> union
   
-> (select student.s_no,s_name,c_no,score from student,sc where student.s_no=sc.s_no and c_no=004 order by score desc limit 2,4);
21、统计各科成绩,各分数段人数:课程 ID,课程名称,【100-85】,【85-70】,【70-60】,【<60】
mysql> select course.c_no 课程 ID,c_name 课程名称,
   
-> count(case when score>85 and score<=100 then score end) [85-100],
   
-> count(case when score>70 and score<=85 then score end) [70-85],
   
-> count(case when score>=60 and score<=70 then score end) [60-70],
   
-> count(case when score<60 then score end) [<60]
    -> from course,sc
   
-> where course.c_no=sc.c_no
   
-> group by course.c_no,c_name;
22、查询每门课程被选修的学生数
mysql> select c_no 课程 ID,count(s_no) 学生人数
    -> from sc
    -> group by c_no;

23、查询出只选修了一门课程的全部学生的学号和姓名

mysql> select student.s_no 学号,student.s_name 姓名,count(c_no) 选课数
   
-> from student
   
-> join sc
   
-> on sc.s_no=student.s_no
   
-> group by student.s_no,student.s_name
   
-> having count(c_no)=1;
24、查询同名同性学生名单,并统计同名人数。
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 count(*) 已选课程人数
   
-> from
    -> (
   
->    select distinct(sc.s_no) from student
   
->    left join sc
   
-> on sc.s_no=student.s_no
   
-> where c_no is not null
    -> ) as  ayixuan;
33、查询选修了“冯老师”所授课程的学生中,成绩最高的学生姓名及其成绩。
mysql> select s_name 学生姓名,score 成绩
   
-> from student,sc,course,teacher
   
-> where student.s_no=sc.s_no and sc.c_no=course.c_no and course.t_no=teacher.t_no
   
-> and t_name= 冯老师
    -> order by score
   
-> limit 1;
34、查询各个课程及相应的选修人数。
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 * from shop;
+———+——–+——-+
| article | author | price |
+———+——–+——-+
|    0001 | B      |  9.95 |
|    0002 | A      | 10.99 |
|    0003 | C      |  1.69 |
|    0004 | B      | 19.95 |
|    0005 | A      |  6.96 |
+———+——–+——-+
5 rows in set (0.02 sec)
 
1、使用相关子查询
mysql> select article,author,price
    -> from shop s1
    -> where price = (
    -> select max(s2.price)
    -> from shop s2
    -> where s1.author=s2.author);
2、使用非相关子查询
 
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;
八、已知表 A =login_ftp 记录着登录 FTP 服务器的计算机 IP、时间等字段信息

请写出 SQL 查询表 A 中存在 ID 重复三次以上的记录。

mysql> select IP from login_ftp
    -> group by IP
    -> having count(*)>3;

九、创建存储过程,要求具有游标 ( 遍历表 ) 示例

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE a CHAR(
16);
  DECLARE b, c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done
= TRUE;

  OPEN cur1;
  OPEN cur2;

  read_loop: LOOP
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF done THEN
      LEAVE read_loop;
    END IF;
    IF b < c THEN
      INSERT INTO test.t3 VALUES (a,b);
    ELSE
      INSERT INTO test.t3 VALUES (a,c);
    END IF;
  END LOOP;

  CLOSE cur1;
  CLOSE cur2;
END;

本文永久更新链接地址:http://www.linuxidc.com/Linux/2017-05/144012.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计41897字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中