【Mysql 基础练习】一套式的mysql数据库练习———从创建数据库表到对数据的增删改查

学习完mysql语句，可以做一下综合练习巩固一下

这个练习主要练习点从创建数据库表到对数据的增删改查

目录

学习完mysql语句，可以做一下综合练习巩固一下

这个练习主要练习点从创建数据库表到对数据的增删改查

----【DDL语句】-----------------------------------------------------------------------------

一、创建库

1、创建一个名称为db_system的数据库

二、创建表

2、在该数据库下创建两张表，具体要求如下

三、修改表

1、在上面员工表的基本上增加一个image列，类型是blod，长度255。2、修改job列，使其长度为60。3、删除gender列。4、表名salary改为usersalary。5、修改表的字符集为utf8;6、列名name修改为username

四、复制和删除表

1、将user表复制成user2表

2、删除user2表

五、课外知识补充-修改字符集（数据库5.6版本下测试有效，其他版本自行测试）

【DML语句】

背景说明：下面的操作在表结构如下两个图来操作的：

一、增

1、在user增加几条5名员工数据数据，数据任意编写

2、在usersalary表中，根据user表的人员信息，给他们插入相应的工资数据，数据不做要求

​编辑

二、删

1、删除id是1的数据（注意有两个表相关联，所以要做考虑如何删除数据）

三、改

1、将Id是2的数据的人员的职位改成“”“总经理”

2、将usersalary的表的所有员工的基本工资改成3000元；总经理的工资改成4000元

四、查

单表查询-- 1、查询id是3的员工信息select * from user where id=3;

-- 2、使用模糊查询，查询员工姓名是三个字的员工信息select * from user where username like '___';

-- 5、统计各个员工的总工资（工资=基本工资+绩效工资）

-- 6、查询员工的姓名和每个员工的工资，并按工资降序排序。

-- 7、查询工资在5000以上的员工相关信息

五、系统函数：

---

---

----【DDL语句】-----------------------------------------------------------------------------

一、创建库

1、创建一个名称为db_system的数据库

#创建数据库
create database db_system;

二、创建表

2、在该数据库下创建两张表，具体要求如下

员工表 user

字段	类型	约束	备注
id	整形	主键，自增长	id
NAME	字符型	非空	姓名
gender	字符	非空	性别
birthday	日期型		生日
entry_date	日期型	非空	入职时间
job	字符型	非空	职位

#创建user表
CREATE TABLE user  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `gender` varchar(2) NOT NULL,
  `birthday` datetime(6),
  `entry_date` datetime(6) NOT NULL,
  `job` varchar(25) NOT NULL,
  PRIMARY KEY (`id`)
);

 

员工绩效表 salary

员工绩效表 salary
字段	类型	约束	备注
id	整形	主键，自增长	id
userId	字符型	非空	外键，关联的是user表的id字段 用户id
baseSalary	小数	非空	基本工资
month	整数	非空	月份
allowances	小数	非空，默认为0	补贴

#创建salary表
CREATE TABLE salary(
  `id` int(0) NOT NULL AUTO_INCREMENT, -- 非空自增约束
  `userId` int(0) NOT NULL,
  `baseSalary` decimal(10, 2) NOT NULL,
  `month` int(0) NOT NULL,
  `allowances` decimal(0, 0) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`), -- 添加主键
  CONSTRAINT `fk_user_id` FOREIGN KEY (`userId`) REFERENCES `db_system`.`user` (`id`) -- 添加外键
);

三、修改表

1、在上面员工表的基本上增加一个image列，类型是blod，长度255。
2、修改job列，使其长度为60。
3、删除gender列。
4、表名salary改为usersalary。
5、修改表的字符集为utf8;
6、列名name修改为username

# 1、在上面员工表的基本上增加一个image列，类型是blod。
ALTER TABLE user add image blob(255);
# 2、修改job列，使其长度为60。
ALTER TABLE user modify job VARCHAR(60);
# 3、删除gender列。
ALTER TABLE user drop column gender;
# 5、修改表的字符集为utf8;
ALTER TABLE user convert to character set utf8;
# 6、列名name修改为username
ALTER TABLE user change name  username VARCHAR(50);
# 4、表名salary改为usersalary。
rename table salary to usersalary;

操作完的表如下：

四、复制和删除表

1、将user表复制成user2表

写法一：分两个步骤)

create table user2 like user;

insert into user2 select * from user;

写法2：合成一个步骤

create table user2  select * from user;

2、删除user2表

drop table user2;

五、课外知识补充-修改字符集（数据库5.6版本下测试有效，其他版本自行测试）

use db_system; -- 切换数据库
select VERSION(); -- 查看数据库版本
----------------查看字符集-------------------------
      --------库 》 表 》列-----------------
1.查看MYSQL数据库服务器和数据库字符集
show variables like '%character%';

2.查看表中所有列的字符集

语法：show full columns from 表名;

例：查看db_system 中的 user的所有字段字符集

show full columns from  `user`;

---------------设置字符集--------------------

设置字符集（大致分成三种方式）:
一是在创建表的时候设置字符集，
二是表建成之后修改字符集
三是在在mysql安装目录下找到my.ini文件下添加默认字符集（这个方法不在这里介绍，感兴趣的同学去百度一下）

1.创建时指定字符集

创建库的时候指定字符集：

语法：create database 库名 default character set=字符集；
例：create database 库名 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

创建表的时候指定字符集：

语法：create table 表名（属性）default character set = 字符集；
例：CREATE TABLE mytable(
    id varchar(40) NOT NULL default '',
    userId varchar(40) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.修改字符集

修改库的字符集
--------库 》 表 》列-----------------

语法：alter database 库名 default character set 字符集;

例：修改 db_system  中的默认字符集

alter database db_system  default character set utf8;

修改表的字符集

语法：alter table 表名 convert to character set 字符集;

例：修改 db_system  中的 user 表的 字符集

alter table `user` convert to character set latin1;

修改字段的字符集

语法：alter table 表名 modify 字段名 字段属性 character set 字符集;

例：修改 db_system  中的 user 表的 name这个字符段的 字符集

alter table  	`user` modify  `name` varchar(50) character set utf8;

---

-----【DML语句】--------------------------------------------------------------

背景说明：下面的操作在表结构如下两个图来操作的：

一、增

1、在user增加几条5名员工数据数据，数据任意编写

# 在user增加几条5名员工数据数据，数据任意编写
insert into  user(username,birthday,entry_date,job)
values('张总1','1992-01-02','2021-05-17','总经理'),
('张总2','1992-01-02','2021-05-17','总经理'),
('张总3','1992-01-02','2021-05-17','总经理'),
('张总4','1992-01-02','2021-05-17','总经理'),
('张总5','1992-01-02','2021-05-17','总经理')
;

2、在usersalary表中，根据user表的人员信息，给他们插入相应的工资数据，数据不做要求

insert into  usersalary(userId,baseSalary,month,allowances)
values(1,2000,5,2500),
(2,2000,5,2500),
(3,2000,5,2500),
(4,2000,5,2500),
(5,2000,5,2500)

二、删

操作的两张表如下：

1、删除id是1的数据（注意有两个表相关联，所以要做考虑如何删除数据）

因为是有关联的表，所以第一种，如果主表删除数据，从表从表的数据关联的数据也相应的删除，那么可以设置删除为，如图所示：

接下俩就可以用sql语句删除数据了：

delete  from user where id=1;

三、改

1、将Id是2的数据的人员的职位改成“”“总经理”

2、将usersalary的表的所有员工的基本工资改成3000元；总经理的工资改成4000元

#1、将Id是2的数据的人员的职位改成“”“总经理”
update user set job='总经理' where id=2;

#2、将usersalary的表的所有员工的基本工资改成3000元；总经理的工资改成4000元
update usersalary set baseSalary=3000;

四、查

单表查询
-- 1、查询id是3的员工信息
select * from user where id=3;

-- 2、使用模糊查询，查询员工姓名是三个字的员工信息
select * from user where username like '___';

-- 
-- 3、连接查询，对两张表进行自然连接查询
select * from user NATURAL join usersalary;
--
-- 4、查询user表按职位分组进行查询
select job from user group by job;

-- 5、统计各个员工的总工资（工资=基本工资+绩效工资）

select baseSalary+allowances from usersalary ;

-- 1、查询id是3的员工信息
select * from user where id=3;

-- 2、使用模糊查询，查询员工姓名是三个字的员工信息
select * from user where username like '___';
-- 
-- 3、连接查询，对两张表进行自然连接查询
select * from user NATURAL join usersalary;
-- 
-- 4、查询user表按职位分组进行查询
select job from user group by job;

-- 5、统计各个员工的总工资（工资=基本工资+绩效工资）

select baseSalary+allowances from usersalary ;

-- 6、查询员工的姓名和每个员工的工资，并按工资降序排序。

select username,baseSalary+allowances 
from usersalary,user 
where usersalary.userId=user.id;

-- 7、查询工资在5000以上的员工相关信息

​select username,baseSalary+allowances  salary
from usersalary,user 
where usersalary.userId=user.id and  salary>5000;

​

五、系统函数：

1、查询系统版本：select @@version;

六、MYSQL的一些注意事项

注意1：left join 语句中，左表的过滤要用给where，right join 中右表的过滤用on 才是精确的

注意2：全连接( Oracle数据库支持full join,mysql是不支持full join的,但仍然可以同过左外连接+ union+右外连接实现)

结束！感谢大家的阅读，大家有好的题目或者建议也可以在评论区留言