主要总结mysql一些常用知识点
[常用命令]
1、查看数据库
show database;
2、创建数据库
create database database_name;
3、切换数据库
use database_name;
4、查看某数据库中所有的数据表
show table;
5、创建数据表
View Code
6、查看数据表结构
describe table_name; --缩写: desc
7、查看数据表中的记录
select * from table_name;
-- 去重复
select distinct name from table_name
8、往数据表中添加数据记录
INSERT INTO table_name
VALUES('puffball','Diane','hanst','f','1999-03-23',NULL);
-- 指定属性
insert into user3 (name) value('asfjl');
9、删除数据
delete from table_name where name='puffball';
10、修改数据
update table_name set name='wang' where owner='haha'
11、建表约束--主键
View Code
12、建表约束--自增
create table user3(
id int primary key auto_increment,
name varchar(20));
12、建表约束--唯一：约束修饰的字段的值不可以重复
View Code
13、非空约束：修饰的字段不能为NULL
复制代码
create table user6(
id int,
name varchar(20) not null );
-－ 反null? 异常
insert into user6 (name) value('jfsl');
复制代码
14、默认约束
复制代码
create table user7(
id int,
name varchar(20),
age int default 10);
insert into user7 (id,name) value(1,'slfj');
insert into user7 (id,name,age) values(1,'slsfj',5);
复制代码
15、外键约束
复制代码
create table classes(
id int primary key,
name varchar(20));
create table students(
id int primary key,
class_id int,
foreign key(class_id) references classes(id));
复制代码
[查询]
1、多表查询
复制代码
-- 两表查询
select sname,cno, degree from student,score
where student.sno = score.sno;
-- 三表查询
select sname, cname,degree from student,course,course,score
where student.sno = score.sno
and course.cno = score.cno;
复制代码
2、分组查询
复制代码
-- 子查询加分组求评均
select cno, avg(degree) from score
where sno in (select sno from student where class='1233')
group by cno;
-- year函数与带in关键字的子查询
select * from student where year(sbirthday) in (select year(sbirthday) from student where sno in (108,117));
复制代码
3、多层嵌套查询
View Code
4、union与not in
View Code
5、any与all
复制代码
-- any表示至少一个
select * from score where cno='34'
and degree>any(select degree from score where cno='334')
order by degree desc;
-- all表示所有
select * from score where cno='34'
and degree>all(select degree from score where cno='334')
order by degree desc;
复制代码
原文地址https://www.cnblogs.com/lisen10/p/sql.html
