• MySQL(一)


    MySQL安装

    MySQL官方下载地址

    启动MySQL服务端

    mysqld -initialize-insecure // 初始化
    // 初始化用户名root,密码空
    mysqld  // 启动服务端
    

    客户端连接

    mysql -h 192.168.16.112 -u root -p

    配置环境变量

    将mysqlin目录添加到环境变量

    启动windows服务

    b. 将MySQL服务制作成windows服务
    
    上一步解决了一些问题,但不够彻底,因为在执行【mysqd】启动MySQL服务器时,当前终端会被hang住,那么做一下设置即可解决此问题:
    
    # 制作MySQL的Windows服务,在终端执行此命令:
    "c:mysql-5.7.16-winx64inmysqld" --install
    
    # 移除MySQL的Windows服务,在终端执行此命令:
    "c:mysql-5.7.16-winx64inmysqld" --remove
    注册成服务之后,以后再启动和关闭MySQL服务时,仅需执行如下命令:
    
    # 启动MySQL服务
    net start mysql
    
    # 关闭MySQL服务
    net stop mysql
    

    MySQL连接

    文件夹=数据库
    文件=表
    数据行=行

    连接

    默认:用户root,密码空。

    创建用户

    create user 'zou1'@'192.168.16.%' indentified by '123';
    create user 'zou2'@'192.168.16.111' indentified by '123';
    create user 'zou3'@'%' indentified by '123';

    授权

    grant select,insert,update  on db1.t1 to 'alex'@'%';
    grant all privileges  on db1.t1 to 'alex'@'%';
    revoke all privileges on db1.t1 from 'alex'@'%';
    

    SQL的数据类型

    数字类型

    tinyint
    int 
    bigint
    float
    double
    decimal // 能准确保存小数值,底层采用字符串形式存储。
    

    字符串类型

    char(n):
    char数据类型用于表示固定长度的字符串,可以包含最多达255个字符。其中m代表字符串的长度。
    varchar(n):
    varchars数据类型用于变长的字符串,可以包含最多达255个字符。其中m代表该数据类型所允许保存的字符串的最大长度,只要长度小于该最大值的字符串都可以被保存在该数据类型中。
     注:虽然varchar使用起来较为灵活,但是从整个系统的性能角度来说,char数据类型的处理速度更快,有时甚至可以超出varchar处理速度的50%。因此,用户在设计数据库时应当综合考虑各方面的因素,以求达到最佳的平衡
    

    文本类型

    text:
    mediumtext:
    longtext:
    注意:文件过大应该保存在硬盘,数据库存放文件地址即可
    

    时间类型

    DATE 
        YYYY-MM-DD(1000-01-01/9999-12-31)
    TIME
        HH:MM:SS('-838:59:59'/'838:59:59')
    
    YEAR
        YYYY(1901/2155)
    
    DATETIME
         YYYY-MM-DD HH:MM:SS(1000-01-01 00:00:00/9999-12-31 23:59:59    Y)
    
    TIMESTAMP
        YYYYMMDD HHMMSS(1970-01-01 00:00:00/2037 年某时)
    

    枚举类型

    CREATE TABLE shirts (
                        name VARCHAR(40),
                        size ENUM('xs', 's', 'm', 'l', 'xl')
                    );
                    INSERT INTO shirts (name, size) VALUES ('dress shirt','l'), ('t-shirt','m'),('polo shirt','s');
    

    集合类型

    CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
                    INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
    

    基本SQL语句

    id自增

    desc t10; // 查看表的类型
    
    show create table t10; // 查看表的创建方式
    
    show create table t10 G;
    
    alter table t10 AUTO_INCREMENT=20; // 修改主键初始值
    

    创建数据库

    创建数据库
    create database db1 default charset utf8;
    查看数据库
    show databases;
    删除数据库
    drop database db1;

    创建表

    创建表

    create table t1(
                    id int auto_increment primary key,
                    name char(10) not null,
                    age int null,
                    size ENUM("xs","s,"m","l","xl"),
                    col SET("a","b","c","d","e")
                    )engine=innodb charset=utf8
    

    查看表
    show tables;
    清空表
    truncate table t1;
    delete from t1;
    删除表
    drop table t1;

    表增删改查

    insert into t1(name,age) values("zou","23");
    insert into tb11(name,age); values('alex',12),('root',18);
    insert into tb12(name,age) select name,age from tb11;
    

    delete from t1 where id=1;
    delete from tb12;
    delete from tb12 where id !=2 ;
    delete from tb12 where id =2 ;
    delete from tb12 where id > 2 ;
    delete from tb12 where id >=2 ;
    delete from tb12 where id >=2 or name='alex';
    


    update t1 set age="18" where id=4;

    select * from t1;
    select * from tb12 where id in (1,5,12);
    select * from tb12 where id not in (1,5,12);
    select * from tb12 where id in (select id from tb11)
    select * from tb12 where id between 5 and 12;
    

    通配符

    通配符:
    				
    select * from tb12 where name like "a%"
    select * from tb12 where name like "a_"
    

    连表查询

    -- 上下连表union和union all
    
    select sid,sname from student UNION select id,tname from teacher;
    
    
    select * from userinfo5,department5 where userinfo5.part_id = department5.id;
    
    select * from userinfo5 left join department5 on userinfo5.part_id = department5.id
    
    select * from department5 left join userinfo5 on userinfo5.part_id = department5.id
    # userinfo5左边全部显示
    
    # select * from userinfo5 right join department5 on userinfo5.part_id = department5.id
    # department5右边全部显示
    
    

    case语句查询

    外键和唯一索引

    唯一索引

    唯一索引和主键的区别,唯一索引可以有一个为空,主键不能为空。

    unique uq_user_host (userid,hostid),
    
    # 注意,外键和所关联的键数据类型必须一致
    # 且必须先创建被关联的表
    create table department(
    	id int auto_increment primary key,
    	title char(15)
    )engine=innodb default charset=utf8;
    
    
    create table userinfo(
    	uid int auto_increment primary key,
    	name varchar(32),
    	department_id int,
    	xx_id int,
    	constraint fk_user_depar foreign key (department_id) references department(id)
    )engine=innodb default charset=utf8;
    

    外键多对多

    // 用户表
    create table userinfo2(
    	id int auto_increment primary key,
    	name char(10),
    	gender char(10),
    	email varchar(64)
    )engine=innodb default charset=utf8;
    
    
    //主机表
    create table host(
    	id int auto_increment primary key,
    	hostname char(64)
    )engine=innodb default charset=utf8;
    
    
    //用户主机关联表
    create table user2host(
    	id int auto_increment primary key,
    	userid int not null,
    	hostid int not null,
    	unique uq_user_host (userid,hostid),
    	CONSTRAINT fk_u2h_user FOREIGN key (userid) REFERENCES userinfo2(id),
    	CONSTRAINT fk_u2h_host FOREIGN key (hostid) REFERENCES host(id)
    )engine=innodb default charset=utf8;
    

    MySQL查询练习

    查询练习题
    答案

  • 相关阅读:
    541. Reverse String II
    540. Single Element in a Sorted Array
    初识python
    openCV
    openCV
    win环境下安装配置openCV-4.3.0
    力扣Leetcode 198. 打家劫舍
    力扣Leetcode 680. 验证回文字符串 Ⅱ
    力扣Leetcode 560. 和为K的子数组
    华师2019软件专硕复试机试题最后一题G:找数
  • 原文地址:https://www.cnblogs.com/zouruncheng/p/7081272.html
Copyright © 2020-2023  润新知