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;
