Home > Database > Mysql Tutorial > body text

数据库操作_MySQL

WBOY
Release: 2016-06-01 13:34:14
Original
1215 people have browsed it

bitsCN.com

CREATE TABLE EMP1 AS SELECT * FROM EMP WHERE EMPNO=1

SELECT * INTO NEW_TABLE FROM EMP WHERE EMPNO=1   (IF NEW_TABLE IS EXIST ) 

 

 数据表基本操作

1.create database name; //创建数据库 (create 内部命令)

  mysql>create  database  数据库名;

2.use databasename; //选择数据库 (use内部命令)

  mysql>use  数据库名;

3.drop database name; //直接删除数据库,不提醒 (drop内部命令)

  mysql>drop  database  数据库名;

4.drop table name; // 直接删除表

  mysql>drop  table  表名;

5.create table name; //创建表

  mysql>create  table  表名;

      **前提是先mysql>use数据库名;

6.show databases;//显示所有数据库

  mysql>show  databases;

7.show tables; //显示数据库中的表 (show内部命令)

  mysql>show  tables;

      **提前是先mysql>use数据库名;

8.describe tablename; //显示表的详细描述

  mysql>describe 表名;

9.select version(),current_date; //显示当前mysql版本和当前日期

  mysql>select  version(), current_date;

  mysql>select  version(),

      ->current_date;

  **select 内部命令 

10.mysql>flush privileges ; //刷新数据库

11.mysql实现修改用户密码:

 11.1 update //修改

       shell>mysql -u root -p

     mysql> update mysql.user set password=PASSWORD(’新密码’) where User='root';

     mysql> flush privileges;

     mysql> quit

 11.2 mysqladmin//修改用户密码

     shell>mysqladmin -u root password new_password

 11.3 自己测试未通过

     shell>mysql -u root -p

    mysql>SET PASSWORD FOR root=PASSWORD("root");

12.创建帐号

  mysql> grant all(delete,updatae,create,drop,insert) privileges on *.* to 用户@localhost identified  by  '密码';

13.重命名表:

  mysql > alter table  表1  rename  表2; //把表t1重命名为t2

14.备份数据库

 14.1 导出整个数据库

          mysqldump -u 用户名 -p 数据库名 > 导出的文件名

        mysqldump -u root -p asb >d:/asb.sql

 

 14.2 导出一个表

    mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名

mysqldump -u root -p asb yg>d:/yg.sql

 

 14.3 导出一个数据库结构

mysqldump -u wcnc -p -d --add-drop-table smgp_apps_wcnc >d:/wcnc_db.sql

// (-d 没有数据 --add-drop-table 在每个create语句之前增加一个drop table)

  mysqldump mysqladmin外部命令

 

 14.4 导入数据库

  /*用source 命令

  */进入mysql数据库控制台,先建立数据库,use数据库

  mysql -u root -p

  mysql>create  数据库名;

  mysql>use 数据库名

  mydql>source d:/数据库名.sql;

 

 14.5 导入表

 // 进入数据库,执行导入操作

      mysql>use数据库名;

      mysql>source d:/表名.sql;

15.将表中记录清空:

  mysql>delete  from 表名;

15.1将表中记录清空:

  mysql>Truncate  tablename;

16.显示表中的记录:

  mysql>select  *  from 表名;

17.一个建库和建表以及插入数据的实例

  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','深圳一中','1976-10-10');

  insert into teacher values('','jack','深圳一中','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状态进入目录

mysqlin,然后键入以下命令:

mysql -uroot -p密码

如果成功,空出一行无任何显示;如有错误,会有提示。(以上命令已经调试,你

只要将//的注释去掉即可使用)。

将文本数据转到数据库中

 文本数据应符合的格式:字段数据之间用tab键隔开,null值用来代替。例:

3 rose 深圳二中 1976-10-10

4 mike 深圳一中 1975-12-23

 数据传入命令load data local infile "文件名" into table 表名;

注意:你最好将文件复制到mysqlin目录下,并且要先用use命令选表所在的库。

18.revoke all on database from user

//移除user用户在database数据库上的所有权限 (自己测试未通过)

 

19.连接mysql。

格式: mysql -h主机地址 -u用户名 -p用户密码

20.登录数据库

  mysql-uroot-p;

  mysql-uroot-pnewpassword;

  mysqlmydb-uroot-p;

  mysql mydb -uroot -pnewpassword;

  mydb数据库名

21.刚安装好的MySql包含一个含空密码的root帐户和一个匿名帐户,这是很大的安全隐患,对于一些重要的应用我们应将安全性尽可能提高,   在这里应把匿名帐户删除、root帐户设置密码,可用如下命令进行:

  mysql>use mysql;

 mysql>delete from User where User="";

 mysql>update User set Password=PASSWORD('newpassword') where User='root';

22.新增用户

  格式:grant select on 数据库.* to 用户名@登录主机 identified by "密码"

例1、增加一个用户test1密码为abc,让他可以在任何主机上登录,并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入MySQL,然后键入以下命令:

mysql>grant select,insert,update,delete on *.* to test1@"%" Identified by "abc";

但例1增加的用户是十分危险的,你想如某个人知道test1的密码,那么他就可以在internet上的任何一台电脑上登录你的MySQL数据库并对你的数据可以为所欲为了,解决办法见例2。

例2、增加一个用户test2密码为abc,让他只可以在localhost上登录,并可以对数据库mydb进行查询、插入、修改、删除的操作(localhost指本地主机,即MySQL数据库所在的那台主机),这样用户即使用知道test2的密码,他也无法从internet上直接访问数据库,只能通过MySQL主机上的web页来访问。

mysql>grant select,insert,update,delete on mydb.* to test2@localhost identified by "abc";

如果你不想test2有密码,可以再打一个命令将密码消掉。

mysql>grant select,insert,update,delete on mydb.* to test2@localhost identified by "";

下面我们来看看MySQL中有关数据库方面的操作。注意:你必须首先登录到MySQL中,以下操作都是在MySQL的提示符下进行的,而且每个命令以分号结束。 

在进行开发和实际应用中,用户不应该只用root用户进行连接数据库,虽然使用root用户进行测试时很方便,但会给系统带来重大安全隐患,也不利于管理技术的提高。我们给一个应用中使用的用户赋予最恰当的数据库权限。如一个只进行数据插入的用户不应赋予其删除数据的权限。MySql的用户管理是通过User表来实现的,添加新用户常用的方法有两个,一是在User表插入相应的数据行,同时设置相应的权限;二是通过GRANT命令创建具有某种权限的用户。其中GRANT的常用用法如下:

  mysql>grant all on mydb.* to NewUserName@HostName identified by "password";

  mysql>grant usage on *.* to NewUserName@HostName identified by "password";

  mysql>grant select,insert,update on mydb.* to NewUserName@HostName identified by "password";

  mysql>grant update,delete on mydb.TestTable to NewUserName@HostName identified by "password";

23.对常用权限的解释:

   23.1 全局管理权限: 

  FILE:在MySQL服务器上读写文件。 

  PROCESS:显示或杀死属于其它用户的服务线程。 

  RELOAD:重载访问控制表,刷新日志等。 

  SHUTDOWN:关闭MySQL服务。

 23.2 数据库/数据表/数据列权限: 

  Alter:修改已存在的数据表(例如增加/删除列)和索引。 

  Create:建立新的数据库或数据表。 

  Delete:删除表的记录。 

  Drop:删除数据表或数据库。 

  INDEX:建立或删除索引。 

  Insert:增加表的记录。 

  Select:显示/搜索表的记录。 

  Update:修改表中已存在的记录。

 23.3 特别的权限: 

  ALL:允许做任何事(和root一样)。 

  USAGE:只允许登录--其它什么也不允许做。

 

最后给出在RedHat9.0下的MySql操作演示:

选用数据库的root用户登录

  [weiwen@weiwenlinux]$mysql -uroot -p

  Enterpassword:MyPassword

  mysql>create database mydb;

  QueryOK,1rowaffected(0.02sec)

  mysql>use mydb;

  Databasechanged

  mysql>create table TestTable(Idintaut_increment primary key,

  UserNamevarchar(16) not null,

  Address varchar(255));

  QueryOK,0rowsaffected(0.02sec)

  mysql>grant all on mydb.* to test@localhost identified by "test";

  QueryOK,0rowsaffected(0.01sec)

  mysql>quit

  Bye

  [weiwen@weiwenlinux]$mysqlmydb -utest -ptest

其中test.sql是用vi编辑好的SQL脚本,其内容为:

  Insert into TestTable(UserName,Address) values('Tom','shanghai');

  Insert into TestTable(UserName,Address) values('John','beijing');

  select * from TestTable;

运行已经编辑好的SQL脚本可以用source filename或.filename。

以上只是对新手的简单练习,要成为一个数据库好手,当以孜孜不倦地追求知识,不断地思考、尝试、再思考。

24.MySql常用命令总结 

 24.1 使用SHOW语句找出在服务器上当前存在什么数据库: 

  mysql>SHOW DATABASES; 

 24.2 创建一个数据库 

  mysql>Create  DATABASE  数据库名;

 24.3 选择你所创建的数据库 

  mysql>USE 数据库名;(按回车键出现Databasechanged时说明操作成功!) 

 24.4 查看现在的数据库中存在什么表 

  mysql>SHOW TABLES; 

 24.5 创建一个数据库表 

  mysql>Create  TABLE表名(name VARCHAR(20),sex CHAR(1)); 

 24.6 显示表的结构: 

  mysql>DESCRIBE 数据库名;

 24.7 往表中加入记录 

  mysql>insert  into 表名values("hyq","M"); 

 24.8 用文本方式将数据装入数据库表中(例如D:/mysql.txt) 

  mysql> LOAD DATA LOCAL INFILE "D:/mysql.txt" INTO TABLE表名; 

 24.9 导入.sql文件命令(例如D:/mysql.sql) 

  mysql>use  database; 

  mysql>source  d:/mysql.sql; 

 24.10 删除表 

  mysql>drop  TABLE  表名; 

 24.11 清空表 

  mysql>delete  from  表名; 

 24.12 更新表中数据 

  mysql>update  表名set  sex="f"  where  name='hyq';

 

 

25. --通过伪列rowid查找重复数据

 

select a.* from t_user a where a.rowid!= 

  (select max(rowid) as rid from t_user where a.grpid =grpid and a.uname=uname);

 

delete from t_user a where a.rowid!= 

  (select max(rowid) as rid from t_user where a.grpid =grpid and a.uname=uname);

 

26. 当表中有重复数据(所有字段相同)时,去重:

select distinct * from  表名;

 

27. 根据字段查询表中重复的数据(单一字段):

    select * from表名where 字段名 in (select字段名   from  表名 group by 字段名 having  count(字段名) > 1);

 

28: 删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录
       delete from 表名where 字段名  in(select  字段名   from 表名  group   by  字段名   having  count(字段名)> 1)
and rowid not in(select min(rowid) from   表名  group by字段名  having count(字段名)>1);

29:根据字段查表里面重复的多字段数据,并显示重复次数:

    Select 字段名1,字段名2,Count(*)From 表名Group By 字段名1,字段名2 HavingCount(*) > 1

30. 指定查询表中前N行数据:

    select * from表名limit  N;

31.指定查询表中第N行后的M行数据:

    select *from 表名 limit N,M;

32. 复制表结构及数据到新表
CREATE TABLE 新表 SELECT * FROM 旧表;

33. 只复制表结构到新表
CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2
或CREATE TABLE 新表 LIKE 旧表;

34. 1、复制表结构及数据到新表
CREATE TABLE 新表 SELECT * FROM 旧表
这种方法会将oldtable中所有的内容都拷贝过来,当然我们可以用delete from newtable;来删除。
不过这种方法的一个最不好的地方就是新表中没有了旧表的primary key、Extra(auto_increment)等属性。需要自己用"alter"添加,而且容易搞错。


2、只复制表结构到新表
CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2
或CREATE TABLE 新表  LIKE 旧表


3、复制旧表的数据到新表(假设两个表结构一样)
INSERT INTO 新表 SELECT * FROM 旧表


4、复制旧表的数据到新表(假设两个表结构不一样)
INSERT INTO 新表(字段1,字段2,.......) SELECT 字段1,字段2,...... FROM 旧表


5、可以将表1结构复制到表2
SELECT * INTO 表2 FROM 表1 WHERE1=2


6、可以将表1内容全部复制到表2
SELECT * INTO 表2 FROM 表1


7、show create table 旧表;
这样会将旧表的创建命令列出。我们只需要将该命令拷贝出来,更改table的名字,就可以建立一个完全一样的表


8、mysqldump
用mysqldump将表dump出来,改名字后再导回去或者直接在命令行中运行

35. 把时间更新成为当前系统时间

Update 表名set 时间字段=now();

 

bitsCN.com
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template