MySQL learning note 1 (add, delete, check and modify)

To create a table:

/*
    Create database
    create database Database name;
*/
CREATE DATABASE mybase;
/*
  Use database
  use Database name
*/
USE mybase;

/*
      Create data table format
      
      create table Table name(
          Column name 1 data type constraint,
          Column name 2 data type constraint,
          Column name 3 data type constraint
      );
      Create user table, user number, name, user address
      Set the numbered column as the primary key constraint to ensure the uniqueness and non emptiness of the column data
      primary key AUTO_INCREMENT
      Let the primary key column data grow automatically
*/
 CREATE TABLE users (
     uid INT PRIMARY KEY AUTO_INCREMENT ,
     uname VARCHAR(20),
     uaddress VARCHAR(200)
 );
/*
Table operation, not commonly used
show tables Show all data tables desc users View structure in table drop table users Delete data table */

 

Modify table structure:

 CREATE TABLE users (
     uid INT PRIMARY KEY AUTO_INCREMENT,
     uname VARCHAR(20),
     uaddress VARCHAR(200)
 );
 /*
    Add column, add field
    alter table Table name add column name data type constraint
 */
  ALTER TABLE users ADD tel INT ;
  
  /*
    Modify column, modify on the original column
    Modify column name, data type constraint  
    alter table Table name modify column name data type constraint
  */
  ALTER TABLE users MODIFY tel VARCHAR(50);
  
  /*
     Modify column name
     alter table Table name change old column name new column name data type constraint
  */
  ALTER TABLE users CHANGE tel newtel DOUBLE;
  
  /*
    Delete column
    alter table Table name drop column name  
  */
  ALTER TABLE users DROP newtel;
  
  /*
     Modify table name
     rename table Table name to new name 
  */
  RENAME TABLE users TO newusers

 

To add data to a data table:

CREATE TABLE product(
   -- Primary key column,Automatic growth
   id INT PRIMARY KEY AUTO_INCREMENT,
   -- Commodity name,Variable character,Not empty
   pname VARCHAR(100) NOT NULL,
   -- prices for goods,double
   price DOUBLE
);

/*
   Add data insert to data table
   Format:
     insert into Table name (column name 1, column name 2, column name 3) values (value 1, value 2, value 3)
     Note: corresponding problems and type problems
*/

INSERT INTO product (id,pname,price) VALUES (1,'Computer',6666.66);
INSERT INTO product (id,pname,price) VALUES (2,'Mobile phone',1888.88);

/*
   Add data format without considering primary key
   Format:
     insert into Table name (column name) values
*/
INSERT INTO product (pname,price) VALUES('Washing machine',800);

/*
   Add data format, all values are given
   Format:
     insert into Table name values (full column values)
*/
INSERT INTO product VALUES (4,'Microwave Oven',300.25);

/*
   Add data format, batch write
   Format:
     insert into Table name (column name 1, column name 2, column name 3) values (value 1, value 2, value 3), (value 1, value 2, value 3)
*/
INSERT INTO product (pname,price) VALUES 
('intelligent robot',25999.22),
('Color TV',1250.36),
('Sofa',5899.02)

 

Modify the data in the table:

/*
   Update data
   Data is modified on the original basis
   Format:
     update Table name set column 1 = value 1, column 2 = value 2 where condition
     where Conditions: uniqueness in data
*/

-- modify
UPDATE product SET price = 15999 WHERE id=2

-- modify
UPDATE product SET pname='Black and white TV', price=100 WHERE id = 6

/*
    The writing method of modification conditions
    id=6    Equal, don't write double equal
    id<>6   Not equal to
    id<=6 
 
    And or not & &|!
    &&To write and
    ||To write or 
    ! To write not
    
    id in (1,3,4,5,6) Contain
*/
UPDATE product SET price = 2000 WHERE id = 1 OR id = 7;

/*
  Delete data in table
  Format:
    delete from Table name where condition
    
    drop table Table name delete entire data table
    Another truncate method is also delete. The difference is that after deletion, a new table is rebuilt, and auto add is restarted. The delete method does not affect auto add
*/
-- delete
DELETE FROM product WHERE id=8;

Pay special attention to one problem: the default code I set during installation is utf8, but the default code for windows is gbk,

If you open the command line, you will find that there is a garbled code problem. Solution: if you want to display normal Chinese characters in the console, add this line:

SET NAMES gbk;

 

 

Query data in table:

 

CREATE TABLE zhangwu (
  id INT PRIMARY KEY AUTO_INCREMENT, -- Accounting ID
  zname VARCHAR(200), -- Account name
  zmoney DOUBLE -- Amount of money
);
INSERT  INTO zhangwu(id,zname,zmoney) VALUES (1,'Meal expenses',247);
INSERT  INTO zhangwu(id,zname,zmoney) VALUES (2,'Wage income',12345);
INSERT  INTO zhangwu(id,zname,zmoney) VALUES (3,'Clothing expenditure',1000);
INSERT  INTO zhangwu(id,zname,zmoney) VALUES (4,'Meal expenses',325);
INSERT  INTO zhangwu(id,zname,zmoney) VALUES (5,'Stock income',8000);
INSERT  INTO zhangwu(id,zname,zmoney) VALUES (6,'Expenditure on playing mahjong',8000);
INSERT  INTO zhangwu(id,zname,zmoney) VALUES (7,NULL,5000);


/*
   Query data for a specified column
   Format:
     select Column name 1, column name 2 from table name
*/
SELECT zname,zmoney FROM zhangwu;

/*
  Query data for all columns
  Format:
    select * from Table name
*/
SELECT * FROM zhangwu;

/*
   Query remove duplicate records
   DISTINCT Keyword follows column name
*/
SELECT DISTINCT zname FROM zhangwu

/*
  Query rename column
  as keyword
*/
SELECT zname AS 'name' FROM zhangwu 

/*
  In query data, direct mathematical calculation
  Columns calculate numbers
*/
SELECT zname,zmoney+1000 AS'sum' FROM zhangwu

-- Query all meal expenses
SELECT * FROM zhangwu WHERE zname='Meal expenses'

-- Query amount is greater than 1000
SELECT * FROM zhangwu WHERE zmoney>1000

-- Query amount between 2000 and 5000 
SELECT * FROM zhangwu WHERE zmoney >= 2000 AND zmoney <= 5000
-- Transformed into between and mode
SELECT * FROM zhangwu WHERE zmoney BETWEEN 2000 AND 5000

-- Query amount is 1000,3500,5000 One of them
SELECT * FROM zhangwu WHERE zmoney = 1000 OR zmoney=3500 OR zmoney=5000
-- Transformed into in mode
SELECT * FROM zhangwu WHERE zmoney  IN (1000,3500,5000)


-- like Fuzzy query with wildcard
-- Query all expenses (including these two words to find out)
SELECT * FROM zhangwu WHERE zname LIKE '%expenditure%'


-- Query account name,Five character
SELECT * FROM zhangwu WHERE zname LIKE'_____'

-- Query account name,Not empty
SELECT * FROM zhangwu WHERE zname IS NOT NULL

SELECT * FROM zhangwu WHERE NOT ( zname IS NULL)

 

Sort query:

/*
  Queries, sorting result sets
  Ascending, descending, sort the specified column
  order by Column name [desc][asc]
  desc Descending order
  asc  In ascending order, can not write
*/
-- Query account table,Price in ascending order
SELECT * FROM zhangwu ORDER BY zmoney ASC

-- Query account table,Price in descending order
SELECT * FROM zhangwu ORDER BY zmoney DESC

-- Query account table,Query all expenses,Descending amount
-- Pre filter condition where Reorder query results
SELECT * FROM zhangwu WHERE zname LIKE'%expenditure%' ORDER BY zmoney DESC

 

Aggregate function:

/*
   Use aggregate function to query calculation
*/

-- count Summation,Sum the number of data in the table  count(Column names)
-- Query in the statistical accounting table,How many pieces of data are there
SELECT COUNT(*)AS'count' FROM zhangwu

-- sum Summation,Sum the data in a column sum(Column names)
-- Query of accounting statement,Sum all amounts
SELECT SUM(zmoney) FROM zhangwu
-- Summation,Count the total amount of all expenses
SELECT SUM(zname) FROM zhangwu WHERE zname LIKE'%income%'

INSERT INTO zhangwu (zname) VALUES ('Lottery revenue')

-- max function,For a column of data,Get maximum
SELECT MAX(zmoney) FROM zhangwu

-- avg function,Calculate the average of all data in a column
SELECT AVG(zmoney)FROM zhangwu

 

Group query:

 

/*
    Query all data
    What's the total cost of eating
    What is the total wage income
    What is the total expenditure on clothing
    What is the total stock income
    How much is the total expenditure for playing mahjong
    
    Group query: group by grouped column name
    Must follow aggregate function
    select When querying, the grouped column should appear after the select column
*/
  SELECT SUM(zmoney),zname FROM zhangwu GROUP BY zname
  
-- Yes zname Group query and sum content,But just pay
SELECT SUM(zmoney)AS 'getsum',zname FROM zhangwu WHERE zname LIKE'%expenditure%'
GROUP BY zname
ORDER BY getsum DESC

-- Yes zname Group query and sum content,But just pay, Display amount greater than 5000
-- Result set is after grouping query,Filter again,out of commission where, Filter again after grouping,keyword having
SELECT SUM(zmoney)AS 'getsum',zname FROM zhangwu WHERE zname LIKE'%expenditure%'
GROUP BY zname HAVING getsum>5000

Tags: MySQL Database Mobile Windows

Posted on Sun, 03 May 2020 12:56:35 -0700 by drums