Java Web开发Basic(二) mysql安装使用

安装

官方安装方法：

shell> groupadd mysqlshell> useradd -r -g mysql mysqlshell> cd /usr/localshell> tar zxvf /path/to/mysql-VERSION-OS.tar.gzshell> ln -s full-path-to-mysql-VERSION-OS mysqlshell> cd mysqlshell> chown -R mysql .shell> chgrp -R mysql .shell> scripts/mysql_install_db --user=mysqlshell> chown -R root .shell> chown -R mysql data# 启动shell> bin/mysqld_safe --user=mysql &# Next command is optionalshell> cp support-files/mysql.server /etc/init.d/mysql.server

我实际的安装方法(没有使用用户组)：

>sudo su>tar xvf mysql-5.6.17-osx10.7-x86_64.tar >ln -s mysql-5.6.17-osx10.7-x86_64/ mysql>scripts/mysql_install_db  --user=mysql # 如果使用--user这个，后面启动报错，找不到mysql.user表>bin/mysqld_safe &

配置

mysqlintalldb会在安装路径底下创建一个默认的my.cnf. 
根据需求修改配置文件：

# These are commonly set, remove the # and set as required.basedir = /usr/local/mysqldatadir = /Users/army/JavaDev/mysqldataport = 3306# server_id = .....socket = /tmp/mysql.sockcharacter-set-server=utf8default-storage-engine=INNODB

为了方便起见，增加mysql的path：

export mysql=/usr/local/mysqlexport PATH=$CATALINA_HOME/bin:$M2:$JAVA_HOME/bin:$mysql/bin:$PATH

或者在bash配置文件中加入mysqlstart、mysql和mysqladmin的别名,就可使用简便的命令替代冗长的命令了。 （注意：修改完毕之后需要退出“终端（Terminal）”之后重新进入，这些命令才会生效）：

alias mysqlstart='sudo /Library/StartupItems/MySQLCOM/MySQLCOM restart'alias mysql='/usr/local/mysql/bin/mysql'alias mysqladmin='/usr/local/mysql/bin/mysqladmin'

这样就可以在终端中比较简单地通过命令进行相应的操作。

启动和停止 #

使用MySQL原生的启动命令：

shell> sudo mysqld_safe (Enter your password, if necessary) 

使用MySQL原生的停止命令：

shell> mysqladmin shutdown -uroot -p  （必须指定用户）(Enter your password, if necessary) 

修改密码

mysqladmin -u root password 'new-password'

然后会提示输入秘密，初始密码为空，直接回车即可。

再次修改密码：

mysqladmin -u root -p xxxx password 'renew-password'

登录

登录本地mysql:

$ mysql -u root -pEnter password:xxx

连接远程mysql:

 $mysql -h 192.168.0.2 -u root -p  ****

增加新用户

grant select,insert,update,delete on *.* to test@localhost identified by '1234'   

或者

create user test@localhost identified by '1234'grant select,insert,update,delete,create,drop on 数据库.* to test@localhost with grant option

查看用户权限：

 mysql> select user,select_priv,insert_priv,update_priv,create_priv,drop_priv,reload_priv from user;+------+-------------+-------------+-------------+-------------+-----------+-------------+| user | select_priv | insert_priv | update_priv | create_priv | drop_priv | reload_priv |+------+-------------+-------------+-------------+-------------+-----------+-------------+| root | Y           | Y           | Y           | Y           | Y         | Y           || root | Y           | Y           | Y           | Y           | Y         | Y           || root | Y           | Y           | Y           | Y           | Y         | Y           || root | Y           | Y           | Y           | Y           | Y         | Y           ||      | N           | N           | N           | N           | N         | N           ||      | N           | N           | N           | N           | N         | N           || test | Y           | Y           | Y           | Y           | Y         | N           |+------+-------------+-------------+-------------+-------------+-----------+-------------+7 rows in set (0.00 sec)

查看数据库

show databases;use mysql;show tables;desc tablename;drop database if exists xxxx;create database xxxx;

建表

DROP TABLE IF EXISTS `workers`;CREATE TABLE `workers` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(20) NOT NULL,  `sex` enum(F,M,S),  `salary` int(11) DEFAULT '0',  `email`  varchar(30),  `EmployedDates`  date,  `department`  varchar(30),  PRIMARY KEY (`id`)) ENGINE=InnoDB  DEFAULT CHARSET=utf8;mysql> alter table workers ADD sex  enum('F','M','S');mysql> desc workers;

修改表结构

  mysql> ALTER TABLE workers_info ADD num int;  ALTER TABLE workers MODIFY EmployedDates date after num;  ALTER TABLE workers ADD tel varchar(15) after salary;  INSERT INTO workers (name,salary,tel,mailbox,department,qq,EmployedDates)   values('xing',10000,'1598232123','[e-mail]','yanfa',736019646,20121221);