mysql running_mysql常用命令

1. 导出数据：

mysqldump --opt test > mysql.test

即将数据库test数据库导出到mysql.test文件，后者是一个文本文件

如：mysqldump -u root -p123456 --databases dbname > mysql.dbname

就是把数据库dbname导出到文件mysql.dbname中。

2. 导入数据:

mysql -u root -p123456 < mysql.dbname。

mysqlimport -u root -p123456 < mysql.dbname。

3. 将文本数据导入数据库:

文本数据的字段数据之间用tab键隔开。

use test;

load data local infile "文件名" into table 表名;

1:使用SHOW语句找出在服务器上当前存在什么数据库：

mysql> SHOW DATABASES;

2:2、创建一个数据库MYSQLDATA

mysql> CREATE DATABASE MYSQLDATA;

3:选择你所创建的数据库

mysql> USE MYSQLDATA; (按回车键出现Database changed 时说明操作成功!)

4:查看现在的数据库中存在什么表

mysql> SHOW TABLES;

5:创建一个数据库表

mysql> CREATE TABLE MYTABLE (name VARCHAR(20), sex CHAR(1));

6:显示表的结构：

mysql> DESCRIBE MYTABLE;

7:往表中加入记录

mysql> insert into MYTABLE values ("hyq","M");

8:用文本方式将数据装入数据库表中(例如D:/mysql.txt)

mysql> LOAD DATA LOCAL INFILE "D:/mysql.txt" INTO TABLE MYTABLE;

9:导入.sql文件命令(例如D:/mysql.sql)

mysql>use database;

mysql>source d:/mysql.sql;

10:删除表

mysql>drop TABLE MYTABLE;

11:清空表

mysql>delete from MYTABLE;

12:更新表中数据

mysql>update MYTABLE set sex="f" where name='hyq';

posted on 2006-01-10 16:21 happytian 阅读(6) 评论(0) 编辑 收藏 收藏至365Key

13：备份数据库

mysqldump -u root 库名>xxx.data

14：例2：连接到远程主机上的MYSQL

假设远程主机的IP为：110.110.110.110，用户名为root,密码为abcd123。则键入以下命令：

mysql -h110.110.110.110 -uroot -pabcd123

(注:u与root可以不用加空格，其它也一样)

15.修改密码

mysqladmin -u root -p password 123456

回车出现

Enter password: ( 注:这是叫你输入原密码. 刚安装时密码为空,所以直接回车即可)

此时mysql 中账号 root 的密码 被改为 123456 安装完毕

--列出指定的列select name, owner form pet

--直接进行算术运算，对字段起别名select sin(1+2) as sin

--where条件select * from pet where (birth>'1980' and species='dog') or species='bird'

--对null的条件select * from pet where sex is not null

--所有名字第四位是n的宠物信息是select * from pet where owner like '___n%'

--所有主人名叫gwen或benny的宠物select * from pet where owner in ('gwen' , 'benny')

--查询出生日期在90年代是宠物，相当与>= and   <=

select * from pet where birth between '1990' and '1999'

--按主人姓名排序，相同的按宠物姓名倒序排列select * from pet order by owner, name desc

--查询性别为公的宠物，按生日倒序排列select * from pet where sex='m' order by birth desc

--char_lenngth()返回的字符的长度，length()返回字节长度SELECT owner,length(owner),char_length(owner) FROM pet p;

--列出养有宠物狗的人名select distinct owner from pet where species='dog'

--用两种方法查询出所有狗和猫的名字、出生年份、出生月份select name, left(birth,4) as year, mid(birth, 6, 2) as month from pet

where species='dog' or species='cat'

select name, year(birth) as year, month(birth) as month from pet

where species in('dog','cat')

--查询所有名字中存在字母'e'的人，将他们养的宠物按类别、年龄排序select name, species, birth

from pet

where owner like '%e%'

order by species,birth desc

--数字函数select round(2.345,2), truncate(2.345,2), mod(323,5)

--日期函数select now(), curdate(), curtime()

select adddate('2007-02-02', interval 31 day)

--求出所有宠物的年龄select name,birth,

truncate(datediff(now(),birth)/365,0) as age1,

year(now())-year(birth) - (dayofyear(birth)>dayofyear(now())) as age2

from pet

--分组函数select min(birth),max(birth),avg(birth),count(*),count(sex),

sum(birth)

from pet

--每种宠物各有几只select species,count(*)

from pet

group by species

--查询年龄最大的宠物的信息select * from pet where birth =

(select max(birth) from pet)

--每年各出生了几只宠物select year(birth), count(*) from pet group by year(birth)\

--鸟和猫的性别比例select species, sex, count(*)

from pet

where species in ('cat','bird')

group by species, sex

--各种宠物年龄的和select species, sum(truncate(datediff(now(),birth)/365,0)) as SumAge

from pet

group by species

--数量大于1的宠物种类select species, count(*) as c

from pet

group by species

having c>=2

--基本双表关联select a.name,a.species, a.sex,b.date, b.type, b.remark

from pet a,event b

where a.name = b.name

--查询宠物产仔时的年龄select a.name, a.species,

truncate(datediff(b.date,a.birth)/365,0) as age

from pet a,event b

where a.name = b.name and b.type='litter'

--90年代出生的狗的事件列表select a.name,birth,species,sex,date,type,remark

from pet a,event b

where a.name=b.name and birth between '1990' and '1999'

and species='dog'

--活着的宠物按发生的事件类型分组，看各种事件发生的次数select type, count(*)

from pet a, event b

where a.name=b.name and a.death is null

group by type

--记录的事件数量超过1条的宠物信息select a.name,species,sex,count(*)

from pet a, event b

where a.name = b.name

group by b.name

having count(*)>=2

--列出发生了两件事情的宠物的事件记录信息select a.name,type,date,remark,b.species,b.sex,b.owner

from event a, pet b

where a.name=b.name and

b.name in

(

select name

from event

group by name

having count(*)=2

)

--插入语句insert into pet (name,species,birth)

values ('KKK','snake','2007-01-01');

insert into pet

values ('KK','Diane','cat','f',null,null);

insert into pet set name='k',owner='Benny'

--更新语句update pet set species='snake',sex='f',birth=now()

where name='k'

--将事件表中生日的日期，更新到pet表中相应宠物的birth字段update pet a

set birth = (

select date

from event b

where a.name=b.name and b.type='birthday'

)

where a.name in (

select name

from event

where type='birthday'

)

--删除语句delete from pet where name like 'k%'