安装
创建服务
mysqld --initialize --datadir=/Users/ezio/database/mysql/data
启动
mysqld_safe --datadir=/Users/ezio/database/mysql/data &
登录
mysql -uroot -p123456
show variables like '%dir%';
create user 'abc'@'%' identified by 'abc123';
新建数据库
CREATE DATABASE database-name;
选择数据库
USE database-name;
新建表
CREATE TABLE table-name (
name1 varchar(30),
name2 varchar(40)
);
删除表
DROP TABLE tablename;
更改表
ALTER TABLE table-name ADD name1 INT NOT NULL AUTO_INCREMENT FIRST,ADD PEIMARY KEY (name1)
ALTER TABLE table-name MODIFY c CHAR(10);
DROP COLUMN name1;
CHANGE COLUMN name1 name2 INT;
MODIFY COLUMN date DATETIME AFTER age / FIRST;
插入表数据
INSERT INTO table-name (name1,name2) VALUES (value1,value2)
ADD COLUMN name1;
删除表数据
DELETE FROM table-name WHERE name1='value1' or name2='value2';
DELETE from tablename WHERE id IN (1,2,3,4);
DELETE from tablename WHERE id BETWEEN 1 AND 254 AND id!=10;
查询表数据
SELECT name1 FROM table-name
排序
select * from dtuic_userprofile order by gmt_created DESC limit 1\G
更新表数据
update MyClass set name='Mary' where id=1;
描述表
DESCRIBE table-name;
查看索引
show index from dt_parser;
建立索引
alter table dt_parser add index name(status);
ALTER TABLE table_name ADD INDEX index_name (column_list) #增加普通索引
ALTER TABLE table_name ADD UNIQUE (column_list) #增加唯一索引
ALTER TABLE table_name ADD PRIMARY KEY (column_list) #增加主键索引
删除索引
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY
联合查询
inner join
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
left join (该语句会读取左边的数据表runoob_tbl的所有选取的字段数据，即便在右侧表tcount_tbl中没有对应的runoob_author字段值)
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
right join (该语句会读取右边的数据表 runoob_tbl 的所有选取的字段数据，即便在左侧表tcount_tbl中没有对应的runoob_author字段值)
SELECT b.runoob_id, b.runoob_author, a.runoob_count FROM tcount_tbl a RIGHT JOIN runoob_tbl b ON a.runoob_author = b.runoob_author;