01.MySQL——MySQL基础

已于 2023-07-19 19:23:23 修改
阅读量533 收藏 1
点赞数 1
分类专栏： MySQL 文章标签： mysql 数据库 linux centos
于 2023-04-24 17:36:24 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_55051736/article/details/130342880
版权

文章目录

数据库基础

数据库概念

存储数据用文件就可以实现，但是使用文件存储数据具有如下的缺点：

  1. 文件的安全性问题。
  2. 文件不利于数据查询和管理。
  3. 文件不利于存储海量数据。
  4. 文件在程序中控制不方便。

   数据库是数据库专家统一编写的数据库服务，该服务以mysqld的形式呈现，在磁盘上有大量的文件保存数据库和数据库内部的数据，把数据库服务端和数据库及其内部的数据统称为数据库。 总之，数据库是提供较为便捷的数据的存取服务的软件集合或解决方案。

   数据库的存储介质是磁盘为主，内存为辅。mysql也是一种文件系统，重点是针对文件内容。

主流数据库

  • SQL Sever： 微软的产品，.Net程序员的最爱，适合中大型项目。
  • Oracle： 甲骨文产品，适合大型项目，复杂的业务逻辑，并发一般来说不如MySQL。
  • MySQL：世界上最受欢迎的数据库，属于甲骨文，并发性好，不适合做复杂的业务。主要用在电商，SNS，论坛。对简单的SQL处理效果好。
  • PostgreSQL :加州大学伯克利分校计算机系开发的关系型数据库。
  • SQLite： 是一款轻型的数据库，它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低。
  • H2： 是一个用Java开发的嵌入式数据库，它本身只是一个类库，可以直接嵌入到应用项目中。

服务器，数据库，表关系

   安装数据库服务器，只是在机器上安装了一个数据库管理系统程序（mysqld），这个管理程序可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。为保存应用中实体的数据，一般会在数据库中创建多个表，以保存程序中实体的数据。

数据库服务器、数据库和表的关系如下：

在这里插入图片描述

  • Client —— mysql客户端
  • MySQL —— mysqld服务端
  • DB —— 数据库(目录)

基本使用

连接数据库

mysql -h 127.0.0.1 -P 3306 -u root -p

   如果没有写 -h 127.0.0.1 默认是连接本地，如果没有写 -P 3306 默认是连接3306端口号。

创建数据库

​ 本质上实在mysql所在目录创建了一个目录

create database helloworld;

使用数据库

use helloworld;

创建数据库表

​ 本质是在目录中创建对应的文件

create table student(
	id int,
	name varchar(32),
	gender varchar(2)
);

表中插入数据

insert into student (id, name, gender) values (1, '张三', '男');
insert into student (id, name, gender) values (2, '李四', '女');
insert into student (id, name, gender) values (3, '王五', '男');

查询表中的数据

select * from student;

数据逻辑存储

在这里插入图片描述

MySQL架构

   MySQL 是一个可移植的数据库，几乎能在当前所有的操作系统上运行。各种系统在底层实现方面各有不同，但是 MySQL 基本上能保证在各个平台上的物理体系结构的一致性。

在这里插入图片描述

SQL语句分类

  • DDL【data definition language】 数据定义语言，对数据库和表结构，更多是属性操作。代表指令: create, drop, alter
  • DML【data manipulation language】 数据操纵语言，对数据库和表结构，更多的是数据内容操作。代表指令： insert，delete，update。DML中又单独分了一个DQL，数据查询语言，代表指令： select
  • DCL【Data Control Language】 数据控制语言，，操作整个MySQL的系统安全和账户管理工作。代表指令： grant，revoke，commit

存储引擎

   存储引擎是数据库管理系统如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。MySQL的核心就是插件式存储引擎，支持多种存储引擎。 一般在公司中存储引擎选InnoDB，因为支持事务。

查看存储引擎

show engines;

库的操作

创建数据库

语法：

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [,create_specification] ...]

create_specification:
	[DEFAULT] CHARACTER SET charset_name
	[DEFAULT] COLLATE collation_name
  • 大写的表示关键字
  • [] 是可选项
  • CHARACTER SET: 指定数据库采用的字符集
  • COLLATE: 指定数据库字符集的校验规则
  • MySQL大小写不敏感
  • 建库和建表建议都加上

创建一个数据库在Linux机器本质上是在做什么？

  在mysql中建立一个数据库，create database本质上是在Linux机器下建立了一个目录。

创建数据库案例

创建名为 db1 的数据库

create database db1;

   创建数据库没有指定字符集和校验规则时，系统使用默认字符集：utf8，默认校验规则：utf8_ general_ ci

创建一个使用utf8字符集的 db2 数据库

create database db2 charset=utf8;

创建一个使用utf字符集，并带校对规则的 db3 数据库

create database db3 charset=utf8 collate utf8_general_ci;

字符集和校验规则

  MySQL数据库的核心工作不外乎存数据和取数据，字符集保证按照特定的编码来存储数据，数据的校验编码用来取数据进行比较。一般字符集和检验规则是匹配的。设置数据库的编码和校验规则， 其实是影响数据库的内部的表所对应的编码和校验规则。

查看系统默认字符集以及校验规则

show variables like 'character_set_database';
show variables like 'collation_database';

查看数据库支持的字符集

show charset;

查看数据库支持的字符集校验规则

show collation;

校验规则对数据库的影响

  创建一个数据库test1，校验规则使用utf8_ general_ ci[不区分大小写]；再创建一个数据库test2，校验规则使用utf8_ bin[区分大小写]。插入相同的数据 ‘a’ ，‘A’，‘b’，‘B’，进行查询，结果完全不同。

mysql> use test1;
mysql> select * from person where name='a';
+------+
| name |
+------+
| a |
| A |
+------+
2 rows in set (0.01 sec)

mysql> use test2;
mysql> select * from person where name='a';
+------+
| name |
+------+
| a |
+------+
2 rows in set (0.01 sec)

操纵数据库

查看数据库

show databases;

显示创建语句

show create database 数据库名；

mysql> show create database mytest;
+----------+----------------------------------------------------------------+
| Database | Create Database |
| mysql | CREATE DATABASE `mytest` /*!40100 DEFAULT CHARACTER SET utf8 */ |
  • /*!40100 default… */ 这个不是注释，表示当前mysql版本大于4.01版本，就执行这句话。

修改数据库

ALTER DATABASE db_name
[alter_spacification [,alter_spacification]...]
alter_spacification:
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name

  对数据库的修改主要指的是修改数据库的字符集，校验规则。MySQL不支持库名字的修改，但可以修改表名。例如将 mytest 数据库字符集改成 gbk。

mysql> alter database mytest charset = gbk;
Query OK, 1 row affected (0.00 sec)
mysql> show create database mytest;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| mytest | CREATE DATABASE `mytest` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+----------------------------------------------------------------+

删除数据库

DROP DATABASE [IF EXISTS] db_name;

  删除数据后，对应的数据库文件夹被删除，里面的数据表也全部被删，所以不要随意删除数据库。

数据库备份

  数据库备份有两种方案，一种是对数据内容做备份，另外一种是对操作语句做备份，mysqldump 是对于操作语句做备份。

操作：退出连接数据库后，在命令行中输入

mysqldump -P3306 -u root -p 密码 -B 数据库名 > 数据库备份存储的文件路径

如果备份的是其中的一张表

mysqldump -u root -p 数据库名 表名1 表名2 > D:/mytest.sql

备份多个数据库

mysqldump -u root -p -B 数据库名1 数据库名2 ... > 数据库存放路径

  如果备份一个数据库时，没有带上-B参数， 在恢复数据库时，需要先创建空数据库，然后使用数据库，再使用source来还原。

示例：将mytest库备份到文件

mysqldump -P3306 -u root -p123456 -B mytest > D:/mytest.sql

  打开看看 mytest.sql 文件里的内容，其实把整个创建数据库，建表，导入数据的语句都装载这个文件中。

数据库还原

mysql > source D:/mysql-5.7.22/mytest.sql;

查看连接情况

show processlist

  该语句可以告诉我们当前有哪些用户连接到我们的MySQL，如果查出某个用户不是你正常登陆的，很有可能你的数据库被人入侵了。当发现自己数据库比较慢时，可以用这个指令来查看数据库连接情况。

表的操作

创建表

语法：

CREATE TABLE table_name (
	field1 datatype,
	field2 datatype,
	field3 datatype
) character set 字符集 collate 校验规则 engine 存储引擎;

说明：

  • field 表示列名
  • datatype 表示列的类型
  • character set 字符集，如果没有指定字符集，则以所在数据库的字符集为准。
  • collate 校验规则，如果没有指定校验规则，则以所在数据库的校验规则为准。

创建表在Linux机器下是在做什么？

  在mysql中，建立一个表本质上实在数据库目录中创建对应的文件。

创建表案例

create table users (
	id int,
	name varchar(20) comment '用户名',
	password char(32) comment '密码是32位的md5值',
	birthday date comment '生日'
) character set utf8 engine MyISAM;

  不同的存储引擎，创建表的文件不一样。users 表存储引擎是 MyISAM ，在数据目中有三个不同的文件，分别是：users.frm：表结构；users.MYD：表数据；users.MYI：表索引。

在这里插入图片描述

查看表结构

desc 表名;

在这里插入图片描述

显示创建语句

show create table user;

修改表

  修改表实际上是表的结构，比如字段名字，字段大小，字段类型，表的字符集类型，表的存储引擎等等，另外还有可以要添加字段，删除字段。

ALTER TABLE tablename ADD (column datatype [DEFAULT expr][,column datatype]...);

ALTER TABLE tablename MODIfy (column datatype [DEFAULT expr][,column datatype]...);

ALTER TABLE tablename DROP (column);
  • 案例：在users表添加二条记录
mysql> insert into users values(1,'a','b','1982-01-04'),(2,'b','c','1984-01-04');
  • 案例：在users表添加一个字段，用于保存图片路径。（插入新字段后，对原来表中的数据没有影响，原来的数据仍然存在）
mysql> alter table users add assets varchar(100) comment '图片路径' after birthday;

  • 案例：修改name，将其长度改成60
mysql> alter table users modify name varchar(60);
  • 案例：删除password列（删除字段一定要小心，删除字段及其对应的列数据都没了）
mysql> alter table users drop password;

在这里插入图片描述

  • 案例：修改表名为employee
mysql> alter table users rename to employee;(to可以省掉)

在这里插入图片描述

  • 案例：将name列修改为xingming。change 修改列名；rename 修改表名
mysql> alter table employee change name xingming varchar(60); --新字段需要完整定义

删除表

语法：

DROP TABLE [IF EXISTS] tbl_name [, tbl_name] ...

示例：

mysql> drop table t1;

数据类型

数据类型分类

在这里插入图片描述

数值类型

在这里插入图片描述

tinyint

mysql> create table tt1(num tinyint);
mysql> insert into  tt1 values(128); -- 越界插入，报错

  在MySQL中，插入数据不符合要求的时候，会直接终止操作，保证数据库内部的数据是可以预期的。所以数据库中的数据类型本质就是一种约束。

  整型可以指定是有符号的和无符号的，默认是有符号的，可以通过UNSIGNED来说明某个字段是无符号的。

mysql> create table tt2(num tinyint unsigned);
mysql> insert into tt2 values(-1); //越界-- 无符号，范围是： 0 ~ 255

bit

语法：

bit[(M)] : 位字段类型。M表示每个值的位数，范围从1到64。如果M被忽略，默认为1

举例：

mysql> create table tt4 ( id int, a bit(8));
mysql> insert into tt4 values(10, 10);
mysql> select * from tt4; 	#发现很怪异的现象，a的数据10没有出现
+------+------+
| id | a |
+------+------+
| 10 |   |
+------+------+

  上述案例发现很怪异的现象，a的数据10没有出现，这是因为bit字段在显示时，是按照ASCII码对应的值显示。10表示的是换行符，当然不会显示。

建议：

  当有这样只存放0或1的值，可以定义为bit(1)。这样可以节省空间。

mysql> create table tt5(gender bit(1));
mysql> insert into tt5 values(0);
mysql> insert into tt5 values(1);
mysql> insert into tt5 values(2); -- 当插入2时，已经越界了
ERROR 1406 (22001): Data too long for column 'gender' at row 1

float

语法：

float[(m, d)] [unsigned] : m指定显示长度，d指定小数位数，占用空间4个字节

案例：

mysql> create table tt6(id int, salary float(4,2));
mysql> insert into tt6 values(100, -99.99);
mysql> insert into tt6 values(101, -99.991); #多的这一点被拿掉了
mysql> select * from tt6;
+------+--------+
| id | salary |
+------+--------+
| 100 | -99.99 |
| 101 | -99.99 |
+------+--------+
2 rows in set (0.00 sec)

  小数 float(4,2)表示的范围是-99.99 ~ 99.99，MySQL在保存值时会进行四舍五入。如果定义的是float(4,2) unsigned 这时，因为把它指定为无符号的数，范围是0 ~ 99.99。

decimal

语法：

decimal(m, d) [unsigned] : 定点数m指定长度，d表示小数点的位数
  • decimal(5,2) 表示的范围是 -999.99 ~ 999.99
  • decimal(5,2) unsigned 表示的范围 0 ~ 999.99
  • decimal和float很像，但是有区别 : float和decimal表示的精度不一样，float表示的精度大约是7位，decimal整数最大位数m为65，小数最大位数d是30。d缺省默认值为0，m缺省默认值为10。如果希望存储高精度的数据，推荐使用decimal。
mysql> create table tt8 ( 
		id int, 
		salary float(10,8), 
		salary2 decimal(10,8)
		);
mysql> insert into tt8 values(100,23.12345612, 23.12345612);
mysql> select * from tt8;
+------+-------------+-------------+
| id   | salary 	 | salary2     |
+------+-------------+-------------+
| 100  | 23.12345695 | 23.12345612 | 
+------+-------------+-------------+

字符串类型

char

语法：

char(L): 固定长度字符串，L是可以存储的长度，单位为字符，最大长度值为255

案例：

mysql> create table tt9(id int, name char(2));
mysql> insert into tt9 values(100, 'ab');
mysql> insert into tt9 values(101, '中国');
mysql> select * from tt9;
+------+--------+
| id  | name |
+------+--------+
| 100 | ab   |
| 101 | 中国  |
+------+--------+

说明：

  char(2) 表示可以存放两个字符，可以是字母或汉字，但是不能超过2个。mysql中的字符，说的就是一个字符，可以是abcd中任意一个字符/1234中任意一个字符/一个汉字。

varchar

语法：

varchar(L): 可变长度字符串，L表示字符长度，最大长度65535个字节

案例：

mysql> create table tt10(id int ,name varchar(6)); --表示这里可以存放6个字符
mysql> insert into tt10 values(100, 'hello');
mysql> insert into tt10 values(100, '我爱你，中国');
mysql> select * from tt10;
+------+--------------------+
| id 	| name 		 |
+------+--------------------+
| 100 	| hello 	 |
| 100 	| 我爱你，中国 |
+------+--------------------+

说明：

  • 关于varchar(len)，len到底是多大和表的编码密切相关。
  • 一个字符串的长度有上限和它是变长的并不直接冲突。
  • varchar长度可以指定为0到65535之间的值，但是有1 - 3 个字节用于记录数据大小，所以说有效字节数是65532。
  • 当表的编码是utf8时，varchar(n)的参数n最大值是65532/3=21844[因为utf8中，一个字符占用3个字节，如果编码是gbk，varchar(n)的参数n最大65532/2=32766，因为gbk中，一个字符占用2字节。

char和varchar比较

在这里插入图片描述

如何选择定长或变长字符串？

  • 如果数据长度确定，就使用char，比如：身份证，手机号，md5。
  • 如果数据长度有变化，就使用varchar, 比如：名字，地址，但是你要保证最长的能存的进去。
  • 定长的磁盘空间比较浪费，但是效率高。
  • 变长的磁盘空间比较节省，但是效率低。
  • 定长的意义是直接开辟好对应的空间。
  • 变长的意义是在不超过自定义范围的情况下，用多少，开辟多少。

日期和时间类型

常用的日期有如下三个：

  • date :日期 'yyyy-mm-dd' ，占用三字节
  • datetime：日期时间 'yyyy-mm-dd HH:ii:ss' 表示范围从 1000 到 9999 ，占用八字节
  • timestamp ：时间戳，从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致，占用四字节

案例：

//创建表
mysql> create table birthday (t1 date, t2 datetime, t3 timestamp);
//插入数据  添加数据时，时间戳自动补上当前时间
mysql> insert into birthday(t1,t2) values('1997-7-1','2008-8-8 12:1:1'); --插入两种时间
mysql> select * from birthday;
+------------+---------------------+---------------------+
| t1 | t2 | t3 |
+------------+---------------------+---------------------+
| 1997-07-01 | 2008-08-08 12:01:01 | 2022-11-12 18:28:55 | 
+------------+---------------------+---------------------+
//更新数据, 时间戳会更新成当前时间
mysql> update birthday set t1='2000-1-1';
mysql> select * from birthday;
+------------+---------------------+---------------------+
| t1 | t2 | t3 |
+------------+---------------------+---------------------+
| 2000-01-01 | 2008-08-08 12:01:01 | 2012-11-12 18:32:09 |
+------------+---------------------+---------------------+

enum和set

语法：

enum：枚举，“单选” 类型；
enum('选项1','选项2','选项3',...);

set：集合，“多选”类型；
set('选项值1','选项值2','选项值3', ...);

  出于效率考虑，这些值实际存储的是“数字”，当我们添加枚举值和集合值时，也可以添加对应的数字编号。但是不建议在添加枚举值，集合值的时候采用数字的方式，因为不利于阅读。

案例：

  有一个调查表votes，需要调查人的喜好， 比如（登山，游泳，篮球，武术）中去选择[可以多选]，（男，女）[单选]。

mysql> create table votes(
	-> username varchar(30),
	-> hobby set('登山','游泳','篮球','武术'), 
	-> gender enum('男','女'));
mysql>insert into votes values('雷锋', '登山,武术', '男');
mysql>insert into votes values('Juse','登山,武术',2);
mysql>select * from votes where gender=2; 
+----------+---------------+--------+
| username | hobby 	  | gender |
+----------+---------------+--------+
| Juse 	   | 登山,武术 |   女    |
+----------+---------------+--------+

集合查询使用 find_ in_ set 函数：

find_in_set(sub,str_list) ：如果 sub 在 str_list 中，则返回下标；如果不在，返回0；str_list 用逗号分隔的字符串。
  • 案例：查询爱好登山的人
mysql> select * from votes where find_in_set('登山', hobby);
+----------+---------------+--------+
| username | hobby      | gender |
+----------+---------------+--------+
| 雷锋 	  | 登山,武术    | 男 |
| Juse 	   | 登山,武术    | 女 |
| LiLei    | 登山        | 男 |
+----------+---------------+--------+

表的约束

  数据类型可以约束数据，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性。

空属性

  有两个值：null（默认的）和not null(不为空)。数据库默认字段基本都是字段为空，但是实际开发时，尽可能保证字段不为空，因为数据为空没办法参与运算。NULL和空的区别：NULL代表的是没有，空代表的是空串。

  • 案例：创建一个班级表，包含班级名和班级所在的教室。

  站在正常的业务逻辑中：如果班级没有名字，你不知道你在哪个班级，如果教室名字可以为空，就不知道在哪上课。所以我们在设计数据库表的时候，一定要在表中进行限制，满足上面条件的数据就不能插入到表中。这就是“约束”。

mysql> create table myclass(
	-> class_name varchar(20) not null,
	-> class_room varchar(10) not null);
mysql> desc myclass;
+------------+-------------+------+-----+---------+-------+
| Field 	 | Type        | Null | Key  | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| class_name | varchar(20) | NO   |      | NULL    |  |
| class_room | varchar(10) | NO   |      | NULL    |  |
+------------+-------------+------+-----+---------+-------+
//插入数据时,没有给教室数据插入失败：
mysql> insert into myclass(class_name) values('class1');
ERROR 1364 (HY000): Field 'class_room' doesn't have a default value

默认值

  default 表示当用户不显示的向指定列中插入，默认值会自动起效果。not null和defalut一般不需要同时出现，因为default本身有默认值，不会为空。default的约束其实降低了，但是可以保证表中数据的完整性。

mysql> create table tt10 (
	-> name varchar(20) not null,
	-> age tinyint unsigned default 0,
	-> sex char(2) default '男'
	-> );
mysql> desc tt10;
+-------+---------------------+------+-----+---------+-------+
| Field | Type           		| Null | Key | Default | Extra|
+-------+---------------------+------+-----+---------+-------+
| name  | varchar(20)    		| NO   |     |   NULL  | 	 |
| age   | tinyint(3) unsigned   | YES  |     |    0    |     |
| sex   | char(2)        		| YES  |     |    男   |     |
+-------+---------------------+------+-----+---------+-------+

  当数据在插入的时候不给该字段赋值，就使用默认值。只有设置了default的列，才可以在插入值的时候，对列进行省略。

mysql> insert into tt10(name) values('zhangsan');
mysql> select * from tt10;
+----------+------+------+
| name 		| age | sex |
+----------+------+------+
| zhangsan  | 0   |  男 |
+----------+------+------+

列描述

  列描述：comment，没有实际含义，专门用来描述字段，会根据表创建语句保存，用来给程序员或DBA来进行了解。

mysql> create table tt12 (
	-> name varchar(20) not null comment '姓名',
	-> age tinyint unsigned default 0 comment '年龄',
	-> sex char(2) default '男' comment '性别'
	-> );

通过desc查看不到comment的信息，但是通过show可以看到。

zerofil

mysql> show create table tt3\G
	***************** 1. row *****************
		Table: tt3
	Create Table: CREATE TABLE `tt3` (
	`a` int(5) unsigned zerofill DEFAULT NULL,
	`b` int(10) unsigned DEFAULT NULL
	) ENGINE=MyISAM DEFAULT CHARSET=gbk
	1 row in set (0.00 sec)

  show看看tt3表的建表语句，可以看到int(5),这个代表什么意思呢？整型不是4字节码？这个5又代表什么呢？当在tt3中插入（1,2），再看结果。

mysql> select * from tt3;
+-------+------+
| a 	| b |
+-------+------+
| 00001 | 2 |
+-------+------+

  可以看到a的值由原来的1变成00001，这就是zerofill属性的作用，如果宽度小于设定的宽度（这里设置的是5），自动填充0。如果没有zerofill这个属性，括号内的数字是毫无意义的要注意的是，这只是最后显示的结果，在MySQL中实际存储的还是1，00001只是设置了zerofill属性后的一种格式化输出而已。

主键

  primary key用来唯一约束该字段里面的数据，不能重复，不能为空，一张表中最多只能有一个主键；主键所在的列通常是整数类型。可以选择，与业务无关的唯一值作为主键。这样设计，主键值与业务无关，业务调整就不会影响整体主键的表结构。

  • 案例：创建表的时候直接在字段上指定主键
mysql> create table tt13 (
	-> id int unsigned primary key comment '学号不能为空',
	-> name varchar(20) not null
	);
mysql> desc tt13;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO | PRI | NULL | |
| name  | varchar(20)      | NO |  	  | NULL | |
+-------+------------------+------+-----+---------+-------+
  • 案例：主键对应的字段中不能重复，一旦主键重复，操作失败
mysql> insert into tt13 values(1, 'aaa');
mysql> insert into tt13 values(1, 'aaa');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
  • 案例：当表创建好以后但是没有主键的时候，可以再次追加主键，但是一定保证主键列中数据不能有重复，否则无法追加。
alter table 表名 add primary key(字段列表)
  • 案例：删除主键
alter table 表名 drop primary key;
mysql> alter table tt13 drop primary key;

复合主键

  在创建表的时候，在所有字段之后，使用primary key来创建主键，如果有多个字段作为主键，可以使用复合主键。

mysql> create table tt14(
	-> id int unsigned,
	-> course char(10) comment '课程代码',
	-> score tinyint unsigned default 60 comment '成绩',
	-> primary key(id, course) -- id和course为复合主键
	-> );
mysql> desc tt14;
+--------+---------------------+------+-----+---------+-------+
| Field | Type 				  | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| id 	| int(10) unsigned    | NO   | PRI |   0  	 |  	 | 
| course| char(10) 			  | NO   | PRI |    	 | 		 |
| score | tinyint(3) unsigned | YES  |     |   60 	 |       |
+--------+---------------------+------+-----+---------+-------+

mysql> insert into tt14 (id,course)values(1, '123');
Query OK, 1 row affected (0.02 sec)
mysql> insert into tt14 (id,course)values(1, '123');
ERROR 1062 (23000): Duplicate entry '1-123' for key 'PRIMARY' -- 主键冲突

自增长

  auto_increment：当对应的字段不给值，会自动的被系统触发，从当前字段中已经有的最大值+1操作，得到一个新的不同的值。通常和主键搭配使用，作为逻辑主键。

  自增长的特点 : 1.设定为自增长，一定是索引。2.自增长字段必须是整数。3.一张表最多只能有一个自增长。

mysql> create table tt21(
	-> id int unsigned primary key auto_increment,
	-> name varchar(10) not null default ''
	-> );
mysql> insert into tt21(name) values('a');
mysql> insert into tt21(name) values('b');
mysql> select * from tt21;
+----+------+
| id | name |
+----+------+
| 1  | 	a   |
| 2  |  b   |
+----+------+

索引

  在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

  索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。

唯一键

  一张表中有往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键，唯一键就可以解决表中有多个字段需要唯一性约束的问题。**唯一键和主键并不冲突，反而是相互补充的，用来共同维护表的完整性。**主键只是众多具有唯一性的属性列中被选择成为主键而已，其他字段的唯一性，在建表的时候也需要保证，但是主键只能有一个，所以MySQL提供了另一种保证数据列信息唯一性的方案——唯一键。

  关于唯一键和主键的区别，可以简单理解成主键更多的是标识唯一性的。而唯一键更多的是保证在业务上，不要和别的信息出现重复。主键更多的是为了保证我们在查找的时候，找到唯一的一条记录。唯一键更多的是为了保证在表中，各个不同的值，一定要在mysql层面保证该值的唯一性。unique 字段允许为NULL,而且允许多个NULL，而primary key的字段不允许为NULL；并且一张表中最多只能有一个主键，而唯一键可以有多个。

mysql> create table student (
		-> id char(10) unique comment '学号不能重复，但可以为空',
		-> name varchar(10)
		-> );
mysql> insert into student(id, name) values('01', 'aaa');
mysql> insert into student(id, name) values('01', 'bbb'); --唯一约束不能重复
ERROR 1062 (23000): Duplicate entry '01' for key 'id'
mysql> insert into student(id, name) values(null, 'bbb'); -- 但可以为空
mysql> select * from student;
+------+------+
| id   | name |
+------+------+
| 01   | aaa  |
| NULL | bbb  |
+------+------+

外键

  MySQL属于关系型数据库，在表和表之间也一定会产生某种关系。外键用于定义主表和从表之间的关联，维护主表和从表之间的约束。外键约束主要定义在从表上，主表则必须是有主键约束或unique约束。当定义外键后，要求外键列数据必须在主表的主键列存在或为null。

语法：

foreign key (字段名) references 主表(列)

案例：

在这里插入图片描述

先创建主键表

create table myclass (
	id int primary key,
	name varchar(30) not null comment'班级名'
);

再创建从表

create table stu (
	id int primary key,
	name varchar(30) not null comment '学生名',
	class_id int,
	foreign key (class_id) references myclass(id)
);

正常插入数据

mysql> insert into myclass values(10, 'C++大牛班'),(20, 'java大神班');
mysql> insert into stu values(100, '张三', 10),(101, '李四',20);

插入一个班级号为30的学生，因为没有这个班级，所以插入不成功

mysql> insert into stu values(102, 'wangwu',30);
ERROR 1452 (23000): Cannot add or update a child row:a foreign key constraint fails (mytest.stu, CONSTRAINT stu_ibfk_1 FOREIGN KEY (class_id) REFERENCES myclass (id))

插入班级id为null，比如来了一个学生，目前还没有分配班级

mysql> insert into stu values(102, 'wangwu', null);

理解外键约束

  首先这个世界是数据很多都是相关性的。理论上，上面的例子，我们不创建外键约束，就正常建立学生表，以及班级表，该有的字段我们都有。此时，在实际使用的时候，可能会出现什么问题？有没有可能插入的学生信息中有具体的班级，但是该班级却没有在班级表中。因为此时两张表在业务上是有相关性的，但是在业务上没有建立约束关系，那么就可能出现问题。解决方案就是通过外键完成的。建立外键的本质其实就是把相关性交给mysql去审核了，提前告诉mysql表之间的约束关系，那么当用户插入不符合业务逻辑的数据的时候，mysql不允许你插入。

程序员Andrew
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
数据库 - 深入浅出MySQL
SimonxxSun的博客
08-28 443
目录 一、SQL 基础 SQL 分类 DDL 语句 1.创建并使用数据库 2.创建并查看表定义 3.创建外键 4.修改表 (modify,change) DML 语句 1.插入记录 insert into 语句 2.更新记录 update 语句 DCL 语句 数值类型 int(x) char(x) 和 varchar(x) enum 枚举类型 SET 类型 ...
她问我：为什么 MySQL 喜欢 B+ 树？我笑着画了 20 张图
weixin_64314555的博客
12-18 2570
「为什么 MySQL 采用 B+ 树作为索引？」这句话，是不是在面试时经常出现。 要解释这个问题，其实不单单要从数据结构的角度出发，还要考虑磁盘 I/O 操作次数，因为 MySQL 的数据是存储在磁盘中的嘛。 这次，就跟大家一层一层的分析这个问题，图中包含大量的动图来帮助大家理解，相信看完你就拿捏这道题目了！ 怎样的索引的数据结构是好的？ MySQL 的数据是持久化的，意味着数据（索引+记录）是保存到磁盘上的，因为这样即使设备断电了，数据也不会丢失。 磁盘是一个慢的离谱的存储设备，有多离谱呢？
深入浅出系列之数据库知识总结
TK_lTlei的博客
08-29 222
数据库 总体框架 增删查改、分页 索引 并发 事务 锁 存储引擎 分布式 分库分表 主从复制 分布式事务 集群 redis和memcached 数据库的三种模式 模式：所有基本表构成了数据库的模式，也叫关系模式。(基本表) 外模式：视图与部分基本表构成了数据库的外模式。也叫子模式。(视图) 内模式：数据库的存储文件与其索引文件构成了关系数据库的内模式，也称为存储模式。 数据查询...
数据库原理
HugeStaff
05-04 3787
一提到关系型数据库，我禁不住想：有些东西被忽视了。关系型数据库无处不在，而且种类繁多，从小巧实用的 SQLite 到强大的 Teradata 。但很少有文章讲解数据库是如何工作的。你可以自己谷歌/百度一下『关系型数据库原理』，看看结果多么的稀少【译者注：百度为您找到相关结果约1,850,000个…】 ，而且找到的那些文章都很短。现在如果你查找最近时髦的技术（大数据、NoSQL或JavaScript
MySQL基础——《深入浅出MySQL》阅读
u010328808的博客
09-17 886
声明：系列文章的内容参考自《深入浅出MYSQL》。 一、SQL基础 1. SQL语句可以分为三类： DDL（Data Definition Language）：数据库定义语言，数据库、表、列、索引等数据库对象的定义。常用的语句关键字有create、drop、alter等。 DML（Data Manipulation Language）：数据库操纵语言，增删改查数据库记录，并检查数据完整性。
01 - MySQL基本语句
傲寒的博客
07-24 124
目录 1. MySQL基本介绍 Mysql数据库介绍 SQL基本内涵 2. SQL语法与函数 SQL语法基础 SQL常用函数 表连接及子查询 3. SQL综合应用 课堂笔记 1. MySQL基本介绍 1.1 Mysql数据库介绍 数据库（database）：存储数据的仓库，本质就是一个文件系统，将数据以特定格式存储，方便对数据的增删查改。 数据库类型： 关系型数据库：可以保存现实生活中的各种关系数据，数据库中存储数据以表为单位；主流关系型数据库：MySQL，Oracle，SQLServe
数据库—MySQL01
WYJ____的博客
11-25 474
1.数据库简介 2.数据库的安装与使用 3.SQL语句 4.条件查询 一、数据库简介 1.数据库概述 （1）什么是数据库 其本质是一个文件系统，数据库按照特定的格式将数据存储起来，用户可以对数据库中的数据进行增加，修改，删除及查询操作。 （2）数据的存储方式 数据保存在内存 int[] arr = newi...
mysql-5.7.34——Linux版
04-26
MySQL 5.7.34 是 MySQL 数据库管理系统的一个稳定版本，专为 Linux 操作系统设计。在Linux服务器上安装 MySQL 5.7，需要理解以下几个关键知识点： 1. **MySQL简介**：MySQL 是一个开源的关系型数据库管理系统，广泛...
MySQL——强大的数据库.pdf
10-10
"MySQL——强大的数据库" MySQL 是一种强大的关系型数据库管理系统，它具有开放源码和商业双重授权模式，提供了高度的可扩展性和灵活性。下面是 MySQL 的一些关键特点和知识点： 1. 安装 MySQL：在安装 MySQL 之前...
mysql-8.0.1264——安装配置文件msyql.ini
09-02
首次安装及启动mysql的 配置文档
MySQL——数据库表.rar
08-04
MySQL是一种广泛使用的开源关系型...以上是对MySQL数据库表的一些基础知识点的概述，实际应用中还有更多高级特性和策略，如复制、主从同步、分区策略、存储引擎选择等，都需要根据具体应用场景进行深入学习和实践。
mysql数据库基础.pdf
01-02
事务具有四大特性——ACID（原子性、一致性、隔离性和持久性）。原子性确保事务中的所有操作要么全部成功，要么全部失败；一致性确保事务完成后，数据库状态符合业务逻辑；隔离性防止并发事务间的干扰，确保数据的...
MYSQL
maoqiwei的博客
07-23 259
1.修改student 表中年龄(sage)字段属性，数据类型由int 改变为smallint 2.为Course表中Cno 课程号字段设置索引,并查看索引 3.为SC表建立按学号(sno)和课程号(cno)组合的升序的主键索引，索引名为SC_INDEX4.创建一视图 stu info,查询全体学生的姓名，性别，课程名，成绩5.删除所有索引
MySQL字段设置的varchar长度小于数据长度自动截取丢弃超出的长度而不是报错？
weixin_43831997的博客
07-23 358
当MySQL数据库设置为==严格模式（STRICT_TRANS_TABLES 或 STRICT_ALL_TABLES）==时，如果尝试插入或更新超过设定长度的数据，将会导致错误，并阻止该事务进行。在默认情况下，或者在非严格模式（NO_STRICT）下，当插入长度超出 VARCHAR 字段定义的字符串时，MySQL会自动截断超出的部分，并发出一个警告，而不是错误。
MySQL中的CREATE EVENT 语句详解
最新发布
u011565038的博客
07-24 526
CREATE EVENT语句在 MySQL 中用于创建一个事件调度器（Event Scheduler）的事件。事件调度器允许你安排数据库任务（如查询、数据更新等）在将来的某个时间点自动执行，或者根据特定的时间间隔重复执行。这对于需要定期执行的任务特别有用，比如数据清理、报表生成等。
Mysql (五)
qq_64410777的博客
07-23 527
以左边的表为基础，接收左表的所有行，以左表的记录与右表的记录进行匹配，匹配左表的所有，以及右表中符合条件的行，不符合的显示null值。表和列的别名 在实际工作中，表和列名可能很长，写起来不方便，如果需要多次声明表和列时，全部展示太复杂，设置别名使书写简化。这里只是判断，结果为空 则不执行 ，如果不为空则执行前面的 结果是7 只要不为空就是7 执行的是count(*)SELECT i.name 姓名,i.score 成绩 from info as i;
[Mysql-DML数据操作语句]
2301_78794952的博客
07-23 1034
insert into student (sid,sname,birthday,ssex,classid) values(9,'张三','2007-1-1','男',1);insert into student(sname,ssex) values('某某1','男'),('某某2','女'),('某某3','男');insert into student values(9,'张三','2007-1-1','男',1);-- 将姓名为某某的生日改为1888-1-1。
Mysql - 索引
m0_69594200的博客
07-23 948
存储引擎、SQL优化工具、SQL优化，索引的分类、语法和使用规则等【笔记】内容
深入MySQL中的IF和IFNULL函数
一起coding，一起嗨。
07-23 414
深入MySQL中的IF和IFNULL函数
MySQL数据库基础教程——传智播客韩忠康
"传智播客的韩忠康老师分享的关于MySQL的PPT教程，内容涵盖数据库基础知识、MySQL的特点及应用、关系型数据库概念以及MySQL的架构等。" 在IT行业中，数据库扮演着至关重要的角色，它为管理和处理大量信息提供了一种...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

程序员Andrew

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值