数据库笔记

初始化 mysqld --initialize-insecure
开启服务端 mysqld
客户端连接服务端 mysql -uroot -p
启动服务net start mysql
关闭服务net stop mysql
刷新权限flush privileges;
密码设置update mysql.user set authentication_string =password('123') where User='root';
破解密码
- 先把原来mysql服务端关闭
- 管理员身份运行执行：mysqld --skip-grant-tables
- 客户端连接服务端 此时修改密码，注意修改密码之后一定要刷新权限
创建用户:
# 指定ip：192.118.1.1的mjj用户登录
create user 'mjj'@'192.118.1.1' identified by '123';

# 指定ip：192.118.1.开头的mjj用户登录
create user 'mjj'@'192.%.%.%' identified by '123';

# 指定任何ip的mjj用户登录
create user 'mjj'@'%' identified by '123';
sql语句
show databases; 查看所有的数据库
use db1; 进入数据库

create database db1; 创建数据库
use db1;

- 创建用户
create table s1(id int,name char(10));

insert into s1(id,name) values (1,'abcd'),(2,'武松');
- 对当前用户授权操作（root用户拥有）
#授权 mjj用户仅对db1.t1文件有查询、插入和更新的操作
grant select on db1.t1 to "mjj"@'%';

# 表示有所有的权限，除了grant这个命令，这个命令是root才有的。mjj用户对db1下的t1文件有任意操作
grant all privileges on db1.t1 to "mjj"@'%';

#mjj用户对db1数据库中的文件执行任何操作
grant all privileges on db1.* to "mjj"@'%';

#mjj用户对所有数据库中文件有任何操作
grant all privileges on *.* to "mjj"@'%';

取消授权
revoke select on db1.s1 from "mjj"@'%';

mysql -umjj -h 192.168.12.74 -p
123

存储引擎
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;

insert into t1(id) value(1);
insert into t2(id) value(1);
insert into t3(id) value(1);
insert into t4(id) value(1);

select *from t1;
select *from t2;
select *from t3;
select *from t4;

复制表
表结构+内容
create database db2;
use db2;
create table b1 select *from db1.t1;
selcet *from b1;
复制表结构
第一种:
create table b2 select *from db1.t1 where 1>2;
select *from b2;
desc b2;
第二种:
create table b3 like db1.t1;
select *from b3;
desc b3;
删除表
drop table 表名;

有符号和无符号tinyint(范围-128~127 顾头顾尾)
默认有符号
create database db3;
use db3;
create table t1(x tinyint);
insert into t1 value(-128);
insert into t1 value(127);
desc t1;
设置无符号(范围0~255)
unsigned:给当前的字段设置约束
create table t2(x tinyint unsigned);
desc t2;
insert into t2 value(-1);报错,超出范围;
insert into t2 value(0);

int类型后面的存储是显示宽度，而不是存储宽度
create table t3(id int(1) unsigned);
insert into t3 value(222222222);
select *from t3;

create table t4(id int(5) unsigned);
insert into t4 value(222);
select *from t4;

create table t5(id int(5) unsigned zerofill);
insert into t5 value(222);
insert into t5 value(22222222);
select *from t5;
有符号和无符号的最大数字需要的显示宽度均为10，
而针对有符号的最小值则需要11位才能显示完全，
所以int类型默认的显示宽度为11是非常合理的

浮点型
float ：在位数比较短的情况下不精准
随着小数的增多，精度变得不准确
参数解释：单精度浮点数（非准确小数值），M是全长，D是小数点后个数。M最大值为255，D最大值为30
double：在位数比较长的情况下不精准
随着小数的增多，精度比float要高，但也会变得不准确
参数解释： 双精度浮点数（非准确小数值），M是全长，D是小数点后个数。M最大值为255，D最大值为30
decimal:如果用小数，则用推荐使用decimal
精准
内部原理是以字符串形式去存
随着小数的增多，精度始终准确 ****
对于精确数值计算时需要用此类型
decaimal能够存储精确值的原因在于其内部按照字符串存储。
参数解释：准确的小数值，M是整数部分总个数（负号不算），D是小数点后个数。 M最大值为65，D最大值为30。

精度验证
create table t6(x float(255,30));
create table t7(x double(255,30));
create table t8(x decimal(65,30));

insert into t6 value(1.111111111111111111111111111);
insert into t7 value(1.111111111111111111111111111);
insert into t8 value(1.111111111111111111111111111);

select *from t6;
select *from t7;
select *from t8;
时间类型:datetime
日期类型
year
create table t9(year year);
insert into t9 values (1900);超出范围
insert into t9 values (1901);
insert into t9 values (2155);
insert into t9 values (2156);超出范围
date、year、datetime
create table t10(d date,t time, dt datetime);
desc t10;
insert into t10 values(now(),now(),now());
select *from t10;

创建学生表
create table student(
id int,
name varchar(10),
born year,
barth date,
classtime time,
cometime datetime
);
insert into student values(
1,'b','2008','2008-11-11','11:11:11','2018-11-11 11:11:11'
);
insert into student values(
2,'c','2008','2008-11-11','11:11:11','2018-11-11 11:11:11'
);
insert into student values(
3,'陈伟',now(),now(),now(),now()
);
select *from student;
insert into student() values(
4,'高永杰',now(),now(),now(),now()
);
select *from student;
字符类型
char和varchar括号内的参数指的都是字符的长度

char类型：定长，简单粗暴，浪费空间，存取速度快
字符长度范围：0-255

varchar类型：变长，精准，节省空间，存取速度慢
字符长度范围：0-65535

length()：查看字节数
char_length():查看字符数
create table t1(x char(5),y varchar(4));
insert into t1 values('你好啊 ','你好啊 ');
select x,char_length(x),y,char_length(y) from t1;

让char显原型
SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
查看当前mysql的mode模式
select @@sql_mode;
select x,char_length(x) y,char_length(y) from t1;

exit;退出后重新登录
找到db4;
select @@sql_mode;还原模式

枚举类型与集合类型:enum 和set
enum
单选 只能在给定的范围内选一个值，如性别 sex 男male/女female

set
多选 在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3...）

create table consumer(
id int,
name varchar(50),
sex enum('male','female','other'),
level enum('vip1','vip2','vip3','vip4'),#在指定范围内，多选一
fav set('play','music','read','study') #在指定范围内，多选多
);

insert into consumer values
(1,'张三','male','vip2','read,study'),
(2,'李四','female','vip4','play'),
(3,'王五','female','vip3','play,read,study');

select * from consumer;
默认为空
insert into consumer(id,name,sex,level,fav)values(4,'马六','male','vip2','');
为NULL
insert into consumer(id,name,sex,level)values(5,'周七','male','vip2');
完整性约束
作用：用于保证数据的完整性和一致性
not null 不可空
default 默认值 创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
create database db5;
use db5;
nid如果为空默认为2,num不可以为空,如果为空则报错
create table t1(nid int not null default 2,num int not null);
nid为空默认为2
insert into t1(num) values(3);
num为空报错
insert into t1(nid) values(3);


unique 不同的,唯一的
单列唯一
只有一列唯一
create table department(
id int,
name char(10) unique
);
insert into department values(1,'it'),(2,'it');报错
insert into department values(1,'it'),(2,'it2');
多列唯一
多个列是唯一的
create table department1(
id int unique,
name char(10) unique
);
insert into department1 values(1,'it'),(2,'sale');
insert into department1 values(1,'it'),(1,'sale');报错
insert into department1 values(1,'it'),(2,'it');报错
insert into department1 values(1,'it'),(1,'it');报错
或者
create table department2(
id int,
name char(10) ,
unique(id),
unique(name)
);

组合唯一
只要有一个字段不一样都可以插入
create table department3(
id int,
name char(10) ,
unique(id,name)
);
insert into department3 values(1,'it'),(2,'it');
insert into department3 values(1,'it1'),(1,'it2');

primary key
主键 一般是id
create table t2(
id int primary key,
name char(10)not null
);
insert into t2(id,name) values(1,'aaaa'),(1,'bbbb');报错
insert into t2(id,name) values(1,'aaaa'),(2,'bbbb');

create table t3(
id int not null unique,
name char(10)not null
);
insert into t3(id,name) values(1,'aaaa'),(2,'bbbb');
结论:
约束：等价于 not null unique,字段的值不为空且唯一
主键查询速度快
复合主键:只要有一个字段不一样都可以插入
create table t4(
ip char(20),
port int,
primary key(ip,port)
);
insert into t4 values('1.1.1.2',80),('1.1.1.2',81);
insert into t4 values('1.1.1.3',80),('1.1.1.4',80);
insert into t4 values('1.1.1.5',84),('1.1.1.5',84);报错

auto_increment
约束:约束的字段为自动增长，约束的字段必须同时被key约束
id自动排序
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);
insert into student(name) values ('张三'),('李四'),('王五');
select * from student;
delete from student where id=2;删除第二个
再次插入一条不指定id的记录,该字段仍按照删除前的位置继续增长

指定id
insert into student values(6,'马六','female');
insert into student values(10,'周七','female');
再次插入一条不指定id的记录，会在之前的最后一条记录继续增长
insert into student(name) values('赵八');

清空表区分delete和truncate的区别：

delete from t1; #如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。

truncate table t1;数据量大，删除速度比上一条快，且直接从零开始。


show variables like'auto_inc%';
步长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;

insert into student(name) values('钱九');
插入的时候，从起始位置3开始，每次插入记录id+5