在线mysql语句转达梦 mysql语句翻译

Mysql

SQL（Structured Query Language）就是是“结构化查询语言”，它是对关系型数据库的操作语言。Mysql也是关系型数据库，带点方言。主要分四类DDL、DML、DCL、DQL。用的最多的是 DQL查询，顺便推荐一个建模软件（就是快速设计数据库表的免费软件）PDmaner,文章写的不是太好，这就是自己之前学习过程中用Typora 记的一些笔记。内容有点杂乱。也不记得借鉴了那些

DCL

（Data Control Language，数据控制语言）用于对数据库，数据表的访问角色和权限的控制等。

grant    - 授权 
revoke   - 撤销授权 
deny     - 拒绝授权

DDL 数据定义

（Data Definition Language，数据定义语言）

create  - 创建
alter   - 修改
drop    - 删除
truncate  - 截断
comment   - 注释
rename    - 重命名

DQL 查询

（也是所有SQL）用得最多的动词。常用的关键字有：

select       -从数据库表中获取数据 
from         - 指定从哪个数据表或者子查询中查询
where        - 指定查询条件
group by     - 结合合计函数，根据一个或多个列对结果集进行分组
having       - 对分组后的结果集进行筛选
order by     - 对结果集进行排序
limit        - 对结果集进行top限制输出
union        - 结果集纵向联合
join         - 结果集横向拼接

DML 增删改

（Data Manipulation Language，数据操作语言）供用户对数据库中数据的操作，包括数据的增加、删除、更新，载入等操作。

update   - 更新数据库表中的数据 
delete   - 从数据库表中删除数据 
insert into    - 向数据库表中插入数据
replace into   - 向数据库表中插入数据，如果存在先删除
load     - 载入数据

TCL

（Transaction Control Language，事务控制语言）又名TPL（Transaction Process Language）事务处理语言，它能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括：

start transaction 或 begin   - 开始事务
savepoint    - 在事务中设置保存点，可以回滚到此处
rollback    - 回滚 
commit      - 提交
set transaction   – 改变事务选项

CCL

（Cursor Control Language，游标控制语言）游标（cursor）是DBMS为用户开设的一个数据缓冲区，存放SQL语句的执行结果。游标控制语言对游标的操作主要有：

declare cursor   - 申明游标
open cursor      - 打开游标
fetch into       - 取值
update where current   - 更新游标所在的值
close cursor     - 关闭游标

连接 / 退出 / 查看 MySQL

首先打开shell命令终端或者命令行程序，键入命令mysql -u root -p，回车后提示输入密码。注意用户名和密码与命令选项之间的空格可有可无。

# 连接
mysql -u[username] -p[password] -A # 中括号中的变量需要替换指定值
mysql -h110.110.110.110 -uroot -p123;
# 退出
exit;
# 或
quit;
# 查看 MySQL 版本
select version();
# 或者
status;

DDL （数据定义）

• 创建数据库

create database [databasename];
# 事例
create database Student;

• 删除数据库

# 命令格式
drop database [databasename];
# 示例
drop database Student;

• 查看所有数据库

show databases;

• 查看当前数据库

select database();
# 或者
status;

• 连接数据库

# 命令格式
use [databasename]

# 示例
use Student;

创建数据表

create table [表名] ( [字段名1] [类型1] [is null] [key] [default value] [extra] [comment],
...
)[engine] [charset];

• 注意是 ( ) 而非 { } 。

create table if not exists student(
    studentNo int unsigned not null comment '学号' auto_increment,
    name varchar(12) not null comment '姓名',
    age int  not null default '0' comment '年龄',
    gender char check(gender in('男','女'))  comment '性别',
    school varchar(12) not null comment '学院',
    grade varchar(12) not null comment '年级',
    major varchar(12) not null comment '专业',
    hobby varchar(128) null comment '爱好',
    primary key(studentNo)
)engine=MyISAM default charset=utf8 auto_increment=20160001;

说明： 上面的建表语句命令格式，除了表名，字段名和字段类型，其它都是可选参数，可有可无，根据实际情况来定。is null表示该字段是否允许为空，不指明，默认允许为NULL；key表示该字段是否是主键，外键，唯一键还是索引；default value表示该字段在未显示赋值时的默认值；extra表示其它的一些修饰，比如自增auto_increment；comment表示对该字段的说明注释；engine表示数据库存储引擎，MySQL支持的常用引擎有ISAM、MyISAM、Memory、InnoDB和BDB(BerkeleyDB)，不显示指明默认使用MyISAM；charset表示数据表数据存储编码格式，默认为latin1。

存储引擎是什么？ 其实就是如何实现存储数据，如何为存储的数据建立索引以及如何更新，查询数据等技术实现的方法。

主键（Primary Key）与唯一键（Unique Key）的区别：
（1）主键的一个或多个列必须为NOT NULL，而唯一键可以为NULL；
（2）一个表只能有一个主键，但可以有多个唯一键。

• 查看 MySQL 支持的存储引擎和默认的存储引擎

# 查看所支持的存储引擎
show engines;

# 查看默认的存储引擎
show  variables  like '%storage_engine';

• 删除数据表

DROP TABLE [IF EXISTS] table_name1 [ ,table_name2, table_name3 ...]
# 示例：
# 删除单个数据表。
drop table [tablename];

# 数据表存在时才删除，不会产生 Warning。
drop table if exists [tablename];

# 同时删除多个数据表。
drop table if exists [tablename0,tablename1,...];

• 查看当前数据库有哪些数据表

show tables; 	# 不能使用limit子句

# 模糊查找
show tables like "%<tb_name>%"

# 指定数据库查看数据表
show tables from [databaseName]

# 或者
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'DATABASE_TO SEARCH_HERE' AND TABLE_NAME LIKE "table_here%"  LIMIT 5;

• 查看数据表结构

desc [tablename];

# 或
describe [tablename];

• 查看建表语句

show create table [tablename]

• 重命名数据表

rename table [tablename] to [newtablename];

• 增加、删除和修改字段自增长

-- 删除字段自增长
# 命令格式
alter table [tablename] change [columnname] [columnname] [type];

# 示例，取消studentNo的自增长
alter table student change studentNo studentNo int(10) unsigned;

-- 增加字段自增长
# 命令格式
alter table [tablename] modify [columnname] [type] auto_increment;

# 或者与上面删除字段自增长相反
alter table [tablename] change [columnname] [columnname] [type] auto_increment;

# 示例，添加studentNo自增长
alter table student modify studentNo int(10) unsigned auto_increment;
-- 说明：添加自增长的列必须为NOT NULL及PRIMARY KEY（UNIQUE）属性。如果不是，需添加相应定义。

-- 修改自增长起始值
# 命令格式
alter table [tablename] auto_increment=[value];

增加、删除和修改数据表的列

-- 增加列
alter table [tablename] add column [columnname] [columdefinition] [after columnname];
-- 重命名列
alter table [tablename] change column [columnname] [newcolumnname] [type];
-- 修改列属性
alter table [tablename] modify column [columnname] [newdefinition];
-- 删除列
alter table [tablename] drop column [columnname], drop column [columnname], ...;

# 示例 column 均可省略，可拼接操作
alter table 表名 add column aaa int comment '示例' after name,add bb int after aaa;
alter table 表名 change column aaa aaaa int , drop bb;
alter table 表名 modify column aaaa  varchar(64) Unique , add bb int;
alter table 表名 drop column aaaa,drop bb,add aaa int;

• 添加、删除和查看索引

-- 添加索引
# 命令格式
alter table [tablename] add index [indexname](字段名1,字段名2…);

# 示例，为数据表student数据列studentNo添加索引
alter table student add index index_studentNo(studentNo);
# 或者
alter table student add index(studentNo);
-- 说明： 上面示例的第二种方法，如果不显示指明索引名称的话，默认以列名称作为索引的名称。添加索引是为了提高
-- 查询的速度。

-- 查看索引
show index from [tablename];

-- 删除索引
# 命令格式
alter table [tablename] drop index [indexname];

# 示例
alter table student drop index index_studentNo;

• 创建临时表

# 命令格式
create temporary table [表名] ( [字段名1] [类型1] [is null] [key] [default value] [extra] [comment],...);

# 示例
create temporary table pig(i int);

• 创建内存表

# 命令格式
create table [表名] ( [字段名1] [类型1] [is null] [key] [default value] [extra] [comment],...)engine=memory;

# 示例
create table pig(i int)engine=memory;

• 修改数据表的存储引擎

alter table [tablename] type|engine=[enginename];

# 示例，将数据表test存储引擎设置为InnoDB
alter table test type=InnoDB;
# 或者
alter table test engine=InnoDB;

• 查看数据库数据表存储位置

show global variables like "%datadir%";

• 创建 merge 表

create table log_YY  
(  
	dt  DATETIME NOT NULL,  
	info VARCHAR(100) NOT NULL,  
	INDEX (dt) 
) engine = MyISAM;

• 清空表内容

truncate [tablename];

DQL（数据查询）

select (distinct) 查询字段                     # distinct 去重操作
from   A表(，、(( cross /  inner / left 、 right + join  ))B表...
(on 连接条件)                             # 连接条件(and/or)
where 各种各样的过滤条件                  # 过滤条件(and/or)
group by 分组列名，..                     # 按列名分组
(having 分组里的筛选条件)                 # 筛选分组后数据(and/or)
(with  rollup 分组统计（与排序矛盾）)     # 统计分组情况
order by ...asc/desc                      # 排序
(limit star,n / LIMIT n OFFSET star);     # 分页

1. select 子句是必选的，其它子句如 from、where、order by 子句等是可选的。
2. 一个 select语句中，子句的顺序是固定的。如 order by 子句不会位于 where子句前面。
3. select语句执行顺序

开始->FROM子句->WHERE子句->GROUP BY子句->HAVING子句->ORDER BY子句->SELECT子句->LIMIT子句->结果

• 检查查询语句的执行效率

explain [select statement];

# 或者
desc [select statement];

• 查看SQL执行时的警告

show warnings;

union的用法

将两次或多次查询结果纵向联合起来 ， + all 则不去重。union只能作用于select结果集，不能直接作用于数据表，join则恰恰相反，只能作用于数据表，不能直接作用于select结果集（可以将select结果集指定别名作为派生表）。

查看数据表行数

-- 主要有两种方法。
-- 第一种：
select count(*) from tableName;
/*
对于 MyISAM 数据表很快，建议使用，因为 MyISAM 数据表事先将行数缓存起来，可直接获取。InnoDB 数据表不建议使用，当数据表行数过大时，因需要扫描全表，查询较慢。*/

-- 第二种：
select table_name,table_rows from information_schema.tables where TABLE_SCHEMA = 'DatabaseName' and table_name='TableName';  

-- 第三种：
show table status like 'tableName';

-- 第四种：
explain select count(*) from 'tableName';
/*
注意：由于 InnoDB 数据表经常更新，未事先存储表行数，所以方法二、三、四对 InnoDB 数据表所获得的是一个估算的不精确结果。*/

DML（数据操作）

插入记录

insert into tablename(column1,column2,...) values(value1,value2,...);

insert into tablename(,,,) select...(子查询);

insert into tablename set column1=value1,column2=value2...;

# 示例
# 插入一行
insert into student(name,school,grade,major,gender) values('lvlv0','software','first year','software engineering',0);

# 如果插入值刚好与数据表的所有列一一对应，那么可以省略书写插入的指定列
insert into student values(10000,'lvlv0','software','first year','software engineering',0);

# 插入多行
insert into student values('lvlv0','software','first year','software engineering',0),('lvlv1','software','first year','software engineering',0);

# 使用select结果集进行插入
insert into tablename select * from temp;

# 注意，temp数据表的定义要与tablename相同，不同的话，则需要指定需要插入的列，示例如下：
insert into tablename(col0,col1,col2) select col0,col1,col2 from temp;

# 使用insert into set
insert into student  set  name='lvlv0', school='software', grade='first year',major='software engineering',gender=0

• replace into进行插入(已有先删除后插入, 否则直接插入)

replace into tbl_name(col_name, ...) values(...)

replace into tbl_name(col_name, ...) select ...

replace into tbl_name set col_name=value, ...

删除记录

# 命令格式
delete from tablename (where ...);# 不加where是清空表，几乎等价于 truncate table 
( 不自动提交 SET AUTOCOMMIT = FALSE; )
# 示例，删除学号为10000的学生记录
delete from student where from studentNo=1000;

修改记录

update 表名 set col=val,col=val...where ...);# 不加where 修改所有

# 将学号为 10000 的学生性别改为女性。
UPDATE student SET gender=1 WHERE studentNo=1000;

备份还原数据

-- 导出数据库的所有数据表。
# 命令格式
mysqldump -u 用户名 -p 数据库名 > 导出的文件名

# 示例
mysqldump -u user_name -p123456 database_name > outfile_name.sql

-- 还原整个数据库。
source file.sql;

-- 导出一个 表 到 sql 文件。  
# 命令格式
mysqldump -u 用户名 -p 数据库名 表名>导出的文件名

# 示例
mysqldump -u user_name -p 123456 database_name table_name > outfile_name.sql

-- 将数据表导出到csv文件。
# 命令格式
SELECT * FROM [TABLE] INTO OUTFILE '[FILE]';
# 或者  
SELECT * FROM [TABLE] INTO OUTFILE '[FILE]' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';  

# 示例
select * from student into outfile "student.csv";

-- 导入 csv 文件。
# 命令格式
LOAD DATA INFILE '[FILE]' INTO TABLE [TABLE];  
# 或者  
LOAD DATA INFILE '[FILE]' INTO TABLE [TABLE] FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

# 示例
load data infile '/root/dablelv/student.csv' into table student;

MySQL 常用函数

日期和时间函数

-- now() 返回当前日期时间。
select now();# 2023-03-01 15:05:12 

-- curdate() 返回当前日期。
select curdate();# 2023-03-01 

-- curtime() 返回当前时间。
select curtime();# 15:10:36

-- Unix 时间戳与日期之间的转换。
select FROM_UNIXTIME(1156219870); 

Select UNIX_TIMESTAMP(’2006-11-04 12:23:00′);

• 时间选取函数。

set @dt = '2019-04-16 15:05:12.123456';

date(@dt)： 返回日期，示例结果2019-04-16
time(@dt)： 返回时间，示例结果15:05:12.123456
year(@dt)： 返回年 ，示例结果2019
month(@dt)： 返回月，示例结果4
day(@dt)：   返回日，示例结果16
hour(@dt)：  返回时，示例结果15
minute(@dt)：返回分，示例结果05
second(@dt)：返回秒，示例结果12
microsecond(@dt)： 返回微秒，示例结果123456
quarter(@dt)：     返回季度，范围1到4。示例结果2
week(@dt)：        返回周，范围0到52。示例结果15
dayofyear(@dt) ： 返回@dt在一年中的日数，范围在1到366。示例结果106
dayofmonth(@dt) ：返回@dt的月份中日期，在1到31范围内。示例结果16
monthname(@dt) ： 返回@dt的月份名字。示例结果April
dayname(@dt) ：   返回@dt的星期名字。示例结果Tuesday

字符串函数

substr(str,pos)                截取 str 从 pos 开始的字符串
substr(str from pos)           截取 str 从 pos 开始的字符串
substr(str,pos,len)            截取 str 从 pos 开始长 len 的字符串
substr(str from pos for len)   截取 str 从 pos 开始长 len 的字符串
-- pos 可为负数

substring_index(str,delim,count) # 按分隔符截取字符串前 N 个或者后 N 个子串

select substring_index('www.mysql.com', '.', 2);# 'www.mysql' / -2 'mysql.com'

# 拼接字符串
concat() # 函数的任意参数为 NULL，则返回结果为 NULL。
concat_ws(',',...) #  以‘,’为连接符， 可取别名

数学函数

conv(N,from_base,to_base)# 进制间的转化

SELECT CONV(2,10,2); -> '0010'

聚合函数

count()     统计指定条件的行数
avg()       求平均数  avg
sum()       求和      sum
max()       取最大值  max
min()       取最小值  min

group by     分组，与 order by 矛盾，其后可加 having(分组条件)'或' with rollup（分组统计）

# 单行比较操作符一般为:
'<' ， '<=' ， '=' ， '<=>' (安全等于)， '!=' ， '<>'(不等于) ， '>' ， '>=' 。
# 多行比较操作符:
in(a,b,c,d...)     等于列表中的任意一个
any/some(  )       需要和单行比较操作符一起使用，和子查询返回的'某一个值'比较
all(    )          需要和单行比较操作符一起使用，和子查询返回的'所有值'比较

其它

-- 1.判断返回
if( 条件 ,"对","错") 
ifnull("1", "1为空") 
case when 条件 then 结果 when 条件 then 结果 .. else 结果 end   'if...else if...else...'
case 值 when 值 then 结果 when 值 then 结果 .. else 结果 end    'switch...case...'
'逻辑符'
非    not / ！
与    and / &&
或    or  /  ||
异或  xor

-- 2.字符串
concat(...""+值+""...)                 'concat' MySQL的拼接字符串语句
trim(s)                                'trim'  去 s 两端空格，只去左右，前面加L/r,
space(n)                               'space' 拼字符串时返还 n 个空格
like('_/%'s'_/%')                      'like'  模糊匹配 '_'一个任意字符 '%'很多任意字符
upper(s)                               'upper'  转大写
lower(s)                               'lower'  转小写
replace(str, a, b) 									   'b替换a', (str,' ','')可以消除空格
char_length(str)                        字符串长度

--  3.正则表达式  REGEXP
 regexp  正则表达式的句式 后加正则表达式
'^'开头,'$'结尾, '.' 任意单字符
'[...]'括号内的任何字符。可加'-'表约束范围。'[a-z]'任何字母，而'[0-9]'任何数字。
'x*'零个或多个在它前面的字符。有点无意义，


--  4.扩展
' 位运算符 '
/*
-- 位运算符是在二进制数上进行计算的运算符。位运算符会先将操作数变成二进制数，然后进行位运算，
-- 最后将计算结果从二进制变回十进制数。
&  按位与
|  按位或
^  按位异或
~  按位取反
>>  按位右移
<<  按位左移
*/


行级锁， select 的查询后面加上 for update 锁死 查询的行 '--- 悲观锁' 排队执行
              '乐观锁'  支持并发，有版本号概念，只有第一个改的可以成功。

部分函数

ABS(x)  			返回x的"绝对值"
SIGN(X) 			返回X的"符号"。正数返回1，负数返回-1，0返回0
PI()    		    返回"圆周率"的值
CEIL(x)，CEILING(x) 		返回大于或等于某个值的"最小整数"
FLOOR(x) 			返回小于或等于某个值的"最大整数"
LEAST(e1,e2,e3…) 		返回列表中的"最小值"
GREATEST(e1,e2,e3…) 	返回列表中的"最大值"
MOD(x,y) 			返回X除以Y后的"余数"
RAND() 				返回0~1的"随机值"
RAND(x)  			返回0~1的随机值，其中x的值用作种子值，"相同的X值会产生相同的随机数"
ROUND(x) 			返回一个对x的值进行"四舍五入"后，最接近于X的"整数"
ROUND(x,y) 			返回一个对x的值进行"四舍五入"后最接近X的值，并"保留到小数点后面Y位"
TRUNCATE(x,y) 		返回数字x"截断为y位小数"的结果
SQRT(x) 		    返回x的"平方根"。当X的值为负数时，返回NULL
POW(x,y)，POWER(X,Y)      返回"x的y次方"
EXP(X)              返回"e的X次方"，其中e是一个常数，2.718281828459045
LN(X)，LOG(X)       返回"以e为底的X的对数"，当X <= 0 时，返回的结果为NULL
LOG10(X)            返回"以10为底的X的对数"，当X <= 0 时，返回的结果为NULL
LOG2(X)             返回"以2为底的X的对数"，当X <= 0 时，返回NULL

三角函数

#   角度 弧度 的转化操作
RADIANS(x)             将角度转化为"弧度"，其中，参数x为角度值
DEGREES(x)             将弧度转化为"角度"，其中，参数x为弧度值
# 
SIN(x)          返回x的"正弦值"，其中，参数x为弧度值
ASIN(x)         返回x的"反正弦值"，即获取正弦为x的值。如果x的值不在-1到1之间，则返回NULL
COS(x) 			返回x的"余弦值"，其中，参数x为弧度值
ACOS(x) 		返回x的"反余弦值"，即获取余弦为x的值。如果x的值不在-1到1之间，则返回NULL
TAN(x) 			返回x的"正切值"，其中，参数x为弧度值
ATAN(x) 		返回x的"反正切值"，即返回正切值为x的值
ATAN2(m,n) 		返回两个参数的"反正切值"
COT(x) 			返回x的"余切值"，其中，X为弧度值

进制转换

BIN(x) 				返回x的"二进制编码"
HEX(x) 				返回x的"十六进制编码"
OCT(x) 				返回x的"八进制编码"
CONV(x,f1,f2) 		返回"f1进制数 变成 f2进制数"

字符串

ASCII(S) 				返回字符串S中的'第一个字符的ASCII码值'
CHAR_LENGTH(s) 			返回字符串's的字符数'。作用与CHARACTER_LENGTH(s)相同
LENGTH(s) 				返回字符串's的字节数'，和字符集有关
CONCAT(s1,s2,......,sn) 		  '连接s1,s2,......,sn'为一个字符串
CONCAT_WS(x,s1,s2,......,sn)      '同CONCAT'(s1,s2,...)函数，但是'每个字符串之间要加上x'
INSERT(str, idx, len,rep)        将str'从'第idx处，len个字符'长'的子串'替换'为字符串rep
replace(str, a, b) 				用字符串'b替换'字符串'str中所有'出现的字符串'a'

UPPER(s)或 UCASE(s)             将字符串s的所有字母'转成大写'字母
LOWER(s)或 LCASE(s)             将字符串s的所有字母'转成小写'字母
LEFT(str,n) 					返回字符串str最'左边'的'n个字符'
RIGHT(str,n) 					返回字符串str最'右边'的'n个字符'
LPAD(str, len, pad) 			用字符串'pad'对'str最左边'进行'填充'，'直到'str的长度为len个字符
RPAD(str ,len, pad) 			用字符串'pad'对'str最右边'进行填充，'直到'str的长度为len个字符
LTRIM(s)			'去掉'字符串's左侧'的'空格'
RTRIM(s)			'去掉'字符串's右侧'的'空格'
TRIM(s) 			'去掉'字符串's开始与结尾'的空格
TRIM(s1 FROM s) 	'去'掉字符串's开始与结尾'的's1'
TRIM(LEADING s1 FROM s)      '去'掉字符串's开始处'的's1'
TRIM(TRAILING s1 FROM s)     '去'掉字符串's结尾处'的's1'
REPEAT(str, n)             返回'str重复n次'的结果
SPACE(n)                   返回'n个空格'
STRCMP(s1,s2)              '比较'字符串's1,s2的ASCII码值'的大小
SUBSTR(s,index,len)     返回'从'字符串's的index位置'其'len个字符'，作用与
                           SUBSTRING(s,n,len)、MID(s,n,len)相同
LOCATE(substr,str)      返回字符串'substr'在字符串'str'中'首次出现的位置'，作用于
                           POSITION(substrIN str)、INSTR(str,substr)相同。未找到，返回0
ELT(m,s1,s2,…,sn)       返回'指定位置'的'字符串'，如果m=1，则返回s1，如果m=2，则返回s2，
                             如果m=n，则返回sn
FIELD(s,s1,s2,…,sn)     返回字符串's'在字符串'列表'中'第一次'出现的位
FIND_IN_SET(s1,s2)      返回字符串s1在字符串s2中出现的位置。'其中，字符串s2是一个以逗号分隔的字符串'
REVERSE(s)              返回s'反转'后的字符串
NULLIF(value1,value2)   '比较'两个字符串，如果value1与value2相等，则返回NULL，否则返回value1

其他函数

FORMAT(value,n)                   返回对'数字'value进行'格式化'后的结果数据。n表示 
                                    '四舍五入' 后保留到'小数点后n位',负数相当于0
CONV(value,from,to)               将value的值进行'不同进制之间的转换'
INET_ATON(ipvalue)                将以点分隔的'IP地址转化为'一个数字
INET_NTOA(value)                  将'数字'形式的IP地址转化为'以点分隔'的IP地址
benchmark(n,expr)                 将表达式expr重复'执行n次'。用于'测试'MySQL
                                    处理expr表达式所'耗费的时间'
CONVERT(value USING char_code)    将value所使用的'字符编码修改'为char_code