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