MySQL数据库简单练习题

简单巩固：

create table student(

id int(10) primary key auto_increment,
name varchar(20) unique,
age int(5) default 16,
sex char(2) not null


);
show databases;
create database libry;
use libry;
show tables;
create table books(
b_id int(11) primary key,
b_name varchar(50),
Authers varchar(100),
Price Float,
pubdate Year,
Note varchar(100),
Num int(11)
);
desc libry;
insert into books(b_id,b_name,Authers,Price,pubdate,Note,Num) values(1,'Tal of AAA','Dickes',23,1995,'novel',11);
insert into books(b_id,b_name,Authers,Price,pubdate,Note,Num) values(2,'EmmaT','Jane lura',35,1993,'Joke',22);
insert into books(b_id,b_name,Authers,Price,pubdate,Note,Num) values(3,'Story of Jane','Jane Tim',40,2001,'novel',0);
insert into books(b_id,b_name,Authers,Price,pubdate,Note,Num) values(4,'Lovey Day','George Byron',20,2005,'novel',30);
insert into books(b_id,b_name,Authers,Price,pubdate,Note,Num) values(5,'Old land','Honore Blade',30,2010,'Law',0);
insert into books(b_id,b_name,Authers,Price,pubdate,Note,Num) values(6,'The Battle','Upton Sara',30,1999,'medicine',40);
insert into books(b_id,b_name,Authers,Price,pubdate,Note,Num) values(7,'Rose Hood','Richard haggard',28,2008,'cartoon',28);
select * from books where Note='novel'like update books set Price+5;




create table books(
b_id int(11) primary key,
b_name varchar(50),
Authers varchar(100),
Price Float,
pubdate Year,
Note varchar(100) not null,
Num int(11)
);






show databases;
create database employee;
use employee;
show tables;
create table einfo(
id int(11) not null primary key auto_increment,
name varchar(50) not null,
sex char(2) default '女',
tel varchar(50),
addr varchar(100),
salary Float
);
insert into einfo(id,name,sex,tel,addr,salary) values(10001,'张一一','男','13456789000','广东韶关',1001.58);
insert into einfo values(10002,'刘小红','女','13454319000','广东江门',1201.21);
insert into einfo values(10003,'李四','男','0751-1234567','广东佛山',1004.11),(10004,'刘强','男','0755-5555555','广东深圳',1501.23),(10005,'王艳','女','020-1232133','广东广州',1405.16);




三、按照以下题目要求实现操作，并将sql语句记录在记事本中。
create table customers(
c_num int(11) primary key not null unique auto_increment,
c_name varchar(50),
c_contact varchar(50),
c_city varchar(50),
c_birth datetime not null


);
alter table customers modify c_contact varchar(50) after c_birth;
alter table customers change c_name c_name varchar(70);
alter table customers change c_contact c_phone varchar(50);
alter table customers add c_gender char(1) not null;
alter table customers rename customers_info;


alter table customers_info drop c_city;




六、 按照以下题目要求实现操作，并将sql语句记录在记事本中。
show databases;
create database wangchuanlu;
use wangchuanlu;
show tables;


create table department(
depid int(10) primary key,
depname varchar(20),
depinfo varchar(100)
);


create table employee(
empid int(10) primary key,
name varchar(5),
sex char(2),
title varchar(20),
birthday date,
depid int(10)  
);


create table salary(
empid int(10) primary key,
basesalary  Float,
titlesalary Float,
deduction Float
);


insert into department(depid,depname) values(111,'生产部');
insert into department(depid,depname) values(222,'销售部');
insert into department(depid,depname) values(333,'人事部');




insert into employee(empid,name,sex,title,birthday,depid) values(1001,'张三','男','高级程师','1975-01-01',111);
insert into employee(empid,name,sex,title,birthday,depid) values(1002,'李四','女','助工','1985-01-01',111),(1003,'王五','男','工程师','1978-11-11',222),(1004,'赵六','男','工程师','1979-01-01',222);




insert into salary(empid,basesalary,titlesalary,deduction) values(1001,2200,1100,200),(1002,1200,200,100),(1003,1900,700,20