ToB企服应用市场:ToB评测及商务社交产业平台

标题: MySQL的具体使用教程 [打印本页]
作者: 慢吞云雾缓吐愁    时间: 2025-1-15 09:28
标题: MySQL的具体使用教程
1. 毗连到MySQL服务器

在使用MySQL之前，需要先毗连到MySQL服务器。可以使用命令行工具（如MySQL Shell或终端）或图形化工具（如phpMyAdmin）来毗连。以下是使用命令行工具毗连到MySQL服务器的示例：
  1. 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的数据库的示例：
  1. create database my_database;
复制代码

实行上述语句后，MySQL将创建一个名为my_database的数据库。如果要查看所有数据库，可以使用show database语句
  1. show databases;
复制代码

2-2删除数据库

使用drop database语句来删除数据库，语法如下：
  1. drop database my_database;
复制代码
  1. 注意：删除数据库是一个危险操作，因为它将永久删除数据库及其包含的所有数据表和数据。在执行此操作之前，请确保你已经备份了重要的数据
复制代码
3. 数据表操作

3.1 选择数据库

在创建数据表之前，需要先选择要在其中创建数据表的数据库。使用USE语句来选择数据库，语法如下：
  1. use my_database;
复制代码
3.2 创建数据表

使用create table语句来创建数据表，语法如下：
  1. CREATE TABLE table_name (
  2.     column1 datatype constraints,
  3.     column2 datatype constraints,
  4.    ...
  5. );
复制代码
其中，table_name是数据表的名称，column1、column2等是数据表的列名，datatype是列的数据类型，constraints是列的约束条件（如主键、非空等）。
例如，创建一个名为users的数据表，包含id、name和email三个列：
  1. CREATE TABLE users (
  2.     id INT PRIMARY KEY AUTO_INCREMENT,
  3.     name VARCHAR(255) NOT NULL,
  4.     email VARCHAR(255) NOT NULL UNIQUE
  5. )engine = innodb default charset = utf-8;
复制代码
以下是MySQL中常见的数据库表字段数据类型的表格列举：
数据类型描述示例整数类型tinyint1字节，取值范围： -128~127CREATE TABLE test (id TINYINT);smallint2字节，取值范围： -32768~32767num SMALLINT;mediumint3字节，取值范围： -8388608~8388607count MEDIUMINT;int4字节，取值范围： -2147483648~2147483647age INT;bigint8字节，取值范围： -9223372036854775808到9223372036854775807big_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语句：
  1. show tables;
复制代码

这将列出当前选择的数据库中的所有数据表。
要查看数据表的结构，可以使用describe(可以缩写成desc)或show colunmns from语句，如查看名为users的数据表结构：
  1. describe users;
复制代码

3.4 修改数据表

语法：alter table 旧数据表名 rename 新数据表名字；
示例：
  1. alter table users rename new_users;
复制代码
3.5 删除数据表

使用drop table 语句来删除数据表，如删除名为users的数据表:
  1. drop table users;
复制代码

4. 数据内容操作

4.1数据操作

1. 插入数据

使用insert into语句来向数据表中插入数据，语法如下：
  1. insert into table_name (column1, column2,...) values (value1, value2,...);   
复制代码
例如，向名为users的数据表中插入一条记载：
  1. insert into users (name,email) values ('john','john@qq.com');
复制代码
  1. 注意值的数量和数据表字段的数量保持一致，除非有默认值可以不用写
复制代码
如果要插入多条记载，写法如下：
  1. INSERT INTO users (name, email) VALUES
  2.     ('Jane Smith', 'jane@example.com'),
  3.     ('Bob Johnson', 'bob@example.com');   
复制代码
2. 查询数据

使用select语句来查询数据表中的数据，语法如下:
  1. SELECT column1, column2,... FROM table_name WHERE condition;
复制代码
其中，column1、column2等是要查询的列名，如果要查询所有列，可以使用*代替；table_name是要查询的数据表名称；WHERE子句用于指定查询条件，可根据需要省略。
例如，查询名为users的数据表中的所有记载：
  1. SELECT * FROM users;
复制代码
查询名为users的数据表中name为Jane Smith的记载：
  1. SELECT * FROM users WHERE name = 'Jane Smith';   
复制代码
指定字段组合不重复记载
类型说明根本语法select distinct 字段名 from 数据表名；#distinct 表示去重；示例select distinct username from users；示例说明表示查询users表中username不重复的记载 （1）like暗昧查询（%表示通配符）

示例：
  1. 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降序排序 示例：
  1. 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语句来更新数据表中的数据，语法如下：
  1. update 数据表名 set 字段1 = 新的值, 字段2 = 新的值,... where 条件;
复制代码
例如，将名为users的数据表中id为1的记载的name字段更新为John Smith：
  1. update users set name = 'John Smith' where id = 1;
复制代码

4. 删除数据

使用delete from 语句来删除数据表中的数据，语法如下：
  1. delete from 数据表名 where 条件;   
复制代码
例如，删除名为users的数据表中id为1的记载：
  1. delete from users where id = 1;
复制代码

  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.联合查询

多表联合查询
先创建几个表单和数据
  1. create table user(id int auto_increment,username char(100),        password char(100),        gid int,primary key(id));
复制代码
  1. 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);
复制代码
  1. create table goods(        id int auto_increment,        name char(100),        price int(20),        primary key(id));
复制代码
  1. 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开始，根据行在窗口的分区内的顺序; 例如：
  1. select *,
  2. rank() over(partition by class order by score desc) as ranking,
  3. dense_rank() over(partition by class order by score desc) as decse,
  4. row_number() over(partition by class order by score desc) as rownumber
  5. from student;
复制代码

（2）分布函数：

类型说明cume_dist()表示当前行及小于当前行在窗口分区总行数中的占比； 需求：按照订单数统计在总订单数中的占比
  1. select *,cume_dist() over(partition by user_num order by amount desc) as cume from order1;
复制代码

（3）前后函数：


应用场景：
三个参数：第一个参数是表达式或字段，第二个参数是偏移量，第三个参数是控制赋值。
如：查询上一个订单间隔当前订单的时间间隔
  1. select *，datediff(dt,last_date) as diff from(
  2.         select user_num,dt,lag(dt,1,dt) over w as last_date from order1
  3.         window w as (order by dt) #给窗口函数起个别名
  4. )t;#t表示给sql语句起别名
复制代码

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

按照时间顺序记载每个用户的最早订单和最晚订单，并不是最小金额和最大金额的订单；
需求：截止当前订单，按照日期排序第一个订单和末了一个订单的订单金额；
  1. select * from(
  2.         select *,
  3.         first_value(amount) over w as first_amount,
  4.         last_value(amount) over w as last_amount
  5.         from order1
  6.         window w as (partition by user_num order by dt)
  7. )t; #t表示给sql语句起个别名
复制代码


免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4