2021-03-01

多表连接查询：
内连接：根据表中的共同字段进行匹配
外连接：左外连接、右外链接
语法：
select 字段 from 表1 inner join 表2 on 表1.字段=表2.字段
创建stu_info和stu_chj表
mysql> create table stu_info(xh int primary key,xm varchar(30) not null,sex enum(‘M’,‘W’)) engine=innodb default charset=utf8;
mysql> create table stu_chj(xh int,yw float(5,2),shx float(5,2));

用内连接查询实现查询每个学生的学号、姓名以及各科成绩
mysql> select stu_info.xh,xm,yw,shx from stu_info inner join stu_chj on stu_info.xh=stu_chj.xh;

例2:把书的名字，价格和所属类型显示出来
mysql> select a.bname,a.price,b.btypename from books a inner join category b on a.btypeid=b.btypeid;
实际使用中inner可省略掉，使用where子句代替。
mysql> select a.bname,a.price,b.btypename from books a, category b where a.btypeid=b.btypeid;
左连接：
select 字段 from a表 left join b表 on 连接条件
a表是主表，指定的字段内容都显示。
b表从表，没有的内容显示null
mysql> select a.bname,a.price,b.btypename from books a left join category b on a.btypeid=b.btypeid;
右连接：
select 字段 from a表 right join b表 on 连接条件
a表是从表，
b表主表，都显示。
mysql> select a.bname,b.* from books a right join category b on a.btypeid=b.btypeid;
算数运算函数
Sum()求和
显示所有图书单价的总和
mysql> select sum(price) from books;
或
select sum(price) as 图书总价 from books;
avg()平均值：
select avg(price) from books where bId<=3;
max() 最大值
mysql> select bName,price from books where price=(select max(price) from books);
min()最小值
mysql> select bName,price from books where price=(select min(price) from books);
count()统计记录数:
统计价格大于40的书籍数量
mysql> select count(*) from books where price>40;
Count（）中还可以增加你需要的内容，比如增加distinct来配合使用
mysql> select count(distinct price) from books where price>40; #指定价格

substr(string ,start,len) 截取：从start开始，截取len长.start 从1开始算起。
Substr截取字符串
mysql> select substr(bTypeName,1,7) from category where bTypeId=10;

#从第二个字符串截取
mysql> select substr(bTypeName,2,7) from category where bTypeId=10;

concat(str1,str2,str3…) 拼接。 把多个字段拼成一个字段输出
mysql> select concat(bName,"-----",publishing) from books;

大小写转换
upper()大写 : 转为大写输出
mysql> select upper(bname) from books where bId=9;
lower()小写：转为小写输出
select lower(bName) from books where bId=10;
MySQL字段约束-索引-外键
字段修饰符 (约束)
NULL和NOT NULL修饰符
创建带修饰符的表，测试ull和not null字段区别
create table worker(id int not null,name varchar(8) not null,pass varchar(20) not null);
insert into worker values(1,‘HA’,‘123456’); #插入数据
insert into worker values(1,‘LB’,null); #插入null值，报错
insert into worker values(2,‘HPC’,’’); 可以插入“空值”
字段类型是not null，为什么可以插入空值？
空值是不占用空间的
为什么not null的效率比null高？
NULL 其实并不是空值，而是要占用空间
创建表，插入数据
mysql> create table test(col1 varchar(10) not null, col2 varchar(10) null)ENGINE=MyISAM;
mysql> insert into test values(’’,null);
mysql> insert into test values(‘1’,‘2’);
mysql> insert into test values(’’,‘1’);
测试
mysql> select * from test where col1 is not null;
mysql> select * from test where col1 <>’’;
DEFAULT 设定字段的默认值
为字段指定默认的值
mysql> create table test2(name varchar(8) not null,dept varchar(25) default ‘SOS’);
验证： select * from test2;
注意：
1、如果字段没有设定default ,mysql依据这个字段是null还是not null，如果为可以为null，则为null。如果不可以为null，报错。
AUTO_INCREMENT字段约束
自动增长
只能修饰 int字段。
创建
mysql> create table items ( id int not null auto_increment primary key , label varchar(20) not null);
mysql> insert into items (label) values (‘aaba’);
mysql> insert into items values (9,‘aaba’);
再插入一条id将为多少
mysql> insert into items (label) values (‘abc’);
验证
mysql> select * from items;
再次插入一条数据，删除，再次添加，查看它的id
mysql> insert into items (label) values (‘abcs’);
mysql> select * from items;

验证：
mysql> delete from items where label=‘abcs’;
mysql> insert into items (label) values (‘abcsw’);
mysql> select * from items;
查看id值为12，不是11，说明主键约束唯一
清除表中的记录
DELETE 不加WHERE条件，清空所有表记录。但是DELETE不会清零AUTO_INCREMENT 值
delete from items;
验证
mysql> insert into items (label) values (“aaaa”);
mysql> select * from items;
truncate
作用： 删除表的所有记录，并清零auto_increment 值。新插入的记录从1开始。
语法： truncate table name；
truncate table items;
验证：
insert into items values(null,‘abv’);
select * from items;

索引
优点：加快搜索速度，减少查询时间
索引类型
1、普通索引：
不具备唯一性
方法一：创建表时添加索引
create table 表名（
列定义…… ,
index 索引名称 （字段）,
index 索引名称 （字段）
）;
create table demo( id int(4), name varchar(20), pwd varchar(20), index(pwd) );
注意：index和 key 是相同的
查看索引
desc demo;
show index from demo;
方法二： 当表创建完成后，使用alter为表添加索引：
alter table 表名 add index 索引名称 （字段1，字段2…）;
例：先删除 alter table demo drop key pwd;
添加索引 alter table demo add key(pwd);
通过explain模拟执行sql查询语句：
explain select bname,btypeid,publishing,price,author from b
ooks where bname=‘Linux傻瓜书’\G
在bname列上创建索引
alter table books add index index_bname(bname);
2、唯一性索引
唯一性允许有NULL值
方法一：创建表时加唯一索引
create table 表名(
列定义,
unique key 索引名1（字段）,
unique key 索引名2（字段）
);
例：create table demo3(id int(4) auto_increment primary key,uName varchar(20),uPwd varchar(20),unique index(uName));
方法二：修改表时加唯一索引
alter table demo3 drop key uName; #删除唯一索引
alter table demo3 add unique(uName); #添加唯一索引
3、主键索引
主键索引是一种特殊的唯一索引， 但不允许有空值。
例：
方法一：创建表创建主键索引
create table demo5(id int(4) not null auto_increment primar
y key,name varchar(20) default null);
方法二：创建表后添加
alter table demo5 drop primary key;删除遇到报错是auto_increment的原因。先取消自增长
alter table demo5 change id id int(4) not null; #取消自增长
alter table demo5 drop primary key; #删除
alter table demo5 change id id int(4) not null primary key auto_increment; 添加主键索引
4、复合索引
两个或更多个列上的索引被称作复合索引
例：
create table fire(host varchar(15) not null,port smallint(4) not null,access enum(‘deny’,‘allow’)not null,primary key(host,port));
测试：
insert into firewall values(‘10.96.52.46’,22,‘deny’);
insert into firewall values(‘10.96.52.46’,21,‘allow’);
insert into firewall values(‘10.96.52.46’,21,‘allow’);
为了进一步提升MySQL的效率，就要考虑建立组合（复合）索引
create table mytable(id int not null,username varchar(16)not null,city varchar(50)not null,age int not null);创建表
alter table mytable add index username_city_age(username,ci
ty,age);将 username, city, age建到一个索引里
外键约束
foreign key就是表与表之间的某种约定的关系，由于这种关系的存在，我们能够让表与表之间的数据，更加的完整，关连性更强。
语法：
create table 表名(
…,
[CONSTRAINT [外键名]] FOREIGN KEY [字段名,字段名2……]
REFERENCES 主表名 // References [ˈrefrənsɪz]
[ON DELETE CASCADE |RESTRICT]
[ON UPDATE CASCADE|RESTRICT]
);
RESTRICT: 拒绝对父表的删除或更新操作。
CASCADE: 从父表删除或更新且自动删除或更新子表中匹配的行。ON DELETE CASCADE和ON UPDATE CASCADE都可用
注意：on update cascade是级联更新的意思，on delete cascade是级联删除的意思，意思就是说当你更新或删除主键表，那外键表也会跟随一起更新或删除。
例：
创建主表，并将sid设为主键
create table stu(sid int primary key,name varchar(50)not null);
创建从表，并将sid设为外键
CREATE TABLE score1(score DOUBLE, sid INT, CONSTRAINT fk_stu_score1_sid FOREIGN KEY(sid) REFERENCES stu(sid) );
注：创建成功，必须满足以下4个条件：
1、确保参照的表和字段存在。
2、组成外键的字段被索引。
3、必须使用ENGINE指定存储引擎为：innodb.
4、外键字段和关联字段，数据类型必须一致。
方法一：
我们创建一个数据库，包含用户信息表和订单表
create database market;
create table user(id int(11)not null auto_increment,name
varchar(50) not null default ‘’,sex int(1) not null default ‘0’,primary key(id)) engine=innodb;
创建时，如果表名是sql关键字，使用时，需要使用反引号``

create table order(o_id int(11) auto_increment, u_id int(11) default ‘0’, username varchar(50), money int(11), primary key(o_id), index(u_id), foreign key order_f_key(u_id) references user(id) on delete cascade on update cascade) ENGINE=innodb;
注：
1、on delete cascade on update cascade 添加级联删除和更新：
测试：
insert into user(name,sex)values(‘HA’,1),(‘LB’,2),(‘HPC’,1);
insert into order(u_id,username,money) values(1,‘HA’,234)
,(2,‘LB’,146),(3,‘HPC’,256);
select * from order;
select * from user;
测试级联删除：
delete from user where id=1; 删除user表中id为1的数据
select * from order;
测试级联更新：
update user set id=6 where id=2;
select * from user;
测试数据完整性

插入一个不存在的用户
insert into order(u_id,username,money) values(7,‘Find’,346);报错
在order里面插入一条数据u_id为7用户，在user表里面根本没有，所以插入不进去
解决： insert into user values(7,‘Find’,100); #先在user表插入7用户再插入
insert into order(u_id,username,money) values(7,‘Find’,346);
方法二：
通过ALTER TABLE 创建外键和级联更新，级联删除
语法：
alter table 数据表名称 add
[constraint [约束名称] ] foreign key (外键字段,…) references 数据表(参照字段,…)
[on update cascade]
[on delete cascade]
)
创建orde1表
mysql> create table order1(o_id int(11) auto_increment, u_id int(11) default ‘0’, username varchar(50), money int(11), primary key(o_id), index(u_id)) ENGINE=innodb;
创建外键约束
mysql> alter table order1 add foreign key(u_id) references user(id) on delete cascade on update cascade, ENGINE =innodb;
查看创建表执行命令
mysql> show create table order1;
删除外键：
语法
alter table 数据表名称 drop foreign key 约束（外键）名称
例：alter table order1 drop foreign key order1_ibfk_1;
show create table order1;