启动MySQL并进行基本数据库操作

安装MySQL并进行数据库管理是每个开发者或DBA必经之路。本文将详细介绍MySQL的安装、用户登录、数据库操作以及常见任务的实现。

启动MySQL

要启动MySQL服务，请按照以下步骤操作：

打开命令提示符，定位到MySQL安装目录：

C:\Program Files\MySQL\MySQL Server 5.6\bin>

输入以下命令启动服务：

mysqld.exe --install

成功安装后，输入以下命令启动MySQL服务：

net start mysql

关闭MySQL服务：

net stop mysql

用户登录

成功启动MySQL后，打开MySQL监控界面并登录：

在命令提示符中输入以下命令：

mysql -u root -p

输入密码后，进入MySQL监控界面。

欢迎信息如下：

Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 8Server version: 5.6.10 MySQL Community Server (GPL)...

数据库操作

查看数据库版本和当前时间

执行以下命令查看数据库信息：

mysql> select version(), current_date;

输出结果：

+-----------+--------------+| version() | current_date |+-----------+--------------+| 5.6.10    | 2013-08-05  |+-----------+--------------+1 row in set (0.01 sec)

查看默认数据库

列出所有数据库：

mysql> show databases;

输出结果：

+--------------------+| Database           |+--------------------+| information_schema || mysql             || performance_schema |+--------------------+3 rows in set (0.00 sec)

创建新数据库

创建名称为leaf的新数据库：

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

切换到新数据库：

mysql> use leaf;Database changed

验证数据库名称：

mysql> select database();+------------+| database() |+------------+| leaf      |+------------+1 row in set (0.00 sec)

创建表

创建表leaf的定义如下：

mysql> create table leaf (    -> leaf_id varchar(6),    -> leaf_name varchar(10),    -> leaf_age int,    -> leaf_sal int,    -> leaf_bir date,    -> leaf_sex varchar(5)    -> );

插入数据示例：

mysql> insert into leaf values ('101','leaf','10','6000','2013-8-5','male'),                   ('102','lea','20','5000','2013-8-4','male'),                   ('103','le','30','4000','2013-8-3','female'),                   ('104','l','35','4000','2013-8-2','female');

输出结果：

Query OK, 4 rows affected, 2 warnings (0.01 sec)

查看表数据

列出表中所有记录：

mysql> select * from leaf;

输出结果：

+---------+-----------+----------+----------+------------+----------+| leaf_id | leaf_name | leaf_age | leaf_sal | leaf_bir   | leaf_sex |+---------+-----------+----------+----------+------------+----------+| 101     | leaf      |       10 |     6000 | 2013-08-05 | male     || 102     | lea       |       20 |     5000 | 2013-08-04 | male     || 103     | le        |       30 |     4000 | 2013-08-03 | femal    || 104     | l         |       35 |     4000 | 2013-08-02 | femal    |+---------+-----------+----------+----------+------------+----------+4 rows in set (0.00 sec)

数据统计

计算工资统计：

mysql> select     -> min(leaf_sal) as min_salary,    -> max(leaf_sal) as max_salary,    -> sum(leaf_sal) as sum_salary,    -> avg(leaf_sal) as avg_salary,    -> count(*) as employee_num    -> from leaf;

输出结果：

+------------+------------+------------+------------+--------------+| min_salary | max_salary | sum_salary | avg_salary | employee_num |+------------+------------+------------+------------+--------------+|       6000 |       8000 |      27000 |  6750.0000 |            4 |+------------+------------+------------+------------+--------------+1 row in set (0.00 sec)