SQL操作指南-优快云博客

本文详细介绍了SQL语言的基础操作，包括创建数据库、数据表，删除数据库、数据表，按条件删除数据行，复制数据库，按条件查询，更新数据表，随机选取数据，按组查询等关键操作。同时，还提供了Python连接MySQL数据库并插入数据的示例代码。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

创建数据库
CREATE DATABASE db_name CHARACTER SET utf8 COLLATE utf8_general_ci;
创建数据表
CREATE TABLE table_name(id int(4) auto_increment primary key, hello varhcahr(20) not null) CHARACTER SET utf8 COLLATE utf8_general_ci;
删除数据库
drop databse db_name
删除数据表
drop table table_name
按条件删除数据行
delete from user where username ='hello
复制数据库
CREATE TABLE mytbl_new LIKE production.mytbl;
INSERT mytbl_new SELECT * FROM production.mytbl;
按条件查询
select * from table_name where col_name = ‘string’ limit 0,6
更新数据表id
1、删除原来主键字段(如id)
alter table table_name drop id;
2、添加主键，自增，放在第一位
alter table table_name add id int(11) primary key auto_increment first;
在数据表中随机选取数据
SELECT * FROM b_user_send_rose_log WHERE id>=(SELECT floor(RAND() * ((SELECT MAX(id) FROM b_user_send_rose_log)-(SELECT MIN(id) FROM b_user_send_rose_log)) + (SELECT MIN(id) FROM b_user_send_rose_log))) ORDER BY id LIMIT 5;
按组查询
select deviceBrand, count(*) from hello_router group by deviceBrand order by count(*) desc;
python连接mysql数据库，并插入数据

import pymysql
conn = pymysql.connect(host='127.0.0.1', user='user', passwd='passwd', db='db_name', port=3306, charset='utf8')
cur = conn.cursor()
for result in results:
    try:
        cur.execute("""insert into table_name(id, ip) values(%s, %s)""", (0, ip_name))
        conn.commit()
    except Exception:
        continue
cur.close()
conn.close()