MySql 常用命令

# 如何用源数据库.sql文件,初始化新创建的数据库
https://blog.csdn.net/younne0915/article/details/79616219

# 初识化 mysql 使用该命令后仍然报错,通过/var/log/messages 根据日志排错即可
[root@1-3 lib]# /usr/bin/mysql_install_db --user=mysql --datadir=/data/mysql/

# centos6.8 卸载mysql
[root@localhost ~]# yum remove mysql mysql-server mysql-libs mysql-server
[root@localhost ~]# find / -name mysql -exec rm -rf {} \;
[root@localhost ~]# rpm -qa | grep mysql # (查询出来的东东yum remove掉)
[root@localhost ~]# rpm -e mysql # 相关文件
# centos6.8 yum 安装 mysql:客户端 mysql-server:服务端 mysql-devel:mysql-devel:开发平台、开发包、开发平台
yum -y install mysql mysql-server mysql-devel

# 设置 root 密码登陆
[root@localhost ~]# mysqladmin -u root password '123456'
[root@localhost ~]# mysql -u root -p
Enter password: 

# 授权远程登陆
mysql> grant all on *.* to 'root'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

# 进入mysql
root@3021094201c5 sysconfig]# mysql
# 查看 mysql 版本
mysql> status;
# 显示数据库
mysql> show databases;
# 创建数据库,并指定编码
mysql> create database discuz charset=utf8;
# 数据库授权 bbs 为用户名 localhost 为数据库服务器 123 为密码
grant all on *.* to bbs@'localhost' identified by "123";
# 刷新权限
flush privileges;
创建数据库,并指定编码
mysql> create database user_name charset=utf8;
Query OK, 1 row affected (0.00 sec)

# 进入 user_name 数据库
mysql> use user_name;
Database changed

# 显示 user_name 中所有的表
mysql> show tables;
Empty set (0.00 sec)

# 创建名为user_name_en表,并创建两个字段,id、name,varchar表示设置数据长度,用字符来定义长度单位,其中1汉字=3字符=3Bytes;
mysql> create table user_name_en (id varchar(20), name varchar(20));
Query OK, 0 rows affected (0.05 sec)

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

# 向表中插入数据
mysql> insert into user_name_en values ("1", "lei");
Query OK, 1 row affected (0.00 sec)

# 查询表中的数据
mysql> select * from user_name_en;
+------+------+
| id | name |
+------+------+
| 1 | lei |
+------+------+
1 row in set (0.00 sec)

# 多条件查询表中的数据
mysql> select * from user_name_en where id = 1 and name = 'lei';
+------+------+
| id | name |
+------+------+
| 1 | lei |
+------+------+
1 row in set (0.00 sec)

# 查看表字段内容
mysql> desc user_name_en;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | varchar(20) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

# 修改表字段长度
mysql> alter table user_name_en modify column name varchar(40);
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc user_name_en;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | varchar(20) | YES  |     | NULL    |       |
| name  | varchar(40) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

# 修改表字段内容
mysql> update user_name_en set name='lei.wang' where id=1; 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user_name_en where id = 1;
+------+----------+
| id   | name     |
+------+----------+
| 1    | lei.wang |
+------+----------+
1 row in set (0.00 sec)

# 清空表
mysql> delete from user_name_en;
Query OK, 1 row affected (0.00 sec)

mysql> select * from user_name_en where id = 1;
Empty set (0.00 sec)

# 删除表
mysql> drop table user_name_en;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

# 删除数据库
mysql> drop database user_name;
Query OK, 0 rows affected (0.05 sec)

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

# 查看数据库字符集
mysql> show variables like "%char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

# 查看MySQL存储引擎
mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.00 sec)

# 查看MySQL默认的存储引擎
mysql> show variables like '%storage_engine%';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| storage_engine | MyISAM |
+----------------+--------+
1 row in set (0.00 sec)

# 显示指定表的引擎
mysql> show create table user_name_en;
+--------------+---------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------+
| user_name_en | CREATE TABLE `user_name_en` (
 `id` varchar(20) DEFAULT NULL,
 `name` varchar(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

# 修改指定表引擎
mysql> alter table user_name_en engine=innodb;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table user_name_en;
+--------------+---------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                          |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------+
| user_name_en | CREATE TABLE `user_name_en` (
  `id` varchar(20) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

# 显示 mysql 链接数
mysql> show processlist;
+------+------+-----------------------+--------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+-----------------------+--------+---------+------+----------+------------------+
| 473 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 1464 | root | localhost:38217 | yiliao | Sleep | 7 | | NULL |
| 1465 | root | localhost:38216 | yiliao | Sleep | 7 | | NULL |
| 1466 | root | localhost:38220 | yiliao | Sleep | 7 | | NULL |
| 1467 | root | localhost:38222 | yiliao | Sleep | 7 | | NULL |
| 1468 | root | localhost:38224 | yiliao | Sleep | 7 | | NULL |
| 1469 | root | localhost:38225 | yiliao | Sleep | 7 | | NULL |
| 1470 | root | localhost:38228 | yiliao | Sleep | 7 | | NULL |
| 1471 | root | localhost:38230 | yiliao | Sleep | 7 | | NULL |
| 1472 | root | localhost:38232 | yiliao | Sleep | 7 | | NULL |
| 1473 | root | localhost:38234 | yiliao | Sleep | 7 | | NULL |
+------+------+-----------------------+--------+---------+------+----------+------------------+
21 rows in set (0.00 sec)

// mysql 查询语句

查询某个字段在那些表中(可能会遇到,自己抄写下面的代码执行后报错) 建议是复制粘贴,修改为自己需要的内容:

SELECT TABLE_SCHEMA,TABLE_NAME FROM information_schema.`COLUMNS` WHERE COLUMN_NAME = 'username' ;

发表评论

zh_CNChinese
zh_CNChinese