Basic MySQL operation (add and revise)

The following is the most basic MySQL additions and deletions to the statement, create an index, delete the index, view the index. The Association of tables in the database. Many IT workers have to command, but also the IT industry interview the most common knowledge points, because it is an entry-level basic command

• Increase

  > create database school;   创建数据库school> use school;              进入数据库> create table class (id int,name char(10),score decimal(5,2));创建一个表class（id整数  name字符串10个  成绩 数据长度5 小数点后面保留2位）> insert into class (id,name,score) values (1,‘zhangsan‘,70.5);  > insert into class (id,name,score) values (2,‘lisi‘,80.5);写入数据> alter table class add address varchar(50) default‘地址不详‘增加一个列

?

• Change

  > update class set name=‘wangwu‘ where id=1;  zhangsan改成wangwu  修改位置是id=1的列

?

• Check

  > show databases;      查看数据库> use school;              进入数据库> show tables；        查看所有表（需要进入数据库查看）> desc class;           查看class表的结构> select * from class;  查看class表的所有数据> select name from class;    查看class表的指定列> select distinct * from class;   查看所有数据 (且不重复)> select * from class where score>85;   条件查看（查看score>85的）> select * from class where score>85 and score<90;   （查看大于85 小于90的数据）> select * from class where exists (select * from class where score<90) and score<80;使用exists查询  先查询小于90的人  再在小于的90的数据中 查询 小于80的数据  最终显示

?

• By deleting

  Delete from class where id=2; Delete line with ID 2
  ALTER TABLE class drop column address; Delete Address column
  drop table class; Delete entire table
  Drop Database School; Deleting a database

?
?

Index

• Create a database and a table to demonstrate the following index operations

  > create database school;> use school;> create table class (id int(4) not null primary key auto_increment,name char(10) not null,score decimal(5,2),address varchar(50) default‘地址不详‘,hobby int(4))charset=utf8;> insert into class (name,score,address,hobby) values (‘zhangsan‘,70.5,‘金川校区‘,2);> insert into class (name,score,address,hobby) values (‘lisi‘,80.5,default,2);

• Normal index

  Create index Name_index on class (name); Create an index
  Show index from class; View Index
  Drop index name_index on class; Delete Index

• Unique index

  Create unique index Name_index on class (name); Create an index
  Drop index name_index on class; Delete Index

• Association database

  > create table hob (hid int,hobname char(10) not null);   创建表用来关联> select c.id,c.name,c.score,c.address,h.hobname from class c inner join hob h on c.hobby=h.hid;查看（class别名c，hob别名h）class表id,name，score，address.。 hob表的hobname  将class表的hobby 关联hob的hid。 （注意：这只是查看）> create temporary table tempclass (select c.id,c.name,c.score,c.address,h.hobname from class c inner join hob h on c.hobby=h.hid;);      （生成临时表class  注意：临时表show tables; 查询不到  去掉参数temporary则会生成永久的表）> select * from class_hob;    查看class_hob临时表。

Basic MySQL operation (add and revise)