mysql操作

mysql中delete、truncate、drop的区别

1.delete和truncate仅删除表数据，drop会将表数据和表结构都删除了；打个比方：delete是单杀，truncate是团灭，drop是将电脑摔了；
2.delete是dml语句，操作完以后如果不想要了，可以回滚，不提交事务；truncate和drop是ddl语句，操作完立刻生效，不可以回滚；
3.执行速度：drop>truncate>delete;

mysql中coalesce()的使用；

select coalesce(userName,"总结") as userName,count(1) from user group by userName with rollup;

image.png

mysql 正则表达式

image.png

查找name字段中以“st”为开头的所有数据；
select name from person_tbl where name regexp '^st';

查找name字段中以“ok”为结尾的所有数据；
select name from person_tbl where name regexp 'ok$';

查找name字段中包含“mar”字符串的所有数据；
select name from person_tbl where name regexp 'mar';

查找name字段中以元音字符开头或以“ok”结尾的所有数据；
select name from person_tbl where name regexp '^[aeiou]|ok$';

mysql alter命令

删除字段：
alter table testalter_tbl drop i;

添加字段：
alter table testalter_tbl add i int;

如果需要指定新增字段的位置，可以使用mysql提供的关键字FIRST(设定为第一列),AFTER(设定为某个字段之后)；
first和after关键字可用于add和modify子句，所以如果想重置数据表字段的位置就需要先使用drop删除字段然后使用add来添加字段并设置位置；

alter table testalter_tbl drop i;
alter table testalter_tbl add i int first;
alter table testalter_tbl drop i;
alter table testalter_tbl add i int after c;

如果需要修改字段类型及名称，你可以在alter命令中使用modify或change子句；
将字段c的类型从char(1)修改为char(10)，可以执行下面命令：
alter table testalter_tbl modify c char(10);

使用change修改字段的名称和类型；
alter table testalter_tbl change c i char(10);

设置字段的默认值：
alter table testalter_tbl modify i int not null default 100;

alter table testalter_tbl alter i set default 1000;

删除字段默认值：
alter table testalter_tbl alter i drop default;

修改表名：
alter table testalter_tbl rename to alter_tbl;

修改存储引擎：
alter table testalter_tbl engine = innodb;

删除外键约束：keyname是外键名称；
alter table testalter_tbl drop foreign key keyname;

mysql 索引

mysql索引的建立对于mysql的高效运行时很重要的，索引可以大大提高mysql的检索速度；
打个比方说，如果合理的设计且使用索引的mysql是一辆兰博基尼，那么没有设计和使用索引的mysql就是一辆人力三轮车；
索引分为单列索引和组合索引。单列索引，即一份索引包含单个列，一个表可以有多个单列索引，但这不是组合索引，即一个索引包含多个列；
创建索引使，你需要确保该索引是应用在sql查询语句的条件（一般作为where子句的条件）。
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
上面都在说使用索引的好处，但是过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，mysql不仅要保存数据，还要保存一下索引文件；
建立索引会占用磁盘空间的索引文件；

修改表结构（添加索引）
alter table tablename add index indexName(columnName)；

创建表的时候直接指定
create table mytable(
id int not null,
username varchar(16) not null,
index [indexName] (username(length)),
unique indexName
)

删除索引的语法
drop index [indexName] on mytable;

唯一索引
create unique index indexName on mytable(username(length))

修改表结构
alter table mytable add unique [indexName] (username(length))

使用alter命令添加索引
alter table tbl_name add primary key(column_list);该语句添加一个主键，这意味着索引值必须是唯一的，且不能为null；
alter table tbl_name add unique index_name(column_list);这条语句创建索引的值必须是唯一的（除了null外，null可能会出现多次）；
alter table tbl_name add index index_name(column_list);添加普通索引，索引值可出现多次；
alter table tbl_name add fulltext index_name(column_list);该语句指定了索引为fulltext，用于全文索引；

mysql 临时表

mysql临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，mysql会自动删除表并释放所有空间；临时表在mysql3.23版本中添加，如果你的mysql版本低于3.23版本就无法使用mysql的临时表。不过现在一般很少有在使用这么低版本的mysql数据库服务了。
mysql临时表只在当前连接可见，如果你使用php脚本来创建mysql临时表，那每当php脚本执行完成后，该临时表也会自动销毁。
如果你使用了其他mysql客户端程序连接mysql数据库服务器来创建临时表，那么只有在关闭客户端时才会销毁临时表，当然你也可以手动销毁；

mysql 复制表

方式一：
使用 show create table user 命令获取创建数据表（create table）语句，该语句包含了原数据表的结构，索引等；
复制以下命令显示的sql语句，修改数据表名，并执行sql语句，通过以上命令将完全的复制数据表结构；
如果你想复制表的内容，你就可以使用 insert into ... select 语句实现；

方式二：
create table targetTable like sourceTable;
insert into targetTable select * from sourceTable;

方式三：
可以拷贝一个表中其中的一些字段：
create table newadmin as
(
select username,password from admin
)
可以将新建的表的字段改名：
create table newadmin as
(
select username,password as pass from admin
)
可以拷贝一部分数据：(只复制username首字母为s的记录)
create table newadmin as
(
select * from admin where left(username,1)='s'
)
可以在创建表的同时定义表中的字段信息：
create table newadmin
( id integer not null auto_increment primary key)
as
(
select * from admin
)

只复制表结构到新表：
create table newTable select * from oldTable where 1=2;
create table newTable like oldTable;

复制表结构及数据到新表：
create table newTable select * from oldTable;

mysql元数据

获取服务器元数据

msyql序列使用

mysql序列是一组整数：1,2,3,...,由于一张数据表只能有一个字段自增主键，如果你想实现其他字段也实现自动增加，就可以使用mysql序列来实现；

1.使用 auto_increment mysql中最简单使用序列的方法就是使用 mysql auto_increment 来定义列；
2.可以使用last_insert_id()函数来获取最后插入表中的自增列的值；

重置序列
alter table t auto_increment =100;

mysql 导出数据

load data infile是select ... into outfile的逆操作，select语法。为了将一个数据库的数据写入一个文件，使用select ... into outfile，为了将文件读回数据库，使用load data infile；
select ... into outfile 'file_name' 形式的select 可以把被选择的行写入一个文件中。该文件被创建到服务器主机上，因此您必须拥有file权限，才能使用此语法；
输出不能是一个已存在的文件，防止文件数据被篡改；
你需要有一个登陆服务器的账号来检索文件。否则select ... into outfile 不会起任何的作用；
在unix中，该文件被创建后是可读的，权限由mysql服务器所拥有。这意味着，虽然你就可以读取该文件，但可能无法将其删除；

使用select... into outfile 语句导出数据；
select * from runoob into outfile '/tmp/runoob.txt';

导出表作为原始数据

mysqldump是mysql用于转存储数据库的实用程序。它主要产生一个sql脚本，其中包含从头重新创建数据库所必须的命令 create table insert等；

将数据表runoob_tbl 导出到/tmp目录中：
mysqldump -u root -p --no-create-info \ --tab=/tmp runoob runoob_tbl

导出sql格式的数据

mysqldump -u root -p runoob runoob_tbl > dum.txt

导出整个数据库的数据，可以使用一下命令：
mysqldump -u root -p runoob > database_dump.txt

备份所有数据库，可以使用以下命令：
mysqldump -u roo -p -- all-databases >database_dump.txt

--all-databases选项在mysql 3.23.12 及以后版本加入。

将数据表及数据库拷贝至其他主机

如果你需要将数据拷贝至其他的mysql服务器上，你可以在mysql命令中指定数据库名及数据表。
在源主机上执行以下命令，将数据备份至dump.txt文件中：
mysqldump -u root -p database_name table_name >dump.txt

如果你需要将备份的数据库导入到mysql服务器中，可以使用以下命令，使用一下命令你需要确认数据库已经创建；
mysql -u root -p database_name < dump.txt

你也可以使用以下命令将导出的数据直接导入到远程的服务器上，但请确保两台服务是相通的，是可以相互访问的：
mysqldump -u root -p database_name \ | mysql -h other-host.com database_name;
以上命令中使用了管道来将导出的数据导入到指定的远程主机上；

如果需要将远程服务器的数据拷贝到本地，也可以在mysqldump命令中指定远程服务器的ip、端口及数据库名。在源主机上执行以下命令，将数据备份到dump.txt文件中；
mysqldump -h other-host.com -P port -u root -p database_name > dump.txt

mysql函数

mysql字符串函数

字符串函数

mysql 数字函数

数字函数

mysql日期函数

日期函数

mysql 高级函数

高级函数