Mysql初步学习

我就是我 2022-04-12 08:25 259阅读 0赞

# Mysql初步学习 #

--------------------

计算机科学导论上布置了数据库mysql的作业，先是下载以及配置环境耗费了一个上午，再是熟悉基本的语法，由于自己手残，以及总是想搞一些有的没的就耗费了比较多的时间，将一些知识点整理一下，有关mysql的安装问题可以去我[上一篇博客][Link 1]看一下。

## 基础知识 ##

### 1.启动问题 ###

unbuntu终端输入 mysql -uroot -p（密码)（linux下安装时会提醒你设立的密码）  
![启动问题][E9_80_89_E5_8C_BA_003.png_q-sign-algorithm_sha1_q-ak_AKID96oGSOGetsa2EAkFQM1RcXBZhcIiqkXB_q-sign-time_1545449158_1545450058_q-key-time_1545449158_1545450058_q-header-list_q-url-param-list_q-signature_b3fbce33b6edb890a8b8b7f58953d38c5679a265]

### 2.建表问题 ###

仿照课本建表时提示出错  
![建表问题][E9_80_89_E5_8C_BA_004.png_q-sign-algorithm_sha1_q-ak_AKID96oGSOGetsa2EAkFQM1RcXBZhcIiqkXB_q-sign-time_1545449172_1545450072_q-key-time_1545449172_1545450072_q-header-list_q-url-param-list_q-signature_537e8714f4aafd6fc1ea427a66fb53c26c5a9a1f]  
问题所在是没有选择进入哪个数据库建表，因此第一步是先要进入或者创建一个数据库。可以选择用show databases来查看当前所有的database![show databases][]，或者可以用create database NAME来创建一个数据库。  
之后用use NAME（数据库名称，来选择进入数据库）  
示例如下  
![数据库问题][E9_80_89_E5_8C_BA_006.png_q-sign-algorithm_sha1_q-ak_AKID96oGSOGetsa2EAkFQM1RcXBZhcIiqkXB_q-sign-time_1545449196_1545450096_q-key-time_1545449196_1545450096_q-header-list_q-url-param-list_q-signature_dac98fc38fb1510245f185ec072676189d7554ce]

### 3.建立表 ###

#### 3.1建立 ####

> create tabale R  
> (  
> NO char(8) NOT NULL,  
> NAME char(8) NOT NULL,  
> SEX char(2) NOT NULL,  
> AGE SMALLINT,  
> CLASS SMALLINT  
> );

--------------------

这样还是蛮简单的，但是就是有的时候会一不小心输错一些东西，这个时候可以采取的弥补方法有  
一、 刚输入完发现有问题，直接在分号后接edit，就会进入vim对你上条的语句进行编辑，可以说是相当方便了。个人理解是进入edit就有点像用编辑器编写c语言，或者是matlab中的脚本，这样的感觉。不过还不知道怎么用edit修改上一次的东西。  
日后了解到了，会补上的。  
二、发现错误已经是很久之后了，对表中数据进行修改，用

> \*\*alter table R change XX(被修改的某元素) XXX(想要修改的值) (数据类型) \*\*  
> ![修改][E9_80_89_E5_8C_BA_007.png_q-sign-algorithm_sha1_q-ak_AKID96oGSOGetsa2EAkFQM1RcXBZhcIiqkXB_q-sign-time_1545449302_1545450202_q-key-time_1545449302_1545450202_q-header-list_q-url-param-list_q-signature_65f24bb76a15f811a064d7f04d5b154787b162c6];

--------------------

#### 3.2查看表中元素信息 ####

> DESC R;  
> ![查看元素][E9_80_89_E5_8C_BA_008.png_q-sign-algorithm_sha1_q-ak_AKID96oGSOGetsa2EAkFQM1RcXBZhcIiqkXB_q-sign-time_1545449291_1545450191_q-key-time_1545449291_1545450191_q-header-list_q-url-param-list_q-signature_799b9a8db09b13c591d9d2a58263d5405302a8de]

--------------------

### 4.向表中插入元素 ###

> INSERT INTO R(NO,NAME,SEX,AGE,CLASS);

需要注意的是向其中的数据类型为字符插入的时候需要加上单引号比如’陌’。  
在插入的时候我们有可能会遇到这样的问题  
![trouble][]  
这样的问题是因为数据库所使用的字符集出错所导致的，解决办法是，在一开始建立数据库的时候我们在后面填上一段话就好。

> create database test CHARACTER SET=UTF8;

暂时没有找到能临时替换的操作（日后找到了我会补上的，一定不会咕！）

### 5.查看表 ###

#### 5.1查看表中元素 ####

> select \*from R;

![查看][E9_80_89_E5_8C_BA_010.png_q-sign-algorithm_sha1_q-ak_AKID96oGSOGetsa2EAkFQM1RcXBZhcIiqkXB_q-sign-time_1545449273_1545450173_q-key-time_1545449273_1545450173_q-header-list_q-url-param-list_q-signature_ac955bd0cf17baae819bae658aa327ea25302fd3]

#### 5.2替换表中元素 ####

> update R SET <种类>=<值> WHERE <种类>=<值>

#### 5.3 查找元素 ####

> select <种类>…FROM S;  
> WHERE 条件

#### 5.4 删除元素 ####

> delete FROM R WHERE 条件

**PS.暂时用到的东西也就是这些日后如果遇到其他问题也会po在上面,进行更新，感觉又进了一个坑的样子，每天进步一点吧**

[Link 1]: https://blog.csdn.net/qq_40184139/article/details/84701673
[E9_80_89_E5_8C_BA_003.png_q-sign-algorithm_sha1_q-ak_AKID96oGSOGetsa2EAkFQM1RcXBZhcIiqkXB_q-sign-time_1545449158_1545450058_q-key-time_1545449158_1545450058_q-header-list_q-url-param-list_q-signature_b3fbce33b6edb890a8b8b7f58953d38c5679a265]: https://mysql-1258355831.cos.ap-chengdu.myqcloud.com/mysql/Mysql/%E9%80%89%E5%8C%BA_003.png?q-sign-algorithm=sha1&q-ak=AKID96oGSOGetsa2EAkFQM1RcXBZhcIiqkXB&q-sign-time=1545449158;1545450058&q-key-time=1545449158;1545450058&q-header-list=&q-url-param-list=&q-signature=b3fbce33b6edb890a8b8b7f58953d38c5679a265
[E9_80_89_E5_8C_BA_004.png_q-sign-algorithm_sha1_q-ak_AKID96oGSOGetsa2EAkFQM1RcXBZhcIiqkXB_q-sign-time_1545449172_1545450072_q-key-time_1545449172_1545450072_q-header-list_q-url-param-list_q-signature_537e8714f4aafd6fc1ea427a66fb53c26c5a9a1f]: https://mysql-1258355831.cos.ap-chengdu.myqcloud.com/mysql/Mysql/%E9%80%89%E5%8C%BA_004.png?q-sign-algorithm=sha1&q-ak=AKID96oGSOGetsa2EAkFQM1RcXBZhcIiqkXB&q-sign-time=1545449172;1545450072&q-key-time=1545449172;1545450072&q-header-list=&q-url-param-list=&q-signature=537e8714f4aafd6fc1ea427a66fb53c26c5a9a1f
[show databases]: https://mysql-1258355831.cos.ap-chengdu.myqcloud.com/mysql/Mysql/%E9%80%89%E5%8C%BA_005.png?q-sign-algorithm=sha1&q-ak=AKID96oGSOGetsa2EAkFQM1RcXBZhcIiqkXB&q-sign-time=1545449185;1545450085&q-key-time=1545449185;1545450085&q-header-list=&q-url-param-list=&q-signature=36e7363805a0bef95a64eb0cb4ce7d478771e4d1
[E9_80_89_E5_8C_BA_006.png_q-sign-algorithm_sha1_q-ak_AKID96oGSOGetsa2EAkFQM1RcXBZhcIiqkXB_q-sign-time_1545449196_1545450096_q-key-time_1545449196_1545450096_q-header-list_q-url-param-list_q-signature_dac98fc38fb1510245f185ec072676189d7554ce]: https://mysql-1258355831.cos.ap-chengdu.myqcloud.com/mysql/Mysql/%E9%80%89%E5%8C%BA_006.png?q-sign-algorithm=sha1&q-ak=AKID96oGSOGetsa2EAkFQM1RcXBZhcIiqkXB&q-sign-time=1545449196;1545450096&q-key-time=1545449196;1545450096&q-header-list=&q-url-param-list=&q-signature=dac98fc38fb1510245f185ec072676189d7554ce
[E9_80_89_E5_8C_BA_007.png_q-sign-algorithm_sha1_q-ak_AKID96oGSOGetsa2EAkFQM1RcXBZhcIiqkXB_q-sign-time_1545449302_1545450202_q-key-time_1545449302_1545450202_q-header-list_q-url-param-list_q-signature_65f24bb76a15f811a064d7f04d5b154787b162c6]: https://mysql-1258355831.cos.ap-chengdu.myqcloud.com/mysql/Mysql/%E9%80%89%E5%8C%BA_007.png?q-sign-algorithm=sha1&q-ak=AKID96oGSOGetsa2EAkFQM1RcXBZhcIiqkXB&q-sign-time=1545449302;1545450202&q-key-time=1545449302;1545450202&q-header-list=&q-url-param-list=&q-signature=65f24bb76a15f811a064d7f04d5b154787b162c6
[E9_80_89_E5_8C_BA_008.png_q-sign-algorithm_sha1_q-ak_AKID96oGSOGetsa2EAkFQM1RcXBZhcIiqkXB_q-sign-time_1545449291_1545450191_q-key-time_1545449291_1545450191_q-header-list_q-url-param-list_q-signature_799b9a8db09b13c591d9d2a58263d5405302a8de]: https://mysql-1258355831.cos.ap-chengdu.myqcloud.com/mysql/Mysql/%E9%80%89%E5%8C%BA_008.png?q-sign-algorithm=sha1&q-ak=AKID96oGSOGetsa2EAkFQM1RcXBZhcIiqkXB&q-sign-time=1545449291;1545450191&q-key-time=1545449291;1545450191&q-header-list=&q-url-param-list=&q-signature=799b9a8db09b13c591d9d2a58263d5405302a8de
[trouble]: https://mysql-1258355831.cos.ap-chengdu.myqcloud.com/mysql/Mysql/%E9%80%89%E5%8C%BA_007.png?q-sign-algorithm=sha1&q-ak=AKID96oGSOGetsa2EAkFQM1RcXBZhcIiqkXB&q-sign-time=1545449212;1545450112&q-key-time=1545449212;1545450112&q-header-list=&q-url-param-list=&q-signature=7c78b20c603ce4d6b89f5e51f4642eda0525199c
[E9_80_89_E5_8C_BA_010.png_q-sign-algorithm_sha1_q-ak_AKID96oGSOGetsa2EAkFQM1RcXBZhcIiqkXB_q-sign-time_1545449273_1545450173_q-key-time_1545449273_1545450173_q-header-list_q-url-param-list_q-signature_ac955bd0cf17baae819bae658aa327ea25302fd3]: https://mysql-1258355831.cos.ap-chengdu.myqcloud.com/mysql/Mysql/%E9%80%89%E5%8C%BA_010.png?q-sign-algorithm=sha1&q-ak=AKID96oGSOGetsa2EAkFQM1RcXBZhcIiqkXB&q-sign-time=1545449273;1545450173&q-key-time=1545449273;1545450173&q-header-list=&q-url-param-list=&q-signature=ac955bd0cf17baae819bae658aa327ea25302fd3