names =['aa','bb','cc']
type(names) # list

infos = ['a','b','c','d']

for temp in infos:
    print(temp)

i = 0
while i<len(infos):
    print(infos[i])
    i+=1

a = [1,2,3]
a.append(4)
# [1,2,3,4]

a = [1,2,3,4]
b = [100,200]
a.extend(b)
# [1,2,3,4,100,200]

b = [1,2,3,4]
b.insert(1,99)
# [1,99,2,3,4]

#判断列表里是否存在
# 笨方法
# 笨方法
inputName = 2
flg = 0 
names = [1,2,3,4,5]
for name in names:
    if name = inputName:
        flg = 1
        break

# 好方法
if inputName in names:
    print('find')
else:
    print('not find')

a = [1,1,2,2,3,3,4]
a.index(1,1,3)  # 左闭右开[1,3)

a.count(2)
# 2

--------------------------------
# del
a = [1,2,3,4,5,6]
del a[0]
# [2,3,4,5,6]

del a 
print(a) # 报错 在内存里把数组删除了
--------------------------------
a.pop()
# [2,3,4,5]

--------------------------------
# clear
b = ['a','b','c']
b.clear() # []
--------------------------------

> 删除部分(切片删除)

a = ['aaa','bbb','ccc']
a[0:2] = 'ab'
print(a) # ['a','b','bbb','ccc']
a[0:2] = 'abcd'
print(a) # ['a','b','c','d','bbb','ccc']

a = ['aaa','bbb','ccc']
a[0:3] = [1,2,3,'a','b']
print(a) # [1,2,3,'a','b','bbb','ccc']

a = [1,3,4,2]
a.reverse()
# [2,4,3,1]

a.sort()  # 默认正序
# [1,2,3,4]
a.sort(reverse=True)
#[4,3,2,1]

a = ('a',1,2,3)

a = (1.0,2.0,3.0)
type(a)
#tuple

b = (1.0)
type(b)
# float
# 元组初始化的时候如果只有一个元素是不会初始化为元组的 会把括号里的值当作实际的类型处理

# 正确做法

c = (1.0,)
type(c)
# tuple

a = ('a','b',[1,2,3],4)

a[2][0] = 'xxx'
# ('a','b',['xxx',2,3],4)

s = 'abcde'
s1 = '+'.join(s) # a+b+c+d+e

li = [1,2,3,4,5,6]
''.join(li) # 123456

range() #就是一个元祖
for i in range(3,10):
    print(i) #循环打印一次，3-9的数字。
for i in range(10):
    print(i) #循环打印一次，0-9的数字。
for i in range(0,10,3):
    print(i) #循环打印一次，0-9的数字，步长3，间接输出0-9所有的被3整除的数。
for i in range(10,0,-2):
    print(i) #循环打印一次，10-1的数字，反向步长为2。
for i in range(10,-1,-2):
    print(i) #循环打印一次，10-0的数字，反向步长为2。

for i in range(0,10,-1):
    print(i)

#bit_length() 当十进制用二进制表示时，最少使用的位数
v = 11
data = v.bit_length()
print(data)

'''
0000 0000   0  1
0000 0001   1  1
0000 0010   2  2
0000 0011   3  2
0000 0100   4  3
0000 0101   5  3
'''

#True   1
#False  0
'''

while True:
    pass
while 1:  # 效率高
    pass

a = 'ABCDEFGHIJK'
print(a[0])
print(a[3])
print(a[5])
print(a[7])

#超过字符串长度咋办？
a[0:70] 'ABCDEFGHIJK'

a = 'ABCDEFGHIJK'
print(a[0:3]) # ABC
print(a[2:5]) # CDE
print(a[0:])  # 默认到最后 ABCDEFGHIJK
print(a[0:-1]) # -1就是最后一个 ABCDEFGHIJ
print(a[0:5:2]) #加步长 ACE
print(a[5:0:-2]) #反向加步长 FDB

s = 'ABCDLSESRF'
s1 = s[0] # A
s2 = s[2] # C
s3 = s[-1] # F
s4 = s[-2] # R

s = 'ABCDLSESRF'
s5 = s[0:4]  # ABCD
s6 = s[0:-1] # ABCDLSESR

s7 = s[:] # ABCDLSESRF
s8 = s[0:] #ABCDLSESRF

s9 = s[0:0] # ''空字符


s = 'ABCDLSESRF'    # s[首:尾:步长]
s10 = s[0:5:2]  # ACL
s11 = s[4:0:-1] # LDCB
s12 = s[3::-1]  # DCBA
s13 = s[3::-2]  # DB
s14 = s[-1::-1] # FRSESLDCBA
s15 = s[::-1]   # FRSESLDCBA

#字符串的操作
s = 'alexWUsir'
s1 = s.capitalize()  # 首字母大写

# 全大写，全小写
s2 = s.upper()
s21 = s.lower()

#大小写翻转
# s3 = s.swapcase()

# 每个隔开(特殊字符或者数字)的单词首字母大写
# s = 'alex*egon-wusir'
# s4 = s.title()

# s = 'fade,crazy*w4rri0r_songsong node_3'
# s4 = s.title()

#居中，空白填充
# s = 'alexWUsir'
# s5 = s.center(20,'~')

# s = 'alex\tsir'
# s6 = s.expandtabs()

# s = 'alex二哥'
# #公共方法
# l = len(s)

#以什么开头结尾 endswith
# s = 'alexWUsir'
# s7 =s.startswith('alex')
# s71 = s.startswith('e',2,5)

# find 通过元素找索引，找不到返回-1
#
# index通过元素找索引，找不到报错
# s = 'alexWUsir'
# s8 = s.find('A')
# s81 = s.index('A')

#strip rstrip lstrip
# s = 'alexWUsir%'
# s9 = s.strip('%')

# s = ' *a%lexWUsi* r%'
# s91 = s.strip(' %*')

# strip 默认删除前后空格

# username = input('请输入名字：').strip()
# if username =='春哥':
#     print('恭喜春哥发财')

# s = 'alexaa wusirl'
# s10 = s.count('al')
# print(s10)

# split   str ---->list
# s = ';alex;wusir;taibai'
# l = s.split('a')
# print(l)

# s = '来看待街坊邻居复合大师街坊法好的撒见客户'
# s11 = s.replace('街坊','老王',1)

# s = 'fhdsklfds'
# for i in s:
#     print(i)
s = 'fdsa苍井空fdsalk'
if '苍井空' in s:
    print('您的评论有敏感词...')

res='{} {} {}'.format('egon',18,'male')
res='{1} {0} {1}'.format('egon',18,'male')
res='{name} {age} {sex}'.format(sex='male',name='egon',age=18)

a = 'abcdefg'
for temp in a:
    print(temp) 


i = 0 ;
length = len(a)
while i<length:
    print(a[i])
    i+=1

#1、使用while循环输入 1 2 3 4 5 6 8 9 10
'''
count = 0
while count < 10:
    count += 1  # count = count + 1
    if count == 7:
        print(' ')
    else:
        print(count)

count = 0
while count < 10:
    count += 1  # count = count + 1
    if count == 7:
        continue
    print(count)
'''
#3、输出 1-100 内的所有奇数
#方法一：
# count = 1
# while count < 101:
#     print(count)
#     count += 2
#方法二：
# count = 1
# while count < 101:
#     if count % 2 == 1:
#         print(count)
#     count += 1

#5、求1-2+3-4+5 ... 99的所有数的和
# sum = 0
# count = 1
# while count < 100:
#     if count % 2 == 0:
#         sum = sum - count
#     else:
#         sum = sum + count
#     count += 1
# print(sum)

#6、用户登陆（三次机会重试）
#input 心中有账号，密码 while

i = 0
while i < 3:
    username = input('请输入账号：')
    password = int(input('请输入密码：'))
    if username == '咸鱼哥' and password == 123:
        print('登录成功')
    else:
        print('登录失败请重新登录')
    i += 1

#格式化输出
# % s d
# name = input('请输入姓名')
# age = input('请输入年龄')
# height = input('请输入身高')
# msg = "我叫%s，今年%s 身高 %s" %(name,age,height)
# print(msg)

"""
name = input('请输入姓名:')
age = input('请输入年龄:')
job = input('请输入工作:')
hobbie = input('你的爱好:')

msg = '''------------ info of %s -----------
Name  : %s
Age   : %d
job   : %s
Hobbie: %s
------------- end -----------------''' %(name,name,int(age),job,hobbie)
print(msg)
"""

name = input('请输入姓名')
age = input('请输入年龄')
height = input('请输入身高')
msg = "我叫%s，今年%s 身高 %s 学习进度为3%%s" %(name,age,height)
print(msg)

count = 0
while count <= 5 :
    count += 1
    print("Loop",count)

else:
    print("循环正常执行完啦")
print("-----out of while loop ------")

输出如下：

Loop 1
Loop 2
Loop 3
Loop 4
Loop 5
Loop 6
循环正常执行完啦
-----out of while loop ------

count = 0
while count <= 5 :
    count += 1
    if count == 3:break
    print("Loop",count)

else:
    print("循环正常执行完啦")
print("-----out of while loop ------")

输出如下：

Loop 1
Loop 2
-----out of while loop ------

如:
二进制       十进制     八进制     十六进制
0000 0000    0          0         0
0000 0001    1          1         1
0000 0010    2          2         2
0000 0011    3          3         3
0000 0100    4          4         4
0000 0101    5          5         5
0000 0110    6          6         6
0000 0111    7          7         7
0000 1000    8         10         8
0000 1001    9         11         9
0000 1010   10         12         a
0000 1011   11         13         b
0000 1100   12         14         c
0000 1101   13         15         d
0000 1110   14         16         e
0000 1111   15         17         f

#!/usr/bin/env python

一直不变的量。     π
BIR_OF_CHINA = 1949
```  

> 在Python中没有一个专门的语法代表常量，程序员约定俗成用变量名全部大写代表常量

     
### 8.注释。

 
### 9.用户交互。input

1. 等待输入.
2. 将你输入的内容赋值给了前面变量。
3. input出来的数据类型全部是str
    
### 10.基础数据类型初始。

#### 数字：int 12,3,45 
    + - * / ** 
    % 取余数
    ps:type()
        字符串转化成数字：int(str) 条件：str必须是数字组成的。
        数字转化成字符串：str(int)

> 注意，自从Python2.2起，如果整数发生溢出，Python会自动将整数数据转换为长整数，所以如今在长整数数据后面不加字母L也不会导致严重后果了。

> #### 注意：在Python3里不再有long类型了，全都是int
#### 字符串：

> str.python当中凡是用引号引起来的都是字符串。
    可相加:字符串的拼接。
    可相乘：str * int

#### bool:布尔值。 
- True
- False
 
 
#### 11.if 分支语句

 
### 12.while。

pip install PyMySQL
# 有可能会提示你pip版本低，于是你按照命令升级，然后不小心ctrl c了，那就直接去python官网重新下载一个覆盖安装即可

cursor1=conn.cursor()

import pymysql

try:
    # 连接数据库
    db = pymysql.connect(host="localhost",user="root",password="123456",db="test170910",port=3306,charset="utf8")
    #创建游标对象
    cursor = db.cursor() 
    # sql语句
    sql = 'insert into students(name,gender,birthday) values("郭大侠",1,"2018-01-11")';
    #执行SQL语句
    cursor.execute(sql) 
    #提交
    db.commit()
except Exception as e:
        db.rollback() 
        print(e)
finally:
        db.close()

import pymysql

try:
    # 连接数据库
    db = pymysql.connect(host="localhost",user="root",password="123456",db="test170910",port=3306,charset="utf8")
    cursor = db.cursor() #创建游标对象
    # sql语句
    sql = 'update  students set name="郭一一" where id >3';
    #执行SQL语句
    cursor.execute(sql) 
    #提交
    db.commit()
    print('ok')
except Exception as e:
        db.rollback() 
        print(e)
finally:
        db.close()

import pymysql

try:
    # 连接数据库
    db = pymysql.connect(host="localhost",user="root",password="123456",db="test170910",port=3306,charset="utf8")
    cursor = db.cursor() #创建游标对象
    # sql语句
    sql = 'delete from students where id =3';
    #执行SQL语句
    cursor.execute(sql) 
    #提交
    db.commit()
    print('ok')
except Exception as e:
        db.rollback() 
        print(e)
finally:
        db.close()

import pymysql

try:
    # 连接数据库
    db = pymysql.connect(host="localhost",user="root",password="123456",db="test170910",port=3306,charset="utf8")
    cursor = db.cursor() #创建游标对象
    # sql语句
    sql = 'select * from students';
    #执行SQL语句
    cursor.execute(sql) 
    #获取返回数据
    print('--------------------')
    data = cursor.fetchone() 
    print(data)
    print('--------------------')
except Exception as e:
        db.rollback() 
        print(e)
finally:
        db.close()

select * from students where name='a'

如果 'a' ===> 'a' or 1 = 1 就会导致全部被查出来 1=1为true

参数化
1. sql语句作为第一个参数  
2. @name的值作为第二个参数(进行一系列的判断，来有效阻止sql注入)
select * from students where name=@name

sname = '对应的一个值'
# 参数是一个列表的形式
params =[sname] 
# 这里的%s不是 print()里的格式化输出  对应 params里的值
sql = 'insert into students(name) values(%s)'
count = cursor.execute(sql,params)

cur.execute('select * from students where id=7')
result=cur.fetchone()
print(result)

cur.execute('select * from students')
result=cur.fetchall()
print(result)

数据库里有一条数据,进行更新操作
1 郭靖

step000 开启事务
begin;

step001_1 此句话会把  1 郭靖这条数据锁起来
step001_2 在临时的一个表里(内存级的临时表) 将数据改成 1 效果
update students set name='小郭' where id=1;


此时你有两种操作  一个是commit一个是rollback

commit操作
step002_0 如果你此时执行一个commit操作
step002_1 将临时表的操作更新到实际的表
step002_2 取消原表的锁定状态

rollback操作
step002_0 会将begin之后所有操作丢弃
step002_1 取消原表的锁定状态

show index from students;

create index 索引名 on 表名(字段名(长度，如果是数组就不用指定))


多个字段索引
create index 索引名 on 表名(name,gender);

drop index [索引名] on 表名

select name,sum(score)
from students 
inner join scores on students.id=scores.stuid
where gender=1
group by students.id;

select subjects.title,avg(scores.score)
from scores 
inner join subjects on scores.subid=subjects.id
group by subjects.title;

select subjects.title,avg(scores.score),max(scores.score)
from scores 
inner join subjects on scores.subid=subjects.id
where subjects.isdelete=0
group by subjects.title;

create table areas(
    aid int primary key auto_increment not null,
    name varchar(20),
    pid int,
    foreign key(pid) references areas(id)
);

select sheng.aid as sid,sheng.name as sname,
shi.aid as shiid,shi.name as shiname
from areas as sheng
inner join areas as shi on sheng.aid=shi.aid
where sheng.pid is null and sheng.name='山西省'
;

create table scores (
    id int primary key auto_increment,
    stuid int,
    subid int,
    score decimal(5,2)
);

学生添加的时候如果添加班级4如何去验证班级是否存在？

此时就会有外键约束

create table scores (
    id int primary key auto_increment,
    stuid int,
    subid int,
    score decimal(5,2),
    foreign key(stuid) references students(id),
    foreign key(subid) references subjects(id),
);

alter table scores add constraint stu_score foreign key(stuid) references students(id);


stu_score 代表约束名字(语法要求)

select * from scores;

select students.name,subjects.title,scores.score    
from scores
inner join students on scores.stuid=students.id
inner join subjects on scores.subid=subjects.id
;

select * 
from scores
inner join  students on students.id=scores.stuid
;

select * 
from students
left join scores on students.id=scores.stuid
;

select * 
from scores 
left join students on students.id=scores.stuid
;

select name,ave(scores) as avg1 from scores
inner join students on scores.stuid=students.id
group by stuid
order by avg1

select * from 表名 ;

select * from students;
##########################################################
| id | name | birthday              | gender | isDelete |
##########################################################
  1    郭靖   2017-01-01 00:00:00       1
  2    黄蓉   2017-01-01 00:00:00       0
  3    郭芙   2017-01-01 00:00:00       0
  4    杨过   2017-01-01 00:00:00       1

select id,name from students;
##########################################################
  id   name
  1    郭靖  
  2    黄蓉   
  3    郭芙   
  4    杨过

select gender from students;
##########################################################
gender
1
0
0
1

select distinct gender from students;
##########################################################
gender
1
0

select * from 表名 where 条件;

select * from students where id=3;
select * from students where id>3;
select * from students where id>=3;
select * from students where id<3;
select * from students where id<=3;

select * from students where name='黄蓉';

select * from students where name!='黄蓉';

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

select * from students where id<4 or isdelete=0;

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

select * from students where name like '黄_';

select * from students where name like '黄__';

select * from students where name like '黄%' or name like '%靖';

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

select * from students where id between 3 and 8;

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

select * from students where address is null;

select * from students where address is not null;

select * from students where address is not null and gender=0;

查询学生总数
select count(*) from students;

查询学生总数未被删除的
select count(*) from students where isdelete=0;

查询最小编号的学生信息  ————子查询
select * from students where id =(
    select min(id) from students where isdelete=0
);

select 列1,列2,聚合... from 表名 group by 列1,列2,列3...

select 列1,列2,聚合... from 表名
group by 列1,列2,列3...
having 列1,...聚合...

方案一
select count(*)
from students
where gender=1;
-----------------------------------
方案二：
select gender as 性别,count(*)
from students
group by gender
having gender=1;

原始集合——>where——>结果集——>分组>结果集——>having——>

select * from 表名
order by 列1 asc|desc,列2 asc|desc,...

select * from 表名 limit start,count;

select * from students
where isdelete=0
limit (n-1)*m,m

select * from 表名
where ...
group by ... having ...
order by ...
limit start,count

select * from 表名

# 全列
insert into students values(0,'八戒',1,'1990-1-1',0);
# 缺省插入
insert into students(name) values('唐僧');

#批量
insert into students values(1,'aaa',1,'1990-1-1',0),(2,'bbb',1,'1990-1-1',0),(3,'ccc',1,'1990-1-1',0);

insert into students(name) values('唐僧'),('八戒'),('悟空');

# 对满足条件的行修改
update 表名 set 列1=值,... where条件 

如
# 把id=2的 生日修改了
update students set birthday='1990-1-1' where id=2; 

# 不写条件就是全部改
update students set gender=0,birthday='2018-6-23' where id=6;

delete from 表名 where 条件