Step 8: basic database and table operations

Step 8: basic operations for databases and tables http://www.bkjia.com/database/201212/173868.htmlMYSQL Entry 2: Search Using Regular Expressions http://www.bkjia.com/database/201212/173869.htmlMYSQL Entry 3: Full Text Search http://www.bkjia.com/database/201212/173873.htmlMYSQL Entry 4: MYSQL Data Types http://www.bkjia.com/database/201212/175536.htmlMYSQL Entry 5: MYSQL Character Set http://www.bkjia.com/database/201212/175541.htmlMYSQL Getting started 6: MYSQL Operators http://www.bkjia.com/database/201212/175862.htmlMYSQL Entry 7: MYSQL common functions http://www.bkjia.com/database/201212/175864.html 1. Operate the database www.2cto.com 1. view the database show databases [like '']. Example: mysql> show databases; + databases + | Database | + databases + | information_schema | luomian | mydb | mysql | net80576314 | phpcms_uat | phpcmsv9 | phpcmsv9_new | rutiao | szwalkers | test | v9test1 | + -------------------- + mysql> show databases like 'php % '; + ----------------- + | Database (php %) | + ----------------- + | phpcms_uat | phpcmsv9 | created | + --------------- + 2. create database [if not exists] dbname; example: mysql> create database if not exists mydb; 3. Select the required Database use dbname example: mysql> use mydb; database changed 4. Delete the database drop Database [if exists] dbname. Example: mysql> drop database if exists mydb; 2. operation table 1. show tables. Example: mysql> show tables; + keys + | Tables_in_test | + --------------------- + | newname | productnotes | test_char | test_inn | test_inn2 | test_priority | test_trans | test_view | + keys +
2. table creation example: mysql> create table user (-> id int (10) not null auto_increment primary key,-> name varchar (50) default 'n'/A' not null,-> sex char (1) null->) engine = InnDB; 3. Copy table example: mysql> create table student select * from user; mysql> create table teacher like user; 4. rename table mysql> rename table teacher to senior_teacher; mysql> alter table student rename to senior_student; 5. Delete the table mysql> drop table if exists senior_teacher; 6. view the create table statement mysql> show create table student; + --------- + tables | Table | Create Table + --------- + ------------------------------------- | student | create table 'student '('id' int (10) not null default '0 ', 'name' varchar (50) not null default 'n'/A', 'sex' char (1) default null) ENGINE = MyISAM default charset = latin1 | + --------- + ------------------------------------- 7. view the table structure mysql> desc student; + ------- + ------------- + ------ + ----- + --------- + ------- + | Field | Type | Null | Key | Default | Extra | + ------- + ------------- + ------ + ----- + --------- + ------- + | id | int (10) | NO | 0 | name | varchar (50) | NO | N/A | sex | char (1) | YES | NULL | + ------- + ------------- + ------ + ----- + --------- + ------- + 8. Modify the table structure www.2cto.com mysql> alter table student add bithday date null; mysql> alter table student modify bithday datetime; mysql> alter table student change bithday birt datetime; mysql> alter table student drop column bithday; 9. mysql> select * from student; + ---- + ------ + ------------------- + | id | name | sex | birt | + ---- + ------ + --------------------- + | 0 | jack | 1 | 00:00:00 | + ---- + ------ + --------------------- + mysql> insert into senior_student select * from student; mysql> insert into student (name, sex, birt) values ('jack', '1', current_date ()); mysql> update student set sex = 0 where name = 'jack'; mysql> delete from student where name = 'jack '; 10. create and view indexes mysql> create index idx_student_name on student (name); mysql> show index from student; + --------- + ------------ + hour + ----------- + hour + ---------- + -------- + ------------ + --------- + | Table | Non_unique | Key_name | hour | Column_name | Collation | Cardinality | sub_part | Packed | Null | Index_type | Comment | + --------- + ------------ + accept + ----------- + accept + ---------- + -------- + ------------ + ---------- + ----------- + | student | 1 | bytes | 1 | name | A | NULL | BTREE | + --------- + ------------ + bytes + -------------- + ------------- + ----------- + ---------- + -------- + ------------ + --------- +