/*** Create a new database named lv_20140827, in order to facilitate the display of Chinese at the command prompt, at the time of creation through character set GBK
Specifying the database character encoding as gbk**/
Create DATABASE lj_20140827 character Set GBK;
/** See which databases have been created. **/
show databases;
/***
Select the database you want to manipulate to operate on a database, you must first select the database, or you will be prompted with an error:
ERROR 1046 (3d000): No Database selected
Two options for using the database:
One: Specified when logging in to the database, command: mysql-d selected database name-h hostname-u user name-P
For example, select the database you just created when you log in: mysql-d samp_db-u root-p
Second: After logging in using the USE statement to specify, command: used database name;
The USE statement can be executed without a semicolon, and a samp_db is used to select the database you just created, and you will be prompted after successful selection: Database changed ***/
/** Creating a database table
Use the CREATE TABLE statement to complete the creation of a table, the common form of CREATE TABLE:
CREATE TABLE table name (column declaration);
To create the students table, for example, the table will hold the number (ID), name, Gender (sex), Age, contact number (tel) of the content: **/
Use lj_20140827;
CREATE TABLE Students
(
ID int unsigned NOT NULL Auto_increment primary key,
Name Char (8) is not NULL,
Sex char (4) NOT NULL,
Age tinyint unsigned is not NULL,
Tel char (+) NULL default "-"
);
/** inserting data into a table
The INSERT statement can be used to insert one or more rows of data into a database table, using the following general form:
Insert [into] table name [(column name 1, column name 2, column name 3, ...)] VALUES (value 1, value 2, value 3, ...); ***/
INSERT into students values (NULL, "Wang Gang", "male", 21, "13811371377");
INSERT into students values (NULL, "Zhang San", "male", 22, "13811371378");
INSERT into students values (NULL, "John Doe", "male", 30, "13811371379");
INSERT into students values (NULL, "Hu Jingtao", "male", 40, "13811371380");
INSERT into students values (NULL, "* * *", "male", 25, "13811371381");
INSERT into students values (NULL, "* * *", "male", 60, "13811371382");
INSERT into students values (NULL, "Zhou Wenli", "female", 29, "13811371383");
INSERT into students values (NULL, "Vinglong", "male", 70, "13811371384");
INSERT into students values (NULL, "* * *", "male", 30, "13811371385");
INSERT into students values (NULL, "Hu Changqing", "female", 43, "13811371386");
INSERT into students values (NULL, "Li Gulong", "male", 65, "13811371387");
INSERT into students values (NULL, "Zhang Junbao", "male", 33, "13811371388");
INSERT into students values (NULL, "Monkey King", "male", 12, "13811371389");
INSERT into students values (NULL, "Zhao", "female", 37, "13811371390");
/** sometimes we just need to insert part of the data, or do not insert in the order of the columns, you can insert it in this form: **/
INSERT into students (name, sex, age) VALUES ("Sun Lihua", "female", 21);
/***select statements are commonly used to obtain data in a database based on certain query rules, with the following basic usage:
Select Column name from table name [query condition]; For example, to query the names and ages of all students in the students table, enter the statement select name,
Age from students; The execution results are as follows: **/
Select name, age from students;
SELECT * from students;
/***where keyword is used to specify the query condition, in the form of the Select Column name from the table name where condition;
To query all gender-based information as an example, enter a query statement: SELECT * from students where sex= "female";
The WHERE clause does not only support the "where Column name = value" Query form, which is named equal to the value, and is supported for the operators of general comparison operations such as =, >, <, >=, <,! =, and some extension operators are [not] null, in, and like Wait a minute. You can also combine queries with OR and and for query criteria, and later learn more advanced conditional query methods, which are no longer introduced. ***/
SELECT * FROM students where sex= "female";
SELECT * from students where name is like "% king";
SELECT * from students where id<5 and age>2;
The/***update statement can be used to modify the data in the table, using the following basic form:
Update table name set column name = new value where update condition; ***/
Update students set Name= ' Wei ' where id=1 and tel= ' 13811371377 ';
/*** Delete the data in the table deletes the data in the table, using the following basic usage: Delete from table name where delete condition; ***/
Delete from students where id=2;
Delete from students;
The/**alter table statement is used to modify the table after it is created, using the following basic usage:
Adding columns
Basic form: ALTER TABLE name add column list data type [after insertion position];**/
ALTER TABLE students add address char (60);
ALTER TABLE students add birthday date after age;
/** Modifying columns
Basic form: ALTER TABLE name change column Name column new name new data type; **/
ALTER TABLE students Change Tel telphone char (+) Default "-";
ALTER TABLE students change name name char (+) not null;
/** Deleting columns
Basic form: ALTER TABLE name drop column name; **/
ALTER TABLE students drop birthday;
/** Renaming a table
Basic: ALTER TABLE name rename new table name;
Rename the students table to workmates:**/
ALTER TABLE students rename workmates;
#删除整张表
#基本形式:
drop table name;
#示例: Delete the workmates table:
drop table workmates;
#删除整个数据库
#基本形式:
drop database name;
#示例: Delete the samp_db database:
Drop database samp_db;
This article is from "Ghost" blog, please make sure to keep this source http://caizi.blog.51cto.com/5234706/1545519
10-minute MySQL starter tutorial