mysql操作手册

上机1
1、安装Mysql；
注意：设置超级用户密码的时候，统一设置为1234
2、进入Mysql；
开始->程序->MySQL->MySQL Server 5.0->MySQL Command Line Client
3、建立数据库BANKxxx；
create database BANKxxx; --xxx为你的学号后三位
show databases; --验证是否建立成功
//命令必须在后面加分号否则出错,如果show databases;打成了show database;的话，也会错误，错误如下：
正常情况下是如下：
4、使用数据库BANK；
use BANKxxx; --使用数据库BANK
show tables; --数据库中没有表
因为此时BANK234为空表。

5、建立表BRANCHxxx, CUSTOMERxxx, LOANxxx, BORROWERxxx,
ACCOUNTxxx, DEPOSITORxxx；
Creating DataBase
☞Creating the Banking database:
Database Schema:
branch (branch_name, branch_city, assets)
customer (customer_name, customer_street, customer_city)
depositor (customer_name, account_number)
account (account_number, branch_name, balance)
borrower(customer_name, loan_number)
loan (loan_number, branch_name, amount)
Creating database Banking steps:
1) CREATE DATABASE Banking… (syntax lie on
DBMS)
2) Creating referenced tables(被参照关系)
3) Creating referencing tables
4) Creating other object of database
Creating Tables
//Creating table customer in SQL
CREATE TABLE customer (
customer_name char(20),
customer_street char(30) NOT NULL,
customer_city char(30)
) ;
CREATE TABLE ACCOUNTxxx (
account_number char(10),
branch_name char(30) NOT NULL,
balance numeric(12.2),
PRIMARY KEY (account_number),
FOREIGN KEY (branch_name)
REFERENCES branch(branch_name),
CONSTRAINT chk_balance CHECK (balance >= 0 ) ) ;
//注意此处必须保证表branch存在，如果不存在的话，先创建branch表
吧，否则会报错如图1，原因如下：
Integrity Constraints in Tables
☞Create table with constraints
CREATE TABLE account (
account_number char(10),
branch_name char(30) NOT NULL,
balance numeric(12.2),
PRIMARY KEY (account_number),
FOREIGN KEY (branch_name)
REFERENCES branch(branch_name),
CONSTRAINT chk_balance CHECK (balance >= 0 ) ) ;
The referenced table must be an existing relation!
Integrity constraints can be added to an existing relation,
if the relation does not satisfies the constraint, reject!
Constraint name make it easy to drop.
图1
//注意:在换行输入时，最后那个括号前到语句后面是不能跟逗号的发生错误的截图如下：
正确的截图如下：
--建立customersxxx表，xxx同前
show tables; --现在数据库中有了表
desc customers; --查看表的属性
以下为创建数据库bankToT的代码：
1创建数据库：create database bankToT;
删除数据库命令格式为：
drop database databasename ;
删除表命令:
Drop table tablename;
2 在刚才创建的数据库中创建表
命令格式：
use databasename; //这个命令允许无结尾分号//创建表branch
CREATE TABLE branch
(
branch_name char(20),
branch_city char(30) NOT NULL,
assets char(30) NOT NULL
) ;
//创建表customer
CREATE TABLE customer (
customer_name char(20),
customer_street char(30) NOT NULL,
customer_city char(30)
) ;
截图如下：
//创建表account
命令：
CREATE TABLE account (
account_number char(20),
branch_name char(20) NOT NULL,
balance numeric(12.2),
PRIMARY KEY (account_number),
FOREIGN KEY (branch_name)
REFERENCES branch(branch_name),
CONSTRAINT chk_balance CHECK (balance >= 0 ) ) ;
然后可能会报错：如下：
原因：因为在创建branch表的时候没有将branch_name设置为主键也就是primary key,你可以翻看之前创建branch表的代码，里面没有primary key(branch_name)这句。

解决方法为：先删掉branch,重建一个有主键的branch 表，代码如下：
CREATE TABLE branch
(
branch_name char(30),
branch_city char(30) NOT NULL,
assets char(30) NOT NULL ,
primary key(branch_name)
) ;
//重新创建表customer
CREATE TABLE customer
(
customer_name char(30),
customer_street char(30) NOT NULL,
customer_city char(30),
primary key(customer_name)
) ;
截图如下：
//创建表account
命令：
CREATE TABLE account (
account_number char(30),
branch_name char(30) NOT NULL,
balance numeric(12.2),
PRIMARY KEY (account_number),
FOREIGN KEY (branch_name)
REFERENCES branch(branch_name), CONSTRAINT chk_balance CHECK (balance >= 0 ) ) ;
截图如下：
这下没错了吧。

如果还有错的话，请访问：
/刁亮19900904/blog/item/938c991929bba4efae51334c.html //创建表depositor
create table depositor
(
customer_name char(30),
account_number char(30),
FOREIGN KEY (customer_name)
REFERENCES customer(customer_name),
FOREIGN KEY (account_number)
REFERENCES account (account_number)
);//注意：如果外键不是其他表的主键的话，要建立该属性的索引，即
// index(attribites)
截图如下：
//创建表loan
create table loan
(
loan_number char(30),
branch_name char(30) not null,
amount char(30),
primary key(loan_number),
foreign key(branch_name) references branch(branch_name) );
截图如下：
//创建表borrower
create table borrower
(
customer_name char(30),
loan_number char(30),
foreign key(customer_name) references customer(customer_name), foreign key(loan_number) references loan(loan_number)
);
截图如下：
至此，ppt里所有表格已经全部创建完毕，数据库整体的截图和各表的属性如下：
6、将书上第2章图2-1，2-3，2-4，2-5，2-6，2-7中的数据输入到表中；
insert into ACCOUNTxxx
values (‘A-101’, ‘Downtown’, 500); --向表中插入数据
select * from ACCOUNTxxx; --验证是否插入数据成功
输入以上语句时可能会报错,那是正常的,报错如下:
原因:因为表account和表branch外关联，所以account是branch的一个子表，必须先给父表赋值。

插入记录成功的截图如下：
Branch表输入结束，表内容截图如下：
表branch创建结束后再创建表account就应该没问题了，截图如下：
当输入如下数据后出错：
原因是branch表中没有Mianus,而是Miamus,更改代码如下：update branch set branch_name = ‘Minus’where assets = 400000;
然后继续插入数据：
Account表中的数据如下：
Customer表的数据如下：
Depositor表如下：
Loan表如下：
写入表borrower时报错如下：
原因：在表customer中没有Jeckson这个人。

Customer表如下：
将Jeckson加入customer中（其他数据本人用第一行的对应数据补全的）：Insert into customer
Values(‘Jackson’,’Spring’,’Pittsfield’);
插入后截图如下：
表borrower内容如下：
7、查询书上第三章3.2, 3.9的作业题
第一步、题中各表的关联关系如下：
第二步、创建数据库
Create database Employee_database234; Use Employee_database234;
截图如下：
//创建表employee234 Create table employee234 (
Employee_name char(30) , Street char(30),
City char(30),
Primary key(employee_name) );
截图如下：
//创建表company234 Create table company234 (
Company_name char(30), City char(30),
Primary key(company_name)
);
截图如下：
//创建表works234
Create table works234
(
Employee_name char(30),
Company_name char(30),
Salary numeric(12.2),
Foreign key(employ_name)
References employee234(employee_name), Foreign key(company_name) References company234(company_name) );
截图如下：
//创建表manages234
Create table manages234
(
Employee_name char(30),
Manager_name char(30),
Foreign key (employee_name)
References employee234(employee_name),
Foreign key(manager_name)
References employee234(employee_name)
);
截图如下：
总共四张表，如图：
第三步、输入数据：需要输入的记录如下（以下记录均为个人编造）
表employee234
部分数据输入的截图如下：
表employee234的截图如下：
表company234
表company234的截图如下：
表works234
表works234的截图如下：
表manages234
表manages234的截图如下：
第四步、习题3.2和习题3.9
3.2
a
select employee234.employee_name, employee234.city
from employee234, works234
where pany_name = ’First Bank Corporation’ and works234.employee_name = employee234.employee_name; 输出如下：
b
select *
from employee234
where employee_name in
(
select employee_name
from works234
where company_name = ‘First Bank Corporation’ and salary > 1000 );
输出如下：
c
select employee_name
from works234
where company_name != ‘First Bank Corporation’输出如下：
d
select employee_name
from works234
where salary > all
(
Select salary
From works234
Where company_name = ‘Small Bank Corporation’);
输出如下：
e
select pany_name
from company234 as T
where not exists
(
Select R.city
From company234 as R
Where pany_name = ‘Small Bank Corporation’)
输出截图如下：
f
select company_name
from works234
group by company_name
having count(distinct employee_name) >= all
(
Select count(distinct employee_name)
From works234
Group by company_name
);
输出结果如下：
g
select company_name
from works234
group by company_name
having avg(salary) > (select avg(salary)
from works234
where company_name= ‘First Bank Corporation’
);
输出结果如下：
习题3.9
a
select employee_name
from works234
where company_name = ‘First Bank Corporation’;
输出截图如下：
b
select e.employee_name
from employee234 as e,works234 as w,company234 as c
where e.employ_name = w.employee_name and
e.city = c.city and pany_name = pany_name;
输出截图如下：
c
select P.employee_name
from employee234 as P , employee234 as R, manages234 as M where P.employee_name = M.employee_name and
M.manager_name = R. employee_name and
P.street = R.street and P.city = R.city
输出截图如下：
d
select employee_name
from works234 as T
where salary >
(
Select avg(salary)
From works234 as S
Where pany_name = pany_name );
输出截图如下：
e
select company_name
from works234
group by company_name
having sum(salary) <= all
(
Select sum(salary)
From works234
Group by company_name
）;
输出截图如下：。