MySQL Add, delete, check, change
Registration, Authorization#创建一个对数据库中的表有一些操作权限的用户, where operation can be replaced with all privileges, DBNAME, TableName can be replaced with *, which means all mysql> grant operation on DBNAME. TABLENAME to ' USERNAME ' @ ' ip_addr ' [identified by ' PASSWD '];mysql> ' Create user ' USERNAME ' @ ' ip_addr ' identified by ' PASS WD ';mysql> revoke operation on DBNAME. TABLENAME from USERNAME; #收回用户的某些权限mysql > Show grants for ' USERNAME ' [@ ' ip_addr ']; #查询用户权限 $ mysqladmin-u username-p [old_pwd] Password new_pwd #更新密码 (note: Do not write if you do not have a password old_pwd)
data type of MySQL fieldint[(M)] #整型double[(m,d)] #双精度浮点型Date #日期类型 format yyyy-mm-dd range 1000-01-01--"9999-12-31char (M) #字符类型blob Text #定长字符串 (will fill with spaces)varchar #变长字符串类型timestamp #时间戳
MySQL data type correlation function Inet_aton (expr) #将一个表示ip地址的字符串转换为整数INET_NTOA (expr) #将一个整数转换为表示ip地址的字符串NOW () #获取当前时间戳TO_DAYS (timestamp) #一年中的哪一天DAYOFWEEK (expr) #一周中的哪一天, starting in Sunday, counting from 1 weekday (expr) #一周中的哪一天, starting in Monday, from 0 count DayOfMonth (expr) #一月中的哪一天, Count dayofyear from 1 (expr) # Day of the year, count from 1 month (expr) #月份1 -12dayname (expr) # Name of the week MonthName (expr) #月份的名字QUARTER (expr) #一年中季度1 -4week (expr) &NBS P #一年中的周数0 -52year (expr) #年份HOUR (expr) #小时0 -23minute (expr) #分钟0 -59second ( Expr) #秒0 -59period_add (expr1, expr2) #增加N个月, expr1 as a date string, EXPR2 for increased time (same as EXPR1 minimum unit) Period_diff (EXPR1, expr2) #两个最小单位相同的日期字符串比较DATE_ADD (expr1, INTERVAL expr2 Date _type) #增加以date_type为单位的expr2个时间间隔DATE_SUB (expr1, INTERVAL EXPR2 date_type) #减少以date_type为单位的expr2个时间间隔 data_fromat (EXPR1, format) # The time represented by EXPR1 is output in format, where the related characters of format have the following meanings:%W week name (Sunday ... Saturday)%d The date of the month with English prefixes (1st, 2nd, 3rd, etc.). )%Y year, number, 4-digit%Y year, number, 2-bit%a abbreviated weekday name (Sun ... Sat)%d days in the month, number (00 ... %e number of days in the month, number (0 ... %m months, numbers (01 ... %c month, number (1 ... %b Abbreviated month name (Jan ... DEC)%j Days of the year (001 ... 366)%H hours (00 ... %k hours (0 ...) %h hours (01 ...) %I) Hours (01 ... %l hours (1 ...) %i minutes, Numbers (00 ...) %r time, 12 hours (Hh:mm:ss [ap]m)%T time, 24 hours (hh:mm:ss)%s seconds (00 ... ()%s seconds (00 ... %p am or pm%w one days of the week (0=sunday ... 6=saturday)%u week (0 ... 52), here Sunday is the first day of the week%u week (0 ... 52), here Monday is the first day of the week with a text "%".
the increase, deletion and investigation of the databasemysql> CREATE DATABASE DBNAME; #创建数据库mysql > Use DBNAME; #连接数据库mysql > select Database (); #查看当前连接的数据库mysql > show databases; #查看所有的数据库mysql > DROP Database DBNAME; #删除数据库
The increase, deletion, investigation and modification of the tablemysql> drop table if exists TABLENAME; CREATE table if not exists TABLENAME (KEY1 varchar) TYPENAME1, KEY2 TYPE NAME2, .... ); #建表mysql> Show tables; #查看当前连接数据库中所有的表名mysql> desc TABLENAME; #查看表结构mysql> Rename table Tablename_old to tablename_new; #表的重命名mysql> drop table TABLENAME; #删除表mysql> ALTER TABLE TABLENAME add KEY TYPENAME; #表中增加一列mysql> ALTER TABLE TABLENAME Modify column KEY1 TYPENAME1 [(befor | after) KEY2] ; #修改字段类型 mysql> ALTER TABLE TABLENAME drop KEY; #删除表的一个字段mysql> INSERT INTO TABLENAME [(KEY1, KEY2, ....)] VALUES (VALUE1, VALUE2, ....); #表中插入数据mysql> Select KEY1, KEY2, ... from TABLENAME where EXPRESSION; #查询表中数据mysql> Delete from TABLENAME where EXPRESSION; #删除表中数据mysql> Update TABLENAME set key1=value1, Key2=value2, ..... where EXPRESSION; #更新表中数据Note: Because the DateTime field setting defaults is not supported in MySQL, only timestamp can be used, but timestamp only 2038
Advanced usage of table queries
predicate: All (qualifying All), DISTINCT (only one for the same field data), Distinctrow (only one entry for the same record), top (a number of records for the first and last, when the percentage is used, top N percent,n is a number), as ( Alias the result field, as preceded by an original name, followed by an alias)
comparison characters:= #等于 > #大于 < #小于 >= #大于等于 <= #小于等于 <> #不等于!> #不大于!< #不小于 not #用 Indicates the opposite before the comparison expression
pattern matching (must be after like):% #替代一个或者多个字符-#仅替代一个字符 [charlist] #字符列中任何单一字符 [^charlist] #不在字符列中的任何单一字符 [NOT] between ... and.. The range of #指定要搜索的闭区间 [not] in #用于 [does not] match any one of the values in the list order by KEY1 (asc| DESC), KEY2 (asc| DESC) ... #将结果以KEY的 (ascending/descending) order, the default is ASC ... And ... #同时满足两个条件GROUP by KEY1, KEY2 ... [Having
CONDITION] #以KEY1, KEY2, and so on, having called some aggregation functions to filter the grouped query results
Aggregation functions:SUM (key) #求和AVG (key) #求均值COUNT (key) #计数COUNT (*) #所有记录计数MAX (key) &N Bsp #最大值MIN (key) #最小值VAR (key) # Variance Stdev (Key) #标准差FIRST (key) #第一个LAST (key) #最后一个CONCAT (KEY1, KEY2, ...) &N Bsp #将keys连接起来成为字符串 #将查询结果写入到另一个表TABLENAME1中mysql > select KEY1, KEY2, ... to TABLENAME1 from TABLENAME2 where expression; #将从TABLENAME1, TABLENAME2 Two tables query data merge show mysql> select KEY1, KEY2, ... from TABLENAME1 where EXPRESSION1 Union Select KEY3, KEY4,.. From TABLENAME2 where expression2; #TABLENAME1表中key3大于子查询结果的记录mysql > select KEY1, KEY2, ... from TABLENAME1 where KEY3 (>|=|<|<>) (any| all| SOME) (select KEY3 from TABLENAME2 where EXPRESSION); #TABLENAME1表中key3 [not] belong to the records in the subquery results list Mysql> select KEY1, KEY2, ... from TABLENAME1 where KEY3 [not] in (select KEY3 from TABLENAME2 where EXPRESSION); #根据子查询的结果来决定是否执行从TABLENAME1表中进行查询mysql > select KEY1, KEY2, ... from TABLENAME1 where exists (select KEY3 from TABLENAME2 where EXPRESSION);
query (delete) content before 5minSELECT * from Email_info where minute (now ()-c_time) > 5; This method will have a problem if it crosses the whole pointSELECT * from Email_info where Timestampdiff (minute, C_time, now ()) > 5;Delete from Email_info where Timestampdiff (minute, C_time, now ()) > 5;Note: Minute is a function of converting time to component clocks, similar to year, DayOfYear, month, MonthName, DayOfMonth, week, weekday, dayname, hour, minute, second, etc.
indexes, views, triggers, stored procedures, cursors, transactionsA
view is a virtual table, which is equivalent to an alias of an SQL statement, which can be added, deleted, and manipulated on the views, provided that there is no group by grouping, no union connection, no subquery, no and no aggregation function, no distinct, Export (computed) columns
index, view increment, delete, changemysql> CREATE index index_name on TABLENAME (KEY); #创建索引mysql> Show index from TABLENAME; #查询索引mysql> DROP INDEX index_name; #删除索引mysql> CREATE View view_name (KEY1, KEY2, ...) as select KEY3, KEY4 from TABLENAME; #创建视图mysql> drop View view_name; #删除视图 a
stored procedure is simply a collection of one or more MySQL statements that are saved for later use. It can be considered a batch file, although their role is not limited to batch processing. 3 main benefits of stored procedures: simple, secure, high performance.
creation, deletion, invocation of stored procedures#创建一个存储过程PROC_NAME, where delimiter//tells the command-line utility to use//as the new statement terminator, you can see that the end definition end//, instead of end, that indicates that the stored procedure ended; finally use delimiter; Restore the original statement terminator, because in MySQL, the default is the Terminator, in order for the stored procedure to work properly, so you need to replace the stored procedure in procedure. #参数中的IN/out indicates whether the parameter is passed in or out, the parameter is untyped, can be a simple variable, or it can be a table name (which can be queried directly by a select @arg), usually stored procedures using SELECT ... into ... Statement saves the result to an output variable delimiter//create procedure proc_name ([in/out] ARG1 TYPENAME, [In/out]arg2 TYPENAME, ...) BEGIN sql_sentences; End//delimiter; Mysql> Call Proc_name (@arg1, @arg2, ...); #调用存储过程, MySQL in front of variables need to add @mysql> drop procedure Proc_name if exists; #删除存储过程mysql > Show CREATE PROCEDURE proc_name; #查看创建存储过程的sql语句mysql > Show procedure status like ' EXP '; #查看存储过程的相关信息A
cursor is a database query stored on a MySQL server that is not a SELECT statement but a structure set that is retrieved by the statement. After the cursor is stored, the application can scroll or browse or change the data in it as needed. Cursor Use steps:
- Need to declare before use
- You must open the cursor (execute the relevant SQL query statement) when you use it, use the FETCH statement to access each row after the cursor opens, fetch what data to retrieve, where it is stored, and point the cursor to the next line (that is, the next fetch will retrieve the next row)
- According to the required row data, must be closed after use
DECLARE cursor_name CURSOR for select KEY1, KEY2, ... from TABLENAME; #声明一个游标open cursor_name; #打开游标FETCH cursor_name into TABLENAME2; #将游标获取的一行记录存到数据表中. Close cursor_name; #关闭游标a
trigger is an SQL statement that is automatically executed by MySQL in response to any of the statements in insert, UPDATE, delete three. Trigger creation criteria: Unique Name, specific table association (view, temp table not), associated action (insert/update/delete), and execution before/after the associated statement executes.
creation, deletion of triggersMysql> CREATE Trigger Tri_name (Before/after) (Insert/update/delete) on TABLENAME for each row BEGIN Sql_sentense end;m ysql> drop trigger Tri_name;
Transactions can be used to maintain the integrity of a database, which guarantees that a batch of MySQL operations are either fully executed or not executed at all. The key to managing transactions is to break down the group of SQL statements into logic quickly and specify when the data should be rolled back and not rolled back. Start transaction represents the beginning of a transaction.
Creation of transactionsStart transaction; #事务开始SQL_SENTENCE1; Sql_sentence2, ..... (Commit/rollback); #事务提交/Rollback # Note: Typically, the result of the execution of the previous SQL statement is judged, and if execution fails, the rollback is executed, and if all the SQL statements in the transaction execute successfully, commit commits the changes to the database.
data import, export
import data in txt formatmysql> Load Data local infile ' FILENAME.txt ' into table TABLENAME; #注: TXT fields are tab-delimited
import a database in SQL format (note: The SQL file here contains the built-in statements and the data in the table)
mysql> use DBNAME; source Filename.sql #方法一
$ mysqldump-u username-p DBNAME <filename.sql #方法二
$ mysql-u username-p-D DBNAME <filename.sql #方法三
Export all table structures in the entire database$ mysqldump-uusername-p [-hip_addr] dbname>filename.sql #包含建表语句以及插入数据的insert语句 $ mysqldump- uUSERNAME -p-d [- Hip_addr] DBNAME >filename.sql #仅包含建表语句 $ mysqldump- uUSERNAME- p [- Hip_addr] --no-create-info dbname>filename.sql #仅包含插入数据的insert $ mysqldump- uUSERNAME- p [- Hip_addr] DBNAME TABLENAME >filename.sql #导出一张表的全部内容
MySQL Learning Summary
