day53_mysql
内容回顾：
MySQL服务器
Mysql是一基于C/S架构的软件
1.如何安装Mysql
2.安装window服务
mysqld --install
mysqld --remove
net start mysql
net stop mysql
3.破解密码
跳过授权表来开启服务器
mysqld --skip-grant-tables
4.统一字符编码
my.ini 配置文件
[mysqld]
# 设置mysql的安装目录 **后面的路径一定是安装sql的目录（自己电脑的）**
basedir=C:\mysql-5.7.22-winx64\mysql-5.7.22-winx64
# 设置mysql数据库的数据的存放目录，必须是data
datadir=C:\mysql-5.7.22-winx64\mysql-5.7.22-winx64\data
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# mysql端口
port=3306
# 字符集
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
\s;
5.创建用户
create user 'xxxx'@'%' indentified by '123'
授权
grant 这个权限 只有root有
grant all privileges on *.* to "xxxx"@'%';
revoke all privileges on *.* from 'xxx'@'%';
6.sql命令
select user();
查看所有的数据库
show databases;
创建数据库
create database db1;
create table t1(id int,name char(10));
engine=innodb
sql语言 是强类型
insert into t1(id,name) values(1,'alex'),(2,'wusir');
今日内容：
1.sql的基本语法
对数据库
create database db1 charset utf8; 默认就是utf-8
# 查看当前创建的数据库show create database db1;
#查看所有的数据库
show databases;
改
alter database db1 charset gbk;
删
drop database db1;
对表
use db1;#切换文件夹
select database();#查看当前所在数据库
create table t1(id int,name char(10));
show create table t1;
show tables; 查看所有的表
desc t1; 查看表的详细结构
#modify修改的意思
alter table t1 modify name char(6);
#改变name为大写的NAME
alter table t1 change name NAMA char(7);
#删除表
drop table t1;
执行如下命令，查看系统库
show databases;
information_schema： 虚拟库，不占用磁盘空间，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等
performance_schema： MySQL 5.5开始新增一个数据库：主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象
mysql： 授权库，主要存储系统用户的权限信息
test： MySQL数据库系统自动创建的测试数据库
SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。SQL语言分为3种类型：
1、DDL语句 数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER
2、DML语句 数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT
3、DCL语句 数据库控制语言： 例如控制用户的访问权限 GRANT、REVOKE
对数据
insert into t1(id,name) values(1,'alex'),(2,'武sir'); 插入
insert into t1 value(3,'日天');
select id from t1;
select id,name from t1;
select * from t1;
select * from db1.t1 where id = 2;
关系型数据库：mysql db2 oracle sqlite 表中存储
非关系型数据库：monogodb redis
key : value 基于model 模型 obj.insert
2.存储引擎
mysql 5.5版本之后默认为 innodb 存储引擎
另外还有 mysiam、memory、blackhone
#memory，在重启mysql或者重启机器后，表内数据清空
#blackhole，往表内插入任何数据，都相当于丢入黑洞，表内永远不存记录
mysql支持的存储引擎
mysql> show engines\G;# 查看所有支持的引擎
mysql> show variables like'storage_engine%';#查看正在使用的存储引
4、Memory 存储引擎
正如其名,Memory 存储引擎中的数据都存放在内存中,数据库重 启或发生崩溃,表中的数据都将消失
7、BLACKHOLE
黑洞存储引擎;能放所有东西，放进去就没有了
创建四张表，分别使用innodb,myisam,memory,blackhole存储引擎，进行插入数据测试
指定表类型/存储引擎
创建之后在硬盘中 frm格式的表结构，.bd的为数据
create table t1(id int)engine=innodb;# 默认不写就是innodb
create table t1(id int)engine=innodb;
create table t2(id int)engine=myisam;
create table t3(id int)engine=memory;
create table t4(id int)engine=blackhole;
#.frm是存储数据表的框架结构
#.ibd是mysql数据文件
#.MYD是MyISAM表的数据文件的扩展名
#.MYI是MyISAM表的索引的扩展名
#发现后两种存储引擎只有表结构，无数据
#memory，在重启mysql或者重启机器后，表内数据清空
#blackhole，往表内插入任何数据，都相当于丢入黑洞，表内永远不存记录
建表
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);
#注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的
查看表结构
mysql> show create table a1\G;
复制表结构和表数据
create table a1 select * from db2.t1;
只复制表结构
create table a2 select * from db2.t1 where 1>2;
create table a3 like db2.t1;
3.数据类型
#1. 数字：
整型：tinyint int bigint ；默认是有符号的，即可以插入负数
tinyint[(m)] [unsigned] [zerofill]
# 创建表时定义记录的字符为无符号类型（0,255） ，使用unsigned
mysql>create table t2(x tinyint unsigned);
小整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-128 ～ 127
无符号：
0 ～255
PS： MySQL中无布尔值，使用tinyint(1)构造。
小数：小说点后面七位还是准确的；；默认后面补0
float ：在位数比较短的情况下不精准
double ：在位数比较长的情况下不精准
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
#参数解释：单精度浮点数（非准确小数值），M是全长，D是小数点后个数。M最大值为255，D最大值为30
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
随着小数的增多，精度比float要高，但也会变得不准确 ***
#参数解释： 双精度浮点数（非准确小数值），M是全长，D是小数点后个数。M最大值为255，D最大值为30
decimal[(m[,d])] [unsigned] [zerofill]
#参数解释：准确的小数值，M是整数部分总个数（负号不算），D是小数点后个数。 M最大值为65，D最大值为30。
1验证FLOAT类型建表：
mysql> create table t5(x float(256,31));
ERROR1425 (42000): Too big scale 31 specifiedforcolumn'x'. Maximumis30.
mysql> create table t5(x float(256,30));
ERROR1439 (42000): Display width out of rangeforcolumn'x'(max = 255)
mysql> create table t5(x float(255,30));#建表成功
Query OK, 0 rows affected (0.03 sec)
0.000001230123123123
存成：0.000001230000
decimal：（如果用小数，则用推荐使用decimal）
精准：内部原理是以字符串形式去存
#2. 字符串：
char（10）：定长；简单粗暴，浪费空间，存取速度快
root存成root000000
varchar：精准，节省空间，存取速度慢
sql优化：创建表时，定长的类型往前放，变长的往后放
比如性别 || 比如地址或描述信息
>255个字符，超了就把文件路径存放到数据库中。
比如图片，视频等找一个文件服务器，数据库中只存路径或url。
#3. 时间类型：
最常用：datetime
三、日期类型
DATE TIME DATETIME TIMESTAMP YEAR
语法：
YEAR
YYYY（1901/2155）
DATE
YYYY-MM-DD（1000-01-01/9999-12-31）
TIME
HH:MM:SS（'-838:59:59'/'838:59:59'）
DATETIME
YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y）
TIMESTAMP
YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）
-- datetime
now() sql的内置函数，根据数据类型生成对应的时间格式
create table t8(born_year year);#无论year指定何种宽度，最后都默认是year(4)
create table t9(d date,t time,dt datetime);
#调用mysql自带的now()函数，获取当前类型指定的时间 如下结构;默认插入时以各自的形式插入数据
mysql> insert into t9 values(now(),now(),now());
在实际应用的很多场景中，MySQL的这两种日期类型都能够满足我们的需要，存储精度都为秒，但在某些情况下，会展现出他们各自的优劣。
下面就来总结一下两种日期类型的区别。
1.DATETIME的日期范围是1001——9999年，TIMESTAMP的时间范围是1970——2038年。
2.DATETIME存储时间与时区无关，TIMESTAMP（用的不多 ）存储时间与时区有关，显示的值也依赖于时区。在mysql服务器，
操作系统以及客户端连接都有时区的设置。
3.DATETIME使用8字节的存储空间，TIMESTAMP的存储空间为4字节。因此，TIMESTAMP比DATETIME的空间利用率更高。
4.DATETIME的默认值为null；TIMESTAMP的字段默认不为空（not null）,默认值为当前时间（CURRENT_TIMESTAMP），
如果不做特殊处理，并且update语句中没有指定该列的更新值，则默认更新为当前时间。
综合练习：
创建一张学生表（student），要求有id,姓名，出生年份，出生的年月日，进班的时间，以及来老男孩学习的现在具体时间。
mysql> create table student(
-> id int,
-> name varchar(20),
-> born_year year,
-> birth date,
-> class_time time,
-> reg_time datetime
-> );
#4. 枚举类型与集合类型
enum 和set
-- 整型 默认是signed 有符号的 范围 -128~127
sql中没有boolean类型 使用 tinyint(1) 来表示boolean值 1 表示true 0 表示false
-- int 类型后面的存储是显示宽度(len(kkk))而不是存储宽度，其它的数据类型都是存储宽度
所以我们来设计表的时候 int类型的字段不用加显示宽度，默认是总长度的位数+1
create table t4(id int(5) unsigned zerofill);
总结一句话，定义int类型不需要加宽度，使用默认就可以
int的存储宽度是4个Bytes，即32个bit，即2**32
无符号最大值为：4294967296-1
有符号最大值： 2147483648-1
有符号和无符号的最大数字需要的显示宽度均为10，而针对有符号的最小值则需要11位才能显示完全，所以int类型默认的显示宽度为11是非常合理的
最后：整形类型，其实没有必要指定显示宽度，使用默认的就ok
-- char 定长 存储速度快 浪费空间
varchar 变长 存储速度慢 节省空间
#注意：char和varchar括号内的参数指的都是字符的长度
#char类型：定长，简单粗暴，浪费空间，存取速度快
字符长度范围：0-255（一个中文是一个字符，是utf8编码的3个字节）
存储：
存储char类型的值时，会往右填充空格来满足长度
例如：指定长度为10，存>10个字符则报错，存<10个字符则用空格填充直到凑够10个字符存储
检索：
在检索或者说查询时，查出的结果会自动删除尾部的空格，除非我们打开 pad_char_to_full_length SQL模式（设置SQL模式：
SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
查询sql的默认模式：select @@sql_mode;）
字符长度范围：0-65535（如果大于21845会提示用其他类型 。mysql行最大限制为65535字节，字符编码为utf-8：https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html）
存储：
varchar类型存储数据的真实内容，不会用空格填充，如果'ab ',尾部的空格也会被存起来
强调：varchar类型会在真实数据前加1-2Bytes的前缀，该前缀用来表示真实数据的bytes字节数（1-2Bytes最大表示65535个数字，正好符合mysql对row的最大字节限制，即已经足够使用）
如果真实的数据<255bytes则需要1Bytes的前缀（1Bytes=8bit 2**8最大表示的数字为255）
如果真实的数据>255bytes则需要2Bytes的前缀（2Bytes=16bit 2**16最大表示的数字为65535）
检索：
尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容
.char填充空格来满足固定长度，但是在查询时却会很不要脸地删除尾部的空格（装作自己好像没有浪费过空间一样），然后修改sql_mode让其现出原形
只是在当前会话生效
SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
#查看当前mysql的mode模式
mysql> select @@sql_mode;
验证之前了解两个函数：
length()：查看字节数
char_length():查看字符数# 创建t1表,分别指明字段x为char类型，字段y为varchar类型
mysql> create table t1(x char(5),y varchar(4));
# char存放的是5个字符，而varchar存4个字符
mysql> insert into t1 values('你瞅啥 ','你瞅啥 ');
# 在检索时char很不要脸地将自己浪费的2个字符给删掉了，装的好像自己没浪费过空间一样，而varchar很老实，存了多少，就显示多少
mysql> select x,char_length(x),y,char_length(y) from t1;
+-----------+----------------+------------+----------------+
| x | char_length(x) | y | char_length(y) |
+-----------+----------------+------------+----------------+
| 你瞅啥 | 3 | 你瞅啥 | 4 |
+-----------+----------------+------------+----------------+
1 row in set (0.02 sec)
#略施小计，让char现原形
mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
#查看当前mysql的mode模式
mysql> select @@sql_mode;
+-------------------------+
| @@sql_mode |
+-------------------------+
| PAD_CHAR_TO_FULL_LENGTH |
+-------------------------+
#原形毕露了吧。。。。
mysql> select x,char_length(x) y,char_length(y) from t1;
枚举类型和集合类型
字段的值只能在给定范围中选择，如单选框，多选框
enum 单选 只能在给定的范围内选一个值，如性别 sex 男male/女female
set 多选 在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3...）
-- enum 枚举 表示多选一
-- set 集合 表示多选一或者多选多
create table consumer(
id int unsigned,
name varchar(20),
sex enum('male','female','other'),
level enum('vip','svip','vvip'),
fav set('smoke','drink','tangtou')
);
insert into consumer values(1,'一宁','other','vvip','smoke,drink,tangtou');
小结：
数据类型：
整型：tinyint int bigint
浮点型： float double decimal
时间：year date time datetime
字符：char 定长 > varchar 变长 > text 文本
枚举：enum
集合：set
布尔：boolean tinyint(1) 存1 表示true,存0 表示false
函数： now() 根据字段的数据类型获取当前的时间格式
char_length: 获取字符长度
length():获取字节数
sql中默认是有符号的
如果设置无符号 unsigned
用0填充 zefofill
对字段进行 约束
4.约束 重点
-
not null 与 default
-
unique
-
primary
-
auto_increment
-
foreign key
-
-------------------
-
PRIMARY KEY (PK) #标识该字段为该表的主键，可以唯一的标识记录
-
FOREIGN KEY (FK) #标识该字段为该表的外键
-
NOT NULL #标识该字段不能为空
-
UNIQUE KEY (UK) #标识该字段的值是唯一的
-
AUTO_INCREMENT #标识该字段的值自动增长（整数类型，而且为主键）
-
DEFAULT #为该字段设置默认值
-
-
UNSIGNED #无符号
-
ZEROFILL #使用0填充
-
约束条件与数据类型的宽度一样，都是可选参数
-
# 3. 是否是key
-
主键 primary key
-
外键 foreign key
-
索引 (index,unique...)
-
默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值 null
-
建表
-
mysql> create table student2(
-
-> id int not null,
-
-> name varchar(50) not null,
-
-> age int(3) unsigned not null default 18,
-
-> sex enum('male','female') default 'male',
-
-> fav set('smoke','drink','tangtou') default 'drink,tangtou'
-
-> );
3、unique
中文翻译：不同的。在mysql中称为单列唯一
作用：保证数据的完整性和一致型
单列唯一
例子2：create table department(
id int unique,
name char(10) unique
);
#第二种创建unique的方式create table department(
id int,
name char(10) ,
unique(id),
unique(name)
);
#联合唯一，只要两列记录，有一列不同，既符合联合唯一的约束
# 创建services表
mysql> create table services(
id int,
ip char(15),
port int,
unique(id),
unique(ip,port)
);
4.primary key
MySQL的一个表中只有唯一的一个主键，不能有多列主键，但可以有复合主键
一个表中可以：
单列做主键
多列做主键（复合主键）
约束：等价于 not null unique,字段的值不为空且唯一
存储引擎默认是（innodb）:对于innodb存储引擎来说，一张表必须有一个主键。
复合主键
create table t16(
ip char(15),
port int,
primary key(ip,port)
);
insert into t16 values
('1.1.1.2',80),
('1.1.1.2',81);
5.auto_increment
约束:约束的字段为自动增长，约束的字段必须同时被key约束
# 创建student
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);
对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
查看可用的 开头auto_inc的词
mysql> show variables like 'auto_inc%';
mysql> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.02 sec)
# 步长auto_increment_increment,默认为1
# 起始的偏移量auto_increment_offset, 默认是1
# 设置步长 为会话设置，只在本次连接中有效
set session auto_increment_increment=5;
#全局设置步长 都有效。
set global auto_increment_increment=5;
# 设置起始偏移量
set global auto_increment_offset=3;
#强调：If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored. 翻译：如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值会被忽略
#全局设置完起始偏移量和步长之后，再次执行show variables like'auto_inc%';发现跟之前一样，必须先exit, 再登录才有效；只要不重启就一直生效，已重启就丢失了，。
mysql> show variables like'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 5 |
| auto_increment_offset | 3 |
+--------------------------+-------+
2 rows in set (0.00 sec)
清空表区分delete和truncate的区别：
delete from t1; #如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。
truncate table t1;数据量大，删除速度比上一条快，且直接从零开始。
表的设计
1.not null 与 default
create table tb1(id int not null default 2,name char(20) not null);
insert into tb1 values(1,'alex');
insert into tb1(name) values('武sir');
insert into tb1(id) values(3);
2.unique
单列唯一
create table dep(id int not null,name varchar(20) unique);
insert into dep(id,name) values(1,'alex');
insert into dep(id,name) values(2,'alex');
多列唯一 表示每一列都唯一
create table dep2(id int unique,name varchar(20) unique);
insert into dep2(id,name) values(1,'alex');
# 不能插入值
insert into dep2(id,name) values(1,'alex2');
insert into dep2(id,name) values(2,'alex');
组合唯一 （联合唯一） 只要有一列不同，就可以插入数据
create table dep3(
id int,
name varchar(20),
unique(id,name)
);
insert into dep3(id,name) values(1,'alex');
insert into dep3(id,name) values(1,'alex2');
insert into dep3(id,name) values(2,'alex');
不能插入的格式
insert into dep3(id,name) values(1,'alex');
3. primary key (索引优化查询)
sql版本中，一张表中只允许有一个主键，通常都是id,cid,nid,sid
student
id
teacher
id
select student.id,teacher.id from
create table stu(
id int primary key auto_increment,
name varchar(10) unique
);
insert into stu(name) values ('alex');
化学反应： not null + unique
create table stu2(
id int not null unique auto_increment,
name varchar(10) unique
);
primary key (索引优化)
key 查询优化
查询优化；
前提 准备大量数据
4.auto_increment
预习内容：
1.foregin key 外键
2.外键的变种
- 1对多或多对一
出版社 书
一 多 √
多 一 X
- 多对多
- 一对一
3.单表查询
