1.mysql--常用sql（1）

1、查询数据库密码策略mysqlshowvariableslike%password%;----------------------------------------------|Variable_name|Value|----------------------------------------------|default_password_lifetime|360||disconnect_on_expired_password|ON||log_builtin_as_identified_by_password|OFF||mysql_native_password_proxy_users|OFF||old_passwords|0||report_password|||sha256_password_proxy_users|OFF|----------------------------------------------7rowsinset(0.01sec)2、修改密码mysqlalteruserrootlocalhostidentifiedby123456;3、查询数据库当前链接数mysqlshowglobalstatuslikeThreads_connected;mysqlshowfullprocesslist;4、数据库的创建、插入数据、删除//创建数据库(每创建一个数据库在/var/lib/mysql/里创建一个文件夹)mysqlcreatedatabasedatabase_1;//查看数据库mysqlshowdatabases;//使用数据库mysqlusedatabase_1;//创建表mysqlcreatetabletable_1(idint(2),namevarchar(10),mailvarchar(2));//查看表的结构mysqldesctable_1;//查看表数据mysqlselect*fromtable_1;//统计表数据行数mysqlselectcount(*)fromtable_1;//插入数据mysqlinsertintotable_1(id,name,mail)values(1,cc,ccqin.com);//可以一次插入多行数据mysqlinsertintotable_1values(5,cc,ccqin.com),(6,cx,cxqin.com);//删除一行数据mysqldeletefromtable_1whereid5;//修改数据mysqlupdatetable_1setnameedwhereid2;//直接调用别的表的结构mysqlcreatetabletable_2liketable_1;//也可以直接插入别的表的数据mysqlinsertintotable_2select*fromtable_1;mysqlselect*fromtable_2;//直接把别的表的结构和数据都拷过来mysqlcreatetabletable_3asselect*fromtable_1;mysqlselect*form table_3;//修改表的结构插入一列mysqlaltertabletable_1addnewlistvarchar(20);mysqlselect*fromtable_1;//删除newlist这一列mysqlaltertabletable_1dropnewlist;//把firstlist这一列添加到第一列mysqlaltertabletable_1addfirstlistvarchar(20)first;//添加afterid这一列到id的后面mysqlaltertabletable_1addafteridvarchar(20)afterid;//修改字段类型mysqlaltertabletable_1modifynamevarchar(20);//修改字段名当字段类型和原来不同时也可修改字段类型mysqlaltertabletable_1 change id new_idint(2);//修改表名mysqlaltertabletable_1renamenew_table;//删除表逻辑删除删除的行数据不释放空间mysqldeletefromtable_2;//和delete很像物理删除通过释放存储表数据所用的数据页来删除数据并且只在事务日志中记录页的释放所以删除不能回滚mysqltruncatetable_3;//drop直接把表的数据和结构都给删了mysqldroptabletable_2;//删除数据库mysqldropdatabasedatabase_1;5、索引的创建和删除//查看索引mysqlSHOWINDEXFROMtable_1;mysqlSHOWCREATETABLEtable_1;//建表时指定索引mysqlcreatetabletable_1(IDINT,NAMEVARCHAR(20),INDEXidx_name(name));//已有表指定索引mysqlcreateINDEXidx_nameontable_1(name);//删除索引mysqldropINDEXidx_nameontable_1;6、主键的创建和删除//查看主键mysqlSHOWCREATETABLEmy_table;//建表时指定主键mysqlCREATETABLEmy_table(idINTPRIMARYKEY,nameVARCHAR(50));//已有表指定主键mysqlALTERTABLEmy_tableADDPRIMARYKEY(id);//删除主键mysqlALTERTABLEmy_tabledropPRIMARYKEY;7、外键的创建和删除//查看外键mysqlSHOWCREATETABLEmy_table;//添加外键mysqlaltertablemy_tableaddconstraint外键名foreignkey(id)referencestable_1(id)onupdatecascadeondeletecascade;-- on update cascade 同步更新-- on delete cascade 同步删除//删除外建mysqlaltertablemy_tabledropforeignkey外键名;8、 查询数据库的大小//information_schema 数据库存放了其他的数据库的信息mysqluseinformation_schema;//查看所有数据库大小mysqlselectconcat(round(sum(DATA_LENGTH/1024/1024),2),MB)asdatafromTABLES;//查看每个库的大小mysqlselecttable_schema,sum(AVG_ROW_LENGTH*TABLE_ROWSINDEX_LENGTH)/1024/1024astotal_mbfrominformation_schema.TABLESgroupbytable_schema;9、查看指定数据库大小mysqlselectconcat(round(sum(DATA_LENGTH/1024/1024),2),MB)asdatafromTABLESwheretable_schema数据库名;10、查看指定数据库的指定表单的大小mysqlselectconcat(round(sum(DATA_LENGTH/1024/1024),2),MB)asdatafromTABLESwheretable_schema数据库名 and table_name表名;11、查询数据库有哪些函数mysqlshowfunctionstatus\G;12、查找某个表在哪个库mysqlSELECTtable_schemaFROMinformation_schema.TABLESWHEREtable_name表名;13、查看数据库当前连接数,可以看到ip,谁连接了,连接了多少个,处于什么状态,连接时长等等mysqlSHOWFULLprocesslist;14、查看数据库配置的最大连接数mysqlshowvariableslike%max_connections%;15、查看数据库所有配置mysqlshowglobalvariables;16、修改存储引擎//列出可用的存储引擎mysqlshowengines;mysqlaltertable库名.表名ENGINEInnoDB;//创建时指定存储引擎mysqlcreatetable库名(namechar(4))engineinnodb;17、无主键表信息列表mysqlselectdistincta.TABLE_SCHEMA,a.TABLE_NAME,b.TABLE_NAMEfrominformation_schema.COLUMNSaleftjoin(selectdistinctTABLE_SCHEMA,TABLE_NAMEfrominformation_schema.COLUMNSwhereCOLUMN_KEYPRI)bona.TABLE_SCHEMAb.TABLE_SCHEMAanda.TABLE_NAMEb.TABLE_NAMEwhereb.TABLE_NAMEisnullanda.TABLE_SCHEMAnotin(information_schema,mysql,performance_schema,sys);18、普通用户的创建、授权//创建一个数据库用户cc密码为abc123.mysqlcreateuser‘cc’‘localhost’ identifiedby‘abc123.’;//授予cc用户对名为mysql的数据库查询、更新、删除、插入权限mysqlgrantselect,update,delete,insertONmysql.*tocclocalhost;//授予用户库所有权限mysqlgrantallprivilegeson库名.*to‘用户名’%identifiedby密码;//移除权限mysqlrevokeselect,update,delete,insertonmysql.*fromcclocalhost;//查看用户cc在localhost的权限mysqlshowgrantsforcclocalhost;//删除用户默认删除的是cc%这个用户如果要删除cclocalhost使用drop删除时需要加上drop user cclocalhostmysqldropusercc;mysqldeletefromuserwhereuserccandhostlocalhost;19、查看当前锁状态mysqlshowstatusliketable_lock%;