数据库操作常用命令

mysql -h localhsot -u root -p

show databases;

use (数据库名称);

show tables;

desc (表名称);

create badabase (数据库名);

create table products(
	id int not null auto_increment,
	cid int not null default ‘0‘,
	name varchar(60) not null default ‘‘,
	num int not null default ‘0‘,
	price double(7,2) not null default ‘0.00‘,
	description text not null default ‘‘,
	ptime int not null default ‘0‘,
	primary key(id),
	key pname(name,price)
);

drop table (表名);

insert into 表名[字段列表] values [值列表1],[值列表2],[值列表3],[值列表4];


insert into products values (‘‘,‘2‘,‘PHP‘,‘20‘,‘20.22‘,‘Good‘,‘‘);


insert into products (cid,name,num,price,description) values 
(‘1‘,‘SBASD‘,‘23‘,‘23.23‘,‘this is sb‘),
(‘2‘,‘SB‘,‘33‘,‘22.23‘,‘this is ssb‘),
(‘4‘,‘4SD‘,‘53‘,‘23.23‘,‘this is sb‘),
(‘1‘,‘S2342SD‘,‘73‘,‘34.23‘,‘this is sb‘),
(‘4‘,‘SB3ASD‘,‘13‘,‘22.23‘,‘this is sb‘),
(‘6‘,‘aasdf‘,‘103‘,‘13.23‘,‘this is sb‘),
(‘8‘,‘SBasdfaASD‘,‘20‘,‘43.23‘,‘this is sb‘),
(‘6‘,‘SBAasdSD‘,‘43‘,‘73.23‘,‘this is sb‘),
(‘5‘,‘SBAasdasdfSD‘,‘258‘,‘523.23‘,‘this is sb‘),
(‘11‘,‘SBasdfasdfASD‘,‘213‘,‘3.23‘,‘this is sb‘),
(‘12‘,‘SBasdfASD‘,‘23‘,‘2213.23‘,‘this is sb‘),
(‘13‘,‘SBasdfASD‘,‘103‘,‘2213.23‘,‘this is sb‘),
(‘14‘,‘SBAasdfSD‘,‘21‘,‘3.23‘,‘this is sb‘),
(‘15‘,‘asdf‘,‘201‘,‘223.23‘,‘this is sb‘),
(‘1‘,‘SBasdfASDasdfaASD‘,‘23‘,‘22.23‘,‘this is sb‘),
(‘3‘,‘SBASD‘,‘23‘,‘23.23‘,‘this is sb‘),
(‘8‘,‘SBfASD‘,‘23‘,‘2233.23‘,‘this is sb‘),
(‘9‘,‘SBASD‘,‘23‘,‘23.23‘,‘this is sb‘);

update 表名 set 字段1=‘value1‘ ,字段2=‘value2‘,字段3=‘value3‘,字段4=‘value4‘ where (条件语句);

update products set price=‘11.11‘,name=‘java‘ where id > 19;

delete from 字段 where 条件

delete from products where id=10;

1. select * from cats, products;


2.两个表总有相同字段的：
select c.name cname,p.name pname from cats as c, products as p;

3.关联数据库：
select c.name cname ,p.name pname from cats as c,products as p where c.id=p.cid and cid=5;

4.同表查询:

嵌套查询:（先执行子查询 子查询里面还有子查询）
select * from products where cid in(select id from cats where name like ‘J%‘);