mysql
数据库操作：增、删、改、查
增 ：create database DBname; 删：drop database DBname; 改： 1、先删除原来数据库： mysqldump -uUsername -p DBname>FilePath/DBname.sql mysql> drop database DBname; 2、再创建新名字数据库： mysql> create database User; Query OK, 1 row affected (0.00 sec) 3、数据库导入： mysql -uroot -p User<user.sql 查：show databases;
数据库字符集操作：
创建时设置字符集：
数据库字符集： 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 和 gbk 10 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 names You can turn off this feature to get a quicker startup with -A Database changed mysql> 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: 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 | | | | +--------+---------------------+------+-----+---------+----------------+ 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: 0 mysql> 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 exists mysql> 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: 0 mysql> 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: 0 mysql> 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 pymysql user=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 python3 from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index from sqlalchemy.orm import sessionmaker, relationship from sqlalchemy import create_engine import 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 框架
浙公网安备 33010602011771号