MySql database learning

Keywords: Database MySQL SQL less

MySql database learning (1)

mySQL (relational database management system)

MySQL is a relational database management system, developed by MySQL AB company in Sweden, which is currently a product of Oracle. MySQL is one of the most popular relational database management systems. In WEB application, MySQL is one of the best RDBMS (Relational Database Management System) application software.

MySQL is a kind of relational database management system, which saves data in different tables instead of putting all data in a large warehouse, which increases speed and flexibility.

The SQL language used by MySQL is the most commonly used standardized language for accessing databases. MySQL software adopts the dual authorization policy, which is divided into community version and commercial version. Due to its small size, fast speed, low total cost of ownership, especially the characteristics of open source, MySQL is generally selected as the website database for the development of small and medium-sized websites.

  • SQL statement of MYSQL
    SQL:Structure Query Language
    DDL: data definition definition language: definition and database / table structure: create (create) drop (delete) alter (modify)
    DML: Data Manipulation Language: mainly used to insert (insert) update (modify) delete (delete) table data
    DCL: data control language: define access rights, cancel access rights, and set grant for security
    DQL: Data Query Language: select (query) from clause where clause

1. CRUD operation of database

  • First, log in to the database server: mysql -uroot -proot

Create database

create database Database name
create database day01;

--When creating a database, specify the character set
create database Database name character set character set;
create database day01_1 character set utf-8;

create database Database name character set character set collate Proofreading rules;
create database day01_2 character set utf-8 collate utf8_bin;

view the database

--View database defined statements
show create database Database name
show create database day_01;
show create database day_01_1;

--View all databases
show databases;

informartion_schema
performance_schema
mysql

Modifying database operations

--Modify the character set of data
alter database Database name character set character set;
alter database day01_1 character set gbk;

Delete database

drop database Database name;
drop database day01;

drop database test;

Other database operation commands

--Switch database (select database)
use Database name
use day01;
--Take a look at the database currently in use
select database();

2. CRUD operation of table

Create table

The name of the create database database;

create table table name(
	Column name column type (length) constraint,
	Column name 2 column type (length) constraint,
);



Type of column:
java          sql       
int           int
char/string   char/varchar
			  char: fixed length
			  varchar: variable length
			  char (3) - space
			  varchar (3) 11
			  Length represents the number of characters
double        double
float 		  float
boolean       boolean
date  		  date  :  YYYY-MM-DD
			  time  : hh:mm:ss
			  Datetime: yyyy-mm-dd HH: mm: SS default value is null
			  Timestamp: yyyy-mm-dd HH: mm: SS defaults to the current time
			  
			  Text: mainly used to store text
			  blob: binary



Column constraints:
		Primary key constraint: primary key
		Unique constraint: unique
		Non NULL constraint: not null

Create table:
	1. Analysis entity: Students
	2. student ID
	3. name
	4. sex
	5. age
create table student(
	sid int primary key,
	sname varchar(31),
	sex int,
	age int
);

View table

--View all tables
  show tables;
  
--View table definition
  show create table student;
  
--View table structure
  desc student;

Modify table
add, modify, change, drop, rename, character set

add column
 alter table table name add column type column
alter table student add chengji int not null;

modify column
alter table student modify sex varchar(2);
change column name
alter table student change sex gender varchar(2);
Delete column (dorp)
alter table student drop chengji;

Modify table name
rename table student to studenttable;
Modify the character set of the table
alter table studenttable character set gbk;

Delete table

drop table studenttable;

Sql operation on CRUD of data in the table completely:
insert data

insert into table name (column name 1, column name 2, column name 3) values (value 1, value 2, value 3); 

--Simple writing method: if the data of insert type full column name, the column name after the table name can be omitted
 insert into table name values (value 1, value 2, value 3);
insert into student values(2,'zhangsan',1,23);

--Note: if you are inserting some columns, the column name cannot be omitted
insert into student(sid,sname) values(3,'lisi');
insert into student values(3,'lisi'); / / this writing method is wrong

--Insert batch data:
insert into student(sid,sname,sex,age) values
(1,'zhangsan',1,23),
(2,'zhangsan',1,23),
(3,'zhangsan',1,23),
(4,'zhangsan',1,23),
(5,'zhangsan',1,23),
(6,'zhangsan',1,23);

--Efficiency of single insertion and batch insertion
  It depends on how much data is inserted. If there is more data, the efficiency of batch insertion is high.

--View data for table
select * from student;

insert into student values(10, 'Li Si', 1, 23);

  • Temporary solution: set names gbk; it's equivalent to the high-speed mysql server software. The current content we input under the command line is GBK code. When the command line window is closed, there will be problems when he enters Chinese.
  • Permanent solution: modify my.ini configuration (in mysql installation path)
    1. Suspend mysql service
    2. Find my.ini configuration file in mysql installation path; C:\Program Files\MySql Server 5.5
    3. Change the 57 line code to gbk
    4. Save file exit
    5. Start mysq service

Delete table record

delete from table name [where condition]

delete from student where sid = 10;
delete from student; if no condition is specified, all data in the table will be deleted one by one.

--Interview question: what's the difference between delete data and truncate data
	delete: DML deletes the data in the table one by one
	truncate: DDL deletes the table first and rebuilds the table
	Which is more efficient: it depends on the amount of data in the table
		If there is less data, delete is more efficient
		If there is more data, truncate is more efficient

Update table record

update Table name set Column names=Column value, column name=Column value2 [ where condition]
  --take sid Change the name of 5 to Li Si
  --If the parameter is a string, the date is quoted in single quotes
  update student set sname = 'Li Si' where sid = 5;


  update Table name set Column names=Column name value2=Column value2 [ where condition]
  
  update student set sname ='Wang Wu',sex='1';
  update student set sname ='Zhao Liu',sex='0' where sid=6;

Query record

select [distinct][*] [Column name2] from Table name [where condition]
distinct :  Remove duplicate data

--Product classification: Mobile Digital,Shoes bag...
1.Classified ID
2.Classification name
3.Classification description
create table category(
  cid int primary key auto_increment,
  cname varchar(10),
  cdesc varchar(31)
);

insert into category values(null,'Mobile phone digital','kk electronic product, kk production');
insert into category values(null,'Shoes schoolbag','kk Leather shoes factory, kk production');
insert into category values(null,'Cigarette drinks','kk Wine, kk production');
insert into category values(null,'Snack biscuits','kk Whoahaha, kk production');
insert into category values(null,'Spicy spicy bar','kk Weilong, kk production');

select * from category;
select cname,cdesc from category;

--All goods
1.commodity ID
2.Trade name
3.prices for goods
4.Date of manufacture
5.Commodity classification ID

//Commodity and commodity classification: relationship
create table product(
	pid int primary key auto_increment
	pname varchar(10),
	price double,
	pdate timestamp,
	cno int
);

insert into product values(null,'Millet 9',998,null,1);
insert into product values(null,'1+',2888,null,1);
insert into product values(null,'Adidas',99,null,2);
insert into product values(null,'Old village head',88,null,3);
insert into product values(null,'Strong wine',35,null,3);
insert into product values(null,'Jenny Bakery',1,null,4);
insert into product values(null,'Weilong spicy strips',1,null,5);
insert into product values(null,'Spicy strips',1,null,5);

--Simple query:
---Query all products:
	select * from product;
---To query the commodity name and price:
	select pname,price from product;
	
---Alias query as Keywords for, as Keywords can be omitted
	--Table alias: select p.pname ,p.price from product p;(Mainly used in multi table query);
	select p.pname,p.price from product as p;
	--Alias: select pname as Trade name ,price as commodity price from product;
	select pname as Trade name,price as commodity price from product;
	//ellipsisasKeyword
	select pname Trade name,price commodity price from product;

---Remove duplicate values
	--Query all prices of goods
	select price from product;
	select distinct price from product;

--select Arithmetic query : Only on the query results + - * /
 select *,price*1.5 from product;
 select *,price*1.5 as el Discount price from product;

 select *,price*0.9 from product;

--Price adjustment enquiry [ where Keyword]
	//Specify the conditions and determine the records to be operated

--Query commodity price>60 All product information of yuan
select * from product where price > 60 ;

--where The latter condition is written as follows:
	--Relational operators: >  >=  <   <=   =  !=  <>
	<> : Not equal to: StandardSQLgrammar
	!= : Not equal to: non-standardSQLgrammar
	--To query all goods whose price is not equal to 88:
	select * from product where price <> 88;
	select * from product where price != 88;

	
	--Query commodity price between 10 and 100
	select * from product where price > 10 and price >100;

	between ...and...
	
	select * from product where price between 10 and 100;
	
	--Logical operation:and , or ,not
	
	--It is found that the commodity price is less than 100 or more than 900.
    select * from product where price <35 or price >900;

	--like : Fuzzy query
		_ : Represents a character
		% : Represents more than one character
	-- Find out the desired products with cakes in their names '%cake%'
		select * from product where pname like '%cake%'
	-- The second name is all products of bear.  '_The bear%'
		select * from product where pname like '_The bear%'

	-- in Get value in a range
	    --Find commodity classification ID At 1,4,5 All the goods in it
	    select * from product where cno in (1,4,5);


--Sort query: order by Keyword
	asc : ascend Ascending (default sort method)
	desc :  descend Descending order
	
	--0.Query all goods and sort by price
	select * from product order by price
	
	--1.Query all goods and sort them in descending order by price( asc -Ascending order  desc-Descending order);
	select * from product order by price desc;

	--2.Query name has small goods, sorted by price descending
		1.Query name has small products
		select * from product where pname like '%Small%';
		2.Sort to get results
		select * from product where pname like '%Small%'; order by price asc;


--Aggregate function:
		sum() :Summation
		avg() :Average value
		count() :Statistical quantity
		max() :Maximum value
		min() :minimum value
	--1.Get the sum of all commodity prices:
	select sum(price) from product;
	--2.Get the average price of all goods:
	select avg(price) from product;
	--3.Get the number of all products:
	select count(*) from product;

	--Be careful: where Aggregate function cannot be followed by condition
	select * from product where price > avg(price);//This is wrong
	--Find out all products whose price is greater than the average price.
	//Query all items
	select * from product;
	//greater than
	//average price
	select avg(price) from product;
	
	select * from product where price > (select avg(price) from product);


--Grouping: group by
	
	--1.according to cno Field grouping, count the number of goods after grouping
	select cno ,count(*) from product group by cno;

	--2.according to cno Group, group count the average price of each group of goods and the average price of goods > 60
	select cno,avg(price)
	from product group by cno
	having avg(price) > 60;
	
	--having Keywords can be grouped after the occurrence of aggregate functions
	--where  Keyword is not allowed to connect aggregate function before grouping


--Writing order
--S..F ..W..G...H..O
	select..from..where..group by ..having ..order by

--Execution sequence
		F..W..G..H..S..O
		from.. where.. group by ..having ..select .. order by

Posted by fresch on Fri, 25 Oct 2019 21:22:24 -0700

Hot Keywords

©2024 Programmer Group