2022年MySQL管理员用户名为root.docx

精品学习资源MySQL的治理员用户名为root ，密码默认为空 修改 root 密码 MySQL配置好后，启动成功，默认密码是空，但是为了安全，设置密码（MySQL有一个默认用户名为root ，密码自己设定：假如设为root ）； 1 ）登录 MySQL root用户：打开命令行，执行：Mysql 代码mysql -uroot -p2）修改 root 密码：Mysql 代码mysql> update mysql.user set password="root"where User="root"；mysql> flush privileges；修改该修改密码的语句： update mysql.user set password="root" where User="root"； 为： update mysql.user set password=password"root"where User="root"；详细说明：见最底下的补充说明；以后再进入 MySQL ，就为：Mysql 代码mysql -uroot -proot7、常用命令：Mysql 代码show databases；- 显示数据库use databasename； -用数据库show tables；- 显示表create table tablenamefield-name-1 fieldtype-1 modifiers,field-name-2fieldtype-2 modifiers,.；- 创建表alter table tablename add new-fielname new fieldtype-为表加入新列insert into tablenamefieldname-1,fieldname-2 ,fieldname-nvalusevalue- 1,value-2,value-n-增delete from tablename where fieldname=value-删update tablename set fieldname=new-value where id=1- 改select * from tablename-查desc tablename-表定义描述show create table tablename-可以查看引擎alter table tablename engine=InnoDB-修改引擎create table tablenameid int11 ,name varchar10 type=INNODB-建表是设置引擎8、例如： （ 1）登录 MySQL 服务器后，查看当前时间，登录的用户以及数据库的版本欢迎下载精品学习资源Mysql 代码mysql> select now,user,version；+-+-+-+| now| user| version |+-+-+-+|2021 - 02 - 2620 : 29 : 51 | rootlocalhost |5.5 . 20|+-+-+-+1 row in set 0.00sec（2） ）显示数据库列表Mysql 代码mysql> show databases；+-+| Database|+-+| information_schema | mysql| performance_schema | test|+-+4 rows in set 0.03sec（3） ）新增数据库并查看Mysql 代码mysql> create database test_db；Query OK,1 row affected 0.00 sec mysql> show databases；+-+| Database|+-+| information_schema | mysql| performance_schema | test| test_db|+-+5 rows in set 0.00sec（4） ）选择数据库Mysql 代码欢迎下载精品学习资源mysql> use test_db；Database changed查看已选择的数据库：Mysql 代码mysql> select database；+-+| database |+-+| test_db|+-+1 row in set 0.00sec（5） ）显示当前数据库的全部数据表Mysql 代码mysql> show tables；Empty set 0.00sec（6） ）新建数据表并查看Mysql 代码mysql> create table person-> id int,-> name varchar20 ,-> sex char1,-> birth date-> ；Query OK,0 rows affected 0.09secMysql 代码mysql> show tables；+-+| Tables_in_test_db |+-+| person|+-+1 row in set 0.00sec（7） ）猎取表结构Mysql 代码欢迎下载精品学习资源mysql> desc person；+-+-+-+-+-+-+| Field | Type| Null | Key | Default | Extra |+-+-+-+-+-+-+| id| int11 | YES | NULL| name | varchar20 | YES | NULL| sex| char1| YES | NULL| birth | date| YES | NULL|+-+-+-+-+-+-+4 rows in set 0.01sec或者Mysql 代码mysql> describe person；+-+-+-+-+-+-+| Field | Type| Null | Key | Default | Extra |+-+-+-+-+-+-+| id| int11 | YES | NULL| name | varchar20 | YES | NULL| sex| char1| YES | NULL| birth | date| YES | NULL|+-+-+-+-+-+-+4 rows in set 0.01sec（8） ）查询表中的数据Mysql 代码mysql> select * from person；Empty set 0.00sec（9） ）插入数据Mysql 代码mysql> insert into personid,name,sex,birth-> values1, 'zhangsan', '1', '1990-01-08' ；Query OK,查询表中的数据：1 row affected 0.04 secMysql 代码mysql> select * from person；+-+-+-+-+| id| name| sex | birth|欢迎下载精品学习资源+-+-+-+-+|1 | zhangsan |1|1990 - 01 - 08 |+-+-+-+-+1 row in set 0.00sec（10 ）修改字段的类型Mysql 代码mysql> alter table person modify sex char8 ；Query OK,1 row affected 0.17 sec Records:1 Duplicates:0 Warnings:0查看字段描述：Mysql 代码mysql> desc person；+-+-+-+-+-+-+| Field | Type| Null | Key | Default | Extra |+-+-+-+-+-+-+| id| int11 | YES | NULL| name | varchar20 | YES | NULL| sex| char8| YES | NULL| birth | date| YES | NULL|+-+-+-+-+-+-+4 rows in set 0.01sec（11 ）新增一个字段Mysql 代码mysql> alter table person addaddress varchar50 ；Query OK,1 row affected 0.27 sec Records:1 Duplicates:0 Warnings:0查看字段描述：Mysql 代码mysql> desc person；+-+-+-+-+-+-+| Field| Type| Null | Key | Default | Extra |+-+-+-+-+-+-+| id| int11 | YES | NULL| name| varchar20 | YES| NULL| sex| char8| YES | NULL| birth| date| YES | NULL|欢迎下载精品学习资源| address | varchar50 | YES | NULL|+-+-+-+-+-+-+5 rows in set 0.01sec（12 ）更新字段内容 查看修改前表的内容：Mysql 代码欢迎下载精品学习资源修改：Mysql 代码mysql> select * from person；+-+-+-+-+-+| id| name| sex | birth| address |+-+-+-+-+-+|1 | zhangsan |1|1990 - 01 - 08 | NULL|+-+-+-+-+-+1 row in set 0.00sec欢迎下载精品学习资源mysql> update person set name='lisi'where id=1；Query OK,1 row affected 0.04 secRows matched:1 Changed:1 Warnings:0mysql> select * from person；+-+-+-+-+-+| id| name | sex | birth| address |+-+-+-+-+-+|1 | lisi |1|1990 - 01 - 08 | NULL|+-+-+-+-+-+1 row in set 0.00secmysql> update person set sex='man' ,address='China'where id=1；Query OK,1 row affected 0.04 secRows matched:1 Changed:1 Warnings:0mysql> select * from person；+-+-+-+-+-+| id| name | sex | birth| address |+-+-+-+-+-+|1 | lisi | man |1990 - 01 - 08 | China|+-+-+-+-+-+1 row in set 0.00sec为了便利下面测试删除数据，在向person 表中插入 2 条数据：Mysql 代码mysql> insert into personid,name,sex,birth,address-> values2, 'wangwu', 'man' , '1990-01-10', 'China' ；欢迎下载精品学习资源Query OK,1 row affected 0.02 sec mysql> insert into personid,name,sex,birth,address-> values3, 'zhangsan', 'man' , '1990-01-10', 'China' ；Query OK,1 row affected 0.04 secmysql> select * from person；+-+-+-+-+-+| id| name| sex | birth| address |+-+-+-+-+-+|1 | lisi| man |1990 - 01 - 08 | China|2 | wangwu| man|1990 - 01 - 10| China|3 | zhangsan | man|1990 - 01 - 10| China|+-+-+-+-+-+3 rows in set 0.00sec（13 ）删除表中的数据删除表中指定的数据：Mysql 代码mysql> delete from person where id=2 ；Query OK,1 row affected 0.02 sec mysql> select * from person；+-+-+-+-+-+| id| name| sex | birth| address |+-+-+-+-+-+|1 | lisi| man |1990 - 01 - 08 | China|3 | zhangsan | man |1990 - 01 - 10 | China|+-+-+-+-+-+2 rows in set 0.00sec删除表中全部的数据：Mysql 代码mysql> delete from person；Query OK,2 rows affected 0.04sec mysql> select * from person； Empty set 0.00sec（14 ）重命名表 查看重命名前的表名：Mysql 代码mysql> show tables；+-+| Tables_in_test_db |+-+| person|欢迎下载精品学习资源+-+1 row in set 0.00sec重命名：Mysql 代码mysql> alter table person rename person_test；Query OK,0 rows affected 0.04sec mysql> show tables；+-+| Tables_in_test_db |+-+| person_test|+-+1 row in set 0.00sec（15 ）新增主键Mysql 代码mysql> alter table person_test add primary keyid；Query OK,0 rows affected 0.11sec Records:0 Duplicates:0 Warnings:0mysql> desc person_test；+-+-+-+-+-+-+| Field| Type| Null | Key | Default | Extra |+-+-+-+-+-+-+| id| int11 | NO| PRI |0| name| varchar20 | YES | NULL| sex| char8| YES | NULL| birth| date| YES | NULL| address | varchar50 | YES | NULL|+-+-+-+-+-+-+5 rows in set 0.00sec删除主键：Mysql 代码mysql> alter table person_test drop primary key；Query OK,0 rows affected 0.18sec Records:0 Duplicates:0 Warnings:0mysql> desc person_test；+-+-+-+-+-+-+| Field| Type| Null | Key | Default | Extra |+-+-+-+-+-+-+欢迎下载精品学习资源| id| int11 | NO|0| name| varchar20 | YES | NULL| sex| char8| YES | NULL| birth| date| YES | NULL| address | varchar50 | YES | NULL|+-+-+-+-+-+-+5 rows in set 0.01sec（16 ）删除表Mysql 代码mysql> drop table person_test；Query OK,0 rows affected 0.04sec mysql> show tables；Empty set 0.00sec（17 ）删除数据库Mysql 代码mysql> show databases；+-+| Database|+-+| information_schema | mysql| performance_schema | test| test_db|+-+5 rows in set 0.00secmysql> drop database test_db； Query OK,0 rows affected 0.11sec mysql> show databases；+-+| Database|+-+| information_schema | mysql| performance_schema | test|+-+4 rows in set 0.00sec（18 ）查看建表语句欢迎下载精品学习资源Mysql 代码mysql> show create table table_name；补充说明： update mysql.user set password="root" where User="root"；修改的不是密码，假如依据这个方式修改了，重新登录时将会报错：Mysql 代码mysql> update mysql.user set password="root"where User="root"；Query OK,3 rows affected 0.00secRows matched:3 Changed:3 Warnings:0mysql> exit ByeC:Usersliqiong>mysql -uroot -pEnter password: *ERROR1045 28000 : Access denied for user'root''localhost'using password: YES请依据以下方式重新修改密码，即可登录成功：Mysql 代码C:Usersliqiong>mysql -urootWelcome to the MySQL monitor. Commands end with； or g. Your MySQL connection id is4Server version:5.5 . 20 MySQL Community Server GPLCopyright c2000 ,2021 , Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.Type'help；' or'h'for help. Type'c'to clear the current input statement.mysql> update mysql.user set password=password"root" where User="root"；Query OK,3 rows affected 0.00secRows matched:3 Changed:3 Warnings:0mysql> flush privileges；Query OK,0 rows affected 0.00sec mysql> exitByeC:Usersliqiong>mysql -uroot -p Enter password: *Welcome to the MySQL monitor. Commands end with； or g. Your MySQL connection id is5欢迎下载精品学习资源Server version:Copyright c5.5 . 20 MySQL Community Server GPL2000 ,2021 , Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respective owners.Type'help statement.mysql>；' or'h'for help. Type'c'to clear the current input欢迎下载