<>MySql Fundamentals

<> Data manipulation language DML
/* insert data mode 1: INSERT INTO Table name ( column 1, column 2……, column n) VALUES( value 1, value 2….. value n); mode 2: INSERT INTO
Table name set Listing 1= value 1,.. Listing n= value n; mode 3: INSERT INTO Table name ( column 1, column 2……, column n)
VALUES( value 1, value 2….. value n),( value 1, value 2….. value n); mode 4:INSERT INTO Table name ( column 1, column 2……, column n)
Query statement ( The number of columns in the query matches the number of inserted columns ) */ INSERT INTO student(NAME,sex,birthday,grade,score,phone,
address,reg_time) VALUE('tom',' male ','2000-1-2',1,98,'15929101866','xian',NOW())
INSERT INTO student SET NAME=' Zhang San ',sex=' male ',birthday='2020-1-3',grade=2,score=97,
phone='15959421585',address=' Shaanxi ',reg_time=NOW() INSERT INTO student(NAME,sex,
birthday,grade,score,phone,address,reg_time) VALUES(' Li Si ',' male ','2000-1-2',3,98,
'15929101899',' Xianyang ',NOW()), (' Wang Wu ',' female ','2000-1-4',3,98,'15929101888',' Xianyang ',NOW()),
(' Li Liu ',' male ','2000-1-5',3,98,'15929101877',' Xianyang ',NOW()) -- No description after table name , Represents inserting data into all columns in a table
The number of values should match the number of columns INSERT INTO stu(num,NAME,sex,birthday,grade,score,phone,address,
reg_time) SELECT * FROM student -- Modify table data UPDATE student SET NAME = ' Li Si ',sex=' male '
-- Add condition UPDATE student SET NAME = ' Zhao Liu ',sex=' female ' WHERE num =2233 -- Delete statement DELETE
FROM student WHERE num = 2237 -- Clear table data TRUNCATE TABLE student ALTER TABLE
<>DQL Data query statement
-- select Query results from surface where condition [ grouping ][ Grouping conditions ] sort /* Keyword query Function query Single-Row Functions character number date Grouping function
Conditional query */ -- The query result is constant SELECT 100 -- expression SELECT 10+10 SELECT grade+1 FROM student
-- function SELECT VERSION() # Query version SELECT NOW() # Query time -- Query the whole table data SELECT * FROM student
-- Query specific columns SELECT num,NAME,sex FROM student -- Remove duplicate data ( All columns of multi row data are the same ) Only for the query results to duplicate
SELECT DISTINCT sex FROM student -- Arithmetic operator +( Simple addition operation , There is no character splicing function ) - * / SELECT grade+
scoreFROM student -- Everyone's class +1 SELECT grade+1 FROM student -- Single-Row Functions ( Process each line ) --
LENGTH( Listing ) Returns the number and length of characters in bytes SELECT LENGTH(NAME) FROM student -- Character function --
(tr1,str2) SELECT CONCAT ('aa','bb','cc') SELECT CONCAT (NAME,' yes ',sex) FROM
student-- Capitalization SELECT UPPER(NAME) FROM student SELECT UPPER('abc') -- Convert to lowercase SELECT
UPPER('ABC') -- String interception SUBSTRING( Listing , Start position , Intercept length ) SELECT SUBSTRING(NAME,1,1) FROM
student-- Specifies the first occurrence location of the string INSTR( Listing ,str) SELECT INSTR(NAME,'o') FROM student --
Remove the space before and after TRIM( Listing ) SELECT TRIM(NAME) FROM student -- Remove the specified characters before and after TRIM(str from Listing )
SELECT TRIM('o' FROM NAME) FROM student -- Fill characters to the specified length LPAD( Listing , Add to specified length , Fill in content )
SELECT LPAD(NAME,6,'z') FROM student -- Replaces the column with the specified character REPLACE( Listing , Old string , New string )
SELECT REPLACE(NAME , 'a','z') FROM student -- Logic processing -- case when condition then Results of establishment
else Unsubstantiated results END from table SELECT CASE WHEN score>=90 THEN 'A' ELSE 'B' END
FROM student SELECT NAME, (CASE WHEN score>=90 THEN 'A' WHEN score>=80 AND score
<90 THEN 'B' WHEN score>=70 AND score<80 THEN 'C' ELSE 'D' END ) score #
Define the alias of the query result FROM student -- IFNULL( Columns judged to be empty , The default value if it is empty ) alias SELECT IFNULL(phone,
' Unregistered mobile number ') Moblie FROM student SELECT IFNULL(phone,NAME) Moblie FROM student --
IF( condition , Meet the results , Not satisfied with the result ) SELECT IF(score>=90,' excellent ',' good ')score FROM student -- Mathematical function --
round rounding SELECT ROUND(5.9) -- ceil Round up SELECT CEIL(5.4) -- floor Round down SELECT
FLOOR(5.9) -- Retains the specified number of decimal places , No carry SELECT TRUNCATE(5.5363,2) -- mod Surplus SELECT MOD(10,3)
-- rand obtain 0-1 Random number between SELECT RAND() -- Date handling function SELECT NOW() # Returns the time of the current system + date SELECT
CURDATE() # Returns the date of the current system SELECT CURTIME() # Returns the time of the current system -- Date formatting -- Get year only SELECT YEAR(
reg_time) FROM student -- Get months only SELECT MONTH(reg_time ) FROM student --
Convert string format to date format in database SELECT STR_TO_DATE('1999-5-23','%Y-%m-%d') -- A string that converts the date format to the specified format
SELECT DATE_FORMAT(reg_time,'%Y year %m month %d day ')m FROM student SELECT COUNT(*),
DATE_FORMAT(reg_time,'%Y year %m month %d day ')m FROM student GROUP BY m -- Calculate the time difference between two dates SELECT
DATEDIFF(reg_time,NOW()) FROM student -- Group function , Aggregate function , Statistical function /*
classification :sum Sum up ,avg average value ,max Maximum ,min minimum value ,count count 1.sum,avg Generally used to deal with numerical type ; max,min,count Can handle any type
2. All grouped functions are ignored null value 3. Can and distinct Collocation to realize the operation of de duplication 4.count General use of function count(*) It is used to count the number of rows */ -- Sum up
SELECT SUM(score) FROM student SELECT SUM(DISTINCT score) FROM student -- Average
SELECT AVG(score) FROM student -- Maximum SELECT MAX(score) FROM student -- minimum value
SELECT MIN(score) FROM student -- Statistical quantity SELECT COUNT(*) FROM student SELECT COUNT
(DISTINCT score) FROM student

©2019-2020 Toolsou All rights reserved,
Huawei 2021 session Hardware Engineer Logical post (FPGA) Super detailed surface !!!Vue-element-admin upgrade ui edition virtual machine VMware Download and install the most detailed tutorial !C++ Move constructor and copy constructor sound of dripping water java Backstage interview pygame Realize full screen mode and adjustable window size mysql Database setting character set configuration modification my.ini file (windows)30 What's the experience of being a junior programmer at the age of 20 C++ Multithreading programming ( Summary of common functions and parameters )python_ cherry tree