MySQL基础

Wesley13
• 阅读 689
-- *************一、mysql基础********************----
1.1 管理数据库
    增加: create database 数据库 default character utf8;
    删除: drop database 数据库;
    修改: alter database 数据库 default character gbk;
    查询: show databases / show create database 数据库;

1.2 管理表:    
     选择数据库:use 数据库;
     增加: create table 表(字段名1 字段类型,字段名2 字段类型......);
     删除: drop table 表;
     修改:        
        添加字段: alter table 表 add [column] 字段名 字段类型;         
        删除字段:   alter table 表 drop [column] 字段名;
        修改字段类型: alter table 表 modify 字段名 新的字段类型;
        修改字段名称 : alter table 表 change 旧字段名 新字段名 字段类型;
        修改表名称:   alter table 表 rename [to] 新表名;
    查询: 
        show tables  /  desc student;

1.3 管理数据:
      增加: insert into 表(字段1,字段2,。。。) values(值1,值2.。。。。);
      删除: delete from 表 where 条件;
      修改: update 表 set 字段1=值1,字段2=值2...... where 条件;
      查询: 
        所有字段: select * from 表;
        指定字段: select 字段1,字段2.... from 表;
        指定别名: select 字段1 as 别名 from 表;
        合并列: select (字段1+字段2) from 表;
        去重: select distinct 字段 from 表;
        条件查询:
            a)逻辑条件 :and(与)     or(或)
                select * from 表 where 条件1 and/or 条件2
            b)比较条件: >  <  >=  <=  =  <>   between and(在。。。之间)
                select * from 表 where servlet>=90;
            c)判空条件: 
                判断null: is null   /  is not null
                判断空字符串: =''    /  <>''
            d)模糊条件: like 
                %:  替换任意个字符
                _:   替换一个字符            
        分页查询:limit 起始行,查询行数
                起始行从0开始
        排序: order by 字段 asc/desc
                asc: 正序,顺序
                desc:反序,倒序
        分组查询:group by 字段
        分组后筛选: having 条件

-- *************二、SQL语句的分类********************----
SQL语句的分类:
    DDL: 数据定义语言
        create / drop / alter 
    DML:数据操作语句
        insert / delete /update / truncate
    DQL: 数据查询语言:
        select / show

-- *************三、数据约束********************----
-- 3.1 默认值
CREATE TABLE student(
    id INT,
    NAME VARCHAR(20),
    address VARCHAR(20) DEFAULT '广州天河'  -- 默认值
)

-- 3.2 非空
-- 需求: gender字段必须有值(不为null)
CREATE TABLE student(
    id INT,
    NAME VARCHAR(20),
    gender VARCHAR(2) NOT NULL -- 非空
)

-- 3.3 唯一
CREATE TABLE student(
    id INT UNIQUE, -- 唯一
    NAME VARCHAR(20)
)

-- 3.4 主键(非空+唯一)
CREATE TABLE student(
    id INT PRIMARY KEY, -- 主键
    NAME VARCHAR(20)
)

-- 3.5 自增长
CREATE TABLE student(
    id INT(4) ZEROFILL PRIMARY KEY AUTO_INCREMENT, -- 自增长,从0开始  ZEROFILL 零填充
    NAME VARCHAR(20)
)

-- 不能影响自增长约束
DELETE FROM student;
-- 可以影响自增长约束
TRUNCATE TABLE student;

-- 3.6 外键约束
-- 员工表
CREATE TABLE employee(
    id INT PRIMARY KEY,
    empName VARCHAR(20),
    deptName VARCHAR(20) -- 部门名称
)

-- 添加员工,部门名称的数据冗余高
INSERT INTO employee VALUES(1,'张三','软件开发部');
INSERT INTO employee VALUES(2,'李四','软件开发部');
INSERT INTO employee VALUES(3,'王五','应用维护部');
INSERT INTO employee VALUES(4,'陈六','软件开发部');

-- 解决数据冗余高的问题:给冗余的字段放到一张独立表中
-- 独立设计一张部门表
CREATE TABLE dept(
    id INT PRIMARY KEY,
    deptName VARCHAR(20)
)

DROP TABLE employee;
-- 修改员工表
CREATE TABLE employee(
    id INT PRIMARY KEY,
    empName VARCHAR(20),
    deptId INT,-- 把部门名称改为部门ID
    -- 声明一个外键约束
    CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE  -- ON UPDATE CASCADE:级联修改
    --           外键名称                  外键               参考表(参考字段)
)

INSERT INTO dept(id,deptName) VALUES(1,'软件开发部');
INSERT INTO dept(id,deptName) VALUES(2,'应用维护部');
INSERT INTO dept(id,deptName) VALUES(3,'秘书部');

INSERT INTO employee VALUES(1,'张三',1);
INSERT INTO employee VALUES(2,'李四',1);
INSERT INTO employee VALUES(3,'王五',2);
INSERT INTO employee VALUES(4,'陈六',3);

-- 问题: 该记录业务上不合法,员工插入了一个不存在的部门数据
INSERT INTO employee VALUES(5,'陈六',4); -- 违反外键约束: Cannot add or update a child row: a foreign key constraint fails (`day16`.`employee`, CONSTRAINT `emlyee_dept_fk` FOREIGN KEY (`deptId`) REFERENCES `dept` (`id`))

-- 1)当有了外键约束,添加数据的顺序: 先添加主表,再添加副表数据
-- 2)当有了外键约束,修改数据的顺序: 先修改副表,再修改主表数据
-- 3)当有了外键约束,删除数据的顺序: 先删除副表,再删除主表数据
-- 修改部门(不能直接修改主表)
UPDATE dept SET id=4 WHERE id=3;
-- 先修改员工表
UPDATE employee SET deptId=2 WHERE id=4;
 
-- 删除部门
DELETE FROM dept WHERE id=2;
-- 先删除员工表
DELETE FROM employee WHERE deptId=2;

-- 级联修改(修改)
-- 直接修改部门
UPDATE dept SET id=5 WHERE id=4;

-- 级联删除
-- 直接删除部门 
DELETE FROM dept WHERE id=1;

-- **************四、存储过程*******************-
什么是存储过程
    存储过程,带有逻辑的sql语句
存储过程特点
    1)执行效率非常快!存储过程是在数据库的服务器端执行的!!!
    2)移植性很差!不同数据库的存储过程是不能移植。
存储过程语法
-- 声明结束符
-- 创建存储过程
DELIMITER $
CREATE PROCEDURE pro_test()
BEGIN
    -- 可以写多个sql语句;
    SELECT * FROM employee;
END $

-- 执行存储过程
CALL pro_test();

-- 4.1 带有输入参数的存储过程
-- 需求:传入一个员工的id,查询员工信息
DELIMITER $
CREATE PROCEDURE pro_findById(IN eid INT)  -- IN: 输入参数
BEGIN
    SELECT * FROM employee WHERE id=eid;
END $ 

-- 调用
CALL pro_findById(4);

-- 4.2 带有输出参数的存储过程
DELIMITER $
CREATE PROCEDURE pro_testOut(OUT str VARCHAR(20))  -- OUT:输出参数
BEGIN
        -- 给参数赋值
    SET str='helljava';
END $

-- 删除存储过程
DROP PROCEDURE pro_testOut;
-- 调用
-- 如何接受返回参数的值??
-- ***mysql的变量******
--  全局变量(内置变量):mysql数据库内置的变量 (所有连接都起作用)
        -- 查看所有全局变量: show variables
        -- 查看某个全局变量: select @@变量名
        -- 修改全局变量: set 变量名=新值
        -- character_set_client: mysql服务器的接收数据的编码
        -- character_set_results:mysql服务器输出数据的编码
        
--  会话变量: 只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开,那么会话变量全部丢失!
        -- 定义会话变量: set @变量=值
        -- 查看会话变量: select @变量
        
-- 局部变量: 在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕,局部变量就丢失!!

-- 1)定义一个会话变量name, 2)使用name会话变量接收存储过程的返回值
CALL pro_testOut(@NAME);
-- 查看变量值
SELECT @NAME;

-- 4.3 带有输入输出参数的存储过程
DELIMITER $
CREATE PROCEDURE pro_testInOut(INOUT n INT)  -- INOUT: 输入输出参数
BEGIN
   -- 查看变量
   SELECT n;
   SET n =500;
END $

-- 调用
SET @n=10;
CALL pro_testInOut(@n);
SELECT @n;

-- 4.4 带有条件判断的存储过程
-- 需求:输入一个整数,如果1,则返回“星期一”,如果2,返回“星期二”,如果3,返回“星期三”。其他数字,返回“错误输入”;
DELIMITER $
CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(20))
BEGIN
    IF num=1 THEN
        SET str='星期一';
    ELSEIF num=2 THEN
        SET str='星期二';
    ELSEIF num=3 THEN
        SET str='星期三';
    ELSE
        SET str='输入错误';
    END IF;
END $

CALL pro_testIf(4,@str);

SELECT @str;

-- 4.5 带有循环功能的存储过程
-- 需求: 输入一个整数,求和。例如,输入100,统计1-100的和
DELIMITER $
CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)
BEGIN
    -- 定义一个局部变量
    DECLARE i INT DEFAULT 1;
    DECLARE vsum INT DEFAULT 0;
    WHILE i<=num DO
          SET vsum = vsum+i;
          SET i=i+1;
    END WHILE;
    SET result=vsum;
END $

DROP PROCEDURE pro_testWhile;

CALL pro_testWhile(100,@result);

SELECT @result;

-- 4.6 使用查询的结果赋值给变量(INTO)
DELIMITER $
CREATE PROCEDURE pro_findById2(IN eid INT,OUT vname VARCHAR(20) )
BEGIN
    SELECT empName INTO vname FROM employee WHERE id=eid;
END $

CALL pro_findById2(1,@NAME);

SELECT @NAME;

-- 练习: 编写一个存储过程 
    如果学生的英语平均分小于等于70分,则输出'一般'
    如果学生的英语平均分大于70分,且小于等于90分,则输出‘良好’
    如果学生的英语平均分大于90分,则输出‘优秀’
    
DELIMITER $
CREATE PROCEDURE pro_testAvg(OUT str VARCHAR(20))
BEGIN 
      -- 定义局部变量,接收平均分
      DECLARE savg DOUBLE;
      -- 计算英语平方分
      SELECT AVG(english) INTO savg FROM student2;
      IF savg<=70 THEN
           SET str='一般';
      ELSEIF savg>70 AND savg<=90 THEN
           SET str='良好';
      ELSE
       SET str='优秀';
      END IF;
END $

CALL pro_testAvg(@str);

SELECT @str;

-- ************五、触发器*****************
触发器作用
    当操作了某张表时,希望同时触发一些动作/行为,可以使用触发器完成!!
SELECT * FROM employee;

-- 日志表
CREATE TABLE test_log(
    id INT PRIMARY KEY AUTO_INCREMENT,
    content VARCHAR(100)
)

-- 需求: 当向员工表插入一条记录时,希望mysql自动同时往日志表插入数据
-- 创建触发器(添加)
CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROW    -- 当往员工表插入一条记录时
     INSERT INTO test_log(content) VALUES('员工表插入了一条记录');

-- 创建触发器(修改)
CREATE TRIGGER tri_empUpd AFTER UPDATE ON employee FOR EACH ROW    -- 当往员工表修改一条记录时
     INSERT INTO test_log(content) VALUES('员工表修改了一条记录');

-- 创建触发器(删除)
CREATE TRIGGER tri_empDel AFTER DELETE ON employee FOR EACH ROW    -- 当往员工表删除一条记录时
     INSERT INTO test_log(content) VALUES('员工表删除了一条记录');

 -- ***********六、mysql权限问题****************
 -- mysql数据库权限问题:root :拥有所有权限(可以干任何事情)
 -- 权限账户,只拥有部分权限(CURD)例如,只能操作某个数据库的某张表
 -- 如何修改mysql的用户密码?
 -- password: md5加密函数(单向加密)
 SELECT PASSWORD('root'); -- *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B
 
--  mysql数据库,用户配置 : user表
USE mysql;

SELECT * FROM USER;

-- 修改密码
UPDATE USER SET PASSWORD=PASSWORD('123456') WHERE USER='root';

-- 分配权限账户
GRANT 权限 ON 数据库.表 TO '账户名'@'账户类型' IDENTIFIED BY '密码';
GRANT SELECT ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';
GRANT DELETE ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';

-- ****** 七,mysql备份和还原********
备份
    mysqldump -uroot -p demo > c:/bak.sql
恢复
    mysql -uroot -p demo < d:/back.sql
注意:不需要登陆
点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Stella981 Stella981
3年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Wesley13 Wesley13
3年前
MySQL基础(一)
一、连接MySQL数据库1连接:2mysqlhhostuuserp34常见错误:5ERROR2002(HY000):Can'tconnecttolocalMySQLserverthroughsocket'/tmp/my
Wesley13 Wesley13
3年前
MYSQL基础知识小盲区
MYSQL必会的知识新学的MySQL小技能:show PROCESSLIST; 查看当前数据库的进程(也就是正在进行的sql操作)!(https://oscimg.oschina.net/oscnet/c7908995a15dcf322850eb4ba0416cc34b1.png)kil
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Stella981 Stella981
3年前
Android蓝牙连接汽车OBD设备
//设备连接public class BluetoothConnect implements Runnable {    private static final UUID CONNECT_UUID  UUID.fromString("0000110100001000800000805F9B34FB");
Wesley13 Wesley13
3年前
thinkphp 基本配置
12returnarray(34//定义数据库连接信息5'DB\_TYPE''mysql',//指定数据库是mysql67'DB\_HOST''localhost',89'DB\_NAME''uchome',//数据库名1011'DB\_USER''root
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究