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);查看插入的数据：
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 | | 103 | wangwu | 40 | NULL | NULL | +-------+--------------+------+------+------------+ 10 rows in set (0.00 sec) MariaDB [test]>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: 清空表数据，表结构保留
MariaDB [test]> select * from Teacher1; +-------+---------------+------+------+-------+ | teaId | teaname | age | sex | phone | +-------+---------------+------+------+-------+ | 101 | fengxiaoqing | 20 | NULL | NULL | | 102 | zhangsan | 30 | NULL | NULL | | 103 | wangwu | 40 | NULL | NULL | | 104 | fengxiaoqing1 | 20 | NULL | NULL | | 105 | zhangsan2 | 30 | NULL | NULL | | 106 | wangwu3 | 40 | NULL | NULL | | 107 | fengxiaoqing4 | 20 | NULL | NULL | | 108 | zhangsan5 | 30 | NULL | NULL | | 109 | wangwu6 | 40 | NULL | NULL | | 110 | fengxiaoqing7 | 20 | NULL | NULL | | 111 | zhangsan8 | 30 | NULL | NULL | | 112 | wangwu9 | 40 | NULL | NULL | +-------+---------------+------+------+-------+ 12 rows in set (0.00 sec) MariaDB [test]> truncate Teacher1; Query OK, 0 rows affected (0.02 sec) MariaDB [test]> select * from Teacher1; Empty set (0.00 sec) MariaDB [test]>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)创建索引：
MariaDB [test]> show index from Teacher; #查看Empty set (0.01 sec) MariaDB [test]> alter table Teacher add index test_Teacher_teaId_teaname (teaId,teaname); #创建索引 Query OK, 0 rows affected (0.34 sec) Records: 0 Duplicates: 0 Warnings: 0MariaDB [test]> show index from Teacher; #再查看，索引就会创建成功 +---------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Teacher | 1 | test_Teacher_teaId_teaname | 1 | teaId | A | 9 | NULL | NULL | | BTREE | | | | Teacher | 1 | test_Teacher_teaId_teaname | 2 | teaname | A | 9 | NULL | NULL | YES | BTREE | | | +---------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec) MariaDB [test]>查看是否走索引：
explain select * from Teacher where teaname = 'feng';MariaDB [test]> explain select * from Teacher where teaname = 'feng'; +------+-------------+---------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+---------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | Teacher | ALL | NULL | NULL | NULL | NULL | 9 | Using where | +------+-------------+---------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) MariaDB [test]> explain select * from Teacher where teaname = 'feng' and teaId = 1; +------+-------------+---------+------+----------------------------+----------------------------+---------+-------------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+---------+------+----------------------------+----------------------------+---------+-------------+------+-----------------------+ | 1 | SIMPLE | Teacher | ref | test_Teacher_teaId_teaname | test_Teacher_teaId_teaname | 107 | const,const | 1 | Using index condition | +------+-------------+---------+------+----------------------------+----------------------------+---------+-------------+------+-----------------------+ 1 row in set (0.01 sec) MariaDB [test]> MariaDB [test]> select * from Teacher where teaname = 'feng' and teaId = 1; +-------+---------+------+------+-----------+ | teaId | teaname | age | sex | phone | +-------+---------+------+------+-----------+ | 1 | feng | 20 | NULL | 188188188 | +-------+---------+------+------+-----------+ 1 row in set (0.01 sec) MariaDB [test]>另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。
网站标题：Python中操作mysql知识（二）-创新互联
文章来源：http://pwwzsj.com/article/dgheic.html
