MySQL数据库常用命令,函数,约束,事务,事件,触发器等介绍

MySQL数据库常用命令,函数,约束,事务,事件,触发器等介绍

1.连接MySQL数据库：

连接本机的数据库：mysql -uroot -p

连接远程主机的数据库：mysql> mysql -h 主机IP -P 端口 -u root -p密码;（需要先登录MySQL管理系统DBMS，也就是进入MySQL控制台，注意密码和-p之间没有空格）

退出MySQL：quit或者exit

2.修改密码：

利用mysqladmin修改密码：mysql> mysqladmin -u root -p旧密码 password 新密码；

利用set password修改密码：mysql> set password for 用户名@localhost = password(‘新密码’);

3.利用alter修改密码：alter user ‘root’@‘localhost’ identified with mysql_native_password by ‘你的密码’

3.数据库的修改：

建立数据库：mysql> create database db_name;

显示所有的数据库：mysql> show databases;

删除数据库：mysql> drop database db_name;

选择数据库 ：mysql> use db_name;

备份数据库（此命令需要在DOS下进行）：mysqldump -u 用户名 -p密码 -B 数据库1 数据库2… > 文件名.sql

恢复数据库（此命令需要进去MySQL进行）：Source 文件名.sql

4.数据表的修改：

创建数据表模板：mysql> create table tb_name (字段名 字段类型, 字段名 字段类型, ······);

显示数据表：mysql> show tables;

显示表结构：mysql> desc tb_name;

删除表：mysql> drop table tb_name;

备份数据表（此命令需要在DOS下进行）：mysqldump -u 用户名 -p密码 数据库 数据表1 数据表2 … > 文件名.sql;

恢复数据表（此命令需要进去MySQL进行）：mysql> Source 文件名.sql;

以上字段名也就是列名，tb_name也就是表名。

追加列: mysql> alter table 表名 add 字段名 字段类型 NOT NULL DEFAULT 'str ’ AFTER 被追加的字段名;

这个命令将会在被追加的字段后面添加一列字段。

修改列: mysql> alter table 表名 modify 字段名 字段类型 NOT NULL DEFAULT ‘’;

删除列: mysql> alter table 表名 drop 字段名;

修改表名: mysql> rename table 旧表名 to 新表名;

修改表的字符集: mysql> alter table 表名 character set 字符集类型;

修改列名: mysql> alter table 表名 change `旧列名` `新列名` 字段类型 NOT NULL DEFAULT ‘’;

5.MySQL的CRUD：

增加(Create)、检索(Retrieve)、更新(Update)和删除(Delete)。

Insert语句：insert into 表名 (字段1名, 字段2名…) values (字段值1, 字段值2…);

(1) 注意，插入的数据需要和字段类型相同，或者可以向字段类型隐式转换；

(2) 字符和日期类型的数据应该在单引号内；

(3) 列可以插入空值[前提是可以为空]，如要添加空值，则values里面为NULL；

(4) insert into 表名 (字段1名, 字段2名…) values (), (), ()…的形式一次添加多个记录；

(5)如果要给表内所有字段添加数据，可以省略字段组（表名后面的括号)。

update语句: update 表名 set 字段 = 要修改的字段值 where 字段 = ‘字段值’;

(1) update语句可以使用新值来代替原来列中的值；

(2) set语句指定要修改哪些列以及赋予哪些值；

(3) where语句指定更新哪些行，如果没有where语句，就会更新所有行；

(4) 如果要修改多个字段，可以设置set 字段1, set 字段2… where…。

delete语句: delete from 表名 where 字段与运算符的表达式;

(1) delete语句如果不添加where，则会删除所有记录（一行为一条记录）；

(2) delete语句只能删除记录，不能删除字段。

select语句: select [distinct] * | {字段1, 字段2 …} from 表名 where 字段与运算符的表达式;

(1) select指定查询哪些列的数据，字段代表列名，* 表示查询所有列，其中 *和{字段}只能选一个，from指定查询哪张表，distinct是可选的，当输入命令包含distinct时，查询结果对重复数据只显示一条。上面的[]、{}、|均不能输入进命令。

(2) 使用select语句可以对查询的列进行运算:select * | {字段1 运算符 字段2 运算符 …} from 表名;

(3) 使用select语句还可以对列起别名:select 字段 as 别名 from 表名;

(4) 在where字句中可以使用的运算符：其中between … and …是闭区间；Like ‘何%’，%表示代替任意字符；Like ‘何_’，_表示代替任意一个字符。

（4.1）

（4.2）all、any、some关键词，all(…)要求对括号里面的所有值都满足条件；any(…)要求对括号里的任意一条满足即可；some和any类似。

（4.3）exists关键词，exists(…)只要括号内有满足的条件，就返回true。

(5) 使用select语句的order by字句进行排序查询结果：select * | {字段…} from 表名 order by 字段 asc | desc, 字段…;

asc表示升序显示，desc表示降序显示，默认升序显示。order by字句默认放在select语句末尾。

对于一条mysql语句，可以将在该命令中的多个字句结合执行，例如select中的distinct、别名、where、order by字句可以结合执行。

(6) regexp运算符：regexp 正则表达式；表示使用正则表达式进行过滤。

6.函数：

统计函数，方便对数据进行查询，有如下函数名：

（1）avg函数：select avg(字段名1) [, avg(字段名2)…] from 表名 [where 字句];

（2）sum函数：select sum(字段名1) [, avg(字段名2)…] from 表名 [where 字句];

（3）max，min函数：select max(字段名1) [, min(字段名2)…] from 表名 [where 字句];

（4）count函数：select count( * ) | count(字段名) from 表名 [where 字句];

（4.1）注意count( * )和count(字段名)对于数据检索有区别，count(字段名)检索时会不考虑值为null的记录。

（5）分组字句group：select 字段1, 字段2… from 表名 group by 字段 [, 字段…] having…;

（5.1）group字句会将记录分组，相同的化为一组，having和where字句类型，都是对分组过后的结果进行过滤，所用的表达式也差不多，但是！having是对分组进行过滤，而where是对记录进行过滤，使用group后就不能再使用where。如果group by后面有多个字段，会根据顺序进行先后分组。

字符串函数，可以添加在查询select语句中使用：select 函数() from table;注意函数之间可以进行嵌套。

（1）charset(str)：返回字符的字符集格式，str可以改为字段；

（2）contact(str[, str...])：连接字符串，str可以改为字段；

（3）instr(str, substr)：返回substring在string中的位置，若没有则返回0，起始位置是1。string也可以改为字段，str可以改为字段；locate(substr，str)和instr具有相同的功能，注意参数的位置相反。

（4）Ucase(str)，Lcase(str)：将str改为大写或小写，str可以改为字段；

（5）left(str, len), right(str, len)：从str左边或右边开始取len个字符，str可以改为字段；

（6）length(str)：返回str的长度[以字节为单位]，str可以改为字段；

（7）replace(str, search_str, replace_str)：在str中用replace_str替换search_str，str可以改为字段；

（8）strcmp(str, str2)：逐字符的比较两个字符串，比较格式根据表的格式，str、str2可以改为字段；

（9）substring(str, pos, len)：在str上的pos位置开始取出len个字符，str可以改为字段；

（10）ltrim(str)，rtrim(str)，trim(str)：分别为去除左端的空格，去除右端的空格，去除两边的空格，str可以改为字段。

3.数学函数，可以添加在查询select语句中使用：select 函数() from table;注意函数之间可以进行嵌套。

（1）abs(num)，绝对值函数；

（2）bin(decimal_num)，将十进制数转换为二进制数；

（3）ceiling(num)，向上取整num；

（4）floor(num)，向下取整num；

（5）conv(num, from_base, to_base)，将from_base进制的num转化为to_base进制的数；

（6）format(num, places)，保留小数位数；

（7）hex(num)，转换为十六进制；

（8）least(num1, num2...)，求num1，num2…中最小的数；

（9）mod(num, denominater)，取余；

（10）rand([seed])，生成随机数。如果使用rand()则每次会生成不同的随机数，如果加入种子，则会根据种子生成随机数，种子不变，随机数不变。

（11）round(num1，num2)，将num1四舍五入，小数点后的精度为num2。

日期函数，可以添加在查询select语句中使用：select 函数() from table;注意函数之间可以进行嵌套。

（1）current_date()、current_time()、current_timestamp()分别返回当前日期、时间、时间戳；

（2）date（datetime）返回datetime的日期部分；

（3）date_add(date, INTERVAL value type) 在date的基础上加上value，value的类型是type，type可以是year，moth，day，hour等，date可以是datetime、datestamp类型；

（4）date_sub(date, INTERVAL value type) 在date的基础上减去value，value的类型是type，type可以是year，month，day，hour等，date可以是datetime、datestamp类型；

（5）datediff(date1, date2)求日期差[天]、timediff(date1, date2)求时间差[时间]；

（6）now()返回当前时间；

（7）year | month | date (datetime)取出datetime的year，month，day。datetime也可以是date。

（8）unix_timestamp()返回1970-1-1到现在的秒数；

（9）from_unixtime(values，'%Y-%m-%d %H:%i:%s')，将values转换为%Y-%m-%d %H:%i:%格式，%Y-%m-%d %H:%i:%s中的%x可以随意组合，其中values是一个int值，这个int值是转换后的结果到1970-1-1所需的时间。

（10）dayname(datetime)和monthname(datetime)分别返回当前时间的星期日期和月份的英文字符串格式。

加密和系统函数： （1）user()，查询正在使用数据库的用户；

（2）database(), 查询正在使用的数据库的名称；

（3）MD5(str)，用MD5方式加密str并输出加密后的字符串。

流程控制函数： （1）if(expr1, expr2, expr3)，如果expr1为真就返回expr2否则返回expr3；

（2）ifnull(expr1, expr2)，如果expr是NULL就返回expr2，否则返回expr1；

（3）case when expr1 then expr1_ when expr2 then expr2_ ... else expr3_ end，这个语句可以理解为多重嵌套，如果expr1为真就返回expr1_，否则如果expr2为真就返回expr2_，否则就返回expr3_。

（4）coalesce(...)返回…中第一个非空的值。

7.查询加强：

SQL语句很灵活，各种函数嵌套等等，多思考！！！

使用where子句：

（1）在mysql中，日期类型可以直接比较，所以我们可以使用这个语句：select 字段 from 表名 where 字段 比较符 日期。注意日期格式。

使用LIKE模糊查询：

（1）select 字段 from 表名 where 字段 LIKE str 表示在表中查询字段，并且字段必须像str，str里面需填写%、_ ，%表示代替任意个字符，_表示单个字符。

分组查询：group by 字段名… having …，将字段名按照having条件进行分组，可以在后面加with rollup子句，将字段值进行汇总添加为最后一条记录。

如果要查询字段值为NULL，需要这么表示：select 字段 from 表名 where 字段 IS NULL，这个语句就是显示字段值为空的记录。

order by子句：order by子句可以将表通过多个字段排序显示，例如：select 字段 from 表名 order by 字段1 asc，字段2 desc…

分页查询：select .... limit start, num；表示从start-1行开始，显示num行记录。例如 select * from table limit start, num;

如果一个select语句同时含有group by，having，limit，order by子句，它们的顺序应该是group by，having，order by，limit

多表查询：select 字段 from 表1，表2... where 过滤语句； （1）注意如果没有where过滤语句，则会产生笛卡尔积，且选择select后面的字段时，如果有多张表内有相同的字段名，那么这里就需要指定是哪张表的字段，语句为 表名.字段。

（2）自连接，多表查询除了可以是不同的表，还可以是一张表当做两张表进行查询，但是需要对表起两个别名，例如select 字段 from 表1 as 表名1, 表1 as 表名2 where 过滤语句。注意select后面的字段也需要指定是那个表的字段，如：表名1.字段名，表名2.字段名。

子查询 （1）子查询就是将一个查询的结果放在另一个查询中，也称为嵌套查询。单行子查询可以直接当做字段值使用，但是多行子查询就需要使用比较运算符in，这里的单行和多行的列数都是1，否则就是一张表了。

（2）子查询可以当做一个临时表来用，被插入到select语句中。

（3）在子查询中可以使用all和any关键字。all()关键字就是将括号里的每一个值拿出来比较，都满足才可以，但是any()关键字只需要满足一个值就可以了。

（4）多列子查询，如果想要将多列子查询返回的语句和外部进行比较，则格式是这样的（字段1，字段2…）= （多列子查询）。注意这里的多列子查询是一行。

复制表： （1）将表的数据复制到令一张表上：insert into 表名1 （字段…）select 字段… from 表名2；

（2）自我复制：insert into 表名 select * from 表名;

（3）构造一张与已经存在表的结构相同的表：create 表名1 my_tmp like 表名2;

合并查询： （1）union all关键字可以将两条select查询语句的结果进行合并，注意是合并记录，这个关键字不会去重。

（2）union关键字可以将两条select查询语句的结果进行合并，注意是合并记录，这个关键字会去重。

8. 内、外连接：

内连接：select ... from 表1 join 表2 on 条件;内连接就是将表一和表二进行连接，如果没有加on条件，则会默认产生笛卡尔积结果，在连接中还可以使用using条件，用using 字段名代替on条件，但是前提是连接的表中必须同时含有字段名；

左外连接：select ... from 表1 letf join 表2 on 条件;左外连接就是将表1与表2中没有对应的记录也显示出来，也就是说表1的记录会全部显示，将其他字段值置为空。

右外连接：select ... from 表1 right join 表2 on 条件;右外连接就是将表2与表1中没有对应的记录也显示出来，也就是说表2的记录会全部显示，将其他字段值置为空。

9. Mysql约束：

主键 ：primary key（主键）或者 字段名 字段类型 primary key。用于唯一的标识表行的数据，当定义主键约束后，该列的字段值就不能有重复。

（1）主键唯一且不能为空；

（2）一张表只能有一个主键，但可以是复合主键，如：在创建表时，加入primary（字段1，字段2）；代表着字段1和字段2必须有一个不同，也就是不能有这两个字段值完全相同的记录。

not null（非空）：字段 字段类型 not null，如果定义字段为not null，则插入的数据不能是null：。

unique（唯一）：字段 字段类型 unique，当定义了该约束，则该字段的字段值不能有重复。

（1）如果字段没有指定not null，则一个字段可以有多个not null值。

（2）一张表可以有多个unique字段。

foreign key（外键）：foreign key (从表字段名）references 主表(主键字段或unique字段) ON update/delete cascade/set null/no action。在定义表时添加的子句。

（1）用于定义主表和从表的关系，外键约束在从表上，而主表必须有被主键或unique约束的字段，当定义外键约束后，外键字段数据要么为null（前提外键字段可以为null），要么必须在主表的被主键约束字段上存在。

（2）表的存储引擎必须是innodb类型的，这样的表才支持外键。

（3）外键字段类型必须和主键字段类型一样。

（4）要删除主表中的记录时，必须得先把从表上与要删除记录的字段相同的记录全部删除才能删除主表的记录。

（5）定义外键最后的on…语句表示我们对父表进行更新或删除操作时，对子表是否联级、置空、无操作。

check：用于强制数据进行约束，比如约束字段必须在一个范围内，或只能使用一些特定的字段值。

（1）语法：字段 字段类型 check （check条件）。

删除约束： alter table 表名 drop primary key；

alter table 表名 drop foerign key 外键名；

添加约束： alter table 表名 add primary key（字段名）；

alter table 表名 add foerign key 外键名（字段名）…

添加外键语句后面的…和上面定义外键时的一致。

10.自增长

在表中，我们通常希望id列进行自动增长：字段名 类型 primary_key auto_increment，这样就设置字段为自增长类型了，添加字段的方式也有不同：insert into 表名 (字段1，字段2… ) values(null, ‘值’…）；insert into 表名 values(null, ‘值’…）；填写的值为null并不代表真的为null，字段会根据上一条记录的字段值进行自增长。

（1）自增长的字段一般要用primary key约束。

（2）自增长也可以单独使用，但是需要配合unique。

（3）自增长修饰的字段一般是整形，也可使小数类型。

（4）自增长默认从1开始，也可以通过语句mysql> alter table 表名 auto_increment = xxx；来指定从xxx开始。

11.索引

索引的原理：

当我们没有索引时，查询会进行全表扫描，速度很慢。建立索引后，形成了数据结构二叉树、B树、B+树等，使用这种数据结构后，查询效果大大增强，但是会增加内存占用，且导致修改、删除、更新语句的效率会降低。

索引的分类：

（1）主键索引，主键自动的为主索引；

（2）唯一索引（unique），使用unique约束字段后自动的创建了唯一索引，希望字段值不能重复而创建索引；

（3）普通索引（index），使用语句对普通的字段创建索引；

（4）全文索引，适用于MyISAM，一般不使用mysql自带的全文索引，而是使用全文搜索的框架Solr和ElasticSearch（ES）。

查询索引： （1）show indexs from 表名;

（2）show index from 表名；

（3）show keys from 表名；

添加唯一索引：create unique index 索引名 on 表名（字段名）。

添加普通索引： （1）create index 索引名 on 表名（字段名）。

（2）alter table 表名 add index 索引名 （字段名）。

删除索引： （1）删除普通索引：drop index 索引名 on 表名；

（2）删除主键索引：alter table 表名 drop primary key；

12.事务：

本质就是多个dml（增加，修改，删除）语句的组合，用于保证数据的一致性。这组dml语句要么全部成功，要么全部失败。

在执行事务的时候，mysql会对数据库加上锁，防止其他用户就该数据库。

基本操作： （1）start transaction——开始一个事务；

（2）savepoint 保存点名——设置保存点；

（3）rollback to 保存点名——回退事务；

（4）rollback——回退全部事务，会删除这个事务中设置的保存点；

（5）commit——提交事务，所有的操作生效，不能回退。

注意事项：

（1）mysql的事务机制需要innodb的存储引擎才可以

（2）开始一个事务 strat transaction或者set autocommit = offf；若使用后者，则结束事务必须要写set autocommit = on；

（3）当一个事务执行一条sql语句时，会锁定该sql执行的记录，和隔离级别无关。注意这里是记录！！！

隔离级别： （1）多个连接开启各自事务操作数据库中的数据时，数据系统要负责隔离操作，以保证各自连接在获取数据时的准确性。

（2.1）脏读：当一个事务读取另一个事务尚未提交的修改时，产生脏读。

（2.2）不可重复读：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，第二个事务已经提交。那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

（2.3）幻读：指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行。InnoDB存储引擎通过多版本并发控制（MVCC）解决了幻读的问题。

（3）查看当前会话的隔离级别：select @@transaction_isolation

（4）查看当前系统的隔离级别：select @@global.transaction_isolation

（5）设置当前会话的隔离级别：set session transaction isolation level 隔离级别；

（6）设置当前系统的隔离级别：set global transaction isolation level 隔离级别；

（7）隔离级别有：read uncommitted、read committed、repeatable read、serializable。mysql默认的隔离级别是repeatable read。

（8）事务的acid特性：

（9）死锁：不同事务均握住了别的事务需要的“锁”而无法完成的情况； 因为这两个事务都在等待对方完成事务，所以永远无法释放锁。

13.储存引擎：

MySQL的表类型由存储引擎决定，主要有MyISAM、innoDB、Memory等。

MyISAM不支持事务，也不支持外键，但访问速度很快，对事务完整性没有要求；

InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率会差一点并且会占用更多的磁盘空间以保留数据和索引。

Memory存储引擎使用存在内存中的内容来创建表。每个Memory表只实际对应一个磁盘空间。Memory类型的表访问很快，因为它的数据是存放在内存中的，并且默认使用HSAH索引，但是Mysql服务一旦关闭，表中的数据就会丢失，但是表单结构还在。

显示存储引擎：show engines;

修改存储引擎：alter table 表名 engine = innodb；

14.视图：

视图的原理：

（1）视图是根据基表（可以是多个基表）创建的，视图是虚拟的表；

（2）视图也是有列的，数据来自基表；

（3）通过视图可以修改基表的数据；

（4）基表的改变也会影响到视图的数据。

使用视图： （1）create view 视图名 as select语句；

（2）alter view 视图名 as select语句；

（3）show create view 视图名；

（4）drop view 视图名1，视图名；

注意事项：

（1）当通过视图修改某些数据时，可能会导致记录在视图中被删除，这是视图默认的行为，所以我们需要在创建视图的末尾添加with check option子句。

15.MySQL管理：

创建用户：create ‘用户名’@允许登录的位置 identified by ‘密码’；

删除用户：drop user ‘用户名’@‘允许登录的位置’；

给用户授权： grant select，insert，update… on 数据库.表 to 用户@IP;

回收用户授权

查看用户权限：show grants for 用户；

16.创建储存过程：

建立过程： delimiter $$

create procedure 过程名(参数)

begin

sql语句;

en $$

delimiter ; //delimiter语句修改默认分隔符；

调用过程： call 过程；

删除过程： drop procedure if exists 过程名;

添加参数： （4.1）在建立过程时，参数应为 （参数名 参数类型…），如（state INT，name char…）;

（4.2）默认参数：在过程中添加语句：

if 参数名 is null then sql语句；

end if；

（4.3）参数验证：如果发现参数不符合规则，我们应该终止过程，使用signal sqlstate ‘xxx’ set message_text = ‘xxxxx’；这个语句可以终止过程，并抛出异常。

（4.4）输出参数：在定义参数的时候，前缀out，即可将这些参数标记为输出参数，在调用过程结束后，即可将这些参数返回。

创建本地变量：

declare 变量名 类型 default 默认值;

17. 函数：

创建函数：函数和过程很相似，但函数只返回单一值。

delimiter $$

create procedure 函数名(参数)

returns 返回类型

函数属性

begin

sql语句;

return 变量；

en $$

delimiter ; //delimiter语句修改默认分隔符；

函数属性： （2.1）deterministic

（2.2）reads sql data

（2.3）modifies sql data

（2.4）…

18. 触发器：

触发器是指在插入、更新和删除语句前后自动执行的一堆SQL代码，是为了保持数据的一致性。

建立触发器：其中sql语句中可以使用NEW关键字得到刚刚插入的行，OLD关键字返回更新前的行以及对应的数值或被删除前的记录。使用new.字段或OLD.字段即可得到具体的字段值。

delimiter $$

create trigger 触发器名

after/before insert/update/delete on 表名

for each row

begin

sql语句

end $$

delimiter；

查看触发器： （1）show triggers；

（2）show triggers like ‘str%’，显示前缀为str的触发器。

删除触发器： drop trigger if exists 触发器名；

19.事件：

创建事件：使一些sql语句按照某种规律自动执行。MySQL事件调度器每时每刻的寻找事件进行执行，是一个后台程序。

show variables like ‘event%’； //查找事件管理器变量。

set global event_scheduler = ON；

delimiter $$

create event 事件名

on schedule

at ‘date’ //定期执行一次或者可以改为

every 1 year starts ‘日期’ ends ‘日期’

do begin

sql语句

end $$

delimiter ；

查看事件：show events；

删除事件：drop event if exists 事件名；

更改事件；用alter替换create，结构一样。

20. 标准化：

第一范式：要求每一行中的每个单元格都应该有单一值，且不能出现重复列。

第二范式：要求每张表应该有单一目的，只能代表一种且仅有一种实体类型。 一张表的每一列都应该在描述该表所代表的实体。

第三范式：表中的列不应该派生自其他列。