MySQL Create, increase, delete, change, check

Mysql

Select version (); Current server version

Select database (); View current Working database

Show databases; Show all databases

Select user ();   or select Current_User (); Current user

Select curdate ();   or select Current_date (); Current date

Select Curtime ();   or select Current_time (); Current time

Select now ();   or select Current_timestamp (); Show system time Current date + time

Show engines; Displays the storage engines supported by the current database server

Show CharSet; View the character sets supported by the current server,

Data type:

Numbers: integers: tinyint, smallint, mediumint, int, bigint,

Decimal: Decimal (p,s) CREATE table T (T decimal (4,2));

String: Fixed length char (), variable length varchar () variable length text large text (0-4g) Longtext, enum () (enum)

Date type datetime binary with date Time type

1) Creating Database > Create Databases * *;

2) Show all databases: >show databases;

3) Delete databases > DROP database * *;

4) Select the database you want to use Show databases; Use * *;

View the included table show TABLES; When the database is selected, this command is equivalent to the following: Show tables from * *;

Another: Display table show tables from MyDB (database name);

Display table structure: >desc mydb.tablename; Equivalent to: >show columns from Mydb.tablename;

CREATE DATABASE IF not EXISTS aa DEFAULT CHARACTER SET UTF8; Determine if AA exists, does not exist on the build, and sets the AA code to UTF8.

CREATE table < table name > (< column name >< data type > [column-Level integrity constraints]

[,< Column name >< data type > [column-Level integrity constraint]] ...

[,< table-level integrity constraints;] );

For example: Create a Student information table CREATE TABLE products (ID char (a) not NULL,

Price float default 0.01,

Constraint primary key (ID));

Note: column-level constraints have two options:

Not NULL, which indicates that the column must not be null, and that it can be followed by a unique option, which indicates that the column value must not be duplicated.

Default, when the value of this column is empty, fill in the default value

The PRIMARY KEY constraint specifies that the value of a combination of one or more columns in a table is unique in the table, that is, a single row of records can be specified. Only one column per table is designated as the primary keyword, and columns of the image and text types cannot be specified as the primary keyword, nor does it allow the specified primary key column to have a null attribute.

CREATE TABLE DD (

ID INT (6) UNSIGNED Zerofill not NULL auto_increment, self-growing

Sname VARCHAR, Sgender enum (' 1 ', ' 0 ', ' secret '), enum selection

PRIMARY KEY (ID)

) engine= MYISAM auto_increment=5 DEFAULT Charset=utf8; Increased from 5, default Utf-8

Build table

CREATE TABLE Students (

Sno INT UNSIGNED not NULL auto_increment,

Sname VARCHAR () not NULL,

Sgender ENUM (' 1 ', ' 0 '),

Sage TINYINT UNSIGNED not NULL,

Sbirthday DATETIME,

Saddress VARCHAR (50),

Sscore DECIMAL (4,1),

PRIMARY KEY (SNO)

) Engine=myisam auto_increment=200901 DEFAULT Charset=utf8;

Increase

INSERT into students values (NULL, ' John Doe ', ' 1 ', 18, ' 2000-1-18 ', ' Zhengzhou ', 82);

INSERT into students values (NULL, ' zz ', ' 0 ', 16, ' 2003-1-18 ', ' Shanghai ', 68.9);

INSERT into students values (null, ' Li 1 ', ' 1 ', 17, ' 2000-1-18 ', ' Zhengzhou ', "--)," (null, ' Li 3 ', ' 0 ', 19, ' 2000-1-18 ', ' Zhengzhou ', 78);

Incomplete information, insert separate columns of information,

INSERT into students (Sname,sage) VALUES (' Jack ', +), (' Andy '), (' Chu ', 17);

The query results are then inserted into the data,

INSERT into students (Sname,sgender,sage,sbirthday,saddress,sscore) SELECT sname,sgender,sage,sbirthday,saddress, Sscore from students;

By deleting

DELETE from students WHERE sno=200918;

DELETE from students; --Unconditionally Delete all records.

TRUNCATE TABLE students; --Directly empty the data table, delete all, auto_increment new distribution, rarely used

Change

Replace into students (sname,sage) VALUES (' A ', 20), (' B ');

REPLACE into students VALUES (200917, ' year ', ' 0 ', ' 2001-5-5 ', ' GUANGZHOU ', 77.68);

UPDATE students SET Sscore=79,sname= ' Zhang 111 ', sbirthday= ' 2001-8-8 ', saddress= ' Shenzhen ' WHERE sno=200902;

UPDATE students SET sscore=0 WHERE Sscore is NULL; --The score is empty, modified to 0 points, not the same as below

UPDATE students SET sscore=0 WHERE sscore= ';

UPDATE students SET sscore=0 WHERE sscore=null;

Check

View data information

SELECT * from students;

SELECT sname name, Sscore score, saddress home address from students;

SELECT * from students WHERE sscore!=78;

SELECT * FROM students WHERE sscore<>78;

SELECT * FROM students WHERE not sscore=78;

SELECT * FROM students WHERE sscore between and 90;

SELECT * FROM students WHERE sbirthday between ' 2001-1-1 ' and ' 2010-1-1 ';

SELECT * FROM students WHERE saddress= ' Zhengzhou ';

SELECT * FROM students WHERE sname like ' li _ '; /*like, _ represents any 1 characters, number */

SELECT * FROM students WHERE sname like '%1% '; /*like,% represents any 0 or more arbitrary symbols */

SELECT * FROM students WHERE Sno Not in (200917,200925); /* in denotes an item within parentheses. And not in is except */

SELECT * FROM students WHERE Sscore are not NULL;

SELECT * from students GROUP by sname ORDER by Sno ASC, Sscore Asc,sno DESC

/*group by is used to group the result by the value of the column name, the tuple of equal value of the property column is a group, and order by sorts the result table by the value of the column name ascending ASC or Descending desc * *

SELECT COUNT (SNO) as number, Min (sscore) as lowest score, Max (Sscore) as highest score, AVG (Sage) as pingjunnianling from students;

SELECT name, year (Date ())-year (birth date) as age from Students

SELECT Sname,count (*) as number from students GROUP by sname; Name and number of names available

Modify the table name and modify DD to student

RENAME TABLE DD to student;

Add column, field, dimension Delete Column

ALTER TABLE tbl_name [add< new column name >< data type >[integrity constraint]]

[drop< column name >< integrity constraint name;]

[modify< column names >< data types >];

Add is used to add new columns and new integrity constraints, and the newly added columns are all null values. (Index, primary key, etc.)

Drop is used to delete the specified integrity constraint (index,primary key, foreign key)

Modify for modifying an existing column definition

ALTER TABLE student ADD Sscore TINYINT;

ALTER TABLE student ADD saddress VARCHAR (+) after sname;

ALTER TABLE student DROP COLUMN sscore;

ALTER TABLE student DROP saddress;

ALTER TABLE suser MODIFY nii INT;

There are two ways to copy a table structure (field):

CREATE TABLE stu like students; Copy only one table structure (field)

CREATE TABLE STU2 SELECT * from students; Equivalent to copying a copy. The data are

MySQL Create, increase, delete, change, check