python mysql 开发_python开发之MYSQL

3.学习SQL语句规则

操作文件夹

1.显示数据库

show databases;

默认数据库：

mysql-用户权限相关数据

test-用于用户测试数据

information_schema-MySQL本身架构相关数据

2.创建数据库

create database db1; 创建文件夹

create database db1 default charset utf8;为创建的文件夹设置字符编码utf-8

3.查看数据库

use name;

显示当前使用的数据库中所有的表：show tables;

4.用户管理

创建用户

create user '用户名'@'IP' identified by 'pwd';

删除用户

drop user 'user'@'IP'；

修改用户

rename user 'user'@'IP'；to 'new user'@'IP'；；

修改密码

set password for 'user'@'IP' =Password('新密码');

PS.用户权限相关数据保存在mysql数据库的user表中，所以也可以直接对其进行操作(不建议)

操作文件

show tables;查看文件

create table t1(id int,name char(10) ) default charset=uft8;创建表结构，id和名称，设置创建表结构的字符编码

create table t1(id int,name char(10)) engine=innodb default charset=utf8;engine=innodb：回滚，在突然断电，断网的倩况下，会保存你上次传输的数据，不会让数据丢失，保持数据的一致性，

create table t1(id int auto_increment,name char(10))engine=innodb default charset=utf8;重点，auto_increment：自增(用于数字类型的)

desc 名称; 查看表数据结构

show create table 表名称;查看该表的建表的代码

show create table 表名称 \G；以竖行结构查看

alter table 表名称 AUTO_INCREMENT=20;设置表的id步长从多少开始

基本会话级别：只限于你当前的用户改变步长，不会改变其他的用户，且用户关闭之后也会消失。

show session variableslike 'auto_inc%';查看全局变量set session auto_increment_increment=2;设置会话步长set session auto_increment_offset=10;设置步长

基于全局级别：适用于全局的所有用户使用的步长

show global variableslike 'auto_inc%';查看全局变量set global auto_increment_increment=2;设置会话步长set blobal auto_increment_offset=10;设置步长

自增步长

基础表级别：CREATE TABLE`t5` (

`nid`int(11) NOT NULLAUTO_INCREMENT,

`pid`int(11) NOT NULL,

`num`int(11) DEFAULT NULL,PRIMARY KEY(`nid`,`pid`)

) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=2 DEFAULT CHARSET=utf8CREATE TABLE`t6` (

`nid`int(11) NOT NULLAUTO_INCREMENT,

`pid`int(11) NOT NULL,

`num`int(11) DEFAULT NULL,PRIMARY KEY(`nid`,`pid`)

) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=20 DEFAULT CHARSET=utf8

SqlServer自增步长

1 username表：2 create tableusername(3 id int not null auto_increment primary key,4 name char(10)not null,5 gender char(10) not null

6 )engine=innodb default charset=utf8;7

8 username表内容：9 insert into username(name,gender) values('杨翔','男'),('alex','男'),('杨翔1','男'),('alex1','男');10

11 zhuju表：12 create tablezhuju(13 id int not null auto_increment primary key,14 name char(10) not null

15 )engine=innodb default charset=utf8;16

17 zhuju表内容：18 insert into zhuju(name) values('c1'),('c2'),('c3'),('c4');19

20 un表：21 create tableun(22 id int not null auto_increment primary key,23 user int not null,24 hostid int not null,25 unique fk_un(user,hostid),#唯一索引的使用26 constraint fk_cc foreign key(hostid) referenceszhuju(id),27 constraint fk_c foreign key(user) references username(id))engine=innodb default charset=utf8;28

29 PS.30 唯一：31 约束不能重复(可以为空)32 主键不能重复(不能为空)

唯一索引unique

外键，一个特殊的索引，只能是指定内容

creattablecolor(

nidint not null primary key,

namechar(16) not null)create tablefruit(

nidint not null primary key,

smtchar(32) null,

color_idint not null,constraint fk_cc foreign key (color_id) referencescolor(nid)

)

外键

a. 用户表和部门表

用户：

alex1root1egon2laoyao3部门:

服务

保安

公关===》 一对多，即一个人可以有同时在多个部门

示例：create table user(

idint not nll auto_increment primary key,

namechar(10)

job_idint not null;constraint fk_user_depar foreign key (id) referencesjob(id)

)engine=innodb default charset=utf8;insert into user(name) values('alex'),('root'),('egon'),('laoyao');create tablejob(

idint not nll auto_increment primary key,

namechar(10))engine=innodb default charset=utf8;

b. 用户表和博客表

用户表：1alex2root3egon4laoyao

博客表：

FK()+唯一1 /yuanchenqi/ 4

2 /alex3714/ 1

3 /asdfasdf/ 3

4 /ffffffff/ 2

===>一对一create tableuserinfo1(

idint auto_increment primary key,

namechar(10),

genderchar(10),

emailvarchar(64)

)engine=innodb default charset=utf8;create tableadmin(

idint not null auto_increment primary key,

usernamevarchar(64) not null,

passwordVARCHAR(64) not null,user_id int not null,unique uq_u1 (user_id),CONSTRAINT fk_admin_u1 FOREIGN key (user_id) REFERENCESuserinfo1(id)

)engine=innodb default charset=utf8;

c. 用户表(百合网) 相亲记录表

示例1：

用户表

相亲表

示例2：

用户表

主机表

用户主机关系表===》多对多create tableuserinfo2(

idint auto_increment primary key,

namechar(10),

genderchar(10),

emailvarchar(64)

)engine=innodb default charset=utf8;create tablehost(

idint auto_increment primary key,

hostnamechar(64)

)engine=innodb default charset=utf8;create tableuser2host(

idint auto_increment primary key,

useridint not null,

hostidint not null,uniqueuq_user_host (userid,hostid),CONSTRAINT fk_u2h_user FOREIGN key (userid) REFERENCESuserinfo2(id),CONSTRAINT fk_u2h_host FOREIGN key (hostid) REFERENCEShost(id)

)engine=innodb default charset=utf8;

外键变种

补充：

字符串:char固定长度，限定的长度，如果没写满就用空格代替varchar 不定长度 有多少占多少

创建表结构：

-create tablet1(

列名 类型null,

列名 类型not null,

列名 类型not null auto_increment primary key,

idint,

namechar(10)

)engine=innodb default charset=utf8;

#innodb:支持事物，原子性操作

#null:不用写就使用null代替

#not null:必须要写内容，不能使用null代替

#auto_increment:表示自增，当使用了自增，那么久不需要在insert(添加)的时候将id写到key中

#primary key:表示约束(不能重复且不能为空)，加速查找

PS.在创建表结构的时候，最后面的一个列不能加，

bit[(M)]二进制位(101001)，m表示二进制位的长度(1-64)，默认m＝1

tinyint[(m)] [unsigned] [zerofill]小整数，数据类型用于保存一些范围的整数数值范围：

有符号：-128 ～ 127.

无符号：

～255特别的： MySQL中无布尔值，使用tinyint(1)构造。int[(m)][unsigned][zerofill]整数，数据类型用于保存一些范围的整数数值范围：

有符号：-2147483648 ～ 2147483647无符号：

～4294967295特别的：整数类型中的m仅用于显示，对存储范围无限制。例如：int(5),当插入数据2时，select 时数据显示为： 00002

bigint[(m)][unsigned][zerofill]大整数，数据类型用于保存一些范围的整数数值范围：

有符号：-9223372036854775808 ～ 9223372036854775807无符号：

～18446744073709551615

decimal[(m[,d])] [unsigned] [zerofill]准确的小数值，m是数字总个数(负号不算)，d是小数点后个数。 m最大值为65，d最大值为30。

特别的：对于精确数值计算时需要用此类型

decaimal能够存储精确值的原因在于其内部按照字符串存储。FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]单精度浮点数(非准确小数值)，m是数字总个数，d是小数点后个数。

无符号：-3.402823466E+38 to -1.175494351E-38,1.175494351E-38 to 3.402823466E+38有符号：1.175494351E-38 to 3.402823466E+38

**** 数值越大，越不准确 ****

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]双精度浮点数(非准确小数值)，m是数字总个数，d是小数点后个数。

无符号：-1.7976931348623157E+308 to -2.2250738585072014E-308

2.2250738585072014E-308 to 1.7976931348623157E+308有符号：2.2250738585072014E-308 to 1.7976931348623157E+308

**** 数值越大，越不准确 ****

char(m)

char数据类型用于表示固定长度的字符串，可以包含最多达255个字符。其中m代表字符串的长度。

PS: 即使数据小于m长度，也会占用m长度varchar(m)

varchars数据类型用于变长的字符串，可以包含最多达255个字符。其中m代表该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中。

注：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡texttext数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。

mediumtext

ATEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.

longtext

ATEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters.

enum

枚举类型，

An ENUMcolumn can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)

示例：CREATE TABLEshirts (

nameVARCHAR(40),

size ENUM('x-small', 'small', 'medium', 'large', 'x-large')

);INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');set集合类型

ASET column can have a maximum of 64 distinctmembers.

示例：CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');

DATE

YYYY-MM-DD(1000-01-01/9999-12-31)

TIME

HH:MM:SS('-838:59:59'/'838:59:59')YEARYYYY(1901/2155)DATETIMEYYYY-MM-DD HH:MM:SS(1000-01-01 00:00:00/9999-12-31 23:59:59Y)TIMESTAMPYYYYMMDD HHMMSS(1970-01-01 00:00:00/2037 年某时)

数据结构

实例：

create tablet1(

idint not null auto_increment primary key,

namechar(10)

)

清空表：

delete fromt1;清除表中的所有内容，不删除文件,不是初始化表，比如计数这类的就不会清除掉truncate tablet1;清除表中的所有内容，不删除文件,初始化表，比如计数这类的就会清除掉delete from tb12 where id !=2；判断不等于2的id删除delete from tb12 where id =2；判断等于2的id删除delete from tb12 where id > 2；判断大于2的id删除delete from tb12 where id >=2；判断小于2的id删除delete from tb12 where id >=2 or name='alex'；判断大于等于2或者name='alex'删除delete from tb12 where id >=2and name='alex';判断大于等于2和name='alex'删除

删除表：

drop table t1;清除表中的内容，且文件也删除掉

插入数据：

设置值：insert into t1(id,name) values(1,'alex');在创建好了的表结构中插入数据，内容都是要对应好的insert into 表名称(name,age) select name,age from表名称; 添加内容的时候可以借用其他的表中的列名称的内容进行添加

更新替换值：update 表名称 set name='alex' where id>12 and name='xxx';#更新值，判断 id>12 and name='xxx'的将他们的name更改为‘alex’update 表名称 set name='alex',age=19 where id>12 and name='xx'；#更新值，判断id>12 and name='xx'的将他们的name='alex',age=19

删除数据：

delete from t1;删除表数据

delete from t1 where id<6;判断id小于6的删除

查看数据：

select * from t1; 查看表中的结构信息

select id,name from tb12;根据指定的名称查看

select id,name from tb12 where id > 10 or name ='xxx'; 根据判断查看

select id,name as cname from tb12 where id > 10 or name ='xxx'; 根据判断查看

select name,age,11 as yuan from tb12;指定的名称查看，在后面写11:赋值新的一行，内容都是11，列中显示的就是yuan

as 可以更改名称

可以将临时表的结果放到表中显示出来selectstudent_id,

(select num from score as s2 where s2.student_id=s1.student_id and course_id =1) as数学from score ass1

通过列里面的num值，找出新了一列，只能查找当前表里面的

命名的方式

(条件语句)as t:将整个条件语句赋值给t

表中的其他操作

操作各种判断语句的顺序

left join :跟在主查询的后面 >where>group by > order by > limit

select * from tb12 where id != 1；查看id不等于1的所有内容select * from tb12 where id in (1,5,12);指定我所输入的id进行查看select * from tb12 where id not in (1,5,12);指定我没有输入的id进行查看select * from tb12 where id in (select id fromtb11)查看我所指定的其他表的idselect * from tb12 where id between 5 and 12;闭区间，取左—右

命令:alter table 需加外键的表 add constraint 外键名 foreign key(需加外键表的字段名) referencnes 关联表名(关联字段名);

注意：外键名不能重复

通配符

select * from tb12 where name like "a%";#以a开头的字符串select * from tb12 where name like "a_"；#a开头的所有(一个字符)

分页

select * from tb12 limit 10;#从开头取前十个select * from tb12 limit 10,10;#从第10个开始取十个select * from tb12 limit 10 offset 20;也是一种取值的方式

用做分页的一个效果

# page= input('请输入要查看的页码')

# page= int(page)

# (page-1) * 10#select * from tb12 limit 0,10; 1#select * from tb12 limit 10,10;2

排序

select * from tb12 order by id desc; 大到小select * from tb12 order by id asc; 小到大

取后10条数据select * from tb12 order by id desc limit 10；

分组

select count(id),max(id),part_id from userinfo5 group bypart_id;

聚合函数count：计数count(1) :根据分组后的数量count(1) ,count(主键)：效率高，max：最大值min：最小值sum：求和avg：平均数distinct列名 ：去重 但是效率不高**** 如果对于聚合函数结果进行二次筛选时？必须使用having ****

select count(id),part_id from 表 group by part_id having count(id) > 1;select count(id),part_id from 表 where id > 0 group by part_id having count(id) > 1;

连表

多表连接：select class.caption,teacher.tname,student.sname,course1.cname fromscoreleft join student on score.student_id=student.sidleft join course1 on score.corse_id=course1.cidleft join class on student.class_id=class.cidleft join teacher on course1.teacher_id=teacher.tid \G;select * from 表名称 left join userinfo5 on userinfo5.part_id =department5.id;左的表显示全部的信息，select * from 表名称 right join department5 on userinfo5.part_id =department5.id；#右边的显示所有的信息select * from 表名称 inner join department5 on userinfo5.part_id =department5.id；将出现null时一行隐藏，

就是在关联的时候没有任何的数据，那么显示的就是null,使用inner进行隐藏

ps.链表是要建立在已经建立了外键的情况下进行关联

if 判断语句

select avg(if(isnull(score.num),0,score.num)),teacher.tname fromscoreleft join course on course.cid=score.course_idleft join teacher on course.teacher_id=teacher.tidgroup byscore.course_idif(isnull(判断的条件),true的结果,false的结果)

case when then sele end 的用法

解释：SELECT

case -------------如果

when sex='1' then '男' -------------sex='1'，则返回值'男'

when sex='2' then '女' -------------sex='2'，则返回值'女'

else 0 -------------其他的返回'其他’

end -------------结束

from sys_user --------整体理解： 在sys_user表中如果sex='1'，则返回值'男'如果sex='2'，则返回值'女' 否则返回'其他’