MYSQL数据库操作指令

一、基本知识点

字段类型

字段类型	描述
int	整数类型，取值范围为-2147483648 到 2147483647 或0 到 4294967295
char	固定字符串类型，可包含字母、数字以及特殊字符，最多 255 个字符。
varchar	可变字符串类型，可包含字母、数字以及特殊字符，最多 255 个字符。
double	带有浮动小数点的大数字
decimal	作为字符串存储的 DOUBLE 类型，允许固定的小数点。
datetime	日期时间类型，格式为YYYY-MM-DD HH:MM:SS，支持的取值范围为 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’

约束

约束类型	描述
PRIMARY KEY	主键，用于保证该字段的值具有唯一性，并且非空
UNIQUE	唯一，用于保证该字段的值具有唯一性，可以为空
NOT NULL	非空，用于保证该字段的值不能为空
DEFAULT	默认值，用于保证该字段有默认值
FOREIGN KEY	外键，用于限制两个表的关系，保证该字段的值必须来自于主表关联列的值，主要为了引用主表中某些的值。例：foreign key(A表字段) references 主表(主表字段)

内置函数

函数名称	描述
concat(str1,str2…)	拼接字符串，返回连接参数产生的字符串
left(str,len)	左截取字符串，返回字符串str的左端len个字符
right(str,len)	右截取字符串，返回字符串str的右端len个字符
substring(str,pos,len)	截取指定位置字符串，返回字符串str的位置pos起len个字符
ltrim(str)	返回删除了左空格的字符串str
rtrim(str)	返回删除了右空格的字符串str
lower(str)	字符串全部转小写
upper(str)	字符串全部转大写
length(str)	返回字符串的字节长度

数学函数

函数名称	描述
round(n,d)	四舍五入
pow(x,y)	x的y次幂
PI()	获取圆周率
abs()	返回数字的绝对值
rand()	随机数，返回0-1间的浮点数
truncate(x, D)	截断至保留D位小数，D可以为负数

日期时间函数

函数名称	描述
current_date()	获取当前的日期
current_time()	获取当前的时间
now()	获取当前的日期时间
date_format(date,format)	进行日期格式化

流程控制及自定义函数

    A 流程控制：case 值 when 比较值1 then 结果1 ... else 结果 end
    B 自定义函数：
        step1 设置分割符：delimiter $$
        step2 创建函数：
            >>> 自定义函数：

	create function 函数名称(参数列表) returns 返回类型 
	begin
	sql语句
	end
	$$

            <<< 创建存储过程的函数:

	create procedure 存储过程名称(参数列表)
	begin
	sql语句
	end
	$$

        step3 还原分割符：delimiter;
        step4 使用:
            自定义函数：select 'str',自定义函数(参数)
            存储过程函数：call 存储过程(参数列表)
备注：存储过程和函数都是为了可重复的执行操作数据库的 sql 语句的集合，
一次编译缓存，无需重复编译；减少网络交互,减少网络访问流量。

二、操作指令

数据库相关指令

命令描述	命令
数据库启动	/etc/init.d/mysqld start
查看mysql进程	ps -ef|grep mysql|grep -v grep
关闭数据库	/etc/init.d/mysqld stop
命令行连接数据库	mysql -u 用户名 -p 密码
查看所有数据库	show databases;
使用数据库	use 数据库名;
查看当前使用的数据库	select database();
删除数据库	drop database 数据库名;
创建数据库	create database 数据库名 charset=utf8;

用户相关指令

命令描述	命令
创建用户	create user 'username'@'host' IDENTIFIED BY 'password';
用户授权	GRANT SELECT/USAGE/ALL ON db2.invoice TO 'user'@'主机';
修改用户密码	update user set password='新密码' where user='用户名';
修改管理员roott用户密码	mysqladmin -u root -p '旧密码' password '新密码'
删除用户	drop user 'user'@'主机'；
权限刷新	flush privileges;

数据表操作指令

命令描述	命令
查看库中表	show tables;
建表	create table table_name(字段名 类型 约束);
查看表结构	desc 表名;
查看表的创建语句	show create table 表名;
表备份	mysqldump –uroot –p 数据库名 > ceshi.sql
表恢复	mysql -uroot –p 新数据库名 < ceshi.sql
删表	drop table (if exists) table_name
表索引查看	show index from 表名;
创建索引	create table create_index(字段名 类型 约束 …… key (字段));
添加索引	create index 索引名称 on 表名(字段名称(长度))
删除索引	drop index 索引名称 on 表名;
监控数据查询速度	set profiling=1; show profiles;
分析查询	explain sql查询语句
定义视图(建议以v_开头)	create view 视图名称 as select语句;
删除视图	drop view 视图名称;

数据操作指令

命令描述	命令
查询	select * from table_name（distinct可去, like可模糊查询,order by可排序,group by可分组）
分组数据筛选	select 列1,聚合 from table_name group by 列1 having 列1,聚合……
分页显示	select * from table_name limit (n-1)*m,m
内连接	select * from 表1,表2 where 表1.列=表2.列 或 select * from 表1 inner join 表2 on 表1.列=表2.列
左连接	select * from 表1 left join 表2 on 表1.列=表2.列
右连接	select * from 表1 right join 表2 on 表1.列=表2.列
新增	insert into table_name(字段1,字段……) values(值1,值……),(值1,值……)
修改	update table_name set 列1=值1, 列2=值2, 列3=值3
删除	delete from table_name where 条件

三、数据库事务

1. 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成；
2. 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。
3. 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改；事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
4. 持久性：事务处理结束后，对数据的修改是永久的，即便系统故障也不会丢失。

事务控制语句

命令	命令描述
BEGIN	开始一个事务
SAVEPOINT identifier	在事务中创建一个保存点
RELEASE SAVEPOINT identifier	删除一个事务的保存点
COMMIT	提交事务，即事务确认
ROLLBACK	事务回滚
ROLLBACK TO identifier	把事务回滚到标记点
SET TRANSACTION	设置事务的隔离级别
SET AUTOCOMMIT=0	禁止自动提交
SET AUTOCOMMIT=1	开启自动提交