当前位置：首页 > 运维知识 > 数据库 >内容正文

数据库

mysql技术内幕sampdb_MySql技术内幕之MySQL入门(1)

发布时间：2025/3/15 数据库 33 豆豆

生活随笔收集整理的这篇文章主要介绍了 mysql技术内幕sampdb_MySql技术内幕之MySQL入门(1) 小编觉得挺不错的,现在分享给大家,帮大家做个参考.

MySql技术内幕之MySQL入门(1)

安装

检查系统中是否已经安装了MySQL

sudo netstat -tap | grep mysql

若没有显示已安装结果，则没有安装。否则表示已经安装。

sudo apt-get install mysql-server mysql-client

安装过程中会让输入密码，记得把密码记住。

关于注释

三种写法：

用#单行注释

用-- 单行注释，注意后面有一空格

/* */ 多行注释

mysql> SELECT 1+1; # 这个注释直到该行结束

mysql> SELECT 1+1; -- 这个注释直到该行结束

mysql> SELECT 1 /* 这是一个在行中间的注释 */ + 1;

mysql> SELECT 1+

这是一个

多行注释的形式

执行SQL语句

ltq@lab:~$ mysql -u root -p

Enter password:

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

Your MySQL connection id is 7

Server version: 5.7.19-0ubuntu0.16.04.1 (Ubuntu)

Oracle is 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> SELECT NOW(); # 查询当前日期和时间，使用分号终止语句

+---------------------+

| NOW() |

+---------------------+

| 2017-10-20 22:07:14 |

+---------------------+

1 row in set (0.00 sec)

mysql> SELECT NOW()\g # 查询当前日期和时间，使用\g终止语句

+---------------------+

| NOW() |

+---------------------+

| 2017-10-20 22:07:56 |

+---------------------+

1 row in set (0.00 sec)

mysql> SELECT NOW(), USER(), VERSION()\g # 查询时间，用户，系统版本，并在一列展示

+---------------------+----------------+-------------------------+

| NOW() | USER() | VERSION() |

+---------------------+----------------+-------------------------+

| 2017-10-20 22:08:28 | root@localhost | 5.7.19-0ubuntu0.16.04.1 |

+---------------------+----------------+-------------------------+

1 row in set (0.00 sec)

mysql> SELECT NOW(), USER(), VERSION()\G #查询时间，用户，系统版本，并在一行展示

*************************** 1. row ***************************

NOW(): 2017-10-20 22:08:43

USER(): root@localhost

VERSION(): 5.7.19-0ubuntu0.16.04.1

1 row in set (0.00 sec)

mysql> SELECT NOW(),

-> USER(),

-> VERSION()\G # 在多行输入

*************************** 1. row ***************************

NOW(): 2017-10-20 22:18:44

USER(): root@localhost

VERSION(): 5.7.19-0ubuntu0.16.04.1

1 row in set (0.00 sec)

mysql> SELECT NOW(),

-> USER(),

-> \c # 使用\c表示不执行上述语句

mysql> SELECT NOW(); SELECT USER(); SELECT VERSION(); # 在一行输入多条语句

+---------------------+

| NOW() |

+---------------------+

| 2017-10-20 22:21:38 |

+---------------------+

1 row in set (0.00 sec)

+----------------+

| USER() |

+----------------+

| root@localhost |

+----------------+

1 row in set (0.00 sec)

+-------------------------+

| VERSION() |

+-------------------------+

| 5.7.19-0ubuntu0.16.04.1 |

+-------------------------+

1 row in set (0.00 sec)

关于命令大小写

一般用大写字母表示SQL关键字和函数名；

用小写字母表示数据库，表和列的名字。

创建数据库

mysql> CREATE DATABASE sampdb; # 创建数据库

Query OK, 1 row affected (0.00 sec)

mysql> SELECT DATABASE(); # 查看当前数据库，结果为NULL

+------------+

| DATABASE() |

+------------+

| NULL |

+------------+

1 row in set (0.00 sec)

mysql> USE sampdb; # 把sampdb设置为当前默认选择的数据库

Database changed

mysql> SELECT DATABASE(); # 查看当前数据库，结果为NULL

+------------+

| DATABASE() |

+------------+

| sampdb |

+------------+

1 row in set (0.00 sec)

mysql> source create_president.sql # 如果已经在终端cd到create_president.sql路径下，那么则可运行该语句

Query OK, 0 rows affected (0.15 sec)

Query OK, 0 rows affected (0.23 sec)

补充，文件create_president.sql的内容如下：

# Create president table for U.S. Historical League

DROP TABLE IF EXISTS president;

#@ _CREATE_TABLE_

CREATE TABLE president

(

last_name VARCHAR(15) NOT NULL,

first_name VARCHAR(15) NOT NULL,

suffix VARCHAR(5) NULL,

city VARCHAR(20) NOT NULL,

state VARCHAR(2) NOT NULL,

birth DATE NOT NULL,

death DATE NULL

);

#@ _CREATE_TABLE_

查看表的信息

mysql> DESCRIBE president; # 查看president表的结构

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

7 rows in set (0.00 sec)

mysql> DESC president; # DESCRIBE的简写为DESC

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

7 rows in set (0.00 sec)

mysql> EXPLAIN president;

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

7 rows in set (0.00 sec)

mysql> SHOW COLUMNS FROM president;

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

7 rows in set (0.00 sec)

mysql> SHOW FIELDS FROM president;

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

7 rows in set (0.00 sec)

查看更加详细的信息

mysql> SHOW FULL COLUMNS FROM president; # SHOW FULL COLUMNS 比 SHOW COLUMNS显示更多的信息

+------------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+

+------------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+

+------------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+

7 rows in set (0.00 sec)

查看与给定模式相匹配的列

如果需要对所查找的信息加以限定，

mysql> DESCRIBE president '%name'; # 查找名称中包含name的项

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql> SHOW FIELDS FROM president like '%name'; # 查找名称中包含name的项，另外一种写法

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

插入数据

利用insert添加行

一次添加一行数据：

INSERT INTO president VALUES ('Washington','George',NULL,'Wakefield','VA','1732-02-22','1799-12-14');

INSERT INTO president VALUES ('Adams','John',NULL,'Braintree','MA','1735-10-30','1826-07-04');

一次添加多行数据：

INSERT INTO president VALUES ('Jefferson','Thomas',NULL,'Albemarle County','VA','1743-04-13','1826-07-04'), ('Madison','James',NULL,'Port Conway','VA','1751-03-16','1836-06-28');

利用文件添加新行

mysql> source insert_president.sql

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.06 sec)