MySQL1
MySQL
下载安装
-
一般使用社区版的5.6版本，安装完成后需要添加环境变量(将安装目录下的bin文件夹目录添加到环境变量)便于以后使用，也可不加
-
可以将mysqld添加到系统服务，跟随开机启动服务端，只需要在终端执行命令(要在管理员权限下进行) mysqld --install(本来是路径 --install，因为其路径已经添加到了环境变量)，移除 mysqld --remove,注册完成启动服务后，以后再启动和关闭mysql服务时，只需要net start mysql net stop mysql 退出exit
-
window下查看任务 tasklist |findstr mysql 杀死进程 taskkill /F /PID 号码
-
直接输入mysql进去，通过select user(); 可以查看当前登录的用户是ODBC@localhost，查看所有用户 select user from mysql.user
-
初始状态下，管理员root，密码为空，默认只允许从本机登录localhost,比较危险，所以需要为管理员设置密码 mysqladmin -uroot password "123" 设置初始密码 由于原密码为空，因此-p可以不用 mysqladmin -uroot -p"123" password "456" 修改mysql密码,因为已经有密码了，所以必须输入原密码才能设置新密码 命令格式: [root@egon ~]# mysql -h172.31.0.2 -uroot -p456 #以管理员权限登录但是是在非本机上服务端，需要添加服务端的远程ip地址 [root@egon ~]# mysql -uroot -p 以管理员权限登录 [root@egon ~]# mysql 以root用户登录本机，密码为空 创建用户 create user '用户名'@'IP地址' identified by '密码'; 删除用户 drop user '用户名'@'IP地址'; 修改用户 rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';; 修改密码 set password for '用户名'@'IP地址' = Password('新密码') show grants for '用户'@'IP地址' -- 查看权限 grant 权限 on 数据库.表 to '用户'@'IP地址' -- 授权 grant all privileges on db1.tb1 TO '用户名'@'IP' 授权db1.tb1所有权限 grant select on db1.* TO '用户名'@'IP' 授权db1下所有数据表的查看权限 grant select,insert on *.* TO '用户名'@'IP' 授权所有数据库下所有数据表的查看，插入权限 revoke select on db1.tb1 from '用户名'@'IP' 取消db1.tb1下的查看权限 revoke 权限 on 数据库.表 from '用户'@'IP地址' -- 取消权限 -
忘记root 密码时，跳过密码登录
关闭mysql(因为mysqld已经加入到了系统服务项中，所以可以通过net stop mysql) # 先关闭mysqld #2 在cmd中执行：mysqld --skip-grant-tables 在服务端进行 #3 在cmd中执行：mysql 在客户端进行登录进去后 #4 执行如下sql：改密码 update mysql.user set authentication_string=password('') where user = 'root'; flush privileges; #5 tskill mysqld #或taskkill -f /PID 7832 #6 重新启动mysql """ 基本的原理时，再登录mysql时，由于mysql服务端是运行在本地,所以root账号密码会存在本地的数据文件mysql中，在登陆过程中会进行密码的校验，在启动过程中不加载这些文件(即跳过授权表)，即可达到跳过密码的验证。是在服务端进行 """ 或者 关闭mysql，可以用tskill mysqld将其杀死 #2. 在解压目录下，新建mysql配置文件my.ini #3. my.ini内容,指定 [mysqld] skip-grant-tables #4.启动mysqld #5.在cmd里直接输入mysql登录，然后操作 update mysql.user set authentication_string=password('') where user='root and host='localhost'; flush privileges; #6.注释my.ini中的skip-grant-tables，然后启动myqsld，然后就可以以新密码登录了
# 修改默认字符编码 需要新建一个.ini的文件，然后将配置文件放入，如果未修改成，可能出现的原因是配置文件的命名加载有问题，未第一个加载，可以设置成my.ini来避免这个问题；也有可能是拷贝过程中字符编码没有统一，导致写入的配置文件错误。 通过\s;查看当前使用的字符编码
#mysql5.5以上：修改方式有所改动
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
数据库
-
创建数据库 CREATE DATABASE 数据库名 charset utf8; 不区分大小写(指create，datebase等)
-
数据库命名规则
可以由字母、数字、下划线、＠、＃、＄ 区分大小写(指数据库名) 唯一性 不能使用关键字如 create select 不能单独使用数字 最长128位查看数据库 show databases; 查看所有 show create database db1;查看数据表创建的过程 select database(); 查看当前的数据库名称 2 选择数据库 USE 数据库名 3 删除数据库 DROP DATABASE 数据库名; 4 修改数据库 alter database db1 charset utf8;
# 备份：数据表结构+数据 mysqldump -u root db1 > db1.sql -p 将数据库db1中的所有导入到db1.sql ，文件路径指的时当前运行终端的位置 备份：数据表结构 mysqldump -u root -d db1 > db1.sql -p 只备份表结构 # 将数据导入到数据库 create datebase db5; # 要先创建数据库 mysqldump -u root -d db5 < db1.sql -p 导入数据 -
基本命令
#2. 操作文件 (数据表) 先切换到文件夹下：use db1，如果不切换到文件夹下则需要写绝对目录 增：create table t1(id int,name char); 查：show tables 查看某一个库下所有的表 desc 表名 查看表结构 select * from 表名 查看表下所有的内容 show create table t1\G; #查看表详细结构，可加\G 改：alter table t1 modify name char(3); alter table t1 change name name1 char(2)，change id id1 char(4);修改多个字段 create table t2 select user,host,password from mysql.user; 复制表结构和特定的字段 create table t2 select user,host,password from mysql.user where 1=2; # 只复制表结构不会将附带之前的记录 create table t3 like t2; # 只复制表结构 删：drop table t1; # 不会将表的记录删除，如果主键设置了自增类型，那么下次再建立相同类型的表格时，可能会出现主键跟随着上次的地方增加 # truncate t2删除整张表 #3. 操作文件中的内容/记录 增：insert （into） t1 values(1,'egon1'),(2,'egon2'),(3,'egon3'); 查：select * from t1; 改：update t1 set name='sb' where id=2; 删：delete from t1 where id=1; 清空表： delete from t1; #如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。 truncate table t1;数据量大，删除速度比上一条快，且直接从零开始， auto_increment 表示：自增 primary key 表示：约束（不能重复且不能为空）；加速查找
数据表
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);
#注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的,多个字段时，最后的字段不能加逗号
1. 修改表名
ALTER TABLE 表名
RENAME 新表名;
2. 增加字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…],
ADD 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
3. 删除字段
ALTER TABLE 表名
DROP 字段名;
4. 修改字段
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
数据类型
1. 整型：tinyinit int bigint 为该类型指定宽度时，仅仅只是指定查询结果的显示宽度，与存储范围无关，其实我们完全没必要为整数类型指定显示宽度，使用默认的就可以了 # 一般使用int
小数：
float ：在位数比较短的情况下不精准 # 一般使用float
double ：在位数比较长的情况下不精准
decimal：（如果用小数，则用推荐使用decimal） 精准 内部原理是以字符串形式去存
2. 字符串： length：查看字节数 char_length:查看字符数 select x,length(x),y,length(y) from t1;
# char和varchar括号内的参数指的都是字符的长度
char（10）：简单粗暴，浪费空间，存取速度快
字符长度范围：0-255（一个中文是一个字符，是utf8编码的3个字节）
存储：
存储char类型的值时，会往右填充空格来满足长度 root存成root000000
例如：指定长度为10，存>10个字符则报错，存<10个字符则用空格填充直到凑够10个字符存储
检索：
在检索或者说查询时，查出的结果会自动删除尾部的空格，除非我们打开pad_char_to_full_length SQL模式（SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';）
varchar：精准，节省空间，存取速度慢 # 一般用varchar
字符长度范围：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的最大字节限制，即已经足够使用
sql优化：创建表时，定长的类型往前放，变长的往后放
比如性别 比如地址或描述信息
>255个字符，超了就把文件路径存放到数据库中。比如图片，视频等找一个文件服务器，数据库中只存路径或url。
3. 时间类型：
最常用：datetime
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 年某时
create table t1(x datetime not null default now()); # 需要指定传入空值时默认取当前时间
create table t2(x timestamp); # 无需任何设置，在传空值的情况下自动传入当前时间
create table t9(name varchar(5),x datetime not null default now()); # 一般用于注册时间，该时间不会变更
create table t10(name varchar(5),x datetime not null default now() on update now()) # 更新时间那么需要额外指定on update now()
create table t11(name varchar(5),x timestamp); # 会再修改其他字段的信息时自动修改更新时间，一般用于记录更新的时间
4. 枚举类型与集合类型
create table consumer(
-> name varchar(50),
-> sex enum('male','female'),
-> level enum('vip1','vip2','vip3','vip4','vip5'), #在指定范围内，多选一
-> hobby set('play','music','read','study') #在指定范围内，多选多
-> );
insert into consumer values
-> ('QQ','male','vip5','read,study'),
-> ('WC','female','vip1','girl'); # 指定为范围内没有将会空下来
约束
约束条件与数据类型的宽度一样，# 都是可选参数
PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录 # 一张表中必须有且只有一个主键。
FOREIGN KEY (FK) 标识该字段为该表的外键
NOT NULL 标识该字段不能为空
UNIQUE KEY (UK) 标识该字段的值是唯一的
AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键） #可以设置起始值和步长，默认都为1
DEFAULT 为该字段设置默认值
UNSIGNED 无符号
ZEROFILL 使用0填充
==========单列做主键===============
#方法一：not null+unique
create table department1(
id int not null unique, #主键
name varchar(20) not null unique,
comment varchar(100)
);
==================多列做主键================
create table service(
ip varchar(15),
port char(5),
service_name varchar(10) not null,
primary key(ip,port) # 多列主键
);
"""
表的三种关系
"""
=================foreign key=============== 多对一 多对多 一对一 主要看主键写在哪个地方
表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一,关联父表，同步更新，同步删除
# 先创建被关联表 再创建关联表 先往被关联表插入数据 再往关联表插入数据
==============一对一====================
create table department(
id int primary key,
name varchar(20) not null
)engine=innodb;
#dpt_id外键，关联父表（department主键id），同步更新，同步删除
create table employee(id int primary key,name varchar(20) not null,dpt_id int,
foreign key(dpt_id) references department(id) on delete cascade on update cascade
)engine=innodb;
#先往父表department中插入记录
insert into department values
(1,'欧德博爱技术有限事业部'),
(2,'艾利克斯人力资源部'),
(3,'销售部');
#再往子表employee中插入记录
insert into employee values
(1,'egon',1),
(2,'alex1',2),
(3,'alex2',2),
(4,'alex3',2),
(5,'李坦克',3),
(6,'刘飞机',3),
(7,'张火箭',3),
(8,'林子弹',3),
(9,'加特林',3);
#删父表department，子表employee中对应的记录跟着删
mysql> delete from department where id=3;
mysql> select * from employee;
#更新父表department，子表employee中对应的记录跟着改
mysql> update department set id=22222 where id=2;
mysql> select * from employee;
=====================多对一=====================
create table press(
id int primary key auto_increment,
name varchar(20)
);
create table book(
id int primary key auto_increment,
name varchar(20),
press_id int not null,
foreign key(press_id) references press(id)
on delete cascade
on update cascade
);
insert into press(name) values
('北京工业地雷出版社'),
('人民音乐不好听出版社'),
('知识产权没有用出版社')
;
insert into book(name,press_id) values
('九阳神功',1),
('九阴真经',2),
('九阴白骨爪',2),
('独孤九剑',3),
('降龙十巴掌',2),
('葵花宝典',3)
;
=====================多对多================== foreign key+一张新的表
create table author(
id int primary key auto_increment,
name varchar(10)
);
create table book(
id int primary key auto_increment,
name varchar(16)
);
create table author2book(
id int primary key auto_increment,
author_id int,
book_id int,
foreign key(author_id) references author(id) on delete cascade on update cascade,
foreign key(book_id) references book(id) on delete cascade on update cascade
);
===============一对一================
create table customer(
id int primary key auto_increment,
name varchar(16),
phone char(11)
);
create table student(
id int primary key auto_increment,
class varchar(10),
course varchar(16),
c_id int unique,
foreign key(c_id) references customer(id) on delete cascade on update cascade
);
