第一部分 数据库操作
1、数据库创建 : create database 数据库名称;
创建一个名称为mydb1的数据库 ----- create database mydb1;
* 通过show databases; 查看所有数据库
创建一个使用utf8字符集的mydb2数据库 ---- create database mydb2 character set utf8;
* 参考mysql 规范查看具有哪些字符集
创建一个使用utf8字符集,并带校对规则的mydb3数据库 ----- create database mydb3 character set utf8 collate utf8_bin;
* 什么是校对规则? 主要用于排序 例如:张三 李四, 排序张三和李四谁在前面 ?? 拼音排序,比划排序
* 每一个字符集对应多个校对规则 参考规范 10.10章节
创建数据库 只需要指定字符集就可以了,一般使用默认校对规则 ----- 如果不指定字符集,将采用系统默认字符集(安装时进行配置)
2、查看和删除数据库
查看当前数据库服务器中的所有数据库 ---- show databases;
查看前面创建的mydb2数据库的定义信息 ----- show create database mydb2;
删除前面创建的mydb1数据库 ----- drop database mydb1;
3、修改数据库(其实就是修改数据库字符集)
语法: alter database 数据库名称 character set 字符集 collate 校对规则;
修改 mydb2 字符集为 gbk ------- alter database mydb2 character set gbk;
4、切换数据库(使用数据库之前,必须先切换该数据库)
语法 : use 数据库名称;
使用mydb2 --- use mydb2;
查看当前使用数据库 ----- select database();
第二部分 数据表操作
1、数据表创建
语法: create table 表名(列名 类型(长度),列名 类型(长度)... );
* 如果数据库字符集没问题,创建表一般采用默认数据库字符集
MySQL数据类型:
String/char 类型 ----- MySQL 字符串类型 varchar char
* varchar和char 区别 : varchar 变长字符串 char 定长字符串
* 例如:保存 "你好你好" 到数据库 ,使用char(8),因为char定长,保存数据库内容"你好你好四个空格" ;varchar(8) 列会根据字段长度进行调整,只会保存"你好你好"
byte short int long ---- MySQL TINYINT 、SMALLINT、INT、BIGINT
float double ---- MySQL float double
boolean ---- MySQL bit 只能存放 0 或者 1
MySQL提供四种与日期相关类型 : date 只有日期,没有时间 time 只有时间,没有日期 timestamp 日期时间都有 datetime 日期时间都有
* 在mysql中 datetime 和 timestamp 保存内容没区别 ----- 区别timestamp 自动更新
MySQL 提供两种大数据类型 :text(大文本数据) blob(大二进制数据)
* 如果想 txt 文件保存数据库 ---- text
* 如果想 mp3 文件保存数据库 ---- blob
TINYBLOB(只能保存255字节 和varchar 最大值一样)、BLOB(64KB)、 MEDIUMBLOB(16MB)、 LONGBLOB(4GB)
创建员工表练习
* 如果sql 语句特别长,分多行编写
create table employee(
id int,
name varchar(40),
gender varchar(20),
birthday date,
entry_date date,
job varchar(20),
salary double,
resume tinytext
);
* 在编写创建数据表sql时,只有字符串类型必须设置长度,其他类型都有默认长度
* 创建数据表前 必须先指定数据库 use 数据库名;
* 创建数据表后,通过show tables 查看当前数据库内所有数据表
2、数据表创建 为字段添加约束
* 约束为了数据表记录更加完整,内容更加有效 ---- 验证措施
主键约束:每条记录,有一个字段唯一标识该记录 primary key (不能重复)
唯一约束:该字段内容,不允许重复 例如:微博系统用户名 unique
非空约束:该字段内容 不允许为空值 null, not null
如果主键是 数字类型,可以在mysql定义主键自增 (仅限于mysql) create table employee( id int primary key not null auto_increment, name varchar(40) unique, gender varchar(20) not null, birthday date, entry_date date, job varchar(20), salary double not null, resume tinytext );
3、数据表修改
少建一列、添加列: alter table 表名 add 列名 类型(长度) 约束;
列错误,修改列 : alter table 表名 modify 列名 类型(长度) 约束;
删除列 : alter table 表名 drop 列名;
列名写错了,改列名: alter table 表名 change 旧列名 新列名 类型(长度) 约束;
修改表名 : rename table 旧表名 to 新表名;
案例:
* desc 表名; 查看表结构
在上面员工表的基本上增加一个image列。 ---- alter table employee add image varchar(100) ;
修改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(40) unique;
4、数据表删除
语法: drop table 表名;
删除user表 ----------- drop table user;
mysql 安装配置注意问题
1、mysql/bin 目录保存 mysql启动程序 mysql.exe 客户端程序 、mysqld-nt.exe 数据库服务器端程序,mysql服务就会启动该进程
2、mysql安装目录 my.ini 是mysql 核心配置文件 [mysql] 对mysql.exe客户端进行配置 [mysqld] 对mysqld-nt.exe 对服务器端配置
basedir --- mysql安装目录
datadir --- mysql 数据库文件存放目录
port ---- mysql 服务端口
* 每次新建一个数据库 在data目录 新建文件夹
* 每次创建一个table ,在数据库目录中创建 .frm文件
如果mysql 安装后 没有配置,或者配置出现问题 通过 mysql server instance configuration winzard 重新配置
* 一定要将mysql/bin 目录加入环境变量 path --- 否则 mysql命令找不到 无法识别
mysql 写错sql 回车后
-> 输入; 回车就可以退出语句
'> 输入'回车
"> 输入"回车
第三部分 数据表中数据记录的增删改查
1、数据记录的插入
语法:insert into 表名(列名,列名 ...) values(值,值 ...);
* values中值的数量和顺序要和 前面 列名数量和顺序 一致
* 插入 值 必须满足 列长度要求 例如: name varchar(40) 只能允许插入40个字符之内内容
* 值 是 字符或者日期类型 ,必须加 ''
* 空值直接写null
insert into employee(id,name,gender,birthday,entry_date,job,salary,resume) values(null,'zhangsan','male','1980-10-10','2000-01-01','hr',3000,'good boy');
* 如果主键是 自增,直接写null 自动生成
* 如何查看插入后数据 select * from employee;
简化插入语句写法一: 省略列名
insert into employee values(null,'lisi','male','1988-05-05','2011-01-10','java developer',4000,'very good!');
* 省略列名,values中必须含有表中所有列的值,按照表中列的顺序出现
简化插入语句写法二 :直插入部分列的值,其他列使用默认值
insert into employee(id,name,gender,salary) values(null,'wangwu','female',3500);
* 如果一些列有默认值,插入时,可以不插入该列的值,值为null
向数据表插入中文数据
insert into employee values(null,'小丽','female','1990-10-10','2012-01-01','销售总监助理',5000,'这是一个不 错的小女孩!');
* ERROR 1406 (22001): Data too long for column 'name' at row 1
* 发生Data too long 错误,除了因为数据过长外,还可能因为字符集问题
通过 show variables like 'character%'; 查看字符集
mysql 客户端相关字符集:client、connection、result
mysql 服务器端相关字符集 : database、server、system(无法改变)
* 字符集问题原因,因为 mysql客户端字符集必须gbk ------ 设置客户端窗口字符集为gbk
* 修改与客户端相关三个字符集 set names gbk; (临时设置当前客户端窗口字符集)
* 永久改变客户端字符集 配置mysql/ my.ini 配置文件 修改 [mysql] default-character-set=gbk (修改后必须重启mysql 服务)
net stop mysql
net start mysql
插入数据时,收到约束限制
2、修改数据表记录
语法:update 表名 set 列名=值,列名=值... where条件语句
* set 为列进行设值,where设置更新哪些行,如果没有where 将修改所有行数据
案例:
将所有员工薪水修改为5000元。 ----------------- update employee set salary = 5000;
将姓名为 zhangsan 的员工薪水修改为3000元。 ------ update employee set salary = 3000 where name='zhangsan';
将姓名为 lisi 的员工薪水修改为4000元,job改为ccc。 ------------ update employee set salary = 4000, job='ccc' where name='lisi';
将'wangwu'的薪水在原有基础上增加1000元。 -------- update employee set salary= salary+1000 where name='wangwu';
将所有女性工资上调20% ; ------- update employee set salary = salary * 1.2 where gender = 'female';
3、数据表记录删除
语法: delete from 表名 where条件语句;
* 不用where 将删除所有数据
删除记录 delete
删除表 drop
truncate也可以实现所有表中记录删除
案例:
删除表中名称为 zhangsan 的记录。 ------------ delete from employee where name ='zhangsan';
删除表中所有记录。 ------ delete from employee;
使用truncate删除表中记录。 ----- truncate employee;
insert into employee(id,name,gender,birthday,entry_date,job,salary,resume) values(null,'zhangsan','male','1980-10-10','2000-01-01','hr',3000,'good boy');
insert into employee values(null,'lisi','male','1988-05-05','2011-01-10','java developer',4000,'very good!');
insert into employee(id,name,gender,salary) values(null,'wangwu','female',3500);
insert into employee values(null,'小丽','female','1990-10-10','2012-01-01','销售总监助理',5000,'这是一个不 错的小女孩!');
truncate 语句 可以将数据表中所有记录删除 效果和delete from employee 一样;
truncate 和 delete 区别 truncate 原理先将数据表删除,再重新创建 ,delete逐行删除 ; truncate后id重新开始,delete后,id继续上一个值增长 ---- truncate 效率更高 truncate 属于 DDL语句,delete属于DML语句 ,truncate不能受事务管理,删除了不可恢复,delete被事务管理,删除后进行恢复
如果 start transaction; 开启事务,delete删除数据后,可以通过rollback; 进行事务回滚,取消delete操作
4、数据记录查询
select |指定列 from 表名;
查询employee表中所有数据 ----- select * from employee; -- 这里代表任意列,显示所有列
查询具体列 查看employee表 name和job 两列 ---- select name,job from employee;
查询数据时可以对查询结果排重 select distinct gender from employee;
* 排重时,只能排掉完全相同两行记录 select distinct name,gender from employee; 因为name都不相同,所以不呢排重
create table student(
id int primary key auto_increment,
name varchar(40),
chinese double,
math double,
english double
);
insert into student values(null,'小王',80,90,65);
insert into student values(null,'老刘',70,55,85);
insert into student values(null,'张蕾蕾',60,80,50);
insert into student values(null,'李连杰',77,80,70);
练习:
查询表中所有学生的信息。 ------- select * from student;
查询表中所有学生的姓名和对应的英语成绩。----- select name,english from student;
过滤表中重复数学成绩。-------------- select distinct math from student;
查询结果列值可以进行运算,通过 as 别名为查询结果列起别名
练习
在所有学生分数每科上加10分特长分。 select name,math+10,chinese+10,english+10 from student;
统计每个学生的总分。 select name,math+chinese+english from student;
使用别名表示学生分数。select name as 姓名,math+chinese+english as 总分 from student;
* 在sql 中起别名使用as 关键字,as关键字可以省略
select name 姓名,math+chinese+english 总分 from student;
区分一下两句sql
select name,math from student; ---- 查询name和math 两列
select name math from student; ---- 查询name一列,起别名math
使用where子句,进行过滤查询。练习:
查询姓名为张蕾蕾 的学生成绩 ---select * from student where name = '张蕾蕾';
查询英语成绩大于80分的同学 ---- select * from student where english > 80;
查询总分大于200分的所有同学 --- select * from student where math+english+chinese > 200;
复杂运算符进行数据查询:
查询英语分数在 80-90之间的同学。 ----- select * from student where english>=80 and english<=90;
* 通过between --- and ---------- select * from student where english between 80 and 90; ---- 必须小的数字在前
查询数学分数为89,90,91的同学。---- 三选一 select * from student where math in (89,90,91);
查询所有姓李的学生成绩。----- 模糊查询 like 占位符
* % 代表任意字符串 _ 代表任意单个字符
select * from student where name like '__' ; 查询姓名为两个字学生信息
select * from student where name like '张%'; 查看所有姓张学生信息
查询数学分>80,语文分>80的同学。
select * from student where math > 80 and chinese> 80 ; 数学和语文成绩都大于80 学生
select * from student where math > 80 or chinese> 80 ; 数学大于80 或者 语文大于80 学生
select * from student where not (math > 80 or chinese> 80) ; 数学和语文都小于等于80 分学生
重点掌握 and 和 or 优先级 select * from student where 5>4 or 6>7 and 7>8; and先执行 条件为真,or先执行条件为假
and 先于or 执行
对查询结果进行排序 order by
语法 ... order by 列名 asc|desc , 列名 asc|desc ...
* 排序时,可以对多列进行排序,asc升序 desc降序
练习:
对数学成绩排序后输出。 ---- select name,math from student order by math; 默认升序
对总分排序按从高到低的顺序输出 ---- select name,math+chinese+english from student order by math+chinese+english desc;
对姓李的学生语文成绩降序输出 ---- select * from student where name like '李%' order by chinese desc;
对于null值操作 :
insert into student values(null,'李四',null,80,75);
查询语文没有成绩的学生 ----- select * from student where chinese is null;
查询语文有成绩学生 ---- select * from student where chinese is not null;
* null 和任何值 做任何运算 结果都是 null
5、分组查询 --- 分组函数
count函数,用来统计查询结果记录条数
练习:
统计一个班级共有多少学生? ----- select count() from student;
统计数学成绩大于70的学生有多少个? ----- select count() from student where math>70;
统计总分大于220的人数有多少? ----- select count(*) from student where math+chinese+english>220;
sum函数,用来对一列的值求和
统计一个班级数学总成绩? select sum(math) from student;
统计一个班级语文、英语、数学各科的总成绩 select sum(chinese),sum(english),sum(math) from student;
* 如果值为null,不参与求和运算
统计一个班级语文、英语、数学的成绩总和 select sum(chinese) + sum(english)+ sum(math) from student;
统计一个班级语文成绩平均分 select sum(chinese)/count(chinese) from student;
avg函数,用来对一列求平均值
求一个班级数学平均分? select avg(math) from student; ===== select sum(math)/count(math) from student;
求一个班级总分平均分? select avg(math+chinese+english) from student;
MAX、MIN函数,用来对一列数据 求最大值和最小值
求班级总分最高分和最低分(数值范围在统计中特别有用) select max(math+chinese+english), min(math+chinese+english) from student;
* 求总分最高学生叫什么 ?? select name from student where math+chinese+english = (select max(math+chinese+english) from student);
分组查询合并相同内容,进行分组,方便统计 通过group by 指定分组列名
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 product,sum(price) from orders group by product;
分组结果可以通过having进行过滤
练习:查询购买了几类商品,并且每类总价大于100的商品 select product,sum(price) from orders group by product having sum(price) > 100;
关于where 和 having 添加条件上区别 ? having 可以在任何情况下替换where ,但是having可以使用分组函数条件表达式,where不可以的
select执行原理分析
select --- from --- where ---- group by ---- having --- order by 关键字出现,必须按照该书写顺序
from 指定表
where 条件过滤 ,不符合数据过滤掉
group by 进行分组
having 对分组结果进行过滤
select 查询需要列
order by 对结果进行排序
关于mysql数据库的备份和恢复
* 企业中使用数据库,定期进行备份
备份命令: cmd> mysqldump -u 用户名 -p 备份数据库名称 > SQL脚本文件位置
例如备份day11 数据库 cmd> mysqldump -u root -p day11 > c:day11.sql
恢复命令: cmd> mysql -u 用户名 -p 数据库名称 < SQL脚本位置
例如恢复 day11 数据库 cmd> mysql -u root -p day11 < c:day11.sql
也可以登录mysql后执行恢复命令 source恢复数据库
cmd> mysql -u root -p 登录
sql> use day11
sql> source SQL脚本位置
员工管理系统 --- 存在employee表,添加dept 部门表
create table dept(
id int primary key auto_increment,
name varchar(40) not null
);
insert into dept values(null,'人力资源部');
insert into dept values(null,'财务部');
insert into dept values(null,'java事业部');
insert into dept values(null,'产品研发部');
将员工加入部门 --- 在employee表 新添加一个字段 dept_id
alter table employee add dept_id int ;
update employee set dept_id = 1;
将小丽调动java事业部 update employee set dept_id = 3 where name='小丽';
为什么要有外键约束?为了保证数据完整性
* java事业部解散了 delete from dept where id = 3; ---- 小丽dept_id 值还是3 ,3对应部门已经不存在了
* 为dept_id 设置外键,引用dept表 主键id ---- 数据库会为你维护数据完整性
将小丽调动财务部 update employee set dept_id = 2 where name='小丽';
对dept_id 添加外键约束 alter table employee add foreign key(dept_id) references dept(id);
再次尝试 删除财务部 delete from dept where id = 2;
* ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key ... 因为删除财务部,小丽引用财务部信息,财务部有人,不能删除
外键结果:A外键引用B 主键,删除B信息时,必须删除依赖B的A中数据 (外键保证数据完整性)
完整性约束有5类:主键primary key,唯一unique , 非空 not null ,外键 foreign key , 条件检查 check(mysql 不支持check)