MySQL安装与讲解
一：数据库讲解15.6之前的源码安装
源码格式 ./configure 选项
make make install
2yum安装
6yum -y install mysql-server mysql
servicemysqld start； chkconfigmysqld on
7yum-y groupinstall mariadbmariadb-client
Systemctlenable/restart mariadb
3基础
端口号 3306
进程名 mysqld
进程所有者/组 mysql/mysql
数据传输协议 tcp
主配置文件 /etc/my.cnf
数据库目录 /var/lib/mysql/
错误日志文件 /var/log/mysqld.log
进程pid号文件 /var/run/mysqld/mysqld.pid
每条sql命令必须以 ; 结尾
sql命令不区分字母大小写
\c 结束sql命令
查看已有的库 showdatabases;
information_schema 虚拟库
存储当前数据库服务器上已有库和表的信息/数据存储在物理内存里
mysql 授权库
存储用户登录信息和权限信息/占用物理存储空间
performance_schema
存储当前数据库服务运行的参数信息
占用物理存储空间
mysql体系结构 （8）
连接池
sql接口
解析器
优化器
缓存区servicemysqlstart /etc/my.cnf 8M
存储引擎
文件系统（硬盘/var/lib/mysql）
管理工具
4数据库连接
mysql -h数据库服务器Ip地址 -u用户名 -p密码 [库名]
mysql -uroot -p123456 -e "show databases;" 非交互
5设置密码
1 mysqladmin -hlocalhost -uroot password "abc123"
2 mysql> setpasswordforroot@"localhost"=password("999");
修改数据库管理员密码
1# mysqladmin-hlocalhost -uroot -ppassword "999"
Enter password: 旧密码
2恢复数据库管理员密码
#servicemysqlstop
#servicemysqlstart --skip-grant-table
#mysql
mysql> update mysql.user
-> set
-> password=password("123")
-> where
-> host="localhost";
mysql> flush privileges;
#servicemysqlstop
#servicemysqlstart
6升级
安装下载高版本的软件包提供mysql数据库服务
servicemysqldstop
rpm-e--nodeps mysql-server mysql
rm-rf/etc/my.cnf
rm-rf/var/lib/mysql/*
tar -xvf MySQL-5.6.rpm.tar
rpm -Uvh MySQL-*.rpm
rpm-qa | grep-i mysql
service mysql start;chkconfig mysql on
cat/root/.mysql_secret
mysql -hlocalhost -uroot -pNlUDn9Wn
mysql>
mysql> setpasswordforroot@"localhost"=password("999");
mysql>quit
mysql -hlocalhost -uroot -p999
mysql> showdatabases;
二数据库操作
1部分SQL命令（库）
查看当前登录的用户信息
select user();
查看当前所在库的名字
select database();
创建库
createdatabase库名；
删除库
drop database库名
切换库
use 库名；
数据库名的命名规则？
可以使用数字/字母/下划线，但不能纯数字
区分大小写，具有唯一性
不可使用指令关键字、特殊字符。
2部分SQL命令（表）
查看里已有的表
show tables；
查看表结构
desc 表名;
查看表记录
select*from表名;
select字段名1，字段名2，字段名N from表名；
select 字段名列表 from库名.表名 where条件；
向表中添加记录
insert into stuinfo(name)values("j"),("t");
表的使用（表必须存储在库里）
建表
create table表名(
字段名1字段类型(宽度)字段约束，
字段名2字段类型(宽度)字段约束，
字段名3字段类型(宽度)字段约束，
.......
);
复制表
createtable 表名 SQL查询命令;
createtableuser2select* from user;
createtableuser3selectid,name,uid from user limit 3;
createtableuser4select* from user where 1 = 2;
删除表的所有记录
deletefrom 表名；
insertintobjb values(1,"zzz");
insertintobjb values(4,"bob");
update 表名 set 字段名=值where条件；
update jfb set jfb_id=8 wherejfb_id=2;
deletefrom 表名where 条件；
delete fromjfbwherejfb_id=3;
delete from user where name regexp '';
3修改表结构(受表中已有记录的限制)
alter table 表名动作；
add 添加新字段
add字段名 类型（宽度）约束条件；
altertablet26
add mail varchar(30)not null default "plj@tarena.com",
addqq varchar(11);
altertablet26add stu_id int(2)first;
alter tablet26add agetinyint(2) unsignednot null default "21"after name;
drop删除已有字段
drop 字段名；
drop字段名,drop 字段名;
4modify修改已有字段的类型
modify字段名 类型（宽度） 约束条件；
alter table t26modify sexenum("boy","girl")default "girl";
change修改字段名
change 源字段名新字段名类型（宽度） 约束条件；
altertable t26change emailmailvarchar(10);
修改表名
altertable 源表名 renam新表名；
三数据库字段类型（表）
1 字符型
char （255） 定长
varchar（65532） 变长
blob
text
createtable stuinfo(namechar（10）);
createtable stuinfo2(namevarchar(3));
2 数值型
微小整型小整型 中整型大整型极大整型
有符号 无符号
createtable t9(namechar(5),agetinyintunsigned);
createtable t10(namechar(5),ageintunsigned);
createtable t11(namechar(5),agetinyintunsigned，payint(2));
浮点型 float 单精度 4字节
double双精度 8字节
float(N,M)
double(N,M)
N数值的总位数
M小数位位数
createtable t11(
namechar(5),
agetinyintunsigned,
pay float(7,2)
3枚举型
set(值1，值2，值N) 选择一个或多个
enum(值1，值2，值N）只能选择一个
createtablestudb.t25(
name varchar(10),
sex enum("boy","girl"),
loveset("game","film","music","girl"),
classset("network","system","server","shell")
);
4日期类型
年 year（YYYY）
两位自动补规则
01~69 2001~2069
70~99 1970~1999
createtablet23(
namechar(10),
s_year year
);
insertinto t23 values("jim",01);
insertinto t23 values("bob",80);
insertinto t23 values("jerry",00);
日期date（YYYYMMDD）
时间time (HHMMSS)
日期时间
datetime
1000-01-01 00:00:00.000000
9999-12-3123:59:59.999999
timestamp
1970-01-0100:00:00.000000
2038-01-1903:14:07.999999
createtablet22(
time1 datetime,
time2 timestamp
);
insertinto t22values(20151211094418,20151211094418);
insertinto t22(time2)values(20160214000000);
insertinto t22(time1)values(20170214200000);
与日期相关函数
now()
year()
month()
day()
time()
select time(now());
select day(now());
selectnow();
四：字段约束条件的设置(限制如何给字段赋值)
1 是否允许给字段赋空null/NULL值 默认允许为NULL值
not null
字段默认值向表中添加新记录时，不给字段赋值时，使用默认值给字段赋值，若没有
设置过默认值的值， 默认值的值是null值
2 default值
createtablet26(
name varchar(10)notnull,
sex enum("boy","girl")notnulldefault "boy",
loveset("game","film","music","girl")default"game,film",
classset("network","system","server","shell")default "shell"
);
insertinto t26(name)values("jerry");
insertinto t26 values("lucy","girl","game","network");
insertinto t26 values(null,null,null,null)
3 非零zerofill
五：索引
1 索引好处
索引优点： 加快查询的速度
索引缺点 ： 占用物理存储空间
减慢 updateinsert delete
2 索引查看
showindexfrom 表名;
3 index索引
一个表中可以有多个INDEX字段
对应的字段值允许有重复
可以赋null 值
把经常做查询条件的字段设置为INDEX字段
INDEX字段的KEY标志是MUL
方式一
create indexnameontt26(name);
create indexsexontt26(sex);
方式二
createtablet27(
name varchar(10)notnull,
sex enum("boy","girl")notnulldefault "boy",
loveset("game","film","music","girl")default"game,film",
classset("network","system","server","shell")default "shell",
index(name),
index(sex)
);
索引名 表名
dropindex name on tt26;
4 unique索引
一个表中可以有多个UNIQUE字段
对应的字段值不允许有重复,但可以赋NULL值；
UNIQUE字段的值允许为NULL,将其修改为不允许为NULL，则此字段限制与主键相同
UNIQUE字段的KEY标志是UNI
方式一
createuniqueindex stu_id ontt26(stu_id);
方式二
create table t28(
stu_idchar(4) ,
iphone char(11),
namevarchar(10),
unique(stu_id),
unique(iphone)
);
drop index stu_id ontt26;
5 主键（primarykey）
一个表中只能有一个PRIMARY字段
对应的字段值不允许重复且不允许为null
主键字段的KEY标志是PRI
如果有多个字段都作为PRIMARY KEY，称为复合主键，必须在建表时一起创建.
通常与 AUTO_INCREMENT 连用
让字段的值自动增长 ++
数值类型
字段必须是primay key 字段
把表中能够唯一定位一条记录的字段设置为主键字段
createtablet211(
namevarchar(10) primary key ,
agetinyint(2)
);
altertablet211dropprimary key;
altertablet211addprimary key(age);
createtablet222(
idint(2)primary key auto_increment,
namevarchar(10) not null,
agetinyint(2) unsigned,
index(name)
);
insert intot222 (name,age) values("bob",23);
altertablet222modify id int(2) notnull;
--------------------------------------------------------------------
altertable t222
add
id int(2) primarykey auto_incrementfirst ;
createtable test3(
id1int(3) zerofill,
level int zerofill
);
数值类型的宽度是 显示宽度 ，不能够控制给字段赋值的大小。字段值的大小由字段类型决定。
insertintotest3 values(9,9);
insertintotest3 values(27,27);
------------------------------------------------------------------
createtable sertab(
cip varchar(15) ,
sernamevarchar(20),
serport smallint(2) ,
status enum("deny","allow") not null default"deny",
primary key (cip,serport)
);
6 外键
1 表的存储引擎必须是innodb
2 字段的类型要一致
3 被参考字段必须是一个key(主键)
createtable表名(字段名列表)engine=存储引擎名 DEFAULT CHARSET=字符集；
创建外键
foreignkey(字段名)references 表名（字段名） ondelete cascade on updatecascade
缴费表
createtable jfb(
jfb_id tinyint(2) primary keyauto_increment,
class char(7)not null default "nsd1509",
namevarchar(15),
xffloat(7,2)default "17800"
)engine=innodb;
insert into jfb(name)values("zzz");
insert into jfb(name)values("mda");
insert into jfb(name)values("jack");
班级表
create table bjb(
bjb_idtinyint(2),
namevarchar(15),
foreignkey(bjb_id)references jfb(jfb_id) ondelete cascade on updatecascade
)engine=innodb;
删除表字段的外键属性
mysql>> 六：存储引擎
1 查看当前数据库服务器支持哪些存储引擎
showengines;
2 存储引擎MyISAM与InnoDB
MyISAM
不支持事务、事务回滚 、 外键
支持表级锁
独享表空间bt.frm 表结构
bt.MYD表记录
bt.MYI 表索引
表级锁
InnoDB
Supports transactions, row-level locking, and foreign keys
事务 ？一次SQL操作从开始到结束的过程
事务回滚 ： 当操作没有正确完成时，把数据恢复到操作之前的 状态。
事务日志文件
ib_logfile0
ib_logfile1
ibdata1
共享表空间 at.frm 表结构
at.ibd 表记录+表索引
行级锁
读锁 （ 共享锁）
写锁（互斥锁 排它锁
3 设置事务状态
mysql> showvariables like "%commit%";
mysql> set autocommit=off； 事务自动提交关闭
starttransaction; 开启事务
4 存储引擎选择
编辑操作多的表使用行级锁(innodb)
查询操作多的表使用表级锁(myisam)
5 存储引擎设置
修改表的存储引擎
altertable 表名engine=存储引擎；
修改数据库服务默认使用的存储引擎
servicemysql stop
vim/etc/my.cnf
default-storage-engine=myisam
:wq
servicemysqlstart
七：文件与数据库的转换（导入与导出）
1 文件到数据库（导入），，，先有表结构
mysql>LOAD DATA INFILE "系统文件名"
INTO TABLE表名
FIELDS TERMINATED BY"分隔符"
LINES TERMINATED BY"\n";
例子
把当前系统用户信息保存到数据库 studb库的user表里。
/etc/passwd studb.user
createtablestudb.user(
name varchar(25),
passwordchar(1),
uidint(2),
gidint(2),
comments varchar(50),
homedirvarchar(50),
shell varchar(20),
index(name)
);
load data infile"/etc/passwd"into table userfieldsterminated by ":" lines terminated by"\n";
altertable user add id int(2) primary keyauto_incrementfirst;
2 数据库到文件（导出）
l库下user表的所有记录保存到系统/mydata目录下user.txt文件里。
mkdir/mydata
chownmysql/mydata
select* frommysql.userinto outfile"/mydata/user.txt";
SELECT查询命令
INTO OUTFILE “文件名”fieldsterminated by "#" lines terminated by"!!!"；
例子
select name,uid,gidfrom userintooutfile"user4.txt"fieldsterminated by "#"lines terminated by"!!!" ;
导出的内容由SQL查询语句决定
若不指定路径，默认会放在执行导出命令时所在库对应的数据库目录下。
应确保mysql用户对目标文件夹有写权限。
八： 查询条件
查询表记录时条件的表示方式
1、字符比较
相等 =
不相等 !=
selectid,namefrom user wherename!="zzz";
selectnamefromuserwhereshell="/sbin/nologin";
2、数值比较
相等不相等大于 大于等于 小于 小于等于
= != > >= < =500 order byuid desc;
14 limit 限制显示查询记录的条目数
limitN,M
N从第几条记录开始显示
第一条记录的编号是零
M共显示几条记录
select* from userlimit0,1 ;
select * from user order by uid desc limit 1；
select * from user order by uid desc limit2,5；
15 嵌套查询
把内层查询结果作为外层查询的查询条件
select字段名列表from 表名 where 条件 (sql查询)；
select name,systemfrom user where system > (select avg(system)from user);
selectnamefrom user wherename = (select name from user2whereuid =3);
selectnamefrom user wherename in (select name from user2whereuid >=3);
16 多表查询
select 字段名列表 from表1，表2，表Nwhere 条件;
*
表1.字段名
表2.字段名
select* fromatab,btab;迪卡尔集
selectatab.name,btab.shellfrom atab,btab where 条件;
selectatab.name from atab,btab whereatab.name = btab.name;
18 左连接查询（以左表纪录为主 显示）
select 字段名列表 from 表aLEFT JOIN表bON条件表达式；
右连接查询（以右表纪录为主 显示）select 字段名列表 from 表aRIGHT JOIN表bON条件表达式；
mysql> select atab.name,btab.shell from atab right join btab onatab.uid =btab.uid;
select * from atab left join btab onatab.uid!=btab.uid;
select name from atab where uid not in (select uid from btab);
九: 数据库授权与撤销
1 查询授权
show grants;连接数据库服务器的用户查看自己的权限信息
查看数据库服务器上有哪些授权用户？
select user,hostfrom mysq.user
2 权限列表
命令 权限
all 所有权限
usage 无权限
SELECT 查询表记录
INSERT 插入表记录
UPDATE 更新表记录
DELETE 删除表记录
CREATE 创建库、表
DROP 删除库、表
RELOAD 有重新载入授权 必须拥有reload权限，才可以执行flush
SHUTDOWN 允许关闭mysql服务 使用mysqladmin shutdown 来关闭mysql
PROCESS 允许查看用户登录数据库服务器的进程 （showprocesslist;）
FILE 导入、导出数据
REFERENCES 创建外键
INDEX 创建索引
ALTER 修改表结构
SHOW DATABASES 查看库
SUPER 关闭属于任何用户的线程
CREATE TEMPORARY TABLES 允许在createtable 语句中使用TEMPORARY关键字
LOCK TABLES 允许使用LOCK TABLES语句
EXECUTE执行存在的Functions,Procedures
REPLICATION SLAVE 从主服务器读取二进制日志
REPLICATION CLIENT 允许在主/从数据库服务器上使用 showstatus命令
CREATE VIEW创建视图
SHOW VIEW 查看视图
CREATE ROUTINE 创建存储过程
ALTER ROUTINE修改存储过程
CREATE USER 创建用户
EVENT 有操作事件的权限
TRIGGER,有操作触发器的权限
CREATE TABLESPACE有创建表空间的权限
3 客户端地址的表示方式？
172.40.7.213 固定Ip地址
192.168.1.% 网段
% 所有地址
pc10.tarena.com 主机名
%.tarena.com 区域
4 授权的信息存储与库和表
user 存储授权用户权限信息 *.*
db 库的权限信息 库名.*
tables_priv 表的权限信息 库名.表名
columns_priv 字段的权限信息 update(name,sex)
5用户授权
用户有授权权限要具备2个条件
1 有授权权限with grant option
2对授权库要有写入权限
3给其他用户授权时，权限不能大过自己本身的权限
grant 权限列表on数据库名 to用户名@"客户端地址"identified by"密码"；
grant 权限列表on数据库名 to用户名@"客户端地址"identified by"密码"with grant option;
withgrantoption 设置授权用户有授权权限
管理员可重设授权用户的密码
SET PASSWORD
FOR 用户名@'客户端地址'=PASSWORD('新密码');
6 权限撤销
* 有过授权才可以撤销权限。
* revoke 撤销的是权限
revoke权限列表on 数据库名from 用户名@"客户端地址"
grantall on*.* to root@"172.40.7.42"identifiedby "123456"with grant option;
revokegrantoption on*.* fromroot@"172.40.7.42";
revokedeleteon*.*fromroot@"172.40.7.42";
revoke allonstudb.*fromroot@"172.40.7.42";
页:
[1]