[python]mysql操作(持续更新中)

neillien

已于 2022-02-21 19:19:18 修改

阅读量261

点赞数

文章标签： mysql python 数据库

于 2022-02-20 14:38:35 首次发布

本文链接：https://blog.csdn.net/neillien/article/details/123030694

版权

Ctrl+f搜关键词，搜不到找其他博客，下面肯定没有

1.alter

alter table 表名 add 列名 数据类型（长度）
alter table 表名 modify 列名 数据类型（长度）
alter table 表名 change 原列名 新列名 数据类型（长度）
alter table 表名 drop 列名

2.其他

insert into 表名[(字段列表)] values (值列表)
update 表名 set 字段1=值1 where 字段=值
delete from 表名 where 字段=值

3.order by

asc升序 desc降序

4.模糊查询

第二个字母是M的员工信息

select * from emp where ename like ‘_M%’

5.表连接查询

join #关键字在表中存在至少一个匹配时返回行

left join #显示左边的全部，即使右边没有

right join #显示右边的全部，即使左边没有

6.pycharm插入操作

import pymysql
conn = pymysql.connect(host='127.0.0.1',user='root',passwd='123',database='dept')
cur = conn.cursor()
cur.execute('create table dep (deptno varchar(20) ,dname varchar(20),loc varchar(20))')
sql = 'insert into dep (deptno,dname,loc) values (%s,%s,%s)'
val = (50,'开发部','北京')
cur.execute(sql,val)   #executemany执行多次，即val有多行数据
conn.commit()
conn.close()

7.pycharm查询操作

result = cur.fetchall()   #获取所有的查询记录结果为元组，可使用for循环

以下本人未全部亲自使用，可作为参考

8.通配符

通配符	描述
%	代表零个或多个字符
_	仅替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist] 或者 [!charlist]	不在字符列中的任何单一字符

9.BETWEEN 操作符

重要事项:不同的数据库对 BETWEEN…AND 操作符的处理方式是有差异的。某些数据库会列出介于"Adams" 和 “Carter” 之间的人，但不包括 “Adams” 和 “Carter” ;某些数据库会列出介于 “Adams” 和 “Carter” 之间并包括 “Adams” 和 “Carter” 的人；而另一些数据库会列出介于 “Adams” 和 “Carter” 之间的人,包括 “Adams” ，但不包括 “Carter” 。

所以，请检查你的数据库是如何处理 BETWEEN…AND 操作符的！

10.Alias 实例: 使用表名称别名

假设我们有两个表分别是：“Persons” 和 “Product_Orders”。我们分别为它们指定别名 “p” 和 “po”。现在，我们希望列出 “John Adams” 的所有定单。我们可以使用下面的 SELECT 语句:

   SELECT po.OrderID,p.LastName, p.FirstName FROM Persons AS p, Product_Orders AS po WHERE 			                                                  		p.LastName='Adams' AND p.FirstName='John'

11.Join

SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name

JOIN: 如果表中有至少一个匹配，则返回行
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配，就返回行

12.UNION

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

13.SQL UNION 语法

SELECT column_name(s) FROM table_name1 UNION

SELECT column_name(s) FROM table_name2

注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

14.SQL UNION ALL 语法

SELECT column_name(s) FROM table_name1 UNION ALL

SELECT column_name(s) FROM table_name2

另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

15.撤销 UNIQUE 约束

MySQL: ALTER TABLE Persons DROP INDEX uc_PersonID

SQL Server / Oracle / MS Access: ALTER TABLE Persons DROP CONSTRAINT uc_PersonID

16.CREATE TABLE 语法

CREATE TABLE 表名称

(

列名称1 数据类型,

列名称2 数据类型,

列名称3 数据类型,

…

)

17.NOT NULL 约束

18.PRIMARY KEY 约束

如果需要命名 PRIMARY KEY 约束，以及为多个列定义 PRIMARY KEY 约束，请使用下面的 SQL 语法：

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons(

Id_P int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)

)

撤销 PRIMARY KEY 约束

如需撤销 PRIMARY KEY 约束，请使用下面的 SQL：

MySQL:

ALTER TABLE Persons DROP PRIMARY KEY

SQL Server / Oracle / MS Access:

ALTER TABLE Persons DROP CONSTRAINT pk_PersonID

20.FOREIGN KEY 约束

如果需要命名 FOREIGN KEY 约束，以及为多个列定义 FOREIGN KEY 约束，请使用下面的 SQL 语法：

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Orders (

Id_O int NOT NULL,

OrderNo int NOT NULL,

Id_P int, PRIMARY KEY (Id_O),

CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)

)

21.撤销 FOREIGN KEY 约束

MySQL: ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders

SQL Server / Oracle / MS Access:

ALTER TABLE Orders DROP CONSTRAINT fk_PerOrders

22.check约束

CHECK 约束用于限制列中的值的范围。

如果对单个列定义 CHECK 约束，那么该列只允许特定的值。

如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。

23.如果需要命名 CHECK 约束，以及为多个列定义 CHECK 约束，请使用下面的 SQL 语法：

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons (

Id_P int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

CONSTRAINT chk_Person CHECK (Id_P>0 AND City=‘Sandnes’)

)

撤销 CHECK 约束

MySQL:

ALTER TABLE Persons DROP CHECK chk_Person

SQL Server / Oracle / MS Access:

ALTER TABLE Persons DROP CONSTRAINT chk_Person

24.DEFAULT 约束

DEFAULT 约束用于向列中插入默认值。

如果没有规定其他的值，那么会将默认值添加到所有的新记录。

25.如果在表已存在的情况下为 “City” 列创建 DEFAULT 约束，请使用下面的 SQL：

MySQL:

ALTER TABLE Persons ALTER City SET DEFAULT ‘SANDNES’

SQL Server / Oracle / MS Access:

ALTER TABLE Persons ALTER COLUMN City SET DEFAULT ‘SANDNES’

26.撤销 DEFAULT 约束

MySQL:

ALTER TABLE Persons ALTER City DROP DEFAULT

SQL Server / Oracle / MS Access:

ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT

27.索引

CREATE INDEX:

CREATE UNIQUE INDEX index_name ON table_name (column_name)

DROP INDEX:

ALTER TABLE table_name DROP INDEX index_name

请使用 TRUNCATE TABLE 命令（仅仅删除表格中的数据）：

TRUNCATE TABLE 表名称

AUTO INCREMENT 字段:

CREATE TABLE Persons (

P_Id int NOT NULL AUTO_INCREMENT,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

PRIMARY KEY (P_Id)

)

28.更新视图

CREATE OR REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition

29.撤销视图

SQL DROP VIEW Syntax DROP VIEW view_name

30.MySQL 数据类型

有三种主要的类型：文本、数字和日期/时间类型。

Text 类型：

数据类型	描述
CHAR(size)	保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字符。
VARCHAR(size)	保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。注释：如果值的长度大于 255，则被转换为 TEXT 类型。
TINYTEXT	存放最大长度为 255 个字符的字符串。
TEXT	存放最大长度为 65,535 个字符的字符串。
BLOB	用于 BLOBs (Binary Large OBjects)。存放最多 65,535 字节的数据。
MEDIUMTEXT	存放最大长度为 16,777,215 个字符的字符串。
MEDIUMBLOB	用于 BLOBs (Binary Large OBjects)。存放最多 16,777,215 字节的数据。
LONGTEXT	存放最大长度为 4,294,967,295 个字符的字符串。
LONGBLOB	用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。
ENUM(x,y,z,etc.)	允许你输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。注释：这些值是按照你输入的顺序存储的。可以按照此格式输入可能的值：ENUM(‘X’,‘Y’,‘Z’)
SET	与 ENUM 类似，SET 最多只能包含 64 个列表项，不过 SET 可存储一个以上的值。

Number 类型：

数据类型	描述
TINYINT(size)	-128 到 127 常规。0 到 255 无符号*。在括号中规定最大位数。
SMALLINT(size)	-32768 到 32767 常规。0 到 65535 无符号*。在括号中规定最大位数。
MEDIUMINT(size)	-8388608 到 8388607 普通。0 to 16777215 无符号*。在括号中规定最大位数。
INT(size)	-2147483648 到 2147483647 常规。0 到 4294967295 无符号*。在括号中规定最大位数。
BIGINT(size)	-9223372036854775808 到 9223372036854775807 常规。0 到 18446744073709551615 无符号*。在括号中规定最大位数。
FLOAT(size,d)	带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DOUBLE(size,d)	带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DECIMAL(size,d)	作为字符串存储的 DOUBLE 类型，允许固定的小数点。

这些整数类型拥有额外的选项 UNSIGNED。通常，整数可以是负数或正数。如果添加 UNSIGNED 属性，那么范围将从 0 开始，而不是某个负数。

Date 类型：

数据类型	描述
DATE()	日期。格式：YYYY-MM-DD 注释：支持的范围是从 ‘1000-01-01’ 到 ‘9999-12-31’
DATETIME()	*日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’
TIMESTAMP()	*时间戳。TIMESTAMP 值使用 Unix 纪元(‘1970-01-01 00:00:00’ UTC) 至今的描述来存储。格式：YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-09 03:14:07’ UTC
TIME()	时间。格式：HH:MM:SS 注释：支持的范围是从 ‘-838:59:59’ 到 ‘838:59:59’
YEAR()	2 位或 4 位格式的年。注释：4 位格式所允许的值：1901 到 2155。2 位格式所允许的值：70 到 69，表示从 1970 到 2069。

即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。

31.MySQL用户管理：添加用户、授权、删除用户

添加用户:

create user zhangsan identified by ‘zhangsan’;

用户名zhangsan，密码张三

授权:

grant all privileges on zhangsanDb.* to zhangsan@’%’ identified by ‘zhangsan’;

flush privileges;

show grants for ‘zhangsan’;

privilegesCode表示授予的权限类型，常用的有以下几种类型: