Error Code: 1007. Can't create database 'jxgl'; database exists 0.000
错误原因
执行创建数据库时,两次执行了同一SQL语句,导致重复创建数据库
CREATE DATABASE test;
3、解决办法
(1)不执行该语句
(2)如果test创建错误,将其删除,重新执行一下上述SQL语句
insert into student values('2005001','钱横',18,'男','Cs'), ('2005002','王林',19,'女','Cs'),
('2005003','李民',20,'男','Is'), ('2005004','赵欣然',16,'女','Ma')
Error Code: 1062. Duplicate entry '2005001' for key 'PRIMARY' 0.000 sec
已经含有主键为2005001的记录,主键是不允许相同的。
没搞清楚外码的概念
首先,外键和外码没有区别,是一个意思!
外码必须是另一个关系的主码, 或者 有唯一约束的列。
百度上的解释是:如果公共关键字在一个关系中是主关键字,那么这个公共关键字被称为另一个关系的外键。由此可见,外键表示了两个
关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表,具有此外键的表被称为主表的从表。外键又称作外关键字。
主键:
SQL Server 中的住码与主键是一个概念,只是描述不一样而已。
主码也就是主键,是惟一标识表中的每一行的字段或者多个字段的组合,它可以实现表的实体完整性
每个表只能有惟一的主码,且不能为空.
10:36:21 drop table student
Error Code: 1217. Cannot delete or update a parent row: a foreign key constraint fails 0.500 sec
count函数,和count(distict ) 函数,
详情查看:http://www.runoob.com/sql/sql-func-count.html
平均值函数:SELECT AVG(column_name) FROM table_name
Group by 的用法
with的用法不熟悉
SQL代码:
/*创建数据库,表以及插入数据*/
create database jxgl;
use jxgl;
drop table student;
create table student
( sno char(7) not null,
sname varchar(16),
sage smallint check(sage>=15 and sage<=45),
ssex char(2) default '男' check(ssex='男' or ssex='女'),
sdept char(2),
primary key(sno)
);
insert into student values('2005001','钱横',18,'男','Cs'),
('2005002','王林',19,'女','Cs'),('2005003','李民',20,'男','Is'),
('2005004','赵欣然',16,'女','Ma');
select *from Student;
drop table course;
create table course
( cno char(2) not null,
cname varchar(20),
cpno char(2),
credit smallint,
primary key(cno)
);
insert into course values('1','数据库系统','5',4),('2','数学分析',null,2)
,('3','信息系统导论','1',3),('4','操作系统原理','6',3),('5','数据结构','7',4),
('6','数据处理基础',null,4),('7','C语言','6',3);
select *from course;
drop table sc;
create table sc
( sno char(7) not null,
cno char(2) not null,
grade smallint null check(grade is null or(grade between 0 and 100)),
primary key(sno,cno),
foreign key(sno) references student(sno),
foreign key(cno) references course(cno)
);
insert into sc values('2005001','1',87),('2005001','2',67),('2005001','3',90)
,('2005002','2',95),('2005003','3',88);
select *from sc;
/*查询数据*/
select count(distinct(course.cno)) from course,sc
where course.cno=sc.cno;
select avg(sage) from student, sc
where student.sno=sc.sno and sc.cno='3';
select avg(grade) from sc,course
where sc.cno=course.cno and course.credit=3;
select course_name,course_num
from (select course.cname as course_name,count(distinct(sc.sno)) as course_num
from sc,course
where sc.cno=course.cno
group by sc.cno) as count_table
where course_num>1
order by course_num desc ,course_name asc;
select T.sage,T.sname from student S,student T
where S.sname='王林' and S.sno<T.sno and S.sage>T.sage;
select sname,sage from student
where sname like '王%';
select sno,cno from sc
where grade=null;
with girl_avg_age as
(select avg(sage) from student
where ssex='女')
select sname,sage
from student
where ssex='男' and sage>girl_avg_age;