1. 备份数据库中的表。
1.1 选择备份路径,执行备份。
退出mysql客户端并切换到D盘,d:
mysqldump -u root -p employee > myemployee.sql
注: employee 是数据库名, myemployee.sql 是备份文件名
确认成功后,尝试删除数据库
2. 删除数据库
mysql -u root -p
mysql> drop database 数据库名;
mysql> drop database employee;
3. 尝试恢复数据库。
3.1 先创建数据库
mysql -u root -p
passwd: xxxxxx
mysql> create database employee;
mysql> exit
d:\> mysql -u root -p employee < myemployee.sql 还原数据库中的表
password: xxxxxx
d:\> mysql -u root -p
password: xxxxxx
mysql> show databases; 显示数据库
mysql> use employee; 打开数据库
mysql> desc employee_tbl; 描述表结构
mysql> show tables; 显示表名
3.2 一条命令执行查询的方法:
D:\>mysql -u root -pzypxxx -e "use employee; select coalesce(name,'总数:'), SUM(signin) as sigin_total from employee_tbl group by name with rollup;"
4. 额外创建一个表user_into
mysql> use employee;
mysql> create table user_info (
-> `id` int(11) NOT NULL,
-> `name` varchar(10) NOT NULL DEFAULT '',
-> `age` int(3) NOT NULL,
-> `sex` varchar(2) NOT NULL,
-> `salary` float(9,2) NOT NULL,
-> PRIMARY KEY(`id`));
mysql> insert into user_info values
-> ('1', '小明', 23, '男', 5200.22),
-> ('2', '小王', 24, '男', 5300.22),
-> ('3', '小美', 26, '女', 6200.32);
5. 更新表结构
mysql>alter table user_info add column `Addr` varchar(100) NOT NULL after `salary`;
mysql>update user_info set Addr='奉贤紫屿培训学校' where name='小明';
6. 联合查询连接功能
mysql> select a.name, a.age, a.salary, b.signin from user_info a inner join employee_tbl b on a.name=b.name;
7. 总结
- 库操作: 增删改查
增:create database 数据库名字;
删:drop database 数据库名字;
改: use 数据库名字; 打开数据库
查:show databases; - 表操作: 增删改查
增:create table 表名(字段名 字段类型 字段属性, 字段名2.....);create table aa (`id` int(10) NOT NULL Auto_increment, `name` varchar(20) NOT NULL);
删:drop table aa;
改:alter table aa ADD COLUMNaddr
varchar(10) NOT NULL AFTERname
;alter table aa MODIFY COLUMN `salary` float(9,2);
alter table aa DEL COLUMN `addr`; 改记录
update aa set salary=5300.33 where name='小明';
查:desc aa; 查表结构
查记录: select 字段1, 字段2 .... 字段N FROM 表名;select * from aa; select * from aa where id=1; select * from aa order by id desc; select * from aa order by id asc; select * from aa group by name; select name, count(*) from aa group by name; select coalesce(name, "总数:"), SUM(signin) from aa group by name with rollup;
8.数据表的备份和还原
8.1 备份数据库:
mysqldump -u root -p 数据库名字 > 备份文件.sql
8.2 还原数据表:
mysql -u root -p -e "create database bbs;" mysql -u root -p bbs < 备份文件.sql
mysql -u root -p -e "create database bbs"
mysql -u root -p bbs < 备份文件.sql