MySQL基础操作与数据类型

目录

• 1.文件夹(库)
  • 增:
  • 改:
  • 查:
  • 删:
• 2.文件(表)
  • 增:
  • 改:
  • 查:
  • 删:
• 3.文件的一行内容
  • 增:
  • 改:
  • 查:
  • 删:
• 4.创建表的完整语法
• 5.整型类型
• 6.补充sql_mode
• 7.浮点型
• 8.字符类型
• 9.日期类型
• 10.枚举与集合类型
• 11.not null与default
• 12.unique
• 13.primary

1.文件夹(库)

增:

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

改:

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

查:

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

删:

drop database 数据库名

2.文件(表)

use 数据库名; # 首先切换文件夹
select database(); # 查看当前所在的文件夹

增:

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

改:

alter table 表名 modify name char(16);

alter table old_name rename new_name;   # 修改表名
用rename来重命名，也可以使用rename to 

alter table person modify name varchar(20); # 修改字段的数据类型
此处modify后面的name为字段名，我们将原来的varchar(25)改为varchar(20) 

alter table person change old_name new_name varchar(25);    # 修改字段名
需要注意的是不管改不改数据类型，后面的数据类型都要写 
如果不修改数据类型只需写成原来的数据类型即可 
tips：我们同样可以使用change来达到modify的效果，只需在其后写一样的字段名 

alter table person add sex boolean;     # 增加无完整性约束条件的字段
此处的sex后面只跟了数据类型，而没有完整性约束条件 

alter table person add age int not null;    # 增加有完整性约束条件的字段
地处增加了一条age字段，接着在后面加上了not null完整性约束条件 

alter table person add num int primary key first;   # 在表头添加字段
默认情况下添加字段都是添加到表尾，在添加语句后面加上first就能添加到表头 

alter table person add birth date after name;   # 在指定位置添加字段
这里添加一条新字段放在name字段后面 
tps：表中字段的排序对表不会有什么影响，不过更合理的排序能便于理解表 

alter table person drop sex;    # 删除字段
和前面删除表或数据库一样，这里也需要用drop 
不同的是，删除字段还要用alter table跟着表名 

alter table person modify id int first;     # 修改字段到第一个位置
first在前面已经讲过，此处要注意的是字段后面要写数据类型 

alter table person modify name varchar(25) after id;    # 修改字段到指定位置
我们把name字段放到了id后面，此处的varchar(25)要写全，varchar不行 
建议操作以上步骤之前都先desc table 

alter table user rename person;     # 修改表的存储引擎
这里先不具体讲各个存储引擎的特点，内容比较多 
修改完之后别忘了使用show create table语句查看，第三节有写用法 
tips：如果表中已存在很多数据，不要轻易修改存储引擎 

alter table score add constraint fk foreign key(stu_id) references student(id);     # 增加表的外键
这里只需使用add增加即可，后面的语法参见第四节中的外键设置 

alter table student3 drop foreign key fk;   # 删除表的外键约束
由于基本的表结构描述无法显示外键，所以在进行此操作前最好使用show create table查看表 
这里的fk就是刚刚设置的外键
需要注意的是：如果想要删除有关联的表，那么必先删除外键 
删除外键后，原先的key变成普通键
至于删除表的操作，在第三节有写，设置外键在第四节也有写如果创建表的时候没有设置外键，可使用上面的方法

查:

show tables; # 查看当前库下所有的表名
show create table 表名; # 查看表的详细信息
desc 表名; # 查看表结构

删:

drop table 表名;

3.文件的一行内容

增:

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

改:

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

查:

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

删:

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.整型类型

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为严格模式：在该模式下，如果插入的数据超过限制，则会立即报错
mysql> set global sql_mode="strict_trans_tables";

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.字符类型

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.日期类型

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，多选一个
集合set，多选多

测试

create table teacher(
    id int,
    name char(16),
    sex enum('male','female','others'),
    hobbies set('play','read','music','piao')
);

11.not null与default

#not null + default

create table t15(
    id int,
    name char(16) not null,
    sex enum('male','female','other') not null default "male"
);

alter table t15 modify name char(16) not null;


insert into t15(id,name) values
(1,'egon1'),
(2,'egon2'),
(3,'egon3');

12.unique

#unique:限制字段的值唯一

#单列唯一
create table t16(
    id int unique,
    name char(16)
);


# 联合唯一
create table server(
    id int unique,
    ip char(15),
    port int,
    unique(ip,port)
);

13.primary

#primary key:单单从约束角度去看，primary key就等同于not null unique
#强调（******）
1、一张表中必须有，并且只能有一个主键
2、一张表中都应该有一个id字段，而且应该把id字段做成主键


create table t17(
    id int primary key,
    name char(16),
    age int,
    sex char(6)
)engine=innodb;


#联合主键
create table t19(
    ip char(15),
    port int,
    primary key(ip,port)
);


# primary key auto_increment
create table t20(
    id int primary key auto_increment,
    name char(16)
)engine=innodb;

# auto_increment注意点：
1、通常与primary key连用，而且通常是给id字段加
2、auto_incremnt只能给被定义成key（unique key，primary key）的字段加

MySQL基础操作与数据类型

标签：指定位置   完整性约束   数据库   base   单选   表的操作   gbk   other   兴趣