MySQL初级

MySQL数据库的介绍

什么是数据库

数据库 (Database) 就是按照数据结构来组织，存储和管理数据的仓库

 

专业的数据库是专门对数据进行创建，访问，管理，搜索等操作的软件，比起我们自己用文件读写的方

 

式对象数据进行管理更加的方便，快速，安全

 

 作用

• 对数据进行持久化的保存
• 方便数据的存储和查询，速度快，安全，方便
• 可以处理并发访问
• 更加安全的权限管理访问机制

常见的数据库

• 关系型数据库： MySQL，Oracle，PostgreSQL，SQLserver。。。。
• 非关系型数据库：Redis内存数据库，MongoDB文档数据库。。。

mysql的基本命令

登录mysql,在终端输入以下命令，进行登录

mysql -u root -p

查看当前mysql中所有的库。

库=>数据库=>就像文件夹一样，库里面可以存储很多个表

show databases;

选择需要操作的库，打开库

 

use mysql ;

 

查看当前库中的所有数据表

 

show tables;

 

查看表中的数据 .

 

# 查看 user 表中的所有数据的所有字段

 

select * from user;

 

# 查看 user 表中的所有数据的 host 和 user 字段列

 

select host,user from user;

 

 库和表的概念与关系

库就像是文件夹，库中可以有很多个表

表就像是我们的excel表格文件一样

每一个表中都可以存储很多数据

mysql中可以有很多不同的库，库中可以有很多不同的表

表中可以定义不同的列（字段），

表中可以根据结构去存储很多的数据

创建库

create database 库名 default charset=utf8;

创建表的语法

 

create table 表名 (

     字段名 类型 字段约束 ,

     字段名 类型 字段约束 ,

     字段名 类型 字段约束 ,

)engine=innodb default charset=utf8;

 

engine=innodb：表引擎

 

 

添加数据

 

insert into 表名 (属性1,属性2,属性3，..) values ( 值1 , 值2 , 值3,.. );

 

 

MySQL基础操作

使用方法:

方式一: 通过在命令行敲命令来操作 ( 有助于命令的掌握)

方式二: 通过图型界面工具,如 Navicat 等(在熟练掌握后再使用)

方式三：通过编程语言(python,php,java,go...)执行mysql命令

SQL ( Structure query language ) 结构化查询语言

     SQL语言分为4个部分：DDL（定义）、DML（操作）、DQL（查询）、DCL（控制）

SQL语句中的快捷键

\G 格式化输出（文本式，竖立显示）

\s 查看服务器端信息

\c 结束命令输入操作

\q 退出当前sql命令行模式

\h 查看帮助

操作数据库的步骤:连接, 打开库, 操作, 关闭退出

通过命令行连接MySQL

数据库操作

 

查看数据库 show databases;

创建数据库 create database 库名 default charset=utf8;

删除数据库 drop database 库名;

打开数据库 use 库名;

数据表操作

 

查看表: show tables;

创建表: create table 表名(字段名1 类型,字段名2 类型)engine=innodb default charset=utf8;

创建表: 如果表不存在,则创建, 如果存在就不执行这条命令

     create table if not exists 表名(字段1 类型,字段2 类型);

 

删除表: drop table 表名;

表结构: desc 表名;

查看建标语句：show create table users;

数据操作 增删改查

 

插入

insert into 表名(字段1,字段2,字段3) values(值1,值2,值3);

insert into 表名(字段1,字段2,字段3) values(a值1,a值2,a值3),(b值1,b值2,b值3);

查询

select * from 表名;

select 字段1,字段2,字段3 from 表名;

select * from 表名 where 字段=某个值;

修改

update 表名 set 字段=某个值 where 条件;

update 表名 set 字段1=值1,字段2=值2 where 条件;

update 表名 set 字段=字段+值 where 条件;

删除

delete from 表名 where 字段=某个值;

退出 MySQL

 

exit; 或者 quit;

 

MySQL的数据类型

数据类型是定义列中可以存储什么类型的数据以及该数据实际怎样存储的基本规则

数据类型限制存储在数据列列中的数据。例如，数值数据类型列只能接受数值类型的的数据

在设计表时，应该特别重视所用的数据类型。使用错误的数据类型可能会严重地影响应用程序的功能和性能。

更改包含数据的列不是一件小事（而且这样做可能会导致数据丢失）。

数据类型：整型、浮点型、字符串、日期等

字符串数据类型

 

最常用的数据类型是串数据类型。它们存储串，如名字、地址、电 话号码、邮政编码等。

不管使用何种形式的串数据类型，串值都必须括在引号内

定长串：char

1. 接受长度固定的字符串，其长度是在创建表时指定的。定长列不允许存储多于指定长度字符的数据。

2. 指定长度后，就会分配固定的存储空间用于存放数据

变长串 varchar

存储可变长度的字符串 varchar(7) 如果实际插入4个字符, 那么它只占4个字符位置,当然插入的数据长度不能超过7个字符。

注意

Text 变长文本类型存储

char(7) 不管实际插入多少字符，它都会占用7个字符位置

既然变长数据类型这样灵活，为什么还要使用定长数据类型？

回答：因为性能，MySQL处理定长列远比处理变长列快得多。

 

当需要存储很大的文本的时候可以使用Text 变长文本类型存储

 

数值类型

 

数值数据类型存储数值。MySQL支持多种数值数据类型，每种存储的数值具有不同的取值范围。支持的取值范围越大，所需存储空间越多

与字符串不一样，数值不需要括在引号内

decimal(5, 2) 表示数值总共5位, 小数占2位

tinyint 1字节(8位) 0-255（无符号）  -128，127（有符号）

int 4字节。   0-42亿（无符号） -21亿，21亿（）

float. 

MySQL中没有专门存储货币的数据类型，一般情况下使用DECIMAL(8, 2)

 

所有数值数据类型（除BIT和BOOLEAN外）都可以有符号或无符号

有符号数值列可以存储正或负的数值

无符号数值列只能存储正数。

默认情况为有符号，但如果你知道自己不需要存储负值，可以使用UNSIGNED关键字

日期和时间类型

 

datetime 8字节1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

表的字段约束

• unsigned 无符号(给数值类型使用，表示为正数，不写可以表示正负数都可以)
• not null 不能为空,在操作数据库时如果输入该字段的数据为NULL ，就会报错
• default 设置默认值
• primary key 主键不能为空,且唯一.一般和自动递增一起配合使用。
• auto_increment 定义列为自增属性，一般用于主键，数值会自动加1
• unique 唯一索引(数据不能重复:用户名)可以增加查询速度,但是会降低插入和更新速度

 主键

表中每一行都应该有可以唯一标识自己的一列，用于记录两条记录不能重复，任意两行都不具有相同的主键值

应该总是定义主键 虽然并不总是都需要主键，但大多数数据库设计人员都应保证他们创建的每个表具有一个主键，以便于以后的数据操纵和管理。

• 记录一旦插入到表中，主键最好不要再修改
• 不允许NULL
• 自增整数类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用自己预先生成主键

unique与primary key，它们的区别如下：

1、unique key要求列唯一，但不包括Null字段，也就是约束的列可以为空且仅要求列中的值除Null之外不会重复即可。

2、primary key也要求列唯一，同时又限制字段的值为not Null，相当于primary key = unique+not null

3、一个表可以有多个unique,但是只能有一个primary key

修改表结构

语法格式：alter table 表名 action (更改的选项)

添加字段

# 语法： alter table 表名 add 添加的字段信息

-- 在 users 表中 追加 一个 num 字段

alter table users add num int not null ;

 

-- 在指定字段后面追加字段 在 users 表中 age 字段后面 添加一个 email 字段

alter table users add email varchar ( 50 ) after age;

 

-- 在指定字段后面追加字段，在 users 表中 age 字段后面 添加一个 phone

alter table users add phone char ( 11 ) not null after age;

 

-- 在表的最前面添加一个字段

alter table users add aa int first ;

 

删除字段

# 删除字段 alter table 表名 drop 被删除的字段名

alter table users drop aa;

 

修改字段

语法格式： alter table 表名 change | modify 被修改的字段信息

change : 可以修改字段名，

modify : 不能修改字段名。

 

# 修改表中的 num 字段 类型，使用 modify 不修改表名

alter table users modify num tinyint not null default 12 ;

 

# 修改表中的 num 字段 为 int 并且字段名为 nn

alter table users change num mm int ;

 

# 注意：一般情况下，无特殊要求，不要轻易修改表结构

 

修改表名

# 语法：alter table 原表名 rename as 新表名

更改表中的自增的值

# 在常规情况下， auto_increment 默认从 1 开始继续递增

alter table users auto_increment = 1000 ;

 

修改表引擎

# 修改表引擎语句

alter table users engine = 'myisam' ;

 

# 推荐在定义表时，表引擎为 innodb 。

 

删除表

 

drop table 表名

 

数据的增删改

添加数据

 

格式： insert into 表名 [( 字段列表 )] values( 值列表 ...);

 

-- 标准添加（指定所有字段，给定所有的值）

insert into stu(id,name,age,sex,classid) values ( 1 , 'zhangsan' , 20 , 'm' , 'lamp138' );

 

-- 指定部分字段添加值

insert into stu(name,classid) value( 'lisi' , 'lamp138' );

 

-- 不指定字段添加值

insert into stu value( null , 'wangwu' , 21 , 'w' , 'lamp138' );

 

-- 批量添加值

insert into stu values

-> ( null , 'zhaoliu' , 25 , 'w' , 'lamp94' ),

-> ( null , 'uu01' , 26 , 'm' , 'lamp94' ),

-> ( null , 'uu02' , 28 , 'w' , 'lamp92' ),

-> ( null , 'qq02' , 24 , 'm' , 'lamp92' ),

-> ( null , 'uu03' , 32 , 'm' , 'lamp138' ),

-> ( null , 'qq03' , 23 , 'w' , 'lamp94' ),

-> ( null , 'aa' , 19 , 'm' , 'lamp138' );

 

修改数据

 

格式： update 表名 set 字段 1= 值 1, 字段 2= 值 2, 字段 n= 值 n... where 条件

 

-- 将 id 为 11 的 age 改为 35 ， sex 改为 m 值

update stu set age= 35 ,sex= 'm' where id= 11 ;

 

 

-- 将 id 值为 12 和 14 的数据值 sex 改为 m ， classid 改为 lamp92

update stu set sex= 'm' ,classid= 'lamp92' where id= 12 or id= 14 -- 等价于下面

update stu set sex= 'm' ,classid= 'lamp92' where id in ( 12 , 14 );

删除数据

 

格式： delete from 表名 [where 条件 ]

 

-- 删除 stu 表中 id 值为 100 的数据

delete from stu where id=100;

 

-- 删除 stu 表中 id 值为 20 到 30 的数据

delete from stu where id>=20 and id<=30;

 

 

-- 删除 stu 表中 id 值为 20 到 30 的数据（等级于上面写法）

delete from stu where id between 20 and 30;

 

-- 删除 stu 表中 id 值大于 200 的数据

delete from stu where id>200;

 

数据查询

检索数据select

检索单列数据

 

select name from stu;

 

如果没有明确排序查询结果，则返回数据的顺序没有特殊意义。

返回数据的顺序和添加数据的顺序可能不同。

检索多个列

select id,name,age,sex from stu;

检索所有列

select * from stu;

一般情况下，最好别使用通配符查询，除非你确实需要表中的所有数据

因为这样会降低检索和应用程序的性能。

检索不同的行 DISTINCT

select distinct age from stu;

返回的结果只有不同的值

如果查询的列多于一个，那么他会把所有的列都检索出来，没有效果。

限制结果 LIMIT

select * from stu limit 3,4;

LIMIT3,4 的含义是从行3开始的4行

常用于分页查询LIMIT 页码，每页显示数

对检索数据进行排序 ORDER BY

排序数据 ORDER BY

select * from stu order by age;

通过age对检索出来的数据进行排序输出（默认升序,可以使用DESC进行降序）

按多个列排序

select * from stu order by age,id;

优先对age进行排序，当age相同的时候再按id进行排序

select * from stu order by age,id DESC;

对age进行升序排序，然后对id进行降序排序

注意

给出order by 语句应该再from 之后，如果有limit语句，应该在order by 之后

检索条件过滤WHERE

select name from stu where age = 19;

在同时使用order by和where语句时，应该让order by位于where之后

组合WHERE子句

在查询的时候允许出现多个where子句

AND和OR

select name from stu where age = 22 and sex = 'm';

select name from stu where age = 22 or sex = 'm';

由于AND的优先级高于OR，在多个这样语句的时候最好用括号括起来

IN与NOT

select name from stu where id  in (1,2,3);    相当于OR

NOT与IN相反

 IN操作符的优势：

在使用长的合法选择项清单时，语法更加清楚直观

计算的次序更容易管理（使用的操作符少）

IN的操作符比OR的操作符执行更快

IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE子句。

LIKE和通配符

LIKE指示MysQL，后跟的搜索模式利用通配符进行模糊查询

百分号(%)通配符在搜索串中，%表示任何字符出现任意次数

select name from stu where name like 'a%"

select name from stu where name like '%a'

select name from stu where name like '%a%"

下划线(_）通配符下划线的用途与%一样，但下划线只匹配单个字符而不是多个字符

注意

不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。

在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。

把通配符置于搜索模式的开始处(比如%放在第一个)，搜索起来是最慢的。
 

聚合函数

select count(id),max(age),min(age), sum(age),avg(age) from stu;

注意：

在使用count时,如果指定列名，则指定列的值为NULL的行被忽略，但如果COUNT()函数中用的是星号(*)，则不忽略
 

分组GROUP BY

例如我们需要获取某个班级的学员人数:
select count(*) as nums from stu where class_id = 2

但如果要返回每个班级的人数怎么办?
此时就需要使用分组了,分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。
select class_id , count(*) as nums from stu group by class_id

HAVING

除了能用GROUP BY分组数据外，MySQL还允许过滤分组，规定包括哪些分组，排除哪些分组。
例如，可能想要列出至少有两个订单的所有顾客。为得出这种数据，必须基于完整的分组而不是个别的行进行过滤
事实上，目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代。
唯一的差别是WHERE过滤行,而HAVING过滤分组
 

select class_id, count(*) as nums from stu group by class_id having nums >= 5;