mysql 开发文档_mysql开发文档.pdf

mysql开发文档

MySQL 开发者

SQL 权威指南

SQL For MySQL Developers

A Comprehensive Tutorial and Reference

整理：侯杰

MySQL 开发范例

第一章 SQL 概要

1.引入一个名为 BOOKSQL 的新用户，他的密码是 BOOKSQLPW。

CREATE USER ‘BOOKSQL’@’LOCALHOST’ IDENTIFIED BY ‘BOOKSQLPW’

create user ‘houjie410782’@’localhost’ identified by ‘630529’

2.给 SQL 用户 BOOKSQL 创建和操作表的权限。

grant all privileges on *.* to ‘BOOKSQL’@’LOCALHOST’ with grant option;

grat all privileges on *.* to ‘houjie410782’@’localhost’ with grant option;

3.创建数据库 tennis。

create database tennis;

4.指定 tennis 为当前数据库。

use tennis;

5.创建表 players ，teams ，matches ，penalties ，committee_members。

create table players

(playerno integer not null,

name char(15) not null,

initials char(3) not null,

birth_date date,

sex char(1) not null,

joined smallint not null,

street varchar(30) not null,

houseno char(4),

- 2 -

MySQL 开发范例

postcode char(6),

town varchar(30) not null,

phoneno char(13),

leagueno char(4),

primary key (playerno)

)

create table teams(

teamno integer not null,

playerno integer not null,

division char(6) not null,

primary key (teamno)

)

create table matches(

matchno integer not null,

teamno integer not null,

playerno integer not null,

won smallint not null,

lost smallint not null,

primary key (matchno)

)

- 3 -

MySQL 开发范例

create table penalties(

paymentno integer not null,

playerno integer not null,

payment_date date not null,

amount decimal(7,2) not null,

primary key (paymentno)

)

create table committee_members(

playerno integer not null,

begin_date date not null,

end_date date,

position char(20),

primary key (playerno,begin_date)

)

6.为表中填充数据。

insert into players values(2,'Everett','R','1948-09-01','M',1975,'Stoney

Road','43','3575NH','Stratford','070-237893','2411'