Learn basic mysql operations in 5 minutes

mysql video tutorialThe column introduces how to quickly learn the basic operations of mysql

Related free learning recommendations: mysql video tutorial

Article Directory

• 1. What is SQL?
• • Category:
• 2. About database CRUD operations
• • 1. Operation table list:
  • 2. Operate the data in the table:
  • • a. Query
    • b.where condition:
• 3. Query
• • 1. Sorting query
  • 2. Aggregation function (column calculation)
  • 3 . Grouping query
  • 4. Sorting query
• 4. Constraints
• • 1. Non-null constraint: not null
  • 2. Unique constraint
  • • Instance operation:
  • 3. Primary key constraint: primary key
  • 4. Automatic growth: auto_increment
##5. Summary
• • Error example

---

1. What is SQL?

structured Query Language: Structured Query Language

Category:

1) DDL (Data Definition Language) data definition language is used Define database objects: database, table, column, etc. Keywords: create, drop, alter, etc.
2) DML (Data Manipulation Language) data manipulation language
is used to add, delete, and modify data in tables in the database. Keywords: insert, delete, update, etc.
3) DQL (Data Query Language) data query language
is used to query the records (data) of tables in the database. Keywords: select, where, etc.
4) DCL (Data Control Language) Data Control Language (understand)
Used to define the access permissions and security levels of the database, and create users. Keywords: GRANT, REVOKE, etc.

2. About database CRUD operations

#Createcreate database hzyc;create database if not exists hzyc98 character set gbk;#Retrieveshow databases;show create database hzyc98；#Updatealter database hzyc98 character set gbk;#Deletedrop database hzyc98;drop database if exists hzyc98; #查看当前使用的数据库select database();show tables;use hzyc98

1. Operation table list:

The table name/header is ：zoomlist

#查show tables; -- show tables_in_hzyc98desc zoomlist;#增create table zoomlist (
	Name  varchar(30),
	Age	  int,
	ID	  int,
	Height double(5,1))#删drop table if exists zoomlist;#改alter table zoomlist rename to newzoomlist;alter table zoomlist character set gbk;alter table zoomlist add Name varchar(20);#加列alter table zoomlist change Age newAge int;alter table zoomlist modify Age char(8);alter table zoomlist drop Name;/*设置类型：*/
 - int、double(5,1)、varchar(20) 
 - date 	#yyyy-MM-dd
 - datetime #yyyy-MM-dd HH:mm:ss 
 - timestamp#时间戳 yyyy-MM-dd HH:mm:ss

2. Operate the data in the table:

#除了数字，其他都需要引号来赋值insert into zoomlist (Name, Age, ID, Height) value('美洲豹',5,'20201207',3.2);insert into zoomlist ('美洲豹',5,'20201207',3.2);#删除delete from zoomlist where [条件];delete from zoomlist;TRUNCATE TABLE zoomlist;#修改update zoomlist set Name = '大笨象' Age = 12 where address = '深圳';update zoomlist set address = '深圳';

a. Query

#查询#尽量不要用 * 先desc一下表里面有啥，然后在决定展示什么东西。SELECT * FROM zoomlist; SELECT Name,Age FROM zoomlist;	 --只显示某个列,方便查看!SELECT　DISTINCT　Name FROM zoomlist; --去除结果中[完全重复]的SELECT Name,score1,score2,scroe1+scroe2 FROM zoomlist;--as:自定义名字展示,也可以不写asSELECT Name,scroe1+IFNULL(scroe2,0) 总分 FROM zoomlist; --ifnull遇到没有值的直接给赋值为0SELECT Name,score1,score2,scroe1+IFNULL(scroe2,0) AS 总分 --显示表头FROM zoomlist,peoplelist; --从zoomlist、peoplelist里面获取

b.where condition:

* > 、= 、= 、!=、--不等号* and、or、not --关键字比&&、||、！好用推荐* BETWEEN...AND --范围内都符合就行* IN( 集合) --特定值的范围* LIKE：模糊查询（1）_:单个任意字符；（2）%：多个任意字符* IS NULL例子：select Name, Age from Student where age between 12 and 20;select Name, Age from Student where age in (12,14,16,18);select Name, Age from Student where name like '%牛%'; --查名字里面包含了牛的学生select Name, Age from Student where name is not null; -- 查询学生：名字空的不查

3. Query

1. Sorting query

select * from employee order by age;select * from employee order by age asc; --升序select * from employee order by age desc; --降序select * from employee order by age desc height desc; --第一个一样的时候，才会用第二个方法排序（age降序，身高降序）

2. Aggregation function (column calculation)

Null data is excluded, and null data will not be involved in the calculation and no error will be reported!

count: count number
1. min, max, sum, avg: evaluation

2. select count(*) from student;select count(ifnull(age,20)) from student; select count(age) from student;--如果没有就不记录select count(id) from student; --我们一般选用主键来统计个数select max(age) from student;select min(age) from student;select sum(age) from student;select avg(age) from student;

3. Group query

group by After that, there are two different groups, and they can no longer view an independent individual.

• Fields to be queried after grouping: grouping fields, aggregate functions.
• What is the difference between where and having?
  where is qualified before grouping, and having is qualified after grouping;
  • where does not meet the conditions and will not participate in the grouping, having does not meet the conditions and will not be displayed;
  • Only having can Followed by aggregate function judgment.

select sex,count(name) from employee group by sex having count(name)4. Sorting query<h2></h2>limit is a dialect of MySQL, used for paging<p></p><pre class="brush:php;toolbar:false">SELECT * FROM student LIMIT 0,5; -- 第1页，从0索引开始，读5个数据SELECT * FROM student LIMIT 7,10; -- 第2页，从7索引开始（第8个数据），读10个数据

4. Constraints

Constraints:
• Primary key constraints: primary key
  1. Non-null constraints: not null
  2. Unique constraints: unique
  3. Foreign key constraints: foreign key

1. Non-null constraint: not null

-- 建表时添加非空约束：
 create table employee(
 	name char(30),
 	sex char(8) not null
 )
 alter table employee modify sex char(8) not null; --添加非空约束
 alter table employee modify sex char(8); --破除非空约束

2. Unique constraint

There can only be one null value, no more;

To delete constraints, you can only use drop index to delete unique constraints

-- 建表时添加唯一约束：
 create table employee(
 	name char(30),
 	sex char(8)，
 	score int unique --分数要唯一
 )
 --添加唯一约束alter table employee modify name char(8) unique;
 --破除唯一约束-- alter table employee modify sex char(8); 不可用--破除name身上的unique约束用drop index除去索引alter table employee drop index name;

Instance operation:

3. Primary key constraint: primary key

A table has only one primary key, non-empty and unique

It is the unique identifier of the record. Equivalent to index

-- 建表时添加主键约束：
 create table employee(
 	id int  primary key, --给id加上主键约束
 	name char(30),
 )
 --添加唯一约束alter table employee modify id int primary key;
 --破除唯一约束-- alter table employee modify id int; 不可用！--破除id身上的primary key约束只能用drop primary keyalter table employee drop primary key;

4. Automatic growth: auto_increment

is only useful for numerical values, and can generally be placed on the primary key for automatic growth

-- 建表时添加auto_increment：
 create table employee(
 	id int auto_increment, --给id加上auto_increment
 	name char(30),
 )
 --添加auto_increment，自动从1开始alter table employee modify id int auto_increment;--设置初值alter table employee auto_increment = 100;
 --破除auto_incrementalter table employee modify id int;

5. Summary

We learned what SQL is, made a simple introduction, and also listed some basic operations of MySQL, as well as what queries and constraints are.

But I have just come into contact with MySQL, so the basic operations are still relatively new. I need to learn more, practice more and practice more to gain true knowledge.

In the future, we will also learn about MySQL's multiple relationships, multi-table queries, transactions (not sure what they are yet), JDBC statements, database connection pool druid, JDBCTemplate... There are still many things to learn, but the task now is to sort out the basic things. It is the last word to consolidate the foundation! ! !

Error example:

If you do not write column names when adding data, you must give all column values, otherwise an error will be reported

The above is the detailed content of Learn basic mysql operations in 5 minutes. For more information, please follow other related articles on the PHP Chinese website!