重庆分公司
重庆分公司，新征程启航
为企业提供网站建设、域名注册、服务器等服务
Python中操作mysql知识（二）
1.创建表Teacher：
create table Teacher(
teaId int not null,
teaname varchar(100),
age int,
sex enum('M', 'F'),
phone int);
创新互联公司专注于企业全网营销推广、网站重做改版、婺城网站定制设计、自适应品牌网站建设、H5页面制作、商城网站建设、集团公司官网建设、外贸网站建设、高端网站制作、响应式网页设计等建站业务，价格优惠性价比高，为婺城等各大城市提供网站开发制作服务。
注意：
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]>
标题名称：Python中操作mysql知识（二）
文章路径：http://cqcxhl.cn/article/pcshhc.html