数据库Mysql

目录

（一）认识数据库

1、什么是数据库

2、数据库分为两大类

3、Mysql简介

4、数据库服务器、数据库和表的关系

5、Linux系统中的数据库

6、SQL介绍

7、SQL语法特点

（二）数据库常用操作指令

1、Mysql登录

2、Mysql退出

3、创建、查看、删除、使用数据库

4、创建、查看、删除、修改数据表

5、操作命令

5、数据表的增删改查

6、数据表的排序、聚合命令、分组

7、数据表的连接查询、子查询

---

（一）认识数据库

1、什么是数据库

DB的全称是data base，即数据库的意思。数据库实际上就是一个文件集合，是一个存储数据的仓库，本质就是一个文件系统，数据库是按照特定的格式把数据存储起来，用户可以对存储的数据进行增删改查操作

2、数据库分为两大类

关系型数据库：是建立在关系模型基础上的数据库Mysql、Oracle、DB2、SQL Server。
非关系型数据库：（NO  SQL数据库）通常指数据之间无关系的数据库mongodb   redis。

3、Mysql简介

Mysql是一个关系型数据库管理系统，由瑞典Mysql  AB公司开发。
世界上最流行的几款数据库之一。
Mysql的优点：是一款轻量级数据库，免费、开源、适用于中大型的网站。

4、数据库服务器、数据库和表的关系

所谓安装数据库服务器，只是在机器上装了一个数据库管理系统（比如Mysql），用来管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。
为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体的数据。

5、Linux系统中的数据库

1、Xshell是一个用于Windous平台的强大的SSH，Telnet终端仿真软件。它使得用户能轻松和安全的从Windous PC上访问Linux主机。
2、Xftp是一个用于Windous平台的强大的FTP和SFTP文件传输程序，Xftp能安全的在Linux和Windous PC之间传输文件。
3、Navicat是一套快速，可靠并价格相当便宜的数据库管理工具，专为简化数据库的管理及降低系统管理成本而设。

6、SQL介绍

SQL是Structured Query Language（结构化查询语言）的缩写。SQL是专为数据库而建立的操作命令集，是一种功能齐全的数据库语言。在使用它时，只需要发出“做什么”的命令，“怎么做”是不用使用者考虑的。

7、SQL语法特点

1、不区分大小写。
2、关键字  字段名  表名  需要用空格或者逗号隔开。
3、每一个SQL语句是用分号结尾。
4、语句可以写一行也可以分开写多行。

（二）数据库常用操作指令

1、Mysql登录

①mysql -uroot -p密码
②mysql -uroot -p密码
   Enter password（输入密码）
③mysql -hip -p端口 -uroot -p
④mysql     host=ip     user=root     password=密码

2、Mysql退出

①exit
②quit

3、创建、查看、删除、使用数据库

①创建数据库命令：create database
●创建数据库：
格式：create database [数据库名称]；
例如：create database qwer_db；
●创建数据库，并指定字符集：
格式：create database [数据库名称] character set [字符集名]；
例如：create database qwer_db character set utf8；
●创建数据库，并指定字符集、排序规则：
格式：create database [数据库名称] character set [字符集名] collate [排序规则]；
例如：create database qwer_db character set utf8 collate utf8_general_ci;

②显示数据库命令：show databases
●显示数据库:
show databases
●模糊查询数据库：
show databases like '%qwer%';
●查看查询某个数据库的创建语句、字符集：
格式：show create databases [数据库名称]；
例如：show create databases qwer_db;

③数据库删除命令：drop database
格式：drop database [数据库名称]；
例如：drop database qwer_db；

④选中某个数据库：use数据库名称
格式：use [数据库名称]；
例如：use qwer_db；
注：这个命令可以不加分号。

4、创建、查看、删除、修改数据表

①数据在数据库中的储存方式
②表中列的数据类型-数值型

③关于数值型的说明
有符号和无符号（UNSIGNED）是什么？
在计算机中，可以区分正负的类型，称为有符号类型。无正负的类型，称为无符号类型，简单地理解为就是，有符号值可以表示负数，0以及正数，无符号值只能为0或正数。
FLOAT(10,2)
FLOAT类型，总长度为10，小数点后两位为2
DOUBLE和DECIMAL类似。
超出范围会四舍五入。

④表中列的数据类型-字符型

⑤关于字符型的说明
CHAR和VARCHAR
char定长字符串 varchar变长字符串
我们看到茶村的结果是一样的，但实际上他们存储时占用的长度是不一样的。
CHAR类型不管存储的值的长度是多少，都会占用（）里设置的字数而VARCHAR则占用实际长度+1个字节。
5.0版本以上（20），指的是20字符，无论存放的是数字、字母还是汉字，都可以存放20个。

⑥表中列的数据类型-日期型

⑦TIMESTAMP和DATETIME的异同
●相同点：两者都可用来表示YYYY-MM-DD HH:MM:SS类型的日期
●不同点：
两者的存储方式不一样，对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。（ps：中国时区为+8区）而对于DATETIME，不做任何改变，基本上是原样输入和输出。
两者所能存储的时间范围不一样，timestamp所能存储的时间范围为：‘1970-01-01 00：00:01.000000’到2038-01-19 03:14:07.999999‘
datetime所能存储的时间范围为：’1000-01-01 00:00:00.000000'到‘9999-12-31 23:59:59.999999’

5、操作命令

①创建表
●创建表基础命令CREATE TABLE
格式：CREATE TABLE [表名]（[列名1] [列类型]，[列名2] [列类型]，[列名3] [列类型]）;
例如：CREATE TABLE STU (ID INT，NAME VARCHAR（20），AGE INT)；
注：
●创建表前，要先选中某个数据库（use[数据库名称]）。
●列和列之间用逗号隔开，列内用空格隔开。
●创建表时，要根据需保存的数据创建相应的列，并根据要存储数据的类型定义相应的列类型。

②约束条件
约束条件就是给列加一些约束，使该字段存储的值更加符合我们的预期。

约束条件	含义
UNSIGNED	无符号，值从0开始，无负数。
ZEROFILL	零填充，当数据的显示长度不够的时候可以使用填补0的效果填充至指定长度，字段会自动添加UNSIGNED。
NOT NULL	非空约束，表示该字段的值不能为空。
DEFAULT	表示如果插入数据时没有给该字段赋值，那么就使用默认值。
PRIMARY KEY	主键约束，表示唯一标识，不能为空，且一个表只能有一个主键。
AUTO_INCREMENT	自增长，只能用于数值列，默认起始值从1开始，每次增长1。
UNITQUE KEY	唯一值，表示该字段下的值不能重复，可以为空，可以有多个。
COMMENT	描述。

注：
如果一列同时有UNSIGNED、ZEROFILL、NOT NULL这几个约束，UNSIGNED、ZEROFILL必须在NOT NULL前面，否则会报错。
例如：
        create table user_info_tab(
        user_id int not null auto_increment,
        user_name char (10),
        password varchar(10),
        user_nick varchar(10),
        card_num bigint,
        primary key(user_id)
        );

③查看表
●显示当前数据库中所有表的名字
格式：show tables；
●显示某张表每一列的属性（列名、数据类型、约束）
格式：desc [数据表名称]；
例如：desc user_info_tab；

④删除表
格式：drop table [数据表名称]；
例如：drop table hesheng；

⑤修改表
●向数据表中添加一列
格式：ALTER TABLE [数据表名称] ADD [列名] [列的数据格式] [约束]；
例如：ALTER TABLE user_info_tab ADD phone VARCHAR(11);
PS：默认自动添加到数据表字段的末尾：
        如果要加在第一列在最后加个FIRST；
        如果要加在某一列的后面，在最后面加个AFTER某一列列名。
●删除数据表的某一列
格式：ALTER TABLE [数据表名称] DROP [列名]；
●修改列的类型和名称
格式：ALTER TABLE [表名] MODIFY [列名] [数据表格式]；（列名不变，其他要变）
格式：ALTER TABLE [表名] [旧列名] [数据格式]；（列名也要改变）

5、数据表的增删改查

增（insert）
--插入数据
INSERT INTO user_info（ user_id,user_name,password,user_nick,card_num）
VALUES
          （1,'zhangsan','abc123','zhangsanfeng',1234567894651329785）,
          （2,'lisi','122bbb','limochou',1234567894651324567）,
          （3,'wangwu','123aa','wangbaiwan',214567894651324567）,
          （4,'liuqi','12aaa','liuchuanfeng',214563356651324567）,
          （5,'zhangliu','12aaa','zhangwuji',214563356658966567）;

删（delete）
语法：
1、delete form 表名 where 条件。
2、delete语句不能删除某一列的值。（可以使用update 表名 set username = '''' where userid = 1）
3、使用delete语句仅删除符合where条件的行数据，不删除表中其他行和表本身。
4、truncate user_info_table（直接把数据清空掉）
注：drop和delete的区别
1、drop是删除数据库、数据表、数据表中的某一列。
2、delete是删除某一行数据。

改（update）
语法：
1、update 表名 set 列名=新值where列名=某值。
2、update语法可以新增、更新原有表行中的各列。
3、set子句指示要修改哪些列和要给与哪些值。
4、where子句指定应更新哪些行，如果没有where子句，则更新所有的行。
例如：update user_info set username = "poopoo" where userid = 1；

查（select）

6、数据表的排序、聚合命令、分组

1、排序（order by）
●使用order by子句，对查询结果进行排序。
●order by指定排序的列asc（升序）desc（降序）。
●order by子句一般位于select语句的结尾。
例如：select product_name,weight from products_info order by weight desc;

2、聚合命令
①distinct：对某一列数据去重。
语句：select distinct 列名 from 表名；（显示此列不重复的数据）
②count：统计总行数。
●count(*)：包括所有列，返回表中的总行数，在统计结果的时候，不会忽略值为null的行数。
●count(1)：包括所有列，1表示一个固定值，没有实际含义，在统计结果的时候，不会忽略列值为null的行数，和count(*)的区别是执行效率不同。
●count(列名)：只包括列名指定列，返回指定列的行数，在统计结果的时候，不统计列值为null，即列值为null的行数不统计在内。
●count(distinct列名)：返回指定列的不重复的行数，在统计结果的时候，会忽略列值为null的行数（不包括空字符和0），即列值为null的行数不统计在内。
●count(*)、count(1)、count(列名)执行效率比较：
⑴如果列为主键，count(列名)优于count(1)。
⑵如果列不为主键，count(1)优于count(列名)。
⑶如果表中存在主键，count(主键列名)效率最优。
⑷如果表中只有一列，则count(*)效率最优。
⑸如果表中有多列，且不存在主键，则count(1)效率优于count(*)。
③MAX：最大值
④MIN：最小值
⑤AVG：平均值
⑥SUM：求和
select max(列名) from 表名；
select min(列名) from 表名；
select avg(列名) from 表名；
select sum(列名) from 表名；
注：也可跟where子句
⑦limit
●select * from 表名 limit m,n；
●其中m是指从哪行开始，m从0取值，0表示第一行。
●n是指从m+1条开始，取n条。
●select * from 表名 limit 0,2（从第一行开始，显示两行结果）。
●如果只给定一个参数，他表示返回最大的行数目；select * from table limit 5；查询前5行。
●limit n等价于limit0,n

3、分组（group by）
●使用group by子句对列进行分组。
●然后还可以使用having子句过滤，having通常跟在group by后，它作用于组。
●不加having过滤：
select 列名，聚合函数from 表名 where 子句 group by 列名。
●加上having过滤：
select 列名，聚合函数 from 表名 where 子句 group by 列名 having 聚合函数 过滤条件。
●注意：使用group by后只能展示分组的列名+家具和函数结果，因为其余列已经基于分组这一列合并。
例如：select sum(price)，count(user_id) from order_info_table group by product_id having count（user_id）>  2；

7、数据表的连接查询、子查询

1、两张表连接查询
INNER JOIN（内连接）：获取两个表中字段匹配关系的行的所有信息。
语法：SELECT * FROM [表名] a INNER JOIN [表名] b ON a.[列名] = b.[列名]；
例如：
select * from user_info_table a inner join order_info_table b on a.user_id = b.user_id；
select * from user_info_table a inner join order_info_table b on a.user_id = b.user_id where b.user_id is null；

LEFT JOIN（左连接）：获取左表所有行的信息，即使右表没有对应匹配的行的信息，右表没有匹配的部分用null代替。
语法：select * from [表名] a left join [表名] b on a.[列名] = b.[列名]；
例如：select * from products_info a left join suppliers_info b on a.supplier_id = b.supplier_id;

RIGHT JOIN（右连接）：与左连接相反，用于获取右表所有记录，即使左表没有对应匹配的行的所有信息，左表没有匹配的部分用null代替。
语法：select * from [表名] a right join [表名] b on a.[列名] = b.[列名]；
例如：select * from products_info a right join suppliers_info b on a.supplier_id = b.supplier_id;

2、子查询（嵌套查询）
●嵌套在其他查询中的查询。
●语句：select 列名1 from 表1 where 列名2 in（select 列名2 from 表2 where 列名3 = 某某某）。
●注意：一般在子查询中，程序先运行嵌套在最内层的语句，在运行外层。因此在写子查询语句时，可以先测试一下内层的子查询语句是否输出了想要的内容，在一层一层往外测试，增加子查询的正确率。

3、补充说明
数据库名和表名在windows中是大小写不敏感的，而在大多数类型的UNIX系统中大小写是敏感的。