小结3 mysql pymysql

最新推荐文章于 2024-07-17 09:26:14 发布

lrshcbxf

最新推荐文章于 2024-07-17 09:26:14 发布

阅读量117

点赞数

本文链接：https://blog.csdn.net/weixin_45467375/article/details/99673453

版权

mysql

安装

初始化

在该文件夹下创建 my.ini 配置文件，编辑 my.ini 配置以下基本信息：

[client]
#设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
#设置3306端口
port = 3306
#设置mysql的安装目录
basedir=C:\\web\\mysql-8.0.11
#设置 mysql数据库的数据的存放目录，MySQL 8+ 不需要以下配置，系统自己生成即可，否则有可能报错
#datadir=C:\\web\\sqldata
#允许最大连接数
max_connections=20
#服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
#创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
#越过密码
skip-grant-tables

修改密码

以管理员身份运行cmd
进入bin目录
连接mysql ：net start mysql
登陆mysql: mysql -u root -p
修改密码：run mysql;
update user set password=PASSWORD(“新密码”) where user=‘root’;

删除ini文件中的最后一句即可

数据类型

数字

日期
在这里插入图片描述
字符串

操作数据库

查看数据库

show databases;

创建数据库

create database 库名；

删除数据库

drop database 库名；

操作数据表

查看数据表信息

use 数据库名;
show tables; #查看库中的所有表名

SHOW COLUMNS FROM 数据表:
显示数据表的属性，属性类型，主键信息，是否为 NULL，默认值等其他信息。

SHOW INDEX FROM 数据表:
显示数据表的详细索引信息，包括PRIMARY KEY（主键）。

SHOW TABLE STATUS LIKE [FROM db_name] [LIKE ‘pattern’] \G:
该命令将输出Mysql数据库管理系统的性能及统计信息。

mysql> SHOW TABLE STATUS  FROM RUNOOB;   # 显示数据库 RUNOOB 中所有表的信息

mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%';     # 表名以runoob开头的表的信息
mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%'\G;   # 加上 \G，查询结果按列打印

创建数据表

use 数据库名；
create table 表名（[colunm1] 类型 (null,defualt可选设)，[column2] 类型，…）ENGINE=InnoDB DEFAULT CHARSET=utf8;

删除数据表

drop table 表名

修改数据表名或者修改数据表字段

ALTER命令
进入数据库
删除
ALTER TABLE 表名 DROP 字段
添加

alter table 表名 add column 字段 属性（例：varchar(20) not null等）;
这一列在表的最后一列位置。

如果我们希望添加在指定的一列，可以用：
alter table t1 add column addr varchar(20) not null after(before) user1;
注意，上面这个命令的意思是说添加addr列到user1这一列后面。

如果想添加到第一列的话，可以用：
alter table t1 add column addr varchar(20) not null first;

修改
ALTER TABLE 表名 MODIFY 字段属性;

修改表名
ALTER TABLE 表名 RENAME TO 新表名;

插入数值

向MySQL数据表插入数据通用的 INSERT INTO SQL语法：

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

编辑数据表内容

查询数值

SELECT column_name,column_name FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]；
查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。

SELECT 命令可以读取一条或者多条记录。
你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据

WHERE 语句

（例：SELECT * from runoob_tbl WHERE runoob_author=’’;）
（like语法 SELECT field1, field2,…fieldN
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = ‘somevalue’；）
在这里插入图片描述
UNION语句 处理重复数据

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
ALL: 可选，返回所有结果集，包含重复数据

ORDER BY语法 排序

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]
使用 ORDER BY 子句将查询数据排序后再返回数据（使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。）

GROUP BY 语法

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

MySQL 的 WHERE 子句的字符串比较是不区分大小写的。你可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。

你可以使用 LIMIT 属性来设定返回的记录数。
你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

更新数据

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

删除数据

DELETE FROM table_name [WHERE Clause]
如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
可以在 WHERE 子句中指定任何条件
可以在单个表中一次性删除记录。

连接(查询)

用JOIN 在两个或多个表中查询数据。
用法：

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

例：
首先存在两个库
pkq库
happy库

#使用INNER JOIN(也可以省略 INNER 使用 JOIN，效果一样)
mysql> SELECT a.height, a.name, b.grade FROM pkq a INNER JOIN happy b ON a.name = b.name;
#此语句等价于sql语句:  SELECT a.height, a.name, b.grade FROM pkq a happy b WHERE a.name = b.name;
#结果
+--------+--------+-------+
| height | name   | grade |
+--------+--------+-------+
|    178 | huahua |    68 |
|  168.3 | Bob    |  99.5 |
|  178.3 | ross   |    80 |
|    154 | min    |    94 |
+--------+--------+-------+
4 rows in set (0.00 sec)

#MySQL LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。
mysql> SELECT a.height, a.name, b.grade FROM pkq a LEFT JOIN happy b ON a.name = b.name;
#结果
+--------+--------+-------+
| height | name   | grade |
+--------+--------+-------+
|  168.3 | Bob    |  99.5 |
|    154 | min    |    94 |
|  178.3 | ross   |    80 |
|    178 | huahua |    68 |
|  163.2 | hehe   |  NULL |
|  158.2 | yang   |  NULL |
|  178.2 | NULL   |  NULL |
|    158 | NULL   |  NULL |
|    187 | NULL   |  NULL |
+--------+--------+-------+

#MySQL RIGHT JOIN 会读取右边数据表的全部数据，即便左边边表无对应数据。
mysql> SELECT a.height, a.name, b.grade FROM pkq a LEFT JOIN happy b ON a.name = b.name;
#结果
+--------+--------+-------+
| height | name   | grade |
+--------+--------+-------+
|    178 | huahua |    68 |
|  168.3 | Bob    |  99.5 |
|  178.3 | ross   |    80 |
|    154 | min    |    94 |
|   NULL | NULL   |  99.5 |
|   NULL | NULL   |  99.5 |
|   NULL | NULL   |  99.5 |
|   NULL | NULL   |    78 |
+--------+--------+-------+

NULL

在查找中where 函数 = 和 != 运算符是不起作用的；
在 MySQL 中，NULL 值与任何其它值的比较（即使是 NULL）永远返回 false，即 NULL = NULL 返回false 。
即where [columnname] =NULL 是无效的
需使用is null 或者is not null

null 表示空值，空值不一定为空，空值不一定等于空字符。

正则表达式

可应用于 REGEXP 操作符中。

格式

select ... from ... where name regexp 'g$'

在这里插入图片描述

事务

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 insert,update,delete 语句

四个原则(ACID)

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

语句

BEGIN 或 START TRANSACTION 显式地开启一个事务；
COMMIT 或 COMMIT WORK 提交事务，并使已对数据库进行的所有修改成为永久性的；
ROLLBACK 或 ROLLBACK WORK 回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier 把事务回滚到标记点；
SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

复制表

复制MySQL的数据表，包括表的结构，索引，默认值等。
1.使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等。
2.复制获取的指令，改变表名。
3.使用 INSERT INTO … SELECT 语句复制内容。
例：

#1.获取代码
mysql> SHOW CREATE TABLE huli;
#结果
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                           |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| huli  | CREATE TABLE `huli` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `weight` float NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
#2.复制粘贴并修改表名
mysql>  CREATE TABLE `pipi` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(20) DEFAULT NULL,
    ->   `weight` float NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

#huli内容
mysql> select * from huli;
+----+--------+--------+
| id | name   | weight |
+----+--------+--------+
|  1 | liuxin |      0 |
|  2 | wangyi |   64.2 |
+----+--------+--------+
#3.复制内容
mysql> insert into pipi (id,name,weight) select id ,name ,weight from huli;

mysql> select * from pipi;
+----+--------+--------+
| id | name   | weight |
+----+--------+--------+
|  1 | liuxin |      0 |
|  2 | wangyi |   64.2 |
+----+--------+--------+
2 rows in set (0.00 sec)

重复数据

设置避免重复数据

1 利用主键
2 添加UNIQUE索引
create table … (id…,name… UNIQUE(id,name));

统计重复数据

例：
统计表中 first_name 和 last_name的重复记录数：
mysql> SELECT COUNT(*) as repetitions, last_name, first_name
    -> FROM person_tbl
    -> GROUP BY last_name, first_name
    -> HAVING repetitions > 1;

以上查询语句将返回 person_tbl 表中重复的记录数。一般情况下，查询重复的值，请执行以下操作：

确定哪一列包含的值可能会重复。
在列选择列表使用COUNT(*)列出的那些列。
在GROUP BY子句中列出的列。
HAVING子句设置重复数大于1。

过滤重复数据

在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据。

mysql> SELECT DISTINCT last_name, first_name
    -> FROM person_tbl;

用 GROUP BY 来读取数据表中不重复的数据：

mysql> SELECT last_name, first_name
    -> FROM person_tbl
    -> GROUP BY (last_name, first_name);

删除重复数据

方法1：

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl  GROUP BY (last_name, first_name, sex);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

方法2：在数据表中添加 INDEX（索引）和 PRIMAY KEY（主键）

mysql> ALTER IGNORE TABLE person_tbl
    -> ADD PRIMARY KEY (last_name, first_name);

索引

索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

创建

CREATE INDEX indexName ON mytable(username(length)); 
#如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

删除

DROP INDEX [indexName] ON mytable;

用ALTER

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)
#该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)
#这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list)
#添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)
#该语句指定了索引为 FULLTEXT ，用于全文索引。

显示索引信息

mysql> SHOW INDEX FROM table_name;

导出数据

将数据表 runoob_tbl 数据导出到 /tmp/runoob.txt 文件中:

mysql> SELECT * FROM runoob_tbl 
    -> INTO OUTFILE '/tmp/runoob.txt';

导出 SQL 格式的数据到指定文件

$ mysqldump -u root -p RUNOOB runoob_tbl > dump.txt
password ******

输出不能是一个已存在的文件。防止文件数据被篡改。

pymysql

PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库
由于mysql 8.0 之后的版本使用密码为caching_sha2_password格式
用mysql_connect 会报错

import pymysql
 
# 打开数据库连接
db = pymysql.connect("连接地址","用户名","密码","打开的数据库名" )
注：最后一项也可改为charset='utf8'
 
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
 
# 使用 execute()  方法执行 SQL 查询 
cursor.execute('')
引号内的语句和mysql的语句用法一样
例：创建新库     create database 库名
 
执行SQL语句方法2：
sql='create database new'
try:
   # 执行sql语句
   cursor.execute(sql)
   # 提交到数据库执行   (插入数据时，此步为必要)
   db.commit()
except:
   # 如果发生错误则回滚
   db.rollback()
   
# 使用 fetchone() 方法获取单条数据.   （查询操作专用）
data = cursor.fetchone()
（fetchone(): 该方法获取下一个查询结果集。结果集是一个对象
fetchall(): 接收全部的返回结果行.
rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数。）
print ("Database version : %s " % data)
 
# 关闭数据库连接
db.close()

可能出现的error

2003: Can’t connect to MySQL server on ‘localhost:3306’
说明你的mysql还没有启动。
解决办法：在服务中，启动mysql服务

NotSupportedError: Authentication plugin ‘caching_sha2_password’ is not supported
原因：使用mysql.connector密码不支持

lrshcbxf

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
小结3 mysql pymysql

文章目录mysql安装初始化修改密码数据类型操作数据库查看数据库创建数据库删除数据库操作数据表查看数据表信息创建数据表删除数据表修改数据表名或者修改数据表字段插入数值编辑数据表内容查询数值WHERE 语句更新数据删除数据连接(查询)NULL正则表达式格式事务四个原则(ACID)语句复制表重复数据设置避免重复数据统计重复数据过滤重复数据删除重复数据索引创建删除用ALTER显示索引信息导出数据pymy...
复制链接

扫一扫