mysql基础语句笔记总结

                                                                             增加数据

#插入数据增加
#带字段
insert into t_student(studentId, stu_name,sex,age,birthday)##insert :插入
values(1,‘小明’,‘男’，18，‘2000-10-10 12：12：12’）；
#不带字段
insert into t_student values(2,‘skdjfk’,18,1993-10-03 34:23:12);
#指定字段插入数据
insert into t_student(studentId,sex,age,birthday)values(1,男，23，0413)；

                                                                                 修改

update:更改
无条件修改
update t_student set stu_name=‘ming’;#全部stu_name全部变为ming；
有条件修改
update t_student set stu_name=‘xing’ where studentId studentId=5;#第五行改为xing。

                                                              删除

delete from t_student where studentId=6;
delete from t_student where sex=‘女’;#条件删除
delete from t_student;全部删除
2中truncate只能清空表中所有数据不能加where表数据从新从一开始。

                                                            查看表信息

select * from t_student;查询所有字段
select stu_name,age from t_student;查询指定字段
select studentId as id, age as ‘年龄’,birthday ‘生日’ from t_student;表上别名的改变给studentId起一个别名id，给age起个别名年龄等；
select * from t_student where age<15;条件查询小于15岁的人；
去掉重复的组合
select distinct sex, age, stu_name from t_student;#distiinct区别区分。
select *from t_student where studentId (not 没有) in（1，3，4）；#查询里面拥有的id=1，3，4里的数据（not）里面没有的数据。
select age,sex from t_student where stu_name in(‘lili’,‘jace’,‘tom’);#查询姓名为lili或者jace或者tom的年龄和性别
select * from t_student where studentId between2 and 4;#查询里面2<=id<=4的数据信息
select * from t_student where stu_name like ‘%c%’;#查询名字里面包含c信息的数据；
select studentId,age,sex,stu_name from t_student where stu_name like’高’;#一个下划线匹配的是一个字符
select * from t_student where stu_name =‘高刚’ and sex=‘男’；两条件同时满足才会输出该句信息
select * from t_student where stu_name =‘高能’ or stx=‘男’；两条件人已满足一个就可查询该信息

                                                        升降

asc升 desc降
select * from t_student order by age asc;数据中年龄升序排列
select * from t_student limit 1,3;从一开始不算一往下3条信息；

                                                               外键

engine=innodb支持外键
name varchar(32),
primary key (id),
foreign key (id) references ?category?(cid)
(2)外面增加外键
alter table article add foreign key (cid) references ?category?(cid)
合并查询
select id,name from ?article? where id=1
union
select id,name from ?article? where id=5;

                                                                       交叉连接

select * from category c cross join article a where c.cid=a.cid;
等价于select *from category c,article a where c.cid=a.cid;
内连接
select *from category c inner join article a on c.cid=a.cid;
左外连接
select *from category c left join article a on c.cid=a.cid;
right
全
select *from category c outer join article a on c.cid=a.cid;
查询那些文章类型 （子查询）
select *from article where cid(select * from category where cname=‘诗’);#select * from category where cname='诗’相当于cid=？
如果存在cid=9则name改
update article aet name='郸饭店’where cid=9 and exists (select cname from category where cid=9);