07-MySQL常规使用二&SQL语法一
1.MySQL
重新部署
1. rm -rf arch/* data/*
2. scripts/mysql_install_db --user=mysqladmin --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
2.空密码修改
show databases;
use mysql;
show tables;
desc user;
show create table user;
mysql> select Host,user,password from mysql.user;
+-----------+------+----------+
| Host | user | password |
+-----------+------+----------+
| localhost | root | |
| hadoop000 | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| hadoop000 | | |
+-----------+------+----------+
6 rows in set (0.00 sec)
mysql>
mysql> update mysql.user set password=password('123456') where user='root';
Query OK, 4 rows affected (0.03 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> select Host,user,password from mysql.user;
+-----------+------+-------------------------------------------+
| Host | user | password |
+-----------+------+-------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| hadoop000 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| ::1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | | |
| hadoop000 | | |
+-----------+------+-------------------------------------------+
6 rows in set (0.00 sec)
mysql> flush privileges;
mysql用户的权限操作，最后加 flush privileges;
3.修改% 任意机器可访问
mysql> update mysql.user set Host='%' where Host='hadoop000';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MySQL: hadoop000
限制公司的xxx机器可访问
4.dbeaver 工具链接
5.mysql命令参数
mysql -uroot -p123456
mysql -u root -p123456
mysql -u root -p 123456 不可以
mysql -u root -p123456 -h192.168.137.251
mysql -u root -p123456 -hlocalhost mysql
6.创建新DB和用户
create database ruozedb;
grant all privileges on ruozedb.* to ruoze@'192.168.137.1' identified by '123456';
flush privileges;
window： 192.168.1.102 ruoze
Linux: 192.168.137.251 mysql vm8 192.168.137.1
7.查看当前mysql链接数 适当kill
show processlist;
kill Id;
找到哪个process的卡，夯住，锁死--》和开发确认好--》kill
SQL语法:
1.CHAR 0-255字节 定长字符串
VARCHAR 0-65535 字节 变长字符串
什么定长 变长
2.
https://www.cnblogs.com/duhuo/p/5682565.html
DDL: create,drop
DML: insert,update,delete,selete
3.库 表 名称大小写
数据查询大小写
4.
ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
生产场景:
1. delete from xxx
AUTO_INCREMENT=3
3. 新数据进来 自增长不是从1开发，要考虑当场的业务场景；
5.字符集
server
client
db
table
column
CHARSET=latin1
mysql> show variables like '%set%';
+---------------------------------------+----------------------------------+
| Variable_name | Value |
+---------------------------------------+----------------------------------+
| auto_increment_offset | 1 |
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
| innodb_monitor_reset | |
| innodb_monitor_reset_all | |
| optimizer_trace_offset | -1 |
| performance_schema_setup_actors_size | 100 |
| performance_schema_setup_objects_size | 100 |
+---------------------------------------+----------------------------------+
14 rows in set (0.00 sec)
https://blog.csdn.net/minwang593/article/details/16941279
mysql> show create database ruozedb;
+----------+--------------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------------+
| ruozedb | CREATE DATABASE `ruozedb` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
6.
create table test select id,name from ruozedata;
create table test1 select id,name from ruozedata where 1<>1;
