数据库操作：增、删、改、查
增 ：create database DBname;删：drop database DBname;改：1、先删除原来数据库：mysqldump -uUsername -p DBname>FilePath/DBname.sqlmysql> drop database DBname;2、再创建新名字数据库：mysql> create database User;Query OK, 1 row affected (0.00 sec)3、数据库导入：mysql -uroot -p User
数据库字符集操作：
创建时设置字符集： 数据库字符集：CREATE DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 修改数据库字符集： ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci表的字符集修改:alter table TbName change character set=utf8;查看表的字符集：show variables like 'character_%';字段的字符集个性：alter table TBName change columnName columnName varchar(20) character set utf8;查看字段的字符集：show full columns from users\G;
数据库授权语句：
用户操作：
创建用户：mysql> create user 'liang'@'%' idenfied by 'r00tme';//创建一个liang用户 ,%:表示任意一台主机mysql> create user 'liang'@'%' identified by 'liang';Query OK, 0 rows affected (0.01 sec)mysql> select host,user from user; //查看创建 user 表；+-----------------+------------------+| host | user |+-----------------+------------------+| % | liang || % | root || % | tony || 192.168.100.149 | tony || localhost | debian-sys-maint || localhost | mysql.sys || localhost | root |+-----------------+------------------+7 rows in set (0.00 sec) 远程 navicat 软件可用这个用户或者直接创建一个root用户，给予所有的权限
mysql> rename user 'liang'@'%' to 'liang1'@'192.168.0.%'; //用户更名语句rename to Query OK, 0 rows affected (0.00 sec)mysql> select host,user from user;+-----------------+------------------+| host | user |+-----------------+------------------+| % | root || % | tony || 192.168.0.% | liang1 || 192.168.100.149 | tony || localhost | debian-sys-maint || localhost | mysql.sys || localhost | root |+-----------------+------------------+7 rows in set (0.00 sec)更改用户密码：mysql> set password for 'liang'@'%'='r00tme';Query OK, 0 rows affected (0.00 sec) //删除用户记录mysql> drop user 'liang1'@'192.168.0.%'; Query OK, 0 rows affected (0.00 sec)
权限操作：
授权语句： grant 权限 on DBname.tablename to 'username'@'%';权限：select,insert,update, all privileges(特殊权限)
mysql> grant all privileges on test.* to 'liang'@'%'; 授权语句；Query OK, 0 rows affected (0.00 sec)
查看授权语句：
mysql> show grants for 'liang';+-------------------------------------------------+| Grants for liang@% |+-------------------------------------------------+| GRANT USAGE ON *.* TO 'liang'@'%' || GRANT ALL PRIVILEGES ON `test`.* TO 'liang'@'%' |+-------------------------------------------------+2 rows in set (0.00 sec)
撤消权限：
mysql> revoke select on test.* from 'liang'@'%';Query OK, 0 rows affected (0.00 sec)
测试外部是否能连接MYSQL
1、防火墙 关闭： 1、systemctl status iptables 2、systemctl stop firewalld
2、iptbles 开启3306端口 [root@localhost home]# iptables -A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT
3、加一个笔记， GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
表的增、删、改、查
建表：
1 mysql> show tables; //显示表语句 2 Empty set (0.00 sec) 3 //创建表语句：create table tablename() 4 mysql> create table user( //创建表结构语句， 5 -> id int auto_increment, 6 -> name varchar(20) not null default '', 7 -> age tinyint unsigned not null default 0, 8 -> index id (id)) //索引，能在一百万的数据中找到这个值的索引，而不是按照顺序找到这个值，索引可以提高处理的速度 索引又分为普通索引index ,唯一性索引unique index:与普通索引的区别是每个索引只能出现一次如ID、身份证号 , 主键primary key ：主键一定是唯一性索引，索引不一定是主键，相当于书的页码。主键参考（http://www.jb51.net/article/34037.htm） 9 -> engine=innodb charset=utf8; //innodb 是表引擎，表引擎有myisam,或者其它，一般常用innodb myisam,charset 一般有utf8 和 gbk10 Query OK, 0 rows affected (0.02 sec)11 12 mysql> show tables; //显示表语句
13 +------------------+ 14 | Tables_in_Mytest | 15 +------------------+ 16 | user | 17 +------------------+ 18 1 row in set (0.00 sec) 19 20 mysql> show create table user; //显示创建表的结构 21 +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 22 | Table | Create Table | 23 +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 24 | user | CREATE TABLE `user` ( 25 `id` int(11) NOT NULL AUTO_INCREMENT, 26 `name` varchar(20) NOT NULL DEFAULT '', 27 `age` tinyint(3) unsigned NOT NULL DEFAULT '0', 28 KEY `id` (`id`) 29 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 30 +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 31 1 row in set (0.00 sec)
删除表：
mysql> drop table user;Query OK, 0 rows affected (0.03 sec) truncate table TableName; 删除表并将索引归零。delete 不会将索引归零。
改表名：
mysql> rename table test1 to user1;Query OK, 0 rows affected (0.02 sec)
改表名二：
mysql> alter table user rename users;Query OK, 0 rows affected (0.02 sec)
查表：
mysql> use mysql;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;
表结构增、删、改、查：
查看表结构：
mysql> desc user;+-------+---------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+---------------------+------+-----+---------+----------------+| id | int(11) | NO | MUL | NULL | auto_increment || name | varchar(20) | NO | | | || age | tinyint(3) unsigned | NO | | 0 | |+-------+---------------------+------+-----+---------+----------------+3 rows in set (0.00 sec)
修改表之增加列：
mysql> alter table user add column testid int not null default 0 after id; //修改user表 add 字段 testid 在id 后面Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0
修改表之修改列：
alter table tbName add columnName columnType [not null default ]
修改前：mysql> desc user;+--------+---------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+---------------------+------+-----+---------+----------------+| id | int(11) | NO | MUL | NULL | auto_increment || testid | int(11) | NO | | 0 | || name | varchar(20) | NO | | | || age | tinyint(3) unsigned | NO | | 0 | || gender | varchar(6) | NO | | | || id2 | int(2) | NO | | 0 | |+--------+---------------------+------+-----+---------+----------------+6 rows in set (0.00 sec)修改语句：mysql> alter table user change id2 idtwo int(11) not null default 1;Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0效果：mysql> desc user;+--------+---------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+---------------------+------+-----+---------+----------------+| id | int(11) | NO | MUL | NULL | auto_increment || testid | int(11) | NO | | 0 | || name | varchar(20) | NO | | | || age | tinyint(3) unsigned | NO | | 0 | || gender | varchar(6) | NO | | | || idtwo | int(11) | NO | | 1 | |+--------+---------------------+------+-----+---------+----------------+6 rows in set (0.00 sec)
修改表之减少列：
mysql> alter table user drop idtwo; //删除idtwo 列Query OK, 0 rows affected (0.06 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc user;+--------+---------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+---------------------+------+-----+---------+----------------+| id | int(11) | NO | MUL | NULL | auto_increment || testid | int(11) | NO | | 0 | || name | varchar(20) | NO | | | || age | tinyint(3) unsigned | NO | | 0 | || gender | varchar(6) | NO | | | |+--------+---------------------+------+-----+---------+----------------+5 rows in set (0.00 sec)
修改表之增加主键：
alter table tbName primary key(主键所在列名);
增加主键前：mysql> desc user;+--------+---------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+---------------------+------+-----+---------+----------------+| id | int(11) | NO | MUL | NULL | auto_increment || testid | int(11) | NO | | 0 | || name | varchar(20) | NO | | | || age | tinyint(3) unsigned | NO | | 0 | || gender | varchar(6) | NO | | | |+--------+---------------------+------+-----+---------+----------------+5 rows in set (0.00 sec)增加主键：mysql> alter table user add primary key(id);Query OK, 0 rows affected (0.05 sec)Records: 0 Duplicates: 0 Warnings: 0效果：mysql> desc user;+--------+---------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+---------------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || name | varchar(20) | NO | | | || age | tinyint(3) unsigned | NO | | 0 | || gender | varchar(6) | NO | | | |+--------+---------------------+------+-----+---------+----------------+4 rows in set (0.00 sec)
主键操作：
修改表之删除主键：
alter table tbName drop primary key;
删除前：mysql> desc user;+--------+---------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+---------------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || name | varchar(20) | NO | MUL | | || age | tinyint(3) unsigned | NO | | 0 | || gender | varchar(6) | NO | | | |+--------+---------------------+------+-----+---------+----------------+4 rows in set (0.01 sec)mysql> alter table user drop primary key; //删除主键语句Query OK, 0 rows affected (0.05 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc user; //主键已被删除+--------+---------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+---------------------+------+-----+---------+----------------+| id | int(11) | NO | MUL | NULL | auto_increment || name | varchar(20) | NO | MUL | | || age | tinyint(3) unsigned | NO | | 0 | || gender | varchar(6) | NO | | | |+--------+---------------------+------+-----+---------+----------------+4 rows in set (0.00 sec)
外键操作：
添加外键：
外键是student 的class_id 连接class 表的cid;
mysql> alter table student add constraint class_id foreign key(class_id) references class(cid);Query OK, 1 row affected (0.04 sec)Records: 1 Duplicates: 0 Warnings: 0
删除外键：
删除外键：mysql> alter table student drop foreign key FK_ID; 外键ID不用加引号Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0
查看外键：
mysql> show create table student;+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| student | CREATE TABLE `student` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `sname` varchar(20) NOT NULL, `gender` varchar(3) NOT NULL, `class_id` int(11) DEFAULT NULL, PRIMARY KEY (`sid`), CONSTRAINT `SID` FOREIGN KEY (`sid`) REFERENCES `py_fulls4` (`cid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
实际应用：连表操作
查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；mysql> select score.student_id,student.sname,score.corse_id,number from score inner join student on student.iid=score.sid where corse_id between 1 and 2 and number<70; -- inner join 代表将一些为 null 去掉。
索引操作之索引的作用：
修改表之增加索引：
alter table tbName add index(columnName);
增加索引语句：mysql> alter table user add index(name);Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0查看索引语句：mysql> show index from user;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| user | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | || user | 1 | id_2 | 1 | id | A | 0 | NULL | NULL | | BTREE | | || user | 1 | name | 1 | name | A | 0 | NULL | NULL | | BTREE | | |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+3 rows in set (0.00 sec)删除索引语句：mysql> alter table user drop index id;ERROR 1091 (42000): Can't DROP 'id'; check that column/key existsmysql> alter table user add index(name);Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0
修改表之删除索引：
alter table tbName drop index columnName;
查看索引语句：mysql> show index from user;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| user | 1 | id_2 | 1 | id | A | 0 | NULL | NULL | | BTREE | | || user | 1 | name_2 | 1 | name | A | 0 | NULL | NULL | | BTREE | | || user | 1 | gender | 1 | gender | A | 0 | NULL | NULL | | BTREE | | |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+3 rows in set (0.00 sec)删除索引语句：mysql> alter table user drop index gender;Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0查看：mysql> show index from user;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| user | 1 | id_2 | 1 | id | A | 0 | NULL | NULL | | BTREE | | || user | 1 | name_2 | 1 | name | A | 0 | NULL | NULL | | BTREE | | |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+2 rows in set (0.00 sec)
索引的作用：
主键索引：加速查找+不能为空+不能重复
普通索引：加速查找
唯一索引：加速索+不能重复
联合索引（多列）：
- 联合主键索引
- 联合唯一索引
- 联合普通索引
例：有 ID ,name,gender 这三个字段，100万条数据，但是在建表时没有设置任何索引， 这就是没有索引的表------>会导致数据从前到后依次查找 1-1000000；
例：创建了索引：
-如果给ID创建一个索引，并且ID也还是一个主键：
-1、会创建额外文件（以某种格式存储）
-2、将ID做为索引作为额外文件（按某种格式存储）
创建索引命令：
create index IndexName on TableName(ColumnName); 创建索引 另一种创建方式； create index IndexName on TableName(ColumnName(16));//表示前16个字符做索引
删除索引：
drop index IndexName on TableName;
索引的种类：
hash索引：
会创建一个索引表，将数据hash的值（877-934）的存储的地址存储在硬盘上：如：
列名 值
1、Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询如：like ,%.
2、hash相当于把key通过hash函数计算，得到key的hash值，再用这个hash值做指针，查找hash表中是否存在key,如果存在就返回key所对应的value,选定
3、hash索引优缺点：
等值查找：速度快（绝对优势），但是前提是键值都是唯一的 如: SELECT … FROM t WHERE C1 = ?;
范围查找：速度慢：如like '%linlin%'; 这样的。
b-tree索引：
B-tree:平衡多路查找树：
#### innodb用的就是B-tree索引######
1、按照多叉树形式存储：
1】整根树只有一个根节点，
2】每个节点都有N个孩子
3】若根节点不是叶子节点，则至少有2个孩子（特殊情况）
数据增、删、改、查
查询语句：
条件查询 % 匹配多个字符， _ 匹配单个字符：mysql> select * from test1 where name like 't%y'; +----+------+-----+--------+---------------------+| id | name | age | gender | date |+----+------+-----+--------+---------------------+| 1 | tony | 19 | male | 2017-06-05 16:58:30 |+----+------+-----+--------+---------------------+单个字符匹配：mysql> select * from test1 where name like 't_'-> ;+----+------+-----+--------+---------------------+| id | name | age | gender | date |+----+------+-----+--------+---------------------+| 3 | to | 20 | male | 2017-07-09 16:58:30 |+----+------+-----+--------+---------------------+1 row in set (0.00 sec) 多条件查询：mysql> select * from test1 where name like 't%' and age>17;+----+------+-----+--------+---------------------+| id | name | age | gender | date |+----+------+-----+--------+---------------------+| 1 | tony | 19 | male | 2017-06-05 16:58:30 || 3 | to | 20 | male | 2017-07-09 16:58:30 |+----+------+-----+--------+---------------------+2 rows in set (0.00 sec) 查询数据之指定查询数据 limit 如题：查询各科成绩前三名的记录:(不考虑成绩并列情况)
select score.sid,score.corse_id,score.number,T.first_num,T.second_num from score left join
-> ( -> select -> sid, -> (select number from score as s2 where s2.corse_id = s1.corse_id order by number desc limit 0,1) as first_num, -> (select number from score as s2 where s2.corse_id = s1.corse_id order by number desc limit 3,1) as second_num -> from -> score as s1 -> ) as T -> on score.sid =T.sid -> where score.number <= T.first_num and score.number >= T.second_num;
操作数据表之插入数据：
//插入数据语句mysql> insert into user(id,name,age,gender) values('1','tony','22','male');Query OK, 1 row affected (0.01 sec)查询语句：mysql> select * from user;+----+------+-----+--------+| id | name | age | gender |+----+------+-----+--------+| 1 | tony | 22 | male |+----+------+-----+--------+1 row in set (0.00 sec) 省略字段名插入数据和查询数据：
mysql> insert into user values('2','liang','18','male');Query OK, 1 row affected (0.01 sec)mysql> select * from user;+----+-------+-----+--------+| id | name | age | gender |+----+-------+-----+--------+| 1 | tony | 22 | male || 2 | liang | 18 | male |+----+-------+-----+--------+2 rows in set (0.00 sec) 条件插入数据：
向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩； insert into score(student_id, corse_id, number) select id,2,(select avg(number) from score where corse_id = 2) from student where id not in (select student_id from score where corse_id = 2);
删除数据：
mysql> delete from user where id=2; //删除表user 数据id=2 这一条数据Query OK, 1 row affected (0.02 sec)mysql> select * from user;+----+---------+-----+--------+| id | name | age | gender |+----+---------+-----+--------+| 1 | micheal | 22 | male |+----+---------+-----+--------+1 row in set (0.00 sec)
修改数据：
updata tbName set dataname='new_value' where dataname='old_value';
mysql> update user set name='micheal' where name='tony';Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from user;+----+---------+-----+--------+| id | name | age | gender |+----+---------+-----+--------+| 1 | micheal | 22 | male || 2 | liang | 18 | male |+----+---------+-----+--------+2 rows in set (0.00 sec)
mysql> update user set age=19 where name='liang';Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from user;+----+---------+-----+--------+| id | name | age | gender |+----+---------+-----+--------+| 1 | micheal | 22 | male || 2 | liang | 19 | male |+----+---------+-----+--------+2 rows in set (0.00 sec)
union 上下连表：
select number from t1 union select number from t2;
视图操作：
视图是虚拟的，不是真实存在的：给某个查询语句设置别名，日后方便使用
创建视图语句：
create view viewName as SQL 语句; EXAMPLE: create view au_view as select * from authority; 使用时可以直接: select * from viewName; //select * from au_view;
mysql> create view view_stu as select * from student;
Query OK, 0 rows affected (0.03 sec)mysql> select * from view_stu;
+----+-----------+--------+----------+| id | sname | gender | class_id |+----+-----------+--------+----------+| 1 | 杀人犯 | 男 | 1 || 2 | 钢蛋 | 女 | 1 || 3 | 炮 | 男 | 2 || 4 | 小明 | 男 | 3 || 5 | 李小红 | 女 | 2 || 6 | 李小红 | 女 | 2 || 7 | 李小红 | 女 | 4 || 8 | 王五 | 男 | 4 |+----+-----------+--------+----------+8 rows in set (0.01 sec)修改视图：
alter view au_view as select * from b;
删除视图：
dop view ViewName;mysql> drop view au_view; Query OK, 0 rows affected (0.00 sec)
触发器：
1、创建触发器：
修改语句终止符delimiter // create trigger abc before insert on student for each row begin insert into teacher(tname) values('liang'); end // delimiter ; insert into student(sname,gender,class_id) values('abc','男',3); example: update 触发器： delimiter // create trigger abcd before UPDATE on student for each row begin update teacher set tname='yu' where tname='liang'; -- insert into teacher(tname) values('liang'); end // delimiter ; -- insert into student(sname,gender,class_id) values('abc','男',3); update student set sname='abcd' where sname='abc'; 2、删除触发器： mysql> drop trigger abcd4;
ERROR 1360 (HY000): Trigger does not exist mysql> drop trigger abc4; Query OK, 0 rows affected (0.02 sec)触发器的 OLD 与 NEW 是？？？？？？？？？
OLD(是delete的数据) NEW (是insert的数据) update(是OLD NEW 都有的数据）
-- delimiter //
-- create trigger abc1 before insert on student for each row -- begin -- insert into teacher(tname) values(NEW.sname); #NEW 会将下方新插入数据，也插入到teacher 表，这个人既是学生也是老师 -- end // -- delimiter ; insert into student(sname) values('bbb');函数
Mysql 与python : pymysql模块
输入帐号｜密码，查询该由帐户的 权限
import pymysqluser=input('username: ').strip()pwd=input('password: ').strip()conn=pymysql.connect(host='192.168.100.128',user='root',password='r00tme',database='test',charset='utf8')cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)print(conn)sql="select * from user where name=%s and password=%s"cursor.execute(sql,[user,pwd])result=cursor.fetchone()sql1="select * from user left join authority on user.id=authority.userid where name=%s"cursor.execute(sql1,[user])result1=cursor.fetchone()print(result['name'],result['password'])print(result1['role'])cursor.close()conn.close()if result: print('login successfully') print('you role is ',result1['role'])else: print('username or password err')
ORM框架：(object relationship maping)
1、框架的种类
1】DB First: 手动创建数据以及表 ---> ORM--->自动生成类
2】Code First: 手动创建类-->ORM框架-->以及表(sqlalchemy 属于这个）
2、sqlalchemy 的功能：
1】创建数据数据库，创建数据表：
1】连接数据库（pymysql 来做这个事情 )
2】类 转换SQL 语句：
2】操作数据行
1】 增
2】删
3】改
4】查
3】sqlalchemy 提供了便利的功能：
3、自己开发WEB框架
- WEB 的本质就是 socket 在交互
- 数据库操作（pymysql, sqlalchemy）
sqlalchemy 例子：
连接表：
#!/usr/bin/env python3from sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Indexfrom sqlalchemy.orm import sessionmaker, relationshipfrom sqlalchemy import create_engineimport random# 1、创建对象的基类Base = declarative_base()# 2、定义User 对象class UserType(Base): __tablename__='usertype'# 3、表结构 id=Column(Integer,primary_key=True,autoincrement=True) title=Column(String(32),nullable=True,index=True)class Users(Base): __tablename__='users' id=Column(Integer,primary_key=True,autoincrement=True) name=Column(String(32),nullable=True,index=True) email=Column(String(16),unique=True) unser_type_id=Column(Integer,ForeignKey('usertype.id')) _tables_args=( UniqueConstraint('id','name',name='uix_id_name'), Index('ix_n_ex','email',) ) user_type=relationship('UserType',backref='what')def create_db(): engine=create_engine("mysql+pymysql://root:P@ssw0rd@192.168.44.132/oldboy",max_overflow=5) Base.metadata.create_all(engine)def drop_db(): engine=create_engine("mysql+pymysql://root:P@ssw0rd@192.16844.132/oldboy",max_overflow=5) Base.metadata.create_all(engine)# 4、初始化数据连接：engine=create_engine("mysql+pymysql://root:P@ssw0rd@192.168.44.132/oldboy",max_overflow=5)#‘数据库类型+数据训驱动：//用户名：口令@机器地址：端口号/数据库名’’#总结：四个步骤完成ORM的创建，现在可以对数据做 增、删、改、查操作了# Base.metadata.create_all(engine) session类型。。。。。Session=sessionmaker(bind=engine)#print(engine)print(Session)session=Session()# obj1=UserType(title='genearl manager ')# session.add(obj1)# session.commit()# session.close()# user_list=session.query(Users).all()#--------插入数据--------------# mail=random.randint(14324321,432143243)# id=random.randint(1,2)## objs=[# Users(name='admin'),# Users(email=str(mail)+'@qq.com'),# Users(unser_type_id=id)# ]# session.add_all(objs)# session.commit()# session.close()#---------- 查-------------# print(session.query(UserType))# user_type_list=session.query(UserType).all()# for row_data in user_type_list:# print(row_data,row_data.title,'====',row_data.id)# user_type_list=session.query(UserType.id,UserType.title).filter(UserType.id>6) #select id,title from usertype where id >2;# for row in user_type_list:# print(row.id,row.title)#--------删---------------session.query(UserType.id).filter(UserType.id==7).delete()## print('=======反向操作=======')# type_list=session.query(UserType)# for row in type_list:# # print(row.id,row.title,session.query(Users).filter(Users.id==row.id).all())# for ut in row.what:# print(row.id,row.title,ut.email)### print('======正向操作======')# user_list=session.query(Users)# for row1 in user_list:# print(row1.id,row1.name,row1.user_type.title)#-------数据库连表查询操作---------user_list=session.query(Users,UserType).join(UserType,isouter=True ---------- .all() ：没有加all()的时候拿数据是以迭代器的形式，加了的了拿数据一次全部加载到内存
练习：
获取用户信息以及与其用户关联的信息与名称
操作：
user_type=relationship('UserType') 会自动将两表相关联的字段建立关联
练习2：获取用户类型
传统方式
与之关联的数据以对象形式列在后面了。
另一种方式：
Relationship :
1、正向操作
2、反向操作
是指==》如上方的 黑金 白金 对应的是多被其它表的外键联合，且黑金 白金 对应的是多个数据对象， 这便是反向操作。
what 代表的是 usertype 表的行对象
下一篇： django 框架