数据库的简单操作

Mysql-front 连接数据库出现问题#1251   
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;  
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'wei123';  
FLUSH PRIVILEGES;
查看数据库的端口  
show global variables like 'port';   

mysql -u root -p     
create database wei;  
drop database wei;  
show databases;  
use wei;   
create table student(
    id int not null auto_increment,  
    name char(32),  
    age int,  
    enroll_date date,  
    primary key(id)
);  
drop table student;  
show tables;  

insert into student(name,age,enroll_date)values('陈颢',54,'2008-9-01');
insert into student(name,age,enroll_date)values('晁雅熙',27,'2014-9-01');
insert into student(name,age,enroll_date)values('王淑艳',25,'2015-9-01');
insert into student(name,age,enroll_date)values('魏家强',18,'2016-9-01');
insert into student(name,age,enroll_date)values('高志远',23,'2017-9-01');
insert into student(name,age,enroll_date)values('崔晓娟',23,'2017-9-01');   

select column_name,column_name from table_name [where clause]  
 [offset M] [limit N]   
#示例  
select * from student where name = '魏家强';  
select * from student where age > 23;  
select * from student where age < 25 and id > 3 and name = '魏家强';  
select * from student where name = '魏家强' or name = '高志远'; 

update table_name set field1 = new-value1,field2 = new-value2 [where clause]  
#示例：  
update student set age = 25 where id = 1;  
update student set name = '李培海',age = 19 where id = 1;  

delete from table_name[where_clause]  
delete from student where name = '李培海';  

#添加列  
alter table student add address varchar(60) after age;  
#修改列  
alter table student change address addr char(60);  
#删除列  
alter table student drop addr;  
#重命名表  
alter table student rename students;  
#清空数据后让id 从1开始自增  
truncate table students;  
#清空数据后不改变自增结果，行删除  
delete from students;  

connection = pymysql.connect(host='localhost',user='root',  
passwd='',db='',port=3306,charset='utf8')  
cursor = connection.cursor()  
execute()  
executemany()  
close()  
fetchone()  
fetchmany()  
fetchall()

cursor.execute('drop table if exists student')

sql = '''create table student(
id int not null auto_increment,
name char(32),
age int,
enroll_date date,
primary key(id));'''
cursor.execute(sql)

sql = '''  
insert into student(name,age,enroll_date)values('陈颢',54,'2008-9-01');
insert into student(name,age,enroll_date)values('晁雅熙',27,'2014-9-01');
insert into student(name,age,enroll_date)values('王淑艳',25,'2015-9-01');
insert into student(name,age,enroll_date)values('魏家强',18,'2016-9-01');
insert into student(name,age,enroll_date)values('高志远',23,'2017-9-01');
insert into student(name,age,enroll_date)values('崔晓娟',23,'2017-9-01');
'''  
try:  
    cursor.execute(sql) 
    conn.commit()#将操作结果提交到数据库  
except:  
    conn.rollback()#如果操作失败将会回滚到原来的位置  
conn.close()#关闭数据库的操作   
#最好是进行如下操作，操作完必须commit 操作  
cursor.execute('insert into student(name,age,enroll_date)values(%s,%s,%s)',('魏家强',18,'2016-9-01'))
cursor.executemany('insert into student(name,age,enroll_date)values(%s,%s,%s)',[('魏家强',18,'2016-9-01'),('陈颢',54,'2008-9-01')])
connection.commit()
connection.rollback()#出现错误可以回滚

use wei #没有将创建数据库名称
show dbs #显示所有萼数据库，没有数据将不会显示
db.dropDatabase()#删除当前的数据库
#数据的插入
db.wei.insert({'url':'http://www.123.com'})#插入数据
#数据的查询
db.pyhton.find({'likes':100}).pretty()#等于
db.python.find({"likes":{$lt:100}}).pretty()#小于
db.python.find({"likes":{$lte:100}}).pretty()#小于或等于
db.python.find({"likes":{$gt:100}}).pretty()#大于
db.python.find({"likes":{$gte:100}}).pretty()#大于或等于
db.python.find({"likes":{$ne:100}}).pretty()#不等于
db.python.find({"likes":{$gte:100},"title":"python"}).pretty()#组合条件的查询
db.python.find({$or:[{"likes":{$gte:100}},{"title":"python"}]}).pretty()#关键字or以及配合and的使用
db.python.update({'title':'pyhton'},{$set:{'title':'python'}},{multi:true})#修改多条文档
db.python.remove({'title':'MongoDB'})#删除文档
python 操作MongoDB
##建立连接
client = pymongo.MongoClient()
client = pymongo.MongoClient('localhost',27017)
client =pymongo.MongoClient('mongodb://localhost:27017')
#获取数据库
mydb = client['mydb']
blogs = mydb['blogs']
#插入文档,也可以插入一个可以迭代的文档
item = {'my':'your'}
blogs.insert(item)
#查询文档
blogs.find_one({'':''}).count()#多少条文件符合条件
#修改文档
blogs.update({'':''},{"$set":{"":""}})
#删除文档
blogs.remove({'':''})