当前位置:网站首页>Database basic work: database query and constraint [exercise]

Database basic work: database query and constraint [exercise]

2020-12-08 11:34:50 osc_ jklrr90y

 Insert picture description here

(1). Write sql sentence , Query all ages greater than 20 Year old employees (2 branch )

(2). Write sql sentence , Query all ages less than 25 A year old female employee (3 branch )

(3). Write sql sentence , Count the number of male and female employees (3 branch )

(4). Write sql sentence , Get employee information in reverse order of age (3 branch )

(5). Write sql sentence , Get which name in the employee has duplicate name phenomenon (3 branch )

(6). Write sql sentence , Check all employees surnamed Zhang (3 branch )

(7). Write sql sentence , The address is Beijing 3 Bar record (3 branch )

(8). Write sql sentence , Query the total number of employees (3 branch )

(9). Write sql sentence , Insert a record into the table (2 branch )

(10). Write sql sentence , Change the address of employee Zhang Si to Nanjing (2 branch )

(11). Write sql sentence , Delete older than 24 Female employee (2 branch )

cmd/Java The connection to the database is in
 Insert picture description here

Basic operation :

cd :change directory Abbreviation . There are many tables in the database 
show databases;
show tables;
desc employee;
 
drop database hzyc98;
drop table employee;

create table employee (Name char(30) not null,Sex char(8),Age int, Address char(30));

【 constraint condition 】
 Create table : constraint ( The default value is 、 Non empty 、 unique )
PRI: Primary key primary( A table has only one primary key , A primary key is a column assigned to the header (Field) Of )
primary key
not null
unique
default 

Add list

insert into employee (Name,Sex,Age , Address ) value(' Zhang San ', ' Woman ',19,' Beijing ' );
insert into employee (Name,Sex,Age , Address ) value(' Li Si ', ' male ',20,' Shanghai ' );
insert into employee (Name,Sex,Age , Address ) value(' Wang Wu ', ' Woman ',25,' Guangzhou ' );
insert into employee (Name,Sex,Age , Address ) value(' Wang Wu ', ' male ',22,' Beijing ' );
insert into employee (Name,Sex,Age , Address ) value(' Xue Liu ', ' Woman ',20,' Beijing ' );
insert into employee (Name,Sex,Age , Address ) value(' Zhao Qi ', ' male ',28,' Shanghai ' );
insert into employee (Name,Sex,Age , Address ) value(' Zhang Si ', ' Woman ',23,' Beijing ' );

The answer is for reference only :

Notice there's one here update Cannot add from, Fallible

1.  Write sql sentence , Query all ages greater than 20 Year old employees (2 branch )
Select * from employee where age >20;2.  Write sql sentence , Query all ages less than 25 A year old female employee (3 branch )
Select * from employee where age <25 and sex = ‘ Woman ’;3.  Write sql sentence , Count the number of male and female employees (3 branch )
Select sex,count(*) from employee group by sex;4.  Write sql sentence , Get employee information in reverse order of age (3 branch )
Select * from employee order by age desc;5.  Write sql sentence , Get which name in the employee has duplicate name phenomenon (3 branch )
Select name from employee group by name count(name)>1;6.  Write sql sentence , Check all employees surnamed Zhang (3 branch )
// Fuzzy query like
Select * from employee where name like ‘ Zhang %;7.  Write sql sentence , The address is Beijing 3 Bar record (3 branch )
select * from employee where address = ' Beijing 'order by address asc limit 0,3;8.  Write sql sentence , Query the total number of employees (3 branch )
Select count(name) from employee;9.  Write sql sentence , Insert a record into the table (2 branch )
Insert into employee (Name ,Sex ,  Age , Address) value (' Zhang Si ', ' Woman ',23,' Beijing ');10. Write sql sentence , Change the address of employee Zhang Si to Nanjing (2 branch )
Update employee set address = ‘ nanjing ’ where name = ‘ Zhang Si ’;11. Write sql sentence , Delete older than 24 Female employee (2 branch )
Delete from employee where age > 24 and sex = ‘ Woman ’;

版权声明
本文为[osc_ jklrr90y]所创,转载请带上原文链接,感谢
https://chowdera.com/2020/12/202012081134252480.html