mysql简单操作(实时更新)

mac安装启动mysql

1. brew install mysql
2. mysql.server start
3. /usr/local/Cellar/mysql/5.6.10/support-files/mysql.server start
4. mysql.server stop
5. /usr/local/Cellar/mysql/5.6.10/support-files/mysql.server stop

 

从表中删除某条记录：

delete from table_name where xx=xxxx;

创建数据库(注意不同系统对大小写的敏感性)：

create database xxx;

 

查看数据库列表

show databases;

 

删除数据库

drop database xxx;

如果数据库不确定是否存在则使用

drop database xxx if xxx;

 

查看表结构

desc xxx;

show columns from table_name;

 

删除表

drop table xxx

 

查看mysql端口号(默认是3306)

show global variables like 'port';

 

修改端口号

vi /etc/mysql/my.cnf

 

查看tables

show tables;

 

创建table

CREATE TABLE IF NOT EXISTS runoob_tbl(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT, `runoob_title` VARCHAR(100) NOT NULL, `runoob_author` VARCHAR(40) NOT NULL, `submission_date` DATE, PRIMARY KEY ( `runoob_id` ) )ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

 

创建新用户

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP

-> ON db_name.*

-> TO 'user_name'@'localhost'

-> IDENTIFIED BY 'abc123';

显示表的结构

show columns from table_name

 

显示索引

show index from table_name

 

显示所有表的信息

show table status from db_name

 

数据类型的介绍

[link](http://www.runoob.com/mysql/mysql-data-types.html)

 

插入数据

insert into table_name (field1, field2, field3) values (value1, value2, value3)

查询

select * from table_name1, table_name2 where xx (and or) [offset M] [limit N]

更新

update table_name set field1=value1, field2=value2 where xxx

删除

delete from table_name where xxx

LIKE

select xxx from xxx where xxx like xxx

 

排序

select xxx from tb_name where xx order by desc/asc;

Group

select column_name, function(column_name)
from table_name
where column_name operator value
group by column_name;