Linux-关于MySQL（一）

最新推荐文章于 2024-07-13 18:10:19 发布

乌冬吴亦几

最新推荐文章于 2024-07-13 18:10:19 发布

阅读量109

点赞数

分类专栏：乌冬linux 文章标签：数据库 mysql linux

本文链接：https://blog.csdn.net/Joan_H/article/details/107603849

版权

乌冬linux 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

1搭建数据库服务器

1.1 相关概念
1.1.1 什么是数据库？
是服务的一种，专门提供数据存储的服务。

1.1.2 数据是什么？
1.1.3 提供数据存储服务的软件
商业软件
开源软件
是否跨平台
软件来源
软件的封包类型 .rpm 、源码

1.1.4 MySQL的发展史和特点

1.1.5 专业术语（行话）

1.2 搭建MySQL数据库服务器
1.2.1 清除其他版本MySQL的配置
1.2.2 安装MySQL服务软件包
1.2.3 启动mysql服务
1.2.4 使用初始密码登录
1.2.5 修改登录密码
1.2.6 断开连接使用新密码登录
1.2.7 修改密码策略
命令修改
永久修改
1.2.8 设置新密码
1.2.9 使用新密码登录
1.2.10 与服务相关的文件

2数据库服务的基本管理

2.1 连接方式
2.2 数据存储步骤（客户端把数据存储到数据库服务器上的步骤
）

2.3 管理命令使用规则
2.4 相关概念：
库（文件夹）
表（文件夹里的文件）
记录（文件里的行）

2.5 库的基本管理命令
库名命名规则？

2.6 表的基本管理命令
表名命名规则？

2.7 记录的基本管理命令：

2.8 SQL（结构化查询语言）命令的使用规则？

2.9 SQL命令的分类
2.10 指定表使用中文字符集，存储汉字。

3MySQL数据类型
字符类型 char varchar blob text

mysql> create table db1.t3(name char , homeaddr  varchar );报错
mysql> create table db1.t3(name char , homeaddr  varchar(3) );
mysql> desc  db1.t3;
mysql> insert into db1.t3 values ("ab","jim");报错
mysql> insert into db1.t3 values ("a","jim");
mysql> select  * from db1.t3;
+------+----------+
| name | homeaddr |
+------+----------+
| a    | jim      |
+------+----------+
1 row in set (0.00 sec)

mysql> desc mysql.user;   练习到

数值类型
整数类型（没有小数点的数） 11 300 507
根据存储数值大小的不同，分为以下类型：
微小整型小整型中整型大整型极大整型

		整型类型有分为有符号 和无符号存储范围
		unsigned  使用无符号存储范围（不可以存储负数）

mysql> create table t5( name  char(10) , age tinyint );
Query OK, 0 rows affected (0.01 sec)

mysql> desc  db1.t5;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name  | char(10)   | YES  |     | NULL    |       |
| age   | tinyint(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into db1.t5 values ("bob" , -129);
ERROR 1264 (22003): Out of range value for column 'age' at row 1
mysql> 
mysql> insert into db1.t5 values ("bob" , 128);
ERROR 1264 (22003): Out of range value for column 'age' at row 1
mysql> 
mysql> 
mysql> insert into db1.t5 values ("bob" , 12);
Query OK, 1 row affected (0.01 sec)

mysql> insert into db1.t5 values ("bob" , 102);
Query OK, 1 row affected (0.00 sec)

mysql> insert into db1.t5 values ("bob" , -12);
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t5;
+------+------+
| name | age  |
+------+------+
| bob  |   12 |
| bob  |  102 |
| bob  |  -12 |
+------+------+
3 rows in set (0.00 sec)

mysql> 

mysql> create table t6( name  char(10) , age tinyint unsigned );
mysql> desc db1.t6;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| name  | char(10)            | YES  |     | NULL    |       |
| age   | tinyint(3) unsigned | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> 
mysql> insert into  db1.t6 values ("bob" ,  -1 );
ERROR 1264 (22003): Out of range value for column 'age' at row 1
mysql> 
mysql> insert into  db1.t6 values ("bob" , 256 );
ERROR 1264 (22003): Out of range value for column 'age' at row 1
mysql> 
mysql> insert into  db1.t6 values ("bob" , 25 );
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t6;
+------+------+
| name | age  |
+------+------+
| bob  |   25 |
+------+------+
1 row in set (0.00 sec)

mysql>	
mysql> create table t7( name  char(10) , age int  );

mysql> insert into db1.t5 values ("lucy",29.5);
Query OK, 1 row affected (0.00 sec)

mysql> select  * from t5;
+------+------+
| name | age  |
+------+------+
| bob  |   12 |
| bob  |  102 |
| bob  |  -12 |
| lucy |   30 |
+------+------+
4 rows in set (0.00 sec)

mysql>

浮点型（有小数点的数）11.88
float 单精度（存储的范围小）
double 双精度（存储的范围大）

mysql> create table  db1.t7(name char(15) , yzl double );
Query OK, 0 rows affected (0.02 sec)

mysql> desc db1.t7;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(15) | YES  |     | NULL    |       |
| yzl   | double   | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into db1.t7  values ("zhuchongzhi" , 3.1415926 );
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t7;
+-------------+-----------+
| name        | yzl       |
+-------------+-----------+
| zhuchongzhi | 3.1415926 |
+-------------+-----------+
1 row in set (0.00 sec)

mysql> insert into db1.t7  values ("zhuchongzhiA" , 3 );
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t7;
+--------------+-----------+
| name         | yzl       |
+--------------+-----------+
| zhuchongzhi  | 3.1415926 |
| zhuchongzhiA |         3 |
+--------------+-----------+
2 rows in set (0.00 sec)

mysql>

小数的组成整数.小数

mysql> create table db1.t8( name  char(10), gz  float(5,2));
Query OK, 0 rows affected (0.02 sec)

mysql> desc db1.t8;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name  | char(10)   | YES  |     | NULL    |       |
| gz    | float(5,2) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into  db1.t8 values ("bob" , 30000);
ERROR 1264 (22003): Out of range value for column 'gz' at row 1
mysql> 
mysql> insert into  db1.t8 values ("bob" , 900);
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t8;
+------+--------+
| name | gz     |
+------+--------+
| bob  | 900.00 |
+------+--------+
1 row in set (0.00 sec)

mysql> insert into  db1.t8 values ("boba" , 999.52);
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t8;
+------+--------+
| name | gz     |
+------+--------+
| bob  | 900.00 |
| boba | 999.52 |
+------+--------+
2 rows in set (0.00 sec)

mysql>

枚举类型（让字段的值只能在列举的范围里选择）
enum(值列表)单选
set(值列表) 多选项

mysql> create table db1.t9 ( name char(10) , sex enum("boy","girl") , loves set("eat","game","sleep","happly") );
Query OK, 0 rows affected (0.01 sec)

mysql> desc db1.t9;
+-------+------------------------------------+------+-----+---------+-------+
| Field | Type                               | Null | Key | Default | Extra |
+-------+------------------------------------+------+-----+---------+-------+
| name  | char(10)                           | YES  |     | NULL    |       |
| sex   | enum('boy','girl')                 | YES  |     | NULL    |       |
| loves | set('eat','game','sleep','happly') | YES  |     | NULL    |       |
+-------+------------------------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql>
mysql> insert into db1.t9 values ("tom","man","music,sleep");
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
mysql> 
mysql> insert into db1.t9 values ("tom","boy","music,sleep");
ERROR 1265 (01000): Data truncated for column 'loves' at row 1
mysql> 
mysql> insert into db1.t9 values ("tom","boy","eat,sleep");
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t9;
+------+------+-----------+
| name | sex  | loves     |
+------+------+-----------+
| tom  | boy  | eat,sleep |
+------+------+-----------+
1 row in set (0.00 sec)

mysql>

日期时间类型
年 year YYYY 2020
日期 date YYYYMMDD 20200718
时间 time HHMMSS 090000
日期时间类型 datetime或timestamp
YYYYMMDDHHMMSS 20200718091328

mysql> create table  db1.t10(
    -> name char(10) , your_s  year , birthday  date , up_class  time ,
    -> party  datetime );
Query OK, 0 rows affected (0.06 sec)

mysql> desc db1.t10 ;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| name     | char(10) | YES  |     | NULL    |       |
| your_s   | year(4)  | YES  |     | NULL    |       |
| birthday | date     | YES  |     | NULL    |       |
| up_class | time     | YES  |     | NULL    |       |
| party    | datetime | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql>
mysql> insert into db1.t10 values ("bob" , 1990 , 20201120 , 090000 , 20200719203000);

mysql> select  * from  db1.t10;
+------+--------+------------+----------+---------------------+
| name | your_s | birthday   | up_class | party               |
+------+--------+------------+----------+---------------------+
| bob  |   1990 | 2020-11-20 | 09:00:00 | 2020-07-19 20:30:00 |
+------+--------+------------+----------+---------------------+
1 row in set (0.00 sec)

mysql>

时间函数(获取数据库服务器时间的命令)

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 09:24:54  |
+-----------+
1 row in set (0.00 sec)	

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2020-07-18 |
+------------+
1 row in set (0.00 sec)	

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-07-18 09:31:06 |
+---------------------+
1 row in set (0.00 sec)

mysql> 
mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
|        2020 |
+-------------+
1 row in set (0.00 sec)


mysql> select month(now());
+--------------+
| month(now()) |
+--------------+
|            7 |
+--------------+
1 row in set (0.00 sec)

mysql> select day(now());
+------------+
| day(now()) |
+------------+
|         18 |
+------------+
1 row in set (0.00 sec)

mysql> select date(now());
+-------------+
| date(now()) |
+-------------+
| 2020-07-18  |
+-------------+
1 row in set (0.00 sec)

mysql> select time(now());
+-------------+
| time(now()) |
+-------------+
| 09:33:01    |
+-------------+
1 row in set (0.00 sec)

mysql> 

mysql> insert into db1.t10 values ("jerry" , year(now()) , curdate() , curtime() , now() );
		
mysql> select  * from db1.t10;
+-------+--------+------------+----------+---------------------+
| name  | your_s | birthday   | up_class | party               |
+-------+--------+------------+----------+---------------------+
| bob   |   1990 | 2020-11-20 | 09:00:00 | 2020-07-19 20:30:00 |
| jerry |   2020 | 2020-07-18 | 09:33:45 | 2020-07-18 09:33:45 |
+-------+--------+------------+----------+---------------------+
2 rows in set (0.00 sec)

mysql>

year 类型要求使用4位数赋值
当使用2位数赋值时：
01_69视为20012069
70_99视为19701999

mysql> insert into  db1.t10(name,your_s) values ("lucy",58),("alicex",88);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select  * from db1.t10;
+--------+--------+------------+----------+---------------------+
| name   | your_s | birthday   | up_class | party               |
+--------+--------+------------+----------+---------------------+
| bob    |   1990 | 2020-11-20 | 09:00:00 | 2020-07-19 20:30:00 |
| jerry  |   2020 | 2020-07-18 | 09:33:45 | 2020-07-18 09:33:45 |
| lucy   |   2058 | NULL       | NULL     | NULL                |
| alicex |   1988 | NULL       | NULL     | NULL                |
+--------+--------+------------+----------+---------------------+
4 rows in set (0.00 sec)

mysql>

关于日期时间 timestamp 和 datetime

当未给timestamp字段赋值时，自动以当前系统时间赋值，而datetime 值为 NULL (空)

mysql> create table db1.t11(meetting  datetime , party timestamp);
mysql> desc db1.t11;
+----------+-----------+------+-----+-------------------+-----------------------------+
| Field    | Type      | Null | Key | Default           | Extra                       |
+----------+-----------+------+-----+-------------------+-----------------------------+
| meetting | datetime  | YES  |     | NULL              |                             |
| party    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+----------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.00 sec)

mysql> insert into  db1.t11  values (19881120213052 , 19890720103052);
Query OK, 1 row affected (0.00 sec)

mysql> select * from db1.t11;
+---------------------+---------------------+
| meetting            | party               |
+---------------------+---------------------+
| 1988-11-20 21:30:52 | 1989-07-20 10:30:52 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> insert into  db1.t11(meetting)  values ( 20200818224518);
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t11;
+---------------------+---------------------+
| meetting            | party               |
+---------------------+---------------------+
| 1988-11-20 21:30:52 | 1989-07-20 10:30:52 |
| 2020-08-18 22:45:18 | 2020-07-18 10:02:21 |
+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-07-18 10:02:45 |
+---------------------+
1 row in set (0.00 sec)

mysql> insert into  db1.t11(party)  values ( 20281118224518);
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t11;
+---------------------+---------------------+
| meetting            | party               |
+---------------------+---------------------+
| 1988-11-20 21:30:52 | 1989-07-20 10:30:52 |
| 2020-08-18 22:45:18 | 2020-07-18 10:02:21 |
| NULL                | 2028-11-18 22:45:18 |
+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql>

乌冬吴亦几

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Linux-关于MySQL（一）

1搭建数据库服务器1.1 相关概念1.1.1 什么是数据库？是服务的一种，专门提供数据存储的服务。1.1.2 数据是什么？1.1.3 提供数据存储服务的软件商业软件开源软件是否跨平台软件来源软件的封包类型 .rpm 、源码1.1.4 MySQL的发展史和特点1.1.5 专业术语（行话）1.2 搭建MySQL数据库服务器1.2.1 清除其他版本MySQL的配置1.2.2 安装MySQL服务软件包1.2.3 启动mysql服务1.2.4 使用初始密码登录1.2.5 修改登录密
复制链接

扫一扫