1.登录mysql

mysql -h地址 -P端口 -u用户名 -p密码
后面4个可以不用按照此顺序, 密码为登录数据库时设置的密码

  1. mysql -hlocalhost -P3306 -uroot -p12345678

⚠️ 默认初始密码：12345678
image.png

地址-hlocalhost和端口-P3306是默认的，在本地可省略。

  1. mysql -uroot -p12345678

image.png

2.查看数据库

  1. show databases;

image.png

3查看指定数据库里的表

  1. show tables from mysql;

image.png

  1. #或者先切换数据库，在查询
  2. use mysql;
  3. show tables;

image.png

4.创建数据库

  1. create database students charset utf8;
  2. -- create database 数据库名 [数据集 utf8];

image.png

5.查看当前所在数据库

  1. select database();

image.png

6.切换数据库

  1. use students;

7.创建数据表

  1. #如果不指定数据库可能就会在其他数据库创建表了。
  2. use students;
  3. mysql > create table student (
  4. id int(10) not null auto_increment,
  5. name varchar(100) not null,
  6. age int(3) not null,
  7. gender varchar(1) not null default 'M',
  8. inclass varchar(2) not null,
  9. grade varchar(2) not null,
  10. submission_data datetime default current_timestamp(),
  11. primary key (id)
  12. )engine=InnoDB default charset=utf8;

image.png

设置default值

int或者varchar类型可以直接设置default加字段。

给日期或时间设置默认值

mysql日期默认值设置有now()和current_timestamp()两种方法。不同方法对应不同字段类型。

  1. submission_date timestamp default now();
  2. submission_date timestamp default current_timestamp();

或者给datetime设置默认值

  1. submission_date datetime default now();
  2. submission_date datetime default current_timestamp();

只有datetime和timestamp混合日期和时间值

日期时间类型

类型 格式 用途
date YYYY-MM-DD 日期值
time HH:MM:SS 时间值和持续时间
year YYYY 年份值
datetime YYYY-MM-DD HH:MM:SS 混合日期和时间
timestamp YYYY-MM-DD HH:MM:SS 混合日期和时间，时间戳

8.查看数据表结构

  1. mysql> describe student;
  2. # 或者
  3. mysql> desc student;
  4. # 或者
  5. mysql> show columns from student;

image.png

添加表字段

给定义好的表新增字段。默认会增加到最后

  1. mysql> alter table student add NO int unsigned not Null auto_increment primary key;

在指定字段后面添加一个字段

  1. mysql> alter table student add address varchar(110) after grade;

修改表字段

修改字段类型

  1.  mysql> ALTER TABLE student modify column name VARCHAR(50);

修改字段名

  1. mysql> alter table student change inclass classes varchar(10);

删除表字段

  1. mysql> alter table student drop column age;

9.重命名表

  1. mysql> rename table student to stu;
  2. -- 或者 alter
  3. mysql> alter table student to stu;

image.png

10.表中插入数据

  1. mysql> insert into student (id,name,age,gender,inclass,grade) values
  2.     -> (100001,'joy',12,'M',2,3),(100002,'sam',13,'M',3,3);

image.png

  1. mysql> insert into student (id,name,age,gender,inclass,grade) values
  2.     -> (100003,'joe',12,'F',2,3),(100004,'tom',13,'M',3,3);

11.查询表的数据

  1. mysql> select * from student;

image.png

select 查询数据

SELECT column_name,column_name FROM table_name [WHERE Clause] [LIMIT N][ OFFSET M]

  1. mysql> select id,name,age from student;

where 查询

SELECT field1, field2,…fieldN FROM table_name1, table_name2… [WHERE condition1 [AND [OR]] condition2…..

  1. mysql> select * from student where age>12;
  2. mysql> select * from student where name='joy';

image.png

  1. select * from student where gender='M';

image.png

update 更新

UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause];

  1. mysql> update student set age=16 where name='sam';
  3. mysql> select * from student;

image.png

order by排序

asc 升序 desc 降序

  1. mysql> select * from student order by age asc;
  2. mysql> select * from student order by age desc;

image.png

like模糊查询

SQL LIKE 子句中使用百分号 %字符来表示任意字符

  1. mysql> select * from student where name like '%m';

image.png

delete删除满足条件的数据

  1. #先插入age小于10的数据
  2. mysql> insert into student (id,name,age,gender,inclass,grade) values
  3.     -> (100005,'joy3',2,'M',2,3),(100006,'samf',3,'M',3,3);
  4. #删除小于10
  5. mysql> delete * from student where age < 10;

image.png

group by对结果集进行分组

根据一个或多个列对结果集进行分组。在分组的列上可以使用 COUNT, SUM, AVG等函数。

https://www.runoob.com/mysql/mysql-group-by-statement.html

union 联合查询

连接两个以上的 SELECT 语句的结果组合到一个结果集合中，多个 SELECT 语句会删除重复的数据。

https://www.runoob.com/mysql/mysql-union-operation.html

12.清空表

  1. delete from student;

13.删除表

  1. drop table student;

image.png

14.换种方式清空表数据

truncate与delete的区别在于：truncate是先按照原数据表结构复制了一份空数据表再删除原表，而delete是数据连同结构一起清空。

  1. truncate student;

15.删除数据库

  1. drop database students;

16.node操作mysql

  1. const mysql = require('mysql');
  3. // 连接信息
  4. const connection = mysql.createConnection({
  5.     host: 'localhost',
  6.     user: 'root',
  7.     password: '12345678',
  8.     database: 'students',
  9.     timezone: "08:00",
  10. });
  12. // 建立连接
  13. /// connection.connect();
  14. connection.connect(function (err) {
  15.     if (err) {
  16.         console.error('error connecting: ' + err.stack);
  17.         return;
  18.     }
  19.     console.log('connected as id ' + connection.threadId);
  20. });
  21. /* 插入数据方法
  22. var data = { id: 2, name: 'jim', age:18, gender:"M", inclass: 2, grade: 9,submission_data: '2021-08-31' };
  23. connection.query('insert into student set ?', data,
  24. function (error, results, fields) {
  25.     if (error) {
  26.         throw error;
  27.     }
  29.     // 打印查询结果
  30.     console.log('INSERT result is: ', results);
  31. }); 
  32. */
  34. // 或者
  35. /* 
  36. connection.query("insert into student (id,name,age,gender,inclass,grade) values (1000010,'joys',15,'M',2,3),(1000021,'sams',16,'M',3,3);",
  37. function (error, results, fields) {
  38.     if (error) {
  39.         throw error;
  40.     }
  42.     // 打印查询结果
  43.     console.log('INSERT result is: ', results);
  44. }); 
  45. */
  47. //更新属性,把name=sams的同学修改age为12
  48. connection.query('update student set age = ? where name = ?', [12, 'sams'],
  49. function (error, results, fields) {
  50.     if (error) {
  51.         throw error;
  52.     }
  54.     // 打印查询结果
  55.     console.log('UPDATE result is: ', results);
  56. });
  58. // 删除数据
  59. connection.query('delete from student where id > 1000000',
  60.     function (error, results, fields) {
  61.         if (error) {
  62.             throw error;
  63.         }
  65.         // 打印查询结果
  66.         console.log('DELETE result is: ', results);
  67.     });
  70. connection.query('SELECT * from student where age > 12', function (error, results, fields) {
  71.   if (error) throw error;
  72.   console.log('查询年龄大于12: ', JSON.parse(JSON.stringify(results)));
  73. });
  75. connection.end(function (err) {
  76.     if (err) {
  77.         console.error('error end: ' + err.stack);
  78.         return;
  79.     }
  80.     console.log('end connection');
  81. });

17.添加用户

查看用户

  1. mysql> select host, user, password from mysql.user;               --5.7版本之前的
  3. mysql> select host, user, authentication_string from mysql.user;  --5.7版本之后包括5.7

创建用户

CREATE USER <用户名> [ IDENTIFIED ] BY [ PASSWORD ] <口令>

  1. mysql> create user u1 identified by '12121212';

修改密码

  1. mysql> alter user 'u1'@'%' identified by '12344321';

18.设置权限

查看用户及权限

  1. -- 语法 show grants for "user"@"host";
  2. mysql> show grants for 'u1'@'%';

设置权限方法

格式 grant 权限类型 on 数据库名.表名 to ‘用户名’@’ip地址’ identified by ‘用户密码’ with grant option;

  1. -- 允许访问所有数据库下的所有表，%表示所有IP
  2. mysql> grant all privileges on *.* to '用户名'@'指定ip' identified by '用户密码' ;
  4. --允许访问指定数据库下的所有表
  5. mysql> grant all privileges on test.* to '用户名'@'指定ip' identified by '用户密码' ;
  7. --允许访问指定数据库下的指定表
  8. mysql> grant all privileges on test.test to '用户名'@'指定ip' identified by '密码';

收回用户权限

  1. mysql> revoke select on test.* from "u1"@"%";
  2. -- 或者
  3. mysql> revoke all on test.* from "u1"@"%";

删除授权用户

  1. mysql> drop user "u1"@"%";                      -- 删除方法1
  3. mysql> delete from mysql.user where user="u1";  -- 删除方法2

刷新权限

  1. # 刷新权限
  2. mysql> flush privileges;

19.忘了密码

腾讯云centos中密码root : root1234

MySQL初始化root密码

mysql默认root用户没有密码，输入mysql –u root 进入mysql
1、进入mysql数据库
use mysql;
2、初始化root密码
mysql>update user set password=PASSWORD(‘123456’) where User=’root’;

1.以超级管理员打开cmd，关闭mysql服务

net mysql stop

2.跳过权限验证登录mysql

mysqld —shared-memory —skip-grant-tables

  1. 在新的窗口中登录mysql

    mysql -u root -p

无需输入密码，直接回车即可。
4. 切换到mysql，将密码置空。

  1. mysql> use mysql;
  2. mysql> update user set authentication_string='' where user='root';
  3. -- 然后刷新权限:
  4. mysql> flush privileges;

5.设置加密规则并更新新密码

  1. mysql> alter user 'root'@'localhost' identified by '12345678';
  3. mysql> grant all privileges  on *.*  to "root"@'localhost';
  5. mysql> flush privileges;

6.设置成功后，重启mysql服务，使用新密码登录

net start mysql

20.远程连接阿里云10038错误

1.排查是否打开阿里云安全策略

image.png

2.检查防火墙端口是否打开

ubuntu系统使用 ufw status
image.png
包含有3306说明可以使用。

3.监听地址配置错误

通过命令查看端口状态 netstat -apn | grep 3306
image.png
mysql默认是只允许本地主机访问127.0.0.1，并关闭了远程连接，所以安装之后打开远程连接，并修改配置允许其他ip访问；

  1. vi /etc/mysql/mysql.conf.d/mysqld.cnf 
  2. #发现
  3. bind-address = 127.0.0.1

image.png

21:centos7安装mysql

1.远程连接失败

解决方法：
1、配置防火墙，开启3306端口；
2、编辑my.cnf配置文件，绑定IP地址；
3、修改用户访问权限，允许所有机器访问；
4、重启mysql。
先看是否能ping通远程服务器，ping 192.168.1.211，如果不可以就是网络问题。然后，检查端口是否被防火墙挡住了，telnet 192.168.1.211 3306，如果连接失败，配置防火墙。

2.telnet mysql时出现：is not allowed to connect to this MySQL serverConnection closed by foreign host问题的解决

原因是因为索要链接的mysql数据库只允许其所在的服务器连接，需要在mysql服务器上设置一下允许的ip权限；

  1. #用密码登录
  2. mysql -u root -p
  3. #授权
  4. grant all privileges on *.* to 'root'@'%' identified by '123456';
  5. #权限生效
  6. flush privileges;