2021-04-21

简单说说数据库中那些最基础必须掌握的一些语句

第一部分——创建/删除/修改

create
            1.创建用户
              create user 用户名 
            2.创建数据库
              create database 数据库名
            3.创建表格
              create table 表名();
drop
            1.删除用户
              drop user 用户名
            2.删除数据库
              drop database 数据库名 
            3.删除表格
              drop table 表名

alter
            1.修改表格添加约束
              alter table 表名 add
            2.修改表格删除约束
              alter table 表名 drop
truncate
            1.删除表格的数据
              truncate table 表名
rename
            1.修改表名
              rename old to new

第一部分具体实现（例子）

-- 创建用户
create user 'ppp'@'%'
    identified by '123456';
-- 赋予权限
grant all on *.* to 'ppp'@'%';
    
-- 删除用户
drop user 'ppp'@'%';

-- 创建数据库
create database PRACTICE;

-- 删除数据库
drop database PRACTICE;

-- 使用数据库
use PRACTICE;

-- 创建数据库表
/*
create table 表明(
    字段名  数据类型  约束
    ...
);
*/
CREATE TABLE EMP(
    -- 字段  数据类型  [约束]
    empno INT PRIMARY KEY,    -- 主键约束
    ename VARCHAR(50) NOT NULL,        -- 非空约束
    job   VARCHAR(50),
    hiredate DATE,
    salary DOUBLE,
    bonus INT,    -- 奖金
    superleader INT,    -- 上级领导
    deptno INT,        -- 部门
    age INT CHECK (age>0 and age<=100),        -- 检查约束
    gender ENUM('男','女') NOT NULL,    -- 非空约束  （还要设置编码格式）
    idcard VARCHAR(18) UNIQUE        -- 唯一约束
)engine=InnoDB default charset=utf8;  -- 设置编码格式

-- drop删除表格:删除表的结构，整张表全部删除，表不在
drop table EMP;

-- truncate删除表格:删除整张表的所有数据！表结构不删除，表还在
truncate table EMP;

-- 修改表名rename
rename table emp to employee;

第二部分——约束

-- 创建部门表（未添加任何约束！）
create table dept(
    deptno int,
    dname varchar(100),
    loc varchar(100)
);

-- 在创建好表格之后再给表格添加约束
-- 修改表格添加主键约束
alter table dept add constraint pk_deptno primary key(deptno);

-- 修改删除表格dept中的主键约束
alter table dept drop primary key;

-- 添加唯一约束
alter table dept add constraint uk_dname unique key(dname);

-- 删除唯一约束
alter table dept drop index uk_dname;

-- 非空约束只能在创建表格的时候进行添加

-- 添加表格的外键约束
alter table emp add constraint fk_deptno
        foreign key(deptno) references dept(deptno);

-- 删除表格的外键约束
alter table emp drop foreign key fk_deptno;

第三部分——查询

-- 一般会遇到以下一些连接符

/*
>    >=    <    <=    =     <>     !=
and连接两个条件    or满足条件之一
between...and
is null     is not null
in
like 模糊查询   %代表任意个长度的任意字符  _代表一个任意字符

AS：取别名
*/

-- 查询结构
/*
    select <列名>
    from <表名>
    [where <查询条件表达式>]
    [order by <排序的列名>[asc或desc]]
*/

第三部分具体实现（例子）

use practice;         使用数据库

-- 我们可以再workbench或者sqlyog里面将刚刚的employee表中的empno设置为自动增长ai（auto-increment）

-- 现在可以添加其中某些字段的值，这个值注意根据约束和数据类型

insert into employee(empno, ename, job, hiredate, salary, bonus, deptno, age, gender, idcard)
                values(1, 'Jack', '软件设计师', '2020-04-21', 5000, null, 1, 20, '男', '360782200001010102');

insert into employee(empno, ename, job, hiredate, salary, bonus, deptno, age, gender, idcard)
                values(2, 'rose', 'manager', '2020-04-921', 3000, 1500, 1, 20, '女', '360782200001010101');
    
-- 未设置empno，则empno自动增长时
insert into employee(ename, job, hiredate, salary, bonus, deptno, age, gender, idcard)
                values('jannet', '开发人员', '2020-04-21', 4000, null, 1, 20, '女', '360782200001010103');

insert into employee(ename, job, hiredate, salary, bonus, deptno, age, gender, idcard)
                values('bob', 'manager', '2020-04-21', 5000, null, 1, 20, '男', '360782200001010104');

-- 给员工部门表所有字段添加值
-- 注意字段的顺序和字段数据类型，约束

-- 刚刚创建practice数据库的时候忘记指定编码格式了，这里加一条
alter database practice character set utf8;
insert into dept value(10, '项目经理', '广东省广州市');

-- 查询所有员工的编号，名字，工资信息
select empno, ename, salary from employee;

-- 查询员工所有信息
select * from employee;

-- 查询年龄大于16岁的所有员工
select * from employee where age>16;

-- 查询性别为男的所有员工信息
select * from employee where gender = '男';

-- 多个条件，查询年龄小于等于26的男性员工信息
select * from employee where age<=26 and gender = '男';

-- 查询工资在3000-8000之间的女性员工信息
select * from employee where (salary between 3000 and 8000) and gender = '女';

-- 查询奖金为空的员工(0不是null！！！)
select * from employee where bonus is null;

-- 查询奖金不为空的员工(0不是null！！！)
select * from employee where bonus is not null;

-- 查询员工工资为1000,3000,5000的员工信息
select * from employee where salary in (1000, 3000, 5000);

-- 也可以用or替代，但是效率会降低，所以一般情况下不使用or来写sql语句--提高sql效率
select * from employee where salary =1000 or salary =3000 or salary =5000;

-- 查询所有员工名字里面带o字母的
select * from employee where ename like '%o%';

-- 查询所有员工名字里面第三个字母是c的员工信息
select * from employee where ename like '__c%';

-- 别名的使用

select salary, bonus from employee;

select salary as '工资', bonus as '奖金' from employee;

-- 而且别名可以省略as，别名可中文可英文
select salary '工资', bonus as '奖金' from employee;

-- order by   asc升序排序（可以省略，默认升序）/  desc降序

-- 按工资升序排序
select * from employee order by salary asc;

-- 工资降序排序，如果工资相同再按名字的升序排序
select * from employee order by salary desc,ename;

-- 工资降序排序，如果工资相同再按名字的降序排序
select * from employee order by salary desc,ename desc;

第四部分——更新

-- 更新结构
/*
    update 表名
    set 属性1=***, 属性2=***
    [where <查询条件表达式>]
*/

-- 在mysql在safe-updates模式中，如果你where后跟的条件不是主键id，那么就会出现一种错误
-- Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.  To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
-- 如果不信的话可以试着尝试一下下面这条语句
update employee set bonus=200, age=21 where ename='jack';
-- 解决办法有三种
-- 1、先执行这条命令更改mysql数据库模式。  SET SQL_SAFE_UPDATES = 0;
-- 2、workbench的edit->preferences->SQL Editor去掉safe updates的勾勾后重启workbench；

-- 3、在where判断条件中跟上主键id    
-- 例如下面这句
update employee set bonus=200, age=21 where ename='jack' and empno>=0;
select * from employee;