编程知识 cdmana.com

Basic operation of MySQL Foundation (I)


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://cdmana.com/2021/11/20211109074137157p.html

Scroll to Top