• 数据库

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;


相关

最新