mysql命令备忘

1 登录

####1-登录-注意-p和密码一起连接！！！
mysql -h localhost -u root -ppassword
####2-修改密码
mysqladmin -u root -p ab12 password djg345



初始化后第一次使用数据库要修改密码：
#user mysql;
ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';
exit;（退出mysql）
修改密码的几种操作-链接

### 4 错误解决
Access denied for user 'root'@'localhost'
https://blog.csdn.net/lhl1124281072/article/details/80277163

2 数据库查询

#### 1-创建数据库
 mysql> create database xhkdb;
#### 2-查看数据库
mysql> show databases;
#### 3-删除数据库
drop database <数据库名>
#### 4-连接数据库、
use <数据库名>
#### 5-查看数据库中所有的表
mysql> show tables;
#### 6-查看表结构
mysql> desc  table_name;
#### 7-简单查看数据内容
select * from table_name

3 简单数据创建操作

#### 1 创建、删除数据库
create database database_name; 
drop database database_name; 
#### 2 创建 删除表
create table mytest( uid bigint(20) not null, uname varchar(20) not null);
drop table mytest; 
----
 create table mytablename (ID int auto_increment not null primary key,usename varchar(20),password varchar(64),sex varchar(10),address varchar(20));  ##创建表单
#### 3 插入表格
insert into mytablename values('','zhangsan','123456','fomale','guiyanag');  ##插入
#### 4 精确查找
select * from mytablename  where ID = '1';  精准查找
#### 5 更新指定表
update fhweb set address='http://rdmprd.fiberhome.com.cn/' where ID='3';
#### 6 删除记录
delete from mytablename  where ID = '1';    删除记录

4 数据库特权操作

MySQL> grant select,insert,update,delete on *.* to user@"localhost" identified by "12";

5、插入中文

5.1 检查

mysql数据库的默认编码是latin1，可以使用下面代码查看数据库编码

mysql> show variables like "%character%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| 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/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

5.2 修改配置文件

#vim /etc/mysql/my.cnf ，并重启服务重启mysql(/etc/init.d/mysql stop /etc/init.d/mysql start)

[client]
default-character-set=utf8


[mysqld]
default-storage-engine=INNODB
character-set-server=utf8
collation-server=utf8_general_ci

6 简单样例

mysql -u root -proot
-------------------------
create database fwdtest;
show databases
use fwdtest
------------------------
create table fhwebsite(ID int auto_increment not null primary key,webname varchar(20),address varchar(64));
------------------------
#### 添加元素
insert into fhwebsite values('1','fhnw','http://fhnw.fiberhome.com/');

insert into fhwebsite values('2','fh-rdm','http://rdmprd.fiberhome.com.cn');
-------------------------------------
#### 注意报错
mysql> insert into fhwebsite values('3',"集成信息平台",'https://sso.fiberhome.com/');
ERROR 1366 (HY000): Incorrect string value: '\xE9\x9B\x86\xE6\x88\x90...' for column 'webname' at row 1
-----------------------------------------------

create database fhweb
use fhweb
create table fhweb(ID int auto_increment not null primary key,webname varchar(64),address varchar(64));
-------
insert into fhweb values('1',"集成信息平台",'https://sso.fiberhome.com/');
-------
insert into fhweb values('2',"网络产出线",'http://fhnw.fiberhome.com/');
-------
insert into fhweb values('3',"rdm平台",'http://rdmprd.fiberhome.com.cn');
-----------------------
#### 精确查找
select * from mytablename  where ID = '1';  精准查找
#### 更新指定表
update fhweb set address='http://rdmprd.fiberhome.com.cn/' where ID='3';
#### 删除记录
delete from mytablename  where ID = '1';    删除记录

grant select,insert,update,delete on *.* to user@"localhost" identified by "12";
mysql -u user -p12 -h localhost

6 尝试归纳

已经在latin1模式下创建的数据库一直无法插入中文，只能在设置修改后重新创建数据库，否则模式一直是latin1；----》修改已经创建的数据库模式是一个思路！后续了解

7 mac安装使用mysql备忘

brew install mysql

MySQL is configured to only allow connections from localhost by default

To connect run:
    mysql -uroot

To have launchd start mysql now and restart at login:
  brew services start mysql
Or, if you don't want/need a background service you can just run:
  mysql.server start

8 docker 启动数据库

docker run --name=mysql -it -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql
mysql -h 127.0.0.1 -P 3306 -u root -p123456