http://www.cnblogs.com/wupeiqi/articles/5713315.html
http://www.cnblogs.com/wupeiqi/articles/5729934.html #练习题
http://www.cnblogs.com/wupeiqi/articles/5748496.html #练习题
#进入数据库
use 库名
#创建表(单表)
create table student(
-> id int null， #可以为空
-> id int not null #不为空
-> id int null default 1，#默认值为1
-> id int null auto_increment #自增
-> id int null auto_increment primary key#主键
-> id int,
-> mame char(20), 指#定字符长度，不是字节
-> age int default 18, #主键
-> gender char(1)
-> )engine=innodb default charset=utf8; #指定引擎和字符集
-> ;
1.列表
2.数据类型
3.是否可以为空：字段使用NOT NULL属性，是因为我们不希望这个字段的值为NULL。 因此，如果用户将尝试创建具有NULL值的记录，那么MySQL会产生错误。
4.默认值
5.自增（一个表只能有一个自增列，字段的AUTO_INCREMENT属性告诉MySQL自动增加id字段下一个可用编号。
6.主键（用于约束，不能为空，不能重复）primary key
约束：不能为空，不能重复
索引：加速查找
关键字PRIMARY KEY用于定义此列作为主键。可以使用逗号分隔多个列来定义主键。
7.外键：
约束：只能是某个表中某列已经存在的数据 FOREIGN KEY
#多表（一对多）
create table userinfo(
-> id int not null auto_increment primary key,
-> name char(20),
-> age int default 18,
-> gender char(1),
-> department_id int
-> constraint xxxx foreign key (dempartment_id) references department(id)#constraint 是约束， xxx是自己起的约束名字，foreign key 指的是自己的，references 是对方的
-> )engine=innodb default charset=utf8;
create table department(
->id int not null auto_increment primary key,
-> title char(32)
->)engine=innodb charset utf8;
#多对多
create table boy(
->id int not null auto_increment primary key,
-> title char(32)
->)engine=innodb charset utf8;
create table girl(
->id int not null auto_increment primary key,
-> title char(32)
->)engine=innodb charset utf8;
create table B2G(
->id int not null auto_increment primary key,
-> bid int,
-> gid int,
->constraint fk1 foreign key (bid) references boy(id)
->constraint fk2 foreign key (gid) references boy(id)
->)engine=innodb charset utf8;
#文件内容操作（数据行）
#mysql引擎
innodb:InnoDB包括了对事务处理和外来键的支持
MyISAM:不支持事务
数据类型：数值、时间和字符串
数值：
整数：tinyint
smallint
int 整型
bigint 长整型
小数：
float（浮点型,位数越大越不精准） 1.2334123-->1.23344422
double（和float差不多，精准稍好）1.2334123-->1.2334122
decimal（精准） 1.2334123-->1.2334123
字符串：
char（19） #定长，空间用不了就用点代替（字符长度）（最多255）
varchar（19）#不定长，该多少就多少（最多255）
text
mediumtext
longtext
二进制
TinyBlob
Blob
MediumBlob
LongBlob
时间类：
date
time
year
datetime
timestamp
bit[(M)]
二进制位（101001），m表示二进制位的长度（1-64），默认m＝1
tinyint[(m)] [unsigned] [zerofill]
小整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-128 ～ 127.
无符号：
0 ～ 255
特别的： MySQL中无布尔值，使用tinyint(1)构造。
int[(m)][unsigned][zerofill]
整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-2147483648 ～ 2147483647
无符号：
0 ～ 4294967295
特别的：整数类型中的m仅用于显示，对存储范围无限制。例如： int(5),当插入数据2时，select 时数据显示为： 00002
bigint[(m)][unsigned][zerofill]
大整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-9223372036854775808 ～ 9223372036854775807
无符号：
0 ～ 18446744073709551615
decimal[(m[,d])] [unsigned] [zerofill]
准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。
特别的：对于精确数值计算时需要用此类型
decaimal能够存储精确值的原因在于其内部按照字符串存储。
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
无符号：
-3.402823466E+38 to -1.175494351E-38,
0
1.175494351E-38 to 3.402823466E+38
有符号：
0
1.175494351E-38 to 3.402823466E+38
**** 数值越大，越不准确 ****
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
无符号：
-1.7976931348623157E+308 to -2.2250738585072014E-308
0
2.2250738585072014E-308 to 1.7976931348623157E+308
有符号：
0
2.2250738585072014E-308 to 1.7976931348623157E+308
**** 数值越大，越不准确 ****
char (m)
char数据类型用于表示固定长度的字符串，可以包含最多达255个字符。其中m代表字符串的长度。
PS: 即使数据小于m长度，也会占用m长度
varchar(m)
varchars数据类型用于变长的字符串，可以包含最多达255个字符。其中m代表该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中。
注：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡
text
text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 ? 1)个字符。
mediumtext
A TEXT column with a maximum length of 16,777,215 (2**24 ? 1) characters.
longtext
A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 ? 1) characters.
enum
枚举类型，
An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)
示例：
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');
set
集合类型
A SET column can have a maximum of 64 distinct members.
示例：
CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
DATE
YYYY-MM-DD（1000-01-01/9999-12-31）
TIME
HH:MM:SS（'-838:59:59'/'838:59:59'）
YEAR
YYYY（1901/2155）
DATETIME
YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y）
TIMESTAMP
YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）
#插入：
insert into userinfo(name,age,gender,department_id) values('alex',17,'男',11111),('ale',18,'男',2222) #可以插入多个值
#自增id下，只输入不是自增的值就可以
+-----+--------------+
| cid | caption |
+-----+--------------+
| 3 | 三年一班 |
| 4 | 三年三班 |
+-----+--------------+
insert into class(caption) values('三年一班'),('三年三班'); #caption是不是自增那列
#查询teacher表的tname段的数据到class表的caption段里
insert into class(caption) select tname from teacher;
#删除表里的数据
相对来说，truncate执行速度要比delete快
+-----+--------------+
| cid | caption |
+-----+--------------+
| 4 | 三年三班 |
| 6 | 三年三班 |
+-----+--------------+
delete删除的弊端是全部删除数据后，再创建的数据会从删除数据的末端开始创建，也就是删除后从上面的6下面开始算，也就是7
delete from test; #test是表
delete from test where caption='三年一班'; #删除test表里的某个字段里的某条数据
delete from test where cid=5; #删除test表里的某个字段里的某条数据
delete from test where cid=5 and caption='三年三班'#删除符合两个条件的数据
delete from test where cid=5 and caption='三年三班'#删除符合两个条件中的一个条件的数据，注意：是符合任意一个条件的数据，这些符合的都删，不是只删除一条
delete from test where cid<14; #删除id小于14的，不删除14
delete from test where cid>16; #删除id大于16的，不删除16
delete from test where id in (1,2,445,5) #批量删除这些id
delete from test where id between 6 and 10 删除id在6和10之间的，6和10也会删除
truncate删除可以规避delete的删除弊端，从头开始创建，但是也和delete有一个一样的弊端，就是删除某一条数据，再创建的时候不会补充删除的那个id，而是从数据最末端的id后开始创建。truncate不能删除表里的具体某个数据，只能删除表
#删除表
drop table 表名字
#更新update
update test set caption='222' 修改caption下的所有数据为222
update test set caption='222' where cid=4 #只修改caption下的id=4的文件成222
update test set caption='222',cid=9 where cid=4 只修改caption下的id=4的文件成222并且id改成9
#查询
select * from test; #查询所有
select cid,caption from test; #查询两个字段
select * from test where cid in (1,2) #查询cid的1和2端口字段
select * from test where cid in (select tid from teacher) #可以在in里加入查询语句，动态查看数据，只能查一列
select cid from test; #查询test的cid段
select * from score order by number #根据number进行排序
select * from score order by number asc；#根据number进行排序 从下到大排序
select * from score order by number desc；#根据number进行排序 从大到小排序
select * from score order by number desc limit 2; #limit取前两个，整体意思是取前两个分数大的
select * from score order by number asc limit 2; #limit取前两个，整体意思是取前两个分数小的
select * from test limit 1,2; #limit 1,2第一个1代表从第几个开始找起，第二个2代表找两个
select * from test where tname like "%狗%" #模糊匹配test的tname里有狗的字段
select * from test where tname like "%狗" #模糊匹配test的tname里以狗结尾的字段
select * from test where tname like "狗%" #模糊匹配test的tname里以狗开头的字段
select * from test where tname like "_狗" #模糊匹配test的tname里狗前头只能有一个字符的字段
select * from test where tname like "__狗" #模糊匹配test的tname里狗前头只能有2个字符的
select cid as '222',caption as '222' from test; #修改cid和caption的名字，只是临时修改，select 查询不加as会变成原来的名字
select cid as '222',caption from test; #修改cid的名字
select cid,caption as '22' from test; #修改caption的名字
select * from course left join teacher on course.teacher_id = teacher.tid where teacger.tname = '郭老师' #查看课程和老师关联的数据，并且查看具体老师
修改前
+-----+---------+
| cid | caption |
+-----+---------+
| 9 | 222 |
| 24 | 222 |
| 25 | 222 |
| 26 | 222 |
| 27 | 222 |
| 28 | 222 |
+-----+---------+
修改后
+-----+------+
| 222 | 222 |
+-----+------+
| 9 | 222 |
| 24 | 222 |
| 25 | 222 |
| 26 | 222 |
| 27 | 222 |
| 28 | 222 |
+-----+------+
#连表
将两张表连接在一起显示
select * from student left join class on student.class_id = class.cid ; #只显示与student相关联的数据，不相关联的数据不显示。left
select * from student right join class on student.class_id = class.cid ; #会显示class表的所有数据和student表与class相关联的数据
left和right的区别在于left是以左边的表为主，right是以右边的表为主，为主的表会显示全部，附表只会显示与主表相关联的数据，但是如果主数据多，相关联的附表的数据少，那么附表会显示null
select student.sid,class.caption,student.sname from student inner join class on student.class_id = class.cid ; #和上面类似，但是inner只保留两边相同的数据，多余的数据一方不够的话，会去掉多余的那部分，防止出现null
student表
+----------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+----------------+
| sid | int(11) | NO | PRI | NULL | auto_increment |
| sname | char(12) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| class_id | int(11) | YES | MUL | NULL | |
+----------+----------+------+-----+---------+----------------
class表
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| cid | int(11) | NO | PRI | NULL | auto_increment |
| caption | varchar(16) | YES | | NULL | |
+---------+-------------+------+-----+---------+----------------+
连表后
+-----+-----------+--------+----------+------+--------------+
| sid | sname | gender | class_id | cid | caption |
+-----+-----------+--------+----------+------+--------------+
| 1 | 刘浩 | 男 | 1 | 1 | 三年二班 |
| 2 | 吴一飞 | 男 | 2 | 2 | 三年一班 |
| 3 | 江浩 | 男 | 2 | 2 | 三年一班 |
| 4 | 小贱 | 男 | 3 | 3 | 三年三班 |
+-----+-----------+--------+----------+------+--------------+
select student.sid,class.caption,student.sname from student left join class on student.class_id = class.cid ; #从得到的连表里获取某一列的数据
#分组：
select * from student group by class_id; #相同的合并成一条
select class_id,count(sid) from student group by class_id; 统计每个id的条数
select class_id,count(sid),max(sid),min(sid),sum(sid),avg(sid) from student group by class_id; #最大的，最小的，和，平均值
+----------+------------+----------+----------+----------+----------+
| class_id | count(sid) | max(sid) | min(sid) | sum(sid) | avg(sid) |
+----------+------------+----------+----------+----------+----------+
| 1 | 1 | 1 | 1 | 1 | 1.0000 |
| 2 | 2 | 3 | 2 | 5 | 2.5000 |
| 3 | 1 | 4 | 4 | 4 | 4.0000 |
+----------+------------+----------+----------+----------+----------+
select class_id as "班级",count(sid) as "人数" from student group by class_id having count(sid)<2; #统计小于2的，但是这里不能用where不支持，要用having
#pymysql
#查询数据
import pymysql
#连接数据库，进入数据库
conn = pymysql.connect(host='192.168.108.149',port=3306,user='root',password='123',database='yutielin',charset='utf8')
cursor = conn.cursor() #游标，取数据
#受影响的行数
v = cursor.execute('select * from student') #取里面的数据
# print(v)
result = cursor.fetchall() #获取所有的数据
print(result)
#result = cursor.fetchone() #只拿一个，多个fetchone，会一次次的拿去下一个，这其中涉及到了指针
#result = cursor.fetchmany(2) #指定拿多少个数据，这里是拿2个
cursor.close()
conn.close()
#用户登录获取用户名和密码（不建议，有漏洞，有被sql注入的风险）
import pymysql
user = input('>>>输入账户：')
pwd = input('>>>输入密码')
#连接数据库，进入数据库
conn = pymysql.connect(host='192.168.108.149',port=3306,user='root',password='123',database='yutielin',charset='utf8')
cursor = conn.cursor() #游标，取数据
sql = 'select * from userinfo where username="%s" and password="%s" '%(user,pwd,)
#受影响的行数
v = cursor.execute(sql) #取里面的数据
# print(v)
result = cursor.fetchone() #获取所有的数据
print(result)
#result = cursor.fetchone() #只拿一个，多个fetchone，会一次次的拿去下一个，这其中涉及到了指针
#result = cursor.fetchmany(2) #指定拿多少个数据，这里是拿2个
cursor.close()
conn.close()
##通过账号密码，拿取数据库的数据（#不安全，有sql注入的风险）
import pymysql
user = input('>>>输入账户：')
pwd = input('>>>输入密码:')
conn = pymysql.connect(host='192.168.108.149',port=3306,user='root',password='123',database='yutielin',charset='utf8') #登录ip，端口，密码，库，字符编码
cursor = conn.cursor() #游标
sql = 'select * from userinfo where username="%s" and password="%s" '%(user,pwd,)
print(sql) #显示上面那条命令的输出情况，用于排错
v = cursor.execute(sql) #变量
result = cursor.fetchone() #获取数据，一次次拿
cursor.close() #关闭cursor
conn.close() #关闭连接
print(result) #打印返回数据
#注入风险的原因
如果输入账号密码是下面这样输入的话，就会有风险，因为alex” 是%s的数值，后面的 --在数据库里是注释的意思，也就是说注释了后面需要验证的密码，所以绕过了密码的验证，这就是sql注入
"alex" --
123
#还有一种sql注入的风险，那就是账号密码都不对的情况下也能够获取数据，具体情况如下：
这里面1=1永远成立，--又注释了密码的验证，所以也绕过了验证，更全，把账户密码都绕过了
>>>输入账户：jkdskds" -- or 1==1
>>>输入密码:dskds
#解决方式：不自己格式化字符串，通过execute的第二个参数传值
import pymysql
user = input('>>>输入账户：')
pwd = input('>>>输入密码:')
conn = pymysql.connect(host='192.168.108.149',port=3306,user='root',password='123',database='yutielin',charset='utf8') #登录ip，端口，密码，库，字符编码
cursor = conn.cursor() #游标
v = cursor.execute('select * from userinfo where username=%s and password=%s',[user,pwd]) #不通过格式化字符串，由pymysql自己做处理，传值由execute的第二个参数传值，也就是[user,pwd]
result = cursor.fetchone() #获取数据，一次次拿
cursor.close() #关闭cursor
conn.close() #关闭连接
print(result) #打印返回数据
#任何的增删改都需要commit才能将数据写入数据库
#插入数据
import pymysql
# user = input('>>>输入账户：')
# pwd = input('>>>输入密码:')
conn = pymysql.connect(host='192.168.108.149',port=3306,user='root',password='123',database='yutielin',charset='utf8') #登录ip，端口，密码，库，字符编码
cursor = conn.cursor() #游标
cursor.execute('insert into userinfo(username,password) values(%s,%s)',['eritc','112']) #不通过格式化字符串，由pymysql自己做处理，传值由execute的第二个参数传值，也就是[user,pwd]
conn.commit() #必须提交，否则无法插入
cursor.close() #关闭cursor
conn.close() #关闭连接
#删除数据
conn = pymysql.connect(host='192.168.108.149',port=3306,user='root',password='123',database='yutielin',charset='utf8') #登录ip，端口，密码，库，字符编码
cursor = conn.cursor() #游标
cursor.execute('delete from userinfo where username=%s',['eritc']) #不通过格式化字符串，由pymysql自己做处理，传值由execute的第二个参数传值，也就是[user,pwd]
conn.commit()
cursor.close() #关闭cursor
conn.close() #关闭连接
#更新数据
conn = pymysql.connect(host='192.168.108.149',port=3306,user='root',password='123',database='yutielin',charset='utf8') #登录ip，端口，密码，库，字符编码
cursor = conn.cursor() #游标
cursor.execute('update userinfo set password=%s where username=%s' ,['12222','alex']) #不通过格式化字符串，由pymysql自己做处理，传值由execute的第二个参数传值，也就是[user,pwd]
conn.commit() #提交
cursor.close() #关闭cursor
conn.close() #关闭连接
#通过cursor.lastrowid可以获取自增的新的id
#通过自增id，新创建一个学院，并且将学院与class 表的课程连接起来，这需要创建两次表数据，以下2次cursor.execute就是在两个表里创建数据，第二条的new_class_id就是创建关联
import pymysql
conn = pymysql.connect(host='192.168.108.149',port=3306,user='root',password='123',database='yutielin',charset='utf8') #登录ip，端口，密码，库，字符编码
cursor = conn.cursor() #游标
cursor.execute('insert into class(caption) values(%s)',['新班级'])
conn.commit()
new_class_id = cursor.lastrowid #获取上面新创建班级的自增id
cursor.execute('insert into student(sname,gender,class_id) values(%s,%s,%s)' ,['李杰','男',new_class_id]) #不通过格式化字符串，由pymysql自己做处理，传值由execute的第二个参数传值，也就是[user,pwd]
conn.commit()
cursor.close() #关闭cursor
conn.close() #关闭连接
#通过pymysql.cursors.DictCursor 将获取的数据类型改成字典类型
import pymysql
#连接数据库，进入数据库
conn = pymysql.connect(host='192.168.108.149',port=3306,user='root',password='123',database='yutielin',charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) #游标，取数据，更改类型成为字典
#受影响的行数
v = cursor.execute('select * from student') #取里面的数据
# print(v)
result = cursor.fetchall() #获取所有的数据
print(result)
#result = cursor.fetchone() #只拿一个，多个fetchone，会一次次的拿去下一个，这其中涉及到了指针
#result = cursor.fetchmany(2) #指定拿多少个数据，这里是拿2个
cursor.close()
conn.close()
更改前（未加ursor=pymysql.cursors.DictCursor前）
((1, '刘浩', '男', 1), (2, '吴一飞', '男', 2), (3, '江浩', '男', 2), (4, '小贱', '男', 3), (8, '李杰', '男', 14))
更改后（加ursor=pymysql.cursors.DictCursor后）
[{'sid': 1, 'sname': '刘浩', 'gender': '男', 'class_id': 1}, {'sid': 2, 'sname': '吴一飞', 'gender': '男', 'class_id': 2}, {'sid': 3, 'sname': '江浩', 'gender': '男', 'class_id': 2}, {'sid': 4, 'sname': '小贱', 'gender': '男', 'class_id': 3}, {'sid': 8, 'sname': '李杰', 'gender': '男', 'class_id': 14}]
浙公网安备 33010602011771号