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;