mysql学习笔记（个人总结）

MySQL

1. 数据库的增删查改

1.1增加

create database 数据库名称;

1.2删除

drop database [name];

1.3查询

show create databese [name];
show databases;

1.4改动

只能改动数据库的字符串类型

alter database [name] charset utf8;

2. 数据表的增删查改

2.1增加

create table [name](
    字符串 字符串类型 约束条件,
    字符串 字符串类型 约束条件,
    字符串 字符串类型 约束条件
                   );

2.2删除

drop table [name];

2.3改动

修改表名
alter table [name] rename [new_name];
增加字段
alter table [name] add [name] [type] [约束条件];
alter table [name] add [name] [tyoe] [约束条件] frist; 将新建的数据放到第一位
alter table [name] add [name] [tyoe] [约束条件] after [字段名]; 将新建的数据放到某一字段的后面
删除字段
alter table [name] drop [字段名];
修改字段
alter table [name] modify [字段名字] [字段类型] [字段约束条件];
alter table [name] change 旧字段名 新字段名 旧字段类型 [完整的性的约束条件];
alter table [name] change 旧字段名 新字段名 新字段类型 [完整的性的约束条件];

2.4复制表

/* 创建一个表来存放查询出来的数据*/
create table [name] select * from [name];
/* 创建一个表但是不需要表的数据只要结构*/
create table [name] select * from [name] where [一个永远不可能成立的条件];
/*这边也有一种简单的表结构拷贝方法*/
create table [name] like [table_name];

2.4表的查询

2.4.1 简单的表查询

desc [name];
show create table [name]; /* 因为msyql会给你一大堆的一行的横线，可以在尾巴加上一个G*/
show create table [name]G;
show tables;
desc [name];

2.5 更新数据

语法
update name set 字段一=值一,字段二=值二···;
实例
update mysql.user set password=password('123') where user='root' and host='localhost';

3. 数据类型

3.1int整数类型

int(num) unsinged [zerefill] /*用零来填充*/
/* 建议是在int后面不要加上的宽度类型 
	！！！！！因为他规定不是存储范围而是显示的范围
	如果使用的话就会显得很不专业（面试减分项）  unsinged 表示的是无符号类型*/

3.2浮点数类型

3.2.1Float double decimal

create table [name] (x float(整数部分,小数部分));  /* float(255,30)*/
create table [name] (x double(整数部分,小数部分));  /*double(255,30) */
create table [name] (x decimal(整数部分,小数部分));  /*decimal(65,30) */

/* 
	float 精度最低
	double 精度还不错
	decimal 精度高 实现的底层是字符串类型的
	其实日常的使用使用float类型即可，decimal精度虽然很高但是整数很低呀
*/

3.3日期类型 now()函数

date 年月日

time 时分秒

datetime年月日时分秒

insert into [name] values(now());  /* now() 该函数表示的是插入当前系统的是时间*/

小技巧

当我们代码输入错误时候 可以使用c来结束代码的执行

3.4字符串类型

字符串的宽度指的就是字符的个数

char定长 不管你传进来的字符够不够长度，我就存储这么多

varchar变长 你传进来几个我就存储几个

其实 char不一定就比varchar浪费空间

正常情况下 varchar没有满的情况下肯定是比char省的

但是只要是varchar只要一满 那就意味着实际存储的字符数比设置的长度加上 1

varchar(4) 'abcd' 5bytes  'abc' 3bytes  /* 这个多余的一  是头的那个一 */
因为多了一个头  所以读取数据速度相对而言会比 char 速度慢一点
char(4) 'abcd' 4bytes  'abc'  4bytes

小技巧

char_length 查看有几个char类型的查看字符数

length；查看字节数

3.5枚举类型 enum

3.6集合类型 set

字段的值只能在给定的范围里面进行选择 （例如：多选框，单选框）

enum 当选 可以用来sex性别定为

set 多选 在给定的一个范围进行选择

/*例子
*/
create table [name] (
	id int,
    name varchar,
    sex enum('男'，'女','等多个'), /*male  female*/
    hobbies set('爱好一','爱好二,'爱好三')
);


insert into [name] values(1,'shabi','男','爱好一');
/* 一旦你传入的数据不在我设定的表格里面的时候    虽然说不会报错  但是插入的话那只能是空值*/

4. 约束条件

4.1非空约束 not null

create table [name](
	id int not null,
    name varchar(10) not null
);

4.2唯一约束 unique

单列唯一

    create table [name] (
        id int unique,
        name varchar(10) unique
    )
    /*方式二 */
    create table [name] (
        id int,
        name varchar(10),
        unique(id),
        unique(name)

    )

联合唯一

意思就是两个和起来不能有重复的

123+456 = 123456 yes

124+456= 124456 yes

456+123=456123 yes

create table [name](
	id int,
    ip varchar(15),
    port int,
    unique(id),
    unique(ip,port)
);

4.3主键约束 primary key

单列主键

对于**innodb存储引擎**来说 一张表必须有一个主键约束，如果没有自定义的话，那么innodb会自己帮我们加一个

/* 1 */
create table [name](
    id int primary key,
    name varchar(10)
);

/* 2 */
create table [name](
	id int,
    name varchar(10),
    primary key(id)
);
/* 3 */
create table [name](
	id int not null unique,
    name varchar(10)
);
/*	对于主键约束(primary key)来讲就是  不为空(not null)，且唯一约束(unique)	*/

联合主键

意思就是两个和起来不能有重复的

123+456 = 123456 yes

124+456= 124456 yes

456+123=456123 yes

create table [name](
	id int,
    ip varchar(15),
    port int,
    primary key(ip,port)
);

4.4自增约束 auto_increment

加入自增约束的时候不能直接使用auto_increment 需要加入主键约束primary key 唯一约束unique

create table s1(
	id int primary key auto_increment,
    name varchar(100)
);
/*	字段增长默认从1开始增长步长为1  */
/*	而且是以上一个插入的数字为基准开始的，也可以自己插入7或者其他的，他会以此为标准	*/


show variables like ‘auto_incre%';
/*anto_increment_increment c
auto_increment——offset 起始变量
设置步长
set session auto_increment_increment = 5；当前的设置
set global auto_increment_increment = 5  全局变量
设置 起始变量
set session auto_increment_offset = 5  当前的设置
set global auto_increment_offset = 5  全局设置
建议是 increment 和 offset 要一样的  或者 起始变量小于步长*/

小技巧 truncate [name];

delete from [name];
/*	如果不写入任何的名字 那么全部都符合条件 那就是全部删除 */
truncate [name];
/* 不建议使用delete删除数据 自增函数还是会保存 可以使用truncate*/

4.5外键约束 foreign key

建立一个表之间的约束

create table [name](
	id int primary key,
    name varchar(10)
);

/*  外键先行原则 */
/* 需要先建立被链接的表  并且保证被关联的字段还得是唯一的 */

create table [name](
	id int primary key,
    name varchar(10),
    sex enum('male','female'),
    besides_id int,
    foreign key(besides_id) references [resides name](id) on delete cascade on update cascade
);
/* on delete cascade 删除同步 on update cascade 更新同步*/
/*这段绝对会报错*/
insert into [name] values(1,'fsf','male',1);
/* 插入的数据的时候也要遵守外键先行原则 */
delete from [name] where [condition];
/* 删除数据的时候也要遵守外键先行原则*/

5.0与表之间的关系

多对一

出版社 书

多对多

作者 书

一对一

一个对一个 唯一

6.0记录的增删改查

6.1插入数据 insert

常规插入
insert into [name] values(data);
insert into name(data) values(data);
指定字段插入
insert into name[字段1··] values (data);
多个数据插入
insert into name values(),()···;
插入搜寻结果
insert into name[字段名1，···] select [字段名] from name where [condition];

6.2更新数据 update

语法
update name set 字段一=值一,字段二=值二···;
实例
update mysql.user set password=password('123') where user='root' and host='localhost';

6.3删除数据 delete

语法
delete from name where confition;
实例
delete from mysql.user where user='root';

6.4查询数据 select

concat() 函数 concat_ws()函数

1.0 单表查询

！！！！！顺序

语法顺序 select > destinct (去重)>from > where > group by > having > order by> limit;

执行顺序 select> from > where>group by > having> distinct(去重)>order by> limit

6.4.1.0 简单查询

select 字段名 from 数据库.表名;
select * from [name];
避免重复 distinct 
select distinct [字段名] from [name];
通过四则运算查询
select salary*12 from [name];  将工资乘12 求年薪
定义一个显示的格式 /*concat() 函数用来链接字符串  */
select concat('name':name,'sex':sex,····) as 字段1,字段2,··· from [name]; /* as 是用来取别名的*/
select concat('name':name) from [name];   
/* concat_ws() 第一个参数就是分割符 */
select concat_ws(':',name,age,···) from [name];

6.4.1.1where约束条件

单条件查询 <,>,<=,>=,=
select * from [name] where id>7;
select * from [name] where id=7;
select * from [name] where id<7;

多条件查询 条件链接and
select * from [name] where id=7 and name='sahbi';
select * from [name] where id>=7 and id<=9; 		 /*查询id大于等于7并且小于等于9的*/
select * from [name] where id>=7 or id<=9; 		/* 查询id大于等7或者小于的9的*/

关键字between 数字 and 数字
select * from [name] where id between 7 and 9;  	/*  等价select * from [name] where id>=7 and id<=9;*/
select * from [name] where id not between 7 and 9; /*  等价 select * from [name] where id>=7 or id<=9; */

关键字 in
 select * from [name] where id in (7,9);  	 /*  等价 select * from [name] where id=7 or id=9; */

关键字 is null(判断某个字段是否为空的话不能使用等号)
 select * from [name] where name='';		 /* null不是一个值 而是一个类型 所以判断某个字段是否为空的话不能使用等号*/
 select * from [name] where 字段名 is null;
 select * from [name] where 字段名 is not null;
 
 关键字模糊查询 like
 通配符'%'  表示一个或多个
 select * from where name like 'sha%';
 
 通配符'_'  表示一个
  select * from where name like 'shab_';

6.4.1.2 group by 分组

select sex from [name] group by sex;   /* 按照性别分组  这样子没有经过设置的话拿到的是给个人的  一定要经过一系列的设置   */
set global sql_mode="only_full_group_by";  /* 在全局变量中设置只取分组的字段*/
/*我们按照sex进行分组那么select查询的字段只能是sex，想要获取其他的相关信息，就需要借助group by关键字和group_concat()函数*/
select sex,group_concat(sex) from [name] group by sex;   #按照性别分组，并查看组内的成员
select sex,group_concat(sex) as sexs from [name] group by sex;

聚合函数 avg max min sum count函数

select id,count(id) from [name] group by sex;
select avg(id) from [name] group by sex;
select max(id) from [name] group by sex;
select sum(id) from [name] group by sex;
c
/* 如果没有group by 那就默认一整组*/
select max(id) from [name];

注意事项 group_concat()函数

我们按照sex进行分组那么select查询的字段只能是sex，想要获取其他的相关信息，就需要借助group by关键字和group_concat()函数

正确应该是和select进行组合使用

分组应该是把大多数相容(相同)的进行分组

6.4.1.3 having过滤

 select score,avg(people) as p from study1 group by score having p>1713766;

having就是写条件的和where差不多 但是又不一样

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aek65B59-1647532768522)(C:UsersYUNsAppDataRoamingTypora ypora-user-imagesimage-20210405234837490.png)]

注意事项

执行优先级从高到低 where>group by>having

where发生在group by之前，因而where中可以有任意字段，但是绝对不能使用聚合函数

having发生在group by之后，因而having可以使用分组之后的字段，无法提取到其他的字段，可以使用聚合函数

6.4.1.4order by排序

升序排序(默认的) asc

 select * from study1 order by score;

降序排序 desc

 select * from study1 order by score desc;

联合使用

 select score,avg(people) as p,group_concat(title) from study1 group by score having p>1716 order by p;
  select score,avg(people) as p,group_concat(title) from study1 group by score having p>1716 order by p desc;

6.4.1.5limit限制条数

select * from study limit 3;


select * from study order by score desc limit 3;

6.4.1.6 正则查询

类似于模糊查询 但是又比模糊查询简单一点

select * from [name] where name like 'sha%';			/* 查找sha开头的*/


select * from [name] where name regexp '^sha';     /* 查找sha开头的*/
select * from [name] where name regexp '^sha.*i$';		/* 查找sha开头然后以结尾的*/
select * from [name] where name regexp '^sha.*(i|o)$';   /*查找sha开头然后以i或者o结尾的*/

2.0 多表查询

6.4.2.0 连表操作 join ~ on ~

小基础

这个就是在笛卡尔积的基础上，进行连表操作

select * from s1,s2_link where s1.link_id=s2_link.id;    /* 最基础的链接 */

内连接

只取两张表有关联的内容

select * from s1 join s2_link on s1.link_id=s2_link.id;

左连接 left

在内链接的基础上保留左表

 select * from s1 left join s2_link on s1.link_id=s2_link.id;

右连接 right

在内连接的基础上保留右表的全部数据

 select * from s1 right join s2_link on s1.link_id=s2_link.id;

全部外链接 union

在内连接的基础上保留左右两表没有对应关系的记录

就是 左右链接合起来

select * from s1 left join s2_link on s1.link_id=s2_link.id
union
select * from s1 right join s2_link on s1.link_id=s2_link.id;

6.4.2.1!!!链接顺序

定义顺序（语法顺序）

select > from > join on > where > group by > having > order by > limit

执行顺序

from > on > join > where > group by > having > select > distinct(去重) >  order by > limit

7.0 权限操作

7.1创建账号

7.1.1 本地账号

create user 'name'@'localhaost' identified by 'passwd';

7.1.2远程账号

create user 'name'@'网段' identified by 'passwd';
create user 'name'@'192.168.10.%' identified by 'passwd';
create user 'name'@'%' identified by 'passwd';


mysql -name -p -h 服务器网段

7.2权限管理

grant授权 revoke收回权限

user:*.*   /*全部开放*/ --
db:database.*   /* 指定库 */
tables_priv:database.table.*  /* 指定表 */
columns_priv:database.table.*  /* 指定数据 */


grant all on *.* to 'name'@'localhost';   /*  给与全部权限 */
grant select on *.* to 'name'@'localhost';  	/*	只给予查询权限 */
revoke all on *.* from 'name'@'localhost';	 /* 收回全部权限	*/
revoke select on *.* from 'name'@'localhost'; 	/*	收回查询权限 */


grant all on database.* to 'name'@'localhost';   /*  给与该数据库全部权限 */

刷新页面

flush privileges;

7.2.1查看用户

select * from mysql.user;

8.0 pymysql操作

8.1 连接数据库,执行操作语句

import pymysql
#建立链接
cone = pymysql.connect(user='root',password='123456',host='localhost',port=3306,database='test',charset='utf8')
#建立游标
c = cone.cursor()
#写语句
sql = '''
        create table movie(
        movielink text not null,
        imglink text not null,
        title text not null,
        English text not null,
        score double not null,
        people int not null,
        inq text not null
        );
    '''
#执行语句
c.execute(sql)
#保存
cone.commit()
#关闭
c.close()
cone.close()


#插入单条数据的时候
sql1 = '''

    insert into [name] values (s%,s%)
    '''
c.execute(sql1,('hhh','hhh'))
#插入多条数据的时候
sql1 = '''

    insert into [name] values (s%,s%)
    '''
c.executemany(sql1,[(),()])

8.1.1 查询数据

c.fetchone()   #一个
c.fetchall()    #全部
c.fetchmany()   #很多 可以规定的



#建立游标  在游标加入pymysql.cursors.DictCursor这个可以直接变成字典
cursor = cone.cursor(pymysql.cursors.DictCursor)


print(cursor.fetchall())
print(cursor.fetchall())

因为我们想不停的查询全部

print(cursor.fetchall())
cursor.scroll(3,mode='absolute')    #相对绝对位置移动

print(cursor.fetchall())
cursor.scroll(-3,mode='relative')   # 相对当前位置移动
print(cursor.fetchall())
cursor.close()
cone.close()

8.2接收返回数据