数据库操作sql练习

一、数据库的创建：

1、创建一个名称为mydb1的数据库

CREATE DATABASE mydb1;

2、创建一个使用utf8字符集的mydb2数据库。

CREATE DATABASE mydb2 CHARACTER SET utf8;

3、创建一个使用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的字符集

SELECT schema_name,default_character_set_name FROM information_schema.schemata WHERE schema_name = 'mydb2';

数据库中表操作的sql练习

一、创建表

1、创建一张员工表employee
		字段		类型
		id			整形
		name		字符型
		gender		字符型
		birthday    日期型
		entry_date  日期型
		job			字符型
		salary		小数型
		resume		文本

CREATE TABLE employee(
id	INT PRIMARY KEY auto_increment,
name	VARCHAR(255) NOT NULL,
gender	VARCHAR(255),
birthday  datetime,
entry_date  datetime,
job	 VARCHAR(255),
salary	FLOAT,
resume	TINYTEXT)CHARSET utf8;

2、创建一张员工表employee2
		字段		类型
		id			整形
		name		字符型
		gender		字符型
		birthday    日期型
		entry_date  日期型
		job			字符型
		salary		小数型
		resume		文本
要求：把id 设置成主键，并且自动增长。name不允许为空。

CREATE TABLE employee2(
id	INT PRIMARY KEY auto_increment,
name	VARCHAR(255) NOT NULL,
gender	VARCHAR(255),
birthday  datetime,
entry_date  datetime,
job	 VARCHAR(255),
salary	FLOAT,
resume	TINYTEXT)CHARSET utf8;

二、删除表

1、删除employee2表

DROP TABLE employee2;

三、数据表的结构的修改：

1、在上面员工表的基本上增加一个image列

ALTER TABLE employee ADD image2 VARCHAR(255);

2、修改job列，使其长度为60

ALTER TABLE employee MODIFY job VARCHAR(60);

3、删除gender列

ALTER TABLE employee DROP gender;

4、表名改为user

RENAME TABLE employee TO USER;

5、修改表的字符集为utf8

ALTER TABLE employee CHARACTER SET utf8;

6、列名name修改为username

ALTER TABLE CHANGE name username VARCHAR(255);

四、查看表结构

1、查看数据库内的所有表

SHOW TABLES;

2、查看employee的建表语句

SHOW CREATE TABLE employee;

3、查看employee的表结构

DESC employee;

表记录的操作

一、插入语句 —insert

1、向employee中插入三个员工信息，要求员工姓名分别是zs,ls,wangwu

INSERT INTO employee(name,gender,birthday,entry_date,job,salary,resume)VALUES('zs','男',NULL,NULL,NULL,NULL,NULL);
INSERT INTO employee(name,gender,birthday,entry_date,job,salary,resume)VALUES('ls','女',NULL,NULL,NULL,NULL,NULL);
INSERT INTO employee(name,gender,birthday,entry_date,job,salary,resume)VALUES('wangwu','男',NULL,NULL,NULL,NULL,NULL);

二、更新语句 —update

1、将所有员工薪水修改为5000元。

UPDATE employee SET salary=5000;

2、将姓名为’zs’的员工薪水修改为3000元。

UPDATE employee SET salary=3000 WHERE NAME='zs';

3、将姓名为’ls’的员工薪水修改为4000元,job改为ccc。

UPDATE employee SET salary=4000,job='ccc' WHERE NAME='ls';

4、将wangwu的薪水在原有基础上增加1000元。

UPDATE employee SET salary=salary+1000 WHERE NAME='wangwu';

三、删除语句 —delete

1、删除表中名称为’zs’的记录。

DELETE FROM employee WHERE NAME='zs';

2、删除表中所有记录。

DELETE FROM employee;

四、查询语句 —select

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);

练习：

1、查询表中所有学生的信息。

SELECT * FROM exam;

2、查询表中所有学生的姓名和对应的英语成绩。

SELECT name,english FROM exam;

3、过滤表中重复数据。

SELECT DISTINCT * FROM exam; 

4、在所有学生分数上加10分特长分。

SELECT * FROM exam SET chinese=(chinese+10),math=(math+10),english=(english+10);

5、统计每个学生的总分。

SELECT *,chinese+math+english FROM exam;

6、使用别名表示学生分数。

SELECT *,chinese+math+english AS 总分 FROM exam;

使用where子句

7、查询姓名为刘备的学生成绩

SELECT * FROM exam WHERE name='刘备';

8、查询英语成绩大于90分的同学

SELECT * FROM exam WHERE english>90;

9、查询总分大于200分的所有同学

SELECT * FROM exam WHERE chinese+math+english>200;

10、查询英语分数在 80－90之间的同学。

SELECT * FROM exam WHERE english BETWEEN 80 AND 90;

11、查询数学分数为89,75,91的同学。

SELECT * FROM exam WHERE math IN(89,75,91);

12、查询所有姓刘的学生成绩。

SELECT * FROM exam WHERE name LIKE '刘%';

13、查询所有姓刘两个字的学生成绩。

SELECT * FROM exam WHERE name LIKE '刘_';

14、查询数学分>80并且语文分>80的同学。

SELECT * FROM exam WHERE math>80 AND chinese>80;

15、查询数学分>80 或者 语文分>80的同学。

SELECT * FROM exam WHERE math>80 OR chinese>80;

使用order by 排序

16、对数学成绩排序后输出。

SELECT * FROM exam ORDER BY math;

17、对总分排序按从高到低的顺序输出

SELECT *,chinese+math+english AS 总分 FROM exam ORDER BY 总分 DESC;

18、对姓刘的学生成绩排序输出

SELECT * FROM exam WHERE name LIKE '刘%' ORDER BY math DESC;

使用count（函数）

19、统计一个班级共有多少学生？

SELECT count(id) FROM exam;

20、统计数学成绩大于或等于90的学生有多少个？

SELECT count(math) FROM exam WHERE math>=90;

21、统计总分大于250的人数有多少？

SELECT count(id) FROM exam WHERE chinese+math+english>250;

使用sum函数

22、统计一个班级数学总成绩？

SELECT SUM(math) FROM exam;

23、统计一个班级语文、英语、数学各科的总成绩

SELECT SUM(chinese),SUM(math),SUM(english) FROM exam;

24、统计一个班级语文、英语、数学的成绩总和

SELECT SUM(chinese)+SUM(math)+SUM(english) FROM exam;

25、统计一个班级语文成绩平均分

SELECT SUM(chinese)/COUNT(id) FROM exam;

使用avg函数

26、求一个班级数学平均分？

SELECT AVG(math) FROM exam;

27、求一个班级总分平均分

SELECT AVG(ifnull(chinese,0))+AVG(ifnull(math,0))+AVG(ifnull(english,0)) FROM exam;

使用max，min函数

28、求班级最高分和最低分（数值范围在统计中特别有用）

SELECT MAX(ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0)) FROM exam;
SELECT MIN(ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0)) 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);

29、查询购买了几类商品，并且每类总价大于100的商品

SELECT product,sum(price) FROM orders WHERE price>100 GROUP BY product;