MYSQL 基本语法

create table t4(
dt datetime,
y year,
d date,
t time,
ts timestamp
);

create table t6(
c1 char,
v1 varchar(1),
c2 char(8),
v2 varchar(8)
);

create tables t8(
id int,
name char(18),
gender enum('male','female')
);

create table t9(
id int,
name char(18),
hobby set('抽烟','喝酒','烫头','洗脚','按摩')
);
insert into t9 values(1,'alex','烫头,喝酒');
insert into t9 values(2,'大壮','洗脚,按摩,打游戏');

数据的

增 #insert into 表 values(值)

#insert into 表(字段1,字段2) values(值1,值2)
#insert into 表(字段1,字段2) select 字段1,字段2 from 表2 

删 #delete from 表 where 条件;

#truncate table 表名;

改 #update 表 set 字段=值 where 条件;

查 #select 字段 from 表

      #where 条件
      #group by 分组
      #having 过滤条件 
      #order by 排序
      #limit m,n 取从m+1开始的前n条
 #扩展

完成性约束###

#无符号 int unsigned
#不能为空 not null
#默认值 default
#唯一约束unique
	#联合唯一 unique(字段1，字段2)	
#主键 primary key
	#联合主键primary key(字段1，字段2）
#自增 auto_increment,

set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

create table t1(
id int unsigned not null,
name char(18) not null
);

联合唯一 unique

create table t2(
id1 int unique,
id2 int
);

create table t3(
id int,
server_name char(12),
ip char(15),
port int(5),
unique(ip,port) #禁止插入都重复的ip+port
);

insert into t3 values(1,'ftp','192.168.14.2','3306');

非空 + 唯一约束

第一个被定义为非空+唯一的那一列会成为这张表的primary key (主键)

一张表只能定义一个主键

create table t4(
id int not null unique,
username char(18) not null unique
);
desc t4;

create table t5(
username char(18) not null unique,
id int primary key
);

联合主键

create table t6(
id int,
server_name char(12),
ip char(15),
port char(5),
primary key(ip,port)
);

create table t7(
id int,
server_name char(12),
ip char(15) not null,
port char(5) not null,
primary key(ip,port)
);

create table t8(
id int,
server_name char(12),
ip char(15) not null,
port char(5) not null,
unique(ip,port)
);

自增 auto_increment

create table t10(
id int unique auto_increment,
name char(12)
);

create table t11(
id int primary key auto_increment,
name char(12)
);
insert into t11(name) values('大庄');

外键 foreign key

关联表 foreign key(id) references 表名(id)

级联更新 on update cascade

级联删除 on delete cascade

学生表

create table student(
id int primary key auto_increment,
name char(12) not null,
gender enum('male','female') default 'male',
class_id int ,
foreign key(class_id) references class(cid)
);

班级表

create table class(
cid int primary key auto_increment,
cname char(12) not null,
startd date
);
insert into class values(1,'py1','2019-3-6');
insert into student values(1,'male','流向',1);
update class set id=2 where id=1; #无法修改，因为student表中1已经有人了

create table student2(
id int primary key auto_increment,
name char(12) not null,
gender enum('male','female') default 'male',
class_id int,
foreign key (class_id) references class(cid) on update cascade on delete cascade #提联更新
);

修改表

修改表名 alter table t1 rename t20;

增加字段 alter table t1 add birthday date not null;

删除字段 alter table t1 drop birthday;

修改字段 alter table t1 modify name char(15) not null;

 alter table t1 change name cname char(14) not null;

alter处理null和unique约束

去掉null约束 alter table t1 modify name char(18) null;

添加null约束 alter table t1 modify name char(18) not null;

去掉unique约束 alter table t1 drop index id;

添加unique约束 alter table t1 modify id int unique;

删除主键 alter table table_test drop primary key;

增加主键 alter table table_rest add primary key(id); #增加主键前需删除重复的id

添加外键 alter table book add constraint fk_id foreign key(press_id) references press(id);

删除外键 alter table book drop foreign key fk_id;

tables associate project

create table class (
cid int unique auto_increment,
caption char(8) unique
);
create table student (
sid int unique auto_increment,
sname char(4),
gender enum('男','女'),
class_id int ,
foreign key(class_id) references class(cid)
);
create table teacher(
tid int unique auto_increment,
tname char(8)
);
create table course(
cid int unique auto_increment,
cname char(4),
teacher_id int,
foreign key(teacher_id) references teacher(tid)
);
create table score (
sid int unique auto_increment,
student_id int,
corse_id int,
number int,
foreign key(student_id) references student(sid),
foreign key(corse_id) references course(cid)
);
insert into class(caption) values('三年二班');
insert into class(caption) values('一年三班');
insert into class(caption) values('三年一班');
insert into student(sname,gender,class_id) values('张里','男',1),('刘金','女',1),('李玉','女',2);
insert into teacher(tname) values('刘是'),('黎明'),('四季');
insert into course(cname,teacher_id) values('生物',1),('体育',1),('物理',2);
insert into score(student_id,corse_id,number) values(1,1,60),(1,2,58),(2,2,100);

day39:

删

delete from 表; 自增数据不会回到初始(是不会清空自增字段offset(偏移量)值
truncate table 表; 会清空表和自增字段的偏移量
delete from 表 where 条件 ;

改

update 表

create table t1(
id int primary key auto_increment,
username char(12) not null,
sex enum('male','female') default 'male',
hobby set('上课','写作业','考试') not null
);
insert into t1 values(1,'李级','male','上课,写作业'),(2,'刘彪','male','考试'),(3,'杨党','female','上课');
insert into t2(id,name) select id,username from t1;
update t1 set id=1,hobby='考试,写作业' where id=2;

创建一个公司成员表

create table company(
id int primary key auto_increment,
name char(12) not null,
age int,
post char(18),
salary int,
QQ char(12)
);
insert into company(name,age,post,salary,QQ) values('小明',19,'店长',38000,'123585'),('刘兴',21,'厨师长',20000,'154854'),('姬性差',25,'会计',18000,'456788'),('李丁',17,'洗碗工',8000,'455885'),('李绘',17,'洗碗工',8000,'355785'),('吴兴山',27,'厨师',16000,'124578'),('吴旺山',27,'厨师',16000,'154568'),('赵晶',17,'副店长',25000,'123355'),('刘民',19,'服务员',9000,'125225'),('胡老千',26,'服务员',9000,'1244565'),('林北',19,'服务员',9000,'1244547');

select 查询

select id as num,username as name from t1;

函数

concat 连接字符串(任一为空结果为空)

select concat('aaa','bbb','ccc');
select concat(id,':',username) from t1;
select concat_ws('|','alex','3714','84'); #concat_ws加上拼接符|

select (
case
when username = 'all' then username
when username = 'alll' then concat(username,'sb')
else concat(username,'lb')
end)
as name
from t1;

使用逻辑运算符

#case when语句 相当于 if条件判断句

where 筛选所有符合条件的行

#比较运算符
     #> < >= <= != =
#范围
     # between 100 and 200     100~200
     # in(100,200)                      100或200
#模糊匹配
   #like
     #like 'j%'      以j开头 用%表示任意长度的任意内容
     #like '成_'     _表示长度为一个的任意字符
   #regexp
     #   '^a'
     #   'g$'
#逻辑运算
     #not   and   or
判断是否为空用is

分组 group by

聚合函数

#count  求个数   
#max    求最大值
#min    求最小值
#sum   求和
#avg    求平均  select post,avg(salary) from employee group by post;

select post,name from company group by post;

select post,group_concat(name) from company group by post;

select post,count(post) from company group by post;

select sex,count(sex) from company group by sex;

select post,avg(salary) from company group by post;

select post,max(salary) from company group by post;

select post,min(salary) from company group by post;

select sex,avg(salary) from company group by sex;

having 过滤语句

#在having语句中可以使用聚合函数，在where中不行
#适合去筛选符合条件的某一组数据，而不是某一行数据
#先分组再过滤：求平均薪资大鱼xx的部门，求人数大于xx的性别，求大于xx人的年龄段
 #查询各岗位内包含的员工个数大于2的岗位名，岗位内包含员工名字，个数
 select post,group_concat(name),count(id) from company group by post having count(id) >= 2;

having在order by 和limit之前

order by 默认升序排列

select * from company order by salary;
select * from company order by salary asc;
select * from company order by salary desc;

limit m,n m默认为0

select * from company order by salary desc limit 1;
select * from company order by salary desc limit 3; 取三条
select * from company order by salary desc limit 2,1; 从第三条开始取一条

limit n offset n; select * from company order by salary desc limit 3 offset 1;

select name,sex,age,QQ from company where sex='female' order by salary desc limit 1;

连表查询

create table department(id int,name varchar(20));
create table employee(id int primary key auto_increment,name varchar(20),sex enum('male','female') not null default 'male',age int,dep_id int);
insert into department values(200,'技术'),(201,'人力资源'),(202,'销售'),(203,'运营');
insert into employee(name,sex,age,dep_id) values('egon','male',18,200),('alex','female',48,201),('wupeiqi','male',38,201),('yuanhao','female',28,202),('liwenzhou','female',18,200),('jingliyang','female',18,204);
select * from department,employee;
select * from department,employee where department.id=dep_id;

所谓连表

 #总是在连接的时候创建一张大表,里面存放的是两张表的笛卡尔积
 #再根据条件进行筛选就可以了

表与表之间的连接方式

 #select * from 表1,表2 where 条件;

内连接 inner join ... on ... 只显示两者均满足要求的数据

 #select * from 表1 inner join 表2 on 条件;
 #select * from department inner join employee on department.id = employee.dep_id;
 #select * from department as t1 inner join employee as t2 on t1.id = t2.dep_id;
 select * from department as t1 inner join employee as t2 on t1.id = t2.dep_id where t1.name='人力资源';

外连接 可选择连接的一方全部的数据，左连接显示全部前者数据，右连接显示全部右表数据

 #左外连接 left join ... on ...
#select * from 表1 left join 表2 on 条件
#select * from department as t1 left join employee as t2 on t1.id = t2.dep_id;
 #右外连接 right join ... on ...
#select * from 表1 right join 表2 on 条件
#select * from department as t1 right join employee as t2 on t1.id = t2.dep_id;
 #全外连接 full join
select * from department as t1 left join employee as t2 on t1.id = t2.dep_id
            union
select * from department as t1 right join employee as t2 on t1.id = t2.dep_id;
#select * from department left join employee on department.id=employee.dep_id
#union
#select * from department right join employee on department.id = employee.dep_id;
select * from department as t1 left join employee as t2 on t1.id=t2.dep_id where t1.id<202;
select * from department as t1 left join employee as t2 on t1.id=t2.dep_id order by age;
select t1.name,count(t1.id),count(t2.id) from department as t1 left join employee as t2 on t1.id=t2.dep_id order by t1.name;
select t1.name,count(t1.id),count(t2.id) from department as t1 left join employee as t2 on t1.id=t2.dep_id group by t1.name;
select t1.name,count(t1.id),count(t2.id) from department as t1 left join employee as t2 on t1.id=t2.dep_id group by t1.name order by count(t1.id) desc;

所谓连表就是把两张表连接在一起之后，就变成了一张大表，从from开始一直到on条件结束就看做一张表

之后where条件group by 分组 order by limit 都正常使用就可以了

查询平均年龄在25岁以上的部门名

select department.id,department.name from employee inner join department on department.id=employee.dep_id group by employee.dep_id having avg(employee.age) < 25;
select * from department where id in (select dep_id from employee group by dep_id having avg(age)>25);
select group_concat(department.name) from employee inner join department on department.id=employee.dep_id group by employee.dep_id having avg(employee.age) > 25;

查询技术部员工姓名

先查询技术部的部门id select name from employee where dep_id in (select id from department where name='技术');

再根据这个部门id找到对应的员工名

  select name from employee where dep_id = (select id from department where name = '技术');

查看不足1人的部门名 #distinct 去重

  select name,num from (select department.name as name,count(employee.id) as num from department left join employee on department.id=employee.dep_id group by department.name) as t1 where num<1; 
  select * from department where id not in (select distinct dep_id from employee);

查询大于所有人平均年龄的员工名与年龄

  select name,age from employee where age> (select avg(age) from employee);

查询大于部门内平均年龄的员工名，年龄

  select name ,age from employee inner join (select employee.dep_id as dep_name,avg(employee.age) as avg_age from employee inner join department on department.id = employee.dep_id group by employee.dep_id) as t1 on t1.dep_name = employee.dep_id  where employee.age>t1.avg_age ;

索引

新的数据结构--树

mysql中存储数据的两种方式

#聚集索引
#非聚集索引

索引的创建与删除

#创建主键 primary key 聚集索引 + 非空 + 唯一
#创建唯一约束 unique 辅助索引 + 唯一
#添加一个普通索引
#创建    create index 索引名 on 表(字段);    create index ind_id on si(id);
#删除    drop index 索引名 on 表;
#知道用了它会加快查询速度

正确使用索引

1,查询的条件字段不能是索引字段

#对哪一个字段创建了索引,就不能用这个字段做条件查询

2.在创建索引的时候应该对区分度比较大的列进行创建

#1/10以下的重复率比较适合创建索引

3.范围

#范围越大越慢，范围越小越快
#like 'a%' 快
#like '%a' 慢

4.按条件参与计算

5.and和or

#id name
#select * from company where id=5 and sex = 'female';   and 快
#select * from company where id=5 or sex='female';       or 慢
#多个条件的组合，如果使用and连接
     #其中一列含有索引，都可以加快查找速度
#如果使用or连接
    #必须所有的列都含有索引，才能加快查找速度

6.联合索引 : 最左侧原则(必须带着最左边的列做条件，从出现范围开始索引失效)

#select * from s1 where id = 18000 and name='eva' and emial='123456789';

索引

root 根节点

branch 分支节点

leaf 叶子节点

父子节点

二叉树

平衡树(btree) 能够让查找某一个值精力速度尽量平衡

#balance tree

平衡树不一定是二叉树

为什么不用二叉树

b+树

#b是balance 平衡的意思
    #为了保证没一个数据查找经历的IO
#分支节点不存储数据--让树的高度尽量矮，让查找一个数据的效率尽量稳定
#在所有椰子结点之间加入了双向的地址链接--查找范围非常快

两种索引的差别

聚集索引，聚簇索引

Innodb 必有且仅有一个

非聚集(簇)索引，辅助索引

innodb

myisam

innodb存储引擎中的 主键默认会创建一个聚集索引

备份库

#备份 
mysqldump -uroot -p123456 -h127.0.0.1 test1 > D:\mysql\sql\tmp.sql
#数据库的恢复
 source D:\mysql\sql\tmp.sql

学习路上不容易，但我们也要一起挥洒汗水，一起加油，一起努力！