mySQL常见命令

基础命令及实例

#打开mysql 环境：
mysql -uroot -p 
#退出

exit 

-- sql 不区分大小写，使用;结尾

-- 查看数据库
show databases;
-- 使用库
use world;
-- 查看当前使用的库
select database();
-- 创建database
create database test charset utf8;

show databases;

drop database test;
-- 查看表
show tables;
-- 创建表
create  table student(
    id int,
    name varchar(10),-- 10为长度限制
    age int 
);
-- 删除表
drop table student ;

-- DML
-- 插入数据
insert into student (id) values (1),(2),(3)

insert into student (id,name,age) values (4,'周杰伦',31),(5,'林俊杰',33);

-- 删除数据
delete from student where id >3;
delete from student ; -- 全部删除

-- 更改数据
update student set name = '张学友' where id = 1;

-- DQL
-- 数据查询
select id ,name from student ;

select * from student;

select * from student where age > 20;

-- 分组聚合sum avg min max count 
select gender,avg(age) from student group by gender order by age asc;

-- 排序 asc | desc 

-- 取五条
select  * from student  limit 5;
-- 从第十条开始去
select  * from student  limit 10,5;
#

使用python 操作mysql

基础操作

from pymysql import Connection

# 构建连接
conn = Connection(
    host='localhost',
    port = 3306,
    user = 'root',
    password='123456'

)
# 打印成功，连接成功
print(conn.get_server_info())

# 获取游标对象
cursor = conn.cursor()
# 选择数据库
conn.select_db("world")
# 执行sql
# cursor.execute("create table test_pymsql(id int);")
cursor.execute("select * from student")
result = cursor.fetchall()

print(result)
for i in result:
    print(i)

# 关闭连接
conn.close()

数据插入

手动确认
 

cursor.execute("insert into student values(6,'小明'，100)")
conn.commit()

自动确认

conn = Connection(
    host='localhost',
    port = 3306,
    user = 'root',
    password='123456'
    autocommit = True
)