mysql基本语句学习归纳

启动/停止：/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行