Python中操作mysql知识（二）-创新互联

1.创建表Teacher：

create table Teacher( teaId int not null, teaname varchar(100), age int, sex enum('M', 'F'), phone int);

注意：

创新互联-成都网站建设公司，专注成都网站设计、网站制作、网站营销推广，域名申请，网络空间，网站托管有关企业网站制作方案、改版、费用等问题，请联系创新互联。

char 和varchar区别：

‘123’ ------>varchar(10) # 3位

'123 ' -------> char(10) # 10位不足10位空格补全

查看新建的Teacher表：

MariaDB [(none)]> use test; Database changed MariaDB [test]> show tables; +----------------+ | Tables_in_test | +----------------+ | Teacher | +----------------+ 1 row in set (0.00 sec)

2.建立超级用户：

grant all privileges on *.* to 'fxq'@'%' identified by '123456' with grant option;

3.插入数据：

insert into Teacher(teaid,teaname,age) values(1,'feng',20);

插入多行数据：

insert into Teater(teaid,teaname,age) values(101,'fengxiaoqing',20),(102,'zhangsan',30),(103,'wangwu',40);

查看插入的数据：

4.数据查询：

select * from Teacher where teaId > 4; select * from Teacher where teaId in(1,4,101); select * from Teacher where teaId like ('%1%');

测试结果：

MariaDB [test]> select * from Teacher where teaId > 3; +-------+--------------+------+------+------------+ | teaId | teaname | age | sex | phone | +-------+--------------+------+------+------------+ | 4 | li | 40 | M | 2147483647 | | 5 | zhao | 50 | F | 2147483647 | | 5 | zhao | 50 | F | 1821113120 | | 101 | fengxiaoqing | 20 | NULL | NULL | | 102 | zhangsan | 30 | NULL | NULL | +-------+--------------+------+------+------------+ 5 rows in set (0.00 sec) MariaDB [test]> select * from Teacher where teaId in(1,4,101); +-------+--------------+------+------+------------+ | teaId | teaname | age | sex | phone | +-------+--------------+------+------+------------+ | 1 | feng | 20 | NULL | 188188188 | | 4 | li | 40 | M | 2147483647 | | 101 | fengxiaoqing | 20 | NULL | NULL | +-------+--------------+------+------+------------+ 3 rows in set (0.01 sec) MariaDB [test]> select * from Teacher where teaId like ('%1%'); +-------+--------------+------+------+-----------+ | teaId | teaname | age | sex | phone | +-------+--------------+------+------+-----------+ | 1 | feng | 20 | NULL | 188188188 | | 101 | fengxiaoqing | 20 | NULL | NULL | | 102 | zhangsan | 30 | NULL | NULL | +-------+--------------+------+------+-----------+ 3 rows in set (0.00 sec) MariaDB [test]> MariaDB [test]>

查询重复数据：

select * from Teacher group by teaname having count(*) >1;

测试结果：

MariaDB [test]> select * from Teacher; +-------+--------------+------+------+------------+ | teaId | teaname | age | sex | phone | +-------+--------------+------+------+------------+ | 1 | feng | 20 | NULL | 188188188 | | 2 | wang | 20 | M | 2147483647 | | 2 | wang | 20 | M | 2147483647 | | 3 | zhang | 30 | M | 2147483647 | | 4 | li | 40 | M | 2147483647 | | 5 | zhao | 50 | F | 2147483647 | | 5 | zhao | 50 | F | 1821113120 | | 101 | fengxiaoqing | 20 | NULL | NULL | | 102 | zhangsan | 30 | NULL | NULL | +-------+--------------+------+------+------------+ 9 rows in set (0.01 sec) MariaDB [test]> select * from Teacher group by teaname having count(*) >1; +-------+---------+------+------+------------+ | teaId | teaname | age | sex | phone | +-------+---------+------+------+------------+ | 2 | wang | 20 | M | 2147483647 | | 5 | zhao | 50 | F | 2147483647 | +-------+---------+------+------+------------+ 2 rows in set (0.00 sec) MariaDB [test]>

联合查询：

select * from a,c where a.id = c.组id

查看表结构：

show create Teacher\G; desc Teacher;

测试结果：

MariaDB [test]> show create table Teacher \G; *************************** 1. row *************************** Table: Teacher Create Table: CREATE TABLE `Teacher` ( `teaId` int(11) NOT NULL, `teaname` varchar(100) DEFAULT NULL, `age` int(11) DEFAULT NULL, `sex` enum('M','F') DEFAULT NULL, `phone` int(11) DEFAULT NULL, KEY `test_Teacher_teaId_teaname` (`teaId`,`teaname`) ) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec) ERROR: No query specified MariaDB [test]> MariaDB [test]> desc Teacher; +---------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+---------------+------+-----+---------+-------+ | teaId | int(11) | NO | MUL | NULL | | | teaname | varchar(100) | YES | | NULL | | | age | int(11) | YES | | NULL | | | sex | enum('M','F') | YES | | NULL | | | phone | int(11) | YES | | NULL | | +---------+---------------+------+-----+---------+-------+ 5 rows in set (0.01 sec) MariaDB [test]>

5.删除数据：

delete from Teacher where teaid='103' #删除指定id数据truncate Teacher1; #清空数据drop table Teacher1; #删除表

测试结果：

delete:删除表中指定数据条目

MariaDB [test]> delete from Teacher where teaid='103'; Query OK, 1 row affected (0.02 sec) MariaDB [test]> select * from Teacher; +-------+--------------+------+------+------------+ | teaId | teaname | age | sex | phone | +-------+--------------+------+------+------------+ | 1 | feng | 20 | NULL | NULL | | 2 | wang | 20 | M | 2147483647 | | 2 | wang | 20 | M | 2147483647 | | 3 | zhang | 30 | M | 2147483647 | | 4 | li | 40 | M | 2147483647 | | 5 | zhao | 50 | F | 2147483647 | | 5 | zhao | 50 | F | 1821113120 | | 101 | fengxiaoqing | 20 | NULL | NULL | | 102 | zhangsan | 30 | NULL | NULL | +-------+--------------+------+------+------------+ 9 rows in set (0.00 sec) MariaDB [test]>

truncate: 清空表数据，表结构保留

drop: 删除整个表

MariaDB [test]> drop table Teacher1; Query OK, 0 rows affected (0.01 sec) MariaDB [test]> show tables; +----------------+ | Tables_in_test | +----------------+ | Teacher | +----------------+ 1 row in set (0.00 sec) MariaDB [test]>

6.更新数据

update Teacher set phone=188188188 where teaId=1;

测试结果：

MariaDB [test]> select * from Teacher; +-------+--------------+------+------+------------+ | teaId | teaname | age | sex | phone | +-------+--------------+------+------+------------+ | 1 | feng | 20 | NULL | NULL | | 2 | wang | 20 | M | 2147483647 | | 2 | wang | 20 | M | 2147483647 | | 3 | zhang | 30 | M | 2147483647 | | 4 | li | 40 | M | 2147483647 | | 5 | zhao | 50 | F | 2147483647 | | 5 | zhao | 50 | F | 1821113120 | | 101 | fengxiaoqing | 20 | NULL | NULL | | 102 | zhangsan | 30 | NULL | NULL | +-------+--------------+------+------+------------+ 9 rows in set (0.01 sec) MariaDB [test]> update Teacher set phone=188188188 where teaId=1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [test]> select * from Teacher; +-------+--------------+------+------+------------+ | teaId | teaname | age | sex | phone | +-------+--------------+------+------+------------+ | 1 | feng | 20 | NULL | 188188188 | | 2 | wang | 20 | M | 2147483647 | | 2 | wang | 20 | M | 2147483647 | | 3 | zhang | 30 | M | 2147483647 | | 4 | li | 40 | M | 2147483647 | | 5 | zhao | 50 | F | 2147483647 | | 5 | zhao | 50 | F | 1821113120 | | 101 | fengxiaoqing | 20 | NULL | NULL | | 102 | zhangsan | 30 | NULL | NULL | +-------+--------------+------+------+------------+ 9 rows in set (0.00 sec) MariaDB [test]>

7.创建索引：

create index 库名_表名_列名1_列名2 （列名1，列名2）; create index test_Teacher_teaId_teaname (teaId,teaname);

查看表有无索引：

MariaDB [test]> show index from Teacher; Empty set (0.01 sec)

创建索引：

查看是否走索引：

explain select * from Teacher where teaname = 'feng';

另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。

分享名称：Python中操作mysql知识（二）-创新互联
新闻来源：http://myzitong.com/article/dgheic.html

Python中操作mysql知识（二）-创新互联

1.创建表Teacher：

2.建立超级用户：

3.插入数据：

插入多行数据：

查看插入的数据：

4.数据查询：

测试结果：

查询重复数据：

测试结果：

联合查询：

查看表结构：

测试结果 ：

5.删除数据：

测试结果：

delete:删除表中指定数据条目

truncate: 清空表数据，表结构保留

drop: 删除整个表

6.更新数据

测试结果：

7.创建索引：

查看表有无索引：

创建索引：

查看是否走索引：

其他资讯

测试结果：