数据库操作_MySQL

bitsCN.com

ＣＲＥＡＴＥ　ＴＡＢＬＥ　ＥＭＰ１　ＡＳ　ＳＥＬＥＣＴ　＊　ＦＲＯＭ　ＥＭＰ　ＷＨＥＲＥ　ＥＭＰＮＯ＝１

ＳＥＬＥＣＴ　＊　ＩＮＴＯ　ＮＥＷ＿ＴＡＢＬＥ　ＦＲＯＭ　ＥＭＰ　ＷＨＥＲＥ　ＥＭＰＮＯ＝１　　　（ＩＦ　ＮＥＷ＿ＴＡＢＬＥ　ＩＳ　ＥＸＩＳＴ　）　

 

 数据表基本操作

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