主键
表中的一个字段，该字段的值是每一行数据的唯一标识。
默认情况下，每张表都要有一个主键，也只能有一个主键。
主键生成策略：代理主键，与业务无关的字段，仅仅是用来标识一行数据，一般定义为int类型，因为int类型存储空间小，同时可以设置自增，避免主键冲突问题。
主键值必须唯一，不能有重复。
create table user(
-> id int primary key auto_increment , //设置id为主键并自增
-> realname varchar(10) ,
-> age int ,
-> integral int ) ;
外键
将表中的某个字段设置为外键，与另一张表的主键进行关联，从而使两张表建立级联关系。
创建主键
create table orders(
-> id int primary key auto_increment ,
-> name varchar(10) ,
-> uid int ,
-> foreign key(uid) references user(id)) ;
外键取值必须是主键中已经存储的值，如果是主键中没有的值，则外键无法存储。
A表的主键和B表的外键建立约束关系后，B表外键的值就需要被A表的主键值所约束，只能从A表中获取已经存在的值存入B表的外键，所以B表就是从表，A表示主表。
删除外键
- 查询建表语句
show create table orders ;
- 获取到外键名称
orders | CREATE TABLE `orders` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`uid` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `uid` (`uid`),
CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
alter table orders drop foreign key orders_ibfk_1 ;
alter table orders drop foreign key 外键名称 ;
表与表之间的关系
-
一对一
A中一条记录对应B中的一条记录，B中一条记录对应A中的一条记录。
人和身份证号
-
一对多
A中一条记录对应B中的多条记录，B中多条记录对应A中的一条记录。
班级和学生关系
create table class( //创建主表
-> id int primary key auto_increment ,
-> name varchar(10) );
create table student( //创建从表
-> id int primary key auto_increment ,
-> name varchar(10) ,
-> cid int ,
-> foreign key(cid) references class(id) ) ;
insert into class values (1 , "一班") , (2 , "二班") ; //添加班级
insert into student (name , cid) values ("zhangsan" , 1) , ("lisi" , 2) , ("wangwu" , 1 ) ; //添加学生
-
多对多
A中多条记录对应B中的多条记录，B中多条记录对应A中的多条记录。
选课关系
create table course( //创建课程表
id int primary key auto_increment ,
name , varchar(10) );
create table student_course( //创建学生-课程表
-> id int primary key auto_increment ,
-> sid int ,
-> cid int ,
-> foreign key(sid) references student(id) ,
-> foreign key(cid) references course(id) ) ;
insert into student_course (sid , cid) values (1 , 3 ) , (2 , 1 ) , (3 , 1 ) , (1 , 2); //插入选课数据
多表关联查询
嵌套查询（子查询）
select * from class where id = (select cid from student where name = 'zhangsan') ;
连接查询
- 内连接
select * from student inner join class where student.cid = class.id ;
select * from student , class where student.cid = class.id ;
//将student表简称为s，class表简称为c
select * from student as s , class as c where s.cid = c.id ;
//用and连接不同查询条件
select * from student as s , class as c where s.cid = c.id and s.name = 'zhangsan' ;
//将查询结果中student表的id命名为studentId，class表中的name命名为class
select s.id studentId , s.name name , c.name class from student as s , class as c where s.cid = c.id and s.name = 'zhangsan';
-
外连接
左查询：左表所有数据和右表满足条件的数据
select * from student s left join class c on s.cid = c.id and s.name = 'zhangsan' ;
所有学生为左表所有数据，查出来的一班为右表符合条件的数据
右连接
select * from student s right join class c on s.cid = c.id and s.name = 'zhangsan' ;
多对多关系级联查询
select * from student s , course c , student_course sc where sc.sid = s.id and sc.cid = c.id and s.id = 1 ;
数据库索引
数据库结构，可用来快速查询数据表中特定的记录，提高数据库查询效率的重要方式，索引是直接添加到字段上的。
索引包括：普通索引、唯一索引、全文索引、单列索引，多列索引、空间索引
创建维护索引需要消耗时间，索引也需要占用物理空间。
主键自带索引，可以给其他字段添加索引。
索引设计原则：
- 出现在where语句中的列，而不是select后面的列（select name from student where id = 1)
- 索引的值，尽量唯一，效率更高
- 不要添加过多的索引，维护成本高
添加索引
alter table student_course add index ssid(sid) ; //方法一
alter table student_course add index 索引名(字段名) ;
create index ccid on student_course(cid) ; //方法二
create index 索引名 on 表名(字段名) ;
删除索引
删除索引需保证作为索引的字段非外键，否则无法删除
- 有外键无法删除
alter table student_course drop index ssid ;
ERROR 1553 (HY000): Cannot drop index 'ssid': needed in a foreign key constraint
- 删除外键后删除
show create table student_course ; //查询建表语句
alter table student_course drop foreign key student_course_ibfk_1 ; //删除外键
alter table student_course drop foreign key student_course_ibfk_2 ;
alter table student_course drop index ssid ; //删除索引，方法一
drop index ccid on student_course ; //删除索引，方法二
数据库视图View
数据库中一张虚拟的表，运行不同用户或者应用程序以不同的方式查看同一张表中的数据。
创建视图
create view viewa as select id , name from student ;
create view viewall as select * from student ;
create view 视图名 as select * from student ;
使用视图
select * from viewa ;
删除视图
drop view viewa ;