SQL语言主要分为:
DQL:数据查询语言,用于对数据进行查询,如select
DML:数据操作语言,对数据进行增加、修改、删除,如insert、update、delete
TPL:事务处理语言,对事务进行处理,包括begintransaction、commit、rollback
DCL:数据控制语言,进行授权与权限回收,如grant、revoke
DDL:数据定义语言,进行数据库、表的管理等,如create、drop对于程序员来讲,重点是数据的增、删、改、查,必须熟练编写DQL、DML,能够编写DDL完成数据库、表的操作,其它操作如TPL、DCL了解即可.
MySQL连接服务端命令
mysql -u root -p
-u 后面是登录的用户名
-p 后面是登录密码
数据库操作的SQL语句
1.查看所有数据库
show databases;
2.创建数据库
create database 数据库名 charset=utf8;
3.切换数据库
use 数据库名;
4.查看当前使用过的数据库
select database();
5.删除数据库
drop database 数据库名;
6.查看创库SQL语句
show create database 数据库名;
表结构操作的SQL语句
1.查看当前数据库中所有表
show tables;
2.创建表
create table表名(
idint unsigned primary key auto_increment not null,
字段名称 数据类型 可选的约束条件,
......
);
3.修改表-添加字段
alter table 表名 add字段名 字段类型 字段约束;
eg:alter table student add gender enum('男','女');
4.修改表-修改字段类型
alter table表名 modify 字段 字段类型 约束
eg:alter table student modify birthday datetime;
5.修改表-修改字段名和类型
alter table表名 change 原字段名 新字段名 字段类型 字段约束;
eg:alter table student birthday birth date not null;
6.修改表-删除字段
alter table表名 drop 字段名;
eg:
altertable student drop birth;
7.查看创表SQL语句
show create table 表名;
8.删除表
drop table 表名;
表数据操作的SQL语句
1.查询数据
1.查询所有列select * from表名;2.查询部分列select 列1,列2,... from 表名;
2.添加数据
1.全列插入insert into 表名 values(...);
eg:insert into student (1,'xx',default,default,'男');2.部分列插入insert into 表名 (列1,列2,...) values(值1,值2,...);
eg:insert into student(name,age) values('xx',18);3.全列多行插入insert into 表名 values(...),(...),...;
eg:insert into student values(2,'cc',55,166,'nan'),(3,'zz',44,176,'男');4.部分列多行插入insert into 表名 (列1,列2,...) values(值1,值2,...),(值1,值2,...),...;
eg:insert into student (name,height) values('张三',144),('李四',177);
3.修改数据
update 表名 set 字段1=值1,字段2=值2 where条件;
eg:update student set age=18,gender='女' where id = 6;
4.删除数据
物理删除:delete from 表名 where条件;
eg:delete from student where id =6;
逻辑删除:
添加删除表示字段,is_delete 0表示未删除,1表示已删除
as关键字
1.as给字段起别名select sum(price) as sum_price fromgoods;2.as给表起别名select s.id,s.name,s.gender from students ass;
# as可省略
distinct关键字
# distinct可以去除重复数据行select distinct 列1,... from表名;
eg:select distinct gender from student;
where条件查询
1.比较运算符查询
等于: =
大于: >
大于等于: >=
小于: <
小于等于: <=
不等于: != 或 <>
例1:查询编号大于3的学生:select * from student where id > 3;
例2:查询编号不大于4的学生:select * from student where id <= 4;
例3:查询姓名不是“黄蓉”的学生:select * from student where name != '黄蓉';
例4:查询没被删除的学生:select * from student where is_delete = 0;
2.逻辑运算符
and
or
not
例1:查询编号大于3的女同学:select * from student where id > 3 and gender = '女';
例2:查询编号小于4或没被删除的学生:select * from student where id < 4 or is_delete = 0;
例3:查询年龄不在10岁到15岁之间的学生:select * from student where not(age >= 10 and age <= 15);
3.模糊查询
like是模糊查询关键字
%表示任意多个任意字符
_表示一个任意字符
例1:查询姓黄的学生:select * from student where name like '黄%';
例2:查询姓黄并且“名”是一个字的学生:select * from student where name like '黄_';
例3:查询姓黄或叫靖的学生:select * from student where name like '黄%' or name like '%靖';
4.范围查询
between .. and .. 表示在一个连续的范围内查询
in 表示在一个非连续的范围内查询
例1:查询编号为3至8的学生:select * from student where id between 3 and 8;
例2:查询编号不是3至8的男生:select * from student where (not id between 3 and 8) and gender = '男';
5.空判断查询
判断为空使用: is null
判断非空使用: is not null
例1:查询没有填写身高的学生:select * from student height is null;
排序
select * from 表名 order by 列1 asc|desc, 列2 asc|desc,...;
说明:1.先按照列1进行排序,如果列1的值相同时,则按照 列2 排序,以此类推2.asc从小到大排列,即升序3.desc从大到小排序,即降序4.默认按照列值从小到大排序(即asc关键字)
例1:查询未删除男生信息,按学号降序:select * from student where is_delete = 0 and gender = '男' order by id desc;
例2:显示所有的学生信息,先按照年龄从大-->小排序,当年龄相同时 按照身高从高-->矮排序:
select * from student order by age desc,height desc;
分页查询
select * from 表名 limit start,count;
说明:
limit是分页查询关键字
start表示开始行索引,默认是0
count表示查询条数
例1:查询前3行男生信息:select * from student gender = '男' limit 0,3;
简写:select * from student gender = '男' limit 3;
已知每页显示m条数据,求第n页显示的数据
查询学生表,获取第n页数据的SQL语句:select * from student limit (n-1)*m,m;
聚合函数
count(col): 表示求指定列的总行数
max(col): 表示求指定列的最大值
min(col): 表示求指定列的最小值
sum(col): 表示求指定列的和
avg(col): 表示求指定列的平均值
1.求总行数
--返回非NULL数据的总行数.
select count(height) fromstudent;--返回总行数,包含null值记录;
select count(*) from student;
2.求最大值
--查询女生的编号最大值
select max(id) from student where gender = '女';
3.求最小值
--查询未删除的学生最小编号
select min(id) from student where is_delete = 0;
4.求和
--查询男生的总身高
select sum(height) from student where gender = '男';
5.求平均值
--求男生的平均身高, 聚合函数不统计null值,平均身高有误
select avg(height) from student where gender = '男';--求男生的平均身高, 包含身高是null的
select avg(ifnull(height,0)) from student where gender = 1;
ifnull函数: 表示判断指定字段的值是否为null,如果为空使用自己提供的值。
分组查询
1.group by的使用
--根据gender字段来分组
select * from table group bygender;--根据name和gender字段进行分组
select * from table group by name,gender;
2.group by + group_concat()的使用
# group_concat(字段名): 统计每个分组指定字段的信息集合,每个信息之间使用逗号进行分割--根据gender字段进行分组, 查询gender字段和分组的name字段信息
select gender,group_concat(name) from table group by gender;
3.group by + 聚合函数
--统计不同性别的人的平均年龄
select gender,avg(age) as avg_price from table group bygender;--统计不同性别的人的个数
select gender,count(*) from table group by gender;
4.group by + having的使用
# having作用和where类似都是过滤数据的,但having是过滤分组数据的,只能用于group by
--根据gender字段进行分组,统计分组条数大于2的
select gender,count(*) from table group by gender having count(*) >2;
内连接查询
select 字段 from 表1 inner join 表2 on 表1.字段1 =表2.字段2--inner join 就是内连接查询关键字, inner可忽略不写--on 就是连接查询条件
例1:使用内连接查询学生表与班级表:
select * from student s join class c on s.id = c.s_id;
左连接查询
select 字段 from 表1 left join 表2 on 表1.字段1 =表2.字段2;--left join 就是左连接查询关键字--on 就是连接查询条件--表1 是左表--表2 是右表
例1:使用左连接查询学生表与班级表:select * from student s left join class c on s.id = c.s_id;
右连接查询
select 字段 from 表1 right join 表2 on 表1.字段1 =表2.字段2;--right join 就是右连接查询关键字--on 就是连接查询条件--表1 是左表--表2 是右表
例1:使用右连接查询学生表与班级表:select * from student s right join class c on s.id = c.s_id;
自连接查询
select a.aid,a.atitle,a.pid,b.atitle from area a join area b on a.pid = b.pid where b.atitle = '广东省';
子查询
例1. 查询大于平均年龄的学生:select * from student where age > (select avg(age) fromstudent);
例2. 查询学生在班的所有班级名字:select name from class where id in (select c_id from student s where s.c_id is not null);
例3. 查找年龄最大,身高最高的学生:select * from student where (age,height) = (select max(age),max(height) from student);
数据库设计-三范式
第一范式:强调的是列的原子性,即不能够在分成其他几列
第二范式:满足第一范式,表必须有一个主键,非主键字段必须完全依赖于主键,而不能只依赖于主键的一部分
第三范式:满足第二范式,非主键列必须直接依赖于主键,不能存在传递依赖
E-R 模型
E-R模型即实体-关系模型,描述数据库存储数据的结构模型
E-R模型效果图:
实体: 用矩形表示,并标注实体名称
属性: 用椭圆表示,并标注属性名称,
关系: 用菱形表示,并标注关系名称
一对一
一对多
多对多
一对一的关系:
说明:
关系也是一种数据,需要通过一个字段存储在表中
1对1关系,在表A或表B中创建一个字段,存储另一个表的主键值
一对多的关系:
说明:
1对多关系,在多的一方表(学生表)中创建一个字段,存储班级表的主键值
多对多的关系:
说明:
多对多关系,新建一张表C,这个表只有两个字段,一个用于存储A的主键值,一个用于存储B的主键值
高级使用:
1.将查询结果插入到其他表中
--把goods表中的商品分类添加到商品分类表
insert into good_cates(name) select cate_name from goods group by cate_name;
2.使用连接更新表中某个字段数据
--将goods表中的分类名称更改成商品分类表中对应的分类id
update goods g join good_cates gc on g.cate_name = gc.name set g.cate_name = gc.id;