mysql 方方面面
时间:2017-05-30 21:42 来源:linux.it.net.cn 作者:IT
一、连接MYSQL
格式：MySQL -h主机地址 -u用户名 －p用户密码 例如：mysql -h127.0.0.1 -uroot -proot 在windows下，需要手工加入mysql安装路径
如果指定端口就加 -P3307 大写P
1．例1：连接到本机上的MYSQL。 首先在打开DOS窗口，然后进入目录 mysqlbin，再键入命令mysql -uroot -p，回车后提示你输密码，如果刚安装好MYSQL，超级用户root是没有密码的，故直接回车即可进入到MYSQL中了，MYSQL的提示符是：mysql> 2．例2：连接到远程主机上的MYSQL。假设远程主机的IP为：110.110.110.110，用户名为root,密码为abcd123。则键入以下命令： mysql -h110.110.110.110 -uroot -pabcd123 （注：u与root可以不用加空格，其它也一样） 3．退出MYSQL命令：exit （回车） 注意：想要成功连接到远程主机，需要在远程主机打开MySQL远程访问权限 方法如下： 在远程主机中以管理员身份进入 输入如下命令 mysql>GRANT ALL PRIVILEGES ON *.* TO 'agui'@%'IDENTIFIEDBY '123' WITH GRANT OPTION; FLUSH PRIVILEGES; //赋予任何主机访问数据的权限 mysql>FLUSH PRIVILEGES //修改生效 agui为我们使用的用户名 密码为123 即：在远程主机上作好设置，我们即可通过mysql -h110.110.110.110 -uagui -p123连接进远程主机
二、修改密码
格式：mysqladmin -u用户名 -p旧密码 password 新密码 1．例1：给root加个密码ab12。首先在DOS下进入目录mysqlbin，然后键入以下命令 mysqladmin -uroot -password ab12 注：因为开始时root没有密码，所以-p旧密码一项就可以省略了。 2．例2：再将root的密码改为djg345。 mysqladmin -uroot -pab12 password djg345
mysqladmin -uroot -h192.168.129.136 -P3307 -prhea password rheanew
三、增加新用户
一, 创建用户: (注意：信息都需要单引号)
命令:CREATE USER 'username'@'host' IDENTIFIED BY 'password';
说明:username - 你将创建的用户名, host - 指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost, 如果想让该用户可以从任意远程主机登陆,可以使用通配符%. password - 该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器.
例子: CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '';
CREATE USER 'pig'@'%';
权限是一个用逗号分隔的你想要赋予的MySQL用户权限的列表 。你可以指定的权限可以分为三种类型：
数据库/数据表/数据列权限：
Alter: 修改已存在的数据表(例如增加/删除列)和索引 。
Create: 建立新的数据库或数据表 。
Delete: 删除表的记录 。
Drop: 删除数据表或数据库 。
INDEX: 建立或删除索引 。
Insert: 增加表的记录 。
Select: 显示/搜索表的记录 。
Update: 修改表中已存在的记录 。
还有很多权限项目，可以先赋值全部权限给一个用户，然后revoke 一个，再show grants for user; 就可以看到到底有多少可以赋值的权限了
全局管理MySQL用户权限：
file: 在MySQL服务器上读写文件 。
PROCESS: 显示或杀死属于其它用户的服务线程 。
RELOAD: 重载访问控制表，刷新日志等 。
SHUTDOWN: 关闭MySQL服务 。
特别的权限：
ALL: 允许做任何事(和root一样) 。
USAGE: 只允许登录--其它什么也不允许做 。
查看用户权限： show grants for user;
grant 权限 on 数据库对象 to 用户
grant select, insert, update, delete on testdb.* to common_user@'%'
如果需要用户拥有继续赋权的能力，最后增加： WITH GRANT OPTION;
二、查看用户
select user,host,password from mysql.user;
三.设置与更改用户密码
命令:SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');如果是当前登陆用户用SET PASSWORD = PASSWORD("newpassword");
例子: SET PASSWORD FOR 'pig'@'%' = PASSWORD("123456");
四.撤销用户权限
命令: REVOKE privilege ON databasename.tablename FROM 'username'@'host';
说明: privilege, databasename, tablename - 同授权部分.
例子: REVOKE SELECT ON *.* FROM 'pig'@'%';
五.删除用户
命令: DROP USER 'username'@'host';
五、显示命令
1．显示数据库列表。 show databases; 刚开始时才两个数据库：mysql和test。mysql库很重要它里面有MYSQL的系统信息，我们改密码和新增用户，实际上就是用这个库进行操作。 2．显示库中的数据表： use mysql； //打开库，学过FOXBASE的一定不会陌生吧 show tables; 3．显示数据表的结构： describe 表名； 4．建库： create database 库名； 5．建表： use 库名； create table 表名 (字段设定列表)； 6．删库和删表： drop database 库名； drop table 表名； 7、将表中记录清空： delete from 表名； 8．显示表中的记录： select * from 表名； 9、显示最后一个执行的语句所产生的错误、警告和通知： show warnings; 10．只显示最后一个执行语句所产生的错误： show errors;
六、一个建库和建表以及插入数据的实例
drop database if exists school; //如果存在SCHOOL则删除 create database school; //建立库SCHOOL use school; //打开库SCHOOL create table teacher //建立表TEACHER ( id int(3) auto_increment not null primary key, name char(10) not null, address varchar(50) default ‘深圳', year date ); //建表结束 //以下为插入字段 insert into teacher values('','glchengang',’XX公司‘，'1976-10-10'); insert into teacher values('','jack',’XX公司‘，'1975-12-23'); 注：在建表中： 1．将ID设为长度为3的数字字段：int(3)并让它每个记录自动加一：auto_increment并不能为空：not null而且让他成为主字段primary key。 2．将NAME设为长度为10的字符字段。 3．将ADDRESS设为长度50的字符字段，而且缺省值为深圳。varchar和char有什么区别呢，只有等以后的文章再说了。 4．将YEAR设为日期字段。 如果你在mysql提示符键入上面的命令也可以，但不方便调试。你可以将以上命令原样写入一个文本文件中假设为school.sql，然后复制到c:\下，并在DOS状态进入目录\mysql\bin，然后键入以下命令： mysql -uroot -p密码 < c:\school.sql 如果成功，空出一行无任何显示；如有错误，会有提示。（以上命令已经调试，你只要将//的注释去掉即可使用）。
七、将文本数据转到数据库中
1．文本数据应符合的格式：字段数据之间用tab键隔开，null值用\n来代替. 例： 3．rose 深圳二中 1976-10-10 4．mike 深圳一中 1975-12-23 2．数据传入命令 load data local infile “文件名” into table 表名； 注意：你最好将文件复制到\mysql\bin目录下，并且要先用use命令打开表所在的数据库。
八、备份数据库
（命令在DOS的\mysql\bin目录下执行） mysqldump --opt school>school.bbb 注释：将数据库school备份到school.bbb文件，school.bbb是一个文本文件，文件名任取，打开看看你会有新发现。
九、启动带跟踪文件的mysqld服务器
mysqld --debug
十、关闭MySQL服务器
mysqladmin -uroot -proot shutdown
十一、在Windows下启动MySQL服务
mysqld --console
十二、执行 SQL文件
Source c:\123.sql
回车即可执行，而且是可以批量sql语句执行
十三、查看修改表结构
desc 表名;
show columns from 表名;
describe 表名;
show create table 表名;
加唯一限制条件的索引549830479
mysql> alter table tablename add unique emp_name2(cardnumber);
删除某个索引549830479
mysql>alter table tablename drop index emp_name;
修改表：549830479
增加字段：549830479
mysql> ALTER TABLE table_name ADD field_name field_type;
修改原字段名称及类型：549830479
mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;
删除字段：549830479
mysql> ALTER TABLE table_name DROP field_name;
PS;修改字段属性
ALTER TABLE tableName MODIFY cloumnName DATETIME;
十四、对应Oracle的Dual操作
mysql实际上可以没有dual，但是考虑到Oracle的习惯，也可以写dual
例如:
select 1+1; 与 select 1+1 from dual; 结果都是2
获取当前时间（日期+时间）
select current_timestamp();
获取当前日期
select current_date();
十五、对应Oracle的函数
decode 用if替代 select if(1>0,1,2);
trim(s) - 移除开头跟字尾的空格符。也可以用 rtrim() 或 ltrim()
replace(target, find, replace) - 传回 target 字符串，将 find 字符串覆盖成 replace 字符串
length(s) - 找字符串的长度
substring(s, position, length) - 传回字符串里的字符串，s 是本来的字符串，position 是开始的位置，length 是传回的字数
select ifnull( a, b ) 替代 nvl(a, b)
大小写：select lower(s) ; select upper(s);
to_char( date, 'yyyymmdd)对应的：date_format(a.ctime,'%Y-%c-%d %h:%i:%s')
十六、JDBC驱动
到mysql的主页，http://www.mysql.com/ download里面有一个connecters，里面下载
我已经存放到D:\nokill\W我的笔记\W文档\Java\网上收集\mysql
十七、MySql相关工具
sqlyog 夏亮推荐，远程管理维护工具
十八、MySql 自动提交autocommit 问题( MySQL事务的理解)
mysql默认是自动提提的，这个很郁闷，可以通过执行语句来设置不自动提交
set autocommit=0;
也可以修改mysql.ini
最后增加：
[mysqld]
init_connect='SET AUTOCOMMIT=0'
但是这个只对普通用户有效，root登录进去后无效 , 仍然是autocommit=TRUE
可以通过 show variables like 'autocommit';来查看
【天啦】这个Autocommit绝对不能设置为false，这个是大忌讳
设置为false后，其他事务链接修改数据库，哪怕commit了，当前事务也无法看到，除非空执行一次commit或者rollback;，也就是说这个autocommit完全不是事务的概念,他会让你看不到别人提交的东西
进一步分析，得出MySQL的事务经验的初步结论：
1. MySQL支持事务，前提是表使用的是InnoDB引擎，这个似乎最新版本是默认值，确认办法是标记my.ini，看看#skip-innodb 是不是前面有一个#来注释，这句话必须带#注释掉
2. MySQL支持事务不彻底，不稳定，见我转载的 MySQL与Oracle的区别，目前感觉MySQL不能用于信息系统，只能是简单的信息存储检索
3. MySQL使用Java操作或者直接用工具输入SQL，默认是没有事务的，即执行就提交，那么需要Begin来开启事务，这样才支持回滚
st = conn.createStatement();
st.execute("begin");
pst = conn.prepareStatement("insert into rhea_kvm(name, os, ip) values (?,?,?)");
pst.setString(1, "my xp");
pst.setString(2, "windows");
pst.setString(3, "192.1.12.1");
pst.execute();
conn.commit();
但是注意，MySQL的事务有些怪异，一个链接Begin后，在Commit或者Rollback前，无法检索到其他事务的提交数据，但是可以删除到，神奇！！！
例如：
A事务，Begin 然后Select * from rhea_kvm 得到0
B事务 Begin; Insert; Commit;
A事务再去检索，还是0，但是去Delete from rhea_kvm;可以删除到一行
A如果Commit或者Rollback;后去检索，就能看到结果
(责任编辑：IT)
一、连接MYSQL格式：MySQL -h主机地址 -u用户名 －p用户密码 例如：mysql -h127.0.0.1 -uroot -proot 在windows下，需要手工加入mysql安装路径 如果指定端口就加 -P3307 大写P 1．例1：连接到本机上的MYSQL。 首先在打开DOS窗口，然后进入目录 mysqlbin，再键入命令mysql -uroot -p，回车后提示你输密码，如果刚安装好MYSQL，超级用户root是没有密码的，故直接回车即可进入到MYSQL中了，MYSQL的提示符是：mysql> 2．例2：连接到远程主机上的MYSQL。假设远程主机的IP为：110.110.110.110，用户名为root,密码为abcd123。则键入以下命令： mysql -h110.110.110.110 -uroot -pabcd123 （注：u与root可以不用加空格，其它也一样） 3．退出MYSQL命令：exit （回车） 注意：想要成功连接到远程主机，需要在远程主机打开MySQL远程访问权限 方法如下： 在远程主机中以管理员身份进入 输入如下命令 mysql>GRANT ALL PRIVILEGES ON *.* TO 'agui'@%'IDENTIFIEDBY '123' WITH GRANT OPTION; FLUSH PRIVILEGES; //赋予任何主机访问数据的权限 mysql>FLUSH PRIVILEGES //修改生效 agui为我们使用的用户名 密码为123 即：在远程主机上作好设置，我们即可通过mysql -h110.110.110.110 -uagui -p123连接进远程主机 二、修改密码格式：mysqladmin -u用户名 -p旧密码 password 新密码 1．例1：给root加个密码ab12。首先在DOS下进入目录mysqlbin，然后键入以下命令 mysqladmin -uroot -password ab12 注：因为开始时root没有密码，所以-p旧密码一项就可以省略了。 2．例2：再将root的密码改为djg345。 mysqladmin -uroot -pab12 password djg345
mysqladmin -uroot -h192.168.129.136 -P3307 -prhea password rheanew 三、增加新用户一, 创建用户: (注意：信息都需要单引号)命令:CREATE USER 'username'@'host' IDENTIFIED BY 'password'; 说明:username - 你将创建的用户名, host - 指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost, 如果想让该用户可以从任意远程主机登陆,可以使用通配符%. password - 该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器. 例子: CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456'; CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456'; CREATE USER 'pig'@'%' IDENTIFIED BY '123456'; CREATE USER 'pig'@'%' IDENTIFIED BY ''; CREATE USER 'pig'@'%';
权限是一个用逗号分隔的你想要赋予的MySQL用户权限的列表 。你可以指定的权限可以分为三种类型： 数据库/数据表/数据列权限：
Alter: 修改已存在的数据表(例如增加/删除列)和索引 。
还有很多权限项目，可以先赋值全部权限给一个用户，然后revoke 一个，再show grants for user; 就可以看到到底有多少可以赋值的权限了
查看用户权限： show grants for user;
grant 权限 on 数据库对象 to 用户grant select, insert, update, delete on testdb.* to common_user@'%'
如果需要用户拥有继续赋权的能力，最后增加： WITH GRANT OPTION; 二、查看用户 select user,host,password from mysql.user;
三.设置与更改用户密码 命令:SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');如果是当前登陆用户用SET PASSWORD = PASSWORD("newpassword"); 例子: SET PASSWORD FOR 'pig'@'%' = PASSWORD("123456");
四.撤销用户权限 命令: REVOKE privilege ON databasename.tablename FROM 'username'@'host'; 说明: privilege, databasename, tablename - 同授权部分. 例子: REVOKE SELECT ON *.* FROM 'pig'@'%';
五.删除用户 命令: DROP USER 'username'@'host'; 五、显示命令1．显示数据库列表。 show databases; 刚开始时才两个数据库：mysql和test。mysql库很重要它里面有MYSQL的系统信息，我们改密码和新增用户，实际上就是用这个库进行操作。 2．显示库中的数据表： use mysql； //打开库，学过FOXBASE的一定不会陌生吧 show tables; 3．显示数据表的结构： describe 表名； 4．建库： create database 库名； 5．建表： use 库名； create table 表名 (字段设定列表)； 6．删库和删表： drop database 库名； drop table 表名； 7、将表中记录清空： delete from 表名； 8．显示表中的记录： select * from 表名； 9、显示最后一个执行的语句所产生的错误、警告和通知： show warnings; 10．只显示最后一个执行语句所产生的错误： show errors;六、一个建库和建表以及插入数据的实例drop database if exists school; //如果存在SCHOOL则删除 create database school; //建立库SCHOOL use school; //打开库SCHOOL create table teacher //建立表TEACHER ( id int(3) auto_increment not null primary key, name char(10) not null, address varchar(50) default ‘深圳', year date ); //建表结束 //以下为插入字段 insert into teacher values('','glchengang',’XX公司‘，'1976-10-10'); insert into teacher values('','jack',’XX公司‘，'1975-12-23'); 注：在建表中： 1．将ID设为长度为3的数字字段：int(3)并让它每个记录自动加一：auto_increment并不能为空：not null而且让他成为主字段primary key。 2．将NAME设为长度为10的字符字段。 3．将ADDRESS设为长度50的字符字段，而且缺省值为深圳。varchar和char有什么区别呢，只有等以后的文章再说了。 4．将YEAR设为日期字段。 如果你在mysql提示符键入上面的命令也可以，但不方便调试。你可以将以上命令原样写入一个文本文件中假设为school.sql，然后复制到c:\下，并在DOS状态进入目录\mysql\bin，然后键入以下命令： mysql -uroot -p密码 < c:\school.sql 如果成功，空出一行无任何显示；如有错误，会有提示。（以上命令已经调试，你只要将//的注释去掉即可使用）。七、将文本数据转到数据库中1．文本数据应符合的格式：字段数据之间用tab键隔开，null值用\n来代替. 例： 3．rose 深圳二中 1976-10-10 4．mike 深圳一中 1975-12-23 2．数据传入命令 load data local infile “文件名” into table 表名； 注意：你最好将文件复制到\mysql\bin目录下，并且要先用use命令打开表所在的数据库。八、备份数据库（命令在DOS的\mysql\bin目录下执行） mysqldump --opt school>school.bbb 注释：将数据库school备份到school.bbb文件，school.bbb是一个文本文件，文件名任取，打开看看你会有新发现。九、启动带跟踪文件的mysqld服务器mysqld --debug十、关闭MySQL服务器mysqladmin -uroot -proot shutdown十一、在Windows下启动MySQL服务mysqld --console
十二、执行 SQL文件 Source c:\123.sql
回车即可执行，而且是可以批量sql语句执行 加唯一限制条件的索引549830479 mysql> alter table tablename add unique emp_name2(cardnumber);
mysql>alter table tablename drop index emp_name;
增加字段：549830479 mysql> ALTER TABLE table_name ADD field_name field_type;
mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;
mysql> ALTER TABLE table_name DROP field_name; PS;修改字段属性 ALTER TABLE tableName MODIFY cloumnName DATETIME;
十四、对应Oracle的Dual操作 mysql实际上可以没有dual，但是考虑到Oracle的习惯，也可以写dual
例如: select 1+1; 与 select 1+1 from dual; 结果都是2
获取当前时间（日期+时间） select current_timestamp(); 获取当前日期 select current_date();
十五、对应Oracle的函数
decode 用if替代 select if(1>0,1,2); trim(s) - 移除开头跟字尾的空格符。也可以用 rtrim() 或 ltrim() replace(target, find, replace) - 传回 target 字符串，将 find 字符串覆盖成 replace 字符串 length(s) - 找字符串的长度 substring(s, position, length) - 传回字符串里的字符串，s 是本来的字符串，position 是开始的位置，length 是传回的字数 select ifnull( a, b ) 替代 nvl(a, b) 大小写：select lower(s) ; select upper(s); to_char( date, 'yyyymmdd)对应的：date_format(a.ctime,'%Y-%c-%d %h:%i:%s')
十六、JDBC驱动 到mysql的主页，http://www.mysql.com/ download里面有一个connecters，里面下载 我已经存放到D:\nokill\W我的笔记\W文档\Java\网上收集\mysql
十七、MySql相关工具
sqlyog 夏亮推荐，远程管理维护工具
十八、MySql 自动提交autocommit 问题( MySQL事务的理解) mysql默认是自动提提的，这个很郁闷，可以通过执行语句来设置不自动提交 set autocommit=0;
也可以修改mysql.ini 最后增加：
[mysqld]
但是这个只对普通用户有效，root登录进去后无效 , 仍然是autocommit=TRUE 可以通过 show variables like 'autocommit';来查看 【天啦】这个Autocommit绝对不能设置为false，这个是大忌讳 设置为false后，其他事务链接修改数据库，哪怕commit了，当前事务也无法看到，除非空执行一次commit或者rollback;，也就是说这个autocommit完全不是事务的概念,他会让你看不到别人提交的东西
进一步分析，得出MySQL的事务经验的初步结论： 1. MySQL支持事务，前提是表使用的是InnoDB引擎，这个似乎最新版本是默认值，确认办法是标记my.ini，看看#skip-innodb 是不是前面有一个#来注释，这句话必须带#注释掉
2. MySQL支持事务不彻底，不稳定，见我转载的 MySQL与Oracle的区别，目前感觉MySQL不能用于信息系统，只能是简单的信息存储检索
3. MySQL使用Java操作或者直接用工具输入SQL，默认是没有事务的，即执行就提交，那么需要Begin来开启事务，这样才支持回滚
st = conn.createStatement(); 但是注意，MySQL的事务有些怪异，一个链接Begin后，在Commit或者Rollback前，无法检索到其他事务的提交数据，但是可以删除到，神奇！！！ 例如： A事务，Begin 然后Select * from rhea_kvm 得到0 B事务 Begin; Insert; Commit; A事务再去检索，还是0，但是去Delete from rhea_kvm;可以删除到一行 A如果Commit或者Rollback;后去检索，就能看到结果
(责任编辑：IT) |