mysql数据库笔记

创建数据库
create database sis2107;

删除数据库
drop database sis2107;

创建表，并添加约束
create table table(id int auto_increment primary key,user varchar(4) not null,password varchar(10),sex char(4));
注：添加条件必须设置主键

添加表，不设置主键
create table table1(id int,user varchar(4),password varchar(10),sex char(4));

修改表，添加主键
alter table table1 add primary key(id);

添加表，添加唯一主键
create table table2(id int primary key,user varchar(4),password varchar(10),sex char(4));

create table table3(id int,user varchar(4),password varchar(10),sex char(4),primary key(id));

添加表，添加复合主键
create table table4(id int,user varchar(4),password varchar(10),sex char(4),age int(11),primary key(id,age));

添加表，添加唯一约束
create table table5(id int,user varchar(4),password varchar(10),sex char(4),age int(11),unique(id));

查看表中约束
show create table table2;

修改表
alter table table1 rename tables1

添加字段
ALTER TABLE table1 ADD age int default null;

删除表
drop table table1；

删除字段
alter table table1 drop name;

添加数据
insert into table1 values(1,"xiaoqiao",18,"woman","2002");

insert into table1(id,name,age,sex,birthday) values(1,"xiaoqiao",18,"woman","2002");

insert into table1(id,name,age,sex,birthday) values(1,"xiaoqiao",18,"woman","2002"),(1,"xiaoqiao",18,"woman","2002");

删除数据
delete from table1 where id=1;

delete from table1

修改数据
update table1 set name="fff",sex="man",age=22 where id=2;

update table1 set name="aaa";

查询数据
select * from table1;

select id,name from table1;

按条件查询
select * from table1 where id<6;

按条件查询“in”，查询集合
select * from table1 where age in (56,22);

按条件查询“BETWEEN AND”，查询范围
select * from table1 where id between 1 and 5;

空值查询
select * from table1 where id is null;

select * from table1 where id is not null;

按条件查询“DISTINCT”，去重数据
select distinct sex from table1;

LIKE模糊查询
“%”通配符查询 查询指定范围"w%n/%n/w%"
select * from table1 where sex like "w%n";

“_”下划线查询 查询缺少字符"m_n/w__an"
select * from table1 where sex like "m_n";

and条件查询
select * from table1 where id>5 and sex="man";

or条件查询
select * from table1 where id<5 or age>30;

and,or 一起查询
select * from table1 where id<5 or sex="man" and age<50;

聚合函数
count() 返回字段条数
select count(*) from table1;

sum() 返回数值和
select sum(age) from table1;

avg() 返回平均值
select avg(age) from table1;

max() 返回最大值
select max(age) from table1;

min() 返回最小值
select min(age) from table1;

order by 排序查询
select * from table1 order by age; 正序
select * from table1 order by age desc; 倒序

group by 关键字分组查询
单独使用
select * from table1 group by sex;

和聚合函数一起使用
SELECT COUNT(*) ,age FROM table1 GROUP BY age;

GROUP BY 和 HAVING 关键字一起使用
SELECT sum(age),sex FROM table1 GROUP BY sex HAVING SUM(age) < 300;

limit 限制查询结果数量
select * from table1 limit 4;

select * from table1 limit 4,7;

取别名 AS
表取别名（当即返回，不是永久）
select * from table1 as t where t.name="ccc";

列取别名（当即返回，不是永久）
select name as user from table1;

子查询(嵌套查询)
select * from table1 where id<(select age from table1 where name="ccc");

联合查询
select version() union select user();

内置表查询
查询数据库：
select schema_name from information_schema.schemata;

查询表名：
select table_name from information_schema.tables;

查询列名：
select column_name from information_schema.columns;

查询数据：
select * from students;