MYSQL几个基础知识

一、MYSQL默认目录结构

源码安装mysql :
​
mysql 主配置目录:/usr/local/mysql55
mysql 数据目录：/data/mysql
mysql 命令目录：/usr/local/mysql55/bin/
mysql 配置文件：/usr/local/mysql55/my.cnf
mysql 启动文件：/usr/local/mysql55/support-files/mysql.server
或者是/etc/init.d/mysqld
mysql 日志文件：/data/mysql
​
yum 安装mariadb程序：

mariadb 主配置目录:/var/lib/mysql
mariadb 数据目录：/var/lib/mysql
mariadb 命令目录：/usr/bin
mariadb 配置文件：/etc/my.cnf
mariadb 启动文件：/usr/bin
mariadb 日志文件：/var/log/mariadb

二、登录MSYQL
源码安装
登录方法：/usr/local/mysql57/bin/mysql -u root -p
YUM安装
登录方法：mysql –u root –p
以上方法通过套接字连接登录
如果使用tcp登录：
/usr/local/mysql57/bin/mysql -h127.0.0.1 -uroot -p

可以通过status命令查看到登录方式

三、基本命令
1、查看状态：status;
2、查看数据库：show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> 

3、创建数据库 create database test;

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> 

4、创建数据表：create table user…

mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table user(id int(10) auto_increment primary key,name varchar(20),job varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user           |
+----------------+
1 row in set (0.00 sec)

mysql> desc user;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(10)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| job   | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.03 sec)

5、增加字段：alter table user add

mysql> alter table user add age int(10);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(10)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| job   | varchar(10) | YES  |     | NULL    |                |
| age   | int(10)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> 

6、删除字段：alter table user drop

mysql> alter table user drop age;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(10)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| job   | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql>

7、新增数据，如全字段新增数据：insert into user values

mysql> select * from user;
Empty set (0.00 sec)

mysql> insert into user values(1,"xiaoming","IT","28");
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----+----------+------+------+
| id | name     | job  | age  |
+----+----------+------+------+
|  1 | xiaoming | IT   |   28 |
+----+----------+------+------+
1 row in set (0.00 sec)

mysql>

8、修改表数据：update user set…

mysql> insert into user values(1,"xiaoming","IT","28");
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----+----------+------+------+
| id | name     | job  | age  |
+----+----------+------+------+
|  1 | xiaoming | IT   |   28 |
+----+----------+------+------+
1 row in set (0.00 sec)

mysql> update user set job ="QA" where name="xiaoming";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+----+----------+------+------+
| id | name     | job  | age  |
+----+----------+------+------+
|  1 | xiaoming | QA   |   28 |
+----+----------+------+------+
1 row in set (0.00 sec)

mysql> 

9、删除表数据： delete from user…

mysql> delete from user where name="xiaoming";
Query OK, 1 row affected (0.01 sec)

mysql> select * from user;
Empty set (0.00 sec)

mysql> 

四、修改密码
1、

#格式：mysql> set password for 用户名@localhost = password('新密码'); 
#范例：
mysql> set password for root@localhost = password('123');

2、直接在shell终端，不登录msyql修改

#格式：mysqladmin -u用户名 -p旧密码 password 新密码
#范例：
mysqladmin -uroot -p123456 password 123

3、登录msyql，直接修改user表。

如果忘记密码，请参考我的上面另篇文章处理方法。