启动/停止:/etc/init.d/mysql start (stop)
service mysql start (stop)
登陆:mysql -u root -p 即可输入密码
安装时mysql未提示设置账户密码请见以下文章:
https://www.cnblogs.com/williamjie/p/11126486.html
建库:create database database_name;
建表:create table table name(col type(size),col2,type(size));
建索引:
Alter 创建
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);
Create index创建
CREATE INDEX index_name ON table_name (column_list);
CREATE UNIQUE INDEX index_name ON table_name (column_list);
删除索引
DROP INDEX index_name ON talbe_name;
ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE table_name DROP PRIMARY KEY;
查看索引
show index from tblname;
show keys from tblname;
表中插入数据
Insert into table_name(col1,col2) values(value1,”value2”);
查询数据,查看表格中所有数据
Select * from table_name;
查看指定数据
Select * from table_name where condition;
Update更新
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause];
Delete删除数据;
DELETE FROM table_name [WHERE Clause];
Union合并
SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions] UNION [ALL | DISTINCT] SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions];
例子:SELECT country FROM Websites UNION SELECT country FROM apps ORDER BY country;
排序order by
SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]
分组 group by
SELECT column_name, function(column_name) FROM table_name
WHERE column_name operator value GROUP BY column_name;
Having
SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value
例子:select id,SUM(score) from table3 where id=1 or id=3 group by id having SUM(score)>100;
Limit
指定返回前行数
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
例子:
select * from table limit 5; --返回前5行
select * from table limit 0,5; --同上,返回前5行
select * from table limit 5,10; --返回6-15行