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 changedMariaDB [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: TeacherCreate 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 specifiedMariaDB [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: 0MariaDB [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]>