MySQL after learning the summary of the point of knowledge

GC is a garbage collection mechanism in Java

DBA (database administrator) unified Password Aptech

relational (SQL) MySQL Oracle SQL Server SQLite (mobile Lightweight database) DB2------structured

Non-relational (NoSQL) redis (stored in key-value pairs) MongoDB (public number)

Mysqld Install and uninstall Mysqld-install prompt service installation runs successfully services.msc can view native services
MySQL Logon and logoff
MySQL Change password

----DOS execution MySQL
1 open MySQL net start MySQL close net stop MySQL
2 login MySQL mysql-h server host address-u user name-p password There is no space between
1 Modify password Set password for ' root ' @ ' localhost ' =password (' Aptech ')

2 Change Password This is to open MySQL can not log in and then enter the following changes
Mysqladmin-u root-p Password

3 View MySQL version information and user name Select version (), user () Note all Caps

4 CREATE DATABASE Database name
5 View all databases show databases
Show CREATE DATABASE name

6 Select the database use database name
7 Deleting a database drop databases name

8 CREATE TABLE Table name (
Field 1 data Type [Field Properties | constraints] [index] [comment],
。。。。。 The last line without the comma field is the column
）

View Table Show tables
#显示表结构
DESC Book;
SHOW CREATE TABLE Book;

#删除表
Use Library;
DROP TABLE Book;
DROP TABLE Library.book;

9 PRIMARY key primary key multi-primary key

Primary keys and autogrow are used together with a primary key that is unique and does not duplicate the null value cannot appear a table can have only one primary key

The unique constraint can be empty but only one null value cannot be duplicated

10 note Comment Plus note names can be enclosed in single quotes

10 character set parentheses followed by charset= character set name

11 no characters/characters unsigned that is not allowed to be negative

----SQL (Structured Query language)

1 DML (Data manipulation language) insert
2 DDL (data definition) Create column such as creat table create view drop table
3 DQL (data query) SELECT
4 DCL (Data Control) grant revoke ALL CAPS

DML (inster update Delete) DQL (select) is important

logical operators for---SQL

Both sides are true and the result is true
False if neither side is true
Not has the highest inverse precedence for Boolean expression values

---View a table for a database
Use database name
Describe table name

---delete a table
Use database name
DROP table Name

---modify table names
Alter table< old table name >rename[to]< new table name >

--Add Field
ALTER TABLE name add field name, etc.
ALTER TABLE test1 ADD ' passwored ' VARCHAR (Ten) not NULL

--Modify Fields

ALTER TABLE name change original field new field data type [Properties]
ALTER TABLE test1 Change ID xinid INT not NULL

--delete Field

ALTER TABLE table name drop field

--Add primary key

Aleter table name add constraint primary Key name primary key table name (primary key field)

Set the ID of the table student as the primary key
ALTER TABLE student ADD constraint pk_student primary key student (ID)

--Add foreign keys
ALTER TABLE name add constraint foreign key name foreign key (foreign key field) references associated table name (primary key column)
Columns such as set student field ID and teacher field name establish a primary foreign key association
ALTER TABLE student ADD constraint Fk_student_teacher foreign key (ID) references Tercher (name)

AUTO_INCREMENT=100 set the initial value to 100
[Email protected]@ auto_increment_increment=5 each growth is 5

Get the default value of the current system time with now ()
Lenddate ' DATETIME not NULL DEFAULT now () COMMENT ' borrowing date ',

----------------------------------expression Current Time default Current_timestamp

--------------------------------above are the contents of the DDL

----------------Code Exercise
#创建数据库
CreateDatabase Ceshi;
#查看数据库
SHOW CREATE DATABASE Ceshi;
#删除数据库
DROP DATABASE Ceshi;

#创建表
Use Ceshi;
CREATE TABLE ' Bird ' (
' ID ' INT (4) UNSIGNED PRIMARY KEY not NULL auto_increment COMMENT ' bird's ID ',
' Name ' VARCHAR (6) UNIQUE KEY COMMENT ' bird's name ',
' Date ' DATETIME DEFAULT now () COMMENT ' Bird's birthday '
) Charset=utf8
#查看表结构
DESC Bird;
SHOW CREATE TABLE Bird;

#删除表结构
DROP TABLE Bird1;
DROP TABLE Ceshi.bird;

#修改表名字
ALTER TABLE bird1 RENAME Bird;

#添加字段
ALTER TABLE bird ADD ' sex ' VARCHAR (1) DEFAULT ' male ';

#修改字段
ALTER TABLE bird change ' sex ' ' Xinsex ' INT (1) DEFAULT ' 1 ';

#删除字段
ALTER TABLE Bird DROP xinsex;

#添加主键约束
CREATE TABLE ' Xiniao ' (
' ID ' INT (4) Not NULL COMMENT ' bird's ID ',
' Name ' VARCHAR (6) UNIQUE KEY COMMENT ' bird's name ',
' Date ' DATETIME DEFAULT now () COMMENT ' Bird's birthday '
) Charset=utf8

ALTER TABLE xiniao ADD CONSTRAINT pk_xiniao PRIMARY KEY Xiniao (ID);

#添加外键约束
ALTER TABLE nan ADD CONSTRAINT fk_ren_nan FOREIGN KEY (ID)
REFERENCES Ren (id)

--------------Create a foreign key for a subclass persist the field of the parent class must be a foreign key of the primary key subclass in the subclass can be a primary key or it may not be a primary key
----deleted, the parent class cannot be deleted first and the data type must be
CREATE TABLE Nian (
Gradeid INT (Ten) PRIMARY KEY not NULL,
' Name ' VARCHAR (6) Not NULL

)

CREATE TABLE Student (
Stuno INT (6) is not NULL,
Gradeid INT (5) PRIMARY KEY not NULL,
CONSTRAINT fk_nian_student FOREIGN KEY (Stuno) REFERENCES Nian (Gradeid)
)

Delete foreign key
ALTER TABLE student drop FOREIGN key write the name of the foreign key
And then delete his index.
ALTER TABLE student drop FOREIGN key write the name of the foreign key

--------------------------use DMA for data operations to add data in the Delete modification table----------------------
Insert Add
Update modification
Delete Delete----------------operations are data

# 1: Separate fields or values with commas
# 2: Field 1, Field 2 ... This section can be omitted,
# But the added value must correspond to the table structure data column order, and the number is consistent
# 3: Provide values to be aware of constraints
---------------Adding data formats
Insert into[(field 1, Field 2 ...) )] VALUES (' Value 1 ', ' Value 2 ', Value 3 ... )

field can be omitted
Insert into table name (field) values (' Value 1 '), (' Value 2 ') can insert multiple data for a field column

#指定字段添加
INSERT into student (Loginpwd,studentname,
Gradeid,phone) VALUES (' 123 ', ' John Doe ', 20, ' 13212345678 ');
#省略字段添加
INSERT into result VALUES (20180001,1, ' 2018-1-31 ', 60);
#违反约束的数据插入会出现异常
INSERT into result VALUES (20180011,1, ' 2018-1-31 ', 60);

#插入多行记录
INSERT into result VALUES (20180001,1, ' 2017-2-28 ', 60),
(20180001,1, ' 2017-3-31 ', 70),
(20180003,1, ' 2017-4-22 ', 80),
(20180004,1, ' 2017-5-31 ', 90)

---------modifying data formats in tables
Update table name set field Column_name=value[,column_name=value3][where condition]

-----Delete data from a table the delete-----------

Format: delete [from] table name plus where condition

#删除表中所有的数据 using INSERT into again, the increment is self-increasing on the original basis.

DELETE from Bank ==truncate table + tables Name

Delete Deletes a whole line after all the delete segment is a table name

#删除指定的数据
DELETE from Student WHERE
studentno=20180005 OR
studentno=20180006
OR studentno=20180007

DELETE from result;

DELETE from Student WHERE
Studentno in (20180001,20180003,20180004)

-----------------------------------usage of like and% and _
#修改数据 login_pwd with 1
UPDATE Xintercher SET login_pwd=666
WHERE login_pwd like ' 1% '

UPDATE Xintercher SET login_pwd=555
WHERE login_pwd like '%1% '

UPDATE xintercher SET studentname= ' xxxx '
WHERE login_pwd like ' 2_ '

Between A and B are values between A and B

WHERE Studentno in (20180001,20180003,20180004) ====where studentno=2018001 or studentno=20180003 or studentno=2018004

-----------------Create a backup table
#创建一个新表
CREATE TABLE stuback (SELECT * from student)

-------------------------------"DQL query statement"------------------------
1 Open Table-shortcut keys to see in refresh
SELECT * from + table name
2 ' is empty adress= ' is null (NULL) this represents a null value adress is null

3 Select field from table name
Where plus conditions
ORDER BY orderby order ASC Descending desc default not written ascending ASC
Limit [position offset number of rows] The offset from 0 starts

5 as an alias to the data column take a new name (read-only and not modify) Note that you want to have a space and a comma to separate the last field without commas can be omitted

#AS作为别名

#给数据列取一个新名字 (read-only and not modified)
SELECT Stuid as number, stuname as name from student
SELECT stuid number, stuname name from student

#给数据表取个别名
SELECT S.stuid,s.stuname from student as S

#把经过计算的结果用一个新名字代替
SELECT CONCAT (' s: ', stuid) as study number from student;

6 removing duplicate records distinct
SELECT DISTINCT studentresult from result;

7# for students whose home address is null, the name of the student's telephone number
SELECT Stuid,stuname,phone from Student
WHERE adress is null;#--------------------------cannot be written =null

8 Aggregation functions

AVG () average count () Number of rows max max min min value sum sum

Check the highest and lowest scores for a student with account number 1
SELECT Max (Studentresult) as the highest score,
Min (studentresult) as the lowest score,
AVG (Studentresult) as average score,
SUM (studentresult) as Total
from result WHERE subno=1001;
Query number of students with grade number 1
SELECT COUNT (*) as student number from student WHERE nian_id;

9 String Functions
Concat (STR,STR) concatenated into a string

Insert (str 3 NEWSTR)
#insert Insert Replacement string 3 represents the beginning of the replacement position 6 is the length of the replacement
SELECT INSERT (' My hometown is Kaifeng City, Henan ', 3, 6, ' haha ');

#截取字符串 substring 3 represents the position at which the Intercept was started 2 is the number of intercepts
SELECT SUBSTRING (' Abcdefghijk ', 3,2);

Lower (str) becomes uppercase upper (str) lowercase

10th-Period function
#获得当前日期
SELECT curdate ();
#获得当前时间
SELECT Curtime ();
#获得当前日期和时间
SELECT now ();

#俩个时间间隔天数
SELECT DATEDIFF (now (), ' 1994-12-09 ')

Year (date) Gets the years in date
Date (date) Gets the date in date
Minute (date) Gets the minute in date
Hour (DATR) gets the hour in date
Adddate (date,n) calculates date date plus n days

11 Mathematical functions

Ceil (2.3) returns the smallest integer greater than or equal to the value 2.3 gets 3
Floor (2.3) returns the largest integer less than or equal to the value 2.3 gets 2

RAND () returns the decimal between 0-1

---------------Code

#上机练习4
#1 Query the name and address of all male students whose semester ID is 2 (two note comma separated)
SELECT Stuname, adress from student
WHERE nian_id=1;

#2查询无电子邮件的学生姓名和年级信息 (is null represents a null value)
SELECT stuname,nian_id from Student
WHERE adress is NULL;
#where adress= ';

#3 Query the name of the student who was born after 1990 years of the Semester ID 2 (year to wrap the birthday in a single display of his year others do not show)
SELECT Stuname from Student
WHERE nian_id=2 and year (birthday) >=1990

#4 Query student score information for the Java subjects exam that dates from 2013-2-15
SELECT SubID from Sub_kemu
WHERE sunname= ' Java '
SELECT Studentresult score from result
WHERE DATE (examdate) = ' 2016-12-12 ' and subno=1001;

#5查询参加了java科目考试的学员总成绩
SELECT SUM (Studentresult) Total from result
WHERE subno=1001

#查询css的平均分
SELECT avg (Studentresult) average score from result
WHERE subno=1001;

#上机练习5

#1 find student information for semester ID 1 by date of birth
SELECT stuname Name, stuid number from student
WHERE nian_id=1
Order by Birthday desc;# (Descending)

#2 by date grades from high to low query number 1 for the exam results information
SELECT * from result
WHERE subno=1001
ORDER by Studentresult Desc,examdate asc;# (last plus semicolon)

#3 Query the top five Java exams in 2013-3-22
SELECT Studentresult top five from result
WHERE DATE (examdate) = ' 2016-12-12 ' and subno=1001
ORDER by Studentresult DESC
LIMIT 0, 5;

#4 query y2 the account name of the most hours
SELECT subid account from Sub_kemu
ORDER by ' subdate ' DESC
LIMIT 1

#5 Check the youngest student's name and class
SELECT stuname Name, nian_id class, birthday from student
#order by DATEDIFF (now (), birthday)
ORDER by Birthday DESC
LIMIT 1

#6 Query 2013--3-22 The minimum score to take the exam appears in which account

SELECT subno, Studentresult from result
WHERE DATE (examdate) = ' 2016-12-12 '
ORDER by Studentresult
LIMIT 0,1

#7 Inquiry Number 10000 students take all the exam information and display it in chronological order.
SELECT * from result
WHERE stuno=3
ORDER by Examdate

#8 Enquiries 10000 students have participated in all the highest polar time subjects in the exams
SELECT Studentresult score, examdate time, subno account from result
WHERE stuno=3
ORDER by Studentresult DESC
LIMIT 1

#上机练习6

#1 Check the number and score of the top five students in the 2016-2-17 exam
SELECT Stuid as study number, studentresult as fraction from student
INNER JOIN Result
WHERE examdate= ' 2016-12-12 '
ORDER by Studentresult DESC
LIMIT 0, 5;

#将所有女学生按年龄由大到小排列 start with the 2nd record the name of the 6 female student the date of birth phone number
SELECT Stuname as name, birthday as date of birth, phone as mobile number, ceil (DATEDIFF (now (), birthday)/365) as age from student
WHERE sex= ' woman '
ORDER by Birthday DESC
LIMIT 1,6
#按出生年份分组统计学生人数 Show the year and the number of people in each group
#2016-12-12 of all participants in the exam have the highest score and the lowest average score
SELECT MAX (Studentresult), MIN (Studentresult), AVG (studentresult) from result
WHERE DATE (examdate) = ' 2016-12-12 '

Sub-query paged query-------------------------------DQL query Group self-connect connection query
#-------------------------Pagination Display
#要求 page Show student Information 5 article per page
SELECT * FROM Student
ORDER by Stuid DESC
#第一页
LIMIT 0,5

SELECT * FROM Student
ORDER by Stuid DESC
#第二页
LIMIT 5,5

SELECT * FROM Student
ORDER by Stuid DESC
#第三页
LIMIT 10,5

() () () () () () ()-------#偏移量的算法是 the current page minus one in the current displayed bar series such as the third page offset =3-1*5 (number of bars)

------------------------------------Subquery Connection Query
#-----------------GROUP BY group query
SELECT COUNT (1) from Student #返回所有总行数 summary All records are represented by numbers in fields
SELECT COUNT (adress) from student #返回具体的列名 statistics are not empty records

#统计每个班级的人数
SELECT COUNT (1) as the total number of nian_id as classes from student
GROUP by nian_id

SELECT Nian_id,count (1) from student
GROUP by nian_id

#统计每个班级男同学和女同学的人数 classes with a population of more than 8 people
SELECT COUNT (1) Total number of persons, sex, nian_id from student
GROUP by sex,nian_id
Having COUNT (1) >8

#上机练习4
#1查询每个年级的总学时数, and in ascending order
SELECT Gradeid grade, SUM (classhour) total hours from ' subject '
GROUP by Gradeid
ORDER by SUM (Classhour)
#2查询每个参加考试的学员的平均分
SELECT studentno number, AVG (Studentresult) average score from result
GROUP by Studentno
#3查询每门课程的平均分, sorted in descending order
SELECT Subjectno Course number, AVG (Studentresult) average score from result
GROUP by Subjectno
ORDER by AVG (Studentresult) DESC
#4查询每个学生参加的所有考试的总分, and in descending order
SELECT studentno number, SUM (Studentresult) score from result
GROUP by Studentno
ORDER by SUM (Studentresult) DESC

#1 Query The total number of hours per class and in ascending order
SELECT nian_id as Class, SUM (' subdate ') score and from Sub_kemu
GROUP by nian_id
ORDER by SUM (' Subdate ')

#2 check the average score for each student who took the exam
SELECT Stuno Learner ID, AVG (studentresult) from result
GROUP by Stuno

#3 query the average score for each course and sort in descending order
SELECT Subno as per course, AVG (studentresult) from result
GROUP by Subno
ORDER by AVG (Studentresult)

#4 Check the total score of each student's participation in all exams and arrange them in descending order
SELECT SUM (Studentresult), Stuno from result
GROUP by Stuno
ORDER by SUM (Studentresult)

#连接 Query

#--------------------------------------Internal connection (equivalent)
#从sub和nian_id中找到课程名字 Grade Name
#第一种写法 where
SELECT DISTINCT n.nian_id, sunname,nian_name from Nian_biao N, Sub_kemu s
WHERE n.nian_id=s.nian_id

#第二种写法 Inner on
SELECT DISTINCT n.nian_id, sunname,nian_name from Nian_biao N
INNER JOIN Sub_kemu S
On n.nian_id=s.nian_id

#查询 number of names test subject name score (3 table inline)
SELECT S.stuid as study number, stuname as name, sub.sunname as subject name, R.studentresult as score
From student S
INNER JOIN Sub_kemu Sub
On S.stuid=sub.subid
INNER JOIN Result R
On R.stuno=sub.subid

#------------------------left and right outside the connection
#左边为基准 to the right without padding with null

SELECT DISTINCT n.nian_id, sunname,nian_name from Nian_biao N
Left JOIN Sub_kemu S
On n.nian_id=s.nian_id

#上机练习8
#查询所有科目的考试成绩信息 (some subjects may never have been tested)
SELECT S.sunname,r.studentresult from Sub_kemu s
Left JOIN result R
On S.subid=r.subno

#查询从未考试的科目
SELECT S.sunname,r.studentresult from Sub_kemu s
Left JOIN result R
On S.subid=r.subno
#where r.studentresult is null; You can't get the grades, some people don't have exams.
WHERE R.examdate is NULL;

SELECT Sunname, Studentresult from Sub_kemu s
Left JOIN result R
On s.subid= r.subno
WHERE R.examdate is NULL;

#----------------------------own connection

CREATE TABLE Fuzi (
ID INT (4) UNSIGNED PRIMARY KEY not NULL auto_increment,
Idname VARCHAR (6) Not NULL,
ParentID INT (4) UNSIGNED
) Charset=utf8

INSERT into Fuzi (id,idname) VALUES (1, ' software development '),
(2, ' art design '),
(3, ' Java Basics '),
(4, ' OOP basics '),
(5, ' database technology '),
(6, ' PS technology '),
(7, ' Color Matching technology ')

#要求把父类子类信息分别显示出来
SELECT P.idname as parent column, C.idname as sub-column from Fuzi p
INNER JOIN Fuzi C
On P.id=c.parentid

#-------------Pagination Display
#要求 page Show student Information 5 article per page
SELECT * FROM Student
ORDER by Stuid DESC
#第一页
LIMIT 0,5

SELECT * FROM Student
ORDER by Stuid DESC
#第二页
LIMIT 5,5

SELECT * FROM Student
ORDER by Stuid DESC
#第三页
LIMIT 10,5

#偏移量的算法是 the current number of pages minus one in the current displayed bar series such as the third page offset =3-1*5 (count)

# subquery is also called nested query Query method from inside to my husband the result of a query is generally a collection of recommendations using the IN keyword

#查询比孙悟空小的学生信息
SELECT * FROM Student
WHERE birthday> (
SELECT MAX (birthday) from student
WHERE stuname= ' Monkey King '
)

#使用子查询 check the highest and lowest scores of students who have attended the most recent Java exam results

Account number for #查询Logic Java
SELECT Subjectno from SUBJECT WHERE subjectname= ' Logicjava '
#查询获得Logic Java last Test date
SELECT MAX (DATE (examdate)) from result
WHERE Subjectno in (
SELECT Subjectno from SUBJECT WHERE subjectname= ' Logicjava ')
#根据课程编号获取考试成绩的最高分和最低分
SELECT MAX (Studentresult), MIN (studentresult) from result
WHERE subjectno= (
SELECT Subjectno from SUBJECT WHERE subjectname= ' Logicjava '
)
and
DATE (examdate) = (
SELECT MAX (DATE (examdate)) from result
WHERE subjectno= (
SELECT Subjectno from SUBJECT WHERE subjectname= ' Logicjava ')
)
---------------------------------------------------------------------
SELECT MAX (Studentresult), MIN (studentresult) from result
WHERE Subno in (
SELECT subid from Sub_kemu WHERE sunname= ' java '
)
and
DATE (examdate) = (
SELECT MAX (DATE (examdate)) from result
WHERE Subno in (
SELECT subid from Sub_kemu WHERE sunname= ' java '
)
)

#检查LogicJava课程最近一次考试. If there's a score of 80,
#以上者, the number and score of the top 5 students are displayed.
SELECT Studentno,studentresult from result
WHERE EXISTS (
SELECT * from result WHERE subjectno= (
SELECT Subjectno from SUBJECT WHERE subjectname= ' Logicjava '
) and examdate= (
SELECT MAX (examdate) from result
WHERE subjectno= (
SELECT Subjectno from SUBJECT WHERE subjectname= ' Logicjava ')
) and studentresult>80
)
and
Subjectno= (
SELECT Subjectno from SUBJECT WHERE subjectname= ' Logicjava '
)
ORDER by Studentresult DESC
LIMIT 5

SELECT * from result WHERE EXISTS
(
SELECT * from result WHERE studentresult>80
)

--------------------------------------------------------------------------------------------------------------- -----
------HAVING clause is used to filter the grouped data where the grouping is not filtered
Execution Order WHERE---GROUP by--having

--datediff (DATE1,DATE2) returns the number of days separated between 1 and 2
--adddate (date,n) calculates date plus n days

---data type double (5,2) value range is -999.99~999.99
tinyint smallint mediumint int float double decimal
String type
Char fixed length varchar variable length

Grammar
where
GROUP BY
ORDER BY
Limit 4, 4
Is the offset of the first data displayed starting from the fifth data is 0 8

--subquery as part of the Where condition the subquery returns no more than one value when compared to the comparison operator

--inner jion ... On
--The subquery must be in a small parenthesis

--Fuzzy query% instead of one or more characters _ represents a character query

--Query student information for students who did not participate in the last Java exam with in

--Advanced Sub-query
exists and NOT exists subqueries are queries that pass the main query field to the back of the query as a conditional return value of TRUE or False

External links (left and right connections)
= and <>
Use inner join on or where to make a connection between tables
Inner Jion connecting two tables (inner can be omitted)
On to set conditions

Transactional atomicity Consistency Isolation durability
Set Autocommit=0 off autocommit = 1 turn on autocommit begin or start transaction start transaction COMMIT TRANSACTION rollback ROLLBACK TRANSACTION

--------------"Database Design"
Modeling
Visio Flowchart UML Diagram---Database model diagram,
PowerDesigner Database Model Diagram---flowchart UML diagram
Rose

Database design Process
Demand analysis and analysis of customer business and data processing requirements
The E-R model diagram of the design database confirms the correctness and completeness of the requirement information
Detailed design application three major paradigms audit database structure
Code writing physical Implementation database writing implementation application
Software testing ....
Software deployment ...

E-r diagram is the entity diagram Database Model diagram two big picture

The relationship between entity entities relationship entities

Entity Rectangle attribute Ellipse Relationship Diamond

Suffix. CDM is a suffix of the e-r diagram
Suffix. PDM is a database model diagram

Map cardinality one-to-many
A one-to-many pair more

The first paradigm ensures that the atomicity of each column (which satisfies the smallest element that is not re-divided) is the first paradigm
The second paradigm ensures that each table describes only one thing
The third paradigm satisfies the second paradigm and then ensures that each column and primary key column are directly related rather than indirectly related

Create an index
CREATE table ' table ' (
' id ' int (one) not NULL auto_increment,
' title ' char (255) CHARACTER not NULL,
' Content ' text CHARACTER NULL,
' Time ' int (ten) null DEFAULT NULL,
PRIMARY KEY (' id '),
INDEX index_name (title (length))-------------------------------
)

How to modify the table structure by adding an index

ALTER TABLE table_name ADD INDEX index_name on (column (length))

Delete Index
DROP INDEX index_name on Tabl

MySQL after learning the summary of the point of knowledge