mysql tode_MySQL常用命令基础操作

最新推荐文章于 2022-04-30 12:32:41 发布

武者麥斯

最新推荐文章于 2022-04-30 12:32:41 发布

阅读量418

点赞数

文章标签： mysql tode

本文链接：https://blog.csdn.net/weixin_33369808/article/details/113298366

版权

MySQL启动与更改密码

mysql启动基本原理说明：

/etc/init.d/mysqld是一个shell启动脚本，启动后最终会调用，mysqld_safe脚本，最后调用mysqld服务启动mysql，我们编辑/etc/init.d/mysqld，可以看到脚本，启动俩个进程mysqld和mysqld_safe，一般故障的时候我们用mysqld_safe来启动，

关闭mysql

1 mysqladmin - uroot -p密码 shut down

2 /etc/init.d/mysqld stop

3 kill USR2`cat path/pid`

优雅的关闭mysql但是不建议用killall杀掉所有的mysql进程，这样会导致mysql数据库起不来，所以网友遇到这样情况也很多，

我们登陆mysql后想分清出那个是正式环境那个是测试环境，

命令行修改登陆提示符

mysql> prompt\u@king\s->PROMPTset to '\u@king\s->'

配置文件修改登陆提示符

在my.cnf配置文件中[mysql]模块下添加如下内容，保存后，无需重启myysql，退出当前的session，重新登陆

[mysql]

prompt=\\u@king\s->

登陆mysql

[root @king~]# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection idis 2Server version:5.5.40MySQL Community Server (GPL)

Oracleis a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type'help;' or '\h' for help. Type '\c'to clear the current input statement.

mysql>

更改root密码

1 mysql> update user set password=password('123456') where user='root' and host='localhost'; #password('12345')是指定一个函数2 Query OK, 1 row affected (0.00sec)3 Rows matched: 1 Changed: 1 Warnings: 0

4 mysql>flush privileges;　　　　　　　　　　　　　　#刷新，没刷新前是在内存里面5 Query OK, 0 rows affected (0.17sec)6 mysql>

说明，修改密码都需要刷新一下哦，linux找回mysql root用户密码

单实例mysql修改丢失root方法

1，首先停止mysql

/etc/init.d/mysql stop

2，使用--skip-grant-tables启动mysql，忽略授权登陆验证

1 mysqld_safe --user=mysql --skip-grant-tables --skip-networking &

2 mysql -u root -p　　　　　　　　#说明-p登陆时密码是空，也可以不加-p，亲测哦(#^.^#)

3 update mysql.user set password=password("newpassword") where user='root'and host='localhost';4 mysql>flush privileges;

5 mysql>quit

6 # /etc/init.d/mysql restart

7 # mysql -uroot -p

8 enter password:

9 mysql>

多实例mysql修改丢失root方法

1关闭mysql

killall mysqld

2启动时加--skip-grant-tables参数

mysql_safe --defaults-file=/data/3306/my.cnf --skip-grant-tables& mysql -u root -p -S /data/3306/mysql.sock

3修改密码

mysql>update mysql.user set password=password("newpassword") where user='root'and host='localhost';

mysql> flush privileges;

Query OK, 0 rows affected (0.03 sec)

SQl的分类

SQl结构化查询语言包含6个部分

数据查询语言(DQL(data query language))，作用从表中获取数据，关键字select，

数据操作语言(DMl(data manipulation language))作用处理表中的数据insert ，update，delete

事务处理语言(TPL)关键字begin，commit和 rollback

数据控制语言(DCl)grant(授权) 和revoke

数据定义语言(DDl)create和drop在数据库中创建新表或删除表alter

指针控制语言(CCl) declare cursor， fetch into 和update where current用于对一个或多个表单独行的操作

查看数据库

show databases;或show database link '';或select database;

1 mysql>show databases;

2 +--------------------+

3 | Database |

4 +--------------------+

5 | information_schema |

6 | mysql |

7 | performance_schema |

8 | student |

9 | test |

10 +--------------------+

11 8 rows in set (0.01 sec)

创建数据库

命令语法：create database 注意库名字不能数字开头

1 mysql>create database king;2 Query OK, 1 row affected (0.01sec)3

4 mysql>show databases;5 +--------------------+

6 | Database |

7 +--------------------+

8 | information_schema |

9 | king |

10 | mysql |

11 | performance_schema |

12 | student |

13 | test |

14 +--------------------+

15 6 rows in set (0.01sec)16

17 mysql>show create database king\G18 *************************** 1. row ***************************

19 Database: king20 Create Database: CREATE DATABASE `king` /*!40100 DEFAULT CHARACTER SET utf8*/

21 1 row in set (0.00sec)22

23 mysql>

创建一个指定字符集的数据库

1 mysql>CREATE DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; #红色指定编码，蓝色校验规则。2 Query OK, 1 row affected (0.01 sec)

删除数据库

drop database

1 mysql>drop database test;2 Query OK, 1 row affected (0.07sec)3

4 mysql>show databases;5 +--------------------+

6 | Database |

7 +--------------------+

8 | information_schema |

9 | king |

10 | mysql |

11 | performance_schema |

12 | student |

13 +--------------------+

14 5 rows in set (0.01 sec)

连接数据库

命令：use 相当于linux下的cd切换目录的命令，use是切换数据库

1 mysql>use king;2 Database changed3 mysql> selectdatabase(); #查看当前的数据库，带()相当于函数，4 +------------+

5 | database() |

6 +------------+

7 | king |

8 +------------+

9 1 row in set (0.00sec)

查看数据库

select database ();相当于linux下的pwd

1 mysql> selectversion();　　　　#查看当前的版本2 +-----------+

3 | version() |

4 +-----------+

5 | 5.5.40 |

6 +-----------+

7 1 row in set (0.06sec)8 mysql> selectuser();　　　　　　#查看当前的用户9 +----------------+

10 | user() |

11 +----------------+

12 | root@localhost |

13 +----------------+

14 1 row in set (0.04sec)15 mysql> selectnow();　　　　　　#查看当前的时间16 +---------------------+

17 | now() |

18 +---------------------+

19 | 2018-11-02 19:26:39 |

20 +---------------------+

21 1 row in set (0.02sec)22 mysql>

表操作

创建表并查看

create table ();

1 mysql> create table im(id int(3) not null, name varchar(20) not null default'QQ'　　　　#创建表id为int类型，name为varchar

2 );3 Query OK, 0 rows affected (0.59sec)4 mysql> show tables fromking;　　　　　　　　　　　　　　#从king数据库中查看表5 +----------------+

6 | Tables_in_king |

7 +----------------+

8 | im |

9 +----------------+

10 1 row in set (0.04sec)11 mysql>desc im;　　　　　　　　　　　　　　　　　　　　#查看表结构12 +-------+-------------+------+-----+---------+-------+

14 +-------+-------------+------+-----+---------+-------+

15 | id | int(3) | NO | | NULL | |

16 | name | varchar(20) | NO | | QQ | |

17 +-------+-------------+------+-----+---------+-------+

18 2 rows in set (0.22sec)19 mysql>

show colums from im;查看表结构

1 mysql>show create table im \G;　　　　　　　　#\G Send command to mysql server，display result vertically向MySQL服务器发送命令，垂直显示结果2 *************************** 1. row ***************************

3 Table: im4 Create Table: CREATE TABLE `im` (5 `id` int(3) NOT NULL,6 `name` varchar(20) NOT NULL DEFAULT 'QQ'

7 ) ENGINE=InnoDB DEFAULT CHARSET=utf88 1 row in set (0.64 sec)

mysql表的字段类型

列类型

需要的存储量

tinyint

1字节

smallint

2个字节

mediumint

3个字节

int

4个字节

integer

4个字节

bigint

8个字节

float(X)

4如果x<=24或8如果25<=X=53

float

4个字节

double

8个字节

double precision

8个字节

real

8个字节

decimal(M，D)

M字节(D+2，如果m

numeric(M，D)

M字节(D+2，如果m

为表的字段创建索引

创建主键索引，查询数据库，按主键查询是最快的，每一个表只有一个主键列，但是可以有多个普通的索引列，主键列要求列的内容必须唯一，而索引列不要求内容必须唯一。

create table student(

id int (4) not null auto_increment,

name char(20) not null,

age tinyint(2) not null default'0',

dept varchar(16) default null,

primary key (id),

key index_name(name));

1 mysql> create table student( id int (4) not null auto_increment, name char(20) n2 ot null, age tinyint(2) not null default'0',dept varchar(16) default null,primar3 y key (id), key index_name(name));4 Query OK, 0 rows affected (0.18 sec)

auto_increment自增，primary key (id), 主键，key index_name(name));普通索引，

1 mysql>desc student;　　　　　　　　#查看创建的student表2 +-------+-------------+------+-----+---------+----------------+

4 +-------+-------------+------+-----+---------+----------------+

5 | id | int(4) | NO | PRI | NULL | auto_increment |

6 | name | char(20) | NO | MUL | NULL | |

7 | age | tinyint(2) | NO | | 0 | |

9 +-------+-------------+------+-----+---------+----------------+

10 4 rows in set (0.17 sec)

alter table student drop primary key ;　　　　　　删除主键，测试不行，

1 mysql>alter table student drop primary key ;2 ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

不正确的表定义；只能有一个自动COLUM它必须被定义为一个密钥

alter table student change id id int primary key auto_increment; 如果创建表忘记添加主键了，就执行这个。

创建普通索引分为唯一索引和普通索引

alter table student drop index index_name; # index固定索引，index_name是你创建表的时候的索引

alter table student add index index_name(name);

create index index_name on student (name(8)); # index固定索引(固定写法)，index_name 随便写但要见名知意， on student 在哪个表上(student)8就是前8个字符创建索引。

create index index_name_dept on student (name，dept)

按条件列查询数据时，联合索引是有前缀生效特性的 index(a，b，c)仅a，ab，abc三个查询条件列可以走索引

查看表的索引

1 mysql> show index fromstudent\G;2 *************************** 1. row ***************************

3 Table: student4 Non_unique: 0

5 Key_name: PRIMARY6 Seq_in_index: 1

7 Column_name: id8 Collation: A9 Cardinality: 0

10 Sub_part: NULL11 Packed: NULL12 Null:13 Index_type: BTREE14 Comment:15 Index_comment:16 *************************** 2. row ***************************

17 Table: student18 Non_unique: 1

19 Key_name: index_name20 Seq_in_index: 1

21 Column_name: name22 Collation: A23 Cardinality: 0

24 Sub_part: NULL25 Packed: NULL26 Null:27 Index_type: BTREE28 Comment:29 Index_comment:30 2 rows in set (0.00 sec)

创建唯一索引

create unique index uni on student(name);

1 mysql>create unique index uni on student (name);2 Query OK, 0 rows affected (0.09sec)3 Records: 0 Duplicates: 0 Warnings: 0

4 mysql>desc student;5 +-------+-------------+------+-----+---------+----------------+

7 +-------+-------------+------+-----+---------+----------------+

8 | id | int(4) | NO | PRI | NULL | auto_increment |

9 | name | char(20) | NO | UNI | NULL | |

10 | age | tinyint(2) | NO | | 0 | |

12 +-------+-------------+------+-----+---------+----------------+

13 4 rows in set (0.02 sec)

要在表的列上创建索引

索引会加快查询的速度，但是会影响更新的速度

索引不是越多越好，要在频繁查询的where后的条件列上创建索引

小表或唯一值极少的列上不建索引，要在带包以及不同内容多的列上创建索引

往表中插入数据

create table test(

id int(4) not null auto_increment,

name char(20) not null,

primary key (id)

);

1 mysql> create table test(id int(4) not null auto_increment, name char(20) not n2 ll,primary key (id));3 Query OK, 0 rows affected (0.01sec)4 mysql>show tables;5 +----------------+

6 | Tables_in_king |

7 +----------------+

8 | student |

9 | test |

10 +----------------+

11 2 rows in set (0.00sec)12 mysql> insert into test (id,name) values(1,'boy'); #插入数据，主键自增我们直接插name，insert into test(name) values('new');13 Query OK, 1 row affected (0.05sec)14 mysql> select *fromtest;15 +----+------+

16 | id | name |

17 +----+------+

18 | 1 | boy |

19 +----+------+

20 1 row in set (0.04 sec)

mysql> delete fromtest;

Query OK,1 row affected (0.05sec)

mysql> select *fromtest;

Emptyset (0.00sec)

mysql> insert into test (id,name) values(1,'boy'),(2,'new');

Query OK,2 rows affected (0.00sec)

Records:2 Duplicates: 0 Warnings: 0

查询数据

select *from 表名;

1 mysql> select *fromtest;2 +----+------+

3 | id | name |

4 +----+------+

5 | 1 | boy |

6 | 2 | new |

7 +----+------+

8 2 rows in set (0.47 sec)

指定条件查询

1 mysql> select *from test where id =1;　　　　#根据id查询2 +----+------+

3 | id | name |

4 +----+------+

5 | 1 | boy |

6 +----+------+

7 1 row in set (0.09 sec)

1 mysql> select *from test where name='new';　　　　#根据name查询，字符串需要带引号2 +----+------+

3 | id | name |

4 +----+------+

5 | 2 | new |

6 +----+------+

7 1 row in set (0.03 sec)

多个条件查询and区交集，or取并集。排序 order by id desc(正序)，sec(倒序)

1 mysql> select *from test where name='new'or id=1;2 +----+------+

3 | id | name |

4 +----+------+

5 | 1 | boy |

6 | 2 | new |

7 +----+------+

8 2 rows in set (0.08 sec)

explain查看索引、

1 mysql> explain select *from test where name='new'\G;2 *************************** 1. row ***************************

3 id: 1

4 select_type: SIMPLE5 table: test6 type: ALL7 possible_keys: NULL8 key: NULL9 key_len: NULL　　　　#可以看到索引为空10 ref: NULL11 rows: 2

12 Extra: Using where

13 1 row in set (0.04 sec)

help explain;

修改表中指定条件固定列的数据

命令语法：update 表名 set 字段=新值，where 条件

1 mysql> update test set name='wangxinxia' where id=2;2 Query OK, 1 row affected (0.90sec)3 Rows matched: 1 Changed: 1 Warnings: 0

4 mysql>desc test;5 +-------+----------+------+-----+---------+----------------+

7 +-------+----------+------+-----+---------+----------------+

8 | id | int(4) | NO | PRI | NULL | auto_increment |

10 +-------+----------+------+-----+---------+----------------+

11 2 rows in set (0.25sec)12 mysql> select* fromtest;13 +----+------------+

14 | id | name |

15 +----+------------+

16 | 1 | boy |

17 | 2 | wangxinxia |

18 +----+------------+

19 2 rows in set (0.00 sec)

truncate table test;和delete from test的区别

truncate 更快，清空物理文件

delete逻辑清楚，按行删

删除数据

1 mysql> delete from student where id=3;2 Query OK, 1 row affected (0.30sec)3 mysql>show tables;4 +----------------+

5 | Tables_in_king |

6 +----------------+

7 | student |

8 | test |

9 +----------------+

10 2 rows in set (0.07sec)11 mysql> select *from student;12 +----+----------+-----+-------+

13 | id | name | age | dept |

14 +----+----------+-----+-------+

15 | 1 | zhangsan | 20 | yanfa |

16 | 2 | zhaoliu | 21 | dba |

17 +----+----------+-----+-------+

增删改表的字段

语法alter table 表名 add sex char(4)；

1 mysql> alter table student add sex char (4);2 Query OK, 2 rows affected (0.54sec)3 Records: 2 Duplicates: 0 Warnings: 0

4 mysql>desc student;5 +-------+-------------+------+-----+---------+----------------+

7 +-------+-------------+------+-----+---------+----------------+

8 | id | int(4) | NO | PRI | NULL | auto_increment |

9 | name | char(20) | NO | UNI | NULL | |

10 | age | tinyint(2) | NO | | 0 | |

12 | sex | char(4) | YES | | NULL | |

13 +-------+-------------+------+-----+---------+----------------+

14 5 rows in set (0.20 sec)

alter table 表名 add qq varchar(10)first；

1 mysql> alter table student add qq varchar(10) first;2 Query OK, 2 rows affected (0.06sec)3 Records: 2 Duplicates: 0 Warnings: 0

4 mysql>desc student;5 +-------+-------------+------+-----+---------+----------------+

7 +-------+-------------+------+-----+---------+----------------+

8 | qq | varchar(10) | YES | | NULL | |

9 | id | int(4) | NO | PRI | NULL | auto_increment |

10 | name | char(20) | NO | UNI | NULL | |

11 | age | tinyint(2) | NO | | 0 | |

13 | sex | char(4) | YES | | NULL | |

14 +-------+-------------+------+-----+---------+----------------+

15 6 rows in set (0.02 sec)

rename table 原表to新表

1 mysql>show tables;2 +----------------+

3 | Tables_in_king |

4 +----------------+

5 | student |

6 | test |

7 +----------------+

8 2 rows in set (0.00sec)9 mysql>rename table test to test1;10 Query OK, 0 rows affected (0.05sec)11

12 mysql>show tables;13 +----------------+

14 | Tables_in_king |

15 +----------------+

16 | student |

17 | test1 |

18 +----------------+

19 2 rows in set (0.00 sec)

alter table test1 rename to test；

mysql插入数据解决乱码问题

set names 库的字符集，这种方法是临时的每次进来之前都需要执行

source test.sql在这个文件中插入set names 库字符集

对已有数据，需要把数据导出去，重新建库建表，在导进来。

创建/查看/删除mysql系统的用户

语法：

create user '用户名'@'主机名' identified by '用户名';　　创建用户

select host,user from mysql.user; 查看用户

drop user 用户名@'%';注意可以是单或者双引号，但不能不加，删除用户

delete from mysql.user where user='用户名' and host=‘@后面指定的主机名’;

1 mysql> create user 'usrabc'@'%' identified by 'usrabc'; #创建usrabc用户2 Query OK, 0 rows affected (0.49sec)3 mysql> select host,user frommysql.user;　　　　　　　　　#查看所有用户4 +-----------+--------+

5 | host | user |

6 +-----------+--------+

7 | % | usrabc |

8 | localhost | root |

9 +-----------+--------+

10 2 rows in set (0.00sec)11 mysql> drop user usrabc@'%';　　　　　　　　　　　　#删除usrabc用户12 Query OK, 0 rows affected (0.00 sec)

处理完用户最好刷新一下权限，flush privileges;

创建mysql用户及赋予用户权限

通过help grant 查看帮助，比较常用的创建用户的方法是：CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass'

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass'

GRANT ALL ON db1.* TO 'jeffrey'@'localhost'　　　　　　　　　　#这俩命令是先用create创建用户，然后在授权GRANT SELECT ON db2.invoice TO'jeffrey'@'localhost'GRANT USAGE ON*.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90

通过grant命令创建用户并授权，命令语法：grant all privileges on dbname.* to username@localhost identified by 'passwd';

grant

all privileges

on dbname.*

to username@localhost

identified by 'passwd'

授权命令

对应权限

目标：库和表

用户名和客户端主机

用户密码

1 mysql> grant all privileges on king.* to liang@localhost identified by '123456';2 #创建liang用户，对king库具备所有权限，允许从localhost主机登陆管理数据库，密码是123456.3 Query OK, 0 rows affected (0.01sec)4 mysql>flush privileges;5 Query OK, 0 rows affected (0.96 sec)

1 mysql> show grants forliang@localhost;　　　　　　　　#查看权限2 +-------------------------------------------------------------------------------

3 -------------------------------+

4 | Grants forliang@localhost5 |

6 +-------------------------------------------------------------------------------

7 -------------------------------+

8 | GRANT USAGE ON *.* TO 'liang'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74

9 329105EE4568DDA7DC67ED2CA2AD9'|

10 | GRANT ALL PRIVILEGES ON `king`.* TO 'liang'@'localhost'

11 |

12 +-------------------------------------------------------------------------------

13 -------------------------------+

14 2 rows in set (0.07 sec)

create和grant配合

1 mysql> create user zh@localhost identified by '123456';2 Query OK, 0 rows affected (0.04sec)3 mysql> grant all on king.*to zh@localhost;4 Query OK, 0 rows affected (0.00sec)5 mysql>flush privileges;6 Query OK, 0 rows affected (0.00 sec)

授权局域网内主机远程连接数据库grant all privileges on king.* to liang@10.0.0.% identified by '123456';把localhost改为ip就可以了。连接，mysql -uliang -p123456 -h10.0.0.%

1 mysql> show grants forzh@localhost;　　　　　　　　#查看zh用户权限2 +-------------------------------------------------------------------------------

3 ----------------------------+

4 | Grants forzh@localhost5 |

6 +-------------------------------------------------------------------------------

7 ----------------------------+

8 | GRANT USAGE ON *.* TO 'zh'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329

9 105EE4568DDA7DC67ED2CA2AD9'|

10 | GRANT ALL PRIVILEGES ON `king`.* TO 'zh'@'localhost'

11 |

12 +-------------------------------------------------------------------------------

13 ----------------------------+

14 2 rows in set (0.16sec)15

16 mysql> revoke insert on king.* fromzh@localhost;　　　#更改权限17 Query OK, 0 rows affected (0.04sec)18

19 mysql> show grants forzh@localhost;20 +-------------------------------------------------------------------------------

21 --------------------------------------------------------------------------------

22 -------------------------------------------------------------+

23 | Grants forzh@localhost24

25 |

26 +-------------------------------------------------------------------------------

27 --------------------------------------------------------------------------------

28 -------------------------------------------------------------+

29 | GRANT USAGE ON *.* TO 'zh'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329

30 105EE4568DDA7DC67ED2CA2AD9'31 |

32 |GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE T33 EMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, A34 LTER ROUTINE, EVENT, TRIGGER ON `king`.* TO 'zh'@'localhost' |

35 +-------------------------------------------------------------------------------

36 --------------------------------------------------------------------------------

37 -------------------------------------------------------------+

38 2 rows in set (0.06 sec)

1 mysql -uroot -p123456 -e "show grants for zh@localhost;"|grep -i grant　　#用linux中grep命令过滤需要的权限2 Grants forzh@localhost3 GRANT USAGE ON *.* TO 'zh'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329

4 105EE4568DDA7DC67ED2CA2AD9'5 GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE T6 EMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, A7 LTER ROUTINE, EVENT, TRIGGER ON `king`.* TO 'zh'@'localhost'

查看mysql的ALL PRIVILEGES权限mysql -uroot -p123456 -e "show grants for zh@localhost;"|grep -i grant|tail -1|tr ‘，’ ‘\n’ >a.txt

我们在授权用户最小的满足业务需求的权限，而不是一味的授权ALl PRIVILEGES。

武者麥斯

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql tode_MySQL常用命令基础操作

MySQL启动与更改密码mysql启动基本原理说明：/etc/init.d/mysqld是一个shell启动脚本，启动后最终会调用，mysqld_safe脚本，最后调用mysqld服务启动mysql，我们编辑/etc/init.d/mysqld，可以看到脚本，启动俩个进程mysqld和mysqld_safe，一般故障的时候我们用mysqld_safe来启动，关闭mysql1 mysqladmin ...
复制链接

扫一扫