1.数据库
创建数据库
create database mydb1;
create database mydb2 character set utf8 collate utf8_bin;
create database mydb3 character set gbk;
collate校对规则
查看mysql存储位置 :show global variables like "%datadir%";
查看所有数据库
show databases;
查看数据库的创建语句
show create database mydb1;
数据库删除
drop database mydb2;
修改数据库
alter database mydb1 character set gbk;
选择数据库
use mydb1;
查看当前使用的数据库:
select database();
2.数据库表
创建数据库表
create table user(
id int,
name varchar(20)
);
创建employee表：
字段 属性
id 整形 int
name 字符型 varchar(10)
gender 字符型 varchar(2)
birthday 日期型 date
entry_date 日期型 date
job 字符型 varchar(20)
salary 小数型 float
resume 大文本型 text
create table employee(
id int,
name varchar(10),
gender varchar(2),
birthday date,
entry_date date,
job varchar(20),
salary float,
resume text
);
查看当前数据的所有表：
show tables;
描述表结构：
desc table_name;
id主键的employee表创建语句：
create table employee(
id int primary key auto_increment ,
name varchar(20),
gender varchar(2) ,
birthday date,
entry_date date,
job varchar(20),
salary double,
resume text
);
修改表：
在上面员工表的基本上增加一个image列。
alter table employee add image blob;
修改job列，使其长度为60。modify
alter table employee modify job varchar(60);
删除gender列。drop
alter table employee drop gender;
表名改为emp。
rename table employee to emp;
修改表的字符集为utf8
alter table emp character set utf8;
列名name修改为username
alter table emp change name username varchar(20);
修改姓名为唯一约束
alter table emp change username username varchar(20) unique;
修改salary字段为非空约束
alter table emp change salary salary double not null;
3.表数据操作：
插入数据：
向employee表中插入三条数据：
insert into employee values(null,'李帅','男','1999-09-09','2000-09-09','吃饭',10000,'特别能吃');
insert into employee values(null,'曹洋','男','1989-09-09','2000-01-01','喂饭',5000,'专业喂饭');
insert into employee values(null,'谷丰硕','男','1998-02-09','2001-09-01','做饭',10000,'会做饭');
更新操作：
将所有员工薪水修改为5000元。
update employee set salary = 5000;
将姓名为'李帅'的员工薪水修改为3000元。
update employee set salary = 3000 where name='李帅';
将姓名为'曹洋'的员工薪水修改为4000元,job改为ccc。
update employee set salary = 4000,job='ccc' where name= '曹洋';
将'谷丰硕'的薪水在原有基础上增加1000元。
update employee set salary = salary + 1000 where name='谷丰硕';
删除语句：
a. Delete语句练习
删除表中名称为’李帅’的记录。
delete from employee where name='李帅';
删除表中所有记录。
delete from employee;
使用truncate删除表中记录
truncate employee;
拓展：
truncate删除表结构并会重新建立表结构，以此形式来删除表中数据。这种删除方式可能会影响到表与表直接的关系，所在在多表结构当中不建议使用，如果是单表则可以使用。
查询语句：
练习1：
查询表中所有学生的信息。
select * from exam ;
查询表中所有学生的姓名和对应的英语成绩。
select name,english from exam;
过滤表中重复数据。distinct去重
select distinct english from exam;
练习2:
在所有学生分数上加10分特长分显示。
select name,chinese+10,math+10,english+10 from exam;
统计每个学生的总分。
select name,chinese+math+english from exam;
使用别名表示学生总分。
select name,chinese+math+english as sum from exam;
select name,chinese+math+english sum from exam;
练习3：
查询姓名为张飞的学生成绩。
select * from exam where name='张飞';
查询英语成绩大于90分的同学。
select * from exam where english > 90;
查询总分大于200分的所有同学。
select name,chinese+math+english sum from exam where chinese+math+english>200;
在where字句中不能使用select语句里的别名，因为where关键字比select关键字执行顺序靠前
from --- where --- select
练习4：
查询英语分数在 80－100之间的同学。
select * from exam where english between 80 and 100;
select * from exam where english >= 80 and english <= 100;
查询数学分数为65,75,77的同学。
select * from exam where math in(65,75,77);
查询所有姓张的学生成绩。
select * from exam where name like '张%';
查询数学分>70，语文分>80的同学。
select * from exam where math > 70 and chinese > 80;
select * from exam where math > 60 or chinese <90;
查询数学成绩为null的学生
insert into exam values(null,'朴乾',60,null,90);
select * from exam where math is null;
练习5：order by
对语文成绩排序后输出。
select chinese from exam order by chinese desc;
对总分排序按从高到低的顺序输出
select name,chinese+math+english as sum from exam order by sum desc;
对姓张的学生成绩排序输出
insert into exam values(null,'张飞飞',11,12,13);
select name,chinese+math+english as sum from exam where name like '张%' order by sum asc;
ifnull的使用：
select name,ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0) from exam;
#select math from exam;
select ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0) as sum from exam order by sum;
4.聚集函数
count练习：
统计一个班级共有多少学生？
select count(name) from exam;
select count(math) from exam;
统计数学成绩大于90的学生有多少个？
select count(math) from exam where math>90;
统计总分大于230的人数有多少？
select count(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) from exam where ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0) > 230;
sum练习：
统计一个班级数学总成绩？
select sum(math) from exam;
统计一个班级语文、英语、数学各科的总成绩
select sum(math),sum(chinese),sum(english) from exam;
统计一个班级语文、英语、数学的成绩总和
select sum(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) from exam;
统计一个班级语文成绩平均分
平均分 = 总分/总人数
select sum(chinese)/count(chinese) from exam;
avg练习：
求一个班级数学平均分？
select avg(math) from exam;
求一个班级总分平均分？
select avg(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) from exam;
max/min函数
练习：
求班级最高分和最低分 （数值范围在统计中特别有用）
create table c (
id int,
date date,
amount int
);
insert into c values(1,'2007-07-09',7);
insert into c values(2,'2007-07-09',3);
insert into c values(2,'2007-07-10',3);
insert into c values(2,'2007-07-1',1);
select id,date from c group by id;
select id,amount,count(*) from c group by id,amount;
group by练习：对订单表中商品归类后，显示每一类商品的总价
select product,sum(price) from orders
group by product;
• 使用having 子句 对分组结果进行过滤
练习：查询购买了几类商品，并且每类总价大于100的商品
select product,count(product) from orders
group by product having sum(price)>100;
在分组之后只能使用having子句过滤条件。
在使用having的位置不可以使用where，但是使用where的位置可以很实用having。
5.多表关系：
//不添加外键的部门表和员工表
create table dept(
id int primary key auto_increment,
name varchar(20)
);
insert into dept values (null,'财务部'),(null,'人事部'),(null,'科技部'),(null,'销售部');
create table emp(
id int primary key auto_increment,
name varchar(20),
dept_id int
);
//添加外键的部门表和员工表
create table dept(
id int primary key auto_increment,
name varchar(20)
);
insert into dept values (null,'财务部'),(null,'人事部'),(null,'科技部'),(null,'销售部');
create table emp(
id int primary key auto_increment,
name varchar(20),
dept_id int,
foreign key(dept_id) references dept(id)
);
insert into emp values (null,'张飞',1),(null,'关羽',2),(null,'刘备',3),(null,'赵云',4);
//手动添加数据
insert into emp value(null,'背景',5); --- 员工不应当插入一个不存在的部门
delete from dept where id = 4; --- 部门已经被裁掉，员工应当提前辞退。
select * from dept where id = 4;
6.多表查询：
笛卡尔积查询：
会将两张表的全部内容相乘进行结果展示。如果左边表有m条数据，右标签有n条数据，则结果数量为m*n条数据。
select * from dept,emp;
在笛卡尔积查询的结果中发现，有一部分数据是正确的，这些数据的dept表的id字段和emp表的dept_id字段是相同的，这些字段关系正确，是我们需要的行数据，所以应该将正确的行数据取出，错误的行数据过滤掉。
在笛卡尔积基础之上过滤掉错误数据：
select * from dept,emp where dept.id = emp.dept_id;
内连接查询：
取出左边表有的且右边表也有的数据。
select * from dept
inner join emp
on dept.id = emp.dept_id;
外连接查询：
insert into emp values(null,'背景',5);
左外链接查询：
在内连接查询的基础之上，获取左边表有且右边表没有的数据。
select * from emp
left join dept
on dept.id = emp.dept_id;
select * from emp as e
left join dept as d
on d.id = e.dept_id;
右外连接查询：
在内连接查询的基础之上，获取右边表有且左边表没有的数据。
insert into dept values(null,'后勤部');
//插入的数据要保证在右边表有，而左边表没有
select * from emp
right join dept
on dept.id = emp.dept_id;
全外连接查询：
在内连接查询的基础之上，获取左边表有而右边表没有的数据和右边表有而左边表没有的数据。
select * from dept
left join emp
on dept.id = emp.dept_id
union
select * from dept
right join emp
on dept.id = emp.dept_id;
解决文字格式不对的办法：
create database mydb character set gbk;
create table employee(
)ENGINE=INNODB charset=gbk;
set names gbk;
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);
-----------------------------------------------------------------------------
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);
-----------------------------------------------------------------------------
create table dept(
id int primary key auto_increment,
name varchar(20)
);
insert into dept values (null,'财务部'),(null,'人事部'),(null,'科技部'),(null,'销售部');
create table emp(
id int primary key auto_increment,
name varchar(20),
dept_id int
);
insert into emp values (null,'张飞',1),(null,'关羽',2),(null,'刘备',3),(null,'赵云',4);
