MySQL语句练习

SQL语言主要分为:

  • DQL：数据查询语言，用于对数据进行查询，如select
  • DML：数据操作语言，对数据进行增加、修改、删除，如insert、update、delete
  • TPL：事务处理语言，对事务进行处理，包括begin transaction、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 表名(
         id int 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:
   alter table 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 from goods;
2.as给表起别名
select s.id,s.name,s.gender from students as s;
# 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;

聚合函数

  1. count(col): 表示求指定列的总行数
  2. max(col): 表示求指定列的最大值
  3. min(col): 表示求指定列的最小值
  4. sum(col): 表示求指定列的和
  5. avg(col): 表示求指定列的平均值

1.求总行数

 

-- 返回非NULL数据的总行数.
select count(height) from student;

-- 返回总行数，包含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 by gender;

-- 根据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 by gender;

-- 统计不同性别的人的个数
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) from student);

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

 

posted @ 2020-03-14 20:43  知否-  阅读(576)  评论(0)    收藏  举报