MySQL 学习（二)

一、约束条件
字段约束条件：限制如何给字段赋值的；（写在类型后面）
NULL 允许为空，默认设置；
NOT NULL 不允许为空；
Key 索引类型；普通索引、唯一索引、全文索引、主键 、外键
Default 设置默认值，不设置时系统定为NULL；
extra 额外设置，字段是否设置为自动增长。
例子：mysql> create table t5(
-> classroom char(7) default “nsd1809”,
-> name char(5) not null,
-> age tinyint(2) unsigned zerofill default 18,
-> sex enum(“boy”,“girl”) not null default “boy”
-> );
mysql> desc t5;
mysql> insert into t5(name) values(“lucy”);
mysql> insert into t5 values(“nsd1902”,“alice”,31,“girl”);
mysql> select * from t5;
数值类型的宽度是显示宽度，不能够给字段赋值的大小，字段的值由类型决定。
mysql> create table t2(
-> name char(3),
-> age int(3) （输入的值大于int()指定的位数时，不会影响输出显示）
-> level int(3) zerofill ); （int 类型最大位数为11位，当输入的数字小于指定的显示位数时，zerofill指的是自动用 0 补齐；）
mysql> insert into t2 values (“laa”,23,8); （结果是：laa | 23 | 008)
##1、修改表结构
基本用法 ： —alter table 库名.表名 执行动作；
执行动作：—add 添加字段； （字段名 类型（宽度）约束条件；可加after 字段名； 或者first;）
----modify 修改字段类型； （字段名 类型（宽度）约束条件；可加after 字段名； 或者first;）
重点：修改时与已经存储的数据矛盾的话，不允许修改。也可以调整位置。
例子：mysql> select * from db2.t1;
mysql> alter table t1 add level int(7) null;
mysql> alter table t1 modify addrhome char(10)default “beijing”;
mysql> alter table t1 modify age tinyint(4) first;
mysql> alter table t1 change addrhome home char(10) default “beijing”;
mysql> desc t1;
----change 修改字段名； (源字段名 新字段名 类型（宽度） 约束条件；)
----drop 删除字段； （字段名；表中有多条记录时，所有列的此字段的值都会被删除。）
----rename 修改表名； （源表名 rename 新表名；表对应的文件名，也会改变）
mysql> alter table t1 rename dogperson;
mysql> show tables;
##2、MySQL键值
设置在表中字段上的，作用是约束如何给字段赋值。同时给字段做索引。
###2.1 MySQL 索引概述
索引：树状目录结构，类似与书的目录
—是对记录集的多个字段进行排序的方法；
----类似于书的目录；
----索引类型：Btree B+tree hash
索引的优点：—通过创建唯一性索引，可以保证数据库表每一行数据的唯一性；
----可以加快数据的检索速度
缺点：----当对表中的数据进行增加、删除、修改的时候，索引也要动态维护，降低了数据的维护速度；
-----索引需要占用物理空间；减慢编制表记录的速度。
###2.2 键值类型
index 普通索引
使用说明： —一个表中可以有多个index 字段；
—字段的值允许有重复，且可以赋NULL值；
—经常把做查询条件的字段设置为index字段；
—index字段的key标志为MUL
查看：desc 表名；
show index from 表名 （显示表的索引详细信息）
show index from 表名 \G （\G，G为大写，每一个字段按一行显示)
Table:t2 表名
key_name:aaa 索引名
column_name:age 字段名
index_type:btree(二叉树)
建表的时候指定索引字段：create table 表名（字段列表，index（字段名），index(字段名)）；
—index (字段1)，index（字段2）
例子： mysql> create table t3(
-> classroom char(7) default “nsd1802”,
-> name char(5) not null,
-> age tinyint(2) unsigned zerofill default 18,
-> sex enum(“boy”,“girl”) not null default “boy”,
-> index(name),
-> index(age),
-> index(classroom)
-> );
mysql> desc t3;
mysql> show index from t3\G;
在已有的表中设置index 字段
----create index 索引名 on 表名 （字段名）；
例子： mysql> create index aaa on t2(age);
mysql> desc t2;
mysql> show index from t2\G;
key_name:aaa
column_name:age
index_type : BTREE(二叉树) 、Hash
删除指定表的索引字段：
----drop index 索引名 on 表名；
例子： mysql> drop index aaa on t2;
mysql> desc t2;
mysql> show index from t2;
查看表的索引信息：
-----show index from 表名；
unique 唯一索引
fulltext 全文索引
primary key 主键
注意事项：—一个表中只能由一个primary key 字段
—对应的字段值不允许有重复，且不允许赋NULL值；
—如果由多个字段都作为primary key ，称为复合主键，必须一起创建；
—主键字段的key标志为PRI；
—通常与AUTO_INCREMENT连用；
—经常把表中能够唯一标识记录的字段设置为主键字段【记录 】*
查看：desc 表 ；(key ---->PRI)
建表的时候指定主键字段：create table 表名 (字段列表， primary key (字段名))；
或者 create table 表名（字段 类型（宽度） primary key);
例子1：mysql> create database stdb;
mysql> create table stdb.t22(
-> person_id char(18),
-> name char(10),
-> age tinyint(2) unsigned,
-> primary key(person_id)
-> );
mysql> desc t22;
mysql> insert into t22 values(“fxxxx”，“haha”,18); 添加 成功
mysql> insert into t22 values(“fxxxx”,“lucy”,19); 失败，不符合主键唯一性；
mysql> insert into t22 values(“bxxxx”,“lucy”,19); 成功；
例子2：mysql> create table stdb.t23(
-> person_id char(18) primary key,
-> name char(10),
-> age tinyint(2) unsigned
-> );
mysql> desc t23;
例子1和例子2 格式不同。
在已有的表中设置primary key 字段
----alter table 表名 add primary key (字段名)
例子：mysql> desc t3；
mysql> alter table t3 add primary key(stu_id);
mysql> desc t3;
mysql> insert into t3 values(null,“nas1801”,“lxd”,18,“boy”); 提示失败，不能为空；
mysql> insert into t3 values(“180101”,“nas1801”,“lxd”,18,“boy”); 添加成功
mysql> insert into t3 values(“nsd180101”,“nas1801”,“gxd”,18,“boy”); 提示失败，nsd180101已经设为主键，不能重复设置。
select * from t3;

删除表中的primary key 字段 (移除主键前，如果有自增属性，必须先去掉 ）
----alter table 表名 drop primary key;
例子： mysql> alter table t3 drop stu_id; (删除之后，赋值仍然不可以为空，即自增属性未变。)
创建复合主键的使用：多个字段一起做主键，插入记录时，只要做主键字段的值不同时重复，就可以插入记录。
例子：mysql> create table t24(
-> clientip char(15),
-> port smallint(2),
-> status enum(“allow”,“deny”),
-> primary key(clientip,port) ----复合主键只能单独一起设置！！
-> );
mysql> desc t24;
mysql> insert into t24 values(“1.1.1.1”,22,“allow”); 成功
mysql> insert into t24 values(“1.1.1.1”,20,“allow”); 成功
mysql> insert into t24 values(“1.1.1.1”,20,“allow”); 失败
mysql> insert into t24 values(“2.1.1.1”,20,“allow”); 成功
主键primary key 通常和auto_increment连用：让字段的值自动增长 i++（即字段类型时数值类型）
mysql> alter table t22 add id int(2) primary key auto_increment first;
foreign key 外键 (作用：限制如何给字段赋值的)
外键：给当前表中字段赋值时，值只能在其他表的指定字段值的范围内选择。
使用外键的条件：
—表的存储引擎必须是innodb；
—字段类型要一致；
—被参照字段必须要是索引类型的一种(primary key)
外键的基本用法：create table 表（字段名列表， foreign key (字段名) reference 表名（字段名） on update cascade）
----foreign key (表A的字段名)
---- reference 表B（字段名）
on update cascade 同步更新；
on delete cascade 同步删除；（真实环境中，不添加该项，以防意外的把有依附关系的表删除！！）
删除外键字段
----alter table 表名 drop foreign key 约束名(即外键名)；
mysql> alter table bjb drop foreign key bjb_ibfk_1; 删除外键
例子：mysql> create table jfb(
// -> jfb_id int(2) primary key auto_increment,
-> name char(15),
-> pay float(7,2)
-> )engine=innodb;
mysql> insert into jfb(name,pay) values(“bob”,2000);
mysql> insert into jfb(name,pay)values(“tom”,20010);
mysql> select * from jfb;
mysql> create table bjb(
-> bjb_id int(2),
-> name char(15),
-> foreign key(bjb_id) references jfb(jfb_id) on update cascade on delete cascade
-> )engine=innodb;
mysql> insert into bjb values(1,“bob”);
mysql> insert into bjb values(2,“tom”);
mysql> update jfb set jfb_id=9 where name=“bob”;
mysql> delete from jfb where jfb_id=2;
mysql> select * from jfb;
mysql> select * from bjb;
mysql> show create table bjb; 查看建表过程，查看外键名；
mysql> alter table bjb drop foreign key bjb_ibfk_1; 删除外键
#二 、MySQL工作原理
##2.1 MySQL体系结构
##2.2 MySQL存储引擎
2.2.1 存储引擎介绍：是MySQL数据库软件自带的功能程序，每种存储引擎的功能和数据存储方式也不同。
存储引擎就处理表的处理器，
2.2.2 查看数据库服务支持的存储引擎有哪些？
mysql> show engines; innodb default
2.2.3 查看已有的表使用的存储引擎
show create table 表名；
2.2.4 修改数据库服务默认使用的存储引擎
vim /etc/my.cnf
[mysqld]
default-storage-engine=myisam
–# systemctl stop mysqld
–#systemctl start mysqld
–# mysql> show engines; 查看默认存储引擎变化；
2.2.5 修改表使用的存储引擎，或建表时指定表使用的存储引擎
alter table 表 engine=存储引擎名；
mysql> create table 表(字段列表) engine=存储引擎名；
存储引擎的配置
修改表的存储引擎
建表时手动指定：
—未指定时，使用默认存储引擎；
—show create table 表名\G ;可确认
设置默认存储引擎
修改/etc/my.cnf配置文件
—default-storage-engine=存储引擎名称
mysql> show variables like ‘default_storage_engine’; 查看系统变量default_storage_engine的值；
列出可用的存储引擎类型
—show engines;或 show engines\g

2.2.6 常用存储引擎的特点
innodb 【特点：支持事务、事务回滚、行级锁 、外键
存储方式：一个表对应2个存储文件
表名.frm (表结构) 表名.ibd（数据和索引）】
MySQL数据库服务使用事务日志文件记录，对innodb存储引擎执行过的sql操作。
—cd /var/lib/mysql
ib_logfile0 、 ib_logfile1 （ ---- >记录SQL命令）
-> insert into t1 values(8888)
ibdata1---->数据源（sql命令执行后产生的数据信息）
myisam 【 特点：不支持事务、事务回滚、外键，支持表级锁
存储方式：一个表对应3个存储文件
表名.frm (表结构) 表名.MYD（数据） 表名.MYI（索引）】
事务：对数据库服务的访问过程（连接数据库服务器 操作数据 断开连接 ）
事务（ACID）:Atomic :原子性 ； Consistency :一致性 ；Isolation:隔离性 ；Durability:持久性；
原子性：事务的整个操作时一个整体，不可分割，要么全部成功要么全部失败；
一致性：事务操作的前后，表中的记录没有变化；
隔离性：事务操作是相互隔离不受影响的；
持久性：数据一旦提交，不可改变。
show variables like “autocommit”; 查看提交状态；
set autocommit=off; 关闭自动提交
rollback; 数据回滚
commit; 提交数据
事务回滚：在事务执行过程中，任何一步操作失败都会恢复之前的所有操作。
MySQL数据库服务使用事务日志文件记录，对innodb存储引擎执行过的sql操作。
锁的作用：解决对表的并发访问冲突问题
锁粒度： 表级锁、行级锁 、页级锁
表级锁（myisam）:给整张表加锁；
行级锁（innodb）:只给表中当前被操作行加锁
页级锁：对整个页面（MySQL管理数据的基本存储单位）进行加锁。
锁类型：读锁、写锁；
读锁：（共享锁）：支持并发读
写锁：（互斥锁 ）：是独占锁，上锁期间其他线程不能读表或者写表
查看当前的锁状态：检查Table_lock开头的变量，%作为通配符
–> show status like ‘Table_lock%’;
2.2.7 建表时如何决定表使用哪种存储引擎
执行写操作多的表适合使用innodb存储引擎，可以并发访问；
执行读操作多的表适合使用myisam存储引擎，节省系统资源；
2.2.8 工作中怎么决定数据库服务器使用哪种引擎
##2.3 数据导入导出
2.3.1 数据导入的命令格式及数据导入时的注意事项
查看导入文件内容时，默认在系统的什么目录下搜索文件？
show variables like “secure_file_priv”; （/var/lib/mysql-files/）
显示 变量 像 “secure_file_priv” ；
导入数据的命令格式：–LOAD DATA INFILE “目录名/文件名”
INTO TABLE 表名
FILEDS TERMINATED BY “列分隔符”
LINES TERMINATED BY “\n”；
注意事项：–字段分隔符要与文件内 的一致；
—指定导入文件的绝对路径
—导入数据的表字段类型要与文件字段匹配
– - 禁用SElinux保护机制
数据导入：把系统文件的内容存储到数据库服务器的表里。
例子：把系统已有用户的信息保存到db3库下的usertab表里。
mysql> create table db3.usertab(
-> username char(50),
-> password char(1),
-> uid int(2),
-> gid int(2),
-> comment char(100),
-> homedir char(100),
-> shell char(50),
-> index(username)
-> );
mysql> desc usertab;
mysql> select * from db3.usertab;
#cp /etc/passwd /var/lib/mysql-files/
mysql> load data infile “/var/lib/mysql-files/passwd” into table db3.usertab fields terminated by “:” lines terminated by “\n”;
（这里会出现一个问题，提示The MySQL server is running with the–secure-file-priv option so it cannot execute this statement，，原因及解决办法：一些版本的mysql对通过文件导入导出做了限制，默认不允许，先查看mysql> show variables like “secure_file_priv”;如果value的值为null则为禁止，若有文件夹目录，则只允许该目录下文件，如果为空，则不限制目录。
需要修改配置文件# vim /etc/my.cnf 添加(secure_file_priv= )然后保存重启服务，即可。）
–mysql> load data infile “/var/lib/mysql-files/passwd” into table db3.usertab fields terminated by “:” lines terminated by “\n”;
–mysql> alter table db3.usertab add id int(2) primary key auto_increment first; 先导入数据再插入id行做主键
mysql> select * from usertab ;
mysql> select * from usertab where id=20;
修改导入文件内容时，默认再系统的什么目录下搜索文件？
#mkdir -p /mysqldata
#chown mysql /mysqldata
#setenforce 0 确保selinux 关闭
#vim /etc/my.cnf
[mysqld]
secure_file_priv="/mysqldata" （这个目录自己设定）
#systemctl restart mysqld
mysql> show variables like “secure_file_priv”;
注意：想要再sql命令下做系统命令可以加一个 system;(比如：mysql > system cp /etc/passwd /var/lib/mysqldata )

2.3.2 数据导出的命令格式及数据导出时的注意事项
把表记录存储到系统文件中
基本用法：---- SQL查询： into outfile “目录名/文件名”
fields terminated by “分隔符” （指定列的间隔符）
lines terminated by “\n”； （指定行的间隔符）
注意事项： -导出的内容由SQL查询语句决定；
— 导出 的表中的记录，不包括字段名；
—禁用SElinux ；
mysql> select username,uid from db3.usertab into outfile “/mysqldata/user1.txt”;
mysql> select * from db3.usertab into outfile “/mysqldata/user2.txt”;
mysql> select username,uid from db3.usertab into outfile “/mysqldata/user3.txt” fields terminated by “##”;
# cat /mysqldata/user1.txt
# cat /mysqldata/user2.txt
# cat /mysqldata/user3.txt
##2.4 管理表记录
插入记录： insert into (值要与字段类型和约束条件匹配，字段列表与字段列表括号间要有 ，号)
插入1条记录给所有字段赋值；
insert into 库.表 values(字段值列表)； （注意：指定的id号要大于原本存在的，因为id时主键）
mysql> insert into usertab values (48 ,“yaya”,“x”,1001,1001,"","/home/yaya","/bin/bash");
插入N条记录给所有字段赋值；
insert into 库.表 values(字段值列表) ，（字段值列表）；
mysql> insert into usertab values (50 ,“yaya2”,“x”,1002,1002,"","/home/yaya2","/sbin/nologin")，
(51 ,“yaya3”,“x”,1003,1003,"","/home/yaya3","/sbin/nologin")
插入1条记录给指定的字段赋值；
insert into 库.表 （字段值列表） values(字段值列表)；
mysql> insert into usertab(username,homedir,shell) values(“lucy”,"/home/lucy","/bin/bash");
插入N条记录给指定的字段赋值；
insert into 库.表 （字段值列表） values(字段值列表)，（字段值列表）;
mysql> insert into usertab(username,homedir,shell) values(“lucy”,"/home/lucy","/bin/bash"), (“tom”,"/home/tom","/bin/bash") , (“lilei”,"/home/lilei","/bin/bash");
mysql> desc usertab;
mysql> select * from usertab;
查看记录：
select * from 库.表 ; （查看表中所有行的所有字段的值）
select 字段名1 ，字段名2，字段名n from 库 . 表 ； （查看表中所有行的指定字段的值）
select 字段名1 ，字段名2，字段名n from 库.表 where 匹配条件； （查看指定行的指定字段的值）
select 字段名列表 from 库.表 where 匹配条件；
例子： select * from usertab;
mysql> select * from usertab where id=1;
mysql> select id ,username ,password from db3.usertab;
mysql> select username ,uid,shell from usertab where id=1;
修改记录字段的值：
修改与条件匹配的记录指定字段的值
update 库.表 set 字段名=值 ，字段名=值 where 匹配条件；
（例子：mysql> update usertab set password=“x” where id=1;
mysql> select password from usertab where id=1;）
修改所有记录指定字段的值（批量修改）
update 库.表 set 字段名=值 ，字段名=值； （ mysql> update usertab set password=“A”;）
删除记录：
删除表中的所有的行
delete from 库.表；
仅删除与条件匹配的记录：
delete from 库.表 where 匹配条件；
mysql> delete from usertab where uid=3;
##2.5匹配条件（查看、修改、删除记录时可以加条件） where 匹配条件
数值比较 ： 字段名 符号（= 、 ！=、<、<=、>、>=） 数字
例子：mysql> select id,uid,username from usertab where uid=2;
mysql> select * from usertab where id<=10;
字符比较 ： 字段名 符号（= 、 ！=） "字符串"
例子：mysql> select username from usertab where username=“apache”;
mysql> select username,shell from usertab where shell="/bin/bash";
mysql> select username,shell from usertab where shell!="/bin/bash";
范围内比较
字段名 between 数字1 and 数字2 在…之间… （数值类型比较）
字段名 In (值列表) 在…里 （数值或者字符类型都可以）
字段名 not in（值列表） 不在…里 （数值或者字符类型都可以）
例子：mysql> select username from usertab where uid between 100 and 150;
mysql> select username ,uid from usertab where uid in(1,7,30,2,5,3,67);
mysql> select username ,uid from usertab where username in(“root”,“rsync”,“mysql”);
mysql> select username from usertab where username not in(“root”,“bin”);
逻辑比较（有多个查询条件）
与 and ：多个条件同时成立 才匹配；
或 or :多个条件，某一个条件成立，就匹配；
非 ！ 或 not ：取反；
例子： mysql> select username,uid from usertab where username=“root” and uid=0;
mysql> select username,uid from usertab where username=“root” or uid=1;
mysql> select username,uid from usertab where username=“root” or uid=1 or shell="/bin/bash";
匹配空 ： 字段名 is null
匹配非空 ：字段名 is not null
例子：mysql> select username ,uid,gid from usertab where uid is null and gid is null;
mysql> update usertab set uid=3000 ,gid=3000 where username=“lucy”;
mysql> select username ,uid,gid from usertab where uid is null and gid is null;
mysql> select id from usertab where username=“yaya” and uid is not null;
mysql> select username from usertab where id=2;
mysql> update usertab set username=null where id=2;
mysql> select username from usertab where id=2;
正则匹配：
字段名 regexp ‘正则表达式’； （^ $ . * [ ] ）
例子 ：mysql> select username from usertab where username regexp ‘[0-9]KaTeX parse error: Double superscript at position 78: …name regexp ' ^̲[0-9] '; …’; （匹配两位字符的)
mysql> select username from usertab where username regexp ‘…’; （匹配大于两位字符的)
模糊匹配：
– 字段名 like ‘表达式’；
表达式 ：%（表示零个或者多个字符）；
_ (表示任意一个字符)
例子 ：mysql> select username from usertab where username like ‘_’; （匹配username是四个字符的记录）
mysql> select username from usertab where username like ‘a%’;
mysql> insert into usertab(username) values(“a”);
mysql> select username from usertab where username like ‘a%’;
mysql> select username from usertab where username like ‘a_t’;
mysql> select username from usertab where username like '%’; （匹配username 字符数大于2个以上的）
四则运算 （字段类型必须为数值类型 ：整型或者浮点型都可以 + - * / %）
例子： mysql> select id,username,uid from usertab where id <=10;
mysql> update usertab set uid=uid+1 where id <=10;
mysql> select id,username,uid from usertab where id <=10;
ysql> select username ,uid,gid from usertab where username=“mysql”;
ysql> select username ,uid,gid,uid+gid as zh from usertab where username=“mysql”;
ysql> select username ,uid,gid,uid+gid as zh ,(uid+gid)/2 as pjz from usertab where username=“mysql”;
ysql> alter table usertab add age tinyint(2) unsigned default 21;
mysql> select username ,age from usertab;
mysql> select username ,age,2019-age s_year from usertab where username=“root”;
聚集函数（对字段的值做统计，字段的类型要求是数值类型）
count（字段名）:统计字段值的个数；
sum（字段名） ：求和
max（字段名） ：输出字段值的最大值；
min（字段名）：输出字段值的最小值；
avg（字段名） ：输出字段值的平均值；
例子：mysql> select max(uid) from usertab;
mysql> select min(uid) from usertab;
mysql> select sum(uid) from usertab;
mysql> select avg(uid) from usertab;
mysql> select count(id) from usertab;
mysql> select count(username) from usertab where shell="/bin/bash";
查询不显示字段重复值：distinct 字段名
例子： mysql> select shell from usertab;
mysql> select distinct shell from usertab;
mysql> select distinct shell from usertab where uid>0 and uid<=100;
查询分组： sql 查询 group by 字段名；
mysql> select distinct shell from usertab where uid>0 and uid<=100 group by shell;
查询排序：（按照数值类型的字段排序，默认是按升序排列）
sql 查询 order by 字段名 asc ; 升序
sql 查询 order by 字段名 desc ; 降序
例子：mysql> select username ,uid from usertab where uid >10 and uid<=100；
mysql> select username ,uid from usertab where uid >10 and uid<=100 order by uid asc; 升序排列
mysql> select username ,uid from usertab where uid >10 and uid<=100 order by uid desc; 降序排列
限制查询显示行数（默认显示所有查询的记录）
sql 查询 Limit 数字； 显示查询结果的前几行
sql 查询 limit 数字1，数字2； 显示查询结果指定范围的行
例子： mysql> select username ,uid from usertab where uid >10 and uid<=100 order by uid desc limit 2;
mysql> select username ,uid from usertab where uid >10 and uid<=100 order by uid desc limit 2 ,5;
mysql> select username,id from usertab limit 2,4; （从第三行开始显示，一共显示4行）（注意：行号是从0开始的）
mysql> select username,uid from usertab where uid>=10 and uid <=100 order by uid desc limit 2,6;