SQL基础1 —— 数据库基础操作

在开始学习SQL之前，你要先安装一个属于自己的数据库软件，我们以MySQL为例。

介于大家的系统环境各不相同，这里只给出下载地址，大家根据自己的系统环境选择相应的版本进行下载安装。

网上可以找到很多的文档可以参考，官档也有很详细的说明。

下载地址：https://dev.mysql.com/downloads/mysql/

官方文档：https://dev.mysql.com/doc/

DBMS、DB、tablespace、table之间的关系

* MySQL DBMS 是数据库管理系统，它由多个DB(DataBase的简称)和管理程序构成。
* DB是存储数据的集合，通常由一个或多个tablespace表空间构成。
* tablespace是用来存储表数据的物理空间，可以把多个表放在一个tablespace里面。
* table是数据存储表现出来的最小形式，我们所有的数据操作都是基于表进行的。

创建数据库

# 连接数据库(我这里用命令行工具直接连接的数据库，当然你也可以选择客户端工具)
# /* */所包含的内容是SQL的注释信息，不会被执行(练习的时候可以不加的哦)
bandianrong@bogon ~ % mysql -uroot -pPASSWORD
mysql: [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 13
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, 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> /*创建一个新的数据库用来做SQL的练习*/
mysql> create database sqltest;
Query OK, 1 row affected (0.00 sec)

mysql> /*显示所有的数据库，可以看到我们刚刚创建的数据库已经在里面了*/
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sqltest            |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> /*切换到我们新建的数据库下*/
mysql> use sqltest;
Database changed

mysql> /*查看用户当前所在的数据库*/
mysql> select database();
+------------+
| database() |
+------------+
| sqltest    |
+------------+
1 row in set (0.00 sec)

mysql> /*查看当前登录用户*/
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> /*创建表空间(注意这个路径一定要真实存在并且拥有读写权限哦)*/
mysql> create tablespace sqltestspace add datafile '/usr/local/mysql/data/sqltestspace01.ibd';
Query OK, 0 rows affected (0.00 sec)

mysql> /*查看表空间信息*/
mysql> select * from information_schema.innodb_tablespaces where name='sqltestspace';
+-------+--------------+-------+------------+-----------+---------------+------------+---------------+-----------+----------------+----------------+---------------+------------+--------+
| SPACE | NAME         | FLAG  | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | SERVER_VERSION | SPACE_VERSION | ENCRYPTION | STATE  |
+-------+--------------+-------+------------+-----------+---------------+------------+---------------+-----------+----------------+----------------+---------------+------------+--------+
|     2 | sqltestspace | 18432 | Any        |     16384 |             0 | General    |          4096 |    114688 |         114688 | 8.0.21         |             1 | N          | normal |
+-------+--------------+-------+------------+-----------+---------------+------------+---------------+-----------+----------------+----------------+---------------+------------+--------+
1 row in set (0.00 sec)

mysql> /*删除表空间(表空间后面我们还会用到，如果你删了，需要再建回来)*/
mysql> drop tablespace sqltestspace;
Query OK, 0 rows affected (0.01 sec)

表的基本操作

mysql> /*创建一张公司的部门表(dept)*/
mysql> create table dept(deptno int,name  char(10))  tablespace sqltestspace;
Query OK, 0 rows affected (0.01 sec)

mysql> /*查看表结构*/
mysql> desc dept;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| deptno | int      | YES  |     | NULL    |       |
| name   | char(10) | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> /*修改字段类型*/
mysql> alter table dept modify name varchar(15);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int         | YES  |     | NULL    |       |
| name   | varchar(15) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> /*修改字段名称和类型*/
mysql> alter table dept change name  deptname varchar(30);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc dept;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| deptno   | int         | YES  |     | NULL    |       |
| deptname | varchar(30) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> /*新增字段*/
mysql> alter table dept add enname varchar(30);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc dept;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| deptno   | int         | YES  |     | NULL    |       |
| deptname | varchar(30) | YES  |     | NULL    |       |
| enname   | varchar(30) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> /*删除字段*/
mysql> alter table dept drop enname;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc dept;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| deptno   | int         | YES  |     | NULL    |       |
| deptname | varchar(30) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> /*插入数据*/
mysql> insert into dept (deptno,deptname) values (1,'人事部');
Query OK, 1 row affected (0.00 sec)

mysql> /*查看dept表数据*/
mysql> select * from dept;
mysql> select * from dept;
+--------+-----------+
| deptno | deptname  |
+--------+-----------+
|      1 | 人事部    |
+--------+-----------+
1 row in set (0.00 sec)

mysql> /*修改数据*/
mysql> update dept set deptno=101;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from dept;
+--------+-----------+
| deptno | deptname  |
+--------+-----------+
|    101 | 人事部    |
+--------+-----------+
1 row in set (0.00 sec)

mysql> /*删除表数据*/
mysql> delete from dept;
Query OK, 1 row affected (0.00 sec)

mysql> select * from dept;
Empty set (0.00 sec)

mysql> /*删除表*/
mysql> drop table dept;
Query OK, 0 rows affected (0.00 sec)