常用的MySQL数据库操作命令

数据库操作

显示数据库：show databases;

查看当前使用的数据库：select database();

创建数据库：
create database 数据库名 character set 字符集 collate 字符集排序规则；
eg：
create table students( id int unsigned primary key auto_increment not null, name varchar(20) default '', age tinyint unsigned default 0, height decimal(5,2), gender enum('男','女','保密') )

约束条件

1. primary key（主键）：物理上存储的顺序
2. not null（非空）：此字段不允许填写空值
3. unique（唯一）：此字段的值不允许重复
4. default（默认）：当不填写此值时会使用默认值，如果填写时以填写为准
5. foreign key（外键）：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常
6. unsigned：不为负
7. auto_increment：自增长

选择数据库：use 数据库名;

查看当前数据库信息：status 或 \s

修改数据库编码格式：alter database 数据库名 character set 编码格式;

直接登录数据库：mysql -D数据库名 -h主机名 -u用户名 -p

删除数据库：drop database 数据库名;

查看数据库版本：select version();

查看时间：select now();

退出数据库：exit 或 quit 或 \q

数据表操作

创建数据表：
create table tableName( column1 datatype contrai, ...... conlumnN datatype, PRIMARY KEY(one or more columns) )character set 字符集;
（字符集可以默认，也可以重新设置）

查看创建的数据表：
show create table 表名; desc 表名;

增加数据表字段：
alter table 表名 add 列名 类型;

使用change修改数据表字段：
alter table 表名 change 原名 新名 类型及约束;

使用modify修改数据表字段：
alter table 表名 modify 列名 类型及约束;
(不能修改列名）

删除数据表字段：
alter table 表名 drop 字段名;

显示字段的全部信息：
show full columns from 表名;

DML数据操作语言

insert 插入语句

向表中插入一行数据：
insert into 表名 values(0,'小明',18);
（字段名是可选的，但值列表和字段名一一对应，多个列表和多个值之间使用逗号分隔）

插入多行数据：
insert into 表名 (字段名） values(值列表1),(值列表2),......,(值列表n);
eg：
insert into students (id,name,age) values(0,'张三',18),(0,'李四',20);

修改数据
update 表名 set 字段1=值1，字段2=值2，...，字段n=字段n[where 条件];
eg: 将所有学生姓名改成张三：
update students set name='张三';
将id是1的学生姓名改成张三，性别改成男：
update students set name='张三',gender=1 where id=1;
（更多列数据用逗号隔开；添加条件限制）

删除数据
delete from 表名 [where 条件];
eg: 删除学生表中的数据：delete from students;
删除id是1的学生数据：delete from students where id=1;
truncate table 表名；
eg: 删除学生表中的数据：truncate table students;

DQL数据查询语言

select基本查询语句
select 列名 from 表名；
eg：查询所有学生信息：select * from student;
查询所有学生的姓名，性别：select name,gender from students;

运算符

1. 算数运算符：+、-、*、/、%
2. 关系运算符：=、>、<、<>、>=、<=、!=
3. 赋值运算符：=
4. 逻辑运算符：and && or || not !

where条件查询
eg：查询id为1的姓名，性别：
select name,gender from students where id=1;
查询学生’邓超‘的信息：
select * from students where name = '邓超';

where多条件查询
select 列名 from 表名 where 条件 运算符 条件
eg：查询id=2或者id=3的学生的姓名，性别：
select name,gender from students where id=1 or id=3;
查询性别为女并且在三班的学生的信息
select * from students where gender='女' and cls_id=3;

like模糊查询
select 列名 from 表名 where 字段 like‘值’；
通配符：
：一个字符 A like 'c’
%：任意长度的字符串 B like ‘CO%’
[]：括号中所指定范围内的一个字符 C like ‘9W0[1-2]’
[^]：不在括号中所指定范围内的一个字符 D like ‘9W[^1-2]’

in关键字查询
select 列名 from 表名 where 字段 in（值1，值2，...);
eg: 查询id为1，2，3，4的学生信息
select * from students where id in (1,5,6,10);

between关键之查询
select 列名 from 表名 where 字段 between（值1，值2，...);
eg：查询id为8-10的学生信息
select * from students where id between 8 and 10;

distinct关键字查询（去重）
select distinct 字段名1，字段名2... from 表名;
eg：查询性别有几种分类：
select distinct gender from students;
查询有几个班级：
select distinct cls_id from studens;

order by关键字查询（排序）
select <字段名列表> from <表名> [where <查询条件>] [order by <排序的列名> [ASC 或DESC] ];
eg：将学生的身高按照升序（降序）排列：
select * from students order by height;
select * from students order by height desc;

limit关键字查询（限制结果集）
select <字段名列表> from <表名> [where <查询条件>] [order by <排序的列名> [ASC 或 DESC] ] [limit <行数>];
eg：只看前3条学生信息：
select * from students limit 3;

连接查询：将多张表中的记录按照指定的跳进进行连接查询方式
多张表需要有主外键关系

1. 内连接：返回连接表中符合连接条件记录的连接查询
   （1）显式内连接：
   select 字段 from 表1 inner join 表2 on 连接条件 where 条件;
   eg：查看学生所在班级
   select s.name,c.name from students s inner join classes c on s.cls_id=c.id

   （2）隐式内连接
   ：select 字段 from 表1，表2 where 表1.条件=表2.条件
   eg：查看学生所在班级
   select s.name as '名字',c.name as '班级' from students s,class c where s.cls_id = c.id;

2. 左外连接：以左表为基表，返回左表中所有记录及连接表中符合条件的记录的外连接
   select 字段 from 表1 left join 表2 on 连接条件 where 条件
   eg：查看老师所在班级
   解 select c.name,t.name from class c left join teacher t on c.t_id = t.id;

3. 右外连接：以右表为基表，返回右表中所有记录及连接表政府和条件的记录的外连接
   select 字段 from 表1 right join 表2 on 连接条件 where 条件
   eg：查看老师所在班级
   select c.name,t.name from class c right join teacher t on c.t_id = t.id;

聚合函数：可以对一组值进行计算，并返回单个值的函数
select 聚合函数<字段> from 表名 [where 条件] [group by 聚合函数]

1. count()总数
2. sum()求和
3. avg()平均值
4. max()最大值
5. min()最小值
6. group_concat(col)由一组的列值连接组合成的结果

eg：查询班级学生的平均身高
select avg(height) as '平均身高' from students;
查询班级有多少同学
select count(*) from students;

子查询：在一个查询的内部包括另一个查询

简单子查询：
eg：查看刘德华同学的所在班级的所有同学
select * from students where cls_id = (select cls_id from students where name = "刘德华");

any/some子查询
any和some用法相同，就是返回子查询结果的任意一个值
eg：查看学生所在班级
select * from students where cls_id = any[some](select id from class where t_id = select id from teacher where name = "赵老师"));

all子查询
外侧中的查询结果要大于等于子查询结果的所有值
eg：查看学生所在班级
select * from students where cls_id >= all (select id from class where t_id = (select id from teachers where name = "赵老师"));

in子查询
子查询返回多行数据时可以使用in关键字查询包含在内的数据
eg：查看id是1，5，6，10的学生信息
select * from students where id in (1,5,6,10);

not in子查询
子查询返回多行数据时可以使用not in关键字查询不包含在内的数据
eg：查看id不是1，5，6，10的学生信息
select * from students where id not in (1,5,6,10);

exists子查询
eg：删除表
drop table if exists temp;
查看王老师所在的班级
select * from class where exits (select * from teacher where name='王');

not exists子查询
eg：创建教师表
create table if Not Exists teachets( );
查看王老师所在的班级
select * from class where not exists (select * from teacher where name='王老师');

Python连接MySQL

pymysql常见类和方法
参数 类型 描述
host str mysql服务器地址，IP地址或域名
port int MySQL服务器端口号
user str 用户名
passwd str 密码
db str 数据库名称
charset str 数据库连接编码

connect()对象支持的方法
cursor()：创建游标并返回游标对象
commit()：提交当前事务
rollback()：回滚当前事务
close()：关闭连接

游标是实现对表中数据进行相关操作的对象

cursor()对象支持的方法（执行sql语句并获得结果）
execute(op)：执行一个SQL命令
fetchone()：取得结果集的一行数据
fechmany(size)：取得结果集的size行数据
fetchall()：获取结果集中的所有数据
rowcount()：返回数据条数或影响行数
close()：关闭游标对象