MySQL使用（学习笔记）

欢迎使用windows自带编辑器运行mysql

你好！ 这是你第一次使用 MySQL数据库 所展示的欢迎页。如果你想学习如何使用, 可以仔细阅读这篇文章，了解一下mysql。

安装

安装可以参考其他csdn文章

启动运行MySQL

windows+R启动管理员窗口
cmd

删除字段

从 first表中删除id字段
ALTER TABLE first DROP id;

删除mysql中重复存入的数据（只保留一条）

DELETE FROM table_a
WHERE id IN
(SELECT id FROM (SELECT id FROM table_a GROUP BY c_1,c_2 HAVING count(*) > 1)e);

添加列到指定位置

如果我们希望添加在指定的一列，可以用：

alter table 表名 add column 列名 varchar(20) not null after user1;

注意，上面这个命令的意思是说添加addr列到user1这一列后面。如果想添加到第一列的话，可以用：

alter table 表名 add column 列名 varchar(20) not null first;
将id这一列放在数据表的第一列
alter table mytqyb MODIFY id int first;
（1为id改前的位置在列表最后一列，经过2，然后就改在第一列如3所示）

将id插入到grade字段后面
ALTER TABLE mytqyb MODIFY id int AFTER grade;

单表操作

1、启动
net start mysql
2、输入密码
mysql -u root -p（无密码直接回车）
3、查看版本和当前时间
select version,current_date();
4、计算
select (20+5)*4;
5、列表计算
select (20+5)*4 As Result,sin(pi()/3);(as result 小写也可以）
6、建立用户和当前时间的数据库，分开输入【select回车
USER（）回车 ， 回车now() 回车;回车】直接输入：select USER(),now();
7、显示数据库
show databases;
8、创建数据库
CREATE DATABASE beiu;(create database beiu;也可以）
9、进入数据库
USE beiu（use beiu也可以）
10、展示列表
SHOW TABLES;（show tables;也可以）
11、在列表中创建姓名，性别，出生日期，出生地
CREATE TABLE mytable (name VARCHAR(20),sex CHAR(1),回车继续输入birth DATE,birthaddr VARCHAR(20));第二种表示create table mytable (name varchar(20),sex char(1),birth DATE,birthaddr varchar(20));（此处DATE也可以小写）
12、展示表
SHOW TABLES;（show tables;也可）
13、展示
DESCRIBE mytable;（describe mytable;也可）
14、向表中加入（两条）信息
insert into mytable回车继续输入values(‘jerry’,‘f’,‘1995-02-11’,‘china’);
insert into mytable回车继续输入values(‘herry’,‘m’,‘1994-02-11’,‘korean’);
15、显示输入信息后的表单
select * from mytable;
16、修正错误记录
update mytable set birth = “1973-09-02” where name = “herry”;
17、选择特定行
select * from mytable where name = “herry”;
18、用组合条件来进行查询
SELECT * FROM mytable WHERE sex = “m” AND birthaddr = “korean”;（select * from mytable where sex=‘m’ and birthaddr=‘korean’;也可）
19、按👆向上键返回上一句代码，如果上一句写错了可用这个方法快速修改。

多表操作

1、查看第一个表mytable的内容
select * from mytable;
2、创建第二个表（包括作者、标题、日期）
mysql> create table title(writer varchar(20) not null,
-> title varchar(40) not null,
-> ssdate date);
3、加入信息
insert into mytable回车继续输入values(‘jerry’,'b2,‘2000-02-11’);
insert into mytable回车继续输入values(‘Hoang’,‘a1’,‘1997-02-11’);
4、查看表select * from title;
5、多表查询（可以进行组合查询）
例如我们要在两个表中查询作者jerry的姓名、性别、标题：
SELECT name,sex,title FROM mytable,title WHERE name=writer AND name=‘jerry‘;
注意：此处两个table中的name和writer不能设置为同一个名字，不然会报错。
由于作者姓名、性别、文章在两个不同表内，因此必须使用组合来进行查询。必须要指定一个表中的记录如何与其它表中的记录进行匹配。
6、多表查询，用于查询a1的文章，作者、出生地和出生日期：
select title,writer,birthaddr,birth from mytable,title
-> where mytable.name=title.writer and title=‘b2‘;
7、增加一列是否单身
alter table mytable add column single char(1);
8、修改记录
update mytable set single=‘y‘ where name=‘herry‘;
update mytable set single=‘n‘ where name=‘jerry‘;
9、增加记录
mysql> insert into mytable
-> values (‘jasoo‘,‘f‘,‘1998-08-17‘,‘china‘,‘y‘);
10、删除记录
mysql> delete from mytable where name=‘jasoo‘;
小心使用下面两个删除
11、删除表
drop table ****(表1的名字)，***表2的名字;
12、删除数据库
drop database 数据库名;

数据库表名和列名（字段名）更改

１、MySQL 通过 ALTER TABLE 语句来实现表名的修改
ALTER TABLE <旧表名> RENAME [TO] <新表名>;
例：使用 ALTER TABLE 将数据表 student 改名为 tb_students_info

提示：修改表名并不修改表的结构，因此修改名称后的表和修改名称前的表的结构是相同的。用户可以使用 DESC 命令查看修改后的表结构

２、修改mysql的列名（字段名）
使用 ALTER TABLE 修改表 tb_emp1 的结构，将 col1 字段名称改为 col3，同时将数据类型变为 CHAR(30)

其中：
旧字段名：指修改前的字段名；

新字段名：指修改后的字段名；

新数据类型：指修改后的数据类型，如果不需要修改字段的数据类型，可以将新数据类型设置成与原来一样，但数据类型不能为空。
例：
使用 ALTER TABLE 修改表 tb_emp1 的结构，将 col1 字段名称改为 col3，同时将数据类型变为 CHAR(30)

数据库的备份

mysql的默认端口是3306，可以编辑用户目录下的 .my.cnf 文件进行修改；
2、sqlserver默认端口号为：1433；
3、oracle默认端口号为：1521；
4、DB2默认端口号为：5000；
5、PostgreSQL默认端口号为：5432。
退到管理员界面
mysql> quit
想进行数据库的备份首先要在命令行窗口进入MySQL服务器安装目录下的bin目录，执行备份命令

使用 mysqldump 命令备份一个数据库的语法格式如下：
mysqldump -u username -p dbname [tbname …]> filename.sql

语法参数说明如下：
username：表示用户名称；
dbname：表示需要备份的数据库名称；
tbname：表示数据库中需要备份的数据表，可以指定多个数据表。省略该参数时，会备份整个数据库；
右箭头“>”：用来告诉 mysqldump 将备份数据表的定义和数据写入备份文件；
filename.sql：表示备份文件的名称，文件名前面可以加绝对路径。通常将数据库备份成一个后缀名为.sql的文件
注意：mysqldump 命令备份的文件并非一定要求后缀名为.sql，备份成其他格式的文件也是可以的。例如，后缀名为.txt的文件。通常情况下，建议备份成后缀名为.sql 的文件。因为，后缀名为.sql的文件给人第一感觉就是与数据库有关的文件。
例：

注意：mysqldump 命令必须在 cmd 窗口下执行，不能登录到 MySQL 服务中执行。
输入密码后，MySQL 会对 test 数据库下的 student 数据表进行备份。之后就可以在指定路径下查看刚才备份过的文件了。

多个数据库的备份

如果要使用 mysqldump 命令备份多个数据库，需要使用 --databases 参数。

加上“–databases”参数后，必须指定至少一个数据库名称，多个数据库名称之间用空格隔开
例：
下面使用 root 用户备份 test 数据库和 mysql 数据库。

执行完后，可以在C下面看到名为 testandmysql.sql 的文件，这个文件中存储着这两个数据库的信息

所有数据库的备份

mysqldump 命令备份所有数据库的语法格式

使用“–all-databases”参数时，不需要指定数据库名称。
例：
下面使用 root 用户备份所有数据库

执行完后，可以在 C:\下面看到名为 all.sql 的文件，这个文件中存储着所有数据库的信息

我的数据库备份成功案例（我的MySQL没设密码）
一、单个列表备份
代码mysqldump -uroot -p abccs mytable > student.sql

必须要在bin环境下，没有密码直接回车，abccs是数据库名称，mytable为列表名
存放地址也可改为D:\saveone.sql根据自己想法。
二、
多个列表备份
代码
mysqldump -u root -p --all-databases>filename.sql

三、
两个列表备份始终报错

MySQL的默认用户名和密码：用户名为root，密码为空。
若不是默认用户的话，需要你进入MySQL输入如下语句，方可查看用户名
select * from mysql.user；

mysql更改登录密码

方法一

方法2

７８９４５６（ｔｗｏ　ｍｙｓｑｌ）
在配置好环境变量的mysql中打开cmd，启动mysql。
1、输入net start mysql

2、输入mysql -u root 不需要密码，直接按回车过
3、输入use mysql
4、输入ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘新密码’;
５、输入exit退出

SQLyog连接MySQL：1251-Client does not support authentication protocol requested by server

问题：

使用Navicat连接MySQL的时候，出现了一个问题：

1251-Client does not support authentication protocol requested by server; consider upgrading MySQL client
原因：

经查，出现这种情况的原因是：MySQL8 之前的版本中加密规则是mysql_native_password,而在MySQL8之后,加密规则是caching_sha2_password

解决方法：
把mysql用户登录密码加密规则还原成mysql_native_password
具体步骤如下：

1、打开cmd命令行窗口，进入到MySQL的bin目录下。

执行命令：mysql -u root -p
输入密码，回车
2、执行下面的命令：
ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘123456’;
3、刷新权限，执行命令：
FLUSH PRIVILEGES;

4、连接成功：

更新mysql密码

mysql自增ID

1、给某一张表先增加一个字段,这里我们就以node_table这张表来举例，在数据库命令行输入下面指令 ：
alter table node_table add id int；

放在第一列：
alter table node_table add id int first;
2、更改id字段属性为自增属性，在数据库命令行输入下面指令 ：
alter table node_table change id id int not null auto_increment primary key;

3、创建表后，将某列 col 或者将某属性设置为主键
alter table table_name add primary key (col_name);
4、 首先对于直接在创建表时设置主键
create table table_name(sno int primary key,sname varchar(15),age int(11));

设置Mysql数据库的默认编码为utf8

my.ini里面修改两处地方，再重启mysql

1.查看默认的编码格式:
mysql>show variables like ‘character%’;

清空MySQL数据表中的数据

方法一：delete
清空全部数据,写日志,可恢复,速度很慢 delete from 表名;
方法二：truncate
清空全部数据,不写日志,不可恢复,速度很快 truncate table 表名;
TRUNCATE语句具体的语法为：truncate table 表名。

mysql 数据库存储路径更改

net stop mysql57
net start mysql57
mysql -u root -p //登录
show global variables like “%datadir%”; //查看数据库存储路径
1.首先把mysql的服务先停掉
使用CTRL+alt+delete将后台MySQL停止，
然后
cmd进入控制台
net stop mysql
2.复制原来数据库目录到新目录
　　复制C:\ProgramData\MySQL\MySQL Server 5.7\data下的文件到
　　新目录：D:\Program Files\database
3.修改MySQL配置文件
　　1、用记事本（管理员权限）打开C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
　　找到datadir=C:\ProgramData\MySQL\MySQL Server 5.7\data
　　在前面加#注释掉
　　在下面添加一行
　　datadir=D:\Program Files\database
　　修改完成后，保存退出。
4.重新启动MySQL
　　开始-cmd
　　net start mysql
cd C:\Program Files\MySQL\MySQL Server 5.7\bin
mysql -u root -p
5.
　　进入MySQL控制台
　　show variables like ‘%datadir%’; #查询MySQL数据库存放目录

如查询显示为D:\Program Files\database即表示修改成功！

联合唯一索引

例如:t_aa 表中有aa,bb两个字段,如果不希望有2条一模一样的记录（即：aa字段的值可以重复； bb字段的值也可以重复，但是一条记录（aa,bb）组合值不允许重复）,需要给 mytable 表添加多个字段的联合唯一索引:

alter table mytable add unique index(aa,bb);

删除表中 的一列

alter table mytable drop column city;
mytable为表名，city为表中的列。