Mysql database basic operation command

Time:2019-10-19

Next: introduction to common commands for getting started with MySQL

Today, I will introduce some basic commands for the operation of guantian MySQL database.

Users and permissions

Create user

mysql>create user test identified by '[email protected]#';

Change Password

5.5 and previous commands

mysql>set password for test=passowrd('[email protected]#3');  

5.6 and above

mysql>update mysql.user set authentication_string=password('A1b2c3#[email protected]') where user='test';

Create users and authorize

mysql>grant select,insert,update on student.* to [email protected] identified by 'A1b2c3#[email protected]';

View authorization

mysql> show grants for [email protected];

Mysql database basic operation command

Remove authority

mysql> revoke insert,update on student.* from [email protected];

Building database and tables

Create Library

mysql> create database student;

mysql> show databases;

Mysql database basic operation command

Create table

mysql> use student;

mysql> create table T1 (name varchar(10) not null,sex varchar(10) not null);

Create a new table from an existing table

mysql> create table T2 as select * from T1;

insert data

mysql> insert into T1 values('zhang','man');

Query OK, 1 row affected (0.03 sec)

mysql> insert into T1 values('li','man');

Query OK, 1 row affected (0.03 sec)

mysql> insert into T1 values('wang','man');

Query OK, 1 row affected (0.02 sec)

mysql> insert into T1 values('zhao','women');

Query OK, 1 row affected (0.05 sec)

#Note that if there are more than two columns, you need to specify the following column field names

mysql> insert into T1(name,sex) values('gege','man');

Query data

Query data

mysql> select user,host from mysql.user;

#View users

mysql> select * from T1 where name like '%an%';

mysql> select * from T1 where age like '2%';

Matching query

Mysql database basic operation command

mysql> select * from T1 order by name,age;

Query ranking

Mysql database basic operation command

mysql> select count(*) as toaolcount from T1;

mysql> select sum(age) as sumvalue from T1;

mysql> select avg(age) as avgvalue from T1;

mysql> select max(age) from T1;

Query value

Mysql database basic operation command

mysql> select score from T1 where score <91;

mysql> select score from T1 where score >=91;

mysql> select * from T1 where score in (96,100);

Conditional query

Mysql database basic operation command

mysql> select * from T2;

mysql> select * from T1;

Mysql database basic operation command

Additions and deletion updates

Add and delete columns

mysql> alter table T1 add age int(4) not null;

mysql> alter table T1 drop age

Update the data in the table

mysql> update T1 set age=25 where name='zhang';

mysql> update T1 set age=23 where name='li';

Delete data

mysql> delete from T1 where age='22';

Indexing and deletion

mysql> create index indexT1 on T1(name(10));

mysql> drop index indexT1 on T1;

Primary key and view

Create primary key

mysql> alter table T1 add primary key(name);

mysql> desc T1;

Mysql database basic operation command

Create and delete views

mysql> create view t1view as select name from T1;

mysql> select * from t1view;

Mysql database basic operation command

mysql> drop view t1view;

mysql> select * from t1view;

ERROR 1146 (42S02): Table 'student.t1view' doesn't exist

#Prompt that this view does not exist

Click attentionThe road of technology for migrant workersReply key words of wechat public account dialog box: 1024 you can get the latest technical dry goods: including system operation and maintenance, database, redis, mogodb, e-book, java basic course, Java practical project, architect comprehensive course, architect practical project, big data, docker container, elk stack, machine learning, bat interview intensive video, etc.