MySQL数据库开发答案(2)

创建 xs，kc，xs_kc三张表： 1、 写出三张表的建表语句

create database sxcj;

use sxcj

create table xs

( sno char(6) primary key , Sname char(8) not null, Zhuanyeming char(10) null,

Sex tinyint(1) not null check(sex int(0,1)), sbir date not null, record tinytin(1) null, photo blob null, comment text null )engine=InooDB;

Create table kc

( kno char(3) primary key, Kname char(16) not null,

Kaikexueqi tinyint(1) not null default 1 check(kaikexueqi >=1 and kaikexueqi <= 8),

Xueshi tinyint(1) not null, Xuefen tinyint(1) null )engine=InooDB;

Create table xs_kc

( sno char(6) not null, Kno char(3) not null, Chengji tinyint(1) null, Primary key(sno,kno),

Foreign key(sno) references xs(sno) on delete cascade on update cascade, Foreign key(kno) references kc(kno) on delete cascade on update cascade );

2、 给XS表增加一个列“备注”，数据类型为text，不允许为空

alter from xs

add comment text not null;

6

3、 创建表 XS的一个名为student的拷贝，并且复制其内容

Create table student as (select * from xs);

4、 用from的join关键字表达下列查询：查找选修了“计算机基础”课程且成绩在80分

以上的学生学号、姓名、课程名及成绩。

Select xs.sno,sname,kname,chengji

From xs join xs_kc on xs.sno=xs_kc.sno Join kc on xs_kc on kc.kno=xs_kc.kno

Where kname=’计算机基础’ and chengji>=80;

5、 用root用户新建“dsh”用户，密码为“shangwu”，授予对sxcj数据库中所有表的select

权限。

Create user

‘dsh@localhost’ identified by ‘shangwu’;

Grani select

On sxcj.*

To dsh@localhost;

7

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说教育文库MySQL数据库开发答案(2)在线全文阅读。