建库建表
(1) 创建数据库mydb11_stu并使用数据库
create database mydb11_stu;
use mydb11_stu;
(2) 创建student表
create table student (
id int(10) not null unique primary key,
name varchar(20) not null,
sex varchar(4),
birth_year int,
department varchar(20),
address varchar(50)
);
(3) 创建score表
create table score (
id int(10) not null unique primary key auto_increment,
stu_id int(10) not null,
c_name varchar(20),
grade int(10) );
2. 插入数据
(1) 向student表插入记录如下:
insert into student values (901, '张三丰', '男', 2002, '计算机系', '北京市海淀区');
insert into student values (902, '周全有', '男', 2000, '中文系', '北京市昌平区');
insert into student values (903, '张思维', '女', 2003, '中文系', '湖南省永州市');
insert into student values (904, '李广昌', '男', 1999, '英语系', '辽宁省皋新市');
insert into student values (905, '王翰', '男', 2004, '英语系', '福建省厦门市');
insert into student values (906, '王心凌', '女', 1998, '计算机系', '湖南省衡阳市');
(2) 向score表插入记录如下:
insert into score values (null, 901, '计算机', 98);
insert into score values (null, 901, '英语', 80);
insert into score values (null, 902, '计算机', 65);
insert into score values (null, 902, '中文', 88);
insert into score values (null, 903, '中文', 95);
insert into score values (null, 904, '计算机', 70);
insert into score values (null, 904, '英语', 92);
insert into score values (null, 905, '英语', 94);
insert into score values (null, 906, '计算机', 49);
insert into score values (null, 906, '英语', 83);
3. 查询
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.