mysql数据库

mysql建表约束

1. 主键约束：唯一确定一张表中的一条记录，也就是我们通过给某个字段添加约束
   create table user(
   id int primary key,
   name varchar(20)
   );
   insert into user values(1,‘张三’);
   insert into user values(1,‘张三’);//error
   insert into user values(2,‘张三’);
   insert into user values(NULL,‘张三’);//error

2. 联合主键：主键加起来不重复就可以
   create table user2(
   id int,
   name varchar(20);
   password varchar(20);
   primary key(id,name);
   );
   insert into user2 values(1,'张三‘,‘123’)；
   insert into user2 values(2,'张三‘,‘123’)；//correct
   insert into user2 values(1,'李四‘,‘123’)；
   insert into user2 values(NULL,'李四‘,‘123’)；//error

3. 自增约束 ：管控id的值 ，自动增长
   create table user3(
   id int primary key auto_increment; //自增
   name varchar(20);
   );
   insert into user3 (name) values(‘张三’)；
   insert into user3 (name) values(‘张三’)；

4. 如果在创建表的时候，忘记创建主键约束…
   create table user4(
   id int;
   name varchar(20);
   );
   alter table user4 add primary key(id); //修改表结构，添加主键
   alter table user4 drop primary key; //删除主键约束
   alter table user4 modify id int primary key; // 通过modify添加主键约束

5. 唯一约束：约束修饰字段的值不能重复
   create table user5(
   id int,
   name varchar(20)
   );
   alter table user5 add unique(name);
   insert into user5 values(1,‘zhangsan’);
   insert into user5 values(1,‘zhangsan’);//error
   create table user5(
   id int,
   name varchar(20),
   unique(id,name) //两个键在一起不重复
   );
   alter table user5 drop index name;
   alter table user5 modify name varchar(20) unique;

6. 非空约束：修饰的字段不能为空
   create table user6(
   id int,
   name varchar(20) not null
   );

7. 默认约束：当我们插入字段值时，没有传值，就会使用默认值
   create table uesr(
   id int,
   name varchar(20),
   age int default 10
   );

8. 外键约束：涉及到父表与子表（父表与子表）
   create table classes(
   id int primary key,
   name varchar(20)
   );
   create table students(
   id int primary key,
   name varchar(20),
   class_id int,
   foreign key(class_id) references classes(id)
   );
   结论： 主表中没没有的数值，在副表中，是不可以使用的
   主表中的记录被副表引用，是不可以删除的

   mysql 语句

   学生表
   student 学号 姓名 性别 出生年月 所在班级
   create table student(
   sno varchar(20) primary key,
   sname varchar(20) not null,
   ssex varchar(20) not null,
   sbirthday datetime,
   class varchar(20)
   );
   课程表
   Course 课程表 课程名称 教师编号’
   create table courses(
   cno varchar(20) not null,
   cname varchar(20) not null,
   tno varchar(20) not null,
   foreign key(tno) references teacher(tno)
   );
   成绩表
   Score 学号 课程号 成绩
   create table score(
   sno varchar(20) not null,
   cno varchar(20) not null,
   degree decimal,
   foreign key(sno) references students(sno)
   foreign key(cno) references students(cno)
   );

   教师表
   Teacher 教师编号 教师名字 教师性别 出生年月 职称 所在部门
   create table teacher(
   tno varchar(20) primary key,
   tname varchar(20) not null,
   tsex varchar(10) not null,
   tbirthday datetime,
   prof varchar(20),
   depart vachar(20) not null
   );

• 查询所有记录
  select * from student;

• 查询教师所有的单位不重复的depart列。(distinct)
  distinct //排重
  select distinct depart from teacher

• 查询score表中成绩在60到80之间的所有记录(between and,> <
  select * from score where degree between 60 and 80;
  select * from score where degree > 60 and degree < 80;

• 查询score表中的成绩为85,86,88 (int)
  select * from score where degree in(85,86,88);

• 查询student表中 "9604"班或者性别为女的记录 (or）
  select * from student class = “9604” or ssex = “女“；

• 以class降序查询student表的所有记录 (order by * desc asc)
  select * from student order by class desc;

• 以cno升序，degree降序查询score的记录

select * from score order by cno asc,degree desc;

• 查询’’ 95031’'班的人数（count)

select count(*) from student where class = ‘‘95031’’;

• 查询score表中的最高分的学生学号与课程号（子查询或者排序）limit
  select sno,cno from score where degree = (select max(degree) from score);
  select sno,cno from score order by degree limit 0,1;(排序)

• 查询每门课的平均成绩 avg (group by)
  select * from cource;

select avg(degree) from score where cno = ‘‘3-105’’
select avg(degree) from score where cno = ‘‘5-105’’
select avg(degree) from score where cno = ‘‘4-105’’
select cno ,avg(degree) from score group by cno;(分组)

• 查询score表中至少有2名学生选修的并以3开头的课程的平均成绩
  select cno,avg(degree) from score group by cno having count(cno)>=2 and cno like ‘3%’;

• 查询分数大于70 小于90 的sno列
  select sno ,degree from score where degree > 70 and degree < 90;

• 查询所有学生的sname ,cno和degree列 (连表查询)
  select sname,cno,degree from student,score where student.sno = score.sno;

• 查询所有学生的sno,cname ,degree(连表查询)
  select sno,cname,degree from course,score where course.cno = score.cno;

• 查询所有学生的sname,cname,degree(连表查询)
  select sname,cname,degree from strudent,counse,score
  where student.sno = score.sno and course.cno = score.cno;

• 查询’‘95031’'班学生每门课的平均分
  select cno ,avg(degree) from score in(select sno from student where class = “95031”) group by cno;

• 查询选修“3-105”课程的成绩高于‘‘109’’ 号同学‘’3-105‘’成绩的所有同学记录
  select * from student where (select degree from score where cno = ‘3-105’> (select degree from score where sno = ‘109’ and cno = ‘3-105’) )

• 查询和学号为108,101的同学同年出生的所有学生的sno,sname和sbirthday的列
  select sno,sname,sbirthday from student where year(sbirthday) in (select year(sbirthday) from student where sno = ‘108’ or sno = ‘101’)

• 查询“张旭”教师任课的学生成绩
  select degree from score where cno in (select cno from course where tno = (select tno from teacher where tname = ‘张旭’)

• 查询选修某课程的同学人数多于5人的教师姓名
  select tname from teacher where tno in (select tno from course where cno in (select cno from score group by cno having count(cno) > 5)

• 查询‘’计算机系‘’与’'电子系‘’不同职称的教师的tname 和 prof
  select * from teacher where depart = ‘‘计算机系’’ and prof not in (select prof from teacher where depart = ‘‘电子工程系’’)
  union
  select * from teacher where depart = ‘‘电子工程系’’ and prof not in (select prof from teacher where depart = ‘‘计算机系’’)

• 查询选修编号为‘’3-105‘’课程且成绩至少高于选修编号为‘’3-245‘’的同学的cno ,sno,degree ，并按degree从高到底次序排序
  select * from score where cno = ‘‘3-105’’ and degree > any(select degree from score where cno = ‘‘3-245’’) order by degree desc;

• 查询选修编号为‘’3-105‘’课程且成绩高于选修编号为‘’3-245‘’的同学的cno ,sno,degree ，并按degree从高到底次序排序
  select * from score where cno = ‘‘3-105’’ and degree > all(select degree from score where cno = ‘‘3-245’’) order by degree desc;

• 查询所有教师和同学的name,sex,birthday (别名)
  select sname as name ,ssex as sex,sbirthday as birthday from student
  union
  select tname,tsex,tbirthday from teacher;

• 查询成绩比该课程平均成绩低的同学的成绩表
  select * from score a where degree < (select avg(degree) from score b where a.cno = b .cno)

mysql的四种连接查询

内连接
inner join or join
外连接
1.左连接left join或者 left outer join
2.右连接right join或者 right outer join
3.完全连接 full join

事务

mysql，事务其实是最小的不可分割的工作单元，事务能够保证一个业务的完整性

1.drop、delete与truncate的区别，分别用于什么场景？
delete和truncate只删除表的数据不删除表的结构
速度,一般来说: drop> truncate >delete
delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;
如果有相应的trigger,执行的时候将被触发. truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.
不再需要一张表的时候，用drop
想删除部分数据行时候，用delete，并且带上where子句
保留表而删除所有数据的时候用truncate
2.MySQL中的存储引擎有哪些？都有什么区别
MyISAM，不支持事务，只有表级锁
InnoDB，支持事务，锁的最小粒度：支持行级锁（什么情况下是行锁，什么情况下是表锁？）
MEMORY，内存存储，不落盘
3.主键和唯一索引的区别？
主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。
主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
唯一性索引列允许空值，而主键列不允许为空值。
主键列在创建时，已经默认为空值 + 唯一索引了。
主键可以被其他表引用为外键，而唯一索引不能。
一个表最多只能创建一个主键，但可以创建多个唯一索引。
主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。
在 RBO 模式下，主键的执行计划优先级要高于唯一索引。 两者可以提高查询的速度。
4. MySQL性能优化的方法有哪些？
5. 假设数据表已建立以下联合索引，示例中哪些可以命中索引吗？命不中的原因是什么？
KEY ix_abc\ (a, b, c),
where a=1 and b=1 and c=1
where b=1 and a=1 and c=1
where b=1 and c=1
where c=1
where d=1 and a=1
6. 如何解决分库分表后的查询效率问题？