MySQL database learning (2) MySQL database after-school exercises (1) DDL and DML exercises

MySQL database after-school exercise 1

1. Question 1

Case:

1. Create the database day01_test01_library

2. Create form books

Field nameField descriptiontype of dataAllow to be emptyonly
b_idBook numberint(11)noYes
b_nameTitlevarchar(50)nono
authorsAuthorvarchar(100)nono
pricepricefloatnono
pubdatePublication dateyearnono
noteDescriptionvarchar(100)Yesno
numin stockint(11)nono

3. Insert records into the books table

1) Specify all field names to insert the first record

2) Insert the second record without specifying the field name

3) Insert multiple records at the same time (all the remaining records)

b_idb_nameauthorspricepubdatenotenum
1Tal of AAADickestwenty three1995novel11
2EmmaTJane lura351993joketwenty two
3Story of JaneJane Tim402001novel0
4Lovey DayGeorge Byron202005novel30
5Old landHonore Blade302010law0
6The BattleUpton Sara301999medicine40
7Rose HoodRichard haggard282008cartoon28

4. Increase the price of novel books by 5.

5. Change the price of the book named EmmaT to 40.

6. Delete the record whose inventory is 0

//1.创建数据库day01_test01_library
create database day_01_test01_library;

The result after running is:

Insert picture description here
//2.创建表格books
create table books(
   bid int(11)  PRIMARY KEY not null,
   b_name varchar(50) not null,
   authors varchar(100) not null,
   price float  not null,
   pubdate year not null,
   note varchar(100),
   num int(11)  not null

);

desc books;

The result after running is:

Insert picture description here
//3.(1)指定所有字段名称插入第一条语句
insert into   books(bid,b_name,authors,price,pubdate,note,num) values  (1,'Tal of AAA','Dickes',23,'1995','novel',11);
select * from  books;

The result after running is:

Insert picture description here
//3.(2)不指定字段名称插入第二条记录
insert  into  books  values(2,'EmmaT','Jane lura',35,1993,'joke',22);
select * from books;

** The result after running is: **

Insert picture description here
//3.(3)同时插入多条数据(剩下的所有数据)
insert into books values (3,'Story of Jane','Jane Tim',40,'2001','novel',0),
                         (4,'Lovey Day','George Byron',20,'2005','novel',30),
                          (5,'Old land','Honore Blade',30,'2010','law',0),
													(6,'The Battle','Upton Sara',30,'1999','medicine',40),
                          (7,'Rose Hood','Richard haggard',28,'2008','cartoon',28);
select * from books;

The result after running is:

Insert picture description here
//4.将小说类型(novel)的书的价格都增加5
update books set  price=price+5 where note='novel';
select * from books;
Insert picture description here
//5.将名称为EmmaT的书的价格改为40
update books set   price=40 where b_name='EmmaT';
select * from books;

The result after running is:

Insert picture description here
//6、删除库存为0的记录
delete  from books where num=0;
select * from books;

The result after running is:

Insert picture description here

2. Question 2

1. Create the database day01_test02_market

2. Create the form customers

Field nametype of data
c_numint(11)
c_namevarchar(50)
c_contactvarchar(50)
c_cityvarchar(50)
c_birthdate

Requirement 3: Move the c_contact field to the back of the c_birth field

Requirement 4: Change the data type of the c_name field to varchar(70)

Requirement 5: Rename the c_contact field to c_phone

Requirement 6: Add the c_gender field to c_name, the data type is char(1)

Requirement 7: Change the table name to customers_info

Requirement 8: Delete the field c_city

//1、创建数据库day01_test02_market

create database day01_test02_market;
Insert picture description here
//2. 创建表格customers
create table customers(
   c_num int(11),
   c_name varchar(50),
   c_contact varchar(50),
   c_city  varchar(50),
   c_birth date
);

desc customers;

The result after running is:

Insert picture description here
//要求3:将c_contact字段移动到c_birth后面
alter table customers  modify c_contact varchar(50) after c_birth;
desc customers;

The result after running is:

Insert picture description here
//要求4:将c_name字段数据类型改为varchar(70)
alter   table customers modify c_name varchar(70);
desc customers;

The result after running is:

Insert picture description here
//要求5:将c_contact 字段改名为c_phone
alter table customers change c_contact  c_phone  varchar(50);
desc  customers;

The result after running is:

Insert picture description here
// 要求6:增加c_gender字段到c_name后面,数据类型为char(1)
alter   table customers add  c_gender   char(1)  after c_name;

The result after running is:

Insert picture description here
//要求7:将表名改为customers_info
alter  table customers  rename  customers_info;

The result after running is:

Insert picture description here
//要求8:删除字段c_city
alter table  customers_info  drop  c_city;
desc customers_info;

The result after running is:

Insert picture description here

Three. Question 3

1. Create the database day01_test03_company

2. Create table offices

Field nametype of data
officeCodeint
cityvarchar(30)
addressvarchar(50)
countryvarchar(50)
postalCodevarchar(25)

3. Create a table employees

Field nametype of data
empNumint(11)
lastNamevarchar(50)
firstNamevarchar(50)
mobilevarchar(25)
codeint
jobTitlevarchar(50)
birthdate
Notevarchar(255)
Sexvarchar(5)

Requirement 4: Modify the mobile field of the employees table behind the code field.

Requirement 5: Rename the birthday field of the employees table to birthday;

Requirement 6: Modify the sex field, the data type is char(1).

Requirement 7: delete the field note;

Requirement 8: Add the field name favorite_activity, and the data type is varchar (100);

Requirement 9: Modify the name of the table employees to employees_info

//1.创建数据库day01_test03_company
create  database  day01_test03_company;

//2.创建表格offices
create  table  offices(
officeCode   int,
city    varchar(30),
address  varchar(50),
country   varchar(50),
postalCode  varchar(25)
);
desc  offices;

The result after running is:

Insert picture description here
//3.创建表格employees
create  table  employees(
  empNum int(11),
  lastName  varchar(50),
  firstName varchar(50),
  mobile   varchar(25),
  code int,
  jobTitle  varchar(50),
  birth date,
  Note  varchar(255),
  Sex  varchar(5)
);
desc employees;

The result after running is:

Insert picture description here
//要求4:将表中employees的mobile字段修改到code字段后面
alter  table  employees  modify  mobile varchar(25)  after  code;
desc  employees;

The result after running is:

Insert picture description here
//要求5:将表中employees的birth字段修改为birthday
alter   table  employees  change  birth   birthday  date ;
desc  employees;

The result after running is:

Insert picture description here
//要求6:修改sex字段,数据类型为char(1)
alter table  employees modify  Sex  char(1);  
desc employees;

The result after running is:

Insert picture description here
//要求7:删除字段note
alter table  employees  drop  note;
desc  employees;

The result after running is:

Insert picture description here
//要求8:增加字段名favoriate_activity,数据类型为varchar(100);
alter  table  employees add  favoriate_activity  varchar(100);
desc   employees;
Insert picture description here
//要求9:将表employees 的名称修改为employees_info
alter  table  employees rename  employees_info;

The result after running is:

Insert picture description here

Four. Question 4

1. Create the database day01_test04db

2. Create a table employee and add records

idnamesexteladdrsalary
10001Zhang Yiyimale13456789000Shaoguan, Guangdong1001.58
10002Liu XiaohongFemale13454319000Jiangmen, Guangdong1201.21
10003Li Simale0751-1234567Foshan, Guangdong1004.11
10004Liu Xiaoqiangmale0755-5555555Shenzhen, Guangdong1501.23
10005Wang YanFemale020-1232133Guangzhou, Guangdong1,405.16

Requirement 3: Query the information of employees whose salary is between 1200 and 1300.

Requirement 4: Query the work number, name, and home address of the employee whose surname is "Liu".

Requirement 5: Change the home address of "Li Si" to "Guangdong Shaoguan"

Requirement 6: Search for employees with "small" in their names

//1.创建数据库day01_test04db
create database  day01_test04db;


//2.创建表格employee,并添加记录
use   day01_test04db;
create table  employee(
    id  int(11),
    name varchar(25),
    sex  char(1),
    tel  varchar(25),
    addr  varchar(25),
    salary  double
)

desc   employee;

insert into    employee values   (10001,'张一一','男','13456789000','广东韶关',1001.58),
                                 (10002,'刘小红','女','13454319000','广东江门',1201.21),
                                 (10003,'李四','男','0751-1234567','广东佛山',1004.11),
                                 (10004,'刘小强','男','0755-5555555','广东深圳',1501.23),
                                 (10005,'王艳','女','020-1232133','广东广州',1405.16);

select * from  employee;

The result after running is:

Insert picture description here


Insert picture description here
//3.要求3:查询薪资在1200到1300之间的员工信息
select  * from  employee  where  salary  between  1200 and 1300;

The result after running is:

Insert picture description here
//4.查询出姓“刘”的员工的工号,姓名,家庭地址
select   id,name,addr   from   employee  where name  like "刘%";

The result after running is:

Insert picture description here
//5.将“李四”的家庭住址修改为“广东韶关”
update employee  set addr='广东韶关'  where  id =10003;
select * from  employee;

Insert picture description here
//6.查询出名字中带"小"的员工
select  * from   employee where  name like '%小%';

Insert picture description here

V. Question 5

1. Create the database day01_test05db

2. Create a form pet

Field nameField descriptiontype of data
namePet namevarchar(20)
ownerPet ownervarchar(20)
speciesspeciesvarchar(20)
sexgenderchar(1)
birthdate of birthyear
deathDate of deathyear

3. Add records

nameownerspeciessexbirthdeath
FluffyharoldCatf20032010
ClawsgwenCatm2004
BuffyDogf2009
FangbennyDogm2000
bowserdianeDogm20032009
ChirpyBirdf2008

4. Add owner_birth of the field owner's birthday.

5. Change the owner of the cat named Claws to kevin

6. Change the owner of the undead dog to duck

7. Query the names of pets without an owner;

8. Query the name, owner, and time of death of the dead cat;

9. Delete the dead dog

10. Query all pet information

//1.创建数据库day01_test05db
create  database day01_test05db;

//2.创建表格pet
create table  pet(
    name  varchar(20),
    owner varchar(20),
    species  varchar(20),
    sex  char(1),
    birth  year,
    death   year
);

desc   pet;

The result after running is:

Insert picture description here
//3.添加数据
insert into pet  values('Fluffy','harold','Cat','f',2003,2010),
                        ('Claws','gwen','Cat','m',2004,null),
                        ('Buffy',null,'Dog','f',2009,null),
												('Fang','benny','Dog','m',2000,null),
												('bowser','diane','Dog','m',2003,2009),
                        ('Chirpy',null,'Bird','f',2008,null);

select * from  pet;

The result after running is:

Insert picture description here
//4.添加字段主人的生日owner_birth
alter table  pet  add owner_birth  date;
desc  pet;

The result after running is:

Insert picture description here
//5.将名称为Claws的猫的主人改为kevin

update    pet  set  owner='kevin' where  name='Claws';
select * from  pet;

The result after running is:

Insert picture description here
//6.将没有死的狗的主人改为duck
update   pet  set  owner='duck'  where species ='Dog' and  death is null;
select  * from  pet;

The result after running is:

Insert picture description here
//7.查询没有主人的宠物的姓名
select  name  from  pet where  owner is null;

The result after running is:

Insert picture description here
//8. 查询已经死了的cat的姓名,主人,以及去世时间;
select  name,owner,death from  pet where  death is not null and species ='Cat';

The result after running is:

Insert picture description here
//9.删除已经死亡的狗
delete   from pet  where  death  is not null and species='Dog';
select * from pet;

The result of the operation is:

Insert picture description here

to sum up

This section mainly focuses on 1. DDL (database definition language) definition library, table structure, etc., including create, drop, alter; 2. DML (data manipulation language), add, delete, modify, and check data, including insert, delete, update, select statements Exercise. Focus on mastering the grammar of these sql statements, which is very important for subsequent database operations.

Insert picture description here