TIPS: MYSQL

最新推荐文章于 2024-10-02 06:14:43 发布

informatica

最新推荐文章于 2024-10-02 06:14:43 发布

阅读量307

点赞数

分类专栏： Coding TIPS 文章标签： table database join null mysql date

本文链接：https://blog.csdn.net/informatica/article/details/5678619

版权

TIPS 同时被 2 个专栏收录

5 篇文章 0 订阅

订阅专栏

Coding

1 篇文章 0 订阅

订阅专栏

mysql -h mysqlhost.mycompany.com -u root -p

use databasename

CREATE TABLE Member (

loginName VARCHAR(20) NOT NULL,

createDate DATE NOT NULL,

password CHAR(255) NOT NULL,

lastName VARCHAR(50),

firstName VARCHAR(40),

street VARCHAR(50),

city VARCHAR(50),

state CHAR(2),

zip CHAR(10),

email VARCHAR(50),

phone CHAR(15),

fax CHAR(15),

PRIMARY KEY(loginName) )

NOT NULL: This column must have a value; it cannot be empty.

DEFAULT value: This value is stored in the column when the row is created

if no other value is given for this column.

AUTO_INCREMENT: You use this definition to create a sequence number.

As each row is added, the value of this column increases by one integer

from the last row entered. You can override the auto number by assigning

a specific value to the column.

UNSIGNED: You use this definition to indicate that the values for this

numeric field will never be negative numbers.

mysqladmin -u accountname -p start; //-p password is empty

mysqladmin -u accountname -p shutdown;

myisamchk -r path/databasename/tablename.MYI; //repair table

CREATE DATABASE databasename;

SHOW DATABASES;

DROP DATABASE databasename;

SHOW TABLES;

SHOW COLUMNS FROM tablename;

DROP TABLE tablename;

DELETE FROM tablename WHERE clause; //remove a row

ALTER TABLE tablename +

ADD columnname definition: Adds a column; definition includes the data

type and optional definitions.

ALTER columnname SET DEFAULT value: Changes the default value for a column.

ALTER columnname DROP DEFAULT: Removes the default value for a column.

CHANGE columnname newcolumnname definition: Changes the definition of a column and renames the column; definition includes the data type and optional definitions.

DROP columnname: Deletes a column, including all the data in the column. The data cannot be recovered.

MODIFY columnname definition: Changes the definition of a column;definition includes the data type and optional definitions.

RENAME newtablename: Renames a table.

INSERT INTO Member (loginName,createDate) VALUES (“bigguy”,”2001-Dec-2”);

LOAD DATA INFILE “datafilename” INTO TABLE tablename;

SELECT price,price*1.08 AS priceWithTax FROM Pet

(The AS clause tells MySQL to give the name priceWithTax to the second

column retrieved.Database does not change)

AVG(columnname) Returns the average of all the values in columnname

COUNT(columnname) Returns the number of rows in which columnname is not blank

MAX(columnname) Returns the largest value in columnname

MIN(columnname) Returns the smallest value in columnname

SUM(columnname) Returns the sum of all the values in columnname

SQRT() Returns the square root of each value in the column

DAYNAME() Returns the name of the day of the week for each value in a date column, rather than the actual date stored in the column.

SELECT * FROM Member ORDER BY DESC lastName

SELECT * FROM Member ORDER BY lastName

SELECT * FROM Pet GROUP BY petType

SELECT query UNION ALL SELECT query

(If ALL is not included, duplicate lines are not added to the result.)

The INNER JOIN returns all rows from both tables where there is a match. If there are rows in Employees that do not have matches in Orders, those rows will not be listed.

The LEFT JOIN returns all the rows from the first table (Employees), even if there are no matches in the second table (Orders). If there are rows in Employees that do not have matches in Orders, those rows also will be listed.

The RIGHT JOIN returns all the rows from the second table (Orders), even if there are no matches in the first table (Employees). If there had been any rows in Orders that did not have matches in Employees, those rows also would have been listed.