mysql的使用和练习解答+ppt

 

数据库

什么是数据库？

数据库就是存储在硬盘上的文件，数据库产品都是采用一种优良的算法在保存数据，方便我们对数据进行增删改查

 

数据库服务器： 安装了数据库软件的计算机

数据库：数据库服务器可以创建多个数据库， 一个web应用对应一个数据库

表： 一个库中有多张表，一个javabean对应一张表

 

 

sql语句

一、针对数据库和表的操作

数据库 database  表 table

 

创建 create

修改 alter

删除 drop

查看 show

 

创建一个数据库

create database a;

 

查看数据库

show databases;

 

删除表user

drop table user;

 

练习

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

create database mydb1;

创建一个使用utf-8字符集的mydb2数据库。 

create database mydb2 character set utf8;

创建一个使用utf-8字符集，并带校对规则的mydb3数据库。

create database mydb3 character set utf8 collate utf8_general_ci;

 

查看数据库的定义信息

show create database mydb2;

 

删除前面创建的mydb3数据库

drop database mydb3;

 

// 使用数据库

use mydb1;

创建一个a表

create table a

(

name varchar(20)

);

 

insert into a (name) values ('aaa');

insert into a (name) values ('bbb');

 

备份mydb1

mysqldump -uroot -proot mydb1 > d:\a.sql          //不能在mysql里面进行访问，在cmd控制台

 

恢复数据库的数据

source d:\a.sql            mysql [-h 主机名或IP] -u root -psorry test<C:/test.sql

 

mydb2的字符集修改为gbk

alter database mydb2 character set gbk;

 

创建一个员工表

字段 属性

id 整形

name 字符型

gender 字符型或bit型

brithday 日期型

entrydate 日期型

job 字符型

salary 小数型

resume 大文本型

 

create table employee

(

id int,

name varchar(20),

gender varchar(6),

birthday date,

entrydate date,

job varchar(20),

salary float,

resume text

);

 

查看表的定义信息

show create table employee;

查看表的结构

desc employee;

 

在上面员工表的基础上增加一个image列。

alter table employee add image blob;

修改job列，使其长度为60。

alter table employee modify job varchar(60);

删除gender列。

alter table employee drop gender;

表名改为users。

rename table employee to users;

修改表的字符集为utf-8

alter table users character set utf8;

列名name修改为username

alter table users change name username varchar(30);

 

 

二、针对表中数据的操作

针对表中数据的操作无外乎四个：增删改查  crud : create read update delete

1 增 insert语句

创建一张 employee 表

create table employee

(

id int,

name varchar(20),

gender varchar(10),

birthday date,

salary float,

entry_date date,

resume text

);

 

向表中添加三条记录

insert into employee (id,name,gender,birthday,salary,entry_date,resume) values (1,'zhangsan','male','1980-11-22',1000,'2000-4-12','good boy');

insert into employee (id,name,gender,birthday,salary,entry_date,resume) values (2,'wangwu','male','1983-11-6',1000,'2004-4-12','good boy');

insert into employee (id,name,gender,birthday,salary,entry_date,resume) values (3,'xiaohong','female','1992-11-22',1000,'2012-2-12','good girl');

 

insert into employee (id,name,gender,birthday,salary,entry_date,resume) values (4,'小王','male','1985-11-22',1000,'2012-2-12','一个好男孩');

 

查看表中所有的数据

select * from employee;

 

查看一下数据库编码的信息

show variables like 'character%';

 

| Variable_name            | Value

+--------------------------+--------

| character_set_client     | utf8

| character_set_connection | utf8

| character_set_database   | utf8

| character_set_filesystem | binary

| character_set_results    | utf8

| character_set_server     | utf8

| character_set_system     | utf8

 

修改客户端编码

set character_set_client=gbk;

 

set character_set_results=gbk;

 

insert employee values (5,'xiaolan','female','1992-11-22',1000,'2012-2-12','good girl');

 

insert employee (id,name) values (6,'aaa');

 

insert employee (id,name) values (7,'bbbb'),(8,'ccc'),(9,'dddd');

 

2 改  update 语句

 

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

update employee set salary=5000

将姓名为’zhangsan’的员工薪水修改为3000元。

update employee set salary=3000 where name='zhangsan';

将姓名为’wangwu’的员工薪水修改为4000元,gender改为female。

update employee set salary=4000,gender='female' where name='wangwu';

将xiaohong的薪水在原有基础上增加1000元。

update employee set salary=salary+1000 where name='xiaohong';

 

3 删  delete 语句

删除表中name为’zhangsan’的记录。

delete from employee where name='zhangsan';

删除表中所有记录。

delete from employee;

使用truncate删除表中记录。

truncate employee;

 

4 查  select 语句

创建一个student表  添加7条记录

create table student(

id int,

name varchar(20),

chinese float,

english float,

math float

);

 

insert into student(id,name,chinese,english,math) values(1,'张小明',89,78,90);

insert into student(id,name,chinese,english,math) values(2,'李进',67,53,95);

insert into student(id,name,chinese,english,math) values(3,'王五',87,78,77);

insert into student(id,name,chinese,english,math) values(4,'李一',88,98,92);

insert into student(id,name,chinese,english,math) values(5,'李来财',82,84,67);

insert into student(id,name,chinese,english,math) values(6,'张进宝',55,85,45);

insert into student(id,name,chinese,english,math) values(7,'黄蓉',75,65,30);

 

练习

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

select * from student;

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

select name,english from student;

过滤表中重复数据。

select distinct english from student;

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

select name,english+10,chinese+10,math+10 from student;

统计每个学生的总分。

select name,english+chinese+math from student;

使用别名表示学生分数。

select name,english+chinese+math as sum from student;

 

where子句用于加查询条件

查询姓名为李一的学生成绩

select * from student where name='李一';

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

select * from student where english>90;

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

select *,english+chinese+math sum from student where english+math+chinese>200;

 

 

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

select * from student where english between 80 and 90;

查询数学分数为89,90,91的同学。

select * from student where math in(89,90,91);

查询所有姓李的学生成绩。

select * from student where name like '李%';

查询姓李的名字是两个字的学生

select * from student where name like '李_';

查询数学分>80，语文分>80的同学。

select * from student where math>80 and chinese>80;

查询英语>80或者总分>200的同学

select *,english+chinese+math sum from student where english>80 or english+chinese+math>200;

 

对数学成绩排序后输出。

select * from student order by math;

对总分排序后输出，然后再按从高到低的顺序输出

select *,english+chinese+math sum from student order by math+english+chinese desc;

对姓李的学生成绩排序输出  order by 从句要位于 where从句的后面

select *,english+chinese+math sum from student where name like '李%' order by math+english+chinese;

 

合计函数 count

insert into student(id,name,chinese,english,math) values(8,'郭靖',null,null,18);

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

select count(*) from student;

select count(chinese) from student;

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

select count(*) from student where math>90;

统计总分大于240的人数有多少？

select count(*) from student where chinese+math+english>240;

 

合计函数  sum

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

select sum(math) from student;

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

select sum(math),sum(english),sum(chinese) from student;

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

select sum(chinese+math+english) from student;

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

select sum(chinese)/count(*) from student;

select sum(chinese)/count(chinese) from student;

 

合计函数   不考虑为null的数据

求一个班级语文平均分？

select avg(chinese) from student;

求一个班级总分平均分

select avg(chinese+math+english) from student;

 

求班级最高分和最低分

select max(chinese) from student;

 

 

订单表

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

 

select * from orders group by product;

select *,count(*) quantity,sum(price) from orders group by product;

 

查询单类商品购买价格高于100的

select *,count(*) quantity,sum(price) from orders group by product having sum(price)>100;