mysql的使用

1.显示全部数据库

SHOW DATABASES;

2.选择数据库

USE databasename：

3.显示当前数据库全部表

SHOW TABLES;

4.显示数据库某个表结构

DESC tablename;

SHOW COLUMNS FROM tablename;

1.创建数据库

CREATE DATABASE databasename;

2.删除数据库

DROP DATABASE databasename;

3.备份数据库

mysqldump -uroot -ppassword databasename > backupfile.sql

4.恢复数据库

mysql -uroot -ppassword databasename < backupfile.sql

1.创建表 / 删除表

创建表:

CREATE TABLE tablename(fname VARCHAR(20) UNIQUE NOT NULL,fage INT DEFAULT 30 CHECK(fage >0),PRIMARY KEY(fname));

CREATE TABLE tablename1(fname VARCHAR(20),fcouple VARCHAR(20),FOREIGN KEY(fname) REFERENCES tablename(fname));

删除表:

DROP TABLE tablename;

2.添加字段 / 修改字段 / 删除字段

增加字段:

ALTER TABLE tablename ADD telephonename INT;

ALTER TABLE tablename ADD PRIMARY KEY(fname,fage);

修改字段:

ALTER TABLE tablename MODIFY telephonename INT DEFAULT "110";

删除字段:

ALTER TABLE tablename DROP telephonename;

3.添加约束 / 删除约束

添加约束:

ALTER TABLE tablename ADD CONSTRAINT nuique1 UNIQUE(fname,fage);

ALTER TABLE tablename ADD CONSTRAINT primary1 PRIMARY KEY(fname,fage);

ALTER TABLE tablename ADD CONSTRAINT foreignkey1 FOREIGN KEY(fcouple) REFERENCES tablename1(fname);

CREATE INDEX findex ON tablename(fname,fage);

ALTER TABLE tablename ADD CONSTRAINT check1 CHECK(fage>0);

删除约束:

ALTER TABLE tablename DROP CONSTRAINT nuiq1;

ALTER TABLE tablename DROP PRIMARY KEY(fname,fage);

ALTER TABLE tablename DROP FOREIGN KEY fcouple;

ALTER TABLE tablename DROP INDEX findex;

4.添加记录 / 更改记录 / 删除记录

添加记录:

INSERT INTO tablename(fname,fage) VALUES("myname",25);

更改记录:

UPDATE tablename SET fname="yourname",fage=100;

UPDATE tablename SET fname="yourname"  WHERE fage=100;

删除记录:

DELETE FROM tablename WHERE fage = 100;

5.数据检索

简单检索:

SELECT fname,fage FROM tablename WHERE fage>25;

排序检索:

SELECT * FROM tablename ORDER BY fage ASC/DESC;

通配符检索:

SELECT * FROM tablename WHERE fname LIKE "_%name";

抑制数据重复检索:

SELECT DISTINCT fage FROM tablename;

分组检索:

SELECT fage,COUNT(*) AS CountOfThisAge FROM tablename GROUP BY fage HAVING COUNT(*)>1;

联合结果集检索:

SELECT fname,fage FROM tablename UNION SELECT fname,fage FROM tablename1;

SELECT fname,fage FROM tablename UNION ALL SELECT fname,fage FROM tablename1;