MySQL的详细使用教程

目录

• 1. 连接到MySQL服务器
• 2. 创建和删除数据库
• • 2-1创建数据库
  • 2-2删除数据库
• 3. 数据表操作
• • 3.1 选择数据库
  • 3.2 创建数据表
  • 3.3 查询数据表
  • 3.4 修改数据表
  • 3.5 删除数据表
• 4. 数据内容操作
• • 4.1数据操作
  • • 1. 插入数据
    • 2. 查询数据
    • • （1）like模糊查询（%表示通配符）
      • （2）限制查询的结果集
      • （3）分组查询
      • （4）分组统计
      • （5）结果集过滤
      • （6）结果集排序
    • 3. 更新数据
    • 4. 删除数据
  • 4.2表字段操作
  • • 1.增加表字段
    • 2.修改表字段
    • 3.删除表字段
    • 4.表字段改名
• 5.统计函数
• • 5.1常见的统计函数
• 6.联合查询
• • 6.1内连接
  • 6.2外连接
  • 6.3子嵌套查询
• 7.窗口函数
• • （1）序号函数：
  • （2）分布函数：
  • （3）前后函数：
  • （4）头尾函数：firs_value() / last_value()

1. 连接到MySQL服务器

在使用MySQL之前，需要先连接到MySQL服务器。可以使用命令行工具（如MySQL Shell或终端）或图形化工具（如phpMyAdmin）来连接。以下是使用命令行工具连接到MySQL服务器的示例：

mysql -u root -p

其中，root 是你的MySQL用户名。执行上述命令后，系统会提示你输入密码，输入正确的密码后即可连接到MySQL服务器。
(密码输入为不可见，这是正常的)

选项 说明 示例 -u 指定用户名 -u root -p 提示输入密码（密码可跟在后面但不安全） -p1234 或 -p -h 指定主机名（默认 localhost） -h 192.168.1.100 -P 指定端口（默认 3306） -P 3307 –ssl 启用 SSL 安全连接 –ssl –socket 指定套接字文件 –socket=/tmp/mysql.sock –database 登录后直接使用指定数据库 –database=testdb

2. 创建和删除数据库

2-1创建数据库

在MySQL中，可以使用create database语句来创建数据库。以下是创建一个名为my_database的数据库的示例：

create database my_database;

执行上述语句后，MySQL将创建一个名为my_database的数据库。如果要查看所有数据库，可以使用show database语句

show databases;

2-2删除数据库

使用drop database语句来删除数据库，语法如下：

drop database my_database;

注意：删除数据库是一个危险操作，因为它将永久删除数据库及其包含的所有数据表和数据。在执行此操作之前，请确保你已经备份了重要的数据

3. 数据表操作

3.1 选择数据库

在创建数据表之前，需要先选择要在其中创建数据表的数据库。使用USE语句来选择数据库，语法如下：

use my_database;

3.2 创建数据表

使用create table语句来创建数据表，语法如下：

CREATE TABLE table_name ( column1 datatype constraints, column2 datatype constraints, ...);

其中，table_name是数据表的名称，column1、column2等是数据表的列名，datatype是列的数据类型，constraints是列的约束条件（如主键、非空等）。

例如，创建一个名为users的数据表，包含id、name和email三个列：

CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL UNIQUE)engine = innodb default charset = utf-8;

以下是MySQL中常见的数据库表字段数据类型的表格列举：

数据类型 描述 示例 整数类型 tinyint 1字节，取值范围： -128~127 CREATE TABLE test (id TINYINT); smallint 2字节，取值范围： -32768~32767 num SMALLINT; mediumint 3字节，取值范围： -8388608~8388607 count MEDIUMINT; int 4字节，取值范围： -2147483648~2147483647 age INT; bigint 8字节，取值范围： -9223372036854775808到9223372036854775807 big_num BIGINT; 浮点数类型 float(m,d) 单精度浮点数，占用4个字节，m总个数，小数位。 price float(5,2); double(m,d) 双精度浮点数，占用8个字节，比FLOAT有更高的精度和更大的表示范围。 weight double(10,2); decimal(m,d) 精确数值类型，用于存储精确的小数。可以指定精度和小数位数。 amount DECIMAL(10,2); 字符串类型 char 固定长度的字符串，长度在创建时指定，不足指定长度时会用空格填充。 code CHAR(5); varchar 可变长度的字符串，长度在创建时指定，存储时只占用实际字符串长度的空间。 name VARCHAR(255); text 用于存储较长的文本数据，有TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT等不同的长度限制。 description TEXT; 日期和时间类型 date 用于存储日期，格式为YYYY-MM-DD。 birth_date DATE; time 用于存储时间，格式为HH:MM:SS。 start_time TIME; datetime 用于存储日期和时间，格式为YYYY-MM-DD HH:MM:SS。 create_time DATETIME; timestamp 也用于存储日期和时间，范围比DATETIME小，从1970-01-01 00:00:01到2038-01-19 03:14:07。并且在插入或更新记录时，TIMESTAMP列会自动更新为当前时间。 last_update TIMESTAMP; 二进制类型 binary 固定长度的二进制数据，类似于CHAR。 binary_data BINARY(10); varbinary(M) 可变长度的二进制数据，类似于VARCHAR。 varbinary_data VARBINARY(255); blob 用于存储大量的二进制数据，有TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB等不同的长度限制。 CREATE TABLE test (image_data BLOB);

3.3 查询数据表

要查看数据库中的所有数据表，可以使用show tables语句：

show tables;

这将列出当前选择的数据库中的所有数据表。

要查看数据表的结构，可以使用describe(可以缩写成desc)或show colunmns from语句，如查看名为users的数据表结构：

 describe users;

3.4 修改数据表

语法：alter table 旧数据表名 rename 新数据表名字；
示例：

alter table users rename new_users;

3.5 删除数据表

使用drop table 语句来删除数据表，如删除名为users的数据表:

drop table users;

4. 数据内容操作

4.1数据操作

1. 插入数据

使用insert into语句来向数据表中插入数据，语法如下：

insert into table_name (column1, column2,...) values (value1, value2,...); 

例如，向名为users的数据表中插入一条记录：

 insert into users (name,email) values (\'john\',\'john@qq.com\');

注意值的数量和数据表字段的数量保持一致，除非有默认值可以不用写

如果要插入多条记录，写法如下：

INSERT INTO users (name, email) VALUES (\'Jane Smith\', \'jane@example.com\'), (\'Bob Johnson\', \'bob@example.com\'); 

2. 查询数据

使用select语句来查询数据表中的数据，语法如下:

SELECT column1, column2,... FROM table_name WHERE condition;

其中，column1、column2等是要查询的列名，如果要查询所有列，可以使用*代替；table_name是要查询的数据表名称；WHERE子句用于指定查询条件，可根据需要省略。

例如，查询名为users的数据表中的所有记录：

SELECT * FROM users;

查询名为users的数据表中name为Jane Smith的记录：

SELECT * FROM users WHERE name = \'Jane Smith\'; 

指定字段组合不重复记录

类型 说明 基本语法 select distinct 字段名 from 数据表名；#distinct 表示去重； 示例 select distinct username from users； 示例说明 表示查询users表中username不重复的记录

（1）like模糊查询（%表示通配符）

示例：

select * from users where username like \'王%\'; #表示查询姓名中姓王的数据

（2）限制查询的结果集

类型 说明 基本语法 select 字段 from 表名 limit 数量； 示例 select * from users limit 3； 示例说明 查询users表中的前3条数据

（3）分组查询

类型 说明 基本语法 select 字段 from 表名 group by 字段名； 示例 select username from users group by username； 示例说明 查询users表中的数据按用户名分组，查询总共有哪些用户；

（4）分组统计

类型 说明 基本语法 select count(字段名) ，字段名 from 表名 group by 字段名； 示例 select count(*) as 数量,province from users group by province ； 示例说明 统计users表中各个省份的明星的数量；

（5）结果集过滤

类型 说明 基本语法 select count(字段名) as result，字段名 from 表名 group by 字段名 having 条件； 示例 select count(*) as result,province from users group by province having result >=2； 示例说明 统计users表中明星的数量大于等于2的省份；

（6）结果集排序

1. 单字段排序，基本语法：

select 字段名 from 数据表名字 order by 字段 排序关键字;

关键词 说明 asc 升序排序(默认值) desc 降序排序

示例：

select * from users order by age asc; #查询出users的数据，按照age字段的升序排序

2. 多字段排序：

类型 说明 基本语法 select 字段名 from 表名 order by 字段1 desc 示例 select * from users order by money desc ,age asc; 示例说明 查询users表中所有的数据，按照money字段降序，若money字段值一样，按照age字段升序

3. 限制排序后的结果集：

类型 说明 基本语法 select 字段名 from 表名 order by 字段 排序规则 limit 数量； 示例 select * from users order by money desc limit 3; 示例说明 查询users表中所有的数据，按照money字段降序，最有钱的3个人；

4. 结果集区间选择：

类型 说明 基本语法 select 字段名 from 表名 order by 字段 排序规则 limit 偏移量 数量； 示例 select * from users order by money desc limit 3 3; 示例说明 查询users表中按照money字段降序，从第4条数据开始往后取3条数据；

3. 更新数据

使用update语句来更新数据表中的数据，语法如下：

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

例如，将名为users的数据表中id为1的记录的name字段更新为John Smith：

update users set name = \'John Smith\' where id = 1;

4. 删除数据

使用delete from 语句来删除数据表中的数据，语法如下：

delete from 数据表名 where 条件; 

例如，删除名为users的数据表中id为1的记录：

delete from users where id = 1;

注意，如果where后面没有跟条件，默认是删除所有数据

4.2表字段操作

1.增加表字段

类型 说明 基本语法 alter table 数据表名称 add 字段名 类型； 示例 alter table users add age int； 示例说明 向users 表中添加一个age字段，类型为int；

2.修改表字段

类型 说明 基本语法 alter table 数据表名称 modify 字段名 类型； 示例 alter table users modify username varchar(33); 示例说明 将users表中的username字段的类型修改为varchar(33);

3.删除表字段

类型 说明 基本语法 alter table 数据表名称 drop 字段名； 示例 alter table users drop password; 示例说明 将表users中的password字段删除；

4.表字段改名

类型 说明 基本语法 alter table 数据表名称 change 原字段名 新字段名 类型； 示例 alter table users change age nianling int(3); 示例说明 将表users中的age字段名称改为nianling 类型为int(3);

5.统计函数

5.1常见的统计函数

类型 说明 示例 sum 求和 select sum(money) as 别名 from users；统计users表中的money字段的总和 count 统计数量 select count(id) as 别名 from users；统计users表中的数据总条数 max 最大值 select max(money) as 最大值 from users；统计users表中的money字段的最大值 min 最小值 select min(money) as 最大值 from users；统计users表中的money字段的最小值 avg 平均值 select avg(money) as 最大值 from users；统计users表中的money字段的平均值

6.联合查询

多表联合查询
先创建几个表单和数据

create table user(id int auto_increment,username char(100),password char(100),gid int,primary key(id));

insert into user(username,password,gid) values(\'张三\',123,2),(\'李四\',987,4),(\'王五\',5677,1),(\'赵六\',88776,3),(\'宋琦\',23435,0),(\'刘元\',34545,7),(\'郭德纲\',456,2),(\'王波\',123,0),(\'王强\',123,3),(\'宋桥\',123,3);

create table goods(id int auto_increment,name char(100),price int(20),primary key(id));

insert into goods(name,price) values(\'奥迪\',99999),(\'比亚迪\',99999),(\'奔驰\',99999),(\'大众\',99999),(\'特斯拉\',99999),(\'宝马\',99999),(\'丰田\',99999);

6.1内连接

• 隐式内连接

类型 说明 示例 基本语法 select 表1.字段名【as 别名】,表n.字段名 from 表1【as 别名】,表n where 条件； 示例 select user.username,goods.name from user,goods where user.gid=goods.id; 示例说明 查询用户表中的哪些用户购买过商品以及商品名称；gid和goods中的id必须一一对应

注意：以上的方式属于隐式内连接，是因为sql语句中未出现inner join关键字。

• 显式内连接

类型 说明 示例 基本语法 select 表1.字段名【as 别名】,表n.字段名 from 表1 inner jion 表n on 条件； 示例 select user.username,goods.name from user inner join goods on user.gid=goods.id; 示例说明 查询用户表中的哪些用户购买过商品以及商品名称；gid和goods中的id必须一一对应

注意：在显式内连接，通过inner join 关键字实现表之间的连接，inner可以省略。

6.2外连接

• 外连接之左连接

类型 说明 示例 基本语法 select 表1.字段名【as 别名】,表n.字段名 from 表1 left jion 表n on 条件； 示例 select * from user left join goods on user.gid=goods.id; 示例说明 以(左表suer)为主，查询出用户购买过哪些商品，并且将商品信息查询出来；

注意：左外连接会把左表中所有的数据查询出来，会把右表中符合条件的数据查询出来。

• 外连接之右连接

类型 说明 示例 基本语法 select 表1.字段名【as 别名】,表n.字段名 from 表1 right jion 表n on 条件； 示例 select * from user right join goods on user.gid=goods.id; 示例说明 以(右表goods)为主，查询出哪些商品被用户购买过，并且将用户信息查询出来；

注意：右外连接会把右表中所有的数据查询出来，会把左表中符合条件的数据查询出来。

6.3子嵌套查询

类型 说明 示例 基本语法 select 字段名 from 表名 where 字段 in （子查询语句）； 示例 select * from user where gid in (select id from goods); 示例说明 将购买过商品的用户信息查询出来；

7.窗口函数

用法：窗口函数 over(partition by 分组字段 order by 排序字段）

（1）序号函数：

类型 说明 rank() 返回数据集中每个值的排名。排名值是管局当前行之前的行数加1，不包含当前行。因此排序的关联值可能产生顺序上的空隙。这个排名会对每个窗口分区进行计算； dense_rank() 返回一组数值中每个数值的排名。这个函数与rank()相似，除了关联值不会产生顺序上的空隙; row_number() 为每行数据返回一个唯一的顺序的序号，从1开始，根据行在窗口的分区内的顺序;

例如：

select *,rank() over(partition by class order by score desc) as ranking,dense_rank() over(partition by class order by score desc) as decse,row_number() over(partition by class order by score desc) as rownumberfrom student;

（2）分布函数：

类型 说明 cume_dist() 表示当前行及小于当前行在窗口分区总行数中的占比；

需求：按照订单数统计在总订单数中的占比

select *,cume_dist() over(partition by user_num order by amount desc) as cume from order1;

（3）前后函数：

• lag() 向上偏移
• lead()向下偏移

应用场景：

1. 取时间间隔为N天的记录
2. 计算本次记录与上次记录的差值
3. 取某一字段的前N行数据或后N行数据

三个参数：第一个参数是表达式或字段，第二个参数是偏移量，第三个参数是控制赋值。

如：查询上一个订单距离当前订单的时间间隔

select *，datediff(dt,last_date) as diff from(select user_num,dt,lag(dt,1,dt) over w as last_date from order1window w as (order by dt) #给窗口函数起个别名)t;#t表示给sql语句起别名

（4）头尾函数：firs_value() / last_value()

按照时间顺序记录每个用户的最早订单和最晚订单，并不是最小金额和最大金额的订单；
需求：截止当前订单，按照日期排序第一个订单和最后一个订单的订单金额；

select * from(select *,first_value(amount) over w as first_amount,last_value(amount) over w as last_amountfrom order1window w as (partition by user_num order by dt))t; #t表示给sql语句起个别名