1.mysql---常用sql（1）

1、查询数据库密码策略

mysql> show variables like '%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   |
+---------------------------------------+-------+
7 rows in set (0.01 sec)

2、修改密码

mysql> alter user root@'localhost' identified by '123456';

3、查询数据库当前链接数

mysql> show global status like 'Threads_connected';
mysql> show full processlist;

4、数据库的创建、插入数据、删除

//创建数据库(每创建一个数据库，在/var/lib/mysql/里创建一个文件夹)
mysql> create database database_1;
//查看数据库
mysql> show databases;
//使用数据库
mysql> use database_1;
//创建表
mysql> create table table_1(id int(2),name varchar(10),mail varchar(2));
//查看表的结构
mysql> desc table_1;
//查看表数据
mysql> select * from table_1;
//统计表数据行数
mysql> select count(*) from table_1;
//插入数据
mysql> insert into table_1(id,name,mail) values(1,'cc','cc@qin.com');
//可以一次插入多行数据
mysql> insert into table_1 values(5,'cc','cc@qin.com'),(6,'cx','cx@qin.com');
//删除一行数据
mysql> delete from table_1 where id=5;
//修改数据
mysql> update table_1 set name='ed' where id=2;
//直接调用别的表的结构
mysql> create table table_2 like table_1;
//也可以直接插入别的表的数据
mysql> insert into table_2 select * from table_1;
mysql> select * from table_2;
//直接把别的表的结构和数据都拷过来
mysql> create table table_3 as select * from table_1;
mysql> select * form table_3;
//修改表的结构，插入一列
mysql> alter table table_1 add newlist varchar(20);
mysql> select * from table_1;
//删除newlist这一列
mysql> alter table table_1 drop newlist;
//把firstlist这一列添加到第一列
mysql> alter table table_1 add firstlist varchar(20) first;
//添加afterid这一列到id的后面
mysql> alter table table_1 add afterid varchar(20) after id;
//修改字段类型
mysql> alter table table_1 modify name varchar(20);
//修改字段名（当字段类型和原来不同时，也可修改字段类型）
mysql> alter table table_1 change id new_id int(2);
//修改表名
mysql> alter table table_1 rename new_table;
//删除表，逻辑删除，删除的行数据不释放空间
mysql> delete from table_2;
//和delete很像，物理删除，通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放，所以删除不能回滚
mysql> truncate table_3;
//drop直接把表的数据和结构都给删了
mysql> drop table table_2;
//删除数据库
mysql> drop database database_1;

5、索引的创建和删除

//查看索引
mysql> SHOW INDEX FROM table_1;
mysql> SHOW CREATE TABLE table_1;

//建表时指定索引
mysql> create table table_1 (
	ID INT
	,NAME VARCHAR(20)
	,INDEX idx_name(name)
);

//已有表指定索引
mysql> create INDEX idx_name on table_1(name);

//删除索引
mysql> drop INDEX idx_name on table_1;

6、主键的创建和删除

//查看主键
mysql> SHOW CREATE TABLE my_table;

//建表时指定主键
mysql> CREATE TABLE my_table (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

//已有表指定主键
mysql> ALTER TABLE my_table ADD PRIMARY KEY (id);

//删除主键
 mysql> ALTER TABLE my_table drop PRIMARY KEY;

7、外键的创建和删除

//查看外键
mysql> SHOW CREATE TABLE my_table;

//添加外键
mysql> alter table my_table add constraint 外键名 foreign key (id) references table_1(id) on update cascade on delete cascade;
-- on update cascade 同步更新
-- on delete cascade 同步删除

//删除外建
mysql> alter table my_table drop foreign key 外键名;

8、 查询数据库的大小

//information_schema 数据库存放了其他的数据库的信息
mysql> use information_schema;
//查看所有数据库大小
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES;
//查看每个库的大小
mysql> select table_schema,sum(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 as total_mb from information_schema.TABLES group by table_schema;

9、查看指定数据库大小

mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='数据库名';

10、查看指定数据库的指定表单的大小

mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema=数据库名' and table_name='表名';

11、查询数据库有哪些函数

mysql> show function status\G;

12、查找某个表在哪个库

mysql> SELECT table_schema FROM information_schema.TABLES WHERE table_name = '表名';

13、查看数据库当前连接数,可以看到ip,谁连接了,连接了多少个,处于什么状态,连接时长等等

mysql> SHOW FULL processlist;

14、查看数据库配置的最大连接数

mysql> show variables like '%max_connections%';

15、查看数据库所有配置

mysql> show global variables;

16、修改存储引擎

//列出可用的存储引擎
mysql> show engines;
mysql> alter table 库名.表名 ENGINE = InnoDB;
//创建时指定存储引擎
mysql> create table 库名 (name char(4)) engine=innodb;

17、无主键表信息列表

mysql> select distinct a.TABLE_SCHEMA,a.TABLE_NAME,b.TABLE_NAME from information_schema.COLUMNS a left join    (    select distinct TABLE_SCHEMA,TABLE_NAME  from information_schema.COLUMNS    where COLUMN_KEY = 'PRI'    ) b on a.TABLE_SCHEMA = b.TABLE_SCHEMA and a.TABLE_NAME = b.TABLE_NAME    where b.TABLE_NAME is null    and a.TABLE_SCHEMA not in ('information_schema','mysql','performance_schema','sys');

18、普通用户的创建、授权：

//创建一个数据库用户cc，密码为abc123.
mysql> create user ‘cc’@‘localhost’ identified by ‘abc123.’; 
//授予cc用户对名为mysql的数据库查询、更新、删除、插入权限
mysql> grant select,update,delete,insert ON mysql.* to cc@'localhost';
//授予用户库所有权限
mysql> grant all privileges on 库名.* to ‘用户名’@'%' identified by "密码";
//移除权限
mysql> revoke select,update,delete,insert on mysql.* from cc@'localhost'; 
//查看用户cc在localhost的权限
mysql> show grants for cc@'localhost'; 
//删除用户（默认删除的是'cc'@'%'这个用户，如果要删除'cc'@'localhost'，使用drop删除时需要加上drop user 'cc'@'localhost'）
mysql> drop user cc;
mysql> delete from user where user='cc' and host='localhost';

19、查看当前锁状态

mysql> show status like 'table_lock%';