04-mysql基本操作

数据库(db)

• 数据库(db):存储数据的仓库。数据库将数据存储在磁盘
• 数据库服务器：是指用来运行数据库服务的一台电脑。
• 数据库管理系统(dbms)：专门用来操作和管理数据库的系统。
• 数据库管理系统的分类：
  • 关系型数据库：MySql,Oracle,Sql Server,db2…
  • 非关系型数据库:redis,MongoDB,NOSQL…
• 数据管理员（dba）：管理数据库。
• 数据库的组成：数据表，对象，对象之间的关系组成。
• 数据表：按照一定规则和数据类型来存储数据的表格。
• 行=记录=实体：数据表中的一行数据。
• 列=字段=域：数据表中的列

MySQL

• MySQL：前Sun公司推出的一个数据库管理系统，现属于Oracle。开源，体积小，性能好

• MySQL5.5及之前的版本免费，之后也要收费。MySQl默认用的是InnoDB存储引擎。

• 使用MySQl之前一定要启动服务：

• 手动启动服务：打开任务管理器-》服务-》MySQL-》启动服

• 用命令启动/关闭服务：以管理员身份打开cmd->net start/stop mysql。

登录MySql客户端：

• 直接打开MySql客户端，输入密码。
• 用命令登录：
• 进行MySQL安装目录的bin目录下-》mysql -u root -p 密码
• 在环境变量的path中配置MySQL安装目录的bin目录-》mysql -u root -p 密码
• 用第三方客户端登录：Navicat for MySQL登录。创建连接-》输入用户名和密码

SQL：结构化查询语言。

专门用来对数据库进行操作的语言。后缀名：.sql

• sql语言的组成：
  • DDL（数据库定义语言）
  • DML（数据库操作语言）
  • DCL（数据库控制语言）
  • TCL(事务控制语言)

DDL：数据库定义语言。

数据定义语言(DDL ，Data Defintion Language)语句：
数据定义语句，用于定义不同的数据对象、数据库、表、列、索引等。常用的语句关键字包括create、drop、alter等

• 查看数据库：show databases;
• 创建数据库：create database 数据库名 [default charset utf8];
• 查看数据库的创建语句：show create database 数据库名；
• 删除数据库的语句：drop database 数据库名；
• 查看数据库信息 进入数据库之后 输入 Status ;
• 进入数据库：use 数据库名；
• 创建表：
  • 创建表的操作属于DDL(数据库定义语言)操作，所以是由名要求的，对于表名称以及列名称的定义要求如下：
    • 必须字母开头
    • 长度为1~30个字符
    • 对于同一用户不能用相同的表名称
    • 不能使用保留字

     create table 表名(
     	列名1 数据类型 列的特征(primary key,null,not null,unique key...)，
     	列名2 数据类型 列的特征，
     	......
     );
  

• 查看当前数据库中所有的表：show tables;
• 查看表的创建语句：show create table 表名；
• 查看表的结构：desc 表名；
• 修改表名：alter table 原表名 rename 新表名；
• 修改表中的字段的类型：alter table 表名 modify 字段名 新数据类型；
• 增加表字段：alter table 表名 add column 字段名 类型;
• 删除表字段：alter table 表名 drop column 字段名;
• 修改字段名：alter table 表名 change 字段原名 字段新名 字段类型;
• 删除表：drop table 表名；

序号	类型	描述
----1	CHAR(长度)	表示字符串类型, 数字
2	VARCHAR(长度)	可以存放数字，字母
3	INT(长度)	表示INT类型
4	DATE	表示日期类型(不包含时分秒)
5	datetime	表示时间类型(包含时分秒)
6	TEXT	放大文本字符串
7	FLOAT	浮点类型
8	BLOB	用于存放二进制文件,例如图片,电影,音乐

char与varchar后面接的数据大小为存储的字符数，而不是字节数

char于varchar的区别
char定义的是固定长度，长度范围为0-255，存储时，如果字符数没有达到定义的位数，会在后面用空格补全存入数据库中

varchar是变长长度，长度范围为0-65535，存储时，如果字符没有达到定义的位数，也不会在后面补空格

DML（重点）：数据库的控制语言。

数据操作语言(DML ， Data Manipulation Language)语句：
数据操作语句，用于添加、删除、更新和查询数据库记录，并检查数据的完整性。常用的语句关键字主要包括insert、delete、update和select等。（DML:添加，修改，删除，DQL:查询）

• 向表中插入数据:
  • 表中所有字段必须都插入数据：insert into 表名 values（值列表）；
  • 写入的列一定要插入数据，表中允许为null列或者有默认值的列可以不插入数据：
  • insert into 表名(列1,列2…) values(值1，值2…);
  • 值列表和列名列表要一一匹配（个数一样，位置一样，类型也要一样）。

  eg:insert into student2 values(1,'aa',18,'1',default,null);
  	insert into student2(sid,sname,sage,ssex) values(1,'bb',22,'0');
  

• 修改表中的数据：记得要写修改条件，如果不写默认修改整张表。
  • update 表名 set 列名1=值1，列名2=值2…where 条件;

  eg: update from student2 set sname='cc' where sid=2;
  

• 删除表中的数据：记得写条件，不写条件，整张表中的数据全删除了。
  sql delete from 表名 where 条件；
  • 注意：删除整张表：
    • drop table 表名; 删除表同时与表相关的所有东西都删除；删除最彻 底。
    • truncate table 表名;只删除表中数据，表中自增列也要从头开始， 一页一页删除；删除效率最高。
    • delete from 表名;只删除表中数据，表中自增列接着，一条一条删 除；
• 查询表中数据:聚合列可以在select,group by,having,order by后面用。

select 列名列表
	  from 表名
	  [where 条件   （边查边筛选）]
	  [group by 分组列]
	  [having 条件   （查询完后分组之后再筛选,有Having一定有group by）]
	  [order by 排序列asc/desc；
	  （排序的列可以是多个，先以第一个列排序，当第一个列的值相同时，才会以第二个列排序）]

eg: select * from student where sid>1004;
		select count(sid),cid from student group by cid 
			order by count(sid) asc;

• 栗子

  • 查询学生人数大于2个人的班级编号和班级总人数。-》查学生表，查班级编号 和班级总人数，条件班级人数大于2

  eg:select count(sid),cid from student group by cid 
  		order by count(sid) asc;
  

  • 查询表中所有的列：select * from 表名；（*代表表中所有的列，模 糊匹配）

  eg:select * from student;
  

  • 聚合函数：

    • count()求总记录数，
    • sum()求和，max()求最大值，
    • min()求最小值，
    • avg()求平均值。

  • 聚合函数一般不可以单独和普通列一起查询，除非普通列作为分组条件。

    eg:select count(sid) as 'count' from student;
    
    	select count(sid),sum(sid),avg(sid),max(sid),min(sid) 
    	from student;
    

  • 去除查询结果集中重复的值（MySql数据库支持）

    • select distinct 列名列表 from 表名；

    eg:select distinct sname,sage from student3;
    

  • 模糊查询：

    • 根据列值(字符类型)部分信息来查询：
      • %通配任意长度的字符，
      • _ 通配任意一个长度的字符，[a-zA-Z]通中括号中任意一个字符。

        select 列名列表 from 表名 where 列名 like '王%';
        eg:select * from student where sname like '%a%';
        

    • 根据数值类型的列查询在一个范围内的所有信息：

      between ...and...
      

      查询学号在1002到1004之间的所有的学生信息
      				eg:select * from student where sid between 1002 and 1004;
      

    • 根据列等于范围内任意一个值来查询 in

      • 查询学号等于1001或者学号等于1004或者学号等于1006的学

        eg:select * from student where sid in(1001,1004,1006);
        

  • 表连接查询:前提：表与表有相同的列，可以作为表之间连接的桥梁。

    • 内连接：表与表之间是平等-

      • 语法1：select 列名列表

        					   from 表1，表2,表3
        					   where 表1中的列=表2中的列 and 表2中的列=表3中的列;
        

        ```sql
          eg:select sid,sname,sage,student.cid,cname 
          					from student,class1 where student.cid=class1.cid;
          						select s.sid,s.sname,sage,s.cid,c.cname 
          						from student s,class1 c where s.cid=c.cid;  
          ```
        

      • (推荐)语法2：select 列名列表

         	   from 表1
         	   inner join 表2 on 表1中的列=表2中的列
         	   inner join 表3 on 表3中的列=表2中的列
      

      eg:select s.sid,s.sname,sage,s.cid,c.cname 
      						from student s
      							inner join class1 c on s.cid=c.cid;
      

    • 外连接：表与表之间有主次之分，主表中的数据全显示，次表中的数据有与主表中对应的数据就显示，没有对应数据空值显示。

      • 左外连接：以左边的表为主；

        语法：select 列名列表 
        					   from 表1
        					   left join 表2 on 表1中的列=表2中的列
        

        //查询所有班级的学生-》以班级表为主
        				eg: select s.sid,s.sname,sage,s.cid,c.cname 
        					from class1 c
        				    	left join student s on c.cid=s.cid;
        				//查询所有的学生信息和对应的班级-》以学生表为主，
        				eg:select s.sid,s.sname,sage,s.cid,c.cname 
        					from student s
        					    left join class1 c on c.cid=s.cid;
        

      • 右外连接：以右边的表为主；

        语法：select 列名列表 
        					   from 表1
        					   right join 表2 on 表1中的列=表2中的列
        				//查询所有班级的学生-》以班级表为主
        

        eg: select s.sid,s.sname,sage,s.cid,c.cname 
        					from student s
        						right join class1 c on c.cid=s.cid;
        				//查询所有的学生信息和对应的班级-》以学生表为主，
        					select s.sid,s.sname,sage,s.cid,c.cname 
        					from class1 c
        						right join student s on s.cid=c.cid;
        

  • 子查询：一个查询语句中嵌套另一个完整查询语句。

    • 能用子查询解决的问题，都可以用连表查询解决；
    • 能用连表查询的问题，不一定能用子查询解决；
    • 解决同一个问题一般子查询的效率要高于边表查询，除非子 查询中用了模糊查询。
      • 子查询：由里到外执行。
        -子查询可以作为条件，还可以作为临时表。

        //查询Java1901班的所有学生信息。
        	select sid,sname,sage from student s
        	inner join class1 c on c.cid=s.cid
        	where c.cname='java1901';
        //用子查询分两步：先查询java1901班级名称所对应的班级编号，
        再根据班级编号查询所有的学生信息。
        	select sid,sname,sage from student
        	where cid=(select cid from class1 where cname='java1901');
        

  • 分页查询（MySql）：

    • select 列名列表 from 表名 limit 当前页起始记录数，显示的条数；
    • MySql分页的总语法：
      -select 列名列表 from 表名 limit (页码-1)*显示的条数，显示的条数；
      • eg: select sid,sname,sage from student limit 0,2;

DCL：数据库的控制语言：

数据控制语言(DCL， Data Control Language)语句：
数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括grant、revoke等。

• 创建用户：create user 用户名[@IP地址] identified by 密码；

eg:create user 'sx' identified by '321';

• 给用户授权：grant 权限 on 数据库.表名 to 用户名@IP地址 identified by 密码；

eg:grant all on school.student to 'sx'@localhost identified by '321';

• 给用户撤消权限：revoke 权限 on 数据库.表名 from 用户名@IP地址;

eg: revoke all on school.student from 'sx'@localhost;

• 数据库备份：以管理员的身份进入cmd->进入SQL/bin->

mysqldump -u 用户名 -p 数据库>路径名+文件名.sql
			eg:mysqldump -u root -p user>D:user.sql

• 恢复数据库的备份：创建数据库-》进入数据库-》source 路径名+文件名.sql;

eg:source D:school.sql;