mysql基础知识点汇总

mysql基础知识点汇总

1、安装mysql

• 1）mysql管网下载对应mysql版本https://dev.mysql.com/downloads/mysql/
• 2）解压，配置mysql.exe的目录到系统环境变量E:\mysql-5.7.20-winx64；
• 3）配置my.ini文件，新建data文件夹（路径与my.ini配置对应）
• 4）以管理员身份进入cmd，来到mysql.exe目录E:\python\mysql\mysql-8.0.12-winx64\bin>
• 5） 安装MySQL继续执行命令：mysqld -install
• 6）启动服务继续执行命令：net start MySQL
• 7）登录MySQL:E:\python\mysql\mysql-8.0.12-winx64\bin>mysql -u root -p
• 8）输入安装过程中提示的密码，即可登录

2、mysql 授予权限grant to ，召回权限revoke from

• 1）create user新建用户，设置密码：

create user 'marvin'@'%' indentified by 'password';

• 2）grant 把database_name数据库下面的全部表的权限给marvin：

grant all privileges on database_name.* to 'marvin'@'%';

• 3）revoke 对marvin收回database_name数据库insert,delete,update的权限：

revoke insert,delete,update on database_name from 'marvin'@'%';

• 4）create database 新建数据库，默认字符集编码utf8：

create database demo default charset utf8;

3、数据库

• 1）创建数据库：

create database database_name;

• 2）use 使用数据库（切换到该数据库里进行接下来的操作）：

use database_name;

4、数据表：

• 1）create 创建表：

create table table_name(
				字段1  类型(长度),
				字段2  类型(长度)
				)

• 2）desc 查看表结构:

 desc table_name;

• 3）drop table删除数据表：

drop table table_name ;

5、查插删改基本操作:

• 1）select 查询table表中满足a字段等于b的全部数据：

select * from table where a=b;

• 2）insert into向表table中插入数据：

insert into table (col1,col2,col3) values(val1,val2,val3);

• 如果是向所有字段（除自增字段）插入数据，可以简写成：

insert into table values(val1,val2,val3);

• 3）delete 删除table表中，满足cola=vala添加的数据：

delete * from table where cola=vala;

• 4）update 更新table表中，满足cola=vala的那条数据，并把colb的值改为val5:

update table set colb=val5 where cola=vala;

6、约束

• 1）主键约束:primary_key
• 2）联合主键约束（两个字段做主键，不能同时重复，且都不能为空）
• 3）unique约束、not null约束、default约束
• 4）外键约束：foreign_key
• 5）自增约束：auto_increment
• 6）可以在建表的时候添加字段约束，也可以建表后的添加、删除约束

7、设计范式

• 1）第一范式：每个元素都是独立不可拆分的，比如：中国四川省成都市武侯区，每一级单独存放。
• 2）第二范式：在满足第一范式的前提下，除主键外，每一列都必须完全依赖于主键；如果要出现不完全依赖，就要有联合主键。
• 3）第三范式：满足第二范式要求，表中的所有数据元素不但要能唯一地被主关键字所标识,而且它们之间还必须相互独立,不存在其他的函数关系。也就是说，对于一个满足2nd NF 的数据结构来说，表中有可能存在某些数据元素依赖于其他非关键字数据元素的现象,必须消除。

8、查询语句：

• 1）year选择year等于今年的数据

select * from table where year(birthday) = year(now()); 

• 2）like 、not like选择不姓王的学生

select * from students where name not like "王%"；

• 3）DESC、ASC 按年龄降序，sno升序（默认是ASC升序)

select * from students order by age DESC,sno; 

• 4）查询students表中的最大年龄和最小年龄max、min：

select max(age) as '最大',min(age) as '最小' from studens;

• 5）having附加条件，查询至少有2名男生的班级出来

select class from studens where sex='男' group by class having count(*)>1;   

• 6）左连接 left join 右连接 right join；从person和card两表中查询cardid相同的数据：

 select * from person left join card on persson.cardID=card.id; 

• 7）union联合两个查询语句，as别名，统一两表字段别名，显示到一起：

select tname as name,tsex as sex from teacher union select sname as name,ssex as sex from students ;

• 8）<分页> limit 从第11条开始，查5条<分页>

select * from students order by age limit 0,2 ;

• 从第11条开始，查5条<分页>

select * from students order by age limit 10,5;

• 每次查5条偏移5条<分页>

select * from students order by age limit 5 offset 5;

• 9） 用any表示至少一个：

select * from score where classno='2-105' and degree>any(select degree from score where classno='3-105') order by degree desc; 

• 10）distinct 查询所有不重复的classno：

select distinct classno from students;

• 11）in 、not in、distinct、exists、not exists查询两个查询关联，（not in，不包含用法类似）：
  （通常不推荐in 和not in、distinct ，推荐采用exists和not exists）

select sname from students where season in (select season_name in season);  

• 12）查询2-105班的平均成绩avg：

select avg(score) from score where classno='2-105';

• 13）虚表：
  select ‘x’;等价于 select ‘x’ from dual;

select 'x' from students where exists (select sno from lesons where teacher='张老师');

• 14）explain 生成执行计划，查看sql语句的效率：

explain select * from students where sno='10003';

explain select * from students where sname like '张%';

explain select * from students where sname like '%鹏';

9、mysql存储数据量最大的数据格式LONGBLOB LONGTEXT，具体多少根据服务器和mysql版本不同。

10、索引index：

• 1）索引可以加速查询，所以应该在经常用于查询筛选的列上建立索引； 索引会额外使用存储空间，会让增删改变慢，因为要更新索引，所以要慎用
• 2）给数据表table_name的cloum_name列创建索引，索引名称：idx_index_name:

create index idx_index_name on table_name(cloum_name);

• 3）drop inde删除索引idx_index_name：

drop index idx_index_name on table_name;

11、视图view ：

• 1）创建一个名为vw_stu_class是视图，视图内保存查询语句，下次可以重用：

create view vw_stu_class as select cno,sname,sno from students,class where students.sno=class.sno;

• 2）重用上面的查询语句：

select * from vw_stu_class ;