mysql实验操作 mysql实操题

#练习1：创建一个库并插入数据
#设置数据库的编码utf8
SET NAMES UTF8;
#丢弃如果存数据库dangdang
DROP DATABASE IF EXISTS dangdang;
#创建数据库编码utf8
CREATE DATABASE dangdang CHARSET=UTF8;
#使用数据库
USE dangdang;
#创建数据表
CREATE TABLE book(
bid INT PRIMARY key AUTO_INCREMENT,
title VARCHAR(128),
writer VARCHAR(16),
price DECIMAL(5,2),
pulish VARCHAR(32),
onSale BOOLEAN);
INSERT INTO book VALUES(null,‘语文’,‘张三’,345.56,‘中国出版社’,1);
INSERT INTO book VALUES(null,‘数学’,‘李四’,56897,‘清华出版社’,0);
INSERT INTO book VALUES(null,‘英语’,‘王五’,4236,‘中国传媒出版社’,1);
INSERT INTO book VALUES(null,‘物理’,‘wangba’,7896,‘国外出版社’,1);
#删除
DELETE FROM book WHERE writer=‘wangba’;
#修改
UPDATE book SET title=‘化学’ WHERE writer=‘张三’;
#查询
SELECT * FROM book;

#练习2：
#丢弃数据库jd,如果存在
drop database if exists jd;
#创建数据库jd
create database jd;
#进入数据库
use jd;
#创建保存学生数据的表student
create table student(
sid int, #整型
name varchar(8), #字符串
sex varchar(1),
score int
);
#插入数据
insert into student values(‘1’,‘tom’,‘M’,‘81’);
insert into student values(‘2’,‘ran’,‘W’,‘59’);
insert into student values(‘3’,‘dong’,‘M’,‘50.5’);
#查询数据
select * from student;

#练习3：
#丢弃数据库，如果存在tedu
drop database if exists tedu;
#创建数据库
create database tedu;
#进入数据库
use tedu;
#创建保存学生数据的表emp
create table emp(
id int, #整型
ename varchar(8), #字符串
phone varchar(11),
addr varchar(16)
);
insert into emp values(‘1’,‘li’,‘15238963454’,‘beijing’);
insert into emp values(‘2’,‘qun’,‘17835758896’,‘hainan’);
insert into emp values(‘3’,‘lian’,‘12568523697’,‘hunan’);
select * from emp;

#练习4
#先丢弃数据库，如果存在xz
DROP DATABASE IF EXISTS xz;
#创建数据库
CREATE DATABASE xz;
#使用数据库
USE xz;
#创建保存用户数据的user表
CREATE TABLE user(
uid INT,
uname VARCHAR(8),
upwd INT,
email VARCHAR(16),
phone VARCHAR(11),
userName VARCHAR(16),
regTime VARCHAR(16),
isOnline VARCHAR(8)
);
#插入数据
INSERT INTO user VALUES(‘1’,‘A’,‘123456’,‘[e-mail]’,‘123123123’,‘A1’,‘2020-09’,‘yes’);
INSERT INTO user VALUES(‘2’,‘B’,‘456456’,‘[e-mail]’,‘456456456’,‘B1’,‘2020-08’,‘no’);
INSERT INTO user VALUES(‘3’,‘C’,‘789789’,‘[e-mail]’,‘789789789’,‘C1’,‘2020-07’,‘yes’);
INSERT INTO user VALUES(‘4’,‘D’,‘987987’,‘[e-mail]’,‘987987987’,‘D1’,‘2020-09’,‘yes’);
INSERT INTO user VALUES(‘5’,‘E’,‘654654’,‘[e-mail]’,‘654654654’,‘E1’,‘2020-09’,‘no’);
INSERT INTO user VALUES(‘6’,‘F’,‘321321’,‘[e-mail]’,‘321321321’,‘F1’,‘2020-08’,‘no’);
#查询结果
SELECT * FROM user;

#练习5
#设置客户端连接服务器的编码
SET NAMES UTF8;
#丢弃数据库，如果存在
DROP DATABASE IF EXISTS sina;
#创建数据库，并设置编码为UTF8
CREATE DATABASE sina CHARSET=UTF8;
#进入数据库
USE sina;
CREATE TABLE news(
nid INT,
title VARCHAR(32),
ctime VARCHAR(10),
orgin VARCHAR(16),
detail VARCHAR(5000)
);
#插入数据
INSERT INTO news VALUES(‘1’,‘中国的标题’,‘2020-9’,‘中国日报’,‘详情一’);
INSERT INTO news VALUES(‘2’,‘美国’,‘2020-9’,‘zhongguo’,‘故’);
INSERT INTO news VALUES(‘4’,‘日本’,‘2020-9-1’,‘日本日报’,‘详情三’);
#修改数据
UPDATE news SET title=‘中国的徒弟’ WHERE nid=‘1’;
#删除数据
DELETE FROM news WHERE nid=‘2’;
SELECT *FROM news;
#练习6
SET NAMES UTF8;
DROP DATABASE IF EXISTS xuezi;
CREATE DATABASE xuezi CHARSET=UTF8;
USE xuezi;
CREATE TABLE laptop(
lid INT PRIMARY KEY,
title VARCHAR(128),
price DECIMAL(7,2),
stockCount SMALLINT,
sheifTime DATETIME,
isIndex BOOLEAN
);
INSERT INTO laptop VALUES(‘1’,‘苹果笔记本’,‘3456’,‘34’,‘2020-9-4’,1);
INSERT INTO laptop VALUES(‘2’,‘华为笔记本电脑’,‘4567’,‘23’,‘2020-5-5’,1);
INSERT INTO laptop VALUES(‘3’,‘惠普笔记本电脑’,‘5000’,‘456’,‘2020-9-7’,0);
#练习7
SET NAMES UTF8;
DROP DATABASE IF EXISTS xz;
CREATE DATABASE xz CHARSET=UTF8;
USE xz;
CREATE TABLE family(
fid INT,
fname VARCHAR(4)
);
INSERT INTO family VALUES(10,‘联想’);
INSERT INTO family VALUES(20,‘戴尔’);
INSERT INTO family VALUES(30,‘小米’);

CREATE TABLE laptop(
lid INT PRIMARY KEY,
title VARCHAR(128),
price DECIMAL(7,2),
spec VARCHAR(40),
detail VARCHAR(128),
shelfTime DATE,
isOnline BOOLEAN,
familyld TINYINT(5)
);
INSERT INTO laptop VALUES(1,‘联想笔记本’,45678,‘40*34’,‘详情一’,‘2020/9/1’,1,10);
INSERT INTO laptop VALUES(2,‘戴尔笔记本’,4678,‘14寸，重2.7公斤’,‘详情二’,‘2020-9-2’,1,20);
INSERT INTO laptop VALUES(3,‘小米笔记本’,66678,‘游戏款16寸’,‘详情三’,‘2020-9-15’,0,30);
#练习8
#设置客户端连接服务器的编码为UTF8
SET NAMES UTF8;

#先丢弃数据库，如果存在xz
DROP DATABASE IF EXISTS xz;
#创建一个新的数据库,设置存储的编码为UTF8
CREATE DATABASE xz CHARSET=UTF8;
#使用数据库
USE xz;
#创建保存用户数据的user表
CREATE TABLE user(
uid INT,
uname VARCHAR(8),
upwd INT,
email VARCHAR(16),
phone VARCHAR(11),
userName VARCHAR(16),
regTime VARCHAR(16),
isOnline VARCHAR(8)
);
#插入数据
INSERT INTO user VALUES(‘1’,‘A’,‘123456’,‘[e-mail]’,‘123123123’,‘张三’,‘2020-09’,‘yes’);
INSERT INTO user VALUES(‘2’,‘B’,‘456456’,‘[e-mail]’,‘456456456’,‘李四’,‘2020-08’,‘no’);
INSERT INTO user VALUES(‘3’,‘C’,‘789789’,‘[e-mail]’,‘789789789’,‘王五’,‘2020-07’,‘yes’);
INSERT INTO user VALUES(‘4’,‘D’,‘987987’,‘[e-mail]’,‘987987987’,‘赵六’,‘2020-09’,‘yes’);
INSERT INTO user VALUES(‘5’,‘E’,‘654654’,‘[e-mail]’,‘654654654’,‘刘七’,‘2020-09’,‘no’);
INSERT INTO user VALUES(‘6’,‘F’,‘321321’,‘[e-mail]’,‘321321321’,‘陈八’,‘2020-08’,‘no’);
#修改数据
UPDATE user SET uname='AAAA’WHERE uid=‘1’;
UPDATE user SET upwd=‘111111’,phone=‘11111111111’,isOnline=‘YES’ WHERE uid=‘1’;
#删除数据
DELETE FROM user WHERE uid=‘2’;
#查询结果
SELECT * FROM user;
#练习9
#设置客户端连接服务器端编码
SET NAMES UTF8;
#丢弃数据库，如果存在
DROP DATABASE IF EXISTS tedu;
#创建数据库，设置存储的编码
CREATE DATABASE tedu CHARSET=UTF8;
#进入数据库
USE tedu;
#创建保存部门数据的表
CREATE TABLE dept(
did INT PRIMARY KEY AUTO_INCREMENT,
dname VARCHAR(8) UNIQUE
);
#插入数据
INSERT INTO dept VALUES(10,‘研发部’);
INSERT INTO dept VALUES(20,‘市场部’);
INSERT INTO dept VALUES(30,‘运营部’);
INSERT INTO dept VALUES(40,‘测试部’);
#创建保存员工数据的表
CREATE TABLE emp(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(16) NOT NULL,
sex BOOLEAN DEFAULT 1, #1-男 0-女
birthday DATE,
salary DECIMAL(7,2), #99999.99
deptId INT,
FOREIGN KEY(deptId) REFERENCES dept(did)
);
#插入数据
INSERT INTO emp VALUES(NULL,‘gongjinglong’,1,‘1990-5-5’,6000,20);
INSERT INTO emp VALUES(NULL,‘daixiangyu’,0,‘1991-8-20’,7000,10);
INSERT INTO emp VALUES(NULL,‘dujiang’,1,‘1995-10-20’,3000,30);
INSERT INTO emp VALUES(NULL,‘wuxiaoxing’,0,‘1992-3-20’,5000,10);
INSERT INTO emp VALUES(NULL,‘wangtao’,1,‘1993-12-3’,8000,20);
INSERT INTO emp VALUES(NULL,‘liuhaonan’,1,‘1991-1-3’,4000,10);
INSERT INTO emp VALUES(NULL,‘xiongjie’,1,‘1990-12-3’,10000,10);
INSERT INTO emp VALUES(NULL,‘dahaozheng’,1,‘1994-12-3’,6000,30);
INSERT INTO emp VALUES(NULL,‘mazhengrui’,1,‘1991-12-3’,9000,10);
INSERT INTO emp VALUES(NULL,‘range’,0,‘1995-12-3’,10000,20);
INSERT INTO emp VALUES(NULL,‘liankun’,1,‘1993-12-3’,8000,30);
INSERT INTO emp VALUES(NULL,‘wangxiaomin’,0,‘1992-12-3’,12000,10);
INSERT INTO emp VALUES(NULL,‘zhuwentao’,0,‘1989-12-3’,8000,10);
INSERT INTO emp VALUES(NULL,‘hanchengyuan’,1,‘1988-12-3’,10000,10);
INSERT INTO emp VALUES(NULL,‘liuyuxi’,1,‘1993-12-3’,22000,NULL);
#练习10
SET NAMES UTF8;
DROP DATABASE IF EXISTS xz;
CREATE DATABASE xz CHARSET=UTF8;
use xz;
CREATE TABLE family(
fid INT PRIMARY KEY,
fname VARCHAR(16) NOT NULL UNIQUE
);
INSERT INTO family VALUES
(10,‘联想’),
(20,‘戴尔’),
(30,‘小米’);
INSERT INTO family VALUES (40,‘华为’);
INSERT INTO family VALUES (50,‘苹果’);

CREATE TABLE laptop(
lid INT PRIMARY KEY,
title VARCHAR(128),
price DECIMAL(7,2) NOT NULL DEFAULT 3000,
spec VARCHAR(64) NOT NULL ,
detail VARCHAR(5000),
shelfTime DATE,
isOnsale BOOLEAN DEFAULT 1,
familyId INT,
#讲family作为外键，范围是family的fid列
foreign key(familyId) references family(fid)
);
INSERT INTO laptop VALUES(1,‘小米Air’,3799,‘开发版’,‘详情1’,‘2020-1-1’,1,30);
INSERT INTO laptop VALUES(2,‘然7000’,4199,‘设计版’,‘详情1’,‘2020-2-1’,1,20);
INSERT INTO laptop VALUES(3,‘然8000’,DEFAULT,‘设计版’,‘详情1’,‘2020-2-1’,0,20);
INSERT INTO laptop VALUES(4,‘然9000’,DEFAULT,‘设计版’,‘详情1’,DEFAULT,0,20);
INSERT INTO laptop (lid,title,price,spec) VALUES(5,‘游戏本’,12999,‘游戏版’);