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%'
转载于:https://blog.51cto.com/dg123/1286791