Mathematical Functions, string functions, aggregate functions _ MySQL

1. -- Mathematical functions; ABS (-8) absolute values; ceiling (3.12); floor; power (2, 3) power; -- round () rounding -- sqrt square root, squar square -- string function -- ASCII returns the leftmost character ascii code selectASCII (#39; 1,

-- Mathematical functions; ABS (-8) absolute value, ceiling (3.12) take online, floor take lower limit, power (2, 3) power,

-- Round () Rounding

-- Sqrt square root and squar Square

-- String functions

-- ASCII returns the leftmost character ascii code of the string.

Select ASCII ('name ')

Select ASCII (name) from biao -- view the first character ascii code of all people's names

-- Char converts ascii code into characters

Select CHAR (70)

Select CHAR (yuwen) from biao -- converts all Chinese scores to characters

-- Note that the (integer) converted expression or constant must be between 0 and. if the value exceeds the limit, n is output.

-- LEN returns the length of the string.

Select LEN ('asdfghh ')

Select LEN (name) from biao -- display the length of all names

-- Charindex returns the index where the first character of a string appears from the beginning.

Select CHARINDEX ('D', 'asdfghhjkkhg ') -- the index starts from 1.

Select CHARINDEX ('20140901', birth) from studant -- view the index displayed on the birthday

-- Difference return similarity 0-4 Indicates similarity

Select DIFFERENCE ('asddfghjk ', 'adfjkgh ')

-- LEFT: truncates a string from the LEFT.

Select LEFT ('asfgdsssdgh', 4)

-- RIGHT: RIGHT

Select right ('asfgdsssdgh', 4)

-- All lower values are converted to lowercase letters.

Select LOWER ('asfasdgsgddfqweg ')

-- Upper case

Select upper ('asfasdgsgddfqweg ')

-- Ltrim remove spaces on the left

Select LTRIM ('asd ')

-- Remove spaces on the right of Rtrim

Select RTRIM ('asd ')

-- Patindex is equivalent to the first character Index bit in the character string returned by charindex

Select PATINDEX ('% df %', 'ssdfghss ')

-- Replace

Select REPLACE (sex, 'female, 'girly ') from biao -- show only, do not change

-- Replicat copy and paste

Select REPLICATE ('asd ', 3)

-- Reverse flip

Select REVERSE ('asdfgghjk ')

-- Space

Select 'A' + SPACE (5) + 'BC'

-- Str is forcibly converted to a string.

Select STR (123456.222,) -- parameter 1 is the value to be converted, and parameter 2 is the length retained after conversion

-- Parameter 3 is the number of digits to be retained after the decimal point

-- Note that parameter 2 cannot be converted when it is less than the integer part of parameter 1

-- Stuff

-- From the position of the index, you can see that you do not need to delete a few digits, and then insert the content to be inserted.

-- Parameter 1 is the string to be inserted.

-- Parameter 2 starts with the index number.

-- Parameter 3 indicates whether to delete several characters from the backend.

-- Parameter 4 is the newly inserted character

Select STUFF ('asddfg', 3, 2, 'Hello ')

-- Substring

-- Truncate a string

-- Parameter 1 is the truncated string.

-- Which index does parameter 2 start?

-- Parameter 3 is the truncation length.

Select SUBSTRING ('asdfghjjgfddrtyy ', 4, 5)

2,

-- Query all

Select * from student

-- Query all information of Li Si

Select * from student where name = 'Li si'

-- Query Li Si's score

Select score from student where name = 'Li si'

-- Add a data record (if data is deleted before, it will be placed in the first row of data deleted from the beginning)

Insert into student values (7, 'Bell ', '2017-4-5', 'male', 79)

-- Rename Zhang quanegan

Update student set name = 'Lee egg 'Where name = 'Zhang quaneg'

-- Change Bell to gender

Update student set sex = 'female 'where name = 'Bell'

-- Zhao liuzhuan

Delete from student where name = 'Zhao Liu'

-- Sort, ascending

Select * from student order by score

-- Descending order

Select * from student order by score desc

-- As long as the highest score

Select top 1 * from student order by score desc

-- Only Min

Select top 1 * from student order by score

-- View the names of all students (use as to display aliases)

Select name as student name from student

-- View the names and scores of all students (alias)

Select name as student name, score as score from student

-- View all information of all students surnamed Wang (fuzzy query)

Select * from student where name like 'Wang %'

-- View all information of all students in 1993

Select * from student where birth like '20140901'

-- View the information of all students with 6 on the last day of their birthdays

Select * from student where birth like '% 6'

-- View all information of students whose name is a dog

Select * from student where name like '% dog %'

-- View all information of a student surnamed Li with only two characters in name

-- Underline fuzzy query, which only represents one character (not commonly used)

Select * from student where name like 'Lee _'

-- View all information of students with scores over 80

Select * from student where score> = 80

-- View information about all students whose scores are between 60 and 80

Select * from student where score between 60 and 80

-- View the names of students whose scores are less than 60

Select name as student name from student where score <60

-- Modify the sex = male for all students whose scores are between 70 and 80

Update student set sex = 'male' where score between 70 and 80

-- Computing 345678 + 789456

Select 345678 + 789456

-- Query all people's names distinct deduplication

Select distinct name from student

-- Check the gender

Select distinct sex from student

-- In is used to determine whether a condition exists. it can be seen as an element.

-- View all information about the name of "Li dog egg" and "bell"

Select * from student where name in ('Lee egg ', 'Bell ')

-- Parentheses in quotation marks and underscores (_) are used to select any value.

Select * from student where name like '_ [Lee's dog, Bell]'

-- Aggregate function: sum avg max min count

-- Sum of all scores

Select SUM (score) as SUM from student

-- Average score of all scores

Select AVG (score) as average score from student

-- View the highest score

Select MAX (score) as highest score from student

-- View Shard points

Select MIN (score) as minute score from student

-- View total number of users

Select COUNT (*) as total COUNT from student

-- Check whether there are several

Select COUNT (*) as quantity from student where name = 'Lee 4'

-- Group by group

-- Grouping by men and women

Select sex from student group by sex

-- Grouping by male and female, calculate the average score after grouping

Select sex as gender, AVG (score) as average score from student group by sex

-- View the number of men and women respectively

Select sex as gender, COUNT (*) as COUNT from student group by sex

-- Check the numbers of men and women with scores over 70.

Select sex as gender, COUNT (*) as COUNT from student where score> = 70 group by sex

-- View groups of men and women with scores over 70 and more than 3

Select sex as gender, COUNT (*) as COUNT from student where score> = 70 group by sex having COUNT (*)> 3

-- Grouping by male and female, calculate the average score after grouping, and the average score is greater than 70

Select sex as gender, AVG (score) as average score from student group by sex having AVG (score)> 70

3. instance

Create table cangku

(

Code int,

Name varchar (50 ),

Zong int,

Price decimal (18,2)

)

Go

Insert into cangku values (1, 'apple', 30, 2.9)

Insert into cangku values (2, 'PE)

Insert into cangku values (3, 'watermelon ', 37, 1.9)

Insert into cangku values (4, 'steamed bream)

Insert into cangku values (5, 'pork ', 20, 5)

Insert into cangku values (6, 'eggplant ', 45, 3.6)

Insert into cangku values (7, 'cucumber ', 60, 2.4)

Insert into cangku values (8, 'cabbage ', 30, 0.8)

Insert into cangku values (9, 'cantalanga)

Insert into cangku values (10, 'Pumpkin ', 30, 1.89)

-- Sold 3 out of 3rd products

Update cangku set zong = 34 where code = 3

-- Sell 7 of the 4th products

Update cangku set zong = 23 where code = 4

-- Sell 5 of the 5th products

Update cangku set zong = 15 where code = 5

-- View all information about the products with the minimum inventory

Select top 1 * from cangku order by zong

-- The minimum number of goods purchased is 30.

Update cangku set zong = 30 from cangku where code = 5

-- Now there are 3rd types, 4th types, and 5th types of products with a price increase

Update cangku set price = price + 1 from cangku where code in (3, 4, 5)

-- 6th types, 7th types, 8th types, all price reduction

Update cangku set price = price-1 from cangku where code in (6, 7, 8)

-- Sold 3 out of 6th products

Update cangku set zong = zong-3 from cangku where code = 6

-- Sell three of the 7th products

Update cangku set zong = zong-3 from cangku where code = 7

-- Sell 9 of 8th products

Update cangku set zong = zong-9 from cangku where code = 8

-- View all information about the products with the minimum inventory

Select top 1 * from cangku order by zong

-- The minimum number of goods purchased is 30.

Update cangku set zong = 30 from cangku where code = 5

Select * from cangku