Mysql 表管理及数据类型-2

最新推荐文章于 2023-10-30 17:34:28 发布

奇漠

最新推荐文章于 2023-10-30 17:34:28 发布

阅读量987

点赞数 2

分类专栏： Linux基础学习文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_50660509/article/details/129399167

版权

Linux基础学习专栏收录该内容

42 篇文章 0 订阅

订阅专栏

数据类型

在mysql数据管理系统中，可以通过存储引擎来决定表的类型。同时，mysql数据库管理系统也提供了数据类型决定表存储数据的类型。

数据类型分为：数值类型、字符串类型、时间和日期类型。

数值类型

整数类型

tinyint、smallint、mediumint、int、bigint

作用：用于存储用户的年龄、游戏的level、经验值等。

- 当整数值超过int数据类型支持的范围时，可以采用bigint。

- 在mysql中，int数据类型是主要的整数数据类型。

- int（n）里的n表示的是select查询结果几种显示的显示宽度，并不影响实际的取值范围，没有影响到显示的宽度

浮点数类型

float、double

作用：用语存储用户的身高、体重、薪水等

float(5，3) 5宽度，3精度（插入的数据）宽度和精度中间用逗号。

精度：小数点后是：3位如果超过三位，会四舍五入，如果不够三位会用0补全

宽度：5 精度是3 控制了小数点前的位数，不超过两位。比如(6,3) 如果插入数据为（123.123），可以插入，但如果小数点前超过三位就会报错。

定点数类型 DEC（了解）
定点数在MySQL内部以字符串形式存储，比浮点数更精确，适合用来表示货币等精度高的数据。

位类型 BIT（了解）
BIT(M)可以用来存放多位二进制数，M范围从1~64，如果不写默认为1位

字符串类型

CHAR系列：CHAR VARCHAR （必须加规定长度）

TEXT系列： TINYTEXT TEXT MEDIUMTEXT LONGTEXT

枚举类型： ENUM

集合类型： SET

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

总结：
1.经常变化的字段用varchar
2.知道固定长度的用char
3.超过255字符的只能用varchar或者text
4.能用varchar的地方不用text

时间和日期类型

DATE TIME DATETIME TIMESTAMP YEAR

create table t8 (
  id1 timestamp NOT NULL default CURRENT_TIMESTAMP,
  id2  datetime default NULL
);
timestamp 类型的列还有个特性：默认情况下，在 insert, update 数据时，timestamp 列会自动以当前时间（CURRENT_TIMESTAMP）填充/更新。“自动”的意思就是，你不去管它，MySQL 会替你去处理。
 
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)

扩展：
select now();查看当前时间

表操作

表完整性约束

主键

每张表里只能有一个主键，不能为空，而且唯一，主键保证记录的唯一性，主键自动为NOT NULL。一个 UNIQUE KEY 又是一个NOT NULL的时候，那么它被当做PRIMARY KEY主键。

创建表并指定约束
mysql> create table t9(hostname char(20),ip char(150),primary key(hostname));
或者
mysql> create table t9(hostname char(20) primary key,ip char(150));

mysql> insert into t9(hostname,ip) values('qfedu.com', '10.10.10.11');
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into t9(hostname,ip) values('qfedu.com', '10.10.10.12');
ERROR 1062 (23000): Duplicate entry 'qfedu.com' for key 'PRIMARY'  #插入了相同名字，报错
 
mysql> insert into t9(hostname,ip) values('qfedu', '10.10.10.11');
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from t9;
+-----------+-------------+
| hostname  | ip          |
+-----------+-------------+
| qfedu     | 10.10.10.11 |
| qfedu.com | 10.10.10.11 |
+-----------+-------------+
2 rows in set (0.00 sec)
 
mysql> insert into t9(hostname,ip) values('qfjy', '10.10.10.12');  
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from t9;
+-----------+-------------+
| hostname  | ip          |
+-----------+-------------+
| qfedu     | 10.10.10.11 |
| qfedu.com | 10.10.10.11 |
| qfjy      | 10.10.10.12 |
+-----------+-------------+
3 rows in set (0.00 sec)
 
mysql> insert into t9(ip) values('10.10.10.13');
ERROR 1364 (HY000): Field 'hostname' doesn't have a default value
删除主键
mysql> alter table tab_name  drop  primary key;
主键被删除之后，这个字段仍然不允许为空值；

自增（auto_incement）

自动编号，且必须与主键组合使用默认情况下，起始值为1，每次的增量为1。当插入记录时，如果为AUTO_INCREMENT数据列明确指定了一个数值，则会出现两种情况：
- 如果插入的值与已有的编号重复，则会出现出错信息，因为AUTO_INCREMENT数据列的值必须是唯一的；
- 如果插入的值大于已编号的值，则会把该插入到数据列中，并使在下一个编号将从这个新值开始递增。也就是说，可以跳过一些编号。如果自增序列的最大值被删除了，则在插入新记录时，该值不会被重用。
（每张表只能有一个字段为自增）（成了key才可以自动增长）

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

mysql> select * from department3;
Empty set (0.00 sec)
 
插入值
mysql> insert into department3(dept_name, comment) values('tom','test'), ('jack', 'test2');
Query OK, 2 rows affected (0.00 sec)    #没有插入dept_id的数据
 
mysql> select * from department3;
+---------+-----------+---------+
| dept_id | dept_name | comment |
+---------+-----------+---------+
|       1 | tom       | test    |
|       2 | jack      | test2   |           #但是因为有自动增长，dept_id从1开始自动增长
+---------+-----------+---------+
2 rows in set (0.00 sec)
 
删除自动增长
mysql> ALTER TABLE department3 CHANGE dept_id  dept_id INT NOT NULL;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

唯一约束 UNIQUE

字段添加唯一约束之后，该字段的值不能重复，也就是说在一列当中不能出现一样的值。

mysql> CREATE TABLE department2 (
     dept_id INT,
     dept_name VARCHAR(30) UNIQUE,
     comment VARCHAR(50)
     );

删除unique
mysql> alter table department2 drop index dept_name;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

null与not null

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

unsigned

整数类型有可选的UNSIGNED属性，表示不允许负值，这大致上可以使正数的上限提高一倍
可以使用这几种整数类型：TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT。分别使用8，16，24，32，64位存储空间
它们可以存储的值的范围从-2(n-1)到2(n-1)-1,其中n是存储空间的位数。

mysql> create table guo(id int(1) default null,qq int(5) unsigned default null);
id为未指定unsigned，而qq为指定unsigned
先对id列插入数据
根据计算得2(n-1)-1为2147483647
mysql> insert into guo(id) values(2147483647);
Query OK, 1 row affected (0.00 sec)
 
插入2147483648则提示超出范围
mysql> insert into guo(id) values(2147483648);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
 
下面对unsigned列进行插入,范围应该为
2(n-1)+2(n-1)-1为4294967295
mysql> insert into guo(qq) values(-2147483648);
ERROR 1264 (22003): Out of range value for column 'qq' at row 1
 
mysql> insert into guo(qq) values(4294967296);
ERROR 1264 (22003): Out of range value for column 'qq' at row 1
 
mysql> insert into guo(qq) values(4294967295);
Query OK, 1 row affected (0.01 sec)
 
MySQL可以为整数指定宽度，例如int(11)，对大多数应用这是没有意义的：它不会限制值的合法范围，只是规定了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，int(1)和int(20)是相同的。
 
create table t1(id tinyint(3) default null,qq tinyint(3) unsigned default null);
id
-128-->127
 
qq
0-->255

指定字符集

 
* 未指定之前，插入
mysql> insert into t1(id,name) values(1,'飞哥');
ERROR 1366 (HY000): Incorrect string value: '\xE7\x9F\xB3\xE5\xAE\x87...' for column 'name' at row 1
 
* 创建表格式指定字符集为utf-8
mysql> create table t6(id int(2),name char(5),age int(4)) default charset=utf8;
Query OK, 0 rows affected (0.00 sec)
 
mysql> desc t6;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(2)  | YES  |     | NULL    |       |
| name  | char(5) | YES  |     | NULL    |       |
| age   | int(4)  | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)
 
mysql> insert into t6(id,name) values(1,'飞哥');
Query OK, 1 row affected (0.00 sec)

默认约束

1.创建一个表
mysql> create table user(id int not null, name varchar(20), number int, primary key(id));
Query OK, 0 rows affected (0.01 sec)
 
mysql> desc user;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| number | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
 
2、设置默认值
mysql> ALTER TABLE user ALTER number SET DEFAULT 0;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
也可以这样修改：
mysql> alter table user change number num int(6) not null default 3;
 
mysql> DESCRIBE user;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| number | int(11)     | YES  |     | 0       |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
 
 
3、插入值
mysql> ALTER TABLE user CHANGE id id INT NOT NULL AUTO_INCREMENT;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> INSERT INTO user(name) VALUES('rock'); 
Query OK, 1 row affected (0.00 sec)
 
mysql> INSERT INTO user(name) VALUES('rock');
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from user;
+----+------+--------+
| id | name | number |
+----+------+--------+
|  1 | rock |      0 |
|  2 | rock |      0 |
+----+------+--------+
2 rows in set (0.00 sec)
 
删除默认值
mysql> ALTER TABLE user ALTER number drop DEFAULT;

oracle和mysql的区别。---面试

-Oracle是大型数据库，而MySQL是中小型数据库。但是MySQL是开源的，但是Oracle是收费的，而且比较贵。
-Oracle的内存占有量非常大，而mysql非常小
-MySQL支持主键自增长，指定主键为auto increment，插入时会自动增长。Oracle主键一般使用序列。
-MySQL字符串可以使用双引号包起来，而Oracle只可以单引号
-MySQL分页用limit关键字，而Oracle使用rownum字段表明位置，而且只能使用小于，不能使用大于。
-MySQL中0、1判断真假，Oracle中true false
-MySQL中命令默认commit,但是Oracle需要手动提交
-MySQL在windows环境下大小写不敏感在unix,linux环境下区分大小写，Oracle不区分

表操作

1、创建

语法：create table 库.表名 (字段类型约束条件，字段类型约束条件)；

2、查看

查看表：show tables; (当前所在库) show tables from 库名;

产看表结构：desc 表名;

select 字段名 from 表名;

3、插入数据

insert into 表名 (字段) values(值,),(值);

4、修改

修改表ALTER TABLE
语法：
1. 修改表名
	  ALTER TABLE 表名  RENAME 新表名;
 
2. 增加字段
	  ALTER TABLE 表名
                    	  ADD 字段名  数据类型 [完整性约束条件…],
                          ADD 字段名  数据类型 [完整性约束条件…];
 
     ALTER TABLE 表名
	                  	  ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;
	                  	  
3. 删除字段
	ALTER TABLE 表名 DROP 字段名;
 
4. 修改字段
	  ALTER TABLE 表名 
	                      MODIFY 字段名 数据类型 [完整性约束条件…];
	  ALTER TABLE 表名 
	                      CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
	  ALTER TABLE 表名 
	                      CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
 
示例：
1. 修改存储引擎
mysql> alter table service 
        -> engine=innodb;                        //engine=myisam|memory|....
   修改字符集
mysql> alter table service
        -> charset=uft8
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）not null + unique
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 new1_service select * from service where 1=2;  //条件为假，查不到任何记录
 
可以复制主键,只复制表结构
mysql> create table t4 like employees;
 
六、删除表
	DROP TABLE 表名;
七、修改表中字段的值
    update 表名 set 列名=值 where 条件

库操作

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

information_schema
SCHEMATA
TABLES
COLUMNS

创建数据库：DDL
mysqladmin -u root -p1 password ‘123’
mysql -u root -pQianFeng@123 -e "show databases"
mysql -u root -pQianFeng@123 -e "create database sipeng"

4. 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 数据库名;