MySQL learning, and the use of SQLyog

1. What is MySQL

Connection address: Introduction to MySQL

1.2, connect to the database

Command line connection!

mysql -uroot -p123456 --连接数据库
update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost'; --修改用户密码
flush privileges;  --刷新权限
----------------------------------------------------
--所有的语句都使用;英文分号结尾
show databases;  --查看所有的库

use school  --切换数据库  use数据库名

show tables;  --查看数据库中所有的表

describe student; --显示数据库中所有的表的信息

create database company;  --创建一个数据库为company

exit;/quit;  --退出连接

--单行注释(SQL的本来注释)
#也是单行注释

/*多行注释*/

Database xxx language
DDL definition
DML operation
DCL control
DQL query

1.3 Operating the database

Operate the database>operate the table in the database>operate the data of the table in the database

1.3.1, create a database

CREATE DATABASE IF NOT EXISTS school;If the database school does not exist, create it

1.3.2, delete the database

DROP DATABASE IF EXISTS hello;If the database hello exists, delete it

1.3.3, use the database


--tab键上面,如果你的表名或者字段名是一个特殊字符,就需要到反引号
USE `SCHOOL`

1.4, database column type

Numerical value

tinyint very small data 1 byte

smalint 2 bytes of smaller data

mediumint medium size data 3 bytes

==int standard integer 4 bytes java int ==

bigint 8 bytes of larger data

float 4 bytes

double floating point 8 bytes

Decimal is generally used in the financial calculation of floating-point numbers in the form of a string

String

char string fixed size 0~255

     ==varchar	可变字符串 			0~65535 **	常用的   String==

tinytext 2^8-1

text text string 2^16-1 save large text

Date and time
  • date YYYY-MM-DD, date format
  • time HH:mm:ss time format
  • ==datetime YY-MM-DD HH:mm:ss The most commonly used time format==
timestamp	时间戳     1970.1.1到现在的毫秒值!
  • year year
null
  • No value, unknown
  • Note that there is no need to use NULL for calculations, and the result of the NULL operation is NULL

1.5, the field properties of the database (emphasis)

Unsigned:
●Unsigned integer
●Declared that the column cannot be declared as a negative number
zerofill:
● 0-filled
● Insufficient digits, use 0 to fill, int(3), 005
autoincrement:
● Usually understood as self-increment, automatically +1 on the basis of the previous record (default)
●Usually design a unique primary key ~ index, which must be an integer type
non emptyNULL not null
●If it is set to not null, if it is not assigned, an error will be reported!
●NULL, if you do not fill in the value, the default is null!
default:
●Set the default value!
● sex, the default value is male, if you do not specify the value of this column, there will be a default value!

expand:

id 主键
`version`	乐观锁
is_delete	伪删除
gmt_create	创建时间
gmt_update	修改时间

1.6, create a database table

--目标:创建一个school数据库
--创建学生表(列,字段)	使用SQL
--学号INT 登陆密码VARCHAR(20),姓名、性别VARCHAR(2)、出生日期(DATETIME),家庭住址,email

--注意点,使用英文(),表的名称 和 字段尽量使用``反引号括起来
--AUTO_INCREMENT 自增
--字符串使用单引号阔起来!
--所有的语句后面加,(英文逗号)
--PRIMARY KEY 主键,一般一个表只有一个唯一的主键
CREATE DATABASE IF NOT EXISTS `school`; 
CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

format

CREATE TABLE [IF NOT EXISTS] `表名`(
	`字段名` 列类型 [属性] [索引] [注释],
	`字段名` 列类型 [属性] [索引] [注释],
	`字段名` 列类型 [属性] [索引] [注释],
	... ...
	`字段名` 列类型 [属性] [索引] [注释]
)[表类型][字符集设置][注释]

Common commands

SHOW CREATE DATABASE `school`  --查看创建数据库的语句
SHOW CREATE TABLE student    --查看创建表的语句
USE school	--进入数据库school
DESC student   --显示表的结构

1.7, the type of data table

--关于数据库的引擎
/*
INNODB 默认使用
MYISAM 早些年使用的
*/
MYISAMINNODB
Transaction supportnot supportstand by
Data row locknot supportstand by
Foreign key constraintnot supportstand by
Full-text indexstand bynot support
The size of the table spaceSmallerLarger, about 2 times

Routine use operation:

  • MYISAM saves space and is faster
  • INNODB has high security, transaction processing, multi-table multi-user operation
In the location where the physical space exists,
all database files are stored in the data directory, and a folder corresponds to a database. The
essence is still the storage of files!
The difference between the MySQL engine in physical files
  • INNODB has only one *.frm file in the database table, and the ibdata1 file in the upper directory
  • MYISAM corresponding file
  • *.frm-definition file of table structure
  • *.MYD-data file (data)
  • *.MYI-index file (index)
Set the character set encoding of the database table. If
CHARSET=utf8
not set, it will be the MySQL default character set encoding latin-1 (Chinese is not supported)
. Configure the default encoding in my.ini
character-set-server=utf8

2. Modify and delete the table

2.1, modification

--修改表名ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1

--增加表的字段ALTER TABLE 表名 ADD 字段名 字段数据类型
ALTER TABLE teacher1 ADD birthday DATETIME

--修改表的字段(重命名,修改约束)
--ALTER TABLE 表名 MODIFY 字段名 字段数据类型[]
ALTER TABLE teacher1 MODIFY age VARCHAR(11) --修改约束(修改字段数据类型)

--ALTER TABLE 表名 CHANGE 旧字段名 新字段名 字段类型[] 
ALTER TABLE teacher1 CHANGE age age1 INT(3) --可以修改字段重命名

--删除表的字段:ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher1 DROP age1

2.2, delete

--删除表(如果表存在则删除)
DROP TABLE IF EXISTS teacher1

All create and delete operations should be judged as much as possible, so as not to report errors~
be careful:

  • `Backquote, use this to enclose the field name
  • Comment-- /* */
  • sql is not case sensitive, it is recommended to write lowercase
  • All symbols are in English!

3. MySQL data management

3.1, foreign key

Method 1: Add constraints when creating a table (troublesome, more complicated)
--创建年级表
CREATE TABLE `grade`(
  `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级ID',
  `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
  PRIMARY KEY(`gradeid`)

)ENGINE=INNODB DEFAULT CHARSET=utf8

--创建学生表
CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`gradeid` INT(10) NOT NULL COMMENT '学生年级',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`),
	KEY `FK_gradeid`(`gradeid`),
	CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

When deleting a table with a foreign key relationship, you must first delete the table that references others (the slave table), and then delete the referenced table (the main table)

Method 2: After the table is created successfully, add a foreign key constraint
CREATE TABLE `grade`(
  `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级ID',
  `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
  PRIMARY KEY(`gradeid`)

)ENGINE=INNODB DEFAULT CHARSET=utf8


-- 学生表的gradeid字段 要去 引用年级表的gradeid
-- 定义外键key  外键foreign key
-- 给这个外键添加约束(执行引用) constraint约束  references引用  
CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`gradeid` INT(10) NOT NULL COMMENT '学生年级',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 创建表的时候没有外键关系
ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`);

The above operations are all physical foreign keys, database-level foreign keys, we do not recommend using them! (Avoid trouble caused by too much data)
Best achieved

  • The database is a simple table, only used to store data, only rows (data) and columns (fields)
  • We want to use data from multiple tables and want to use foreign keys (programs to achieve)

3.2, DML language

Database meaning: data storage, data management
DML language: data manipulation language

  • INSERT
  • UPDATE
  • DELETE

3.2.1, add

insert
--插入语句(添加)
-- INsert into 表名(`字段名1`,`字段名2`,`字段名3`) values ('值1','值2','值3',...)
INSERT INTO `grade` (`gradename`) VALUES ('大四')


-- 由于主键自增我们可以省略(如果不写表字段,它就会一一匹配)
INSERT INTO `grade` VALUES('大三')

-- 写插入语句,我们一定要数据和字段一一对应!

-- 插入多个字段
INSERT INTO `grade`(`gradename`) VALUES('大一'),('大二')

INSERT INTO `student`(`name`) VALUES ('张三')

INSERT INTO `student`(`name`,`password`,`sex`) VALUES ('李四','lisi123','女')

INSERT INTO `student`(`name`,`password`,`sex`) VALUES ('王五','wangwu456','男'),('六顺','liushun789','女')

Syntax: INsert into 表名(Field name 1 Field name 2 Field name 3 ,......) values ('值1','值2','值3',...)
Notes:
1. Use commas to separate fields and fields
2. Fields can be omitted, but the following values ​​must be one-to-one correspondence, no less
3. You can insert more than one at the same time Statement, the values ​​after VALUES need to be separated by commasVALUES (),(),......

3.2.2, modification

update who to modify (condition) set original value = new value
-- 修改学员名字,带了条件
-- 语法:UPDATE 表名 SET 字段名 = 值 WHERE 条件
UPDATE `student` SET `name`='RYGAR' WHERE `name`='张三'

-- 不指定条件的情况下,会改动所有的表
UPDATE `student` SET `name`='长江七号'


-- 修改多个属性
UPDATE `student` SET `name`='RYGAR',`password`='RYGAR123' WHERE `password`='liushun789'

-- 语法:
-- UPDATE 表名 SET `字段名1`='值1',`字段名2`='值2',... WHERE 条件

Condition: where clause operator id is equal to a certain value, greater than a certain value, modified in a certain interval

Operatormeaningrangeresult
=equal5=6false
<> or !=not equal to5<>6true
>more than the
<Less than
>=greater or equal to
<=Less than or equal to
BETWEEN...AND...In a certain range [x,x]
ANDMe and you&&5>1&&1>2false
ORMe or you
-- 通过多个条件定位数据
UPDATE `student` SET `name`='长江七号' WHERE `name`='RYGAR' AND `sex`='女'

Syntax: UPDATE table name SET 字段名1='value 1', 字段名2='value 2',... WHERE condition
Note:

  • Try to put backticks in the field name`
  • Condition, filter condition, if not specified, all columns will be modified
  • The value can be a specific value or a variable
UPDATE `student` SET `name`='孙悟空',`password`='sunwukong123',`birthday`=NOW() WHERE `name`='长江七号' AND `password`='lisi123'

This now() and current_time are both the current time

3.2.3, delete

delete command
Syntax: `delete from table name [where condition]
--删除数据(避免这样写,会全部删除)
DELETE FROM `student`

--删除指定数据
DELETE FROM `student` WHERE id=1
TRUNCATE command
Function: completely empty a database table, the structure of the table and index constraints will not change!
--清空student表
--语法: TRUNCATE 表名
TRUNCATE `student`
The difference between DELETE and TRUNCATE Similarities : Both
can delete data, neither will delete the table structure.
Differences:
1. TRUNCATE resets the auto-increment
column , the counter will be reset to zero 2. TRUNCATE will not affect the transaction
-- 测试delete和truncate的区别
CREATE TABLE `test`(
  `id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `call` VARCHAR(20) NOT NULL COMMENT '电话',
  PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `test`(`call`) VALUES ('1'),('2'),('3')

DELETE FROM `test` -- 不会影响自增

TRUNCATE `test` -- 自增会归零

DELETE delete problem, restart the database, phenomenon (MySQL5.0 version has these symptoms, but MySQL8.0 version fixes these problems)

  • The INNODB auto-increment column will start from 1 (the breakpoint will be lost if it exists in the memory)
  • MYISAM continues from the previous increment (the ones stored in the file will not be lost)

3.2.4, DQL query data (emphasis)

DQL (Data Query Language: Data Query Language)

  • All query operations use it SELECT
  • It can do simple query and complex query
  • The core language, the most important statement in the database
  • Most frequently used sentence
-- 查询全部
SELECT * FROM 表名;

-- 查询指定字段
SELECT `字段1`,`字段2`,... FROM 表名;

-- 别名,给字段名起别名 AS,也可以给表起别名
SELECT `字段名1` AS 别名1,`字段名2` AS 别名2,... FROM 表名 AS 表别名;

-- 函数 Concat(a,b)
SELECT CONCAT(`别名`,字段名) AS 别名 FROM 表名


Sometimes, the column names are not so obvious. We play the role of alias AS field name AS alias
De-
duplicate DISTINCT : remove the duplicate data in the results of the SELECT query, and only display one
-- 查询一下哪些同学参加了考试,成绩
SELECT * FROM result  -- 查询全部的考试成绩
Insert picture description here
SELECT `StudentNO` FROM result --查询有哪些同学参加了考试
Insert picture description here


Duplicate data is found, de-duplicate useDISTINCTKey words

SELECT DISTINCT `studentNo` FROM result -- 发现重复数据,去重
Insert picture description here


View MySQL version

SELECT version();
Insert picture description here
Database column (expression)
SELECT 100*3-1 AS 计算结果 --用来计算(表达式)
SELECT @@auto_increment_increment -- 查询自增的步长(变量)

-- 学员考试成绩+1分查询
SELECT `StudentNo`,`StudentResult`+1 AS `提分后` FROM result
Insert picture description here

Expressions in the database: text values, columns, null, functions, calculation expressions, system variables...
selectexpression from table

3.2.5, where condition clause

Role: Retrieving dataMeet the criteriaValue of
The search condition consists of one or more expressions! The result is a boolean

Logical Operator
| Operator| Syntax| Description|
|–|--|–|
| and && | a and b / a&&b | Logical AND, both are true, and the result is true |
| or || | a or b / a || b | Logical OR, one of which is true, the result is true |
| Not! | not a / !a | Logical negation, true to false, false to true! |
Try to use English letters
-- 查询考试成绩在95~100分之间
SELECT studentNo,`StudentResult` FROM result WHERE studentResult>=95 AND StudentResult<=100;
-- AND &&
SELECT studentNo,`StudentResult` FROM result WHERE studentResult>=95 && StudentResult<=100;
-- 模糊查询(区间范围 BETWEEN ... AND ... )
SELECT studentNo,`StudentResult` FROM result WHERE studentResult BETWEEN 95 AND 100;
Insert picture description here


Query the results except for student number 1000

-- !=
SELECT studentNo,`StudentResult` FROM result WHERE studentNo != 1000;
-- NOT
SELECT studentNo,`StudentResult` FROM result WHERE NOT studentNo = 1000;
Insert picture description here
Fuzzy query: comparison operator
Operatorgrammardescription
IS NULLa IS nullIf the operator is NULL, the result is true
IS NOT NULLa IS NOT nullIf the operator is not null, the result is true
BETWEEN...AND...a BETWEEN b AND cIf a is between b and c, the result is true
LIKEa LIKE BSQL match, if a matches b, the result is true
INa IN (a1,a2,a3…)Suppose that a is in a1, or a2...and the result is true

Find the student surnamed Liu

-- LIKE关键词,%(代表0到任意个字符) _(一个字符)
SELECT `StudentNo`,`StudentName` FROM `student` WHERE `StudentName` LIKE '刘%';
Insert picture description here


Query the student whose surname is Liu, and the student whose name is two characters

SELECT `StudentNo`,`StudentName` FROM `student` WHERE `StudentName` LIKE '刘_';
Insert picture description here


Query the student whose surname is Liu, and the student whose name is three characters

SELECT `StudentNO`,`StudentName` FROM `student` WHERE `StudentName` LIKE '刘__';
Insert picture description here


Search for students whose name contains "嘉"

SELECT `StudentNo`,`StudentName` FROM `student` WHERE `StudentName` LIKE '&嘉&';
Insert picture description here


==IN (specific one or more values)==Keyword
query No. 1001, 1003, 1003 student

SELECT `StudentNo`,`StudentName` FROM `student` WHERE `StudentNo` IN (1001,1002,1003);
Insert picture description here


Inquire about students in Anhui and students in Luoyang, Henan

SELECT `StudentNo`,`StudentName` FROM `student` WHERE `Address` IN ('安徽','河南洛阳');
```![在这里插入图片描述](https://img-blog.csdnimg.cn/20210607111324993.png)
> null、not null

查询地址为空的学生null/''
```sql
SELECT `StudentNO`,`StudentName` FROM `student` WHERE `Address`='' OR `Address` IS NULL;
Insert picture description here


Query students with date of birth, not empty

SELECT `StudentNo`,`StudentName` FROM `student` WHERE `BornDate` IS NOT NULL;

Query students who do not have a date of birth (empty)

SELECT `StudentNo`,`StudentName` FROM `student` WHERE `BronDate` IS NULL;
Insert picture description here