mysql 常见指令

shell> mysql -h host -u user -p      登录

shell> mysql -uroot -p    本地登录

mysql> SELECT VERSION(), CURRENT_DATE;  版本与日期

//建库

mysql> CREATE DATABASE menagerie;

//查询库

mysql> SHOW DATABASES;

//使用

mysql> USE menagerie

//建表

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),

    -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

//显示表

mysql> SHOW TABLES;

//插入

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),

    -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

//显示具体表

mysql> DESCRIBE pet;

//查找

SELECT what_to_select

FROM which_table

WHERE conditions_to_satisfy;

//查找全部

mysql> SELECT * FROM pet;

//限制查找行

mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')

    -> OR (species = 'dog' AND sex = 'f');

//限制查找列

mysql> SELECT name, species, birth FROM pet

    -> WHERE species = 'dog' OR species = 'cat';

//查找去掉相同项

mysql> SELECT DISTINCT owner FROM pet;

//要想找出以“fy”结尾的名字：

mysql> SELECT * FROM pet WHERE name LIKE '%fy';

//REGEXP

• 在模式开始处使用“^”或在模式的结尾用“$”。

• mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b';

//修改

mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

//添加一列

alter table aa add sex int after id;.

//删除数据库

drop database name

//表的约束

定义主键约束   primary key:不允许为空，不允许重复

删除主键：alter table tablename drop primary key ;

定义主键自动增长   auto_increment

定义唯一约束   unique

定义非空约束   not null

定义外键约束 constraint ordersid_FK foreign key(ordersid) references orders(id),

 

 

 

 

 

 

 

 

 

 

 

 

转载于:https://my.oschina.net/u/3164851/blog/809895