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）结果集排序
- 单字段排序，基本语法：
select 字段名 from 数据表名字 order by 字段 排序关键字;
| 关键词 | 说明 |
|---|---|
| asc | 升序排序(默认值) |
| desc | 降序排序 |
示例：
select * from users order by age asc; #查询出users的数据，按照age字段的升序排序
- 多字段排序：
| 类型 | 说明 |
|---|---|
| 基本语法 | select 字段名 from 表名 order by 字段1 desc |
| 示例 | select * from users order by money desc ,age asc; |
| 示例说明 | 查询users表中所有的数据，按照money字段降序，若money字段值一样，按照age字段升序 |
- 限制排序后的结果集：
| 类型 | 说明 |
|---|---|
| 基本语法 | select 字段名 from 表名 order by 字段 排序规则 limit 数量； |
| 示例 | select * from users order by money desc limit 3; |
| 示例说明 | 查询users表中所有的数据，按照money字段降序，最有钱的3个人； |
- 结果集区间选择：
| 类型 | 说明 |
|---|---|
| 基本语法 | 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 rownumber
from student;
（2）分布函数：
| 类型 | 说明 |
|---|---|
| cume_dist() | 表示当前行及小于当前行在窗口分区总行数中的占比； |
需求：按照订单数统计在总订单数中的占比
select *,cume_dist() over(partition by user_num order by amount desc) as cume from order1;
（3）前后函数：
- lag() 向上偏移
- lead()向下偏移
应用场景：
- 取时间间隔为N天的记录
- 计算本次记录与上次记录的差值
- 取某一字段的前N行数据或后N行数据
三个参数：第一个参数是表达式或字段，第二个参数是偏移量，第三个参数是控制赋值。
如：查询上一个订单距离当前订单的时间间隔
select *，datediff(dt,last_date) as diff from(
select user_num,dt,lag(dt,1,dt) over w as last_date from order1
window 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_amount
from order1
window w as (partition by user_num order by dt)
)t; #t表示给sql语句起个别名
扫一扫
1641
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
