set password for 'user name'@'IP address' = Password('New password');
Note: the data related to user permissions are saved in the user table of mysql database, so you can also operate them directly (not recommended)
I'm tired after watching for so long. Take a break for 10 seconds and continue to look down
4, Authority management
mysql has the following restrictions on permissions:
all privileges: except grant All permissions outside select: Check permissions only select,insert: Query and insert permissions ... usage: No access alter: use alter table alter routine: use alter procedure and drop procedure create: use create table create routine: use create procedure create temporary tables: use create temporary tables create user: use create user,drop user,rename user and revoke all privileges create view: use create view delete: use delete drop: use drop table execute: use call And stored procedures file: use select into outfile and load data infile grant option: use grant and revoke index: use index insert: use insert lock tables: use lock table process: use show full processlist select: use select show databases: use show databases show view: use show view update: use update reload: use flush shutdown: use mysqladmin shutdown(close MySQL) super: use change master,kill,logs,purge,master and set global. Also allow mysqladmin Debug login replication client: Access to server location replication slave: Used by replication slaves
The database and other internal permissions are as follows:
Database name.* All in the database Database name.surface Specify a table in the database Database name.stored procedure Specifies the stored procedure in the database *.* All databases
The permissions for users and IP are as follows:
user name@IP address Users can only change IP Can't access until user name@192.168.1.% Users can only change IP Can only be accessed under segment(wildcard%Represents any) user name@% Users can choose any IP Next visit(default IP Address is%)
1. View permissions:
show grants for 'user'@'IP address'
2. Authorization
grant jurisdiction on database.surface to 'user'@'IP address'
3. Cancel authorization
revoke jurisdiction on database.surface from 'user name'@'IP address'
Examples of authorization are as follows:
grant all privileges on db1.tb1 TO 'user name'@'IP' grant select on db1.* TO 'user name'@'IP' grant select,insert on *.* TO 'user name'@'IP' revoke select on db1.tb1 from 'user name'@'IP'
MySQL table operation
1, View table
show tables; # View all database tables select * from Table name; # View all contents of the table
2, Create table
create table Table name(
Whether the column name type can be empty,
Can column name type be empty
)ENGINE=InnoDB DEFAULT CHARSET=utf8
Give an example to explain in detail
CREATE TABLE `tab1` ( `nid` int(11) NOT NULL auto_increment, `name` varchar(255) DEFAULT zhangyanlin, `email` varchar(255), PRIMARY KEY (`nid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Note:
-
Default value. You can specify the default value when creating a column. If it is not actively set when inserting data, the default value will be automatically added
-
Auto increment: if Auto increment column is set for a column, it does not need to be set when inserting data. It will be auto increment by default (there can only be one auto increment column in the table). Note: 1. For auto increment column, it must be index (including primary key) 2. For auto increment, step size and starting value can be set
-
Primary key, a special unique index, does not allow null values. If the primary key uses a single column, its value must be unique. If it is multiple columns, its combination must be unique.
3, Delete table
drop table Table name
4, Empty table contents
delete from Table name truncate table Table name
5, Modify table
Add column:
alter table Table name add Column name type
Delete column:
alter table Table name drop column Listing
Modify column:
alter table Table name modify column Column name type; -- type
alter table Table name change Original column name new column name type; -- Column name, type
Add primary key:
alter table Table name add primary key(Listing);
Delete primary key:
alter table Table name drop primary key;
alter table Table name modify Listing int, drop primary key;
Add foreign key:
alter table From table add constraint Foreign key name (e.g.: FK_From table_(main table) foreign key From table(Foreign key field) references Main table(Primary key field);
To delete a foreign key:
alter table Table name drop foreign key Foreign key name
Modify default values:
ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
Delete default:
ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
Do these operations seem troublesome and waste time? Don't panic! There is a special software that can provide these functions. The operation is very simple. This software is called Navicat Premium. You can download it on the Internet and practice your hand. However, the following table content operation is about to be discussed. It is recommended to write commands yourself
6, Basic data type
The data types of MySQL are roughly divided into: value, time and string
bit[(M)]
Binary bits (101001), m Indicates the length of the binary bit (1)-64)，default m＝1
tinyint[(m)] [unsigned] [zerofill]
Small integer, the data type is used to save integer value ranges of some ranges:
Signed:
-128 ～ 127.
Unsigned:
0 ～ 255
special: MySQL No Boolean value in, use tinyint(1)Construction.
int[(m)][unsigned][zerofill]
Integer, data type, integer numeric range used to hold some ranges:
Signed:
-2147483648 ～ 2147483647
Unsigned:
0 ～ 4294967295
Special: in integer type m For display only, unlimited storage range. For example: int(5),When inserting data 2, select When, the data is displayed as 00002
bigint[(m)][unsigned][zerofill]
Large integer, the data type is used to save the integer value range of some ranges:
Signed:
-9223372036854775808 ～ 9223372036854775807
Unsigned:
0 ～ 18446744073709551615
decimal[(m[,d])] [unsigned] [zerofill]
Exact decimal values, m Is the total number of numbers (minus sign does not count), d Is the number after the decimal point. m 65 max, d The maximum value is 30.
Special: this type is required for accurate numerical calculation
decaimal The reason for storing exact values is that they are stored internally as strings.
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
Single precision floating-point number (non exact small value), m Is the total number of numbers, d Is the number after the decimal point.
Unsigned:
-3.402823466E+38 to -1.175494351E-38,
0
1.175494351E-38 to 3.402823466E+38
Signed:
0
1.175494351E-38 to 3.402823466E+38
**** The larger the value, the less accurate it is ****
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
Double precision floating-point number (non exact small value), m Is the total number of numbers, d Is the number after the decimal point.
Unsigned:
-1.7976931348623157E+308 to -2.2250738585072014E-308
0
2.2250738585072014E-308 to 1.7976931348623157E+308
Signed:
0
2.2250738585072014E-308 to 1.7976931348623157E+308
**** The larger the value, the less accurate it is ****
char (m)
char The data type is used to represent a fixed length string and can contain up to 255 characters. among m Represents the length of the string.
PS: Even if the data is less than m Length will also occupy m length
varchar(m)
varchars The data type is used for variable length strings and can contain up to 255 characters. among m Represents the maximum length of the string allowed to be saved by the data type. As long as the length is less than the maximum value, the string can be saved in the data type.
Note: Although varchar It is flexible to use, but from the perspective of the performance of the whole system, char Data types are processed faster, sometimes even faster than varchar 50% of processing speed%. Therefore, when designing the database, users should comprehensively consider various factors in order to achieve the best balance
text
text The data type is used to store large variable length strings, which can be grouped as many as 65535 (2**16 − 1)Characters.
mediumtext
A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.
longtext
A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters.
enum
Enumeration type,
An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)
Example:
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');
set
Collection type
A SET column can have a maximum of 64 distinct members.
Example:
CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
DATE
YYYY-MM-DD(1000-01-01/9999-12-31)
TIME
HH:MM:SS('-838:59:59'/'838:59:59')
YEAR
YYYY(1901/2155)
DATETIME
YYYY-MM-DD HH:MM:SS(1000-01-01 00:00:00/9999-12-31 23:59:59 Y)
TIMESTAMP
YYYYMMDD HHMMSS(1970-01-01 00:00:00/2037 (sometime in)
MySQL table content operation
The operation of table content is nothing more than adding, deleting, changing and checking. Of course, the most used is checking, and checking this piece is the most difficult. Of course, for the great God, it is so easy. It is still very difficult for me to use it flexibly. Let's operate it one by one
1, Increase
insert into surface (Listing,Listing...) values (value,value,...)
insert into surface (Listing,Listing...) values (value,value,...),(value,value,value...)
insert into surface (Listing,Listing...) select (Listing,Listing...) from surface
Example:
insert into tab1(name,email) values('zhangyanlin','zhangyanlin8851@163.com')
2, Delete
delete from surface # Delete all data in the table delete from surface where id＝1 and name＝'zhangyanlin' # Delete the row with ID =1 and name='zhangyanlin '
3, Change
update surface set name ＝ 'zhangyanlin' where id>1
4, Check
select * from surface select * from surface where id > 1 select nid,name,gender as gg from surface where id > 1
There are too many conditions to check. I'll list them. As for the combination, it depends on your understanding
a. Conditional judgment where
select * from surface where id > 1 and name != 'aylin' and num = 12; select * from surface where id between 5 and 16; select * from surface where id in (11,22,33) select * from surface where id not in (11,22,33) select * from surface where id in (select nid from surface)
b. Wildcard like
select * from surface where name like 'zhang%' # All (multiple strings) beginning with zhang select * from surface where name like 'zhang_' # All (one character) beginning with zhang
c. Limit limit
select * from surface limit 5; - First 5 lines ### last **Why don't I fully advocate self-study? ①**Daniel on the platform basically has many years of work experience. Have you ever thought about what the industry threshold was before and what the industry threshold is now? In the past, enterprises didn't have such high requirements for programmers' ability. Even more than a decade ago, you only had to write a“ Hello World"，You can get started in this industry, so you can get started before. ②There are also some excellent young cattle. They may also become talents by themselves, but they must have excellent learning ability, excellent self-management ability (time management, meditation, persistence, etc.) and be good at finding and summarizing problems. If you think your goal is very clear, you can achieve the second goal②According to the current market, you are really suitable for self-study. In addition, for most people, class registration must be the best way to grow rapidly. However, there is a problem. The quality of training institutions in the market is uneven. If you don't find a good training course, it will be a waste of energy, time and money. You need to choose by yourself. I personally suggest that online training is more cost-effective than offline training, and the price of offline training is basically 2 W Online education is now relatively mature. During the epidemic, students have basically experienced the online learning mode. Compared with offline, the advantages of online are mainly in the following aspects: ①Price: the online price is basically half of the offline price; ②Teacher: relatively speaking, the teachers of online education are stronger and richer than offline education, and the resources are better coordinated; ③Time: learning time is relatively more free. There is no need to study naked. It is suitable for learning and working while reducing the pressure of life; ④Course: in terms of course content, it is more in-depth than offline. **What technologies should be learned to meet the requirements of the enterprise? (summarized in the figure below)** **[Java Free collection method of full set of information: stamp here](https://codechina.csdn.net/m0_60958482/java-p7)**   Daniel basically has many years of work experience. Have you ever thought about what the industry threshold was before and what the industry threshold is now? In the past, enterprises didn't have such high requirements for programmers' ability. Even more than a decade ago, you only had to write a“ Hello World"，You can get started in this industry, so you can get started before. ②There are also some excellent young cattle. They may also become talents by themselves, but they must have excellent learning ability, excellent self-management ability (time management, meditation, persistence, etc.) and be good at finding and summarizing problems. If you think your goal is very clear, you can achieve the second goal②According to the current market, you are really suitable for self-study. In addition, for most people, class registration must be the best way to grow rapidly. However, there is a problem. The quality of training institutions in the market is uneven. If you don't find a good training course, it will be a waste of energy, time and money. You need to choose by yourself. I personally suggest that online training is more cost-effective than offline training, and the price of offline training is basically 2 W Online education is now relatively mature. During the epidemic, students have basically experienced the online learning mode. Compared with offline, the advantages of online are mainly in the following aspects: ①Price: the online price is basically half of the offline price; ②Teacher: relatively speaking, the teachers of online education are stronger and richer than offline education, and the resources are better coordinated; ③Time: learning time is relatively more free. There is no need to study naked. It is suitable for learning and working while reducing the pressure of life; ④Course: in terms of course content, it is more in-depth than offline. **What technologies should be learned to meet the requirements of the enterprise? (summarized in the figure below)** **[Java Free collection method of full set of information: stamp here](https://codechina.csdn.net/m0_60958482/java-p7)** [External chain picture transfer...(img-oRBZIsGy-1629248494998)] [External chain picture transfer...(img-emndWzwk-1629248495000)]