1、基础知识
大小写不敏感
以分号结束一句sql语句
单行注释:
1.-- (后面有一个空格)
2.#
多行注释:
/* */
2、基础语法
查看数据库:
show databases;
使用数据库:
use 数据库名字;
创建数据库:
create database 数据库名 charset UTF8;
删除数据库:
drop 数据库名
查看当前使用的数据库:
select database();
查看有哪些表:
use world;
show tables;
创建表:
use world;
create table student(
id int,
name varchar(30),
age int
);
删除表:
drop table student;
数据插入
use world;
create table student(
id int,
name vachar(10),
age int
);
insert into student(id) values(1),(2),(3);
insert into student(id,name,age) values(4,'肖',19),(5,'钧',20),(6,'文',21);
条件删除(不带条件默认为全部删除)
delete from student where id>5;
更新内容(不带条件默认为全部更新)
update student set age=98 where age=11;
数据查询
select Name,id from city;
*代表全部的列
select * from city where CountryCode ='CHN';
计算语法:
select 聚合函数 from 表 [where 条件] group by 列
select District, sum(population) from city where CountryCode='chn' group by District;
sum求和,avg求平均值,min,max,count(列)求数量
排序:
select * from city where Population >1000000 and CountryCode ='chn' order by Population asc;
desc降序,asc升序(默认)
结果分页限制:
select * from student limit 5;
#显示五条
select *from student limet 10,5;
#跳过前十条,从第十一条开始显示五条。
3、通过pycharm管理数据库
from pymysql import Connection
conn=Connection(
host='localhost',
port=3306,
user='root',
password='123456'
)
# mysql数据库服务器的版本
# print(conn.get_server_info())
cursor=conn.cursor()
conn.select_db('test') #选择数据库
cursor.execute("create table test_pymysql(id int);")
conn.close()
from pymysql import Connection
conn=Connection(
host='localhost',
port=3306,
user='root',
password='123456'
)
# mysql数据库服务器的版本
# print(conn.get_server_info())
cursor=conn.cursor()
conn.select_db('world') #选择数据库
cursor.execute("select * from city where countrycode='chn';")
result=cursor.fetchall()
for r in result:
print(r)
conn.close()
from pymysql import Connection
conn=Connection(
host='localhost',
port=3306,
user='root',
password='123456',
autocommit=True
)
# mysql数据库服务器的版本
# print(conn.get_server_info())
cursor=conn.cursor()
conn.select_db('test') #选择数据库
# cursor.execute("select * from city where countrycode='chn';")
# result=cursor.fetchall()
# for r in result:
# print(r)
cursor.execute('insert into test_pymysql values(3),(2);')
conn.commit()
# 确认后修改才生效,或者用autocommit=True
conn.close()