MySQL数据库

数据库概念

简而言之可视为电子化的文件柜子，用户可以文件进行 增/删/改/查 等操作。

关系型数据库

MySQL

非关系型数据库（NoSQL）

MySQL数据库

数据表的概念

id	name	password	表头
1	kzero	mima	记录
2	zoiiiiii	mima
3	admin	admin
键	字段

表的记录：表中的任意一行称为记录

表的字段：表中的任意一列称为字段

表头(header)：每一列的名称;

列(col)：具有相同数据类型的数据的集合;

行(row)：每一行用来描述某条记录的具体信息;

值(value)：行的具体信息, 每个值必须与该列的数据类型相同;

键(key)：键的值在当前列中具有唯一性。

MySQL数据类型

数值类型

日期/时间类型

字符串类型

如何选择数据类型

日期：按照格式

数值和字符串：按照大小

建表的约束

主键约束(PRI)

让数据表里的一条记录成为唯一的，不可重复的（也不可为空）。

create table 数据表名(
	id int primary key,
	表头1 数据类型
);

例子：

mysql> create table user(
    -> id int primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> show tables;
+-------------------------+
| Tables_in_test_database |
+-------------------------+
| test_table              |
| user                    |
+-------------------------+
2 rows in set (0.00 sec)

mysql> describe user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into user values(1,'kzero');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user;
+----+-------+
| id | name  |
+----+-------+
|  1 | kzero |
+----+-------+
1 row in set (0.01 sec)

mysql>  insert into user values(2,'codekzero');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | kzero     |
|  2 | codekzero |
+----+-----------+
2 rows in set (0.00 sec)

联合主键

只要联合的主键值加起来不重复就可以了

当一个字段可能存在重复值，无法确定这条数据的唯一性时，再加上一个字段，两个字段联合起来确定这条数据的唯一性

create table 数据表名(
	id int,
	表头1 数据类型,
	表头2 数据类型,
	primary key(id,表头1)
);

例子：

mysql> create table user2(
    -> id int,
    -> name varchar(20),
    -> password varchar(20),
    -> primary key(id,name)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into user2 values(1,'kzero','mima');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user2;
+----+-------+----------+
| id | name  | password |
+----+-------+----------+
|  1 | kzero | mima     |
+----+-------+----------+
1 row in set (0.00 sec)

mysql> insert into user2 values(2,'kzero','mima');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user2;
+----+-------+----------+
| id | name  | password |
+----+-------+----------+
|  1 | kzero | mima     |
|  2 | kzero | mima     |
+----+-------+----------+
2 rows in set (0.00 sec)

mysql> insert into user2 values(3,'codekzero','mima');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user2;
+----+-----------+----------+
| id | name      | password |
+----+-----------+----------+
|  1 | kzero     | mima     |
|  2 | kzero     | mima     |
|  3 | codekzero | mima     |
+----+-----------+----------+
3 rows in set (0.00 sec)

忘记创建主键约束

create table 数据表名 (
	id int,
	表头1 数据类型
);

使用add，修改表结构，添加主键

alter table 数据表名 add primary key;

例子：

mysql> create table user4 (
    -> id int,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> describe user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table user4 add primary key(id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

使用drop，删除主键

alter table 数据表名 drop primary key;

例子：

mysql> alter table user4 drop primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

使用modify，修改字段，添加主键

alter table 数据表名 modify id int primary key;

例子：

mysql> alter table user4 modify id int primary key;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

自增约束(PRI)

会自增id值

create table 数据表名(
	id int primary key auto_increment,
	表头1 数据类型
);

insert into 数据表名(表头1) values('值1');

例子：

mysql> create table user3(
    -> id int primary key auto_increment,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into user3(name) values('kzero');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user3;
+----+-------+
| id | name  |
+----+-------+
|  1 | kzero |
+----+-------+
1 row in set (0.00 sec)

mysql> insert into user3(name) values('kzero');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user3;
+----+-------+
| id | name  |
+----+-------+
|  1 | kzero |
|  2 | kzero |
+----+-------+
2 rows in set (0.00 sec)

mysql> insert into user3(name) values('kzero');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user3;
+----+-------+
| id | name  |
+----+-------+
|  1 | kzero |
|  2 | kzero |
|  3 | kzero |
+----+-------+
3 rows in set (0.00 sec)

唯一约束(UNI)

约束修饰的字段的值不能重复

直接创建唯一约束

create table 数据表名(
	id int,
	表头1 数据类型,
	unique(表头1)
);

例子：

mysql> create table user6(
    -> id int,
    -> name varchar(20),
    -> unique(name)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> describe user6;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

或

create table 数据表名(
	id int,
	表头1 数据类型 unique
);

例子：

mysql> create table user7(
    -> id int,
    -> name varchar(20) unique
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> describe user7;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

或

create table 数据表名(
	id int,
	表头1 数据类型,
	unique(id,表头1)
);

例子：

mysql> create table user8(
    -> id int,
    -> name varchar(20),
    -> unique(id,name)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> describe user8;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  | MUL | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> insert into user8 values(1,'kzero');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user8 values(1,'kzero');
ERROR 1062 (23000): Duplicate entry '1-kzero' for key 'user8.id'

mysql> insert into user8 values(2,'kzero');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user8 values(1,'codekzero');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user8;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | codekzero |
|    1 | kzero     |
|    2 | kzero     |
+------+-----------+
3 rows in set (0.00 sec)

小结：MUL是两个键在一起不重复就可以。

创建后修改唯一约束

create table 数据表名(
	id int,
	表头1 数据类型
);

alter table 数据表名 add unique(表头1);

例子：

mysql> create table user5(
    -> id int,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> alter table user5 add unique(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe user5;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into user5 values(1,'kzero');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user5 values(1,'kzero');
ERROR 1062 (23000): Duplicate entry 'kzero' for key 'user5.name'

mysql> insert into user5 values(1,'codekzero');
Query OK, 1 row affected (0.00 sec)

小结：UNI是id可以重复，字段不可重复。

删除唯一约束

alter table 数据表名 drop index 表头;

mysql> describe user7;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table user7 drop index name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe user7;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

modify 添加唯一约束

mysql> alter table user7 modify name varchar(20) unique;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe user7;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

小结

1. 创建添加

非空约束

默认约束

外键约束

MySQL数据库下载

1. MySQL Community Downloads 下载好dmg，直接安装。

2. 配置环境变量

   终端输入（编辑配置文件）

   vim ~/.zshrc

   按 i 进行编辑，在尾巴添加

   PATH=$PATH:/usr/local/mysql/bin
   alias mysql=/usr/local/mysql/bin/mysql

   按 esc 取消编辑

   按住 shift + 再按两下 z 保存（或 输入 :wq 按 回车键 保存）

   终端输入（立即生成）

   source ~/.zshrc

一、使用终端操作数据库

登陆数据库

mysql -u root -p
Enter password:(输入数据库密码)

登录成功

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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>

退出数据库

exit;

创建数据库

create database 数据库名;

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

删除数据库

drop database 数据库名;

查看所有数据库

show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_database      |
+--------------------+
5 rows in set (0.00 sec)

选择要使用的数据库

use 数据库名

mysql> use test_database;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

创建数据表

create table 数据表名(
表头1 数据类型,
表头2 数据类型
);

mysql> create table test_table(
    -> name varchar(20),
    -> passwored varchar(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

删除数据表

drop tabel 数据表名;

mysql> create table test_table(
    -> name varchar(20),
    -> passwored varchar(20)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> drop table test_table;
Query OK, 0 rows affected (0.01 sec)

查看选中数据库的所有数据表

show tables;

mysql> show tables;
+-------------------------+
| Tables_in_test_database |
+-------------------------+
| test_table              |
+-------------------------+
1 row in set (0.00 sec)

在数据表中添加数据记录

insert into 数据表名
(表头1,表头2,...表头n)
value
(值1,值2,...值n);

表的记录：表中的随便任意一行称为记录

表的字段：表中的随便任意一列称为字段

insert into 数据表名
value('值1','值2');

mysql> insert into test_table
    -> value('zoiiiiii','mima');
Query OK, 1 row affected (0.00 sec)

再次查询

mysql> select * from test_table;
+----------+-----------+
| name     | passwored |
+----------+-----------+
| kzero    | mima      |
| admin    | admin     |
| zoiiiiii | mima      |
+----------+-----------+
3 rows in set (0.00 sec)

查询表中的数据

select * from 数据表名;

mysql> select * from test_table;
+-------+-----------+
| name  | passwored |
+-------+-----------+
| kzero | mima      |
| admin | admin     |
+-------+-----------+
2 rows in set (0.00 sec)

条件查询表中的数据

slect * from 数据表名 where 条件;

slect * from 数据表名 where id=1;

slect * from test_table where name='kzero';
+-------+-----------+
| name  | passwored |
+-------+-----------+
| kzero | mima      |
+-------+-----------+
1 row in set (0.00 sec)

查看数据表的结构

describe 数据表名;

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| name      | varchar(20) | YES  |     | NULL    |       |
| passwored | varchar(20) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

删除数据

delete from 数据表名 where 条件;

delete from test_table where name='admin';

Query OK, 1 row affected (0.01 sec)

再次查询

mysql> select * from test_table;
+----------+-----------+
| name     | passwored |
+----------+-----------+
| kzero    | mima      |
| zoiiiiii | mima      |
+----------+-----------+
2 rows in set (0.00 sec)

修改数据

update 数据表名 set 字段1=值1,字段2=值2,... where 条件;

mysql> update test_table set name='codekzero' where name='zoiiiiii';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

再次查询

mysql> select * from test_table;
+-----------+-----------+
| name      | passwored |
+-----------+-----------+
| kzero     | mima      |
| codekzero | mima      |
+-----------+-----------+
2 rows in set (0.00 sec)

总结

增加

insert

删除

delete

修改

updata

查询

select

二、使用可视化工具操作数据库

Navcat Premium

localhost（数据库服务器）> 数据库 > 数据表 > 数据记录

三、在编程语言中操作数据库