(1)
Creating, Deleting, and Basic Queries:
Show databases mysql->show databases;
Create database mysql->create database db;
Drop database mysql->drop database db;
Select database mysql->use db
Create table mysql->create table mytable(name varchar(20),sex(char(1),birth date);
Delete table: mysql->drop table mytable;
Show table contents: mysql->show tables;
Show table structure: mysql->describe mytable;
Update:
1. Operations on columns:
Add a field to a table: mysql->alter table yourtable add name varchar(20)not null;
Delete a field: mysql->alter table yourtable drop name ;
2. Operations on Rows:
Insert a single record mysql->insert into mytable values('summer','m','1983-08-24');
Delete a single record mysql->delete from mytable where name='summer';
Modify a single record mysql->update mytable set sex='vm' where name='summer';
Insert multiple records mysql->insert into mytable select *from yourtable;(
In this form of INSERT statement, the data values for the new rows are not explicitly specified in the statement body. Instead, a database query is specified within the statement. The logical restrictions of this query are:
?The query cannot contain an ORDER BY clause. ?The query result must contain the same number of columns as the INSERT statement, and the data types must be compatible column by column. )
Simple Queries:
1. Display column names in query results
a. Using the as keyword: select name as 'Name' from students order by age
b. Direct expression: select name ‘Name’ from students order by age
(II)
(1). Query statement:
select username,uid from supesite.supe_userspaces where catid=’91’;
select T1.image from supesite.supe_spaceimages AS T1 INNER JOIN supesite.supe_spaceitems AS T2 ON T1.itemid = T2.itemid where T2.username = '".$username."' LIMIT 1;
(2). INSERT statement:
insert into cdb_members (username,password) values ('$username','$passwd');
(3). UPDATE statement:
update vpopmail.vpopmail set pw_privilege='1' where pw_name='haha';
(4). Statement to modify table structure:
alter table vpopmail add pw_haha int (10) default null;
alter table vpopmail drop pw_haha;
alter table haha add uid int (10) not null auto_increment, add primary key (uid);
(5). Create Table Database:
create table lian (a int,b char(10));
create database jie;
(6) Delete Database Table Records:
drop database jie;
drop table lian;
delete from lian where username='dd';
(7) MySQL Backup
mysqldump –all-databases > all_databases.sql
(8) MySQL Restore
mysql < all_databases.sql
(9) Create MySQL Account
mysql> grant all privileges on *.* to [email='lianbinjie'@'localhost']'lianbinjie'@'localhost'[/email]
-> identified by '840611';
mysql> GRANT SELECT,UPDATE ON *.* TO [email='monty'@'%']'monty'@'%'[/email] (account accessible over the network)
-> IDENTIFIED BY '840611';
(10) Change Password for an Existing Account
mysql> grant all privileges on *.* to [email='lianbinjie'@'localhost']'lianbinjie'@'localhost'[/email]
-> identified by '840611';
mysql> flush privileges;
