#—————————
#—-cmdCommand line to connect to MySql———
cd C:\Program Files\MySQL\MySQL Server 5.5\bin
# Start MySQL server
net start mysql
# Close the MySQL server.
net stop mysql
# Enter the MySQL command line.
mysql -h localhost -u root -p
#—————————
#—-MySqlUser management —
#To change the password: first, under DOS, enter the bin directory of the MySQL installation path, then type the following command:
mysqladmin -uroot -p123 password 456;
#Adding users
#Format: grant permissions on database. * to username @ Login Host identified by ‘password’
/*
For example, add a user user1 password to password1 so that it can login on the machine and have permission to query, insert, modify, delete all databases. First, connect the root user to MySQL, then type the following command:
grant select,insert,update,delete on *.* to user1@localhost Identified by “password1”;
If you want the user to log on to MySQL on any machine, change localhost to “%”.
If you don’t want user1 to have a password, you can call another command to remove the password.
grant select,insert,update,delete on mydb.* to user1@localhost identified by “”;
*/
grant all privileges on wpj1105.* to sunxiao@localhost identified by ‘123’; #all privileges All permissions
#—————————-
#—–MySqlDatabase operation basis –
# Create database
create database namage default character set utf8 collate utf8_general_ci;
# If there is a deletion in the database
drop database if exists manage;
# Database entry
use manage;
# Delete database
drop manage;
# Look at the structure of tables
desc class;
# View table data
select * from class;
# Create class table and add fields:
create table class(
id int(10) not null auto_increment,
name varchar(30) not null default ” noname”,
add_time datetime no t null,
primary key(id)
)
ENGINE = INNODB charset=utf8；
# 1、Add 2 data to the table: if the add_time field is datetime
insert into class(name,add_time) values (“First grade, “2018-08-31 15:33”.
insert into class(name,add_time) values (“Grade two, “2018-08-31 15:33”.
# 2、Add 2 data to the table: if the add_time field is timestamp
insert into class(name) values (“First grade “);
insert into class(name) values (“Two grade “);
# Create a student table and add fields:
CREATE table student(
id int(10) not null primary key auto_increment unique, # uniqueUniqueness, not repeatable
name varchar(30) not null default “noname ” comment “The name “,
age int(10) not null default 0 comment “Age “,
birthday datetime not null comment “”Birthday,”
class_id int(10) ,
foreign key(class_id) references class(id)
);
# Add 4 data to the table:
insert into student(name,age,birthday,class_id) values (“Lu Yumeng, “23,” 1996-07-11 “,” 1 “;
insert into student(name,age,birthday,class_id) values (“Wang Zhimin, “23,” 1996-08-12 “,” 1 “;
insert into student(name,age,birthday,class_id) values (“Zhao Guangzheng, “23,” 1996-09-13 “,” 2 “;
insert into student(name,age,birthday,class_id) values (“Furukawa, “23,” 1996-10-14 “,” 2 “;
# Create the score table and add fields: decimal (5,2) 5 is the valid length, and 2 is the 2 after the decimal point.
create table course(
id int (10) not null primary key auto_increment,
name varchar(30) not null ,
score DECIMAL(5,2) not null,
class_id int(10) not null,
stu_id int (10) not null,
foreign key(class_id) references class(id),
foreign key (stu_id) references student(id)
);
# Add 5 data to the table:
insert into course(name,score,class_id,stu_id) values (“Mathematics, “90.6,1,1”;
insert into course(name,score,class_id,stu_id) values (“Chinese, “135.44”, “1,5”;
insert into course(name,score,class_id,stu_id) values (“English “,” 100 “,” 2,3 “;
insert into course(name,score,class_id,stu_id) values (“Political “,” 98 “,” 1,2 “;
insert into course(name,score,class_id,stu_id) values (“History “,” 89.92 “,” 2,4 “;
As shown in the figure,
# Find all the data in three tables and tables:
SELECT * FROM student;
SELECT * FROM class;
SELECT * FROM course;
# Query the name name of id=1 in the student table.
select name from student where id=1;
# Query the data of name= “Wang Zhimin” in student table
select * from student where name = “Wang Zhimin “;
# Query data in student table with age greater than 15
select id,name from student where age>”15″;
andAnd;
# Query data in student table with age greater than 15 and less than 30.
select * from student where age>”15″ and age<“30”;
or Or;
# Query data in student table with age greater than 15 or less than 30.
select * from student where age>”15″ and age<“30”;
between Between;
# Query data in the student table with ages between 15 and 30.
select * from student where age > “15” between age > “30”;
in Contain
# Query data in specified collection
select * from student where id in (1,3,5);
sort
idAscending order: select * from student order by ID ASC;
idDescending order: select * from student order by ID DESC;
id Maximum value: select max (ID) from student;
The minimum value of birthday is select min (birth) from student;
idAverage value: select AVG (ID) as’ average from student;
Statistical data: select count (*) from student
Name statistics: select count (name) from student; (if empty, no statistics)
idSum: select sum (ID) from student
Query for article J data after article I (excluding article i): select * from student limit 2,5; 5 data from Article 3 (3-8)
# Modify id=2’s age to 66.
update student set age=66 where id=2;
# Modify name and age of id=2
update student set name = “haha”, birth = “1999-01-01” where id=2;
# Modifying the table name
alter table student rename to stu;
# Modify the name of a field.
alter table stu change name names varchar(30);（Modify field name named names)
# Modify default values for tables
alter table stu alter text set default ‘system’；
# Delete default values
alter table stu alter text drop default;
# Add column
alter table stu add (text char(10));
# drop primary key
alter table stu drop primary key;
# Delete all data in table but do not delete table
delete * from student;
# Delete a column
alter table student drop column birth;
# Add a column
alter table student add column haha varchar(30);
# Delete a line
delete from student where id=6;
# Add a line
insert into student(name,age,birth,class_id) values (“Jiang Yidi, “18,” 2000-11-11 “”
#—————————
#—-cmdCommand line to connect to MySql———
cd C:\mysql\bin
# Start MySQL server
net start mysql
# Close the MySQL server.
net stop mysql
# Enter the MySQL command line.
mysql -h localhost -u root -p
#—————————
#—-MySqlUser management —
#To change the password: first, under DOS, enter the bin directory of the MySQL installation path, then type the following command:
mysqladmin -uroot -p123 password 456;
#Adding users
#Format: grant permissions on database. * to username @ Login Host identified by ‘password’
/*
For example, add a user user1 password to password1 so that it can login on the machine and have permission to query, insert, modify, delete all databases. First, connect the root user to MySQL, then type the following command:
grant select,insert,update,delete on *.* to user1@localhost Identified by “password1”;
If you want the user to log on to MySQL on any machine, change localhost to “%”.
If you don’t want user1 to have a password, you can call another command to remove the password.
grant select,insert,update,delete on mydb.* to user1@localhost identified by “”;
*/
grant all privileges on wpj1105.* to sunxiao@localhost identified by ‘123’; #all privileges All permissions
#—————————-
#—–MySqlDatabase operation basis –
# Create database
create database namage default character set utf8 collate utf8_general_ci;
# If there is a deletion in the database
drop database if exists manage;
# Database entry
use manage;
# Delete database
drop manage;
# Look at the structure of tables
desc class;
# View table data
select * from class;
# Create class table and add fields:
create table class(
id int(10) not null auto_increment,
name varchar(30) not null default ” noname”,
add_time datetime no t null,
primary key(id)
)
ENGINE = INNODB charset=utf8；
# 1、Add 2 data to the table: if the add_time field is datetime
insert into class(name,add_time) values (“First grade, “2018-08-31 15:33”.
insert into class(name,add_time) values (“Grade two, “2018-08-31 15:33”.
# 2、Add 2 data to the table: if the add_time field is timestamp
insert into class(name) values (“First grade “);
insert into class(name) values (“Two grade “);
# Create a student table and add fields:
CREATE table student(
id int(10) not null primary key auto_increment unique, # uniqueUniqueness, not repeatable
name varchar(30) not null default “noname ” comment “The name “,
age int(10) not null default 0 comment “Age “,
birthday datetime not null comment “”Birthday,”
class_id int(10) ,
foreign key(class_id) references class(id)
);
# Add 4 data to the table:
insert into student(name,age,birthday,class_id) values (“Lu Yumeng, “23,” 1996-07-11 “,” 1 “;
insert into student(name,age,birthday,class_id) values (“Wang Zhimin, “23,” 1996-08-12 “,” 1 “;
insert into student(name,age,birthday,class_id) values (“Zhao Guangzheng, “23,” 1996-09-13 “,” 2 “;
insert into student(name,age,birthday,class_id) values (“Furukawa, “23,” 1996-10-14 “,” 2 “;
# Create the score table and add fields: decimal (5,2) 5 is the valid length, and 2 is the 2 after the decimal point.
create table course(
id int (10) not null primary key auto_increment,
name varchar(30) not null ,
score DECIMAL(5,2) not null,
class_id int(10) not null,
stu_id int (10) not null,
foreign key(class_id) references class(id),
foreign key (stu_id) references student(id)
);
# Add 5 data to the table:
insert into course(name,score,class_id,stu_id) values (“Mathematics, “90.6,1,1”;
insert into course(name,score,class_id,stu_id) values (“Chinese, “135.44”, “1,5”;
insert into course(name,score,class_id,stu_id) values (“English “,” 100 “,” 2,3 “;
insert into course(name,score,class_id,stu_id) values (“Political “,” 98 “,” 1,2 “;
insert into course(name,score,class_id,stu_id) values (“History “,” 89.92 “,” 2,4 “;
As shown in the figure,
classTable:
studentTable:
courseTable:
# Find all the data in three tables and tables:
SELECT * FROM student;
SELECT * FROM class;
SELECT * FROM course;
# Query the name name of id=1 in the student table.
select name from student where id=1;
# Query the data of name= “Wang Zhimin” in student table
select * from student where name = “Wang Zhimin “;
# Query data in student table with age greater than 15
select id,name from student where age>”15″;
andAnd;
# Query data in student table with age greater than 15 and less than 30.
select * from student where age>”15″ and age<“30”;
or Or;
# Query data in student table with age greater than 15 or less than 30.
select * from student where age>”15″ and age<“30”;
between Between;
# Query data in the student table with ages between 15 and 30.
select * from student where age > “15” between age > “30”;
in Contain
# Query data in specified collection
select * from student where id in (1,3,5);
sort
idAscending order: select * from student order by ID ASC;
idDescending order: select * from student order by ID DESC;
id Maximum value: select max (ID) from student;
The minimum value of birthday is select min (birth) from student;
idAverage value: select AVG (ID) as’ average from student;
Statistical data: select count (*) from student
Name statistics: select count (name) from student; (if empty, no statistics)
idSum: select sum (ID) from student
Enquiry of J data after clause I (excluding article I)
select * from student limit 2,5; #5 data from third (3-8)
# Modify id=2’s age to 66.
update student set age=66 where id=2;
# Modify name and age of id=2
update student set name = “haha”, birth = “1999-01-01” where id=2;
# Modifying the table name
alter table student rename to stu;
# Modify the name of a field.
alter table stu change name names varchar(30);（Modify the field name named names);
# Modify default values for tables
alter table stu alter text set default ‘system’；
# Delete default values
alter table stu alter text drop default;
# Add column
alter table stu add (text char(10));
# drop primary key
alter table stu drop primary key;
# Delete all data in table but do not delete table
delete * from student;
# Delete a column
alter table student drop column birth;
# Add a column
alter table student add column haha varchar(30);
# Delete a line
delete from student where id=6; If associated, the associated data needs to be deleted.
# Add a line
insert into student(name,age,birth,class_id) values (“Jiang Yidi, “18,” 2000-11-11 “”
#—————————
#—-Database backup — —
# Export data
# The default location to enter a database storage location is C: Program Files MySQL MySQL Server 5.5 bin Location Change to find itself
cd C:\Users\memgmeng\Documents\Navicat\MySQL\servers\mysql1602A
# Enter mysqldump-u root-p Manager & gt; D: manager.
# Import data
# Enter MySQL -u root -p and password into MySQL
# Create an empty database, such as mana
# Enter database: use mana;
# Import files: source D:/manager.sql; D:/ and imported files can not be spaces.
