sql学习笔记(MySQL)

首先来看看sql语句的分类:

DDL(数据定义问题)

数据定义语言-Data Definition Language

用来定义数据库的对象，如数据库、数据表、视图、索引等。

DML(数据操纵问题)

数据处理语言-Data Manipulation Language

在数据表中更新，增加和删除记录

如update、insert、delete

DCL(数据控制问题)

数据控制语言-Data Control Language

指用于设置用户权限和控制事物语句

容易grant,revoke,if...else,while,begin transaction

DQL(数据查询问题)

数据查询语言-Data Query Language

select

SQL语句学习流程 ： 数据库操作语句--->数据表结构操作语句--->数据表记录操作语句 


一、数据库操作语句
1、创建数据库 会为每个软件系统创建单独数据库
语法 ： create database 数据库名称 ;  (创建数据库采用数据库服务器默认字符集 )
复杂写法 create database 数据库名称 character set 字符集 collate 比较规则 ; 

创建一个名称为mydb1的数据库。 create database mydb1;
创建一个使用utf8字符集的mydb2数据库。 create database mydb2 character set utf8;
创建一个使用utf8字符集，并带校对规则的mydb3数据库。create database mydb3 character set utf8 collate utf8_bin;

补充：每次创建一个数据库在 数据存放目录中生成一个文件夹 ， 每个文件夹中存在 db.opt 存放默认字符集和校对规则 
datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/"


2、查询数据库 
show databases; ----- 查看所有数据库 
show create database 数据库名; ------ 查看数据编码集


3、删除数据库
语法 ：drop database 数据库名称; 

查看当前数据库服务器中的所有数据库 show databases;
查看前面创建的mydb2数据库的定义信息 show create database mydb2;
删除前面创建的mydb1数据库 drop database mydb1;


4、修改数据库编码集 
语法：alter database 数据库名称 character set 字符集 collate 比较规则; 

修改mydb2字符集为gbk;  alter database mydb2 character set gbk;

切换当前使用数据库： use 数据库名称
查看当前正在使用数据库: select database();


==================================================== 所有数据库相关操作语句都属于DDL 语句
二、数据表结构SQL语句 
1、数据表的创建 
语法：create table 表名(列名 类型(长度),列名 类型(长度)... );
一个数据表 可以存在很多列，每列具有类型和长度 
* 创建表时没有指定 字符集，将采用数据库默认字符集 
* 创建表之前 必须使用use db 语法指定操作数据库 


创建day12 数据库 create database day12;
切换到day12 数据库 use day12; 


例如： 
User {
   id   int                              
   name string
   password string
   birthday date    
}
一个java类 对应数据库中一张数据表，一个java对象 对应数据表中一条数据记录 


MySQL 常用数据类型
java 中 String char  -----  mysql 中字符串型 char varchar 
* char是定长 varchar是变长 
例如：char(8) 保存 lisi，因为lisi 只有四个字符，所有会补充四个空格，成为8个字符存入 char(8)中 ，如果有 varchar(8) 自动根据存放内容改变长度


java 中 byte short int long float double ----- mysql 中数值类型 TINYINT 、SMALLINT、INT、BIGINT、FLOAT、DOUBLE


java 中 boolean ---- mysql 逻辑性 bit 存放一位数值 0 或者1 


java 中 Date ----- mysql 日期类型 date (只有日期) time(只有时间) datetime(日期时间都有) timestamp(日期时间都有 )
* datetime和timestamp 表现形式上完全相同，区别就在于timestamp 在数据库可以自定更新（当前时间）


java中 大数据类型 inputStream 二进制文件 Reader 文本文件 ------- mysql 大数据类型 blob(存放大二进制数据) text(存放大的文本文件)
* tinyblob tinytext 255字节 blob text 64KB mediumblob mediumtext 16MB  longblob longtext 4GB


例如： 
User {
   id   int    ------ mysql int                        
   name string  ------ mysql varchar
   password string  ----- mysql varchar
   birthday date    ----- mysql date 
}


员工表建表语句
create table employee (
   id int,
   name varchar(20),
   gender varchar(20),
   birthday date,
   entry_date date,
   job varchar(30),
   salary double,
   resume longtext
);


查看表结构 desc 表名; 


*** 创建数据表时，只有字符串类型必须写长度，而其他类型都有默认长度  


2、单表创建时约束
约束用来保证数据有效性和完整性 
主键约束 primary key : 信息记录某个字段可以唯一区分其他信息记录，这个字段就可以是主键 （唯一 非空）
唯一约束 unique : 该字段的值不允许重复 
* 一张表中可以有很多个唯一约束，只能有一个(两个)作为主键约束 


非空约束 not null ：该字段的值不能为空 


create table employee2 (
   id int primary key auto_increment,
   name varchar(20) unique not null,
   gender varchar(20) not null,
   birthday date not null,
   entry_date date not null,
   job varchar(30) not null,
   salary double not null,
   resume longtext
);


* 如果主键约束类型为 数值型 int bigint ，添加auto_increment 自动增长 


3、数据表结构修改
1) 增加列 语法: alter table 表名 add 列名 类型(长度) 约束; 
2) 修改现有列类型、长度和约束 语法：alter table 表名 modify 列名 类型(长度) 约束;
3) 修改现有列名称 语法：alter table 表名 change 旧列名 新列名 类型(长度) 约束;
4) 删除现有列 语法：alter table 表名 drop 列名 ;
5) 修改表名 rename table 旧表名 to 新表名; 


* 修改表的字符集：alter table student character set utf8;


在上面员工表的基本上增加一个image列。---- alter table employee add image varchar(100) ;
修改job列，使其长度为60。 ----alter table employee modify job varchar(60) not null;
删除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(20) unique not null;


查看当前数据库内所有表：show tables 
查看当前数据表字符集 ： show create table user;


4、数据表删除 
语法：drop table 表名; 


5、查看数据表结构
desc 表名; 查看表结构
show tables ; 查看当前库内所有表名
show create table 表名; 查看建表语句和字符集 


==================================================================== 所有数据表结构操作语句都是 DDL 
三、数据表中数据记录的增删改查操作 
1、向数据表插入记录 
语法：insert into 表名(列名,列名,列名...) values(值,值,值...);  为数据表的每列进行赋值 


注意事项
1) 插入值 类型必须和 列类型匹配
2) 值长度不能超过 列定义长度
3) 值的顺序和 列顺序对应 
4) 字符串和日期型值 必须写 单引号
5) 插入空值 可以写 null 


新建employee表，插入三个员工信息
insert into employee(id,name,gender,birthday,entry_date,job,salary,resume) values(1,'zhangsan','male','1990-10-10','2010-01-01','sales',4000,'good boy !');


语法2: 省略所有列名，但是后面值必须要和表中所有列进行匹配，按照表中列顺序 
insert into employee values(2,'lisi','male','1988-10-01','2008-08-17','hr',3500,'good hr !');


语法3：省略可以为空，有默认值部分列名，后面值要和前面列进行匹配
insert into employee(id,name,job,salary) values(3,'wangwu','boss',20000);


* 在插入记录后 ，通过select * from employee; 查看所有员工信息 


插入一条中文记录
insert into employee(id,name,job,salary) values(4,'小明','清洁员',1500);
出错了：
ERROR 1366 (HY000): Incorrect string value: '\xC3\xF7' for column 'name' at row 1 ; 
错误原因：mysql client 采用默认字符集编码 gbk 


查看系统所有字符集 ： show variables like 'character%';


解决办法：修改客户端字符集为gbk 
MYSQL中共有6个地方字符集 ：client connetion result 和客户端相关 、database server system 和服务器端相关 


第一种：当前窗口临时修改 set names gbk ;
* 只对当前窗口有效，关闭后就会失效


第二种：配置mysql/my.ini 文件
[mysql] 客户端配置
[mysqld] 服务器端配置 


修改客户端字符集 [mysql] 后字符集  default-character-set=gbk


使用mysql -u root -p 密码连入数据库后，如果进行数据库操作，直接操作，如果要进行数据表结构和数据记录操作，必须先切换到操作的数据库 use db;


2、数据记录更改操作
语法: update 表名 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';


3、数据记录的删除操作
语法：delete from 表名 where条件语句 ;
* 如果没有where语句，将删除表中 所有记录


如果要删除表中所有数据记录，使用 truncate table 表名; 等价于 delete from 表名; 


试题：如果使用delete删除表中所有记录和使用truncate table删除表中所有记录 有何不同 ？
truncate 删除数据，过程先将整个表删除，再重新创建 
delete 删除数据，逐行删除记录 
* truncate 效率要好于 delete 
truncate 属于DDL ，delete 属于DML ======== 事务管理只能对DML 有效 ，被事务管理SQL语句可以回滚到SQL执行前状态 


删除表中名称为’zhangsan’的记录。 ------ delete from employee where name='zhangsan';  
删除表中所有记录。----- delete from employee; (可以事务回滚)
使用truncate删除表中记录。 ---- truncate table employee;


========================================================================================== insert update delete 属于DML 语句
4、数据表记录的查询 
语法一 ： select [distinct] * | 列名,列名... from 表名; 
select * from 表名; 查询该表中所有列信息
select 列名,列名... from 表名;  查询表中指定列的信息 
distinct 用于排重 


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


查询表中所有学生的信息。 ---------  select * from exam; 
查询表中所有学生的姓名和对应的英语成绩。 ----- select name,english from exam;
过滤表中重复数据 (查询英语成绩，排除完全相同重复数据) ---- select distinct english from exam; 


语法二：select 表达式(列名执行运算) from 表名;
select 列名 as 别名 from 表名; 


在所有学生分数上加10分特长分。 ---- select name,chinese+10,math+10,english+10 from exam;
统计每个学生的总分。------- select name,chinese+math+english from exam;
使用别名表示学生分数。----- select name,chinese+math+english as 总分 from exam;


***  在对列起别名时，as可以省略 select name,chinese+math+english as 总分 from exam; ------ select name,chinese+math+english 总分 from exam;
select name,math from exam; 查询name和math两列的值
select name math from exam; 查询name列值，起别名math


语法三: select 列名 from 表名 where条件语句


查询姓名为关羽的学生成绩 -------select * from exam where name='关羽'; 
查询英语成绩大于90分的同学 ----- select * from exam where english > 90;
查询总分大于200分的所有同学 ----- select * from exam where chinese+math+english > 200;


运算符 
1) 相等= 不等 <>
2) between  ...and... 在两者之间取值 between 70 and 80 等价于 >=70 <=80  ----- 注意前面那个数要比后面那个数要小 
3) in(值,值,值) 在指定值中任取一个 in(70,80,90) 值可以是70、80或者90 
4) like '模糊查询pattern' 进行模糊查询 ，表达式有两个占位符 % 任意字符串 _ 任意单个字符 例如： name like '张%' 所有姓张学员  
name like '张_' 所有姓张名字为两个字学员 
5) is null 判断该列值为空


6) and 逻辑与 or 逻辑或 not 逻辑非 


查询英语分数在 90－100之间的同学。 -------- select * from exam where english>=90 and english <= 100;  select * from exam where english between 90 and 100;
查询数学分数为65,75,85的同学。 ---- select * from exam where math in(65,75,85); 
查询所有姓赵的学生成绩。---- select * from exam where name like '赵%';
查询英语分>80，语文分>80的同学。 ---- select * from exam where english > 80 and chinese > 80;


insert into exam values(null,'刘备',null,55,38);
查询语文没有成绩学员 select * from exam where chinese is null;
查询语文有成绩学员 select * from exam where chinese is not null;




语法四： select * from 表名 order by 列名 asc|desc ;  ---- asc升序 desc降序 
对数学成绩排序后输出。----------- select * from exam order by math; 默认asc升序 
对总分排序按从高到低（降序）的顺序输出  ------------ select * from exam order by math+chinese+english desc;
对学生成绩按照英语进行降序排序，英语相同学员按照数学降序 ------------- select * from exam order by english desc,math desc;


聚集函数 指SQL语句中内置函数 ---------- 分组函数（用于统计）
1) count 统计查询结果记录条数 select count(*)|count(列名) from 表名; 


统计一个班级共有多少学生？ ------------ select count(*) from exam; 
统计英语成绩大于90的学生有多少个？ ------- select count(*) from exam where english > 90;
统计总分大于220的人数有多少？ --------select count(*) from exam where chinese+math+english > 220;


2) sum 统计某一列数据的和  select sum(列名) from 表名; 


统计一个班级数学总成绩？ ----- select sum(math) from exam;
统计一个班级语文、英语、数学各科的总成绩 ---- select sum(chinese),sum(math),sum(english) from exam;
统计一个班级语文、英语、数学的成绩总和 select sum(chinese+math+english) from exam;  select sum(chinese)+sum(math)+sum(english) from exam;
**** 刘备语文null ，null进行所有运算 结果都是null 
select sum(chinese)+sum(math)+sum(english) from exam; 含有刘备英语和数学成绩
select sum(chinese+math+english) from exam;  不含刘备英语和数学成绩 
* 使用ifnull函数处理 null情况 
select sum(ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0)) from exam;  含有刘备英语和数学成绩


统计一个班级语文成绩平均分  ------ select sum(chinese)/count(*) from exam; 


3) avg 统计某一列平均值 select avg(列名) from 表名;
求一个班级数学平均分？ ---- select avg(math) from exam;
求一个班级总分平均分？---- select avg(ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0)) from exam; 


4) max 统计一列最大值 min 统计一列最小值 
求班级最高分和最低分（数值范围在统计中特别有用）select max(chinese+math+english) ,min(ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0)) from exam; 


语法五：select 分组函数 from exam 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;


在group by 语句后面 添加having 条件语句 ---- 对分组查询结果进行过滤 
练习：查询购买了几类商品，并且每类总价大于100的商品
select product,sum(price) from orders group by product having sum(price) > 100;


试题：where 和 having 条件语句的区别 ？ 
where 是在分组前进行条件过滤，having 是在分组后进行条件过滤 
使用where地方都可以用 having替换 ， 但是having可以使用分组函数，而where后不可以用分组函数 


小结 select 语句 ： S-F-W-G-H-O 组合 select ... from ... where ... group by... having... order by ... ; 
顺序不能改变 
解析顺序 ： from  - where -  group by - having - select - order by 


=================================================================================================================
MySQL 数据库的备份和恢复
1、备份命令 mysql/bin/mysqldump 将数据库SQL语句导出 
语法：mysqldump -u 用户名 -p 数据库名 > 磁盘SQL文件路径 


例如： 备份day12数据库 --- c:\day12.sql 
cmd > mysqldump -u root -p day12 > c:\day12.sql 回车输入密码 


INSERT INTO `exam` VALUES (1,'关羽',85,76,70),(2,'张飞',70,75,70),(3,'赵云',90,65,95),(4,'刘备',NULL,55,38);


2、恢复命令 mysql/bin/mysql 将sql文件导入到数据库 
语法： mysql -u 用户名 -p 数据库名 < 磁盘SQL文件路径
***** 导入SQL 必须手动创建数据库 ，SQL不会创建数据库 


例如：将c:\day12.sql 导入 day12数据库
cmd > mysql -u root -p day12 < c:\day12.sql 回车密码 


补充知识：恢复SQL也可以在数据库内部执行 source c:\day12.sql 




多表设计（外键约束）
create table emp (
  id int primary key auto_increment,
  name varchar(20),
  job varchar(20),
  salary double
);


insert into emp values(null,'小丽','人力资源',4500);
insert into emp values(null,'小张','Java工程师',5000);
insert into emp values(null,'老李','财务经理',8000);
insert into emp values(null,'小刘','项目经理',10000);


create table dept(
   id int primary key auto_increment,
   name varchar(20)
);


insert into dept values(null,'人力资源部');
insert into dept values(null,'财务部');
insert into dept values(null,'技术研发部');


让员工表和部门表发生关系，知道员工属于哪个部门 ，在员工表添加部门id字段 
alter table emp add dept_id int ;
update emp set dept_id = 1 where name = '小丽';
update emp set dept_id = 2 where name = '老李';
update emp set dept_id = 3 where name = '小刘';
update emp set dept_id = 3 where name = '小张';


公司因为财政问题，解散技术研发部 delete from dept where name ='技术研发部'; ----- 小张和小刘 失去了组织 


emp表 中dept_id字段 引用 dept表 id 字段 ------- 添加外键约束 （保证数据有效和完整性）
将emp表中dept_id 设置为外键约束 alter table emp add foreign key(dept_id) references dept(id) ;
无法删除技术研发部 ，因为小刘和小张信息 依赖技术研发部 记录 ！！！！！


多表设计原则 ：所有关系数据只能存在三种对应关系 （一对一、一对多、多对多）
1、多对多关系 ： 雇员和项目关系
一个雇员可以参与多个项目
一个项目可以由多个雇员参与


建表原则：必须创建第三张关系表，在关系表中引用两个实体主键 作为外键 
关系表中每条记录，代表一个雇员参与了一个项目 


2、一对多关系： 用户和博客关系
一个用户可以发表多篇博客
一个博客只能由一个作者 


建表原则：不需要创建第三方关系表，只需要在多方添加 一方主键作为 外键 


3、一对一关系 ： 这种关系很少见到  负责人和工作室
一个负责人 管理一个工作室
一个工作室 只有一个负责人 


建表规则：在任一方添加对方主键 作为外键 


====================================================================================================================
多表查询——笛卡尔积 
将A表中每条记录 与 B表中每条记录进行 匹配 获得笛卡尔积 
select * from emp;
select * from dept;


select * from emp,dept; 显示结果就是笛卡尔积 
笛卡尔积结果 就是 两个表记录乘积 例如A 表3条 B表4条 ---- 笛卡尔积 12条


笛卡尔积结果是无效的，必须从笛卡尔积中选取有效的数据结果 ！！！


多表查询 连接查询 内连接查询 
从A表中选择一条记录，去B表中找对应记录 ----- 内连接 必须A表和B表存在对应记录才会显示 


create table A(A_ID int primary key auto_increment,A_NAME varchar(20) not null);
insert into A values(1,'Apple');
insert into A values(2,'Orange');
insert into A values(3,'Peach');


create table B(A_ID int primary key auto_increment,B_PRICE double);
insert into B values(1,2.30);
insert into B values(2,3.50);
insert into B values(4,null);


使用内连接 select * from a,b where a.a_id = b.a_id;
* 内连接查询结果条数 一定小于 两个表记录较多哪个表 ----- 例如 A表3条 B表5条 ---- 内连接结果条数 <= 5


select * from emp,dept where emp.dept_id = dept.id ; 将emp 表和dept 表进行内连接 
在内连接查询时 添加条件，查询人力资源部有哪些 员工 ？？ select * from emp,dept where emp.dept_id = dept.id and dept.name ='人力资源部'; 
工资大于7000员工来自哪个部门？ select * from emp,dept where emp.dept_id = dept.id and emp.salary > 7000;


select * from emp,dept where emp.dept_id = dept.id ; 写法一
select * from emp inner join dept on emp.dept_id = dept.id ; 写法二