MySQL Learning notes

1. The role of the database: storing data.
The database stores data differently from other means of storage:
1. Can store large amounts of data
2. Data is not lost
3. Security
4. Sharing
5. Easy to find data
2. History of database development:
1. File system.
2. Database system:
Mesh structure
Layered structure
Relational structure: Sqlserver,mysql
Relational-Object Type database: Oracle (God Scott)
Access,db2,sysbase
3. Noun:
Dbms:database managerment System Database management Systems
DB: Database
4. Installation
5.sql:structer Query Language: Structured querying language
Classification:
1.DML: Data Manipulation language (Insert,update,delete)
2.dcl:data Control Language data controlling language (grant, REVOKE)
3.ddl:data define Language data definition language (create,drop)
4.dql:data Query Language data Query Language (SELECT)
5.TPL: Transaction Processing language
Comments in 6.sql: #注释.

Data type:
Int,smallint,long
Float,double
Char: fixed-length string
VARCHAR: A variable-length string. The length specified is the maximum length.
Date,datetime,timestamp (timestamp, auto Insert (1970.1.1-2037.12.31))
Blob:binary Large Object
Clob:character Large OBJCT

#创建数据库
Create DATABASE MyDB;
#删除数据库
Drop database mydb;
#查看所有的数据库
show databases;
#更改当前的数据库
Use MyDB;
#查看创建数据库的sql语句
Show CREATE Database mydb;


#创建表
CREATE TABLE T1
(
ID int,
Name varchar (20),
Age int
);
#查询表的所有数据
SELECT * from T1;

#删除表
drop table T1;
#查看创建表的sql语句
Show CREATE table T1;
#查看当前数据库中所有表
Show tables;

#设定前端工具的字符编码
#设定插入字符的编码
Set CHARACTER_SET_CLIENT=DBK;
#设定结果集的编码
Set CHARACTER_SET_RESULTS=GBK;
#插入语句
INSERT into T1 (id,name,age) VALUES (1, ' Zhang San ', 20);
Insert T1 (id,name,age) VALUES (2, ' Zhang Mowgli ', 21);
Insert T1 VALUES (3, ' Zhang Sanfeng ', 25);
#更新语句
#修改一列的数据
Update T1 Set name = ' Zhang Trixan ';
#修改某一行的数据
Update T1 Set name = ' Zhang Sanfeng ' where id = 2;

#删除
Delete from t1 where id = 3;
#删除所有的数据
Delete from T1;

#查询
#显示所有的记录
SELECT * from T1; # * indicates all fields
#显示所有记录的姓名
Select name from T1;
Name of the record #显示id =3
Select name from t1 where id = 3;
#显示多个字段, fields are separated by commas
Select Name,age from t1 where id = 3;
#显示年龄大于等于23的人
SELECT * from T1 where age >=23;
#显示姓名是郭靖的人
SELECT * from t1 where name = ' Guo Jing ';
#显示年龄在23到35之间的人
SELECT * from T1 where age >=23 and age <=25; # and OR not
SELECT * from T1 where age between and 25;
#显示年龄是21或者姓名叫令狐冲的人
SELECT * from t1 where age = or name = ' Make Fox punch ';
#显示姓名不叫郭靖的人
SELECT * from t1 where name! = ' Guo Jing ';
SELECT * from t1 where name <> ' Guo Jing ';

#模糊查询
#通配符
%: Represents any number of characters (0~ multiple)
_: Represents any one character (must have)
[]: Represented in a certain interval (MySQL is not supported)
[^]: represents not in a certain interval (MySQL is not supported)

#查询所有姓张的人
SELECT * from t1 where name is like ' Zhang% ';
#查询姓张, a person whose name is 2 characters long
SELECT * from t1 where name is like ' Zhang _ ';
#查询名字中包含一个张字的人
SELECT * from T1 the where name like '% Zhang% ';

#完整性: correctness + accuracy = completeness.
Imposes constraints on the data.
Custom integrity: self-defined constraints
Domain integrity: Constraints defined by the language itself
Entity integrity: Requires that each table must have a primary key.
Primary key: The only value that distinguishes a column or columns from a record.
Referential integrity: for two tables. A field in one table refers to the primary key of another table. This field is called a foreign key.
The table in which the foreign key resides is called a Word table or from a table. The referenced table is called the primary table.
1. When you add a record, you must first add the records from the primary table, and then add the child table records.
2. When there is a reference relationship, you cannot modify the primary key of the record in the primary table.
3. When deleting a record, you must first delete the record in the Word table, and then delete the record in the main table.

#创建表, set Field Operation
#timestamp类型可以主动插入也可以自动由系统插入值
CREATE TABLE t3 (
ID int,
Name varchar (20),
Operation timestamp
);

#插入一条记录
Insert into t3 (id,name) VALUES (1, ' Zhang San ');

#创建表student, and add various constraints
CREATE TABLE Student
(
ID int primary KEY, #主键约束
Name varchar, #唯一约束
Age int is not NULL, #非空约束
Sex varchar (2),
Address varchar () Default ' Chongqing ' #默认约束
) ;


INSERT into student (id,name,age,sex,address) VALUES (1, ' Zhang Mowgli ', 20, ' Male ', ' Beijing ');
INSERT into student (id,name,age,sex,address) VALUES (2, ' Zhang Sanfeng ', 20, ' Male ', ' Beijing ');
INSERT into student (id,name,age,sex,address) VALUES (3, ' Little Dragon Girl ', 16, ' female ', ' tomb ');
INSERT into student (Id,name,age,sex) VALUES (4, ' Huang Rong ', 18, ' female ');
INSERT into student (id,name,age,sex,address) VALUES (5, ' Make Fox Punch ', 25, ' male ', default);
INSERT into student (id,name,age,sex,address) VALUES (6, ' Guo Jing ', 25, ' Male ', ' Peach Blossom Island ');


#演示别名的问题
#给字段或者表可以起别名 (specified with AS keyword, as can be omitted).
Select ID as number, name as name, sex gender, age ages, address addresses from student;

#添加修改字段的问题
#添加一个字段birthday Date
ALTER TABLE student add birthday date;
#查看表的结构
DESC student;
#删除字段 Birthday
ALTER TABLE student drop column birthday;

#演示distinct (different) keywords
#distinct必须写在所有字段的前面, distinct refers to all the fields that follow are different.
#查看student表中有几个年龄
Select distinct age from student;
#查看姓名和不同的年龄
Select distinct name,age from student;

#查询null的值
#查询没有名字的人
SELECT * FROM student where name = NULL; #差不到
Select *from student where name is null;
Select *from student where name is null or name = ';

#演示关键字in (expressed in ....) Inside
#查询地址在桃花岛, people in Beijing
SELECT * FROM student where address = ' peach blossom Island ' or address = ' Beijing ';
SELECT * from student where address in (' Peach Blossom Island ', ' Beijing ');

#创建分数表
CREATE TABLE Score
(
ID int PRIMARY KEY,
Sid Int,
China int,
History int,
中文版 int,
Constraint Fk_sid foreign key (SID) references student (ID)
) ;

INSERT into score values (1,1,54,85,69);
INSERT into score values (2,3,71,60,89);
INSERT into score values (3,4,71,34,80);
INSERT into score values (4,6,87,79,81);

#查询给每个人语文成绩提高5后的成绩
#字段支持各种表达式
Select China + 5 from score;
#查询一下每个人考试总分
Select Chinese + history + 中文版 from score;

#演示in语句
A SELECT statement, called a subquery, is nested #在一个 the SELECT statement.
#子查询必须写在where the condition.
#查询一下参加考试的人的姓名
Select name from student where ID in (select SID from Score);
#查询一下参加考试的人的姓名和成绩
Select Name,china,history,english from student s,score c where s.id = C.sid;
#查询一下参加考试的人的姓名和成绩, Total
Select Name,china,history,english,china + history+english total from student s,score C where s.id = C.sid;
#查询一下没有参加考试的人的姓名
Select name from student where ID not in (select SID from Score);

#排序 (Order BY)
#如果值一样, the primary key is then sorted by default in ascending order.
#显示语文成绩降序排序
SELECT * FROM score ORDER BY China Desc;
#按多个字段排序
SELECT * FROM Score ORDER by China Desc,id Desc;
SELECT * FROM Score ORDER by China Asc,id Desc;
#按照表达式排序
#按照总分降序排序
Select *,china + history + Chinese score from score order by Chinese + history + 中文版 desc;

#多表查询
#分类: inline, outreach (left outer link, right outer link), Cross connect
#查询的原理:
A table is made up of pages and records are stored in a page. Each page will have an index. Each table has an index page. The size of the page is 8k.
#交叉查询 (cross join)
SELECT * FROM student cross join score;
#内联查询 (relationship with primary foreign key) (inner join)
#查询参加考试人的姓名
Select name from student s inner joins score C on s.id = C.sid;
#查询没有参加考试人的姓名
Select name from student s inner joins score C on s.id! = C.sid; #交叉查询

#内联查询和子查询的关系:
All inline can be replaced with subqueries. Subqueries may not necessarily be replaced with inline queries.

#左外链接是以左边的表为基准 (how many records are on the left table, and how many records must be in the results)
# (left outer JOIN, outer can be omitted)
#显示所有人的成绩
Select s.*,c.* from student s left outer joins score C on s.id = C.sid;
Select s.*,c.* from student S left joins score C on s.id = C.sid;
Select s.*,c.* from student s right joins score C on s.id = C.sid;

#演示关键字limit n,m, N is subscript, M is the intercept of several records
#limit关键字只适用于mysql
#显示总成绩前三名
Select S.name,c.china,c.history,c.english, Chinese + history + 中文版 from student S,score C
where s.id = C.sid
Order BY Chinese + history + 中文版 desc limit 0,3;
#显示年龄最大的3个人
SELECT * FROM Student ORDER BY age DESC limit 0,3;

#演示auto_increment
#auto_increment的字段类型必须是int或相关的类型
CREATE TABLE T4
(
ID int primary KEY auto_increment,
Name varchar (20)
) ;

#聚合函数
#max, Min,sum,avg,count
#查询语文最高分
Select MAX (China) from score;
#查询年龄的总和
Select SUM (age) from student;
#查询总共有多少学生
Select COUNT (*) from student;

#分组
GROUP BY
#根据性别分组
Select COUNT (*) from student group by sex;
#希望显示男, the situation of women and subgroups
Select Sex,count (*) number from student group by sex;

Note: Fields after the SELECT keyword can only be written according to the grouped fields except the aggregate function
Select Name,sex,count (*) number from student group by Sex,name;

#根据条件进行分组
#having Grouping conditions
#条件是分组条件
#根据性别进行分组, the age of the members in the group is greater than 17
Select Sex,count (*) from student where age > N Group by sex;
#根据性别进行分组, requires more than 3 members in a group
Select Sex,count (*) from student GROUP by sex has count (*) >=3;

#where, on,having the difference between the three representative conditions:
cannot be used interchangeably. Where is the general condition. On is used in multi-table connection queries. Having only
Used in a grouped query.
The WHERE keyword is typically followed by the field's expression.
Having the keyword is usually the office and function expressions.


Summarizes the complete wording of the SELECT statement.
Select [Fields ...] from [table name, table name] where ...
Group By field expression ... having ....
Order By field 1, Field 2 ...
Limit .....

Execution procedure: The Where Condition filter is performed first. The group by is then executed at the execution of order by. Last execution limit


#函数
#日期函数
#查询当前的时间
Select Now (); #年月日, minutes, seconds.
Select Current_date (); #年月日
Select Current_time (); #时分秒
Year (date); #获取年 SELECT Year (now ());

#字符串函数
Left (string, length) how long to intercept the string
Right (string, length
#数学函数

#数据库的备份与恢复
#备份 (do not add a semicolon after the statement)
Typing mysqldump-u root-proot mydb>d:/mydb.sql in a DOS window
#恢复 (do not add a semicolon after the statement, create a good database first)
1. Client-side typing in DBMS (Database management System): Source D:/mydb.sql
2. In a DOS environment: Mysql-u root-proot Mydb<d:/mydb.sql

#注: The suffix of the backed up file can be made arbitrary.



#创建用户zhangwuji, password 123 (must be created with root, root is super user)
Create user Zhangwuji identified by ' 123 ';
#给用户zhangwuji赋看到数据库mydb的权限
Grant all on MyDB to Zhangwuji;
#给用户zhangwuji赋操作表t1的权限
Grant all on T1 to Zhangwuji; #给zhuangwuji赋予了增删改查四种权限
Grant SELECT on T1 to Zhuangwuji; #给zhuangwuji赋予查询四种权限
Grant Select,insert on T1 to Zhuanguji; #给zhuangwuji赋予查询和添加四种权限

#回收zhangwuji对表t1的增删改查权限
Revoke all on T1 from Zhangwuji;

MySQL Learning notes