Shulou(Shulou.com)06/01 Report--

This article is to share with you about the basic operation of mysql. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

Article catalogue

What is SQL? Classification: 2. About database CRUD operation 1. Operation table list:2. Manipulate the data in the table: a. Query b.where conditions: 3. Query 1. Sort query 2. Aggregate function (column calculation) 3. Group query 4. Sort query 4, constraint 1. Non-null constraint: not null2. Unique constraint instance operation: 3. Primary key constraint: primary key4. Automatic growth: auto_ increment 5. Summary error example 1. What is SQL?

Structured Query Language: structured query language

Classification:

1) DDL (Data Definition Language) data definition language

Used to define database objects: databases, tables, columns, etc. Keywords: create, drop,alter, etc.

2) DML (Data Manipulation Language) data manipulation language

Used to add, delete and modify the data of the tables in the database. Keywords: insert, delete, update, etc.

3) DQL (Data Query Language) data query language

Used to query the records (data) of tables in the database. Keywords: select, where, etc.

4) DCL (Data Control Language) data control language (understanding)

Used to define the access rights and security level of the database, and to create users. Keywords: GRANT, REVOKE, etc.

Second, about the database CRUD operation # 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;#, view the currently used database select database (); show tables;use hzyc981. Operation table list:

Table name / header is: zoomlist

# check show tables;-show tables_in_hzyc98desc zoomlist;# add create table zoomlist (Name varchar (30), Age int, ID int, Height double (5jue 1)) # delete drop table if exists zoomlist;# to alter table zoomlist rename to newzoomlist;alter table zoomlist character set gbk;alter table zoomlist add Name varchar (20); # add alter table zoomlist change Age newAge int;alter table zoomlist modify Age char (8); alter table zoomlist drop Name / * setting type: * /-int, double (5 yyyy-MM-dd 1), varchar (20)-date # yyyy-MM-dd-datetime # yyyy-MM-dd HH:mm:ss-timestamp# timestamp yyyy-MM-dd HH:mm:ss2. Manipulate the data in the table: # except for numbers, quotation marks are required to assign insert into zoomlist (Name, Age, ID, Height) value ('Jaguar', 5 "20201207"); insert into zoomlist ('Jaguar ", 5" 20201207 "); # Delete delete from zoomlist where [condition]; delete from zoomlist;TRUNCATE TABLE zoomlist;# modifies update zoomlist set Name =' stupid elephant 'Age = 12 where address =' Shenzhen'; update zoomlist set address = 'Shenzhen' a. Query # try not to use * desc what's in the table and then decide what to show. SELECT * FROM zoomlist; SELECT Name,Age FROM zoomlist;-- only shows a certain column for easy viewing! SELECT DISTINCT Name FROM zoomlist;-- removes the [completely duplicated] SELECT Name,score1,score2,scroe1+scroe2 FROM zoomlist;--as in the result: custom name display, or you can leave asSELECT Name,scroe1+IFNULL (scroe2,0) total score FROM zoomlist unwritten. -- if ifnull encounters no value, it directly assigns the total score of 0SELECT Name,score1,score2,scroe1+IFNULL (scroe2,0) AS-- displays the header FROM zoomlist,peoplelist;-- gets the b.where condition from zoomlist and peoplelist: * >, <, =, =,! =,-- unequal sign * and, or, not-- keyword ratio & &, | |,! Easy to use recommended * BETWEEN...AND-- all within the range of the line * IN (collection)-- range of specific values * LIKE: fuzzy query (1) _: single arbitrary character (2)%: multiple arbitrary character * IS NULL examples: select Name, Age from Student where age between 12 and 20 position select Name, Age from Student where age in (12 and 14, 16, 18); select Name, Age from Student where name like'% Niu%';-- check the name contains Niu student select Name, Age from Student where name is not null;-- query student: the name is empty, do not check three, query 1. Sort query select * from employee order by age;select * from employee order by age asc;-ascending select * from employee order by age desc;-descending select * from employee order by age desc height desc;-when the first is the same, the second method is used to sort (age descending, height descending) 2. Aggregate function (calculation of columns)

Excluded null data, and there is null data will not participate in the calculation, will not report an error!

Count: count min, max, sum, avg: evaluate select count (*) from student;select count (ifnull (age,20)) from student;select count (age) from student;-- without select count (id) from student;-- We usually use the primary key to count select max (age) from student;select min (age) from student;select sum (age) from student;select avg (age) from student;3. Grouping query

After group by, there are two different groups, and they can no longer look at an individual.

Fields queried after grouping: grouped fields, aggregate functions. The difference between where and having? Where is qualified before grouping, and having is qualified after grouping; if where does not meet the conditions, having will not be displayed if it does not meet the conditions; only having can be followed by the aggregate function to judge. Select sex,count (name) from employee group by sex having count (name)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.