当前位置：移动技术网 > IT编程>数据库>Mysql > #Linux小白#篇十六之MySQL（二、表管理及数据类型）

#Linux小白#篇十六之MySQL（二、表管理及数据类型）

2020年07月30日 | 移动技术网IT编程 | 我要评论

表管理及数据类型

数据类型
表操作

创建表(表的基本操作)
查看表结构
表完整性约束

设置唯一约束 UNIQUE
设置主键约束 PRIMARY KEY
设置字段值增 AUTO_INCREMENT

修改表

示例：

复制表
删除表
扩展

库操作

数据类型

分类：
数值类型
字符串类型
时间和日期类型

数值类型：
整数类型 TINYINT SMALLINT MEDIUMINT INT BIGINT
作用：用于存储用户的年龄、游戏的Level、经验值等。
在这里插入图片描述
浮点数类型 FLOAT DOUBLE
作用：用于存储用户的身高、体重、薪水等
float(5.3) 5宽度 3精度
宽度不算小数点
mysql> create table t12(id float(6,2));
mysql> insert into t1 values (‘2.22’);

定点数类型 DEC
定点数在MySQL内部以字符串形式存储，比浮点数更精确，适合用来表示货币等精度高的数据。
位类型 BIT
BIT(M)可以用来存放多位二进制数，M范围从1~64，如果不写默认为1位

字符串类型：
CHAR系列 CHAR VARCHAR
TEXT系列 TINYTEXT TEXT MEDIUMTEXT LONGTEXT
BLOB 系列 TINYBLOB BLOB MEDIUMBLOB LONGBLOB
BINARY系列 BINARY VARBINARY
枚举类型： ENUM
集合类型： SET
在这里插入图片描述
枚举类型：枚举列可以把一些不重复的字符串存储成一个预定义的集合
mysql> create table enum_table( e ENUM(‘fish’,‘apple’,‘dog’));
Query OK, 0 rows affected (0.35 sec)
mysql> insert into enum_table(e) values(‘fish’);
Query OK, 1 row affected (0.11 sec)

mysql> select * from enum_table;
±-----+
| e |
±-----+
| fish |
±-----+
1 row in set (0.00 sec)

mysql> insert into enum_table(e) values(‘nihao’);
ERROR 1265 (01000): Data truncated for column ‘e’ at row 1

mysql> insert into t8(id1) values(‘20180109000000’);
mysql> select * from t8;
±--------------------±-----+
| id1 | d2 |
±--------------------±-----+
| 2018-01-09 00:00:00 | NULL |
±--------------------±-----+
1 row in set (0.00 sec)

表操作

SQL结构化查询语言分类
1、DDL:定义语言，定义数据库、表、视图、索引过程，eg：create、drop……
2、DML：操作语言，eg：插入数据insert、删除数据delete、更新数据update……
3、DQL：查询语言，select
4、DCL：控制语言，eg：控制用户访问权限，grant、remove

MySQL表操作 DDL（数字可不加引号，字符串必须加）
表是数据库存储数据的基本单位，由若干个字段组成，主要用来存储数据记录。
表的操作包括:
创建表、查看表、修改表和删除表。

这些操作都是数据库管理中最基本，也是最重要的操作。
本节内容包括:
创建表—— create table
查看表结构—— desc table, show create table（看的是创建的过程）
修改表—— alter table
复制表—— create table
删除表—— drop table

创建表(表的基本操作)

语法：
create table 表名(自定义)(
字段名1 类型[(宽度) 约束条件],约束条件：是否是主键，是否为空
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
)[存储引擎字符集];
在同一张表中，字段名是不能相同
宽度和约束条件可选
字段名和类型是必须的

示例：创建如下表格
表:school.student1

id	name	sex	age
1	tom	male	23
2	Jack	male	21
3	Alice	famale	-19

//创建数据库school
mysql> CREATE  DATABASE school;				        
mysql> use school;
mysql> create table student1(
    -> id int,
    -> name varchar(50),                 
    -> sex enum('m','f'),
    -> age int
    -> );
Query OK, 0 rows affected (0.03 sec)

// 向表中插入内容
语法：
insert into 表名(字段1,字段2...)  values(字段值列表...);
mysql> insert into student1(id,name,sex,age) values(1,'xingdia','m','26');

// 查看表结构
mysql> desc student1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | YES  |     | NULL    |       |
| name  | varchar(50)   | YES  |     | NULL    |       |
| sex   | enum('m','f') | YES  |     | NULL    |       |
| age   | int(11)       | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> select id,name,sex,age from student1;	//查询表中所有字段的值
Empty set (0.00 sec)

mysql> select * from student1;		/查询表中所有字段的值
Empty set (0.00 sec)

mysql> select name,age from student1;		      //查询表中指定字段的值
Empty set (0.00 sec)

mysql> insert into student1 values (1,'xingdian','m',33),(2,'alice','m',20),(3,'jack','m',40);                                 //按表中字段顺序插入，可以不写字段名，插入多个用逗号隔开
Query OK, 3 rows affected (0.14 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into student1(name,age) values ('zhuzhu',10),('gougou',20);          //只向指定的字段插入值
Query OK, 2 rows affected (0.12 sec)
Records: 2  Duplicates: 0  Warnings: 0

查看表（当前所在库）
mysql> show tables;			                                    
+------------------+
| Tables_in_school |
+------------------+
| student1         |
+------------------+
1 row in set (0.00 sec)
查看当前在哪个库
mysql> show tables;	
+------------------+
| database（）      |
+------------------+
| school           |
+------------------+

表school.student2

	字段名	数据类型
编号	id	int
姓名	name	varchar(50)
出生年份	born_year	year
生日	birthday	date
上课时间	class_time	time
注册时间	reg_time	datetime

mysql> create table student2(
    id int,
    name varchar(50),         
    born_year year,
    birthday date,
    class_time time,
    reg_time datetime 
    );
mysql> desc student2;
mysql> insert into student2 values(1,'tom',now(),now(),now(),now());#now()是用来获取当前时间
mysql> insert into student2 values(2,'jack',1982,19821120,123000,20140415162545);

now（）函数出来的时间都是一样的，但系统会自动根据字段规定的数据类型，截取时间
在这里插入图片描述
表school.student3

id	id	int
姓名	name	varchar(50)
性别	sex	enum(‘male’,‘female’)
爱好	hobby	set(‘music’,‘book’,‘game’,‘disc’)

mysql> create table student3(
     id int,
     name varchar(50),
     sex enum('male','female'),
     hobby set('music','book','game','disc')
     );

mysql> desc student3;
mysql> show create table student3\G    
mysql> insert into student3 values (1,'tom','male','book,game');
mysql> insert into student3 values (2,'jack','male','film');
mysql> select * from student3;

查看表结构

DESCRIBE查看表结构
DESCRIBE 表名;
DESC 表名;

SHOW CREATE TABLE查看表详细结构
SHOW CREATE TABLE 表名;

表完整性约束

作用：用于保证数据的完整性和一致性

约束条件	说明
PRIMARY KEY (PK)	标识该字段为该表的主键，可以唯一的标识记录，不可以为空 UNIQUE + NOT NULL
FOREIGN KEY (FK)	标识该字段为该表的外键，实现表与表（父表主键/子表1外键/子表2外键）之间的关联
NOT NULL	标识该字段不能为空
UNIQUE KEY (UK)	标识该字段的值是唯一的，可以为空，一个表中可以有多个UNIQUE KEY
AUTO_INCREMENT	标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT	为该字段设置默认值

说明：

是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值

sex enum('male','female') not null default 'male' #不可以为空，默认值是male
age int unsigned NOT NULL default 20 		#必须为正值（无符号） 不允许为空  默认是20

是否是key
主键 primary key 唯一标识，不可为空
外键 forengn key 连接多个表

NOT NULL
表school.student4

mysql> create table school.student4(
    id int not null,
   name varchar(50) not null,
    sex enum('m','f') default 'm' not null,
     age int unsigned default 18 not null,
    hobby set('music','disc','dance','book') default 'book,dance'
   );

mysql> insert into student4 values(1,'jack','m',20,'book');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student4;

mysql> insert into student4(id,name) values(2,'robin');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student4 values(3,NULL,'m',40,'book');
ERROR 1048 (23000): Column 'name' cannot be null

设置唯一约束 UNIQUE

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
表company.department1

CREATE TABLE company.department1 (
		dept_id INT,
		dept_name VARCHAR(30) UNIQUE, 
		comment VARCHAR(50)
		);
mysql> desc department1;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_id   | int(11)     | YES  |       | NULL    |       |
| dept_name | varchar(30) | YES  | UNI | NULL    |       |
| comment   | varchar(50) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

（unique表示这个值是唯一的，不能重复，如下图只能创建一个CEO）

在这里插入图片描述

设置主键约束 PRIMARY KEY

primary key 字段的值是不允许重复，且不允许不NULL（UNIQUE + NOT NULL）
单列做主键
表school.student6 方法一

mysql> create table student6(
     id int primary key not null auto_increment, #auto_increment自动自动增长
    name varchar(50) not null,
    sex enum('male','female') not null default 'male', 
    age int not null default 18
    );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into student6 values (1,'alice','female',22);#按字段依次插入

mysql> insert into student6(name,sex,age) values
     ('jack','male',19),
     ('tom','male',23);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from student6;
+----+-------+------+-----+
| id | name  | sex  | age |
+----+-------+------+-----+
|  1 | alice | female |  22 | 
|  2 | jack  | male  |  19 | 
|  3 | tom   | male  |  23 | 
+----+-------+------+-----+
3 rows in set (0.00 sec)

设置字段值增 AUTO_INCREMENT

表company.department3

CREATE TABLE department3 (
	dept_id INT PRIMARY KEY AUTO_INCREMENT,
	dept_name VARCHAR(30),
	comment VARCHAR(50)
	);

如下所示，没有设置id，但是可以执行成功，因为id是自动增长的
在这里插入图片描述

修改表

语法：

修改表名

 ALTER TABLE 表名  RENAME 新表名;

增加字段

  ALTER TABLE 表名
                    	  ADD 字段名  数据类型 [完整性约束条件…],
                          ADD 字段名  数据类型 [完整性约束条件…];

  ALTER TABLE 表名
	                  	  ADD 字段名1  数据类型 [完整性约束条件…]  AFTER 字段名2;#在字段名2后增加字段名1

示例：增加一个字段在age之后
在这里插入图片描述

删除字段

ALTER TABLE 表名 DROP 字段名;

修改字段

  ALTER TABLE 表名 
	                      MODIFY 字段名 数据类型 [完整性约束条件…];#改类型
  ALTER TABLE 表名 
	                      CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…]; #改字段名
  ALTER TABLE 表名 
	                      CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];  #字段名和数据类型都修改

示例：

示例：
1. 修改存储引擎
mysql> alter table service 
        -> engine=innodb;                        //engine=myisam|memory|....

2. 添加字段
mysql> create table student10 (id int);
mysql> alter table student10
    -> add name varchar(20) not null,
    -> add age int not null default 22;
    
mysql> alter table student10
    -> add stu_num int not null after name;		                        //添加name字段之后

mysql> alter table student10                        
    add sex enum('male','female') default 'male' first;		        //添加到最前面

3. 删除字段
mysql> alter table student10
        -> drop sex;

mysql> alter table service
        -> drop mac;

4. 修改字段类型modify
mysql> alter table student10
        -> modify age tinyint  not null ;         //注意保留原有的约束条件
    
mysql> alter table student10
        -> modify id int not null primary key ;	            //修改字段类型、约束、主键

5. 增加约束（针对已有的主键增加auto_increment）
mysql> alter table student10 modify id int not null primary key auto_increment;         //错误，该字段已经是primary key
ERROR 1068 (42000): Multiple primary key defined

mysql> alter table student10 modify id int not null auto_increment;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

6. 增加主键
mysql> alter table student1
        -> add primary key(id);

7. 增加主键和自动增长
mysql> alter table student1
        -> modify id int not null primary key auto_increment;

8. 删除主键[primary key auto_increment]
a. 删除自增约束
mysql> alter table student10 modify id int not null; 

b. 删除主键
mysql> alter table student10                                 
        -> drop primary key;

复制表

复制表结构＋记录（key不会复制: 主键、外键和索引）复制表结构/记录+表结构，不会将Key（表描述）复制

mysql> create table new_service select * from service;

可以复制主键,只复制表结构。。。

mysql> create table t4 like employees;

只复制表结构

mysql> create table new1_service select * from service where 1=2;  //条件为假，查不到任何记录

删除表

DROP TABLE 表名;

扩展

修改数据表中字段的值:
语法：

Update 表名 set 列名=值where 条件
update student set name='123' where id=1

在这里插入图片描述
删除某一行：
语法：

delete from 表名 where id=1
delete from type where id=1

库操作

系统数据库
information_schema：虚拟库，主要存储了系统中的一些数据库对象的信息，例如用户表信息、列信息、权限信息、字符信息等
performance_schema：主要存储数据库服务器的性能参数
mysql：授权库，主要存储系统用户的权限信息
sys：主要存储数据库服务器的性能参数

创建数据库：DDL

#mysqladmin -u root -p1 create db1
直接去创建数据库目录并且修改权限
mysql> create database xingdian;

数据库命名规则：
区分大小写
唯一性
不能使用关键字如 create select
不能单独使用数字

查看数据库

mysql> show databases;
mysql> show create database xingdian;查看创建数据库的详细信息-
mysql> select database(); 查看当前所在的库

切换数据库

mysql> use xingdian;
mysql> show tables;

删除数据库

DROP DATABASE 数据库名;

使用非交互式查看数据库信息
在这里插入图片描述

本文地址：https://blog.csdn.net/huhuhuhu555/article/details/107643539

您可能感兴趣的文章:

如对本文有疑问，点击进行留言回复！！

MySQL学习11_表连接

文章目录表连接表连接方式表连接在多个表之间通过一定的连接条件，使表之间发生关联，进而能从多个表之间获取数据表连接... [阅读全文]
MySql – 浅谈MyISAM与InnoDB的区别及选择

MySql – 浅谈MyISAM与InnoDB的区别及选择1.MyISAM2.InnoDB3.MyISAM与In... [阅读全文]
ISAM、MyISAM、InnoDB、ACID详解

ISAM索引顺序存取方法(ISAM, Indexed Sequential Access Method)最初是I... [阅读全文]
MySQL学习日记（五）MySQL事务和字符集

MySQL事务和字符集1、事务当多个用户访问同一数据时，一个用户在更改数据的过程中可能有其它用户同时发起更改请求... [阅读全文]
MySQL学习7_数据库表数据查询

文章目录简单查询条件查询where比较条件查询逻辑运算符模糊查询 like范围查询简单查询select * fr... [阅读全文]
MySQL-关系代数-并、交、差、等值连接、自然连接、左连接。。。

如果本文对您有所帮助，可以点一下赞????最近找实习要用到数据库，刚好复习了一下，记录笔记1、传统的集合运算是二... [阅读全文]
mysql技术4--索引及执行计划

文章目录一.索引的介绍1. 索引的作用2. 索引的分类(算法)3. BTREE索引算法演变4. Btree索引功... [阅读全文]
MySQL substring()函数简介

Mysql中，SUBSTRING 函数从给定字符串中指定的位置开始返回一个指定长度的子字符串。语法1：SUBST... [阅读全文]
MySQL数据库的19个常用命令

MySQL常用名令1.登录mysql –h 主机名 –u用户名 –p密码2.显示所有数据库show databa... [阅读全文]
mysql安装问题和密码问题

第一步：下载mysql-5.7.17-winx64解压版本：http://dev.mysql.com/downl... [阅读全文]