Common MYSQL commands _ MySQL

MYSQL common commands

Summary: mysql operations are commonly used for addition, deletion, modification, and query. MYSQL operations are a complete solution -- SQL killer

MYSQL operations-SQL killer code:

-- ================================ Simple query, add, delete, and modify ================

-- View all data in the student table

Select * from studio

-- Insert a new student information

Insert into studio (st_name, st_sex, st_age, st_add, st_tel) values ("Huang Lanqi", 13943943334, 'nanchong ', '123 ')

-- View all data of class

Select * from class

-- Add two pieces of data to the class table

Insert into class (cl_class, cl_coding, cl_o_time, cl_remark) values ('New electric training class', 'gxa-ncs-001 ', '2017-03-11', 'are good friends ')

Insert into class (cl_class, cl_coding, cl_o_time) values ('Aba normal training class', 'gxa-ABSZ-001 ', '2017-03-11 ')

-- Importance of updating a data condition

Update class set cl_remark = 'is really good' where cl_id = 5

-- Importance of deleting a data condition

Delete from class where cl_id = 7

-- Modify the column title

Select cl_id as 'class primary key', cl_class as 'class name' from class

Select name = st_name from studio

-- Use text strings

Select 'name: ', st_name from studio

-- ================== Query, add, delete, modify, ======================

-- Mainly involves or and not between in like ><=!> ! <! = <> () <=> = Is null is not null

-- Query all information with cl_id greater than 1

Select * from class where cl_id> 1

-- Use or

Select * from class where cl_id <> 10 or cl_class = 'baijie first class'

-- Use and

Select * from class where cl_id <> 10 and cl_class = 'baijie first class'

-- Use like and %

Select * from class where cl_class like 'baijie %'

Select * from class where cl_remark like '% am %'

-- Use

Select * from class where cl_id between 3 and 5

-- Use between with not

Select * from class where cl_id not between 3 and 5

-- Use is not null

Select * from class where cl_remark is not null

-- Use in

Select * from class where cl_class in ('first class of Qixing ', second class of 'baijie ')

-- ========================== Use mathematical operators ======================= ===

-- Mainly involves + = */

-- Query the number of weeks required for Java-related courses, which is calculated based on the number of 5 days per week and 6 courses per day.

Select 'Result '= co_num/5/6 from course where co_name in ('Java basics', 'Java project start ')

-- ============================== Use the aggregate function ========================= ======

-- COUNT SUM AVG MAX MIN

-- Query the total number of courses with less than 50 course hours

Select count (*) from course where co_num <50

-- Query the total number of lessons in all courses

Select sum (co_num) from course

-- Calculate the total course time fee. assume 50 RMB for each course.

Select sum (co_num) * 50 from course

-- Query the courses with the least class hours

Select min (co_num) from course

-- Query the courses with the most class hours

Select max (co_num) from course

-- Query the average number of lessons per course

Select avg (co_num) from course

-- ========================== Use mathematical functions ======================= ============

-- Includes the ABS function, PI () function, SIN () function, and EXP () function.

-- Query the sine of each course

Select sin (co_num) from course

-- Query the absolute value of each course

Select abs (co_num) from course

-- Query the number of hours of each course multiplied by the circumference rate. I don't know anything about it. it seems that it cannot be reached by 8.5.

Select pi () * co_num from course

-- Query the index of each course

Select exp (co_num) from course

-- Returns 5 randomly generated numbers (0 ~ Random float value between 1)

Declare @ I tinyint

Set @ I = 1

While @ I <= 5

Begin

Select rand (@ I) as 'randomly generated number', @ I as 'current value'

Set @ I = @ I + 1

End

-- Returns a numeric expression rounded to the specified length or precision-ROUND

Select round (345.456,-1) as 'parameter is-1'

, Round (345.456,-) as 'parameter is-2'

, Round (345.456, 0) as 'parameter is 0'

, Round (345.456, 1) as 'parameter is 1'

, Round (345.456, 2) as 'parameter is 2'

-- ======================== Use the date function ============================= =

-- DAY (), MONTH (), YEAR () -- Returns the number of days, months, and years of the specified date;

Select day (cl_s_time) as 'day' from class -- return day

Select 'month' = month (cl_s_time) from class -- return month

Select 'year' = year (cl_s_time) from class -- return year

-- DATEADD (datepart, number, date) -- Increase the number of given date types on the date;

Select dateadd (yyyy, 4, cl_s_time) as 'add 4 years later 'from class -- datepart-year

Yy, yyyy

Select dateadd (q, 2, cl_s_time) as 'add 2 quarters later 'from class

-- Datepart-quarter

Qq and q

Select dateadd (mm, 3, cl_s_time) as 'adds the 'From class after January 1, March

-- Datepart-month

Mm, m

-- Datepart-one day of each year

Dy, y

-- Datepart-date

Dd, d

-- Datepart-week

Wk, ww

-- Datepart-Hour

Hh

-- Datepart-minutes

Mi, n

-- Datepart-seconds

Ss, s

-- Datepart-millisecond

MS

-- DATEDIFF (datepart, date1, date2) -- gets the number difference of the given date type between two dates (the result of the entire function is a date2-date1 );

Select datediff (mm, cl_s_time, cl_o_time) as 'total duration' from class

-- Datepart (datepart, date) -- returns the value of the specified date type (integer) based on the given date );

-- In fact, this is equivalent to the DAY, MONTH, and YEAR functions.

Select datepart (dd, cl_s_time) as 'date' from class

-- GETDATE () -- returns the current date and time. When designing a database, we usually use it as the default value.

Update class set cl_s_time = getdate () where cl_id = 6

Select * from class

Select CONVERT (varchar (100), GETDATE (), 0): 05 16 2006 AM

Select CONVERT (varchar (100), GETDATE (), 1): 05/16/06

Select CONVERT (varchar (100), GETDATE (), 2): 06.05.16

Select CONVERT (varchar (100), GETDATE (), 3): 16/05/06

Select CONVERT (varchar (100), GETDATE (), 4): 16.05.06

Select CONVERT (varchar (100), GETDATE (), 5): 16-05-06

Select CONVERT (varchar (100), GETDATE (), 6): 16 05 06

Select CONVERT (varchar (100), GETDATE (), 7): 05 16, 06

Select CONVERT (varchar (100), GETDATE (), 8): 10:57:46

Select CONVERT (varchar (100), GETDATE (), 9): 05 16 2006 10: 57: 46: 827AM

Select CONVERT (varchar (100), GETDATE (), 10): 05-16-06

Select CONVERT (varchar (100), GETDATE (), 11): 06/05/16

Select CONVERT (varchar (100), GETDATE (), 12): 060516

Select CONVERT (varchar (100), GETDATE (), 13): 16 05 2006 10: 57: 46: 937

Select CONVERT (varchar (100), GETDATE (), 14): 10: 57: 46: 967

Select CONVERT (varchar (100), GETDATE (), 20): 10:57:47

Select CONVERT (varchar (100), GETDATE (), 21): 10:57:47. 157

Select CONVERT (varchar (100), GETDATE (), 22): 05/16/06 10:57:47 AM

Select CONVERT (varchar (100), GETDATE (), 23 ):

Select CONVERT (varchar (100), GETDATE (), 24): 10:57:47

Select CONVERT (varchar (100), GETDATE (), 25): 10:57:47. 250

Select CONVERT (varchar (100), GETDATE (), 100): 05 16 2006 AM

Select CONVERT (varchar (100), GETDATE (), 101): 05/16/2006

Select CONVERT (varchar (100), GETDATE (), 102): 2006.05.16

Select CONVERT (varchar (100), GETDATE (), 103): 16/05/2006

Select CONVERT (varchar (100), GETDATE (), 104): 16.05.2006

Select CONVERT (varchar (100), GETDATE (), 105): 16-05-2006

Select CONVERT (varchar (100), GETDATE (), 106): 16 05 2006

Select CONVERT (varchar (100), GETDATE (), 107): 05 16,200 6

Select CONVERT (varchar (100), GETDATE (), 108): 10:57:49

Select CONVERT (varchar (100), GETDATE (), 109): 05 16 2006 10: 57: 49: 437AM

Select CONVERT (varchar (100), GETDATE (), 110): 05-16-2006

Select CONVERT (varchar (100), GETDATE (), 111): 2006/05/16

Select CONVERT (varchar (100), GETDATE (), 112): 20060516

Select CONVERT (varchar (100), GETDATE (), 113): 16 05 2006 10: 57: 49: 513

Select CONVERT (varchar (100), GETDATE (), 114): 10: 57: 49: 547

Select CONVERT (varchar (100), GETDATE (), 120): 10:57:49

Select CONVERT (varchar (100), GETDATE (), 121): 10:57:49. 700

Select CONVERT (varchar (100), GETDATE (), 126): 2006-05-16T10: 57: 49.827

Select CONVERT (varchar (100), GETDATE (), 130): 18 ???? ?????? 1427 10: 57: 49: 907AM

Select CONVERT (varchar (100), GETDATE (), 131): 18/04/1427 10: 57: 49: 920AM

-- ================ Use a string function =====================================

-- String link operator

The result of the select statement is '='. the class name is '+ cl_class +', and the class number is '+ cl_coding from class.

-- Use the SUBSTRING function to intercept a string

Select substring (cl_class, 1, 4) from class

-- Returns 3 characters from the left of the string.

Select left (cl_class, 3) from class

-- Similarly, return

Select right (cl_class, 3) from class

-- Number of characters returned

Select len (cl_class) from class

-- Replace

Select replace (cl_class, 'training', 'harden ') from class

-- =====================Use system functions ===========================

Select host_id ()

-- Return workstation ID

Select host_name ()

-- Return the name of the computer on which the workstation runs

Select db_id ()

Select db_name ()

Select object_id ('Stu _ course_add ')

-- Obtain the server ID of the server object by name

Select object_name (151671588)

-- The opposite is true.

-- ======== Xueyun.com-tianhaokan-==========

-- Order by function-sort

Select * from studio order by st_name

-- Multiple sorting conditions

Select * from studio order by st_name DESC, st_age DESC, st_sex DESC

-- Conditional mainly refers to the position of the condition and clause.

Select * from studio where cl_id = 1 order by st_name

-- Group by clause function-GROUP Statistics

Select cl_id as 'class number', count (*) as 'students 'from studio group by cl_id

-- Average age statistics by dormitory

Select ho_id as 'dormitory number', avg (st_age) as 'average age' from studio group by ho_id

-- Multi-group

Select ho_id as 'dormitory number', cl_id as 'class number', avg (st_age) as 'average age' from studio group by ho_id, cl_id

-- Conditional mainly refers to the position of the condition and clause.

Select ho_id as 'dormitory number', avg (st_age) as 'average age' from studio where cl_id = 1 group by ho_id

-- Use the having clause function-specify search conditions for a group or aggregation, which is usually used with the group by clause. After grouping query is completed, filter progress.

Select ho_id as 'dormitory number', avg (st_age) as 'average age' from studio group by ho_id having avg (st_age)> 35

-- Multiple conditions

Select ho_id as 'dormitory number', avg (st_age) as 'average age' from studio group by ho_id having avg (st_age)> 35 and ho_id> 2

-- === Xueyun.com-tianfenghang-[url] www.ixueyun.com [/url] ======= join query ===== xueyun.com- [url] www.ixueyun.com [/url] ====

-- A query using the union clause is called a joint query. function: combines two or more query result sets into a single result set, which includes all row data in all sets.

-- Next we try to combine multiple queries

Select * from studio where cl_id = 1

Union

Select * from studio where ho_id = 1

Union

Select * from studio where st_age> = 30

-- Next we will continue to use the example above to add All to see the effect

Select * from studio where cl_id = 1

Union all

Select * from studio where ho_id = 1

Union all

Select * from studio where st_age> = 30

-- Continue to use it and sort it.

Select * from studio where cl_id = 1

Union all

Select * from studio where ho_id = 1

Union all

Select * from studio where st_age> = 30

Order by st_id

-- =========== Connection query ==========================

-- Connection query, function-query data from multiple tables together

-- Inner join: use the comparison operator ><... to compare some databases between tables and list the data rows matching the connection conditions in these tables.

-- Equi join, of course, is to use the equal sign. if it is wrong, you should also ask

Select * from studio inner join class on studio. cl_id = class. cl_id

-- Specify and sort the columns to be queried (also called natural connections)

Select st_id as 'number', st_name as 'student name', cl_class as 'class name' from studio inner join class on studio. cl_id = class. cl_id order by st_id

-- Use table alias

Select st. st_name as 'student name', st. cl_id as 'class number', cl. cl_class as 'class name' from studio as st inner join class as cl on st. cl_id = cl. cl_id

-- Unequal connections. this is a funny question. Since equal signs are used for equivalent connections, shouldn't they be non-equals?

-- Next we will connect to the third table to see how it works.

Select st. st_name as 'student name', st. cl_id as 'class number', cl. cl_class as 'class name', ho. ho_coding as 'dormitory number'

From studio as st inner join class as cl

On st. cl_id = cl. cl_id

Inner join hostel as ho

On st. ho_id = ho. ho_id

-- Let's add another condition for him.

-- Where st. cl_id> 2

-- Sort him again

-- Order by st. st_id

-- External connection:

-- Different from the internal connection, the internal connection must have at least one row in two tables that meet the connection conditions. The outer join will return rows that meet any conditions.

-- His table has the master-slave relationship. he uses each row in the master table to match the rows in the table. Unlike the internal connection, he does not discard the rows that do not match, instead, it is filled with null to the slave result set.

-- Left outer join

Select st. st_id as 'student number', st. st_name as 'student name', cl. cl_id as 'class number', cl_class as 'class name'

From studio as st left outer join class as cl

On st. cl_id = cl. cl_id

Where cl. cl_id> 2

-- Multi-table

Select tka. te_co_id as 'course schedule no'

, Cl. cl_id as 'class number', cl. cl_class as 'class name'

, Co. co_id as 'course ID', co. co_name as 'course name', co. co_num as 'number of Lessons'

, Te. te_name as 'Instructor name'

From te_kc_ap as tka left outer join class as cl

On tka. cl_id = cl. cl_id

Left outer join

Course as co

On tka. co_id = co. co_id

Left outer join

Teacher as te

On tka. te_id = te. te_id

-- ================================ Right outer link ================= =====

Select st. st_id as 'student number', st. st_name as 'student name', cl. cl_id as 'class number', cl_class as 'class name'

From studio as st right outer join class as cl

On st. cl_id = cl. cl_id

Where cl. cl_id> 2

-- Multi-table

Select tka. te_co_id as 'course schedule no'

, Cl. cl_id as 'class number', cl. cl_class as 'class name'

, Co. co_id as 'course ID', co. co_name as 'course name', co. co_num as 'number of Lessons'

, Te. te_name as 'Instructor name'

From te_kc_ap as tka

Right outer join class as cl

On

Tka. cl_id = cl. cl_id

Right outer join teacher te

On

Tka. te_id = te. te_id

Right outer join course co

On

Tka. co_id = co. co_id

-- ======= Full connection ======================

Select st. st_id as 'student number', st. st_name as 'student name', cl. cl_id as 'class number', cl_class as 'class name'

From studio as st full outer join class as cl

On st. cl_id = cl. cl_id

Order by st. st_id

-- Multi-table

Select tka. te_co_id as 'course schedule no'

, Cl. cl_id as 'class number', cl. cl_class as 'class name'

, Co. co_id as 'course ID', co. co_name as 'course name', co. co_num as 'number of Lessons'

, Te. te_name as 'Instructor name'

From te_kc_ap as tka

Full outer join class as cl

On

Tka. cl_id = cl. cl_id

Full outer join teacher te

On

Tka. te_id = te. te_id

Full outer join course co

On

Tka. co_id = co. co_id

-- ============ Cross-connection ====================

-- This method returns the Cartesian product of all data rows in two tables without the where clause (the rows in the first table are multiplied by the rows in the second table)

-- Use the student and class tables for cross-query

Select st_name, cl_class from studio cross join class

Select st_name, cl_class from studio, class

Select st_name, cl_class from studio cross join class

-- ========= Self-connection ===

----------------- Create a table at the beginning -------------

Create table zone (

Id int primary key identity (1, 1) not null,

Z_zone varchar (30 ),

Z_id int references zone (id ))

-- Can you give a default value here?

Select * from zone

Insert into zone (z_zone) values ('Beijing ')

Insert into zone (z_zone, z_id) values ('Beijing', 4)

Insert into zone (z_zone) values ('Sichuan ')

Insert into zone (z_zone, z_id) values ('Chengdu ', 6)

Insert into zone (z_zone, z_id) values ('mianyang ', 6)

Insert into zone (z_zone) values ('Jiangsu ')

Insert into zone (z_zone, z_id) values ('Nanjing ', 10)

Insert into zone (z_zone, z_id) values ('Suzhou ', 10)

Insert into zone (z_zone, z_id) values ('Wuxi ', 10)

Insert into zone (z_zone, z_id) values ('changzhou ', 10)

----------------------------------------------

-- Take a look at the general use of self-connection

Select a. z_zone, B. z_zone from zone as a inner join zone as B on a. z_id = B. id

-- Extended application

Select B. z_zone, count (a. z_zone) as 'number of jurisdictions 'from zone as a inner join zone as B on a. z_id = B. id group by B. z_zone

-- Simply put, you can connect yourself to the same table.

Select a. st_name, a. st_add, B. st_name, B. st_add from studio as a inner join studio as B on a. st_add = B. st_add

-- If we find that someone is equal to ourselves, add a condition.

Select a. st_name, a. st_add, B. st_name, B. st_add from studio as a inner join studio as B on a. st_add = B. st_add and a. st_name! = B. st_name

-- ===== Subquery ===== love micro-network-day bombing-====

-- Insert another SQL statement into an SQL statement to teach you how to set up a query. the entered SQL statement is called a subquery. Is an additional method for processing multi-table operations

-- A subquery is also called an internal query, while a Select statement containing a subquery is considered as an external query. a subquery can include one or more subqueries, or include any number of subqueries.

-- Subquery using in

Select * from studio where cl_id in (select cl_id from class where cl_id> 2)

-- Use not in

Select * from studio where cl_id not in (select cl_id from class where cl_id> 2)

-- Subquery using the comparison operator -- any indicates any value in the subquery. all indicates each value in the subquery.

-- Use any

Select * from class where cl_id> any (select cl_id from studio where st_age> 30)

-- Use all

Select * from class where cl_id> all (select cl_id from studio where st_age> 30)

-- =========== A paging SQL statement ======

Select top 3 * from studio

Where st_id> all (select top 3 st_id from studio order by st_id)

Order by st_id

-- Use exists. when this keyword is used to introduce a subquery, it basically tests whether the data exists.

-- We can query the classes where the persons are located. they are numbered 1.

Select * from studio where exists (select cl_id from class where studio. cl_id = class. cl_id and class. cl_id = 1)

-- Use not exists

Select * from studio where not exists (select * from class where studio. cl_id = class. cl_id and class. cl_id = 1) order by st_id

-- Generate a new table based on the query

Select st_name into class_3 from studio where cl_id = 3

-- Batch insert data into a table

Insert into class_3 select st_name from studio where cl_id = 4

----------------------- SQL programming --------------

Declare @ max int;

-- Declare a variable @ max

Set @ max = 1;

-- Assign values to the variable @ max

While @ max <10

-- If @ max is less than 10, it enters the loop.

Begin

Set @ max = @ max + 1; -- add 1 to @ max every cycle

Print @ max;

-- Print the current @ max value

End

Print 'finally finished looping ';

BitsCN.com