Database Base Operations

Database base operation is turned on using the tab completion function in the MySQL database

    1）修改主配置文件/etc/mysql/my.cnf（mysql和mariadb目录有些不同）        vim /etc/mysql/my.cnf            [mysql]            #no-auto-rehash            auto-rehash    2）重启并登录测试    3）可以设置临时支持他不键补全        msyql -uroot -p --auto-rehash

Make MySQL support Simplified Chinese

    vim /etc/msyql/my.cnf（mysql和mariadb目录有些不同）        [client]        default-character-set=uft8        [mysql]        default-character-set=uft8    service mysqld restart

MySQL database table and library management operations

    Note: Most commands are case-insensitive, and some unique commands can be abbreviated 1) View database structure 1 View database list information show: Help show  Help Item:item is shown below to take a closer look at helping show authors show {binary | master} logs Show                Binlog events [in ' Log_name '] [from POS] [limit [offset,] row_count] Show character set [Like_or_where] Show collation [Like_or_where] show [full] columns from tbl_name [from db_name] [like_or_where                 ] Show contributors show CREATE DATABASE db_name show Create event Event_Name Show Create function Func_name Show CREATE PROCEDURE proc_name show Create t Able Tbl_name Show create trigger trigger_name show CREATE VIEW View_name Sho                w databases [like_or_where] Show engine Engine_name {status | mutex} show [storage] engines Show ErroRS [limit [offset,] row_count] Show events show function Code func_name Show                 function status [Like_or_where] show grants for user show index from Tbl_name [from Db_name] Show master status show open tables [from db_name] [like_or_where] Show plug INS show procedure Code Proc_name Show procedure Status [Like_or_where] Show                Privileges Show [Full] processlist show profiles [types] [for query n] [offset n] [limit n]  Show Profiles show slave hosts show slave status Show [global | Session] Status [Like_or_where] Show table status [from db_name] [like_or_where] Show [FULL ] tables [from db_name] [like_or_where] show triggers [from db_name] [like_or_where] show [g Lobal | Session] VariablES [like_or_where] Show warnings [limit [offset,] row_count] Like_or_where: Like ' pattern ' |                WHERE Expr 2 View data table information use database name; # #使用数据库 show tables;                User db_name;                Use DB1;   Select COUNT (*) from mytable;                # selects from db1.mytable use DB2;   Select COUNT (*) from mytable; # Selects from db2.mytable 3 Displays the structure (field) of the data table describe [database.]            Table name describe Mysql.user; Help describe {describe | DESC} tb_name [Col_name | wild] tbl_name: Table name col_name: Column Name 2) DDL (Data definition Language) Statement Action 1 Create a new library and create a new table creates database name; CREATE TABLE table name (define field); Help      Create Help Item:items, as shown below, to further review the CREATE DATABASE create event          Create function Create function UDF CREATE index CREATE PROCEDURE                Create server Create TABLE Create tablespace create trigger Create user CREATE View example: MariaDB [mysql]> CREATE DATABASE Authe                R                MariaDB [mysql]> use Auther; MariaDB [auther]> CREATE TABLE users (user_name char) not null,user_passwd char (+) default ', PRIMARY key (User_nam                e));                MariaDB [auther]> Show tables;        MariaDB [auther]> desc users; 2 Drop Delete library and delete table drop tables [database name.]            Table name; drop database name; drop [temporary] table [if exists] tb_name [, tb_name2 ...]; Help Drop helper Item:item is shown below to further review the drop database drop event Dr           Op function Drop function udf     Drop Index drop procedure drop Server drop table Drop tab Lespace drop trigger drop user Drop View example: Mariad                B [haha]> drop table haha.users;        MariaDB [haha]> drop database haha; 3 "ALTER TABLE structure ALTER tables [database name]                The table name help alter helper Item:item is shown below to further review the ALTER DATABASE ALTER event ALTER function ALTER LOGFILE GROUP ALTER PROCEDURE alter serve R ALTER TABLE ALTER TABLESPACE ALTER VIEW 4 copy table Create Tabl            E tb_name2 select * from tb_name1;        CREATE TABLE Tb_name2 Select Id,user_name from tb_name1;        5 Create a temporary table creating temporary table tb_name;            6 "Table rename ALTER TABLE Tb_name_old to Tb_name_new; Rename Table tb_nam_old to Tb_name_new;                    Helping rename help Item:item is shown below to further see the Rename table rename user 3) DML (Database Operations language) Statement action 1 Insert inserts new data into table name (Field 1, Field 2, ...). VALUES ([Call Function] ' value of field 1 ', [Call Function] ' field 2 ', ...            ）; Help insert Insert [low_priority | delayed | high_priority] [ignore][into] tbl_name [(col_n Ame,...)] {values | value} ({expr | default},...), (...),...                                [on duplicate key update col_name=expr [, col_name=expr] ...] Insert [low_priority | delayed | high_priority] [ignore][into] tbl_name set col_name={expr | default},.. .                        [on duplicate key update col_name=expr [, col_name=expr] ...]                 Insert [low_priority | high_priority] [IGNORE] [into] tbl_name [(Col_name,...)] Select ...                            [on duplicate key update col_name=expr[, col_name=expr] ...]               Example: MariaDB [auther]> INSERT into users (USER_NAME,USER_PASSWD) VALUES (' Shen ', password (' 1234 ')); MariaDB [auther]> INSERT into users values (' list ', password (' 1234 '));                # #字段内容也可以省略 MariaDB [auther]> select * from users; +-----------+--------------------------------+                | User_name |                user_passwd | +-----------+--------------------------------+                | List |                *a4b6157319038724e3560894f7f93 | | Shen |                *a4b6157319038724e3560894f7f93 | +-----------+--------------------------------+ 2 Update change original data Update table name set field name 1= value 1[, field 2= value 2] where conditional expression                        Help Update single-table syntax:update [low_priority] [Ignore] tbl_name                        Set Col_name1={expr1|default} [, Col_name2={expr2|default}] ...                        [Where Where_condition]        [Order BY ...]                [Limit Row_count] multiple-table syntax:update [low_pri                        Ority] [Ignore] tbl_name set Col_name1={expr1|default} [, Col_name2={expr2|default}] ... [WHERE Where_condition] Example: MariaDB [auther]> update Auther.user                S set User_passwd=password (") Where user_name= ' list ';                MariaDB [auther]> SELECT * from Auther.users; +-----------+--------------------------------+                | User_name |                user_passwd | +-----------+--------------------------------+                |                                List |                | | Shen |                *a4b6157319038724e3560894f7f93 | +-----------+--------------------------------+ MariaDB [(none)]> update Mysql.user set Password=password  (' xm1234 ') where user= ' root '; # #修改root的密码 MariaDB [(none)] > Flush Privileges; # #刷新权限 3 "Delete delete unwanted data" Delete from table name where conditional expression; Help Delete Singl                        E-table Syntax:delete [low_priority] [quick] [ignore] from Tbl_name                        [Where Where_condition]                        [Order BY ...] [Limit Row_count] multiple-table syntax:delete [low_priority] [quick] [Ignore] tbl_name [.*]                    [, tbl_name[.*]] ... from table_references [where Where_condition]                        delete [low_priority] [quick] [ignore] from tbl_name[.*] [, tbl_name[.*]] ... Using table_references [where Where_condition] Example: MariaDB [A                 Uther]> Delete from auther.users where user_name= ' list '; 4) DQL (data Query Language) statement Operation SELECT query statement select Field Name 1, field Name 2, .... from table name [where conditional expression]; Help Select SELECT [All | distinct | disti Nctrow][high_priority][straight_join][sql_small_result] [sql_big_result] [Sql_buffer_result][sql_cache | sql_no_                    Cache] [sql_calc_found_rows]select_expr [, select_expr ...] [From Table_references [where Where_condition] [group by {col_name | expr | position }[ASC | desc], ... [with rollup]]                    [Having where_condition]                    [ORDER BY {col_name | expr | position}[asc | desc], ...] [Limit {[offset,] row_count | row_count offset Offset}]                    [Procedure procedure_name (Argument_list)] [into outfile ' file_name ' [character set charset_name] Export_options|into dumpfile ' file_name ' |into var_name [, Var_name ] [FOR Update |-lock in share mode]] distinct: Data deduplication; Sql_cache: Explicit Specifies the result of a cached query statement; Sql_no_cache: explicitly specify not to cacheThe result of a query statement; Query_cache_type server variable has three values: on: Enabled; Sql_no_ca Che: not cached; cache is cached; off: off; demand: on-demand caching; SQL_CAC                He: Cache; default no cache; Example: MariaDB [auther]> select * from Auther.users;                MariaDB [auther]> Select user_name from Auther.users;        MariaDB [auther]> SELECT * from auther.users where user_name= ' Shen '; Query execution path: request-to-query cache request-to-query cache--resolver-to-preprocessor----Query execution engine--storage engine--cache- The execution flow of the response SELECT statement: From--------and--and have------" T--and limit fields can use aliases: col1 as ALIAS1, col2 as ALIAS2, ... : As can sometimes omit multiple table queries: Join operation: Cross join: Cartesian product; Inner connection: Equivalent connection: Make Table                To establish a connection in an equivalent manner;    No equivalent connection: Natural connection from external connection: Left outer connection: From TB1 left join TB2 in tb1.col = Tb2.col right outer connection: from TB1 R join TB2 o n Tb1.col = tb2.col subquery: A query is nested within a query; for subqueries in a WHERE clause; (1 ) is used to compare subqueries in expressions: Subqueries can only return a single value, (2) subqueries for in: Subqueries can return a list value, (3) subqueries for exists: for fro                         Sub-query in M clause; Select Tb_alias.col1, ... from (SELECT clause) as Tb_alias WHERE clause; Union query: Combine execution results of multiple query statements; Union SELECT clause Union select cluase; 5) condition Control (very important, generally not without control conditions) 1 The WHERE statement specifies the filter condition for the "select" function; filter: boolean expression; where Where_condition s        Elect * from Tb_name where a=b; 2 The GROUP BY statement "groups" the results of the query according to the specified fields for the "aggregation" operation; 3 "The ORDER BY statement checks theThe poll results are sorted, ascending ASC, descending desc.        4 The Having statement conditionally filters the result of grouping aggregation select * from Tb_name GROUP BY score have count (*) >n; 5 The Limit statement limits the number of results of the output [limit {[offset,] row_count | row_count offset-offset}] limit Row_count 6 "Related conditional control operator arithmetic operator: +,-, *,/,% comparison operator: =, <>,! =, <=>,;, >=, < <= interval: between min and Max list: in () fuzzy comparison: like (),% matches any, _ matches one character value: Is null, is not NULL logical operator: And,or,not 7 "Query cache: Cache query execution result; key: hash value of query statement; Value: Query statement            6) MySQL function 1 "concat (): String connection function 2" math function avg (), SUM (), Max (), Min (), count (), 3 "Text processing function        Trim (), locate (), upper (), lower (), substring ();            4 "Time function date (), Curtime (), Day (), year (), now () 7) Example: establishing a database imployee_salary show databases; Create Database imployee_salary;mployee_salary; Create table it_salary (Job category char () not NULL, name char () not NULL, age int, employee ID int NOT NULL,            Education char (6), salary int not null,primary key (employee ID));            Insert into it_salary (Job category, category, name, age, employee ID, education, years, salary) values (' Network engineer ', ' Wang ', 27,011, ' undergraduate ', 3,4800);    SELECT * from It_salary; 8) Explain: Parse the execution path of the query statement

Other common commands

    show status ：显示广泛的服务器状态信息    status：显示当前服务器状态    show grants：显示授权用户的安全权限    show errors or warnings：显示服务器错误或警告信息    select user() or current_user：显示当前连接用户    select now() or current_timestamp：显示当前时间    select database()：显示当前数据库

How to solve MySQL data garbled

    1）mysql数据乱码的可能原因        服务器系统字符设置问题        数据表语系设置问题        客户端连接语系的问题    2）解决方法        1》在创建数据库时设定            create database 库名 character set ‘utf8‘ collate ‘utf8_general_ci‘;        2》在创建表时设定字符集            create table tbl_name(字段 格式) default charset=utf8;        3》使用set names 设置默认字符集            set names utf8;  ##或者charset utf8        4》永久修改，修改主配置文件在[mysql]字段中加入default_character_set=utf8。            vim /etc/my.cnf                [mysql]      ##注意，不是[mysqld],否则启动时会报错。                default-character-set=utf8

Database Base Operations