mysql 操作命令小结_select,insert, update, delete, create, drop, reloa

目录

CentOS安装mysql

常用基本操作

平台相关型

##高级技巧型

### UPDATE

### INSERT

### ALTER

### SELECT

### 视图

CentOS安装mysql

yum -y install mysql               # 安装 client
yum search mysql-server 
yum install -y xx-mysql-server     # 安装服务
  
vi /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-grant-tables                  # (1)添加密码验证
 
systemctl start xx-mysqld.service  # 启动服务
ps aux | grep mysqld               # 检测服务是否启动
 
 
mysql -uroot -p                    # 直接回车，进入mysql，修改root密码
 
mysql> use mysql;
mysql> update user set password=password("xxx") where user="root";
mysql> flush privileges;
mysql> quit
 
vi /etc/my.cnf  # 再次打开注销(1)步，添加的内容
systemctl restart mysqld.service  # 重启服务
 
mysql -uroot -p  
Enter password: 

安装可能错误及解决办法：

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
 
解决办法：
(1) 检测服务是否启动
# ps aux | grep mysqld
mysql      950  0.0  0.0 113248  1568 ?        Ss   12:42   0:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
mysql     1058  0.0  0.3 1026748 112844 ?      Sl   12:42   0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=gd_67_213.err --pid-file=gd_67_213.pid --socket=/tmp/mysql.sock
root     16041  0.0  0.0 112652   976 pts/0    S+   12:55   0:00 grep --color=auto mysqld
(2)建立软连接(可能受权限控制，/var/lib/mysql/mysql.sock无法创建，需手动添加)
ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock
(3)重启服务即可

Failed to start mysqld.service: Unit mysqld.service failed to load: No such file or directory.
解决办法：安装mysql-server服务
yum install mysql-server

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
启动了密码验证
解决办法：
(1)修改/etc/my.cnf 加入skip-grant-tables
(2)重启服务
(3)进入mysql，修改密码
(4)my.cnf改回原样，重启即可

ERROR 1130: Host '192.168.1.3' is not allowed to connect to this MySQL server
navicat 连不上，因为未授权，只能localhost登陆，解决办法
 
mysql -u root -p
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'xxx' WITH GRANT OPTION;
mysql> flush privileges;

access denied for user 'root'@'xxx' (using password: YES)
使用navicat连接出现错误，拒绝连接，密码验证通过错误，权限问题
 
mysql> use mysql;
mysql> grant all privileges on *.* to root@'%' identified by '123';
Query OK, 0 rows affected (0.07 sec)
mysql> FLUSH PRIVILEGES; 
 
// all --> 所有权限，可以是select、insert、update、delete、create、drop、index、alter、grant、references、reload、shutdown、process、file 等，逗号分隔
// *.* --> 数据库名称.表名称
// root@'%' --> root用户，% 匹配所有ip
// 123 --> 密码

常用基本操作

平台相关型

##高级技巧型

### UPDATE

### INSERT

### ALTER

### SELECT

### 视图

CREATE VIEW v_match AS SELECT a.PLAYERNO,a.NAME,MATCHNO,WON,LOST,c.TEAMNO,c.DIVISION  FROM PLAYERS a,MATCHES b,TEAMS c WHERE a.PLAYERNO=b.PLAYERNO AND b.TEAMNO=c.TEAMNO;