showing syntax error while using sequence in mysql

Question

-- creating table

 create table student( studID int(10),
    -> name varchar(100),
    -> roll_no int(20));

--inserting values in table

insert into student(name,roll_no) values("suraj",79);
insert into student(name,roll_no) values("ajay",70);

--creating sequence

CREATE SEQUENCE stud_seq AS INT
    -> START WITH 100
    -> INCREMENT BY 1;

--error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SEQUENCE stud_seq AS INT

Answer 1

It looks like you are confusing things. In MySql, you use a sequence by declaring an autoincrement field

 create table student ( 
  studID int autoincrement,
  name varchar(100),
  roll_no int(20);

Now, studID will start from 1 and automatically increment for each record inserted where the value is not specified or is null.

So

insert into student(name,roll_no) values("suraj",79);
insert into student(name,roll_no) values("ajay",70);

will allocate 1 to suraj and 2 to ajay.

To change the value of the autoinc field to 100

alter table student
auto_increment=100

More Information at w3schools or mysqltutorial