mysql三列数据关联指令_3.MySQL基本的命令行操作

最新推荐文章于 2023-07-27 17:16:08 发布

范洋洋

最新推荐文章于 2023-07-27 17:16:08 发布

阅读量218

点赞数

文章标签： mysql三列数据关联指令

本文链接：https://blog.csdn.net/weixin_42411003/article/details/113270320

版权

一.使用SQLyog和基本命令行操作

1.SQLyog需要学会新建数据库，新建表和字段，执行语句：

2.基本命令行操作：

1 --MySQL数据库单行注释2 /*

3 MySQL数据库多行注释4 */

6 --所有的语句都以分号结尾7

8 mysql -u root -p123456 --连接数据库9

10 update mysql.user set authentication_string=password('654321') where user='root' and host='localhost';11 flush privileges; --修改用户密码并刷新权限12

13 --------------------------------------------------

15 show databases; --查看所有的数据库16 use school; --切换数据库使用use 数据库名17 show tables; --查看数据库中的所有表18 describe student; --显示数据库中student表的信息19

20 create database westos; --创建一个数据库21

22 exit; --退出连接

二.结构化查询语句分类

三.操作数据库

操作数据库：增删改查四个操作

操作数据库中的表：字段类型，字段属性(约束)，表类型(引擎)，表字符集，增删改查四个操作

操作数据库中表的数据：外键，DML语言(增删改)，DQL语言(查)

1.操作数据库(了解)

创建数据库： create database [if not exists] westos; --中括号表示可选项

删除数据库： drop database [if exists] test;

使用数据库： use `school`; --使用数据库，如果名称是一个特殊的字段，需要加反引号。

查看数据库： show databases; --查看所有的数据库

补充： show warnings; --查看warning

2.操作数据库中的表

(1)数据库字段的列类型

数值：

tinyint：十分小的数据，1个字节

samllint：较小的数据，2个字节

mediumint：中等大小的数据，3个字节

int：标准的整数，4个字节，常用

bigint：较大的数据，8个字节

float：浮点数，4个字节

double：浮点数，8个字节(有精度问题)

decimal：字符串形式的浮点数，一般是用在金融计算的时候

字符串：

char：字符串固定大小的，0-255

varchar：可变字符串，0-65535，常用对应Java的String

tinytext：微型文本，2^8-1

text：文本字符串，2^16-1,保存大文本

时间日期：

date：YYYY-MM-DD，日期格式

time：HH：mm：ss，时间格式

datetime：YYYY-MM-DD HH：mm：ss，常用的时间格式

timestamp：时间戳，1970.1.1到现在的毫秒数。

year：年份

null：

理解为"没有值"或"未知值"(注意不要使用NULL进行运算，如果运算结果为NULL)

(2)数据库字段的列属性/列约束(重点)

Unsigned：

无符号的整数

声明了该列不能声明为负数

zerofill：

0填充的

不足的位数，使用0来填充，

自增：

通常理解为自增，自动在上一条记录的基础上加1(默认)

通常用来设计唯一的主键索引，必须为整数类型

可以自定义设计主键自增的起始值和步长

null 和 not null：

设置为not null，如果不赋值就会报错

设置为null，如果不填写值，默认就为null

默认：

设置默认的值！

(2.1)拓展：表设计规范

1 /*每个表，都必须存在以下五个字段！未来做项目会使用，表示一个记录存在的意义！2 id 主键3 `version` 乐观锁4 is_delete 伪删除5 gmt_create 创建时间6 gmt_update 修改时间7 */

(3)数据表的类型

MySQL的数据表的类型 : MyISAM , InnoDB , HEAP , BOB , CSV等...

常见的 MyISAM 与 InnoDB 类型

(3.1)两种类型支持的功能上的区别：

(3.2)适用场合：

适用MyISAM : 节约空间及相应速度

适用InnoDB : 安全性 , 事务处理及多用户操作数据表

(3.3)数据表的存储位置：

MySQL数据表以文件方式存放在磁盘中

包括表文件 , 数据文件 , 以及数据库的选项文件

位置 : Mysql安装目录\data\下存放数据表 . 目录名对应数据库名 , 该目录下文件名对应数据表

两种类型物理文件上的区别：

InnoDB类型数据表只有一个 *.frm文件 , 以及上一级目录的ibdata1文件

MyISAM类型数据表对应三个文件 :

* . frm -- 表结构定义文件

* . MYD -- 数据文件(data)

* . MYI -- 索引文件(index)

(4) 数据表的字符集编码

注：不设置的话，会使用mysql默认的字符集编码(不支持中文)

1 CREATE TABLE表名(2 ......3 )CHARSET = utf8;

(5)创建数据库中的表

(5.1)格式：

1 create table [if not exists]`表名`(2 `字段名` 列类型 [属性] [索引] [注释],3 .......4 `字段名` 列类型 [属性] [索引] [注释],5 `字段名` 列类型 [属性] [索引] [注释]

6 )[表类型] [字符集设置] [注释]

(5.2)案例：

1 # 创建学生表(列,字段)2 # 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email3 # 创建表之前 , 一定要先选择数据库4

5 # 字符串使用单引号括起来！6 # 所有的语句后加英文逗号，最后一个不用加7 # PRIMARY KEY主键8 CREATE TABLE IF NOT EXISTS`student` (9 `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',10 `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',11 `pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',12 `sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',13 `birthday` datetime DEFAULT NULL COMMENT '生日',14 `address` varchar(100) DEFAULT NULL COMMENT '地址',15 `email` varchar(50) DEFAULT NULL COMMENT '邮箱',16 PRIMARY KEY(`id`)17 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

(5.3)常用命令：

通过已经创建的数据库，查看创建数据库的语句

通过已经创建的数据表，查看创建数据表的语句

1 # 查看创建数据库的SQL语句2 SHOW CREATE DATABASEschool;3 # 查看创建数据表的SQL语句4 SHOW CREATE TABLEstudent;5 # 显示表结构6 DESCstudent;7 # 设置严格检查模式(不能容错了)8 SET sql_mode='STRICT_TRANS_TABLES';

(6)修改数据库中的表

(6.1)格式：

添加字段：

ALTER TABLE 表名 ADD 字段名列属性[属性]

删除字段：

ALTER TABLE 表名 DROP 字段名

修改表名：

ALTER TABLE 旧表名 RENAME AS 新表名

修改字段：

ALTER TABLE 表名 MODIFY 字段名列类型[属性]

ALTER TABLE 表名 CHANGE 旧字段名新字段名列类型[属性]

注：

change：一般只用于修改表的列名，这里修改了列类型可能出错，当赋值了varchar类型后不能转换为int类型

modify：用于修改表的列类型和列属性/列约束

(6.2)案例：

1 --增加表的字段

2 ALTER TABLE teacher ADD age INT(3)3

4 --删除表的字段

5 ALTER TABLE teacher DROPage16

7 --修改表名

8 ALTER TABLE student RENAME ASteacher9

10 --修改表的字段(修改约束，重命名)

11 ALTER TABLE teacher MODIFY age VARCHAR(11) --修改约束(用于修改表的列类型和列属性/列约束)

12 ALTER TABLE teacher CHANGE age age1 INT(1) --重命名(一般只用于修改表的列名，这里修改了列类型可能出错，当赋值了varchar类型后不能转换为int类型)

(7)删除数据库中的表

(7.1)格式：

1 DROP TABLE [IF EXISTS] 表名

(7.2)案例：

--删除表

DROP TABLE [IF EXISTS] teacher

注：当表不存在时，如果不写if exists会报错

(8)查看数据库表的结构

1 --查看表的结构

2 DESCRIBE teacher3 --简写

4 DESC teacher

3.操作数据库中表的数据

(1)外键

(1.1)外键概念

如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。

在实际操作中，将一个表的值放入第二个表来表示关联，所使用的值是第一个表的主键值(在必要时可包括复合主键值)。此时，第二个表中保存这些值的属性称为外键(foreign key)。

注意：

外键不要一定是另一个表的主键，但必须是唯一性索引。

主键约束和唯一性约束都是唯一性索引。

(1.2)外键作用

保持数据一致性，完整性，主要目的是控制存储在外键表中的数据,约束。使两张表形成关联，外键只能引用外表中的列的值或使用空值。

(1.3)创建外键

方式一：建表时指定外键约束

方式二：建表后修改外键约束

(1.3.1)建表时指定外键约束

创建年级表，然后在创建学生表，年级表中的id为学生表年级的外键。

其中关键为：声明外键和指定约束 KEY `FK_gradeid` (`gradeid`), CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)

注意：删除主表grade时必须先删除与它相关的从表，然后再删除主表。

1 # 年级表(id\年级名称)2 CREATE TABLE`grade` (3 `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级ID',4 `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',5 PRIMARY KEY(`gradeid`)6 ) ENGINE=INNODB DEFAULT CHARSET=utf87

8 # 学生信息表9 #(学号,姓名,性别,年级,手机,地址,出生日期,邮箱,身份证号)10

11 CREATE TABLE`student` (12 `studentno` INT(4) NOT NULL COMMENT '学号',13 `studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',14 `sex` TINYINT(1) DEFAULT '1' COMMENT '性别',15 `gradeid` INT(10) DEFAULT NULL COMMENT '年级',16 `phoneNum` VARCHAR(50) NOT NULL COMMENT '手机',17 `address` VARCHAR(255) DEFAULT NULL COMMENT '地址',18 `borndate` DATETIME DEFAULT NULL COMMENT '生日',19 `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',20 `idCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',21 PRIMARY KEY(`studentno`),22 KEY`FK_gradeid` (`gradeid`),23 CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES`grade` (`gradeid`)24 ) ENGINE=INNODB DEFAULT CHARSET=utf8

(1.3.2)建表后修改外键约束

先建立年级表，再建立学生表，最后绑定外键约束

基本语法：alter table 从表名 add constraint 约束名 foreign key(从表中作为外键的列名) references 主表名(主表中的列名)

# 年级表(id\年级名称)CREATE TABLE`grade` (

`gradeid`INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级ID',

`gradename`VARCHAR(50) NOT NULL COMMENT '年级名称',PRIMARY KEY(`gradeid`)

) ENGINE=INNODB DEFAULT CHARSET=utf8

# 学生信息表

#(学号,姓名,性别,年级,手机,地址,出生日期,邮箱,身份证号)CREATE TABLE`student` (

`studentno`INT(4) NOT NULL COMMENT '学号',

`studentname`VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',

`sex`TINYINT(1) DEFAULT '1' COMMENT '性别',

`gradeid`INT(10) DEFAULT NULL COMMENT '年级',

`phoneNum`VARCHAR(50) NOT NULL COMMENT '手机',

`address`VARCHAR(255) DEFAULT NULL COMMENT '地址',

`borndate`DATETIME DEFAULT NULL COMMENT '生日',

`email`VARCHAR(50) DEFAULT NULL COMMENT '邮箱',

`idCard`VARCHAR(18) DEFAULT NULL COMMENT '身份证号',PRIMARY KEY(`studentno`)

) ENGINE=INNODB DEFAULT CHARSET=utf8

# 创建外键方式二 : 创建子表完毕后,修改子表添加外键ALTER TABLEstudentADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);

以上操作都是物理外键(数据库级别的外键)，小型数据库不在乎效率使用物理外键，大型数据库不建议这么使用！

大型数据库最佳实践：

数据库就是单纯的表，只用来存储数据，只有行(记录)和列(字段)

我们想使用多张表的数据，想使用外键则通过程序去实现

(1.4)删除外键

(1.4.1)删除主表：需要先删除具有外键的从表，再删除主表

(1.4.2)删除从表的外键约束：先删除从表的外键，再删除从表的索引

1 # 删除外键2 ALTER TABLE student DROP FOREIGN KEYFK_gradeid;3 # 发现执行完上面的,索引还在,所以还要删除索引4 # 注:这个索引是建立外键的时候默认生成的5 ALTER TABLE student DROP INDEX FK_gradeid;

(2)DML语言：(重点)

(2.1)数据库的意义

数据存储

数据管理

(2.2)DML语言：数据操作语言

增：insert

删：delete

改：update

(2.3)增加：insert

(2.3.1)语法：

INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3', ...),('值1','值2','值3', ...), ...

(2.3.2)注意事项：

字段名和值之间都必须用英文逗号隔开

字段名可以省略，但是字段值必须与表结构中的字段值一一对应

可同时插入多条记录，用小括号和逗号隔开

(2.3.3)用例：

1 INSERT INTO grade(gradename) VALUES ('大一');2

3 # 主键自增,那能否省略呢?4 INSERT INTO grade VALUES ('大二');5

6 # 查询:INSERT INTO grade VALUE ('大二')错误代码： 1136

7 # Column count doesn`t match value count at row 1

9 # 得出结论:'字段1,字段2...'该部分可省略 , 但添加的值务必与表结构,数据列,顺序相对应,且数量一致.10

11 # 一次插入多条数据12 INSERT INTO grade(gradename) VALUES ('大三'),('大四');

(2.4)删除：delete

(2.4.1)语法：

delete

DELETE FROM 表名 [WHERE condition];

注意 :condition为筛选条件 , 如不指定则删除该表的所有列数据

turncat

TRUNCATE [TABLE] table_name;

用于完全清空表数据 , 但表结构 , 索引 , 约束等不变 ;

(2.4.2)注意事项：

相同 : 都能删除数据 , 不删除表结构 , 但TRUNCATE速度更快

不同 :

使用TRUNCATE TABLE 重新设置AUTO_INCREMENT计数器

使用TRUNCATE TABLE不会对事务有影响

(2.4.3)用例：

1 # 创建一个测试表2 CREATE TABLE`test` (3 `id` INT(4) NOT NULLAUTO_INCREMENT,4 `coll` VARCHAR(20) NOT NULL,5 PRIMARY KEY(`id`)6 ) ENGINE=INNODB DEFAULT CHARSET=utf87

8 # 插入几个测试数据9 INSERT INTO test(coll) VALUES('row1'),('row2'),('row3');10

11 # 删除表数据(不带where条件的delete)12 DELETE FROMtest;13 # 结论:如不指定Where则删除该表的所有列数据,自增当前值依然从原来基础上进行,会记录日志.14

15 # 删除表数据(truncate)16 TRUNCATE TABLEtest;17 # 结论:truncate删除数据,自增当前值会恢复到初始值重新开始;不会记录日志.18

19 # 同样使用DELETE清空不同引擎的数据库表数据.重启数据库服务后20 # InnoDB : 自增列从初始值重新开始 (因为是存储在内存中,断电即失)21 # MyISAM : 自增列依然从上一个自增数据基础上开始 (存在文件中,不会丢失)

(2.5)修改：update

(2.5.1)语法： UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHERE condition];

(2.5.2)注意事项：

column_name 为要更改的数据列

value 为修改后的数据 , 可以为变量 , 具体指 , 表达式或者嵌套的SELECT结果

condition 为筛选条件 , 如不指定则修改该表的所有列数据

(2.5.3)用例：

1 UPDATE `student` SET `studentname`='小明',`gradeid`= 1 WHERE `studentname`='明明' AND studentno = 1;2 UPDATE `student` SET `borndate`= CURRENT_TIME WHERE `studentname`='红红'

(2.5.4)where条件字句：可以简单的理解为 : 有条件地从表中筛选数据

转载：https://www.cnblogs.com/hellokuangshen/p/10250196.html

范洋洋

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql三列数据关联指令_3.MySQL基本的命令行操作

一.使用SQLyog和基本命令行操作1.SQLyog需要学会新建数据库，新建表和字段，执行语句：2.基本命令行操作：1 --MySQL数据库单行注释2 /*3 MySQL数据库多行注释4 */56 --所有的语句都以分号结尾78 mysql -u root -p123456 --连接数据库910 update mysql.user set authentication_string=passwor...
复制链接

扫一扫