【笔记】MySQL常用命令

MySQL

第一章 认识数据库

1.数据库介绍

数据库DB：data base 就是一个文件集合，用来存储数据。

DB分类：关系型数据库、非关系型数据库

RDBMS关系型数据库管理系统：Relational Database Management System

主流的RDBMS：Oracle（收费，一般政府、银行用）、MySQL、SQL Server、DB2、SQLite（手机上轻量级）; 通过表来管理关系（核心）;

非关系型数据库：MongoDB、Redis

MySQL由MySQL AB公司开发→Sun收购→Oracle（甲骨文）收购，用C，C++编写；

MySQL优点：1、开源免费，支持多平台；2、轻量级数据库；3、大中型网站也适用；

数据库服务器（MySQL）、数据库（DB）、表（Table）的关系：

所谓安装数据库服务器就是在机器上装一个数据库管理系统（如MySQL），用来管理数据库。一般开发会针对每个应用创建一个数据库。

为保存实体的数据，一般会在数据库中创建多个表。

2. 连接MySQL方式

(1) Windows

① Navicat（数据库管理工具） ：安装后，输入主机名（ip），端口号（默认3306），账户，密码进行连接。

② Cmd命令连接：mysql -h主机号 -P端口 -u用户名 -p密码

(2) Linux：命令行连接（参数后不加空格）

mysql -h主机号 -P端口 -u用户名 -p密码

(3) （了解）命令行常用操作

退出数据库：exit、quit

查看数据范围：help 数据名；eg：help int；

命令行备份与恢复：

备份：——以管理员身份运行cmd；

——进入到MySQL安装目录下的bin目录：cd ...\bin

——备份数据库到某个文件：mysqldump -u root -p 需要备份的库名>目标文件 名.sql

恢复：——以管理员身份运行cmd；

——进入到MySQL安装目录下的bin目录：cd ...\bin

——创建新的数据库（编码需要相同）

——之前备份的文件恢复到新数据库：mysql -u root -p 新库名<备份文件，输 入密码

(4) Navicat使用

快捷键：1）crtl r 执行所有语句；

2）crtl +/- 调整字体大小

备份与恢复：

备份：右键数据库名字—转储SQL文件—结构和数据；

恢复：新建数据库（字符集必须相同）→右键运行SQL文件→刷新表→恢复成功；

3. SQL介绍

SQL（Structured Query Language）结构化查询语言：专门为数据库建立的操作命令集，功能齐全的数据库语言。

SQL分为：DQL数据查询语言（select）、DML数据操作语言（增删改）、TPL事务处理语言、DCL控制语言、DDL定义、CCL指针。

SQL语法特点：1、不区分大小写；

2、关键字、字段名、表名用空格和逗号隔开；

3、每个语句用‘；’结尾；

4、语句可以一行，也可以分开写多行；

SQL注释：（1）#、--，单行注释

（2）选中按crtl / 注释，crtl shift / 取消注释

（3）多行注释：/*注释内容*/

第二章 数据库常用命令操作

第1节 创建、查看、删除、使用数据库

1. 创建数据库: Create database 数据库名 charset=’utf-8’；（charset默认就是utf-8，可省略）

2. 查看数据库: show databases；

3. 删除数据库: drop database 数据库名；

4. 选中某个数据库: use 数据库名；

5. （了解即可）其他常用命令：

(1) 查看当前使用数据库：select database();

(2) 查看创建表的语句：show create table 表名；

(3) 修改命令行编码为gbk：set names ‘gbk’；一般用于命令行汉字显示乱码时；

第2节 创建、查看、删除、修改数据表

1. 创建表

1）表中数据的存储方式：列→行→表→数据库；

2）表中数据类型

a. 数值型

说明：1、decimal（M,D）定点数，共M位，其中D位小数值；float和double也可以和decimal一样定点显示，例如float（10,2），不设定点就按照实际小数位展示；

2、int（n），这个n不写默认是11，只有在zerofill时,小于n的会用0前补足n位，但是超过n的也会正常显示，这个不影响int类型的实际范围；

3、Bigint（n）默认的n=20；

b. 字符型（需要加“”号）

c. 日期型

3）创建表：create table 表名；

Primary key 一般放在最后；

2. 查看表（当前数据库）

(1) show tables；查看当前数据库所有的表

(2) Desc 表名；查看表的具体结构

3. 删除表：drop table 表名；

在程序中，一般会先删除表后再创建表，防止表名冲突，语句为：

Drop table if exists 表名；

4. 修改表：alter table（列的增、删、改）

(1) 向数据表中添加一列：

语法：alter table 表名 add 列名 列的数据格式 约束条件；

默认自动添加到表末尾；

如果需要添加到第一列在最后面加first；

如果需要加到某一列后面，在最后加个alter 列名；

(2) 删除表中某一列：

Alter table 表名 drop 列名；

(3) 修改列的类型和名称（change力度更大，改名必须change）

① 修改列的类型modify

Alter table 表名 modify 列名 数据格式 约束条件；

② 修改列名change

Alter table 表名 change 旧列名 新列名 数据格式 约束条件；

第3节 数据表的增、删、改、查

1.INSERT 语句（增）

如果添加值为默认值，values填写default，若为自增长的填写null；

2. DELETE语句（删）

慎用，删除后无法恢复数据，企业中一般使用逻辑删除，即添加字段isdelete，默认值0代表不删，1代表删除，查询时只展示为0的数据；

3. update 语句（改）

4. Select 语句（查）：只是筛选，不改变源数据

（1）As ：给字段起别名，一般在select中用，改了结果的展示；也可以省略；

（2）Select语句

说明：1）<>与!= 效果相同；2）like后也可以跟‘_’，代表一个字符，‘%’是任意长度字符；

5.（了解即可）两个数据表之间的操作

（1）复制表a 到表b

语法：Create table B select * from A；

（2）更新表a的数据，从a与b相交结果中拿出一列复制给a的列，两列的数据类型需要相同；

语法：update A inner join B on 条件 set A列=B列；

（3）给表a添加表b中的数据行（表A必须存在，且列的数据类型相同）

语法：insert into A（列1，列2） select 值1，值2 from B where；

或者直接复制全部数据：insert into A select* from B；

第4节 数据表的排序、聚合命令、分组

1．Order by 排序子句

语句：order by 指定排序的列 asc（升序）/desc（降序）；

注意：1）一般位于select语句结尾；

2）默认以asc排序；

3）可以多条件排序，eg：order by age desc,id asc；（先age倒序排，若age同，id升序排）

2．聚合函数（不能在where中用，可以在having用）

（1）Distinct 对列数据去重

（2）Count 统计总行数

（3）最大值max，最小值min：返回满足条件的最大值、最小值

（4）Avg 平均值

（5）Sum 求和函数

3．limit 分页子句

语法：select * from 表名 limit m，n；

M代表从哪行开始，0表示第一行，默认从0开始取值；

n代表一共取多少条数据；

eg：limit 0,2；从第一行开始，共取2行；

注意：1）n=-1，代表取值到最后一行；

2）m=0时可省略，即limit 5=limit 0,5；

4．group by 分组子句（可与having同用）

语法：

select 列名，聚合函数 
from 表名 
【where 筛选条件】
group by 列名 having 聚合函数 过滤条件
【order by 子句 limit 显示个数】；

注意：1）使用group by 只能展示分组后的列名和聚合函数结果，其余列已经被组合并了。

3）where和having的区别：where对from后指定表进行数据筛选，属于操作原始数据；having是对group by的数据筛选，只能与group by一起用。

5．（了解）其他常用函数

（1）Round(n,d) 四舍五入函数，d默认为0；

eg：round（2.5555,2）表示小数位2位，结果为2.56

（2）concat(列名1,’拼接1’,列名2) 拼接字符串函数，跟在select后用；

显示结果为：列名1拼接1列名2

（3）length(‘字符串’) 返回字符串包含字符个数，跟在select后用；

（4）截取字符串：

① Select left(‘abc’,1) from 表名；从左起截取1个字符

② Select substring(‘abc123’,0,3) from 表名；从0位置（初始值0）开始，截取3位（长度）；

（5）去除空格：去左空格ltrim(‘字符串’)，右空格rtrim(‘字符串’)，两端空格rtrim(ltrim(‘字符串’))

（6）大小写转换：lower(‘字符串’)，upper(‘字符串’)

（7）X的y次幂：pow(x,y)

（8）获取圆周率：PI()，默认获取6位；round(PI(),10)获取10位

（9）随机数：rand(),在0-1.0之间的浮点数随机取一个；

常用法eg随机取表中的一条数据：

Select * from 表名 order by rand() limit1;

（10）日期时间函数

① 取电脑的当前日期，时间，日期时间：

SELECT CURRENT_DATE(),CURRENT_TIME(),NOW();

② 修改日期时间的显示格式date_format()，其中中间分隔符随意；

第5节 数据表连接查询和子查询

1. inner join 内连接（可以和where语句一起用）

2. Left join 左连接

3. Right join右连接

4. 笛卡尔积内连接：不对两表数据处理，直接用笛卡尔积得到x*y条数据的虚拟表，然后进行筛选。

语法：select * from 表a，表b where 条件

得到的结果与 inner join 相同，但是inner join效率更高；

5. 自关联：把一个表看成两个表

6. 子查询（嵌套查询）

根据子查询的返回值又可以分为（返回值运算可以用 =、>、<，值前可以加any、some、all）：

（1）标量子查询：返回值是一行一列，也就是只有一个值；

eg：select .. where age > (select avg(age) from 表)

（2）列子查询：返回值是一列，可以有多行；

Eg：select * from 表 where 值 in （列子查询）

（3）行子查询：返回值是一行，可以有多列；

eg：select * from 表 where （值1，值2）=（行子查询）

（4）表子查询：返回值是一个表；

eg：select * from 表 inner join （表子查询） as 重命名 on 条件

第6节 （不常用）一些SQL功能

1. 自定义函数：delimiter $$

2. 存储过程（放在服务器上）：一条或多条SQL语句的集合

（1）创建存储过程

create procedure 存储过程名称（参数列表）

begin

Sql语句；

end

（2）使用存储过程：call 存储过程名称；

3. 视图：隐藏真正的表结构，对于比较重要的数据只让别人访问视图，无法访问真实表；show tables时也会显示视图；

(1) 创建视图：create view 视图名称（v-开头）as select语句；

(2) 使用视图：select * from 视图名称；

(3) 删除视图：drop view 视图名称；（服务端删除）

4. 事务：广泛用于订单，银行交易，中途异常不会执行sql语句；

5. 索引：提高查询效率，使用B-tree树状存储（重点）；

(1) 开启运行时间检测：set profiling=1；（0时关闭）

(2) 查看运行时间：show profiles；

(3) 创建索引，是用在某个字段上的：

Create index 索引名称 on 表名 (字段名称(长度))；

其中字符串需要写明长度，数值不需要；

(4) 查看索引：show index from 表名;

(5) 删除索引：drop index 索引名称 on 表名；

(6) 建表的时候也可以直接创建索引：key(列名)，另外主键和unique的列名也自动建索引；

6.分析查询语句效率：不返回查询结果，只是对语句的分析

语法：explain select语句；

其中key代表是否用索引，row代表查询行数；

Navicat中上方功能“解释”也可以直接分析；

7.外键，foreign key 基本不用，限制了表的查询效率；

8.ifnull(‘值1’,’值2’)，判断是否为空，若空返回值1，非空返回值2

9.（重点）MySQL操作日志general-log

① 客户端连接MySQL

② 查日志是否开启：show variables like ‘general%’；

③ 打开：set global general_log=1;

④ 关闭：set global general_log=0;

⑤ 打开对应目录日志文件，操作MySQL，查看日志变化。

Linux中自动刷新：tail -f 文件名；