创建两个table表的关联,用到外键:
外键的使用条件:外键用来约束两个表,外键名字数据库中唯一,mysql数据库存储引擎使用innodb。innodb引擎支持事务,外键。
而存储引擎为mysiam的数据库不支持。
外键的创建:
首先创建表class;
class | CREATE TABLE `class` (
`class_id` int(11) NOT NULL AUTO_INCREMENT,
`caption` char(10) NOT NULL,
PRIMARY KEY (`class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
然后创建表student:
student | CREATE TABLE `student` (
`student_id` int(11) NOT NULL AUTO_INCREMENT,
`student_name` char(10) NOT NULL,
`gender` char(10) NOT NULL,
`c_id` int(11) NOT NULL,
PRIMARY KEY (`student_id`),
KEY `c_id` (`c_id`),
CONSTRAINT `student_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `class` (`class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
然后创建class和student表索引:
alter table student add foreign key (c_id) references class(class_id);
然后查看表student:
MariaDB [job]> desc student;+--------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+----------------+
| student_id | int(11) | NO | PRI | NULL | auto_increment |
| student_name | char(10) | NO | | NULL | |
| gender | char(10) | NO | | NULL | |
| c_id | int(11) | NO | MUL | NULL | |
+--------------+----------+------+-----+---------+----------------+
在接着查看表class:
MariaDB [job]> desc class;
+----------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+----------------+
| class_id | int(11) | NO | PRI | NULL | auto_increment |
| caption | char(10) | NO | | NULL | |
+----------+----------+------+-----+---------+----------------+
2.创建视图
create view table(名) AS select ...
example:
create view new_table_3 AS select * from new_table_2 where course_id=1 or course_id=2; #创建视图new_table_3
3.更新视图
update 表 set ..where ..
example:
update student set register_date="2015-7-1" where student_id=4; #更新数据表student
4.修改数据表
alter table 表 ...
example:
alter table student drop phone; #修改数据表student,删除列
alter table student add phone int ; #添加字段phone
5.添加数据表信息
insert into 表 ...
insert into student(student_id,student_name,gender,c_id,register_date) values(7,"x","nv",3,"2016-1-1"); #添加信息
6.算数运算
example:
select student_name,avg(number),sum(number) from new_table_3; #求平均值和总和
select * from student where student_name like "%"; #查询名字类似“%”
select * from student limit 2 offset 3; #从第三个开始查询两条信息
7.查看表结构
desc 表
example:
desc student;#显示student表结构
select columes from student; #显示表结构
8.查看索引
show index from 表...
drop index [indexname] on mysql;
example:
show index from class; #查看表class索引
alter table student add index register_date(register_date); #修改表student,添加索引
drop index register_date on student; #删除索引register_date