mysql数据库操作

1. 创建数据库
   CREATE DATABASE db_name CHARACTER SET utf8 COLLATE utf8_general_ci;

2. 创建数据表
   CREATE TABLE table_name(id int(4) auto_increment primary key, hello varhcahr(20) not null) CHARACTER SET utf8 COLLATE utf8_general_ci;

3. 删除数据库
   drop databse db_name

4. 删除数据表
   drop table table_name

5. 按条件删除数据行
   delete from user where username ='hello

6. 复制数据库
   CREATE TABLE mytbl_new LIKE production.mytbl;
   INSERT mytbl_new SELECT * FROM production.mytbl;

7. 按条件查询
   select * from table_name where col_name = ‘string’ limit 0,6

8. 更新数据表id
   1、删除原来主键字段(如id)
   alter table table_name drop id;
   2、添加主键，自增，放在第一位
   alter table table_name add id int(11) primary key auto_increment first;

9. 在数据表中随机选取数据
   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;

10. 按组查询
    select deviceBrand, count(*) from hello_router group by deviceBrand order by count(*) desc;

11. 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()