12.3数据库知识总结

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命令，后面参数为脚本文件(即导出的备份