mysql语句联系_MySQL语句练习

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;