MySQL关系数据库
配置文件my.ini
SQL常用的命令
查看DB
show databases;
添加DB gc1
create database gc1;
删除
drop database gc1;
table 的操作同上
describe table; //描述table的详细信息
1.增加列
alter table [table_name] add [column_name] [data_type] [not null] [default]
eg:
alter table account add c1 int(11) not null default 1;
delete:
alter table account drop c1;
2.修改列的信息
alter table [table_name] add [old_column_name] [new_column_name][data_type] ;
修改表名
alter table [table_name] rename [new_name];
3.查看或者插入表数据。
select * from table_name; //查看表的所有信息
select col_name1,col_name2,... from table_name; 查看某几列的信息。
insert into table_name values (值1,值2,。。。); //所有列的数值都需要。
insert into table_name (col1_name1,col2_name) values(值1,值2);//插入指定列
4.条件语法where
组合条件 and,or; ();
select * from table_name where col_name 运算符 值;
//运算符 between like =,>,<
eg:
select * from book where title ='t' and id >1;
null 的判断 is null ,is not null;
5.distinct的关键字
去除重复的数据。
6.order by
select * from table_name [where 语句] order by col_name[asc/dsc];
select * from table_name [where 语句] order by col_name[asc/dsc],col2_name [asc/dsc];
不加asc/dsc 默认为asc。
7.使用limit截取查询结果
select * from table_name [where 语句] [order by 语句] limit [offset] rowcount;
offset为可选项,第一条记录为0,rowCount 从offset位置开始,获取的记录条数。
limit roCount=limit 0,riwCount;
8.insert into 与select的组合使用 数据迁移
nsert into table_name select col1,col2 from table2_name;
insert into table_name (col1_name1,col2_name) select col1,col2 from table2_name;//插入指定列
9.更新表数据
update table_name set col_name =xxx [where 语句];
修改多列:
update table_name set col1=xxx,col2 =xxx...[where 语句];
10.
where 语句中 in
select * from table_name where col_name in(value1,value2...);
select * from table_name where col_name in(select col1_name from table2_name);
where 语句中 between,not between;
select * from table_name where col_name between vla1 and val2;
where 语句中的like ;模糊匹配
select * from table_name where col_name like pattern;
pattern :匹配模式 ‘abc’ %abc' %通配符