MySQL笔记（pymysql）

一.cmd登录

mysql -u root -p
password:123456

二.数据库操作

查看当前所有数据库：show databases;
创建数据库:  create database 数据库名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
删除数据库:  drop database 数据库名;
进入数据库:  use 数据库名;
查看数据表： show tables;

三.数据表操作

1.创建表结构

create table  表名(
         列名  类型,
         列名  类型,
         列名  类型
)default charset=utf8;

create table cgynb(
                                                                                      --主键（不能为空，也不能重复）
         id int  not null auto_increment  primary key,  --不允许为空 & 主键 & 自增
         name  varchar(10) not null,     --不允许为空
         email varchar(10) null,   --允许为空(默认)
         age int default 3   --插入数据时，如果不给age列设置值，默认值：3
)default charset=utf8;

2.删除表

drop table 表名;

3.清空表

delete from 表名;
truncate table 表名;  （速度快，但是无法回滚撤销等）

4.修改表

1.添加列

alter table 表名 add 列名 类型;
alter table 表名 add 列名 类型 default 默认值;
alter table 表名 add 列名 类型 not null default 默认值;
alter table 表名 add 列名 类型 not null primary key;

2.删除列

alter table 表名 drop column 列名;

3.修改列

（修改类型）
alter table 表名 modify column 列名 类型;
（修改类型+名称）
alter table 表名 change 原列名 新列名 新类型;
eg.alter table tb change id iidd varchar(9) not null primary key auto_increment;
（修改列默认值）
alter table 表名 alter 列名 set default 1000;
（删除列默认值）
alter table 表名 alter 列名 drop default;
（添加主键）
alter table 表名 add primary key;
（删除主键）
alter table 表名 drop primary key;

4.修改行

（新增数据）
insert into 表名(列名，列名，，，) values(对应的值，对应的值，，，);
insert into 表名(列名) values(对应的值)，(对应的值),,,;
（删除数据）
delete from 表名;
delete from 表名 where 条件;
（修改数据）
update 表名 set 列名=值;
update 表名 set 列名=值 where 条件;
update 表名 set 列名=值 where 条件1 and 条件2;
eg:
update tb1 set age=age+1 where id=2;
update tb1 set name=concat(name,'123') where id=2;-- concat是一个函数，可以拼接字符串
（查询数据）
select * from 表名;
select 列名，列名，，，from 表名;
select 列名，列名，，，as 别名，别名，，，， from 表名;
select * from 表名 where 条件;

5.数据表——类型

1.整型:

变量名 tinyint;
变量名 bigint;
变量名 int unsigned;
变量名 int(5) zerofill; 用于显示，当不满足5位时，在左边补零

2.浮点型：

变量名 decimal(整体数字个数，小数位数)；
weight decimal(5,3)

3.字符型：

char(m)  定长字符串，最多容纳255个字节
name char(3)      无论输入几个字符，都会用三个字符存储(不够用空格补齐）

varchar(m)  不定长字符串，最多容纳65535个字节
name varchar(3)   占用字符数即为插入的字符数

4.大字符串:

text

5.时间：

datatime---存啥是啥
timestamp---储存跟时区有关
二者范围是不一样的：
datetime:YYYY-MM-DD(1000-01-01 00:00:00/9999-12-31 23:59:59)
timestamp:YYYY-MM-DD(1970-01-01 00:00:00/2037年)

eg.create table t(
	id int not null primary key auto_increment,
	dt datetime,
	tt timgstamp
) default charset=utf8;

SQL注入

比如有个登录系统

    user='cgy'
    password='123'
    sql='select * from users where name="{}",password="{}"'.format(user,password)
    cursor.execute(sql)
    conn.commit()

这时候看上去没问题，但是如果输入用户名的是

" or 1=1 --

此时整个SQL语句就是

select * from users where name="" or 1=1; --",password="123"

我们会发现无论输入什么用户名什么密码，or 1=1 都能解决，并且密码都被注释掉了所以数据库的东西就都能被查出来了

当然这个是可以解决滴

cursor.execute('select * from users where name=%s and password=%s',[user,password])
或者
cursor.execute('select * from users where name=%(name)s and password=%(password)s',{'name':user,'password':password})

必备的SQL语句

1.条件

1.
	select * from info where id>=2;
						     id!=2
							 id between 1 and 3
2.
	select * from info where name='陈于' and age=49;
                             id=1 or age=49

3.
	select * from info where id in (1,4,6);
id not in (1,4,6)

4.
	子查询:select * from info where id in (select id from depart);
	
5.
	判断存在：select * from info where exists (select * from depart where id=5);
也可以是not exists
(查询depart表中有id=5,存在则正常执行前一句sql语句) 

2.通配符（like,%,_）

%：n个字符

select * from info where name like “陈%”;
select * from info where email like “%@live.com”;

_：一个字符

select * from info where email like “cgy@live.co_”
select * from info where name like “_与”;

3.映射

有这样两张表

eg.

select
	id,
	name,
	(select title from depart where depart .id=info.depart_id) as 岗位 
	from info;

这样操作，我们就讲两张表联系起来了。

4. 判断语句（when,then,else,end）

select
id,name,
       case depart_id when 1 then "第一部门" end v1,
       case depart_id when 1 then "第一部门" else "其他" end v2,
       case depart_id when 1 then "第一部门" when 2 then "第二部门" else "其他" end v3,
       case when age<18 then "少年" end v4,
       case when age<18 then "少年" else "油腻男" end v5,
       case when age<18 then "少年" when age<30 then "青年" else "油腻男" end v6
from info;

5.排序（order by,asc,desc）

顺序

select * from info order by age asc;
倒序
select * from info order by age desc;

如果用于排序的那个值存在相等的情况，则默认根据id从小到大排序。

想要使得age相等时，让id从大到小排序

select * from info order by age asc,id desc;

6.取部分（limit）

select * from info limit 5;

结合一下前面的就是：

select name,age,email,(select title from depart where info.depart_id=depart.id) as 岗位 from info where depart_id=1 order by age desc limit 3 offset 2;
解读：从info中找到depart_id的值为1的数据，根据年龄逆序排序，在第2个位置之后，最多取3个数据。

7.分组（group by）

通过by后的列名分组，将相同的数据分为一组

select
(select title from depart where info.depart_id=depart.id) as 部门,
count(1) as 人数
from info;

这样我们就得到了每一个部门的人数了

那么如果我们想要设置条件呢？
诶，这里是不可以用where的，要用having

8.sql语句执行顺序

where
group by
having
order by
limit(offset)

9.左右连表

主表 left outer join 从表 on 条件;
从表 right outer join 主表 on 条件;

select info.name,info.age,info.email,depart.title from info left outer join depart on info.depart_id = depart.id;

select info.name,info.age,info.email,depart.title from depart right outer join info on info.depart_id=depart.id;

个人的一点理解：就是我认为可以将后面的（info left outer join depart on info.depart_id=depart.id）看作是一个新的表，在从其中找到name,age,email,depart.title

主从表的作用：

select info.name,info.age,info.email,depart.title from info right outer join depart on info.depart_id=depart.id;

此时是以depart为主表，会体现出depart的全部内容，所以会多出一行空的值

python 操作

链接：
import pymysql
conn=pymysql.connect(host='localhost',port=3306,user='root',passwd='123456',charset='utf8')
还可以添加一个database=''参数，相当于使用use语句
cursor=conn.cursor()

执行：
cursor.execute(sql语句)
1查询：fetchone()/fetchall()
eg:  
cursor.execute(sql语句)
print(cursor.fetchall())
2新增、删除、修改：conn.commit()
eg:
cursor.excute(sql语句)
conn.commit()