MySQL

Use MySQL on the command line

1. Login

mysql -u 'database' -p

Then enter the password

2. Create a database

create database 'database_name'; 
or
create database 'database_name' character set utf8;

Modify database encoding

alter database 'database_name' character set utf8; 

Check whether the database is created successfully

show databases;

3. Enter the database

use 'database_name';

4. Create tables

Create a table

create table 'table_name'(
    id int primary key,
    name varchar(30) not null);

For example, if we want to create a student information table:

create table stu_info (
    stu_id integer not null primary key,
    first_name varchar(30),
    last_name varchar(30));

Check if the table is created successfully

show tables;

Search table column elements

describe 'table_name';

Or we can use anthor form

desc 'table_name';

4. Modify tables

Add columes

alter table  'table_name'  add  'colume_name'  [element_type / colume_position];

For example, if we want to add a colume about city after the colume last_name:

alter table stu_info add city varchar(30) after last_name;

Insert elements

insert into 'table_name' values ('values');

For example, if we want to insert some students information table:

insert into stu_info values(1, "James", "Harden");
insert into stu_info values(2, "Lebron", "James");

Delete elements

delete from 'table_name' where [conditions]

For example, if we want to delete a row:

delete from stu_info where stu_id > 10;

Change values

update 'table_name' set  'colume_name' = 'value' [where conditions];

For example:

update stu_info set first_name = 'The best' where stu_id = 8;

Search elements

select * from 'table_name' [where conditions];

This is a entir program about creating a database and a table.

USE lab1;
create table stu_info (
stu_id integer not null primary key,
first_name varchar(30),
last_name varchar(30));

show tables;
describe stu_info;
-- desc stu_info;

alter table stu_info add city varchar(30) after last_name;
desc stu_info;

alter table stu_info drop city;
desc stu_info;

insert into stu_info values(1, "James", "Harden");
insert into stu_info values(2, "Lebron", "James");
insert into stu_info values(3, "Kevin", "Durant");
insert into stu_info values(4, "Russel", "Westbrook");
insert into stu_info values(5, "Stephon", "Curry");
insert into stu_info values(89, "Lebron", "Davis");
insert into stu_info values(8, "Anthony", "Davis");
insert into stu_info values(20, "Karmelon", "Anthony");
select * from stu_info;

delete from stu_info where stu_id > 10;
select * from stu_info;

update stu_info set first_name = 'The best' where stu_id = 8;
select * from stu_info;

show databases;