管理MySQL的命令
以下列出了使用Mysql数据库过程中常用的命令:
select*form user\G;查看详情,\G行列 规则显示
-
USE 数据库名 :选择要操作的Mysql数据库,使用该命令后所有Mysql命令都只针对该数据库。
-
SHOW DATABASES: 列出 MySQL 数据库管理系统的数据库列表。
-
SHOW TABLES: #显示指定数据库的所有表,使用该命令前需要使用 use命令来选择要操作的数据库。
-
SHOW COLUMNS FROM 数据表: #显示数据表的属性,属性类型,主键信息 ,是否为 NULL,默认值等其他信息。
-
等于desc user;
-
create database testdb charset "utf8"; #创建一个叫testdb的数据库,且让其支持中文
-
drop database testdb; #删除数据库
-
SHOW INDE
5. mysql 常用命令
MySQL 创建数据表
语法
1CREATE
TABLE
table_name (column_name column_type);
创建一个student表
1234567create
table
student(
stu_id
INT
NOT
NULL
AUTO_INCREMENT,
name
CHAR
(32)
NOT
NULL
,
age
INT
NOT
NULL
,
register_date
DATE
,
PRIMARY
KEY
( stu_id )
);
实例解析:
- 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL, 在操作数据库时如果输入该字段的数据为NULL ,就会报错。
- AUTO_INCREMENT定义列为自增的属性,一般用于主键,数值会自动加1。
- PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键,列间以逗号分隔。
MySQL 插入数据
语法
123INSERT
INTO
table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );
插入数据
1234567891011mysql>
insert
into
student (
name
,age,register_date)
values
(
"alex li"
,22,
"2016-03-4"
)
-> ;
Query OK, 1 row affected (0.00 sec)
mysql>
select
*
from
student;
+
--------+---------+-----+---------------+
| stu_id |
name
| age | register_date |
+
--------+---------+-----+---------------+
| 1 | alex li | 22 | 2016-03-04 |
+
--------+---------+-----+---------------+
1 row
in
set
(0.00 sec)
MySQL 查询数据
语法
1234SELECT
column_name,column_name
FROM
table_name
[
WHERE
Clause]
[OFFSET M ][LIMIT N]
- 查询语句中你可以使用一个或者多个表,表之间使用逗号(,)分割,并使用WHERE语句来设定查询条件。
- SELECT 命令可以读取一条或者多条记录。
- 你可以使用星号(*)来代替其他字段,SELECT语句会返回表的所有字段数据
- 你可以使用 WHERE 语句来包含任何条件。
- 你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
- 你可以使用 LIMIT 属性来设定返回的记录数。
1234567891011121314151617181920mysql>
select
*
from
student limit 3 offset 2;
+
--------+---------+-----+---------------+
| stu_id |
name
| age | register_date |
+
--------+---------+-----+---------------+
| 3 | alex li | 24 | 2016-03-04 |
| 4 | alex li | 24 | 2016-03-01 |
| 5 | alex li | 24 | 2016-03-02 |
+
--------+---------+-----+---------------+
3
rows
in
set
(0.00 sec)
比如这个SQL ,limit后面跟的是3条数据,offset后面是从第3条开始读取
mysql>
select
*
from
student limit 3 ,1;
+
--------+---------+-----+---------------+
| stu_id |
name
| age | register_date |
+
--------+---------+-----+---------------+
| 4 | alex li | 24 | 2016-03-01 |
+
--------+---------+-----+---------------+
1 row
in
set
(0.00 sec)
而这个SQL,limit后面是从第3条开始读,读取1条信息。
MySQL where 子句
语法
12SELECT
field1, field2,...fieldN
FROM
table_name1, table_name2...
[
WHERE
condition1 [
AND
[
OR
]] condition2.....
以下为操作符列表,可用于 WHERE 子句中。
下表中实例假定 A为10 B为20
操作符 描述 实例 = 等号,检测两个值是否相等,如果相等返回true (A = B) 返回false。 <>, != 不等于,检测两个值是否相等,如果不相等返回true (A != B) 返回 true。 > 大于号,检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true (A > B) 返回false。 < 小于号,检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true (A < B) 返回 true。 >= 大于等于号,检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true (A >= B) 返回false。 <= 小于等于号,检测左边的值是否小于于或等于右边的值, 如果左边的值小于或等于右边的值返回true (A <= B) 返回 true。 使用主键来作为 WHERE 子句的条件查询是非常快速的。
1select
*
from
student
where
register_date >
'2016-03-04'
;
MySQL UPDATE 查询
语法
12UPDATE
table_name
SET
field1=new-value1, field2=new-value2
[
WHERE
Clause]
1update
student
set
age=22 ,
name
=
"Alex Li"
where
stu_id>3;
MySQL DELETE 语句
语法
1DELETE
FROM
table_name [
WHERE
Clause]<br><br>
delete
from
student
where
stu_id=5;
MySQL LIKE 子句
语法
123456SELECT
field1, field2,...fieldN table_name1, table_name2...
WHERE
field1
LIKE
condition1 [
AND
[
OR
]] filed2 =
'somevalue'
select
*
from
student
where
name
binary
like
"%Li"
;
select
*
from
student
where
name
binary
like
binary
"%Li"
; #只匹配大写
MySQL 排序
1234SELECT
field1, field2,...fieldN table_name1, table_name2...
ORDER
BY
field1, [field2...] [
ASC
[
DESC
]]
使用
ASC
或
DESC
关键字来设置查询结果是按升序或降序排列。 默认情况下,它是按升序排列。
select
*
from
student
where
name
like
binary
"%Li"
order
by
stu_id
desc
;
MySQL GROUP BY 语句
1234SELECT
column_name,
function
(column_name)
FROM
table_name
WHERE
column_name operator value
GROUP
BY
column_name;
示例
12345678910111213141516171819202122232425262728293031323334353637mysql>
SELECT
*
FROM
employee_tbl;
+
----+--------+---------------------+--------+
| id |
name
|
date
| singin |
+
----+--------+---------------------+--------+
| 1 | 小明 | 2016-04-22 15:25:33 | 1 |
| 2 | 小王 | 2016-04-20 15:25:47 | 3 |
| 3 | 小丽 | 2016-04-19 15:26:02 | 2 |
| 4 | 小王 | 2016-04-07 15:26:14 | 4 |
| 5 | 小明 | 2016-04-11 15:26:40 | 4 |
| 6 | 小明 | 2016-04-04 15:26:54 | 2 |
+
----+--------+---------------------+--------+
接下来我们使用
GROUP
BY
语句 将数据表按名字进行分组,并统计每个人有多少条记录:
mysql>
SELECT
name
,
COUNT
(*)
FROM
employee_tbl
GROUP
BY
name
;
+
--------+----------+
|
name
|
COUNT
(*) |
+
--------+----------+
| 小丽 | 1 |
| 小明 | 3 |
| 小王 | 2 |
+
--------+----------+
3
rows
in
set
(0.01 sec)
使用
WITH
ROLLUP
mysql>
SELECT
name
,
SUM
(singin)
as
singin_count
FROM
employee_tbl
GROUP
BY
name
WITH
ROLLUP
;
+
--------+--------------+
|
name
| singin_count |
+
--------+--------------+
| 小丽 | 2 |
| 小明 | 7 |
| 小王 | 7 |
|
NULL
| 16 |
+
--------+--------------+
4
rows
in
set
(0.00 sec)
其中记录
NULL
表示所有人的登录次数。<br>
我们可以使用
coalesce
来设置一个可以取代
NUll
的名称,
coalesce
语法:
mysql>
SELECT
coalesce
(
name
,
'总数'
),
SUM
(singin)
as
singin_count
FROM
employee_tbl
GROUP
BY
name
WITH
ROLLUP
;
MySQL ALTER命令
我们需要修改数据表名或者修改数据表字段时,就需要使用到MySQL ALTER命令。
删除,添加或修改表字段
1alter
table
student
drop
register_date; #从student表删除register_date 字段
alter
table
student
add
phone
int
(11)
not
null
; #添加phone字段
修改字段类型及名称
如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。
例如,把字段 c 的类型从 CHAR(1) 改为 CHAR(10),可以执行以下命令:
1mysql>
ALTER
TABLE
testalter_tbl
MODIFY
c
CHAR
(10);
使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后,紧跟着的是你要修改的字段名,然后指定新字段名及类型。尝试如下实例:
123mysql>
ALTER
TABLE
testalter_tbl CHANGE i j
BIGINT
;
mysql>
ALTER
TABLE
testalter_tbl CHANGE j j
INT
;
ALTER TABLE 对 Null 值和默认值的影响
当你修改字段时,你可以指定是否包含只或者是否设置默认值。
以下实例,指定字段 j 为 NOT NULL 且默认值为100 。
12mysql>
ALTER
TABLE
testalter_tbl
->
MODIFY
j
BIGINT
NOT
NULL
DEFAULT
100;
修改表名
1mysql>
ALTER
TABLE
testalter_tbl RENAME
TO
alter_tbl;
pymsql
pymsql是Python中操作MySQL的模块,其使用方法和MySQLdb几乎相同。
下载安装
1pip3 install pymysql
使用操作
1、执行SQL
1234567891011121314151617181920212223242526#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
# 创建连接
conn = pymysql.
connect
(host=
'127.0.0.1'
, port=3306,
user
=
'root'
, passwd=
'123'
, db=
't1'
)
# 创建游标
cursor
= conn.
cursor
()
# 执行SQL,并返回收影响行数
effect_row =
cursor
.
execute
(
"update hosts set host = '1.1.1.2'"
)
# 执行SQL,并返回受影响行数
#effect_row =
cursor
.
execute
(
"update hosts set host = '1.1.1.2' where nid > %s"
, (1,))
# 执行SQL,并返回受影响行数
#effect_row =
cursor
.executemany(
"insert into hosts(host,color_id)values(%s,%s)"
, [(
"1.1.1.11"
,1),(
"1.1.1.11"
,2)])
# 提交,不然无法保存新建或者修改的数据
conn.
commit
()
# 关闭游标
cursor
.
close
()
# 关闭连接
conn.
close
()
2、获取新创建数据自增ID
12345678910111213#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
conn = pymysql.
connect
(host=
'127.0.0.1'
, port=3306,
user
=
'root'
, passwd=
'123'
, db=
't1'
)
cursor
= conn.
cursor
()
cursor
.executemany(
"insert into hosts(host,color_id)values(%s,%s)"
, [(
"1.1.1.11"
,1),(
"1.1.1.11"
,2)])
conn.
commit
()
cursor
.
close
()
conn.
close
()
# 获取最新自增ID
new_id =
cursor
.lastrowid
3、获取查询数据
12345678910111213141516171819#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
conn = pymysql.
connect
(host=
'127.0.0.1'
, port=3306,
user
=
'root'
, passwd=
'123'
, db=
't1'
)
cursor
= conn.
cursor
()
cursor
.
execute
(
"select * from hosts"
)
# 获取第一行数据
row_1 =
cursor
.fetchone()
# 获取前n行数据
# row_2 =
cursor
.fetchmany(3)
# 获取所有数据
# row_3 =
cursor
.fetchall()
conn.
commit
()
cursor
.
close
()
conn.
close
()
注:在fetch数据时按照顺序进行,可以使用cursor.scroll(num,mode)来移动游标位置,如:
- cursor.scroll(1,mode='relative') # 相对当前位置移动
- cursor.scroll(2,mode='absolute') # 相对绝对位置移动
Mysql 连接(left join, right join, inner join ,full join)
我们已经学会了如果在一张表中读取数据,这是相对简单的,但是在真正的应用中经常需要从多个数据表中读取数据。
本章节我们将向大家介绍如何使用 MySQL 的 JOIN 在两个或多个表中查询数据。
你可以在SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。
JOIN 按照功能大致分为如下三类:
- INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录。
- LEFT JOIN(左连接):获取左表所有记录,即使右表没有对应匹配的记录。
- RIGHT JOIN(右连接): 与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。
Suppose you have two tables, with a single column each, and data as follows:
123456A B
- -
1 3
2 4
3 5
4 6
Inner join
An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.
1234567select
*
from
a
INNER
JOIN
b
on
a.a = b.b;
select
a.*,b.*
from
a,b
where
a.a = b.b;
a | b
--+--
3 | 3
4 | 4
其实就是只显示2个表的交集
Left join
A left join will give all rows in A, plus any common rows in B.
12345678select
*
from
a
LEFT
JOIN
b
on
a.a = b.b;
a | b
--+-----
1 |
null
2 |
null
3 | 3
4 | 4
Right join
A right join will give all rows in B, plus any common rows in A.
12345678select
*
from
a
RIGHT
JOIN
b
on
a.a = b.b;
a | b
-----+----
3 | 3
4 | 4
null
| 5
null
| 6
Full join
A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa
12345678910select
*
from
a
FULL
JOIN
b
on
a.a = b.b;
a | b
-----+-----
1 |
null
2 |
null
3 | 3
4 | 4
null
| 6
null
| 5
mysql 并不直接支持full join,but 总是难不到我们
123456789101112select
*
from
a
left
join
b
on
a.a = b.b
UNION
select
*
from
a
right
join
b
on
a.a = b.b;
+
------+------+
| a | b |
+
------+------+
| 3 | 3 |
| 4 | 4 |
| 1 |
NULL
|
| 2 |
NULL
|
|
NULL
| 5 |
|
NULL
| 6 |
+
------+------+
6
rows
in
set
(0.00 sec)