mysql application:
1.client: client program, which connects to the server
2.SQLyog is easy to use and can be executed directly from multiple command lines.
3. Send e-mail: the default port is net25. The default port of the database is 3306. This port is unified according to the port of the server
4.server side: on linux.
show databases; Displays the names of all databases in mysql.
show tables; Displays the names of all tables in the current database.
What is a transaction:
Transaction is the smallest work unit of database operation and a series of operations executed as a single logical work unit; These operations are submitted to the system as a whole, either all or none, or both succeed or fail at the same time.
Add / delete this query: query (more for testing)
Type of index
Index optimization
Optimization of slow sql
Differences between different storage engines
lock
Write - h is the server
-P (capital P) is the specified port
Command: MySQL - uroot - h127 0.0. 1 -p
mysql -uroot -h127.0.0.1 -P3306 -proot
First all libraries, second all tables
%Indicates a qualified IP identified password******
Password forgotten: reset password
flush privileges must execute this sentence
Reset password: mysqld --initialize --user=root --console
Authorization command
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'gloryroad' WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO 'testman'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
show databases; use test; #Use a database show tables; #Can you see what tables it has create database huanghuang; #Create table drop database huanghuang; # delete select database(); #View the library currently in use mysql> create table huanghuang(id int,name varchar(20),age int,birthday datetime ); Query OK, 0 rows affected (0.25 sec) #Create a table
show tables; # See if the watch I built is there
#Insert several statements:
mysql> insert into huanghuang values(1,"huang",28,"1980-10-10 10:10:10"); Query OK, 1 row affected (0.02 sec) mysql> insert into huanghuang values(2,"li",18,"1981-11-10 10:10:10"); Query OK, 1 row affected (0.01 sec) mysql> insert into huanghuang values(3,"zhang",18,"1981-11-10 10:10:10"); Query OK, 1 row affected (0.01 sec) mysql> insert into huanghuang values(4,"wang",18,"1981-11-10 10:10:10"); Query OK, 1 row affected (0.02 sec) mysql> insert into huanghuang values(4,"zhao",10,"1981-11-10 10:10:10"); Query OK, 1 row affected (0.02 sec)
#How many pieces of data are viewed
select * from huanghuang;
#Look what's in the watch
show create table huanghuang;
#View table structure
desc huanghuang;
#Multiple lines can be inserted with values
mysql> insert into huanghuang values(5,"zhao",10,"1981-11-10 10:10:10"),(6,"qian ",8,"2010-1-1 1:1:1"); Query OK, 2 rows affected (0.05 sec) Records: 2 Duplicates: 0 Warnings: 0
#Specified field name: corresponding to each
mysql> insert into huanghuang(id,name,age,birthday) values(6,"zhao",10,"1981-11- 10 10:10:10"),(7,"qian",8,"2010-1-1 1:1:1"); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0
#Query specified column
select id,name from huanghuang;
#Change the query to Chinese, which is called alias
select id Serial number,name name from huanghuang;
#Simple query
select * from huanghuang where id=1; select * from huanghuang where id!=1; select * from huanghuang where id>=1; select * from huanghuang where id<=1; select * from huanghuang where id between 2 and 5; select * from huanghuang where id in(1,2,3); select * from huanghuang where id in (select id from huanghuang where id>=5);
Intersection with id
select id from huanghuang where id>=5 and id <=6;
With name
select id from huanghuang where id>=5 and name="qian"; #or select id from huanghuang where id>=5 or name="qian"; select id from huanghuang where name="zhang" or name="qian";
#Sort:
select * from huanghuang order by id desc;
Do not write is the default:
select * from huanghuang order by id; select * from huanghuang order by id asc; select * from huanghuang order by id,age asc; #Special note: you can only use descending order first, such as id descending order, and then use the same id for descending order after the search results are obtained (use the id to search the results first, and then use the id for sorting twice) select * from huanghuang order by id desc, age desc;
select * from huanghuang order by id desc, age desc limit 1;
#The first result is taken, and the next 1 is taken, starting from 0 and starting from 5
select * from huanghuang order by id desc, age desc limit 5,1;
#Get different names distinct (different, different)
select distinct name name from huanghuang;
#How many statistical names are there
select count(name) name from huanghuang;
Tip:
The database is used to store things. Don't let it do anything else
Grouping: connected with multiple tables, most of them are tested in the interview.
Grouping must have aggregation functions, such as count(*)
select count(*) from huanghuang group by age having age =1 select count(*),age from huanghuang group by age;
#The average age is greater than 10
mysql> select avg(age) average age,id from huanghuang group by id having avg(age) > 10;
Inside avg sum, this is called aggregate function
New data in database
create table student( id int not null auto_increment, name varchar(20) not null, sex char(1), submission_data date, primary key(id) )engine = innodb character set utf8;
create table grade( id int not null auto_increment, stuid int not null, class varchar(20) not null, grade int(3), primary key(id) )engine = innodb character set utf8;
insert into student (name,sex,submission_data) values("Zhang San","male","2010-10-10");
insert into student (name,sex,submission_data) values("Li Si","male","2010-10-10");
insert into student (name,sex,submission_data) values("Wang Wu","male","2010-10-10");
insert into student (name,sex,submission_data) values("Zhao Liu","male","2010-10-10");
insert into grade (name,sex,submission_data) values("Ding Yi","male","2010-10-10");
insert into grade (stuid,class,grade) values(1,"computer","100"); insert into grade (stuid,class,grade) values(1,"guitar","90"); insert into grade (stuid,class,grade) values(1,"Fine Arts","80"); insert into grade (stuid,class,grade) values(2,"computer","100"); insert into grade (stuid,class,grade) values(2,"guitar","90"); insert into grade (stuid,class,grade) values(2,"Fine Arts","80"); insert into grade (stuid,class,grade) values(3,"guitar","90");
#Multiple table queries are associated with each other through IDs
#Equal to inner connection
select a.name,sum(grade) from student a inner join grade b on a.id=b.stuid group by b.stuid;
select a.name,sum(grade) from student a left join grade b on a.id=b.stuid group by b.stuid;
If there is no data in the right table, NULL will be displayed, and the left connection is dominated by the left table;
All linked tables a and b in on must have data
select a.name,sum(grade) from student a left join grade b on a.id=b.stuid group by a.id;
The data on the right side of the right connection must have the data on the left. If there is no data on the left, NULL will be displayed
select a.name,sum(grade) from student a right join grade b on a.id = b.stuid group by b.stuid;
Theoretical basis:
Internal connection: (# efficiency is too low, so select is too low)
If the id of table a appears in the stuid of table b, the data will be displayed in the result
Left connection:
If the id of table a appears in the stuid of table b, the related data of table b will be displayed. If it does not appear in table b, the related row will display NULL
Right connection:
If the stuid of table b appears in the id of table a, the data of table a will be displayed. If it does not appear in table a, the data column of table a will be NULL
Summary understanding:
If there is no data in the right table, NULL will be displayed, and the left connection is dominated by the left table;
All linked tables a and b in on must have data
The data on the right side of the right connection must have the data on the left. If there is no data on the left, NULL will be displayed
union all no duplication union duplication Union
select id from student union all select stuid from grade;
select id from student union select stuid from grade;
Foreign key
A column of table B is associated with a column of table a, and all values of this column of data of table B must appear in the associated column of table a
Data created: create table a (id int , primary key(id) ); create table b (id int, primary key(id), CONSTRAINT FK_ID FOREIGN KEY(id) REFERENCES a(id) );
The id of table A is the foreign key of table B
mysql> delete from b where id =2; Query OK, 1 row affected (0.02 sec) mysql> insert into b values(2); Query OK, 1 row affected (0.02 sec)
#explain how to check whether there is a problem with your sql. See how many rows are scanned by executing the sql. For example, 100000 rows are scanned by executing the sql
truncate table b;
Clear all the table data, indexes and so on. The table is still missing
Go to the table with drop
drop table b； Just kill them all
delete from b;
All the data of b is gone. His index is in.
Neither truncate nor drop logs, and there is no rollback.
Fuzzy query“
mysql> select * from student where name like 'Zhang%'; +----+------+------+-----------------+ | id | name | sex | submission_date | +----+------+------+-----------------+ | 1 | Zhang San | male | 2010-10-10 | +----+------+------+-----------------+ 1 row in set (0.00 sec)
mysql> select * from student where name like '%five'; +----+------+------+-----------------+ | id | name | sex | submission_date | +----+------+------+-----------------+ | 3 | Wang Wu | male | 2010-10-10 | +----+------+------+-----------------+ 1 row in set (0.00 sec)
mysql> select * from student where name like '%Wang Wu%'; +----+------+------+-----------------+ | id | name | sex | submission_date | +----+------+------+-----------------+ | 3 | Wang Wu | male | 2010-10-10 | +----+------+------+-----------------+ 1 row in set (0.00 sec)