Date: 20140115
Auth: Jin参考:http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#select一:数据库操作1:显示数据库mysql> show databases;2:创建数据库mysql> create database sky;mysql> CREATE DATABASE sky CHARACTER SET utf8 COLLATE utf8_general_ci;3:选择数据库mysql> use sky;4: 查看当前数据mysql> select database(); 5:删除数据库mysql> drop database sky;6、修改数据名 修改数据库目录,貌似不是一个好方法创建一个新库,然后dump管道导入新库,这个也是整库复制的方法ALTER {DATABASE | SCHEMA} [db_name]ALTER DATABASE用于更改数据库的全局特性。这些特性储存在数据库目录中的db.opt文件中CHARACTER SET子句用于更改默认的数据库字符集。COLLATE子句用于更改默认的数据库校对集(COLLATE)mysql> alter database dbtest CHARACTER SET utf8 COLLATE utf8_general_ci; Query OK, 1 row affected (0.01 sec)字符集介绍http://blog.sina.com.cn/s/blog_9707fac301016wxm.htmlmysql> show variables like '%character%';+--------------------------+----------------------------+| Variable_name | Value |+--------------------------+----------------------------+| character_set_client | latin1 || character_set_connection | latin1 || character_set_database | utf8 || character_set_filesystem | binary || character_set_results | latin1 || character_set_server | latin1 || character_set_system | utf8 || character_sets_dir | /usr/share/mysql/charsets/ |+--------------------------+----------------------------+8 rows in set (0.00 sec)二:表结构操作
<一>基本表操作1:显示数据表,需要先选择数据库mysql> use mysql;mysql> show tables;2:创建数据表
mysql> use diege;create table 表名 (字段1,字段2,…字段n,[表级约束] [Type|Engine=表类型|存储引擎]其中字段格式为:字段名 字段类型 [字段约束]create table 表名 (字段名1 字段类型1 [字段约束]1,字段2,…字段n,[表级约束] [Type|Engine=表类型|存储引擎]mysql> create table tb_name(-> id int(6) not null auto_increment,-> uname char(15) not null,-> sex bit default 1,-> birthday date not null default '1970-01-01',-> email varchar(20) default null,-> comment varchar(100),-> primary key(id),-> unique(uname)-> )ENGINE=Innodb default charset=utf8;
3 查看表结构
mysql> describe tb_name;mysql> desc tb_name;4:复制表
mysql> create table new_tb_name like tb_name;5:根据已存在表的字段产生新表
SELECT vale1, value2 into Table2 from Table1要求目标表Table2不存在,因为在插入时会自动创建表Table2,并将Table1中指定字段数据复制到Table2中。Mysql不支持。SQL SERVER支持MYSQL可用的方法没有指定列mysql> create table temp_diege select * from temp_username;复制指定列的数据mysql> create table temp_diege select username,ctime from logmysql> create table testproduct select productId,pname from Product;Query OK, 9 rows affected (5.00 sec)Records: 9 Duplicates: 0 Warnings: 0注意只复制,字段和数据,不会复制索引比较:
Insert into Table2(field1,field2,...) select value1,value2,... from Table1要求目标表Table2必须存在,由于目标表Table2已经存在,所以我们除了插入源表Table1的字段外,还可以插入常量。6:删除表格
mysql> drop table new_tb_name;<二>修改表操作
字段操作包括:add,drop,change,modify,表操作rename(更改表名)1. 改变表的名字mysql> alter table tb_name1 rename tb_name2;2. 增加列
alter table tb_name add col_name typemysql> alter table tb_name add age smallint;好像没办法同时添加两列增加列并设置默认值mysql> alter table tb_name add age smallint default 0;增加列并设置为索引mysql> alter table tb_name add age smallint not null primary key;mysql> alter table tb_name add age smallint not null unique;3. 删除列
alter table tbl_name drop col_namemysql> alter table tb_name drop age;4. 改变列名字
alter table tbl_name change old_col_name col_namemysql> alter table tb_name change nianning age; ERROR 1064 (42000): You have an error in your SQL syntax; mysql> alter table tb_name change nianning age int(4);必须要指定字段类型,原来什么字段就是什么字段即可5. 改变列属性
方法1:alter table tbl_name modify col_name typemysql> alter table tb_name modify age int(2);从smallint修改为int(2)方法2alter table tbl_name change old_col_name col_name typemysql> alter table tb_name change age age smallint;从int(2)修改为smallint改变列名的同时改变属性
mysql> alter table tb30 change uname username varchar(30);mysql> alter table tb_name change age nianning int(4);6、改变列的属性,为字段添加默认值
字段已存在,不改变字段的名字和类型,只增加默认值mysql> alter table tb_name modify age int(4) default 18;必须要指定字段的类型<三>修改表添加和删除索引
(一)添加新字段为为索引也就是添加新字段,只不过约束为是索引主键索引mysql> alter table tb_name add uid int(11) not null primary key;唯一索引mysql> alter table tb1 add uname varchar(25) not null unique; 全文索引mysql> alter table tb2 add fulltext(email);ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexesinnodb不支持全文索引普通索引不知道怎么直接加,只有新加列再修改为普通索引mysql> alter table tb1 add email varchar(50) not null; mysql> alter table tb1 add index index_email(email);多列索引-联合索引
先添加两列,然后两列组成联合索引mysql> alter table tb1 add username varchar(50) not null;mysql> alter table tb1 add uuid int(11) not null; mysql> alter table tb1 add index un_index(uuid,username);(二)修改原来的字段为索引
改变列的属性,将现有字段修改为索引字段已存在,不改变字段的名字和类型,只是修改该字段为索引PRIMARY KEY 主键索引-只可以添加一个,Key_name为PRIMARYmysql>ALTER TABLE `table_name`add PRIMARY KEY( `column` )mysql> alter table tb1 add PRIMARY KEY(id); UNIQUE 唯一索引-可以添加多个, Key_name为字段名,同一个子段都可以添加多次mysql>ALTER TABLE `table_name`add UNIQUE( `column` )mysql> alter table tb1 add UNIQUE(uname);FULLTEXT 全文索引 InnoDB 不支持全文索引mysql>ALTER TABLE `table_name` add FULLTEXT( `column` )mysql> alter table tb1 add FULLTEXT(comment);ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexesINDEX 普通索引mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )mysql> alter table tb1 add index index_email(email);多列索引-联合索引 联合索引本质上还是普通索引mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )mysql> alter table tb1 add index index_union(id,uname);注意:字段不用引号括起来(三)查看索引
mysql> show index from tblname;mysql> show keys from tblname;mysql> show keys from tb1;+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------ +------------+---------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------ +------------+---------+| tb1 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | || tb1 | 0 | uname | 1 | uname | A | 0 | NULL | NULL | | BTREE | || tb1 | 1 | index_email | 1 | email | A | 0 | NULL | NULL | | BTREE | || tb1 | 1 | index_union | 1 | id | A | 0 | NULL | NULL | | BTREE | || tb1 | 1 | index_union | 2 | uname | A | 0 | NULL | NULL | | BTREE | |+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------ +------------+---------+· Table表的名称。· Non_unique如果MySQL索引不能包括重复词,则为0。如果可以,则为1。· Key_name索引的名称。#普通索引才可以指定列名称· Seq_in_index 索引中的列序列号,从1开始。· Column_name列名称。· Collation列以什么方式存储在索引中。在MySQL中,有值‘A’(升序)或NULL(无分类)。· CardinalityMySQL索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL使用该索引的机会就越大。· Sub_part如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为NULL。· Packed指示关键字如何被压缩。如果没有被压缩,则为NULL。· Null如果列含有NULL,则含有YES。如果没有,则该列含有NO。· Index_type用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)。· Comment(四)修改表删除索引
ALTER TABLE 'table_name' DROP INDEX index_name;普通索引mysql> alter table tb1 drop index index_email;唯一索引【唯一索引也像普通索引一样删除】mysql> alter table tb1 drop index uname;主健索引mysql> alter table tb1 drop PRIMARY KEY;【注意】:如果主键索引是自增列是无法删除三:处理表数据
insert,delete,update
准备演示的表
mysql> create table users (-> id int(6) not null auto_increment,-> uname varchar(25) not null,-> sex bit default 0,-> birthday date default '1970-1-1',-> email char(35),-> department varchar(50),-> comment varchar(200) ,-> PRIMARY KEY(id),-> UNIQUE(uname)-> )ENGINE=Innodb DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.04 sec)mysql> describe users;+------------+--------------+------+-----+------------+----------------+| Field | Type | Null | Key | Default | Extra |+------------+--------------+------+-----+------------+----------------+| id | int(6) | NO | PRI | NULL | auto_increment || uname | varchar(25) | NO | UNI | NULL | || sex | bit(1) | YES | | b'0' | || birthday | date | YES | | 1970-01-01 | || email | char(35) | YES | | NULL | || department | varchar(50) | YES | | NULL | || comment | varchar(200) | YES | | NULL | |+------------+--------------+------+-----+------------+----------------+7 rows in set (0.00 sec)
1、插入记录 insert
Insert into 表名 (字段1,字段2,…) values (字段1的值,字段2的值,…);关键字value或者values都可以插入必须字段,有默认值和自增列字段外的所有字段
mysql> insert into users (uname,email,department,comment) value ('diege','diege@foxmail.com','tech','a good boy');mysql> select * from users;+----+-------+------+------------+-------------------+------------+------------+| id | uname | sex | birthday | email | department | comment |+----+-------+------+------------+-------------------+------------+------------+| 1 | diege | | 1970-01-01 | diege@foxmail.com | tech | a good boy |+----+-------+------+------------+-------------------+------------+------------+插入所有字段,插入所有字段就没有必要列出字段了
mysql> insert into users values (2,'lily',1,'1990-01-01','lily@foxmail.com','product','a good work');mysql> select * from users where uname='lily';+----+-------+------+------------+------------------+------------+-------------+| id | uname | sex | birthday | email | department | comment |+----+-------+------+------------+------------------+------------+-------------+| 2 | lily | | 1990-01-01 | lily@foxmail.com | product | a good work |+----+-------+------+------------+------------------+------------+-------------+1 row in set (0.00 sec一个insert插入多条记录
mysql> insert into users (uname,email,department,comment) value ('tom','tom@foxmail.com','tech','a good worker'), ('sum','sum@foxmail.com','product','a good boy')2、删除记录 delete 和truncate
(1)清空表mysql> delete from table_name;mysql> truncate table_name;两者区别truncate 相当于drop table table_nam,然后再create table_name 速度快delete from table_name; 是一条条记录删除(2)根据条件删除一般不会delete from table_name 删除数据,都会根据条件删除,所以要根据where子句约束删除的范围mysql> delete from users where uname='lily';mysql> delete from users where uname like 'lily%';mysql> delete from users where id > 5; mysql> delete from users where uname like 'tom%' and department='tech';(3)同时删除多表记录两张表:Product表和ProductPrice表。前者存在Product的基本信息,后者存在Product的价格。mysql> create table Product (-> productId intdesign(6) not null auto_increment,-> pname varchar(25) not null,-> created date default '2000-1-1',-> PRIMARY KEY(productId),-> UNIQUE(pname)-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;mysql> create table ProductPrice (-> productId int(6) not null,-> price int not null,-> PRIMARY KEY(productId)-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入测试数据
mysql> insert into Product(pname,created) values ('hat1','2000-11-25'),('hat2','2003-11-25'),('hat3','2008-06-25'), ('hat4','2010-08-08');mysql> insert into Product(pname,created) values ('T-Shirts1','2004-06-15'),('T-Shirts2','2002-11-10'),('T-Shirts3','2000-03 -19'),('T-Shirts4','2013-07-15'),('T-Shirts5','2009-04-05')mysql> select * from Product;+-----------+-----------+------------+| productId | pname | created |+-----------+-----------+------------+| 1 | hat1 | 2000-11-25 || 2 | hat2 | 2003-11-25 || 3 | hat3 | 2008-06-25 || 4 | hat4 | 2010-08-08 || 15 | T-Shirts1 | 2004-06-15 || 16 | T-Shirts2 | 2002-11-10 || 17 | T-Shirts3 | 2000-03-19 || 18 | T-Shirts4 | 2013-07-15 || 19 | T-Shirts5 | 2009-04-05 |+-----------+-----------+------------+mysql> insert ProductPrice values (1,100),(2,88),(3,150),(4,218),(15,190),(16,168),(17,218),(18,298),(19,398);注意 这里的id没有外键约束备份两份数据mysql> create table ProductBak select * from Product; mysql> create table ProductPriceBak select * from ProductPrice;第一种跨表删除的方式是不用join,在delete时指定用半角逗号分隔多个表来删除,如下sql语句:
mysql> select a.*,b.* from Product as a,ProductPrice as b where a.productId=b.productId and a.created < '2004-01-01'; +-----------+-----------+------------+-----------+-------+| productId | pname | created | productId | price |+-----------+-----------+------------+-----------+-------+| 1 | hat1 | 2000-11-25 | 1 | 100 || 2 | hat2 | 2003-11-25 | 2 | 88 || 16 | T-Shirts2 | 2002-11-10 | 16 | 168 || 17 | T-Shirts3 | 2000-03-19 | 17 | 218 |+-----------+-----------+------------+-----------+-------+mysql> select p.*, pp.* FROM Product p, ProductPrice pp WHERE p.ProductId = pp.productId AND p.created < '2004-01-01'; +-----------+-----------+------------+-----------+-------+| productId | pname | created | productId | price |+-----------+-----------+------------+-----------+-------+| 1 | hat1 | 2000-11-25 | 1 | 100 || 2 | hat2 | 2003-11-25 | 2 | 88 || 16 | T-Shirts2 | 2002-11-10 | 16 | 168 || 17 | T-Shirts3 | 2000-03-19 | 17 | 218 |+-----------+-----------+------------+-----------+-------+4 rows in set (0.01 sec)删除操作
DELETE p.*, pp.* FROM product p, productPrice pp WHERE p.productId = pp.productId AND p.created < '2004-01-01'
两张表都删除
mysql> delete a.*,b.* from Product as a,ProductPrice as b where a.productId=b.productId and a.created < '2004-01-01';
确认
mysql> select a.*,b.* from Product as a,ProductPrice as b where a.productId=b.productId and a.created < '2004-01-01'; Empty set (0.00 sec)两个表个删除4条只删除其中一张表用 p.created > '2004-01-01'; 做条件mysql> select a.*,b.* from Product as a,ProductPrice as b WHERE a.ProductId = b.productId AND a.created > '2004-01-01';+-----------+-----------+------------+-----------+-------+| productId | pname | created | productId | price |+-----------+-----------+------------+-----------+-------+| 3 | hat3 | 2008-06-25 | 3 | 150 || 4 | hat4 | 2010-08-08 | 4 | 218 || 15 | T-Shirts1 | 2004-06-15 | 15 | 190 || 18 | T-Shirts4 | 2013-07-15 | 18 | 298 || 19 | T-Shirts5 | 2009-04-05 | 19 | 398 |+-----------+-----------+------------+-----------+-------+上面如果除去一个相同的字段productId则为自然连接已经删除ProductPrice以为例mysql> delete b.* from Product as a,ProductPrice as b WHERE a.ProductId = b.productId AND a.created > '2004-01-01';
查询ProductPrice表
mysql> select * from ProductPrice;Empty set (0.00 sec)mysql> select * from Product;+-----------+-----------+------------+| productId | pname | created |+-----------+-----------+------------+| 3 | hat3 | 2008-06-25 || 4 | hat4 | 2010-08-08 || 15 | T-Shirts1 | 2004-06-15 || 18 | T-Shirts4 | 2013-07-15 || 19 | T-Shirts5 | 2009-04-05 |+-----------+-----------+------------+5 rows in set (0.00 sec) 第二种跨表删除的方式是使用inner join在join中指定两表之间的关联关系,如下sql语句: mysql> select a.*,b.* from Product as a INNER JOIN ProductPrice as b ON a.productID = b.productID WHERE a.created < '2004-01 -01'; +-----------+-----------+------------+-----------+-------+| productId | pname | created | productId | price |+-----------+-----------+------------+-----------+-------+| 1 | hat1 | 2000-11-25 | 1 | 100 || 2 | hat2 | 2003-11-25 | 2 | 88 || 6 | T-Shirts2 | 2002-11-10 | 6 | 168 || 7 | T-Shirts3 | 2000-03-19 | 7 | 218 |+-----------+-----------+------------+-----------+-------+4 rows in set (0.00 sec)mysql> select p.*, pp.* FROM Product p INNER JOIN ProductPrice pp ON p.productId = pp.productId WHERE p.created < '2004 -01-01'; +-----------+-----------+------------+-----------+-------+| productId | pname | created | productId | price |+-----------+-----------+------------+-----------+-------+| 1 | hat1 | 2000-11-25 | 1 | 100 || 2 | hat2 | 2003-11-25 | 2 | 88 || 6 | T-Shirts2 | 2002-11-10 | 6 | 168 || 7 | T-Shirts3 | 2000-03-19 | 7 | 218 |+-----------+-----------+------------+-----------+-------+INNER连接关键字 A INNER JOIN B ON A.id=B.idProduct as a INNER JOIN ProductPrice as b ON a.productID = b.productIDDELETE p.*, pp.* FROM product p INNER JOIN productPrice pp ON p.productId = pp.productId WHERE p.created < '2004-01-01'mysql> delete a.*,b.* from Product as a INNER JOIN ProductPrice as b ON a.productID = b.productID WHERE a.created < '2004- 01-01';
确认
mysql> select a.*,b.* from Product as a INNER JOIN ProductPrice as b ON a.productID = b.productID WHERE a.created < '2004- 01-01'; Empty set (0.01 sec)只删除一个表这次我删除物品表格mysql> select a.*,b.* from Product as a INNER JOIN ProductPrice as b ON a.productID = b.productID WHERE a.created > '2004-01 -01'; +-----------+-----------+------------+-----------+-------+| productId | pname | created | productId | price |+-----------+-----------+------------+-----------+-------+| 3 | hat3 | 2008-06-25 | 3 | 150 || 4 | hat4 | 2010-08-08 | 4 | 218 || 5 | T-Shirts1 | 2004-06-15 | 5 | 190 || 8 | T-Shirts4 | 2013-07-15 | 8 | 298 || 9 | T-Shirts5 | 2009-04-05 | 9 | 398 |+-----------+-----------+------------+-----------+-------+mysql> delete b.* from Product as a INNER JOIN ProductPrice as b ON a.productID = b.productID WHERE a.created > '2004-01-01';
确认
mysql> select * from ProductPrice; Empty set (0.00 sec)mysql> select * from Product;+-----------+-----------+------------+| productId | pname | created |+-----------+-----------+------------+| 3 | hat3 | 2008-06-25 || 4 | hat4 | 2010-08-08 || 5 | T-Shirts1 | 2004-06-15 || 8 | T-Shirts4 | 2013-07-15 || 9 | T-Shirts5 | 2009-04-05 |+-----------+-----------+------------+5 rows in set (0.00 sec)注意:跨表删除不必删除所有表的数据,上面的sql语句表会同时删除Product和ProductPrice两张表中的数据,但是你可以指定 DELETE
product.*从而只删除product表中的记录,而不处理ProductPrice表中的记录。跨表删除也可以使用left join,例如我们要删除所有在ProductPrice表中没有记录的Product表记录。如下sql语句:
DELETE p.* FROM product p LEFT JOIN productPrice pp ON p.productId = pp.productId WHERE pp.productId is null3 修改记录 update
update tb_name set 字段=值1,子段2=值2 where 条件1)修改单个记录mysql> update users set uname='hellen' where id=2;2)修改一个表的多条记录
mysql> update users set sex=1 where id>2;Query OK, 3 rows affected (0.37 sec)还是条件控制,条件控制一个还是多个,控制需要修改的记录范围3)跨表修改多条记录
http://www.jb51.net/article/32648.htm假定我们有两张表,一张表为Product表存放产品信息,其中有产品价格列Price;另外一张表是ProductPrice表,我们要将ProductPrice表中的价格字段Price更新为Price表中价格字段的80%。 准备表mysql> describe Product;+-----------+-------------+------+-----+------------+----------------+| Field | Type | Null | Key | Default | Extra |+-----------+-------------+------+-----+------------+----------------+| productId | int(6) | NO | PRI | NULL | auto_increment || pname | varchar(25) | NO | UNI | NULL | || created | date | YES | | 2000-01-01 | |+-----------+-------------+------+-----+------------+----------------+3 rows in set (0.00 sec)mysql> describe ProductPrice;
+-----------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------+---------+------+-----+---------+-------+| productId | int(6) | NO | PRI | NULL | || price | int(11) | NO | | NULL | |+-----------+---------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> alter table Product add price int(11) default 200;mysql> update Product as a,ProductPrice as b set a.price=b.price where a.productId=b.productId;Query OK, 9 rows affected (0.01 sec)Rows matched: 9 Changed: 9 Warnings: 0上面仅仅是准备数据,可以无视mysql> select * from Product;+-----------+-----------+------------+-------+| productId | pname | created | price |+-----------+-----------+------------+-------+| 1 | hat1 | 2000-11-25 | 100 || 2 | hat2 | 2003-11-25 | 88 || 3 | hat3 | 2008-06-25 | 150 || 4 | hat4 | 2010-08-08 | 218 || 15 | T-Shirts1 | 2004-06-15 | 190 || 16 | T-Shirts2 | 2002-11-10 | 168 || 17 | T-Shirts3 | 2000-03-19 | 218 || 18 | T-Shirts4 | 2013-07-15 | 298 || 19 | T-Shirts5 | 2009-04-05 | 398 |+-----------+-----------+------------+-------+9 rows in set (0.00 sec)假设Product中price 是总公司的产品定价,现在网络商务部要搞双11活到,所有商品8折,现在要修改对外显示的ProductPrice的表方法1,类自然连接mysql> update Product as a,ProductPrice as b set b.price=a.price*0.8 where a.productId=b.productId; Query OK, 9 rows affected (0.01 sec)Rows matched: 9 Changed: 9 Warnings: 0
确认
mysql> select * from ProductPrice;+-----------+-------+| productId | price |+-----------+-------+| 1 | 80 || 2 | 70 || 3 | 120 || 4 | 174 || 15 | 152 || 16 | 134 || 17 | 174 || 18 | 238 || 19 | 318 |+-----------+-------+9 rows in set (0.00 sec)方法2 内连接 INNER JOIN on setmysql> update Product as a INNER JOIN ProductPrice as b ON a.productId=b.productId set b.price=a.price*0.6 where a.created < '2004-01-01'; Query OK, 0 rows affected (0.01 sec)Rows matched: 4 Changed: 0 Warnings: 0
方法3 左外连接 left outer join #未实践
UPDATE product p LEFT JOIN productPrice pp ON p.productId = pp.productId SET p.deleted = 1 WHERE pp.productId IS null
同时修改两个表
UPDATE product p INNER JOIN productPrice pp ON p.productId = pp.productId SET pp.price = pp.price * 0.8, p.dateUpdate = CURDATE() WHERE p.dateCreated < '2004-01-01';