学习完mysql语句,可以做一下综合练习巩固一下
这个练习主要练习点从创建数据库表到对数据的增删改查
目录
----【DDL语句】-----------------------------------------------------------------------------
五、课外知识补充-修改字符集(数据库5.6版本下测试有效,其他版本自行测试)
2、在usersalary表中,根据user表的人员信息,给他们插入相应的工资数据,数据不做要求
1、删除id是1的数据(注意有两个表相关联,所以要做考虑如何删除数据)
2、将usersalary的表的所有员工的基本工资改成3000元;总经理的工资改成4000元
单表查询-- 1、查询id是3的员工信息select * from user where id=3;
-- 2、使用模糊查询,查询员工姓名是三个字的员工信息select * from user where username like '___';
-- 6、查询员工的姓名和每个员工的工资,并按工资降序排序。
----【DDL语句】-----------------------------------------------------------------------------
一、创建库
1、创建一个名称为db_system的数据库
#创建数据库
create database db_system;
二、创建表
2、在该数据库下创建两张表,具体要求如下
字段 | 类型 | 约束 | 备注 |
---|---|---|---|
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+右外连接实现)
结束!感谢大家的阅读,大家有好的题目或者建议也可以在评论区留言