MySQL学习笔记 02 - 常用命令

一、用户操作

1、连接MySQL

格式：mysql -h主机地址 -u用户名 -p用户密码

• 连接本机上的MySQL
  首先打开DOS窗口，然后进入目录mysql\bin，然后再键入命令mysql -u root -p,然后回车，提示你输入密码，注意：用户名前可以有空格也可以没有空格，但是密码前不能有空格，否则让你重新输入密码。
  进入成功的提示符是mysql>
• 连接到远程主机上的MySQL。
  格式：mysql -hIP地址 -u 用户名 -p密码

假设远程主机的IP为：110.110.110.110，用户名为root,密码为123。
则键入以下命令：
mysql -h110.110.110.110 -u root -p 123

• 退出MySQL命令：exit 回车
• 停止本地的MySQL服务
  cmd命令行 执行 net stop mysql服务名称
• 启动本地的mysql服务
  cmd命令行 执行 net start mysql服务名称

2、修改密码

语法格式：mysqladmin -u用户名 -p旧密码 password 新密码

• 给root加个密码123.
  -首先在DOS下进入目录mysql\bin，然后键入以下命令

mysqladmin -u root -password 123

注：因为开始时root没有密码，所以-p旧密码一项就可以省略了。

• 将root的密码改为456.

mysqladmin -u root -p ab12 password 456

3、增加新用户

注意：和上面不同，下面的因为是MYSQL环境中的命令，所以后面都带一个分号作为命令结束符。

格式：grant select on 数据库.* to 用户名@登录主机 identified by “密码”

• 增加一个用户test1密码为abc，让他可以在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。首先用root用户连入MYSQL，然后键入以下命令：

grant select,insert,update,delete on *.* to [email=test1@”%]test1@”%[/email]” Identified by “abc”;

但增加的用户是十分危险的，你想如某个人知道test1的密码，那么他就可以在internet上的任何一台电脑上登录你的mysql数据库并对你的数据可以为所欲为了，解决办法见2。

2、增加一个用户test2密码为abc,让他只可以在localhost上登录，并可以对数据库mydb进行查询、插入、修改、删除的操作（localhost指本地主机，即MYSQL数据库所在的那台主机），这样用户即使用知道test2的密码，他也无法从internet上直接访问数据库，只能通过MYSQL主机上的web页来访问了。

 grant select,insert,update,delete on mydb.* to [email=test2@localhost]test2@localhost[/email] identified by “abc”;

如果你不想test2有密码，可以再打一个命令将密码消掉。

grant select,insert,update,delete on mydb.* to [email=test2@localhost]test2@localhost[/email] identified by “”;

4、查询数据库版本及登陆用户

• 查询当前数据库的版本信息

select version() from dual;

• 查询当前登录数据库的用户

select user() from dual;

二、库操作

1、创建数据库

命令：create database <数据库名>

• 建立一个名为student的数据库

mysql> create database student;

• 创建数据库并分配用户

`CREATE DATABASE 数据库名;`

2. `

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON 数据库名.* TO 数据库名@localhost IDENTIFIED BY '密码';`

SET PASSWORD FOR '数据库名'@'localhost' = OLD_PASSWORD('密码');

依次执行3个命令完成数据库创建。注意：中文 “密码”和“数据库”是户自己需要设置的。

2、显示数据库

语法格式：

mysql> show databases;

注意：为了不再显示的时候乱码，要修改数据库默认编码。以下以utf-8编码页面为例进行说明。

• 进入mysql的文件夹，找到my.ini文件。用记事本打开。
• 在[mysqld]下面加character-set-server=utf8
  
• 在最末尾
• [client]下面加

default-character-set=utf8

• [mysql]下面加

no-auto-rehash-set=utf8

如果没有，就新建，小编就是自己建的。

• 重启MySQL服务，在服务里找MySQL，重启该服务。
• cmd进入MySQL，查看编码命令:

show variables like 'character%';

修改前：
修改后：

3、删除数据库

语法格式：

drop database<数据库>;

实例：删除一个名为student的数据库。

drop database student;

4、使用数据库

语法格式：

use <数据库>;

实例：试图操作名为student的数据库。

use student;

• use 语句可以通告MySQL把student数据库作为默认（当前）数据库使用，用于后续语句。该数据库保持为默认数据库，直到语段的结尾，或者直到发布一个不同的use语句.
• 使用use语句为一个特定的当前的数据库做标记，不会阻碍您访问其它数据库中的表。

5、数据库的打印

MySQL中SELECT命令类似于其他编程语言里的print或者write，你可以用它来显示一个字符串、数字、数学表达式的结果等等。

语法格式：

mysql> select database();

• 显示MySQL的版本

mysql> select version(); 

mysql> select version();
±-----------+
| version() |
±-----------+
| 5.7.29-log |
±-----------+
1 row in set (0.00 sec)

• 显示当前时间

mysql> select now(); 

mysql> select now();
±--------------------+
| now() |
±--------------------+
| 2020-03-11 19:16:28 |
±--------------------+
1 row in set (0.00 sec)

• 显示年月日

select dayofmonth(current_date);

mysql> select dayofmonth(current_date);
±-------------------------+
| dayofmonth(current_date) |
±-------------------------+
| 11 |
±-------------------------+
mysql> select month(current_date);
±--------------------+
| month(current_date) |
±--------------------+
| 3 |
±--------------------+
1 row in set (0.00 sec)
mysql> select year(current_date);
±-------------------+
| year(current_date) |
±-------------------+
| 2020 |
±-------------------+
1 row in set (0.00 sec)

• 显示字符串

mysql> select “hello word”;
±-----------+
| hello word |
±-----------+
| hello word |
±-----------+
1 row in set (0.00 sec)

• 当计算器用

select 100/6+7;

mysql> select 100/6+7;
±--------+
| 100/6+7 |
±--------+
| 23.6667 |
±--------+
1 row in set (0.00 sec)

• 串接字符串

1. select concat(string,string2,.....);
   说明 : string1,string2代表字符串,concat函数在连接字符串的时候，只要其中一个是NULL,那么将返回NULL。

select concat('hello','word') as wenhouyu;

mysql> select concat(‘hello’,‘word’) as wenhouyu;
±----------+
| wenhouyu |
±----------+
| helloword |
±----------+
1 row in set (0.00 sec)

2. CONCAT_WS(separator,str1,str2,...);
   说明 : string1,string2代表字符串,concat_ws 代表 concat with separator,第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。

6、查看库及表编码

• 数据库的字符编码问题

show character set;

• 查看数据库支持的所有字符集

show char set;

• 查看数据库编码，语法：

SHOW CREATE DATABASE db_name; 

• 查看表编码，语法：

SHOW CREATE TABLE tbl_name;

• 查看字段，语法：

SHOW FULL COLUMNS FROM table_name; 

• 创建数据库时指定编码，语法：

CREATE DATABASE [IF NOT EXISTS] db_name DEFAULT CHARSET 字符集 [COLLATE 排序规则];

三、表操作

1、创建表

语法格式：

create table <表名> ( <字段名1> <类型1> [,..<字段名n> <类型n>]);

说明：

1. 使用CREATE TABLE关键词创建数据表
2. tab_name是数据表的名称
3. col_name是列名称
4. datetype是列的数据类型
5. 创建每一列以逗号结尾，但最后一列不允许写逗号
6. 常见的数据类型有：SMALLINT、MEDIUMINT、INT/INTEGER、BIGINT、FLOAT、DOUBLE、DATE、DATETIME、TIMESTAMP、TIME、YEAR、CHAR(M)、
   VARCHAR(M)、BLOB[(M)]、TEXT[(M)]等
   实例：创建MyClass表

字段名	数据类型	数据长度	是否为空	是否为主键	自动增加	默认值
id	int	40	否	primary key	auto_increment
name	char	20	否
sex	int	4	否			0
degree	double	16	是

mysql> create table MyClass(
> id int(4) not null primary key auto_increment,
> name char(20) not null,
> sex int(4) not null default '0',
> degree double(16,2));

2、向表中添加数据

MySQL数据库中insert语句支持向数据表中添加一行或者多行语句。

• 插入一行

INSERT INTO table(column1,column2...) VALUES (value1,value2,...);

首先，在INSERT INTO子句之后，在括号内指定表名和逗号分隔列的列表。
然后，将括号内的相应列的逗号分隔值放在VALUES关键字之后。

• 插入多行

   INSERT INTO tasks(subject,start_date,end_date,description)
 VALUES ('任务-1','2020-01-01','2020-01-02','Description 1'),
                ('任务-2','2020-01-01','2020-01-02','Description 2'),
                ('任务-3','2020-01-01','2020-01-02','Description 3');

3、删除数据

• 语法格式：

delete from 表名 where 表达式

4、查询及修改表中的数据

• 查询数据
  查询数据语法结构：

SELECT col1,col2,…… FROM tab_name;

1. SELECT FROM关键字用于查询表中数据
2. col1,col2是表中的列名称，如果要查询所有列可以使用*
3. tab_name是表名称

Select * from dept;

• 修改数据
  修改数据语法结构：

UPDATE tab_name SET col1=value1, col2=value2,...WHERE condition;

1. UPDATE SET关键字用于修改表中的数据
2. tab_name是表名称
3. col1,col2是表中的列名称
4. value1,value2是列中的值
5. WHERE是修改数据的条件

语法格式：

update 表名 set 字段=新值,… where 条件
   mysql> update MyClass set name='Mary' where id=1;

5、增加字段

语法格式：

alter table 表名 add字段 类型 其他;

• 加索引

 mysql> alter table 表名 add index 索引名 (字段名1[，字段名2 …]);

例子： mysql> alter table employee add index emp_name (name);

• 加主关键字的索引

mysql> alter table 表名 add primary key (字段名);

例子： mysql> alter table employee add primary key(id);

• 加唯一限制条件的索引

 mysql> alter table 表名 add unique 索引名 (字段名);

例子： mysql> alter table employee add unique emp_name2(cardnumber);

• 删除某个索引

mysql> alter table 表名 drop index 索引名;

例子： mysql>alter table employee drop index emp_name;

• 增加字段：

mysql> ALTER TABLE table_name ADD field_name field_type;

• 修改原字段名称及类型：

mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;

• 删除字段：

MySQL > ALTER TABLE table_name DROP field_name;

6、修改表名

语法格式：

rename table 原表名 to 新表名;

例如：在表MyClass名字更改为YouClass
mysql> rename table MyClass to YouClass;

注意：
当你执行 RENAME 时，你不能有任何锁定的表或活动的事务。你同样也必须有对原初表的 ALTER 和 DROP 权限，以及对新表的 CREATE 和 INSERT 权限。
如果在多表更名中，MySQL 遭遇到任何错误，它将对所有被更名的表进行倒退更名，将每件事物退回到最初状态。