mysql shell 回车换行_shell 环境下MySQL的基本操作指令总结

╰+哭是因爲堅強的太久メ 2023-01-12 07:54 306阅读 0赞

一、对数据库的基本操作

show databases;                      //列出数据库

use database\_name;               //使用database\_name数据库

create database data\_name;   //创建名为data\_name的数据库

drop database data\_name;     //删除一个名为data\_name的数据库

use dbname; status                 //查看数据库dbname的详细信息

alter database db\_name character set  utf8;  //修改数据库编码

show variables like '%dir%';    // 查看mysql相关存放目录

mysql -uroot -proot -e "select \* from test.item;"   \#在系统中使用mysql命令执行sql语句

二、对表的基本操作

1、列出所有表

show tables;

1.1、查询数据库 yf\_role 中有多少张表

SELECT COUNT(\*) TABLES, table\_schema FROM information\_schema.TABLES   WHERE table\_schema = 'yf\_role' GROUP BY table\_schema;

2、查看表的详细信息：

show table status where name = 'table\_name';

3、优化表格，将已删除的数据的空间释放出来(delete操作删除数据后，空间并没有释放，而是等待新数据去填充)，仅对MyISAM, BDB和InnoDB引擎表起作用。语句执行过程中，MySQL会锁定表。

optimize table  table\_name;

4、查询某个数据库某个表的字段信息

select \* from information\_schema.columns where TABLE\_SCHEMA='数据库' and TABLE\_NAME='表名'

5、查询data\_base.20171112\_login的所有字段名

select COLUMN\_NAME from information\_schema.columns where table\_schema='data\_base' and table\_name='20171112\_login'

6、创建一个名为tab\_name的新表

create tabletab\_name(

idint(10) not null auto\_increment primary key,

namevarchar(40),

pwdvarchar(40)

) charset=gb2312;

eg:CREATE TABLE\`test\` (

\`id\`int(11) NOT NULLAUTO\_INCREMENT,

\`name\`varchar(10) DEFAULT NULL,PRIMARY KEY(\`id\`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

//id自增的时候从7开始(表示前面已经有6行数据了)

CREATE TABLE \`test\` (

\`id\` int(11) NOT NULL AUTO\_INCREMENT,

\`name\` varchar(10) DEFAULT NULL,

PRIMARY KEY (\`id\`)

) ENGINE=MyISAM AUTO\_INCREMENT=7 DEFAULT CHARSET=latin1

7、删除名为tab\_name的数据表

drop table tab\_name

8、显示名为tab\_name的表的数据结构

describe tab\_name

show columns from tab\_name

9、删除表所有数据，不会重置自增的ID

delete from tab\_name;

truncate table  \`db\_name\`.\`tab\_name\`;    //截断表重置自增的id为0

10、插入与更新表内容

(1)插入

insert into table\_name (列1, 列2,...) values (值1, 值2,....)

insert into report.user\_live (date,pid,osid,level) values ('2017-07-26','3','0','2')

insert into 表名称 values (值1, 值2,....)

insert into report.user\_live  values ('2017-07-26','3','0','2')

insert into table\_name (列1, 列2,...) values (值1, 值2,....) on duplicate key update field=values(field)；    //field是字段名称，如果插入是，主键已经重复，则更新给出字段。

例如：insert into report.user\_live (date,pid,osid,level) values('2017-07-26','3','0','2') on duplicate key update level = values(level);

插入这行数字时，主键是(date,pid,osid)复合主键，并且该主键的数据已经存在且唯一，此时出入多一行数据，则键非主键level字段更新到对应的唯一主键对应的记录中。

往表table1插入表table2的数据,前提是两个表结构一致，或者要插入的字段一致

表字段相同的情况：INSERT INTO  dbname.table1  select  \*  FROM dbname.table2;

表字段不同的情况：INSERT INTO dbname.table1 (a,b,c,d,e,f,g) SELECT a,b,c,d,e, x as f,y as g FROM dbname.table2;

(2)更新

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

例如：UPDATE \`mydb\`.\`mytable\` SET \`myname\` = 'xst' WHERE \`id\` = '3';

以下实例将更新 id 为 3 的 content 字段值的 "C++" 替换为 "Python"：

update db.table set content=replace(content, 'C++', 'Python') where id='3';

三、修改表结构

修改表名

alter table tab\_name rename to new\_tab\_name;

重命名数据表

RENAME TABLE \`test\`.\`mytest\` TO \`test\`.\`mytest\_back\`;

修改字段名,必须为当前字段指定数据类型等属性，否则不能修改

alter table tab\_name change old\_col new\_col varchar(40);

用一个已存在的表来建新表，但不包含旧表的数据

create table new\_tab\_name like old\_tab\_name;

创建表table1复制表table2的结果

create  table  table1  like  table2;

新增字段

alter table tab\_name add col\_name varchar(20);

删除字段

alter table tab\_name drop col\_name;

修改字段属性,若加上not null则要求原字段下没有数据

alter table tab\_name modify col\_name varchar(40) not null;

新增索引

ALTER TABLE \`db\_name\`.\`tab\_name\` ADD INDEX \`index\_name\` (\`col\_name\`);

新增组合索引

ALTER TABLE \`db\_name\`.\`tab\_name\` ADD INDEX \`index\_name\` (\`id\`, \`name\`);

设置主键

alter table tab\_name add primary key (col\_name);

删除主键

alter TABLE tab\_name drop primary key (col\_name);

创建一个表复制另一个表的一些字段

create table newadmin as ( select username, password from admin );

修改数据库默认字符集

ALTER DATABASE db\_name DEFAULT CHARACTER SET character\_name \[COLLATE ...\];

修改表的默认字符集

ALTER TABLE tab\_name DEFAULT CHARACTER SET character\_name \[COLLATE...\];

例如：ALTER TABLE logtest DEFAULT CHARACTER SET utf8 COLLATE utf8\_general\_ci;

修改字段的字符集

ALTER TABLE tab\_name CHANGE c\_name c\_name CHARACTER SET character\_name \[COLLATE ...\];

例如：ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8\_general\_ci;

把表默认的字符集和所有字符列(CHAR,VARCHAR,TEXT)改为新的字符集

ALTER TABLE tbl\_name CONVERT TO CHARACTER SET character\_name \[COLLATE ...\]

例如：ALTER TABLE logtest CONVERT TO CHARACTER SET utf8 COLLATE utf8\_general\_ci;

四、用户与用户权限管理

1、赋予用户权限

方法一：先创建用户再赋予权限：

USE \`mysql\`;

CREATE USER 'backend'@'%' IDENTIFIED BY 'Back123end'; //用户名backend，密码Back123end

FLUSH PRIVILEGES;

grant select,update,insert,delete,create,index,show databases on \*.\* to 'backend'@'%';  //第一个 \* 符号代表所有数据库，第二个 \* 符号代表所有数据表，backend表示数据库用户名。%符号代表所有主机

GRANT CREATE, INSERT,SELECT,SHOWDATABASES,INDEX,ALTER,UPDATE ON \`db\_backend\`.\* TO 'backend’@'%';    //给任何主机上的backend用户赋予db\_backend数据库所有表的对应权限。

方法二：直接创建并赋予权限：

Grant all privileges on \*.\*  to  'backend'@'%' identified by '123456' with grant option;

2、查看mysql用户权限

方法一：直接查询mysql数据库的user表；

方法二： show grants for 你的用户;  或者  show grants for root@'localhost';

3、移除一个用户的权限

revoke insert,update,delete,alter,select ON \*.\* from 'xbb'@'localhost' IDENTIFIED BY '123';

revoke alter on \*.\*  from ‘backend’@’%’;

4、删除用户

drop user username;

5、更改mysql数据库root用户密码

UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE user='root';

在之前的版本中，密码字段的字段名是 password，5.7版本改为了 authentication\_string，所以修改语句如下：

update mysql.user set authentication\_string = password('test123'), password\_expired = 'N', password\_last\_changed = now() where user = 'root';

update mysql.user set plugin="mysql\_native\_password" where user='root';     //该字段默认为 "auth\_socket",不用密码就能登录该用户

FLUSH PRIVILEGES;

五、数据的备份与恢复

1.导出

shell：

mysqldump -u root -p dbname > test.sql    \#导出整个数据库，包括结构和数据

mysqldump -u root -pXXXXXX --no-data dbname  tablename > table.sql     //导出表结构

mysqldump -u root -p dbname  tablename > test.sql      //导出表结构和数据

mysqldump -u root -p -d –add-drop-table db\_name > test.sql   \#导出数据库结构，-d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table

mysql -h 127.0.0.1 -P 3306 -uroot -pXXXXXX -D dbname -e "select \* from table" > /tmp/test.xls

mysql --default-character-set=utf8 -uroot -pXXXXXX -e "select \* from db.table" > /tmp/test.txt

mysql --default-character-set=utf8 -uroot -pXXXXXX -e "select \* from db.table into outfile '/tmp/test.csv' fields terminated by ',' enclosed by '\\"' escaped by '\\\\\\' lines terminated by '\\n';"

mysql客户端：

show variables like '%secure%'; \#查询数据库配置的导出目录权限是否开启，若开启则使用secure\_file\_priv设置的目录(如果使用其他目录会报权限问题)

select \* from db.table into outfile '/tmp/file.txt'; \#导出的文件类型可为.xls，.csv，.txt等

select \* from db.table into outfile '/tmp/file.csv' character set gbk;

select \* from db.table into outfile '/tmp/file.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\\n';

2.导入

shell：

mysql -h127.0.0.1  -uroot -pXXXXXX < /tmp/test.sql

mysql-ib --default-character-set=utf8 -u root -pXXXXXX -e "load data infile '/tmp/test.csv' into table $db.$table character set UTF8 fields terminated by ',' enclosed by '\\"' escaped by '\\\\\\' lines terminated by '\\n';"

mysql客户端：

use dbname;  source /home/pt/test.sql

load data local infile '/tmp/data.txt' into table db.table;

load data local infile '/tmp/data.txt' into table db.table fields terminated by ',' optionally enclosed by '"' lines terminated by '\\n'

load data local infile '/tmp/data.txt' into table db.table (col1,col2); \#导入某个表的col1和col2两列的数据

load data infile '/tmp/data.txt' into table db.table ignore 1 lines; \#忽略文件头部的起始标题行

涉及的字段说明：

fields terminated by ',' -- 字段以逗号分隔

optionally enclosed by '"' -- 双引号包裹字段

lines terminated by '\\n' -- 换行符为'\\n'

其他：

因为msyql中的文件多是utf-8的编码，这个时候我们需要把utf8的编码转化为gbk的编码(从test1.xls转到test2.xls)：

iconv -c -f utf8 -t gbk test1.xls > test2.xls

六、分区操作

查看分区：SELECT PARTITION\_NAME,TABLE\_ROWS FROM INFORMATION\_SCHEMA.PARTITIONS WHERE TABLE\_NAME = 'table\_name';

删除分区：ALTER TABLE table\_name DROP PARTITION p\_name;

FLUSH TABLE;

七、其他

1.Show语句用法

show tables或show tables from database\_name; -- 显示当前数据库中所有表的名称。

2. show databases; -- 显示mysql中所有数据库的名称。

3. show columns from table\_name from database\_name; 或show columns from database\_name.table\_name; -- 显示表中列名称。

4. show grants for user\_name; -- 显示一个用户的权限，显示结果类似于grant 命令。

5. show index from table\_name; -- 显示表的索引。

6. show status; -- 显示一些系统特定资源的信息，例如，正在运行的线程数量。

7. show variables; -- 显示系统变量的名称和值。

8. show processlist; -- 显示系统中正在运行的所有进程，也就是当前正在执行的查询。大多数用户可以查看他们自己的进程，但是如果他们拥有process权限，就可以查看所有人的进程，包括密码。

9. show table status; -- 显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间。

10. show privileges; -- 显示服务器所支持的不同权限。

11. show create database database\_name; -- 显示create database 语句是否能够创建指定的数据库。

12. show create table table\_name; -- 显示创建表的语句。

13. show engines; -- 显示安装以后可用的存储引擎和默认引擎。

14. show innodb status; -- 显示innoDB存储引擎的状态。

15. show logs; -- 显示BDB存储引擎的日志。

16. show warnings; -- 显示最后一个执行的语句所产生的错误、警告和通知。

17. show errors; -- 只显示最后一个执行语句所产生的错误。

18. show \[storage\] engines; --显示安装后的可用存储引擎和默认引擎。

19.show create database db\_name;   //查看数据库编码

20.show create table tab\_name;    //查看数据表编码

21.show full columns from tab\_name;   //查看数据表字段编码

2.Infobright相关

将MySQL数据导出csv文件：

SELECT \* FROM \`20170913\_login\` INTO OUTFILE '/mydata/csv/20170913\_login.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\\n';

导入cs文件到Infobright中：

load data infile '/mydata/csv/20170913\_login.csv' into table \`login\` fields terminated by ',' optionally enclosed by '"' lines terminated by '\\n';

导入IB后，中文变成乱码的解决方法：

1、在IB中执行：show variables like "%char%";

mysql> show variables like "%char%";

\+--------------------------+-----------------------------------------------------+

| Variable\_name | Value |

\+--------------------------+-----------------------------------------------------+

| character\_set\_client | utf8 |

| character\_set\_connection | utf8 |

| character\_set\_database | latin1 |

| character\_set\_filesystem | binary |

| character\_set\_results | utf8 |

| character\_set\_server | latin1 |