数据库操作常用命令

修改表的语法
=========================
增加列[add 列名]
=========================
①alter table 表名 add 列名列类型列参数【加的列在表的最后面】
   例：alter table test add username char(20) not null default '';
       alter table test add birth date not null default '0000-00-00';

②alter table 表名 add 列名列类型列参数 after 某列【把新列加在某列后面】
   例：alter table test add gender char(1) not null default '' after username;

③alter table 表名 add 列名列类型列参数 first【把新列加在最前面】
   例：alter table test add pid int not null default 0 first;

=========================
删除列[drop 列名]
=========================
①alter table 表名 drop 列名
   例：alter table test drop pid;

=========================
修改列[modife 列名]
=========================
①alter table 表名 modify 列名新类型新参数【修改列类型】
   例：alter table test modify gender char(4) not null default '';
②alter table 表名 change 旧列名新列名新类型新参数【修改列名和列类型】
   例：alter table test change pid uid int unsigned not null default 0;

=========================
查询列

1.链接数据库：

mysql -h localhsot -u root -p

2.显示MySql中的数据库：

show databases;

3.进入数据库：

use (数据库名称);

4.查看数据库中的表：

show tables;

5.显示表结构：

desc (表名称);

6.创建数据库:

create batabase (数据库名);

7.创建表:

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)
);

8.删除表：

drop table (表名);

9.插入表数据：

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');

10.更新表数据：

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

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

11.删除数据：

delete from 字段 where 条件

delete from products where id=10;

12.查询数据:

①多表查询：

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%');