1.创建数据库
show databases;显示数据库
create database 库名;
2.定位数据库
use 库名;
show tables;查看当前的表
3.创建表
create table 表名(
id int not null auto_increment primary key,
name var char(50) not null
);
//创建一张表带主键和外键的
create table user(
id int not null auto_increment primary key,
listid int not null,
primary key(id),
foreign key(listid) references user(‘id’)
);
alter table list add foreign key(‘listid’) references user(id);
4.删除表
drop table 表名;
5.自增列必须是主键,且主键不能为空,主键比一定是自增 ;唯一键值不能重复但可以为null
6.查找
简单查询:select * from user;
select id,name,sex,age from user;
如果想给表的列起个别名
select id as ‘序号’, name as ‘姓名’ from user;
简单查询里的条件查询:
select * from user where id=2;
select * from user where id=2 and name=‘悟空’;
子查询:select * from 库名 where id in (select userid from user where id=2);
多表查询:select user:id
7.增加:insert into 表名 values(null ,’’,1,2,3);
insert into 表名(name,sex,age)values (“悟空”,“男”,“12”);
8.删除
delete from user where id=2;
多行删除
delete from user where id in (select id from user);
9.修改
update user set name=“悟空” where id=2;
命令框中文乱码
set names gb2312;
改变表中所有编码格式
alter table 表名 convert to character set utf8;
显示表中的数据编码方式
SHOW FULL COLUMNS FROM 表名;
修改表中的格式
alter table user change username name varchar(100) character set utf8 collate utf8_unicode_ci not null default ‘’;
07-28
07-28