MySQL常用操作和主从配置教程

MySQL常用操作和主从配置

介绍关于MySQL的常用操作和注意事项

MySQL的基本操作,增删改查

按照数据库的操作对象分成3类,

数据库操作

1.创建数据库:create database 数据库名称(库选项)

库选项,用来约束数据库的。 字符集设定: charset/character set 具体字符集(数据存储的编码格式),常用的字符集有GBK和UTF8 校对集设定:collate 具体校对集(数据比较的规则) --是单行注释,#也可以作为注释 创建数据库:create database mvcdemo charset utf8; 注意在控制台使用mysql数据库的时候首先应该指定其字符集:set names gbk

2.查看数据库:show databases

查看指定的数据库(模糊查询),%表示匹配多个字符,_表示匹配任意一个字符:show databases like '' 注意转移字符使用\ 查看数据库的创建语句:show create database mvcdemo

3.更新数据库:

数据库名字不可以修改,数据库的修改仅限于库选项,库选项包括字符集和校对集 alter database 数据库的名字 库选项 —————>alter database mvcdemo charset gbk

4.删除数据库:drop database mvcdemo

数据库中的表的操作

创建表:create table [if not exists] 表名(字段名字 列类型)[表选项] 其中if not exists表示如果表存在就不执行后面的语句,否则就执行后面的语句并创建表

表选项:控制表的表现:

字符集:charset/character set 具体字符集 collate:校对集 存储引擎:engine:具体的存储引擎(innodb和myisam)默认是innodb

create table if not exists student(name varchar(10),gender varchar(10),number varchar(10),age int)charset utf8; 查看有结构的表的创建语句:show create table student\G 查看表的结构:desc student/describe student/show columns from student

3.修改表:

修改表名:rename table 旧表名 to 新表名 修改表选项:字符集、校对集和存储引擎。alter table my_student charset=gbk 修改表字段(字段的新增,修改,重命名,删除) 新增字段:alter table 表名 add [column] 字段名 数据类型 [列属性] [位置]

其中字段名可以存在表中的任意的位置,first:第一个位置;after字段名:默认是在最后一个字段之后 给学生表增加id:alter table my_student add column id int first 修改字段:修改字段通常是指修改属性或者说是数据类型。

alter table 表名 modify 字段名 数据类型 [属性] [位置]

将学号字段变成固定长度且放在第二位: alter table my_student modify number char(10) after id 重命名字段 alter table my_student change gender sex varchar(10) 删除字段 alter table my_student drop age

4.删除表:drop table my_student

5.删除数据:delete from my_student

查看数据库服务器支持哪一些字符集:

show character set

查看数据库默认使用的对外数据处理的字符集:

show variables like 'character_set%'

查看所有的校对集

show collation

MySQL中的数据类型

数值型:数值型包括整数型和小数型。

Tinyint：迷你整型,使用一个字节存储,表示的状态可以达到256种 Smallint:小整型,使用2个字节存储,表示的状态可以达到65536种 mediumint:中整型,使用3个字节存储 int:标准整型,使用4个字节存储 Bigint:大整型,使用8个字节存储

小数型:带有小数点或者范围超出整型的数据类型。

在SQL中,将小数型细分成2种，浮点型和定点型 浮点型:小数点浮动,精度有限,而且会丢失精度

定点型:小数点固定,精度固定,不会丢失精度

浮点型数据是一种精度型数据,因为超出精度之后会丢失精度(自动四舍五入),其中float(M,D)表示有M位有效数字,其中D表示有D位小数

时间日期型:

Datetime:时间日期,格式是YYYY-mm-dd HH:ii:ss表示的范围是从1000到9999,年有0值。 Date:日期就是指的时间日期型中的日期部分 Time:时间段，指定的，某个区间之间,-时间到+时间 Timestamp:从1970开始计算的时间,其中的格式与Datetime时间日期类型完全一致 Year:年份 create table my_date(d1 datetime,d2 date,d3 time,d4 timestamp,d5 year) charset utf8 insert into my_date values('2017-4-11 18:54:50','2017-4-11','18:55:00','2017-4-11 18:54:50',2017);

MySQL中的字段属性

主键:primary key。

`

mysql> create table my_pri1(
name varchar(20) not null comment '姓名',
number char(10) primary key comment '学号,不能重复')charset utf8;


mysql> create table my_pri2(
-> number char(10) comment '学号',
-> course char(10) comment '课程代码',
-> score tinyint unsigned default 60 comment '成绩',
-> -- 增加复合主键
-> primary key(number,course));


追加主键方式一:alter table my_pri3 modify course char(10) primary key;
追加主键方式二:alter table my_pri3 add primary key(course);

删除主键:alter table 表名 drop primary key;

2. 查看自增长:

    show variables like 'auto_increment%';查看起始值和步长
    set auto_increment_increment=5;//设置自增长的步长为5 
`

3. 删除唯一键:

    alter table 表名 drop index 索引的名字;//索引可以通过建表语句来得到,默认是字段的名字

`

MySQL中的触发器:

触发器:

触发器有三个要素,分别是事件的类型、事件的触发时机和事件的触发对象。其中，事件的类型有三种，分别是对表的增删改；触发的时间有两个分别是操作之前和之后；触发的对象指代的是表中的某一条记录。注意一张表中只能最多有6个触发器。

创建触发器:

create trigger 触发器名 触发时间 事件类型 on 触发对象(表名) for each row begin            
    每一行都有一个语句的结束符;
end; 

create table my_goods(id int primary key auto_increment,name varchar(20) not null,price decimal(10,2) default 1,inv int comment '库存数量')charset utf8;

insert into my_goods(name,price,inv) values('iphone6ps',5888,102),('戴尔笔记本',5999,76);

create table my_order(id int primary key auto_increment,g_id int not null comment '商品id',g_number int comment '商品数量')charset utf8;

-- 触发器,订单生成一个商品的数量就减少
-- 指定临时语句的结束符
delimiter $$

create trigger after_order after insert 
on my_order for each row
begin
update my_goods set inv=inv-1 where id=2;
end
$$
delimiter ;

-- 查看所有触发器
show triggers ;

MySQL中的外键

新建外键

    create table my_foreign1(
        id int primary key auto_increment,
        name varchar(20) comment '学生姓名',
        c_id int comment '班级id',
        -- 增加外键
        foreign key(c_id) references my_class(id)
    )charset utf8;

    //在创建表之后增加外键
    alter table 表名 add [constraint 外键名字] foreign key(外键字段) references 父表(主键字段);

`

删除外键

外键不可修改,只能先删除然后再新增.
alter table my_foreign drop foreign key 外键名

MySQL中的视图

创建视图

create view 视图名 as select语句

create view my_student_v1 as select * from student;

create view my_book_v1 as select * from book;

视图内容不可以修改,但是视图的来源可以修改,结构也可以修改

alter view my_v1 as select name from book;

删除视图:

drop view 视图名 

mysql的关键字:

order by。在sql语句中是针对于具体的列进行排序,默认是进行升序的排序并显示所查数据。如果是多列共同orderby那么是只有在前面的列完全相同的时候才考虑后面的列是否有序并进行排序。 2.

MySQL服务器的主从同步:

通过MySQL的主从同步的配置,实现数据库的读写分离,从而提高过大的压力下数据库的可靠性。

主从同步的理论支持:

Master首先将主数据库服务器中对数据的操作记录到二进制日志(Binary log)当中,换句话说,Mysql将每次一操作的事务都存储在了二进制文件(Binary log)当中,这一次的操作也叫做一次Binary log 事件; Slave子服务器会在本地开启一个IO线程,该IO线程会在Master主服务器上打开一个连接,然后将远程的Binary log文件拷贝到本地,如果读取Binary log得到的信息与Master保持的是一致的,此时子服务器的IO线程就会睡眠避免在Master上产生事件。最后,IO线程将事件写入到Relay log之中。 Slave子服务器重做Relay log中的事件。就是主MySQL服务器中的事务操作会在子服务器上重新的做一遍。子服务器上会创建一个SQL Thread线程专门完成这一步的操作。

首先准备两台已经安装好Mysql的主机,我这里采用的是虚拟机,ip地址分别是:192.168.33.13和192.168.33.14。其中192.168.33.13作为主服务器,192.168.33.14作为从服务器来实现主从同步。

首先配置主服务器:192.168.33.13:vim /etc/my.cnf

            [mysqld]
            server-id=1
            log-bin=master-bin
            log-bin-index=master-bin.index
            datadir=/var/lib/mysql
            socket=/var/lib/mysql/mysql.sock
            user=mysql
            # Disabling symbolic-links is recommended to prevent assorted security risks
            symbolic-links=0

            [mysqld_safe]
            log-error=/var/log/mysqld.log
            pid-file=/var/run/mysqld/mysqld.pid

之后service mysqld restart重启mysql,登录本地服务器查看log-bin文件:show master status;

然后配置从服务器:192.168.33.14:vim /etc/my.cnf

            [mysqld]
            server-id=2
            relay-log-index=slave-relay-bin.index
            relay-log=slave-relay-bin
            datadir=/var/lib/mysql
            socket=/var/lib/mysql/mysql.sock
            user=mysql
            # Disabling symbolic-links is recommended to prevent assorted security risks
            symbolic-links=0

            [mysqld_safe]
            log-error=/var/log/mysqld.log
            pid-file=/var/run/mysqld/mysqld.pid

之后service mysqld restart重启mysql,登录本地服务器查看信息:show slave status;

在主库上配置一个从库的账号用于登录实现主从同步:

grant replication slave on *.* to 'beautifulsoup'@'192.168.33.14' identified by 'password';

flush privileges;

在从库上配置主库连接的信息:

change master to master_host='192.168.33.13',master_port=3306,master_user='beautifulsoup',master_password='password',master_log_file='master-bin.000001',master_log_pos=0;

start slave;