"MySQL must know" notes _ continuous Update

"MySQL must Know"

Structured Querylanguage,mysql is a database system management software software that can also be understood as a database server. Pronunciation, my Sequel,/?si?kw?l/see kuo

1th. Chapter 2.3

schema, outline, column. Row rows

2. Database: A collection of data that is stored in an organized way
4. DBMS Databasemanegersystem Database Management system
6. A structured list of table, tables, and a particular type of data
   1. A table name has uniqueness in one data
   2. The characteristics of the table, we use schema[mode] to describe
8. column, columns, a field in a table, all tables are made up of multiple columns
   1. The data type of each column datatype must be the same, which requires a more detailed column, easy to manage
10. Row, which is the storage of specific data.
12. PrimaryKey, primary key, one or more groups of columns that uniquely identify each row in a table.
    1. No two rows have the same [primary key value]
    2. Any row must have a primary key value
    3. The value of the primary key column is not updated. The value of the primary key column is not reused

13. Common operations and operators (operator)

    SHOW databases; #显示所有数据库USE Lengqian; #选择要使用的数据库SHOW TABLES; #显示所有table表SHOW COLUMNS from student; Displays all columns of the table, displays the properties of the column, etc. # displays all rows of a table. Property has Field,type,null,key,default,extra (.. Whether to allow null, key information, default value, other information) ######### #SELECT, indicating what to retrieve from that table, retrieving with limit, distinct:select name from student; retrieving a single column SELECT Name, score from student; retrieves multiple columns select * from student; retrieves all rows select DISTINCT score from student; lists different rows of score Here you can see how many different fractions of the select name from Studet limit 5; Retrieve the first five select score from student limit 2, 8; retrieved from 3rd and subsequent 7 (8 after line 2nd, The first line is line No. 0) ####### #排序数据, combined with order By,descselect score from student order by score; #数据时默认从小到大排序SELECT score from Student order B Y score desc, descending order, letter or number select Score,name from student ORDER by score DESC, name; #DESE只针对其前面有效SELECT score from student Order by score limit 1; The #检索出分数最高的一行SELECT score from student ORDER by score DESC LIMIT 3; #倒数三名 ######## #过滤数据 binding clause where (; !=,between operator) Select score from student where score >=60;select score, name from student where score between and 80 ; #包括60和80seLect name from student the where name is Null#is NULL is the value of the ######## #过滤说句, with the clause where (and,or, in, not, etc.) SELECT Sname,score from S Tudent WHERE score =80 OR score = 90 "; SELECT Sname,score from student WHERE score in (60,100); #包含60, 100SELECT sname, English_score, math_score from student wher E (English_score =60 or english=80) and Math_score >60;##### #多个操作符的时候要考虑计算顺序, commonly used brackets. And>or, in the order of calculation.   SELECT sname, English_score,math_score from student WHERE english_score =60 OR english_score = up and Math_score >60;

    `

15. Common wildcards, like,

    以上检索是精准的检索，LIKE通用配符是一个"模糊"操作，比精确稍粗狂点，有点正则表达式的赶脚。仅仅与%，_配合使用。条件完全吻合才能返回。#### %就是代表任意数量的任意字符（0个也行）， _就是代表一个字符，不多不少SELECT sname FROM student WHERE sname LIKE "leng%";SELECT sname FROM student WHERE sname LIKE "%ABC%";#不区分大小写SELECT sname FROM studnet WHERE sname LIKE "%qian";SELECT sname FROM student WHERE sname LIKE "_abc";#注意：这里的匹配不区分大小写，通用配符占用资源比较多，所有常用在其他操作符后面的

17. Regular expression

      regular expressions are also a wildcard, unlike pure like, where the regular expression matches the existence of an object rather than an exact match, with more rough, letters, numbers, positions, and so on. # # #基本匹配字符 "", "." Any one character select sname from student WHERE sname REGEXP "LENG"; #只要sname列值中有leng这四个字符即可, Case-insensitive # # #进行OR匹配, i.e. | And or is the same as select Sname From student where sname REGEXP "Liu|li", #SELECT sname from studnt where sname REGEXP ". Ng"; #在正则表达式中. Represents any one character # # #进行多个任意匹配之一 , "[12]" = "[1],[2],[12]" Three cases select ID from the products WHERE ID REGEXP "[0-9]old"; matches "any number old" # # #匹配特殊字符, combined with \\\ escape (two backslash, One of them is MySQL own explanation), in MySQL special characters have. [ ] _ | Select ID from the products WHERE ID REGEXP "\\\ |"; #检索有 | The row and column values # # #重复元字符, * any; + one or any one;? 0 or 1; {n} specifies the number of times, {n,} at least n; {n,m} The range of matching quantities select Id,name from the products WHERE ID REGEXP "\\.examples?\\)"; match contains ". Example" or " . examples) "text. Select Phon,name from list WHERE phone REGEXP "^1[0-9]{10}"; matches 1, total 11 digit text, probably is the mobile phone number. # # #定位元字符 ^ start, such as ^s;$ end, such as t$.  

19. A calculated field is a concatenation of multiple columns, etc., used to calculate the total amount of our calculations.

    ###concat拼接SELECT name, number, CONCAT(name, "(",number , ")") AS list FROM student;如lengqian(13027136666),AS 语句可省略,list为别名。####执行算数计算，算数操作符+-*/SELECT number ，price, number*price AS list FROM products;直接返回计算结果

21. Using Data processing functions

    ####文本处理函数，upper，lower，length(),soundex()SELECT name FROM student WHERE SOUNDEX("name")=SOUNDEX("lenggqian");#读起来差不多的函数SELECT Upper(name) FROM student;名字全部转化为大写字母。####时间日期处理函数，curtime,curdate()####s数值处理函数，Abs,Cos()

23. Summary functions

    ###汇聚函数，avg,max,min,count,sum(),可以多个组合使用SELECT MAX(mark),AVG(price) AS avg_price FROM prodects;

25. Group data, group the original table according to our requirements, and later filter, calculate and so on. the difficulty lies in the distinction between the groupby and the

    10/2/2017 9:19:55 PM

27. Using subqueries, in the same statement, the query is based on another query.

    ###利用子查询进行过滤现在，我们知道表a有id,product列，表b有id，name列我们已知name="lengqian",求对应的product数量。SELECT product FROM a WHERE id IN (SELECT b_id FROM b WHERE name="lengqian" ); #查询顺序是由内到外的。

15. Junction, is a mechanism used to correlate tables in a SELECT statement. Originally intended to conserve resources, you must use the fully qualified column name when the ambiguity column appears

    ###在a表总有id，company，b表中有id,product列。#id是a的主键，a_id是b的外键，通过这个产生联结。    SELECT company, product     FROM a,b    WHERE a_id = b_id    ORDER BY company;    ###上面为内部联结，还有一个更地道的使用方法inner join ,on    SELECT company, product    FROM a INNER JOIN b    ON a_id = b_id    ORDER BY company;

16. Advanced junction, with "table alias," Self-junction ", Natural coupling, external junction

        ###表别名，只能在查询总使用，不返回到客户机        SELECT verd_id, prod_name        FROM vendors AS v, products AS p        WHERE v.vend_id = p.vend_id;                    ###自联结，表是同一个,在products表中有pro_id,prd_name，prod_id列，我们已知id为xiaomi,求出生成xiaomi的公司所生产的所有产品。        SELECT p1.prod_id, prod_name         FROM products AS p1,products AS p2        WHERE p1.vend_id = p2.vend_id        AND p2.prod_id ="XIAOMI";        ##利用子查询实现上面功能。        SELECT vend_id , prod_name        FROM vendors, products        WHERE vend_id = (SELECT vend_id FROM         products WHERE prod_id = "XIAOMI");        ###自然联结和外部联结见书本

17. Combine socialize, UNION, simply combine the results of a similar two query, as
# # #这种情况下, or can also implement its basic functions.

18.## #全文搜索

3. Using MySQL:

create database  mysql_shiyan; #穿件数据库use mysql_shiyan; #启用某个数据库create table A (id int(10),name char(20),phont int(12));#创建表格A及表格A中的表头详细信息create table B ( menorwomen char(10),age int(3));#创建表格Binsert into A(id,name,phont) values(01,"lengqian",12344444);#添加信息的三种方法。。。insert into A(id,phont) values(02,345666768);insert into A values(03,"zouqian",4456465);select * from A;#显示表格A内容，表格样式drop database mysql_shiyan;#删除drop table xxx;

3. Constraint type

• Primary key, PRIMARY key: Primary key (primary key) is used to constrain a row in the table, as a unique identifier for this row, the primary key in a table can be accurately positioned to a row, so the primary key is very important. The primary key cannot be duplicated and cannot be empty. There is also a special primary key-the composite primary key. A primary key can be a column in a table, or it can be identified by two or more columns in a table
• Foreign key, foreign key: foreign key (foreign key) ensures both data integrity and the relationship between tables. A table can have multiple foreign keys, and each foreign key must REFERENCES (reference) The primary key of another table, the column that is constrained by the foreign key, and the value must have a corresponding value in the column it references.
• Default: Default value constraint, default, defaults are used when columns with default constraints are empty when the insert data is null.
• Unique, unique: Unique constraint (unique) is relatively simple, it specifies that the value of a column specified in a table must not have duplicate values, that is, each value of this column is unique.
• Not NULL, nonempty: Non-null constraint (NOT NULL), can be understood by a name, a column that is not NULL constrained, must not be empty when inserting a value. Violation of non-null constraint in MySQL, no error, only warning

4. Keywords

1.SELECT

1.select：从一个或多个表中检索信息。SELECT id FROM people;#会检索id列的所有id号码SELECT * FROM people；#会检索people表格的所有列SELECT DISTINCT id FROM people；检索出来不同的id号码SELECT idFROM peopleLIMIT 5，8;#从id列的第6行检索出来8个id号码。

2. Clause clause

SELECT id，name，priceFROM peopleORDER BY prince；#此处的子句用来排序的，以prince列来排序，默认是从小到大，从a到z。SELECT id，name，priceFROM peopleORDER BY prince DESC;#倒序，descend下降拓展：结合limit和DESC可检索最大或最小的数字：SELECT id，name，priceFROM peopleORDER BY princeLIMIT 1;

3. WHERE

SELECT id，name，priceFROM peopleWHERE price = 250；#检索出来prince为250的行，多个就多个!

4, like operation symbol, wildcard, general character, used to match a part of the special characters

%，代表是0，1甚至无数个字符。    SELECT id，name，priceFROM peopleWHERE name LIKE %leng%；#检索name列中，xxxlengxx的行。%就是代替字符，leng%,检索前面四个字符是leng, l%g，检索首字符是l,最后字符是g的对象。_,下划线，只能匹配单个字符SELECT id，name，priceFROM peopleWHERE id LIKE 1_；#只能是一个字符

5 Regular Expression RegExp

.表示任意一个字符：SELECT id，name，priceFROM peopleWHERE id REGEXP ".00"；#or,|,这个两个都是代表或：SELECT id，name，priceFROM peopleWHERE prince LIKE "100 or200 or300"；#匹配这三个数字的prince列[xyz],匹配字符集中任何一个，记住，是一个，等同于"x|y|z"[^xyz],匹配XYZ以外任何字符，一个或多个。重复元字符*   匹配前面字符0或者无数次+   匹配前面字符1或者无数次？       匹配前面字符0或者1次{n}     匹配前面字符n次{n,}    匹配至少n次{n,m}   匹配前面字符n到m次定位元字符^       第一位$       最后SELECT id，name，priceFROM peopleWHERE peince REGEXP "^[0-2]{3}" 1；#匹配第一位是0，1或者2一共3次的字符串，比如012，221xx，222xx等等.

10 Create a calculated field, field

Role:

• Combine information from different table columns in the appropriate format, as the information we need,比如，name列，country列，计算 SELECT CONCAT(name，"(",country,")") FROM xxx;结果就是lengqian(China)

• Performs arithmetic calculations, passes existing information through simple operations, and outputs the results we need directly

  SELECT id,price,quantity,prince*quantity AS expanded_prince  #计算结果返回，还有+-/基本运算符号。FROM listORDER BY id; #最终显示的列一共有4列，最后一列是我们的计算结果。

11 Using data processing functions

1,upper () function, turn capital:

SELECT name,Upper(name) AS cap_nameFROM listORDER BY name;#会出现name，cap_name列，后者字母全部为大写。

2, Date time processing function:

SELECT name,price FROM listWHERE DATE(lq_date)="2017-8-24";#从表中lq_date列中选择日期为2017-8-24的行。注意：日期格式一定是yyyy-mm-dd还有TIME（）函数

12 Summary Data

Functions are: AVG (), COUNT (), MAX (), MIN (), SUM (), average average

SELECT AVG(s_price) AS avg_price FROM producrs;#将products表中的s_price列的所有行数据取平均数，返回。SELECT COUNT(*) AS cou_row FROM producrs;#将products表中的列数计算，返回。SELECT AVG(s_price) AS avg_price FROM producrsWHERE pro_name="xiaomi";#将products表中的xiaomi价格取平均数，返回。也可多个函数一次操作SELECT AVG(s_price) AS avg_price ,MAX(sprice) AS max_prince,MIN(sprice) AS min_price,FROM producrs;    

13 Grouping data

Divide the original data into groups of data that we need, such as in this case, we need to know each product and its total.

SELECT product,SUM(amount_price) AS sum_product     #选择product列，并创建sum_product列FROM tableGROUP BY product #group by          #按照product进行分组。HAVING sum_product>2000;                    #总数不得少于2000，HAVing可用于分组                                            #order仅适用于行，having可代替order注：sum改为COUNT(*)就是返回个数

14 using sub-query 15 junction table

It probably means that a certain statement links two tables, A, B, and returns the information we need:
For example, A has a sid,sname column, B has a id,mark column,
We need to find mark scores below 60 from the Mark column in B, and a middle school sname name

SELECT sname,sid,id,mark #sname,sid等等出场顺序不同，导致后面列的顺序不同FROM A,BWHERE A.sid=B.id and mark>=60; #WHERE语句表明符合一定条件,注意“完全限定列名”

20 updating and deleting data

Update,delete
UPDATE Student

SET sname="lengqian"WHERE sid=100;将sid=100这一列的snme改为lengqianDELETE studentWHERE sid=100;#删除sid=100这一列

21 Creating and modifying tables

创建新表：CREATE TABLE a(aid int NOT NULL AUTO_INCREMENT,  #常见aid列，并且自动增加aname char(20) NOT NULL, #不可为空aprice char(10) NULL DEFAULT 1000, #不赋值的时候，默认价格为1000PRIMARY KEY(aid) #设计主键，为aid)新增列：ALTER TABLE aADD alocation char(20);删除列：ALTER TABLE aDROP COLUMN slocation;删除表：DROP TABLE a;重命名表：RENAME TABLE a  TO b;

22 Using Views

Views, which are virtual tables, can be seen with show tables, but cannot be deleted. Simply put, it is a virtual table generated from the original table according to our needs. can also be seen as a set of instructions.

CREATE VIEW pricenotnull ASSELECT price FROM aWHERE prince IS NOT NULL;#将重新生成的表格，成为我们需要的视图##使用视图select * from pricenotnullwhere...#删除视图drop view xxx;

23 Using Stored Procedures

Like a module that packs some of our column statements into a process, the procedure process

24 Using Cursors

Cursor, cursors, cursors

25 triggers

Is that when a specific statement is executed, the program automatically performs the relevant action. With Insert,delete and update three, only the table can be executed, views and virtual tables are not.

For example, each time a new order is added, the program automatically removes the order quantity from the total inventory and returns the amount of surplus that we need.

28 Security Management

is to classify the operators of the database, root users and ordinary users, to prevent errors caused by error operations.

1 Show All Users:

USE mysql；SELECT user FROM user；#就可以显示所有的用户了

2 New User:

`CREATE USER lengqian IDENTIFIED BY "lengqian"`

3 View and edit permissions for a user:

SHOW GRANTS FOR lengqian;GRANT SELECT ON product.* TO lengqian;#赋予lengqian用户在product数据库所有表格的select操作

4 Change user password:

SET PASSWORD FOR lengqian =PASSWOED("lengqianlengqian");

Something need to know 10/5/2017 8:49:45 PM

1. When I manually entered the table row, found that after entering 5 lines, and then input, the ID (auto_increment) automatically starting from 6.
2. The input line is to be noticed.

3. create table xxx(id not null auto_increment,                city not null default "wuhan",                primary key (id));###插入行insert into xxx(id, city)values(null,"guangzhou"),        (null,null);#city直接显示为null.

Add

1. MySQL database is divided into server and client, after installation in Windows, the application bar will appear mysql command line client , that is, the client, only on the premise that the server is open, the client and the Web application to properly connect and run. Start the server-side approach, see Windows Start MySQL server under Linux server, start MySQL server 3/2/2018 4:16:27 PM
2. Currently using python2.7, the MySQLdb driver is used to connect to the database and the application, but from python3.0 onwards, pymysql drivers are supported. Configuring the transition details see links pymysql and Python3 3/5/2018 8:49:13 PM

"MySQL must know" notes _ continuous Update