Mysql8_数据库基础操作

---

一：创建数据库

1：查看数据库信息：

SHOW DATABASES;

二：创建数据库

CREATE DATABASE test_db;
SHOW DATABASES;

/*Mysql创建数据库语法：*/
create database database_name

/**database_name 为：要创建的数据库名称，该数据库名称不能与已经存在的数据库名称同名***/

三：查看数据库定义信息：

SHOW CREATE DATABASE test_db

四：删除数据库：

drop  database database_name ;
/***database_name :为要删除的数据库名称，若指定的数据库不存在，则删除出错****/

C:Usersadmin>cd  C:Program FilesMySQLMySQL Server 8.0in   #切换到win10 的mysql安装目录下

C:Program FilesMySQLMySQL Server 8.0in>mysql -h127.0.0.1 -u root -p   #登录mysql root用户 
Enter password: ******   #输入密码
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 12
Server version: 8.0.20 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> show databases;  ---查看数据库信息
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
6 rows in set (0.00 sec)

mysql> create database zoo;   --创建zoo数据库
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
| zoo                |
+--------------------+
7 rows in set (0.00 sec)

mysql> use zoo ;     --使用zoo数据库
Database changed
mysql> show create database zoo;  --查看zoo数据库信息
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                               |
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| zoo      | CREATE DATABASE `zoo` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> drop database zoo;    --删除zoo数据库
Query OK, 0 rows affected (0.02 sec)

mysql> show databases ;   ---查看数据库信息
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
6 rows in set (0.00 sec)

mysql>

二：创建数据表

1：mysql 创建表语法

create  table  <表名>{
   字段名称1 ，数据类型【列级别约束条件】 【默认值】，
   字段名称2，数据类型【列级别约束条件】 【默认值】，
   字段名称3 ，数据类型【列级别约束条件】 【默认值】，
   字段名称4，数据类型【列级别约束条件】 【默认值】，
   字段名称5，数据类型【列级别约束条件】 【默认值】，
  .........
  【表级别约束条件】

}

/***********
 *  create table 创建表的时候，必须指定以下信息：
*  1：创建的表名称，不区分大小写，不能使用mysql语言关键字，如：dorp,alter,insert
*  2:数据表中的每一列的名称和数据类型，如果创建多个，要用逗号隔开。
*
*
*
*************************/

C:Program FilesMySQLMySQL Server 8.0in>mysql -h 127.0.0.1 -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 17
Server version: 8.0.20 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> show databases;    ----展示数据库信息
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
| zoo                |
+--------------------+
7 rows in set (0.00 sec)

mysql> create database test_db;   ---创建数据库
Query OK, 1 row affected (0.04 sec)

mysql> show databases;     ----展示数据库信息
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| test_db            |
| world              |
| zoo                |
+--------------------+
8 rows in set (0.00 sec)

mysql> use test_db;    --使用数据库
Database changed
mysql> show create database test_db;   ---查看数据库名称信息
+----------+-----------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                   |
+----------+-----------------------------------------------------------------------------------------------------------------------------------+
| test_db  | CREATE DATABASE `test_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
-----创建数据库表
mysql> CREATE TABLE tb_emp1     
    -> (
    -> id      INT(11),
    -> name   VARCHAR(25),
    -> deptId  INT(11),
    -> salary  FLOAT
    -> );
Query OK, 0 rows affected, 2 warnings (0.76 sec)

mysql> show tables;    --查看表信息
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_emp1           |
+-------------------+
1 row in set (0.04 sec)

mysql>

2：主键约束

主键(主码）：是表中某列或者某几列的组合。其主键约束要求主键列的数据唯一，并且不允许为空。主键能够唯一标识某一条数据记录，可以结合外键来定义不同数据表之间的关系，并且可以加快数据库查询的速度。

2-1：单字段主键

在定义列的同时指定主键，语法规则：

字段名  数据类型  primary key (默认值)

mysql> CREATE TABLE tb_emp2
    -> (
    -> id    INT(11) PRIMARY KEY,     /**但字段创建主键约束***/
    -> name  VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT
    -> );
Query OK, 0 rows affected, 2 warnings (0.20 sec)

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_emp1           |
| tb_emp2           |
+-------------------+
2 rows in set (0.00 sec)

在定义完所有列之后指定主键，语法规则：

【constraint <约束名称>】 primary key 【字段名】

/*******【例4.3】定义数据表tb_emp 3，其主键为id，SQL语句如下：*********/

mysql> CREATE TABLE tb_emp3
    -> (
    -> id INT(11),
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> PRIMARY KEY(id)     /***创建所有列之后创建主键***/
    -> );
Query OK, 0 rows affected, 2 warnings (0.24 sec)

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_emp1           |
| tb_emp2           |
| tb_emp3           |
+-------------------+
3 rows in set (0.00 sec)

mysql>

2-2：多字段联合主键

主键由多个字段联合组成。

语法：

primary key [字段1，字段2.,........]

/****【例4.4】定义数据表tb_emp4，假设表中间没有主键id，为了唯一确定一个员工，可以把name、deptId联合起来做为主键，SQL语句如下：***/
mysql> CREATE TABLE tb_emp4
    ->  (
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> PRIMARY KEY(name,deptId)
    -> );
Query OK, 0 rows affected, 1 warning (0.10 sec)

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_emp1           |
| tb_emp2           |
| tb_emp3           |
| tb_emp4           |
+-------------------+
4 rows in set (0.00 sec)

mysql>

3:外键约束

外键用来在两个表之间建立连接的，可以是一列或者是多列。一个表可以有一个或者是多个外键。外键对应的是参照完整性，一个表 的外键可以为空值，若不为空值，则每一个外键必须等于另外一个表的主键的某个值。

外键：首先它是一个表的字段，虽然可以不是本表的主键，但要对应另外一个表的主键，外键的主要作用是保证数据引用的完整性，定义外键，不允许删除在另外一个表中具有关联关系的行。

外键的作用：保持数据的一致性/完整性。

主表：对于两个表具有关联关系的表而言，相关联字段中主键所在的那个表为主表。

从表：对于两个具有关联关系的表而言，相关联字段中外键所在的那个表为从表。

创建外键的语法：

【constaraint  <外键名>】 foreign key 字段名1【，字段名2，...】
references <主表名称> 主键列1【，主键列2.....】

外键名：为定义的外键约束的名称。一个表中不能有相同的名称外键。

字段名：标识子表需要添加约束的字段列

主表名：即被子表外键所依赖的表的名称

主键列：表示主表中定义的主键列，或者是列组合

注释: 主外键列的类型必须一致。

/**********【例4.5】定义数据表tb_emp5，并在tb_emp5表上创建外键约束。***************************/

mysql> CREATE TABLE tb_dept1    /*****创建一个部门表tb_dept1，SQL语句如下********/
    -> (
    -> id       INT(11) PRIMARY KEY,
    -> name    VARCHAR(22)  NOT NULL,
    -> location  VARCHAR(50)
    -> );
Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> CREATE TABLE tb_emp5   /********定义数据表tb_emp5，让它的键deptId作为外键关联到tb_dept1的主键id，SQL语句为：********/
    -> (
    -> id      INT(11) PRIMARY KEY,
    -> name   VARCHAR(25),
    -> deptId  INT(11),
    -> salary   FLOAT,
    -> CONSTRAINT fk_emp_dept1 FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
    -> );
Query OK, 0 rows affected, 2 warnings (0.51 sec)

mysql> show  tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_dept1          |
| tb_emp1           |
| tb_emp2           |
| tb_emp3           |
| tb_emp4           |
| tb_emp5           |
+-------------------+
6 rows in set (0.00 sec)

mysql>

4：非空约束

非空约束：指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据的时候没有指定值，数据库会报错。

非空约束语法如下：

字段名  数据类型  not  null

----【例4.6】定义数据表tb_emp6，指定员工的名称不能为空，SQL语句如下：
mysql> CREATE TABLE tb_emp6
    -> (
    -> id     INT(11) PRIMARY KEY,
    -> name   VARCHAR(25) NOT NULL,
    -> deptId  INT(11),
    -> salary  FLOAT
    -> );
Query OK, 0 rows affected, 2 warnings (0.18 sec)

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_dept1          |
| tb_emp1           |
| tb_emp2           |
| tb_emp3           |
| tb_emp4           |
| tb_emp5           |
| tb_emp6           |
+-------------------+
7 rows in set (0.00 sec)

mysql>

5:唯一约束

唯一约束：要求该列唯一，允许为空，但只能出现一次。唯一约束可以确保一列或者几列不能出现重复。

语法如下；

----定义在列之后的唯一约束
 字段名  类型  unique


----定义在所有完成列之后指定唯一约束条件

【constraint <约束名称>】 unique （<字段名>）

----【例4.7】定义数据表tb_dept2，指定部门的名称唯一，SQL语句如下：
CREATE TABLE tb_dept2 
(
id      INT(11) PRIMARY KEY,
name    VARCHAR(22) UNIQUE,
location  VARCHAR(50)
);
-----【例4.8】定义数据表tb_dept3，指定部门的名称唯一，SQL语句如下：
CREATE TABLE tb_dept3 
(
id      INT(11) PRIMARY KEY,
name    VARCHAR(22),
location  VARCHAR(50),
CONSTRAINT STH UNIQUE(name)
);

注意：一个表可以有多个非空约束，主键约束只有1个。主键列值不能为空，非空约束列可以有null存在。

6：默认约束

默认约束：指定某列的默认值。

语法：

字段名  字段类型  default 默认值

------【例4.9】定义数据表tb_emp7，指定员工的部门编号默认为1111，SQL语句如下：
CREATE TABLE tb_emp7 
(
id      INT(11) PRIMARY KEY,
name   VARCHAR(25) NOT NULL,
deptId  INT(11) DEFAULT 1111, 
salary  FLOAT
);

7：设置表的属性自动增加：

在数据库应用中，经常希望在每次插入数据记录时候，系统自动生成字段主键值，可以通过外表的主键 添加 auto_increment 关键字来实现。

默认值 在mysql 中的 auto_increment 的初始值是1 ，没新增1条记录，字段值自动加1.一个表只能有1个字段使用auto_increment 约束且该字段必须为主键约束。auto_increment 约束的字段可以是任何证书类型（tinynt,smalln ,int ,bignt等）

语法：

字段名  数据类型 auto_increment

-----【例4.10】定义数据表tb_emp8，指定员工的编号自动递增，SQL语句如下：
CREATE TABLE tb_emp8 
(
id      INT(11) PRIMARY KEY AUTO_INCREMENT,
name   VARCHAR(25) NOT NULL,
deptId  INT(11), 
salary  FLOAT
);


 insert into tb_emp8 (name ,salary) values('aaaa','1212'),('bbbbb','2323');

三：查看数据表结构

1:查看表基本结构

describe/desc 语句 可以查看表字段信息，其中包括 字段名称、字段数据类型、是否为主键、是否有默认值 等

describe  表名

desc  表名

null : 表示该列是否可以储存null值

key：表示该列是否已编制索引。pri 表示该列是表主键的一部分 uni 表示该列是 unique 索引一部分  mul 表示在列中某个给定值允许出现多次

default : 表示该列是否有默认值。有的话指定值是多少。

extra:表示可以获取的与指定列有关的附加信息。例如：auto_increment

2：查看表详细结构

show  create table <表名>

mysql> show tables;
 +-------------------+
 | Tables_in_test_db |
 +-------------------+
 | tb_dept1          |
 | tb_dept2          |
 | tb_dept3          |
 | tb_emp1           |
 | tb_emp2           |
 | tb_emp3           |
 | tb_emp4           |
 | tb_emp5           |
 | tb_emp6           |
 | tb_emp7           |
 | tb_emp8           |
 +-------------------+
 11 rows in set (0.00 sec)

mysql> show create table tb_dept3;
 +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | Table    | Create Table                                                                                                                                                                                                                                  |
 +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | tb_dept3 | CREATE TABLE `tb_dept3` (
   `id` int NOT NULL,
   `name` varchar(22) DEFAULT NULL,
   `location` varchar(50) DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `STH` (`name`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
 +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 1 row in set (0.00 sec)

mysql> show create table tb_dept3 G
 *************************** 1. row ***************************
        Table: tb_dept3
 Create Table: CREATE TABLE `tb_dept3` (
   `id` int NOT NULL,
   `name` varchar(22) DEFAULT NULL,
   `location` varchar(50) DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `STH` (`name`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
 1 row in set (0.00 sec)

mysql>

四：修改数据表

1：修改表名

ALTER TABLE <旧表名>  RENAME  [TO] <新表名> ;
----To 为可选参数，使用与否不影响结果

【例4.13】将数据表tb_dept3改名为tb_deptment3。
ALTER TABLE tb_dept3 RENAME tb_deptment3;

2:修改字段数据类型

ALTER TABLE <表名>  MODIFY <字段名> <数据类型> ;
----表名：指要修改数据类型的字段所在的表名称；字段名：指需要修改的字段类型，数据类型：指修改后字段的新数据类型。

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_dept1          |
| tb_dept2          |
| tb_deptment3      |
| tb_emp1           |
| tb_emp2           |
| tb_emp3           |
| tb_emp4           |
| tb_emp5           |
| tb_emp6           |
| tb_emp7           |
| tb_emp8           |
+-------------------+
11 rows in set (0.00 sec)

mysql> desc tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(22) | NO   |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> ALTER TABLE tb_dept1 MODIFY name VARCHAR(30);
Query OK, 0 rows affected (0.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(30) | YES  |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

3：修改字段名

ALTER TABLE <表名>  CHANGE <旧字段名>  <新字段名> <数据类型>  ;
---“旧字段名”：指修改前的字段名   “新字段名”：指修改后的字段名  “新数据类型”：指修改后的数据类型。如果不需要修改字段的数据类型，将新字段数据类型设置成与原来一样即可。但是数据类型不能为空。

mysql> desc tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(30) | YES  |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
---【例4.15】将数据表tb_dept1中的location字段名称改为loc，数据类型保持不变，SQL语句如下：
mysql> ALTER TABLE tb_dept1 CHANGE location loc VARCHAR(50);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_dept1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| loc   | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

---【例4.16】 将数据表tb_dept1中的loc字段名称改为location，同时将数据类型变为VARCHAR(60)，SQL语句如下：
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_dept1          |
| tb_dept2          |
| tb_deptment3      |
| tb_emp1           |
| tb_emp2           |
| tb_emp3           |
| tb_emp4           |
| tb_emp5           |
| tb_emp6           |
| tb_emp7           |
| tb_emp8           |
+-------------------+
11 rows in set (0.00 sec)

mysql> desc tb_dept1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| loc   | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> ALTER TABLE tb_dept1 CHANGE loc location VARCHAR(60);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(30) | YES  |     | NULL    |       |
| location | varchar(60) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

4:添加字段

ALTER TABLE <表名>  ADD   <新字段名> <数据类型>  [约束条件]  [FIRST| AFTER 已存在字段名]  ;
-----新字段名为：需要添加的字段名称。first 为可选参数，其作用是将新增加的字段设置为表的第一个字段。after 为可选参数。其作用是将添加的字段增加到指定的“已存在字段”的后面

---