数据库mysql 中的相关指令

mysql 中的相关指令：

显示所有的数据库	show databases;
创建数据库（create database 数据库名 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;）	create database 数据库名　（charset = utf8）;
删除某个数据库	drop database 数据库名;
设置数据库用户名密码	set password for root@localhost = password('123456');
查看数据库中的表	show tables；
查看当前使用的是哪个数据库	select database();
查看表的结构	desc 表名;
查看表详细的结构	show create table 表名 \G
修改表名	alter table  旧表名 rename 新表名;
表中添加字段（默认是向末尾添加新字段）	alter  table  表名  add  字段名  字段类型及约束条件  （这里加个first就代表向表中第一位置添加字段）
表中指定位置添加字段	alter table 表名 add 字段名 字段类型及约束条件 after 表中其他字段;
修改字段的数据类型或约束条件（字段不重命名）	alter table 表名 modify 字段名 新的字段类型
修改字段的数据类型或约束条件（字段重命名）	alter table 表名 change 旧字段名  新字段名（不改字段名时，这里还要写一遍旧的字段名） 数据类型及约束
一次添加多个字段	alter table 表名 add (新字段名1 类型及约束  ,   新字段名2 类型及约束)
删除某个字段	alter table 表名 drop 要删除的字段名
全列插入数据	insert into 表名 value(s)  ( , , ,)，（这里可以再插入一条数据）;
部分插入（没有插入的字段采用默认值，没有默认值就为空，前提是这个字段允许为空）	insert into 表名 （要插入的字段名，中间用逗号隔开） values (插入的数据)
修改数据（可以修改多条数据，在没有指定where条件时，将修改该表内所有的数据）	update 表名 set 字段名=新的数据 where 条件（比如 id=1）;
删除数据（可以删除多条数据，在没有指定where条件时，将删除该表内所有的数据）	delete from 表名 where 条件（比如 id=1）;
查询所有的列	select * from 表名;
查询指定的列	select 字段名1，字段名2 from 表名;
给表起个别名	例   select s.name,s.age from students as s;
去重	select distinct 字段名 from 表名;
条件查询，where后面支持多种运算符，进行条件处理  比较运算符	select * from students where id(<,>.=,!=,>=,<=);
条件查询，where之逻辑运算符  and  or  not	配合比较运算符一起用
模糊查询 like  %表示任意多个任意字符， a%b  >>  aab, axxb, auuub, ab	select * from students where name like '周%';
模糊查询 like   下划线_表示一个任意字符 a_b ==> acb, aub, aib	select * from students where name like '周_';
范围查询  in 表示在一个非连续的集合内	select * from students where  id  in (1,2,3);
between...and...表示在一个连续的集合内	select * from students where  id between 1 and 3;
空判断  null 与 '' 是不同的  注意这里用的是 is ,而不是=	select * from students where height is null
优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符	and比or先运算，如果同时出现并希望先算or，需要结合()使用
排序order by 默认asc从小到大排序（升序）desc从大到小排（降序）	select * from students order by height asc，age asc;(当升高一样时按照年龄排序)
	查询的时候又有条件又有排序，那么条件要写在排序的前面，否则报语法错误。
count()  计算行数	select count(*或某个字段名) from  表名;
max(),min()  此字段(列)最大或最小值	select min(字段名)  from 表名;
sum() 求此字段（列）的和	select  sum(字段名)  from 表名;
avg()  求此字段（列）的平均值（此列值的和/此字段的行数）	select  avg(字段名)  from 表名;
分组group by 字段名（与group_concat使用看谁和谁在一个分组里，和count看每个分组的数量）	select gender,group_concat(name) from 表名 group by gender;（按照性别分组，显示每个性别下都有哪些人）  下图
having	对group by 的结果进行筛选，having的语法位置是在group by的后面
limit	限制查询结果的数量
A表 inner（left,right） join B表 on 条件	连接查询

 

 

windows命令行进入centos的mysql数据库  >>>　　mysql -h  虚拟机中centos的ip地址  -u  root(这里root是管理员用户名)  -p

创建表时完整性约束条件

1）主键，primary key　　　　2)非空 not null ,注意当某字段设置成非空且没有设置默认值时，在进行部分插入时要注意该字段必填。

3）唯一 unique  此字段的值不可重复  　　    4）默认值，default  ,当某条数据在此字段上不填写内容时，使用默认值

5）外键， foreign key　　　　6)自动增加， auto_increment  ,设置某属性值自动增加，mysql 语句的特色 

数据类型

1） 整数 int  , tinyint   

 2)  浮点数 decimal ,decimal(5,2) 代表共存在5位数，小数占2位   

 3）字符串   char（固定长度的字符串） , varchar （可变长度的字符串）   

如char（3），如果填充“ab”时会补一个空格为“ab空格” 。 而varchar（3）则不会，3只代表最大字节的长度，这两个在输入字节长度大于规定最大长度时会报错

4） 日期时间 date 示例 2020-03-06, time 示例 10:30:32, datetime 示例 2020-03-06  10:30:32

5）枚举类型  enum  

使用数据类型原则：尽量使用取值范围小的，而不是用大的，这样可以节省存储空间

分组group by  字段名

1）与group_concat()函数一起使用

 

 

 

 2）与聚合函数一起使用：

下面是表达男生组中有多少人，女生组中有多少人

 

下面是表达男生组中身高最高的是多少，女生组中身高最高的是多少

 

 

 

 having语句：having后面的条件运算符与where的相同，有group by语句的出现不一定有having语句的出现，那有having语句的出现就一定会有group by语句的出现。

 

limit语句：不指定初始位置时，记录从第一条记录开始显示。显示记录的条数有limit关键字指定。

 

 

 

也可以指定从哪条记录开始显示，并且可以指定显示多少条记录。

查询时，从记录的下标为3的这个位置开始查起(下标是从0开始进行记数的)，一共查询3条记录。前面的3代表，从哪条记录开始查起，后面的3代表，一共查询几条记录，它们之间用逗号分开。    limit关键字是mysql中所特有的。该关键字可以指定需要显示的记录的初始位置，0代表显示第一条记录。

 

 

 

连接查询（分为内连接查询，右连接查询，左连接查询）

1)内连接查询:查询的结果是两表匹配到的数据(A表 inner join B表 on 条件)

 

2)左连接查询:查询的结果为两表匹配到的数据，左表特有的数据，对于右表不存在的数据使用null填充（左表为主表）(A表 left join B表 on 条件)

如下查询学生的包括其班级的详细信息（很明显意思是以学生表为主表。班级表为附表）

select s.name,c.name from students as  s left join classes as c on s.cls_id=c.id; 

上面会以显示学生为主要条件，当存在学生表中cls_id无法在班级表id找到时，两表连接后会显示该学生数据，但后面的班级表会显示null,但班级表中有的id ，而这个id在学生表cls_id中无法找到时，此班级表将不会显示

 

3)右连接查询:查询的结果为两表匹配到的数据，右表特有的数据，对于左表不存在的数据使用null填充（右表为主表）(A表 right join B表 on 条件)

 

 

 自关联查询（典型例题：省市区三级联动）sql文件直接百度有很多

如何导入sql文件>>>　　这里将百度下载的sql文件放到桌面上，注意   命令行 cd Desktop 到桌面上。

然后命令行在桌面上进入mysql，选择数据库。然后 source sql文件名.后缀

当然，也可以直接  source  sql文件路径。

好了开始自关联查询：比如查询广东省都有哪些城市。（这里就用到了自关联查询，即将一张表看做两张表，自己关联自己）

 

 

 子查询：在一个selet语句中嵌入另外一个select语句。子查询是嵌入到主查询中。【下面的例子是在mysql数据库中有两个表分别是学生表和班级表下完成的，学生表有一个外键班级id号cls_id】

子查询分类:

1）标量子查询（查询结果是一行一列）   如查询学生身高大于平均身高的人。

 　　

 

 2）列级子查询（子查询的结果是一列多行）  如查询有学生的班级有哪些？

可以通过内连接查询

select  distinct classes.name from classes inner join students on classes.id=students.cls_id；

通过子查询去实现　　　　你会发现要先查询学生表的cls_id字段，select  cls_id from students 结果是一个一列多行的表

select  name  from  classes  where id in ( select  cls_id from students )；

 

3 )行级子查询（子查询的结果是一行多列）  　　如查询表中年龄最大而且身高最高的人

要先查询表中年龄最大的值和身高最高的值 　　select   max(age),max(height)   from  student；

  　　

 

select * from students where  (age,height) = （select max(age),max(height)   from  student）;   注意，这里查询的结果是表中身高最高且同时年龄最大的人

 

4）表级子查询   　　（子查询的结果是多行多列）   表级子查询一般都能通过连接查询实现，建议直接连接查询。

 

子查询中的关键字

 1）any 和same的用法一样（在条件查询的结果中匹配任意一个即可，等价于 in）　　　格式：　　主查询   where  某字段名  =  any  （列级子查询） 

如查找有学生的班级的名，最终要查询的是班级的信息　　可以左连接查询 

 select classes.name  from  classes left join students on classes.id=students.cls_id;

这里子查询语句：

select  classes.name  from  classes where  id =any（select cls_id from students）; 　

或 select  classes.name  from  classes where  id =some（select cls_id from students）; 　

或 select   classes.name  from  classes where  id in （select cls_id from students）　；

 2) all关键字   （需要满足内层查询的所有条件）    　　格式：　　主查询  where  某字段名  =  all  （列级子查询）   等于里面所有

3）exists关键字    exists关键字后面的子查询语句只返回true或false ,只有当返回true时，外层的主查询语句才会执行

　　　　　　　　注意：exists关键字比in关键字的运行效率高，在实际开发时，大数据量时推荐使用exists关键字。

 

数据库设计：一共有6个范式，但一般需要遵守3个范式即可。

1）每一列都是不可分割的原子数据（比如在一个电话字段，这个字段里可能有固定电话和移动电话，这是一个字段里存在两个数据，不合理，应分成两个字段）

2）在第一范式基础上，一个表中必须有一个主键，没有包含在主键的列必须完全依赖于主键（可以通过一个字段去关联另一张表）

3）在第二范式的基础上，非主键列直接依赖于主键，而不是传递依赖（在范式二分离的表的情况下再次分离表）

 

外键约束的创建与删除

创建外键有两种方法，一是在创建表的时候对某个字段指定外键。 

二是创建完表后修改表添加  alter table A表  add foreign key  A表中要添加的外键的字段名  references B表（B表要与A表连接的字段名）

注意：添加外键的那个字段的数据类型必须要与相关联的那个表的字段的数据类型一致。

 删除外键约束  alter table 表名  drop foreign key  外键名；     这个外键名需要 show create table 表名 \G   查询

但再次上面方法查询表  发现还有个key没删，  alter table 表名  drop  key key的名字;   进行删除。