手动不易，转发请注明出处 --Trance
数据库系统命令:
(1).查看存储过程状态:show procedure status;
(2).显示系统变量:show variables (最好使用模糊查询)
(3).查询当前系统正在执行的进程(相当于DDL(数据库定义语言) 或 DML(数据库操作语言) 或 DCL(数据库控制语言)):show processlist
(4).显示操作对于的操作内容权限:show privileges
(5).显示数据库支持引擎:show engines
(6).显示最后一个执行的语句所产生的错误、警告和通知:show warnings
(7).显示最后一个执行语句的错误:show errors
(8).数据库帮助命令:在登录情况下help
(9).数据库退出指令:exit 或 quit
(10).数据库操作命令大全:在未登录情况下 mysql - help
(11).set names = 编码 进入数据库后马上设置 防止乱码
操作术语:
DDL:
Data Definition Language 数据定义语言
DML:
Data Manipulation Language 数据操纵语言
DQL:
Data Query Language 数据查询语言
DCL:
Data Control Language 数据控制语言
TCL:
Transaction Control Language 事务控制语言
数据库设计原则(三大范式 3NF):
第一范式(1NF):
原子性:数据不可再分:一个表中的数据(字段值)不可再分 (注意:从应用需求来决定,没有绝对性)
例子(错误):
id name
1 张三,重庆
例子(正确):
id name address
1 张三 重庆
第二范式(2NF):
唯一性:保证行数据的唯一性
例子(错误):
id name
1 张三
1 李四
例子(正确):
id name
1 张三
2 李四
第三范式(3NF):
独立性:消除传递依赖:禁止非主键字段依赖非主键依赖,在发生数据冗余,难以维护修改 (注意:发生这种情况一定是多主键表)
例子(错误):
id name classno classname
1 张三 1 一班
1 李四 2 二班
例子(正确):
id name classno
1 张三 1
1 李四 2
classno classname
1 一班
2 二班
数据库连接方式:
连接本地:
mysql -u 用户名 -p \n
输入密码
连接远程:
mysql -h 主机名 -u 用户名 \n (在对方的数据库中拥有登录用户并且有权限)
输入密码
数据库用户操作:
查询所有用户:
select user from user;
创建用户:
create user 用户名 identified by 密码
用户授权:
grant 权限名称(如:select、delete等DML语句、全部(ALL)) on *.*(注意这里前面的*是代表数据库,第二个*是表名,这里表示所有的数据库.表) to 用户
撤销用户权限:
revoke 权限 on *.* from 用户
删除用户:
drop user 用户名 (这个有点问题,待解决)
delete from mysql where user = '用户名称'
查询用户信息:
show grants for 用户名
密码转换:
select password('密码') 注意:这是一个41个长度的16进制密码,如果在登录后设置密码必须转换为41个长度16进制密码,否则报错
未登录设置用户密码:
mysqladmin -u 用户名 -p 新密码(注意这里是第一次安装设置)
修改用户密码:
mysqladmin -u 用户名 -p 旧密码 password 新密码
登录后设置用户密码:
set password for 用户名 = password(新密码),如果是当前用户直接set password = password(新密码)
修改用户名:
MYSQL5.0以后 rename user 用户名 to 新用户名
update 表名 set user = '新用户名' where user = '旧用户名'
刷新权限:
flush privileges
权限列表:
表的创建、插入、修改、删除、查询权限:
create
insert
update
delete
select (select(列名...),可以作用在某列上)
数据库与表的删除和修改权限:
drop
alter
modify
chanage
外键权限:
references
临时表权限:
create temporary tables
索引权限:
index
视图权限:
create view
show view
存储过程、函数权限:
cteate routine
alter routine
execute(execute on procedure)
execute(execute on function)
重载权限:
reload
锁表权限:
lock tables
复制权限:
replication client
replication slave
查看数据库权限:
show databases
查看进程权限:
process
创建用户权限:
create user
文件访问权限:
file
杀死线程权限:
super
数据库管理员命令:
mysqladmin -V 数据库版本信息
mysqladmin -u 用户 -p 旧密码 password 新密码 修改密码
mysqladmin -i(number) 重复执行间隔数
mysqladmin -c(count) 重复执行次数
mysqladmin variables 数据库变量
mysqladmin -? 数据库帮助
mysqladmin -u root -p reload 重载权限信息
mysqladmin -uroot -p shutdown 使用安全模式关闭数据库
待研究...
库操作:
(1).查看存在数据库:
show databases
(2).创建数据库:
create database if not exists 数据库名称 (注意:后面最好加上字符编码,防止乱码) -> create database 数据量名称 character set utf8(你需求的字符编码) collate utf8_general_ci(子集)
(3).使用数据库:
use 数据量名称
(4).删除删除库:
drop database if exists 数据库名称
(5).查看创建数据库信息:
show create database 数据库名称
表操作:
(1).查看数据库表:show tables
(2).创建表:
create table if not exists 表名(
属性名称 属性类型[完整性约束条件],
属性名称 属性类型[完整性约束条件],
属性名称 属性类型[完整性约束条件]
)engine=引擎类型,charset=字符编码集,comment=表备注;
(3)完整性约束条件表:
primary key 主键约束
foreign key 外键约束
unique 唯一约束
not null 不能为空
auto_increment 自增
default 默认值
(4)设置表主键:
create table 表名(
属性名称 属性类型 primary key, <---- 光标
属性名称 属性类型[完整性约束条件],
属性名称 属性类型[完整性约束条件]
)engine=引擎类型,charset=字符编码集;
多字段主键：
create table 表名(
属性名称 属性类型[完整性约束条件],
属性名称 属性类型[完整性约束条件],
primary key(属性1,属性2,...) <---- 光标
)engine=引擎类型,charset=字符编码集;
添加主键:
alter table 表名 add primary key(列名)
修改主键:
alter table 表名 change 列名 属性名称 属性类型[完整性约束条件]
删除主键：
alter table 表名 drop primary key
修改自增变量值:
alter table 表名 auto_increment = 从数字几开始.
(5)设置外键：
外键是表的一个特殊字段。如果字段id是一个表A的属性,且依赖于表B的主键.
那么,称表B为父表,表A为子表,id为表A的外键。通过id字段将父表B和子表A建立关联关系.
设置表的外键指在创建表设置某个字段为外键.
外键的好处:可以使得两张表关联,保证数据的一致性和实现一些级联操作.
级联操作:
on delete (在删除的时候) restrict(限制外表中的外键改动) cascade(跟随外键改动) set null(设置为空) set default(设置默认值) no action(无动作,默认的)
on update (在删除的时候) restrict(限制外表中的外键改动) cascade(跟随外键改动) set null(设置为空) set default(设置默认值) no action(无动作,默认的)
创建表时设置外键:
create table 表名(
属性名称 属性类型[完整性约束条件],
属性名称 属性类型[完整性约束条件],
constraint 外键名称 foreign key(主键表列属性(也就是本表列属性)) references 外键表名称(外键表列属性) on (update 级联操作 或 delete 级联操作) <---- 光标
)engine=引擎类型,charset=字符编码集;
单独设置外键:
alter table employee add constraint 外键名称 foreign key(外键表列属性(也就是本表列属性)) references department(主键表列属性) on (update 级联操作 或 delete 级联操作)
修改外键:
外键不支持修改
删除外键:
alter table 表名 drop foreign key 外键名称
(6).设置唯一约束:
创建唯一约束:
建表时创建:
create table 表名(
属性名称 属性类型[完整性约束条件],
constraint 唯一约束名称 unique key(列名) <---- 光标
)engine=引擎类型,charset=字符编码集;
建表后创建:
alter table 表名 add constraint 唯一约束名称 unique key(列名)
删除唯一约束:
alter table 表名 drop index 约束
修改唯一约束:
唯一约束不支持修改
(7).设置默认值:
建表时创建:
create table 表名(
属性名称 属性类型[完整性约束条件] default 默认值 <---- 光标
)engine=引擎类型,charset=字符编码集;
建表后创建:
alter table 表名 alter column 列名 set default 默认值
删除默认值:
alter table 表名 alter column 列名 drop default
修改默认值:
同创建方式
(8).设置不为空值:
建表时创建:
create table 表名(
属性名称 属性类型[完整性约束条件] not null <---- 光标
)engine=引擎类型,charset=字符编码集;
建表后创建:
alter table 表名 change 列名 列名(属性名称) 属性类型[完整性约束条件]
修改不能为空值:
alter table 表名 modify 列名 属性类型[完整性约束条件] not null
删除空值:
(在实际意义上算是修改字段属性了) alter table 表名 change 列名 列名(属性名称) 属性类型[完整性约束条件]
(9).自增变量:
auto_increment 默认是从1开始递增
建表时创建:
create table 表名(
属性名称 属性类型[完整性约束条件] auto_increment <---- 光标
)engine=引擎类型,charset=字符编码集,(如果要改变自增变量起始值)auto_increment = 起始值;
建表后创建:
alter table 表名 modify 列名 int auto_increment (注意:自增的前提 此列必须为主键 或者 唯一约束)
alter table 表名 auto_increment = 起始值
删除自增:
alter table 表名 modify 列名 属性类型[完整性约束条件]
修改自增:
同建表后创建
(10).删除表:
drop if exists 表名
(11).清空表数据:
truncate 表名
(12).改变列名称:
alter table 表名 change 列名 列名(属性名称) 属性类型[完整性约束条件]
(13).改变列类型属性约束:
alter table 表名 modify 列名(属性名称) 属性类型[完整性约束条件]
(14).增加一列:
alter table 表名 add column 列名(属性名称) 属性类型[完整性约束条件]
(15).删除一列:
alter table 表名 drop column 列名
(16).表引擎:
创建时指定表引擎:
create table 表名(
属性名称 属性类型[完整性约束条件]
)engine=引擎类型; <---- 光标
修改表引擎:
alter table 表名 engine = 引擎名称
删除表引擎:
不存在
引擎类型:(存储机制,也称为表类型)
InnoDB
MyIsam
BDB
archive
Mermory
...
默认是innodb
(17).查看表状态:
show table status from 数据库名称 where name = '表名'
show table status
(18).查看一个表几行几列:
mysqlshow -u root -p 数据库名称 -v -v
(19).查看表列信息:
show columns from 表名 或者 show columns from 数据库.表名
(20).表备注:
创建表时:
create table if not exists 表名(
属性名称 属性类型[完整性约束条件],
属性名称 属性类型[完整性约束条件],
属性名称 属性类型[完整性约束条件]
)engine=引擎类型,charset=字符编码集,comment=表备注;
创建表后:
alter table 表名 comment = 备注信息
修改表备注:
同创建表后
删除表备注:
暂时未发现
(21).创建相似表:
create if not exists table 表名 like 存在表名 (注意:只是复制了表结构,数据不会复制)
数据类型：
数值类型:
整数类型:
tinyint -> 1字节 -> 小整数值
smallint -> 2字节 -> 大整数值
mediumint -> 3字节 -> 大整数值
int -> 4字节 -> 大整数值
bigint -> 8字节 -> 极大整数值
float -> 4字节 -> 单精度浮点数值
double -> 8字节 -> 双精度浮点数值
decimal 附属double
字符串类型:
char -> 0-255字节 -> 定长字符串
varchar -> 0-255字节 -> 变长字符串
tinyblob -> 0-255字节 -> 不超过255个字符的二进制字符串
tinytext -> 0-255字节 -> 短文本字符串
blob -> 0-65535字节 -> 二进制形式的长文本数据
text -> 0-65535字节 -> 长文本数据
mediumbob -> 0-16777215字节 -> 二进制形式的中等长度文本数据
mediutext -> 0-16777215字节 -> 中等长度文本数据
logngblob -> 0-4294967295字节 -> 二进制形式的极大文本数据
longtext -> 0-4294967295字节 -> 极大文本数据
varbinary(m) -> 允许长度0-m个字节的定长字符串,值的长度+1个字节
binary(m) -> 允许长度0-m个字节的定长字符串
日期和时间类型:
date -> 4字节 -> 1000-01-01/9999-12-31 yyyy-mm-dd -> 年/月/日
time -> 3字节 -> '-838:59:59'/'838:59:59' hh:mm:ss -> 时/分/秒
year -> 1字节 -> 1901/2155 yyyy -> 年
datetime -> 8字节 -> 1000-01-01 00:00:00/9999-12-31 23:59:59 yyyy-mm-dd hh:mm:ss -> 年/月/日 时/分/秒
datestamp -> 4字节 -> 1970-01-01 00:00:00/2037 yyyymmdd hhmmss -> 时间戳 年/月/日 时/分/秒
复合类型:
enum -> 包含65536个元素,其中一个元素保留,用来保存错误信息 -> enum('值1','值2'...)
set -> 包含64个元素,不能包含2个相同的元素 -> set('值1','值2'...)
索引:
(1).查看索引:
show index from 表名
show keys from 表名
创建索引
create index 索引名称 on 表名(列名);
alter table 表名 add index 索引名称(列名)
唯一索引:
create unique index 索引名称 on 表名(列名)
alter table 表名 add unique 索引名称(列名)
与普通索引相似,但是要求索引列值是唯一不能重复的,可以为空.
主键索引:
create primary key on 表名(列名)
alter table 表名 add primary key(列名)
就是主键列,不允许为空值,一般为Int类型和自增
组合索引:
create index 索引名称 on 表名(列名1,列名2,...)
全文索引:
create fulltext 索引名称 on 表名(列名1,列名2,...)
alter table 表名 add fulltext (列名1,列名2,...)
删除索引:
drop index 索引名称 on 表名
alter table 表名 drop index 索引名称
(注意如果有主外键约束,需要先删除主外键约束)
视图:
创建视图:
默认字段:create view 视图名称 as 查询语句 (注意:这里创建的视图是虚拟表,在查看表可以看到,但是是虚拟的)
自声明字段:create view 视图名称 ('字段1','字段2'...) as 查询语句 (注意:查询的字段列数必须和视图表列数相同)
删除视图:
drop view if exists 视图名称
修改视图:
视图用来查询数据,如果做修改就毫无意义了,而且即使修改也会出现各种问题
注意: -> 在MYSQL中,字符串和日期值必须使用单引号引用,数值类型可有可无
DML语句 -> 插入数据语法:
(1).insert into 表名 (列名1,列名2,列名3...) values('值1','值2'...) (注意:自增字段忽略值)
(2).replace into 表名 (列名1,列名2,列名3...) values('值1','值2'...) (注意:插入数据与主键和唯一键有重复则会变成更新数据,否则增加一行数据)
(3).insert into 表名 (列名1,列名2,列名3...) select 列名1,列名2... from 其他表名 (注意:列数量要一致)
(4).insert into 表名 set 列名1 = 值1 , 列名2 = 值2 ...
载入文本数据(load data):(在有主键的方式下运行有问题):
(1).load data infile '完整数据文件地址' into table 表名 (普通方式)
(2).load data infile '完整数据文件地址' into table 表名 fields terminted by '列分割符'
(3).load data local infile '完整数据文件地址' into table 表名 ... (指定local本地读取,未指定在服务器上读取)
(4).load data low_priority infile '完整数据文件地址' into table 表名 ... (指定low_priority会在其他人没读取这个表的时候才把数据插入)
(5).load data infile '完整数据文件地址' replace into table 表名 (普通方式)
(6).load data infile '完整数据文件地址' into table 表名 fields terminted by '列分割符' ignore 行数 lines (忽略前几行导入)
(7).load data infile '完整数据文件地址' into table 表名 fields terminted by '列分割符' lines ternubated by '行分割符'
(8).load data infile '完整数据文件地址' into table 表名 fields terminted by '列分割符' enclosed by '符号' (字段包含符号)
(9).load data infile '完整数据文件地址' into table 表名 fields terminted by '列分割符' escaped by '符号' (转义符)
复制表:
复制表结构生成新表:
create table 新表名称 like 旧表名称
复制表和数据一起:
create table 新表名称 select * from 旧表名称
复制表中其中一些字段:
create table 新表名称 (select '列名1','列名2'...)
复制表中其中一些字段并且改名:
create table 新表名称 (select '列名1' as '新表列名'.... from 旧表名称)
复制表中其中一些字段并且增加字段:
create table 新表名称(列名 属性类型[完整性约束条件])(select '列名'... from 旧表)
复制旧表数据到新表:
全表数据:
insert into 新表名称 select * from 旧表名称
单列数据:
insert into 新表名称('列名1','列名2'...) select '列名1','列名2' from 旧表名称
DML语句 -> 删除数据语法:
删除数据:
(1).delete from 表名 (删除表中全部数据)(以行为单位)
(2).delete from 表名 where 条件表达式 (带条件的删除,以行为单位)
(3).delete from 表名 where 条件表达式 order by '列名' desc 或者 asc (用于指定这些数据的删除顺序)(以行为单位)
(4).delete from 表名 limit 数目 (删除指定范围的数据)(以行为单位)
DML语句 -> 修改数据语法:
修改数据:
(1).update 表名 set '列名1' = '值1','列名2' = '值2' (修改表中所有数据)(以行为单位)
(2).update 表名 set '列名1' = '值1','列名2' = '值2' where 条件表达式 (带条件的删除,以行为单位)
(3).update 表名 set '列名1' = '值1','列名2' = '值2' order by '列名' desc 或者 asc (用于指定这些数据的修改顺序)(以行为单位)
(4).update 表名 set '列名1' = '值1','列名2' = '值2' limit 数目 (修改指定范围的数据)(以行为单位)
DML语句 -> 查询数据语法:
(1).基本查询:
(1).select 表达式
举例 1: select 1
结果: 1
举例 2: select 1 + 1
结果: 2
举例 3: select 1 > 0
结果: 1 (这里返回是 true 和 false , 1 代表 true , 0 代表 false)
举例 4: select 1 < 0
结果: 0 (这里返回是 true 和 false , 1 代表 true , 0 代表 false)
举例 5: select now()
结果: 当前时间
(2).别名 as
select '列名' as '别名' .... from 表名 (给字段取别名)
select 别名.列名1,别名.列名2 ... from 表名 as 别名 (给表取别名)
(3).去重 distinct (默认为all)
select distinct '列名' from 表名 (distinct * 无效)
(4).from 数据源
select 输出 from 数据源(表)
(5).where子句
select 输出 from 数据源(表) where 筛选条件列 (对数据源进行条件筛选,筛选机制是按行判断,where子句依赖from子句)
(6).where子句各种运算符:
算术运算符: + - * / %
比较运算符: > >= < <= = <> == != (最后2种不推荐使用,应该遵循SQL语法规范)
逻辑运算符: and(与) or(或者) not(非)
select 输出 from 数据源 where 筛选条件列 = 列值
select 输出 from 数据源 where true
select 输出 from 数据源 where 1
select 输出 from 数据源 where false
select 输出 from 数据源 where 0
select 输出 from 数据源 where not(筛选条件表达式)
(7).is 运算符
'列名' is null (判断字段是否是空值,就是没有值得意思)
'列名' is not null (判断某个字段不是空值)
'列名' is true (判断某个字段为真)
'列名' is false (判断某个字段为假:0,0.00,'',null等)
(8).between运算符 (范围判断 什么和什么之间)
select '列名1'... from 表名 where 筛选条件列 between ? and ? (方法1)
select '列名1'... from 表名 where 筛选条件列 比较运算符 ? and ? (方法2)
(9).in 运算符 (查询指定范围但无任何顺序范围)
select '列名1'... from 表名 where 筛选条件列 in ('值1'...)
(10).like 运算符 (模糊查询)
(注意:like 查询依赖2个特殊符号: 1. % 代表任何长度的任何字符 2. _ 代表一个长度的任何字符)
select '列名1'... from 表名 where 筛选条件列 like '%值%'
(如果遇到查询与符号相同的需求可以使用转义字符 如: \% \_)
(11).group by 分组 (将多行数据,以某种标准进行分类存放)
(注意:这里会有点抽象,我们根据分组查询的信息只有部分是有用的,比如共有几个分组等)
select count(*) from 表名 group by '列名' (得到的是每个分组下的数据条数)
select 组信息 form 表名 group by '列名' (可以这样理解,我们得到的是组信息,而不是单条数据信息)
(12).having (只能对分组结果进行筛选,就是必须与group by 连用)
select count(*) from 表名 group by '列名' having count(*) > 数字
(13).order by (对结果进行排序 asc 或 desc)
select * from 表名 order by '列名1'...
(14).limit (指定范围数据查询,起始行号从0开始,取出指定的函数)
select * from 表名 limit 起始行号,显示的记录条数
(2).连接查询:
(1).交叉连接 cross join
select * from 表1,表2,... on 连接条件...
select * from 表1 join 表2 on 连接条件 join 表3 on 连接条件...
select * from 表1 cross join 表2 on 连接条件 cross join 表3 on 连接条件...
交叉连接结果: (表1 + 表2)个列 (表1 * 表2)个行
(2).内连接 inner join
select * from 表1 inner(可省略) join 表2 on 连接条件 (注意:连接条件一般是参与表连接的n个表,相同列的相等)
select '列名1'... from 表1 inner(可省略) join 表2 on 连接条件 (注意:连接条件一般是参与表连接的n个表,相同列的相等)
(注意:跟主外键并无实际的关系)
(3).左连接 left(outer) join
select * from 表1 left join 表2 on 连接条件 (注意:内连接结果,但是多了一个功能就是可以将左表不符合内连接条件的数据也查询出来)
(4).右连接 right(outer) join
select * from 表1 right join 表2 on 连接条件 (注意:内连接结果,但是多了一个功能就是可以将右表不符合内连接条件的数据也查询出来)
(5).全连接 (注意:在mysql中是没有这种语法的)
(注意:内连接结果,但是多了一个功能就是可以将左、右表不符合内连接条件的数据也查询出来)
(3).子查询: (就是用一个查询的结果,作为另一个查询的判断条件)
(注意:子查询必须带有圆括号)
(1).表子查询:
(一个子查询返回的结果理论上是多行多列的时候,此时可以当作一个"表"来使用,通常放在from后面)
例子:
select * from (select * from 表名) as 别名 (注意:把查询结果集当作数据源必须要有别名,否则报错)
(2).行子查询:
(一个子查询返回的结果理论上是一行多列的时候,此时可以当作一个行来使用,通常放在行比较语法中)
例子:
select * from (select * from 表名 where 列名 = 值) as 别名
(3).列子查询:
(一个子查询返回的结果理论上是多行一列的时候,此时可当多个值使用,类似(6,2,5,3))
例子:
select * from 表名 where 列名 in (select 列名 from 表名 where 列名 = 值)
(4).标量子查询:
(一个子查询返回的结果理论上是一行一列的时候,此时可以当作一个值使用)
例子:
select * form 表名 where 列名 = (select 列名 或 其他)
(5).all
select * from 表名 where 列名 比较运算符 all(子查询) (用来遍历判断,只要是比较为真的都被视为结果)
(6).any
select * from 表名 where 列名 比较运算符 any(子查询) (用来遍历判断,只要是比较为真的都被视为结果)
(7).exists 子查询
(该子查询如果有数据,则返回真,否则为假,带隐式连接,子查询使用了主查询的数据)
select * from 表名1 where exists (select * from 表名2 where 列名 = 值 where 表名2.列名 = 表名1.列名)
(4).联合查询: union
(1).select * from 表1 union [all | distinct] select * from 表2 ...
(注意:参与表必须字段数一致,字段类型也应该一致,联合查询默认会自动消除重复行distinct,如果希望被允许,使用all)
事务:
(事务简单来说就是: 要成功一起成功,失败一个也是失败,全部是真才是真,一个为假,也是假)
(1).开启、关闭自动提交事务
set autocommit = 0 或者 1 或者 true 或者 false
(2).手动模式下每执行一条语句需要手动提交
commit (注意语句在没提交事务的情况下,执行后是在内存中,等待生效)
(3).事务的基本执行流程:
(1).开启一项事务: start transaction 或者 begin
(2).执行多条DML语句
(3).判断这些语句的结果
如果没错误 commit 如果有错误 rollback
例子:
(无错误情况下)
start transaction
insert、delete、update、quer
commit
(有错误情况下)
start transaction
insert、delete、update、query (过程出错)
rollback
mysql编程:
(1).语句块包含符:
[标识符 :] begin
...
end [标识符];
(2).流程控制:
(1).if语句:
if 条件表达式 then
begin
...
end;
else if 条件表达式 then
begin
...
end;
...n个else if 语句...
else
begin
...
end;
(2).case 语句：
case 值
when 匹配值 then begin ... end;
...
else begin ... end;
end case
(3).loop 语句:
标识符:loop
begin
if(条件) then (必须有一个退出循环的条件,否则形成死循环)
leave 标识符;
...
end if;
end;
end loop 标识符
(4).while 语句:
while 条件表达式 do
... (这里退出靠循环机制)
end while;
(5).repeat 语句:
repeat
...
until(条件表达式)
end repeat;
(2).变量声明:
(1).普通变量 不带 @ 符号 (只能在编程环境中使用:函数内部、存储过程、触发器)
声明形式:
declare 变量名 属性类型 默认值...
赋值形式:
set 变量名 = 值
(2).会话变量 带 @ 符号
声明形式:
set @变量名 = 值
select @变量名 := 值
selent 值 into @变量名
(3).函数:
(1).声明方式:
delimiter // (定义语义分隔符)
create function 函数名称(形参1 类型1,形参2,...)
returns 返回值类型
begin
...
return 返回值;
end;
//
(2).调用方式:
select 函数名称()
(3).删除函数:
drop function 函数名
注意事项:
在函数体中不能有select或者其他返回结果集语句
(4).存储过程: procedure
(1).声明方式:
delimiter 分隔符
create procedure 存储过程名称(in|out|inout 形参1 类型1,...)
begin
...
end;
分隔符
(2).调用存储过程:
call 存储过程名称()
(5).触发器:
(1).声明方式:
create trigger 触发器名称 触发时间(before、after) 触发事件(insert、update、delete) on 表名 for each row
begin
...
end;
(6).游标:
(1).声明方式:
(1).定义游标:declare 游标名称 cursor for 查询语句
(2).打开游标:open 游标名称
(3).游标抓取:fetch 游标名称 into 变量...
(4).关闭游标:close 游标名称
delimiter 分隔符
create procedure 存储过程名称(in|out|inout 形参1 类型1,...)
begin
// 声明游标变量...
// 定义游标 declare 游标名称 for select 列名1,列名2... from 表名
// 打开游标 open 游标名称
// 游标抓取 fetch 游标名称 into 游标变量...
// 关闭游标 close 游标名称
...
end;
分隔符
mysql导出(备份):
(1).导出全部数据库:
mysqldump -u 用户名 -p -A > 导出的文件名.sql
(2).导出全部数据库自定义路径
mysqldump -u 用户名 -p -A > (路径 如:F:\...\xx.sql)导出的文件名.sql
(3).导出单个数据库
mysqldump -u 用户名 -p -B 数据库名1,数据库2... > 导出的文件名.sql
(默认路径:C:\Users\当前用户,注意以每台电脑的系统盘符为准)
(4).导出单个数据库自定义路径
mysqldump -u 用户名 -p -B 数据库名 > (路径 如:F:\...\xx.sql)导出的文件名.sql
(5).导出指定数据库所有表自定义路径
mysqldump -u 用户名 -p -B 数据库名 --tables > (路径 如:F:\...\xx.sql)导出的文件名.sql
(6).导出指定数据库指定表自定义路径
mysqldump -u 用户名 -p 数据库名 表名1,表名2... > (路径 如:F:\...\xx.sql)导出的文件名.sql
(7).导出指定数据库指定表(带过滤条件)自定义路径
mysqldump -u 用户名 -p -B 数据库名 表名 --where="条件" > (路径 如:F:\...\xx.sql)导出的文件名.sql
(8).导出指定库所有表结构
mysqldump -u 用户名 -p -d -B 数据库名 > (路径 如:F:\...\xx.sql)导出的文件名.sql
(9).导出查询数据为txt文件到本地(首先要登录)(需要权限非常麻烦)
select 字段,... into outfile '导出路径' fields terminated by '分隔符'
mysql导入(还原):
(1).第一种方式:
mysql -u root -p < (路径 如:F:\...\xx.sql)导入文件名.sql
(1).第二种方式(需登录):
source (路径 如:F:\...\xx.sql)导入文件名.sql
mysql函数:
------------ 字符串函数 ------------
(1). ascii(字符串) -> 返回字符串的第一个字符的ascii值
(2). ord(字符串) -> 如果字符串是单字符返回如ascii值,如果是字符串多字符返回(ascii(字符1) + ascii(字符2) + ...)
(3). conv(数字,原进制,转换进制) -> 将一个数字从x进制转换为x进制
(4). bin(数字) -> 将数字转换为2进制 以字符串返回
(5). oct(数字) -> 将数字转换为8进制 以字符串返回
(6). hex(数字) -> 将数字转换为16进制 以字符串返回
(7). char(数字,...) -> 每个数字对应了ascii码表上的值,返回一个组成字符串
(8). concat(字符串1,字符串2...) -> 拼接字符串
(9). length(字符串) -> 返回字符串的长度
(10).locate(字符串1,字符串2) -> 返回字符串1在字符串2第一次出现的位置(下标1开始)
(11).position(字符串1 in 字符串2) -> 返回字符串1在字符串2第一次出现的位置(下标1开始)
(12).locate(字符串1,字符串2,位置) -> 返回字符串1在字符串2指定位置第一次出现的位置(下标1开始)
(13).instr(字符串1,字符串2) -> 返回字符串2在字符串1第一次出现的位置
(14).ipad(字符串,补齐长度,补齐字符串) -> 使用补全字符串补全字符串左边,根据补全长度
(15).rpad(字符串,补齐长度,补齐字符串) -> 使用补全字符串补全字符串右边,根据补全长度
(16).left(字符串,长度) -> 返回字符串左边长度个字符
(17).right(字符串,长度) -> 返回字符串右边长度个字符
(18).substring(字符串,起始位置,截取长度) -> 截取字符串
(19).substring(字符串 from 起始位置 for 截取长度) -> 截取字符串
(20).mid(字符串,起始位置,截取长度) -> 截取字符串
(21).substring(字符串,起始位置) -> 截取字符串
(22).substring(字符串 from 起始位置) -> 截取字符串
(23).substring_index(字符串,分割符,索引) -> 使用分隔符分割字符串然后获取分割后数组长度个内容(整数 左边开始 负号 右边开始)
(24).ltrim(字符串) -> 去除字符串左边的空格
(25).rtrim(字符串) -> 去除字符串右边的空格
(26).trim([[both | leading | trailing] [remstr] from] 字符串) -> 通过指定条件((both(两边) leading(头部) trailing(尾部)) 条件字符串 from 原始字符串)去除字符串指定内容
(27).soundex(字符串) -> 返回一个同音的字符串 感觉无用...
(28).space(数字) -> 返回一个多个空格的字符串
(29).replace(字符串,被替换字符串,替换字符串) -> 替换字符串中需要被替换的字符串
(30).repeat(字符串,数字) -> 返回由多个此字符串拼接的字符串
(31).reverse(字符串) -> 颠倒字符串的顺序并且返回
(32).insert(字符串,起始位置,插入长度,插入字符串) -> 在一个字符串中插入另一个字符串
(33).elt(数字,字符串1,字符串2,字符串3...) -> 返回指定索引位置的字符串
(34).field(查找字符串,字符串1,字符串2...) -> 返回查找字符串在这些字符串中的索引
(35).find_in_set(查找字符串,字符串集('a,b,c,d,e...')) -> 返回查找字符串在字符串集中的索引
(36).lower(字符串) -> 返回小写字符串
(37).lcase(字符串) -> 返回小写字符串
(38).upper(字符串) -> 返回大写字符串
(39).ucase(字符串) -> 返回大写字符串
(40).load_file(文件名称) -> 读文件,以字符串的形式返回
------------ 数学函数 ------------
(41).abs(数字) -> 返回数字绝对值
(42).sign(数字) -> 返回数字正负符号
(43).mod(除数,被除数) -> 返回余数
(44).floor(数字) -> 返回不大于数字的最大整数值
(45).ceiling(数字) -> 返回不小于数字的最小整数值
(46).round(数字,保留小数位) -> 返回数字的四舍五入,保留小数位个小数位
(47).exp(数字) -> 返回数字自然对数的底
(48).log(数字) -> 返回数字的自然对数
(49).log10(数字) -> 返回数字以10为底的自然对数
(50).pow(数字,次幂) -> 返回数字的次幂(平方)
(51).sqrt(数字) -> 返回数字的非负数平方根
(52).pi() -> 返回圆周率(π)
(53).cos(n) -> 返回余弦值
(54).sin(数字) -> 返回数字的正弦值
(55).tan(数字) -> 返回数字的正切值
(56).acos(数字) -> 返回数字的反余弦值
(57).asin(数字) -> 返回数字的反正弦值
(58).atan(数字) -> 返回数字的反正切值
(59).cot(数字) -> 返回数字的余切
(60).rand() -> 返回0 - 0.1 之间的随机浮点值
(61).rand(数字) -> 返回以数字作为初始值的随机浮点值
(62).degrees(数字) -> 把数字弧度转换为角度返回
(63).radians(数字) -> 把数字角度转换为弧度返回
(64).truncate(保留数字,保留位数) -> 返回保留数字的保留位小数
(65).least(数字1,数字2,数字3...) -> 返回最小值
(66).greatest(数字1,数字2,数字3...) -> 返回最大值
------------ 日期函数 ------------
(67).dayofweek(日期) -> 返回输入日期是星期几 (注意:这里返回的是国际标准 6=星期天 0=星期一 1=星期二...)
(68).weelkday(日期) -> 返回输入日期是星期几
(69).dayofmonth(日期) -> 返回日期在月份中的天数(几号)
(70).dayofyear(日期) -> 返回日期在年中是第几天
(71).month(日期) -> 返回日期中的月份
(72).dayname(日期) -> 返回日期是星期几(英文字母返回)
(73).monthname(日期) -> 返回日期是几月(英文字母返回)
(74).quarter(日期) -> 返回日期是第几个季度
(75).week(日期) -> 返回日期是一年中的第几周
(76).year(日期) -> 返回日期的年份
(77).hour(时间) -> 返回时间的小时数
(78).minute(时间) -> 返回时间的分钟数
(79).second(时间) -> 返回时间的秒数
(80).period_add(原始日期,增加月数) -> 在原始日期基础上增加数月
(81).period_diff(日期1,日期2) -> 计算2个日期间的相隔月份数
(82).日期和时间运算
类型:
[type值 含义 期望的expr格式]:
second 秒 seconds
minute 分钟 minutes
hour 时间 hours
day 天 days
month 月 months
year 年 years
minute_second 分钟和秒 "minutes:seconds"
hour_minute 小时和分钟 "hours:minutes"
day_hour 天和小时 "days hours"
year_month 年和月 "years-months"
hour_second 小时,分钟,秒 "hours:minutes:seconds"
day_minute 天,小时,分钟 "days hours:minutes"
day_second 天,小时,分钟,秒 "days"
注意:type值不完整,取值从右端开始取值
函数:
(1).date_add(日期,interval 增加时间或日期 type) -> 增加时间或日期
(2).date_sub(日期,interval 增加时间或日期 type) -> 减少时间或日期
(3).adddate(日期,interval 增加时间或日期 type) -> 增加时间或日期
(4).subdate(日期,interval 增加时间或日期 type) -> 减少时间或日期
(5).extract(type from 日期或时间) -> 返回格式化日期或时间
(83).to_days(日期) -> 返回计算西元0年至日期多少天,不算1582年以前
(84).from_days(天数) -> 返回相隔西元0年多少天的年份,不算1582年以前
(85).date_format(日期,格式化样式)
根据format字符串格式化date值
(在format字符串中可用标志符:
%m 月名字(january……december)
%w 星期名字
%d 有英语前缀的月份的日期
%y 年,数字,4位
%y 年,数字,2位
%a 缩写的星期名字
%d 月份中的天数,数字
%e 月份中的天数,数字
%m 月,数字
%c 月,数字
%b 缩写的月份名字
%j 一年中的天数
%h 小时
%k 小时
%i 小时
%l 小时
%i 分钟,数字
%r 时间,12小时
%t 时间,24小时
%s 秒
%p am或pm
%u 星期
%% 字符%)
(86).time_format(时间,格式化样式) -> 格式化时间,同格式化日期类似,区别在于这里只针对时间
(87).curdate() + 0 -> 返回当前日期值,加0代表无分隔符,不加有分割符
(88).current_date() -> 返回当前日期值
(89).curtime() + 0 -> 返回当前时间,加0代表无分隔符,不加有分割符
(90).current_time() -> 返回当前时间
(91).now() -> 返回当前日期时间
(92).sysdate() -> 返回当前系统日期时间
(93).current_timestamp() -> 返回当前日期时间,这里是格式化后的时间戳
(94).unix_timestamp() -> 返回一个当前unix时间戳
(95).unix_timestamp(日期) -> 返回一个指定日期unix时间戳
(96).from_unixtime(unix时间戳) -> 返回一个指定unix时间戳的日期
(97).from_unixtime(unix时间戳,格式化样式) -> 回一个指定unix时间戳的格式化日期
(98).sec_to_time(秒数) -> 返回一个秒数转化时间的值
(99).time_to_sec(时间) -> 把时间转化成秒数
------------ 转换函数 ------------
(100).cast(字段 as 数据类型) -> 类型转换,(是否成功取决于类型是否能被转换)
(101).convert(字段,数据类型) -> 类型转换,(是否成功取决于类型是否能被转换)
网友评论