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(别名)