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知识（二）-创新互联
当前URL：
http://bzwzjz.com/article/dgheic.html
