MySQL的管理员用户名为root

MySQL的管理员用户名为root，密码默认为空修改root密码MySQL配置好后，启动成功，默认密码是空，但是为了平安，设置密码〔MySQL有一个默认用户名为root，密码自己设定：假设设为root〕。

1〕登录MySQL root用户：翻开命令行，执行：
Mysql代码
1mysql -uroot -p
2〕修改root密码：
Mysql代码
2mysql> update er set password="root" where User="root";
3mysql> flush privileges;
修改该修改密码的语句：update er set password="root" where User="root"; 为：update er set password=password("root") where User="root";详细说明：见最底下的补充说明。

以后再进入MySQL，那么为：
Mysql代码
4mysql -uroot -proot
7、常用命令：
Mysql代码
5show databases;--显示数据库
6use databasename; --用数据库
7show tables;--显示表
8create table tablename(field-name-1 fieldtype-1 modifiers,field-name-2
fieldtype-2 modifiers,....);--创立表
9alter table tablename add new-fielname new fieldtype--为表参加新列
10insert into
tablename(fieldname-1,fieldname-2,fieldname-n)valuse(value-1,value-2,value-n)--增
11delete from tablename where fieldname=value--删
12update tablename set fieldname=new-value where id=1--改
13select * from tablename--查
14desc tablename--表定义描述
15show create table tablename--可以查看引擎
16alter table tablename engine=InnoDB--修改引擎
17create table tablename(id int(11),name varchar(10) )type=INNODB--建表是设置引擎
8、例如：〔1〕登录MySQL效劳器后，查看当前时间，登录的用户以及数据库的版本
Mysql代码
18mysql> select now(),user(),version();
19+---------------------+----------------+-----------+ 20| now() | user() | version() | 21+---------------------+----------------+-----------+ 22| 2021-02-2620:29:51 | rootlocalhost | 5.5.20 | 23+---------------------+----------------+-----------+ 241 row in set (0.00 sec)
〔2〕显示数据库列表
Mysql代码
25mysql> show databases;
26+--------------------+
27| Database |
28+--------------------+
29| information_schema |
30| mysql |
31| performance_schema |
32| test |
33+--------------------+
344 rows in set (0.03 sec)
〔3〕新增数据库并查看
Mysql代码
35mysql> create database test_db;
36Query OK, 1 row affected (0.00 sec)
37
38mysql> show databases;
39+--------------------+
40| Database |
41+--------------------+
42| information_schema |
43| mysql |
44| performance_schema |
45| test |
46| test_db |
47+--------------------+
485 rows in set (0.00 sec)
〔4〕选择数据库
Mysql代码
49mysql> use test_db;
50Database changed
查看已选择的数据库：
Mysql代码
51mysql> select database();
52+------------+
53| database() |
54+------------+
55| test_db |
56+------------+
571 row in set (0.00 sec)
〔5〕显示当前数据库的所有数据表
Mysql代码
58mysql> show tables;
59Empty set (0.00 sec)
〔6〕新建数据表并查看
Mysql代码
60mysql> create table person(
61 -> id int,
62 -> name varchar(20),
63 -> sex char(1),
64 -> birth date
65 -> );
66Query OK, 0 rows affected (0.09 sec)
Mysql代码
67mysql> show tables;
68+-------------------+
69| Tables_in_test_db |
70+-------------------+
71| person |
72+-------------------+
731 row in set (0.00 sec)
〔7〕获取表构造
Mysql代码
74mysql> desc person;
75+-------+-------------+------+-----+---------+-------+ 76| Field | Type | Null | Key | Default | Extra | 77+-------+-------------+------+-----+---------+-------+ 78| id | int(11) | YES | | NULL | | 79| name | varchar(20) | YES | | NULL | | 80| sex | char(1) | YES | | NULL | | 81| birth | date | YES | | NULL | | 82+-------+-------------+------+-----+---------+-------+ 834 rows in set (0.01 sec)
或者
Mysql代码
84mysql> describe person;
85+-------+-------------+------+-----+---------+-------+ 86| Field | Type | Null | Key | Default | Extra | 87+-------+-------------+------+-----+---------+-------+ 88| id | int(11) | YES | | NULL | | 89| name | varchar(20) | YES | | NULL | | 90| sex | char(1) | YES | | NULL | | 91| birth | date | YES | | NULL | | 92+-------+-------------+------+-----+---------+-------+ 934 rows in set (0.01 sec)
〔8〕查询表中的数据
Mysql代码
94mysql> select * from person;
95Empty set (0.00 sec)
〔9〕插入数据
Mysql代码
96mysql> insert into person(id,name,sex,birth)
97 -> values(1,'zhangsan','1','1990-01-08');
98Query OK, 1 row affected (0.04 sec)
查询表中的数据：
Mysql代码
99mysql> select * from person;
100+------+----------+------+------------+
101| id | name | sex | birth |
102+------+----------+------+------------+
103| 1 | zhangsan | 1 | 1990-01-08 |
104+------+----------+------+------------+
1051 row in set (0.00 sec)
〔10〕修改字段的类型
Mysql代码
106mysql> alter table person modify sex char(8);
107Query OK, 1 row affected (0.17 sec)
108Records: 1 Duplicates: 0 Warnings: 0
查看字段描述：
Mysql代码
109mysql> desc person;
110+-------+-------------+------+-----+---------+-------+ 111| Field | Type | Null | Key | Default | Extra | 112+-------+-------------+------+-----+---------+-------+ 113| id | int(11) | YES | | NULL | |
114| name | varchar(20) | YES | | NULL | |
115| sex | char(8) | YES | | NULL | |
116| birth | date | YES | | NULL | |
117+-------+-------------+------+-----+---------+-------+ 1184 rows in set (0.01 sec)
〔11〕新增一个字段
Mysql代码
119mysql> alter table person add(address varchar(50));
120Query OK, 1 row affected (0.27 sec)
121Records: 1 Duplicates: 0 Warnings: 0
查看字段描述：
Mysql代码
122mysql> desc person;
123+---------+-------------+------+-----+---------+-------+ 124| Field | Type | Null | Key | Default | Extra | 125+---------+-------------+------+-----+---------+-------+ 126| id | int(11) | YES | | NULL | | 127| name | varchar(20) | YES | | NULL | | 128| sex | char(8) | YES | | NULL | | 129| birth | date | YES | | NULL | |
130| address | varchar(50) | YES | | NULL | |
131+---------+-------------+------+-----+---------+-------+
1325 rows in set (0.01 sec)
〔12〕更新字段内容查看修改前表的内容：
Mysql代码
133mysql> select * from person;
134+------+----------+------+------------+---------+
135| id | name | sex | birth | address |
136+------+----------+------+------------+---------+
137| 1 | zhangsan | 1 | 1990-01-08 | NULL |
138+------+----------+------+------------+---------+
1391 row in set (0.00 sec)
修改：
Mysql代码
140mysql> update person set name='lisi' where id=1;
141Query OK, 1 row affected (0.04 sec)
142Rows matched: 1 Changed: 1 Warnings: 0
143
144mysql> select * from person;
145+------+------+------+------------+---------+
146| id | name | sex | birth | address |
147+------+------+------+------------+---------+
148| 1 | lisi | 1 | 1990-01-08 | NULL |
149+------+------+------+------------+---------+
1501 row in set (0.00 sec)
151
152mysql> update person set sex='man',address='China' where id=1;
153Query OK, 1 row affected (0.04 sec)
154Rows matched: 1 Changed: 1 Warnings: 0
155
156mysql> select * from person;
157+------+------+------+------------+---------+
158| id | name | sex | birth | address |
159+------+------+------+------------+---------+
160| 1 | lisi | man | 1990-01-08 | China |
161+------+------+------+------------+---------+
1621 row in set (0.00 sec)
为了方便下面测试删除数据，在向person表中插入2条数据：
Mysql代码
163mysql> insert into person(id,name,sex,birth,address) 164 -> values(2,'wangwu','man','1990-01-10','China');
165Query OK, 1 row affected (0.02 sec)
166
167mysql> insert into person(id,name,sex,birth,address) 168 -> values(3,'zhangsan','man','1990-01-10','China');
169Query OK, 1 row affected (0.04 sec)
170
171mysql> select * from person;
172+------+----------+------+------------+---------+
173| id | name | sex | birth | address |
174+------+----------+------+------------+---------+
175| 1 | lisi | man | 1990-01-08 | China |
176| 2 | wangwu | man | 1990-01-10 | China |
177| 3 | zhangsan | man | 1990-01-10 | China |
178+------+----------+------+------------+---------+
1793 rows in set (0.00 sec)
〔13〕删除表中的数据删除表中指定的数据：
Mysql代码
180mysql> delete from person where id=2;
181Query OK, 1 row affected (0.02 sec)
182
183mysql> select * from person;
184+------+----------+------+------------+---------+
185| id | name | sex | birth | address |
186+------+----------+------+------------+---------+
187| 1 | lisi | man | 1990-01-08 | China |
188| 3 | zhangsan | man | 1990-01-10 | China |
189+------+----------+------+------------+---------+
1902 rows in set (0.00 sec)
删除表中全部的数据：
Mysql代码
191mysql> delete from person;
192Query OK, 2 rows affected (0.04 sec)
193
194mysql> select * from person;
195Empty set (0.00 sec)
〔14〕重命名表查看重命名前的表名：
Mysql代码
196mysql> show tables;
197+-------------------+
198| Tables_in_test_db |
199+-------------------+
200| person |
201+-------------------+
2021 row in set (0.00 sec)
重命名：
Mysql代码
203mysql> alter table person rename person_test;
204Query OK, 0 rows affected (0.04 sec)
205
206mysql> show tables;
207+-------------------+
208| Tables_in_test_db |
209+-------------------+
210| person_test |
211+-------------------+
2121 row in set (0.00 sec)
〔15〕新增主键
Mysql代码
213mysql> alter table person_test add primary key(id);
214Query OK, 0 rows affected (0.11 sec)
215Records: 0 Duplicates: 0 Warnings: 0
216
217mysql> desc person_test;
218+---------+-------------+------+-----+---------+-------+ 219| Field | Type | Null | Key | Default | Extra | 220+---------+-------------+------+-----+---------+-------+ 221| id | int(11) | NO | PRI | 0 | | 222| name | varchar(20) | YES | | NULL | | 223| sex | char(8) | YES | | NULL | | 224| birth | date | YES | | NULL | | 225| address | varchar(50) | YES | | NULL | | 226+---------+-------------+------+-----+---------+-------+ 2275 rows in set (0.00 sec)
删除主键：
Mysql代码
228mysql> alter table person_test drop primary key;
229Query OK, 0 rows affected (0.18 sec)
230Records: 0 Duplicates: 0 Warnings: 0
231
232mysql> desc person_test;
233+---------+-------------+------+-----+---------+-------+ 234| Field | Type | Null | Key | Default | Extra | 235+---------+-------------+------+-----+---------+-------+ 236| id | int(11) | NO | | 0 | |
237| name | varchar(20) | YES | | NULL | | 238| sex | char(8) | YES | | NULL | | 239| birth | date | YES | | NULL | | 240| address | varchar(50) | YES | | NULL | | 241+---------+-------------+------+-----+---------+-------+ 2425 rows in set (0.01 sec)
〔16〕删除表
Mysql代码
243mysql> drop table person_test;
244Query OK, 0 rows affected (0.04 sec)
245
246mysql> show tables;
247Empty set (0.00 sec)
〔17〕删除数据库
Mysql代码
248mysql> show databases;
249+--------------------+
250| Database |
251+--------------------+
252| information_schema |
253| mysql |
254| performance_schema |
255| test |
256| test_db |
257+--------------------+
2585 rows in set (0.00 sec)
259
260mysql> drop database test_db;
261Query OK, 0 rows affected (0.11 sec)
262
263mysql> show databases;
264+--------------------+
265| Database |
266+--------------------+
267| information_schema |
268| mysql |
269| performance_schema |
270| test |
271+--------------------+
2724 rows in set (0.00 sec)
〔18〕查看建表语句
Mysql代码
273mysql> show create table table_name;
补充说明：update er set password="root" where User="root";修改的不是密码，如果按照这个方式修改了，重新登录时将会报错：
Mysql代码
274mysql> update er set password="root" where User="root";
275Query OK, 3 rows affected (0.00 sec)
276Rows matched: 3 Changed: 3 Warnings: 0
277
278mysql> exit
279Bye
280
281C:\Users\liqiong>mysql -uroot -p
282Enter password: ****
283ERROR 1045 (28000): Access denied for user 'root''localhost' (using password: Y
284ES)
请按照以下方式重新修改密码，即可登录成功：
Mysql代码
285C:\Users\liqiong>mysql -uroot
286Wele to the MySQL monitor. mands end with ; or \g.
287Your MySQL connection id is 4
288Server version: 5.5.20 MySQL munity Server (GPL)
289
290Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved.
291
292Oracle is a registered trademark of Oracle Corporation and/or its
-
-
293affiliates. Other names may be trademarks of their respective
294owners.
295
296Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 297
298mysql> update er set password=password("root") where User="root"; 299Query OK, 3 rows affected (0.00 sec)
300Rows matched: 3 Changed: 3 Warnings: 0
301
302mysql> flush privileges;
303Query OK, 0 rows affected (0.00 sec)
304
305mysql> exit
306Bye
307
308C:\Users\liqiong>mysql -uroot -p
309Enter password: ****
310Wele to the MySQL monitor. mands end with ; or \g.
311Your MySQL connection id is 5
312Server version: 5.5.20 MySQL munity Server (GPL)
313
314Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved. 315
316Oracle is a registered trademark of Oracle Corporation and/or its
317affiliates. Other names may be trademarks of their respective
318owners.
319
320Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 321
322mysql>
. 优选-。