|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
|
一、数据库基本操作
数据库操作:
查看存储引擎: show engines;
查看数据库: show databases; 或者show create database oldboy\G
创建数据库: create database oldboy default charset
=
utf8;
删除数据库: drop database oldboy;
进入数据库: use oldboy;
数据表操作:
创建数据表:
create table tb_emp1 (
-
>
id
int
(
11
),
-
> name varchar(
25
),
-
> deptId
int
(
11
),
-
> salary
float
-
> )engine
=
innodb default charset
=
utf8;
primary key:
create table tb_emp2 (
-
>
id
int
(
11
) primary key,
-
> name varchar(
25
),
-
> deptId
int
(
11
),
-
> salary
float
-
> )engine
=
innodb default charset
=
utf8;
多个primary key:
create table tb_emp3 (
id
int
(
11
), name varchar(
25
), deptId
int
(
11
), salary
float
,
primary key(name, deptId) )engine
=
innodb default charset
=
utf8;
外键约束foreign key:
主表:
create table tb_dept1 (
-
>
id
int
(
11
) primary key,
-
> name varchar(
22
)
not
null,
-
> location varchar(
50
)
-
> )engine
=
innodb default charset
=
utf8;
从表:
create table tb_emp5 (
-
>
id
int
(
11
) primary key,
-
> name varchar(
25
),
-
> deptId
int
(
11
),
-
> salary
float
,
-
> constraint fk_emp_dept1 foreign key(deptId) references tb_dept1(
id
)
-
> )engine
=
innodb default charset
=
utf8;
非空约束
not
null:
create table tb_emp6 (
-
>
id
int
(
11
) primary key,
-
> name varchar(
25
)
not
null,
#非空
-
> deptId
int
(
11
),
-
> salary
float
,
-
> constraint fk_emp_dept2 foreign key(deptId) references tb_dept1(
id
)
-
> )engine
=
innodb default charset
=
utf8;
唯一性约束unique,要求该列唯一，允许为空，但只能有一个值为空:
create table tb_dept2 (
-
>
id
int
(
11
) primary key,
-
> name varchar(
22
) unique,
-
> location varchar(
50
)
-
> )engine
=
innodb default charset
=
utf8;
默认值default:
create table tb_emp7 (
-
>
id
int
(
11
) primary key,
-
> name varchar(
25
)
not
null,
-
> deptId
int
(
11
) default
1111
,
-
> salary
float
,
-
> constraint fk_emp_dept3 foreign key(deptId) references tb_dept1(
id
)
-
> )engine
=
innodb default charset
=
utf8;
自增主键auto_increment:
create table tb_emp8 (
-
>
id
int
(
11
) primary key auto_increment,
-
> name varchar(
25
)
not
null,
-
> deptId
int
(
11
),
-
> salary
float
,
-
> constraint fk_emp_dept5 foreign key(deptId) references tb_dept1(
id
)
-
> )engine
=
innodb default charset
=
utf8;
插入tb_emp8三条数据:
insert into tb_emp8(name,salary) values(
'Lucy'
,
1000
),(
'lura'
,
1200
),(
'Kevin'
,
1500
);
id
自增
查看表结构: desc tb_emp8; 或者show create table tb_emp8\G
修改数据表alter
修改表名: alter table tb_dept2 rename tb_deptment3;
修改字段类型: alter table tb_dept1 modify name varchar(
30
);
修改字段名: alter table tb_dept1 change location loc varchar(
50
);
添加字段: alter table tb_dept1 add managerId
int
(
10
);
添加有约束条件的字段: alter table tb_dept1 add column1 varchar(
12
)
not
null;
在某个位置添加字段: alter table tb_dept1 add column2
int
(
11
) first;
在某个字段后面添加新字段: alter table tb_dept1 add column3
int
(
11
) after name;
删除字段: alter table tb_dept1 drop column2;
修改表的存储引擎: alter table tb_deptment3 engine
=
MyISAM;
删除外键约束: alter table tb_emp9 drop foreign key fk_emp_dept;
删除数据表: drop table
if
exists tb_dept2;
删除外键关联的主表，需要首先取消外键关联，否则删除主表失败
数据类型
整形: TINYINT
1
个字节
2
*
*
8
-
1
=
255
个值
整形: smallint
2
个字节
整形:
int
4
个字节
整形: bigint
8
个字节
浮点数
单精度:
float
4
个字节
双精度: double
8
个字节
decimal: 不固定，一般用于财务系统
日期时间类型
year:
1
个字节,格式 YYYY
time:
3
个字节,格式 HH:MM:SS
date:
3
个字节,格式 YYYY
-
MM
-
DD
datetime:
8
个字节，格式 YYYY
-
MM
-
DD HH:MM:SS
timestamp:
4
个字节，格式YYYY
-
MM
-
DD HH:MM:SS
字符串类型
char(n): 固定长度字符串
#浪费内存，但查询速度快
varchar(n): 非固定长度字符串
#节省内存，但查询速度慢
text: 存放文本
longtext: 存放大数据文本
between ..
and
.. 关键字使用
select
4
between
4
and
6
,
4
between
4
and
6
,
12
between
9
and
10
;
in
,
not
in
关键字使用
select
2
in
(
1
,
3
,
5
,
'thks'
),
'thks'
in
(
1
,
3
,
5
,
'thks'
);
like用来匹配字符串
'%'
: 匹配任何数目的字符
'_'
: 只能匹配一个字符
select查询数据
create table fruits (
-
> f_id char(
10
)
not
null,
-
> s_id
int
not
null,
-
> f_name char(
255
)
not
null,
-
> f_price decimal(
8
,
2
)
not
null,
-
> primary key(f_id)
-
> ) engine
-
> ) engine
=
innodb
=
utf8;
插入字段:
insert into fruits(f_id,s_id,f_name,f_price) values
-
> (
'a1'
,
101
,
'apple'
,
5.2
),
-
> (
'b1'
,
102
,
'blackberry'
,
10.2
),
-
> (
'bs1'
,
102
,
'orange'
,
11.2
),
-
> (
'bs2'
,
105
,
'melon'
,
8.2
),
-
> (
't1'
,
102
,
'banana'
,
10.3
),
-
> (
't2'
,
102
,
'grape'
,
5.3
),
-
> (
'o2'
,
103
,
'coconut'
,
9.2
),
-
> (
'c0'
,
101
,
'cherry'
,
3.2
),
-
> (
'a2'
,
103
,
'apricot'
,
2.2
),
-
> (
'l2'
,
104
,
'lemon'
,
6.4
),
-
> (
'b2'
,
104
,
'lemon'
,
7.6
),
-
> (
'm1'
,
106
,
'mango'
,
15.6
),
-
> (
'm2'
,
105
,
'xbabay'
,
2.6
),
-
> (
't4'
,
107
,
'xbababa'
,
3.6
),
-
> (
'm3'
,
105
,
'xxtt'
,
11.6
),
-
> (
'b5'
,
107
,
'xxxx'
,
3.6
);
单表查询:
查询表: select f_id,f_name
from
fruits;
查询条件where:
select f_id,f_name
from
fruits where f_price
=
10.2
;
#等号 =
select
*
from
fruits where f_price <
10
;
#小于
select
*
from
fruits where s_id
in
(
101
,
102
) order by f_name (desc);
#in关键字,按f_name排序，desc降序，asc升序
select
*
from
fruits where f_price between
2.00
and
10.20
;
#between and
select
*
from
fruits where f_name like
'b%'
;
#like关键字匹配, %匹配任何多个字符
select
*
from
fruits where f_name like
'_____y'
;
#_匹配任意一个字符
select
*
from
fruits where s_id
=
'101'
and
f_price >
=
5
;
#and多条件匹配
select
*
from
fruits where s_id
=
'101'
or
s_id
=
102
;
#or多条件匹配
order by查询结果排序
select
*
from
fruits order by f_name;
select
*
from
fruits order by f_price desc;
#desc倒叙排列
group by分组
select s_id,count(
*
) as Total
from
fruits group by s_id;
#根据s_id分组,s_id相同的数量
select s_id,count(
*
) as Total
from
fruits group by s_id having count(f_name) >
1
;
#having后面加上查询条件
limit限制查询的数量
select
*
from
fruits limit
4
;
#查询四条
select
*
from
fruits limit
4
,
3
;
#索引为4，从第五条开始返回3条
inner join 内连接,返回两表中都有的记录
create table suppliers (
-
> s_id
int
(
11
)
not
null auto_increment primary key,
-
> s_name char(
50
)
not
null,
-
> s_city char(
50
),
-
> s_zip char(
10
),
-
> s_call char(
50
)
not
null
-
> )engine
=
innodb default charset
=
utf8;
以下操作是fruits和suppliers关联
select suppliers.s_id, s_name, f_name, f_price
from
fruits inner join suppliers on
-
> fruits.s_id
=
suppliers.s_id;
#on后面是条件查询，
left join 返回包括左表中的所有记录和右表连接字段的所有记录
select s_name,f_price
from
fruits left join suppliers on fruits.s_id
=
suppliers.s_id;
子查询:
select s_id, f_name
from
fruits where s_id
=
(select s1.s_id
from
suppliers as s1 where s1.s_city
=
'Tianjin'
);
union合并查询结果并去重
union
all
合并查询不去重
select s_id ,f_name,f_price
from
fruits where f_price <
9.0
union
all
select s_id,f_name,f_price
from
fruits where s_id
in
(
101
,
103
);
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
|
二、pymysql模块操作数据库
例子:
#!/usr/bin/python
# --*-- coding:utf-8 --*--
import
pymysql
conn
=
pymysql.Connect(host
=
'127.0.0.1'
,user
=
'root'
,password
=
'aixocm'
,port
=
3306
,database
=
'oldboy'
,charset
=
'utf8'
)
cursor
=
conn.cursor()
v
=
cursor.execute(
'select * from student'
)
print
(v)
#cursor.fetchone() #获取一条数据
#cursor.fetchmany(2) #获取多条数据
result
=
cursor.fetchall()
#获取所有数据
print
(result)
cursor.close()
conn.close()
#!/usr/bin/python
# --*-- coding:utf-8 --*--
import
pymysql
conn
=
pymysql.Connect(host
=
'127.0.0.1'
,user
=
'root'
,password
=
'aixocm'
,port
=
3306
,database
=
'oldboy'
,charset
=
'utf8'
)
cursor
=
conn.cursor()
#v = cursor.execute('insert into student(name) values("test")')
#print(v)
v
=
cursor.execute(
'delete from student where sid=7'
)
conn.commit()
#提交事务
cursor.close()
conn.close()
#!/usr/bin/python
# --*-- coding:utf-8 --*--
import
pymysql
num
=
8
conn
=
pymysql.Connect(host
=
'127.0.0.1'
,user
=
'root'
,password
=
'aixocm'
,port
=
3306
,database
=
'oldboy'
,charset
=
'utf8'
)
cursor
=
conn.cursor()
#v = cursor.execute('insert into student(name) values("test")')
#print(v)
v
=
cursor.execute(
'delete from student where sid=%d'
%
(num))
#防止sql注入
conn.commit()
cursor.close()
conn.close()
|
本文转自小白的希望 51CTO博客，原文链接：http://blog.51cto.com/haoyonghui/1945907
，如需转载请自行联系原作者
