xwblog
0%

mysql

发表于 分类于
本文字数： 8.5k 阅读时长 ≈ 8 分钟

1.操作文件夹（库）

1
2
3
切换文件夹：use db1;

查看当前所在的文件夹：select database();

增

1
create database db1 charset utf8;

查

1
2
3
show create database db1;

show  database;

改

1
alter database db1 charset gbk;

删

1
drop database db1;

2.操作文件（表）

增

1
create table t1(id int ,name char);

查

1
2
3
4
5
show create table t1;//查看一个表的详细信息

show tables;

desc t1;//查看表结构

改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
修改字段

  alter table t1 modify name char(6)    //修改字段数据类型
  
  alter table t1 change name NAME char(7);//修改字段名
 
修改表名

  alter table t1 rename T1;  //修改表名
  
增添字段
   
   alter  table t1 addd age  int,add sex char(1);
   
   alter table t1 add age int*first*; //添加到那个位置
   
   alter table t1 add age int after name; //新字段添加到那个字段后
   
 删除字段
   
   alter table t1 drop name;

删

1
drop table t1;

3.操作文件内容（记录）

增

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
指定字段、不指定字段插入单条、多条数据

	insert into t1(id,name) values(1,'egon1'),(2,'egon2'),(3,'egon3');

	insert into t1 values(1,'egon1'),(2,'egon2'),(3,'egon3');

插入查询的结果

	insert into 表名（字段1，字段2...字段n） select (字段1，字段2...字段n) from  表2 where  ...;

复制表

	create table t1 select host,user from mysql.user;  //将查出的表数据和表结构给新建的表格

	create table t2 select host,user from mysql.user where 1>5;  // 将查出的表的结构给新的表格

	create table t3 like mysql.user;  //将表的结构给新的表格  （我未实现）

查

1
2
3
4
5
6
7
8
9
单表查询语法

	select  （distinct）字段1，字段2，字段3 from 库.表  where 条件 group by 分组条件 having 过滤 order by 排序字段 limit n；

	select id,name from db1.t1;

	select * from db1.t1;

//distinct  去除查询结果的重复，如查询员工的部门字段，可能一个部门重复显示，用distinct，让其单一显示。

改

1
2
3
update db1.t1 set name='sb';
 
 update db1.t1 set name='bing' where id = 2;

删

1
2
3
4
5
6
7
8
delete from t1; //删除数据，但是自增长的字段不会恢复到初值，一般与where连用
 
 delete from t1 where id = 2;
 
 truncate t1;//把文件所有数据清空
什么是存储引擎？

*->存储引擎就是表的类型*

4、存储引擎、数据类型

查看MySQL支持的存储引擎

1
show engines;  //查看MySQL支持的存储引擎

指定表类型/存储引擎

1
2
3
4
5
6
7
8
9
10
11
12
13
create table t1(id int)engine=innodb; //我们经常用的
create table t2(id int)engine=memory; //数据丢进去存在内存里，服务器软件关闭就清空了
create table t3(id int)engine=blackhole; //黑洞 数据丢进去就没有
create table t4(id int)engine=myisam;

insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);


查询表太乱时，后面加 \G
select * from mysql.user\G;

int

1
2
create table t5(id int(5) unsigned zerofill); //创建一个显示宽度为5的数据，不足五位的用0补位
create table t5(id int unsigned); //没有指定的话，默认为10位

float

1
float(M,D); M是一共的位数，D小数点占位数

表（文件）相关操作

1
now() -----------------------------------当前时间

虽然char 在存 和 取的时候长度（数值）可以变，但是我们可以让他现出原形。
设置：SET sqlmode = ‘PADCHARTOFULL_LENGTH’;

枚举和集合类型

5、约束条件

约束条件not null 和 default

1
2
3
4
5
6
7
create table t4(id int unsigned zerofill);

create table t5(id int,name char(6),sex enum('male','female') not null default 'male');
 
insert into t5( id,name) values(1,'bing');

以下这个错误可能是某个字段有空格：ERROR 1054 (42S22): Unknown column 'male' in 'field list'

约束条件 unique key

单列唯一
1
2
3
4
5
6
7
方式1
	create table department(id int,name char(10) unique);
	
方式2
	create table department(id int,name char(10) ,unique(name));
	
	insert into department values(1,'IT'),(2,'OP');
联合唯一
1
2
3
create table services(id int unique,ip char(15),port int,unique(ip,port) );

insert into services values(1,'192.168.11.10',80),(2,'192.168.11.10',81),(3,'192.168.11.13',80);

约束条件primary key ：（not null 、unique） 不能为空且唯一

1
存储引擎（innodb）:对于innodb存储引擎来说，一张表内必须要有一个主键
单列主键
1
2
3
4
5
create table t6(id int primary key,name char(16));
insert into t6 values(1,'bing'),(2,'chuan');

insert into t6(name) values('xiu');//老师讲为第一次默认为0，其他不能再插入。
#ERROR 1364 (HY000): Field 'id' doesn't have a default value
复合主键
1
2
3
create table t7(ip char(15),port int,primary key(ip,port));

insert into t7 values('1.1.1.1',80),('1.1.1.1',81);

约束条件 auto_increment

1
2
3
create table t8(id int primary key auto_increment,name char(15));

insert into t8(name) values('nihao'),('buha');

约束条件 foreign key:建立表之间的关系

1.建立表关系
1
2
3
4
5
#先建被关联的表，并且保证被关联的字段唯一
create table dep(id int primary key,name char(10),comment char(20));

#创建要关联的表
create table emp(id int primary key,name char(15),sex enum('male','female'),depid int,foreign key(depid) references dep(id) on delete cascade on update cascade);//on delete cascade 删除同步---》删除被关联的表中相关数据，关联的表相应数据也会同步删除 on update cascade 更新同步---》更新被关联的表中相关数据，关联的表相应数据也会同步更新
2.插入数据
1
2
3
4
5
6
7
8
9
#先往被关联表插入数据
insert into dep values(1,'IT','技术部门'),(2,'销售','销售部门');

#再往关联的表中插入数据
insert into emp values(1,'bing','male',1);

#操作被关联的表 同步更新 同步删除
delete from dep where id =1;
update dep set id=202 where id = 2;

6、表之间的三种关系：多对多、多对一、一对一

多对一

1
> **出版社 书（pressid int,foreign key(pressid) reference press(id)）;**![在这里插入图片描述](https://img-blog.csdnimg.cn/20200811100644175.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMzAxNDI4,size_16,color_FFFFFF,t_70)

多对多

1
2
> **作者 书**	![在这里插入图片描述](https://img-blog.csdnimg.cn/20200811101017578.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMzAxNDI4,size_16,color_FFFFFF,t_70)
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200811101117188.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMzAxNDI4,size_16,color_FFFFFF,t_70)

一对一

7、数据（记录）相关操作

单表查询

where 约束条件
1
2
3
4
5
6
7
8
9
10
11
select *  from employ where id > 7;
select name,salary from employee where post = "teacher" and salary > 8000;

select name,salary from employee where salary between 20000 and 30000;
select name,salary from employee where salary not between 20000 and 30000;

select * from employ where age = 73 or age = 80 or age = 66;
select * from employ where age in(73,80,66);

select * from employee where name like "jin%"; %代表后面任意字符
select * from employee where name like "jin__"; _代表任意一个字符
group by 分组

mysql> set global sqlmode = “ONLYFULLGROUPBY”;#设置过后，只能取分组的字段
分组过后，只能取分组的字段，以及每个组聚合的结果 select post from employee group by post;

没有group by则默认整体算作一组 select max(salary) from employee;

聚合函数：max、min、avg、sum、count

1
2
3
4
5
6
7
8
9
#每个职位有多少个员工
select post ,count(id) as emp_count from employee group by post;
select post ,max(salary) as emp_count from employee group by post;
select post ,min(salary) as emp_count from employee group by post;
select post ,ave(salary) as emp_count from employee group by post;
select post ,sum(age) as emp_count from employee group by post;
 
#group_concat
select post,group_concat(name) from employee group by post;
HAVING过滤
order by排序
1
2
3
select * from employee order by age asc;#升序
select * from employee order by age desc;#降序
select * from employee order by age asc,id desc;#先按照age升序排，如果age相同则按照id降序排
limit
1
2
select * from employee limit 3;
select * from employee order by salary desc limit 1;

0,5;从初始位置取5条

1
2
3
4
5
*语法顺序* 
select （distinct）字段1，字段2，字段3 from 库.表 where 条件 group by 分组条件 having 过滤 order by 排序字段 limit n；
		
*执行顺序*  
from表1、表2->on关系->（左右内）->where->group by->having->select->distinct->字段->order by->limit
like
1
2
3
4
select * from employee where name like "jin%";
#正则表达式
select * from employee where name regexp "^jin" ;
select * from employee where name regexp "^jin.*(g|n)$" ;#jin开头g、n结尾

多表数据查询

where连表 不建议用。有三种正规的连表方式

三种连表方式：内连接、左连接、右连接、全外连接

内连接：只取两张表的公共部分
1
2
表1 inner join 表2 on 表1.字段 = 表2.字段
select * from employee inner join department on employee .dep_id = department .id;
左连接：在内连接的基础上保留左表
1
2
表1 left join 表2 on 表1.字段 = 表2.字段
select * from employee left join department on employee .dep_id = department .id;
右连接：在内连接的基础上保留右表
1
2
表1 right join 表2 on 表1.字段 = 表2.字段
select * from employee right join department on employee .dep_id = department .id;
全外连接：在内连接的基础上保留左右两表没有对应关系的记录
1
2
3
表1 left join 表2 on 表1.字段 = 表2.字段 union 表1 right join 表2 on 表1.字段 = 表2.字段select * from employee left join department on employee .dep_id = department .id
union
select * from employee right join department on employee .dep_id = department .id；

8、select完整语法

9、select执行顺序

10、子查询

where exists();是否存在 true false

11、权限管理

1.创建账号

本地帐号
1
create user 'egon1'@'localhost' identified by '123';# mysql -uegon1 -p123
远程账号
1
2
3
4
5
create user 'egon2'@'192.168.31.10' identified by '123';# mysql -uegon2 -p123 -h 服务端ip

create user 'egon3'@'192.168.31.%' identified by '123';# mysql -uegon2 -p123 -h 服务端ip

create user 'egon4'@'%' identified by '123';# mysql -uegon2 -p123 -h 服务端ip

2.授权

1
2
3
4
user；* . *
db;db1.* tables_priv
db1.t1 columus_priv
id,name
查看权限
1
2
3
4
select * from mysql.user\G 查看用户权限
select * from mysql.db\G 查看库的用户权限
select * from mysql.tables_priv\G 查看表的用户权限
select * from mysql.columus_priv\G 查看表字段的用户权限
放权限 (select->查看权限 update->更新权限)
1
2
3
4
5
6
7
8
9
10
11
12
放所有权限
grant all on *.* to 'egon1'@'localhost';
grant select on *.* to 'egon1'@'localhost';

放库权限
grant select on db1.* to 'egon1'@'localhost';

放表权限
grant select on db1.t1 to 'egon1'@'localhost';

放字段权限
grant select(id,name),update(age) on db1.t1 to 'egon1'@'localhost';
回收权限
1
2
3
4
5
6
回收所有权限
revoke select on *. * from 'egon1'@'localhost';
回收库权限
revoke select on db1. * from 'egon1'@'localhost';
回收表权限
revoke select on db1.t1 from 'egon1'@'localhost';

12、登录功能设计

有问题的

修正过的

增、删、改、查

13、视图（将连接 的虚拟表存储起来）

14、存储过程

无参
有参

15、事务

-------------文章到底啦！感谢您的阅读-------------
感谢您的阅读和支持,您的支持是对我最大的帮助！

欢迎关注我的其它发布渠道