PHP 之 MySQL 操作(1)

1.创建数据库 创建一个名为bookstore的数据库

mysql> create database bookstore;

2.删除一个数据库

mysql> drop database books;

3.查询数据库

mysql> show databases;

4.使用数据库

mysql> use bookstore;
Database changed

 

二.创建数据表

        图书号(ID)   图书名(bookname)  出版社(publisher)  作者(author)  单价(price)  图书简介(detail)  出版日期(publishdata)

mysql> create table book(
    -> id int not null auto_increment,
    -> bookname varchar(50) not null default "",
    -> publisher varchar(80) not null default "",
    -> author varchar(30) not null default "",
    -> price double not null default 0.00,
    -> detail text,
    -> publishdate date,
    -> primary key(id),
    -> index book_bookname(bookname),
    -> index book_price(price));
Query OK, 0 rows affected (0.25 sec)

1. show tables 命令查看当前数据库下共有多少张数据表

mysql> show tables;
+---------------------+
| Tables_in_bookstore |
+---------------------+
| book                |
+---------------------+

2. desc 命令查看数据表的详细结构

mysql> desc book;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| bookname    | varchar(50) | NO   | MUL |         |                |
| publisher   | varchar(80) | NO   |     |         |                |
| author      | varchar(30) | NO   |     |         |                |
| price       | double      | NO   | MUL | 0       |                |
| detail      | text        | YES  |     | NULL    |                |
| publishdate | date        | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

3. 插入操作

mysql> insert into book values(null,"百年孤独","电子工业出版社","李明",59.00,"优质","2017-5-12");
Query OK, 1 row affected, 4 warnings (0.04 sec)

mysql> insert into book(bookname,publisher,author,price)values("南渡北归","电子工业出版社","张三",45.00);
Query OK, 1 row affected, 3 warnings (0.03 sec)

4.查询数据记录

mysql> select id,bookname from book;

5.更新mysql数据库中存在的记录 update

mysql> update book set price=79 where id=2

mysql> select id,price from book;
+----+-------+
| id | price |
+----+-------+
|  3 |     0 |
|  1 |    59 |
|  2 |    79 |
+----+-------+

6.删除mysql数据表中的记录

mysql> delete from book where id=3;

 7.查看完整表格

mysql> select*from book;
+----+----------+-----------+--------+-------+--------+-------------+
| id | bookname | publisher | author | price | detail | publishdate |
+----+----------+-----------+--------+-------+--------+-------------+
|  1 | ????     | ???????   | ??     |    59 | ??     | 2017-05-12  |
|  2 | ????     | ???????   | ??     |    79 | NULL   | NULL        |
+----+----------+-----------+--------+-------+--------+-------------+

8. 删除表

mysql> drop table book;

9.使用AS子句为字段取别名

select bookname'图书名称',author'图书作者',price'图书价格' from book;

 

10. 解决mysql数据库操作中文乱码问题

(1) 查看编码

mysql> show variables like 'char%';

(2) 查看数据库编码

mysql> show create database bookstore;

(3) 查看数据表编码

mysql> show create table books;

(4) 修改数据库数据表编码

ALTER DATABASE `bookstore` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci 

ALTER TABLE `books` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

(5) 改变原数据库编码方式

set character_set_database=utf8;
set character_set_server=utf8;
set character_set_client=utf8;
set character_set_connection=utf8;

11. 导入sql文件

　　E:\wamp\www\bookorama.sql

create table customers
( customerid int unsigned not null auto_increment primary key,
  name char(50) not null,
  address char(100) not null,
  city char(30) not null
);

create table orders
( orderid int unsigned not null auto_increment primary key,
  customerid int unsigned not null,
  amount float(6,2),
  date date not null
);

create table books
(  isbn char(13) not null primary key,
   author char(50),
   title char(100),
   price float(4,2)
);

create table order_items
( orderid int unsigned not null,
  isbn char(13) not null,
  quantity tinyint unsigned,

  primary key (orderid, isbn)

);
create table book_reviews
(
  isbn char(13) not null primary key,
  review text
);

mysql> source bookorama.sql;

 12. mysql别名

database(数据库),table(表),column(列),index(索引),alias(别名)