MySQL 持续更新

创建数据库

create database if not exists demo1 default character set utf8;

显示数据库

show databases;

使用like从句

show databases like ‘demo1%’;

显示创建数据库语句(同时也可以查看修改的信息)

show create database demo1;

修改数据库

alter database demo1 default character set gb2312;

删除数据库

drop database if exists demo1；

选择数据库

use demo1;

显示存储引擎

show engines

设置默认存储引擎(临时起效，重启还原)

set default_storage_engine=MyISAM

创建数据表

create table t1
(
id INT(11) PRIMARY KEY AUTO_INCREMENT COMMENT ‘主键’,
age TINYINT(3) NOT NULL COMMENT ‘年龄’,
name VARCHAR(50) NOT NULL COMMENT ‘姓名’
);

修改数据表

添加在首列

alter table t1 add column fst INT first;

添加在某个字段后

alter table t1 add column sec CHAR after fst;

修改字段数据类型

alter table t1 modify name char;

删除字段

alter table t1 drop sec;

修改字段名

alter table t1 change fst ft BIGINT;

修改表名

alter table t1 rename to t2;

删除数据表

drop table t1;

创建表时创建复合主键

create table t2
(
id int(11) not null,
name char(20) not null,
age tinyint(3),
primary key(id,name)
);

向没有主键的表添加主键

alter table t1 add primary key (id);

添加外键

创建表时添加外键

create table t2
(
id INT(11) PRIMARY KEY ,
name VARCHAR(10),
deptid INT(11),
CONSTRAINT fk_dept
FOREIGN KEY(deptid) REFERENCES t1(id)
);

修改表时添加外键

alter table t2 add constraint fk_dept
foreign key(deptid) references t1(id);

删除外键约束

alter table t2 drop foreign key fk_dept;

创建表时添加唯一约束

create table t3
(
id INT(11) PRIMARY KEY,
name VARCHAR(20) UNIQUE,
location TEXT
);

修改表时添加唯一约束

alter table t3 add constraint unique_location unique(location);

删除唯一约束

alter table t1 drop index unique_location;

设置默认值

在创建表时设置默认值

create table t1
(
id INT(11) NOT NULL PRIMARY KEY,
name varchar(20) default ‘tom’,
age tinyint(3) default 18
);

查询数据库

select * from t1;
select distinct name from t1;(去重)
select t1.name from table as t1;(表:别名)
select name as t1_name from t1;(字段:别名)
select * from t1 limit 4;(限制输出前4行)
select * from t1 order by age;(按年龄排序)
select * from t1 order by name desc, age asc;(先按name降序，再按age升序)
select * from t1 where name=1;(单一条件查询)
select * from t1 where name=1 and age<4;(多条件查询)
select name from t1 where name like ‘1%’;(使用like的模糊查询)
select * from t1 where login_date<‘2018-2-22’;(日期作为条件查询)
select greatest(name,age) from t1 where id=8;(找出最大值)

内连接查询

select name,age,dept_name from t1,t2 where t1.dept_id=t2.dept_id;
select student.name,age,master from student inner join department on student.dept_id=department.id;

外连查询

select age from student s left outer join department d on s.dept_id=d.id;

子查询

select name from student where id in (select id from department where name!=‘c’);
select name from student where id not in (select id from department where name=‘c’);

分组查询

select dept_id,group_concat(name) as names from student group by dept_id;

分组查询指定过滤条件

select dept_id,group_concat(name) as names from student group by dept_id having count(name)>1;