MySQL基本CRUD与简单案例

创建数据库
mysql>create database mydb;
mysql>show databases;


查看数据库的创建语句
mysql>show create database mydb;


创建一个名称为mydb1的数据库
mysql>create database mydb1;


创建一个使用utf-8字符集的mydb2数据库
mysql>create database mydb2 character set utf8;




创建一个使用utf-8字符集，并带校对规则的mydb3数据库
mysql>create database mydb3 character set utf8 collate utf8_general_ci;


查看某个字符集下的校对规则
mysql>show collation like 'utf8%';


校对规则
utf8
collations(21种)

utf8_xxx_ci/cs/bin

ci: 存储的数据不区分大小写
cs: 字符区分大小写
bin: 二进制格式

gbk
latin1
....


删除前面创建的mydb1数据库
mysql>drop database if exists mydb1;


不能使用命令修改数据库的名称, 只能修改库的定义信息(字符集/校对规则)


查看服务器中的数据库，并把其中某一个库的字符集修改为gbk
mysql>alter database mydb character set gbk;






找到MySQL存储数据的文件目录
%MYSQL_HOME%/data/

备份数据库表中的数据
mysqldump -u用户名 -p 数据库名 > 文件名.sql

>mysqldump -uroot -p mysql > d:\mysql.sql



恢复数据库
1. 创建库
mysql>create database mydata;

2. 使用库
mysql>use mydata

3. source 文件名.sql



性别 char(1)
姓名 varchar(20)
Mark
Loretta
年龄 int(11), integer(11)
薪水 double(10, 2)

一篇文章
TEXT, 64K, 2E16
LONGTEXT 

CLOB

图片等二进制文件
BLOB, 64K
LONGBLOB


时间日期
生日           DATE
订单生成时间   DATETIME
时间戳         TimeStamp



创建user表
create table user(
id int,
name varchar(20),
password varchar(16),
birthday date
);


查看当前库中的表
mysql>show tables;
mysql>show create table user;
mysql>desc user;

创建员工employee表
create table employee(
id int,
name varchar(20),
sex char(1),
birthday date,
entry_date date,
job varchar(20),
salary double(10, 2),
resume text
);


在上面员工表的基本上增加一个image列
mysql>alter table employee add column image blob;


修改job列，使其长度为60
mysql>alter table employee modify job varchar(60);


删除sex列
mysql>alter table employee drop column sex;


删除表
mysql>drop table user;


表名改为user
mysql>rename table employee to user;


修改表的字符集为utf-8
mysql>alter table user character set gbk;


列名name修改为username
mysql>alter table user change name username varchar(20);


mysql>desc employee;


CRUD语句


create(insert)
retrieve(select)
update
delete


插入三条数据至employee表
mysql>insert into employee(id, name, sex, birthday, entry_date, job, salary, resume) values(1, 'Loretta', '女', '1992-01-03', '2013-08-10', '技术总监', 30000, '漂亮的好总监');
mysql>insert into employee values(2, '老王', '男', '1982-10-11', '2011-01-05', 'CEO', 50000, '好CEO');
mysql>insert into employee values(3, '老李', '男', '1989-11-12', '2012-01-05', 'CEO助理', 20000, '好助理');


查询表中的数据
mysql>select * from employee\G




将所有员工薪水修改为5000元。
mysql>update employee set salary = 5000;

将姓名为’老李’的员工薪水修改为3000元。
mysql>update employee set salary = 3000 where name = '老李';


将姓名为’Loretta’的员工薪水修改为4000元,job改为"教学总监"。
mysql>update employee set salary = 4000, job = '教学总监' where name = 'Loretta';


将“老王”的薪水在原有基础上增加1000元。
mysql>update employee set salary = salary + 1000 where name = '老王';




删除表中的数据
delete from employee;
delete from employee where name = '老王';


truncate table employee;


删除表中名称为’老王’的记录
mysql>delete from employee where name = '老王';


删除表中所有记录
mysql>delete from employee;


使用truncate删除表中记录
mysql>truncate table employee;


创建student学生表并插入数据
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,98,56);
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,90);
insert into student(id,name,chinese,english,math) values(5,'李水',82,84,67);
insert into student(id,name,chinese,english,math) values(6,'张恩谁',99,89,89);


查询表中所有学生的信息。
select id, name, chinese, english, math from student;


查询表中所有学生的姓名和对应的英语成绩。
select name, english from student;


过滤表中重复数据
select distinct english from student;




在所有学生分数上加10分特长分
select name, (chinese + 10) as chinese2, (english + 10) english2, (math + 10) math2 from student;

统计每个学生的总分
select name, (chinese+english+math) total from student;


使用别名表示学生分数
select name, chinese ch, english eng, math ma from student;


使用where子句，进行过滤查询。练习：
查询姓名为”王五”的学生成绩
select name, chinese, english, math from student where name = '王五';


查询英语成绩大于90分的同学
select name, english from student where english > 90;


查询总分大于200分的所有同学
select name, (chinese+english+math) total from student s where chinese+english+math > 200;


查询英语分数在 80－90之间的同学。
update student set english = 80 where id = 5;
update student set english = 90 where id = 6;
select name, english from student where english >= 80 and english <= 90;
select id, name, english from student where english between 80 and 90;


查询数学分数为89,90,91的同学。
select name, math from student where math in(89, 90, 91);
select name, math from student where math = 89 or math = 90 or math = 91;


查询所有姓李的学生成绩。
select name, chinese, english, math from student where name like '李%';
select name, chinese, english, math from student where name like '张__';


查询数学分>80，语文分>80的同学。
select name, math, chinese from student where math > 80 and chinese > 80;


插入数据至student
insert into student(id,name,chinese,english,math) values(7,'笑然',null,84,67);
insert into student(id,name,chinese,english,math) values(8,'泽中',99,null,89);


查询没有参加英语考试的同学
select name from student where english is null;


查询所有参加语文考试的同学成绩
select name, chinese from student where chinese is not null;




对数学成绩排序后输出。
select name, math from student order by math;


对总分排序后输出，然后再按从高到低的顺序输出
select name, (chinese+english+math) total from student where chinese+english+math is not null order by total desc ;


对姓李的学生成绩排序输出
select name, (chinese+english+math) total from student where name like '李%' order by total;


统计一个班级共有多少学生？
select count(id) from student;


统计数学成绩大于90的学生有多少个？
select count(id) from student where math >= 90;


统计总分大于250的人数有多少？
select count(id) from student where chinese+english+math > 250;


统计一个班级数学总成绩？
select sum(math) from student;


统计一个班级语文、英语、数学各科的总成绩
select sum(chinese), sum(english), sum(math) from student;


统计一个班级语文、英语、数学的成绩总和
select sum(chinese+english+math) from student;


求一个班级数学平均分？
select avg(math) from student;


求一个班级总分平均分?
select avg(chinese+english+math) from student;


统计一个班级语文成绩平均分
select avg(chinese) from student;


求班级最高分和最低分（数值范围在统计中特别有用）
select max(chinese+english+math), min(chinese+english+math) from student;




创建orders表并插入数据
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,'洗衣机',700);
insert into orders(id,product,price) values(3,'洗衣粉',10);
insert into orders(id,product,price) values(4,'桔子',29);
insert into orders(id,product,price) values(5,'洗衣粉',10);




对订单表中商品归类后，显示每一类商品的总价
select product, sum(price) from orders group by product;


查询购买了几类商品，并且每类总价大于100的商品
select product, sum(price) from orders group by product having sum(price) > 100;