mysql基本语法

MySQL`基本语法总结

    #进入与退出数据库
    mysql -uroot -ppassword;
    exit/\q
    #显示所有库
    show databases;
    #创建库
    create database db_name;
    #删除库
    drop database db_name;
    #使用数据库
    use db_name;
    #显示所有表
    show tables;
    #创建表
    create table tb_name(create_difinition.....);
    #显示建表信息
    desc tb_name;
    #删除表
    drop table tb_name;
    #指定列插入
    insert into tb_name(column_name) values(column_values);
    #全列插入
    insert into tb_name values(all_values);
    #多行插入
    insert into tb_name(column_names) values(value_1),(value_2).....
    #指定列查询
    select column_name from tb_name;
    #全列查询
    select * from tb_name;
    #带条件查询
    select * from tb_name where conditions;
    #修改数据
    update tb_name set field_1 = value_1,field_2 = value_2 where a_field = a_value;
    #删除数据
    delete from tb_name where a_field = a_value;
    #表结构修改
    #添加列
    alter table tb add name varchar(20) after 字段名/first;
    #删除列
    alter table tb drop name;
    #修改字段类型
    alter table tb modify gender char(10);
    #修改字段名称
    alter table tb change name new_name char(10)
    #修改表名
    alter table tb rename to new_student;

常用权限

  约束类型    默认         非空          唯一            自增长               主键             外键         
  关键字     default    not null    unique key    auto_increment    primary key    foreign key

默认

    create table tb(
        id int,
        name varchar(20),
        age int default 18);
    desc tb;
    -------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int(11)     | YES  |     | NULL    |       |
    | name  | varchar(20) | YES  |     | NULL    |       |
    | age   | int(11)     | YES  |     | 18      |       |
    +-------+-------------+------+-----+---------+-------+
    insert into tb(name) values("郭康伦"),("无名"),("墨染"),("南北");
    select * from tb;
    id   | name      | age  |
    +------+-----------+------+
    | NULL | gkl       |   18 |
    | NULL | nh        |   18 |
    | NULL | 郭康伦    |   18 |
    | NULL | 无名      |   18 |
    | NULL | 墨染      |   18 |
    | NULL | 南北      |   18 |
    +------+-----------+------+
    



非空

    create table tb_1(
        id int,
        name varchar(20) not null);
        
    insert into tb_1(id) values(1);
    ERROR 1364 (HY000): Field 'name' doesn't have a default value
    



唯一

    CREATE TABLE tb2(
         id int UNIQUE KEY,
         name varchar(20)
    );
    #重复则报错
    
    





自增长

    create table tb3(
        id int primary key auto_increment,
        name varchar(20)
    )auto_increment =100;
    insert into tb3(name) values("郭康伦"),("南北"),(" 无名");
    mysql> select * from tb3;
    +-----+-----------+
    | id  | name      |
    +-----+-----------+
    | 100 | 郭康伦    |
    | 101 | 南北      |
    | 102 | 无名      |
    +-----+-----------+
    
    







主键

    主键的作用: 可以唯一标识 一条数据，每张表里面只能有一个主键,。
    主键特性： 非空且唯一。当表里没有主键的时，第一个出现的非空且为唯一的列，被当成主键。
    
    create table tb4( 
        id int primary key,
        name varchar(20) not null
    ); 
    #删除主键约束
    alter table tb4 drop primary key;
    #添加主键约束
    mysql> alter table tb3
             -> add primary key(id);
    





外键

    外键约束 ：保持数据一致性，完整性实现一对多关系。
    外键必须关联到键上面去，一般情况是，关联到另一张表的主键
    建立选课系统中的4张表: (学院表，学生表，课程表，选课表) , 并每张表插入4条数据
    
    
    
    1、学院表
    
        create table college(
            college_id int primary key auto_increment,
            college_name varchar(20) not null);
    
    
    
    2、学生表
    
        create table student(
            student_id int primary key auto_increment,
            student_name varchar(20),
            s_id int,
            foreign key(s_id) references college(college_id));
    
    
    
    
    
    3、课程表
    
        create table course(
            course_id int primary key auto_increment,
            course_name varchar(20) not null,
            c_id int,
            foreign key(c_id) references college(college_id));
    
    
    
    4、选课表
    
        create table choice(
            id_c int,
            id_s int,
            primary key(id_s,id_c),
            foreign key(id_s) references student(student_id),
            foreign key(id_c) references course(course_id));
    
    
    
    
    
    
    
    5、插入数据
    
        insert into college values(null,"外国语学院");
        insert into college values(null,"土木学院");
        insert into college values(null,"计算机学院");
        insert into college values(null,"经管学院");
        
        ---------------------------------------------------------------------------------------------
         college_id | college_name    |
        +------------+-----------------+
        |          1 | 外国语学院      |
        |          2 | 土木学院        |
        |          3 | 计算机学院      |
        |          4 | 经管学院        |
        +------------+-----------------+
        ---------------------------------------------------------------------------------------------
        
        
        
        insert into student values(null,"小明",1);
        insert into student values(null,"小红",3);
        insert into student values(null,"郭康伦",2);
        insert into student values(null,"小白",4);
        select * from student join college on student.s_id = college.college_id;
        ---------------------------------------------------------------------------------------------
         student_id | student_name | s_id | college_id | college_name    |
        +------------+--------------+------+------------+-----------------+
        |          1 | 小明         |    1 |          1 | 外国语学院      |
        |          2 | 小红         |    3 |          3 | 计算机学院      |
        |          3 | 郭康伦       |    2 |          2 | 土木学院        |
        |          4 | 小白         |    4 |          4 | 经管学院        |
        +------------+--------------+------+------------+-----------------+
        
        ---------------------------------------------------------------------------------------------
        
        
        
        
        
        insert into course values(null,"英语",1);
        insert into course values(null,"土木结构",2);
        insert into course values(null,"python",3);
        insert into course values(null,"经济管理学",4);
        select * from course join college on course.c_id = college.college_id;
        -----------+-----------------+------+------------+-----------------+
        | course_id | course_name     | c_id | college_id | college_name    |
        +-----------+-----------------+------+------------+-----------------+
        |         1 | 英语            |    1 |          1 | 外国语学院      |
        |         2 | 土木结构        |    2 |          2 | 土木学院        |
        |         3 | python          |    3 |          3 | 计算机学院      |
        |         4 | 经济管理学      |    4 |          4 | 经管学院        |
        +-----------+-----------------+------+------------+-----------------+
        
        
        insert into choice values(1,2);
        insert into choice values(1,3);
        insert into choice values(2,2);
        insert into choice values(2,4);
        insert into choice values(3,4);
        insert into choice values(3,3);
        insert into choice values(4,4);
        insert into choice values(4,2);
        select * from choice join student on student_id = choice.id_s join course on course.course_id = id_c;
        
         id_c | id_s | student_id | student_name | s_id | course_id | course_name     | c_id |
        +------+------+------------+--------------+------+-----------+-----------------+------+
        |    1 |    2 |          2 | 小红         |    3 |         1 | 英语            |    1 |
        |    1 |    3 |          3 | 郭康伦       |    2 |         1 | 英语            |    1 |
        |    2 |    2 |          2 | 小红         |    3 |         2 | 土木结构        |    2 |
        |    2 |    4 |          4 | 小白         |    4 |         2 | 土木结构        |    2 |
        |    3 |    3 |          3 | 郭康伦       |    2 |         3 | python          |    3 |
        |    3 |    4 |          4 | 小白         |    4 |         3 | python          |    3 |
        |    4 |    2 |          2 | 小红         |    3 |         4 | 经济管理学      |    4 |
        |    4 |    4 |          4 | 小白         |    4 |         4 | 经济管理学      |    4 |
        +------+------+------------+--------------+------+-----------+-----------------+------+
        
    











子查询和连接查询

数据准备

    create table students(
        number int UNIQUE KEY auto_increment,  
        name varchar(20) UNIQUE KEY, 
        age int not null, 
        birth date not null
    )auto_increment=201804001;
    
    insert into students (name, age, birth) value
    ( '刘一', 16, '2002-01-01'),
    ( '陈二', 17, '2002-01-01'),
    ( '张三', 18, '2002-01-01'),
    ( '李四', 19, '2002-01-01'),
    ( '王五', 20, '2002-01-01'),
    ( '赵六', 21, '2002-01-01'),
    ( '孙七', 22, '2002-01-01'),
    ( '周八', 23, '2002-01-01'),
    ( '吴九', 23, '2002-01-01'),
    ( '郑十', 23, '2002-01-01');
     number    | name   | age | birth      |
    +-----------+--------+-----+------------+
    | 201804001 | 刘一   |  16 | 2002-01-01 |
    | 201804002 | 陈二   |  17 | 2002-01-01 |
    | 201804003 | 张三   |  18 | 2002-01-01 |
    | 201804004 | 李四   |  19 | 2002-01-01 |
    | 201804005 | 王五   |  20 | 2002-01-01 |
    | 201804006 | 赵六   |  21 | 2002-01-01 |
    | 201804007 | 孙七   |  22 | 2002-01-01 |
    | 201804008 | 周八   |  23 | 2002-01-01 |
    | 201804009 | 吴九   |  23 | 2002-01-01 |
    | 201804010 | 郑十   |  23 | 2002-01-01 |
    +-----------+--------+-----+------------+
    
    
    create table subjects(
        number int(4) unsigned zerofill NOT NULL auto_increment,
        title varchar(20),
        duration int,
        PRIMARY KEY (number)
    );
    
    
    insert into subjects (title, duration) value
    ('python基础', 32),
    ('python进阶', 16),
    ('web前端', 16),
    ('python框架', 32),
    ('python项目', 32);
    -------+--------------+----------+
    | number | title        | duration |
    +--------+--------------+----------+
    |   0001 | python基础   |       32 |
    |   0002 | python进阶   |       16 |
    |   0003 | web前端      |       16 |
    |   0004 | python框架   |       32 |
    |   0005 | python项目   |       32 |
    +--------+--------------+----------+
    
    
    create table grades(
        student_number int,
        subject_number int(4) unsigned zerofill NOT NULL,
        grade int not null
    );
    
    
    
    insert into grades values
    (201804001, 0001, 90),
    (201804002, 0001, 89),
    (201804003, 0001, 88),
    (201804004, 0001, 87),
    (201804005, 0001, 86),
    (201804006, 0001, 85),
    (201804007, 0001, 84),
    (201804008, 0001, 83),
    (201804009, 0001, 82),
    (201804010, 0001, 81),
    (201804001, 0002, 80),
    (201804002, 0002, 79),
    (201804003, 0002, 78),
    (201804004, 0002, 77),
    (201804005, 0002, 76),
    (201804006, 0002, 75),
    (201804007, 0002, 74),
    (201804008, 0002, 73),
    (201804009, 0002, 72),
    (201804010, 0002, 71);
    student_number | subject_number | grade |
    +----------------+----------------+-------+
    |      201804001 |           0001 |    90 |
    |      201804002 |           0001 |    89 |
    |      201804003 |           0001 |    88 |
    |      201804004 |           0001 |    87 |
    |      201804005 |           0001 |    86 |
    |      201804006 |           0001 |    85 |
    |      201804007 |           0001 |    84 |
    |      201804008 |           0001 |    83 |
    |      201804009 |           0001 |    82 |
    |      201804010 |           0001 |    81 |
    |      201804001 |           0002 |    80 |
    |      201804002 |           0002 |    79 |
    |      201804003 |           0002 |    78 |
    |      201804004 |           0002 |    77 |
    |      201804005 |           0002 |    76 |
    |      201804006 |           0002 |    75 |
    |      201804007 |           0002 |    74 |
    |      201804008 |           0002 |    73 |
    |      201804009 |           0002 |    72 |
    |      201804010 |           0002 |    71 |
    +----------------+----------------+-------+
    

    #查找张三的成绩
    select name,title,grade from students join grades on students.number = grades.student_number join subjects on subjects.number = grades.subject_number where name = "张三";
     name   | title        | grade |
    +--------+--------------+-------+
    | 张三   | python基础   |    88 |
    | 张三   | python进阶   |    78 |
    +--------+--------------+-------+
    



用户权限

    #查看用户
    select user,host from mysql.user;
    #修改当前账号密码
    set password = password("123456");
    #创建用户
    create user "gkl"@"%"identified by "123";
    #查看用户权限
    show grants for "gkl"@"%";
    删除用户：
        drop user 'test'@'localhost';
        drop user "gkl"@"%";
    #查看编码
    show variables like "%char%";



mysql配置文件

    /etc/mysql/mysql.conf.d$ vim mysqld.cnf