数据行-增删改查

数据行-增删改查

数据行操作相关的SQL语句

• 插入数据

insert into 表名（列名，列名，列名） values （数据（对应列的值），数据（对应列的值），数据（对应列的值））；

mysql> create table tb1(
    -> name varchar(11),
    -> password varchar(16)
    -> )default charset=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into tb1(name,password) values ('yujinlong',20020115);
Query OK, 1 row affected (0.01 sec)

mysql> insert into tb1 values ('yu',1232);
Query OK, 1 row affected (0.01 sec)

• 删除数数据

delete from 表名
delete from 表名 where 条件

mysql> select * from tb1;
+-----------+----------+
| name      | password |
+-----------+----------+
| yujinlong | 20020115 |
| yu        | 1232     |
| yujin     | 466      |
| yujinl    | l466     |
+-----------+----------+
4 rows in set (0.00 sec)
-- 调价删除
mysql> delete from tb1 where password='1232';
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb1;
+-----------+----------+
| name      | password |
+-----------+----------+
| yujinlong | 20020115 |
| yujin     | 466      |
| yujinl    | l466     |
+-----------+----------+
3 rows in set (0.00 sec)
清空表
mysql> delete from tb1;
Query OK, 3 rows affected (0.01 sec)

mysql> select * from tb1;
Empty set (0.00 sec)
-- 多个条件进行筛选
mysql> delete from tb1 where name='y' and password='y';
Query OK, 1 row affected (0.01 sec)
-- 也可以使用范围删除
mysql> delete from tb1 where id>9;
Query OK, 1 row affected (0.01 sec)

• 修改数据

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

mysql> update tb1 set name='along';
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from tb1;;
+-------+----------+
| name  | password |
+-------+----------+
| along | l466     |
| along | 1232     |
+-------+----------+
2 rows in set (0.00 sec)
-- 出现这个错误原因是因为我在语句结束的时候输入了两个分号；
ERROR:
No query specified
-- 可以通过具体跳进进行修改
mysql> update tb1 set name='yujinlong' where password='1232';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tb1;
+-----------+----------+
| name      | password |
+-----------+----------+
| along     | l466     |
| yujinlong | 1232     |
+-----------+----------+
2 rows in set (0.00 sec)

-- 也可以对数据进行拼接后在进行更新
mysql> update tb1 set name=concat(name,'yu') where password='l466';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tb1;
+-----------+----------+
| name      | password |
+-----------+----------+
| alongyu   | l466     |
| yujinlong | 1232     |
+-----------+----------+
2 rows in set (0.00 sec)

• 查看数据

select * from 表名 查询表中所有列的数据
select 列名，列名，列名 from 表名；
select 列名，列名 as 别名，别名 from 表名
select * from 表名 where 条件

mysql> select * from tb1 where name='yujinlong';
+-----------+----------+
| name      | password |
+-----------+----------+
| yujinlong | 1232     |
+-----------+----------+
1 row in set (0.00 sec)

mysql> select name as '姓名' from tb1;
+-----------+
| 姓名      |
+-----------+
| alongyu   |
| yujinlong |
+-----------+
2 rows in set (0.00 sec)

-- 也可以增加一个列，让其显示固定的值
mysql> select *,1 from tb1;
+-----------+----------+---+
| name      | password | 1 |
+-----------+----------+---+
| alongyu   | l466     | 1 |
| yujinlong | 1232     | 1 |
+-----------+----------+---+
2 rows in set (0.00 sec)

使用python对数据进行数据行的操作

用一个小小的登录系统进行一个数据库增删改查的运用

模拟实现一个用户管理系统

在开始之前我们先使用自带的客户端也就命令行创建数据库和数据表

-- 创建数据库
create database user default chatset=utf8 collate  utf8_general_ci 

-- 创建数据表
create table users(
id int(11) not null auto_increment primary key,
name varchar(16) not null,
password varchar(32) not null
)default charset=utf8;

在程序汇总编写实现登录、注册、删除、更改密码等功能

# -*- coding: UTF-8 -*-
"""
@Project ：pythonProject 
@File    ：数据行—增删改查.py
@IDE     ：PyCharm 
@Author  ：于金龙@阿龙的代码在报错
@Date    ：2024/8/27 上午11:49 
"""
import pymysql

conn = pymysql.connect(host='localhost',
                       port=3306,
                       user='root',
                       passwd="20020115",
                       db='user',
                       charset='utf8')
cursor = conn.cursor()


def register():
    user = input("输入用户名")
    password = input("输入密码")
    cursor.execute('select * from users where name=%s', (user,))
    result = cursor.fetchone()
    if result:
        print('用户名重复')
    else:
        cursor.execute('insert into users (name,password) values (%s,%s)', (user, password))
        conn.commit()
        print('注册成功')


def remove():
    del_use = input('输入用户名进行删除')
    del_password = input("输入密码")
    cursor.execute('select * from users where name=%s and password%s', (del_use, del_password))
    result = cursor.fetchall()
    if result:
        cursor.execute('delete from users where name=%s', (del_use,))
        conn.commit()
        print("删除成功")
    else:
        print('删除失败，密码错误')


def update():
    update_use = input('输入要修改密码的用户名：')
    old_password = input('输入原来的密码')
    cursor.execute("select * from users where name=%s and password=%s", (update_use, old_password))
    result = cursor.fetchone()
    if result:
        new_password = input("输入新的密码")
        cursor.execute('update users set password=%s where name=%s', (new_password, update_use))
    else:
        print('账号密码错误，更改密码失败')


def log_on():
    name = input('输入用户名')
    password = input('输入密码')
    cursor.execute('select name,password from users where name=%s and password=%s', (name, password))
    user = cursor.fetchall()
    if user:
        print('登录成功')
    else:
        print('密码或账号不匹配')


def run():
    while True:
        a = input("1:登录  2：注册 3：更改密码 4：注销账户 q:退出 \n请选择")
        if a == '1':
            log_on()
            break
        elif a == '2':
            register()
        elif a == '3':
            update()
        elif a == '4':
            remove()
        elif a.upper() == 'Q':
            break
        else:
            print('其他功能敬请期待')
            continue


if __name__ == '__main__':
    run()

fetchall()：是获取在数据库中可以查询到的所有数据

fetchone():获取数据中可以查询到的第一条数据