MySQL学习笔记

** 更新数据**

update table_name set column = ... where ...;

创建一个表
最后一个属性不能有逗号

create table table_name(
	attribute_1  type,
	attribute_2  type
	);

插入数据

insert into table_name (attribute_1,attribute_2,……) values("String" ,integer,……)；

批量插入数据

insert into table_name (column1,column2...)select column1,column2...from table1_name;

删除行

Delete from table_name where condition;

删除一个表

drop table table_name;

导出表的脚本文件

mysqldump -uroot -p123456 database_name table_name > path\file_name.sql

导入脚本文件

mysqldump -uroot -p123456 database_name < path\file_name.sql

Start at the Beginning

Simple data type

; means the order end
create databases

create databases [database_name];

without upper and lower (reserved word)
保留字(reserved word)：指在高级语言中已经定义过的字，使用者不能再将这些字作为变量名或过程名使用。）和变量名，我们把保留字大写，把变量和数据小写。
具体的名字区分大小写。

connect database

 use <数据库名字>;

create table

remember the last line without comma

CREATE TABLE [TABLE_NAME]
(
		col_name1 type(length),
		col_name2 type(length)
);

If the input num over the length ,mysql will report out of the range.

Insert values

INSERT INTO [table_name] (column_1,column_2) values (value_1,value_2);
INSERT INTO [table_name] values (value_1,value_2);   --the amount value equal to the column

Drop DataBase

DROP DATABASE 	[Database_name];

CONSTRANT

c-a.Primary Key

--type one
CREATE TABLE [table_name](
	name varchar(10) PRIMARY KEY,
	num int(3)
);
--type two
CREATE TABLE [table_name](
	name VARCHAR(10),
	num int(3),
	CONSTRAINT [constraint_name] PRIMARY KEY(name)--primary key can be more than one.
	);	 

c-b.Default

when the values doesn’t include the key the default value will add in the key which set with default value.

CREATE TABLE [table_name](
  ...
  column_name data_type() DEFAULT 	default_value,
  ...
  );

c-c.Unique constraint

CREATE TABLE [table_name](
	column_name data_type(),
	...
	Unique (column_name),
	...
	);

c-d.Foreign Key

If the key with foreign Key,then the foreign won’t be deleted.And if the foreign key doesn’t exit the values won’t be inserted.

CREATE TABLE [table_name](
	column_name data_type(),
	...
  FOREIGN KEY (column_name) REFERENCE [foreign_table_name] (foreign_column_name)
	);

c-e.Not Null

if values without the key values ,mysql will deny it to insert.

CREATE TABLE [table_name](
  ...
  column_name data_type() NOT NULL,
  ...
  );

Select sentence

s-a.AND OR logic word

SELECT * FROM [table_name] WHERE [conditoin_1] AND/OR [condition_2];

s-b.IN & NOT IN

use to filter the values in tables
Query the values with column_value1,column_value2,…

SELECT * FROM table_name WHERE column_name IN (column_value1,column_value2,...);

Query the values without column_value1,column_value2,…

SELECT * FROM table_name WHERE column_name NOT IN (column_value1,column_value2,...);

s-c.blurry select

Use key word LIKE with ‘’ and ‘%’.
If you know the number of the character then you should use '’
If you don’t know the number of the character then you should use ‘%’

SELECT * FROM table_name WHERE column_name LIKE 'known_character_'
SELECT * FROM table_name WHERE column_name LIKE 'known_character%'

s-d.Order By

The measure about order is not the condition about the result,so it needn’t the where as the condition.

SELECT *FROM table_name ORDER BY [ASC|DESC];

ASC------ascending order
DESC—descending order

s-e.count() sum() avg() min() max()

count() can be used for all data type,because it just count the amount.
sum() and avg() are only use for the number.
max() min() not only for the number but also the date and number .
The function can be used after SELECT , WHERE and HAVING.

Tip
About the aggregate function it can’t be used in WHERE because it is easy to consider we won’t use AVG() or something likes it in where since there isn’t exit the value.

s-f.GROUP BY

SELECT column_in_group_by_result FROM table_name GROUP BY column_name;

After group by some column can’t be used in the Query . Such as after group by some column can’t be shown in the result.

s-g.HAVING

HAVING execute after group by and WHERE execute before the GROUP BY.
reference bloghttps://www.cnblogs.com/lmaster/p/6373045.html

SELECT column_in_group_by_result FROM table_name GROUP BY column_name HAVING [count()]...

s-h.JOIN table_name ON conditon

Link multiple tables

SELECT table1_column , table2_column FROM table1 JOIN table2
ON conditon 
...;

s-i subquery

Subquery is a query nested in SELECT INSERT UPDATE DELETE . And Subquery can be nested in subquery.==Subquery can be used in everywhere in the query but should be closed with ’ ( ) ’ .

• query with the result about subquery with keyword IN and NOT IN.

SELECT * FROM table1 WHERE column_name IN (SELECT column_name FROM table2 [WHERE condition ])

• subquery with the compare word

SELECT * FROM table1 WHERE column_name [<,>,=](SELECT [MAX(column_name),COUNT(...),MIN(...)]FROM table2 [WHERE ...])

• subquery in the FROM
  The subquery result will be use as the tempory table which called derivative table or physical subquery.

SELECT * FROM (SELECT * FROM table1 [WHERE condition])

ALTER TABLE

a-DROP TABLE

DROP TABLE table_name;

a-RENAME TABLE

RENAME TABLE table_Old_name TO table_new_name;

a-ADD column

• add a column with default order

ALTER TABLE table_name ADD COLUMN column_name data_type [constraint] [AFTER column_name,FIRST]

If didn’t add constraint the new column will without constraint ,and the position is default at end.
if with default constraint the new column will apprence with the default value.

a- DROP column

ALTER TABLE table_name  DROP COLUMN column_name.

a- RENAME column CHANGE

• change the column name , data-type,constraint.----------------CHANGE

ALTER TABLE table_name CHANGE column_old_name colunm_new_name data-type [constraint]

The data-type must be added and change it may influence the data.If without constraint the column will without the constraint.

a-MODIFY DATA-TYPE

ALTER TABLE table_name MODIFY column_name data-type.

The key word Change can also use for modifing the data-type.

a-Change the data == UPDATE … SET … ==

UPDATE table_name SET column_name = value ... WHERE condition;

Without the condition it will change the whole column.

d- DELETE A ROW DELETE

DELETE FROM table_name WHERE condition;

SOMETHING THAT USEFULL

Tip

• When insert values the keys’ order can be upset.
• We can use AS to rename the column_name.

SELECT column_name AS new_name FROM table_name;

• HAVE is same as WHERE but HAVE is used to filter the values were grouped and WHERE is executed before GROUP BY.
• Because of the safe the database can’t be rename.