Linux环境下MySQL的入门教程

Linux环境下数据库的学习总结（一）

该篇讲 mysql 的基本使用

启动MySQL

在Linux内安装好MySQL后，使用根用户才能进行操作

//进入根用户操作命令
[centos6@bogon ~]$su //输入su
Password: // 输入密码
[root@bogon centos6]# service mysqld start //服务器启动指令
Starting mysqld:                                           [  OK  ] //提示启动成功
[root@bogon centos6]# mysql //打开MySQL
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

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> //可输入命令行

查看，创建数据库指令

查看数据库

命令：
 show databases; //在结束部分一定要加分号（英文）

mysql> show databases; 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| LLL                |
| mysql              |
| red_A2             |
| test               |
+--------------------+ //显示当前您所拥有的数据库
5 rows in set (0.09 sec)

创建一个数据库

命令： 
create database + 库名 ;

mysql> create database LIU;
Query OK, 1 row affected (0.00 sec)

结果可以用之前的查看数据库命令来看到

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| LIU  //新建的数据库 |
| LLL                |
| mysql              |
| red_A2             |
| test               |
+--------------------+

对于数据库内的使用，查看与创建表格

数据库的使用

命令:
use + 想使用的数据库库名;

mysql> use LIU;
Database changed

在当前数据库下创建表格

命令: create table + 表名(
     列名 + 类型 ,
     列名 + 类型 ，
     ...）；
**注意在最后定义的列名加类型后不需要用逗号**

mysql> create table students (
    -> name varchar(20),
    -> english float(4,2),
    -> chinese float(4,2),
    -> math float(4,2));
Query OK, 0 rows affected (0.04 sec)

建表完成后可以用 desc+ 表名 语句查看表的基本信息

mysql> desc students;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| english | float(4,2)  | YES  |     | NULL    |       |
| chinese | float(4,2)  | YES  |     | NULL    |       |
| math    | float(4,2)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
//表的基本信息在此

在表中插入数据

命令：
insert into + 表名 values('字符型',数字型); 

mysql> insert into students values('Linda',79,88,92);
Query OK, 1 row affected (0.00 sec) 
//可多次插入大量数据，但是得保持格式一致

mysql> insert into students values('Ana',91,77,66);
Query OK, 1 row affected (0.00 sec)

mysql> insert into students values('Paco',89,87,76);
Query OK, 1 row affected (0.00 sec)

mysql> insert into students values('Rob',96,57,76);
Query OK, 1 row affected (0.00 sec)

查看表格

命令：
select*from + 表名;

+-------+---------+---------+-------+
| name  | english | chinese | math  |
+-------+---------+---------+-------+
| Linda |   79.00 |   88.00 | 92.00 |
| Ana   |   91.00 |   77.00 | 66.00 |
| Paco  |   89.00 |   87.00 | 76.00 |
| Rob   |   96.00 |   57.00 | 76.00 |
+-------+---------+---------+-------+
4 rows in set (0.00 sec)

对表格进简单的删添与更改工作

修改表

命令：
alter table + 表名
add(列名 数据类型); //添加

alter table + 表名 
modify(列名 数据类型); //更改

alter table + 表名
drop(列名) ; //删除该列

添加列

mysql> alter table students add history float(4,2) after name;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
\\在学生姓名后面添加一列历史成绩 

mysql> select * from students;
+-------+---------+---------+---------+-------+
| name  | history | english | chinese | math  |
+-------+---------+---------+---------+-------+
| Linda |    NULL |   79.00 |   88.00 | 92.00 |
| Ana   |    NULL |   91.00 |   77.00 | 66.00 |
| Paco  |    NULL |   89.00 |   87.00 | 76.00 |
| Rob   |    NULL |   96.00 |   57.00 | 76.00 |
+-------+---------+---------+---------+-------+
4 rows in set (0.00 sec)

mysql> desc students;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| history | float(4,2)  | YES  |     | NULL    |       |
| english | float(4,2)  | YES  |     | NULL    |       |
| chinese | float(4,2)  | YES  |     | NULL    |       |
| math    | float(4,2)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec) 

修改某列信息

mysql> alter table students modify history int;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from students;
+-------+---------+---------+---------+-------+
| name  | history | english | chinese | math  |
+-------+---------+---------+---------+-------+
| Linda |    NULL |   79.00 |   88.00 | 92.00 |
| Ana   |    NULL |   91.00 |   77.00 | 66.00 |
| Paco  |    NULL |   89.00 |   87.00 | 76.00 |
| Rob   |    NULL |   96.00 |   57.00 | 76.00 |
+-------+---------+---------+---------+-------+
4 rows in set (0.00 sec)

mysql> desc students;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| history | int(11)     | YES  |     | NULL    |       |
| english | float(4,2)  | YES  |     | NULL    |       |
| chinese | float(4,2)  | YES  |     | NULL    |       |
| math    | float(4,2)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
//将历史成绩数据类型从浮点型更改为整型

删除某列

 mysql> alter table students drop history;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from students;
+-------+---------+---------+-------+
| name  | english | chinese | math  |
+-------+---------+---------+-------+
| Linda |   79.00 |   88.00 | 92.00 |
| Ana   |   91.00 |   77.00 | 66.00 |
| Paco  |   89.00 |   87.00 | 76.00 |
| Rob   |   96.00 |   57.00 | 76.00 |
+-------+---------+---------+-------+
4 rows in set (0.00 sec)

mysql> desc students;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| english | float(4,2)  | YES  |     | NULL    |       |
| chinese | float(4,2)  | YES  |     | NULL    |       |
| math    | float(4,2)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
//将历史成绩这一列完全删除