主键的创建方式
1.
create tablestud(
idint not null unique,
namevarchar(20)
);
mysql> descstud;+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
2.
create tablestud2(
idint,
namevarchar(20),primary key(id)
);
mysql> descstud2;+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
3.
create tablestud3(
idint not null unique,
namevarchar(20) not null unique);
mysql> descstud3;+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | NO | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
4.
create tablestud4(
namevarchar(20) not null unique,
idint not null unique);
mysql> descstud4;+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(20) | NO | PRI | NULL | |
| id | int(11) | NO | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
总结：
1.每张表只能有一个主键
2.每张表不一定只有一个非空且唯一的字段
3.如果表中只有一个非空且唯一的字段，那他就是主键；如果表中不只有一个非空且唯一的字段那么第一个非空且唯一的字段就是主键
联合主键
create tablebook2author (
idint not null uniqueauto_increment,
book_idint,
author_idint,primary key(book_id,author_id)
)；
mysql> descbook2author;+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | UNI | NULL | auto_increment |
| book_id | int(11) | NO | PRI | NULL | |
| author_id | int(11) | NO | PRI | NULL | |
+-----------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
使用insert插入值，效果：
mysql> select * from book2author;
+----+---------+-----------+
| id | book_id | author_id |
+----+---------+-----------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
| 4 | 1 | 2 |
| 5 | 1 | 3 |
+----+---------+-----------+
5 rows in set (0.00 sec)
可以理解为：ID号为1的书由ID号为1,2,3的三个作者共同创作完成。
数据库存储引擎
什么是存储引擎
mysql中建立的库===>文件夹
库中建立的表===>文件
现实生活中我们用来存储数据的文件应该有不同的类型：比如存文本用txt类型，存表格用excel，存图片用png等
数据库中的表也应该有不同的类型，表的类型不同，会对应mysql不同的存取机制，表类型又称为存储引擎。
存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方
法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型(即存储和
操作此表的类型)
mysql支持的存储引擎
MySQL常用的存储引擎
MyISAM存储引擎
由于该存储引擎不支持事务、也不支持外键，所以访问速度较快。因此当对事务完整性没有要求并以访问为主的应用适合使用该存储引擎。
InnoDB存储引擎(主要使用)
由于该存储引擎在事务上具有优势，即支持具有提交、回滚及崩溃恢复能力等事务特性，所以比MyISAM存储引擎占用更多的磁盘空间。因此当需要频繁的更新、删除操作，同时还对事务的完整性要求较高，需要实现并发控制，建议选择。
MEMORY
MEMORY存储引擎存储数据的位置是内存，因此访问速度最快，但是安全上没有保障。适合于需要快速的访问或临时表。
BLACKHOLE
黑洞存储引擎，可以应用于主备复制中的分发主库。
使用存储引擎
方法1：建表时指定
MariaDB [db1]> create table innodb_t1(id int,name char)engine=innodb;
MariaDB[db1]> create table innodb_t2(id int)engine=innodb;
MariaDB[db1]> show create tableinnodb_t1;
MariaDB[db1]> show create table innodb_t2;
方法2：在配置文件中指定默认的存储引擎
/etc/f[mysqld]
default-storage-engine=INNODB
innodb_file_per_table=1
查看
[root@egon db1]# cd /var/lib/mysql/db1/
[root@egon db1]# ls
db.opt innodb_t1.frm innodb_t1.ibd innodb_t2.frm innodb_t2.ibd
练习
创建四个表，分别使用innodb，myisam，memory，blackhole存储引擎，进行插入数据测试
MariaDB[db1]> create table t1(id int)engine=innodb;
MariaDB[db1]> create table t2(id int)engine=myisam;
MariaDB[db1]> create table t3(id int)engine=memory;
MariaDB[db1]> create table t4(id int)engine=blackhole;
MariaDB[db1]>quit[root@egon db1]# ls /var/lib/mysql/db1/#发现后两种存储引擎只有表结构，无数据
db.opt t1.frm t1.ibd t2.MYD t2.MYI t2.frm t3.frm t4.frm
#memory，在重启mysql或者重启机器后，表内数据清空
#blackhole，往表内插入任何数据，都相当于丢入黑洞，表内永远不存记录
存储过程
存储过程是一个SQL语句集合，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行。
创建存储过程
--创建存储过程
delimiter//
create procedurep1()BEGIN
select * fromt1;END//delimiter ;--执行存储过程
call p1()
对于存储过程，可以接收参数，其参数有三类：
in仅用于传入参数用
out 仅用于返回值用
inout 既可以传入又可以当作返回值
有参数的存储过程：
--创建存储过程
delimiter \\create procedurep1(in i1 int,in i2 int,
inout i3int,
out r1int)BEGIN
DECLARE temp1 int;DECLARE temp2 int default 0;set temp1 = 1;set r1 = i1 + i2 + temp1 +temp2;set i3 = i3 + 100;end\\
delimiter ;--执行存储过程
set @t1 =4;set @t2 = 0;
CALL p1 (1, 2 ,@t1, @t2);SELECT @t1,@t2;
结果集：
delimiter //
create procedurep1()begin
select * fromv1;end //delimiter ;
结果集＋out：
delimiter //
create procedurep2(in n1 int,
inout n3int,
out n2int,
)begin
declare temp1 int;declare temp2 int default 0;select * fromv1;set n2 = n1 + 100;set n3 = n3 + n1 + 100;end //delimiter ;
事务：
delimiter \\create PROCEDUREp1(
OUT p_return_codetinyint)BEGIN
DECLARE exit handler forsqlexceptionBEGIN
--ERROR
set p_return_code = 1;rollback;END;DECLARE exit handler forsqlwarningBEGIN
--WARNING
set p_return_code = 2;rollback;END;
STARTTRANSACTION;DELETE fromtb1;insert into tb2(name)values('seven');COMMIT;--SUCCESS
set p_return_code = 0;END\\
delimiter ;
游标：
delimiter //
create procedurep3()begin
declare ssid int; --自定义变量1
declare ssname varchar(50); --自定义变量2
DECLARE done INT DEFAULTFALSE;DECLARE my_cursor CURSOR FOR select sid,sname fromstudent;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done =TRUE;openmy_cursor;
xxoo: LOOPfetch my_cursor intossid,ssname;if done thenleave xxoo;END IF;insert into teacher(tname) values(ssname);endloop xxoo;closemy_cursor;end //delimter ;
动态执行sql：
delimiter \\CREATE PROCEDUREp4 (in nid int)BEGIN
PREPARE prod FROM 'select * from student where sid > ?';EXECUTE prod USING @nid;DEALLOCATE prepareprod;END\\
delimiter ;
删除存储过程
drop procedure proc_name;
执行存储过程
--无参数
call proc_name()--有参数，全in
call proc_name(1,2)--有参数，有in，out，inout
set @t1=0;set @t2=3;
call proc_name(1,2,@t1,@t2)
索引
索引简介
索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。
索引优化应该是对查询性能优化最有效的手段了。
索引能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。
索引特点：创建与维护索引会消耗很多时间与磁盘空间，但查询速度大大提高！
索引语法
创建索引
--创建表时--语法：
CREATE TABLE表名 (
字段名1 数据类型[完整性约束条件…],
字段名2 数据类型[完整性约束条件…],[UNIQUE] INDEX | KEY
[索引名] (字段名[(长度)] [ASC |DESC])
);--------------------------------
--创建普通索引示例：
CREATE TABLEemp1 (
idINT,
nameVARCHAR(30) ,
resumeVARCHAR(50),INDEXindex_emp_name (name)--KEY index_dept_name (dept_name)
);--创建唯一索引示例：
CREATE TABLEemp2 (
idINT,
nameVARCHAR(30) ,
bank_numCHAR(18) UNIQUE,
resumeVARCHAR(50),UNIQUE INDEXindex_emp_name (name)
);--创建全文索引示例：
CREATE TABLEemp3 (
idINT,
nameVARCHAR(30) ,
resumeVARCHAR(50),
FULLTEXTINDEXindex_resume (resume)
);--创建多列索引示例：
CREATE TABLEemp4 (
idINT,
nameVARCHAR(30) ,
resumeVARCHAR(50),INDEXindex_name_resume (name,resume)
);---------------------------------
添加和删除索引
---添加索引
---CREATE在已存在的表上创建索引
CREATE [UNIQUE] INDEX索引名ON 表名 (字段名[(长度)] [ASC |DESC]) ;---ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQUE] INDEX索引名 (字段名[(长度)] [ASC |DESC]) ;CREATE INDEX index_emp_name onemp1(name);ALTER TABLE emp2 ADD UNIQUE INDEXindex_bank_num(band_num);--删除索引
语法：DROP INDEX 索引名 on表名DROP INDEX index_emp_name onemp1;DROP INDEX bank_num on emp2;
索引测试实验
--创建表
create table Indexdb.t1(id int,name varchar(20));--存储过程
delimiter $$create procedureautoinsert()BEGIN
declare i int default 1;while(i<500000)doinsert into Indexdb.t1 values(i,'yuan');set i=i+1;end while;END$$
delimiter ;--调用函数
call autoinsert();--花费时间比较：--创建索引前
select * from Indexdb.t1 where id=300000;--0.32s--添加索引
create index index_id onIndexdb.t1(id);--创建索引后
select * from Indexdb.t1 where id=300000;--0.00s
pymsql
pymsql是Python中操作MySQL的模块，其使用方法和MySQLdb几乎相同。
一、下载安装：
pip3 install pymysql
二、使用
1、执行SQL
import pymysql
# 创建连接
conn= pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
# 创建游标cursor = conn.cursor()
# 执行SQL，并返回收影响行数
effect_row= cursor.execute("update hosts set host = '1.1.1.2'")
# 执行SQL，并返回受影响行数
#effect_row= cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))
# 执行SQL，并返回受影响行数
#effect_row= cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
# 提交，不然无法保存新建或者修改的数据
mit()
# 关闭游标cursor.close()
# 关闭连接
conn.close()
2、获取新创建数据自增ID
import pymysql
conn= pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')cursor = conn.cursor()cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])
mit()cursor.close()
conn.close()
# 获取最新自增ID
new_id= cursor.lastrowid
3、获取查询数据
import pymysql
conn= pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')cursor = conn.cursor()cursor.execute("select * fromhosts")
# 获取第一行数据
row_1= cursor.fetchone()
# 获取前n行数据
# row_2= cursor.fetchmany(3)
# 获取所有数据
# row_3= cursor.fetchall()
mit()cursor.close()
conn.close()
注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：
cursor.scroll(1,mode='relative') # 相对当前位置移动
cursor.scroll(2,mode='absolute') # 相对绝对位置移动
4、fetch数据类型
关于默认获取的数据是元祖类型，如果想要或者字典类型的数据，即：
import pymysql
conn= pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
# 游标设置为字典类型cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
r= cursor.execute("call p1()")
result= cursor.fetchone()
mit()cursor.close()
conn.close()
