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;