MySQL基本语法1001无标题_Mysql基础语法

mysql

SELECT VERSION(), CURRENT_DATE, now();

select user();

create database pets;

show databases;

use pets;

CREATE TABLE cats

(

id INT unsigned NOT NULL AUTO_INCREMENT, # Unique ID for the record

name VARCHAR(150) NOT NULL, # Name of the cat

owner VARCHAR(150) NOT NULL, # Owner of the cat

birth DATE NOT NULL, # Birthday of the cat

PRIMARY KEY (id) # Make the id the primary key

);

show tables;

describe cats; desc

INSERT INTO cats ( name, owner, birth) VALUES

( 'Sandy', 'Lennon', '2015-01-03' ),

( 'Cookie', 'Casey', '2013-11-13' ),

( 'Charlie', 'River', '2016-05-21' );

SELECT * FROM cats;

增加/删除/修改列：

alter table cats add gender char(1) [after name];

alter table cats drop gender;

alter table cats change name name_new char(30);

alter table cats modify name char(10);

alter table cats alter age set default 10;

alter table cats alter age drop default;

alter table cats rename to cats_t;

查看用户权限：

SHOW GRANTS FOR 'admin'@'localhost';

日期计算

http://dev.mysql.com/doc/refman/5.7/en/date-calculations.html

自定义变量

mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;

mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;

自增列：

CREATE TABLE animals (

id MEDIUMINT NOT NULL AUTO_INCREMENT,

name CHAR(30) NOT NULL,

PRIMARY KEY (id)

);

INSERT INTO animals (name) VALUES ('dog'),('cat'),('penguin'),('lax'),('whale'),('ostrich');

更新列：

update user set age =age+1 where id=1;