MySQL Base usage

1. Change the password

mysqladmin -u root -p password 123456   //修改密码为123456

2. Go to MySQL

mysql -u root -p

3. Displaying the database

show databases

4. Create a school database

create database school;

5. Using the database

use 数据库名

6. Create a table

CREATE TABLE demo(    ->id int PRIMARY KEY AUTO_INCREMENT,    ->name char(20) NOT NULL,    ->age int);

7. Show Table

show tables;

8. Inserting data

insert into demo (id,name,age) values(1, "mike", 19);

9. View the inserted content

select * from demo;

10. Display table Information

//方法一desc demo;//方法二show columns from demo;

11. Add Column Operations

alter table demo add hobby char(20);  //加的列在表的最后面alter table demo add qq int after age;  //加在某列的前面alter table demo add mail char(30) first;  //加在列的最前面

12. Delete Column operations

alter table demo drop hobby;

13. Modifying columns

alter table 表名 modify 列名 新类型 新参数  //修改列类型alter table 表名 change 旧列名 新列名 新类型 新参数  //修改列名和列类型

14. Deleting a database

drop database 数据库名

15. View the database storage engine

show engines;//support列为YES表示引擎可用，DEFAULT表示数据库当前默认的引擎

16. Changing the Order of columns

alter table demo modify id int first;  //修改顺序为第一位alter table demo modify mail char after hobby;  //移动到其他列的后面

17. Delete

delete from demo where name="mike";

18. Modifications

update demo set id=1 where name="mike";

19. Find

select * from demo;  //*代表所有列select * from demo where id=1;  //查询单行select * from demo where id>1;  //多行查询select name,mail from demo where id>=1;  //指定列查找

20. Projection operations and generalized operations
Take out part of the column called projection operation
Take out some of the columns for operations called generalized operations

select id,name,age+1 from demo

21. Comparison Operators
Between and within a range

between 100 and 500

Int<4,11> within a set
and
Not in

where <price between 100 and 300> or <price between 400 and 500>;select * from demo where 0;select * from demo where 1;

. Null Query

select *from demo where qq is null;select *from demo where qq is not null;

The null attribute is not optimized and is generally set to NOT NULL

Group grouping and statistical functions

//count求行select count(*) from demo;//avg求平均值select avg(age) from demo;//sum求和select sum(age) from demo;//min求最大//max求最小//groupselect max(price),cat_id from goods group by cat_id;

. As Alias

select id,name,age-id as z from demo;

Having

select id,name,qq-age as sheng from demo having sheng>200;

26. Constraints
a>. Constraints ensure the integrity and consistency of the data.
b>. Constraints are divided into table-level and column-level constraints.
c>. Constraint types include:

NOT NULL（非空约束）PRIMARY KEY（主键约束）UNIQUE KEY（唯一约束）DEFAULT（默认约束）PROEIGN KEY（外键约束）

FOREIGN KEY constraints
Maintain data consistency and integrity.
Implement one-to-one or one-to-many constraints.

Requirements for FOREIGN KEY constraints
A>. The parent and child tables must use the same storage engine and prohibit the use of temporary tables.
B>. The data table's storage engine can only be innodb.
C>. Foreign key columns and reference columns must have similar data types. Where the length of a number or whether the sign bit must be the same, and the length of the character can be different.
D>. The foreign key column and the reference column must create an index. If no index exists for the foreign key column, MySQL will automatically create the index.

Edit the default storage engine for a data table
MySQL configuration file:
Default-storage-engine=innodb

Referential actions for FOREIGN KEY constraints
A>. CASCADE: Delete or update from parent table and automatically delete or update matching rows in child table
B>. Set NULL: deletes or updates rows from the parent table, and sets the foreign key column in the child table to null. If you use this option, you must ensure that the child table column does not specify not NULL.
C>. RESTRICT: denies deletion or update of the parent table.
D>. NO ACTION: standard SQL keyword, same as restrict in MySQL.

Table-level constraints and column-level constraints
A constraint established on a data column, called a column-level constraint.
Constraints that are established on multiple data columns are called table-level constraints.

Column-level constraints can be declared either when a column is defined, or after a column definition.
Table-level constraints can only be declared after a column definition.

Add a PRIMARY KEY constraint

alter table 表名 add primary key (列名);

Add a FOREIGN KEY constraint

alter table 表名 add foreign key (外键列) references 参照表 (参照列);

Add a UNIQUE Constraint

alter table 表名 add unique key (列名);

Add a DEFAULT constraint

alter table 表名 set default 默认值;

Delete a DEFAULT constraint

alter table 表明 drop default;

Check index and CHECK constraint

SHOW INDEX  //查索引SHOW INDEXES  //查约束

Delete a PRIMARY KEY constraint

alter table 表名 drop primary key;

Remove UNIQUE Constraint

alter table 表名 drop index 列名;  //注意删除的是约束不是字段

Delete a FOREIGN KEY constraint

alter table 表名 foreign key 外键别名(constraint);

27. Modifying the column definition

alter table 表名 modify 列名 数据类型 其他属性alter table 表名 change 列名 新列名 数据类型 其他属性

28. Two ways to rename a table

alter table 表名 rename 新表名rename table 表名 to 新表名

29. Record operations

Increase

Inserting records

insert 表名 列名称（可省略） values|value (可以一次插入多条记录)insert 表名 set 值 （只能一次插入单条记录）  //与第一种的区别在于，此方法可以使用子查询（SubQuery)insert 表名 select  //此方法可以将查询结果插入到指定数据表

Delete

Deleting records

delete from 表名 where 条件  //若省略where，所有记录都将删除

Change

Update record (single-table update)

update 表名 set 更新的列值 where 条件  //若省略where，所有记录都将更新

Check

Find Records

select version();  //查看mysql版本select now();  //查看当前时间select id,name from info;select name,age from info  where id>=2;select info.id,info.name from info;  //有多张表时可以清楚使用

. As aliases

select id as userId, username as uname from info;

. Where Condition expression
< > =% */function wait

32. Query result grouping (group by)

select sex from info group by sex;  //通过性别分组

Grouping conditions

select age from info having age > 16;

Sort results (order by)

select * from info order by id desc;  //id号降序排列

Limit the number of results returned (limit)

select * fom info LIMIT 2;

insert test(name) select name from info where age>30

33. Sub-Query and connection

set names gbk;  //以gbk编码显示客户端

Sub-query
Indicates the SELECT clause in the other SQL statement now.

Subqueries are nested only inside the query and must always appear within parentheses.

A subquery can contain multiple keywords or conditions, such as: DISTINCT, GROUP by, LIMIT, function, and so on.

The outer query of a subquery can be: SELECT, INSERT, UPDATE, set, or do.

A subquery can return a header, row, column, or subquery.

Subqueries that use comparison operators
=, <, >=, <=, <>,! =, <=>

Grammatical structure

operand comparison_operator subquery;

AVG () not average

select ROUND(AVG(price), 2) FROM goods;  //平均值留两位小数，四舍五入

subqueries using [NOT] in
Syntax structure:
Operand comparison operator [not] in (subquery) = The any operator is equivalent to in.
The!=all or <>all operator is equivalent to not.

Sub-query using [NOT] exists (less used)
exists returns TRUE if the subquery returns any rows, otherwise false is returned.

34. Writing query results to a data table

INSERT [INTO] 表名 [（列名，...)] SELECT ...

35. Multi-table Update
Connection type:
INNER joins inside joins
In MySQL, the Join,cross join and the inner join are equivalent.
Left[outer] Join, LEFT OUTER join
Right[outer] Join, right outer join

UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate=cate_name SET goods_cate=cate_id;

INNER join is an inner join
Is the comparison between the internal and reference tables of the Update table.
On is the condition
Set is the value to which the change is made.

CREATE .... SELECT
Create a data table and write the results of the query to the data table.

1. Create
2. Write
3. Update
   For one step.
   Can write information at creation time

"' SQL
CREATE TABLE Tdb_brands
(
brand_id SMALLINT UNSIGNED PRIMARY KEY auto_increment,
Brand_Name VARCHAR (+) not NULL
)

SELECT brand_name from Tdb_goods GROUP by Brand_Name;

UPDATE tdb_brands INNER JOIN tdb_goods on Tdb_brands.brand_name=tdb_goods.brand_name set tdb_brands.brand_name=tdb_ goods.brand_id;
``
After a joint modification, there are nma->id types and names to follow.
Change with Alter

alter table tdb_goods change good_cate cate_id SMALLINT UNSIGNED NOT NULL,change brand_name brand_id smallint unsigned not null;

37. Connect
Grammatical structure
JOIN
INNER JOIN: The inner link and the cross join equivalence, are Cartesian product operations.
Cross joing: Crossover connection
Left [OUTER] Join Zuo Connection: is based on the record of the leftmost table, in the connected right table to find matching records match, found no match to the left table, with null padding.
Right [OUTER] join: is based on the record of the right table, in the connected left table to find the matching record with the connection, can not find the right table matches, with null padding.

Connection conditions
Use the ON keyword to set the connection conditions,
Use the Where keyword to filter the result set records.

Left JOIN: Displays a record of all and right-to-right join conditions for the right side of the table.
Right join: Displays the records that match the conditions of the connection and all the records in the left table.

If a LEFT join is used, a record A is present in the right table, and no corresponding record is found in the list, then the result is returned with the corresponding field content in record a only, and the other fields are null in the record (similar to the straight join.

38. Check the details of all items (implemented via left outer connection)

SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g LEFT JOIN tdb_goods_cates AS c ON g.cate_id=c.cate_id LEFT JOIN tdb_goods_brands AS b ON g.brand_id=b.brand_id\G;

39. Find out more about all products (via right-side connection)

SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g RIGHT JOIN tdb_goods_cates AS c ON g.cate_id=c.cate_id RIGHT JOIN tdb_goods_brands AS b ON g.brand_id=b.brand_id\G;

40. Multi-Table Connection

SELECT col_name1,col_name2,col_name3,col_name4,col_name5 FROM tbl_name1 AS t1 INNER JOIN tbl_name2 AS t2 ON join_condition INNER JOIN tbl_name3 AS t3 ON join_condition\G;

SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g    ->INNER JOIN tdb_goods_cate AS c ON g.cate_id=c.cate_id    ->INNER JOIN tdb_goods_brands AS b ON g.brand_id=b.brand_id\G;

Connect three tables to display query results, note that there are no commas in the middle of different inner joins!!! No comma!!! No comma!!!

The results shown are the same as those for the tdb_goods of the commodity table, but were previously shown through a single-table query, which was shown through a three-sheet connection.

A multi-table connection is actually a reverse constraint on a foreign key. The foreign key stores the data separately, and the multi-table connection also ties the data together.

41. External connection

A LEFT JOIN B join_condition

The result set of data table B depends on the data table A.
The result set of data table A depends on all data tables (except for table B) based on the left join condition

The left OUTER join condition determines how data table B is retrieved (in the case where the Where condition is not established)
If a record of table a conforms to the where condition, but there is no record in data table B that matches the join condition, an additional B row is generated for all columns that are empty.

If the records that are found by using an inner join do not exist in the Connection database table, and you try the following in the WHERE clause: Col_name is NULL, if col_name is defined as not Null,mysql will stop searching for more rows after it finds records that meet the attached persistence condition.

42. Self-Connection
The same data table is connected to itself.

SELECT s.type_id, s.type_name, p.type.name AS parent_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p    -> ON s.parent_id=p.type_id;

Note that the connection must be an alias, where S is the Son child table, p is the parent table, and in order to show that the product does not exist in the parent class, the case is to use a left join instead of inner join.

Find all categories and their parent classes

SELECT s.type_id, s.type_name, p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p ON s.parent_id=p.type_id;

Find all classes and their subclasses

SELECT p.type_id,p.type_name FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id=p.type_id;

Find the number of all categories and their subclasses

SELECT p.type_id, p.type_name,count(s.type_name) AS children_count FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id=p.type_id GROUP BY p.type_name ORDER BY p.type_id;

43. Multiple Table deletion

DELETE t1 FROM tdb_goods as t1 LEFT JOIN .....

The temporary T1 here refers not to the original table, but to the copy of the original table T1, the new T1 obtained after linking with T2.
The data for this temporary T1 table is the extra data to be deleted.

Multi-table deletion (single-table simulation multiple table delete duplicate data)

DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2) AS t2 ON t1.goods_name=t2.goods_name WHERE t1.goods_id > t2.goods_id;

44. Operators and functions

character Functions

%: any of the characters
_: Any one character

Numeric arithmetic functions

Date-time functions

SELECT DATE_FORMAT(‘2017-7-15‘, ‘%m/%d/%Y‘);  //07/15/2017

Information Functions

Aggregation Functions

Cryptographic Functions

MD5 (): Information Digest algorithm, prepare for future Web pages, use MD5 () as much as possible

SELECT MDT(‘admin‘);

PASSWORD (): password algorithm, modify the client's own password by PASSWORD () modifying the password of the current user and other and other users (important for MySQL database).

SET PASSWORD=PASSWORD(‘dimitar‘);  //把密码修改成dimitar

45. Custom Functions
A user-defined function (user-defined function, UDF) is a way to extend MySQL to the same algorithm as built-in functions.
Two prerequisites for a custom function: parameter (0 or more) return value (only one)
Functions can return any type of value, and they can also receive parameters of these types.

To create a custom function:

CREATE FUNCTION 函数名() RETURNS VARCHAR(30) RETURNSET NAMES gbk;USE test;SELECT NOW();SELECT DATA_FORMAT(NOW(), ‘%Y年%m月%d日 %H点：%i分：%s秒‘);//以上函数可以写成函数方便以后调用：CREATE FUNCTION date() RETURNS VARCHAR(30)RETURN DATE_FORMAT(NOW(),‘%Y年%m月%d日 %H点：%i分：%s秒);SELECT date();  //调用

About function bodies
a>. The function body is composed of legitimate SQL statements.
b>. The function body can be a simple select or INSERT statement.
c>. function body If it is a composite structure, use begin ... End statement.
d>. A composite structure can contain declarations, loops, and control structures.

Custom Functions with Parameters:
CREATE FUNCTION F1 (NUM1)

Wating ....

MySQL Base usage