安装相关命令
设置密码:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
查看已有文件夹:
show databases;
删除表中数据:
DELETE FROM students
WHERE xxx = xxx;
关闭连接:
exit;
开始服务:
net start mysql8
启动:
mysql -u root -p
root123
操作命令
创建数据库:create database design DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
显示数据库:show databases;
创建表
create table user(
id int not null auto_increment primary key,
user_name varchar(64) not null,
user_comment text not null)
default charset=utf8;
进入数据库:
use xueqiu;
显示表:
show tables;
显示数据:
select * from app_1_userinfo;
运行:
python manage.py makemigrations
python manage.py migrate
修改列的类型:
ALTER TABLE 表名 MODIFY 列名 类型;
允许列为空/非空:
ALTER TABLE 表名 MODIFY COLUMN 列名 数据类型 NULL;
ALTER TABLE 表名 MODIFY COLUMN 列名 数据类型 NOT NULL;
插入:
INSERT INTO app_1_task (title, detail)
VALUES ("修改功能", "修改表中的数据");
删除表的数据,重设自增ID为1:
truncate storeserver_storepoint;
truncate storeserver_userinfo;
遇到的问题:
1366, "Incorrect string value: '\\xF0\\x9F\\x90\\xB6\\xE3\\x80...' for column
修改数据库字符集:将数据库的字符集修改为支持存储 emoji 和特殊字符的字符集,比如 utf8mb4。这可以通过修改数据库配置文件或执行 ALTER DATABASE 命令来实现。
ALTER DATABASE your_database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
结合python使用
输入数据:
import pymysql
while True:
user = input("用户:")
if user.upper() == 'Q':
break
password = input("密码:")
mobile = input("电话:")
# 链接mysql
conn = pymysql.connect(host="localhost", port=3306, user="root", password="root123",
charset='utf8', db='design')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 2. 发送指令
sql = "insert into admin(username,password,mobile) values (%s,%s,%s)"
cursor.execute(sql,[user,password,mobile])
conn.commit()
# 3. 关闭链接
cursor.close()
conn.close()
查询数据:
import pymysql
# 链接mysql
conn = pymysql.connect(host="localhost", port=3306, user="root", password="", charset='utf8', db='bruce')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 2. 发送指令 (千万不能用字符串格式化去做SQL的拼接,有安全隐患,SQL的注入风险),可用下面的两种方法方法
# 第二种
cursor.execute("select * from admin where id > 2")
data_list = cursor.fetchall()
for row_dict in data_list:
print(row_dict)
、
删除数据:
import pymysql
# 链接mysql
conn = pymysql.connect(host="localhost", port=3306, user="root", password="", charset='utf8', db='bruce')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 2. 发送指令 (千万不能用字符串格式化去做SQL的拼接,有安全隐患,SQL的注入风险),可用下面的两种方法方法
# 第二种
cursor.execute("delete from admin where id=%s or id=%s", [4,9])
conn.commit()
# 3. 关闭链接
cursor.close()
conn.close()
修改数据:插入数据使用cursor.execute
# 链接mysql
conn = pymysql.connect(host="localhost", port=3306, user="root", password="root123",
charset='utf8',
db='design')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 2. 发送指令
sql = "insert into admin(username,password,mobile) values (%s,%s,%s)"
cursor.execute(sql, [user_id, user_name, user_comment])
conn.commit()
# 3. 关闭链接
cursor.close()
conn.close()
return "success"
查看数据:
@app.route("/show", methods=["GET", "POST"])
def show_user():
# 链接mysql
conn = pymysql.connect(host="localhost", port=3306, user="root", password="root123", charset='utf8',
db='design')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 2. 发送指令
sql = "select * from admin"
cursor.execute(sql)
data_list = cursor.fetchall()
# 3. 关闭链接
cursor.close()
conn.close()
print(data_list)
return render_template('show.html')