mysql-基础的操作

DDL
create drop

DCL
grant revoke

DML
delete update insert

create database mydatabase ;

create database mydatabase2 charset gbk;

create table class (
    id int(3),
    name varchar(10),
    age int(3)
) ;

insert into my_student values(34,'扯淡');

改变列

alter table table_name change column_name new_column_name type;

删除列
alter table table_name drop column_name;

修改列
alter table table_name modify column_name type;

show create table table_name;

create table my_date(
    d1 date,
    d2 time,
    d3 datetime,
    d4 timestamp,
    d5 year
) charset utf8;

insert into my_date values('1990-01-01','12:12:12','1990-01-01 12:12:12','1990-01-01 12:12:12',69);

insert into my_date values('1990-01-01','12:12:12','1990-01-01 12:12:12','1990-01-01 12:12:12',70);

update my_date set d1 = '2019-01-01' where d5 = '1970';

create table my_enum(
    gender enum('男','女')
) charset utf8;

insert into my_enum values('i');

//类似编程里的enum类型,从1开始编码
select gender +1 from my_enum;

select gender from my_enum;

//bit set
create table my_set (
    hobby set('篮球','足球','乒乓球')
) charset utf8;

// binary 100
insert into my_set values('篮球');

create table my_primary(
    username varchar(10) primary key
) charset utf8;

create table my_primary2(
    username varchar(10) ,
    primary key(username)
) charset utf8;

create table my_primary3(
    username varchar(10) 
) charset utf8;

create table my_auto(
    id int primary key auto_increment,
    name varchar(10) not null comment '啊'
) charset utf8;

insert into my_auto values(null,'tom');

-- 删除自增长
alter table my_auto modify id int;

show variables like 'auto_increment%';

//唯一键  列属性 允许为空
create table my_unique1(
    id int primary key auto_increment,
    username varchar(10) unique
) charset utf8;

create table my_unique2(
    id int primary key auto_increment,
    username varchar(10),
    unique key(username)
) charset utf8;

create table my_unique3(
    id int primary key auto_increment,
    username varchar(10) 
) charset utf8;

alter table my_unique3 add unique key(username);

//删除唯一键
alter table my_unique1 drop index username;

 

 

 

select * ,if(student_age > 20 ,'符合','不符合') as judge from student; 

delimiter $$

create function my_sum(end_value int) return int

begin

end

//创建过程
create procedure my_pro1()
  select * from sys_role;

--修改语句结束符
delimiter $$
create procedure my_pro2()
   begin
       declare i int default 1; 
       set @sum = 0; 

       while i <= 100 do
           set @sum = @sum + i;
           set i = i + 1;
        end while;

        select @sum;
   end$$

   set @n1 = 1;
   set @n2 = 2;
   set @n3 = 3;

   delimiter $$

   create procedure my_pro4(in int_1 int, out int_2 int, inout int_3 int)

   begin
    select int_1,int_2,ing_3;

    set int_1 = 10;

    set int_2 = 100;

    set int_3 = 1000;

    select int_1,int_2,ing_3;

    set @n1 = 'a';
   set @n2 = 'b';
   set @n3 = 'c';

   select @n1,@n2,@n3;
   end$$

   use mydb;
   create table my_goods(
       id int primary key auto_increment,
       name varchar(20) not null,
       inv int
   ) charset utf8;

   create table my_orders(
       id int primary key auto_increment,
       goods_id int not null,
       goods_num int not null
   ) charset utf8;

   insert into my_goods values (null,'手机',100);

   delimiter $$
   create trigger after_insert_order_t after insert on my_orders for each row

   begin
       update my_goods set inv = inv - 1 where id = 1;

   end
   $$

    delimiter $$
   create trigger after_insert_order_t after insert on my_orders for each row

   begin
       update my_goods set inv = inv - new.goods_num where id = new_goods_id;

   end
   $$