pythonMySQL相关

MySQL

一、安装

  1. 下载完成后，在MySQL中添加my.ini文件,添加下列内容

    点击查看代码 
    [mysqld]
 ​
 # port
 port=X
 ​
 # set basedir to your installation path
 basedir=E:\\mysql-5.7.31-winx64
 ​
 # set datadir to the location of your data directory
 datadir=E:\mysql-5.7.31-winx64\data

  2. 初始化

    "E:\mysql-5.7.31-winx64\bin\mysqld.exe"  --initialize-insecure

    生成了data文件夹

  3. 自定义服务

    "E:\mysql-5.7.31-winx64\bin\mysqld.exe" --install mysql57
    net start mysql57  //开启服务
 net stop mysql57  //关闭服务
    "E:\mysql-5.7.31-winx64\bin\mysqld.exe" --remove mysql57

    删除自定义服务

  4. 加入环境变量

  5. 忘记或修改密码

    • 在my.ini文件中添加节点skip-grant-tables=1

    • 重启MySQL服务

    • 执行

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

    • 删除节点skip-grant-tables=1

二、客户端使用MySQl

1. 库

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

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

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

  • 进入数据（进入文件）：use 数据库;

2. 表

  • 查看当前所有表：show tables;

  • 创建表：create table 表名(字段)default charset=utf8;

  • 删除表 drop table 表名;

  • 清空表 delete from 表名; 或 truncate table 表名;（速度快、无法回滚撤销等）

  • 修改

    • 添加字段

      alter table 表名 add 列名 类型 not null default 默认值;
 alter table 表名 add 列名 类型 not null primary key auto_increment;

    • 删除字段

      alter table 表名 drop column 列名;

    • 修改字段类型

      alter table 表名 modify column 列名 类型;

    • 修改字段类型 名称

      alter table 表名 change 原列名 新列名 新类型;

    • 修改默认值

      ALTER TABLE 表名 ALTER 列名 SET DEFAULT 1000;
ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;

    • 添加主键

      alter table 表名 add primary key(列名);

    • 删除主键

      alter table 表名 drop primary key;

  • 列类型

    • int[(m)][unsigned][zerofill]

      int				表示有符号，取值范围：-2147483648 ～ 2147483647
int unsigned	表示无符号，取值范围：0 ～ 4294967295
int(5)zerofill	仅用于显示，当不满足5位时，按照左边补0，例如：00002；满足时，正常显示。

    • tinyint[(m)] [unsigned] [zerofill]

      有符号，取值范围：-128 ～ 127.
无符号，取值范围：0 ～ 255

    • bigint[(m)][unsigned][zerofill]

      有符号，取值范围：-9223372036854775808 ～ 9223372036854775807
无符号，取值范围：0  ～  18446744073709551615

    • FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

      单精度浮点数，非准确小数值，m是数字总个数，d是小数点后个数。

    • DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

      双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。

    • char(m) 定长字符串，m代表字符串的长度，最多可容纳255个字符。

    • varchar(m)变长字符串，m代表字符串的长度，最多可容纳65535个字节。

    • text text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。

    • datetime

      YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59）

    • timestamp

      YYYY-MM-DD HH:MM:SS（1970-01-01 00:00:00/2037年）

    • date

      YYYY-MM-DD（1000-01-01/9999-12-31）

    • time

      HH:MM:SS（'-838:59:59'/'838:59:59'）

3. 表中数据

  • 增

    insert into 表名 (列名,列名,列名) values(对应列的值,对应列的值,对应列的值);

  • 删

    delete from 表名;
delete from 表名 where 条件;

  • 改

    update 表名 set 列名=值;
update 表名 set 列名=值 where 条件;

  • 查

    select * from 表名;
select 列名,列名,列名 from 表名;
select 列名,列名 as 别名,列名 from 表名;
select * from 表名 where 条件;

  • sql注入

    select * from users where name='' or 1=1 -- ' and password='123'

  • between and

    select * from info where id between 2 and 4;   -- id大于等于2、且小于等于4

  • in not in

    select * from info where id in (select id from depart);

  • exists

    select * from info where not exists (select * from depart where id=5);

  • 通配符

    like

    • % 表示任意 0 个或多个字符。

    • _表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句。

    • []表示括号内所列字符中的一个（类似正则表达式）

    • [^] ：表示不在括号所列之内的单个字符。

    • 查询内容包含通配符时,由于通配符的缘故，导致我们查询特殊字符 “%”、“_”、“[” 的语句无法正常实现，而把特殊字符用 “[ ]” 括起便可正常查询。

    • MySQL 中使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式。

  • 条件选择

    case when else end

    select 
	id,
	name,
	case depart_id when 1 then "第1部门" end v1,
	case depart_id when 1 then "第1部门" else "其他" end v2,
	case depart_id when 1 then "第1部门" when 2 then "第2部门" else "其他" end v3,
	case when age<18 then "少年" end v4,
	case when age<18 then "少年" else "油腻男" end v5,
	case when age<18 then "少年" when age<30 then "青年" else "油腻男" end v6
from info;

  • 排序

    order by desc   --倒序
order by asc   	--正序

  • 取部分

    select * from info limit 5;   		-- 获取前5条数据
select * from info limit 3 offset 2;-- 从位置2开始，向后获取前3数据

  • 分组

    select age,count(id) from info where id > 2 group by age having count(id) > 1 order by age desc limit 1;
- 要查询的表info
- 条件 id>2
- 根据age分组
- 对分组后的数据再根据聚合条件过滤 count(id)>1
- 根据age从大到小排序
- 获取第1条

  • 左右联表

    主表 left outer join 从表 on 主表.x = 从表.id 
从表 right outer join 主表 on 主表.x = 从表.id

  • 联合

    • union

    • union all

4. 授权

1. 用户管理

  • 创建用户

    create user '用户名'@'连接者的IP地址' identified by '密码';

  • 修改用户

    rename user '用户名'@'IP地址' to '新用户名'@'IP地址';

  • 修改密码

    set password for '用户名'@'IP地址' = Password('新密码')

2. 授权管理

  • 授权

    grant 权限 on 数据库.表 to   '用户'@'IP地址'

  • 查看授权

    show grants for '用户'@'IP地址'

  • 取消授权

    revoke 权限 on 数据库.表 from '用户'@'IP地址'

5. 索引

6. 函数

  • 创建函数

    delimiter $$
create function f1(
    i1 int,
    i2 int)
returns int
BEGIN
    declare num int;
    declare maxId int;
    select max(id) from big into maxId;
    
    set num = i1 + i2 + maxId;
    return(num);
END $$
delimiter ;

     

  • 执行函数

    select f1(11,22);

select f1(11,id),name from d1;

     

  • 删除函数

    drop function f1;

     

7. 存储过程

  • 创建

    delimiter $$
create procedure p1()
BEGIN
    select * from d1;
END $$
delimiter ;

  • 执行

    call p1()

     

  • 删除

    drop procedure proc_name;

     

8. 视图

  • 创建视图

    create view v1 as select id,name from d1 where id > 1;

     

  • 使用视图

    select * from v1

     

  • 删除视图

    drop view v1

     

  • 修改视图

    alter view v1 as SQL语句

     

9. 触发器

  • 创建

    CREATE TRIGGER 触发器名称 AFTER(before) DELETE(UPDATE  INSERT) ON 表名 FOR EACH ROW
BEGIN
    ...
END

  • 删除

    DROP TRIGGER 触发器名称;

  • 示例

    delimiter $$
CREATE TRIGGER tri_before_insert_t1 BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
	-- NEW.id  NEW.name  NEW.email
	-- INSERT INTO t2 (name) VALUES();
	IF NEW.name = 'alex' THEN
        INSERT INTO t2 (name) VALUES(NEW.id);
    END IF;

END $$
delimiter ;

三、python使用MySQL

1. 安装第三方模块

pip3 install pymysql

2. 使用

  • 操作数据库

    点击查看代码 
    import pymysql
 ​
 # 连接MySQL（socket）
 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8")
 cursor = conn.cursor()
 ​
 # 1. 查看数据库
 # 发送指令
 cursor.execute("show databases")
 # 获取指令的结果
 result = cursor.fetchall()
 print(result) # (('information_schema',), ('mysql',), ('performance_schema',), ('sys',))
 ​
 # 2. 创建数据库（新增、删除、修改）
 # 发送指令
 cursor.execute("create database db3 default charset utf8 collate utf8_general_ci")
 conn.commit()
 ​
 # 3. 查看数据库
 # 发送指令
 cursor.execute("show databases")
 # 获取指令的结果
 result = cursor.fetchall()
 print(result) # (('information_schema',), ('db3',), ('mysql',), ('performance_schema',), ('sys',))
 ​
 # 4. 删除数据库
 # 发送指令
 cursor.execute("drop database db3")
 conn.commit()
 ​
 # 3. 查看数据库
 # 发送指令
 cursor.execute("show databases")
 # 获取指令的结果
 result = cursor.fetchall()
 print(result) # (('information_schema',), ('mysql',), ('performance_schema',), ('sys',))
 ​
 # 5. 进入数据库，查看表
 # 发送指令
 cursor.execute("use mysql")
 cursor.execute("show tables")
 result = cursor.fetchall()
 print(result) # (('columns_priv',), ('db',), ('engine_cost',), ('event',), ('func',), ('general_log',),....
 ​
 # 关闭连接
 cursor.close()
 conn.close()

     

  • 操作数据表

    点击查看代码 
    import pymysql
 ​
 # 连接MySQL
 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8")
 cursor = conn.cursor()
 ​
 # 1. 创建数据库
 """
 cursor.execute("create database db4 default charset utf8 collate utf8_general_ci")
 conn.commit()
 """
 ​
 # 2. 进入数据库、查看数据表
 """
 cursor.execute("use db4")
 cursor.execute("show tables")
 result = cursor.fetchall()
 print(result)
 """
 ​
 # 3. 进入数据库创建表
 cursor.execute("use db4")
 sql = """
 create table L4(
     id int not null primary key auto_increment,
     title varchar(128),
     content text,
     ctime datetime
 )default charset=utf8;
 """
 cursor.execute(sql)
 conn.commit()
 ​
 # 4. 查看数据库中的表
 """
 cursor.execute("show tables")
 result = cursor.fetchall()
 print(result)
 """
 ​
 # 5. 其他 drop table... 略过
 ​
 ​
 # 关闭连接
 cursor.close()
 conn.close()
posted @ 2021-09-10 16:07  wq512  阅读(34)  评论(0)  编辑  收藏  举报