MYSQL 语句

标注：
1.[root@host]# 这个代表在cmd操作
2.mysql> 这个表示登陆mysql服务操作
3.window 怎么直接打开DOS（管理员权限模式）。可以如下：
直接搜索栏输入CMD后（不要立即回车进入），按住CTRL+SHIFT+ENTER键可直接进入管理员模式命令行。

一、登入MYSQL数据库

1.linux打开mysql服务

linux::sudo service mysql start  #打开MySQL 服务

2.window打开mysql服务

在管理员状态下

[root@host]# net start mysql

如果出现服务名无效或者MySQL正在启动 MySQL无法启动
因为net start +服务名，启动的是win下注册的服务。此时，系统中并没有注册mysql到服务中。即当前路径下没有mysql服务。
则在管理员权限下进入mysql server的bin目录

[root@host]# mysqld --install

成功：出现Service successfully install代表你已经安装成功，不成功请检查你是否有在管理员权限下进行。
然后就可以在任意位置输入

在管理员状态下输入[root@host]# net start mysql //如果是mysql8.0服务名为mysql80

当然还有可以到此电脑->管理->服务找到mysql相关的服务直接打开。

3.登入本地mysql服务

[root@host]# mysql -u 用户名 -p   
Enter password:******  #登录后进入终端
mysql>

我们每个mysql最初始都会有一个root用户，代表最高权限的用户。
在其他应用连接mysql数据库时，我们经常会用到’localhost’，‘用户名’，‘密码’或者’服务器地址’，‘用户名’，‘密码’
localhost代表本地，意思是mysql在本地的本地数据库（不用联网也能登陆），127.0.0.1也是一样的意思。换成服务器的ip地址，就可以连接到服务器的mysql数据库，就可以实现数据的云操作。
在cmd连接远程服务器mysql

4.登入服务器mysql服务

[root@host]# mysql -h192.168.0.110 -u用户名 -p密码

如果出现ERROR 1045 (28000): Access denied for user ‘用户名’@‘操作的IP地址’ (using password: YES)可能是密码不对的原因。
（-h就是host，-u就是user，-p就是password，用空格隔开-x后面的信息也是可以的）
如果不输入-h，就是默认本地127.0.0.1
所以本地连接其实也可以是
登入本地mysql的另一种方式

[root@host]# mysql -h127.0.0.1 -u用户名 -p密码

二、创建用户并修改其权限

1.创建一个没有权限的用户

mysql> create user 用户名 identified by '密码';
下次登陆：
[root@host]# mysql -u 用户名 -p  
Enter password:密码  #登录后进入终端
mysql>

2.给予用户权限

mysql> grant 权限 on 数据库.数据表 to '用户' @ '主机名';

若是全部权限
mysql> grant all on *.* to '用户名'@'%';

3.创建用户并给予用户权限

mysql> grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码"

这里举个例子（来自博客）
如，增加一个用户ywliyq密码为123456，让其可以在本机上登录， 并对所有数据库有查询、插入、修改、删除的权限

mysql> grant select,insert,update,delete on *.* to ywliyq@localhost Identified by "123456";

如果希望该用户能够在任何机器上登陆mysql，则将localhost改为"%"。

mysql> grant select,insert,update,delete on *.* to ywliyq@'%' Identified by "123456";

4.记得密码情况下mysql5.0和8.0修改密码cmd操作

当记得密码时：
1.[root@host]#  mysqladmin -u用户名 -p旧密码 password 新密码。

5.记得密码情况下mysql5.0修改密码mysql服务操作

1.可以登入mysql服务后
mysql> set password for 用户名@localhost = password('新密码');

2.用UPDATE直接编辑user表
首先登录MySQL。
mysql> use mysql;
mysql> update user set password=password('123') where user='root' and host='localhost';
mysql> flush privileges;

6.记得密码情况下mysql8.0修改密码mysql服务操作

1.
mysql> SET PASSWORD = '123456'

2.
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

7.忘记密码情况下mysql5.0修改密码mysql服务操作

1. 关闭正在运行的MySQL服务。
2. 打开DOS窗口，转到mysql\bin目录。
3. 输入mysqld --skip-grant-tables 回车。–skip-grant-tables 的意思是启动MySQL服务的时候跳过权限表认证。
4. 再开一个DOS窗口（因为刚才那个DOS窗口已经不能动了），转到mysql\bin目录。
5. 输入mysql回车，如果成功，将出现MySQL提示符 >。
6. 连接权限数据库： use mysql; 。
7. 改密码：update user set password=password(“123”) where user=“root”;（别忘了最后加分号） 。
8. 刷新权限（必须步骤）：flush privileges;　。
9. 退出 quit。
10. 注销系统，再进入，使用用户名root和刚才设置的新密码123登录。

8.忘记密码情况下mysql8.0修改密码mysql服务操作

方法一:利用–init-file参数解决
该参数指定服务启动时先执行一个包含sql命令文件，因此，只需要将重置密码的命令写在该文件中，以此参数指定启动时执行该命令，启动完成即可重置系统密码了。
第一步，关掉系统服务

.[root@host]#net stop mysql

第二步，创建一个文本文件，内含一条密码修改命令

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '';

第三步：命令行方式启动服务器，指定启动时执行上述的密码修改命令文件

mysql> mysqld --init-file=d:mysqlc.txt --console

方法二，想办法让–skip-grant-tables参数用起来
同方法一，先关掉系统服务
实测，在mysql8系统下，用

mysqld --console --skip-grant-tables --shared-memory

可以无密码启动服务
服务启动后，以空密码登入系统

mysql.exe -u root

然后执行sql命令将root用户密码设置为空

UPDATE mysql.user SET authentication_string='' WHERE user='root' and host='localhost';

MySQL8的一些特性导致老方法重置不大管用了，建议使用–init-file参数解决，实测安全可靠。
数据库管理密码丢失是很头疼的事情，如果不能顺利找回，麻烦很大。
以上内容来自博客

三、创建SQL数据库

1.最简单最基础的创建SQL数据库

mysql> CREATE DATABASE 数据库名;

2.普通用户创建数据库(cmd操作)

需要特定的权限来创建或者删除 MySQL 数据库。
可以使用最高级的root命令用户来创建。

[root@host]# mysqladmin -u root -p CREATE 数据库名;
>>输入密码

3.如果不存在数据库则创建，存在则不创建且不报错（推荐）

mysql> create database if not exists test_db default charset utf8 collate utf8_general_ci;

四、使用数据库

mysql> use 数据库名;

五、查看数据库

1.查看显示所有数据库

mysql> show databases;

2.查看当前使用的数据库（常用）

mysql> select database();

3.查看数据库使用的端口

mysql> show variables  like 'port';

4.查看当前数据库大小

use  information_schema;
mysql> select concat(round(sum(data_length)/(1024*1024),2) + round(sum(index_length)/(1024*1024),2),'MB') as 'DB Size'
    -> from tables 
    -> where table_schema='数据库名';

5.查看数据所占的空间大小

mysql> use information_schema;
mysql> select concat(round(sum(data_length)/(1024*1024),2),'MB') as 'DB Size'
    -> from tables
    -> where table_schema='数据库名';

6.查看索引所占的空间大小

mysql> use information_schema;
mysql> select concat(round(sum(index_length)/(1024*1024),2),'MB') as 'DB Size' 
    -> from tables 
    -> where table_schema='INVOICE';

7.查看数据库编码

mysql> show variables like 'character%';

character_set_client 为客户端编码方式；
character_set_connection 为建立连接使用的编码；
character_set_database 为数据库的编码；
character_set_results 为结果集的编码；
character_set_server 为数据库服务器的编码；

mysql> show variables like 'collation%';

8.查看数据库整体信息（status）

mysql> status;

9.查看表

mysql> show tables;

10.查看某个数据库的表信息。

mysql> select * from information_schema.tables where table_schema='databasename'\G;

11.查看某种具体表的信息

mysql> select * from information_schema.tables where table_name ='table_name'\G;

12.查看数据库的所有用户信息（常用）

mysql> select distinct concat('user: ''',user,'''@''',host,''';') as query from mysql.user;

其实也可以进入mysql数据库里，查看user表

use mysql;
 mysql> select user,host from user;

13.查看某个具体用户的权限（常用）

 mysql> show grants for 'root'@'localhost';
 show grants for '用户名'@'ip地址';

14.查看数据库的最大连接数

mysql>  show variables like '%max_connections%';

15.查看数据库当前连接数，并发数。

show status like 'Threads%';

Threads_cached : 代表当前此时此刻线程缓存中有多少空闲线程。
Threads_connected :代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。
Threads_created :代表从最近一次服务启动，已创建线程的数量。
Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态，这里相对应的线程也是sleep状态。

16.查看数据文件存放路径（常用）

show variables like '%datadir%';

引用博客

（补充 variables信息）

MySQL Variables在这里实际上指的是一些参数，用于初始化或设定数据库对系统资源的占用，文件存放位置等。在新安装好系统后，就已经被初始化好了。但是我们有时候不想采取默认值，所以就需要对这些值做出改变。
查看系统变量的值（show）
查看方法一： 系统变量存放在performance_schema数据库里的GLOBAL_VARIABLES和SESSION_VARIABLES表中，可以直接通过查看表的内容获得。

mysql> use performance_schema
Database changed

mysql> show tables like '%variables';
+-------------------------------------------+
| Tables_in_performance_schema (%variables) |
+-------------------------------------------+
| global_variables                          |
| persisted_variables                       |
| session_variables                         |
+-------------------------------------------+
3 rows in set (0.00 sec)

查看方法二： 使用show variables语法

mysql> SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern' | WHERE expr]

修改系统变量的值

mysql> set [GLOBAL | SESSION] 需要设置的变量

引用博客

（补充mysql中\G和\g的作用）

\g:等价于“；”
\G:将查询到的横向表格纵向输出，方便阅读

六、Mysql数据类型

1.mysql数据类型

整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT

浮点数类型：FLOAT、DOUBLE、DECIMAL

字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB

日期类型：Date、DateTime、TimeStamp、Time、Year

其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

(1) 整型

MySQL数据类型	含义（有符号）
tinyint(m)	1个字节 范围(-128~127)
smallint(m)	2个字节 范围(-32768~32767)
mediumint(m)	3个字节 范围(-8388608~8388607)
int(m)	4个字节 范围(-2147483648~2147483647)
bigint(m)	8个字节 范围(±9.22*10的18次方)

(2) 浮点型(float和double)

MySQL数据类型	含义
float(m,d)	单精度浮点型 8位精度(4字节) m总个数，d小数位
double(m,d)	双精度浮点型 16位精度(8字节) m总个数，d小数位

(3) 定点数

浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。

MySQL数据类型	含义
decimal(m,d)	参数m<65 是总个数，d<30且 d<m 是小数位。

(4)字符串(char,varchar,_text)

MySQL数据类型	含义
char(n)	固定长度，最多255个字符
varchar(n)	固定长度，最多65535个字符
tinytext	可变长度，最多255个字符
text	可变长度，最多65535个字符
mediumtext	可变长度，最多2的24次方-1个字符
longtext	可变长度，最多2的32次方-1个字符

char和varchar：
1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。
2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，
所以varchar(4),存入3个字符将占用4个字节。
3.char类型的字符串检索速度要比varchar类型的快。
varchar和text：
1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，text是实际字符数+2个字节。
2.text类型不能有默认值。
3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text,在都创建索引的情况下，text的索引似乎不起作用。

(5)二进制数据(_Blob)

1._BLOB和_text存储方式不同，_TEXT以文本方式存储，英文存储区分大小写，而_Blob是以二进制方式存储，不分大小写。
2._BLOB存储的数据只能整体读出。
3._TEXT可以指定字符集，_BLO不用指定字符集。

(6)日期时间类型

MySQL数据类型	含义
date	日期 ‘2008-12-2’
time	时间 ‘12:25:36’
datetime	日期时间 ‘2008-12-2 22:06:44’
timestamp	自动存储记录修改时间

若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间。

2.数据类型的属性

MySQL关键字	含义
NULL	数据列可包含NULL值
NOT NULL	数据列不允许包含NULL值
DEFAULT	默认值
PRIMARY KEY	主键
AUTO_INCREMENT	自动递增，适用于整数类型
UNSIGNED	无符号
CHARACTER SET name	指定一个字符集

3.MYSQL数据类型的长度和范围

数据类型	字节长度	范围或用法
Bit	1	无符号[0,255]，有符号[-128,127]，天缘博客备注：BIT和BOOL布尔型都占用1字节
TinyInt	1	整数[0,255]
SmallInt	2	无符号[0,65535]，有符号[-32768,32767]
MediumInt	3	无符号$[0,2^{24}-1]$，有符号$[-2^{23},2^{23}-1]]$
Int	4	无符号$[0,2^{32}-1]$，有符号$[-2^{31},2^{31}-1]$
BigInt	8	无符号$[0,2^{64}-1]$，有符号$[-2^{63},2^{63}-1]$
Float(M,D)	4	单精度浮点数。天缘博客提醒这里的D是精度，如果D<=24则为默认的FLOAT，如果D>24则会自动被转换为DOUBLE型。
Double(M,D)	8	双精度浮点。
Decimal(M,D)	M+1或M+2	未打包的浮点数，用法类似于FLOAT和DOUBLE，天缘博客提醒您如果在ASP中使用到Decimal数据类型，直接从数据库读出来的Decimal可能需要先转换成Float或Double类型后再进行运算。
Date	3	以YYYY-MM-DD的格式显示，比如：2009-07-19
Date Time	8	以YYYY-MM-DD HH:MM:SS的格式显示，比如：2009-07-19 11：22：30
TimeStamp	4	以YYYY-MM-DD的格式显示，比如：2009-07-19
Time	3	以HH:MM:SS的格式显示。比如：11：22：30
Year	1	以YYYY的格式显示。比如：2009
Char(M)	M	定长字符串。
VarChar(M)	M	变长字符串，要求M<=255
Binary(M)	M	类似Char的二进制存储，特点是插入定长不足补0
VarBinary(M)	M	类似VarChar的变长二进制存储，特点是定长不补0
Tiny Text	Max:255	大小写不敏感
Text	Max:64K	大小写不敏感
Medium Text	Max:16M	大小写不敏感
Long Text	Max:4G	大小写不敏感
TinyBlob	Max:255	大小写敏感
Blob	Max:64K	大小写敏感
MediumBlob	Max:16M	大小写敏感
LongBlob	Max:4G	大小写敏感
Enum	1或2	最大可达65535个不同的枚举值
Set	可达8	最大可达64个不同的值
Geometry
Point
LineString
Polygon
MultiPoint
MultiLineString
MultiPolygon
GeometryCollection

4.数据类型使用建议

1、在指定数据类型的时候一般是采用从小原则，比如能用TINY INT的最好就不用INT，能用FLOAT类型的就不用DOUBLE类型，这样会对MYSQL在运行效率上提高很大，尤其是大数据量测试条件下。
2、不需要把数据表设计的太过复杂，功能模块上区分或许对于后期的维护更为方便，慎重出现大杂烩数据表
3、数据表和字段的起名字也是一门学问
4、设计数据表结构之前请先想象一下是你的房间，或许结果会更加合理、高效
5、数据库的最后设计结果一定是效率和可扩展性的折中，偏向任何一方都是欠妥的

5.按存储引擎选择数据类型：

• MyISAM 数据存储引擎和数据列：MyISAM数据表，最好使用固定长度(CHAR)的数据列代替可变长度(VARCHAR)的数据列。
• MEMORY存储引擎和数据列：MEMORY数据表目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系。两者都是作为CHAR类型处理的。
• InnoDB 存储引擎和数据列：建议使用 VARCHAR类型。

对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的CHAR列不一定比使用可变长度VARCHAR列简单。因而，主要的性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR，因 此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的。

6.浮点数与定点数的选择：

1.浮点数存在误差问题；
2.对货币等对精度敏感的数据，应该用定点数表示或存储；
3.编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较；
4.要注意浮点数中一些特殊值的处理。
以上来自博客

7.数据库一般不用来存储图片

首先，图片上可以存储到数据库里的，可以通过二进制流将图片存到数据库中。

**但是，强烈不建议把图片存储到数据库中！！！！**首先对数据库的读/写的速度永远都赶不上文件系统处理的速度，其次数据库备份变的巨大，越来越耗时间，最后对文件的访问需要穿越你的应用层和数据库层。图片是数据库最大的杀手。一般来说数据库都是存储一个URL，然后再通过URL来调用图片。

图片，文件，二进制数这三样东西慎重存储到数据库中。现在很多企业都是这样来处理图片的，用MySQL数据库存储URL，图片则是存储在阿里云上。

8.MySQL 字段属性应该尽量设置为 NOT NULL

除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持 NOT NULL。这看起来好像有点争议，请往下看。
1、首先，我们要搞清楚空值 “” 和 NULL 的概念：
1）空值是不占用空间的
2）MySQL中的NULL其实是占用空间的
所谓的 NULL 就是什么都没有，连 \0 都没有，\0 在字符串中是结束符，但是在物理内存是占空间的，等于一个字节，而 NULL 就是连这一个字节都没有。
2、其次，在数据库里是严格区分的，任何数跟 NULL 进行运算都是 NULL, 判断值是否等于 NULL，不能简单用 =，而要用 IS NULL关键字。
3、数据库的字段 col1 设为 NOT NULL, 仅仅说明该字段不能为 NULL, 也就是说只有在:

INSERT INTO table1(col1) VALUES(NULL);

这种情况下数据库会报错，而:

INSERT INTO table1(col1) VALUES('');

不会报错。
（如果字段是自增ID，第一句不会报错，这不能说明是可以为NULL,而是 数据库系统会根据ID设的缺省值填充，或者如果是自增字段就自动加一等缺省操作。）
4、含有空值的列很难进行查询优化，而且对表索引时不会存储 NULL 值的，所以如果索引的字段可以为 NULL，索引的效率会下降很多。因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用 0、一个特殊的值或者一个空串代替空值。
5、联表查询的时候，例如 LEFT JOIN table2，若没有记录，则查找出的 table2 字段都是 null。假如 table2 有些字段本身可以是 null，那么除非把 table2 中 not null 的字段查出来，否则就难以区分到底是没有关联记录还是其他情况。
（以上来自菜鸟教程）

七、表

1.最简单的创建表

在 MySQL 中，可以使用 CREATE TABLE 语句创建表。其语法格式为：

CREATE TABLE <表名> ([表定义选项])[表选项][分区选项];

其中，[表定义选项]的格式为：

<列名1> <类型1> [,…] <列名n> <类型n>

2.不存在则建表，存在则不建（推荐）

CREATE TABLE IF NOT EXISTS 表名(
   <列名1> <类型1> [,…] <列名n> <类型n>
)ENGINE=存储引擎 DEFAULT CHARSET=设置编码;

例子：

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` ),
   constraint  c1 foreign key (depart_id) references depart_table(depart_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建 MySql 的表时，表名和字段名外面的符号 ` 不是单引号，而是英文输入法状态下的反单引号，也就是键盘左上角 esc 按键下面的那一个 ~ 按键，

• 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
• AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。 PRIMARY KEY关键字用于定义列为主键。
• 您可以使用多列来定义主键，列间以逗号分隔。
• ENGINE 设置存储引擎，CHARSET 设置编码。
• UNSIGNED 代表只能插入正数

3.查看表

 mysql> SHOW TABLES;

4.查看表信息

 mysql> DESCRIBE <表名>;
或简写成：
 mysql> DESC <表名>;

如果键是PRI，则列是主键或多列主键中的列之一。
如果键是UNI，则该列是唯一索引的第一列。(唯一索引允许多个空值，但可以通过检查Null字段来判断该列是否允许空。)
如果键为MUL，则该列是非唯一索引的第一列，其中允许在列中多次出现给定值。

5.显示创建表时的CREATE TABLE语句

 mysql> SHOW CREATE TABLE <表名>\G；

往表中插入数据

 mysql> insert into 表名(属性名) values(插入的值);