mysql练习

MySql 实践

基本命令

1, 查询当前访问数据库的用户信息

select user();
show processlist;

2, 查询数据库服务器中的数据库列表

show databases;

3, 使用数据库 scott

use scott;

4, 查询当前数据库的名称

select database();

5, 查询 scott 数据库的创建语句

show create database scott; // 以表格方式显示
show create database scott \G // 以行的方式显示

6, 查询数据库中的表列表

show tables;

7, 查询 scott.emp 表的创建语句

show create table emp \G
show create table emp;

8, 查询 scott.emp 表的结构描述

desc emp;

函数相关

1, 查询当前日期

select current_date(); // 不加括号也可以, 考虑到这是一个函数, 还是加上括号了

2, 查询当前日期加时间

select current_timestamp();

3, 查看距离元旦还有几天

 select datediff('2019-1-1', '2018-6-29');

CRUD

使用 bit_student 数据库中的 student 表

1, 查询学生的数量

 select count(*) from student;

2, 查询学生编号 , 姓名 , 总分 , 并按照总分由高到低排序

select id, name, math+english+chinese as total
from student
order by total desc; // desc 降序

3, 查询学生编号 , 姓名 , 总分 , 并按照编号升序排序

select id, name, math+english+chinese as total
from student
order by id; // 默认升序

4, 查询各科的平均分

select avg(math), avg(english), avg(chinese)
from student; // avg() 求平均值的函数

5, 查询语文成绩最高的学生的编号 , 姓名 , 语文成绩

select id, name, chinese
from student
where chinese=(select max(chinese) from student); // max() 求最大值的函数

使用 scott 数据库

1, 查询和 FORD 同一部门的员工信息

 select * from emp 
 where deptno=(
 select deptno from emp
 where ename='FORD'
 );

2, 查询和 10 号部门的工作相同的员工的名字 , 岗位 , 工资 , 部门编号 , 但是不包括 10 号部门自己

 select ename, job, sal, deptno
 from emp
 where job in(
 select distinct job from emp where deptno=10)
 and deptno<>10; // distinct 如果结果中有完全相同的行，就去除重复行

3, 查询和 FORD 的部门和岗位完全相同的员工 , 不包括 FORD 自己

select * from emp
where deptno=(
select deptno from emp where ename='FORD')
and
job in(
select job from emp where ename='FORD')
and
ename<>'FORD';

4, 查询高于自己部门平均工资的员工信息

 select * from emp
 where sal>(select avg(sal) from emp);

案例

1, 创建一张学生信息表 , 包含编号 , 姓名 , 生日 , 性别 , 邮箱 ; 其中编号为主键

create table student_info(
id int(6) unsigned zerofill not null primary key comment '编号',
name varchar(20) not null default ' ' comment '姓名',
birthday date default null comment '生日',
sex char(2) not null default '保密' comment '性别',
email varchar(20) default null comment '邮箱'
);

• 为学生信息表增加证件照字段

alter table student_info add ID_photo TEXT; // 设置为 TEXT 类型, 存储图片路径

• 增加家庭住址字段 , 默认为空字符串

 alter table student_info add home_address varchar(50) default ' ';

• 往学生信息表中插入三条记录

insert into student_info (id, name, birthday, sex, email, home_address)
values (1, '钢铁侠', '1970-1-1', '男', 'ironman@qq.com', '美国纽约');

insert into student_info (id, name, birthday, sex, email, home_address)
values (2, '美国队长', '1870-2-2', '男', 'captain@qq.com', '美国纽约布鲁克林区');

insert into student_info (id, name, birthday, sex, email, home_address)
values (3, '绿巨人', '1970-3-3', '男', 'hulk@qq.com', '美国-俄亥俄州-代顿');

• 更新学生信息表中的数据

update student_info 
set home_address='美国-纽约长岛' 
where id=1; // 把编号为 1 的学生住址改为 '美国-纽约长岛'

• 删除指定学生表中的数据

delete from student_info; // 删除整个表的数据，但是表的结构还存在
truncate table student_info; // 这个指令也把整个表记录删除

上述两种方法删除整表的区别:

效果一样，truncate 速度快

delete 可以带 where 条件，删除更加灵活

delete 可以返回被删除的记录数，而 truncate 返回 0

推荐使用delete

• 为邮箱字段添加/删除唯一键

alter table student_info add unique(email); // 添加唯一键约束
alter table student_info drop index email; // 删除唯一键约束

唯一键约束添加后，实际上建立了一个索引，将该索引删除后，就等于删除了联合唯一约束

2, 在 scott 数据库中创建一张表 emp_backup , 和 emp 表结构相同 , 数据相同

create table emp_backup like emp;
insert into emp_backup select * from emp;

3, 在 emp_backup 表中快速批量插入数据 , 使数据超过 100 条

可以多次使用 insert into emp_backup select * from emp;

很快数据就超过 100 条

4, 删除 emp_backup 表中的重复记录

create table emp_backup_tt like emp_backup;
insert into emp_backup_tt select distinct * from emp_backup; 
// distinct 排除重复记录
drop table emp_backup;
alter table emp_backup_tt rename emp_backup;

5, 清空 emp_backup 中的数据

delete from emp_backup; // 删除整个表的数据，但是表的结构还存在
truncate table emp_backup; // 这个指令也把整个表记录删除

6, 备份和还原 scott 数据库

C:\Users\RJM>mysqldump -u root -p scott > I:\04_数据库\sql\scott.sql
Enter password: ****

还原

先删除原来的数据库

再创建一个空的数据库 , 最好和原来的数据库名字一样

然后恢复

source I:\04_数据库\sql\scott.sql