登录mysql:mysql -u root -p
工具:navicat for mysql
===================基础==================================
建立数据库:create database test default charset=utf8;
创建表:栗子:
create table person_info
(
person_id smallint(5) unsigned auto_increment,
name varchar(50) not null comment '姓名',
country varchar(60) default 'China',
salary decimal(10,2) default 0.00 comment '工资',
primary key (person_id)
)engine=innodb default charset=utf8;
查看表结构:desc table_name;
删除表:drop table table_name;
创建一个与当前某个表一样的空表:create table table_name like table_name1;
修改表:
前提:alter table table_name
1.修改字段类型:modify countryvarchar(50)default 'China'#修改country为50字符
2.修改字段名:change old_col_name new_col_name column_definition(old_col_name 旧字段 new_col_name 新字段)
country为50字符:change country countryvarchar(50) default 'China'
3.增加字段:add col_name column_definition
4.删除字段:drop col_name
=======================表连接===============================
1、表链接(内链接)
select col1,col2...from table1,table2 where tab1.col3=tab2.col3;
2、表外链接
select col1,col2...from tab1 left join tab2 on tab1.col3=tab2.col3; 左侧为主来匹配
select col1,col2...from tab1 rigth join tab2 on tab1.col3=tab2.col3;
3、子查询
select * from tab1 where col[in][=](select * from tab2 where col...);
栗子:
先创建表:
create table A
(
id smallint(5) unsigned auto_increment,
name varchar(50) not null,
primary key (id)
)engine=innodb default charset=utf8;
desc A;
create table B
(
id smallint(5) unsigned auto_increment,
address varchar(50) not null,
A_id smallint(5) unsigned,
primary key (id)
)engine=innodb default charset=utf8;
desc B;
insert into A(name)
values('张三'),('李四'),('王小');
insert into B(address,A_id)
values('北京',1),('海',3),('南京',10);
查询语句如下:
内连接(推荐使用):select A.name,B.address from A,B where A.id=B.A_id;
左连接,左表是全的:select A.name,B.address from A left jion B on A.id=B.A_id;
右连接,右侧是全的:select A.name,B.address from A rigthjion B on A.id=B.A_id;
子查询:select * from A where id in(select A_id from B where address = '北京');