Mysql中常用语句，以book数据库为例-一一网

【摘要】查看所有数据库
show databases;
1
创建数据库
create database book;
1
删除数据库
drop database book;
1
查看所有表
show tables;
1
创建表
create table books(
id int primary key auto_increment,
title varchar…

查看所有数据库

show databases;

  
 
  1

创建数据库

create database book;

  
 
  1

删除数据库

drop database book;

  
 
  1

查看所有表

show tables;

  
 
  1

创建表

create table books(
id int primary key auto_increment,
title varchar(100) not null,
author varchar(50),
price decimal(5,2));

  
 
  1
  2
  3
  4
  5

删除表

drop table books;

  
 
  1

查看表中字段的属性

desc books;

  
 
  1

添加表中字段

alter table books add press varchar(50) comment "出版社";

  
 
  1

删除表中字段

alter table books drop column press;

  
 
  1

修改表中字段

alter table books change id id int;

  
 
  1

添加主键

alter table books add primary key(id,author);

  
 
  1

删除所有主键

alter table books drop primary key;		//自增主键删除会报错

  
 
  1

添加外键

alter table books add foreign key(author) references author(name);

  
 
  1

通查看创建表的代码查看外键

show create table books;

  
 
  1

删除外键

alter table books drop foreign key 外键名;

  
 
  1

添加数据

insert into books(id,title,author,price,press) values(1001,'Java从入门到放弃','路小狗','9.9','剑桥大学');

  
 
  1

删除表中所有数据

delete from books;

  
 
  1

查询表中所有数据

select * from books;

  
 
  1

带有where条件的查询

select * from books where id=1001 and title='Java从入门到放弃';

  
 
  1

带有where条件的更新

update books set id=1002,title='斗罗大陆',author='唐家三少',price=89.99,press=' ' where id = 1001;

  
 
  1

带有where条件的删除

delete from books where id = 1002;

  
 
  1

查询记录的条数

select count(*) rows from books;

  
 
  1

时间差函数

select timestampdiff(day,'2018-03-01 00:00:00', '2021-04-27 16:00:00') 自定义字段名;
select timestampdiff(hour,'2021-04-26 00:00:00', '2021-04-27 16:00:00') 自定义字段名;
select timestampdiff(minute,'2021-04-26 00:00:00', '2021-04-27 16:00:00') 自定义字段名;
select timestampdiff(second,'2021-04-26 00:00:00', '2021-04-27 16:00:00') 自定义字段名;

  
 
  1
  2
  3
  4