mysql命令练习题 1
代码块
create database books
show databases
use books
show TABLES
DROP DATABASE books
-- 创建表：
create table book(
-- id（整形主键）
id int PRIMARY KEY auto_increment,
-- name（书籍名称）
name varchar(20),
-- price(浮点型)
price float(5,2),
-- author(作者)
author varchar(20),
-- publish(出版社)
publish varchar(20)
)
-- 3 添加数据
INSERT into book VALUES(1,'北平无故事','25','刘和平','作家出版社')
INSERT into book VALUES(2,'人间失格','16','太宰治著','作家出版社'),(3,'高兴','16','贾平凹','人民出版社'),(4,'源氏物语','57','刘和平','人民出版社'),
(5,'卡夫卡文集','9','卡夫卡','邮电出版社'),(6,'大家','12','王蒙','邮电出版社'),(7,'拉片子','37','杨健','清华出版社'),(8,'古代散文','5','归有光','安徽出版社'),
(9,'百花散文','6','孙虹选','百花文艺出版社'),(10,'方令孺散文集','5','方令孺','安徽文艺')
-- 4 查询所有图书的信息，并按价格降序显示
SELECT * FROM book
SELECT * FROM book ORDER BY price DESC
-- 5查询所有作家出版社的图书信息，并按价格降序显示
SELECT publish ,price FROM book ORDER BY price desc
-- 6查询出所有刘和平的图书信息 ，并输出。
SELECT * FROM book WHERE author="刘和平"
-- 7 删除ID是2的记录，如果没有相关记录则提示
DELETE FROM book WHERE id =2
-- 8 将所有价格不足10元的图书调到10元，并查看信息
UPDATE book set price = 10 where price <10
-- 9 查看所有图书的价格情况，并升序显示
SELECT publish,price FROM book ORDER BY price ASC
-- 10 查看所有价格低于20元的图收信息
SELECT * FROM book WHERE price<20
-- 11所有图书的价格上调20%，并查看信息
UPDATE book set price=20 WHERE price <20
mysql命令练习题4
--------------------------------------------------------------作业----------------------------------------------------
代码块
-- 创建数据库
CREATE DATABASE test
-- 使用数据库
use test
-- 在test数据库中创建emp表，表结构如下图所示(id设置为主键自增长)
CREATE table emp(empno int(11) PRIMARY KEY auto_increment not null,ename VARCHAR(50),job VARCHAR(50),mgr int DEFAULT null,hiredate date,sal DECIMAL(7,2),comm DECIMAL(7,2) DEFAULT null, deptno int(11))
-- 向表中添加数据
insert into emp VALUES(1001,"甘宁","文员",1013,"2000-12-17",8000.00,DEFAULT,20),
(1002,"黛绮丝","销售员",1006,"2001-02-20",16000.00,3000.00,30),
(1003,"殷天正","销售员",1006,"2001-02-22",12500.00,5000.00,30),
(1004,"刘备","经理",1009,"2001-04-02",29750.00,DEFAULT,20),
(1005,"谢逊","销售员",1006,"2001-09-28",12500.00,14000.00,30),
(1006,"关羽","经理",1009,"2001-05-01",28500.00,DEFAULT,30),
(1007,"张飞","经理",1009,"2001-09-01",24500.00,DEFAULT,10),
(1008,"诸葛亮","分析师",1004,"2007-04-19",30000.00,DEFAULT,20),
(1009,"曾阿牛","董事长",DEFAULT,"2001-11-17",50000.00,DEFAULT,10),
(1010,"韦一笑","销售员",1006,"2001-09-08",15000.00,0.00,30),
(1011,"周泰","文员",1008,"2007-05-23",11000.00,DEFAULT,20),
(1012,"程普","文员",1006,"2001-12-03",9500.00,DEFAULT,30),
(1013,"庞统","分析师",1004,"2001-12-03",30000.00,DEFAULT,20),
(1014,"黄盖","文员",1007,"2002-01-23",13000.00,DEFAULT,10),
(1015,"张三","文员",1007,"2002-01-23",53000.00,DEFAULT,50)
-- 1查询表中所有内容
SELECT *FROM emp
-- 2.修改表名为”yuangong_table”
ALTER TABLE emp RENAME to yuangong_table
-- 改回
ALTER TABLE yuangong_table RENAME to emp
-- 3.修改job字段的类型为varchar(20)
alter table yuangong_table MODIFY job VARCHAR(20)
-- 查看表结构
DESC yuangong_table
-- 4.删除表中姓名为张三并且工作（job）的所有信息
DELETE from emp WHERE ename="张三"and job="文员"
-- 在添加回去
insert into emp VALUES(1015,"张三","文员",1007,"2002-01-23",53000.00,DEFAULT,50)
-- 5.查询表中empno字段从1004至1008所有员工的记录
SELECT * from emp WHERE empno>=1004 and empno<=1008
-- 6.查询表中所有job字段是文员并且姓名是黄盖的员工的所有信息
SELECT * FROM emp WHERE job="文员" and ename="黄盖"
-- 7.查询表中在2001年以后入职的员工信息
SELECT * FROM emp WHERE hiredate>"2001-12-31"
mysql命令练习题5
-----------------------------------------------------------作业----------------------------------
代码块
CREATE DATABASE db_test
USE db_test
-- 2.在test数据库中创建yuangong表，表结构如下图所示(id设置为主键自增长)
CREATE TABLE yuangong(empno int(11) primary key auto_increment not null,
ename VARCHAR(50), job VARCHAR(50), mgr int DEFAULT null, hiredate date,
sal DECIMAL(7,2),comm DECIMAL(7,2)DEFAULT null,deptno int (11))
-- 3.向yuangong表中添加记录
INSERT into yuangong VALUES(1001,"甘宁","文员",1013,"2000-12-17",8000.00,DEFAULT,20),
(1002,"黛绮丝","销售员",1006,"2001-02-20",16000.00,3000.00,30),
(1003,"殷天正","销售员",1006,"2001-02-22",12500.00,5000.00,30),
(1004,"刘备","经理",1009,"2001-04-02",29750.00,DEFAULT,20),
(1005,"谢逊","销售员",1006,"2001-09-28",12500.00,14000.00,30),
(1006,"关羽","经理",1009,"2001-05-01",28500.00,DEFAULT,30),
(1007,"张飞","经理",1009,"2001-09-01",24500.00,DEFAULT,10),
(1008,"诸葛亮","分析师",1004,"2007-04-19",30000.00,DEFAULT,20),
(1009,"曾阿牛","董事长",DEFAULT,"2001-11-17",50000.00,DEFAULT,10),
(1010,"韦一笑","销售员",1006,"2001-09-08",15000.00,0.00,30),
(1011,"周泰","文员",1008,"2007-05-23",11000.00,DEFAULT,20),
(1012,"程普","文员",1006,"2001-12-03",9500.00,DEFAULT,30),
(1013,"庞统","分析师",1004,"2001-12-03",30000.00,DEFAULT,20),
(1014,"黄盖","文员",1007,"2002-01-23",13000.00,DEFAULT,10),
(1015,"张三","文员",1007,"2002-01-23",53000.00,DEFAULT,50)
-- 1查询表中所有内容
SELECT *FROM yuangong
-- 2.修改表名为”emp”
ALTER TABLE yuangong rename to emp
-- 改回
ALTER TABLE emp RENAME to yuangong
-- 3.修改ename字段的类型为varchar(40)
ALTER TABLE emp MODIFY ename VARCHAR(40)
-- 4.删除表中empno是1014并且ename是黄盖的员工信息
DELETE FROM emp WHERE empno=1014 AND ename="黄盖"
-- 5.查询表中empno字段的值是1007，1009或1011员工的所有记录
-- and 是 并且 OR是或者
SELECT * FROM emp WHERE empno=1007 or empno=1009 or empno=1011
-- 6.查询表中所有job字段是文员并且姓名是张三的员工的所有信息
SELECT *FROM emp WHERE job="文员" and ename="张三"
-- 7.查询表中在2001年以后入职的员工信息
SELECT * FROM emp WHERE hiredate>"2001-12-31"
mysql命令练习题6
代码块
-- 创建数据库
CREATE DATABASE db_test1
-- 使用表
use db_test1
-- 创建 表结构
create TABLE yuangong(empno int(11) PRIMARY key auto_increment not null,
ename VARCHAR(50),
job VARCHAR(50),
mgr int DEFAULT null,
hiredate date,
sal DECIMAL(7,2) DEFAULT null,
comm DECIMAL(7,2) DEFAULT null,
deptno int(11))
-- 向表中添加数据
insert into yuangong VALUES(1001,"甘宁","文员",1013,"2000-12-17",8000.00,DEFAULT,20),
(1002,"黛绮丝","销售员",1006,"2001-02-20",16000.00,3000.00,30),
(1003,"殷天正","销售员",1006,"2001-02-22",12500.00,5000.00,30),
(1004,"刘备","经理",1009,"2001-04-02",29750.00,DEFAULT,20),
(1005,"谢逊","销售员",1006,"2001-09-28",12500.00,14000.00,30),
(1006,"关羽","经理",1009,"2001-05-01",28500.00,DEFAULT,30),
(1007,"张飞","经理",1009,"2001-09-01",24500.00,DEFAULT,10),
(1008,"诸葛亮","分析师",1004,"2007-04-19",30000.00,DEFAULT,20),
(1009,"曾阿牛","董事长",DEFAULT,"2001-11-17",50000.00,DEFAULT,10),
(1010,"韦一笑","销售员",1006,"2001-09-08",15000.00,0.00,30),
(1011,"周泰","文员",1008,"2007-05-23",11000.00,DEFAULT,20),
(1012,"程普","文员",1006,"2001-12-03",9500.00,DEFAULT,30),
(1013,"庞统","分析师",1004,"2001-12-03",30000.00,DEFAULT,20),
(1014,"黄盖","文员",1007,"2002-01-23",13000.00,DEFAULT,10),
(1015,"张三","文员",1007,"2002-01-23",53000.00,DEFAULT,50)
-- 1.修改表名为”emp”
ALTER TABLE yuangong RENAME to emp
-- 2.向表中添加记录，字段对应值分别为（1016，光头强，程序员，1007，2018-10-31，NULL，30）
insert into emp VALUES(1016,"光头强","程序员",1007,"2018-10-31",NULL,DEFAULT,30)
-- 3.查询emp表中所有记录
SELECT * FROM emp
-- 4.删除表中empno是1007并且job是经理的员工信息
DELETE FROM emp WHERE empno=1007 and job="经理"
-- 添加回去
insert into emp VALUES (1007,"张飞","经理",1009,"2001-09-01",24500.00,DEFAULT,10)
-- 5.查询表中empno字段的值是1007，1009或1011员工的所有记录
SELECT * FROM emp WHERE empno=1007 or empno=1009 or empno=1011
-- 6.修改表中empno值是1004员工的姓名为“马云”
UPDATE emp set ename="马云" WHERE empno=1004
s
-- 改回
UPDATE emp set ename="刘备" WHERE empno=1004
-- 7.查询表中在2001年以后入职的员工信息
SELECT * FROM emp WHERE hiredate>="2002-1-1"
-------------------------------------------------作业--------------------------------------
mysql命令练习题7
代码块
CREATE TABLE yuangong(
sid int PRIMARY key auto_increment,
sname VARCHAR(20) NOT NULL,
sex VARCHAR(4) DEFAULT '男',
job VARCHAR(50) not null,
birthday DATE,
salary INT,
comm INT,
withhold INT
)
INSERT into yuangong VALUES(1001,'张三','男','高级程师',1975-1-1,2200,1100,200),(1002,'李四','女','助工',1985-1-1,1200,200,100),
(1003,'王五','男','工程师',1978-11-11,1900,700,200),(1004,'赵六','男','工程师',1979-1-1,1960,700,150)
修改表名为”emp”（10分）
ALTER TABLE yuangong RENAME emp
向表中添加字段Hobby,设置类型为varchar(50),设置唯一约束（10分）
ALTER TABLE emp ADD Hobby varchar(50) UNIQUE
向表中添加记录，字段对应值分别为（1005，林青霞，女，架构师，1969-12-12，8000，NULL，100，阅读）（5分）
INSERT INTO emp VALUES(1005,"林青霞","女","架构师",1969-12-12,8000,"NULL",100,"阅读")
SELECT * FROM emp
修改姓名为“林青霞”的记录性别为男（5分）
UPDATE emp set sex="男" WHERE sname="林青霞"
修改sname字段的类型为varchar(20)（5分）
alter table emp modify sname varchar(20)
查询表中sid字段的值从1002至1005员工的所有记录（5分）
SELECT * FROM emp WHERE sid >1001 and sid <1006
SELECT * FROM emp WHERE sid between 1002 and 1005
修改表中job值是高级工程师员工的job为“架构师”（5分）
UPDATE emp set job ="架构师" WHERE job ="高级程师"
UPDATE emp set birthday ="1979-1-1" WHERE sid = 1004
删除表中sid是1001或1005或1003的员工的记录（10分）
DELETE FROM emp WHERE sid in("1001","1005","1003")
修改姓名为“赵六”的记录性别位“女”（5分）
UPDATE emp set sex="女" WHERE sname="赵六"
修改表中姓名是1002员工的salary在原来的基础上+2000（10分）
UPDATE emp set salary =salary+2000 WHERE sid=1002
mysql命令练习题8
-------------------------------------------------------------作业-------------------------------------
代码块
create database db_test8
use db_test8
CREATE TABLE yuangong(
sid int PRIMARY key auto_increment,
sname VARCHAR(20) NOT NULL,
sex VARCHAR(4) DEFAULT '男',
job VARCHAR(50) not null,
birthday DATE,
salary INT,
comm INT,
withhold INT
)
INSERT into yuangong VALUES(1001,'张三','男','高级工程师','1975-1-1',2200,1100,200),(1002,'李四','女','助工','1985-1-1',1200,200,100),
(1003,'王五','男','工程师','1978-11-11',1900,700,200),(1004,'赵六','男','工程师','1979-1-1',1960,700,150)
-- 选择db_test数据库（5分）
USE db_test8
-- 修改表名为”emp”（10分）
ALTER TABLE yuangong rename to emp
-- 向表中添加字段Hobby,设置类型为varchar(50),设置唯一约束（10分）
ALTER TABLE emp add hobby VARCHAR(50) UNIQUE
-- 使用desc语句查看表结构（5分）
desc emp
-- 向表中添加记录，字段对应值分别为（1005，林青霞，女，架构师，1969-12-12，8000，NULL，100，阅读）（5分）
INSERT into emp VALUES(1005,'林青霞','女','架构师','1969-12-12',8000,NULL,100,'阅读')
-- 修改sname字段的类型为varchar(20)（5分）
ALTER TABLE emp modify sname VARCHAR(20)
-- 查询表中sid字段的值从1002至1005员工的所有记录（5分）
SELECT * FROM emp WHERE sid>=1002 and sid<=1005
-- 修改表中job值是高级工程师员工的job为“架构师”（5分）
UPDATE emp set job='架构师' WHERE job='高级工程师'
SELECT * FROM emp
-- 删除表中sid是1001并且sname是张三的员工的记录
DELETE from emp WHERE sid=1001 and sname='张三'
-- 修改表中sid是1002员工的salary在原来的基础上+2000（10分）
UPDATE emp set salary =salary+2000 WHERE sid=1002
mysql命令练习题9
-----------------------------------------------------作业-----------------------------------------------------------
代码块
create database db_test9
use db_test9
CREATE TABLE yuangong(
sid int PRIMARY key auto_increment,
sname VARCHAR(20) NOT NULL,
sex VARCHAR(4) DEFAULT '男',
job VARCHAR(50) not null,
birthday DATE,
salary INT,
comm INT,
withhold INT
)
INSERT into yuangong VALUES(1001,'张三','男','高级工程师','1975-1-1',2200,1100,200),(1002,'李四','女','助工','1985-1-1',1200,200,100),
(1003,'王五','男','工程师','1978-11-11',1900,700,200),(1004,'赵六','男','工程师','1979-1-1',1960,700,150)
-- 选择db_test数据库（5分）
USE db_test9
-- 修改表名为”emp”（10分）
ALTER TABLE yuangong rename to emp
-- 向表中添加字段Hobby,设置类型为varchar(50),设置唯一约束（10分）
ALTER TABLE emp add hobby VARCHAR(50) UNIQUE
-- 使用desc语句查看表结构（5分）
desc emp
-- 向表中添加记录，字段对应值分别为（1005，林青霞，女，架构师，1969-12-12，8000，NULL，100，阅读）（5分）
INSERT into emp VALUES(1005,'林青霞','女','架构师','1969-12-12',8000,NULL,100,'阅读')
-- 修改sname字段的类型为varchar(20)（5分）
ALTER TABLE emp modify sname VARCHAR(20)
-- 查询表中sid字段的值1002或1003或1005员工的所有记录（5分）
SELECT * FROM emp WHERE sid=1002 or sid=1003 or sid=1005
-- 修改表中job值是高级工程师员工的job为“架构师”（5分）
UPDATE emp set job='架构师' WHERE job='高级工程师'
SELECT * FROM emp
-- 删除表中sid是1003并且sname是王五的员工的记录
DELETE from emp WHERE sid=1003 and sname='王五'
-- 修改表中姓名是1004员工的salary在原来的基础上-300（10分）
UPDATE emp set salary =salary-300 WHERE sid=1004
