MYSQL
- 创建表
create table 表名 (
列名 类型 [约束] ,
列名 类型 [约束] ,
列名 类型 [约束] ,
…
列名 类型 [约束]
)
create table student(
stu_id varchar(10) primary key ,
stu_name varchar(10) ,
stu_sex varchar(2),
stu_address varchar(100),
stu_age int ,
stu_date timestamp
)
-
查询表的数据
select 字段列表 from 表名 [条件]
select * from student ; -
插入数据
insert into 表名 [(字段名1,字段名2…)] values (字段值1,字段值2,…);
insert into student values (‘101’ ,‘zhangsan’,‘男’,‘asdfa’,18, str_to_date(‘08.09.2008 08:09:33’,’%d.%m.%Y %h:%i:%s’) );
insert into student values (‘102’ ,‘lisi’,‘女’,‘asdfa’,20, str_to_date(‘08.09.2018 14:09:33’,’%d.%m.%Y %H:%i:%s’) );
select date_format( stu_date,’%d.%m.%Y %H:%i:%s’) bir from student;
insert into student(stu_id,stu_age ,stu_name) values (‘103’,22,‘wangwu’);
(%H : 24小时制 %h:12小时制)
-
修改数据
update 表名 set 字段名 = 新字段值 [条件]
update student set stu_age = 21 where stu_name = ‘zhangsan’; -
删除数据
delete from 表名 [条件]
delete from student where stu_name = ‘zhangsan’; -
删除表
drop table student ; -
约束
auto_increment 自增长
primary key :主键约束(非空、唯一)
not null 非空约束
unique 唯一约束
default 默认
enum 枚举(可以为null)
CREATE TABLE student2(
stu_id int auto_increment primary key ,
stu_name varchar(10) not null ,
stu_sex enum(‘男’,‘女’),
stu_address varchar(100) unique ,
stu_age int ,
stu_date timestamp default current_timestamp
);
insert into student2 values (111, ‘apple’,‘男’,‘address1’,19,null) ;
insert into student2 values (null, ‘seven’,‘男’,‘address2’,20,null) ;
insert into student2 values (null, ‘six’,null,‘address3’,20,null) ;
insert into student2 values (null, ‘five’,null,‘address4’,20,null) ;
insert into student2 values (null, ‘lili’,‘女’,‘address5’,12,null) ;
insert into student2 values (null, ‘live’,‘女’,‘address6’,17,null) ;
-
别名(as 取别名 关键字可省略)
select stu_id as Stuid,stu_name as “Stu Name” from student2 ; -
排序order by(升序asc 降序desc)
select * from student2 order by stu_age asc ,stu_id desc ; -
模糊查询 like (% : 0-N个字符, _一个字符)
select * from student2 where stu_name like ‘__l%’; -
去重distanct
select distinct stu_sex from student2 ; -
聚合函数(max最大值 min最小值 avg平均值 sum求和 count)
select max(stu_age) max_age ,min(stu_age),avg(stu_age),sum(stu_age) from student2 ;
select count(*) from student2 ; --计算表中总数据条数 -
分组:group by(只能select分组的字段跟聚合函数。分组后面如果要用条件过滤,需要使用having,而不能使用where)
select max(stu_age) max_age ,min(stu_age),avg(stu_age),sum(stu_age) , stu_sex from student2 group by stu_sex having avg(stu_age) > 18 ; -
外键约束
foreign key (本表字段) references 外表(外表字段)
create table stutest (
test_id int primary key ,
test_name varchar(10) ,
test_score int ,
test_user_id int ,
foreign key (test_user_id) references student2(stu_id) – 把test_user_id引用为student2(stu_id)
);
insert into stutest values (1 , ‘语文’,88, 111 );
insert into stutest values (2 , ‘数学’,98, null );
insert into stutest values (3 , ‘数学’,76, 111 );
insert into stutest values (4 , ‘英语’,48, 112 );
-
内连接/左连接/右连接
select * from stutest ,student2 where stutest.test_user_id = student2.stu_id;–内连接
select * from stutest inner join student2 on stutest.test_user_id = student2.stu_id; --内连接
select * from stutest left join student2 on stutest.test_user_id = student2.stu_id; --左连接
select * from stutest right join student2 on stutest.test_user_id = student2.stu_id; --右连接 -
in , not in(not in在mysql里面有坑,后面跟的结果集里面不能有null项)
select * from student2 where stu_age = (select max(stu_age) from student2 );
select * from student2 where stu_id in (select test_user_id from stutest where test_user_id is not null and test_name = ‘数学’) ; -
子查询
-
分页
select * from student2 limit 4 ,2 ; -
增加列
alter table 表名 add (列名 列类型 [约束] , 列名 列类型 [约束] , … );
alter table 表名 add column 列名 列类型 [约束];
alter table 表名 add column 列名 列类型 [约束] [FIRST]/[AFTER 列名];
// 增加一列到首列/到某列的后面
alter table student add (stu_weight int); -
修改列
alter table表名 change 旧列名 新列名 类型
alter table student change stu_weight stu_weight1 DECIMAL -
修改表名
alter table 表名 rename to 新表名
alter table student rename to student1 -
删除列
alter table 表名 drop column 列名
alter table student1 drop column stu_weight -
删除表
drop table表名
drop table student1