查看表结构:
MariaDB [mysql]> desc user;
查看表数据,纵向显示
MariaDB [mysql]> select * from user\G;
创建数据库:
MariaDB [(none)]> create database testdb;
创建数据库并指定字符编码
MariaDB [(none)]> create database testdb charset utf8;
创建用户并赋予权限(不包括localhost)
MariaDB [mysql]> grant all on test.* to'charlie'@'%' identified by 'charlie123';
创建用户并赋予权限(包括localhost)
MariaDB [mysql]> grant all on test.* to'charlie'@'localhost' identified by 'charlie123';
刷新立即生效:
flush privileges;
查看授权:
MariaDB [mysql]> show grants for charlie;
查看创建数据库情况:
MariaDB [(none)]> show create database testdb;
删除数据库:
MariaDB [(none)]> drop database testdb;
创建数据库表:
create table student( id int auto_increment, name char(32) not null, age int not null, register_data datenot null, primary key (id));
插入新数据:
MariaDB [testdb]> insert into student (name,age,register_data) values("charlie07",21,"2017-8-11")
边缘值查询:
MariaDB [testdb]> select * from student limit 2 offset 3;
条件判断过滤:
MariaDB [testdb]> select * from student where id > 2;
like模糊查询:
MariaDB [testdb]> select * from student where register_data like"2017-08%";
修改数据:
MariaDB [testdb]> update student set name="CR7",age=27where id=1;
批量修改数据:
MariaDB [testdb]> update student set name="CR7",age=27where id>3;
删除数据:
MariaDB [testdb]> delete from student where name="CR7";
排序(升序):
MariaDB [testdb]> select * from student orderby id asc;
排序(降序):
MariaDB [testdb]> select * from student orderby id desc;
按照名字分组汇总:
MariaDB [testdb]> select name,count(*) from student groupby name;
某个字典的汇总:
MariaDB [testdb]> select name,sum(age) from student;
添加字段:
MariaDB [testdb]> alter table student add sex enum("M","F");
删除字段:
MariaDB [testdb]> alter table student drop age;
修改字段内容不为空:
MariaDB [testdb]> alter table student modify sex enum("F","M") not null;
修改字段以及字段名:
MariaDB [testdb]> alter table student change sex gender char(32) not null default"X";
2.python连接Mariadb数据库:
#!/usr/bin/env python3# this is Charlie scripts!
import pymysql
# 创建连接(相当于创建socket链接)
conn = pymysql.connect(host='192.168.199.148', port=3306, user='root', passwd='1', db='testdb')
# 创建游标(相当于建立socket实例)
cursor = conn.cursor()
# # 执行SQL,并返回收影响行数# effect_row = cursor.execute("select * from student")# print(cursor.fetchall())
data = [
(None,"Ronaldo1","1985-2-5","M"),
(None,"Ronaldo2","1985-2-5","FM"),
(None,"Ronaldo3","1985-2-5","M"),
]
# 默认使用事务的方式插入数据
cursor.executemany("insert into student (id,name,register_data,gender) values(%s,%s,%s,%s)",data)
# 提交
conn.commit()
3.利用orm创建表结构:
#!/usr/bin/env python3# this is Charlie scripts!import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
engine = create_engine("mysql+pymysql://root:1@192.168.199.148/testdb",
encoding='utf-8',echo=True)
Base = declarative_base() # 生成orm基类classUser(Base):
__tablename__ = 'user'# 表名
id = Column(Integer, primary_key=True)
name = Column(String(32))
password = Column(String(64))
Base.metadata.create_all(engine) # 创建表结构