Mysql在Mac终端以及Navicat 的基本操作

1.进入MySQL：

     打开终端，输入/usr/local/MySQL/bin/mysql -u root -p ，其中，root为数据库用户名。

输入密码后（密码输入不会被显示）：

2.接下来就可以对数据库进行操作了

~创建数据库：create database 数据库名；

 

~显示当前所有数据库：show databases；

可以看到db1就在数据库列表里。

~删除数据库： drop database 数据库名；

~使用数据库：use 数据库名；

         输入use library;

~查看当前使用数据库：select database();    select database()是个函数

 可以看到当前使用的数据库是library

3.对数据库里的表进行操作

DDL-查询表

   ~查询当前数据库下的所有表名称：show tables;

 

~查询表结构： desc 表名称；（注意查的是表的结构信息，而不是数据）

Field这一列的信息是表头；

 创建表：

 

操作表-数据类型：

age int

score double(总长度，小数点后保留的位数)   如范围是0~100 ，要求小数点后保留两位

则 score double(5,2)

 删除表

     drop table 表名称；   或者： drop table if exists 表名称；

修改表

修改表名： alter table 表名 rename to 新的表名；

添加新的一列：alter table   表名 add 列名 数据类型；

修改数据类型：alter table 表名 modify 列名 新数据类型；

修改列名和数据类型：alter table 表名 change 列名 新列名 新数据类型；

删除列：alter table 表名 drop 列名；

4.DML-操作数据（对表中的数据进行操作）

   添加数据（insert）：（1）  给指定列添加数据：insert into 表名（列名1，列名2）

                                                             values（值1，值2);

                     （2）给全部列添加数据    insert into 表名 values（添加各列的值）;

                        (3)  批量添加全部列数据         insert into 表名 values（添加各列的值）,

                                                           （添加各列的值）,

                                                           （添加各列的值）;     

                                批量添加指定列的数据  insert into 表名（列名1，列名2，...）

                                                                       values(值1，值2...),

                                                                        values(值1，值2...),

                                                                        values(值1，值2...);

修改数据（update）：update 表名 set 列名1=值1，列名2=值2,...where 条件;

                         注意：若没有where条件语句，则会将表中全部数据修改！！！

删除数据（delete）：  delete from 表名 where 条件；(直接删除那一行)

                         注意：若没有where条件语句，则会将表中全部数据删除！！！

5.DQL-数据查询（重要！！！）                                                                                                                                                                                      

（1）基础查询

• 查询多个字段     select  字段列表  from  表名；      select  *  from 表名； --查询所有列（所有数据）但不要使用*，因为要求代码规范
• 
• 去除重复记录     select distinct  字段列表  from 表名；  例： select distinct score from stus;
• 起别名                AS: AS也可以省略    例：select   name , score as 数学成绩 from stus;                                                                   或者：select   name , score  数学成绩 from stus;

（2）条件查询

                 select 字段列表 from 表名 where 条件列表；

     

例1：查询年龄在20到30之间的    select * from stus where age between 20 and 30;

          注意：在SQL里，日期也是可以比较大小的。

例2：查询年龄等于20或者年龄等于18的学员信息：select * from stus where age=18 or age = 20;

        或：select * from stus where age in(18,20);

 例3：查询成绩不为空的学生：select * from stus where score is not null;      

注意：在SQL里，null的判断不能用=或者！= ，而应该用is或者is not

  例4：查询第二个字是“梅”的学生：select * from stus where  name like "_梅%";

 例5：查询名字里含“想”的学生：select * from stus where  name like "%想%";

• [ ] 可以匹配集合内的字符，例如 [ab] 将匹配字符 a 或者 b。用脱字符 ^ 可以对其进行否定，也就是不匹配集合内的字符。

例6：

SELECT *FROM mytable
WHERE col LIKE '[^AB]%'; -- 不以 A 和 B 开头的任意文本

模糊查询like中的%不限个数，可以是0个，可以是多个。  

(3)排序查询（order by）

语法：select 字段列表 from 表名 order by 排序字段名1【排序方式1】，排序字段名2【排序方式2】...

排序方式：ASC：升序排列（默认值）

                 DESC：降序排列

 注意：如果有多个排序条件，当前边的条件值一样时，才会根据第二条件进行排序

例1：查询学生信息，按年龄升序排序：select * from stus order by age;   或：select * from stus order by age asc;

例2：查询学生信息，按成绩降序排序：select * from stus order by score desc;

例3：查询学生信息，按成绩降序排列，如果成绩一样，再按年龄升序排列

         select * from stus order by score desc,age desc;

（4）聚合函数 

             a.概念：将一列数组作为一个整体，进行纵向运算。

             b.聚合函数分类：

           

           c.聚合函数语法：select  聚合函数名（列名）from 表；

                注意：null值不参与所有聚合函数运算。

          例1：统计班上一共多少个学生  select count(id) from stus;

                   注意：count统计的列名不能为null

                              主键或*（*：代表所有，只要那一行数据有一个不为空就能统计出来，且*会主                                                选择速度最快的那列统计）                                                              

                                除非必要，不能写*

          例2：查询成绩最高分：select max(score) from stus;

           例3：查询成绩平均分：select avg(score) from stus;

       

（5）分组查询（group by）：

        分组查询语法：

select 字段列表 from 表名 【where 分组条件限定】 group by 分组字段名 【having 分组后条件过滤】   

       注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无意义

having是分组后的筛选，可理解为最后一轮筛选

 例1：查询男同学和女同学各自的数学平均分   

                select gender 性别,avg(score) 平均成绩 from stus group by gender;

                

例2：查询男同学和女同学各自的数学平均分，以及各自人数。

        select gender 性别,avg(score) 平均成绩,count(id) 人数 from stus group by gender; 

        

例3：查询男同学和女同学各自的数学平均分，以及各自人数。要求：分数低于80分的不参与分组。

select gender ,avg(score) 平均成绩,count(id) 人数  from stus where score > 80 group by gender;   

例4：查询男同学和女同学各自的数学平均分，以及各自人数。要求：分数低于70分的不参与分组,分组之后人数大于两个。

 select gender 性别,avg(score) 平均成绩,count(id) 人数  from stus where score > 80 group by gender having count(id) > 2;

(6)分页查询

LIMIT：限制返回的行数。可以有两个参数，第一个参数为起始行，从 0 开始；第二个参数为返回的总行数。

语法：select 字段列表 from 表名 limit 起始索引，查询条目数。

例1：从0开始查询，查询三条数据   

   select * from stus limit 0,3;

        

例2：每页显示三条数据，查询第一页数据

   select * from stus limit 0,3;

         

 例3：每页显示三条数据，查询第二页数据

         select * from stus limit 3,3;

          

起始索引 =  （当前页面-1）*每页显示的条数

小结可以看黑马javaweb课堂p18

6.约束

（1）概念：约束是作用于表中列上的规则，用于限制加入表的数据。约束的存在保证了数据库中数据的正确性、有效性和完整性。

（2）约束的分类：

    

注意：默认约束中，没给值时才是默认值，给null就不会默认约束 

   自动增长：auto-increment   应用条件：当列是数字类型且是唯一约束。结果：就算给null也会自增。

 如非空约束：

                                

 （3）外键约束

      概念：  外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性。

      语法：

             添加约束

                 

                               constraint是约束的意思，外键名称是可选项

              建完表后添加外键约束

alter table 表名 add constraint 外键名称 forein key(外键字段名)   references 主表名称（主表列名称）

               删除约束： alter table 表名 drop forein key 外键名;             

     例：      

                            

     在该例中，dep_id作为外键去关联部门表里的主键id,员工表为从表，部门表为主表。（先创主表，再创从表，添加数据同理）

           

创建好外键约束后，主表部门表里的部门就不能随意删除了，因为和员工表里的dep_id关联了起来，要想删掉部门表里的部门，必须把从表里的该部门人员删去。

如果是建好表后再添加外键：

              alter  table emp add constraint  fk_emp_dept foreign key(dep_id) references dept(id);

都是往【从表】里添加外键