mysql手册01_常用SQL语句大全

た入场券 2022-11-26 01:53 281阅读 0赞

**mysql手册01\_常用SQL语句大全**

--------------------

基础查询语句：
    select * from t_blog;
    select id,title from t_blog;
    
    条件查询:
    select * from t_blog where id = 40;
    select * from student where degree between 60 and 80;
    select * from student where degree > 60 and degree < 80;
    select * from student where classes='1001' or sex='女';
    select * from student where degree in(60, 80, 100);
    
    模糊查询：
    select * from t_blog where content like '%数据库%';
    select * from t_blog where content not like '%广告%';
    注 '3%'表示以3开头，'%3'表示以3结尾。
    
    排序查询：
    select * from student order by degree asc;
    select * from student order by degree desc;
    select * from student order by classes asc,degree desc;
    
    计数和统计：
    select count(*) from student where sex='男';
    select max(degree) from student;
    select min(degree) from student;
    select avg(degree) from student;
    
    通过嵌套和分页查询分数最高的student数据：
    select * from student where degree=(select max(degree) from student);
    select * from student order by degree desc limit 0,1;
    
    按班级分组查询平均分：
    select avg(degree) from student group by classes;
    
    查询学生个数大于10人的班级平均分
    select classes,avg(degree) from student group by classes having count(classes)>10;
    
    两表联查
    select sname,cname,degree from student,classes where student.classes=classes.id;
    
    字段去重查询:
    select distinct species from pet;
    
    使用year函数查询出生年份为2006年的记录：
    select * from pet where year(birth)=2006;
    
    使用year函数和now函数查询出生年份为今年的记录：
    select * from pet where year(birth)=year(now());
    
    使用year函数和now函数查询年龄：
    select name,year(now())-year(birth) as '年龄' from pet;
    
    //any---查询1班中分数大于2班中至少一个人（2班最低分）的数据
    select * from student where classes='1班' and degree>any(select degree from student where classes='2班');
    
    //all---查询1班中分数大于2班所有人（2班最高分）的数据
    select * from student where classes='1班' and degree>all(select degree from student where classes='2班');
    
    //求并集，注：union前后的select语句必须拥有相同数量的列,在union之前的select语句里设置别名：
    select tname as name,tsex as sex,tbirthday as birthday from teacher 
    union
    select sname,ssex,sbirthday from student;

退出数据库服务器：
    exit;

*  **mysql常用数据类型：**

数值类型：
    tinyint, smallint, mediumint, int或integer, bigint, float, double, decimal
    日期和时间类型：
    date	2020-08-10
    time	12:22:32
    year	2020
    datetime	2020-08-10 12:22:32
    timestamp	时间戳
    字符串类型(blob表示二进制形式)：
    char, varchar, tinyblob, tinytext, blob, text, mediumblob, mediumtext, longblob, longtext

*  **mysql建表约束**

-- 主键约束：字段值不重复且不为空，能够唯一确定表中的一条数据
    -- 自增约束：如名，一般和主键约束配合使用
    -- 唯一约束：约束字段值不可重复
    -- 非空约束：约束字段值不可为空
    -- 默认约束：约束字段的默认值
    -- 外键约束：从表的外键值要参照主表。被从表引用的主表记录不可删除。

//设置id主键约束,id不能为空也不能重复
    create table user(id int primary key, name varchar(20));
    //设置联合主键id和name，id和name不能为空，不能同时重复
    create table user2(id int, name varchar(20), password varchar(20), primary key(id,name));
    //设置id自增约束，配合主键约束使用
    create table user3(id int primary key auto_increment, name varchar(20));
    //给已经创建好的表的id字段添加主键约束
    alter table user4 add primary key(id);
    //给已经创建好的表的id字段删除主键约束
    alter table user4 drop primary key(id);
    //使用modify字段添加或删除约束
    alter table user4 modify id int primary key;
    //唯一约束
    create table user5(id, name varchar(20) unique);
    create table user5(id, name varchar(20), unique(id,name));
    alter table user5 add unique(name);
    alter table user5 modify name varchar(20) unique;
    //删除唯一约束
    alter table user5 drop index name;
    //非空约束
    create table user6(id int, name varchar(20) not null);
    //默认约束
    create table user7(id int, name varchar(20), age int default 18);
    //外键约束
    create table classes(id int primary key, name varchar(20));
    create table students(id int primary key, name varchar(20), class_id int, foreign key(class_id) references classes(id));

*  **数据库的三大设计范式**

第一范式：确保每列保持原子性
    第二范式：满足第一范式的前提下，要求确保表中的每列都和主键相关
    第三范式：满足第二范式的前提下，确保每列都和主键列直接相关,而不是间接相关

不满足第一范式：
    create table students2(id int primary key, name varchar(20), address varchar(30));
    insert into students2 values(1,'张三','中国江苏南京');
    insert into students2 values(2,'李四','中国安徽合肥');
    insert into students2 values(3,'王五','中国浙江杭州');
    满足第一范式：
    create table students3(id int primary key, name varchar(20), country varchar(30), province varchar(30), city varchar(30));
    insert into students3 values(1,'张三','中国','江苏','南京');
    insert into students3 values(2,'李四','中国','安徽','合肥');
    insert into students3 values(3,'王五','中国','浙江','杭州');
    
    不满足第二范式：
    create table myorder(
    	product_id int,
    	customer_id int,
    	product_name varchar(20),
    	customer_name varchar(20),
    	primary key(product_id, customer_id));
    满足第二范式：
    create table myorder(id int primary key, product_id int, customer_id int);
    create table product(id int primary key, name varchar(20));
    create table customer(id int primary key, name varchar(20));
    
    不满足第三范式：
    create table myorder(id int primary key, product_id int, customer_id int, customer_phone varchar(15));
    满足第三范式：
    create table myorder(id int primary key, product_id int, customer_id int);
    create table customer(id int primary key, name varchar(20), customer_phone varchar(15));

*  **复制表数据做条件查询**

student表：
    +----+------+---------+--------+
    | id | name | classes | degree |
    +----+------+---------+--------+
    |  1 | zs   | 1班     |   87.5 |
    |  2 | lisi | 2班     |   65.5 |
    |  3 | ww   | 3班     |   91.5 |
    |  4 | zs1  | 1班     |   46.5 |
    |  5 | zs2  | 1班     |     54 |
    |  6 | zs3  | 1班     |    100 |
    |  7 | ww2  | 3班     |   99.5 |
    |  8 | ww3  | 3班     |     20 |
    |  9 | lisi | 2班     |   85.5 |
    | 10 | ssss | 1班     |     65 |
    | 11 | ssad | 1班     |     66 |
    +----+------+---------+--------+
    
    查询低于班级平均分的student数据
    select * from student a where degree<(select avg(degree) from student b where a.classes = b.classes);
    结果：
    +----+------+---------+--------+
    | id | name | classes | degree |
    +----+------+---------+--------+
    |  2 | lisi | 2班     |   65.5 |
    |  4 | zs1  | 1班     |   46.5 |
    |  5 | zs2  | 1班     |     54 |
    |  8 | ww3  | 3班     |     20 |
    | 10 | ssss | 1班     |     65 |
    | 11 | ssad | 1班     |     66 |
    +----+------+---------+--------+

*  **SQL的四种连接查询**

内连接: inner join 或 join
    外连接-左连接：left join 或 left outer join
    外连接-右连接：right join 或 right outer join
    外连接-完全外连接：full join 或 full outer join

create table person(id int, name varchar(20), cardId int);
    create table card(id int, name varchar(20)); 
    
    insert into card values(1,'饭卡'),(2,'公交卡'),(3,'银行卡'),(4,'购物卡'),(5,'门禁卡');
    insert into person values(1,'zhangsan',1),(2,'lisi',3),(3,'wangwu',6);
    
    内连接查询（按对应条件查询出两个表的值）：
    select * from person inner join card on person.cardId=card.id;
    +------+----------+--------+------+-----------+
    | id   | name     | cardId | id   | name      |
    +------+----------+--------+------+-----------+
    |    1 | zhangsan |      1 |    1 | 饭卡      |
    |    2 | lisi     |      3 |    3 | 银行卡    |
    +------+----------+--------+------+-----------+
    
    左连接查询（左边表person数据全部取出，如果条件不对应就将右表card的数据设为NULL值）：
    select * from person left join card on person.cardId=card.id;
    +------+----------+--------+------+-----------+
    | id   | name     | cardId | id   | name      |
    +------+----------+--------+------+-----------+
    |    1 | zhangsan |      1 |    1 | 饭卡      |
    |    2 | lisi     |      3 |    3 | 银行卡    |
    |    3 | wangwu   |      6 | NULL | NULL      |
    +------+----------+--------+------+-----------+
    
    右连接查询（右边表card数据全部取出，如果条件不对应就将左表person的数据设为NULL值）：
    select * from person right join card on person.cardId=card.id;
    +------+----------+--------+------+-----------+
    | id   | name     | cardId | id   | name      |
    +------+----------+--------+------+-----------+
    |    1 | zhangsan |      1 |    1 | 饭卡      |
    |    2 | lisi     |      3 |    3 | 银行卡    |
    | NULL | NULL     |   NULL |    2 | 公交卡    |
    | NULL | NULL     |   NULL |    4 | 购物卡    |
    | NULL | NULL     |   NULL |    5 | 门禁卡    |
    +------+----------+--------+------+-----------+
    
    完全外连接查询
    select * from person full join card on person.cardId=card.id;
    结果：
    ERROR 1054 (42S22): Unknown column 'person.cardId' in 'on clause'
    
    原因：mysql不支持完全外连接查询语句，而是通过左连接union右连接来完成查询的：
    select * from person left join card on person.cardId=card.id 
    union 
    select * from person right join card on person.cardId=card.id;
    +------+----------+--------+------+-----------+
    | id   | name     | cardId | id   | name      |
    +------+----------+--------+------+-----------+
    |    1 | zhangsan |      1 |    1 | 饭卡      |
    |    2 | lisi     |      3 |    3 | 银行卡    |
    |    3 | wangwu   |      6 | NULL | NULL      |
    | NULL | NULL     |   NULL |    2 | 公交卡    |
    | NULL | NULL     |   NULL |    4 | 购物卡    |
    | NULL | NULL     |   NULL |    5 | 门禁卡    |
    +------+----------+--------+------+-----------+