MySQl语句

1.查询
 
(1)查询存在的数据库
 
  show databases;
 
  选择所要的某个数据库  use databasename;
 
(2)查询当前数据库存在的所有的表
 
  show tables;
 
(3)查询表结构
 
  describe tablename;
 
(4)查询表格列的属性
 
  show columns from tableName；
 
(5)查询记录
 
  select name from tablename where id=xxx;
 
(6)查询当前时间
 
  select now();
 
  select current_time;
 
(7)查询当前日期
 
  select current_date;
 
(8)查询当前用户
 
  select user();
 
(9)查询数据库版本
 
  select version();
 
(10)查询当前使用的数据库
 
  select database();
 
(11)查询当前服务器支持哪个存储引擎
 
  show engines;

(12)查看建表的sql语句

show create table table_name\g;  //大小写均可

 

2.创建
 
(1)创建数据库
 
  create database databasename;
 
  create database DATABASE_NAME default character set utf8;
 
(2)创建一张表
 
  create table tablename (name VARCHAR(20), sex CHAR(1));
 
  create table if not exists students(……);           //创建表是先判断表是否存在
 
(3)创建临时表：(建立临时表linshi)
 
  create temporary table linshi(name varchar(10));
 
(4)从已经有的表table1中复制表的结构到表table2
 
  create table table2 select * from table1 where 1<>1; //只复制表结构
 
  create table table2 select * from table1;          //复制表结构和表中的数据
 
(5)往表中links加入记录
 
  insert into links(name,url) values('xiaoxiaozi','http://www.xiaoxiaozi.com');
 
  insert into links set name='xiaoxiaozi',url='http://www.xiaoxiaozi.com';

3.修改
 
(1)对表重新命名
 
  alter table tablename1 rename as tablename2;
 
(2)修改列的类型
 
  alter table tablename modify id int unsigned;     //修改列id的类型为int unsigned
 
  alter table tablename change id sid int unsigned;//修改列id的名字为sid，而且把属性修改为int unsigned

 

(3)在一个已经建好的表中添加一列，可以用诸如：

alter table t1 add column addr varchar(20) not null;//这条语句会向已有的表t1中加入一列

 

alter table t1 add column addr varchar(20) not null after user1; //注意，上面这个命令的意思是说添加addr列到user1这一列后面。

 

alter table t1 add column addr varchar(20) not null first;//添加到第一列的话

 

(4)修改列名

 

alter table table_name change old_name new_name integer;

 

(5)建表时忘设主键，怎么弥补？

 

alter table pay_order change p_order p_order varchar(10) primary key;

 

(6)增加外键

 

alter table 表名 add foreign key(pubid) references pub_com(id) on delete restrict on update restrict;

 

(7)更新表中数据
 
  update tablename set sex='f' where name='john';

(7)更新表中数据一次更新多个
 
  update tablename set sex='f',gender='g' where name='john';

 

4.删除
 
(1)删除某个数据库
 
  drop database databasename;        //删除数据库前，没有提示
 
  mysqladmin drop databasename;   //删除数据库前，有提示
 
(2)删除某张表
 
  drop table tablename;
 
(3)清空某张表
 
  delete from tablename;
 
(4)删除符合条件的某些记录
 
  delete from tablename where id=xxx;
 
(5)删除授权：
 
  revoke all privileges on *.* from root@”%”;
 
  delete from user where user=”root” and host=”%”;
 
  flush privileges;             //刷新数据库

 

(6)删除外键：

 

ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol;

 

(7)删除列

 

alter table table_name drop column_name; //删除表列

 

5.备份
 
(1)备份数据库：(将数据库test备份)
 
  mysqldump -u root -p test>c:\test.txt
 
(2)备份表格：(备份test数据库下的mytable表格)
 
  mysqldump -u root -p test mytable>c:\test.txt
 
(3)将备份数据导入到数据库：(导回test数据库)
 
  mysql -u root -p test<c:\test.txt

 

6、应用案例：

 

CREATE TABLE person (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);
 
CREATE TABLE shirt (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
    PRIMARY KEY (id)
);

CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
                      price DECIMAL,
                     PRIMARY KEY(category, id)
) TYPE=INNODB;

 

CREATE TABLE customer (id INT NOT NULL,
                      PRIMARY KEY (id)
) TYPE=INNODB;

 

CREATE TABLE product_order (no INT NOT NULL  AUTO_INCREMENT,            product_category INT NOT NULL,
                           product_id INT NOT NULL,
                          customer_id INT NOT NULL,
                           PRIMARY KEY(no),
                           INDEX (product_category, product_id),
                           FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT,
                          INDEX (customer_id),
                           FOREIGN KEY (customer_id)
                           REFERENCES customer(id)
) TYPE=INNODB;