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.

Recommended Today

Introduction and overview of NoSQL

1.1 INTRODUCTION1.1.1 1 under the background of Internet era, why do you use nosql1 single machine MySQL In the 1990s, the number of visitors to a website was generally small, and it could be easily handled with a single database. At that time, there were more static web pages and less dynamic interactive websites. Under […]