python——mysql数据库基础

通过终端如何进入／退出mysql

#进入
mysql -u root -p 
退出
quit

create database 数据库名 charset=utf8;
drop database 数据库名;
use 数据库名;
select database();
show databases;
show tables;

创建表

create table students(
id int auto_increment primary key not null,
name varchar(10) not null,
gender bit default 1,
birthday datetime
);
#查看表的信息
desc students;

alter table 表名 add|change|drop 列名 类型；
drop table 表名;
rename table 原表名 to 新表名

数据操作

查询：

select * from 表名;

消除重复

select distinct gender from students;

where

逐个匹配每一行

select * from 表名 where 条件
select * from students where id>3;

增加：
全列插入

insert into 表名 values(0,'黄蓉',1,'1990-1-1',0);

缺省插入：

insert into 表名(列1，...)values(值1...)

条件删除：(物理删除)

delete from 表名 where 条件

同时插入多条数据：

insert into 表名(name) values('xjx'),('bird');

修改

update students set birthday = "1990-2-2" where id = 2;

备份与恢复

sudo -s
cd /var/lib/mysql
mysqldump -uroot -p 数据库 > ~/Desktop/bak.sql

and、or、not

select * from students where id>3 and gender = 1;

模糊查询

like表示任意字符
%:可匹配0或多个字符
_:只能匹配一个字符

select * from students where name like '李%';

范围查询

in表示在一个非连续范围类
between...and...

select * from students where id in(1,3,8);
select * from students where id between 3 and 8;

空判断

null 与''是不同的

select * from students where birthday is null;

优先级

(), not, 比较运算符，逻辑运算符，and比or先运算

聚合函数

count(*)
max(列)
min(列)
sum(列)

select count(*) from students;
select count(*) from students where isdelete=0;
select max(id) from students where gender=0;
select min(id) from students where isdelete=0;

子查询

select * from students where id=(select min(id) from students where isdelete=0);

分组

查询男女生总数

select gender as 性别,count(*)
from students
group by gender;

对groupy by的结果集再进行筛选

select gender as 性别,count(*)
from students
group by gender having count(*)>2;

排序

asc
desc

select * from subject
where isdelete=0
order by id desc;

获取部分行

select * from 表名
limit start, count

分页
已知：每页显示m条数据，当前显示第n页。n从1开始