MySQL数据库连接和基本操作

SQL和数据库技术 2023-04-14

474

编者按：基础操作也许会涵盖了80%的工作。

【免责声明】本公众号文章仅代表个人观点，与任何公司无关，仅供参考。

编辑|SQL和数据库技术(ID:SQLplusDB)

## MySQL数据库连接

### 连接MySQL命令

可以通过如下命令连接和断开MySQL服务器。

    --连接远程的数据库
    shell> mysql -h <主机名> -u <用户名> -p<密码>
    --连接本地数据库
    shell> mysql -u <用户名> -p<密码>

例：

ubuntu@mysql-vm:~$ mysql -u root -prootrootmysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 10Server version: 8.0.29-0ubuntu0.20.04.3 (Ubuntu)Copyright (c) 2000, 2022, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

### 断开MySQL命令

可以通过输入exit或者quit（\q）或者Control+D退出MySQL连接。

例：

mysql> exit
Bye


或者
mysql> quit
Bye


或者
mysql> \q
Bye




或者 Control+D
mysql> ^DBye


```

## MySQL数据库基本操作

### 执行查询

能够登录MySQL就可以发行SQL，进行操作了。

在Oracle中，存在一个虚拟表dual（只有空的一行一列），用来构成select的语法规则。

对于MySQL而言，则省去了这个表，可以直接执行select查询。

例：Oracle


SQL> select sysdate from dual;


SYSDATE
---------
19-FEB-21


例：MySQL




mysql> select version(),current_date;
+-----------+--------------+
| version() | current_date |
+-----------+--------------+
| 8.0.23    | 2021-02-19   |
+-----------+--------------+
1 row in set (0.00 sec)
```


查看当前登录的用户：


```
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)


```

#### MySQL命令行



默认mysql命令行一般的使用方法如下：


```
- 每个SQL语句以分号(;)  或者（\g）结束。
- 对于关键字不区分大小写。
- 可以以分号（;）为分割，发行多个SQL。
- 如果不想执行正在输入的查询语句，可输入 \c取消。
```


另外，我们可以通过help查看mysql命令行工具的帮助内容。
例：
```
mysql> help


For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/


List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
resetconnection(\x) Clean session context.
query_attributes Sets string parameters (name1 value1 name2 value2 ...) for the next query to pick up.
ssl_session_data_print Serializes the current SSL session data to stdout or file


For server side help, type 'help contents'


```
help contents命令来查看SQL命令的帮助内容。


例：
```
mysql> help contents
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
   Account Management
   Administration
   Components
   Compound Statements
   Contents
   Data Definition
   Data Manipulation
   Data Types
   Functions
   Geographic Features
   Help Metadata
   Language Structure
   Loadable Functions
   Plugins
   Prepared Statements
   Replication Statements
   Storage Engines
   Table Maintenance
   Transactions
   Utility


```

#### 提示符的含义

MySQL的提示符能够表示监视器所处状态。



| 提示符 |含义  |
| --- | --- |
|mysql>  |准备好新的查询|
|->  |等待多行查询的下一行|
|'>  |等待下一行，等待以单引号 ( ')开头的字符串的完成|
|">  |等待下一行，等待以双引号 ( ")开头的字符串的完成|
|/*>  |等待下一行，等待以开头的注释完成/*|

### 数据库操作

MySQL数据库操作相关的常用命令如下：



```
show databases;
create database <数据库名>;
use <数据库名>;
select database();
drop database <数据库名>;
```


#### 1. 查看mysql中的数据库


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


#### 2. 创建数据库


例：
```
mysql> create database testdb;
Query OK, 1 row affected (0.00 sec)


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


```


#### 3. 使用指定的数据库


```
mysql> use testdb;
Database changed
```


#### 4. 查看当前使用的数据库
```
mysql> select database();
+------------+
| database() |
+------------+
| testdb     |
+------------+
1 row in set (0.00 sec)


```
#### 5. 删除数据库
```
mysql> drop database testdb;
Query OK, 0 rows affected (0.01 sec)


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


```

### 用户操作

用户相关的常用命令如下：

```
CREATE USER ...
ALTER USER  ...
DROP USER  ...
GRANT <权限> on <对象> to <用户名>；
REVOKE  <权限> on <对象> from <用户名>；
```

#### 1.创建用户

创建用户

```
mysql> create user test identified by 'test';
Query OK, 0 rows affected (0.02 sec)
```


查看用户信息：
```
mysql> SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES where user ='test';
+------+------+-----------+
| USER | HOST | ATTRIBUTE |
+------+------+-----------+
| test | %    | NULL      |
+------+------+-----------+
1 row in set (0.00 sec)
```


参考：
https://dev.mysql.com/doc/refman/8.0/en/account-management-statements.html


### 数据库表操作


表相关的常用命令如下：
```
show tables;
create table 表名(列名1 类型，列名2 类型，...);
desc 表名;
rename table 表名 to 新表名;
drop table 表名;
```


#### 1. 查看当前数据库中所有表
```
mysql> show tables;
Empty set (0.00 sec)


```
#### 2. 创建表
```
mysql> create table tbl(c1 int, c2 varchar(20));
Query OK, 0 rows affected (0.03 sec)
```


#### 3. 查看表结构
```
mysql> desc tbl;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1    | int         | YES  |     | NULL    |       |
| c2    | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)


```
#### 4. 修改表名
```
mysql> rename table tbl to tab1;
Query OK, 0 rows affected (0.02 sec)


mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| tab1             |
+------------------+
1 row in set (0.01 sec)


```


#### 5. 删除表
```
mysql> create table t(c1 int);
Query OK, 0 rows affected (0.02 sec)


mysql> drop table t;
Query OK, 0 rows affected (0.01 sec)
```

### 表列操作

可以通过ALTER TABLE修改表的列相关属性。



```
----添加列
alter table 表名 add 列名 列类型；
----修改列名
alter table 表名 change 旧列名 新列名 列类型；
----修改列类型
alter table 表名 modify 列名 列类型
----修改列排列位置
alter table 表名 modify 列名1 数据类型 first | after 列名2；
----删除列
alter table 表名 drop 列名
```


#### 1. 添加列
```
mysql> alter table tab1 add c3 varchar(20);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> alter table tab1 add c4 varchar(20);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> desc tab1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1    | int         | YES  |     | NULL    |       |
| c2    | varchar(20) | YES  |     | NULL    |       |
| c3    | varchar(20) | YES  |     | NULL    |       |
| c4    | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
```


#### 2. 修改列名
```
mysql> alter table tab1 change c4 b1 varchar(20);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> desc tab1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1    | int         | YES  |     | NULL    |       |
| c2    | varchar(20) | YES  |     | NULL    |       |
| c3    | varchar(20) | YES  |     | NULL    |       |
| b1    | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)


```


#### 3. 修改列类型
```
mysql> alter table tab1 modify b1 char(10);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> desc tab1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1    | int         | YES  |     | NULL    |       |
| c2    | varchar(20) | YES  |     | NULL    |       |
| c3    | varchar(20) | YES  |     | NULL    |       |
| b1    | char(10)    | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)


```


#### 4.修改列排列位置
```
mysql> alter table tab1 modify c3 varchar(20) after b1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> desc tab1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1    | int         | YES  |     | NULL    |       |
| c2    | varchar(20) | YES  |     | NULL    |       |
| b1    | char(10)    | YES  |     | NULL    |       |
| c3    | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)


```


#### 5. 删除列
```
mysql> alter table tab1 drop b1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> desc tab1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1    | int         | YES  |     | NULL    |       |
| c2    | varchar(20) | YES  |     | NULL    |       |
| c3    | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


mysql>
```

### 数据操作

MySQL作为关系型数据库，支持标准SQL的语法如增删改查操作（SELECT/UPDATE/DELTE/INSERT）。

#### 1.插入数据 (insert)
可以通过insert向表中插入数据。


```
INSERT INTO 表名 VALUES( 值1 ，值2 ...);
INSERT INTO 表名 SELECT 列1，列2 ... FROM 表2；


INSERT INTO 表名 (列1，列2 ...) VALUES( 值1 ，值2 ...);
INSERT INTO 表名 (列1，列2 ...)  SELECT 列1，列2 ... FROM 表2；
```


INSERT INTO VAlUES例：
```
mysql> insert into tab1  value(1,'text','text');
Query OK, 1 row affected (0.01 sec)


mysql> select * from tab1;
+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
|    1 | text | text |
+------+------+------+
1 row in set (0.00 sec)


mysql> insert into tab1 (c1,c2) value(2,'text2');
Query OK, 1 row affected (0.00 sec)


mysql> select * from tab1;
+------+-------+------+
| c1   | c2    | c3   |
+------+-------+------+
|    1 | text  | text |
|    2 | text2 | NULL |
+------+-------+------+
2 rows in set (0.00 sec)


```
INSERT INTO SELECT例：
```
mysql> create table tab2(col1 int,col2 varchar(20),col3 varchar(20));
Query OK, 0 rows affected (0.03 sec)


mysql>  insert into tab2 select * from tab1;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0


mysql> select * from tab2;
+------+-------+------+
| col1 | col2  | col3 |
+------+-------+------+
|    1 | text  | text |
|    2 | text2 | NULL |
+------+-------+------+
2 rows in set (0.00 sec)


mysql>  insert into tab2(col1,col3) select c1,c2 from tab1;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0


mysql> select * from tab2;
+------+-------+-------+
| col1 | col2  | col3  |
+------+-------+-------+
|    1 | text  | text  |
|    2 | text2 | NULL  |
|    1 | NULL  | text  |
|    2 | NULL  | text2 |
+------+-------+-------+
4 rows in set (0.00 sec)


mysql>


```


#### 2.删除数据 (delete)


可以通过delete语句删除数据。
```
DELETE FROM 表名 WHERE 条件；
```


例：
```
mysql> delete from tab2 where col3='text2';
Query OK, 1 row affected (0.01 sec)


mysql> select * from tab2;
+------+-------+------+
| col1 | col2  | col3 |
+------+-------+------+
|    1 | text  | text |
|    2 | text2 | NULL |
|    1 | NULL  | text |
+------+-------+------+
3 rows in set (0.00 sec)


```


#### 3.更新数据（update）
可以通过update进行表中数据的更新。


update例：
```
mysql> update tab2 set col2='update' where col1=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> select * from tab2;
+------+--------+------+
| col1 | col2   | col3 |
+------+--------+------+
|    1 | text   | text |
|    2 | update | NULL |
|    1 | NULL   | text |
+------+--------+------+
3 rows in set (0.00 sec)
```

#### 4.查询（SELECT）

如前面的介绍，可以通过SELECT语句查询表中的数据。

SELECT看似简单，实际上语法也最为复杂。

这里仅介绍简单的条件查询。



```
select * from 表名 where 条件语句；
select 列1，列2 ... from 表名 where 条件语句；
```


例：
```
mysql> select * from tab2;
+------+--------+------+
| col1 | col2   | col3 |
+------+--------+------+
|    1 | text   | text |
|    2 | update | NULL |
|    1 | NULL   | text |
+------+--------+------+
3 rows in set (0.00 sec)


mysql> select col1, col2 from tab2 where col1=1;
+------+------+
| col1 | col2 |
+------+------+
|    1 | text |
|    1 | NULL |
+------+------+
2 rows in set (0.00 sec)
```

关于各种操作更详细的语法可以参考在线文档。

https://dev.mysql.com/doc/refman/8.0/en/entering-queries.html
https://dev.mysql.com/doc/refman/8.0/en/alter-table.html
https://dev.mysql.com/doc/refman/8.0/en/sql-statements.html
https://dev.mysql.com/doc/refman/8.0/en/insert.html
https://dev.mysql.com/doc/refman/8.0/en/delete.html
https://dev.mysql.com/doc/refman/8.0/en/update.html
https://dev.mysql.com/doc/refman/8.0/en/select.html
https://dev.mysql.com/doc/refman/8.0/en/create-user.html

文章转载自SQL和数据库技术，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL数据库连接和基本操作

评论