运维开发宝典026-MySQL02数据库表操作

大家好我是云计算磊哥从业20年的IT老鸟。运维培训15年总结了一套从入门到精通的全运维开发宝典手册。准备用300天时间写一套博文手把手从安装软件讲起从行业到产品从过去到未来从理论到操作从视频到文档工具一站式。从零基础入门到20k运维开发工程师岗位诸多就业问题。多方位全方面的给你讲清楚云计算这个行业该如何做。关注我。后续AI大模型开发课程更精彩。开源数据库MySQL DBA运维实战 表1 初识SQL语言SQLStructured Query Language 即结构化查询语言DDL data definition language语句 数据库定义语言 数据库、表、视图、索引、存储过程例如CREATE DROP ALTERDMLdata manipulation language语句 数据库操纵语言 插入数据INSERT、删除数据DELETE、更新数据UPDATEDQL Data Query Language 语句 数据库查询语言 查询数据SELECTDCL Data Control Language语句 数据库控制语言 例如控制用户的访问权限GRANT、REVOKE2 名词解释数据库服务器: 运行着数据库应用程序的设备.数据库: 表的管理单元.默认数据库:information_schema 虚拟库主要存储了系统中的一些数据库对象的信息例如用户表信息、列信息、权限信息、字符信息等performance_schema 主要存储数据库服务器的性能参数mysql 授权库主要存储系统用户的权限信息sys 主要存储数据库服务器的性能参数表: 记录的管理单元记录: 字段的管理单元字段: 字段名字段类型长度字段约束组成类型: 字符数字日期约束: 不能为空自动增长等图示3 DDL1库3.1 定义库创建业务数据库语法CREATE DATABASE 数据库名;要求区分大小写唯一性不能使用关键字如 create select不能单独使用数字查看数据库语法 SHOW DATABASES;选择数据库USE 数据库名查询当前库SELECT database();删除数据库DROP DATABASE 数据库名;系统中的位置/usr/local/mysql/data/ 源码安装/var/lib/mysql/ RPM安装3.2 数据类型MySQL常见的数据类型前言在MySQL数据库管理系统中可以通过存储引擎来决定表的类型。同时MySQL数据库管理系统也提供了数据类型决定表存储数据的类型。数值类型​ 整数类型 INT​ 浮点数类型 FLOAT DOUBLE​ 定点数类型 DEC​ 位类型 BIT字符串类型​ CHAR系列 CHAR VARCHAR​ TEXT系列 TEXT​ BLOB 系列 BLOB​ BINARY系列 BINARY VARBINARY​ 枚举类型ENUM​ 集合类型 SET时间和日期类型​ DATETIMEDATETIMETIMESTAMPYEAR数据类型测试整数类型测试tinyint,int1 创建一个表 mysql create table test1( tinyint_test tinyint, int_test int ); 2 查询表结构 mysql desc test1; ----------------------------------------------------- | Field | Type | Null | Key | Default | Extra | ----------------------------------------------------- | tinyint_test | tinyint(4) | YES | | NULL | | | int_test | int(11) | YES | | NULL | | ----------------------------------------------------- 2 rows in set (0.01 sec) 3 插入数值 mysql insert into test1 values (111,111); Query OK, 1 row affected (0.09 sec) mysql insert into test1(tinyint_test) values(128); ERROR 1264 (22003): Out of range value for column tinyint_test at row 1 mysql insert into test1(int_test) values(2147483647); Query OK, 1 row affected (0.05 sec) mysql insert into test1(int_test) values(2147483648); ERROR 1264 (22003): Out of range value for column int_test at row 1 结论 TINYINT有符号型最大127 INT有符号型最大2147483647浮点数类型测试float1创建一个表 mysqlcreate table test4(float_test float(5,2));//一共5位小数占2位 Query OK,0rows affected(0.00sec)2查询表结构 mysqldesc test4;---------------------------------------------------|Field|Type|Null|Key|Default|Extra|---------------------------------------------------|float_test|float(5,2)|YES||NULL||---------------------------------------------------1rowinset(0.00sec)3插入合法数据非法数据 mysqlinsert into test4 values(10.2),(70.243),(70.246);Query OK,3rows affected(0.00sec)Records:3Duplicates:0Warnings:04查询表内容 mysqlselect* from test4;------------|float_test|------------|10.20||70.24||70.25|------------3rowsinset(0.00sec)5插入非法数据 mysqlinsert into test4 values(1111.2);ERROR1264(22003): Out of range valueforcolumnfloat_testat row1时间和日期类型测试year、date、time、datetime、timestamp1 创建一个表 mysql create table test_time( d date, t time, dt datetime ); Query OK, 0 rows affected (0.03 sec) 2查看表结构 mysql desc test_time; -------------------------------------------- | Field | Type | Null | Key | Default | Extra | -------------------------------------------- | d | date | YES | | NULL | | | t | time | YES | | NULL | | | dt | datetime | YES | | NULL | | -------------------------------------------- 3 rows in set (0.01 sec) 3插入时间 mysql insert into test_time values(now(),now(),now()); Query OK, 1 row affected, 1 warning (0.02 sec) 4查看表内容 mysql select * from test_time; ------------------------------------------- | d | t | dt | ------------------------------------------- | 2016-12-18 | 00:06:10 | 2013-12-18 00:06:10 | ------------------------------------------- 1 row in set (0.00 sec)字符串类型测试CHAR、VARCHAR1创建表 mysql create table vc ( v varchar(4), c char(4) ); Query OK, 0 rows affected (0.03 sec) 2 查看表结构 mysql desc vc; ---------------------------------------------- | Field | Type | Null | Key | Default | Extra | ---------------------------------------------- | v | varchar(4) | YES | | NULL | | | c | char(4) | YES | | NULL | | ---------------------------------------------- 2 rows in set (0.00 sec) 3插入数据 mysql insert into vc values(a,a); Query OK, 1 row affected, 1 warning (0.00 sec) mysql insert into vc values(ab ,ab ); Query OK, 1 row affected, 1 warning (0.00 sec) 4查询表内容 mysql select * from vc; ------------ | v | c | ------------ | a | a | | ab | ab | ------------ 1 row in set (0.00 sec) 5调动函数 length(v) 统计长度的函数 mysql select length(v),length(c) from vc; ---------------------- | length(v) | length(c) | ---------------------- | 1 | 1| | 4 | 2 | ---------------------- 1 row in set (0.00 sec) mysql select concat(v,), concat(c,) from vc; //在后面加字符看的更清楚 ------------------------------ | concat(v,) | concat(c,) | ------------------------------ | a | a | | ab | ab | ------------------------------ 1 row in set (0.00 sec) 在检索的时候CHAR列删除了尾部的空格而VARCHAR则保留这些空格枚举类型、集合类型ENUM类型SET测试作用字段的值只能在给定范围中选择 常见的是单选按钮和复选框 enum 单选 只能在给定的范围内选一个值如性别 sex 男male/女female set 多选 在给定的范围内可以选择一个或一个以上的值爱好1,爱好2,爱好3...目标表school.student3 姓名 name varchar(50) 性别 sex enum(m,f) 爱好 hobby set(music,book,game,disc)示例1 创建表 mysql use school mysql create table student3( name varchar(50), sex enum(m,f), hobby set(music,book,game,disc) ); Query OK, 0 rows affected (0.31 sec) 2查看表结构 mysql desc student3; --------------------------------------------------------------------- | Field | Type | Null | Key | Default | Extra | --------------------------------------------------------------------- | name | varchar(50) | YES | | NULL | | | sex | enum(m,f) | YES | | NULL | | | hobby | set(music,book,game,disc) | YES | | NULL | | --------------------------------------------------------------------- 3 rows in set (0.00 sec) mysql show create table student3\G *************************** 1. row *************************** Table: student3 Create Table: CREATE TABLE student3 ( name varchar(50) default NULL, sex enum(m,f) default NULL, hobby set(music,book,game,disc) default NULL ) ENGINEMyISAM DEFAULT CHARSETlatin1 1 row in set (0.00 sec) 3插入数据 mysql insert into student3 values (tom,m,book,game); Query OK, 1 row affected (0.00 sec) 4查询数据 mysql select * from student3; ----------------------- | name | sex | hobby | ----------------------- | tom | boy | book,game | ----------------------- 1 row in set (0.00 sec) 5插入非法数据 mysql insert into student3 values (jack,m,film); ERROR 1265 (01000): Data truncated for column hobby at row 1 超出范围3.3 DDL2表前言在了解的数据类型后下面我们就完整的学习一张表该如何管理。 通过什么类型存储什么样的数据呢 表的创建之后后期该如何管理呢分类MySQL表操作 DDL目的表是数据库存储数据的基本单位由若干个字段组成主要用来存储数据记录。表的操作 包括创建表、查看表、修改表和删除表。 这些操作都是数据库管理中最基本也是最重要的操作。本节内容包括:命令汇总创建表 create table 查看表结构 desc table, show create table 表完整性约束 修改表 alter table 复制表 create table ... 删除表 drop table表school.student1示例1.创建表 mysql CREATE DATABASE school; //创建数据库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) 2.查看所有表 mysql show tables; //查看表当前所在库 ------------------ | Tables_in_school | ------------------ | student1 | ------------------ 1 row in set (0.00 sec) 3.查看表内容 mysql select * from student1; //查询表中所有字段的值 Empty set (0.00 sec) mysql select id,name,sex,age from student1; //查询表中所有字段的值 Empty set (0.00 sec) mysql select name,age from student1; //查询表中指定字段的值 Empty set (0.00 sec) 4.查看表结构 mysql desc student1; 5.表中插入内容 mysqlinsert into student values (1,zhangsan,m,33) , (2,lisi,f,20) , (1,wangwu,m,40) , 6.查询表的内容 mysql select * from student3.4 完整性约束[扩展]目的由于生活中需要避免重名的情况所以数据库中使用完整性约束来限定此类要求。 用于保证数据的完整性和一致性约束类型PRIMARY KEY (PK) 标识该字段为该表的主键可以唯一的标识记录不可以为空 UNIQUE NOT NULL 员工信息表 FOREIGN KEY (FK) 标识该字段为该表的外键实现表与表父表主键/子表1外键/子表2外键之间的关联 员工工资表 UNIQUE KEY (UK) 标识该字段的值是唯一的可以为空一个表中可以有多个UNIQUE KEY AUTO_INCREMENT 标识该字段的值自动增长整数类型而且为主键 DEFAULT 为该字段设置默认值 字段是否有默认值缺省的默认值是NULL如果插入记录时不给字段赋值此字段使用默认值 NOT NULL 标识该字段不能为空 是否允许为空默认NULL可设置NOT NULL字段不允许为空必须赋值 UNSIGNED 无符号正数 ZEROFILL 使用0填充例如0000001约束类型测试默认值、空值DEFAULT、NOT NULL目的 1 某列设置为“不允许为空”请思考可否不插入数值。 2 某列设置默认值并不输入数值请思考该列是否会显示默认值。 1创建表 mysql create table student.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 ); Query OK, 0 rows affected (0.01 sec) 2 查看表结构 MariaDB [student] desc student4; ------------------------------------------------------------------------- | Field | Type | Null | Key | Default | Extra | ------------------------------------------------------------------------- | id | int(11) | NO | | NULL | | | name | varchar(50) | NO | | NULL | | | sex | enum(m,f) | NO | | m | | | age | int(10) unsigned | NO | | 18 | | | hobby | set(music,disc,dance,book) | YES | | dance,book | | ------------------------------------------------------------------------- 5 rows in set (0.00 sec) 3 插入数据 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 select * from student4; 4插入非法数据 随便找一列插入空值 mysql insert into student4 values(3,NULL,m,40,book); ERROR 1048 (23000): Column name cannot be null 注意观察输出错误的结果理解默认值和空值的含义。NULL函数是空值的意思。设置唯一约束 UNIQUE目的 1 unique唯一的特性。 2 unique是可以为空的。 为部门创建一张员工信息表 1 创建表 表company.department1 mysql CREATE TABLE company.department1 (dept_id INT, dept_name VARCHAR(30) UNIQUE, comment VARCHAR(50) ); 2查看表结构 观察UNIQUE的显示位置 MariaDB [(none)] desc company.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 | | --------------------------------------------------- 3 rows in set (0.00 sec) 3插入数据 insert into department1 values (1,zhangsan,yyy); insert into department1 values (1,NULL,yyy); 非法数据 insert into department1 values (1,zhangsan,yyy); ERROR 1062 (23000): Duplicate entry zhangsan for key dept_name设置主键约束 PRIMARY KEY目的 1.primary key 字段的值是不允许重复且不允许NULLUNIQUE NOT NULL 2.单列做主键 3.多列做主键复合主键 1创建表 表school.student6 方法一 mysql create table student6( id int primary key not null 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) 2查询表结构 desc student6; ------------------------------------------------------------------ | Field | Type | Null | Key | Default | Extra | ------------------------------------------------------------------ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(50) | NO | | NULL | | | sex | enum(male,female) | NO | | male | | | age | int(11) | NO | | 18 | | ------------------------------------------------------------------ 4 rows in set (0.00 sec) 3插入数据 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 4查询表内容 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) 5 插入非法数据 MariaDB [company] insert into student6(name,sex,age) values (3,jack,male,19); ERROR 1136 (21S01): Column count doesnt match value count at row 1 6 主键设置了自动增长再次尝试插入数据。成功注意不要插入主键3.5 DDL3(修改)[课下作业]分类修改表名ALTER TABLE 表名 RENAME 新表名;增加字段ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…], ADD 字段名 数据类型 [完整性约束条件…]; 追加 ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] FIRST; 首列 ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名; 中间删除字段ALTERTABLE表名DROP字段名;修改字段ALTERTABLE表名MODIFY字段名 数据类型[完整性约束条件…];ALTERTABLE表名 CHANGE 旧字段名 新字段名 旧数据类型[完整性约束条件…];ALTERTABLE表名 CHANGE 旧字段名 新字段名 新数据类型[完整性约束条件…];示例1.修改表名ALTERTABLEt1RENAMEt2;2.添加字段 mysqlcreatetablestudent10(idint);mysqlaltertablestudent10-addnamevarchar(20)notnull,-addageintnotnulldefault22;mysqlaltertablestudent10-addstu_numintnotnullaftername;//添加name字段之后mysqlaltertablestudent10-addsexenum(male,female)defaultmalefirst;//添加到最前面3.删除字段 mysqlaltertablestudent10-dropsex;mysqlaltertableservice-dropmac;4.删除主键[primarykeyauto_increment]a.删除自增约束 mysqlaltertablestudent10modifyidintnotnull;b.删除主键 mysqlaltertablestudent10-dropprimarykey;5.1修改字段类型modifymysqlaltertablestudent10-modifyagetinyintnotnulldefault22;//注意保留原有的约束条件mysqlaltertablestudent10-modifyidintnotnullprimarykey;//修改字段类型、约束、主键5.2增加约束针对已有的主键增加auto_increment mysqlaltertablestudent10modifyidintnotnullprimarykeyauto_increment;//错误该字段已经是primary keyERROR1068(42000): Multipleprimarykeydefined mysqlaltertablestudent10modifyidintnotnull???auto_increment;Query OK,0rowsaffected(0.01sec)Records:0Duplicates:0Warnings:06.增加复合主键 mysqlaltertablestudent10addprimarykey(host_ip,port);7.增加主键 mysqlaltertablestudent1-addprimarykey(id);8.增加主键和自动增长 mysqlaltertablestudent1-modifyidintnotnullprimarykeyauto_increment;9.修改存储引擎 mysqlaltertableserviceengineinnodb;//enginemyisam|memory|....innodb提供事物支持及主外键高级功能 MyISAM不支持事物处理强调高性能。 memory仅内存3.6 DDL4(复制删除)[扩展]分类复制表结构记录 key不会复制: 主键、外键和索引只复制表结构复制表结构包括Key删除表示例1 .复制表结构记录 key不会复制: 主键、外键和索引mysqlcreatetablenew_serviceselect*fromservice;2.只复制表结构mysqlcreatetablenew1_serviceselect*fromservicewhere12;//条件为假查不到任何记录3.复制表结构包括Keymysqlcreatetablet4likeemployees;4.删除表DROPTABLE表名;mysql阶段csdn配套视频 https://edu.csdn.net/course/detail/40864