数据库-mysql-linux下常用命令

安装数据库：# yum -y install mysql mysl-server mysql-devel
查看是否安装成功：# rmp -qa |grep mysql
卸载数据库：# rmp -e mysql 和 #rmp –nodeps mysql

启动数据库服务：# serice mysqld start
设置开机自启：# chkconfig mysqld on
查看开机自启：# chkconfig –list|grep mysqld

修改密码：# mysqladmin -u root -p password ‘root’(把密码设置成root)
数据库配置文件修改：# vi /etc/my.cnf
进入数据库： mysql -u root -p （输入root密码进入）

进入后开始使用sql语言

退出sql：mysql>exit或者quit

创建数据库：mysql>create database mytest;
显示数据库：mysql>show databases;
删除数据库：mysql>drop database mytest;
连接数据库：mysql>use mytest;
显示当前选择数据库：mysql>select database();

显示当前时间：mysql>select now();
显示字符串：mysql>select “welcome to my blog!”;
计算：mysql>select (4*4)/10+25;

创建数据表：mysql>create table mytable(
id int(4) not null primary key auto_increment,
name char(20) not null,
sex int(4) not null default ‘0’,
degree double(16,2));
显示数据表：mysql>show tables;
展示数据表内容：mysql>describe mytable;
删除数据表：mysql>drop table mytable;
给数据表添加列：mysql>alter table mytable add myc1 int(11) not null default 1;
删除数据表列：mysql>alter table mytable drop myc1;

修改表列信息：mysql>alter table mytable change myc1 newmyc1 varchar(255);
修改表名：mysql>alter table mytable rename newmytable;
查看表数据（全部）：mysql>select * from mytable;
查看表数据（选列）：mysql>select mytitle,mycontent from mytable;
插入表数据：insert into mytable values(3, ‘t’,’c’);
插入指定列数据：mysql>insert into mytable(mycontent) values(‘c’);

条件查询where：mysql>select * from mytable where mytitle = ‘t’;
组合条件查询where：mysql>select * from mytable where mytitle = ‘t’ and mycol =’x’;
null判断：select * from mytable where id is null;(is 不是=)
☆distinct（去重查询）：mysql>select distinct from mytable;

select结果按列排序：mysql>select * from mytable where mypages>0 order by myid asc,mypages desc;

select结果limit截取：mysql>select * from mytable where mypages>0 order by myid asc,mypages desc limit 0,2;

从另一张表插入数据：mysql>insert into mytable2(mytitle) select mycontent from mytable1 where id !=1;

更新表数据update：mysql>update mytable set mycol = ‘niceday’ ,mytitle = ‘nicetree’ where myid =3;