MySQL 语言汇总

MySQL，mysql，数据储存，数据提取

MySQL 数据库管理系统 语言汇总

DDL—数据定义语言 Data Definition Language

用来定义数据库对象：库、表、列等

操作数据库：

创建：create database 数据库名称; 删除：drop database 数据库名称; 修改：ALTER DATABASE mydb1 CHARACTER SET 要修改的字符集名称 查询：show databases; – 查询所有数据库的名称 show create database 数据库名称; – 查询创建数据库语句

操作表：

创建

create table 表名(列名1 类型1,列名2 类型2,…列名n 类型n); create table student(sname varchar(20),age int,gender varchar(6));

数据类型

约束类型

创表检测

CREATE TABLE IF NOT EXISTS 表名;

复制表结构

CREATE TABLE name2 LIKE name1;'

删除：drop table 表名;

查询

查询该数据库下所有表名称

show tables;

查询表结构

desc name；

查看创建表的语句

show create table name；

查看约束类型

show index from table

修改：

ALTER TABLE 表名

修改表名

RENAME TO 新表名

添加列

ADD 列名 数据类型 约束类型

修改列名

CHANGE 旧列名 新列名 数据类型 约束类型

删除列

DROP 列名

添加外键

ADD FOREIGN KEY（当前表ID） REFERENCE 关联名（关联表id）

修改列类型及约束

MODIFY 列名 数据类型 约束类型

删除列的约束

MODIFY 列名 数据类型

或

DROP 约束类型 列名

DML—数据库操作语言 Data Manipulation Language

用来操作数据库记录（增、删、改）

主要命令

Insert --向表中插入数据

方法一：insert into 表名(列名1,列名2,…) values(值1,值2,…); insert into 表名(列名1,列名2,…) values(值1,值2,…)，(值1,值2,…)，.... insert into 表名(列名1,列名2,…) select col1，col2，...from name where.....

列名数量和值数量必须一致，没有列名 默认为全部列名。 插入值的类型必须与列名字段的类型一致 非空必须插入数值，空的可以不插入 列名可以打乱，但值必须跟列名排列一致 支持批量插入多行 支持子查询

方法二：insert into 表名 set 列名1=值1，列名2=值2.....);

update --更新表数据

update 表名 set 列名1 = 值1 , 列名2 = 值2… [where 条件] update stu set sex = “1” where sname = “lisi”;

筛选和连接方式和查询中一样

delete–删除表数据

delete from 表名 [where 条件]; truncate table 表名;-- 删除所有记录 先删除表，再创建表。 delete from stu where sname = ‘lisi’

不常用命令

call --调用一个PL/SQL或java子程序

Explain plan --解析分析数据访问路径

批量导入导出数据

LOAD DATA INFILE 'F:\\repo\\python tasks\\projects\\ecommerce_Analysis\data\\tianchi_mobile_recommend_train_user.txt' INTO TABLE `tianchi_mobile_recommend_train_user` FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' IGNORE 1 LINES (`user_id`,`item_id`,`behavior_type`,`user_geohash`,`item_category`,`time`);

批量插入数据

使用mysql 语句脚本

DQL—数据查询语言 Data Query Lanuage

用来查询记录（数据）

SELECT selection_list /要查询的列名称/ FROM table_list /要查询的表名称/ (连接类型) JOIN table2 on 关联id WHERE condition /行条件/ GROUP BY grouping_columns /对结果分组/ HAVING condition /分组后的行条件/ ORDER BY sorting_columns /对结果分组/ LIMIT offset_start, row_count /结果限定/

条件查询：where 子句 条件运算符：=、!=、<>(不等于)、<、<=、>、>=； 逻辑运算符：AND；OR；NOT； 模糊查询符： LIKE BETWEEN…AND； 在什么范围之间 IN(set)； IS NULL；为空 IS NOT　NULL　不为空 (连接类型) INNER、LEFT、RIGHT、 FULL

SELECT <字段名表> FROM <表或视图名>

基础查询

查询所有记录

select * from name;

查询指定列名(字段) 的列记录 采用函数查询 这些函数最好放到 select from之间 部分可以放到where having后面

多列查询：select col1，col2，col3 from name；

去重查询

select distinct col1 from name；

字符函数

字符串字节个数查询

select length(col) from name;

字符串合并查询

字段中null 与任何字段拼接 ，结果都是null

字符型字段：SELECT CONCAT(first_name,' ',last_name) as 姓名 FROM employees;

数值型字段：SELECT a+b as c FROM employees; 自动将字段转数值，成功就运算，失败就转成0再相加

大小写转换查询

大写：select UPPER(col) from name;

小写：select LOWER(col) from name;

字符截取查询

select substr(col,截取序号,截取数量) from name；

切割字符串 留取切割位置前面的字符

SUBSTRING_INDEX(str,‘切割依据’,'切割字符的位置')

子字符位置查询 没有返回值0

select instr(col,'查询字符') from name；

去掉指定的首尾字符a

select trim('a' from col) from name；

左填充指定中长度字符

select LPAD(col,指定长度，'填充字符') from name；

左填充指定中长度字符

select RPAD(col,指定长度，'填充字符') from name；

字符替换

select replace(col,‘需替换字符’，'替换字符') from name；

数学函数

对小数取位数 四舍五入

select round(1.56,566,n); select round（col，n）from name;

向上取整

select ceil(1.56);

向下取整

select floor(1.56);

截断n位小数

select truncate(1.56,n);

取余数

select mod(m,n);

取绝对值

select abs(number);

求参数的n次方

select pow(number，方次);

求参数的平方根

select sqrt(number);

求随机数

select rand（）；

统计函数

查看列的非空行数

select count(col) from name;

查看不重复的行数

select COUNT(DISTINCT，col) from name;

查看记录行数

select count(*) from name;

查询最大最小数

select max(col) from name； select min(col) from name；

查列平均数

select avg(col) from name；

查列和

select sum(col) from name；

查标准差

select std(col) from name；

日期函数

返回当前日期、时间

SELECT now(); SELECT curdate(); SELECT curtime();

选择年月日时分秒

select year(col) from name； select month(col) from name； select monthname(col) from name； select day(col) from name； select hour(col) from name； select minute(col) from name； select second(col) from name；

转换成日期格式

select str_to_date('2012-2-3','%Y-%m-%d');

日期转换成字符

select date_format('2012-2-3','%Y年%m月%d日');

日期相差的天数

select datediff(日期1，日期2)；

时间的相差数

select timediff(时间1，时间2)；

替换查询

查询 null 并将null替换成n

SELECT IFNULL(commission_pct,n) FROM employees;

null替换1,非null替换0查询

SELECT ISNULL(commission_pct) FROM employees;

if 判断

select if(判断语句，'是返回的值'，'否返回的值')；

case 多重判断

select case when 判断语句1 then '符合赋值' when 判断语句2 then '符合赋值2' when 判断语句3 then '符合赋值3'.....else 赋值n end as 名称 from name；

非表格查询

查询常量值

select 100； or select 'wow';

查询表达式

select 10*60;

起别名

select 150+156 as reslut

select 150+156 reslut

查询库内容的方法

select version() select database() select user() select password('str') 查询字符串的密码形式 select md5('str') 查询字符串的密码形式

条件查询

WHERE 条件 只能在 group by分组前使用

查询指定列名相关 条件的行记录

GROUP BY 条件

聚类查询

select * from name group by col ；

以 字段1聚类来 查询字段2的平均值

select col1 avg(col2) avg from name group by col1 ；

HAVING 条件 ， 在聚类分组后使用

select col1 avg(col2) avg from name group by col1 having avg >200 ；

ORDER BY 条件

指定列排序

升序排列

select * from name order by col asc；

降序排序

select * from name order by col desc；

多层排序

先升序再降序

select * from name order by col asc,col desc；

先降序再升序

select * from name order by col desc,col asc；

可以叠加多层

select * from name order by col1 desc,col2 asc,col3 desc；

LIMIT 条件

查询前n条记录

select * from name limit n；

limit m,n 表示查询从index=m开始的n条记录

select * from tablename limit n,m;

查看第n~m之间的记录

select * from tablename limit n,m-n;

查看倒数第n个记录

select * from tablename order by id desc limit n;

查看倒数从n开始的m个记录 不包括n

select * from tablename order by id desc limit n,m;

高级查询

合并表格查询

select * from name1，name2；

结果为笛卡尔积，行数是两表行数的乘积

交叉连接

select n1.*,n2.* from name1 n1 cross join name2 n2;

效果和上面一样

关联查询-内连接

必须是关联表，且与表顺序无关，关联后取并集

等值连接

select n1.col1,n2.col2 from name1 n1,name2 n2 where n1.id = n2.id；

inner join on 与上面相同

select * from name1 inner join name2 on name1.id = name2.id；

多表连接

select n1.col1,n2.col2 from name1 n1,name2 n2,name3 n3 where n1.id = n2.id and n1.id = n3.id；

select * from name1 inner join name2 on name1.id = name2.id inner join name3 on name1.id = name3.id；

inner join using() 除了关联外，关联字段必须同名 生成表会去重相同列

select * from name1 inner join name2 using(id)；

非等值连接

select n1.col1,n2.col2 from name1 n1,name2 n2 where 非等值条件；

自连接

给表重命名，结果和内连接一样，取并集

select * from name n1,name n2 where n1.id=n2=id;

用内连接语法

给表重命名，结果和外连接一样，从表多删少补

select * from name n1 left join name n2 on n1.id=n2.id;

关联查询-外连接

两表可以不是关联表，其中左表为主表数据全部展示，右表为从表，匹配后多删少补，用null补充，如果不要null，加where 表.key is null

select * from 主表 left join 从表 on 主表.id = 从表.id；

select * from 从表 right join 主表 on 主表.id = 从表.id；

全外连接

select * from 表1 full join 表2 on 表1.id = 表2.id；

子查询 多层查询

子查询语言放在主查询语句的 select、from、where、having、exists 后面

where、having 子查询 将子查询作为条件依据

条件运算符（产生单个数据的子查询 ）

where col [=、!=、<>(不等于)、<、<=、>、>=] (子查询)

多行操作符（产生列数据的子查询）

having col [in ,not in，all, any,some] (子查询)

select 子查询

select (子查询-只能是单个数据) from name......

from 子查询

from （子查询-是多行多列的表格）

exists 子查询 相关子查询

select exists （子查询语句-任意结果均可）

子查询有结果返回值为1 无结果，返回值为0

联合查询

查询语句1 union 查询语句2 union 查询语句3 union... 列数必须相同，并按行拼接，并自动去重 使用union all 不去重

select * from name where col like '%a%' union select * from name where col > 0;

DCL—数据控制语言 Data control language

用来定义访问权限和安全级别

Grant —为用户授予权限

revoke–撤回授权权限

事务命令

查看事务支持的引擎

SHOW ENGINES;

一个或多个sql语句组成一个执行单元，执行后要么都成功，要么都失败

事务的特性ACID

原子性（Atomicity）：一个事务是一个执行单元，不可分割，执行只有成功或失败

一致性（Consistency）使数据一个一致状态改成另一个一致状态

隔离性（Isolation） 不受其他事务影响

持久性（Durability）执行成功，就会永久改变数据

隐式事务

没有明显的开启和结束标志，通常对单个语句有效

默认命令有 insert、update、delete

显式事务

多个语句组合，有开启和结束语句

必须设置自动提交为off

查看：show variables like 'autocommit'

设置： set autocommit = 0

事务执行过程

1、开启事务

set autocommit = 0 start transaction（可选）

2、执行语句

select、update、delete、insert 等多个语句

3、事务结束

rollback —回滚事务

搭配savepoint使用

没有设置保存点，语句全部回滚撤销执行 设置保存点，只回滚到保存点后的语句

Commit --提交事务 只有提交了才能全部执行完

多个事务并行的问题

设置隔离性

查看事务隔离类型：select @@transaction_isolation;

更改当前事务级别 set global.... 设置为全局事务级别

读取其他事务已提交的数据

set session transaction isolation level read committed;

读取其他事务未提交的数据

set session transaction isolation level read uncommitted;

可重复读取其他事务提交前的数据

set session transaction isolation level repeatable read;

阻止其他事务操作只能自己操作数据， 且等自己提交后，其他事务才能操作

set session transaction isolation level serializable

mysql高阶 封装语句脚本

封装语句格式

DELIMITER $ create 模式 name（函数模式，参数名，参数类型） begin sql语句； end$

delimiter 符号

定于确认符号，可以随意修改

创建

创建视图VIEW

DELIMITER $ create view name begin sql语句； end$

创建储存过程PROCEDURE

DELIMITER $ create procedure name（IN/OUT/INOUT，参数名，参数类型） begin sql语句； end$

函数模式： in、out、inout

创建函数FUNCTION

DELIMITER $ create function 函数名（参数名，参数类型） returns 参数类型 begin sql定义语句； sql计算语句； return 函数名 end$

调用

call name(参数，@命名)

select @命名

修改

DELIMITER $ atler(view/procedure/function) name() as 修改语句

查询

desc name，show create (view/procedure/function) name

删除

drop (view/procedure/function) n1,n2,n3...

可以同时删除多个

DNMS-数据库管理系统 Database Management System

RDBMS-关系型数据库管理系统

二元关系模型-表格格式

Oracle-oracle公司

Mysql-oracle公司

关系型数据库

开源免费 GPL许可证

千万数据量级别

可创建管理多个数据库

创建管理多张表

sql server-Microsof公司

db2-IBM公司

Nosql-非关系型数据库管理系统

弥补RDBMS的不足

数据库介绍

数据库系统（全称是：数据库管理系统 DBMS）是计算机上的一个软件系统，数据库系统控制着 数据的存储和操作。

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库

根据数据组织的方式

非关系型 数据库系统

子主题

关系型 数据库系统（Relational DBMS）

关系型数据库中的数据都是用 包含行和列的一张张表，来存储数据信息的

包括常见的 MySQL，Oracle，SQLServer

SQL

全称：结构化查询语言 Structured Query language

通过SQL语言，在数据库中进行读取数据、添加数据、删除数据、修改数据 等操作

HIVE

SQL的扩大版，大型数据仓库

安装及使用mysql

在Linux安装

在windows 中安装

在视窗软件中使用

Navicat

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root1';

SQLlog

HeidiSQL

在python中使用

应用程序要访问数据库，其实是访问数据服务进程，这个数据服务进程再去读写磁盘上的数据文件

使用Python语言访问mysql数据库， 推荐使用客户端库 mysqlclient 和 PyMySQL

pip install mysqlclient

import MySQLdb

pip install PyMySQL

import pymysql

import MySQLdb

import pymysql

示例

show profile 检查执行语句运行结果

查看执行语句：show profiles;

开启查询功能：set profiling=on;

查看profile状态：show variabes like 'profiling';

诊断sql执行过程

show profile cpu,memery,bolok io for query n;

mysql 索引功能

索引就是一种数据结果，类似于字典，排序后快速查找数据，提高查询效率

是一种满足特定查找算法的数据结构，其本身也是一个很大的数据，储存在磁盘中

索引分类

单值索引

一个索引包含单个列，一张表可以有多个单列索引

唯一索引

索引列的值唯一，可以是空值

复合索引

一个索引包含多个列

索引语法

创建索引

CREATE [unique] INDEX 索引名 ON 表名(索引列名，...);

修改索引

ALTER 表名 ADD [unique] INDEX 索引名 ON (索引列名，...);

删除索引

DROP INDEX 索引名 ON 表名

查看索引

SHOW INDEX FROM 表名\G；

索引结构

常用结构：BTree索引

哪些情况需要建索引

哪些情况不要建索引

其他索引：Hash索引、full_text索引、R-tree 索引

检查mysql 语句执行性能

explain+mysql 语句