Mysql Operation

 

 

1 数据库
CREATE DATABASE mydatabase;	建立数据库
USE mydatabase;	使用数据库
DROP DATABASE mydatabase;	删除数据库
2 表
CREATE TABLE IF NOT EXISTS abcdef( id INT UNSIGNED AUTO_INCREMENT, title VARCHAR(100) NOT NULL, PRIMARY KEY (id) )ENGINE=INNODB DEFAULT CHARSET=utf8;	创建表
SHOW TABLES;	查看表
DESCRIBE abcdef;	查看表结构
RENAME TABLE abcdef TO new_abcdef;	表改名
DROP TABLE abcdef;	删除表
ALTER TABLE abcdef ADD height1 CHAR(30) NULL;	新增列、字段
ALTER TABLE abcdef MODIFY title VARCHAR(10) NULL;	修改列类型
ALTER TABLE abcdef CHANGE title title1 VARCHAR(10) NULL;	修改列名称title改为title1,后面可以修改列类型。
ALTER TABLE abcdef DROP height1;	删除列
ALERT TABLE tbl_data MODIFY id INT, DROP PRIMARY KEY;	去除主键
3 增删查改
INSERT INTO table_name SET name=""	增加
DELETE FROM category WHERE id=1;	删除
UPDATE person SET `name` = '111' WHERE id = 0;	更改
	查询
SELECT RIGHT(name,1) FROM category;	查询name字段的第一个字符
SELECT `name` FROM category WHERE id IN ( SELECT id FROM category_ WHERE name='category1' )	嵌套查询（比连接查询快）
SELECT category.*, category_.* FROM category,category_ WHERE category.id=category_.id;	连接查询
SELECT category.*,category_.* FROM category LEFT JOIN category_ ON category.id=category_.id;	左连接:where影响右表
SELECT category.*,category_.* FROM category RIGHT JOIN category_ ON category.id=category_.id;	右连接：where影响左表
SELECT * FROM category INNER JOIN category_ ON category.id=category_.id;	内连接：和连接查询相同
SELECT * FROM category WHERE CONCAT(data1,data3,data5, data7) LIKE '%somebody%'	模糊查询  全部匹配一次
BEGIN DECLARE i INT DEFAULT 2; WHILE i < 16 DO   INSERT INTO tbl_data(id, data1, data2, data3, data4, data5, data6, data7, data8) VALUES(i, "", "", "", "", "", "", "", "");   SET i=i+1; END WHILE ; commit;   END	函数查询   循环插入函数