mysql数据操作类型_MySQL基础操作与数据类型

1.文件夹(库)

查看字符集编码的指令

show variables like "%char%";

MySQL客户端连接服务端时的完整指令

mysql -h 127.0.0.1 -P 3306 -u root -p

1.增:

create database 数据库名 charset utf8(指定该库的编码方式);

2.改:

alter database 数据库名 charset gbk;(更改编码方式)

3.查:

show databases; # 查所有库的库名

show create database 数据库名; # 单独查看某一个库的信息

4.删:

drop database 数据库名

2.文件(表)

use 数据库名; # 首先切换文件夹

select database(); # 查看当前所在的文件夹

1.增:

create table 表名(id int,name char);

2.改:

2.1 修改存储引擎

mysql> alter table service

-> engine=innodb;

2.2 添加字段

mysql> alter table student10

-> add name varchar(20) not null,

-> add age int(3) not null default 22;

mysql> alter table student10

-> add stu_num varchar(10) not null after name; //添加name字段之后

mysql> alter table student10

-> add sex enum('male','female') default 'male' first; //添加到最前面

2.3 删除字段

mysql> alter table student10

-> drop sex;

mysql> alter table service

-> drop mac;

2.4 修改字段类型modify

mysql> alter table student10

-> modify age int(3);

mysql> alter table student10

-> modify id int(11) not null primary key auto_increment; //修改为主键

2.5 增加约束(针对已有的主键增加auto_increment)

mysql> alter table student10 modify id int(11) not null primary key auto_increment;

ERROR 1068 (42000): Multiple primary key defined

mysql> alter table student10 modify id int(11) not null auto_increment;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

2.6 对已经存在的表增加复合主键

mysql> alter table service2

-> add primary key(host_ip,port);

2.7 增加主键

mysql> alter table student1

-> modify name varchar(10) not null primary key;

2.8 增加主键和自动增长

mysql> alter table student1

-> modify id int not null primary key auto_increment;

2.9 删除主键

a. 删除自增约束

mysql> alter table student10 modify id int(11) not null;

b. 删除主键

mysql> alter table student10

-> drop primary key;

3.查:

show tables; # 查看当前库下所有的表名

show create table 表名; # 查看表的详细信息

desc 表名; # 查看表结构

4.删:drop table 表名;

3.文件的一行内容

1.增:

insert into 数据库名.表名 values(1,'la'),(2,'lala'),(3,'lalala');

2.改:

update 库名.表名 set name='sb' where id > 1;

3.查:

select id,name from 库名.表名;# 查 id和name

select * from 库名.表名;# 查所有

4.删:

delete from 库名.表名 where name='qwe';

truncate 库名.表名; #当数据量比较大的情况下，使用这种方式，删除速度快 也可删除自增带来的问题

创建不同引擎的表

create table t1(id int)engine = innodb;

create table t2(id int)engine = myisam;

create table t3(id int)engine = blackhole;

create table t4(id int)engine = memory;

4.创建表的完整语法

一 创建表的完整语法

create table 表名(

字段名1 类型[(宽度) 约束条件],

字段名2 类型[(宽度) 约束条件],

字段名3 类型[(宽度) 约束条件]

);

#解释：

类型：使用限制字段必须以什么样的数据类型传值

约束条件：约束条件是在类型之外添加一种额外的限制

# 注意：

1. 在同一张表中，字段名是不能相同

2. 宽度和约束条件可选，字段名和类型是必须的

3、最后一个字段后不加逗号

create database db37;

5.整型类型

类型

大小

范围(有符号)

范围(无符号)unsigned约束

用途

TINYINT

1 字节

(-128，127)

(0，255)

小整数值

SMALLINT

2 字节

(-32 768，32 767)

(0，65 535)

大整数值

MEDIUMINT

3 字节

(-8 388 608，8 388 607)

(0，16 777 215)

大整数值

INT或INTEGER

4 字节

(-2 147 483 648，2 147 483 647)

(0，4 294 967 295)

大整数值

BIGINT

8 字节

(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)

(0，18 446 744 073 709 551 615)

极大整数值

FLOAT

4 字节 float(255,30)

(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)

0，(1.175 494 351 E-38，3.402 823 466 E+38)

单精度 浮点数值

DOUBLE

8 字节 double(255,30)

(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)

0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)

双精度 浮点数值

DECIMAL

对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 double(65,30)

依赖于M和D的值

依赖于M和D的值

小数值

1、作用：id号，各种号码，年龄，等级

2、分类：

tinyint(**)

int (*****)

bigint(***)

3、测试：默认整型都是有符号的

create table t1(x tinyint);

insert into t1 values(128),(-129);

create table t2(x tinyint unsigned);

insert into t2 values(-1),(256);

create table t3(x int unsigned);

#4294967295

insert into t3 values(4294967296);

create table t4(x int(12) unsigned);

insert into t4 values(4294967296123);

4、强调：对于整型来说，数据类型后的宽度并不是存储限制，而是显示限制

所以在创建表示，如果字段采用的是整型类型，完全无需指定显示宽度，

默认的显示宽度，足够显示完整当初存放的数据

# 显示时，不够8位用0填充，如果超出8位则正常显示

create table t5(x int(8) unsigned zerofill);

insert into t5 values(4294967296123);

insert into t5 values(1);

6.补充sql_mode

# 查看sql_mode

mysql> show variables like "%sql_mode%";

+----------------------------+---------------------+

| Variable_name | Value |

+----------------------------+---------------------+

| binlogging_impossible_mode | IGNORE_ERROR |

| block_encryption_mode | aes-128-ecb |

| gtid_mode | OFF |

| innodb_autoinc_lock_mode | 1 |

| innodb_strict_mode | OFF |

| pseudo_slave_mode | OFF |

| slave_exec_mode | STRICT |

| sql_mode | STRICT_TRANS_TABLES |

+----------------------------+---------------------+

8 rows in set (0.00 sec)

#修改sql_mode为严格模式：在该模式下，如果插入的数据超过限制，则会立即报错

# 模式设置和修改(以解决非严格模式下,插入大于限制的数不报错):

方式一:先执行select @@sql_mode,复制查寻出来的值并将其中的NO_ZERO_IN_DATE,NO_ZERO_DATE删除，然后执行set sql_mode='修改后的值'或者set session sql_mode='修改后的值';，例如：set session sql_mode='STRICT_TRANS_TABLES';改为严格模式 # session可以不用写

# 此方法只在当前会话中生效，关闭当前会话就不生效了。

方式二：先执行select @@global.sql_mode,复制查询出来的值并将其中的NO_ZERO_IN_DATE,NO_ZERO_DATE删除，然后执行set global sql_mode = '修改后的值'。

此方法在当前服务中生效，重新MySQL服务后失效

方法三：在mysql的安装目录下，或my.cnf文件(windows系统是my.ini文件)，新增 sql_mode = STRICT_TRANS_TABLES

添加my.cnf如下：

[mysqld]sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER

7.浮点型

作用：存储身高、体重、薪资

分类：

float (*****)

double (**)

decimal (**)

测试：

#相同点

#1、对于三者来说，都能存放30位小数，

#不同点：

1、精度的排序从低到高：float,double,decimal

2、float与double类型能存放的整数位比decimal更多

create table t9(x float(255,30));

create table t10(x double(255,30));

create table t11(x decimal(65,30));

insert into t9 values(1.111111111111111111111111111111);

insert into t10 values(1.111111111111111111111111111111);

insert into t11 values(1.111111111111111111111111111111);

mysql> select * from t9;

+----------------------------------+

| x |

+----------------------------------+

| 1.111111164093017600000000000000 |

+----------------------------------+

1 row in set (0.00 sec)

mysql> select * from t10;

+----------------------------------+

| x |

+----------------------------------+

| 1.111111111111111200000000000000 |

+----------------------------------+

1 row in set (0.00 sec)

mysql> select * from t11;

+----------------------------------+

| x |

+----------------------------------+

| 1.111111111111111111111111111111 |

+----------------------------------+

1 row in set (0.00 sec)

8.字符类型

类型

大小

用途

CHAR

0-255字节

定长字符串

VARCHAR

0-65535 字节

变长字符串

TINYBLOB

0-255字节

不超过 255 个字符的二进制字符串

TINYTEXT

0-255字节

短文本字符串

BLOB

0-65 535字节

二进制形式的长文本数据

TEXT

0-65 535字节

长文本数据

MEDIUMBLOB

0-16 777 215字节

二进制形式的中等长度文本数据

MEDIUMTEXT

0-16 777 215字节

中等长度文本数据

LONGBLOB

0-4 294 967 295字节

二进制形式的极大文本数据

LONGTEXT

0-4 294 967 295字节

极大文本数据

1、作用：姓名，地址，描述类的信息

2、分类：

char 定长

varchar 变长

3、测试:字符的宽度限制单位是字符个数

create table t12(x char(4)); # 超出4个字符则报错，不够4个字符则用空格补全成4个字符

create table t13(y varchar(4));# 超出4个字符则报错，不够4个字符那么字符有几个就存几个

insert into t12 values('hello');

insert into t13 values('hello');

insert into t12 values('a'); #'a '

insert into t13 values('a'); #'a'

set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH";

select char_length(x) from t12; #4

select char_length(y) from t13; #1

# 注意：

针对char类型，mysql在存储时会将数据用空格补全存放到硬盘中

但会在读出结果时自动去掉末尾的空格，因为末尾的空格在以下场景中是无用

mysql> select * from t14 where name="lxx"; # name字段明确地等于一个值，该值后填充空格是没有用

mysql> select * from t14 where name like "lxx"; # name字段模糊匹配一个值，该值后填充空格是有用的

# 对比char与varchar

name char(5)

# 缺点：浪费空间

# 优点：存取速度都快

egon alex lxx wxx yx

name varchar(5)

# 缺点：存取速度都慢

# 优点：节省空间

(1bytes+egon)(1bytes+alex)(1bytes+lxx)

9.日期类型

大小 (字节)

范围

格式

用途

DATE

3

1000-01-01/9999-12-31

YYYY-MM-DD

年月日

TIME

3

'-838:59:59'/'838:59:59'

HH:MM:SS

时分秒

YEAR

1

1901/2155

YYYY

年份值

DATETIME

8

1000-01-01 00:00:00/9999-12-31 23:59:59

YYYY-MM-DD HH:MM:SS

年月日时分秒

TIMESTAMP

4

1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07

YYYYMMDD HHMMSS

混合日期和时间值，时间戳

1、作用：时间相关

2、分类：

date：1999-01-27

time: 11:11:11

datetime:1999-01-27 11:11:11

year:1999

3、测试

create table student(

id int,

name char(16),

born_year year,

birth date,

class_time time,

reg_time datetime

);

insert into student values(1,'egon','2000','2000-01-27','08:30:00','2013-11-11 11:11:11');

10.枚举与集合类型

类型

大小

用途

ENUM

对1-255个成员的枚举需要1个字节存储; 对于255-65535个成员，需要2个字节存储; 最多允许65535个成员。

单选：选择性别

SET

1-8个成员的集合，占1个字节 9-16个成员的集合，占2个字节 17-24个成员的集合，占3个字节 25-32个成员的集合，占4个字节 33-64个成员的集合，占8个字节

多选：兴趣爱好

枚举类型(enum)

# 示例:

CREATE TABLE shirts (

name VARCHAR(40),

size ENUM('x-small', 'small', 'medium', 'large', 'x-large')

);

INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');

集合类型(set)

A SET column can have a maximum of 64 distinct members.

# 示例：

CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));

INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');

# 作用与分类：

枚举enum:多选一个

集合set:多选多,如选着多个相同的会自动去重