SQL语句基础练习

#创建一个名称为mydb1的数据库
CREATE DATABASE mydb1;

#创建一个使用utf8字符集的mydb2数据库
CREATE DATABASE mydb2 CHARACTER SET utf8;

#创建一个使用utf8字符集，并带比较规则的mydb3数据库。
CREATE DATABASE mydb3 CHARACTER SET utf8 COLLATE utf8_general_ci;

#修改mydb2字符集为gbk;
ALTER DATABASE mydb2 CHARACTER SET gbk;

#删除数据库mydb3。
DROP DATABASE mydb3;

#查看所有数据库。
SHOW DATABASES;

#查看数据库mydb1的字符集
SHOW CREATE DATABASE mydb1;

#创建一张员工表employee
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
gender	VARCHAR(30),
birthday DATE,
entry_date DATE,
job VARCHAR(30),
salary DOUBLE,
RESUME TEXT
 );

#创建表employee2
CREATE TABLE employee2(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
gender VARCHAR(30),
birthday DATE,
entry_date DATE,
job VARCHAR(30),
salary DOUBLE,
RESUME TEXT
);

#删除employee2表
DROP TABLE employee2;

 #在上面员工表的基本上增加一个image列。
ALTER TABLE employee ADD image varchr(20);

#修改job列，使其长度为60。
ALTER TABLE employee MODIFY job VARCHAR(60);

#删除gender列。
ALTER TABLE employee DROP gender;

#表名改为user。
RENAME TABLE employee TO USER;

#修改表的字符集为utf8
ALTER TABLE USER CHARACTER SET utf8;

#列名name修改为username
ALTER TABLE USER CHANGE NAME username VARCHAR(20) NOT NULL;

#查看数据库内的所有表
use 数据库名；
SHOW TABLES;

#查看employee的建表语句
SHOW CREATE TABLE employee;

#查看employee的表结构
DESC employee;

#向employee中插入三个员工信息，要求员工姓名分别是zs,ls,wangwu
INSERT INTO employee (NAME) VALUES ('zs'),('ls'),('wangwu');

#将所有员工薪水修改为5000元。
UPDATE employee SET salary=5000 ;

#将姓名为’zs’的员工薪水修改为3000元。
UPDATE employee SET salary=3000 WHERE NAME='zs';

#将姓名为’ls’的员工薪水修改为4000元,job改为ccc。
UPDATE employee SET salary=3000,job='ccc' WHERE NAME='ls';

#将wangwu的薪水在原有基础上增加1000元。
UPDATE employee SET salary=salary+1000 WHERE NAME='wangwu';

#删除表中名称为’zs’的记录。
DELETE FROM employee WHERE NAME='zs';

#删除表中所有记录
DELETE FROM employee;

create table exam(
        id int primary key auto_increment,
        name varchar(20) not null,
        chinese double,
        math double,
        english double
    );
    insert into exam values(null,'关羽',85,76,70);
    insert into exam values(null,'张飞',70,75,70);
    insert into exam values(null,'赵云',90,65,95);
    insert into exam values(null,'刘备',97,50,50);
    insert into exam values(null,'曹操',90,89,80);
    insert into exam values(null,'司马懿',90,67,65);
#查询表中所有学生的信息。
select * from exam;
#查询表中所有学生的姓名和对应的英语成绩。
SELECT NAME,english FROM exam;
#过滤表中重复数据
select id,name,chinese,math,english from ( select * from exam order by chinese,math,english desc) a group by a.name;
#在所有学生分数上加10分特长分。
select * , (chinese + math+ english) as "总分" , (chinese + math+ english) + 10 as "特长分" from exam;
#统计每个学生的总分。
select * , (chinese + math+ english) as "总分" from exam;
#使用别名表示学生分数。
select * , (chinese + math+ english) as "总分" from exam;
#查询姓名为刘备的学生成绩
select * from exam where name = "刘备";
#查询英语成绩大于90分的同学
select * from exam where english > 90;
#查询总分大于200分的所有同学
select * from exam where (chinese + math + english) > 200;
#查询英语分数在 80－90之间的同学。
SELECT * FROM exam WHERE english BETWEEN 80 AND 90;
# 查询数学分数为89,75,91的同学。
SELECT * FROM exam WHERE math = 89 OR math = 75 OR math = 91;
#查询所有姓刘的学生成绩
SELECT * FROM exam WHERE NAME LIKE "刘%";
#查询所有姓刘两个字的学生成绩。
select * from exam where name like "刘_";
#查询数学分>80并且语文分>80的同学。
select * from exam where math > 80 and chinese > 80;
#查询数学分>80 或者 语文分>80的同学。
select * from exam where math > 80 or chinese > 80;
#对数学成绩排序后输出。
select * from exam order by math;
#对总分排序按从高到低的顺序输出
select *, (chinese + math + english) as "总分" from exam order by (chinese + math + english) desc;
#对姓刘的学生成绩排序输出
select * from exam where name like "刘%" order by math desc;
#统计一个班级共有多少学生？
select  count(id) from exam;
#统计数学成绩大于或等于90的学生有多少个？
select  count(math) from exam where math >= 90;
#统计总分大于250的人数有多少？
select count(id) from exam where (chinese + math + english) > 250;
#统计一个班级数学总成绩？
select sum(math) as "数学总成绩" from exam;
#统计一个班级语文、英语、数学各科的总成绩
select sum(chinese),sum(math),sum(english) from exam;
#统计一个班级语文、英语、数学的成绩总和 
select sum(chinese)+sum(math)+sum(english) from exam;
#统计一个班级语文成绩平均分
select sum(chinese) / count(id) from exam;
#求一个班级总分平均分
select avg(chinese + math + english) from exam;
#求班级最高分和最低分（数值范围在统计中特别有用）
select max(chinese + math + english) from exam;
select min(chinese + math + english) from exam;

create table orders(
    id int,
    product varchar(20),
    price float
    );

    insert into orders(id,product,price) values(1,'电视',900);
    insert into orders(id,product,price) values(2,'洗衣机',100);
    insert into orders(id,product,price) values(3,'洗衣粉',90);
    insert into orders(id,product,price) values(4,'桔子',9);
    insert into orders(id,product,price) values(5,'洗衣粉',90);
#查询购买了几类商品，并且每类总价大于100的商品
select product,sum(price) from orders where price > 100 group by product;