Shulou(Shulou.com)06/01 Report--

1. Increase the basic knowledge of MySQL: add a single piece of data: insert into table (column name, column name.) Values (value, value, value...) Add multiple pieces of data: insert into table (column name, column name...) Values (value, value, value...), (value, value, value...) Import data from other tables: insert into table (column name, column name...) Select column name, column name. From delete: delete specific data: delete from table where condition change: modify table specific data: update table set column name = value where condition query: query details: select column name 01 as alias, column name 02 from table where condition

Common conditions:

1. Logical operator

And: and; or: or; not: not

2. Comparison operator

Equal to =; greater than >; greater than or equal to > =; less than 60

3. Scope

In: in it; not in: not in it; between: in the interval

Example: select * from test where nid between 5 and 10

4. Wildcard characters

%: match any zero or any number of characters

_: match any character

Example: select * from test where name like'Li%'

5. Null value

Is null: null

Is not null: non-empty

6. Restrictive conditions

Limit 3: take the first three lines

Limit 3pr 5: take 5 lines from the third line

Limit 3 offset 5: take 3 lines from the fifth line

7. Sort

Order by column asc: sort from small to large

Order by column desc: sort from largest to smallest

Order by column 1 desc, column 2 asc: sort from largest to smallest according to column 1, or from smallest to largest if the same

8. Grouping

Select count (column name), sum (column name), max (column name), min (column name) from table where condition group by column name 01, column name 02 order by column name

Special note: group by must be after where and before order by

9. Connecting tables

If there is no correspondence, it does not show:

Select A.xx B.oo from A, B where A.x=B.o does not show any results if there is no data corresponding to A.x=B.o.

Example: select tb1.num,tb2.name from tb1,tb2 where tb1.sid = tb2.sid

If there is no correspondence, it does not show:

Select A.xx B.oo from An inner join B where A.x=B.o An and B have peer-to-peer positions, and no data corresponding to A.x=B.o will display any results.

Table An is all displayed. If there is no correspondence in B, the value is null.

Select A.num, A.name, B.name from A left join B on A.nid = B.nid

Select score.sid,score.course_id,score.num,student.sname from score left join student on score.student_id = student.sid

Table B shows all. If there is no corresponding relationship in B, the value is null.

Select A.num, A.name, B.name from A right join B on A.nid = B.nid

Select score.sid,score.course_id,score.num,student.sname from studentright join score on score.student_id = student.sid

10. Import and export databases

Export existing database data mysqldump-u user name-p password database name > export file path # structure + data mysqldump-u user name-p password-d database name > export file path # structure import existing data mysqldump-u root-p password database name

< 文件路径二、视图 视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个select语句保存在数据字典中的。通过视图，可以展现基表的部分数据；视图数据来自定义视图的查询中使用的表，使用视图动态生成。 1、创建视图 格式：CREATE VIEW 视图名称 AS SQL语句示例： create view temp1 as select score.sid,score.course_id,score.num,student.sname from score left join student on score.student_id = student.sid; 2、修改视图 格式：ALTER VIEW 视图名称 AS SQL语句示例： alter view temp2 as select score.sid,score.course_id,score.num,student.sname from score left join student on score.student_id = student.sid where course_id in (1,2); 3、使用视图 格式：SELECT * FROM 视图名称示例： select * from temp2; 4、删除视图 格式：DROP VIEW 视图名称示例： drop view temp2;三、存储过程 存储过程是一个SQL语句集合，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行。 1、创建无参数存储过程 格式：CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...] 过程体示例： delimiter // create procedure p1() BEGIN select * from score; END// delimiter; 2、创建带参数的参数过程 存储过程可以接收的参数有三种，in：仅参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值，out：该值可在存储过程内部被改变，并可返回，inout：调用时指定，并且可被改变和返回。 示例： delimiter // create procedure p2( in i1 int, out r1 int, inout i2 int ) BEGIN DECLARE temp1 int; DECLARE temp2 int default 5; set temp1 = 10; set i2 = i1 + 100; set r1 = i1 + temp1 + temp2; END// delimiter; 3、执行存储过程 无参数： call p1() 有参数： set @t1 = 0;set @t2 = 1;call p2(1,@t1,@t2);select @t1,@t2; 4、删除存储过程 drop procedure 存储过程名; 5、pymysql执行存储过程 #!/usr/bin/env python# -*- coding:utf-8 -*-import pymysqlconn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)# 执行存储过程cursor.callproc('p1', args=(1, 22, 3, 4))# 获取执行完存储的参数cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")result = cursor.fetchall()conn.commit()cursor.close()conn.close()print(result) 6、条件语句 IF-THEN-ELSE语句 delimiter \\CREATE PROCEDURE p3 ( in i1 int)BEGIN IF i1 = 1 THEN select * from t1; ELSEIF i = 2 THEN select * from t2; ELSE select * from t3; END IF;END\\delimiter ; CASE-WHEN-THEN-ELSE语句 delimiter \\CREATE PROCEDURE p3 ( in i1 int)BEGIN CASE i1 WHEN 1 THEN select * from t1; WHEN 2 THEN select * from t2; ELSE select * from t3; END CASE;END\\delimiter ; 7、循环语句 WHILE-DO…END-WHILE delimiter \\CREATE PROCEDURE p4 ()BEGIN DECLARE i int; set i = 1; WHILE i < 3 DO select i; set i = i + 1; END WHILE;END\\delimiter ; REPEAT...END REPEAT 此语句的特点是执行操作后检查结果 delimiter \\CREATE PROCEDURE p5 ()BEGIN DECLARE i int; set i = 1; REPEAT select i; set i = i + 1; until i >

3 END REPEAT;END\\ delimiter

LOOP...END LOOP

Delimiter\\ CREATE PROCEDURE p5 () BEGIN DECLARE i int default 0; loop_lable: loop set i = I + 1; if I

< 3 then iterate loop_lable; end if; if i >

5 then leave loop_lable; end if; select i; END loop loop_lable;END\\ delimiter

8. Basic functions of stored procedures

String class

CHARSET (str) / / return string character set CONCAT (string2 [,...]) / / concatenate string INSTR (string, substring) / / returns the position where substring first appeared in string, there is no return 0LCASE (string2) / / convert to lowercase LEFT (string2, length) / / fetch length characters LENGTH (string) / / string length LOAD_FILE (file_name) / / read from the file LOCATE (substring, string [) Start_position] is the same as INSTR, but you can specify the starting position LPAD (string2, length, pad) / / add pad to the beginning of string repeatedly until the string length is lengthLTRIM (string2) / / remove the front-end space REPEAT (string2, count) / / repeat count times REPLACE (str, search_str, replace_str) / / replace search_strRPAD (string2, length, pad) with replace_str in str / / add it with pad after str Until the length is lengthRTRIM (string2) / / remove the backend space STRCMP (string1, string2) / / compare the size of the two strings character by character, SUBSTRING (str, position [, length]) / / start with the position of str, take length characters, note: when dealing with strings in mysql By default, the first character subscript is 1, that is, the parameter position must be greater than or equal to 1TRIM ([[BOTH | LEADING | TRAILING] [padding] FROM] string2) / / remove the specified character UCASE (string2) / / convert it to uppercase RIGHT (string2,length) / / take the last length character of string2 SPACE (count) / / generate count spaces

Mathematics class

ABS (number2) / / absolute value BIN (decimal_number) / / decimal to binary CEILING (number2) / / rounding up CONV (number2,from_base,to_base) / / binary conversion FLOOR (number2) / / rounding down FORMAT (number,decimal_places) / / reserved decimal HEX (DecimalNumber) / / convert hexadecimal note: if a string can be passed into HEX (), its ASC-11 code is returned If HEX ('DEF') returns 4142143, you can also pass a decimal integer and return its hexadecimal code. For example, HEX (25) returns 19LEAST (number, number2 [,..]) / / minimum MOD (numerator, denominator) / / residual POWER (number, power) / / Index RAND ([seed]) / / Random number ROUND (number [, decimals]) / / rounding. Decimals returns 1 for decimal places SIGN (number2) / / positive number A negative number returns-1

Date and time class

ADDTIME (date2, time_interval) / / add time_interval to date2CONVERT_TZ (datetime2, fromTZ, toTZ) / / convert time zone CURRENT_DATE () / / current date CURRENT_TIME () / / current time CURRENT_TIMESTAMP () / / current timestamp DATE (datetime) / / return the date part of datetime DATE_ADD (date2, INTERVAL d_value d_type) / / add date or time DATE_FORMAT (datetime) to date2 FormatCodes) / / use formatcodes format to display datetimeDATE_SUB (date2, INTERVAL d_value d_type) / / subtract one time DATEDIFF (date1, date2) / / two date differences DAY (date) / / return date DAYNAME (date) / / English DAYOFWEEK (date) / / week (1-7) 1 is DAYOFYEAR (date) / / the day of the year EXTRACT (interval_name FROM date) / / the specified part of the MAKEDATE (year, day) extracted from the date / / gives the date string MAKETIME (hour, minute, second) / / the generation time string MONTHNAME (date) / / English month name NOW () / / current time SEC_TO_TIME (seconds) / second conversion time STR_TO_DATE (string) Format) / / string conversion time, displayed in format format TIMEDIFF (datetime1, datetime2) / / two time differences TIME_TO_SEC (time) / / time seconds] WEEK (date_time [ Start_of_week]) / / week YEAR (datetime) / / year DAYOFMONTH (datetime) / / Day of month HOUR (datetime) / hour LAST_DAY (date) / / month Last date of date MICROSECOND (datetime) / / microsecond MONTH (datetime) / / month MINUTE (datetime) / / return symbol, plus or minus or 0SQRT (number2) / / squared

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.