Basic operation of MySQL Foundation (I)

faramita_ of_ mine 2021-11-25 17:08:41
basic operation mysql foundation


The main content of this article is as follows :

One 、 install MySQL

Search for phpStudy V8.1 , Download about 78m about : Official website download address Click to download

phpStudy It can be upgraded and replaced at any time mysql edition , Install other common plug-ins . and phpStudy For database migration , Backup , start-up , The operation is very convenient .
 Insert picture description here
installed phpStudy I'll be in D Disk unzip a folder phpstudy_pro, In the future, if necessary, you can take the exam directly

MySQL The default account and password are root root

After starting the database , Click operation in the left navigation bar to modify the database password .
 Insert picture description here

Two 、 Database management tools

phpStudy The default comes with an easy-to-use MySQL Management tools HeidiSQL, Don't want to install SQLyog or Navicat Database management tools can use it .
 Insert picture description here
Click on the management , Enter basic information to connect to the database .
 Insert picture description here
You can also use it SQLyog Management database : Download address open SQLyog, Create a new connection , Fill in the relevant information , Can be in JspStudy Of MySQL Connect to the database at startup
 Insert picture description here
You can also use idea Connect MySQL
 Insert picture description here

3、 ... and 、 database

Database is the basic unit of data table storage , That is, a database stores one or more tables , Data tables in the same database can be associated with queries . General , In actual development, each project will use an independent database , It is not recommended to connect to multiple databases for a project . stay SQL Enter the command in the window and click execute , Will create a name called mydb The database of .

CREATE DATABASE mydb

An error will be reported when executing again , database mydb Already exist (exists)

 Error code : 1007
Can't create database 'mydb'; database exists

Use the command to view all databases

SHOW DATABASES

Delete database

DROP DATABASE mydb;

Using a database , It is convenient to create tables in the database .

USE mydb;

Four 、 Table operations

The identification of each column of data in the database is called a field , Table operations are generally operations on fields . When creating a table, there are usually one or more fields , They are separated by commas . The two most commonly used field types in the database are int and char It is used to store integer and string data respectively ,char You need to specify the number of stored characters after the type .

CREATE TABLE mytb (
id INT,
name CHAR(5)
);

After creating the table, you can use desc Table name view table structure .

Add, delete and modify fields , below SQL Show me how to manipulate fields

ALTER TABLE mytb DROP id; Delete field
ALTER TABLE mytb ADD ids INT; Add fields
ALTER TABLE mytb ADD id INT FIRST; Add a field at the beginning
ALTER TABLE mytb ADD sex INT AFTER name; Add a field in name after
ALTER TABLE mytb MODIFY name CHAR(10); modify name The length of the field is 10
ALTER TABLE mytb MODIFY ids BIGINT NOT NULL DEFAULT 100; modify ids The property of the field is long Not null. The default value is 100

Change the table name to mytable

ALTER TABLE mytb RENAME TO mytable;

Delete table mytable

DROP TABLE mytable

Be careful :

Delete table has drop,truncate,delete Three operations , Their differences are as follows :

drop: Delete data and table structure , Release space .

drop table user;

truncate: Keep the table structure , Delete data , Release space .

truncate table user;

delete: Keep the table structure , Delete data , Don't free up space .

delete from user;

The execution speed of the three , Generally speaking :drop > truncate > delete

Free space can be reflected in :

1. adopt delete The deleted row data does not free up space , If the table id If it's incremental , So the table data id It may not be continuous ; And by truncate Deleting data frees up space , If the table id If it's incremental , New data id Start from scratch again , Not at the maximum of deleted data id Increasing in value .

2. Use delete When deleting data ,mysql Did not delete the data file , Instead, delete the identification bit of the data file , No documentation , So it won't completely free up space . The deleted data will be saved in a link list , When new data is written ,mysql The deleted space will be used to write again . namely , The deletion operation will bring some data fragments , It is these fragments that are taking up hard disk space .

5、 ... and 、 Data addition, deletion, modification and query

Each piece of data in a database is usually called a record , The common operations of records are adding, deleting, modifying and querying , Insert records into the data table using the following syntax :

INSERT INTO table [( Field name [, Field name ...])]
VALUES (value [, value...]);

Be careful :

The inserted data should be of the same data type as the field .
The size of the data should be within the specified range of the column , for example : You can't change a length to 80 The string of is added to the length of 40 In the column of .
stay values The data positions listed in must correspond to the arrangement positions of the added columns . Character and date data should be enclosed in single quotes .
Insert a null value , Not specified or insert into table value(null) You can insert a null value .
Example :

INSERT INTO `emp` (`name`,`sal`) VALUES (' Zhang San ',10000.5);

Modify the record

Modify the records in the data table and use the following syntax :

UPDATE table_name SET Field name 1=expr1 [, Field name 2=expr2 ...] [WHERE where_definition]

Be careful :

  • UPDATE Syntax to update columns in the original table row with new values .
  • SET Clause to indicate which columns to modify and which values to give .
  • WHERE Clause specifies which rows should be updated . If not WHERE Clause , Update all rows .
    Example :
UPDATE emp SET `name` =' Li Si '
UPDATE emp SET `name`=' The king 2 ' WHERE `id` = 1;
UPDATE emp SET `name`=' The king 2 ',`sal`=16.5 WHERE `id` = 1;
UPDATE emp SET `name`=' The king 2 ',`sal`=16.5 WHERE `id` IN(1,3,5);

Be careful : If the data is modified , All fields of the table are the same , Sending a modification command will cause multiple pieces of data to be modified . meanwhile SQLYog Can pop up the window . Therefore, in order to avoid this problem, major database manufacturers put forward three paradigms of database as constraints .

Delete record

Delete the data in the data table using the following syntax :

delete from table_name [WHERE where_definition]

Be careful :

  • If not used where Clause , All data in the table... Will be deleted .
  • Use delete Statement only delete records , Don't delete the table itself .
  • To delete a table , Use drop table sentence .
    Example :
delete from `emp` where id = 1;

Simple query

The following syntax is generally used to query the data in the data table :

SELECT [DISTINCT] *|{
 Field name 1, Field name 2, Field name 3..} FROM table;

Parameters :

  • Select Specify which columns of data to query .
  • * Number means to query all columns , It is rarely used in practical development *, It increases the burden of queries .
  • From Specify which table to query .
  • DISTINCT Optional , When the result is displayed , Whether to eliminate duplicate data
    Example :
SELECT * FROM `emp`;
SELECT `name`,`sal` FROM `emp`;
SELECT DISTINCT `name`,`sal` FROM `emp`;

practice :

1. Create database user And in user Create table in Library user (int id,String name,int age,String password), And insert... Into the database 3 The following records .

User{id=1,name=‘ Zhang San ’,password=‘123456’}

User{id=2,name=‘ Li Si ’,password=‘123456’}

User{id=3,name=‘ The king 2 ’,password=‘123456’}

2. The above id by 1 The password of is changed to :abc123.

3. Delete password as 123456 All users of .

4. stay user Add a field of integer type to the table age stay name after , And insert data .

User{id=4,name=‘ Pockmarks ’,password=‘123456’,age=12}
Reference code :

CREATE DATABASE `user`;
CREATE TABLE `user`( `id` INT, `name` CHAR(10), `password` CHAR(10) );
INSERT INTO `user` (`id`, `name`, `password`) VALUES ('1', ' Zhang San ', '123456');
INSERT INTO `user` (`id`, `name`, `password`) VALUES ('2', ' Li Si ', '123456');
INSERT INTO `user` (`id`, `name`, `password`) VALUES ('3', ' The king 2 ', '123456');
UPDATE `user` SET `password` = 'abc123' WHERE `id` = '1';
DELETE FROM `user`.`user` WHERE `password` = '123456';
ALTER TABLE mytb ADD age INT AFTER name;
INSERT INTO `user` (`id`, `name`, `password`,`age`) VALUES ('4', ' Pockmarks ', '123456',12);

6、 ... and 、 Alias operators and sorting

During the query , Using the name of the original field is sometimes inconvenient ,MySQL Provide aliases to handle such situations .

SELECT Field name AS Alias FROM Table name ;

AS It can be omitted

Example :

SELECT `name` full name ,`sal` salary FROM `emp`;

Be careful : No single quotation marks can be used at the alias , However, in order not to conflict with some keywords in the database, it is recommended that all table names 、 Field names are enclosed in database specific single quotes ( Generally located tab Above key ).

Operator

stay WHERE Related operators are often used in clauses , Common operators are comparison operators 、 Logical operators, etc .

Comparison operator

> < <= >= = <>/!= Greater than 、 Less than 、 Greater than ( Less than ) be equal to 、 It's not equal to
BETWEEN ...AND... The value displayed in a certain range ( With head and tail )
IN(set) Displayed in the in Values in the list , example :in(100,200)
LIKE ‘% Zhang _’ Fuzzy query
IS NULL Determine whether it is null (is not null)

LIKE In the sentence ,% Represents zero or more arbitrary characters ,_ Represents a character .

Example :

name LIKE ‘_a%' Indicates that the second character is a People who name like ' Zhang %' Everyone surnamed Zhang name like '_ Wei ' All names are two words and the second word is “ Wei ” name like '% merchant %' The name contains “ merchant ” word name like '% friend ' Name with “ friend ” ending

Logical operators

and Multiple conditions hold at the same time
or Any of several conditions holds
not Don't set up , example :where not(salary>100);

Example :

SELECT `id`, `name`,`sal` FROM `emp` WHERE id > 1;
SELECT `id`, `name`,`sal` FROM `emp` WHERE id BETWEEN 1 AND 3;
SELECT `id`, `name`,`sal` FROM `emp` WHERE `name` IS NULL;
SELECT `id`, `name`,`sal` FROM `emp` WHERE `name` LIKE '% king %' AND id = 1;

Sort

The sorted column is the column name in the table , It can also be SELECT The column name specified after the statement .

ASC Ascending 、DESC Descending

ORDER BY Clause should be in SELECT End of statement .

Case study :

SELECT `id`, `name`,`sal` FROM `emp` WHERE `id` > 0 ORDER BY `name` ASC ;
SELECT * FROM `mytabe` ORDER BY `sex` DESC,`id` DESC

7、 ... and 、 Three paradigms of database ( self-taught )

First normal form (1NF): Fields are atomic , Can not be further divided . All relational database systems satisfy the first paradigm

The fields in the database table are all single property , Can not be further divided . for example , Name field , The last and first names must be taken as a whole , It's impossible to tell which part is the last name , Which part is the name , If you want to distinguish a family name from a first name , Must be designed as two separate fields .

Second normal form (2NF): Second normal form (2NF) It's in the first paradigm (1NF) Based on , That is to meet the second paradigm (2NF) We must first satisfy the first paradigm (1NF).

It is required that each instance or row in the database table must be uniquely differentiated . Usually you need to add a column... To the table , To store the unique identity of each instance . This unique attribute column is called the primary key or primary key . The attributes of an entity depend entirely on the primary keyword . The so-called complete dependence refers to the fact that there can't be attributes that only depend on a part of the primary key , If there is , Then this part of the attribute and the main keyword should be separated to form a new entity , There is a one to many relationship between the new entity and the original entity . To achieve this distinction, you usually need to add a column... To the table , To store the unique identity of each instance . In short And , The second paradigm is that non primary attributes are not partially dependent on primary keywords .

Third normal form (3NF): Meet the third paradigm (3NF) The second paradigm must be satisfied first (2NF). In short , Third normal form (3NF) It is required that a database table does not contain non primary key information that has been included in other tables . So the third paradigm has the following characteristics : 1, Each column has only one value 2, Every line can distinguish . 3, Each table does not contain non primary key information that other tables already contain . for example , Only the name of the sender can appear in the post table id, Instead of the person who posted the post id, The name of the sender also appears , otherwise , As long as the same poster appears id All records , The name part of them must be strictly consistent , This is data redundancy .
Chapter exercises :( Dictation 10 minute )

Data preparation :

create table student(
id int,
name varchar(20),
chinese float,
english float,
math float
)ENGINE = InnoDB DEFAULT CHARSET = utf8;
insert into student(id,name,chinese,english,math) values(1,' Zhang Xiaoming ',89,78,90);
insert into student(id,name,chinese,english,math) values(2,' Li Jin ',67,98,56);
insert into student(id,name,chinese,english,math) values(3,' Wang Wu ',87,78,77);
insert into student(id,name,chinese,english,math) values(4,' Li Yi ',88,98,90);
insert into student(id,name,chinese,english,math) values(5,' Li Laicai ',82,84,67);
insert into student(id,name,chinese,english,math) values(6,' Zhang Jinbao ',55,85,45);
insert into student(id,name,chinese,english,math) values(7,' Huang Rong ',75,65,30);

Homework :

1. Query all the information of all students in the table .
2. Look up the names of all the students and their English scores .
3. Filter English duplicate data in the table .
4. Add... To all students' math scores when querying 10 Points of specialty points of .
5. Count the total score of each student .
6. Use aliases to indicate student scores .
7. Query the grades of students named Wang Wu
8. Query English score greater than 90 Classmate
9. Total query score greater than 200 All students
10. Query English scores in 80-90 Between the students .
11. Query math score is 89,90,91 Classmate .
12. Check the grades of all students surnamed Li .
13. Query math score >80, Chinese is divided into >80 Classmate .
14. Output math scores in descending order .
15. After sorting the total score, output , And then output it from high to low
16. Sort the students' grades by their surname Li

Refer to the answer :

1. Query all the information of all students in the table .
mysql>SELECT * FROM student;
2. Look up the names of all the students and their English scores .
mysql>SELECT name,english FROM student;
3. Filter English duplicate data in the table .
mysql>SELECT DISTINCT english FROM student;
4. Add... To all students' math scores 10 Points of specialty points of .
mysql>SELECT name,math+10 FROM student;
5. Count the total score of each student .
mysql>SELECT name,chinese+english+math FROM student;
6. Use aliases to indicate student scores .
mysql>SELECT name AS full name ,chinese+english+math Total score FROM student;
7. Query the grades of students named Wang Wu
mysql>SELECT * FROM student WHERE name=' Wang Wu ';
8. Query English score greater than 90 Classmate
mysql>SELECT * FROM student WHERE english>90;
9. Total query score greater than 200 All students
mysql>SELECT name AS full name ,chinese+english+math Total score FROM student WHERE (chinese+english+math)>200;
10. Query English scores in 80-90 Between the students .
mysql>SELECT name,english FROM student WHERE english BETWEEN 80 AND 90;
11. Query math score is 89,90,91 Classmate .
mysql>SELECT name,math FROM student WHERE math IN (89,90,91);
12. Check the grades of all students surnamed Li .
mysql>SELECT * FROM student WHERE name LIKE ' Li %';
13. Query math score >80, Chinese is divided into >80 Classmate .
mysql>SELECT * FROM student WHERE math>80 AND chinese>80;
14. Sort the math grades and output them .
mysql>SELECT name,math FROM student ORDER BY math;
15. After sorting the total score, output , And then output it from high to low
mysql>SELECT name full name ,chinese+english+math Total score FROM student ORDER BY Total score DESC;
16. Sort the students' grades by their surname Li
mysql>SELECT name full name ,chinese+english+math Total score FROM student WHERE name LIKE ' Li %' ORDER BY Total score ;
版权声明
本文为[faramita_ of_ mine]所创,转载请带上原文链接,感谢
https://javamana.com/2021/11/20211109074137157p.html

  1. 应急响应入门之Linux分析排查
  2. Twitter如何升级Hadoop+Kafka架构实现实时处理数十亿个事件?
  3. 引人入胜,实战讲解“Java性能调优六大工具”之linux命令行工具
  4. docker 查看实时日志
  5. JFrog Artifactory 7.27 上传应用到私服和从maven私服下载制品
  6. Ces protocoles http simples
  7. [including thesis + source code] JavaWeb hospital triage registration management system SSH [package running successfully]
  8. Java初学者,想知道如何用if语法当条件成立后什么都不执行,否则执行动作
  9. 体验.NET Core使用IKVM对接Java
  10. 深入JavaScript高级语法-coderwhy
  11. 排序算法--Java实例/原理
  12. 停止docker时报错:Warning: Stopping docker.service, but it can still be activated by: docker.socket
  13. 【完整示例】采用jenkins pipeline实现自动构建并部署至k8s
  14. 【Linux】腾讯云服务器,使用FRP内网穿透,端口映射,远程访问内网ubuntu机器
  15. 关于#java#的问题:resultMap type映射不到我想要的类 只能映射java的内部类 加了全路径也映射不了 怎么解决
  16. 排序算法--Java實例/原理
  17. 就这一次,阿里最新出品源码阅读指南,一套搞定JDK+vm源码
  18. 两个小时手写了个Zookeeper分布式服务注册中心
  19. Algorithme de tri - - instance / principe Java
  20. Plongez dans la syntaxe avancée javascript - coderwhy
  21. JavaScript高级程序设计读后感(一)之零碎知识点查漏补缺
  22. 先到先学!Alibaba甩出第四次更新的JDK源码高级笔记(终极版)
  23. Java File类
  24. How To Install MariaDB on linux
  25. #yyds干货盘点# Mybatis 的 XML 配置
  26. Spring认证中国教育管理中心-Spring Data MongoDB教程七
  27. Linux进程和任务管理
  28. Linux文件系统日志分析
  29. Redis-客户端-重点知识
  30. Redis-事件-重点知识
  31. Redis-AOF持久化-重点知识
  32. Redis-RDB持久化-重点知识
  33. http://lx.gongxuanwang.com/sszt/32.htm
  34. 回顾我两个月面试阿里,携程,小红书,美团,网易等等(Java岗)
  35. JavaScript高级程序设计读后感(一)之零碎知识点查漏补缺
  36. Rocketmq source code analysis: message sending process
  37. Rocketmq source code analysis: how does rocketmq store messages?
  38. RocketMQ source analysis: how to debug the RocketMQ source in IDEA
  39. How To Install MariaDB on linux
  40. Comment installer mariadb sur Linux
  41. http://lx.gongxuanwang.com/sszt/7.htm
  42. Classe de fichiers Java
  43. Premier arrivé, premier servi! Alibaba lance la quatrième mise à jour de JDK source Advanced notes (Ultimate)
  44. #yyds干货盘点#设计模式之【工厂模式】
  45. Java * SpringBoot实现万能文件在线预览,已开源,真香
  46. Redis | 第4章 Redis中的数据库《Redis设计与实现》
  47. Liang Tingwei's first variety show of "director, please give advice" reshapes the classic work "spring of a new town"
  48. Redis | 第4章 Redis中的数据库《Redis设计与实现》
  49. 关于centos docker版本过低导致 is not a valid repository/tag: invalid reference format
  50. Redis 源码简洁剖析 02 - SDS 字符串
  51. 回顧我兩個月面試阿裏,攜程,小紅書,美團,網易等等(Java崗)
  52. Rétrospectivement, j'ai passé deux mois à interviewer Ali, ctrip, Little Red Book, meituan, NetEase, etc. (Java post)
  53. Docker + webhook Automation Deployment Front End Project
  54. Java技术之Spring、Hibernate框架整合方法
  55. http://lx.gongxuanwang.com/sszt/32.htm
  56. 亚马逊自己的 Linux 发行版现在完全基于 Fedora 了
  57. Redis 源码简洁剖析 02 - SDS 字符串
  58. Java技術之Spring、Hibernate框架整合方法
  59. Méthode d'intégration des cadres de printemps et d'hibernation de la technologie Java
  60. Redis source Concise Analysis 02 - SDS String