1、MySQL的安装
step1:yum search mysql #查看可安装列表
root@localhost ~]# yum search mysql
step2: sudo yum install mysql-server.x86_64 #安装mysql服务器版本
[root@localhost ~]# sudo yum install mysql-server.x86_64
step3:验证安装是否成功。
[ym@localhost ~]$ mysql #验证安装是否成功
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)
step4:先查看 /etc/rc.d/init.d/mysqld status看看mysql是否已经启动.另外看看是不是权限问题.
[root@localhost ~]# /etc/rc.d/init.d/mysqld status #看mysql是否已经启动
mysqld is stopped
step5: service mysqld start #启动服务
[root@localhost ~]# service mysqld start
Initializing MySQL database:
Installing MySQL system tables… OK
Filling help tables… OK
Step6:验证是否安装成功
[ym@localhost dir1]$ mysql
2、如何创建/删除数据库、看所有库、用指定库查看当前库、看当前用户,以及显示当前库中存在的表
语法如下:
mysql> create database ORCL; #创建数据库
mysql> drop database ORCL; #删除数据库
mysql> show databases; #查看所有库
mysql> use ORCL; #使用指定库
mysql> select database(); #查看当前库
mysql> select user(); #查看当前用户
mysql> show tables; #显示当前库中存在的表
创建数据库案例展示:
一、普通用户操作
[ym@localhost ~]$ mysql #普通用户,登录mysql
mysql> show databases; #普通用户,查看当前所有库
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.00 sec)
mysql> create database ORCL; #普通用户,创建库(存在权限问题)
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'ORCL'
二、根用户操作(管理员权限)
[ym@localhost ~]$ su -
Password:
[root@localhost ~]#mysql #打开mysql
mysql> show databases; # 查看当前所有库
+--------------------+
| Database |
+--------------------+
| information_schema |
| ORCL |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> drop database ORCL; #删除库
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
mysql> create database ORCL; #新建库
Query OK, 1 row affected (0.00 sec)
mysql> show databases; #查看库
+--------------------+
| Database |
+--------------------+
| information_schema |
| ORCL |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> use ORCL; #指定当前库
Database changed
mysql> create table test(id int, username varchar(20)); #在当前库中建立test表
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test values(0, 'ym'); #在test表中插入数据
Query OK, 1 row affected (0.00 sec)
mysql> select * from test; #查看test表数据
+------+----------+
| id | username |
+------+----------+
| 0 | ym |
+------+----------+
1 row in set (0.00 sec)
mysql> select database(); #查看当前数据库库名
+------------+
| database() |
+------------+
| ORCL |
+------------+
1 row in set (0.00 sec)
mysql> select user(); #查看当前用户
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> show tables; #显示当前数据库中存在的表
+----------------+
| Tables_in_ORCL |
+----------------+
| test |
+----------------+
1 row in set (0.00 sec)
4、显示表结构
mysql> describe test; #显示表结构
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| username | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
5、连接MySQL
mysql -h主机地址 -u用户名-p用户密码
一、连接本机上的MySQL
[root@localhost ~]# mysql -uroot -p
Enter password:
二、连接到远程主机上的MySQL
设远程主机的IP为:110.110.110.110,用户名为root,密码为abcd123。
则键入以下命令:
mysql -h110.110.110.110 -uroot -pabcd123
(注:u与root可以不用加空格,其它也一样)
三、退出MySQL:exit +Enter回车
mysql> exit
Bye
[root@localhost ~]#
6、修改密码
格式:mysqladmin -u用户名 -p旧密码 password新密码
一、给root用户加个密码 123
[root@localhost ~]# mysqladmin -u root password "123" #给根用户增加密码
(注:因为开始时root没有密码,所以-p旧密码一项就可以省略了。)
[root@localhost ~]# mysql -uroot -p123 #用新修改的密码登录
另外两种登录方式(隐藏密码登录):
[root@localhost ~]# mysql -uroot –p #方式一
Enter password:
[root@localhost ~]# mysql -u root –p #方式二
Enter password:
注:因为开始时root没有密码,所以“-p旧密码”一项就可以省略了。
另外,当root用户已存在密码时,语法如下:
[root@localhost ~]# mysqladmin -uroot -p123 password "1234" #将密码123修改为1234
[root@localhost ~]# mysql -uroot –p #新密码重新登录
Enter password:
7、增加/删除用户
格式:grant select on数据库.* to用户名@登录主机 identified by "密码"
案例一:增加用户
mysql> create user 'ymtest'@'localhost' identified by '123'; #创建用户
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL ON *.* TO 'ymtest'@'localhost'; #赋给用户所有权限
Query OK, 0 rows affected (0.00 sec)
(或增加指定的权限:增加一个用户'ymtest'密码为123,它可以在任何主机上登录,并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入MYSQL,然后键入以下命令:
grant select,insert,update,delete on *.* to ‘'ymtest'’@"%" Identified by "123";)
mysql> flush privileges; #在添加一个用户后最后要刷新下系统的权限表,必须!
Query OK, 0 rows affected (0.01 sec)
mysql> exit;
[root@localhost ~]# mysql -uymtest –p #验证
Enter password:
mysql> select user(); #查看当前用户
+------------------+
| user() |
+------------------+
| ymtest@localhost |
+------------------+
1 row in set (0.00 sec)
案例二:删除用户
[root@localhost ~]# mysql -uymtest –p #可以在当前用户下删除自身
Enter password:
mysql> DELETE FROM mysql.user WHERE User="ymtest" and Host="localhost";
Query OK, 1 row affected (0.00 sec)
mysql> flush privileges; #注意:刷新下系统的权限表!!!
Query OK, 0 rows affected (0.00 sec)
mysql> exit;
Bye
[root@localhost ~]# mysql -uymtest –p #删除成功了
Enter password:
ERROR 1045 (28000): Access denied for user 'ymtest'@'localhost' (using password: YES)
8、查看当前所有的用户
mysql> show databases; #查看当前库名
+--------------------+
| Database |
+--------------------+
| information_schema |
| ORCL |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> use mysql; #切换数据库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select host, user, password from user; #查看当前所有用户和密码
+-----------------------+------+-------------------------------------------+
| host | user | password |
+-----------------------+------+-------------------------------------------+
| localhost | root | *A4B6157319038724E3560894F7F932C8886EBFCF |
| localhost.localdomain | root | |
| 127.0.0.1 | root | |
| localhost | | |
| localhost.localdomain | | |
+-----------------------+------+-------------------------------------------+
5 rows in set (0.00 sec)
9、用批处理方式使用MySQL
[ym@localhost dir1]$ vim mytest.sql #编辑批处理文件
[ym@localhost dir1]$ mysql -uymtest -p<mytest.sql #执行批处理文件
Enter password:
id username
0 ym
1 xqd
id username
0 ym
1 xqd
[ym@localhost dir1]$
注:
/* mytest.sql文件内容为:
use ORCL
select * from test;
select * from test;
*/
10、mysql 管理员创建的数据库,普通用户如何使用
假设有管理员用户创建了数据库ORCL,和普通用户ym
在ym用户登录时,开始查看数据库信息为:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
在管理(root)用户下,执行如下赋权限命令
mysql> grant all privileges on ORCL.* to y;
再次,ym用户登录,即有:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ORCL |
+--------------------+
2 rows in set (0.00 sec)
11、Python连接MySQL数据库
Step0:安装MySQLdb 包
[root@localhost ~]# yum search MySQLdb #搜索MySQLdb 匹配项
=========================== Matched: MySQLdb =============
MySQL-python.x86_64 : An interface to MySQL
[root@localhost ~]# yum install MySQL-python.x86_64 #安装MySQL-python.x86_64
Installed:
MySQL-python.x86_64 0:1.2.3-0.3.c1.1.el6
Complete!
[root@localhost ~]# python #进入到Python指令界面
>>> import MySQLdb #验证MySQLdb是否安装成功
Step1:测试连接操作
[root@localhost ~]# python
>>> import MySQLdb
>>>conn=MySQLdb.connect(host='localhost',user='root',passwd='1234',db='ORCL',port=3306)
>>> cur=conn.cursor()
>>> cur.execute('select * from test')
>>> results=cur.fetchall()
>>> results
((0L, 'ym'), (1L, 'xqd'), (2L, 'ym1'), (3L, 'xqd1'))
>>> cur.close()
>>> conn.close()
------------------------------------------未完待续,持续更新中---------------------------------------------
11、数据库备份与还原
12、Oracle与MySQL数据相互导入导出操作
13、存储过程,函数,触发器