MySQL知识总结

        服务软件：MySQL 或者Maria DB(二选一，MySQL收费，Maria DB免费）

操作软件：SQLYog

SQL端口：3306

环境变量：需配置装好MySQL的bin目录地址到系统变量Path中，我的电脑右键属性-高级系统设置

cmd小黑窗口快捷启动方式：

Maria DB启动小黑窗口：（应用程序4个中最下面一个黑色的程序启动）

CMD启动小黑窗口：

mysql -uroot -proot（第一种登录方式） 

2.1）mysql -uroot -p

2.1)  password (第二种登录方式）

语法：mysql.exe执行文件

代表参数

-u 用户名user，紧接着写的

-p 密码password，紧接着写的

MySQL常见1251错误解决办法：

问题：1251 client does not support authentication protocol requested by server;consider upgrading Mysql client

ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'
问题原因:
主要是由于mysql8以前的加密规则与mysql8以后的存在差异。

解决办法:

1、首先mysql必须是装好了，可以通过管理员身份打开的cmd中在mysql文件（若已配置环境变量就无须）下输入：

net start mysql是否服务启用成功

服务启动成功之后，输入命令：mysql -u root -p（第一次登录没有密码，直接按回车过）！
然后在mysql>后按照如下步骤输入：

ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;

(#修改加密规则 )

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; 

(#更新一下用户的新密码为password)

FLUSH PRIVILEGES; 

( #刷新权限)，完成这三步操作后用新的密码即可以登录。

CRUD:增删改查（Create新增，Retrieve检索查询 ,Update修改更新，Delete删除）

增： create database cgb2110 DEFAULT CHARACTER SET utf8; （创建表方式一）

        create database cgb211002 charset utf8;（创建表方式二）

        create table user(age int, name varchar(5)); （创建表）

        insert into user values(25，“tony”)；（表中插入一条数据）

        insert into user values(25，“tony”)，(28，“lucy”)；（表中插入二条数据）

删： drop database cgb2110; (删除库）

        drop table user;（删除表）

        delete from user;（删除user表中的所有数据） 

        delete from  use where id=100;（删除表中id是100的这一条数据） 

改：update user set id=100,age=22 where name="zhangsan";(改name=zhangsan这条数据）  

       update user set id=100,age=18；（表所有数据修改成id=100，age=18,不改变数据条数）  

查：show databases; （展示所有库）

       use cgb2110;（使用表cgb2110）

       show tables; (展示所有表）

       desc user; （查看表结构）

       select* from user; (从user 这个表中查询所有数据)  

       select * from tb_door order by tel desc;(从tb_door表中按照tel降序查询所有数据,desc降序）

       select count(1) from user; (从user这个表中查询所有记录的条数）

*注意：插入数据：int类型数据不用引号，varchar和char类型要用英文""双引或者‘’单引包裹起来。

            快捷键：选中库或者表按F5刷新；输入语句后分号前按F9执行，或者选中语句按F9执行

1.增加列：alter table user add age varchar(30) ;(从user表中新增列age并且数据类型为varchar）

                 alter table user add column age varchar(30) ;(与上效果相同，两种均可，可加column)

2.修改列：alter table user modify column age varchar(8); (user表中改age列数据类型为varchar）

3.删除列：alter table user drop column age;(user表中删除age列，后面不可用 ，分开删除多列） 

4.修改列名：alter table user rename column age to address;(修改user表中的age列名为address)

5.修改表名：alter table user rename to admin;(将user表里将user的表名改为admin)

6.set names gbk;    出现乱码解决方案- 输入代码确认后然后关窗口,临时方案，重启小黑窗口失效

7.修改数据库名：alter database cgb2110 modify name=cgb2111;（数据库改名，新cgb2111）??

字段约束（创建表时针对某列进行设置）：

唯一约束：create table user(id int unique,name varchar(10),age int);(创建id字段唯一约束）

                  INSERT INTO USER VALUES(1001,"jack",25);（OK，1 row(s) affected）

                  INSERT INTO USER VALUES(1001,"tony",28);(id不能相同NOK, 错误码: 1062
                  Duplicate entry '1001' for key 'user.id' )

                  INSERT INTO USER VALUES(1002,"tony",28);（ID不同，OK，1 row(s) affected）

非空约束：create table emp(id int not null,name varchar(10),age int);(创建id字段唯一约束）

                  INSERT INTO emp VALUES(1001,"lucy",20); （OK，1 row(s) affected）

                  INSERT INTO emp VALUES(NULL,"lili",28);(id为空，NOK, 错误码: 1048
                  Column 'id' cannot be null)

                  INSERT INTO emp VALUES(1002,'davida',30 );(id不为空，OK）

主键约束：CREATE TABLE employer(id INT PRIMARY KEY AUTO_INCREMENT,NAME   VARCHAR(10));（设置id为主键并自增，*注意主键列设置自增只能为int类型，设置主键不自增则可以为varchar类型）

                  INSERT INTO employer VALUES(NULL,"jack");(OK, id已经自增，设置null结果为1）

                    *设置主键后，后续插入值主键可以不用手动设置值。

数据类型

命名规则

• 字段名必须以字母开头，尽量不要使用拼音
• 长度不能超过30个字符（不同数据库，不同版本会有不同）
• 不能使用SQL的保留字，如where,order,group
• 只能使用如下字符az、AZ、0~9、$ 等
• Oracle习惯全大写：USER_NAME，mysql习惯全小写：user_name
• 多个单词用下划线隔开，而非java语言的驼峰规则

字符

• char长度固定，不足使用空格填充，最多容纳2000个字符，char(11)存储abc，占11位。查询速度极快但浪费空间
• varchar变长字符串，最多容纳4000个字符，varchar(11)存储abc，只占3位。查询稍慢，但节省空间。Oracle为varchar2
• 大文本: 大量文字（不推荐使用，尽量使用varchar替代）

以utf8编码计算的话，一个汉字在u8下占3个字节

注：不同数据库版本长度限制可能会有不同

数字

• tinyint,int整数类型
• float,double小数类型
• numeric(5,2) decimal(5,2)—也可以表示小数,表示总共5位,其中可以有两位小数
• decimal和numeric表示精确的整数数字

日期

• date 包含年月日
• time时分秒
• datetime包含年月日和时分秒
• timestamp时间戳，不是日期，而是从1970年1月1日到指定日期的毫秒数

图片

• blob 二进制数据，可以存放图片、声音，容量4g。早期有这样的设计。但其缺点非常明显，数据库庞大，备份缓慢，这些内容去备份多份价值不大。同时数据库迁移时过大，迁移时间过久。所以目前主流都不会直接存储这样的数据，而只存储其访问路径，文件则存放在磁盘上。

SQLYog报错1064解决办法：

现象如下，执行任何一个语句，均报错，但大多能在报错后返回结果

查看日志，发现sqlyog给所有的查询都同时执行了explain extended

在mysql 5.7或更早的版本内，这么做可以得到详细的执行扩展信息（SQLYog不仅仅会执行查询，还会向你展示性能等扩展情况），但mysql 8里，经过查阅文档发现，已经改为了直接使用explain，不含extended的语句
后来在sqlYog里找到 工具-选项-增强工具，取消explain扩展信息的勾选

之后就不会再遇到这个问题了

基础函数

lower        select 'ABC',lower('ABC') from dept; --数据转小写，显示字段名'ABC' 和‘lower('ABC')’;

                 select 'ABC' a,lower('ABC') b from dept; --数据转小写，转前后分别设置别名a和b显示
upper       select upper(dname) from dept --数据转大写，表头字段名显示upper(dname)
length      select length(dname) from dept --数据的长度，表头字段名显示length(dname)
substr      select dname,substr(dname,1,3) FROM dept; --截取[1,3],从第1个字符开始截取3个。
concat     select dname a,concat(dname,'123')b from dept --拼接数据123，显a原别名，b新别名
replace    select dname,replace(dname,'a','666') X from dept --把a字符替换成666，设置别名X

                 select dname a,replace(dname,'a','666') X from dept --功能同上，原别名a,转后别名X
ifnull        select ifnull(comm,10) comm from dept2--判断,如果comm是null,用10替换,设别名comm

2021.11.25

 一般where不能用别名

多表联查，where可以用别名，where没有什么要求

多条语句间不用;    可以选中再执行

封装：提高代码复用性， 高内聚

*调用空对象的方法抛出空指针异常; 对象==null，未赋值调用 常出现的Bugs常考 ，

提高了代码的健壮性。