01 mysql login
*A: MySQL is A database that requires A user name and password to log in to. It provides A default user name and password to log in to. Format 1: CMD > mysql -u username -p password For example, run mysql -uroot -prootCopy the codeFormat 2: CMD > mysql --host= IP address --user= user name --password= password Example: mysql --host=127.0.0.1 --user=root --password=rootCopy the code02 INTRODUCTION and Classification of SQL statements
* A: SQL classification * Data Definition Language (DDL), used to define database objects such as databases, tables, and columns. Key words: CREATE, ALTER, DROP, etc. * Data Manipulation Language (DML) is used to update records of tables in the database. * Data Control Language: referred to as DCL(Data Control Language), used to define database access permissions and security levels, and create users. * Data Query Language (DQL) is used to Query the records of tables in the database. Select * B from where * B: General SQL syntax SQL statements can be written in one or more lines and end with a semicolon (;). Space and indentation can be used to improve the readability of statements. SQL statements of the MySQL database are not case sensitive. Comments can also be made using /**/Copy the code03 Data types in the data table
* A: we often use in MySQL data types are as follows Detailed data type classification | | type name as follows: - : | : - : | : - : | integer type tinyInt smallint | small integer | small integer mediumint | medium-sized integer int (integer) | | decimal integer types of normal size float | | single-precision floating-point Numbers double | double-precision floating-point number | | a decimal (m, d) during compression strict fixed-point number date type | | YYYY | 1901 ~ 2155 time | HH: MM: SS | - 838:59:59 ~ 838:59:59 date | - DD YYYY - MM | 1000-01-01 ~ 1000-01-01 datetime|YYYY-MM-DD HH:MM:SS|1000-01-01 00:00:00~ 9999-12-31 23:59:59 timestamp|YYYY-MM-DD HH:MM:SS|1970~01~01 00:00:01 UTC 03:14:07 ~ 2038-01-19 UTC text, binary type | CHAR (M) | M is an integer between 0 and 255 VARCHAR (M) | M is an integer between 0 and 65535 TINYBLOB | 0 ~ 255 bytes in length BLOB | 0 ~ 65535 bytes in length MEDIUMBLOB | 0 ~ 167772150 bytes in length LONGBLOB | 0 ~ 4294967295 bytes in length TINYTEXT | 0 ~ 255 bytes in length TEXT | 0 ~ 65535 bytes in length MEDIUMTEXT | 0 ~ 167772150 bytes in length LONGTEXT | 0 ~ 4294967295 bytes in length VARBINARY (M) | 0 ~ M bytes longer in length byte string BINARY (M) | 0 ~ M bytes in length of fixed-length byte stringCopy the code04 Creating a database
* A: Create database format: * create database name; * create database name character set; Utf8 CREATE DATABASE DAY21_1; utf8 CREATE DATABASE DAY21_1; CREATE DATABASE day21_2 CHARACTER SET UTf8;Copy the codeMySQL database: show databases; To view information about a database definition: show create database Database name; For example, run show create database day21_1; * C: drop database name; For example, drop database day21_2; * D: other database operation command switch database: use database name; For example, use day21_1; * E: check the database in use: select database();Copy the code05 Create a data table
* A: format: create table name (table name type (length) constraint); CREATE TABLE sort (sid INT, sname VARCHAR(100));Copy the code06 constraints
* A: create table name (column name type (length) constraint); Restrict what data can and cannot be written to each column.Copy the code* B: Which constraints: Primary key constraint Non-null constraint Unique constraint foreign key constraintCopy the code07SQL code save
Ctrl + S saves A write SQL statement in SQLyog.Copy the code08 Create a user table
* A: create user table: create user table, user id, user name, user addressCopy the codeCREAT TABLE users (uid INT, uname VARCHAR(20), uaddress VARCHAR(200));Copy the code09 Primary key Constraints
* A: The primary key is the field used to identify the current record. Its characteristic is non-empty, unique. In general, primary keys have no meaning in development and are used to identify the current record. Create table tablename(id int primary key,.......);Copy the code2. Create a primary key when creating a table. At the end of the table creation, specify the primary key create table tablename(id int,.......) Alter table table_name drop primary key; alter table sort drop primary key; 4. Automatic primary key growth: Generally, primary keys are self-growing fields and do not need to be specified. Alter table auto_increment increment alter table auto_increment incrementCopy the code10 Common table operations
* A: show tables; Check the table structure on the fusionmanager. The format is desc. For example: desc sort;Copy the code* B: drop table name; For example: drop table sort;Copy the code11 Modify the table structure
Alter table table_name add table name type (length) constraint; ALTER TABLE sort ADD sdesc varchar(20) ALTER TABLE sort ADD sdesc varchar(20);Copy the codeAlter table name modify column name type (length) constraint; ALTER TABLE sort MODIFY sname vARCHar (50) not NULL ALTER TABLE sort MODIFY sname varchar(50) not null; Alter table alter table name change old column name new column name type (length) constraint; ALTER TABLE sort CHANGE sname snamename vARCHar (30) ALTER TABLE sort CHANGE sname snamename varchar(30); Alter table table_name drop table_name; ALTER TABLE sort DROP snamename; * E: rename a table to rename a new table; RENAME TABLE sort TO category RENAME TABLE sort TO category; * F: alter table character set salter table name character set ALTER TABLE category CHARACTER SET GBK ALTER TABLE category CHARACTER SET GBKCopy the code12 Data table Add data _1
Insert into table (1, 2, 3...) Values (1, 2, 3..) ; Insert some columns into the tableCopy the codeINSERT INTO product (id,pname,price) VALUES (1,' pname ',5555.99); INSERT INTO product (id,pname,price) VALUES (2,' smart ',9999); * Note: list, table name problem corresponds to problem, number, data typeCopy the code13 Add data _2 to the data table
Insert into product (pname,price) VALUE(' washer ',800);Copy the codeInsert into values (1, 2, 3...) ; INSERT INOT product VALUES (4,' microoven ',300.25) * C: INSERT INOT product VALUES (4,' microoven ',300.25) * C: INSERT INOT product VALUES (4,' microoven ',300.25) * C: Insert into table name (1, 2, 3) values (1, 2, 3),(1, 2, 3) INSERT INTO product (pname,price) VALUES (' 80809 ', '80809'), (' 80809 ', '80809')Copy the code14 Updating Data
SQL > alter table name set field name = value, field name = value; SQL > alter table name = value; Update table_name set table_name = table_name where table_name = table_name; * B: Note that the column name type must be the same as the modified value. The modification value cannot exceed the maximum length. If the value is a string or date, add '.Copy the codeUPDATE sort SET sname=' sname '; UPDATE sort SET sname=' sname '; UPDATE sort SET sname=' s002' WHERE sid='s002'; UPDATE sort SET sname=' s003' WHERE sid='s003';Copy the code15 Deleting Data
* A: syntax: delete from table name [where condition]; Truncate table name;Copy the code* B: delete from table where all entries are in the table; Truncate table name; Delete Mode: delete the number of AUTO_INCREMENT records. Truncate Directly deletes the table and re-creates the table. Auto_increment is set to zero and the table starts again. * C: e.g. DELETE FROM sort WHERE sname=' sname '; DELETE FROM sort;Copy the code16 Prepare data tables and test data
* A: query statement, in the development of the most times, here use "zhangwu" account table. Create accounting tables: CREATE TABLE zhangwu (id INT PRIMARY KEY AUTO_INCREMENT, -- account id name VARCHAR(200), -- account name money DOUBLE, -- amount); * B: INSERT INTO zhangwu(id,name,money) VALUES (1,' 50 ',247); INSERT INTO zhangwu(id,name,money) VALUES (2,' zhangwu ',12345); INSERT INTO zhangwu(id,name,money) VALUES (3,' 50 ',1000); INSERT INTO zhangwu(id,name,money) VALUES (4,' 50 ', 100); INSERT INTO zhangwu(id,name,money) VALUES (5,' zhangwu ',8000); INSERT INTO zhangwu(id,name,money) VALUES (6,' zhangwu ',8000); INSERT INTO zhangwu(id,name,money) VALUES (7,null,5000);Copy the code17 Basic data query
* A: select field 1, field 2... From the name of the table; For example, select ID,name from zhangwu;Copy the codeSelect * from * where (select * from * where (select * from * where (select * from * where)); For example, select * from zhangwu; Note: the use of "*" can be used in practice, learning process, in practical development, not recommended. Cause: The information about the fields to be queried is not clear. If there are many fields, the query speed is slow. * C: select distinct from table name; For example, select distinct money from zhangwu; * D: alias query, use as keyword, as can be omitted. Aliases can be used to alias fields in a table. When a query statement is complex, using an alias can greatly simplify operations. Table alias format: select * from table name as alias; Select * from table name alias; Column alias format: SELECT column name as alias from table name; Select column name alias from table name; For example, select * from zhangwu as zw; Select money as m from zhangwu; Select money m from zhangwu; We can perform operations on columns directly in SQL statements. Select pname,price+10000 from product;Copy the code18 Data Query condition _1
* A: Conditional query where statement table conditional filtering. If the conditions are met, the operation is performed. If the conditions are not met, the operation is not performed.Copy the code* B: select * FROM (select * from (select * from (select * from (select * from (select * from))); * C: while conditions are as follows: the comparison operators < < = > = = < > > -- -- -- -- -- -- -- -- -- -- is greater than, less than, greater than (less than), equal to, does not equal BETWEEN... AND... -- -- -- -- -- -- -- -- -- -- - displayed IN a certain range of values (including head tail) (set) IN -- -- -- -- -- -- -- -- -- -- -- show value, IN the list IN case: (100200) IN the LIKE wildcards -- -- -- -- -- -- -- -- -- -- - fuzzy query, LIKE statements have two wildcard: % is used to match multiple characters; Cases of first_name like 'a %'; _ matches a character. Cases of first_name like 'a_; IS NULL Determines whether the value IS NULL ------------ IS NULL; Is not null. Judgment is not null * D logical operators and -- -- -- -- -- -- -- -- -- -- -- -- multiple conditions at the same time set up or -- -- -- -- -- -- -- -- -- -- -- -- any multiple conditions was not -- -- -- -- -- -- -- -- -- -- -- --, example: where not (salary > 100); SELECT * FROM zhangwu WHERE name = 'zhangwu '; SELECT * FROM zhangwu WHERE money >1000; SELECT * FROM zhangwu WHERE money >=2000 AND money <=5000; SELECT * FROM zhangwu WHERE money BETWEEN 2000 AND 5000; SELECT * FROM zhangwu WHERE money =1000 OR money =5000 OR money =3500; SELECT * FROM zhangwu WHERE money =1000 OR money =3500; SELECT * FROM zhangwu WHERE money IN(1000,5000,3500);Copy the code19 Conditional Query of data _2
* A fuzzy query query the account name contains "expenditure" accounting information. SELECT * FROM zhangwu WHERE name LIKE "% % %";Copy the codeSELECT * FROM gjp_ledger WHERE ldesc LIKE "_____"; SELECT * FROM zhangwu WHERE name IS NOT null; SELECT * FROM zhangwu WHERE NOT (name IS NULL);Copy the code20 Sort Query
* A: Sort A query using the format * Order by statement, you can sort the results of the query. Placed at the end of the SELECT statement. * SELECT * FROM table_name ORDER BY table_name; * ASC ascending (default) * DESC descendingCopy the code* B: Order by [desc][ASC] desc desc order by [desc][ASC] desc desc SELECT * FROM zhangwu SELECT * FROM zhangwu SELECT * FROM zhangwu ORDER BY zmoney DESC where (SELECT * FROM zhangwu ORDER BY zmoney DESC) SELECT * FROM zhangwu ORDER BY zmoney DESC where (SELECT * FROM zhangwu ORDER BY zmoney DESC SELECT * FROM zhangwu WHERE zname LIKE'% spend %' ORDER BY zmoney DESCCopy the code21 Aggregate function
* A: aggregate function * B: Function introduction * Before we do the query is horizontal query, they are based on the condition of the line of judgment, while the use of aggregate function query is vertical query, it is A column of values to calculate, and then return A single value; In addition, aggregate functions ignore null values. * count: count the number of rows where the specified column is not NULL. * sum: Computes the numeric sum of the specified column, if the column is specified; * Max: Calculates the maximum value of the specified column. If the specified column is a string, then the string type is used instead of a numeric type. * min: Evaluates the minimum value of the specified column, using string sort if the specified column is a string; Avg: Evaluates the average value of the specified column, 0 if the specified column type is not numeric;Copy the code* C: SELECT count(*)AS'count' FROM zhangwu -- SELECT count(*)AS'count' FROM zhangwu -- SELECT count(*)AS'count' FROM zhangwu -- SELECT sum(zname) FROM zhangwu SELECT sum(zname) FROM zhangwu SELECT sum(zname) FROM zhangwu SELECT zname FROM zhangwu SELECT Max (zmoney) FROM zhangwu WHERE zname = '% income %' INSERT INTO zhangwu (zname) VALUES (' zname ') -- Max function SELECT Max (zmoney) FROM zhangwu SELECT avG (zmoney)FROM zhangwu SELECT AVG(zmoney)FROM ZhangwuCopy the code22 Group Query
*A: Group query *A: Group by = group by = parent group by = parent group by = parent group by = parent group by = parent group by = parent group by = parent group by = parent group by = parent group by = parent group by = parent group by = parent group by = parent group by = parent group by = parent * SELECT 1, 2... FROM 表名 GROUP BY column HAVING condition; * The HAVING substatement is used to filter data after grouping, similar to the WHERE condition. * b: having the distinction of having and where * is after the grouping of data filtering. * the where is in front of the group of data filtering * having behind can use grouping function (statistical function) * behind the where can not use a group functions.Copy the code* B: case code /* Query all data eating expenses total how much salary income total how much clothing expenses total how much stock income total how much playing mahjong expenses total how much grouping query: */ select SUM(zmoney),zname FROM zhangwu group by zname -- SELECT SUM(zmoney)AS 'getsum',zname FROM zhangwu WHERE zname LIKE'% spend %' GROUP BY zname ORDER BY zhangwu SELECT SUM(zmoney)AS '% spend %',zname FROM zhangwu WHERE zname LIKE'% spend %' GROUP BY zname ORDER BY Select * from 'where' where (select * from 'where' where (select * from 'where')) Having SELECT SUM(zmoney)AS 'getsum',zname FROM zhangwu WHERE zname LIKE'% spending %' GROUP BY zname having getsum>5000Copy the codeAttachment: Personal e-commerce in Taiwan open source project
Finally, attached is my personal open source e-commerce business In Taiwan project:Copy the codeIt’s a square pavilion.
Link: github.com/ChenJinchen…
Square Pavilion is a cube project for e-Commerce. In fantasy novels, there are always some powerful neutral organizations between various forces, and these organizations usually have the character “pavilion”. Therefore, this project is named “Sifang Pavilion”, which is inclusive and accumulates, absorbing the long meaning of hundreds of schools. Welcome interested partners to work together to improve and promote this project. Github square-pavilion