Mysql数据库的应用!!!
打开命令控制台, 进入mysql安装路径,并进入子路径bin, 然后便可作以下操作
c:/mysql/bin> mysql -h [hostname] -u [usename] -p [password]
//mysql -u root -p 回车;
c:/mysql/bin> mysqladmin -u root password newpassword
c:/mysql/bin> mysqladmin -u root reload
1>. mysql> show databases; //查看数据库 默认的mysql , test
2>. mysql> use test; //使用数据库,打开数据库
3>. mysql> show tables; //查看数据库里的表
4>. mysql> exit; //退出
1.sql语句的应用:
(1)创建表:
create table book(
id bigint not null auto_incremnent,
isbnNumber varchar(20) not null,
title varchar(50) not null,
description varchar(200),
primary key(id)
);
Example:
CREATE TABLE flight(id int NOT NULL,
startPlace VARCHAR(25) NOT NULL,
stopPlace VARCHAR(25) NOT NULL,
PRIMARY KEY(id));
Example:
CREATE TABLE passenger( id int NOT NULL,
name VARCHAR(25) NOT NULL,
flight_id int,
PRIMARY KEY(id), CONSTRAINT FOREIGN KEY(flight_id)
(2)desc book;显示表的结构
(3)insert :插入数据
insert into book values(“ddsfsfd5566”,"java","good");
指定字段插入
insert into book (isbnNumber,title)values("dfsdfsd45454","jsp");
(4)select *from book;
select title from book where id=1;
(5)update
update book set title="java " where id=1;
(6)delete
delete from book where id=1;
DELETE FROM yourTableName WHERE condition1 AND(OR) condition2;
(7)
1> ALTER TABLE yourTableName ADD columnName columnType columnConstraint;//添加字段
2> ALTER TABLE yourTableName MODIFY columnName newColumnType newcolumnConstraint;//修改字段类别和约束条件
3> ALTER TABLE yourTableName CHANGE oldcolumnName newColumnName newColumnType
newcolumnConstraint; //修改字段名,字段类别和约束条件!!!
4> ALTER TABLE yourTableName ADD [CONSTRAINT constraint] type (column);//添加字段约束条件
5> ALTER TABLE yourTableName DROP columnName; //删除该字段
Example: ALTER TABLE passenger ADD CONSTRAINT passenger_flight_fk FOREIGN KEY
(flight_id) REFERENCES flight(id); //!!!!!!!!!!!!!!!!!!Foreign key
Example1: ALTER TABLE orders ADD CONSTRAINT orders_customer_fk FOREIGN KEY
(customer_id) REFERENCES customer(id);
Example2: ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customer
(id);
2.数据库关系的设计(重点)
一对一
多对一
多对多
主键和外键的建立
Database Design Steps: 数据库设计步骤
1. Map Entities to Tables: 映射实体到表
2. Map the Attributes to Columns(Name, Type): 映射属性到列(字段)
3. Use Contraints (not null?, unique?, check?): 使用限制
4. Map the Unique Identifiers to Primary Keys(PK): 映射唯一标志符到主键
5. Map Relationships to Foreign Keys(FK): 映射表关系到外键
------------------------------------------------------------------------
single table 单表
create table book( //书
id bigint not null auto_increment,
isbnNumber varchar(20) not null,
title varchar(50) not null,
price double,
description varchar(200),
primary key(id)
);
---------------------------------------------------------------------------
one-to-one relationship 一对一关系 Customer - Address
create table customer(
id bigint not null auto_increment,
name varchar(50) not null,
sex varchar(10),
address_id bigint,
primary key(id));
create table address(
id bigint not null auto_increment,
city varchar(20) not null,
street varchar(50) not null,
primary key(id));
ALTER TABLE customer ADD CONSTRAINT customer_fk FOREIGN KEY(address_id) REFERENCES
address(id);
------------------------------------------------------------------------------------------
----------
one-to-many relationship: 多对一关系 customer - orders
create table orders(// 订单
id bigint not null auto_increment,
ordernumber varchar(50) not null,
customer_id bigint not null,
primary key(id));
ALTER TABLE orders ADD CONSTRAINT orders_fk FOREIGN KEY(customer_id) REFERENCES customer
(id);
------------------------------------------------------------------------------------------
-----------
many-to-many relationship : 多对多关系 Author - Book
创建中间表,通过主键,外键实现多对多
create table author (//作者
id BIGINT not null auto_increment,
name VARCHAR(50) not null,
primary key (id)
);
create table author_book (//中间表
author_id BIGINT not null,
book_id BIGINT not null
);
alter table author_book
add constraint author_bookFK0 foreign key (author_id) references author(id);
alter table author_book
add constraint author_bookFK1 foreign key (book_id) references book(id);
3.数据库的导入导出命令
导出数据:
C:/Program Files/MySQL/MySQL Server 5.0/bin> //如果环境变量path里设置了路径,则不用到该目录下
mysqldump -h localhost -u root -p bbb -r d:/bbb.sql //把bbb这个数据库导出到D盘,文件名叫bbb.sql
mysqldump -h localhost -u root -p bbb student -r d:/student.sql //导出单个表,及数据!文件名叫student.sql
mysqldump -h localhost -u root -p -d bbb -r d:/bbb.sql //导出数据库,只导结构,不导出数据,文件名叫bbb.sql
mysqldump -h localhost -u root -p -d bbb student -r d:/bbb.sql //导出单个表,只导表结构,不导出数据,文件名叫bbb.sql
导入数据:
进入mysql数据库控制台,如mysql -u root -p
mysql>use bbb //打开bbb数据库
mysql>source d:/bbb.sql //然后使用source命令,后面参数为脚本文件(即导出的备份