mysql数据库的基本操作

2018-11-29 本文已影响0人清风徐来_简

一、基本增删改查
二、创建索引的几种方式
三、单表查询
四、连表查询

一、基本增删改查

1.数据库的增删改查

show databases; ---> 查看当前都有哪些数据库。

create database 数据库名; ---> 创建数据库（charset utf8，这个不写，为默认编码）。

show create database 数据库名； ---> 查看创建的数据库。

select database（）； ---> 查看当前所在的库的位置（在哪个库里）。

use 数据库名； ---> 选择数据库。

drop database 数据库名； ---> 删除数据库。

alter database 数据库名 charset gbk； ---> 修改数据库的编码。

2.表的增删改查

create table t1（id int，name char（10））；--->（engine=innodb；不写默认为innodb引擎）,创建表。

insert into t1 values （1，'hehe'）,(2,'haha')； ---> 向表中插入记录。

select * from t1；/ select id，name from t1； ---> 查询表内容。

desc t1； ---> 查看表结构。

show tables； ---> 查看当前数据库有哪些表。

show create table t1； ---> 查看表的详细结构。

show create table t1\G(不带分号)---> 也是查看表的详细结构，有一丢丢不同。

create table b1 select * from t1（db1.t1）； ---> 复制t1表，为b1(可在本数据库或其他数据库复制)。

create table b2 select * from db1.t1 where 1>5 / false； ---> 只复制表结构，不要数据。

create table b3 like db1.t1； ---> 也是只复制表结构，不要数据。

drop table 表名； ---> 删除表。

alter table b1 add name char(11); ---> 增加单个字段。

alter table b1 add (age int,sex char(11)); ---> 增加多个字段；

alter table t1 drop id； ---> 删除单个字段。(不能全部删除，起码留一个)。

alter table b1 drop name,drop age; ---> 删除多个字段。

alter table t1 modify name char（6）； ---> 修改表中name字段的char属性。

alter table t1 change name NAME char(7)； ---> 修改表中name字段为NAME字段。

update db1.t1 set name='zhangsan';---> 修改t1中的所有name字段都是‘zhangsan’。

update db1.t1 set name='zhangsan' where id=2;---> 修改t1中id为2的name字段中，为‘zhangsan’。

delete from t1;---> 清空t1表的内容。

delete from t1 where id=2; ---> 清空t1表中id为2的内容。

二、创建索引的几种方式

1.唯一索引：

mysql> create table t1 (id int,name varchar(10),unique(name));

Query OK, 0 rows affected (0.35 sec)
mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

2.唯一索引方式2:

mysql> create table t5(id int,name varchar(10));

Query OK, 0 rows affected (0.34 sec)

mysql> alter table t5 add unique(id);

Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t5;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | UNI | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

3.联合唯一索引：

mysql> create table t2(id int,name varchar(10),addr varchar(20),unique(name,addr));

Query OK, 0 rows affected (0.34 sec)
mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  | MUL | NULL    |       |
| addr  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

4.主键索引：

mysql> create table t3(id int,name varchar(10),primary key (id));

Query OK, 0 rows affected (0.34 sec)
mysql> desc t3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

5.主键索引方式2：

mysql> create table t4(id int,name varchar(10));

Query OK, 0 rows affected (0.45 sec)

mysql> alter table t4 add primary key (id);

Query OK, 0 rows affected (0.64 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

6.普通索引:

mysql> create table t6 (id int,name varchar(10));

Query OK, 0 rows affected (0.40 sec)

mysql> alter table t6 add index id_index (id);

Query OK, 0 rows affected (0.28 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t6;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | MUL | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

三、单表查询

关键字及执行优先级

from 找到表；

where 拿着where指定的约束条件，去表中取出一条条记录；

子句中可以使用（都有哪些约束条件）：

  1，比较运算符：>、<、>=、<=、!=

  2，between 80 and 100:值在80到100之间

  3，in(80,90,100):值在括号里

  4，like‘haha_’（%，_是通配符，和正则差不多）
     like 'hehe%'/ like 'hehe_'/ like '_hehe_'/ like '%hehe%'
     %任意多    _就一个

  5，逻辑运算符：在多个条件直接可以使用逻辑运算符and or not

例：

单条件查询：
  select id,name from employee where id > 5;

多条件查询：
  select name from employee where post='teacher' and salary>10000;

关键字查询 between and：
  select name,salary from employee where salary between 10000 and 20000;

关键字in集合查询：
  select name,salary from employee where salary=3000 or salary=4000 or salary=9000;
  select name,salary from employee where salary in/not in (3000,3500,4000,9000);

关键字like模糊查询：
  select * from employee where name like 'zhang%' ;
  select age from employee where name like 'zhang_';

group by 将取出的记录按grout by（），进行分组；

分组指的是：将所有记录按照某个相同字段进行归类。在这个类里进行筛选取值。
可按照任意字段分组，但分组完毕后只能查看该字段，如果想查看组内信息，需要借助聚合函数。

聚合函数:max()求最大值
        min()求最小值
        avg()求平均值
        sum() 求和
        count() 求总个数
        group_concat(name) 分组后name都有谁
        必须使用group by才能使用group_concat()函数，将所有的name值连接

例：

每个部门有多少个员工
    select post,count(id) from employee group by post;

    报错：不行。select * from employee where avg(age) > 30;
    正常：可以。select * from employee where age > 20 group by post having max(salary) > 10000;
    正常：可以。select sum(salary) from employee WHERE depart_id=3;

having 将分组的结果进行having过滤（二次筛选）；

where发生在group by之前，having发生在group by之后，可使用聚合函数。

例：
1.select depart_id,count(id) from employee group by depart_id having depart_id = 3;
2.select depart_id,count(id) from employee group by depart_id having count(id)>7;
3.select max(salary) 最大工资 from employee where id>2 group by depart_id having count(id)>3;

报错：不行。select post,group_concat(name),count(1) from employee group by post where count(1)<2;
原因：group by 后接 where 报错，应该接 having。
正常：可以。select post,group_concat(name),count(1) from employee group by post having count(1)<2;

group_concat(name)：显示都是谁；

having和where语法上是一样的。

select * from employee where id>15;    
select * from employee having id>15;

但是having和where不一样的地方在于以下几点！！！

1.where和having的区别                                                                                
1. Where 是一个约束声明，使用Where约束来自数据库的数据，Where是在结果返回之前起作用的                                        
（先找到表，按照where的约束条件，从表文件中取出数据），Where中不能使用聚合函数                                              
2. Having是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作                                                     
（先找到表，按照where的约束条件，从表文件中取出数据，然后group by分组，                                                
  如果没有group by则所有记录整体为一组，然后执行聚合函数，然后使用having对聚合的结果进行过滤），                                     
  在Having中可以使用聚合函数。                                                                          
3. where的优先级比having的优先级高                                                                     
4. having可以放到group by之后，而where只能放到group by 之前。

select 执行select，选择结果；

order by 将结果按条件排序；

按单列排序
    升序：SELECT * FROM employee ORDER BY age;
    升序：SELECT * FROM employee ORDER BY age ASC;
    降序：SELECT * FROM employee ORDER BY age DESC;
按多列排序
    先按照age升序排序，如果年纪相同，则按照id降序
         SELECT * from employee
               ORDER BY age ASC,
                id DESC;

limit 限制结果的显示条数。

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条

limit 0，5 / limit  5
起始位置，查询个数。
起始位置默认从0开始，从0往后算（按索引就是0也算，按第几个就是0不算）。

四、连表查询

1.认识笛卡尔积，将两张表强连接到一起

mysql> select * from department;
+------+--------------+
| id   | name         |
+------+--------------+
|  200 | 技术         |
|  201 | 人力资源     |                                   表一
|  202 | 销售         |
|  203 | 运营         |
+------+--------------+
4 rows in set (0.08 sec)

mysql> select * from employee;
+----+----------+--------+------+--------+
| id | name     | sex    | age  | dep_id |
+----+----------+--------+------+--------+
|  1 | aaaaa    | male   |   18 |    200 |
|  2 | bbbbb    | female |   48 |    201 |
|  3 | ccccc    | male   |   38 |    201 |                    表二
|  4 | xxxxx    | female |   28 |    202 |
|  5 | yyyyy    | male   |   18 |    200 |
|  6 | zzzzz    | female |   18 |    204 |
+----+----------+--------+------+--------+
6 rows in set (0.00 sec)

mysql> select * from employee,department;

+----+----------+--------+------+--------+------+--------------+
| id | name     | sex    | age  | dep_id | id   | name         |
+----+----------+--------+------+--------+------+--------------+
|  1 | aaaaa    | male   |   18 |    200 |  200 | 技术         |
|  1 | aaaaa    | male   |   18 |    200 |  201 | 人力资源     |
|  1 | aaaaa    | male   |   18 |    200 |  202 | 销售         |
|  1 | aaaaa    | male   |   18 |    200 |  203 | 运营         |
|  2 | bbbbb    | female |   48 |    201 |  200 | 技术         |
|  2 | bbbbb    | female |   48 |    201 |  201 | 人力资源     |
|  2 | bbbbb    | female |   48 |    201 |  202 | 销售         |
|  2 | bbbbb    | female |   48 |    201 |  203 | 运营         |
|  3 | ccccc    | male   |   38 |    201 |  200 | 技术         |
|  3 | ccccc    | male   |   38 |    201 |  201 | 人力资源     |
|  3 | ccccc    | male   |   38 |    201 |  202 | 销售         |               迪
|  3 | ccccc    | male   |   38 |    201 |  203 | 运营         |
|  4 | xxxxx    | female |   28 |    202 |  200 | 技术         |               卡
|  4 | xxxxx    | female |   28 |    202 |  201 | 人力资源     |
|  4 | xxxxx    | female |   28 |    202 |  202 | 销售         |               尔
|  4 | xxxxx    | female |   28 |    202 |  203 | 运营         |                
|  5 | yyyyy    | male   |   18 |    200 |  200 | 技术         |               积
|  5 | yyyyy    | male   |   18 |    200 |  201 | 人力资源     |                
|  5 | yyyyy    | male   |   18 |    200 |  202 | 销售         |
|  5 | yyyyy    | male   |   18 |    200 |  203 | 运营         |
|  6 | zzzzz    | female |   18 |    204 |  200 | 技术         |
|  6 | zzzzz    | female |   18 |    204 |  201 | 人力资源     |
|  6 | zzzzz    | female |   18 |    204 |  202 | 销售         |
|  6 | zzzzz    | female |   18 |    204 |  203 | 运营         |
+----+----------+--------+------+--------+------+--------------+
24 rows in set (0.00 sec)

通过上面发现，有许多数据是无效的，只有employee的dep_id =department 的 id那一列是真实有效的数据。所以引出了下文↓。

2.inner join内连接

mysql> 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 | aaaaa   |   18 | male   | 技术         |
|  2 | bbbbb   |   48 | female | 人力资源     |                      内连接
|  3 | ccccc   |   38 | male   | 人力资源     |
|  4 | xxxxx   |   28 | female | 销售         |
|  5 | yyyyy   |   18 | male   | 技术         |
+----+---------+------+--------+--------------+
5 rows in set (0.00 sec)

3.左连接（employee 在左）

mysql> select * from employee left join department on employee.dep_id=department.id;

+----+----------+--------+------+--------+------+--------------+
| id | name     | sex    | age  | dep_id | id   | name         |
+----+----------+--------+------+--------+------+--------------+
|  1 | aaaaa    | male   |   18 |    200 |  200 | 技术         |
|  5 | yyyyy    | male   |   18 |    200 |  200 | 技术         |
|  2 | bbbbb    | female |   48 |    201 |  201 | 人力资源     |
|  3 | ccccc    | male   |   38 |    201 |  201 | 人力资源     |
|  4 | xxxxx    | female |   28 |    202 |  202 | 销售         |
|  6 | zzzzz    | female |   18 |    204 | NULL | NULL         |
+----+----------+--------+------+--------+------+--------------+
6 rows in set (0.00 sec)

4.左连接（department在左）

mysql> select * from department left join employee on employee.dep_id=department.id;

+------+--------------+------+---------+--------+------+--------+
| id   | name         | id   | name    | sex    | age  | dep_id |
+------+--------------+------+---------+--------+------+--------+
|  200 | 技术         |    1 | aaaaa  | male   |   18 |    200 |
|  201 | 人力资源     |    2 | bbbbb   | female |   48 |    201 |
|  201 | 人力资源     |    3 | ccccc   | male   |   38 |    201 |
|  202 | 销售         |    4 | xxxxx   | female |   28 |    202 |
|  200 | 技术         |    5 | yyyyy   | male   |   18 |    200 |
|  203 | 运营         | NULL | NULL    | NULL   | NULL |   NULL |
+------+--------------+------+---------+--------+------+--------+
6 rows in set (0.00 sec)

5.符合条件连接查询

示例1：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25，
      即找出年龄大于25岁的员工以及员工所在的部门
select employee.name,department.name from employee inner join department
on employee.dep_id = department.id
where age > 25;

示例2：以内连接的方式查询employee和department表，并且以age字段的升序方式显示。
select employee.id,employee.name,employee.age,department.name from employee,department
where employee.dep_id = department.id
and age > 25
order by age asc;

6.子查询

子查询是将一个查询语句嵌套在另一个查询语句中。
内层查询语句的查询结果，可以为外层查询语句提供查询条件。
子查询中可以包含：in，not in，any，all，exists，not exists等关键字。

带in关键字的子查询：

查询平均年龄在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='技术');

查看不足1人的部门名：
    select name from department
    where id not in 
    (select dep_id from employee group by dep_id);

带exists关键字的子查询：

EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。而是返回一个真假值。
True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

department表中存在dept_id=203，Ture

select * from employee  where exists (select id from department where id=200);

department表中存在dept_id=205，False

select * from employee  where exists (select id from department where id=204);

还可以包含比较运算符：=，！=，>，<等。

查询大于所有人平均年龄的员工名与年龄
select name,age from employee
where age > (select avg(age) from employee);

查询大于部门内平均年龄的员工名、年龄
思路：
    (1)先对员工表(employee)中的人员分组(group by)，查询出dep_id以及平均年龄。
    (2)将查出的结果作为临时表,再对根据临时表的dep_id和employee的dep_id作为筛选条件将employee表和临时表进行内连接。
    (3)最后再将employee员工的年龄是大于平均年龄的员工名字和年龄筛选。

    select t1.name,t1.age from employee as t1
    inner join
    (select dep_id,avg(age) as avg_age from employee group by dep_id) as t2
    on t1.dep_id = t2.dep_id
    where t1.age > t2.avg_age;

mysql数据库的基本操作

一、基本增删改查

1.数据库的增删改查

2.表的增删改查

二、创建索引的几种方式

1.唯一索引：

2.唯一索引方式2:

3.联合唯一索引：

4.主键索引：

5.主键索引方式2：

6.普通索引:

三、单表查询

四、连表查询

1.认识笛卡尔积，将两张表强连接到一起

2.inner join内连接

3.左连接（employee 在左）

4.左连接（department在左）

5.符合条件连接查询

6.子查询

猜你喜欢

热点阅读