MySQL-基本操作


create database db1;

alter database db1 charset utf-8;               #(前面是库,后面是要改成什么样)

show databases;

show create database db2;                       #查看库的创建

drop database db2;                              

create table tb2(id int(3),name char(20),age int(2));                   #创建表

insert into tb1(id,name,age) values(1,"A",18);                          #插入数据

alter table student add Now int(1);                                     #增加字段(Now)

select * from tb1;

update db1.tb1 set name='L' where name='B';                         #改数据

alter table tb1 change name name char(10);                          #改字段

alter table db1.tb1 charset utf8;                                   #改字符集

delete from tb1 where name='L';

这是清空数据库的操作,是否想要清空整个数据库: delete from tb1;

限制

create table student2(
    id int not null primary key auto_increment,     #主键并自增长
    name char(20) not null,         #不为空
    age int not null default 18,    #默认18
    sex enum('boy','girl') default 'boy',   #单选
    hope set('milk','apple','origin') default 'milk,apple'  #多选
);

unique单列唯一

此列永不重复

create table department(
    id int,
    name char(10) unique
);

主键primary key

作用:

值唯一,切不为空

create table t14(
    id int primary key,
    name char(16)
);

外键foreign key

被关联表(主表):

create table dep(
    id int primary key,
    name varchar(20) not null,
    descripe varchar(20) not null
   );

关联表(从表):

create table emp(
    id int primary key auto_increment,
    name char(10) not null,
    age int default 18,
    dep_id int,
    constraint fk_dep foreign key(dep_id) references dep(id)
    on delete cascade   #随着更新   
    on update cascade 
);

升降序

升序:ASC

降序:DESC

SELECT * from employee
    ORDER BY age ASC;

制定查询次数limit

SELECT * FROM employee ORDER BY salary DESC LIMIT 3;                    #默认初始位置为0 

SELECT * FROM employee ORDER BY salary DESC LIMIT 0,5;      #从第0开始,即先查询出第一条,然后包含这一条在内往后查5条

SELECT * FROM employee ORDER BY salary DESC LIMIT 5,5;      #从第5开始,即先查询出第6条,然后包含这一条在内往后查5条

分组group by

将所有记录按照某个相同字段进行归类,且默认只显示一部分数据

select post,count(post) as '总人数' from employee group by post;       #post只是一个字段而已
+-----------------------------------------+-----------+
| post                                    | 总人数    |
+-----------------------------------------+-----------+
| operation                               |         5 |
| sale                                    |         5 |
| teacher                                 |         7 |
| 老男孩驻沙河办事处外交大使                  |         1 |
+-----------------------------------------+-----------+

子查询

  1. 子查询是将一个查询语句嵌套在另一个查询语句中。
  2. 内层查询语句的查询结果,可以为外层查询语句提供查询条件。
  3. 子查询中可以包含:IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
  4. 还可以包含比较运算符:= 、 !=、> 、<等

个人解释:先查出共有的部分,然后在另外表上根据共有的部分去查或者过滤你想要的内容


查询平均年龄在25岁以上的部门名

select id,name from department
    where id in 
        (select dep_id from employee group by dep_id having avg(age) > 25);

查看技术部员工姓名

select name from employee
    where dep_id in 
        (select id from department where name='技术');

join 和 子查询 优劣比较:https://www.jianshu.com/p/cb1e14c0ac39

多表查询join

内链接(inner join ):

找到两张表共有的部分,共有部分,共有部分,共有部分,如果后面的表没有和前表有共有部分,那么就不会显示。

 select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id;
+----+---------+------+--------+--------------+
| id | name    | age  | sex    | name         |
+----+---------+------+--------+--------------+
|  1 | egon    |   18 | male   | 技术         |
|  2 | alex    |   48 | female | 人力资源     |
|  3 | wupeiqi |   38 | male   | 人力资源     |
|  4 | yuanhao |   28 | female | 销售         |
|  5 | nvshen  |   18 | male   | 技术         |
+----+---------+------+--------+--------------+

左连接(left join):

优先以左边的表为准,可以想象成在内连接的基础上增加了左表有,右表没有的结果

select *  from employee left join department on employee.dep_id=department.id;
+----+----------+--------+------+--------+------+--------------+
| id | name     | sex    | age  | dep_id | id   | name         |        #这里为了节省代码用*代替
+----+----------+--------+------+--------+------+--------------+
|  1 | egon     | male   |   18 |    200 |  200 | 技术         |
|  5 | nvshen   | male   |   18 |    200 |  200 | 技术         |
|  2 | alex     | female |   48 |    201 |  201 | 人力资源     |
|  3 | wupeiqi  | male   |   38 |    201 |  201 | 人力资源     |
|  4 | yuanhao  | female |   28 |    202 |  202 | 销售         |
|  6 | xiaomage | female |   18 |    204 | NULL | NULL         |
+----+----------+--------+------+--------+------+--------------+

全外链接:

MySQL 不支持full join,可以用其他方式,说白了就是左连接 +右连接,然后使用union(联合)-。-

select * from employee left join department on employee.dep_id=department.id union select * from employee right join department on employee.dep_id = department.id;
+------+----------+--------+------+--------+------+--------------+
| id   | name     | sex    | age  | dep_id | id   | name         |
+------+----------+--------+------+--------+------+--------------+
|    1 | egon     | male   |   18 |    200 |  200 | 技术         |
|    5 | nvshen   | male   |   18 |    200 |  200 | 技术         |
|    2 | alex     | female |   48 |    201 |  201 | 人力资源     |
|    3 | wupeiqi  | male   |   38 |    201 |  201 | 人力资源     |
|    4 | yuanhao  | female |   28 |    202 |  202 | 销售         |
|    6 | xiaomage | female |   18 |    204 | NULL | NULL         |
| NULL | NULL     | NULL   | NULL |   NULL |  203 | 运营         |
+------+----------+--------+------+--------+------+--------------+

发表评论