MySQL 基本语法

Posted on | In ,

  1. 创建数据库

    1
    2
    create database 数据库名 [其他选项];
    create database my_database character set gbk;

  1. 创建表

    1
    2
    3
    4
    5
    6
    7
    8
    create table 表名(列声明);
    create table STUDENTS (
     	  ID int usigned not null auto_increment primary key,
      	  NAME char(8) not null,
         SEX char(4) not null,
       	AGE tinyint usigned not null,
       	TEL char(13) null default "_"
    );

  2. 插入

    1
    2
    3
    insert [into] 表名[(列名1, 列名2，列名3, ...)] values(值1, 值2, 值3, ...);
    insert into students(name, sex, age, tel) values("王钢蛋", "男", "18", "13576883818");
    insert into students values(NULL, "王钢蛋", "男", "18", "13576883818");

  1. 查询

    1
    2
    3
    4
    5
    6
    7
    8
    select 列名 from 表名 [查询条件];
    select * from students;
    select name, age from students;
    select * from students where sex="男";
    select * from students where sex="男" and name="王钢蛋";
    select * from students where age > 16;
    select * from students where name like "%王%";
    select * from students where id < 5 and name like "%王%";

  1. 更新

    1
    2
    3
    4
    update 表名 set 列名=值 where 更新条件;
    update students set tel="13576894563" where name="王钢蛋";
    update students set age = age + 1;
    update students set age = age + 1, name="王铁锤" where name="王刚蛋";

  1. 删除

    1
    2
    3
    4
    delete from 表名 where 删除条件;
    delete from students where id = 2;
    delete from students where age < 20;
    delete from students;

  2. 修改列

    1
    2
    3
    alter table 表名 change 列名 新列名 新数据类型;
    alter table students change tel telphone char(13) default "_";
    alter table students change name name char(16) not null;

  3. 删除列

    1
    2
    alter table 表名 drop 列名;
    alter table students drop name;

  1. 重命名列

    1
    2
    alter table 表名 rename 新表名;
    alter table students rename workmates;

  1. 删除整张表

    1
    drop table 表名;

  1. 删除整个数据库

    1
    drop database 数据库名;
------ end ------