MySQL数据库学习笔记[转]

MySQL数据库学习笔记[转]

(实验环境:Redhat9.0,MySQL3.23.54)
纲要：
一，连接MySQL
二，MySQL管理与授权
三，数据库简单操作
四, 数据库备份
五，后记
一，连接MySQL
格式：mysql -h 远程主机地址 -u 用户名 -p 回车
输入密码进入：

mysql -u root -p 回车
Enter password: ,输入密码就可以进入
mysql> 进入了
退出命令:>exit 或者ctrl+D

二，MySQL管理与授权
1.修改密码：
格式：mysqladmin -u 用户名 -p 旧密码 password 新密码

2.增加新用户:
>grant create,select,update....(授予相关的操作权限)
->on 数据库.*
-> to 用户名@登录主机 identified by '密码'

操作实例：

给root用户添加密码:
# mysqladmin -u root password 52netseek
因为开始root没有密码,所以-p旧密码一项可以省略.
登陆测试：
# mysql -u root -p 回车
输入密码,成功登陆.

将原有的mysql管理登陆密码52netseek改为52china.
# mysqladmin -u root -p 52netseek password '52china'

创建数据库添加用户并授予相应的权限:
mysql> create database phpbb;
Query OK, 1 row affected (0.02 sec)

mysql> use phpbb;
Database changed
mysql> grant create,select,update,insert,delete,alter
-> on phpbb.*
-> to phpbbroot@localhost identified by '52netseek';
Query OK, 0 rows affected (0.00 sec)

授予所有的权限:
>grant all privileges
>on bbs.*
>to bbsroot@localhost identified by '52netseek'

回收权限：
revoke create,select,update,insert,delete,alter
on phpbb.*
from phpbbroot@localhost identified by '52netseek';

完全将phpbbroot这个用户删除:
>use mysql
>delete from user
where user='phpbbroot' and host='localhost';
>flush privileges; 刷新数据库

三，数据库简单操作
1.显示数据库列表:
>show databases;
mysql
test
2.使其成为当前操作数据库
>use mysql; 打开数据库.
>show tables; 显示mysql数据库中的数据表.
3.显示数据表的表结构:
>describe 表名;
>describe user; 显示user表的表结构：
4.创建数据库,建表
>create database 数据库名;
>use 数据库名；
>create table 表名(字段设定列表)
5.删除数据库，册除表
>drop database 数据库名;
>drop table 表名;
6.显示表中的记录;
select * from 表名;
7.修改数据库结构:
增加字段：
alter table dbname add column <字段名><字段选项>
修改字段：
alter table dbname change <旧字段名> <新字段名><选项>
删除字段:
alter table dbname drop column <字段名>

实例操作：
>create database office;
>use office;
mysql> create table personal(
-> member_no char(5) not null,
-> name char(,
-> birthday date,
-> exam_score tinyint,
-> primary key(member_no)
-> );
Query OK, 0 rows affected (0.01 sec)
>desc personal; 显示表结构：
+------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| member_no | char(5) | | PRI | | |
| name | char( | YES | | NULL | |
| birthday | date | YES | | NULL | |
| exam_score | tinyint(4) | YES | | NULL | |
+------------+------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

insert into personal values ('001','netseek','1983-03-15','95');
insert into personal values ('002','heihei','1982-02-24','90');
insert into personal values ('003','gogo','1985-05-21','85');
insert into personal values ('004','haha','1984-02-25','84');
insert into personal values ('005','linlin','1982-04-28','85');
insert into personal values ('006','xinxin','1985-03-15','75');

mysql> select * from personal;
+-----------+---------+------------+------------+
| member_no | name | birthday | exam_score |
+-----------+---------+------------+------------+
| 001 | netseek | 1983-03-15 | 95 |
| 002 | heihei | 1982-02-24 | 90 |
| 003 | gogo | 1985-05-21 | 85 |
| 004 | haha | 1984-02-25 | 84 |
| 005 | linlin | 1982-04-28 | 85 |
| 006 | xinxin | 1985-03-15 | 75 |
+-----------+---------+------------+------------+

修改数据库表：
要求: 在birthday这后增加一个为height的字段,数据类型为tinyint.
将字段exam_score 改名为scores，数据类型不变
>alter table personal
->add column height tinyint after birthday,
->change column exam_score scores tinyint;

mysql> select * from personal;
+-----------+---------+------------+--------+--------+
| member_no | name | birthday | height | scores |
+-----------+---------+------------+--------+--------+
| 001 | netseek | 1983-03-15 | NULL | 95 |
| 002 | heihei | 1982-02-24 | NULL | 90 |
| 003 | gogo | 1985-05-21 | NULL | 85 |
| 004 | haha | 1984-02-25 | NULL | 84 |
| 005 | linlin | 1982-04-28 | NULL | 85 |
| 006 | xinxin | 1985-03-15 | NULL | 75 |
+-----------+---------+------------+--------+--------+

给表中插入数据：
>update personal set scores=95+5 where name='netseek';
>select scores from personal where name='netseek';
+--------+
| scores |
+--------+
| 100 |
+--------+

删除表名字为'gogo'所有的信息中的的:
> delete from personal where name='gogo';

册除数据库中的表:
mysql>drop table if exists personal;

三，数据库的导入与导出
导出：
使用select into outfile 'filename'语句
使用mysqldump实用程序
使用select into outfile 'filename'语句

1.只能处理单个表，输出文件只有数据，没有表结构
我们要将office,其中有一个表为personal,现在要把personal卸成文本文件out.txt:
>use office;
>select * from personal into outfile 'out.txt'; 可以看在/var/lib/mysql/office/目录下有out.txt
select * from personal into outfile './out.txt'; 可以看在out.txt 在/var/lib/mysql/目录下用out.txt

2.使用mysqldump实用程序(可以轻松处理多个表)
# cd /var/lib/mysql
导出建立相关表的建表命令和插入指令
# mysqldump bbs >bbs.sql 将数据库bbs导入到bbs.sql中

如果要将bbs.sql导入数据库可以使用:
mysql> create database bbstest; 先建立一个名为office 的数据库.
# mysql bbstest <bbs.sql (这个常用在将本地的数据库文件传到服务器上，再导入到数据库中)

只想导出建表指令:
# mysqldump -d bbs >bbscreate.sql
只想导出插入数据的sql指令：
# mysqldump -t bbs >bbsinsert.sql
同时导出数据库中建表指令和表中的数据：
# mysqldump -T./ bbs cdb_admingroups (其中./表示当前目录,cdb_admingroups为bbs数据库其中的一个表)
#ls
cdb_admingroups.sql 导出了建表指令
cdb_admingroups.txt 导出了表中的数据

导入：
从文件中加载数据库:
mysql>load data infile "/tmp/name.txt" into table names;
mysql>select * from names;

四,数据库备份

1.手动拷贝备份:
MySQL数据库的文件保存在目录/var/lib/mysql中,数据库为每个库建立一个目录,所有的数据库文件都在这些目录中.
[root@linuxhero mysql]#ls
bbs mysql mysql.sock phpbb test office 显示其中的数据库.

如果我们要将现在的数据库目录备份为mysql.bak .
[root@linuxhero lib]# cp -rf mysql mysql.bak
如果数据库遭到了破坏，现在要将数据库恢复：
[root@linuxhero lib]# cp -rf mysql.bak/* mysql
恢复数据库以后，var/lib/mysql中的文件已改变了，要更改文件的所属权限必须改变MySQL数据库的用户读写权限。
所以我们得启动和运行mysql，并登陆数据库:
[root@linuxhero lib]# /etc/init.d/mysqld start
[root@linuxhero lib]# mysql -u root -p
Enter password:输入密码成功登陆.
mysql> show databses;

2.利用mysqldump来备份数据库
[root@linuxhero mysql]# mysqldump --opt bbs -u root -p > bbs.sql
Enter password:
注:--opt添加备份的其它选项,bb为其中一个数据库名,
上面的意思是：使用重定向输出将备份写入到文件bb.sql中.
[root@linuxhero mysql] #less bbs.sql

如果要恢复bb这个数据库，则进行如下操作:
[root@linuxhero mysql] #mysql bbs -u root -p < bbs.sql

如果要备份所有数据库:
[root@linuxhero mysql] #mysqldump --opt --all-databases -u root -p >mysql.bak
Enetr password:输入密码即可
恢复所有数据库,不用输入数据库的名字:
[root@linuxhero mysql] #mysql -u root -p < mysql.bak
Enetr password: 输入密码即可

五,后记:
MySQL数据库个人学习笔记，这是我个人学习过程中的一个简单的总结，希望对于希望学习mysql的朋友有所帮助，如果有什么不对或者不妥的地方请多多指教，欢迎大家与我交流学习Linux相关的知识。

MySQL服务的配置和使用:
初始状态，给MySQL结密码:
#mysqladmin -u root password 密码字符串
修改MySQL管理员的密码:
#mysqladmin -u root -p password 新的密码字符串.
#mysql -uroot -p 输入密码登陆.

数据库的创建与删除:
create database netseek;
user netseek
dorp database netseek;
show databases;

表的创建与删除:

部分字段。。。图表画上.
mysql> create table student(
    -> sno varchar(7) not null,
    -> sname varchar(20) not null,
    -> ssex char(1) default 't',
    -> sbirthday date,
    -> sdepa char(20),
    -> primary key (sno)
    -> );
mysql> show tables;
+---------------+
| Tables_in_stu |
+---------------+
| student       |
+---------------+
1 row in set (0.00 sec)

mysql> desc student
    -> ;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| sno       | varchar(7)  | NO   | PRI |         |       |
| sname     | varchar(20) | NO   |     |         |       |
| ssex      | char(1)     | YES  |     | t       |       |
| sbirthday | date        | YES  |     | NULL    |       |
| sdepa     | char(20)    | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

复制表
create table xstudent like student;(可以复制表结构及内容，且能从表源中复制键)

删除表:
mysql> drop table xstudent;

更改表名:
alter table xstu rename to xstudent;
alter table studnet rename to xstudent;
修改表:
增加字段:
mysql> alter table xstu add saddress varchar(25);

改更字段名，和字段类型.
mysql> alter table xstu change saddress shome text;

alter table xstu change shome shome varchar(25); 这样也可以更改字段类型.
alter table xstu modify shome varchar(25); ,这个也一样可以更改字段类型.

删除字段:
alter table xstu drop shome; 即可删除shome这个字段.

总结:alter可以完成多项任务,更改表名，更改字段名，和类型，删除字段.
也可以用一条alter语句完成多项任务:
mysql> alter table xstudent
    -> add saddress varchar(25),
    -> change sbirthday sage int(3),
    -> rename to xstu;

表中的数据插入与删除,修改.

mysql> insert into student (sno,sname,ssex,sbirthday,sdepa)
-> values ('02320','Linda',default,19850516,'computer');
>select * from student; 就可以看到刚才插入的数据.

insert还可以同是插入多个values子句，可以同进插入多个条记录.
mysql> insert into student values
    -> ('02322','JinPing','m',19830215,'computer'),
    -> ('02323','Lenmi','f',19850819,'english');
删除记录:
mysql> delete from student where sno='02322';
册除字段为sname中的字段值的前2位，为Li的记录。
mysql> delete from student where left(sname,2)='Li';

如果要删除表中所有的记录:
mysql> delete from student;
Query OK, 2 rows affected (0.01 sec)

另一种删除表中所有记录的方法:
mysql> truncate table student;
Query OK, 0 rows affected (0.00 sec)

总结，对比一下delete,truncate,可以看出来什么：
truncate命令不管表中有多少记录，它都是删除表，然后重建请表，因此0 rows affected;而delete
命令是将表中所有的记录一个一个删除，所以2 rows affected,所以，truncate命令比delete命令要快,
特别是在表中记录非常多时非常明显。

修改记录:update
mysql> update student set sdep='english' where sno='0318990';

创建索引与删除索引。
为什么要创建：
为了加速数据查询的速度，MySQL允许用户为一个表的特定字段设置索引，一个索引就是该字段值的一个列表。

索引既可以在使用create table句语创建的同时创建，也可以使用create index语句，向已存在的表中添加。

1.创建表的同是创建索引：
创建一个选课表course,将课程编号cno设为主键，同时为课程名称cname字段创建一个名为cna的索引。

mysql> create table course(
    -> cno varchar(5) not null,
    -> cname varchar(30) not null,
    -> teacher varchar(20),
    -> primary key (cno),
    -> index cna(cname)
    -> );
如果将:index cna(cname)改为unique(cname),创建的是UNIQUE索引，该索引要求索引字段中的值必须
是唯一的，也就是说，表中各条记录中该字段的值不能相同，若向表中插入一个与现有记录中访字段值相同的记录，则会失败。

2. 向已存的表中添加索引。
为为表student中的sname字段创建名为sna的索引.
create index sna on student (sname);

删除索引:
mysql> drop index sna on student;
为为表student的sname字段建名为sna的索引，并指定索引长度值为10，可以使用:

mysql> create index sna on student (sname(10));
注：这里指定索引长度值为10，是基于大多数名字通常在前10个字符是不一样的考虑，这样创建的索引
文件会更小一些，既可以节省磁盘空间，又可以加速速insert等速度。

用户的创建和删除:

授权表:
查看数据库，mysql 中表user前4个字段的内容：
mysql> select host,user,password,select_priv from mysql.user;
查看数据库mysql中表db的前4个字段内容:
mysql> select host,db,user,select_priv from mysql.db;

注:虽然在表db中定义了允许任何用户可以从任何主机访问数据库test,但由于在表user中限制任何用户只
能从本地localhost来连接数据库，因为在这两个表的共同作用下，MySQL默认设置是任何用户只能从本地
完全访问数据库test。

创建用户与授权:
利用insert语句添加用户.
mysql> insert into mysql.user (host,user,password)
    -> values ('%','guest',password('guest'));

mysql> flush privileges;
利用开始加的用户登陆:
[root@rhel4 ~]# mysql -h 192.168.0.3 -u guest -p

让远程客户端连接到linux系统中的数据库，还要开启TCP3306端口:
/sbin/iptables -I INPUT -p tcp --dport 3306 -j ACCEPT

删除用户:
利用delete句语删除guest用户
mysql> delete from mysql.user where user='guest';

mysql> update mysql.user set password=password('123456')
    -> where user='guest';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

还有一种更改密码的方运河，要将用户guest的密码更改为guest
mysql> set password for guest@'%'=password('guest');
注:guest@'%'基本格式:用户名@客户端的域名，eg:netseek@'%.linux-lab.cn'.
或者试试与update配合.

grant 用户授权:
mysql> grant all on stu.* to netseek@'%' identified by '52netseek';
Query OK, 0 rows affected (0.00 sec)
# mysql -h www.linux-lab.cn -u netseek -p 这个登陆成功
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

grant all on stu.* to okman@'%.linux-lab.cn' identified by 'okman';
Query OK, 0 rows affected (0.00 sec)
mysql -h www.linux-lab.cn -uokman -p 这个连接为什么不能成功.

授予不同级别的权限：
要新建一个用户tom，他能从子网192.168.0.0中任何主机连接到数据库服务器，可以读取数据库
stu的内容，并且能修改表course中字段teacher的值.
grant select on stu.* to tom@'192.168.0.%' identified by '123456';
grant update(teacher) on stu.course to tom@'192.168.0.36'

 

授予管理权限:
>grant all on stu.* to admin@locahost identified by '52netseek'
->with grant option;

>show grants for admin@lochost. 来检测该用户授权是否正确.

收回权限:
revoke create,drop on stu.* from admin@localhost;
revoke grant option on stu.* from admin@localhost;