What is a mysql

MySQL is an open source relational database management system that uses structured Query Language (SQL) for database management. MySQL is developed by MySQL AB in Sweden and is currently a product of Oracle. MySQL is one of the most popular relational database management systems. MySQL keeps data in different tables instead of putting all data in one large warehouse, which increases speed and flexibility. The structured Query Language used by MySQL is the most commonly used standardized language for accessing databases. MySQL software adopts the double licensing policy, which is divided into community edition and enterprise edition. Due to its small size, fast speed and low total cost of ownership, especially the characteristics of open source, MySQL is generally selected as the website database for the development of small and medium-sized websites. A Database is a warehouse that organizes, stores, and manages data according to data structures. Each database has one or more different apis for creating, accessing, managing, searching, and copying saved data. Although we can store data in files, reading and writing data in files is relatively slow. Using a relational database management system to store and manage large amounts of data makes this easier. Mysql's logo is a dolphin, as shown below:Copy the code

Mysql configuration file

1/ For Linux

The configuration file is my.cnf file in /etc, which is modified in [mysqld] or mysql server configurationCopy the code

2/ For Windows

The configuration file is my.ini, which is modified in the [mysqld] section or mysql server configuration sectionCopy the code

3/ Query the parameters in the mysql configuration file

Show variable like '% XXXXSSSS %'; show variable like '% XXXXSSSS %'; For example, show variables like '%secure%'; View all parameters that contain secure. For example, what is the current value of secure-file-privCopy the code

4/ Modify parameters in the configuration file

Set global = XXX Restart the mysql service after modifying the configuration file for the modification to take effectCopy the code

Restart or stop the mysql service

Service mysql start # mysql stop # service mysql restart # mysql status # Whether you use mysql or mysqld depends on the name of the service you are configuring. Don't worry about the name. Cp support-files/mysql.server /etc/init.d/mysqlCopy the code

How do I log in to the mysql database

Mysql -h host -u user -ppassword -p database -p port Mysql > select * from user where username = 'localhost'; mysql > select * from user where username = 'localhost'; mysql > select * from user where username = 'localhost'; Access denied for user XXXX to database # -p Mysql -u root -p mysql-p123456 mysql -u root -p mysql-p123456 mysql -u root -p mysql-p123456  mysql -u root -p123456 -p mysqlCopy the code

Change the mysql login password

<1> Show variables like 'validate_password%' to check the mysql password setting policyCopy the code

<2> As shown in the preceding figure, the password must be 8 bits long. The password authentication strength is medium(there are three levels low,medium, and strong). <3> As you can see from the figure above, the current password length is 8 bits, if you don't mind, you don't need to change it. Set global validate_PASSword_length =6Copy the code

<4> To change the password validation strength, run the set global validate_PASSword_policy =LOW command. ALTER USER 'root'@'localhost' IDENTIFIED BY 'new password '; ALTER USER 'root'@'localhost' IDENTIFIED BY' new password '; Command to modify. <6> Introduction to mysql password policy parameters; Validate_password_length: specifies the total length of a fixed password. Validate_password_dictionary_file: Validate_password_mixed_case_count: The password must contain at least the number of upper and lower case letters. Validate_password_number_count: Specifies the minimum number of Arabic digits in the password. Validate_password_policy: specifies the password strength verification level. The default value is MEDIUM. Validate_password_special_char_count: Specifies the minimum number of special characters in a password. <7> Values of validATE_password_policy are as follows: 0/LOW: validates only the length. As long as the length is the same as required by validATE_PASSword_length. 1/MEDIUM: verifies the length, digits, case, and special characters. 2/STRONG: verifies length, digits, case, special characters, and dictionary files.Copy the code

Mysql > query user

Viewing All Users

MySQL has a built-in database named MySQL. This database stores some data of MySQL, such as users, permission information, stored procedures, and so on. So we can view all users through the following query statement. mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,'''; ') AS query FROM mysql.user; mysql> SELECT User, Host, Password FROM mysql.user; mysql> SELECT DISTINCT User FROM mysql.user;Copy the code

View the current login user

mysql> SELECT user(); mysql> SELECT current_user(); // No parenthesesCopy the code

And,or,in, not in

In: must be parentheses (), cannot use [] parentheses,, recommend that can use in do not use or, because in performance is better and easier to understand. Not in: same as above. Note: mysql preferentially processes AND statements when both AND and OR statements are present. () is commonly used to determine processing order and disambiguate.Copy the code

Mysql > select * from ‘mysql’;

<1> mysql -u XXX -pxxx # -u username -p password <2> Show databases; <3> Enter a database use db_name; <4> Show all existing tables in the current database: show tables; <4> CREATE a new DATABASE and specify the encoding format of the DATABASE CREATE DATABASE DBName DEFAULT CHARSET UTF8MB4 COLLATE UTF8MB4_general_CI; <5> Create table, CREATE TABLE 'author' ('authorid' char(20) NOT NULL,'name' char(20) NOT NULL,'age' char(20) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1; <6> select * from table_name; <7> select id,name,age,first_dpt_name from 表名; <8> show columns from table name; Insert table name (id,name) values (007,' XXX ') <10> Exit mysql exit <11> Query data type of a column in the table SELECT CHARSET(email) FROM tablename; Alter table old table name rename new table name; Alter table XXX add column name varchar(10) alter table XXX add column name varchar(10); Alter table XXX drop column name; Alter table XXX modify address char(10) alter table XXX modify address char(10) ALTER DATABASE caitu99 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; ALTER TABLE TBtest CONVERT TO CHARACTER SET UTF8MB4 COLLATEutf8mb4_general_ci; Alter table TBtest character set UTF8MB4 COLLATE UTF8MB4_general_CI; ALTER TABLE tbtest MODIFY email VARCHAR(60) CHARACTER SET UTF8MB4 COLLATE UTf8MB4_general_ci; <13> Delete table data and retain table structure truncate TABLE KUaISHou_SPIDer_DF_temp; <14> Delete from table_name where course_id=4; Select * from table_name where table_name = 'XXX'; Select * from table_name where table_name = 'XXX' select * from table_name where table_name = 'XXX' Select * from table_name where table_name = 'XXX' and table_name = 'XXX' where table_name = 'XXX' <16>create table table2 select * from table1; <17>insert into table2 select * from table1; <18>show columns from table_name <18>show columns from table_name Tablename = desc tablename <19> Alter table sname = varchar(20); alter table sname = varchar(20)  student modify column sname varchar(20); <20> The method of changing both the column name and the column data type can be written as alter table table name change column old column name New column name new column data type For example :basic_df Column sname is of type char(20) and is now changed to stuname Alter table basic_df change column sname stuname vARCHar (20); <21> DROP Truncate DELETE Differences between the three types DROP TABLE name Deletes the table structure and data. The most thorough deletion mode is to release all space occupied by the table. However, exercise caution when maintaining the truncate table name to preserve the table structure and delete table data. The index content is deleted but the index name is retained. When inserting data into the table, the index content is automatically added, and no index is required. When a table is truncate, the space occupied by the table and index will be restored to the initial size. This command deletes all data in the table at one time without recording the deletion operation in logs. Therefore, data cannot be restored. The DELETE operation does not reduce the space occupied by the table or index. This command deletes data line by line and records the deletion operation in a log, which can be rolled back later. If a large amount of data is to be deleted, do not use this command. In other words, DROP deletes a table, while TRUNCate and DELETE delete data in the table. In addition, the differences between TRUNCate and DELETE are as follows: <22>desc tablename <23> Add a column and set the primary key to Describe. Alter table table_name add ID BIGint primary key auto_increment; <24> Drop database name; < 25 > change the database name Command did not change the database name directly, but can be indirectly To create a new database, and then copy table to the new database, finally, the original database delete < 26 > in data table, delete data delete a certain condition data is a very cautious, Delete from table_name where XXXX if there is no where statement, the entire table will be deleted. SELECT * FROM information_schema.processList; SELECT * FROM information_schema.processlist; 2/ Show processList; 3/ Kill a process kill thread_id; <28> Show index from table_nameCopy the code

Push files from the server to the database

The command is as follows:  load data local infile '/root/houzhen03/xxx' into table xxx fields terminated by ',' optionally enclosed by '"' escaped  by '"' lines terminated by '\n'; Description of the parameters in the command: <1> fields terminated by ',' > <2> > lines optionally enclosed by 'fields terminated by ',' > <2> > lines optionally enclosed by' fields terminated by ',' Terminated by '\n' # separator between linesCopy the code

Import/export of database files

<1> Secure_file_priv is used to limit LOAD DATA, SELECT... OUTFILE, LOAD_FILE() to the specified directory. Because the secure_file_priv parameter is read-only, set global secure_file_priv= "" cannot be used; If the <2>secure-file-priv command is NULL, mysqld is not allowed to be imported or exported. For example, if secure-file-priv is set to /var/lib/mysql-files/, the import and export of mysqld can only be performed in /var/lib/mysql-files/. <3> Secure-file-priv has no value, indicating that the import and export of mysqld in any directory is not restricted.Copy the code

Char vs. varchAR

<1> VARCHar is more flexible than CHAR, but char is more efficient than VARCHar. <2> Store the string 'ABC', using char(10), Varchar2 (10) indicates that the stored character will take up to 10 bytes (including 7 empty characters, which will be automatically filled with 7 Spaces). Varchar2 (10) indicates that the stored character will take up to 3 bytes. 10 is the maximum value. It would be slightly more efficient to intercept <3>char than vARCHAR. Since the length of the char type is fixed, the system does not need to change the value based on the actual length of the variable value. Varchar does not. Char is space for time efficiency, while VARCHAR is space efficiency first. <6> Char is stored in 1 byte for ASCII characters and 2 bytes for Chinese characters. Varchar stores 2 bytes for each English character and 2 bytes for each Chinese character, both of which store non-Unicode character data. <7> In MySQL, char, varchar, and text can be used to store data of the character type. Char, varchar, and text can be used to specify the maximum length of the character, but text cannot be used. As it is stored, it is intercepted). Char > varchar > text <8> Use vARCHAR for fields with fixed length that are often changed using a char. Use vARCHar for fields with more than 255 characters. Use varchar only or textCopy the code

The difference between bigint and int

Bigint occupies more disk space and memory space. After all, the memory space is limited. Invalid usage leads to more data input and output, increasing the I/O pressure, which is bad for performance. Therefore, it is recommended to use int unsigned when adding a primary key, but not bigInt.Copy the code

How to use like in mysql?

Dataframe = call. get_data_from_db("select XXX, XXX from table where rank = 'A%'", "raw_db"); Is A % ', '%, if to be closing in on A' % '11 / problem: A sqlalchemy. Exc. DataError: (pymysql.err.DataError) (1406, "Data too long for column 'global_network' at row 25") MySQL > SET @@global.sql_mode= ' '; 12/ distinct usage select distinct column name from table; Select distinct(column name) from table select distinct(column name) from table; 13/ drop table if exists table_name; If the table exists, delete itCopy the code

There are two types of variables in MySQL

1/ System variable:

<1> SESSION (SESSION or LOCAL) level: only affects the current SESSION. <2> GLOBAL level: A <3> quantity valid for the entire MySQL server with both levels MySQL initializes session level variables with global-level variables when establishing a connection, but once the connection is established, changes to global-level variables do not affect session level variables. <4> Check the values of system variables by using the show vairables statement: show variables like 'log%'; show variables where Variable_name like 'log%' and value='ON'; Show SESSION /local variables show SESSION /local variables show SESSION /local variables show SESSION /local variables show global variables; <5> Set and change the value of the system variable When the MySQL server is started, there are two ways to set the value of the system variable: 1) command line parameters, such as: Mysqld --max_connections=200 2) mysqld --max_connections=200 2) mysqld -- mysqld --max_connections=200 SET @@GLOBAL.var_name = value; SET SESSION var_name = value; SET @@SESSION.var_name = value; If there is no level qualifier before the variable name, the session-level variable is modified. Note: Unlike at startup, variables set at run time are not allowed to use suffix letters 'K', 'M', etc., but expressions can be used to achieve the same effect, such as: [SQL] SET GLOBAL read_size = 2*1024*1024 If you use show variables, you will find that the Settings do not take effect. This is because using show variables alone is equivalent to using show session variables and querying session variables. Query global variables only with Show Global variables. If you want to change the session variable, you can use something like set wait_timeout=10. Set session wait_timeout=10; Syntax like this.Copy the code

2/ state variable

You can use the show status statement to check the status of the MySQL server. Status variables can be SET only by the MySQL server itself. They are read-only for users and cannot be SET or modified by the SET statement. SQL: Dist_id string 'block ID', fid String' block ID', vv bigint ' SQL Query the ID of the top 10 video files that are played in each group. Select dist_id,fid,vv from (select dist_id,fid,vv,row_number() over(partition by) dist_id order by vv desc) as rn from table1) as t where rn <= 10Copy the code

Mysql uses’ ‘when creating a database table

For example,

  CREATE TABLE IF NOT EXISTS `basic_df` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `description` varchar(500) NOT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
Copy the code

Basic_df, id, name, and other fields in the database are marked with symbols. In fact, the above table names and attributes are entered by the leftmost key of the numeric row, instead of the usual quotation marks. If the table name or attribute name is a regular name and is not a keyword, it is not necessary to use this symbol. In general, the use of ' ', because you do not know the situation, the data table name or field name and keyword the same name, this will be wrong. To avoid such errors, the database table names and field names are distinguished by ' '. Other, ' 'symbols have no special meaning. If you can ensure that the database table names and field names are normal and not the same as keywords, you can also omit the symbol.Copy the code

A union is a union all

In the real world, you might be in a situation where two tables are merged vertically (up and down), using the UNION and UNION ALL keywords. The difference is that the UNION will sort and de-duplicate data, resulting in low query efficiency.Copy the code

UNION ALL does not carry out deduplication and sorting, and the query efficiency is high. Note that two or more tables that are merged must have the same number of fields; otherwise, an error will be reported.

1. Union: Perform the union operation on two result sets, excluding duplicate lines, which is equivalent to distinct, and sort the default rules at the same time. 2. Union all: Perform union operation on two result sets, including repeated lines, that is, all results are displayed, no matter whether they are repeated or not; 1, union: sort the obtained results 2, union all: No sorting operation will be performed on the obtained results. Select * from student2 where ID < 4 union select * from student2 where ID > 2 and ID < 6 2 Select * from student2 where ID < 4 union all select * from student2 where ID > 2 and ID < 6 Union all only merges query results and does not perform deduplication or sorting operations. On the premise of no deduplication, the execution efficiency of using union All is higher than that of using UnionCopy the code

with xxx as

To summarize the use of with, it is similar to creating a temporary table, and is similar to the operation shown in the red box below. The following is an example:Copy the code

Case when then else end

Case and end are used together; they must exist togetherCopy the code

   -- The first way:
   select
      s.s_id,
      s.s_name,
      s.s_sex,
      case
      when s.s_sex = '1' then 'male'
      when s.s_sex = '2' then 'woman'
      else 'other'
      end as sex,  Create a new field and give it a new name
      s.s_age,
      s.class_id
   from t_b_student s
   where 1 = 1;
Copy the code

   -- The second way
   select
      s.s_id,
      s.s_name,
      s.s_sex,
      case s.s_sex
      when '1' then 'male'
      when '2' then 'woman'
      else 'other'
      end as sex,
      s.s_age,
      s.class_id
   from t_b_student s
   where 1 = 1;
Copy the code

Cast () in mysql

Select count (20.3456 as decimal(10,2)) as num // result: 2019-03-08 15:31:26 select ('2019-03-08 15:31:26') as date 2019-03-08 select ('2019-03-08 15:31:26' as time) as date 15:31:26 select ('2015-11-03 15:31:26' as char) as date // result: 2015-11-03 15:31:26Copy the code

If you want to add a column to an existing table, you can use something like:

alter table TABLE_NAME add column NEW_COLUMN_NAME varchar(255) not null; This statement adds a new column to the existing table, which is the last column in the table. Alter table TABLE_NAME add column NEW_COLUMN_NAME vARCHar (255) not null after COLUMN_NAME; Notice that the command above means to add a new column after a column. Alter table TABLE_NAME add column NEW_COLUMN_NAME vARCHar (255) not null first;Copy the code

Add an index to an existing table

Mysql >ALTER TABLE 'table_name' ADD PRIMARY KEY (' column ') alter table yx_marketing_details add index(id); Mysql >ALTER TABLE 'table_name' ADD UNIQUE(' column ') 3 Mysql >ALTER TABLE 'table_name' ADD INDEX index_name (' column ') 4 Mysql >ALTER TABLE 'table_name' ADD FULLTEXT(' column ') 5 Mysql >ALTER TABLE 'table_name' ADD INDEX index_name (' column1 ', 'column2', 'column3')Copy the code