常用MySQL命令整理-白红宇

安装

创建服务

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;