After the MySQL dataDir directory is migrated, the error prompts “can’t connect to local MySQL server through socket ‘/ var/lib/MySQL”/ mysql.sock ‘
solve:
Open my.cnf The changes are as follows:
[client]
port = 3306
socket = /new/mysql/ mysql.sock
[mysqld]
datadir=/new/mysql
socket=/new/mysql/ mysql.sock
https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-7.html#mysqld-5-7-7-bug
Previously, view definitions were not permitted to contain derived tables (subqueries) in the FROM clause. This restriction has now been lifted.
Previously, derived tables (subqueries) or views in the FROM clause could not be merged into the outer query if they contained subqueries in the SELECT list. Instead, they were processed using materialization. This restriction has now been lifted so that queries previously executed using materialization can be executed more quickly using merging. (Bug #12755, Bug #11745276, Bug #60417, Bug #11865600)
0x05 Solution
(1) Upgrade mysql version to 5.7.7 and above
(2) Change the subquery to a combination of multiple views
1. You First Database1.1 Introduction to Relational Databases (video)1.2 Attributes of Relational Databases1.3 Query Information_Schema with SELECT1. 4 Tables: At the Core of Every Database (video)1.5 CREATE Your First Few TABLEs1.6 ADD a COLUMN with ALTER TABLEs1.7 Update Your Database as the Structure Changes (video)1.8 RENAME and DROP COLUMNs in Affiliations1.9 Migrate Data with INSERT INTO SELECT DISTINCT1.10 Delete tables with DROP TABLE
2. Enforce Data Consistency with Attribute Constrains2.1 Better Data Quality with Constrains (video)2.2 Types of Database Constrains2.3 Conforming with Data Types2.4 Types CASTs2.5 Working with Data Types (video)2.6 Change Types with ALTER COLUMN2.7 Convert Types USING a Function2.8 The Not-Null and Unique Constrains (video)2.9 Disallow NULL values with SET NOT NULL2.10 What Happens If You Try to Enter NULLs?2.11 Make Your Columns UNIQUE with ADD CONSTRAINT
3. Unique Identify Records with Key Constraints3.1 Keys and Superkeys (video)3.2 Get to Know SELECT COUNT DISTINCT3.3 Identify Keys with SELECT COUNT DISTINCT3.4 Primary Keys (video)3.5 Identify the Primary Key3.6 ADD Key CONSTRAINTs to the Tables3.7 Surrogate Keys (video)3.8 ADD A SERIAL Surrogate Key3.9 CONCATenate Columns to A Surrogate Key3.10 Test Your Knowledge before Advancing
4. Glue Together Tables with Foreign Keys4.1 Model 1:N Relationships with Foreign Keys (video)4.2 REFERENCE A Table with A FOREIGN KEY4.3 Explore Foreign Key Constrains4.4 JOIN Tables Linked by A Foreign Key4.5 Model More Complex Relationships (video)4.6 Add Foreign Keys to the “Affiliations” Table4.7 Populate the “professor_id” Column4.8 Drop “firstname” and “lastname”4.9 Referential Integrity (video)4.10 Referential Integrity Violations4.11 Change the Referential Integrity Behavior of A Key4.12 Roundup (video)4.13Count Affiliations Per University4.14 Join All the Table Together
1. You First Database
1.1 Introduction to Relational Databases (video)
1.2 Attributes of Relational Databases
1.3 Query Information_Schema with SELECT
information_schema is a meta-database that holds information about your current database. information_schema has multiple tables you can query with the known SELECT * FROM syntax:
tables: information about all tables in your current databasecolumns: information about all columns in all of the tables in your current database…
In this exercise, you’ll only need information from the 'public' schema, which is specified as the column table_schema of the tables and columns tables. The 'public' schema holds information about user-defined tables and databases. The other types of table_schema hold system information – for this course, you’re only interested in user-defined stuff.
Instruction 1
Get information on all table names in the current database, while limiting your query to the 'public' table_schema.
-- Query the right table in information_schema
SELECT table_name
FROM information_schema.tables
-- Specify the correct table_schema value
WHERE table_schema = 'public';
Instruction 2
Now have a look at the columns in university_professors by selecting all entries in information_schema.columns that correspond to that table.
-- Query the right table in information_schema to get columns
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'university_professors' AND table_schema = 'public';
Instruction 3
Finally, print the first five rows of the university_professors table.
-- Query the first five rows of our table
SELECT *
FROM university_professors
LIMIT 5;
1. 4 Tables: At the Core of Every Database (video)
1.5 CREATE Your First Few TABLEs
You’ll now start implementing a better database model. For this, you’ll create tables for the professors and universities entity types. The other tables will be created for you.
The syntax for creating simple tables is as follows:
CREATE TABLE table_name (
column_a data_type,
column_b data_type,
column_c data_type
);
Attention: Table and columns names, as well as data types, don’t need to be surrounded by quotation marks.
Instruction 1:
Create a table professors with two text columns: firstname and lastname.
-- Create a table for the professors entity type
CREATE TABLE professors (
firstname text,
lastname text
);
-- Print the contents of this table
SELECT *
FROM professors;
Instruction 2:
Create a table universities with three text columns: university_shortname, university, and university_city.
-- Create a table for the universities entity type
CREATE TABLE universities (
university_shortname text,
university text,
university_city text
);
-- Print the contents of this table
SELECT *
FROM universities;
1.6 ADD a COLUMN with ALTER TABLEs
Oops! We forgot to add the university_shortname column to the professors table. You’ve probably already noticed:
In the development, we may encounter that mybatisplus uses entity class attribute for SQL operation. The entity has this attribute, but the database table does not have this field (that is, entity class attribute is not a database table field). If you don’t handle it, you will report an error.
FIRE
@TableName: Database Table Related
@TableId: table primary key identifier
@TableField: table field identifier
@TableLogic: Table field logical processing annotation (logical deletion)
Solution
@TableField(exists = false): indicates that the property is not a database table field, but is required to be used.
@TableField(exists = true): indicates that the property is a database table field.After adding this annotation to the attributes of the entity class, this field will not map to the database.
@TableField(exist = false)
private String deptName;Oracle self-growing columns can be set as sequences, such as self-growing IDs.
Create a sequence
--Create Sequence dept_deptid_seq
create sequence dept_deptid_seq
increment by 10
start with 1
maxvalue 9990
nocycle
nocache
--Create default sequence no_no_seq
create sequence no_no_seq
Sequence (start with n) as the value of the first sequence, the sequence value (increment by n) for each of the increase, the maximum sequence as the maxvalue (n), the sequence of the minimum value for (minvalue n)
cycle said if the sequence of maximum value is greater than the start from the minimum cycle (the default is not cycle), database cache n said pre-allocated n values stored in memory (the default) 20
The query sequence
Note: After a sequence has been created, the value of the current sequence cannot be directly queryed.nextVal must be used to query the value of the next sequence before using currVal to query the value of the current sequence.
select sequence_name ,last_number, min_value,max_value,increment_by,cache_size from user_sequences
Modify the sequence
alter sequence sequence_name increment by 1 cache 3000
Batch modification sequence
--bulk change sequence
declare
v_sql varchar2(2000);
CURSOR seqs IS select sequence_name from user_sequences where user_sequences.CACHE_SIZE<=20 and user_sequences.LAST_NUMBER>10000;
begin
FOR seq IN seqs LOOP
v_sql:='alter sequence '||seq.sequence_name
||' increment by 1 cache 3000';
dbms_output.put_line(v_sql);
execute immediate v_sql;
end loop;
end;
The cursor places the sequence to be modified. Loop out the sequence name and modify it.
SetObject() method, error Parameter index out of range and com. The MySQL. JDBC. Exceptions. Jdbc4. MySQLSyntaxErrorException error
Learn how to use the setObject() method in PreparedStatement for placeholders in SQL procedures. , appear when to replace the Parameter index out of range error:
1, the number of placeholders, and transfer the Object [] array element number
2, different placeholders used in Chinese?Rather than?
If the above situation is modified, the com. Mysql. JDBC. Exceptions. Jdbc4. MySQLSyntaxErrorException error
see first in the use of setObject () method before and after the difference between a PreparedStatement object:
Object[] objects = new Object[]{"name","age","xixi","23"};
sql = "insert into student (?,?) values(?,?);";
//Get the execution object (connection as Connection object)
preparedStatement = connection.preparedStatement(sql);
System.out.println(preparedStatement.toString());
// determine if the parameter exists, and replace it
if (obs!=null&&obs.length>0){
for (int i = 0; i < obs.length; i++) {
preparedStatement.setObject(i+1,obs[i]);
}
}
System.out.println(preparedStatement.toString());
Perform before:
com. Mysql. JDBC. JDBC42PreparedStatement @ 443 b7951:
insert into student (* * NOT SPECIFIED, the NOT SPECIFIED) values (NOT SPECIFIED, the NOT SPECIFIED * *);
com after execution. Mysql. JDBC. JDBC42PreparedStatement @ 443 b7951:
insert into student (” name “, “age”) values (‘ hee hee ‘, ’23’);
All ** Not SPECIFIED ** placeholders are replaced by elements in the Objects[] array with single quotes.
in the mysql database, the parameters need to add single quotation marks, it is enclosed within a string value. Column names, table names, and so on use backquotes to distinguish them from special keywords (the symbol to the left of the 1 digit).
So if you want to replace non-attribute values, you should choose other methods such as concatenation SQL statements to prevent errors.
Error: Unable to open database files while the database is running.
One, the problem report error two, the solution idea three, the treatment method
First, the problem report error
Can’t open file (errno:24)
Second, the solution
First we need to look at the error log of the database. Then determine whether the table is corrupt or a permission problem. There may also be insufficient disk space to properly access the table; Operating system limitations are also a concern; Use the perl error tool to see specific errors!
linux:/usr/local/mysql/bin # ./perror 24
OS error code 24: Too many open files
Maximum number of open files exceeded! The maximum number of open files on the system is 65535. It cannot be exceeded! The maximum number of open files in the database must be out of limit! Show variables like ‘open_files_limit’; MySQL> reset MySQL = 2048; MySQL> reset MySQL = 2048;
Three, the treatment method
repair table ;
chown mysql Permissions
Clean up junk data from the disk
SELECT
STUFF(
(
SELECT
',' + CONVERT ( VARCHAR ( 10 ), ichr_high_risk_item ) --, 合并后的分隔符 ichr_high_risk_item 需要合并的字段
FROM
initialcarehighrisk --Table Name
where woma_healthno='' --condition, if none will to merge all data
FOR xml path ( '' )
),
1,
1,
''
) Recall SQLServer group sort once to reanalyze
row_number() over ( PARTITION BY t1.col_2 ORDER BY 1 )
-- Code
DELETE FROM table_name t
WHERE t.rowid IN (
SELECT rid
FROM(
SELECT t1.rowid rid,row_number() over ( PARTITION BY t1.col_2 ORDER BY 1 ) rn
FROM table_name t1
) t1
WHERE t1.rn > 1
);
-- PARTITION BY t1.col_2
-- The first grouping is based on the second column
-- ORDER BY 1
-- then sort by the first column
-- row_number()
-- reassign row numbers to the grouped sorted data
-- similar to a grouped set of numbers
-- for example, the first two steps are divided into three groups
-- row_number() will renumber the first group from 1, 1.2.3.4 ....
-- the second group will still be numbered from 1, 1.2.3.4 ....
-- and so on down the line
-- WHERE t1.rn > 1
-- Filter the grouped sorted data to filter out all rows numbered greater than 1 (i.e. duplicate rows)
-- keep only the first record, filter all others greater than 1 and then use t.rowid IN () to delete them to achieve de-duplication
--Querying the 50 most CPU-intensive queries
SELECT TOP 50
DB_NAME(dbid) AS DBNAME,
OBJECT_NAME(objectid,dbid) as OBJECTNAME,
total_worker_time/execution_count/1000/1000 AS [CPU Average execution (sec)],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) N'Execution Statements'
,st.text N'Full Words'
,total_worker_time/1000/1000 AS [Total CPU time consumed (seconds)]
,execution_count [number of runs]
,qs.total_worker_time/qs.execution_count/1000/1000 AS [average_execution_time_of_CPU(sec)]
,last_execution_time AS [last execution time]
,max_worker_time /1000/1000 AS [max_execution_time(sec)]
,total_physical_reads N 'total_physical_reads'
,total_logical_reads/execution_count N'number of logical reads per execution'
,total_logical_reads N'total_logical_reads'
,total_logical_writes N'total_logical_writes'
,*
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE last_execution_time> dateadd(minute,-100,getdate())
ORDER BY total_worker_time/execution_count DESC;
GOHow to use it when sorting individual fields
example.setOrderByClause("user_type ASC"); //Output sorted by user type in ascending order
Running SQL looks something like this
SELECT ...
FROM tab_sy_user
order by user_type ASC
The method of using when sorting multiple fields separated by a comma
The query results are sorted by the order in which the fields are sorted, and if the conditions are the same, continue the conditional sort by the next field until the last field sort is complete.
//First according to user_type ascending ASC sorting, if there is the same condition, in accordance with the level condition descending DESC sorting
example.setOrderByClause("user_type ASC, level DESC");
Running SQL looks something like this
SELECT ...
FROM tab_sy_user
order by user_type ASC, level DESC
Mysql error:
due to the following code
create table sell(
id int,
name varchar(10),
sellnumbers tinyint,
sellmoney int,
money int，//There is an extra comma on this line. The line closest to the end of the line does not need a comma, as in (1, 2, 3,) this is the same error
);