mysql常用操作

一、数据库的连接、退出

 

mysql -uroot –p                           #连接本地，推荐使用

退出：ctr+z或者exit、quit、\q

二、查询、创建、删除表

show databases;

create database teatmandb;

drop database teatmandb;

use testmandb;

select database();

create table user(id int(11));

show tables;

drop table data;

三、数据的增删改查

3.1 创建一个表

create table book(
   id INT NOT NULL AUTO_INCREMENT,
   book_name VARCHAR(100) NOT NULL,
   author VARCHAR(40) NOT NULL,
   publish_date DATE,
   price float,
   PRIMARY KEY (id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

show tables;

desc book;

3.2 插入数据

insert into book values(1,"seleinum","weilaoshi","2018-10-10",100);

insert into book values(2,"mysql","weilaoshi","2018-10-11",200);

insert into book values(3,"math","weilaoshi","2018-10-14",200);

insert into book(book_name,author,publish_date,price) values("linux","weilaoshi","2019-10-14",50);

insert into book(book_name,author,publish_date,price) values("mondb","weilaoshi","2019-11-14",60);

select count(*) from book;

select * from book;

3.3 删除数据

delete from book where id=4;

delete from book where book_name="linux";

delete from book;

 

3.4 数据的改

update book set book_name="math db" where book_name="math";

 

3.5 数据的查找

select book_name from book;

select book_name as 书名 from book;

select id as 序号,book_name as 书名 from book;

select * from book where id>=2;

select * from book where author<>"weilaoshi";

select *from book order by price;

select *from book order by price desc;

select *from book order by price asc;

select count(*),author from  book group by author;

select avg(price),author from  book group by author;

select sum(price),author from  book group by author;

select sum(price),author from book group by author  having sum(price)>200;

select count(*),author from book where id >5 group by author;

四、实用小知识

varchar：可变字长；省空间，但是执行效率低

char：存储空间固定；浪费资源，但是执行快

数据的常用搜索引擎：

MyISAM：不支持事务、强调性能，执行速度比InnoDB类型更快，适合做很多count的计算；

INnoDB:支持事务；可靠性要求比较高；

memory：数据存在内存中