2016-12-25

Mysql入门

Mysql 是最流行的关系型数据库管理系统，尤其是在Web应用方面。
Mysql,由瑞典MySQL AB公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
Mysql是开源的、免费的、支持大型的数据库
Mysql使用标准的SQL数据语言形式。

1	ps: ${ xxx } 表示 xxx 是变量

mysql安装

Mac 系统用 brew 安装mysql

安装mysql

1 2	brew update # 更新下brew brew install mysql # brew安装 mysql

设置 MySQL 用户以及数据存放地址

1
2
3

unset TMPDIR

mysql_install_db --verbose --user=`whoami` --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp

忘记mysql数据库密码

1	update user set authentication_string=password('my_password') where user='root';

修改密码

1	SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');

可能遇到的问题

错误码：ERROR 1045 (28000)

cd /etc

sudo vim my.cnf

编辑如下代码到 my.cnf 文件,保存成功应该就可以了
[mysqld]
skip-grant-tables
lower_case_table_names=1

权限不足的时候可以创建一个账号

1
2
3

CREATE USER 'golden'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'golden'@'localhost';
FLUSH PRIVILEGES;

启动mysql

mysql.server start
sudo /etc/init.d/mysql start 

or

sudo /usr/local/mysql/support-files/mysql.server start;

开机启动mysql

1	/etc/init.d/mysqld start

通过sql文件执行脚本

source  /Users/zsr/learn/crawler/sql/createtable.sql; 

or 

mysql -D samp_db -u root -p < createtable.sql

1	mysql -u ${ databasename } -p # 然后输入mysql密码

创建一个数据库，设置character是为了兼容汉字

1	create database ${newdatabasename} character set gbk;

查看所有的数据库

1	show databases;

删除数据库

1	drop database ${databasename};

选中 testdb 数据库

1	use ${databasename};

创建一张表

use ${databasename}

create table students
（
 id int unsigned not null auto_increment primary key,
 name char(8) not null,
 sex char(4) not null,
 age tinyint unsigned not null,
 tel char(13) null default "-"
);

修改表名

1	alter table ${oldtablename} rename to ${newtablename};

查看所有表

1	show tables;

删除表

1	drop table ${tabename};

显示数据表的属性，属性类型，主键信息

1	show columns from students

查看列

1	desc ${tablename};

添加列

1	alter table ${tablename} add column ${newcolumnname} varchar(30);

删除列

1	alter table ${tablename} drop column ${columnname};

修改列名

1	alter table ${tablename} change ${ondcolumnname} ${newcolumnname} int;

修改列属性

1	alter table ${tablename} modify ${oldattr} char(80);

增加数据

1	insert into ${tablename} values (${column1}, ${column2}); /如果自增id，则column1为0/

修改数据

1	update ${tablename} set ${attr}=${newAttr} where ${id=3}; /id=3代表查询条件/

查询数据

1	select * from ${tablename} where ${id=3};

删除数据

1	delete from ${tablename} where ${id=3};

详细的常用查询操作
1.查询从第n条开始的m条数据、asc 升序(desc 降序)

1
2
3

select * from ${tablename} limit n,m;
select ${column1},${column2} from ${tablename} order by ${column} asc limit n,m
select ${column1},${column2} from ${tablename} order by ${column} desc limit n,m

查看数据库编码

1	show variables like 'character%';

查看数据库位置

1	show variables like 'datadir%'

数据库中文乱码

mysql> show variables like '%char%';
+--------------------------------------+----------------------------+
| Variable_name                        | Value                      |
+--------------------------------------+----------------------------+
| character_set_client                 | latin1                       |
| character_set_connection             | utf8                       |
| character_set_database               | utf8                       |
| character_set_filesystem             | binary                     |
| character_set_results                | utf8                       |
| character_set_server                 | utf8                       |
| character_set_system                 | utf8                       |
| character_sets_dir                   | /usr/share/mysql/charsets/ |
| validate_password_special_char_count | 1                          |
+--------------------------------------+----------------------------+
set character_set_client=utf8;

mysql 数据库中文乱码是因为数据库默认的编码方式是 latin1
解决办法就是将其手动改为utf8即可
但是这只是临时办法，变量只是session级别的存储，数据库重启之后就没了

1
2
3

sudo vim /etc/mysql/my.cnf
or
sudo vim /etc/my.cnf

在my.cnf 文件里面配置数据库的编码集

[mysqld]
character-set-server=utf8
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8

备份数据

1 2	mysqldump -udbusername -pdbpwd dbname > ./epoos.sql scp -r root@xxx.193.173.xx:/xxx/xxx/xxx/epoos.sql /Users/zsr/epoos

还原数据

1	mysql -h127.0.0.1 -uroot -p123456 epoos < epoos.sql

如果上面的还原功能不好使，可以

1
2
3

1.创建新数据库 newdb
2.use newdb
3.执行备份的sql文件

ubuntu安装mysql

1
2
3

sudo apt-get update 
sudo apt-get install mysql-server # 安装 mysql 服务端
mysql_secure_installation # mysql 安装安全配置向导，设置密码

执行安全配置向导可能报如下错误：

1 2	# 报错信息 Error: Access denied for user 'root'@'localhost'

解决方案：

sudo -iu root # 用 root 用户执行此命令即可
mysql_secure_installation

# 中文解释
1)为root用户设置密码；
2)删除匿名账号；
3)取消root用户远程登录；
4)删除test库和对test库的访问权限；
5)刷新授权表使修改生效。

新建新用户

# 创建 dev 用户
mysql> CREATE USER 'dev'@'%' IDENTIFIED BY 'Abc123456!';
# 给用户 dev 所有数据库的权限
mysql> grant all PRIVILEGES on *.* to 'dev'@'%' IDENTIFIED BY 'Abc123456!';
# 刷新配置
flush privileges;

如果遇到创建失败

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql> grant all PRIVILEGES on *.* to 'dev'@'%'' at line 1

可能是因为新版的的mysql版本已经将创建账户和赋予权限的方式分开了，可使用如下命令。

1	GRANT ALL PRIVILEGES ON . TO 'dev'@'%' WITH GRANT OPTION;

远程端口不通

1 2	# 测试端口是否通顺 telnet 10.216.8.142 3306

mysql 数据库经常会遇到本地能连通但是其它机器通过 ip 访问的时候就不通的情况。
排查可以从下面三种情形入手（具体方式可以网上搜一搜，很多，这里就不列了）

这种情形比较常见的原因有三个：
1.机器防火墙 3306 端口没有开
2.登录的数据库用户没有开 ip 访问的权限，找到user表，赋予其 % 权限。
3.数据库初始的时候地址没有注释掉 bind-address

1
2
3

# 这个文件配置可能在/etc/my.cnf/;/etc/mysql/my.cnf;也可能在 /etc/mysql/mysql.conf.d
# 具体情形根据系统有所不同，但是只需要找到 bind-address 将其注释掉即可
#bind-address  = 127.0.0.1

卸载mysql

把下面几个目录全部删掉应该就可以完全卸载mysql了
值得注意的是，如果有数据库需要备份的，需提前做好备份。

rm -rf ~/Library/PreferencePanes/My*
sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*
sudo rm -rf /var/db/receipts/com.mysql.*

sudo rm /usr/local/mysql
sudo rm -rf /usr/local/mysql*
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*

mysql安装

ubuntu安装mysql

新建新用户

远程端口不通

卸载mysql

相关链接