本文共 22222 字,大约阅读时间需要 74 分钟。
目录
开始学习MySQL 数据库前,先了解下RDBMS的一些术语:
常用的几种约束
NOT NULL 约束强制列不接受 NULL(空) 值。
NOT NULL 约束强制字段始终包含值。这意味着,如果不向字段添加值,就无法插入新记录或者更新记录。CREATE TABLE 表名 (字段名1 类型 NOT NULL)
UNIQUE 约束唯一标识数据库表中的每条记录。
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。 PRIMARY KEY 拥有自动定义的 UNIQUE 约束。 请注意,每个表可以有多个 UNIQUE 约束,但是每个表只能有一个 PRIMARY KEY 约束。CREATE TABLE 表名 (字段名1 类型 UNIQUE)
PRIMARY KEY 约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值。 主键列不能包含 NULL 值。 每个表都应该有一个主键,并且每个表只能有一个主键。主键类似身份证号
CREATE TABLE 表名 (字段名 类型 PRIMARY KEY ); 如需要设置主键自增长则在PRIMARY KEY AUTO_INCREMENTCREATE TABLE 表名 (字段名 类型 PRIMARY KEY AUTO_INCREMENT);
创建用户 create user '用户名'@'IP地址' identified by '密码';删除用户 drop user '用户名'@'IP地址';修改用户 rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';;修改密码 set password for '用户名'@'IP地址' = Password('新密码')PS:用户权限相关数据保存在mysql数据库的user表中,所以也可以直接对其进行操作(不建议)
show grants for '用户'@'IP地址' -- 查看权限grant 权限 on 数据库.表 to '用户'@'IP地址' -- 授权revoke 权限 on 数据库.表 from '用户'@'IP地址' -- 取消权限
常用权限: all privileges 除grant外的所有权限 select 仅查权限 select,insert 查和插入权限 usage 无访问权限对于目标数据库以及内部其他: 数据库名.* 数据库中的所有 数据库名.表 指定数据库中的某张表 数据库名.存储过程 指定数据库中的存储过程 *.* 所有数据库对于用户和IP: 用户名@IP地址 用户只能在改IP下才能访问 用户名@192.168.1.% 用户只能在改IP段下才能访问(通配符%表示任意) 用户名@% 用户可以再任意IP下访问(默认IP地址为%)
示例
grant all privileges on db1.tb1 TO '用户名'@'IP'grant select on db1.* TO '用户名'@'IP'grant select,insert on *.* TO '用户名'@'IP'revoke select on db1.tb1 from '用户名'@'IP'
#百分号匹配法 grant all on *.* to 'test'@'192.168.200.%' identified by 'test123';#子网掩码配置法 grant all on *.* to 'test'@'192.168.200.0/255.255.255.0' identified by 'test123';#刷新权限 flush privileges;#远程登陆连接 mysql -utest -ptest123 -h 192.168.200.96
flush privileges,将数据读取到内存中,从而立即生效。
# 启动免授权服务端mysqld --skip-grant-tables# 客户端mysql -u root -p# 修改用户名密码update mysql.user set authentication_string=password('666') where user='root';flush privileges;
A、条件 select * from 表 where id > 1 and name != 'alex' and num = 12; -- 筛选id>1且name不为alex且num为12的数据 select * from 表 where id between 5 and 16; -- 筛选id值在5到16范围内的数据 select * from 表 where id in (11,22,33) -- 筛选id值为11或22或33的数据 select * from 表 where id not in (11,22,33) -- 反之 select * from 表 where id in (select nid from 表)B、SQL通配符 select * from 表 where name like '%le%' -- 选取name包含有le的所有数据 select * from 表 where name like 'ale_' -- ale开头的所有(一个字符) select * from 表 where name regexp "^[awv]"; -- 选取name以'a'、'w'或'v'开始的所有数据 select * from tb where name regexp "^[a-c]"; -- 选取name以a到c开头范围内的所有的数据 select * from tb where name regexp "^[^a-c]";-- 选取name非以a到c开头的所有数据C、限制 select * from tb limit 2; -- 前2行 select * from tb limit 2,2; -- 从第2行开始的后2行 select * from tb limit 2 offset 2; -- 从第2行开始的后2行D、排序 select * from tb order by name asc; -- 按照name升序排列 select * from tb order by name desc; -- 按照name降序排列E、分组 select * from tb group by name; -- 根据名字分组 select * from tb group by 2; -- 根据第2个字段分组 select * from employee_tbl group by name having id>4; -- 根据名字分组且id大于4的数据 -- where作用于分组前,having作用于分组后且可以用聚合函数,在where中就不行F、聚合函数(经常作用于分组查询配合使用) SUM(字段) -- 求和 COUNT(字段) -- 次数统计 AVG(字段) -- 平均值 MAX(字段) -- 最大 MIN(字段) -- 最小
WHERE子句中的条件:
= -- 等于<> -- 不等于。注释:在 SQL 的一些版本中,该操作符可被写成 !=> -- 大于< -- 小于>= -- 大于等于<= -- 小于等于BETWEEN-- 在某个范围内LIKE -- 搜索某种模式IN -- 指定针对某个列的多个可能值
外键可以理解为一种约束,它有以下限制(FOREIGN KEY为创建外键的关键词)
注意:作为外键一定要和关联主键的数据类型保持一致
-- 1、建立从表的时候就和主表建立外键CREATE TABLE TABLE_NAME( '字段' 类型, '字段' 类型, ... ... FOREIGN KEY (从表字段) REFERENCES 主表(字段));-- 2、建表完成之后,也可以通过sql语句和主表建立联系ALTER TABLE 从表 ADD CONSTRAINT 外键名称(形如:FK_从表_主表) FOREIGN KEY (从表字段) REFERENCES 主表(字段);
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称
实例:
-- 创建班级表 (主表)CREATE TABLE class( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), priece INT);DESC class;INSERT INTO class(name,priece) VALUES ("python",15800), ("linux",14800), ("go",16800), ("java",18800);-- 创建学生表(子表)CREATE TABLE student (sid INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), age INT, sex TINYINT(1), class_id INT, FOREIGN KEY (class_id) REFERENCES class(id));(解释下表信息中的这些内容mysql> SHOW CREATE TABLE student;| student | CREATE TABLE `student` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `sex` tinyint(1) DEFAULT NULL, `class_id` int(11) DEFAULT NULL, PRIMARY KEY (`sid`), //主键 KEY `class_id` (`class_id`),//这里的class_id是外键字段 CONSTRAINT `student_ibfk_1` // 这是外键名 FOREIGN KEY (`class_id`) REFERENCES `class` (`id`)// 以哪个表的(字段) 做为参考,做关联)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |mysql> select * from class;+----+--------+--------+| id | name | priece |+----+--------+--------+| 1 | python | 88 || 2 | linux | 99 || 3 | go | 188 || 4 | java | 78 |+----+--------+--------+mysql> select * from student;+-----+-------+----------+------+------+| sid | name | class_id | age | sex |+-----+-------+----------+------+------+| 1 | Aaron | 2 | 22 | 0 || 2 | Lunti | 2 | 18 | 1 || 3 | Pint | 4 | 20 | 1 || 4 | tonly | 3 | 20 | 0 |+-----+-------+----------+------+------+-- 建表之后添加外键 (abc为指定外键名)ALTER TABLE student ADD CONSTRAINT abc FOREIGN KEY (class_id) REFERENCES class(id);SHOW CREATE TABLE student;-- 这样绑定的class_id超出class的id则不让插入INSERT INTO student(name, age, sex, class_id) VALUES ("tony",17,0,1);-- 删除外键 ALTER TABLE student DROP FOREIGN KEY abc;---- 注:是外键名不是外键字段-- 删除外键之后,则可以插入超出class id范围的数据INSERT INTO student(name, age, sex, class_id) VALUES ("tony",17,0,5);
表里面有相应的一些记录,也做了外键约束,可以尝试对主表做修改,看看有什么不同
主表:被外键所绑定的表
子表:绑定了主键的表尝试删除主表的记录:
mysql> DELETE FROM class where id=2;ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`samp_db`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))提示做了外键约束,不能删除
如果想要修改,则在字表做级联操作
3、什么是级联(CASCADED)了
cascade 表示级联操作,就是说,如果主键表中被参考字段更新,外键表中也更新,主键表中的记录被删除,外键表中改行也相应删除
语法
ALTER TABLE 子表 ADD FOREIGN KEY(外键) REFERENCES 主表(主键)ON DELETE CASCADE;
例如级联删除:
mysql> ALTER TABLE student ADD FOREIGN KEY (class_id) REFERENCES class(id) -> ON DELETE CASCADE;
4、INOODB下支持的ON语句
--外键约束对子表的含义: 如果在父表中找不到候选键,则不允许在子表上进行insert/update--外键约束对父表的含义: 在父表上进行update/delete以更新或删除在子表中有一条或多条对 -- 应匹配行的候选键时,父表的行为取决于:在定义子表的外键时指定的 -- on update/on delete子句-----------------innodb支持的四种方式--------------------------------------------cascade方式 在父表上update/delete记录时,同步update/delete掉子表的匹配记录-----外键的级联删除:如果父表中的记录被删除,则子表中对应的记录自动被删除-------- FOREIGN KEY (charger_id) REFERENCES ClassCharger(id) ON DELETE CASCADE------set null方式 在父表上update/delete记录时,将子表上匹配记录的列设为null -- 要注意子表的外键列不能为not null FOREIGN KEY (charger_id) REFERENCES ClassCharger(id) ON DELETE SET NULL------Restrict方式 :拒绝对父表进行删除更新操作(了解)------No action方式 在mysql中同Restrict,如果子表中有匹配的记录,则不允许对父表对应候选键 -- 进行update/delete操作(了解)
新建库
再新建两张表建库:mysql> create database company; 进库:mysql>use company; 建员工表:mysql> create table emp( -> emp_id int auto_increment primary key not null, -> emp_name varchar(50), -> age int, -> dept_id int -> ); 建部门表mysql> create table dep( -> dept_id int, -> dept_name varchar(100) -> ); 往员工表插入记录mysql> insert into emp(emp_name,age,dept_id) values -> ('A',19,200), -> ('B',26,201), -> ('C',30,201), -> ('D',24,202), -> ('E',20,200), -> ('F',38,204); 往部门表插入记录mysql> insert into dep values -> (200,'人事部'), -> (201,'技术部'), -> (202,'销售部'), -> (203,'财政部');
MYSQL常用的几种连接查询方法
INNER JOIN 内连接 LEFT JOIN 左连接 RIGHT JOIN 右连接 MYSQL 没有全连接(FULL JOIN)笛卡尔积查询:
假如一张表示M表,另一张表示N表 查询的总记录是:M*N记录 M的每一条记录都会匹配N的每一条记录select * from emp inner join dep; 和 select * from emp,dep查询结果都一样mysql> select * from emp,dep; +--------+----------+------+---------+---------+-----------+| emp_id | emp_name | age | dept_id | dept_id | dept_name |+--------+----------+------+---------+---------+-----------+| 1 | A | 19 | 200 | 200 | 人事部 || 1 | A | 19 | 200 | 201 | 技术部 || 1 | A | 19 | 200 | 202 | 销售部 || 1 | A | 19 | 200 | 203 | 财政部 || 2 | B | 26 | 201 | 200 | 人事部 || 2 | B | 26 | 201 | 201 | 技术部 || 2 | B | 26 | 201 | 202 | 销售部 || 2 | B | 26 | 201 | 203 | 财政部 || 3 | C | 30 | 201 | 200 | 人事部 || 3 | C | 30 | 201 | 201 | 技术部 || 3 | C | 30 | 201 | 202 | 销售部 || 3 | C | 30 | 201 | 203 | 财政部 || 4 | D | 24 | 202 | 200 | 人事部 || 4 | D | 24 | 202 | 201 | 技术部 || 4 | D | 24 | 202 | 202 | 销售部 || 4 | D | 24 | 202 | 203 | 财政部 || 5 | E | 20 | 200 | 200 | 人事部 || 5 | E | 20 | 200 | 201 | 技术部 || 5 | E | 20 | 200 | 202 | 销售部 || 5 | E | 20 | 200 | 203 | 财政部 || 6 | F | 38 | 204 | 200 | 人事部 || 6 | F | 38 | 204 | 201 | 技术部 || 6 | F | 38 | 204 | 202 | 销售部 || 6 | F | 38 | 204 | 203 | 财政部 |+--------+----------+------+---------+---------+-----------+24 rows in set (0.06 sec)
mysql> select emp.dept_id,emp.emp_name,dep.dept_name from emp inner join dep on emp.dept_id=dep.dept_id;+---------+----------+-----------+| dept_id | emp_name | dept_name |+---------+----------+-----------+| 200 | A | 人事部 || 201 | B | 技术部 || 201 | C | 技术部 || 202 | D | 销售部 || 200 | E | 人事部 |+---------+----------+-----------+5 rows in set (0.00 sec)
mysql> select emp.dept_id,emp.emp_name,dep.dept_name from emp left join dep on emp.dept_id=dep.dept_id;+---------+----------+-----------+| dept_id | emp_name | dept_name |+---------+----------+-----------+| 200 | A | 人事部 || 200 | E | 人事部 || 201 | B | 技术部 || 201 | C | 技术部 || 202 | D | 销售部 || 204 | F | NULL |+---------+----------+-----------+6 rows in set (0.00 sec)
mysql> select emp.dept_id,emp.emp_name,dep.dept_name from emp right join dep on emp.dept_id=ddep.dept_id;+---------+----------+-----------+| dept_id | emp_name | dept_name |+---------+----------+-----------+| 200 | A | 人事部 || 201 | B | 技术部 || 201 | C | 技术部 || 202 | D | 销售部 || 200 | E | 人事部 || NULL | NULL | 财政部 |+---------+----------+-----------+6 rows in set (0.00 sec)
查询员工年龄大于等于25岁的部门
SELECT DISTINCT department.dept_name FROM employee,department WHERE employee.dept_id = department.dept_id AND age>25;
全外连接(基本不会用到):
查询employee表,但dept_id必须在department表中出现过:
mysql> select * from emp -> where dept_id IN -> (select dept_id from dep);+--------+----------+------+---------+| emp_id | emp_name | age | dept_id |+--------+----------+------+---------+| 1 | A | 19 | 200 || 2 | B | 26 | 201 || 3 | C | 30 | 201 || 4 | D | 24 | 202 || 5 | E | 20 | 200 |+--------+----------+------+---------+5 rows in set (0.00 sec)
=、!=、>、>=、<、<=、<>
查询员工年龄大于等于25岁的部门
mysql> select dept_id,dept_name from dep -> where dept_id IN -> (select DISTINCT dept_id from emp where age>=25);+---------+-----------+| dept_id | dept_name |+---------+-----------+| 201 | 技术部 |+---------+-----------+1 row in set (0.00 sec)
EXISTS关字键字表示存在。在使用EXISTS关键字时,内层查询语句不返回查询的记录。
而是返回一个真假值。Ture或False 当返回Ture时,外层查询语句将进行查询;当返回值为False时,外层查询语句不进行查询EXISTS 返回为Ture,进行查询emp表
mysql> select * from emp -> WHERE EXISTS -> (SELECT dept_name from dep where dept_id=203);+--------+----------+------+---------+| emp_id | emp_name | age | dept_id |+--------+----------+------+---------+| 1 | A | 19 | 200 || 2 | B | 26 | 201 || 3 | C | 30 | 201 || 4 | D | 24 | 202 || 5 | E | 20 | 200 || 6 | F | 38 | 204 |+--------+----------+------+---------+6 rows in set (0.00 sec)
EXISTS 返回为False,进行查询emp,则查询结果为空
mysql> select * from emp -> WHERE EXISTS -> (SELECT dept_name from dep where dept_id=205);Empty set (0.00 sec)
复制表的内容
create table emp_bak (select * from emp)
提示:这个复制表的,只能表的字段 类型和记录,不能复制表的约束条件(比如:主键,外键)
MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。
虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。
索引特点:创建与维护索引会消耗很多时间与磁盘空间,但查询速度大大提高!一般索引是针对的大表的,来创建的
索引合并:使用多个单列索引组合查询搜索
覆盖索引:select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖语法CREATE TABLE 表名 ( 字段名1 数据类型 [完整性约束条件…], 字段名2 数据类型 [完整性约束条件…], 索引类型 索引名 (字段名), );
CREATE TABLE emp1 ( id INT, name VARCHAR(30) , resume VARCHAR(50), INDEX index_emp_name (name), );
CREATE TABLE emp2 ( id INT, name VARCHAR(30) , bank_num CHAR(18) UNIQUE , resume VARCHAR(50), UNIQUE INDEX index_emp_name (name) );
CREATE TABLE emp3 ( id INT, name VARCHAR(30) , resume VARCHAR(50), FULLTEXT INDEX index_resume (resume) );
CREATE TABLE emp4 ( id INT, name VARCHAR(30) , resume VARCHAR(50), INDEX index_name_resume (name, resume) );
第一种语法:CREATE 索引类型 索引名 ON 表名 (字段名)第二种语法: ALTER TABLE 表名 ADD 索引类型 索引名 (字段名) 示例:CREATE INDEX index_emp_name on emp1(name);ALTER TABLE emp2 ADD UNIQUE INDEX index_bank_num(band_num);
语法:DROP INDEX 索引名 on 表名DROP INDEX index_emp_name on emp1;DROP INDEX bank_num on emp2;
小结
在SQL 连接查询中 on,where,having的区别
1、on、where、having这三个都可以加条件的子句中,on是最先执行,where次之,having最后。有时候如果这先后顺序不影响中间结果的话,那最终结果是相同的。但因为on是先把不符合条件的记录过滤后才进行统计,它就可以减少中间运算要处理的数据,按理说应该速度是最快的。
2、where应该比having快点的,因为它过滤数据后才进行sum,所以having是最慢的。但也不是说having没用,因为有时在步骤3还没出来都不知道那个记录才符合要求时,就要用having了。 3、在两个表联接时才用on的,所以在一个表的时候,就剩下where跟having比较了。在这单表查询统计的情况下,如果要过滤的条件没有涉及到要计算字段,那它们的结果是一样的,只是where可以使用rushmore技术,而having就不能,在速度上后者要慢。 4、如果要涉及到计算的字段,就表示在没计算之前,这个字段的值是不确定的,根据上篇写的工作流程,where的作用时间是在计算之前就完成的,而having就是在计算后才起作用的,所以在这种情况下,两者的结果会不同。 5、在多表联接查询时,on比where更早起作用。系统首先根据各个表之间的联接条件,把多个表合成一个临时表后,再由where进行过滤,然后再计算,计算完后再由having进行过滤。由此可见,要想过滤条件起到正确的作用,首先要明白这个条件应该在什幺时候起作用,然后再决定放在那里
FROM ,ON , WHERE, SELECT , GROUP BY , ORDER BY ,HAVING
MySQL 事务主要用于处理操作量大,复杂度高的数据。比如说,在人员管理系统中,你删除一个人员,你即需要删除人员的基本资料,也要删除和该人员相关的信息,如信箱,文章等等,这样,这些数据库操作语句就构成一个事务,但是一旦有某一个出现错误,即可回滚到原来的状态,从而保证数据库数据完整性。!
一般来说,事务是必须满足4个条件(ACID): Atomicity(原子性)、Consistency(稳定性)、Isolation(隔离性)、Durability(可靠性)
事务广泛的运用于订单系统、银行系统等多种场景。如果有以下一个场景:A用户和B用户是银行的储户。现在A要给B转账500元。那么需要做以下几件事:
1. 检查A的账户余额>500元;
2. A账户扣除500元; 3. B账户增加500元;正常的流程走下来,A账户扣了500,B账户加了500,皆大欢喜。那如果A账户扣了钱之后,系统出故障了呢?A白白损失了500,而B也没有收到本该属于他的500。以上的案例中,隐藏着一个前提条件:A扣钱和B加钱,要么同时成功,要么同时失败。事务的需求就在于此。
<1> 原子性(Atomicity):原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。
<2> 一致性(Consistency):事务前后数据的完整性必须保持一致。在事务执行之前数据库是符合数据完整性约束的,无论事务是否执行成功,事务结束后的数据库中的数据也应该是符合完整性约束的。在某一时间点,如果数据库中的所有记录都能保证满足当前数据库中的所有约束,则可以说当前的数据库是符合数据完整性约束的。
比如删部门表前应该删掉关联员工(已经建立外键),如果数据库服务器发生错误,有一个员工没删掉,那么此时员工的部门表已经删除,那么就不符合完整性约束了,所以这样的数据库也就性能太差啦!<3>隔离性(Isolation):事务的隔离性是指多个用户并发访问数据库时,一个用户的事务不能被其它用户的事务所干扰,多个并发事务之间数据要相互隔离。
<4>持久性(Durability):持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响。
三、隔离性:
将数据库设计为串行化程的数据库,让一张表在同一时间内只能有一个线程来操作。如果将数据库设计为这样,那数据库的效率太低了。所以数据库的设计这没有直接将数据库设计为串行化,而是为数据库提供多个隔离级别选项,使数据库的使用者可以根据使用情况自己定义到底需要什么样的隔离级别。不考虑隔离性可能出现的问题:
事务操作
开启事务 start transaction; 回滚事务 回滚事务,即撤销指定的sql语句(只能回退insert delete update语句),回滚到上一次commit的位置Rollback;提交事务提交事务,提交未存储的事务Commit;保留点事务处理中设置的临时占位符 你可以对它发布回退(与整个事务回退不同)savepoint
转账实例:
创建一个银行库create database bank; use bank;创建用户表create table user_info(id int PRIMARY KEY auto_increment,name VARCHAR(20),balance INT);插入两条用户账户记录A和BINSERT INTO user_info (name,balance) VALUE ("A",1000), ("B",1000); select * from user_info;开启事务start transaction;假如没有提交事务,那么转账完全失败UPDATE user_info set balance=balance-500 WHERE name=”A”;UPDATE user_info set balance=balance+500 WHERE name=”B";commit;提交事务则转账成功回滚只是回到commit提交之后的位置rollback;事务中所有sql语句执行正确则需要自己手动提交commit;否则有任何一条执行错误,需要自己提交一条rollback,这时会回滚所有操作,而不是commit会给你自动判断和回滚。mysql解决办法:设定一个保留点(插入总要的数据记录)savepoint 保留点名字;使用ROLLBACK TO 保留点名字;回滚到一个保存点。使用RELEASE 保留点名字;删除一个保存点,但是保留该保存点建立后执行的命令的效果。保存点只能在一个事务块里面建立。在一个事务里面可以定义多个保存点。
注:事务中所有sql语句执行正确则需要自己手动提交commit;否则有任何一条执行错误,需要自己提交一条rollback,这时会回滚所有操作,而不是commit会给你自动判断和回滚。
mysql解决办法:设定一个保留点(插入总要的数据记录)
保存点只能在一个事务块里面建立。在一个事务里面可以定义多个保存点。
delimiter \\ -- 此条可更改SQL语句结束符create PROCEDURE p1( OUT p_return_code tinyint)BEGIN DECLARE exit handler for sqlexception BEGIN -- ERROR set p_return_code = 1; rollback; END; DECLARE exit handler for sqlwarning BEGIN -- WARNING set p_return_code = 2; rollback; END; START TRANSACTION; DELETE from tb1; insert into tb2(name)values('seven'); COMMIT; -- SUCCESS set p_return_code = 0; END\\delimiter ;
pymsql是Python中操作MySQL的模块,其使用方法和MySQLdb几乎相同
pip3 install pymysql
执行SQL
#!/usr/bin/env python# -*- coding:utf-8 -*-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)]) # 提交,不然无法保存新建或者修改的数据conn.commit() # 关闭游标cursor.close()# 关闭连接conn.close()
#!/usr/bin/env python# -*- coding:utf-8 -*-import pymysql conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')cursor = conn.cursor()cursor.execute("select * from hosts") # 获取第一行数据row_1 = cursor.fetchone() # 获取前n行数据 注意游标是动的# row_2 = cursor.fetchmany(3)# 获取所有数据# row_3 = cursor.fetchall() conn.commit()cursor.close()conn.close()
注:在fetch数据时按照顺序进行,可以使用cursor.scroll(num,mode)来移动游标位置,如:
fetch数据类型
关于默认获取的数据是元祖类型,如果想要或者字典类型的数据,即:
#!/usr/bin/env python# -*- coding:utf-8 -*-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() conn.commit()cursor.close()conn.close()
存储过程是一个SQL语句集合,类似函数,需要主动调用。
# 无参数存储过程# 创建存储过程delimiter //create procedure p1()BEGIN select * from t1;END//delimiter ;# 执行存储过程call p1()
都说了类似函数,那必须得可以接收参数,且参数有三类:
# 有参数存储过程# 创建存储过程delimiter \\ # 结尾分号改为\\create procedure p1( in i1 int, in i2 int, inout i3 int, out r1 int)BEGIN DECLARE temp1 int; # 创建申明局部变量 DECLARE temp2 int default 0; set temp1 = 1; set r1 = i1 + i2 + temp1 + temp2; set i3 = i3 + 100;end\\delimiter ;# 执行存储过程DECLARE @t1 INT default 3;DECLARE @t2 INT;CALL p1 (1, 2 ,@t1, @t2);SELECT @t1,@t2;
drop procedure proc_name;
执行为函数名加括号;
DECLARE代表创建一个局部变量
# 无参数call proc_name()# 有参数,全incall proc_name(1,2)# 有参数,有in,out,inoutDECLARE @t1 INT;DECLARE @t2 INT default 3;call proc_name(1,2,@t1,@t2)
pymysql执行存储过程
#!/usr/bin/env python# -*- coding:utf-8 -*-import pymysqlconn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)# 执行存储过程cursor.callproc('p1', args=(1, 22, 3, 4))# 获取执行完存储的参数cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")result = cursor.fetchall()conn.commit()cursor.close()conn.close()print(result)
转载地址:http://fezwi.baihongyu.com/