MySQL基本SQL语句

客户端工具的使用

客户端工具mysql使用

mysql: mysql命令行工具，一般用来连接访问mysql数据库

选项	说明
-u, --user=name	指定登录用户名
-p, --password	指定登录密码(注意是小写p),一定要放到最后面
-h, --host=name	指定数据库的主机地址
-P, --port=xxx	指定数据库的端口号(大写P)
-S, --socket=name	指定socket文件
-e, --execute=name	使用非交互式操作(在shell终端执行sql语句)

案例：使用mysql客户端工具连接服务器端（用户名：root、密码：123）

# mysql -uroot -p123

注：以上连接方式虽然可以连接进入到MySQL，但是官方不建议我们直接把密码写入在终端，建议-p然后直接回车，然后在终端中输入密码。

案例：连接10.1.1.100服务器上的MySQL数据库（用户名：itheima，密码：123）

# mysql -h 10.1.1.100 -P 3306 -uitheima -p
Enter password:123

案例：根据不同的套接字连接同步的数据库

# mysql -S /tmp/mysql.sock -uroot -p
Enter password:123

案例：在不进入MySQL内部的情况下，执行SQL语句，获取数据信息

# mysql -e "show databases;" -uroot -p
Enter password:123

扩展了解：

默认库	描述
information_schema	1、对象信息数据库，提供对数据库元数据的访问，有关MySQL服务器的信息，例如数据库或表的名称，列的数据类型或访问权限等； 2、在INFORMATION_SCHEMA中，有数个只读表，它们实际上是视图，而不是基本表，因此你将无法看到与之相关的任何文件； 3、视图，是一个虚表，即视图所对应的数据不进行实际存储，数据库中只存储视图的定义，在对视图的数据进行操作时，系统根据视图的定义去操作与视图相关联的基本表
mysql	1、mysql数据库是系统数据库。它包含存储MySQL服务器运行时所需的信息的表。比如权限表、对象信息表、日志系统表、时区系统表、优化器系统表、杂项系统表等。 2、不可以删除,也不要轻易修改这个数据库里面的表息。
performance_schema	MySQL5.5开始新增一个数据库，主要用于收集数据库服务器性能；并且库里表的存储引擎均PERFORMANCE_SCHEMA，而用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表
sys	1、mysql5.7增加了sys 系统数据库，通过这个库可以快速的了解系统的元数据信息； 2、sys库方便DBA发现数据库的很多信息，解决性能瓶颈； 3、这个库是通过视图的形式把information_schema 和performance_schema结合起来，查询出更加令人容易理解的数据

客户端工具mysqladmin使用

mysqladmin：客户端管理mysql数据库工具

㈠常用选项

选项	描述
-h, --host=name	指定连接数据库主机
-p, --password	指定数据库密码
-P, --port=#	指定数据库端口
-S, --socket=name	指定数据库socket文件
-u, --user=name	指定连接数据库用户

㈡常用命令

命令	描述
password [new-password]	更改密码
reload	刷新授权表
shutdown	停止mysql服务
status	简短查看数据库状态信息
start-slave	启动slave
stop-slave	停止slave
variables	打印可用变量
version	查看当前mysql数据库的版本信息

案例：更改root账号的密码为root

# mysqladmin password '新密码' -p
Enter password:'旧密码'

# mysqladmin password 'root' -p
Enter password:123

案例：更改密码后，建议刷新授权表（mysql> flush privileges;）

# mysqladmin reload -p
Enter password:123

案例：停止mysql

# mysqladmin shutdown -p
Enter password:123

service mysql_3306 stop

案例：查看mysql状态

# mysqladmin status -p
Enter password:123

案例：打印可用变量（mysql本身预置了很多变量信息）

# mysqladmin variables -p
Enter password:123

案例：查询mysql版本

# mysqladmin version -p
Enter password:123

MySQL中的SQL语句

什么是SQL？

SQL 是 Structure Query Language(结构化查询语言)的缩写,它是使用关系模型的数据库应
用语言,由 IBM 在 20 世纪 70 年代开发出来,作为 IBM 关系数据库原型 System R 的原型关
系语言,实现了关系数据库中的信息检索。

20 世纪 80 年代初,美国国家标准局(ANSI)开始着手制定 SQL 标准,最早的 ANSI 标准于
1986 年完成,就被叫作 SQL-86。标准的出台使 SQL 作为标准关系数据库语言的地位得到了
加强。SQL 标准目前已几经修改更趋完善。

正是由于 SQL 语言的标准化,所以大多数关系型数据库系统都支持 SQL 语言,它已经发展
成为多种平台进行交互操作的底层会话语言。

SQL语句的分类

DDL(Data Definition Languages)语句:

数据定义语言,这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 create、drop、alter、rename、truncate。
DML(Data Manipulation Language)语句:

数据操纵语句,用于添加、删除、更新和查询数据库记录,并检查数据完整性,常用的语句关键字主要包括 insert、delete、update等。
DCL(Data Control Language)语句:

数据控制语句,用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括 grant、revoke 等。
DQL(Data Query Language)语句:

数据查询语句，用于从一个或多个表中检索信息。主要的语句关键字包括 select

MySQL中如何求帮助

亘古不变的官档（软件作用）

MySQL5.6官方文档

MySQL5.7官方文档

man文档（工具作用）

man文档可以对mysql的一些基本工具及后台命令求帮助，比如：

[root@db01 ~]# man mysql
[root@db01 ~]# man mysql_install_db
[root@db01 ~]# man mysqldump
[root@db01 ~]# man mysqld

MySQL的命令行求帮助（主要针对SQL语句求帮助）

mysql> help;
mysql> ?
mysql> help create table;

根据内容进行查找帮助
mysql> ? contents
   Account Management
   Administration
   Data Definition
   Data Manipulation
   Data Types
   Functions
   Functions and Modifiers for Use with GROUP BY
   Geographic Features
   Language Structure
   Storage Engines
   Stored Routines
   Table Maintenance
   Transactions
   Triggers
   
寻求账户管理的帮助（一级一级的向内部查）
mysql> ? Account Management
mysql> ? CREATE USER

注：在mysql内部，没有clear命令，也就是无法使用clear实现清屏，如果想实现清屏操作，可以使用快捷键Ctrl + Shift + L

SQL语句的基本操作

☆ MySQL的内部结构

注：我们平常说的MySQL，其实主要指的是MySQL数据库管理软件。

一个MySQL DBMS可以同时存放多个数据库，理论上一个项目就对应一个数据库。如博客项目blog数据库、商城项目shop数据库、微信项目wechat数据库。

一个数据库中还可以同时包含多个数据表，而数据表才是真正用于存放数据的位置。（类似我们Office软件中的Excel表格），理论上一个功能就对应一个数据表。如博客系统中的用户管理功能，就需要一个user数据表、博客中的文章就需要一个article数据表、博客中的评论就需要一个message数据表。

一个数据表又可以拆分为多个字段，每个字段就是一个属性。

一个数据表除了字段以外，还有很多行，每一行都是一条完整的数据（记录）。

☆ 数据库的基本操作

① 创建数据库

普及英语小课堂：

创建 => create

数据库 => database

基本语法：

mysql> create database 数据库名称;

特别注意：在MySQL中，当一条SQL语句编写完毕后，一定要使用分号;进行结尾，否则系统认为这条语句还没有结束。

案例：创建数据库的相关案例

创建db1库
create database db1;

创建db1库并指定默认字符集
create database db1 default charset gbk;

如果存在不报错(if not exists)
create database if not exists db1 default character set utf8;
说明：不能创建相同名字的数据库！

扩展：编码格式，常见的gbk（中国的编码格式）与utf8（国际通用编码格式）

latin1 256个字符（abcd、1234、传统字符）

国内汉字无法通过256个字符进行描述，所以国内开发了自己的编码格式gb2312，升级gbk

中国台湾业开发了一套自己的编码格式big5

很多项目并不仅仅只在本地使用，也可能支持多国语言，标准化组织开发了一套通用编码utf8，后来5.6版本以后又进行了升级utf8mb4

编写SQL语句是一个比较细致工作，不建议大家直接在终端中输入SQL语句，可以先把你要写的SQL语句写入一个记事本中，然后拷贝执行。

② 查询已创建数据库

英语小课堂：

显示 => show

数据库 => database

基本语法：

显示所有数据库

mysql> show databases;

显示某个数据库的数据结构

mysql> show create database db_itheima;

③ 修改数据库信息

在MySQL5以后的版本中，MySQL不支持更改数据库的名称。我们所谓的修改数据库主要修改的是数据库的编码格式。

英语小课堂：

修改 => alter

数据库 => database

mysql> alter database 数据库名称 default charset=新编码格式;

案例：把db_itheima数据库的编码格式更改为gbk

mysql> alter database db_itheima default charset=gbk;

④ 删除数据库

英语小课堂：

删除 => drop

数据库 => database

基本语法：

mysql> drop database 数据库名称;

案例：删除db_itheima数据库

mysql> drop database db_itheima;

数据表的基本操作

☆ 数据表的创建

英语小课堂：

创建 => create

数据表 => table

基本语法：

mysql> create table 数据表名称(
	字段1 字段类型 [字段约束],
	字段2 字段类型 [字段约束],
	...
);

案例：创建一个admin管理员表，拥有3个字段（编号、用户名称、用户密码）

mysql> create database db_itheima;
mysql> use db_itheima;

use在MySQL中的含义代表选择，use 数据库名称相当于选择指定的数据库。而且use比较特殊，其选择结束后，其尾部可以不加分号；但是强烈建议所有的SQL语句都要加分号，养成一个好习惯。

mysql> create table tb_admin(
	id tinyint,
    username varchar(20),
    password char(32)
) engine=innodb default charset=utf8;

tinyint ：微整型，范围-128 ~ 127，无符号型，则表示0 ~ 255

表示字符串类型可以使用char与varchar，char代表固定长度的字段，varchar代表变化长度的字段。

案例：创建一个article文章表，拥有4个字段（编号、标题、作者、内容）

mysql> use db_itheima;
mysql> create table tb_article(
	id int,
	title varchar(50),
	author varchar(20),
	content text
) engine=innodb default charset=utf8;

text ：文本类型，一般情况下，用varchar存储不了的字符串信息，都建议使用text文本进行处理。

varchar存储的最大长度，理论值65535个字符。但是实际上，有几个字符是用于存放内容的长度的，所以真正可以使用的不足65535个字符，另外varchar类型存储的字符长度还和编码格式有关。1个GBK格式的占用2个字节长度，1个UTF8格式的字符占用3个字节长度。GBK = 65532~65533/2，UTF8 = 65532~65533/3

☆ 查询已创建数据表

英语小课堂：

显示 => show

数据表 => table

显示所有数据表（当前数据库）

mysql> use 数据库名称;
mysql> show tables;

显示数据表的创建过程（编码格式、字段等信息）

mysql> show create table 数据表名称;
或
mysql> desc 数据表名称;

☆ 修改数据表信息

① 数据表字段添加

英语小课堂：

修改 => alter

数据表 => table

基本语法：

mysql> alter table 数据表名称 add 新字段名称 字段类型 first|after 其他字段名称;
选项说明：
first：把新添加字段放在第一位
after 字段名称：把新添加字段放在指定字段的后面

案例：在tb_article文章表中添加一个addtime字段，类型为date(年-月-日)

mysql> alter table tb_article add addtime date after content;
mysql> desc tb_article;

② 修改字段名称或字段类型

修改字段名称与字段类型（也可以只修改名称）

mysql> alter table tb_admin change username user varchar(40);
mysql> desc tb_admin;

仅修改字段的类型

mysql> alter table tb_admin modify user varchar(20);
mysql> desc tb_admin;

③ 删除某个字段

mysql> alter table tb_article drop 字段名称;
mysql> desc tb_article;

④ 修改数据表引擎（MyISAM或InnoDB）

mysql> alter table tb_article engine=myisam;
mysql> show create table tb_article;

⑤ 修改数据表的编码格式

mysql> alter table tb_admin default charset=gbk;
mysql> show create table tb_admin;

⑥ 修改数据表名称

移动表到另一个库里并重命名
rename table db01.t1 to db02.t11;
或者
alter table db01.t1 rename db02.t11;

只重命名表名不移动
rename table tt1 to tt2;
或者
alter table tt1 rename  tt2;

☆ 删除数据表

英语小课堂：

删除 => drop

数据表 => table

mysql> drop table 数据表名称;

数据的增删改查（重点）

英语小课堂：

增加：insert

删除：delete

修改：update

查询：select

☆ 数据的增加操作

基本语法：

mysql> insert into 数据表名称([字段1,字段2,字段3...]) values (字段1的值,字段2的值,字段3的值...);

特别注意：在SQL语句中，除了数字，其他类型的值，都需要使用引号引起来，否则插入时会报错。

第一步：准备一个数据表

mysql> use db_itheima;
mysql> create table tb_user(
	id int,
	username varchar(20),
	age tinyint unsigned,
	gender enum('男','女','保密'),
	address varchar(255)
) engine=innodb default charset=utf8;

unsigned代表无符号型，只有0到正数。tinyint unsigned无符号型，范围0 ~ 255

enum枚举类型，多选一。只能从给定的值中选择一个

第二步：使用insert语句插入数据

mysql> insert into tb_user values (1,'李向阳',24,'男','广东省广州市');
mysql> insert into tb_user(id,username,age) values (2,'马鹏',23);

☆ 数据的查询操作

基本语法：

mysql> select * from 数据表名称 [where 查询条件];
mysql> select id,username,age from 数据表名称 [where 查询条件];

案例：查询tb_user表中的所有记录

mysql> select * from tb_user;

案例：查询tb_user表中的id，username以及age字段中对应的数据信息

mysql> select id,username,age from tb_user;

案例：只查询id=2的小伙伴信息

mysql> select * from tb_user where id=2;

案例：查询年龄大于23岁的小伙伴信息

mysql> select * from tb_user where age>23;

☆ 数据的修改操作

基本语法：

mysql> update 数据表名称 set 字段1=更新后的值,字段2=更新后的值,... where 更新条件;

特别说明：如果在更新数据时，不指定更新条件，则其会把这个数据表的所有记录全部更新一遍。

案例：修改username='马鹏'这条记录，将其性别更新为男，家庭住址更新为广东省深圳市

mysql> update tb_user set gender='男',address='广东省深圳市' where username='马鹏';

案例：今年是2020年，假设到了2021年，现在存储的学员年龄都差1岁，整体进行一次更新

mysql> update tb_user set age=age+1;

☆ 数据的删除操作

基本语法：

mysql> delete from 数据表名称 [where 删除条件];

案例：删除tb_user表中，id=1的用户信息

mysql> delete from tb_user where id=1;

delete from与truncate清空数据表操作

mysql> delete from 数据表;
或
mysql> truncate 数据表;

delete from与truncate区别在哪里？

delete：删除数据记录
- 数据操作语言（DML）
- 在事务控制里，DML语句要么commit，要么rollback
- 删除大量记录速度慢，只删除数据不回收高水位线
- 可以带条件删除
truncate：删除所有数据记录
- 数据定义语言（DDL）
- 不在事务控制里，DDL语句执行前会提交前面所有未提交的事务
- 清里大量数据速度快，回收高水位线（high water mark）
- 不能带条件删除

自动增长（水位线）与主键约束

☆ 自动增长（对某个字段进行自动编号）

mysql> create table tb_user(
	id int not null auto_increment,
	username varchar(20),
	age tinyint unsigned,
	gender enum('男','女','保密'),
	address varchar(255)
) engine=innodb default charset=utf8;

not null代表非空约束，这个字段只要是插入数据就必须要有值。

☆ 主键约束（非空、唯一）

create table tb_user(
	id int not null auto_increment,
	username varchar(20),
	age tinyint unsigned,
	gender enum('男','女','保密'),
	address varchar(255),
	primary key(id)
) engine=innodb default charset=utf8;

插入数据库时，id位置直接写NULL即可

mysql> insert into tb_user values (null,'李向阳',24,'男','广东省广州市');
mysql> insert into tb_user values (null,'马鹏',23,'男','广东省深圳市');
mysql> insert into tb_user values (null,'上官婉儿',18,'女','湖南省长沙市');

扩展常见的数据类型

回顾数据表的创建语法

基本语法：

mysql> create table 数据表名称(
	字段名称1 字段类型 字段约束,
	字段名称2 字段类型 字段约束,
	...
	primary key(主键字段 => 不能为空、必须唯一)
) engine=innodb default charset=utf8;

经验之谈：一般情况下，我们在设计数据表时，必须拥有一个id主键字段，非空、自动编号、主键约束。

在创建数据表时，必须明确指定字段的名称以及字段的类型，那到底MySQL中的SQL语句支持哪些字段类型呢？

答：参考下图

数值类型

☆ 整数类型（整数、精准）

应用场景：人的年龄、考试成绩等等相关的信息都可以使用整数类型进行存储，那整数类型有这么多形式，我应该选择哪一个呢？

答：整数类型的选择主要取决于数值的范围。

比如存储中国人的人口信息，可以用INT类型也可以用BIGINT类型，但是在数据库设计原则中，不是越大越好，而是正好满足条件即可。

案例：设计一个数据表如tb_student学生表（id、username、age、mobile）

mysql> use 数据库名称;
mysql> create table tb_student(
	id mediumint unsigned not null auto_increment,
	username varchar(20),
	age tinyint unsigned,
	mobile char(11),
	primary key(id)
) engine=innodb default charset=utf8;

☆ 小数类型（浮点类型、定点类型）

① 浮点类型（近似值）

FLOAT和DOUBLE

FLOAT和DOUBLE类型代表近似数字数据值。MySQL对于单精度值使用四个字节，对于双精度值使用八个字节。 FLOAT单精度浮点数精确到约7位小数，DOUBLE双精度浮点数精确到大约15位小数。FLOAT类型会随着数值的增大精度会减小。

举例说明

FLOAT(M,D)，其中，M表示存储的有效位数，D代表小数点后面的位数；即整数位数+小数部分不能超过M值。
colum1 FLOAT(7,4)
上面例子中，如果你插入为999.00009到cloum1列，那么mysql在存储时会四舍五入变为999.0001插入。

DOUBLE(7,4) ：DOUBLE类型的使用与FLOAT类型完全一致，唯一的区别就是占用字节不同且浮点数的精度有所不同。

② 定点类型（精确值）

DECIMAL和NUMERIC

DECIMAL和NUMERIC 类型的存储精确的数值数据。使用这些类型时，重要的是要保留精确的精度，例如使用货币数据。在MySQL中, NUMERIC被作为DECIMAL来应用，所以下面的举例DECIMAL同样适用于 NUMERIC 。

举例说明

salary DECIMAL(5,2)
在上面的例子中，薪水字段的类型为decimal，表示精确的数字。其中，5代表精度，2代表刻度。
精度表示数值存储的有效位数，刻度表示小数点后面可存储的位数。
DECIMAL(5,2)能够存储五位数和两位小数的任何值，因此可以存储在salary 列中的值的范围-999.99是 999.99

特殊注意：
DECIMAL(M)和DECIMAL(M,0)是相等的，存储长度取决于M的值，默认情况下M值为10.刻度为0表示没有小数。

案例：定义一个职员表tb_staff，定义字段（id、username、salary、addtime）

mysql> use db_itheima;
mysql> create table tb_staff(
	id smallint unsigned not null auto_increment,
	username varchar(20),
	salary decimal(11,2),
	addtime date,
	primary key(id)
) engine=innodb default charset=utf8;

字符串类型

☆ CHAR类型

CHAR类型的字符串为定长.长度范围是0到255之间的任何值.占用定长的存储空间,不足的部分用空格填充；读取时删掉后面的空格。

password char(32)

mobile char(11)

存储空间

CHAR(M)类型的存储空间和字符集有关系，一个中文在utf8字符集中占用3个bytes、gbk占用2个bytes、数字和字符统一用一个字符表示。

存储机制

在不够M长度时，MySQL在存储数据时，需要填充特殊的空格.

举例说明

字段名称 CHAR(M),M表示字符数

应用场景：加密后的密码 => char(32)，手机号码mobile(11)，身份证号码等等

案例：创建一个tb_admin数据表，定义字段（id、username、password）

mysql> use db_itheima;
mysql> create table tb_admin(
	id tinyint unsigned not null auto_increment,
	username varchar(10),
	password char(32),
	primary key(id)
) engine=innodb default charset=utf8;

☆ VARCHAR类型

VARCHAR是变长存储,仅使用必要的存储空间.

name varchar(10)

存储空间

VARCHAR(M)类型的存储空间和字符集有关系，一个中文在utf8字符集中占用3个bytes、gbk统一占用2个bytes、数字和字符一个字符表示。

存储机制

VARCHAR(M)字段存储实际是从第二个字节开始存储,然后用1到2个字节表示实际长度,剩下的才是可以存储数据的范围,因此最大可用存储范围是65535-3=65532字节;

第一个字节标识是否为空.（长度小于255字节,使用一个字节来表示长度;大于255字节使用两个字节来表示长度)。

应用场景：主要适合存储长度不固定的字符串信息，如用户名称、产品标题、新闻标题、新闻描述（不超过255个字符）

案例：创建一个tb_news数据表，定义字段（id、title、description、addtime）

mysql> use db_itheima;
mysql> create table tb_news(
	id int not null auto_increment,
	title varchar(80),
	description varchar(255),
	addtime date,
	primary key(id)
) engine=innodb default charset=utf8;

☆ TEXT类型

TEXT代表文本类型的数据，当我们使用VARCHAR类型存储数据时（早期最大只能存储255个字符，MySQL5版本中，其gbk可以存储3万多个字符，utf8格式可以存储2万多个字符），如超过了VARCHAR的最大存储范围，则可以考虑使用TEXT文本类型。

经验之谈：255个字符以内（包括），定长就使用CHAR类型，变长就使用VARCHAR类型，如果超过255个字符，则使用TEXT文本类型。

应用场景：文本内容、产品的详细介绍等等

案例：创建一个tb_goods产品表，定义字段（id、name、price、content）

mysql> use db_itheima;
mysql> create table tb_goods(
  id int not null auto_increment,
  name varchar(80),
  price decimal(11,2),
  content text,
  primary key(id)
) engine=innodb default charset=utf8;

☆ 其他类型

BLOB：保存二进制的大型数据（字节串），没有字符集，eg：图片、音频视频等。

实际运维工作中，很少将文件直接保存在数据库端，一般文件的存储都是基于路径进行操作的。

ENUM枚举类型：多选一，从给定的多个选项中选择一个，如gender enum('男','女','保密')

SET集合类型：多选多，从给定的多个选项中选个多个，如hobby set('吃饭','睡觉','打豆豆')

日期时间类型

经验之谈：时间类型的选择比较简单，主要看你需要的时间格式，是年月日、小时分钟秒等等。

㈠ DATE类型（年-月-日）

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

㈡ DATETIME(年月日小时分钟秒)

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displaysDATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

DATETIME范围相对于TIMESTAMP范围更广，'1000-01-01 00:00:00' to '9999-12-31 23:59:59'

㈢ TIMESTAMP(年月日小时分钟秒)

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

TIMESTAMP选项如果不插入时间，则MySQL会自动调用系统时间写入数据

注意：

Invalid DATE, DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00').

无效的日期，日期时间等会被替换成‘0000-00-00’或‘0000-00-00 00:00:00’

㈣ TIME（小时:分钟:秒）

MySQL retrieves and displays TIME values in 'HH:MM:SS' format (or 'HHH:MM:SS' format for large hours values).TIME values may range from '-838:59:59' to '838:59:59'.

说明：小时部分可以是这么大，因为可以使用TIME类型不仅代表一个时间(必须小于24小时),而且可以表示运行时间或两个事件之间的时间间隔(可能大于24小时,甚至负数)。

注意：

TIME这一列如果存储缩写，需要注意mysql的解释方式。无效的时间值会被转换成'00:00:00' 。

'11:12' means '11:12:00', not '00:11:12'.

'12' and 12 are interpreted as '00:00:12'.

㈤ YEAR

YEAR(4) and YEAR(2) differ in display format, but have the same range of values.

For 4-digit format, MySQL displays YEAR values in YYYY format, with a range of 1901 to 2155, or 0000.

For 2-digit format, MySQL displays only the last two (least significant) digits; for example, 70 (1970 or 2070) or 69 (2069).

无效的值将会被转换成'0000'.

案例：编写tb_article文章表，定义字段（id、title、description、addtime）

mysql> use db_itheima;
mysql> create table tb_article1(
	id int not null auto_increment,
	title varchar(80),
	description varchar(255),
	addtime datetime,
	primary key(id)
) engine=innodb default charset=utf8;

mysql> create table tb_article2(
	id int not null auto_increment,
	title varchar(80),
	description varchar(255),
	addtime timestamp,
	primary key(id)
) engine=innodb default charset=utf8;

mysql> insert into tb_article1 values (null,'mysql从入门到放弃','mysql很好很强大...',null);

mysql> insert into tb_article1 values (null,'mysql从入门到放弃','mysql很好很强大...',null);

SQL查询语句（重点）

回顾SQL查询语句

基本语法：

mysql> select */字段列表 from 数据表名称 where 查询条件;

SQL查询五子句

基本语法：

mysql> select */字段列表 from 数据表名称 where 子句 group by 子句 having 子句 order by 子句 limit 子句;

① where子句

② group by子句

③ having子句

④ order by子句

⑤ limit子句

特别注意：五子句的顺序是固定的，不能颠倒。

WHERE子句

符号	说明
%	匹配0个或任意多个字符
_(下划线)	匹配单个字符
like	模糊匹配
=	等于,精确匹配
>	大于
<	小于
>=	大于等于
<=	小于等于
!=和<>	不等于
! 和 not	逻辑非
\|\| 和 or	逻辑或
&& 和 and	逻辑与
between...and....	两者之间
in (....)	在...
not in (....)	不在

案例：like模糊查询语句，查询姓"关"的同学信息（name字段对应值应该以"关"开头）

准备测试数据

mysql> use db_itheima;
mysql> create table tb_student(
	id mediumint not null auto_increment,
	name varchar(20),
	age tinyint unsigned default 0,
	gender enum('男','女'),
	address varchar(255),
	primary key(id)
) engine=innodb default charset=utf8;
插入测试数据
mysql> insert into tb_student values (null,'刘备',33,'男','湖北省武汉市');
mysql> insert into tb_student values (null,'貂蝉',18,'女','湖南省长沙市');
mysql> insert into tb_student values (null,'关羽',32,'男','湖北省荆州市');
mysql> insert into tb_student values (null,'大乔',20,'女','河南省漯河市');
mysql> insert into tb_student values (null,'赵云',25,'男','河北省石家庄市');
mysql> insert into tb_student values (null,'小乔',18,'女','湖北省荆州市');

使用like模糊查询，获取姓"关"的同学信息

mysql> select * from tb_student where name like '关%';

like模糊查询，有点类似于管道命令的中的数据检索。有两个关键字：%百分号与_下划线，%百分号代表任意个任意字符，_下划线代表任意的某个字符（只能匹配1个）

案例：like模糊查询语句，查询名字中带"蝉"字的同学信息

mysql> select * from tb_student where name like '%蝉%';

案例：like模糊查询语句，查询云字结尾且名字为两个字的同学信息

mysql> select * from tb_student where name like '_云';

案例：获取学生表中，id编号为3的同学信息

mysql> select * from tb_student where id=3;

案例：获取年龄大于25周岁的同学信息

mysql> select * from tb_student where age>25;

案例：获取学生表中，性别不为男的同学信息（获取女同学的信息）

mysql> select * from tb_student where gender<>'男';

案例：获取班级中年龄大于30岁的男同学信息

mysql> select * from tb_student where age>30 && gender='男';

案例：获取id值为1、3、5的同学信息

mysql> select * from tb_student where id=1 or id=3 or id=5;

案例：获取年龄在18周岁~25周岁之间的同学信息

mysql> select * from tb_student where age>=18 and age<=25;
或
mysql> select * from tb_student where age between 18 and 25;

案例：获取id值为2、4、6的同学信息

mysql> select * from tb_student where id in (2,4,6);

DISTINCT数据去重

案例：获取tb_student学生表学员年龄的分布情况。

mysql> select distinct age from tb_student;

GROUP BY子句(重点和难点)

group by子句的作用：对数据进行分组操作，为什么要进行分组呢？分组的目标就是进行分组统计。

日常生活中的分组太多了，如按男女进行分组，按成绩进行分组，按院校、系部分组，按部门进行分组。

根据给定数据列的查询结果进行分组统计，最终得到一个分组汇总表

注：一般情况下group by需与统计函数一起使用才有意义

☆ 统计函数

常见统计函数	说明
max	求最大值
min	求最小值
sum	求和
avg	求平均值
count	求总行数

案例：求tb_student表中一共有多少个记录

mysql> select count(*) from tb_student;

案例：求年龄的最大值与最小值

mysql> select max(age) from tb_student;
与
mysql> select min(age) from tb_student;

案例：针对id字段求和

mysql> select sum(id) from tb_student;

案例：求学员表中年龄的平均值

mysql> select avg(age) from tb_student;

☆ GROUP BY分组

案例：求tb_student表中，男同学的总数量与女同学的总数量

mysql> select gender,count(*) from tb_student group by gender;

在MySQL5.7以后版本中，分组字段必须出现在select后面的查询字段中

案例：求tb_student表中，男同学年龄的最大值与女同学年龄的最大值

mysql> select gender,max(age) from tb_student group by gender;

☆ GROUP BY分组原理

记住：以后只要涉及到求每个学科、每个部分、每个年级、每个系部薪资最高、成绩最好、薪资的平均值等等，就是基于GROUP BY + 统计函数。

HAVING子句

having与where类似，根据条件对数据进行过滤筛选
where针对表中的列发挥作用，查询数据
having针对查询结果集发挥作用，筛选数据

案例：having在做简单查询时可以替代where子句

mysql> select * from tb_student having age>20;

案例：按学科进行分组，求每个学科拥有多少人

mysql> create table tb_student(
	id mediumint not null auto_increment,
	name varchar(20),
	age tinyint unsigned default 0,
	gender enum('男','女'),
	subject enum('ui','java','yunwei','python'),
	primary key(id)
) engine=innodb default charset=utf8;

mysql> insert into tb_student values (null,'悟空',255,'男','ui');
mysql> insert into tb_student values (null,'八戒',250,'男','python');
mysql> insert into tb_student values (null,'唐僧',30,'男','yunwei');
mysql> insert into tb_student values (null,'沙僧',150,'男','java');
mysql> insert into tb_student values (null,'小白龙',100,'男','yunwei');
mysql> insert into tb_student values (null,'白骨精',28,'女','ui');
mysql> insert into tb_student values (null,'兔子精',22,'女','yunwei');
mysql> insert into tb_student values (null,'狮子精',33,'男','yunwei');
或
mysql> insert into tb_student values (null,'悟空',255,'男','ui'),(null,'八戒',250,'男','python'),(null,'唐僧',30,'男','yunwei'),(null,'沙僧',150,'男','java'),(null,'小白龙',100,'男','yunwei'),(null,'白骨精',28,'女','ui'),(null,'兔子精',22,'女','yunwei'),(null,'狮子精',33,'男','yunwei');

mysql> select subject,count(*) from tb_student group by subject;

案例：求每个学科中，学科人数大于3人的学科信息

mysql> select subject,count(*) from tb_student group by subject having count(*)>3;

ORDER BY子句

主要作用的就是对数据进行排序（升序、降序）

升序：从小到大，1,2,3,4,5

mysql> select * from 数据表名称 ... order by 字段名称 asc;

降序：从大到小，5,4,3,2,1

mysql> select * from 数据表名称 ... order by 字段名称 desc;

案例：按年龄进行排序（由大到小）

mysql> select * from tb_student order by age desc;

LIMIT子句

基本语法：

mysql> select * from 数据表名称 ... limit number; 查询满足条件的number条数据
或
mysql> select * from 数据表名称 ... limit offset,number; 从偏移量为offset开始查询，查询number条记录
offset的值从0开始

offset偏移量：

案例：获取学生表中，年龄最大的学员信息

mysql> select * from tb_student order by age desc limit 1;

案例：从偏移量为1的元素开始查询，查询2条记录

mysql> select * from tb_student limit 1,2;

LIMIT子句在开发项目中，主要应用于数据分页。

案例：实现数据分页（参考上图）

第一页：

mysql> select * from tb_student limit 0,2;

第二页：

mysql> select * from tb_student limit 2,2;

SQL多表查询（了解）

什么是多表查询

我们刚才学习的SQL五子句都主要是针对单表情况，我们在实际工作中，也可能会接触到一些复杂的多表查询。

UNION联合查询

UNION联合查询的作用：把多个表中的数据联合在一起进行显示。应用场景：分库分表

第一步：创建两个结构相同的学生表tb_student1与tb_student2

mysql> create table tb_student1(
	id mediumint not null auto_increment,
	name varchar(20),
	age tinyint unsigned default 0,
	gender enum('男','女'),
	subject enum('ui','java','yunwei','python'),
	primary key(id)
) engine=innodb default charset=utf8;
mysql> insert into tb_student1 values (1,'悟空',255,'男','ui');

mysql> create table tb_student2(
	id mediumint not null auto_increment,
	name varchar(20),
	age tinyint unsigned default 0,
	gender enum('男','女'),
	subject enum('ui','java','yunwei','python'),
	primary key(id)
) engine=innodb default charset=utf8;
mysql> insert into tb_student2 values (2,'唐僧',30,'男','yunwei');

第二步：使用UNION进行联合查询

mysql> select * from tb_student1 union select * from tb_student2;

交叉查询（了解）

基本语法：

mysql> select */字段列表 from 数据表1,数据表2;
或
mysql> select */字段列表 from 数据表1 cross join 数据表2;

运行结果：

字段总数 = 数据表1的字段 + 数据表2的字段

总记录数 = 数据表1的记录数 * 数据表2的记录数 => (笛卡尔积)

准备两张数据表：产品分类表与产品的信息表

tb_category

mysql> create table tb_category(
	id smallint not null auto_increment,
	name varchar(20),
	pid smallint default 0,
	primary key(id)
) engine=innodb default charset=utf8;

insert into tb_category values (null,'手机',0);		=>  id=1
insert into tb_cateogry values (null,'电脑',0);		=>  id=2
insert into tb_category values (null,'游戏手机',1);	   =>  游戏手机属于手机分类

pid代表所属的父级类别，如果自己就是顶级分类，则为0

tb_goods

mysql> create table tb_goods(
	id int not null auto_increment,
	title varchar(80),
	price decimal(11,2),
	cid smallint default 0,
	primary key(id)
) engine=innodb default charset=utf8;

insert into tb_goods values (null,'IPhone 11',5699.00,1);
insert into tb_goods values (null,'MI 10',4699.00,1);
insert into tb_goods values (null,'ThinkPad X1',9999.00,2);

insert into tb_goods values (null,'Nike air',999.00,10);

cid代表产品的所属分类编号，与tb_category表要一一对应

案例：把分类表与产品表进行交叉，求结果

mysql> select * from tb_category cross join tb_goods;

交叉连接本身是没有意义的，其只是强制把两个表甚至多个表进行连接在一起。但是交叉查询中也有正确的结果，所以我们所谓的多表连接只需要在交叉连接的基础上增加一个连接条件，则就是我们想要的结果了。所以交叉查询是多表查询的基础。

内连接查询（重点）

基本语法：

mysql> select 数据表1.字段列表,数据表2.字段列表 from 数据表1 inner join 数据表2 on 连接条件;

案例：获取产品表中每个产品的分类信息

mysql> select tb_goods.*,tb_category.name from tb_goods inner join tb_category on tb_goods.cid=tb_category.id;

内连接查询：把两个表甚至多个表进行连接，然后拿表1中的每一条记录与表2中的每一条记录进行匹配，如果有与之对应的结果，则显示。反之，则忽略这条记录。

外连接查询（重点）

内连接查询要求：表1和表2中的每一条记录必须要一一对应，如果无法匹配，则这条记录会被自动被忽略掉，那如果我需要保留表1中的所有记录或表2中的所有记录怎么办呢？

答：使用外连接查询

基本语法：

左外连接查询：把左表中的每一条数据都保留，右表匹配到结果就显示，匹配不到就NULL

select 数据表1.字段列表,数据表2.字段列表 from 数据表1 left join 数据表2 on 连接条件;

右外连接查询：把右表中的每一条数据都保留，左表匹配到结果就显示，匹配不到就NULL

select 数据表1.字段列表,数据表2.字段列表 from 数据表1 right join 数据表2 on 连接条件;

案例：获取产品表中每个产品的分类信息（无论匹配到与否）

mysql> select tb_goods.*,tb_category.name from tb_goods left join tb_category on tb_goods.cid=tb_category.id;

别名机制：简化内外连接

案例：获取产品表中每个产品的分类信息（无论匹配到与否）

原始语句：

mysql> select tb_goods.*,tb_category.name from tb_goods left join tb_category on tb_goods.cid=tb_category.id;

给数据表起个简单的名字，如tb_category叫c，tb_goods叫g

① mysql> select * from tb_goods left join tb_category;
起别名
② mysql> select * from tb_goods g left join tb_category c;
写on条件
③ mysql> select * from tb_goods g left join tb_category c on g.cid=c.id;
筛选字段
④ mysql> select g.*,c.name from tb_goods g left join tb_category c on g.cid=c.id;

posted @ 2021-02-24 17:40 BennyZion 阅读(236) 评论(0) 收藏举报

刷新页面返回顶部

归寻

MySQL基本SQL语句

客户端工具的使用

客户端工具mysql使用

客户端工具mysqladmin使用

㈠ 常用选项

㈡ 常用命令

MySQL中的SQL语句

什么是SQL？

SQL语句的分类

MySQL中如何求帮助

SQL语句的基本操作

☆ MySQL的内部结构

☆ 数据库的基本操作

① 创建数据库

② 查询已创建数据库

③ 修改数据库信息

④ 删除数据库

数据表的基本操作

☆ 数据表的创建

☆ 查询已创建数据表

☆ 修改数据表信息

① 数据表字段添加

② 修改字段名称或字段类型

③ 删除某个字段

④ 修改数据表引擎（MyISAM或InnoDB）

⑤ 修改数据表的编码格式

⑥ 修改数据表名称

☆ 删除数据表

数据的增删改查（重点）

☆ 数据的增加操作

☆ 数据的查询操作

☆ 数据的修改操作

☆ 数据的删除操作

自动增长（水位线）与主键约束

☆ 自动增长（对某个字段进行自动编号）

☆ 主键约束（非空、唯一）

扩展常见的数据类型

回顾数据表的创建语法

数值类型

☆ 整数类型（整数、精准）

☆ 小数类型（浮点类型、定点类型）

① 浮点类型（近似值）

② 定点类型（精确值）

字符串类型

☆ CHAR类型

☆ VARCHAR类型

☆ TEXT类型

☆ 其他类型

日期时间类型

㈠ DATE类型（年-月-日）

㈡ DATETIME(年月日小时分钟秒)

㈢ TIMESTAMP(年月日小时分钟秒)

㈣ TIME（小时:分钟:秒）

㈤ YEAR

SQL查询语句（重点）

回顾SQL查询语句

SQL查询五子句

WHERE子句

DISTINCT数据去重

GROUP BY子句(重点和难点)

☆ 统计函数

☆ GROUP BY分组

☆ GROUP BY分组原理

HAVING子句

ORDER BY子句

LIMIT子句

SQL多表查询（了解）

什么是多表查询

UNION联合查询

交叉查询（了解）

内连接查询（重点）

外连接查询（重点）

别名机制：简化内外连接

公告

㈠常用选项

㈡常用命令