mysql语句_day05

DDL语句之分组查询:group by

1. select 字段列表 from 表名  group by 分组字段名称;

    注意事项: 
        1)查询的字段列表中可以使用 分组字段
        2)group by之后不能使用聚合函数

    -- 带条件分组查询的语法:  where 条件 必须放在group by 之前,否则语法错误!
    -- select 字段列表包含分组字段,聚合函数.. from 表名 where 条件  group by 分组字段;

2. 筛选查询 having
	where条件,group by,having  必须先有条件,分组,然后才筛选!
	注意:筛选的后面可以使用聚合函数,group by的后面是不能使用聚合函数的

分页查询 limit

select 字段列表 from 表名   limit 起始行数,每页显示的条数;
	起始行数:从0开始算的,    
	起始行数 = (当前页码-1)*每页显示的条数
	
	前提条件:每页显示2条, 
	第一页数据
		SELECT * FROM 表名 LIMIT 0,2;
	第二页数据
		SELECT * FROM 表名 LIMIT 2,2 ;
	第三页数据
		SELECT * FROM 表名 LIMIT 4,2 ;
	第四页数据
		SELECT * FROM 表名 LIMIT 6,2 ;
	第五页数据
        SELECT * FROM 表名 LIMIT 8,2;
	第六页数据
		SELECT * FROM 表名 LIMIT 10,2;
	第七页数据
		SELECT * FROM 表名 LIMIT 12,2;

数据库的备份和还原

方式1:图形界面化工具
		备份:
			选中库名,右键----->BACKUP/Export 备份导出---->
			选中Sql脚本,存储到本地某个磁盘上----->StructureAndData:表的结构和数据都需要备份
		还原:
			先在SqlYog将myee_2203删除了,新建库右键---->Import 导入---->选中执行execute sql 脚本
方式2:		
		备份:
			以管理员身份进入dos,不需要登录mysql
						  保存到指定路径上
			mysqldump -uroot -p你的密码  库名 >  磁盘上的路径xx\xx\xx.sql
		还原: 
			进入dos控制台,登录mysql
 		 	先将库删除,新建一个库
  			使用这个库 use库名;
  
 			加载指定路径的sql脚本
  			source 备份的sql脚本路径(D:\EE_2203\day9\code\mysql_01.sql)

数据库的约束

概念:限制用户操作数据库的一种行为(非法行为)
直接插入null值或者某个非业务字段id一直在重复

示例:
	创建一张表stu表
	CREATE TABLE stu(
			```
			id INT,
			NAME VARCHAR(20),
			gender VARCHAR(3) DEFAULT '男'    -- 加入默认约束
			```
	);
	INSERT INTO stu VALUES(1,'一一','女'),(2,'尔尔','男') ;
	INSERT INTO stu(id,NAME) VALUES(3,'三三') ;
	上面gender字段没有插入数据,默认就是null,空值(没有意义)
	
1)默认约束 default :如果某个字段没有插入数据,那么默认约束起作用了

    通过sql语句将默认约束去掉
        ALTER TABLE stu MODIFY gender VARCHAR(3) ;
        INSERT INTO stu(id,NAME) VALUES(4,'思思') ;

    通过sql语句给gender字段加上默认约束
    ALTER TABLE stu MODIFY gender VARCHAR(3) DEFAULT '女' ;
    DELETE FROM stu WHERE id = 4;	

2)非空约束 not null,当前这个字段值不能为null
    CREATE TABLE stu(
       id INT, -- 编号
       NAME VARCHAR(20), -- 姓名
       age INT, -- 年龄
       address  VARCHAR(50) NOT NULL-- 地址 ,加入非空约束
    ) ;
	INSERT INTO stu VALUES(1,'一一',19,'西安市'),(2,'尔尔',15,'北京市') ;

    地址插入一个null值
    	INSERT INTO stu VALUES(3,'叄三',22,NULL) ;  -- 这种直接插入null也属于非法行为
    当address字段设置not null约束,直接限定用户不能插入null值
    	Column 'address' cannot be null

	通过sql语句将非空约束去掉--修改表的类型
        ALTER TABLE stu MODIFY address VARCHAR(50) ;
        DELETE FROM stu WHERE id = 3;

	通过sql语句将非空约束加上
        ALTER TABLE stu MODIFY address VARCHAR(50) NOT NULL  ;

        DROP TABLE stu ;

3)唯一约束unique (属于一种index :索引)
    CREATE TABLE stu(
        id INT , -- 编号
        NAME VARCHAR(20), -- 姓名
        phone_number VARCHAR(11) UNIQUE -- 电话号码 必须设置唯一的
    ) ;
    INSERT INTO stu VALUES(1,'一一','13388886666'),(2,'张三','13255559999') ;
    INSERT INTO stu VALUES(3,'叄三','13388886666') ;-- 重复值 Duplicate entry '13388886666' for key 'phone_number'

	通过sql语句将唯一约束删除

		alter table stu modify phone_number varchar(11) ; -- 修改表的字段类型
	删除唯一约束的语法:atler table 表名 drop index  索引的字段名称(就当前类的字段名称一致的) ;
		ALTER TABLE stu DROP INDEX phone_number ;

	删除id为3的数据
		DELETE FROM stu WHERE id = 3 ;

	通过sql语句添加唯一约束,此时语法就和修改表的字段类型的语法一致了
		ALTER TABLE stu MODIFY phone_number VARCHAR(11) UNIQUE ;

		DROP TABLE stu ;

4)主键约束 primary key(非空且唯一的) 一般和auto_increment(自增长约束)使用多一些
    设置在id非业务字段上
    CREATE TABLE stu(
          id INT PRIMARY KEY AUTO_INCREMENT , -- 编号   加入主键约束,加入自增长
          NAME VARCHAR(20) -- 姓名
    );

    DROP TABLE stu ;
    INSERT INTO stu VALUES(1,'一一'),(2,'尔尔') ;
    INSERT INTO stu VALUES(1,'宇凡') ;-- Duplicate entry '1' for key 'PRIMARY' id是一个主键,值不能重复
    INSERT INTO stu VALUES(NULL,'琪琪') ;-- Column 'id' cannot be null
	
	通过sql将主键约束删除
		alter table 表名 drop PRIMARY  KEY ;
		ALTER TABLE stu DROP PRIMARY  KEY;

		DELETE FROM stu  WHERE NAME = '宇凡' ;

	通过sql语句将主键约束加入(唯一起作用)
		ALTER TABLE stu MODIFY id INT PRIMARY KEY  ;
	
	加入自增长约束之后,不需要给id赋值了,自己自增	
		INSERT INTO stu(NAME) VALUES('高圆圆'),('文章') ;
	自增长约束可以自己插入新的id,然后在之前id上继续自增
		INSERT INTO stu VALUES(17,'赵又廷') ;

		INSERT INTO stu(NAME) VALUES('张三') ;
	mysql有一个函数:获取最后一次自增长主键的id值
		SELECT LAST_INSERT_ID() ;

		SELECT * FROM stu ;	
	
-- 5)外键约束 foreign key
SHOW TABLES ;

DROP TABLE employee ;

描述员工信息,创建一个表,员工编号,员工姓名,员工性别,员工所在部门
    CREATE TABLE emp(

    ```
    id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
    emp_name VARCHAR(10) , -- 员工姓名
    emp_gender VARCHAR(5), -- 员工性别
    emp_dept_name VARCHAR(10) -- 员工所在的部门名称
    ```

    ) ;

    -- 插入员工表数据
    INSERT INTO emp(emp_name,emp_gender,emp_dept_name)
    VALUES('一一','男','开发部'),
    ('尔尔','女','测试部'),
    ('三三','男','运维部'),
    ('思思','女','测试部'),
    ('呜呜','男','开发部') ,
    ('溜溜','男','运维部') ;
    DROP TABLE emp;

    上面存在的问题:员工表中包含员工信息以及部门信息,这种情况不能在数据库中出现
    查询出来的数据部门信息存在冗余,重复性大,优化改进:一张表始终描述一件事情
    将员工表拆分出来:将部门信息放在部门表中

    创建一张部门表 dept表
    CREATE TABLE dept(
        id INT PRIMARY KEY AUTO_INCREMENT, -- 主键自增长约束 部门编号
        dept_name VARCHAR(20)   -- 部门名称
    ) ;
    插入三个部门
    INSERT INTO dept(dept_name) VALUES('开发部'),('测试部'),('运维部') ;

	创建员工表
    CREATE TABLE emp(
    ```
    id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
    emp_name VARCHAR(10) , -- 员工姓名
    emp_gender VARCHAR(5), -- 员工性别
    dept_id INT -- 员工的部门id
    ```
    ) ;

    INSERT INTO emp(emp_name,emp_gender,dept_id)
    VALUES('一一','男',1),
    ('尔尔','女',2),
    ('三三','男',3),
    ('思思','女',2),
    ('呜呜','男',1) ,
    ('溜溜','男',3) ;

    插入了一条员工数据
    	INSERT INTO emp(emp_name,emp_gender,dept_id) VALUES('张三丰','男',4) ;

    问题2:员工表中插入一条不存在的部门,两种表没有建立关系
    解决:外键约束 foreign key ,员工表dept_id 和部门表主键id进行关联
    外键所在的表----从表   (员工表)
    部门表: 主表 

    员工表:从表
        CREATE TABLE emp(

        ```
        id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
        emp_name VARCHAR(10) , -- 员工姓名
        emp_gender VARCHAR(5), -- 员工性别
        dept_id INT,  -- 员工的部门id
        CONSTRAINT  -- 声明 
         dept_emp_fk -- 外键名称  命名规则:主表名称_从表名称_fk
         FOREIGN KEY (dept_id)   -- 作用在这个dept_id字段上 
         REFERENCES dept(id)  -- 关联主表的主键id
        ```

        ) ;

        DROP TABLE emp ;

	删除3号部门
    加入外键约束:修改和删除是很麻烦,需要先修改或者删除从表数据,然后修改/删除主表数据
    将id为3的和id为的3清除
        DELETE FROM emp WHERE id= 3 OR id = 6 ;
        DELETE FROM dept WHERE id = 3;

        SELECT * FROM dept ;
        SELECT * FROM emp ;