Mysql基础

安装

https://downloads.mysql.com/archives/community/

初始化

"D:安装目录\bin\mysqld.exe" --initialize-insecure
#会创建一个初始化root账户（无密码）

配置文件

在安装目录创建my.ini
内容:
[mysqld]

# port 端口
port = 3306

#set basedir to your installation path安装目录
basedir = D:\\EXE\\mysql-5.7.31-winx64

#set datadir tO the location 0 f your data directory创建的文件目录
datadir = D:\\EXE\\mysql-5.7.31-winx64\\data

#非严格模式
#sql_mode=STRICT_TRANS_TABLES,NO_ZERO_in_date,NO_zero_date,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_user,NO_ENGINE_SUBSTITUTION

一些命令

show variables like '%time_zone%'; -- 查询当前数据库的时区

set time_zone ='0:00'; -- 改时区

启动Mysql

• 临时启动

"安装目录\bin\mysqld.exe"

• 制作成windous服务

"安装目录\bin\mysqld.exe" --install 名称可自定义
启动:
net start 名字
关闭
net stop 名字

删除服务
"安装目录"\bin\mysqld.exe" --remove 名字

• 使用命令行连接Mysql

"安装目录"\bin\mysqld.exe" -h ip地址 -P 端口 -u 用户名 -p 密码

• 设置密码

mysql> set password = password("root123");

• 忘记密码

在配置文件中[mysqld节点下添加]
skip-grant-tables=1

让后重启mysql，就不用密码就可用登录了

设置密码
use mysql;
update user set authentication_string = password("新密码"),password_last_changed=now() where user ='root';

数据库操作

• 查看当前所有数据库: show databases;

• 创建数据库：create database 数据库名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

• 删除数据库:drop database 数据库名;

• 进入数据库： use 数据库;

数据表操作

• 查看当前数据库下的所有数据表show tables;
• 创建表结构

create table 表名(
	列名 类型,
    列名 类型,
    列名 类型 -- 一定要记住最后一个不用加","号
) default charset=utf8;

-- 例子
create table tb1(
	id int,
    name varchar(16),
) default charset=utf8;

-- 定义规则
create table tb1(
    userid int primary key, -- 主键 （不能为空,不能重复）
	id int not null,-- 不允许为空
    name varchar(16) null,-- 允许为空（默认）
    age int default 3, -- 插入数据时，如果不给age值则默认为3
    nbid int auto_increment primary key -- 不允许为空&主键&自增
) default charset=utf8;

-- 一个表中只能有一个自增列，一般都是主键

• 删除表drop table 表名;
• 清空表delete from 表名; 或truncate table 表名;(速度快，但无法回滚撤销等)

修改表（列操作）

• 查看所有列: desc 表名

• 添加列

  alter table 表名 add 列表 类型;
  alter table 表名 add 列表 类型 DEFAULT 默认值;
  alter table 表名 add 列表 类型 not null default 默认值;
  alter table 表名 add 列表 类型 not null auto_increment primary key;
  
  
  alter table 表名 add column 列名 char(1) not null default '男' check (列名 in ('男', '女'));
  -- 使用CHECK关键字可以添加约束，确保这个列的取值仅能为'男'或'女'

• 删除列

  alter table 表名 drop column 列名;

• 修改列类型

  alter table 表名 modify column 列名 类型;

• 修改列 类型—+名称

  alter table 表名 change 原列名 新列名 新类型 (后面一样可用更规则);

• 修改列 默认值

  ALTER TABLE 表名 ALTER 列名 SET DEFAULT 1000;

• 删除列 默认值

  ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;

常见列类型

int[(m)][unsigned][zerofill]
int   -- 表示有符号，取值范围:int的最小范围到最大范围
int unsigned -- 无符号 ，取值范围: 0~4294967295
int(5)zerofill -- 仅用于显示，当不满足5位时，按照左边补0，列如：00002；满足时，正常显示

数据操作/数据类型

• 整数

-- 在表中添加数据
insert into 表名(列名,列名) values(数据,数据);
-- 查看表中的数据
select 列名(可用通配符比如*代表这个表中的所有数据) from 表名;

tinyint[(m)] [unsigned] [zerofill]
有符号,取值范围:-128 ~127
无符号 : 0~255

bigint[(m)][(unsigned)] [(zerofill)]
有符号: -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 
无符号:0到18446744073709551615

mediumint
-- 一个中等大小整数，有符号的范围是-8388608到8388607，无符号的范围是0到16777215。 一位大小为3个字节


smallint
-- 一个小整数。有符号的范围是-2^15 (-32,768) 到 2^15 - 1 (32,767) 的整型数据，无符号的范围是0到65535。一位大小为 2 个字节

• 小数

decimal[(m,d)] [unsigned] [zerofill]
-- 在MySQL中，Decimal类型是一种数值类型，用于表示精确的十进制数。它通常用于存储要求高精度的和精确度的的小数，例如货币、积分等。
-- 在MySQL中，Decimal类型的可以表示很大的数值范围，具体取决于其精度和刻度。精度表示总位数，刻度表示小数点后的位数。
decimal(m,d) -- m表示整数位数 d表示小数点后的个数（m最大值=65,d最大值=30）
-- 需要注意的是，虽然Decimal类型的表示范围很大，但在实际使用时，应根据具体需求选择合适的精度和刻度，以避免数据溢出或精度损失的问题。

-- 如果超出了小数的精度，则数据库会对其作四舍五入

FLOAT [(M,D)] [UNSIGNED] [ZEROFILL]
-- 单精度浮点小数，非准确小数值，m是数字总个数，d是小数点后个数
DOUBLE [(M,D)] [UNSIGNED] [ZEROFILL]
-- 双精度浮点小数，非准确小数值，m是数字总个数，d是小数点后个数

• 字符类型

  char(m) -- m代表字符串的长度，最多可容纳255【字符】(即使数据小于m的长度也会占用m个字符数,并且在存储的时候空的位置默认用空格补齐，但查询数据的时候会去除，如果不想去除在配置文件中的sql-mod中加入 PAD_CHAR_TO_FULL_LENGTH)
  -- 查看模式sql-mod: show variables like 'sql_mode';
  -- 一般用于固定长度的内容
  
  
  varchar(m) -- m代表字符串的最大长度，最多可容纳65535个【字节】
  -- 当内容小于m时会按照真实长度存储；如果超出（严格模式下会报错）非严格模式会截断
  
TINYTEXT：-- 非常小的文本类型，最大长度为255个字符。

TEXT：-- 文本类型，最大长度为65535个字符。

MEDIUMTEXT：-- 中等大小的文本类型，最大长度为16777215个字符。

LONGTEXT：-- 非常大的文本类型，最大长度为4294967295个字符。

BLOB：-- 二进制大对象类型，用于存储二进制数据，最大长度为65535个字节。

TINYBLOB：-- 非常小的二进制大对象类型，最大长度为255个字节。

MEDIUMBLOB： -- 中等大小的二进制大对象类型，最大长度为16777215个字节。

LONGBLOB：-- 非常大的二进制大对象类型，最大长度为4294967295个字节。

• 时间

date：仅存储日期，格式为'YYYY-MM-DD'。-- '1000-01-01'到'9999-12-31'，共9888天。

time：仅存储时间，格式为'HH:MM:SS'。-- '00:00:00'到'23:59:59'，共838859秒。

datetime：存储日期和时间，格式为'YYYY-MM-DD HH:MM:SS'。-- '1000-01-01 00:00:00'到'9999-12-31 23:59:59'，共3153583767分钟。--（不做任何改变，输入的啥输出就是啥）

timestamp：存储日期和时间，格式与DATETIME相同，但时间值以秒为单位存储，范围从'1970-01-01 
00:00:01'到'2038-01-19 03:14:07' -- (会将用户输入的时间从当前时区转换成utc时间，查询时将其又转化为客户端当前的时区进行返回)

year：仅存储年份，可以使用2位（'YY'）或4位（'YYYY'）格式。-- 

数据行操作

• 新增数据

insert into 表名 (列名,列名,列名) values(对应列的值，对应列的值，对应列的值)
-- 新增多条
insert into 表名 (列名,列名) values(值,值),(值,值);
-- 可不指定列名（默认从第一列向后添加）【必须要有与值对应数量的列】
insert into 表名 values(值,值),(值,值);

• 删除数据

  delete from 表名;
  delete from 表名 where 条件;
  --  比如:
  delete from tb1 where name="jxms" and password ="123";
  delete from tb1 where name="jxms" or password ="123";
  delete from tb1 where id>9;
  
  -- 删除数据并重置自增id
  truncate table 表名;

• 修改数据

  update 表名 set 列名=值;
  update 表名 set 列名=值 where 条件
  -- 比如:
  update tb1 set name="jxsm" where id=1;
  -- concat是一个函数，可以拼接字符串(name为数据库中的name)
  update users set name=concat(name,"123") where id=2;
  -- 可以用自身数据相加（age必须为整形）
  update tb1 set age=age+1;

• 查询数据

  select * from 表名;
  select 列名 列名 列名 from 表名;
  select 列名,列名 as 别名,列名 from 表名; -- 别名是让最后的结果的表头变成别名
  select * from 表名 where 条件;
  
  -- 比如
  select * from tb1 where id !=1;
  select name,id,111 from tb1; -- 查询的时候会新曾一列，默认值为111

常用函数

数学函数

（1）ABS(x)

返回x的绝对值

（2）PI()

返回圆周率π，默认显示6位小数

（3）SQRT(x)

返回非负数的x的二次方根

（4）MOD(x,y)

返回x被y除后的余数

（5）CEIL(x)、CEILING(x)

返回不小于x的最小整数

（6）FLOOR(x)

返回不大于x的最大整数

（7）ROUND(x)、ROUND(x,y)

前者返回最接近于x的整数，即对x进行四舍五入；后者返回最接近x的数，其值保留到小数点后面y位，若y为负值，则将保留到x到小数点左边y位

（8）SIGN(x)

返回参数x的符号，-1表示负数，0表示0，1表示正数

（9）POW(x,y)和、POWER(x,y)

返回x的y次乘方的值

（10）EXP(x)

返回e的x乘方后的值

（11）LOG(x)

返回x的自然对数，x相对于基数e的对数

（12）LOG10(x)

返回x的基数为10的对数

（13）RADIANS(x)

返回x由角度转化为弧度的值

（14）DEGREES(x)

返回x由弧度转化为角度的值

（15）SIN(x)、ASIN(x)

前者返回x的正弦，其中x为给定的弧度值；后者返回x的反正弦值，x为正弦

（16）COS(x)、ACOS(x)

前者返回x的余弦，其中x为给定的弧度值；后者返回x的反余弦值，x为余弦

（17）TAN(x)、ATAN(x)

前者返回x的正切，其中x为给定的弧度值；后者返回x的反正切值，x为正切

（18）COT(x)

返回给定弧度值x的余切

 

字符串函数

（1）CHAR_LENGTH(str)

计算字符串字符个数

（2）CONCAT(s1,s2，...)

返回连接参数产生的字符串，一个或多个待拼接的内容，任意一个为NULL则返回值为NULL

（3）CONCAT_WS(x,s1,s2,...)

返回多个字符串拼接之后的字符串，每个字符串之间有一个x

（4）INSERT(s1,x,len,s2)

返回字符串s1，其子字符串起始于位置x，被字符串s2取代len个字符

（5）LOWER(str)和LCASE(str)、UPPER(str)和UCASE(str)

前两者将str中的字母全部转换成小写，后两者将字符串中的字母全部转换成大写

（6）LEFT(s,n)、RIGHT(s,n)

前者返回字符串s从最左边开始的n个字符，后者返回字符串s从最右边开始的n个字符

（7）LPAD(s1,len,s2)、RPAD(s1,len,s2)

前者返回s1，其左边由字符串s2填补到len字符长度，假如s1的长度大于len，则返回值被缩短至len字符；前者返回s1，其右边由字符串s2填补到len字符长度，假如s1的长度大于len，则返回值被缩短至len字符

（8）LTRIM(s)、RTRIM(s)

前者返回字符串s，其左边所有空格被删除；后者返回字符串s，其右边所有空格被删除

（9）TRIM(s)

返回字符串s删除了两边空格之后的字符串

（10）TRIM(s1 FROM s)

删除字符串s两端所有子字符串s1，未指定s1的情况下则默认删除空格

（11）REPEAT(s,n)

返回一个由重复字符串s组成的字符串，字符串s的数目等于n

（12）SPACE(n)

返回一个由n个空格组成的字符串

（13）REPLACE(s,s1,s2)

返回一个字符串，用字符串s2替代字符串s中所有的字符串s1

（14）STRCMP(s1,s2)

若s1和s2中所有的字符串都相同，则返回0；根据当前分类次序，第一个参数小于第二个则返回-1，其他情况返回1

（15）SUBSTRING(s,n,len)、MID(s,n,len)

两个函数作用相同，从字符串s中返回一个第n个字符开始、长度为len的字符串

（16）LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str,str1)

三个函数作用相同，返回子字符串str1在字符串str中的开始位置（从第几个字符开始）

（17）REVERSE(s)

将字符串s反转

（18）ELT(N,str1,str2,str3,str4,...)

返回第N个字符串

 

日期和时间函数

（1）CURDATE()、CURRENT_DATE()

将当前日期按照"YYYY-MM-DD"或者"YYYYMMDD"格式的值返回，具体格式根据函数用在字符串或是数字语境中而定

（2）CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE()

这四个函数作用相同，返回当前日期和时间值，格式为"YYYY_MM-DD HH:MM:SS"或"YYYYMMDDHHMMSS"，具体格式根据函数用在字符串或数字语境中而定

（3）UNIX_TIMESTAMP()、UNIX_TIMESTAMP(date)

前者返回一个格林尼治标准时间1970-01-01 00:00:00到现在的秒数，后者返回一个格林尼治标准时间1970-01-01 00:00:00到指定时间的秒数

（4）FROM_UNIXTIME(date)

和UNIX_TIMESTAMP互为反函数，把UNIX时间戳转换为普通格式的时间

（5）UTC_DATE()和UTC_TIME()

前者返回当前UTC（世界标准时间）日期值，其格式为"YYYY-MM-DD"或"YYYYMMDD"，后者返回当前UTC时间值，其格式为"YYYY-MM-DD"或"YYYYMMDD"。具体使用哪种取决于函数用在字符串还是数字语境中

（6）MONTH(date)和MONTHNAME(date)

前者返回指定日期中的月份，后者返回指定日期中的月份的名称

（7）DAYNAME(d)、DAYOFWEEK(d)、WEEKDAY(d)

DAYNAME(d)返回d对应的工作日的英文名称，如Sunday、Monday等；DAYOFWEEK(d)返回的对应一周中的索引，1表示周日、2表示周一；WEEKDAY(d)表示d对应的工作日索引，0表示周一，1表示周二

（8）WEEK(d)、WEEKOFYEAD(d)

前者计算日期d是一年中的第几周，后者计算某一天位于一年中的第几周

（9）DAYOFYEAR(d)、DAYOFMONTH(d)

前者返回d是一年中的第几天，后者返回d是一月中的第几天

（10）YEAR(date)、QUARTER(date)、MINUTE(time)、SECOND(time)

YEAR(date)返回指定日期对应的年份，范围是1970~2069；QUARTER(date)返回date对应一年中的季度，范围是1~4；MINUTE(time)返回time对应的分钟数，范围是0~59；SECOND(time)返回制定时间的秒值

（11）EXTRACE(type FROM date)

从日期中提取一部分，type可以是YEAR、YEAR_MONTH、DAY_HOUR、DAY_MICROSECOND、DAY_MINUTE、DAY_SECOND

（12）TIME_TO_SEC(time)

返回以转换为秒的time参数，转换公式为"3600*小时 + 60*分钟 + 秒"

（13）SEC_TO_TIME()

和TIME_TO_SEC(time)互为反函数，将秒值转换为时间格式

（14）DATE_ADD(date,INTERVAL expr type)、ADD_DATE(date,INTERVAL expr type)

返回将起始时间加上expr type之后的时间，比如DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 SECOND)表示的就是把第一个时间加1秒

（15）DATE_SUB(date,INTERVAL expr type)、SUBDATE(date,INTERVAL expr type)

返回将起始时间减去expr type之后的时间

（16）ADDTIME(date,expr)、SUBTIME(date,expr)

前者进行date的时间加操作，后者进行date的时间减操作

 

条件判断函数

（1）IF(expr,v1,v2)

如果expr是TRUE则返回v1，否则返回v2

（2）IFNULL(v1,v2)

如果v1不为NULL，则返回v1，否则返回v2

（3）CASE expr WHEN v1 THEN r1 [WHEN v2 THEN v2] [ELSE rn] END

如果expr等于某个vn，则返回对应位置THEN后面的结果，如果与所有值都不想等，则返回ELSE后面的rn

 

系统信息函数

（1）VERSION()

查看MySQL版本号

（2）CONNECTION_ID()

查看当前用户的连接数

（3）USER()、CURRENT_USER()、SYSTEM_USER()、SESSION_USER()

查看当前被MySQL服务器验证的用户名和主机的组合，一般这几个函数的返回值是相同的

（4）CHARSET(str)

查看字符串str使用的字符集

（5）COLLATION()

查看字符串排列方式

 

加密函数

（1）PASSWORD(str)

从原明文密码str计算并返回加密后的字符串密码，注意这个函数的加密是单向的（不可逆），因此不应将它应用在个人的应用程序中而应该只在MySQL服务器的鉴定系统中使用

（2）MD5(str)

为字符串算出一个MD5 128比特校验和，改值以32位十六进制数字的二进制字符串形式返回

（3）ENCODE(str, pswd_str)

使用pswd_str作为密码，加密str

（4）DECODE(crypt_str,pswd_str)

使用pswd_str作为密码，解密加密字符串crypt_str，crypt_str是由ENCODE函数返回的字符串

 

其他函数

（1）FORMAT(x,n)

将数字x格式化，并以四舍五入的方式保留小数点后n位，结果以字符串形式返回

（2）CONV(N,from_base,to_base)

不同进制数之间的转换，返回值为数值N的字符串表示，由from_base进制转换为to_base进制

（3）INET_ATON(expr)

给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数，地址可以使4或8比特

（4）INET_NTOA(expr)

给定一个数字网络地址（4或8比特），返回作为字符串的该地址的点地址表示

（5）BENCHMARK(count,expr)

重复执行count次表达式expr，它可以用于计算MySQL处理表达式的速度，结果值通常是0（0只是表示很快，并不是没有速度）。另一个作用是用它在MySQL客户端内部报告语句执行的时间

（6）CONVERT(str USING charset)

使用字符集charset表示字符串str

Python方面

• 安装pymysql : pip install pymysql

连接数据库

import pymysql
#连接数据库
#连接信息  db参数可不加表示默认自动进入db这个数据库
conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',passwd='jxsm',charset="utf8",db="数据库名")
#创建游标
cursor  = conn.cursor()

#发送命令
cursor.execute("show databases")
#获取命令输出结果
result = cursor.fetchall()
print (result)


#创建数据库
cursor.execute( 'create database 数据库名 DEFAULT CHARSET utf8 COLLATE  utf8_general_ci')
#必须要有才能执行（增删改查都是）
conn.commit()

#关闭连接
cursor.close()
conn.close

数据操作

查询数据

import pymysql
#连接数据库 #并默认进入到temp中
conn = pymysql.connect(host='127.0.0.1', port=3306,user='root',passwd='jxsm',charset="utf8" ,db="temp")
#创建游标，之后通过游标进行操作
cursor  = conn.cursor()

#发送命令
# cursor.execute("insert into temp (name,age) values('jxsm',12)")
#查询数据
cursor.execute("select * from temp where id=2")
#获取命令输出结果
result = cursor.fetchall() #或 result = cursor.fetchone
#fetchone()`每次调用只获取一行数据，因此如果需要获取整个结果集，则需要调用多次。
#而fetchall()函数则用于获取查询结果集中的所有行数据，返回一个包含元组的列表。如果结果集太大，可能会消耗大量内存，因此需要谨慎使用。
print (result,type(result))

• 储存案例

  def writeIn(username: str, password: str):  
      # 连接数据库  
      conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='jxsm', charset='utf8', db='userdb')  
      # 创建游标  
      cursor = conn.cursor()  
    
      # 存储数据  
      cursor.execute(f"INSERT INTO `user` (`name`, `password`) VALUES ('{username}', '{password}')")  
      # 提交更改  
      conn.commit()  
    
      # 打印返回结果  
      result = cursor.fetchall()  
      print(result)  
    
      # 断开连接  
      cursor.close()  
      conn.close()

• 查询案例

  def query(name: str, password: str) -> bool:  
      # 连接数据库  
      conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='jxsm', charset='utf8', db='userdb')  
      # 创建游标  
      cursor = conn.cursor()  
      # 发送查询命令  
      cursor.execute(f"select name, password from user where name=%s and password=%s", (name, password))  
      # 获取返回结果  
      result = cursor.fetchall()  
      if result is None or cursor.rowcount == 0: 
          cursor.close()  
          conn.close() 
          return False
      else:  
          if result[0] == name:
              if result[1] == password:
                  cursor.close()  
                  conn.close()
                  return True
              else:
                  cursor.close()  
                  conn.close()
                  return False
          else :
              cursor.close()  
              conn.close()
              return False

  时间计算

  #获取时间差秒
  import datetime  
    
  # 创建一个datetime对象  
  dt = datetime.datetime(2023, 3, 14, 12, 0, 0)  
    
  # 计算时间差  
  td = dt - datetime.datetime.now()  
    
  # 获取剩余的时间秒数  
  remaining_seconds = td.total_seconds()  
    
  print("剩余的时间秒数：", remaining_seconds)

防注入

#在sql命令拼接的时候用pymysql自带的方法，通过占位符来实现与列表或元组实现
  cursor.execute("select name, password from user where name=%s and password=%s", (name, password))
  #当让也可以使用字典实现
  cursor.execute("select name, password from user where name=%(n1)s and password=%(n2)s", {n1:name,n2:password})

一些常用的命令

#获取返回结果的行数
游标名.rowcount
#获取当前时间 使用datetime
import datetime
print(datetime.datetime.now())