Shulou(Shulou.com)05/31 Report--

This article introduces what are the basic operation commands in the MySQL database, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Users and permissions

Create a user

Mysql > create user test identified by 'BaC321@#'

Modify the password

Version 5.5 and previous commands

Mysql > set password for test=passowrd ('! 1A / 2 / 3')

5.6 and above orders

Mysql > update mysql.user set authentication_string=password ('A1b2c3benchmark') where user='test'

Create users and authorize

Mysql > grant select,insert,update on student.* to test@localhost identified by 'A1b2c3 roommates

View authorization

Mysql > show grants for test@localhost

Remove permission

Mysql > revoke insert,update on student.* from test@localhost

Building database and table

Create a library

Mysql > create database student; mysql > show databases

Create a tabl

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 column field name as follows: mysql > insert into T1 (name,sex) values ('gege','man')

Query data

Query data

Mysql > select user,host from mysql.user; # View user mysql > select * from T1 where name like'% an%'; mysql > select * from T1 where age like'2%'

Matching query

Mysql > select * from T1 order by name,age

Query sorting

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 > select score from T1 where score select score from T1 where score > = 91; mysql > select * from T1 where score in (96100)

Conditional query

Mysql > select * from T2; mysql > select * from T1

Add, delete, update

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 a primary key

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

Create and delete views

Mysql > create view t1view as select name from T1; mysql > select * from t1view

Mysql > drop view T1 View; mysql > select * from T1 View; ERROR 1146 (42S02): Table 'student.t1view' doesn't exist # indicates that this view does not exist about which basic operation commands are shared in the MySQL database. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.